--Last Modified April 4, 2007
	--Made some formatting changes to make reports from 10g systems easier to read
--Last Modified Feb 21, 2007
	--Added " select * from V$version;" to try and catch cases when the database has been incompletly upgraded.
	--Added a free space check
--Last Modified Feb 08, 2007
	--Add a check for invalid indexes
--Last Modified Nov 27, 2006
	--Removed Table Row Lengths query
	--Added usernames to the "Worst Total SQL by CPU Time "& "Worst SQL by CPU Time per transaction" queries
	--Modified the "Find Large Blobs" to query dba_ views and not user_ views
--Last Modified June 19, 2006 
	--Added a query to report on average row lengths of each table which can be used to estimate memory footprints
	--Relocated Segement Statistics reports below the index defintions to allow for easier navigation

--Last Modified April 19
	--Added Shared Pool diagnostic information

--Last Modified April 4
	--Added queries from Fino to check cursors and parse counts
	

--Last Modified: Jan 27
	--Added a query to report on the contents of the dba_jobs table

--Last Modified: Jan 23, 2006
	--Added queries to report on rollback usage mostly for migration

--Last Modified: Jan 16, 2006
	--Added a report for histograms
	--Added some leaf blocks and clustering factor to report on indexes
	--Added query to report system statistics
	--Slighly modified formatting so as not output as many ********** lines (used for navigating between report sections)

--Last Modified: Dec 13, 2005
	--Changed Worst SQL By Buffer Gets per Execution to also output explain plans if statistics_level=all
	--Folded the query against dba_indexes into the one with dba_ind_columns and added AVG_LEAF_BLOCKS_PER_KEY to the output
	--Remove frag percent cacluation, proved not to be as useful as once thought
	--Removed one of the table output queries to make the report easier to deal with and added chain_cnt to the output
	--Added a Querys against the V$SEGMENT_STATISTICS to output segment level statistics to aid in idenifing objects that are being being heavily accessed
	--Added a query to output tables with more than 1000 rows (the tables where most tuning activity will be targeted)

--Last Modified: Nov 30, 2005
	--Added a query to report on Temp File I/O to determine if the pga_aggregate_target is under sized

--Last Modified: Aug 17, 2005
	--Added check to "Find SQL WithHigh RowCounts returned per execution or high total number of rows returned"
	--Removed one of the two QueueEntry checks

-- Modified: Aug 15, 2005
	--Added Sclability info query
	--Added rows_processed to the "Worst Total SQL by CPU Time" query

--Gather Info Script 

--This script needs to be run while logged in as either the sys or system user.  i.e.
--        sqlplus "sys/sys as sysdba" @d:\temp\gather_info_script

--A report.txt file will be generated in the directory from which SQL*Plus was started. 
--

--Some SQL in this report is 9i specific because of this, errors running this file against
--and 8i database can be ignored.

spool report.txt
set trimspool on

Prompt ***********************************************************************
set pagesize 50
select min(to_char(LOGON_TIME, 'mon-dd-fmday-am-hh-mi-ss')) "Instance Start Time"
from  v$session;

select to_char(sysdate, 'mon-dd-fmday-am-hh-mi-ss') "Script execution Time"
from  dual;

col days_up for 999.99
Select (sysdate - min(LOGON_TIME)) days_up from v$session;

Prompt ***********************************************************************
set linesize 200
show parameters
set linesize 1000
select * from V$instance;
 select * from V$version;
Prompt ***********************************************************************


col event  format        a25  heading "Wait Event" trunc
col timew     format    999,999,999.99  heading "Time(secs)|Waited"
select event,time_waited/100 timew, AVERAGE_WAIT
from   v$system_event
order by AVERAGE_WAIT;

col name for a30
set linesize 2000
Prompt **V$DB_CACHE_ADVICE*****************************************************
col name format a25
select * from V$DB_CACHE_ADVICE;
Prompt **V$PGA_TARGET_ADVICE*****************************************************
select * from V$PGA_TARGET_ADVICE;
Prompt **V$SHARED_POOL_ADVICE*****************************************************
select * from V$SHARED_POOL_ADVICE;
Prompt *Cursor Check**********************************************************
Prompt ** Open Cursors in Database********************************************
select name,value from v$sysstat where name like '%cursors current%';
Prompt ** Session Cursor Cache Hits ******************************************
select name,value from v$sysstat where name like '%session cursor cache hits';
Prompt ** Cursor Parse Counts ************************************************
select name,value from v$sysstat where name like '%parse count%';

