Hi All,
I am very poor at performance tuning side. Concurrent job completed in 60 mins. I need to find which part of the job was took more time.
I have find the job sid, sql_id but didn't find the explain for that sql_id. Could you please help on this.
SQL> select * from table(dbms_xplan.display_cursor('39c3gyvun83f9',null,'ALL'));
--------------------------------------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
SQL_ID 39c3gyvun83f9, child number 0
BEGIN DBMS_STATS.GATHER_TABLE_STATS( ownname => :ownname , tabname =>
:tabname , estimate_percent => :estimate_percent , degree => :degree ,
method_opt => :method_opt , block_sample => FALSE , partname =>
:partname , granularity => :granularity , stattab => :stattab , statown
=> :statown, no_invalidate => FALSE ,
cascade => TRUE ); end;
NOTE: cannot fetch plan for SQL_ID: 39c3gyvun83f9, CHILD_NUMBER: 0
Please verify value of SQL_ID and CHILD_NUMBER;
It could also be that the plan is no longer in cursor cache (check v$sql_plan)
SQL> select * from table(dbms_xplan.display_awr('39c3gyvun83f9',null,null,'ALL'));
--------------------------------------------------------------------------------------------------------------------------------------
No output
SQL> select
s.begin_interval_time,
s.end_interval_time ,
q.snap_id,
q.dbid,
q.sql_id,
q.plan_hash_value,
q.optimizer_cost,
q.optimizer_mode
from
dba_hist_sqlstat q,
dba_hist_snapshot s
where
-- q.dbid = nnnnnnn
--and
q.sql_id = '39c3gyvun83f9'
and q.snap_id = s.snap_id
and s.begin_interval_time between sysdate-2 and sysdate
order by s.snap_id desc;
--------------------------------------------------------------------------------------------------------------------------------------
1/4/2019 8:00:16.910 AM 1/4/2019 9:00:27.303 AM 616 3704048913 39c3gyvun83f9 0 0 ALL_ROWS
1/4/2019 7:00:06.247 AM 1/4/2019 8:00:16.910 AM 615 3704048913 39c3gyvun83f9 0 0 ALL_ROWS
1/3/2019 11:00:32.766 PM 1/4/2019 12:00:37.967 AM 607 3704048913 39c3gyvun83f9 0 0 ALL_ROWS
1/3/2019 3:00:56.713 PM 1/3/2019 4:00:01.428 PM 599 3704048913 39c3gyvun83f9 0 0 ALL_ROWS
1/3/2019 7:00:02.214 AM 1/3/2019 8:00:12.436 AM 591 3704048913 39c3gyvun83f9 0 0 ALL_ROWS
1/2/2019 11:00:28.319 PM 1/3/2019 12:00:33.007 AM 583 3704048913 39c3gyvun83f9 0 0 ALL_ROWS
1/2/2019 3:00:52.743 PM 1/2/2019 4:00:57.016 PM 575 3704048913 39c3gyvun83f9 0 0 ALL_ROWS