Useful V$session sql for Sql tuning

Hi All,

Below are some v$sql which i have found useful so i have noted down here . These are compilation of sql for wide range of issues

/*How many rows the sessoin has processed */
SELECT sql_id,SQL_FULLTEXT,DISK_READS,BUFFER_GETS,ROWS_PROCESSED,CPU_TIME/1000/1000,ELAPSED_TIME/1000/1000,PHYSICAL_READ_BYTES/1024/1024/1024 READ_BYTES_GB,executions FROM V$SQL WHERE SQL_ID IN (
'aq1d68pz6qfkq'
--select SQL_ID from v$session
--where sql_id = 'c2h4zw1a664fq'
)

/* how much temp space the session is using */
Select sample_time,event,sql_id,is_sqlid_current,temp_space_allocated/1024/1024 TEMP_MB
from v$active_session_history
where sql_id = '43rxgr4sga42b'
order by sample_time desc 

/* getting the Session id */
select sid from v$session
where sql_id = '43rxgr4sga42b'

/* On what is the session waiting */
select sid,Event,total_waits,time_waited/100,Average_wait,max_wait/100,wait_class from V$SESSION_EVENT
where sid = 584
order by total_waits desc 

/* How to find how many blocks a Table has, Crosscheck with Disk reads,Can also be used for finding size of index */ 
/* Instead of segment name but index name that should give size of index*/ 
select PARTITION_NAME,TABLESPACE_NAME,BLOCKS,BYTES/1024/1024/1024 size_of_table from dba_SEGMENTS
where SEGMENT_NAME = 'PROVIDER_DIM'
21+2+

/*Right now what it is doing */
select sample_time,sql_plan_operation,sql_plan_options,event,p1 file_no,p2 blockno,session_state,pga_allocated/1024/1024,temp_space_allocated from  v$active_session_history
where sql_id = 'gm3u8qa8kkt8v'
--and event = 'db file sequential read'
order by sample_time desc

/* what is the file and block on which its waiting as of now */ 
SELECT p1 file#, p2 block#, p3 class#,EVENT
 FROM v$session_wait where sid =87

/* to what does that block relate to */ 
SELECT relative_fno, owner, segment_name, segment_type FROM dba_extents
 WHERE file_id in  
  ( 7
 ) and 1458141 between block_id and block_id + blocks-1

/* checking index names  */ 
SELECT *
FROM all_indexes
WHERE TABLE_NAME = 'PROVIDER'

/* checking what is the depth of index */
SELECT INDEX_NAME,BLEVEL,LEAF_BLOCKS
FROM all_indexes
WHERE TABLE_NAME = 'MEMBER_DIM'
AND INDEX_NAME = 'XPKMEMBER'

/*checking how is table partitioned */

ALL_PART_TABLES

select * from all_ind_columns
WHERE index_name IN ( SELECT INDEX_NAME
FROM all_indexes
WHERE TABLE_NAME = 'PROVIDER')

/*Checking Explain plans */
select * from table (dbms_xplan.display_awr('7t1yt7gt0kmrz'))
select * from table (dbms_xplan.display_cursor('7t1yt7gt0kmrz'))
/* helps in case you cant find plan in display_cursor */
select * from v$sql_plan
where sql_id = '7t1yt7gt0kmrz'


/* Temp space your SQL is currently using*/
select 
   a.username, 
     a.sql_id,
   sum(srt.blocks * 8 / 1024) "Used Space in MB",
   sum(srt.blocks * 8 / 1024)/1024 "Used Space in GB"
from 
   v$session    a,
   v$sort_usage srt 
where 
   a.saddr = srt.session_addr 
 --  and sid in ( select  sid FROM v$SESSION WHERE OSUSER='abc')
   group by a.username,  a.sql_id

If the SQL is no longer active and you need to find what caused the issue 

select max(snap_id) from dba_hist_snapshot

11672

select * from dba_objects where object_name=upper('p_load_member_measure_detail')

182790

select distinct sql_id from dba_hist_active_sess_history where snap_id>=11672-6 and plsql_entry_object_id=182790

/* Very helpful query to check how much time a Sql took to run */

select sql_id, sum(executions_delta),

sum(elapsed_time_delta)/1000/1000, sum(cpu_time_delta)/1000/1000,sum(rows_processed_delta),