/* shared pool diagnostic based on: Metalink Note:146599.1
https://metalink.oracle.com/metalink/plsql/f?p=130:14:7683074227855145454::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,146599.1,1,1,1,helvetica
*/


SELECT SUM(PINS) "EXECUTIONS",
            SUM(RELOADS) "CACHE MISSES WHILE EXECUTING"
            FROM V$LIBRARYCACHE;
            
            SELECT substr(sql_text,1,40) "SQL",
	    count(*) ,
	    sum(executions) "TotExecs"
	    FROM v$sqlarea
	    WHERE executions < 5
	    GROUP BY substr(sql_text,1,40)
	    HAVING count(*) > 30
ORDER BY 2;

SELECT * FROM X$KSMLRU WHERE ksmlrsiz > 0;

select '0 (<140)' BUCKET, KSMCHCLS, KSMCHIDX, 10*trunc(KSMCHSIZ/10) "From",
count(*) "Count" , max(KSMCHSIZ) "Biggest",
trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"
from x$ksmsp
where KSMCHSIZ<140
and KSMCHCLS='free'
group by KSMCHCLS, KSMCHIDX, 10*trunc(KSMCHSIZ/10)
UNION ALL
select '1 (140-267)' BUCKET, KSMCHCLS, KSMCHIDX,20*trunc(KSMCHSIZ/20) ,
count(*) , max(KSMCHSIZ) ,
trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"
from x$ksmsp
where KSMCHSIZ between 140 and 267
and KSMCHCLS='free'
group by KSMCHCLS, KSMCHIDX, 20*trunc(KSMCHSIZ/20)
UNION ALL
select '2 (268-523)' BUCKET, KSMCHCLS, KSMCHIDX, 50*trunc(KSMCHSIZ/50) ,
count(*) , max(KSMCHSIZ) ,
trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"
from x$ksmsp
where KSMCHSIZ between 268 and 523
and KSMCHCLS='free'
group by KSMCHCLS, KSMCHIDX, 50*trunc(KSMCHSIZ/50)
UNION ALL
select '3-5 (524-4107)' BUCKET, KSMCHCLS, KSMCHIDX, 500*trunc(KSMCHSIZ/500) ,
count(*) , max(KSMCHSIZ) ,
trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"
from x$ksmsp
where KSMCHSIZ between 524 and 4107
and KSMCHCLS='free'
group by KSMCHCLS, KSMCHIDX, 500*trunc(KSMCHSIZ/500)
UNION ALL
select '6+ (4108+)' BUCKET, KSMCHCLS, KSMCHIDX, 1000*trunc(KSMCHSIZ/1000) ,
count(*) , max(KSMCHSIZ) ,
trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"
from x$ksmsp
where KSMCHSIZ >= 4108
and KSMCHCLS='free'
group by KSMCHCLS, KSMCHIDX, 1000*trunc(KSMCHSIZ/1000);


SELECT pool,name,bytes FROM v$sgastat where pool = 'large pool';


Prompt *Scalability info***********************************************

select sum(FETCHES) FETCHES,
sum(ROWS_PROCESSED) ROWS_PROCESSED,
sum(EXECUTIONS) EXECUTIONS,
sum(LOADS) LOADS,
sum(PARSE_CALLS) PARSE_CALLS,
sum(DISK_READS) DISK_READS,
sum(BUFFER_GETS) BUFFER_GETS,
sum(CPU_TIME*0.000001) CPU_TIME_secs,
sum(ELAPSED_TIME*0.000001) ELAPSED_TIME_Secs
from v$sql;

set linesize 190
Prompt *System Statistics***********************************************

select pname, pval1 from  sys.aux_stats$  where            sname = 'SYSSTATS_MAIN';

Prompt *DBA_jobs report***********************************************
set linesize 350
col INTERVAL for a50
col WHAT  for a50
select JOB, LOG_USER,LAST_DATE,TOTAL_TIME,BROKEN,INTERVAL,FAILURES,WHAT from dba_jobs;

Prompt *Queue Entry Table Check***********************************************
Select sum(bytes) aSum, segment_name,owner  from dba_segments where Segment_name='QUEUEENTRY' group by segment_name,owner;

Prompt *EndQueue Entry Table Check***********************************************
Prompt ***Check to make sure log mode and archiver don't conflict*************
col name for a10
select NAME, LOG_MODE,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL from v$database;
show parameter log_archive_start

Prompt ***********************************************************************
Prompt ***Check the disk I/O occuring in the system***************************

