These are just a few SQL’s I use to find out about performance issues.
Sorry the page isn’t simple but WordPress seems to be being an ass when it comes to accessing files.
Use at your own risk!
Not validated for correctness or even working under all circumstances.
Think about what you are running in your database.
SCRIPTS
perf_advice - query advisors (unreliable at large allocations)perf_blockers - show blocking from gv$sessionperf_longquery - look through dba_hist_active_sess_history and see where we have queries - outside of transactions! - running longperf_longtrans - look through ASH history and see where we have transactions (XID) running longer than 3 minutesperf_monrep - generate monitoring report : dbms_perf.REPORT_SQLperf_pga - check the overall settings, generic SQL and ASH by processperf_planflip - show sql's with more than 1 PHVperf_sesstemp - show pga and TEMP for sessions, and grouped by Parallel QCperf_sga - show some SGA information, including subpools and resize operationsperf_spm_hints - show all of the hints associated with sql_profiles/_patch/_plan_baselinesperf_sqlcost - for a sql_id, show historic costs; dba_hist_sqlstat, ASH, GV$SQL infoperf_sqlobj - show object information associated with a SQL_IDperf_sqlplan - dbms_xplan.display_cursor, awr, manual extract of plan inc some XML,perf_sqlstats - show stats and indexes related to OBJECTS in a sql_idperf_statsprefs - show global, inc hidden, and table stats prefsperf_stathist - show statistics history for a tableperf_tabfrag - check dbms_space to see how fragmented a table has become
perf_sqlobj – show object information associated with a SQL_ID
perf_sqlobj.sql
-- perf_sqlobj.sql v1.0 n chandler.-- v1.1 n chandler. 2025-11-20 account for covering indexes-- v1.2 n chandler. 2025-11-28 formatting some columns--prompt show object relevant statistics for tables/indexes accessed by a sql_idprompt object names are pulled out of dba_hist_sql_plan and basic info shownprompt this should help you determine staleness, selectivity and cardinality, and identify alternate execution optionsprompt also ensure SAMPLE is 0 or 100% - anything else is using ESTIMATE which is less stable and can lead to bad histogramsset pages 200 lines 250col OWNER for a30col TABLE_NAME for a30col OBJECT_OWNER for a30col COLUMN_NAME for a30col PARTITION_NAME for a15col SUBPARTITION_NAME for a30col STALE_STATS for a5col SAMPLE for a5col OBJECTS for a80col plan_hash_values for a80 wrapcol object_type for a10col density for 0.999999999999999col num_distinct for 999999999999999col num_nulls for 999999999999999define sql_idundefine sql_idbreak on index_nameprompt Enter the sql_id of the query to pull all objects out of the execution plansprompt &&sql_idspool perf_sqlstats.&&sql_id..out appendpromptprompt What objects are in the execution plans:prompt all objects may not appear in all executions plansprompt - different indexes can be selectedprompt - objects can be transformed out of plansprompt - covering indexes can eleminate table accessWITH objects as ( SELECT distinct dhsp.object_type||': '||dhsp.object_owner||'.'||dhsp.object_name objects ,dhsp.sql_id ,dhsp.plan_hash_value FROM dba_hist_sql_plan dhsp WHERE 1=1 AND ( dhsp.object_type like '%TABLE%' OR dhsp.object_type like '%INDEX%' ) AND sql_id = '&&sql_id' UNION SELECT distinct gvp.object_type||': '||gvp.object_owner||'.'||gvp.object_name objects ,gvp.sql_id ,gvp.plan_hash_value FROM gv$sql_plan gvp WHERE 1=1 AND ( gvp.object_type like '%TABLE%' OR gvp.object_type like '%INDEX%' ) AND sql_id = '&&sql_id' ORDER BY 1)SELECT objects,sql_id,LISTAGG(plan_hash_value,' ' ON OVERFLOW TRUNCATE) WITHIN GROUP (order by plan_hash_value) plan_hash_values FROM objects GROUP BY objects,sql_id ORDER BY objects,sql_id/promptprompt object_stats for table and indexes used in the queryprompt if you are using a covering index on a partitioned table, partition stats will not showWITH objects AS ( SELECT dhsp.object_type ,dhsp.object_owner ,dhsp.object_name ,dhsp.sql_id FROM dba_hist_sql_plan dhsp WHERE 1=1 AND ( dhsp.object_type like '%TABLE%' OR dhsp.object_type like '%INDEX%' ) AND sql_id = '&&sql_id' UNION SELECT gvp.object_type ,gvp.object_owner ,gvp.object_name ,gvp.sql_id FROM gv$sql_plan gvp WHERE 1=1 AND ( gvp.object_type like '%TABLE%' OR gvp.object_type like '%INDEX%' ) AND sql_id = '&&sql_id' ORDER BY 1)SELECT objects.object_type ,NVL(dts.owner,dis.owner) owner ,NVL(dts.table_name,dis.table_name) table_name ,dis.index_name ,NVL(dts.partition_name,dis.partition_name) partition_name ,NVL(dts.subpartition_name,dis.subpartition_name) subpartition_name ,NVL(dts.global_stats,dis.global_stats) global_stats ,NVL(dts.num_rows,dis.num_rows) num_rows ,NVL(dts.blocks,dis.leaf_blocks) blocks ,NVL(dts.last_analyzed,dis.last_analyzed) last_analyzed ,NVL(dts.stale_stats,dis.stale_stats) stale_stats ,NVL(dts.stattype_locked,dis.stattype_locked) locked ,dts.avg_row_len ,dis.clustering_factor -- DECODE to prevent divide by 0 error ,TO_CHAR(ROUND((NVL(dts.sample_size,dis.sample_size)/NVL(DECODE(dts.num_rows,0,1,dts.num_rows),DECODE(dis.num_rows,0,1,dis.num_rows)))*100,2))||'%' sample FROM objects LEFT OUTER JOIN dba_tab_statistics dts ON (dts.owner = objects.object_owner AND dts.table_name=objects.object_name) LEFT OUTER JOIN dba_ind_statistics dis ON (dis.owner = objects.object_owner AND dis.index_name=objects.object_name)WHERE objects.sql_id = '&&sql_id'ORDER BY dts.owner,dts.table_name,dts.partition_name NULLS FIRST,dts.subpartition_name NULLS FIRST,dis.index_name/col index_owner for a30col table_owner for a30col is_used_in_plan for a16promptprompt now show every index definition on the relevant tables so we can see if there could be an alternativeprompt you will need to look at the predicates / FILTER conditions in the execution plan to determineprompt if other indexes could be suitable for the query-- get all tables and indexes from the plan-- pull all table names associated with those objects-- and show indexes related to the table namesWITH feed AS ( SELECT dhsp.object_type ,dhsp.object_owner ,dhsp.object_name ,dhsp.sql_id FROM dba_hist_sql_plan dhsp WHERE 1=1 AND ( dhsp.object_type like '%TABLE%' OR dhsp.object_type like '%INDEX%' ) AND sql_id = '&&sql_id' UNION SELECT gvp.object_type ,gvp.object_owner ,gvp.object_name ,gvp.sql_id FROM gv$sql_plan gvp WHERE 1=1 AND ( gvp.object_type like '%TABLE%' OR gvp.object_type like '%INDEX%' ) AND sql_id = '&&sql_id' ORDER BY 1), -- now we need to add all of the tables for the indexes mentioned above in case of covering index objects AS ( SELECT ind.table_owner, ind.table_name, ind.index_name FROM dba_indexes ind JOIN feed obj ON (obj.object_owner=ind.table_owner AND (obj.object_name = ind.table_name OR obj.object_name = ind.index_name) ) )SELECT dic.table_owner ,dic.table_name ,dic.index_owner ,dic.index_name ,dic.column_name ,dic.column_position ,dic.descend ,(select 'YES' from feed WHERE dic.index_name IN (SELECT feed.object_name FROM feed) AND rownum = 1) is_used_in_plan FROM dba_ind_columns dic JOIN objects ob ON (dic.table_owner=ob.table_owner AND (dic.table_name = ob.table_name OR dic.index_name = ob.index_name)) GROUP BY dic.table_owner,dic.table_name,dic.index_owner,dic.index_name,dic.column_name,dic.column_position,dic.descend ORDER BY dic.table_owner,dic.table_name,is_used_in_plan desc NULLS LAST,dic.index_owner,dic.index_name,dic.column_position/col column_name for a30col data_type for a10 truncatecol nullable for a4col histogram for a30prompt and lets have a look at those columns, just in the table (global stats if partitioned)WITH feed AS ( SELECT dhsp.object_type ,dhsp.object_owner ,dhsp.object_name ,dhsp.sql_id FROM dba_hist_sql_plan dhsp WHERE 1=1 AND ( dhsp.object_type like '%TABLE%' OR dhsp.object_type like '%INDEX%' ) AND sql_id = '&&sql_id' UNION SELECT gvp.object_type ,gvp.object_owner ,gvp.object_name ,gvp.sql_id FROM gv$sql_plan gvp WHERE 1=1 AND ( gvp.object_type like '%TABLE%' OR gvp.object_type like '%INDEX%' ) AND sql_id = '&&sql_id' ORDER BY 1), -- now we need to add all of the tables for the indexes mentioned above in case of covering index objects AS ( SELECT ind.table_owner, ind.table_name, ind.index_name FROM dba_indexes ind JOIN feed obj ON (obj.object_owner=ind.table_owner AND (obj.object_name = ind.table_name OR obj.object_name = ind.index_name) ) )SELECT dtc.owner ,dtc.table_name ,dtc.column_name ,dtc.data_type ,dtc.nullable ,dtc.density ,dtc.num_distinct ,dtc.num_nulls ,dtc.histogram ,dtc.num_buckets FROM dba_tab_columns dtc JOIN objects ON (dtc.owner=objects.table_owner AND dtc.table_name = objects.table_name) GROUP BY dtc.owner ,dtc.table_name ,dtc.column_name ,dtc.data_type ,dtc.nullable ,dtc.density ,dtc.num_distinct ,dtc.num_nulls ,dtc.histogram,dtc.num_buckets,dtc.column_id ORDER BY dtc.owner,dtc.table_name,dtc.column_id/-- it would be handy to extract predicates from the filter to concentrate on the ones I care aboutspool offbreak on xxx_xxx
perf_advice– query advisors (not accurate for very large settings)
perf_advice.sql
-- perf_advice.sql v1.0 n chandler---- pull advisor information from current PDBset lines 400 pages 5000 verify oncol cdb for a15col pdb for a15col name for a60col this_inst for a12col value for 999999999999999999999col pga_target_for_estimate for 999999999999999999999col STATISTIC for a80 wrapcol JAVA_POOL_SIZE_FOR_ESTIMATE heading JAVA_POOL_SZ_ESTIMATEcol PGA_TARGET_FOR_ESTIMATE heading PGA_TARG_ESTIMATEcol SHARED_POOL_SIZE_FOR_ESTIMATE heading S_POOL_SIZE_ESTIMATEcol SHARED_POOL_SIZE_FACTOR heading SIZE_FACTORcol this_pdb_name new_value this_pdb_nm for a15col this_cdb_name new_value this_cdb_nm for a15SELECT sys_context('userenv','cdb_name') AS this_cdb_name,sys_context('userenv','con_name') AS this_pdb_name FROM dual;spool perf_advice.out appendprompt ==========================================================================================prompt remember - this is an estimate and the larger the allocation, the less accurate it becomesprompt e.g. if your SGA is over 50G, its pretty unreliableprompt ==========================================================================================promptSELECT '&&this_cdb_nm' cdb, '&&this_pdb_nm' pdb,SYS_CONTEXT('userenv','instance') this_inst,inst_id,name,open_mode FROM gv$pdbs order by inst_id,name;PROMPT gv$aq_message_cache_adviceSELECT '&&this_cdb_nm' cdb, '&&this_pdb_nm' pdb,gva.* FROM gv$aq_message_cache_advice gva ORDER BY inst_id,size_for_estimate;PROMPT gv$java_pool_adviceSELECT '&&this_cdb_nm' cdb, '&&this_pdb_nm' pdb,gvjpa.* FROM gv$java_pool_advice gvjpa ORDER BY inst_id,java_pool_size_for_estimate;PROMPT gv$memory_target_adviceSELECT '&&this_cdb_nm' cdb, '&&this_pdb_nm' pdb,gvmta.* FROM gv$memory_target_advice gvmta ORDER BY inst_id,memory_size;PROMPT gv$mttr_target_adviceSELECT '&&this_cdb_nm' cdb, '&&this_pdb_nm' pdb,gvmttr.* FROM gv$mttr_target_advice gvmttr ORDER BY inst_id,mttr_target_for_estimate;PROMPT gv$pga_target_adviceSELECT '&&this_cdb_nm' cdb, '&&this_pdb_nm' pdb,gvpga.* FROM gv$pga_target_advice gvpga ORDER BY inst_id,pga_target_for_estimate;PROMPT gv$px_buffer_adviceSELECT '&&this_cdb_nm' cdb, '&&this_pdb_nm' pdb,gvpx.* FROM gv$px_buffer_advice gvpx ORDER BY inst_id,statistic;PROMPT gv$sga_target_adviceSELECT '&&this_cdb_nm' cdb, '&&this_pdb_nm' pdb,gvsga.* FROM gv$sga_target_advice gvsga ORDER BY inst_id,sga_size;PROMPT gv$shared_pool_adviceSELECT '&&this_cdb_nm' cdb, '&&this_pdb_nm' pdb,gvspa.* FROM gv$shared_pool_advice gvspa ORDER BY inst_id,shared_pool_size_for_estimate;PROMPT gv$streams_pool_adviceSELECT '&&this_cdb_nm' cdb, '&&this_pdb_nm' pdb,gvstream.* FROM gv$streams_pool_advice gvstream ORDER BY inst_id,streams_pool_size_for_estimate;spool off
perf_blockers – show blocking from gv$session
perf_blockers.sql
-- perf_blockers.sql v1.0 n chandler.---- show blocking sessions, and then again with the SQL textset lines 250 pages 200col username_program for a30col wait_time_sec for 999,999.00col logon_time for a19col wait_class for a11 trunccol event for a64 trunccol status for a5 trunccol final_blocker for a13 trunccol program for a20 trunccol machine for a20 trunccol module for a10 trunccol service_name for a15 trunccol sid_serial_inst for a15col sql_text for a80 wrapcol osuser for a10 truncatespool perf_blockers.out appendprompt gv$session onlySELECT gvs.sid||','||gvs.serial#||'@'||gvs.inst_id sid_serial_inst ,LPAD(' ', (level-1)*2, '>') || NVL(username,REGEXP_REPLACE(program,'^.* ','')) username_program ,gvs.osuser ,gvs.lockwait ,gvs.status ,gvs.sql_id ,gvs.event ,DECODE(gvs.final_blocking_session||'@'||gvs.final_blocking_instance,'@','blocker!',gvs.final_blocking_session||'@'||gvs.final_blocking_instance) final_blocker ,TO_CHAR(gvs.logon_time,'YYYY-MM-DD HH24:MI:SS') logon_time ,wait_time_micro/1000000 wait_time_sec ,WAIT_CLASSfrom gv$session gvswhere level > 1 or exists (SELECT 1 FROM gv$session WHERE blocking_session = gvs.sid AND blocking_instance=gvs.inst_id)connect by prior gvs.sid = gvs.blocking_session and prior gvs.inst_id=gvs.blocking_instancestart with gvs.blocking_session is null/set lines 250prompt gv$session with sql_textWITH blocks as (SELECT /*+ MATERIALIZE */ gvs.sid||','||gvs.serial#||'@'||gvs.inst_id sid_serial_inst ,LPAD(' ', (level-1)*2, '>') || NVL(username,REGEXP_REPLACE(program,'^.* ','')) username_program ,gvs.osuser ,gvs.status ,gvs.sql_id ,gvs.prev_sql_id ,gvs.module ,gvs.machine ,gvs.program ,TO_CHAR(gvs.logon_time,'YYYY-MM-DD HH24:MI:SS') logon_time ,wait_time_micro/1000000 wait_time_sec ,wait_class ,gvs.service_name FROM gv$session gvs WHERE level > 1 OR EXISTS (SELECT 1 FROM gv$session WHERE blocking_session = gvs.sid AND blocking_instance=gvs.inst_id) CONNECT BY PRIOR gvs.sid = gvs.blocking_session AND PRIOR gvs.inst_id = gvs.blocking_instance START WITH gvs.blocking_session is null )SELECT blk.sid_serial_inst ,blk.username_program ,blk.status ,blk.module ,blk.machine ,blk.program ,blk.service_name ,blk.sql_id ,blk.prev_sql_id ,'| '||replace(gvsql.sql_text,CHR(13),' ') as sql_text FROM blocks blk LEFT OUTER JOIN gv$sql gvsql ON (blk.sql_id=gvsql.sql_id)/spool off
perf_longquery – look through dba_hist_active_sess_history and see where we have queries – outside of transactions! – running long
perf_longquery.sql
-- perf_longquery.sql v1.0 n chandler 2025-10-10-- v1.1 n chandler 2025-10-14 better grouping---- look through ASH history and see where we have queries - outside of transactions - running long-- excluding any Oracle internal users-- queries are also grouped by SQL_EXEC_ID and SQL_START_TIME as well as SID/SERIAL#/INST_ID to minimise-- catching the wrong start/edn times across repeated queries with the same SQL_ID.-- qc_sid_serial_instance shows if there was a query coordinator, and therefore if it's a parallel query slaveset line 2000 pages 5000 head on feed off trimspool oncol awr_retention for 9999999999col this_host for a30col this_cdb for a15col this_pdb for a15col transaction_id for a16col start_day for a9col start_time for a19col end_day for a9col end_time for a19col duration for a25col duration_seconds for 999,999,999col sid_serial_instance for a20col qc_sid_serial_instance for a20set colsep ","spool perf_longquery.out appendSELECT (SELECT EXTRACT(DAY FROM retention) AS awr_retention FROM dba_hist_wr_control WHERE con_id = SYS_CONTEXT('userenv','con_id') ) awr_retention ,SYS_CONTEXT('userenv','host') this_host ,SYS_CONTEXT('userenv','cdb_name') this_cdb ,SYS_CONTEXT('userenv','con_name') this_pdb ,sql_id sql_id ,sql_exec_id execution_number ,session_id||'.'||session_serial#||' @'||instance_number sid_serial_instance ,DECODE(qc_session_id||'.'||qc_session_serial#||' @'||qc_instance_id ,'. @',null ,qc_session_id||'.'||qc_session_serial#||' @'||qc_instance_id) qc_sid_serial_instance-- ,TO_CHAR(sql_exec_start,'YYYY-MM-DD HH24:MI:SS') sql_exec_start-- ,TO_CHAR(MIN(sample_time),'DAY') start_day ,TO_CHAR(MIN(sample_time),'YYYY-MM-DD HH24:MI:SS') start_time-- ,TO_CHAR(MAX(sample_time),'DAY') end_day ,TO_CHAR(MAX(sample_time),'YYYY-MM-DD HH24:MI:SS') end_time ,MAX(sample_time)-MIN(sample_time) duration ,EXTRACT(DAY FROM MAX(sample_time)-MIN(sample_time)) * 86400 + EXTRACT(HOUR FROM MAX(sample_time)-MIN(sample_time)) * 3600 + EXTRACT(MINUTE FROM MAX(sample_time)-MIN(sample_time)) * 60 + EXTRACT(SECOND FROM MAX(sample_time)-MIN(sample_time)) duration_seconds FROM dba_hist_active_sess_history-- FROM gv$active_session_history WHERE xid IS NULL AND sql_id IS NOT NULL AND sql_exec_id IS NOT NULL -- exclude oracle internal users AND user_id NOT IN (SELECT user_id FROM dba_users WHERE oracle_maintained='Y') -- restrict to last 32 days AND sample_time > systimestamp - INTERVAL '33' DAY GROUP BY sql_id,instance_number,session_id,session_serial#,qc_instance_id,qc_session_id,qc_session_serial#,sql_exec_id,sql_exec_start HAVING max(sample_time)-min(sample_time) > INTERVAL '20' SECOND ORDER BY duration,start_time/spool offset head on feed on colsep " "
perf_longtrans – look through ASH history and see where we have transactions (XID) running longer than 3 minutes
perf_longtrans.sql
-- perf_longtrans.sql v1.1 n chandler 2025-10-10---- look through ASH history and see where we have transactions (XID) running longer than 3 minutes-- and show their SQL ID list so we can see what's going on-- useful for TAC processing and drain_timeout-- NOTE: queries outside of transactions are excluded (XID IS NULL) as are internal Oracle users-- this is a sample of a sample. the information is approximate. the exact start and end,-- and some mid-transaction SQL may be missedset line 2000 pages 0 trimspool on head off feed off verify off echo offcol awr_retention for 9999999999col this_host for a30col this_cdb for a15col this_pdb for a15col transaction_id for a16col start_day for a9col start_time for a19col end_day for a9col end_time for a19col duration for a25col duration_minutes for 999,999,999col sql_id_list for a80 wrapset colsep ","spool perf_longtrans.out appendprompt AWR_RETENTION,THIS_HOST ,THIS_CDB ,THIS_PDB ,TRANSACTION_ID ,START_DAY,START_TIME ,END_DAY ,END_TIME ,DURATION ,DURATION_MINUTES,SQL_ID_LISTSELECT (SELECT EXTRACT(DAY FROM retention) AS awr_retention FROM dba_hist_wr_control WHERE con_id = SYS_CONTEXT('userenv','con_id') ) awr_retention ,SYS_CONTEXT('userenv','host') this_host ,SYS_CONTEXT('userenv','cdb_name') this_cdb ,SYS_CONTEXT('userenv','con_name') this_pdb ,xid transaction_id ,TO_CHAR(MIN(sample_time),'DAY') start_day ,TO_CHAR(MIN(sample_time),'YYYY-MM-DD HH24:MI:SS') start_time ,TO_CHAR(MAX(sample_time),'DAY') end_day ,TO_CHAR(MAX(sample_time),'YYYY-MM-DD HH24:MI:SS') end_time ,MAX(sample_time)-MIN(sample_time) duration ,EXTRACT(DAY FROM MAX(sample_time)-MIN(sample_time)) * 1440 + EXTRACT(HOUR FROM MAX(sample_time)-MIN(sample_time)) * 60 + EXTRACT(MINUTE FROM MAX(sample_time)-MIN(sample_time)) duration_minutes ,LISTAGG(DISTINCT sql_id,' ' ON OVERFLOW TRUNCATE) WITHIN GROUP (ORDER BY sql_id) sql_id_list FROM dba_hist_active_sess_history-- FROM gv$active_session_history WHERE xid IS NOT NULL -- exclude oracle internal users AND user_id NOT IN (SELECT user_id FROM dba_users WHERE oracle_maintained='Y') -- maybe restrict to last 32 days-- AND sample_time > systimestamp - INTERVAL '33' DAY GROUP BY xid HAVING max(sample_time)-min(sample_time) > INTERVAL '3' MINUTE ORDER BY duration,start_time/set lines 250 pages 250 head on feed on colsep " "spool off
perf_monrep – generate monitoring report : dbms_perf.REPORT_SQL
perf_pga – check the overall settings, generic SQL and ASH by process
perf_planflip – show sql’s with more than 1 PHV
perf_sesstemp – show pga and TEMP for sessions, and grouped by Parallel QC
perf_sga – show some SGA information, including subpools and resize operations
perf_spm_hints – show all of the hints associated with sql_profiles/_patch/_plan_baselines
perf_sqlcost – for a sql_id, show historic costs; dba_hist_sqlstat, ASH, GV$SQL info
perf_stathist – show statistics history for a table
perf_sqlplan – dbms_xplan.display_cursor, awr, manual extract of plan inc some XML
perf_statsprefs – show global, inc hidden, and table stats prefs
perf_tabfrag – check dbms_space to see how fragmented a table has become
perf_gatherstale – gathers stale stats for a schema
sysstats.sql – show calculation of system statistics, as they influence the optimizer
sysstats.sql
-- perf_sys_stats.sql v1.0 n chandler 2020.03.03 (based on a script by Franck Pachot, based on research by Chris Antognini)-- show the current system statistics and the relationship between single bytew reads (cost) and multibytes reads in terms of single byte reads-- which is a key relationship for the optimizer-- bug - I'm pretty sure this is an old version but it's the only one I can find now-- and it needs debugging as there's a couple of minor mistakes in there-- ALSO, there is defensive coding in System Stats, for example, if your SREADTIM > MREADTIM, or your MBRC is 0, Oracle will apply some default calcs instead-- I DO NOT TAKE THE DEFENSIVE CODING INTO ACCOUNT!!! Understand where your stats are impossible.set linesize 200column pname format a30column sname format a20column pval2 format a20column CALCULATED format 999999999.9999column narrative format a150column reset_systats_command format a80select sname,pname,pval1 from sys.aux_stats$ order by 1,2;spool perf_sysstat.out appendselect name from v$database;select pname,pval1,calculated,formula from sys.aux_stats$ where sname='SYSSTATS_MAIN'modelreference sga on (--select name,value from v$sgaselect 'Database Buffers' name,sum(bytes) value from v$sgastat where name in ('shared_io_pool','buffer_cache')) dimension by (name) measures(value)reference parameter on (select name,decode(type,3,to_number(value)) value from v$parameter where name='db_file_multiblock_read_count' and ismodified!='FALSE'union allselect name,decode(type,3,to_number(value)) value from v$parameter where name='sessions'union allselect name,decode(type,3,to_number(value)) value from v$parameter where name='db_block_size'union allSELECT a.ksppinm name, to_number(b.ksppstvl) value FROM x$ksppi a, x$ksppsv b WHERE a.indx=b.indx AND ksppinm like '_db_file_optimizer_read_count')dimension by (name) measures(value)partition by (sname) dimension by (pname) measures (pval1,pval2,cast(null as number) as calculated,cast(null as varchar2(120)) as formula) rules(calculated['MBRC']=coalesce(pval1['MBRC'],parameter.value['db_file_multiblock_read_count'],parameter.value['_db_file_optimizer_read_count'],8),calculated['MREADTIM']=coalesce(pval1['MREADTIM'],pval1['IOSEEKTIM'] + (parameter.value['db_block_size'] * calculated['MBRC'] ) / pval1['IOTFRSPEED']),calculated['SREADTIM']=coalesce(pval1['SREADTIM'],pval1['IOSEEKTIM'] + parameter.value['db_block_size'] / pval1['IOTFRSPEED']),calculated['_multi block Cost per block']=round(1/calculated['MBRC']*calculated['MREADTIM']/calculated['SREADTIM'],4),calculated['_single block Cost per block']=1,formula['MBRC']=case when pval1['MBRC'] is not null then 'MBRC' when parameter.value['db_file_multiblock_read_count'] is not null then 'db_file_multiblock_read_count' when parameter.value['_db_file_optimizer_read_count'] is not null then '_db_file_optimizer_read_count (db_file_multiblock_read_count not set, which is good!)' else '= not sure so used 8' end,formula['MREADTIM']=case when pval1['MREADTIM'] is null then '= IOSEEKTIM + db_block_size * MBRC / IOTFRSPEED' end||' = '||pval1['IOSEEKTIM']||'+('||parameter.value['db_block_size']||'*'||calculated['MBRC']||'/'||pval1['IOTFRSPEED']||')',formula['SREADTIM']=case when pval1['SREADTIM'] is null then '= IOSEEKTIM + db_block_size / IOTFRSPEED' end||' = '||pval1['IOSEEKTIM']||'+('||parameter.value['db_block_size']||'/'||pval1['IOTFRSPEED']||')',formula['_multi block Cost per block']='= 1/MBRC * MREADTIM/SREADTIM = 1/'||calculated['MBRC']||' * '||calculated['MREADTIM']||'/'||calculated['SREADTIM'],calculated['_maximum mbrc']=sga.value['Database Buffers']/(parameter.value['db_block_size']*parameter.value['sessions']),formula['_maximum mbrc']='= buffer cache blocks/sessions (small cache limiter) = ' || sga.value['Database Buffers']/parameter.value['db_block_size']||'/'||parameter.value['sessions'],formula['_single block Cost per block']='relative to the multi block cost per block. Always 1!',formula['CPUSPEED']='overrides CPUSPEEDNW when set',formula['CPUSPEEDNW']='CPU speed Ghz - non workload',formula['IOSEEKTIM']='IO seek time in ms',formula['IOTFRSPEED']='IO transfer speed in KB/s',formula['MAXTHR']='Maximum IO system throughput',formula['SLAVETHR']='average parallel slave IO throughput') order by 1;prompt Capture current System Statisticsselect sname,pname,pval1,'exec dbms_stats.set_system_stats('''||pname||''','||decode(pval1,null,'NULL',pval1)||');' as reset_systats_command from sys.aux_stats$ where sname = 'SYSSTATS_MAIN' order by 1,2;prompt Simplified check for an 8k block sizewith stats as( select pname,pval1,calculated from sys.aux_stats$ where sname='SYSSTATS_MAIN'modelreference sga on (--select name,value from v$sgaselect 'Database Buffers' name,sum(bytes) value from v$sgastat where name in ('shared_io_pool','buffer_cache')) dimension by (name) measures(value)reference parameter on (select name,decode(type,3,to_number(value)) value from v$parameter where name='db_file_multiblock_read_count' and ismodified!='FALSE'union allselect name,decode(type,3,to_number(value)) value from v$parameter where name='sessions'union allselect name,decode(type,3,to_number(value)) value from v$parameter where name='db_block_size'union allSELECT a.ksppinm name, to_number(b.ksppstvl) value FROM x$ksppi a, x$ksppsv b WHERE a.indx=b.indx AND ksppinm like '_db_file_optimizer_read_count')dimension by (name) measures(value)partition by (sname) dimension by (pname) measures (pval1,pval2,cast(null as number) as calculated) rules(calculated['MBRC']=coalesce(pval1['MBRC'],parameter.value['db_file_multiblock_read_count'],parameter.value['_db_file_optimizer_read_count'],8),calculated['MREADTIM']=coalesce(pval1['MREADTIM'],pval1['IOSEEKTIM'] + (parameter.value['db_block_size'] * calculated['MBRC'] ) / pval1['IOTFRSPEED']),calculated['SREADTIM']=coalesce(pval1['SREADTIM'],pval1['IOSEEKTIM'] + parameter.value['db_block_size'] / pval1['IOTFRSPEED']),calculated['_multi block Cost per block']=round(1/calculated['MBRC']*calculated['MREADTIM']/calculated['SREADTIM'],4),calculated['_single block Cost per block']=1))select pname ,calculated ,case when calculated < .2708 then 'System Stats have been set for execution plan costing '||round(abs((1-(calculated/0.2708)))*100)||'% lower for multiblock reads (favours SCANS).' when calculated > .2708 then 'System Stats have been set for execution plan costing '||round(abs((1-(calculated/0.2708)))*100)||'% higher for multiblock reads (favours INDEXES).' else 'Stats calculations shows this to be the DEFAULT' end as Narrative from stats where pname = '_multi block Cost per block'/--examples--PNAME CALCULATED NARRATIVE--_multi block Cost per block .2708 Stats calculations shows this to be the DEFAULT--PNAME CALCULATED NARRATIVE--_multi block Cost per block .0119 System Stats have been set for execution plan costing 96% lower for multiblock reads (favours SCANS).prompt NOTE: 0.2708 is for an 8k block size! 16k block size is slightly different.prompt Running a "gather_system_stats(exadata)" will generally result in a very low multi-block cost per block (e.g. 0.0119) meaning indexesprompt need to be extremely accurate and selective to be considered by the optimizer instead of full table scansprompt IF IT IS NOT THE DEFAULT (above), it should be heavily documented WHY and with proofs of why the different value is better than the defaultprompt Warning: changing it can affect a lot of SQL Optimizations. Proceed with caution.spool off
check.sql – database overview
Neil Chandler's DB Blog 