ETL load running slow- How to identiy issue

Hi All,

V$session is all you need to resolve your ETL load issue. It tells you what your sql is waiting for and what is happening with sql. Below are few notes that i found helpful. In my case parallel hint was causing too many slave processes and query coordinator was taking time to read all the messages from different slaves.

Below are very good articles by Arup Nanda which i found helpful

 Basics about v$session table , V$session_event and Active session history

http://www.oracle.com/technetwork/issue-archive/2013/13-jan/o13dba-1871177.html

http://www.dba-oracle.com/t_awr_report_cpu.htm

http://www.oracle.com/technetwork/issue-archive/2012/12-jul/o42dba-1566567.html

select sid, state,event,SECONDS_IN_WAIT,BLOCKING_SESSION,BLOCKING_INSTANCE,

row_wait_obj#,

       row_wait_file#,

       row_wait_block#,

       row_wait_row#,p1,p2

from v$session

where username = 'ABC'

Now either you can check what is the blocker on either from row_wait_obj#, which is the object id in the dba objects or you can check with block id which is p1 and file id p2 which can be checked in dba_objects

select * from dba_objects

where object_id= 85988

-------------------------------------------------------------------------------------------------------------------------------------------------------------

select session_id, sample_time, session_state, event, wait_time, time_waited, sql_id, sql_child_number CH#

from v$active_session_history

where user_id =1000

and sample_time between

    to_date('27-FEB-14 09.00.00 AM','dd-MON-yy hh:mi:ss PM')

       and

    to_date('27-FEB-14 11.0.00 AM','dd-MON-yy hh:mi:ss PM')

select event, total_waits, (time_waited*10)/1000 tw_ms,

       average_wait*10 aw_ms, max_wait*10 mw_ms

from v$session_event

where sid in (

select sid from v$session

where username = 'ABC' and

OSUSER='KKKK' )

order by 3 desc