col name for a100
col "Ratio" for 999.999
set linesize 600
set pagesize 75
col TABLESPACE for a10
select v$datafile.name ,V$TABLESPACE.name Tablespace, v$filestat.*
from v$filestat, v$datafile,V$TABLESPACE where
v$datafile.FILE# = v$filestat.FILE#
and PHYBLKRD / (decode(PHYRDS, 0,1,phyrds)) >= 1
and v$datafile.TS#=v$TABLESPACE.TS#
order by 5;

col TempFile for a80
col "Ratio" for 999.999
set linesize 250
set pagesize 75
select name TempFile ,PHYBLKRD "BLKs Read",PHYRDS "Phys Read", READTIM,WRITETIM,AVGIOTIM,LSTIOTIM,MINIOTIM,MAXIORTM,MAXIOWTM
from V$TEMPSTAT,  V$TEMPFILE where
V$TEMPFILE.FILE# = V$TEMPSTAT.FILE#
and PHYBLKRD / (decode(PHYRDS, 0,1,phyrds)) >= 1
order by 4;

set linesize 170
select TABLESPACE_NAME,RELATIVE_FNO,BYTES FreeBytes,BLOCKS freeblocks from DBA_FREE_SPACE;
Prompt ******Rollback Reports****************
Prompt **The queries relating to rollbacks were not built from scracth*
Prompt **they were pulled from http://www.akadia.com/services/ora_optimize_undo.html**

show parameters undo

SELECT SUM(a.bytes) "UNDO_SIZE"
  FROM v$datafile a,
       v$tablespace b,
       dba_tablespaces c
 WHERE c.contents = 'UNDO'
   AND c.status = 'ONLINE'
   AND b.name = c.tablespace_name
   AND a.ts# = b.ts#;
   
   SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
         "UNDO_BLOCK_PER_SEC"
  FROM v$undostat;
  
  Prompt**Undo Size = Undo retention*block size*Undo block per Sec *****

SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
       SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
       (TO_NUMBER(e.value) * TO_NUMBER(f.value) *
       g.undo_block_per_sec) / (1024*1024) 
       "NEEDED UNDO SIZE [MByte]"
  FROM (
       SELECT SUM(a.bytes) undo_size
         FROM v$datafile a,
              v$tablespace b,
              dba_tablespaces c
        WHERE c.contents = 'UNDO'
          AND c.status = 'ONLINE'
          AND b.name = c.tablespace_name
          AND a.ts# = b.ts#
       ) d,
       v$parameter e,
       v$parameter f,
       (
       SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
          undo_block_per_sec
         FROM v$undostat
       ) g
 WHERE e.name = 'undo_retention'
  AND f.name = 'db_block_size'
/



Prompt *******Find Large Blobs***************************************************

col COLUMN_NAME for a30
select b.TABLE_NAME,b.COLUMN_NAME,a.TABLESPACE_NAME,sum(bytes) Bytes, sum(blocks)
from dba_segments a, dba_lobs b 
where a.SEGMENT_NAME=b.SEGMENT_NAME
group by b.TABLE_NAME,b.COLUMN_NAME,a.TABLESPACE_NAME
having sum(bytes)> 3000000
order by 4;
Prompt *Report on Tables *****************************************************

set linesize 240
col OWNER for a20
col TABLE_NAME for a30
col NUM_ROWS for 999,999,999
col BLOCKS for 999,999,999
col AVG_ROW_LEN for 999,999
col CHAIN_CNT for 999.99
--select OWNER,TABLE_NAME,NUM_ROWS,BLOCKS,AVG_ROW_LEN,CHAIN_CNT,LAST_ANALYZED,(case when blocks <100 then 0 else (1-((num_rows*avg_row_len )/((case when blocks =0 then 1 else blocks end)*16384)))*100 end) frag_per 
select OWNER,TABLE_NAME,NUM_ROWS,BLOCKS,AVG_ROW_LEN,CHAIN_CNT,LAST_ANALYZED
from dba_tables
where OWNER not in ('SYS','SYSTEM','WMSYS','WKSYS','XDB','QS','QS_CBADM','QS_CS','QS_ES','QS_OS','QS_WS','ODM','ODM_MTR','OLAPSYS','ORDSYS','OUTLN')
order by 1,2;
Prompt **End Report on Tables*************************************************
Prompt *Report Tables that have more than 10000 rows**************************

