ETL Session monitoring while loading data

Hi All,

This are sql which are handy while running ETL jobs/Procedure and monitoring what is happening in your db. Can give us good insight why our code is running slow

/* First understand how much UNDO, Tempspace, CPU you have for env */

select tablespace_name,bytes/1024/1024/1024 in_gb from dba_data_files

where tablespace_name like '%UND%'

SELECT tablespace_name,

  SUM(bytes_used)/1024/1024/1024 IN_GB,

  SUM(bytes_free)

FROM V$temp_space_header

GROUP BY tablespace_name;

show parameter cpu_count

/* important you need to multiply block size with block to get space used */

show parameter block_size

/* How big is the table you are looking at */

select blocks,BYTES/1024/1024/1024 from dba_SEGMENTS

where tableSPACE_NAME = 'TBS_VBR_REP_DAT'

AND SEGMENT_NAME = 'EBM_RESULTS'

select * from dba_SEGMENTS

/* Finding session info, once you are connected */

select * from v$session

where osuser = ' '

select sid,saddr,serial#,state,event,seconds_in_wait time_it_waiting_now,

wait_time/100 time_in_past_waited,username,server,program,type,p1,p2 from v$session

where osuser = ' '

select sid,saddr,serial#,sql_id,state,event,seconds_in_wait time_it_waiting_now,

wait_time/100 time_in_past_waited,username,server,program,type,p1,p2 from v$session

where sid = '90'

/*checking plan for that sql */

select * from table (dbms_xplan.display_cursor('4vs9fmtywqty3'))

/* WHAT IS THE EVENT YOUR SESSION MOST WAITING ON */

select sid,Event,total_waits,time_waited/100,Average_wait,max_wait/100,wait_class from V$SESSION_EVENT

where sid = '90'

order by time_waited desc

/* Temp space your session is currently using */

select * from v$tempseg_usage

where  session_num = '1031'

and session_addr = '00000000DB44C080'

/* 8k is the size of our blocks so multiplied by 8000 to make bytes */

select (sum(blocks)*8000)/1024/1024/1024 in_GB from v$tempseg_usage

where  session_num = '1031'

and session_addr = '00000000DB44C080'

/* used_ublk tells how many undo block used by your session Multiply with block size, urec is number of records */

/* Before measure undo make sure indexes are dropped , Insert will generated least undo , Update slightly more and delete max */

select

   t1.sid,

   t1.username,

   t2.xidusn,

   t2.used_urec,

   t2.used_ublk,

   t2.log_io,

   t2.phy_io,

   t2.START_TIME

from

   v$session     t1,

   v$transaction t2

where

   t1.saddr = t2.ses_addr

   and sid = 90

  

/* Simple query to directly measure undo generation */