How to get execution plans of sql that were run as part of ETL

Hi All,

This might look simple but queries come in handy. Always remember the plan which we get from explain plan is not the actual plan its only estimated plan. The plan that we are getting from below queries is the Actual plan

select a.*, to_char(substr(sql_text,1,400)) from

(

select sql_id, sum(executions_delta), sum(elapsed_time_delta)/1000/1000

from dba_hist_sqlstat where snap_id >= 13222-240

group by sql_id

)a, dba_hist_sqltext b

where a.sql_id=b.sql_id

order by 3 desc

 

Select * From Dba_Hist_Osstat

Where Stat_Name Like '%LOAD%'

And Snap_Id >= 14672-240

order by 6 desc

 

 Select * From Dba_Hist_Snapshot

 where snap_id in (1273,1173,1151,11218,12351)

Long running Loads and one Inactive session

Hi Guys,

Usually by looking at V$session, active session history we are able to find the issue with the load, what is happening. What are the event on which load is waiting like reading from temp space, updating index. However this was one long running load where i was not able to find any information on what is happening for almost half a day as if load is not running. 

Below are 3 of my favourite links from Oracle which will help you understand your ETL loads better than ever. Read it as generic examples. It can be applied to Sql server, Teradata anything

What is Session and How to find what is happening with ETL load

Different database events on which a session can be waiting and how to analyse them

How to find why a data load took long time. Analysis done after load has finished

 

Below is my analysis on session which Active session history did not capture 

Note - ACTIVE SESSION HISTORY will capture only if the session is active in case of long loads the session becomes inactive and active session history will not capture it DBA_HIST_ACTIVE_SESS_HISTORY

select * from DBA_HIST_ACTIVE_SESS_HISTORY--- look into this to see which session are running

select event,p1text,p2text,wait_class,blocking_session_status,current_obj#

,pga_allocated,temp_space_allocated from DBA_HIST_ACTIVE_SESS_HISTORY

MASTER QUERY

select count(event),event,current_obj#,object_name

 from DBA_HIST_ACTIVE_SESS_HISTORY,dba_objects  where sql_id = 'ABCCFDFDDD'

 and current_obj# = object_id

and sample_time >= '27-MAR-14 05.00.02.829000000 AM'

 group by event,current_obj#,object_name

 order by 1 desc