set linesize 240
col OWNER for a20
col TABLE_NAME for a30
col NUM_ROWS for 999,999,999
col BLOCKS for 999,999,999
col AVG_ROW_LEN for 999,999
col CHAIN_CNT for 999.99
--select OWNER,TABLE_NAME,NUM_ROWS,BLOCKS,AVG_ROW_LEN,CHAIN_CNT,LAST_ANALYZED,(case when blocks <100 then 0 else (1-((num_rows*avg_row_len )/((case when blocks =0 then 1 else blocks end)*16384)))*100 end) frag_per 
select OWNER,TABLE_NAME,NUM_ROWS,BLOCKS,AVG_ROW_LEN,CHAIN_CNT,LAST_ANALYZED
from dba_tables
where OWNER not in ('SYS','SYSTEM','WMSYS','WKSYS','XDB','QS','QS_CBADM','QS_CS','QS_ES','QS_OS','QS_WS','ODM','ODM_MTR','OLAPSYS','ORDSYS','OUTLN')
and NUM_ROWS >=10000
order by NUM_ROWS;


Prompt **End Report Tables that have more than 10000 rows*******************************


Prompt **Report on Indexes ***************************************************

set linesize 350
set pagesize 100
col index_owner for a20
col table_name for a30
col index_name for a30 
col column_name for a25
col column_position for 99
col CLUSTERING_FACTOR for 999,999,999,999,999
col LEAF_BLOCKS for 999,999,999

select a.index_owner, a.table_name,a.index_name,a.column_name,a.column_position,DISTINCT_KEYS,AVG_LEAF_BLOCKS_PER_KEY,CLUSTERING_FACTOR,LEAF_BLOCKS
from dba_ind_columns a,dba_indexes b
where a.INDEX_OWNER not in ('SYS','SYSTEM','WMSYS','WKSYS','XDB','QS','QS_CBADM','QS_CS','QS_ES','QS_OS','QS_WS','ODM','ODM_MTR','OLAPSYS','ORDSYS','OUTLN')
and a.INDEX_NAME=b.INDEX_NAME and a.INDEX_OWNER=b.owner
order by 1,2,3,5;

set linesize 150
Prompt *End Rerport on Indexes************************************************
Prompt **Report for Indexes Not VALID ***************************************************

set linesize 350
set pagesize 100
col index_owner for a20
col table_name for a30
col index_name for a30 
col column_name for a25
col column_position for 99
col CLUSTERING_FACTOR for 999,999,999,999,999
col LEAF_BLOCKS for 999,999,999

select b.owner, b.table_name,b.index_name, FUNCIDX_STATUS,STATUS
from dba_indexes b
where b.OWNER not in ('SYS','SYSTEM','WMSYS','WKSYS','XDB','QS','QS_CBADM','QS_CS','QS_ES','QS_OS','QS_WS','ODM','ODM_MTR','OLAPSYS','ORDSYS','OUTLN')
and (b.FUNCIDX_STATUS ='DISABLED' or STATUS !='VALID')
order by 1,2,3;

set linesize 150
Prompt *Report for invalid Indexes************************************************

Prompt **Report on Histograms************************************************

set linesize 300
col ENDPOINT_NUMBER for 999,999,999,999
col ENDPOINT_ACTUAL_VALUE for a100

select owner, TABLE_NAME,COLUMN_NAME,ENDPOINT_NUMBER,ENDPOINT_VALUE,ENDPOINT_ACTUAL_VALUE
from DBA_HISTOGRAMS
where ENDPOINT_VALUE >1
order by 1,2;
Prompt **End Report on Histograms*************************************
Prompt *Report on SegmentStatistics *****************************************************
col STATISTIC_NAME for a30
select OBJECT_TYPE,STATISTIC_NAME,sum(value) from V$SEGMENT_STATISTICS group by OBJECT_TYPE,STATISTIC_NAME order by 3;
select TABLESPACE_NAME,STATISTIC_NAME,sum(value) from V$SEGMENT_STATISTICS group by TABLESPACE_NAME,STATISTIC_NAME order by 1,3;

select OBJECT_NAME,TABLESPACE_NAME,OBJECT_TYPE,STATISTIC_NAME,VALUE from V$SEGMENT_STATISTICS
where value > 100 order by value,STATISTIC_NAME,OBJECT_NAME;

Prompt **End Report on SegmentStatistics*************************************

Prompt **Find Update Heavy Operations*****************************************

