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);