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
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 */