col LAST_LOAD_TIME for a20
col SQL_TEXT for a60
select username, LAST_LOAD_TIME,EXECUTIONS,SQL_TEXT from v$sql, dba_users
where 
(upper(SQL_TEXT) like '%INSERT%'
or upper(SQL_TEXT) like '%DELETE%'  
or upper(SQL_TEXT) like '%UPDATE%'
or upper(SQL_TEXT) like '%DBMS_LOB.WRITE%')
and upper(SQL_TEXT) not like '%SELECT%'
and dba_users.user_id=v$sql.PARSING_USER_ID 
order by EXECUTIONS;

Prompt **End Find Update Heavy Operations*************************************
Prompt *Worst Total SQL by CPU Time ************************************************
 
set linesize 191
set pagesize 100
col username for a16
col cpu_time_secs for 999,999,999,999
col elapsed_time_secs  for 999,999,999,999,999
col Elap_Exec  for 999,999,999,999
col ROWS_PROCESSED for 999,999,999,999,999
 
col executions for 999,999,999
col sql_text for a65
select c.username, CPU_TIME*0.000001 cpu_time_secs,
ELAPSED_TIME*0.000001 elapsed_time_secs,round(ELAPSED_TIME*0.000001/executions) Elap_per_Exec,
executions,ROWS_PROCESSED,b.piece,b.sql_text
from v$sql a, v$sqltext b,dba_users c
where 
a.address=b.address
and
(
ELAPSED_TIME*0.000001>5
or executions  > 1000
)
and executions>0
and c.user_id=a.PARSING_USER_ID 
order by ELAPSED_TIME,CPU_TIME,a.HASH_VALUE, b.piece asc
/
 
Prompt *END Worst Total SQL by CPU Time **************************************
Prompt *Worst SQL by CPU Time per transaction*********************************
 
set linesize 180
set pagesize 100
col username for a16
col cpu_time_secs for 999,999,999,999
col elapsed_time_secs  for 999,999,999,999,999
col Elap_Exec  for 999,999,999,999
col LAST_LOAD_TIME for a20
 
col executions for 999,999,999
col sql_text for a65
select c.username,CPU_TIME*0.000001 cpu_time_secs,
ELAPSED_TIME*0.000001 elapsed_time_secs,round(ELAPSED_TIME*0.000001/executions) Elap_per_Exec,
executions,LAST_LOAD_TIME,b.piece,b.sql_text
from v$sql a, v$sqltext b, dba_users c
where 
a.address=b.address
and
(
ELAPSED_TIME*0.000001>5
or executions  > 1000
)
and executions>0
and c.user_id=a.PARSING_USER_ID 
order by Elap_per_Exec,ELAPSED_TIME,CPU_TIME,a.HASH_VALUE, b.piece asc;
/

Prompt *END Worst SQL by CPU Time per transaction*****************************

Prompt *Worst SQL By Buffer Gets per Execution********************************
/* 
Uncomment this code when statistics level = all to get explain plans
This code in it's current form is lengthy in execution and could negatively effect production systems
variable v_statistics_level varchar2(10);
begin
	select upper(value) into :v_statistics_level from v$system_parameter where name = 'statistics_level';
end;
/
print v_statistics_level


--Assume 100 000 Logical IO's per second for a 1GHZ machine
--Assume 100 Physical IO's a second
--PIO * 1000 = LIO
set linesize 120
set serveroutput on size 1000000
begin
    dbms_output.put_line (rpad('buffer_gets',10,' ')||rpad('disk_reads',10,' ')||rpad('executions',10, ' ')||rpad('ge', 10, ' ')||rpad('piece', 10, ' ')||rpad('sql_text',64,' '));
    dbms_output.put_line (rpad('rows',10, ' ')||'Execution_Plan');

    for c_address in (select distinct address from v$sql where (
            buffer_gets>100000
            or executions  > 1000
            )
            and executions>0)
    loop
        -- Print the sql script.
        for c_1 in (select a.address, buffer_gets,disk_reads,executions, buffer_gets/executions ge, b.piece,b.sql_text
            from v$sql a, v$sqltext b
            where 
            a.address=b.address and
            a.address = c_address.address and
            executions > 0
            order by buffer_gets/executions ,a.HASH_VALUE, b.piece asc)
        loop
          dbms_output.put_line (rpad(c_1.buffer_gets,10,' ')||rpad(c_1.disk_reads,10,' ')||rpad(c_1.executions,10, ' ')||rpad(round(c_1.ge,2), 10, ' ')||rpad(c_1.piece, 10, ' ')||rpad(c_1.sql_text,64,' '));
        end loop;
        
        -- Print the execution plan if statistics_level='all'
        if :v_statistics_level = 'ALL' then
            for c_2 in ( select c.output_rows output_rows, LPAD(' ',2*(c.depth-1))||c.operation||Nvl2(c.optimizer, 'GOAL: '||c.optimizer, '')||nvl2(c.options, ' ('||c.options||') ', '')||c.Object_name Execution_Plan from V$SQL_PLAN_STATISTICS_all c where c.address = c_address.address)
            loop
                dbms_output.put_line (rpad(c_2.output_rows,10, ' ')||c_2.Execution_Plan);
            end loop;
        end if;	
    end loop;
end;
/
*/
Prompt *End Worst SQL By Buffer Gets per Execution****************************
Prompt *Worst SQL By Factor **************************************************

