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