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