set linesize 150
set pagesize 100
col  buffer_gets for 999,999,999,999
col disk_reads  for 999,999,999,999
col "% Bad" for 99.999
col sql_text for a64
select buffer_gets,disk_reads,executions,b.piece,((disk_reads*1000)+buffer_gets)/tot.sumed*100  "% Bad" ,b.sql_text
from v$sql a, v$sqltext b,
(select sum((disk_reads*1000)+buffer_gets) sumed from v$sql) tot
where 
a.address=b.address
and
(
disk_reads > 1000
or buffer_gets>100000
or executions  > 1000
)
order by  disk_reads*1000 + buffer_gets ,a.HASH_VALUE,b.piece asc
/
Prompt *End Worst SQL By Factor **********************************************
Prompt *Find SQL where LIOs are close to PIOs*********************************

--Find SQL where LIOs are close to PIOs


set linesize 250
set pagesize 100
col  buffer_gets for 999,999,999,999
col dr_per_exec for 999,999,999,999
col "Percent_disk" for 999.99
col sql_text for a64
select buffer_gets,disk_reads,executions,disk_reads/executions dr_per_exec,b.piece,disk_reads/buffer_gets*100 Percent_disk  ,b.sql_text
from v$sql a, v$sqltext b
where 
a.address=b.address
and disk_reads >1000
and executions >0
order by (buffer_gets/disk_reads)desc,disk_reads/executions ,a.HASH_VALUE,b.piece asc
/
Prompt *END Find SQL where LIOs are close to PIOs*****************************
Prompt *Find SQL ordered by PIOs**********************************************



set linesize 250
set pagesize 100
col  buffer_gets for 999,999,999,999
col dr_per_exec for 999,999,999,999
col gets_per_exec 999,999,999,999
col "Percent_disk" for 99.999
col sql_text for a64
select buffer_gets,disk_reads,executions,disk_reads/executions dr_per_exec,round(buffer_gets/executions) gets_per_exec, b.piece,b.sql_text
from v$sql a, v$sqltext b
where 
a.address=b.address
and buffer_gets >1000
and executions >0
order by  disk_reads,a.HASH_VALUE,b.piece asc
/

Prompt *END Find SQL ordered by PIOs*****************************************
Prompt *Find SQL ordered by Execution Counts**********************************



set linesize 250
set pagesize 100
col  buffer_gets for 999,999,999,999
col dr_per_exec for 999,999,999,999
col gets_per_exec 999,999,999,999
col "Percent_disk" for 99.999
col sql_text for a64
select buffer_gets,disk_reads,executions,disk_reads/executions dr_per_exec,round(buffer_gets/executions) gets_per_exec, b.piece,b.sql_text
from v$sql a, v$sqltext b
where 
a.address=b.address
and buffer_gets >0
and executions >1000
order by  executions,a.HASH_VALUE,b.piece asc
/

Prompt *END Find SQL ordered by Execution Counts************************************
Prompt *Find SQL WithHigh RowCounts returned per execution or high total number of rows returned**********************************


set linesize 126
set pagesize 100
col  buffer_gets for 999,999,999,999
col rows_per_exec for 999,999,999,999
col gets_per_exec 999,999,999,999
col ROWS_PROCESSED 999,999,999,999
col "Percent_disk" for 99.999
col sql_text for a64


select  a.executions,a.ROWS_PROCESSED,a.ROWS_PROCESSED/a.executions Rows_per_exec, b.piece,b.sql_text
from (select address, HASH_VALUE,executions,ROWS_PROCESSED from v$sql where executions != 0) a, v$sqltext b
where 
a.address=b.address
and a.executions != 0
order by  a.ROWS_PROCESSED,Rows_per_exec,a.HASH_VALUE,b.piece asc
/

Prompt *END Find SQL With High RowCounts returned per execution or high total number of rows returned**********************************

spool off