sum(buffer_gets_delta), sum(disk_reads_delta) from dba_hist_sqlstat where sql_id in (

'56xcp6m5h01b7',

'5p8c3y8mmgs94'

) and  snap_id>=11672-6

group by sql_id

select * from dba_hist_sqltext where sql_id='5p8c3y8mmgs94'

select * from table(dbms_xplan.display_awr('5p8c3y8mmgs94'))

How to Invalidate a Plan

Gather stats with No_invalidate  option to make sure new plan is taken.

execute dbms_stats.gather_table_stats('SCHEMA','PROVIDER', cascade=>true, no_invalidate=>false);

Lack of Temp space causing performance issues

select  sid,serial#,state,seconds_in_wait,event,event#,blocking_session,program FROM v$SESSION
WHERE OSUSER='ABC'
AND USERNAME ='ORAKK'
and program = 'SQL Developer'


select session_id,object_name, object_type,CURRENT_OBJ#, event, sum(time_waited) from v$active_session_history, dba_objects
where session_id in
( select  sid FROM v$SESSION
WHERE OSUSER='ABC'
AND USERNAME ='ORAKK'
and blocking_session is null )
and object_id = CURRENT_OBJ#
group by object_name, object_type,CURRENT_OBJ#, event,session_id
order by 6 desc


select * from all_objects
where object_id =87968




 SELECT p1 file#, p2 block#, p3 class#,EVENT
 FROM v$session_wait where sid in (
 select  sid FROM v$SESSION
WHERE OSUSER='ABC'
AND USERNAME ='ORAKK'
and blocking_session is null)
and upper(event) like '%DIRECT%READ%TEMP%'
order by event


Session wait is important because it tells you what your session is blocked on









 SELECT relative_fno, owner, segment_name, segment_type
 FROM dba_extents
 WHERE file_id in 
  ( SELECT p1
 FROM v$session_wait where sid in (
 select  sid FROM v$SESSION
WHERE OSUSER='ABC'
AND USERNAME ='ORAKK'
and blocking_session is null)
and upper(event) like '%DIRECT%READ%TEMP%'
 ) and 1632256 between block_id and block_id+1632256-1



 select DISTINCT  from dba_extents
 WHERE SEGMENT_NAME = 'TEMP'


 where UPPER(SEGMENT_NAME) LIKE '%TEMP%'


 where file_id = 1509








select * from V$TEMPSEG_USAGE-- gives which queries are using temp segemnt


select * from  v$sort_usage


1. Ran the trend chart query in stage1
2. Checked the v$active_session_history table for what is the event that the query is waiting on
3. 'direct path read temp' --- This is the even that the query is waiting for
4. Screenshot for total time waited on this event below
5. What it means ??--- Query is pulling data from temp segment to PGA. (by passing SGA buffer cache). Most of the time query is waiting for pulling data from temp, which means i/o is not able to supply data to PGA at a good rate
6. Solution – Check for i/o -- is the server using RAID (mostly yes) .So why is it not able to supply the data at the rate required from temp


Number of records that need to be pulled by Quality trend chart for doing calculation is 9,993153- ( approx 10 million). There is a condition in the query for pivoting which is multiplying 10 million by 24 giving us 239 million.


To perform operation on 239 million query takes some time.




Below query gives the space in TEMP in GB being used by the query


select
   a.username,
   sum(srt.blocks * 8 / 1024) "Used Space in MB",
   sum(srt.blocks * 8 / 1024)/1024 "Used Space in GB"
from
   v$session    a,
   v$sort_usage srt
where
   a.saddr = srt.session_addr
   and sid in ( select  sid FROM v$SESSION WHERE OSUSER='ORAKK')
   group by a.username

















select * from V$TEMPSEG_USAGE


Get the block id from the tempseg_usage table and see who is using it


 


How to check on what event how much time is being taken. Useful to check how much time oracle takes to write to database


select  
   se.event,
   sum(se.total_waits),
   sum(se.total_timeouts),
   sum(se.time_waited/100) time_waited
from        
   v$session_event se,
   v$session       sess
where
   sess.sid = se.sid
   and sess.sid in  ( select  sid FROM v$SESSION
WHERE OSUSER='ABC'
AND USERNAME ='ORAKK'
and blocking_session is null )
group by se.event
order by 4 desc