1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

what this statement is doing?

Discussion in 'SQL PL/SQL' started by ecivgamer, Sep 14, 2013.

  1. ecivgamer

    ecivgamer Active Member

    Messages:
    73
    Likes Received:
    0
    Trophy Points:
    130
    Hi all,



    I'm having odds to understand what this statement is doing, please find code below.

    It consumes 99.28% of cpu at particular time and lasts for 1285 seconds, ``explain plan`` shows that it is executed with ``table access full``.

    Other statements work hardly because system is overloaded.

    I'm not sured I can change it 'cause it is not mine, but at least I could advice to reschedule it at another part of day.

    But first I should know what does it do to describe it for my boss. Does it show scheduled jobs or what?

    Help me please, thanks ahead.



    WITH pm_retention AS

    (

    SELECT 'x' x,

    quest_ppcm_collector.get_ppcm_parameter ( 'PM_SNAPSHOT_RETENTION_DAYS' ) pm_snapshot_retention_days

    FROM DUAL

    ),

    pm_job AS

    (

    SELECT 'x' x,

    owner,

    job_name,

    CAST (start_date AS date) start_date,

    CAST (last_start_date AS date) last_start_date,

    CAST (next_run_date AS date) next_run_date,

    quest_ppcm_collector.get_job_repeat_minutes ( start_date, repeat_interval ) repeat_minutes,

    enabled

    FROM dba_scheduler_jobs

    WHERE owner IN (

    SELECT SYS_CONTEXT ('USERENV', 'SESSION_USER')

    FROM DUAL

    UNION

    SELECT table_owner

    FROM user_synonyms

    WHERE synonym_name = 'QUEST_PPCM_SNAPSHOT')

    AND job_name = 'QUEST_PPCM_JOB_PM_' || SYS_CONTEXT ('USERENV', 'INSTANCE')

    UNION ALL

    SELECT 'x' x,

    priv_user owner,

    TO_CHAR (job) job_name,

    last_date start_date,

    last_date last_start_date,

    next_date next_run_date,

    quest_ppcm_collector.get_job_interval_minutes (interval) repeat_minutes,

    case when broken='Y' then 'FALSE' else 'TRUE' end enabled

    FROM dba_jobs

    WHERE priv_user I N (

    SELECT SYS_CONTEXT ('USERENV', 'SESSION_USER')

    FROM DUAL

    UNION

    SELECT table_owner

    FROM user_synonyms

    WHERE synonym_name = 'QUEST_PPCM_SNAPSHOT'

    )

    AND instance = SYS_CONTEXT ('USERENV', 'INSTANCE')

    AND what = 'BEGIN quest_ppcm_collector.take_snapshot(''PM''); END;'

    )



    SELECT owner, job_name, start_date, last_start_date, next_run_date, ROUND (repeat_minutes, 2) repeat_minutes, enabled,

    pm_snapshot_retention_days

    FROM pm_retention JOIN pm_job ON (pm_retention.X = pm_job.X(+))
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Looks like you have Spotlight or Foglight installed; these statements are querying the data dictionary for information about a snapshot process owned by quest_ppcm_collector. This is par for the course for many monitoring tools. I'll ask this -- is there also a step that shows that a MERGE JOIN CARTESIAN is being executed? If so, and you're on 10g or later, you should probably use dbms_stats to gather fixed object stats:

    exec dbms_stats.gather_fixed_objects_stats()

    or

    exec dbms_stats.gather_fixed_objects_stats(NULL)


    Read here:

    http://dfitzjarrell.wordpress.com/2008/01/28/lies-damned-lies-and-statistics/

    It explains why you need to gather these statistics.