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(+))