Chapter 2 < oracle_quick.ksh #!/bin/ksh # First, we must set the environment . . . . ORACLE_SID=$ORACLE_SID export ORACLE_SID ORACLE_HOME=`cat /etc/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'` export ORACLE_HOME PATH=$ORACLE_HOME/bin:$PATH export PATH echo "Please enter the number of seconds between snapshots." read elapsed $ORACLE_HOME/bin/sqlplus –s perfstat/perfstat<<! execute statspack.snap snap" ; exit ! sleep $elapsed $ORACLE_HOME/bin/sqlplus –s perfstat/perfstat<<! execute statspack.snap; select name, snap_id, to_char(snap_time,' dd Mon YYYY HH24:mi:ss') from stats\$snapshot, v\$database where snap_id > (select max(snap_id)-2 from stats\$snapshot) ; < features_used.sql select samp.dbid, fu.name, samp.version, detected_usages, total_samples, decode(to_char(last_usage_date, 'MM/DD/YYYY, HH:MI:SS'), NULL, 'FALSE', to_char(last_sample_date, 'MM/DD/YYYY, HH:MI:SS'), 'TRUE', 'FALSE') currently_used, first_usage_date, last_usage_date, aux_count, feature_info, last_sample_date, last_sample_period, sample_interval, mt.description from wri$_dbu_usage_sample samp, wri$_dbu_feature_usage fu, wri$_dbu_feature_metadata mt where samp.dbid = fu.dbid and samp.version = fu.version and fu.name = mt.name and fu.name not like '_DBFUS_TEST%' and /* filter test features */ bitand(mt.usg_det_method, 4) != 4 /* filter disabled feat */; < phys_disk_reads.sql break on begin_interval_time skip 2 column phyrds format 999,999,999 column begin_interval_time format a25 select begin_interval_time, filename, phyrds from dba_hist_filestatxs natural join dba_hist_snapshot ; < display_statistics.sql prompt prompt This will query the dba_hist_sysstat to display all values prompt that exceed the value specified in prompt the "where" clause of the query. prompt set pages 999 break on snap_time skip 2 accept stat_name char prompt 'Enter Statistic Name: '; accept stat_value number prompt 'Enter Statistics Threshold value: '; col snap_time format a19 col value format 999,999,999 select to_char(begin_interval_time,'yyyy-mm-dd hh24:mi') snap_time, value from dba_hist_sysstat natural join dba_hist_snapshot where stat_name = '&stat_name' and value > &stat_value order by to_char(begin_interval_time,'yyyy-mm-dd hh24:mi'); < track_hot_write.sql prompt prompt This will identify any single file who's write I/O prompt is more than 25% of the total write I/O of the database. prompt set pages 999 break on snap_time skip 2 col filename format a40 col phywrts format 999,999,999 col snap_time format a20 select to_char(begin_interval_time,'yyyy-mm-dd hh24:mi') snap_time, filename, phywrts from dba_hist_filestatxs natural join dba_hist_snapshot where phywrts > 0 and phywrts * 4 > ( select avg(value) all_phys_writes from dba_hist_sysstat natural join dba_hist_snapshot where stat_name = 'physical writes' and value > 0 ) order by to_char(begin_interval_time,'yyyy-mm-dd hh24:mi'), phywrts desc; < rpt_sysstat_hr.sql prompt This will query the dba_hist_sysstat view to prompt display average values by hour of the day set pages 999 break on snap_time skip 2 accept stat_name char prompt 'Enter Statistics Name: '; col snap_time format a19 col avg_value format 999,999,999 select to_char(begin_interval_time,'hh24') snap_time, avg(value) avg_value from dba_hist_sysstat natural join dba_hist_snapshot where stat_name = '&stat_name' group by to_char(begin_interval_time,'hh24') order by to_char(begin_interval_time,'hh24'); < plot_values_by_hour.sql prompt prompt This will query the dba_hist_sysstat view to display prompt average values by day-of-the-week prompt set pages 999 accept stat_name char prompt 'Enter Statistic Name: '; col snap_time format a19 col avg_value format 999,999,999 select to_char(begin_interval_time,'day') snap_time, avg(value) avg_value from dba_hist_sysstat natural join dba_hist_snapshot where stat_name = '&stat_name' group by to_char(begin_interval_time,'day') order by decode( to_char(begin_interval_time,'day'), 'sunday',1, 'monday',2, 'tuesday',3, 'wednesday',4, 'thursday',5, 'friday',6, 'saturday',7 ) ; Chapter 3 < sess_waits_ash.sql select b.username, a.stat_name, round((a.value / 1000000),3) time_secs from v$sess_time_model a, v$session b where a.sid = b.sid and b.sid = 123 order by 3 desc; < elapsed_time.sql select s.sid, s.username, s.module, round(t.value/1000000,2) "elapsed processing time (sec)" from v$sess_time_model t, v$session s where t.sid = s.sid and t.stat_name = 'db time' and s.username IS NOT NULL and t.value/1000000 >= 1; < pct_of_tot_elapsed_time.sql select e.stat_name "E.STAT_NAME" , Round((e.value - b.value)/1000000,2)"Time (s)" , Round(decode( e.stat_name,'DB time' , to_number(null) , 100*(e.value - b.value) )/ (select nvl((e1.value - b1.value),-1) FROM dba_hist_sys_time_model e1 , dba_hist_sys_time_model b1 WHERE b1.snap_id = b.snap_id AND e1.snap_id = e.snap_id AND b1.dbid = b.dbid AND e1.dbid = e.dbid AND b1.instance_number = b.instance_number AND e1.instance_number = e.instance_number AND e1.stat_name = 'DB time' AND b1.stat_id = e1.stat_id ),2) "Percent of Total DB Time" from dba_hist_sys_time_model e, dba_hist_sys_time_model b WHERE b.snap_id = &pBgnSnap AND e.snap_id = &pEndSnap AND b.dbid = &pDbId AND e.dbid = &pDbId AND b.instance_number = &pInstNum AND e.instance_number = &pInstNum AND b.stat_id = e.stat_id AND e.value - b.value > 0 ORDER BY 2 DESC < avg_workload_hour_of_the_day.sql select to_char(end_interval_time,'HH24') "Hour of Day", Round(avg(newtime.value-oldtime.value)/1000000,2) "Avg DB Time (Sec)" from dba_hist_sys_time_model oldtime, dba_hist_sys_time_model newtime, dba_hist_snapshot sn where newtime.snap_id = sn.snap_id and oldtime.snap_id = sn.snap_id-1 and newtime.stat_name = 'DB time' and oldtime.stat_name = 'DB time' having avg(newtime.value-oldtime.value) > 0 group by to_char(end_interval_time,'HH24'); Chapter 4 < cache_advice.sql column c1 heading 'Cache Size (meg)' format 999,999,999,999 column c2 heading 'Buffers' format 999,999,999 column c3 heading 'Estd Phys|Read Factor' format 999.90 column c4 heading 'Estd Phys| Reads' format 999,999,999 select size_for_estimate c1, buffers_for_estimate c2, estd_physical_read_factor c3, estd_physical_reads c4 from v$db_cache_advice where name = 'DEFAULT' and block_size = (select value from v$parameter where name = 'db_block_size') and advice_status = 'ON'; < display_stat.sql prompt prompt This will query the dba_hist_sysstat view to display all values prompt that exceed the value specified in the "where" clause of the query. prompt set pages 999 break on snap_time skip 2 accept stat_name char prompt 'Enter Statistic Name: '; accept stat_value number prompt 'Enter Statistics Threshold value: '; col snap_time format a19 col value format 999,999,999 select to_char(begin_interval_time,'yyyy-mm-dd hh24:mi') snap_time, value from dba_hist_sysstat natural join dba_hist_snapshot where stat_name = '&stat_name' and value > &stat_value order by to_char(begin_interval_time,'yyyy-mm-dd hh24:mi') ; < wait_time_detail_10g.sql -- ************************************************* -- Copyright © 2005 by Rampant TechPress -- This script is free for non-commercial purposes -- with no warranties. Use at your own risk. -- -- To license this script for a commercial purpose, -- contact info@rampant.cc -- ************************************************* prompt prompt This will compare values from dba_hist_waitstat with prompt detail information from dba_hist_active_sess_history. prompt set pages 999 set lines 80 break on snap_time skip 2 col snap_time heading 'Snap|Time' format a20 col file_name heading 'File|Name' format a40 col object_type heading 'Object|Type' format a10 col object_name heading 'Object|Name' format a20 col wait_count heading 'Wait|Count' format 999,999 col time heading 'Time' format 999,999 select to_char(begin_interval_time,'yyyy-mm-dd hh24:mi') snap_time, object_type, object_name, wait_count, time from dba_hist_waitstat wait, dba_hist_snapshot snap, dba_hist_active_sess_history ash, dba_data_files df, dba_objects obj where wait.snap_id = snap.snap_id and wait.snap_id = ash.snap_id and df.file_id = ash.current_file# and obj.object_id = ash.current_obj# and wait_count > 50 order by to_char(begin_interval_time,'yyyy-mm-dd hh24:mi'), file_name; < file_io_hogs.sql prompt This will identify any single file who's write I/O prompt is more than 25% of the total write I/O of the database. prompt set pages 999 break on snap_time skip 2 col filename format a40 col phywrts format 999,999,999 col snap_time format a20 select to_char(begin_interval_time,'yyyy-mm-dd hh24:mi') snap_time, filename, phywrts from dba_hist_filestatxs natural join dba_hist_snapshot where phywrts > 0 and phywrts * 4 > ( select avg(value) all_phys_writes from dba_hist_sysstat natural join dba_hist_snapshot where stat_name = 'physical writes' and value > 0 ) order by to_char(begin_interval_time,'yyyy-mm-dd hh24:mi'), phywrts desc; Chapter 5 < sql_shared_cursor.sql select version_count, address, hash_value, parsing_schema_name, reason, sql_text from ( select address,'' ||decode(max( UNBOUND_CURSOR),'Y', ' UNBOUND_CURSOR') ||decode(max( SQL_TYPE_MISMATCH),'Y', ' SQL_TYPE_MISMATCH') ||decode(max( OPTIMIZER_MISMATCH),'Y', ' OPTIMIZER_MISMATCH') ||decode(max( OUTLINE_MISMATCH),'Y', ' OUTLINE_MISMATCH') ||decode(max( STATS_ROW_MISMATCH),'Y', ' STATS_ROW_MISMATCH') ||decode(max( LITERAL_MISMATCH),'Y', ' LITERAL_MISMATCH') ||decode(max( SEC_DEPTH_MISMATCH),'Y', ' SEC_DEPTH_MISMATCH') ||decode(max( EXPLAIN_PLAN_CURSOR),'Y', ' EXPLAIN_PLAN_CURSOR') ||decode(max( BUFFERED_DML_MISMATCH),'Y', ' BUFFERED_DML_MISMATCH') ||decode(max( PDML_ENV_MISMATCH),'Y', ' PDML_ENV_MISMATCH') ||decode(max( INST_DRTLD_MISMATCH),'Y', ' INST_DRTLD_MISMATCH') ||decode(max( SLAVE_QC_MISMATCH),'Y', ' SLAVE_QC_MISMATCH') ||decode(max( TYPECHECK_MISMATCH),'Y', ' TYPECHECK_MISMATCH') ||decode(max( AUTH_CHECK_MISMATCH),'Y', ' AUTH_CHECK_MISMATCH') ||decode(max( BIND_MISMATCH),'Y', ' BIND_MISMATCH') ||decode(max( DESCRIBE_MISMATCH),'Y', ' DESCRIBE_MISMATCH') ||decode(max( LANGUAGE_MISMATCH),'Y', ' LANGUAGE_MISMATCH') ||decode(max( TRANSLATION_MISMATCH),'Y', ' TRANSLATION_MISMATCH') ||decode(max( ROW_LEVEL_SEC_MISMATCH),'Y', ' ROW_LEVEL_SEC_MISMATCH') ||decode(max( INSUFF_PRIVS),'Y', ' INSUFF_PRIVS') ||decode(max( INSUFF_PRIVS_REM),'Y', ' INSUFF_PRIVS_REM') ||decode(max( REMOTE_TRANS_MISMATCH),'Y', ' REMOTE_TRANS_MISMATCH') ||decode(max( LOGMINER_SESSION_MISMATCH),'Y', ' LOGMINER_SESSION_MISMATCH') ||decode(max( INCOMP_LTRL_MISMATCH),'Y', ' INCOMP_LTRL_MISMATCH') ||decode(max( OVERLAP_TIME_MISMATCH),'Y', ' OVERLAP_TIME_MISMATCH') ||decode(max( SQL_REDIRECT_MISMATCH),'Y', ' SQL_REDIRECT_MISMATCH') ||decode(max( MV_QUERY_GEN_MISMATCH),'Y', ' MV_QUERY_GEN_MISMATCH') ||decode(max( USER_BIND_PEEK_MISMATCH),'Y', ' USER_BIND_PEEK_MISMATCH') ||decode(max( TYPCHK_DEP_MISMATCH),'Y', ' TYPCHK_DEP_MISMATCH') ||decode(max( NO_TRIGGER_MISMATCH),'Y', ' NO_TRIGGER_MISMATCH') ||decode(max( FLASHBACK_CURSOR),'Y', ' FLASHBACK_CURSOR') ||decode(max( ANYDATA_TRANSFORMATION),'Y', ' ANYDATA_TRANSFORMATION') ||decode(max( INCOMPLETE_CURSOR),'Y', ' INCOMPLETE_CURSOR') ||decode(max( TOP_LEVEL_RPI_CURSOR),'Y', ' TOP_LEVEL_RPI_CURSOR') ||decode(max( DIFFERENT_LONG_LENGTH),'Y', ' DIFFERENT_LONG_LENGTH') ||decode(max( LOGICAL_STANDBY_APPLY),'Y', ' LOGICAL_STANDBY_APPLY') ||decode(max( DIFF_CALL_DURN),'Y', ' DIFF_CALL_DURN') ||decode(max( BIND_UACS_DIFF),'Y', ' BIND_UACS_DIFF') ||decode(max( PLSQL_CMP_SWITCHS_DIFF),'Y', ' PLSQL_CMP_SWITCHS_DIFF') ||decode(max( CURSOR_PARTS_MISMATCH),'Y', ' CURSOR_PARTS_MISMATCH') ||decode(max( STB_OBJECT_MISMATCH),'Y', ' STB_OBJECT_MISMATCH') ||decode(max( ROW_SHIP_MISMATCH),'Y', ' ROW_SHIP_MISMATCH') ||decode(max( PQ_SLAVE_MISMATCH),'Y', ' PQ_SLAVE_MISMATCH') ||decode(max( TOP_LEVEL_DDL_MISMATCH),'Y', ' TOP_LEVEL_DDL_MISMATCH') ||decode(max( MULTI_PX_MISMATCH),'Y', ' MULTI_PX_MISMATCH') ||decode(max( BIND_PEEKED_PQ_MISMATCH),'Y', ' BIND_PEEKED_PQ_MISMATCH') ||decode(max( MV_REWRITE_MISMATCH),'Y', ' MV_REWRITE_MISMATCH') ||decode(max( ROLL_INVALID_MISMATCH),'Y', ' ROLL_INVALID_MISMATCH') ||decode(max( OPTIMIZER_MODE_MISMATCH),'Y', ' OPTIMIZER_MODE_MISMATCH') ||decode(max( PX_MISMATCH),'Y', ' PX_MISMATCH') ||decode(max( MV_STALEOBJ_MISMATCH),'Y', ' MV_STALEOBJ_MISMATCH') ||decode(max( FLASHBACK_TABLE_MISMATCH),'Y', ' FLASHBACK_TABLE_MISMATCH') ||decode(max( LITREP_COMP_MISMATCH),'Y', ' LITREP_COMP_MISMATCH') reason from v$sql_shared_cursor group by address ) join v$sqlarea using(address) where version_count>&versions order by version_count desc, address; < check_bind_sensitive_sql.sql select sql_id, child_number, is_bind_sensitive, is_bind_aware from v$sql where sql_text = 'select max(id) from acs_test_tab where record_type = :l_record_type'; The following are additional sample queries for 11g adaptive cursor sharing: select hash_value, sql_id, child_number, range_id, low, high, predicate from v$sql_cs_selectivity; And: select hash_value, sql_id, child_number, bucket_id, count from v$sql_cs_histogram; And: select sql_id, hash_value, plan_hash_value, is_bind_sensitive, is_bind_aware, sql_text from v$sql; And: select hash_value, sql_id, child_number, bind_set_hash_value, peeked, executions, rows_processed, buffer_gets cpu_time from v$sql_cs_statistics; column "Tablespace" format a13 column "Used MB" format 99,999,999 column "Free MB" format 99,999,999 colimn "Total MB" format 99,999,999 select fs.tablespace_name "Tablespace", (df.totalspace - fs.freespace) "Used MB", fs.freespace "Free MB", df.totalspace "Total MB", round(100 * (fs.freespace / df.totalspace)) "Pct. Free" from (select tablespace_name, round(sum(bytes) / 1048576) TotalSpace from dba_data_files group by tablespace_name ) df, (select tablespace_name, round(sum(bytes) / 1048576) FreeSpace from dba_free_space group by tablespace_name ) fs where df.tablespace_name = fs.tablespace_name; < plan9i.sql -- ************************************************* -- Copyright © 2005 by Rampant TechPress -- This script is free for non-commercial purposes -- with no warranties. Use at your own risk. -- -- To license this script for a commercial purpose, -- contact info@rampant.cc -- ************************************************* set echo off; set feedback on set pages 999; column nbr_FTS format 999,999 column num_rows format 999,999,999 column blocks format 999,999 column owner format a14; column name format a24; column ch format a1; column object_owner heading "Owner" format a12; column ct heading "# of SQL selects" format 999,999; select object_owner, count(*) ct from v$sql_plan where object_owner is not null group by object_owner order by ct desc ; --spool access.lst; set heading off; set feedback off; set heading on; set feedback on; ttitle 'full table scans and counts| |The "K" indicates that the table is in the KEEP Pool (Oracle8).' select p.owner, p.name, t.num_rows, -- ltrim(t.cache) ch, decode(t.buffer_pool,'KEEP','Y','DEFAULT','N') K, s.blocks blocks, sum(a.executions) nbr_FTS from dba_tables t, dba_segments s, v$sqlarea a, (select distinct address, object_owner owner, object_name name from v$sql_plan where operation = 'TABLE ACCESS' and options = 'FULL') p where a.address = p.address and t.owner = s.owner and t.table_name = s.segment_name and t.table_name = p.name and t.owner = p.owner and t.owner not in ('SYS','SYSTEM') having sum(a.executions) > 9 group by p.owner, p.name, t.num_rows, t.cache, t.buffer_pool, s.blocks order by sum(a.executions) desc; column nbr_RID format 999,999,999 column num_rows format 999,999,999 column owner format a15; column name format a25; ttitle 'Table access by ROWID and counts' select p.owner, p.name, t.num_rows, sum(s.executions) nbr_RID from dba_tables t, v$sqlarea s, (select distinct address, object_owner owner, object_name name from v$sql_plan where operation = 'TABLE ACCESS' and options = 'BY ROWID') p where s.address = p.address and t.table_name = p.name and t.owner = p.owner having sum(s.executions) > 9 group by p.owner, p.name, t.num_rows order by sum(s.executions) desc; --************************************************* < plan10g.sql -- ************************************************* -- Copyright © 2005 by Rampant TechPress -- This script is free for non-commercial purposes -- with no warranties. Use at your own risk. -- -- To license this script for a commercial purpose, -- contact info@rampant.cc -- ************************************************* spool plan.lst set echo off set feedback on set pages 999; column nbr_FTS format 99,999 column num_rows format 999,999 column blocks format 9,999 column owner format a10; column name format a30; column ch format a1; column time heading "Snapshot Time" format a15 column object_owner heading "Owner" format a12; column ct heading "# of SQL selects" format 999,999; break on time select object_owner, count(*) ct from dba_hist_sql_plan where object_owner is not null group by object_owner order by ct desc ; --spool access.lst; set heading on; set feedback on; ttitle 'full table scans and counts| |The "K" indicates that the table is in the KEEP Pool (Oracle8).' select to_char(sn.end_interval_time,'mm/dd/rr hh24') time, p.owner, p.name, t.num_rows, -- ltrim(t.cache) ch, decode(t.buffer_pool,'KEEP','Y','DEFAULT','N') K, s.blocks blocks, sum(a.executions_delta) nbr_FTS from dba_tables t, dba_segments s, dba_hist_sqlstat a, dba_hist_snapshot sn, (select distinct pl.sql_id, object_owner owner, object_name name from dba_hist_sql_plan pl where operation = 'TABLE ACCESS' and options = 'FULL') p where a.snap_id = sn.snap_id and a.sql_id = p.sql_id and t.owner = s.owner and t.table_name = s.segment_name and t.table_name = p.name and t.owner = p.owner and t.owner not in ('SYS','SYSTEM') having sum(a.executions_delta) > 1 group by to_char(sn.end_interval_time,'mm/dd/rr hh24'),p.owner, p.name, t.num_rows, t.cache, t.buffer_pool, s.blocks order by 1 asc; column nbr_RID format 999,999,999 column num_rows format 999,999,999 column owner format a15; column name format a25; ttitle 'Table access by ROWID and counts' select to_char(sn.end_interval_time,'mm/dd/rr hh24') time, p.owner, p.name, t.num_rows, sum(a.executions_delta) nbr_RID from dba_tables t, dba_hist_sqlstat a, dba_hist_snapshot sn, (select distinct pl.sql_id, object_owner owner, object_name name from dba_hist_sql_plan pl where operation = 'TABLE ACCESS' and options = 'BY USER ROWID') p where a.snap_id = sn.snap_id and a.sql_id = p.sql_id and t.table_name = p.name and t.owner = p.owner having sum(a.executions_delta) > 9 group by to_char(sn.end_interval_time,'mm/dd/rr hh24'),p.owner, p.name, t.num_rows order by 1 asc; --************************************************* -- Index Report Section --************************************************* column nbr_scans format 999,999,999 column num_rows format 999,999,999 column tbl_blocks format 999,999,999 column owner format a9; column table_name format a20; column index_name format a20; ttitle 'Index full scans and counts' select to_char(sn.end_interval_time,'mm/dd/rr hh24') time, p.owner, d.table_name, p.name index_name, seg.blocks tbl_blocks, sum(s.executions_delta) nbr_scans from dba_segments seg, dba_indexes d, dba_hist_sqlstat s, dba_hist_snapshot sn, (select distinct pl.sql_id, object_owner owner, object_name name from dba_hist_sql_plan pl where operation = 'INDEX' and options = 'FULL SCAN') p where d.index_name = p.name and s.snap_id = sn.snap_id and s.sql_id = p.sql_id and d.table_name = seg.segment_name and seg.owner = p.owner having sum(s.executions_delta) > 9 group by to_char(sn.end_interval_time,'mm/dd/rr hh24'),p.owner, d.table_name, p.name, seg.blocks order by 1 asc; ttitle 'Index range scans and counts' select to_char(sn.end_interval_time,'mm/dd/rr hh24') time, p.owner, d.table_name, p.name index_name, seg.blocks tbl_blocks, sum(s.executions_delta) nbr_scans from dba_segments seg, dba_hist_sqlstat s, dba_hist_snapshot sn, dba_indexes d, (select distinct pl.sql_id, object_owner owner, object_name name from dba_hist_sql_plan pl where operation = 'INDEX' and options = 'RANGE SCAN') p where d.index_name = p.name and s.snap_id = sn.snap_id and s.sql_id = p.sql_id and d.table_name = seg.segment_name and seg.owner = p.owner having sum(s.executions_delta) > 9 group by to_char(sn.end_interval_time,'mm/dd/rr hh24'),p.owner, d.table_name, p.name, seg.blocks order by 1 asc; ttitle 'Index unique scans and counts' select to_char(sn.end_interval_time,'mm/dd/rr hh24') time, p.owner, d.table_name, p.name index_name, sum(s.executions_delta) nbr_scans from dba_hist_sqlstat s, dba_hist_snapshot sn, dba_indexes d, (select distinct pl.sql_id, object_owner owner, object_name name from dba_hist_sql_plan pl where operation = 'INDEX' and options = 'UNIQUE SCAN') p where d.index_name = p.name and s.snap_id = sn.snap_id and s.sql_id = p.sql_id having sum(s.executions_delta) > 9 group by to_char(sn.end_interval_time,'mm/dd/rr hh24'),p.owner, d.table_name, p.name order by 1 asc; spool off < optimizer_index_cost_adj.sql col c1 heading 'Average Waits for|Full Scan Read I/O' format 9999.999 col c2 heading 'Average Waits for|Index Read I/O' format 9999.999 col c3 heading 'Percent of| I/O Waits|for Full Scans' format 9.99 col c4 heading 'Percent of| I/O Waits|for Index Scans' format 9.99 col c5 heading 'Starting|Value|for|optimizer|index|cost|adj' format 999 select a.average_wait c1, b.average_wait c2, a.total_waits /(a.total_waits + b.total_waits) c3, b.total_waits /(a.total_waits + b.total_waits) c4, (b.average_wait / a.average_wait)*100 c5 from v$system_event a, v$system_event b where a.event = 'db file scattered read' and b.event = 'db file sequential read' ; < delete_forall.sql set serveroutput on declare type t_id_tab is table of forall_test.id%TYPE; type t_code_tab is table of forall_test.code%TYPE; l_id_tab t_id_tab := t_id_tab(); l_code_tab t_code_tab := t_code_tab(); l_start number; l_size number := 10000; begin -- Populate collections. for i in 1 .. l_size loop l_id_tab.extend; l_code_tab.extend; l_id_tab(l_id_tab.last) := i; l_code_tab(l_code_tab.last) := to_char(i); end loop; -- Time regular updates. l_start := dbms_utility.get_time; for i in l_id_tab.first .. l_id_tab.last loop delete from forall_test where id = l_id_tab(i) and code = l_code_tab(i); end loop; rollback; dbms_output.put_line('Normal Deletes : ' || (dbms_utility.get_time - l_start)); l_start := dbms_utility.get_time; -- Time bulk updates. forall i in l_id_tab.first .. l_id_tab.last delete from forall_test where id = l_id_tab(i) and code = l_code_tab(i); dbms_output.put_line('Bulk Deletes : ' || (dbms_utility.get_time - l_start)); rollback; end; / < update_forall.sql set serveroutput on declare type t_id_tab is table of forall_test.id%TYPE; type t_forall_test_tab is table of forall_test%ROWTYPE; l_id_tab t_id_tab := t_id_tab(); l_tab t_forall_test_tab := t_forall_test_tab (); l_start number; l_size number := 10000; begin -- Populate collections. for i in 1 .. l_size loop l_id_tab.extend; l_tab.extend; l_id_tab(l_id_tab.last) := i; l_tab(l_tab.last).id := i; l_tab(l_tab.last).code := to_char(i); l_tab(l_tab.last).description := 'Description: ' || to_char(i); end loop; -- Time regular updates. l_start := dbms_utility.get_time; for i in l_tab.first .. l_tab.last loop update forall_test set row = l_tab(i) where id = l_tab(i).id; end loop; dbms_output.put_line('Normal Updates : ' || (dbms_utility.get_time - l_start)); l_start := dbms_utility.get_time; -- Time bulk updates. -- **************************************************** -- *** Here is the forall -- **************************************************** forall i in l_tab.first .. l_tab.last update forall_test set row = l_tab(i) where id = l_id_tab(i); dbms_output.put_line('Bulk Updates : ' || (dbms_utility.get_time - l_start)); commit; end; / < oracle10g_quick.ksh -- ************************************************* -- Copyright © 2005 by Rampant TechPress -- This script is free for non-commercial purposes -- with no warranties. Use at your own risk. -- -- To license this script for a commercial purpose, -- contact info@rampant.cc -- ************************************************* spool rpt_last.lst set pages 9999; set feedback on; set verify off; column reads format 999,999,999 column writes format 999,999,999 select to_char(sn.end_interval_time,'yyyy-mm-dd HH24'), (newreads.value-oldreads.value) reads, (newwrites.value-oldwrites.value) writes from dba_hist_sysstat oldreads, dba_hist_sysstat newreads, dba_hist_sysstat oldwrites, dba_hist_sysstat newwrites, dba_hist_snapshot sn where newreads.snap_id = (select max(sn.snap_id) from dba_hist_snapshot) and newwrites.snap_id = (select max(sn.snap_id) from dba_hist_snapshot) and oldreads.snap_id = sn.snap_id-1 and oldwrites.snap_id = sn.snap_id-1 and oldreads.stat_name = 'physical reads' and newreads.stat_name = 'physical reads' and oldwrites.stat_name = 'physical writes' and newwrites.stat_name = 'physical writes' ; prompt *********************************************************** prompt This will identify any single file who's read I/O prompt is more than 10% of the total read I/O of the database. prompt prompt The "hot" file should be examined, and the hot table/index prompt should be identified using STATSPACK. prompt prompt - The busy file should be placed on a disk device with prompt "less busy" files to minimize read delay and channel prompt contention. prompt prompt - If small file has a hot small table, place the table prompt in the KEEP pool prompt prompt - If the file has a large-table full-table scan, place prompt the table in the RECYCLE pool and turn on parallel query prompt for the table. prompt *********************************************************** column mydate format a16 column file_name format a40 column reads format 999,999,999 select to_char(sn.end_interval_time,'yyyy-mm-dd HH24') mydate, new.filename file_name, new.phyrds-old.phyrds reads from dba_hist_filestatxs old, dba_hist_filestatxs new, dba_hist_snapshot snwhere sn.snap_id = (select max(snap_id) from dba_hist_snapshot) and new.snap_id = sn.snap_id and old.snap_id = sn.snap_id-1 and new.filename = old.filename --and -- new.phyrds-old.phyrds > 10000 and (new.phyrds-old.phyrds)*10 > ( select (newreads.value-oldreads.value) reads from dba_hist_sysstat oldreads, dba_hist_sysstat newreads, dba_hist_snapshot sn1 where sn.snap_id = sn1.snap_id and newreads.snap_id = sn.snap_id and oldreads.snap_id = sn.snap_id-1 and oldreads.stat_name = 'physical reads' and newreads.stat_name = 'physical reads' and (newreads.value-oldreads.value) > 0) ; prompt *********************************************************** prompt This will identify any single file who's write I/O prompt is more than 10% of the total write I/O of the database. prompt prompt The "hot" file should be examined, and the hot table/index prompt should be identified using STATSPACK. prompt prompt - The busy file should be placed on a disk device with prompt "less busy" files to minimize write delay and channel prompt channel contention. prompt prompt - If small file has a hot small table, place the table prompt in the KEEP pool prompt prompt *********************************************************** column mydate format a16 column file_name format a40 column writes format 999,999,999 select to_char(sn.end_interval_time,'yyyy-mm-dd HH24') mydate, new.filename file_name, new.phywrts-old.phywrts writes from dba_hist_filestatxs old, dba_hist_filestatxs new, dba_hist_snapshot sn where sn.snap_id = (select max(snap_id) from dba_hist_snapshot) and new.snap_id = sn.snap_id and old.snap_id = sn.snap_id-1 and new.filename = old.filename --and ---- new.phywrts-old.phywrts > 10000 and (new.phywrts-old.phywrts)*10 > (select(newwrites.value-oldwrites.value) writes from dba_hist_sysstat oldwrites, dba_hist_sysstat newwrites, dba_hist_snapshot sn1 where sn.snap_id = sn1.snap_id and newwrites.snap_id = sn.snap_id and oldwrites.snap_id = sn.snap_id-1 and oldwrites.stat_name = 'physical writes' and newwrites.stat_name = 'physical writes' and (newwrites.value-oldwrites.value) > 0) ; prompt *********************************************************** prompt The data buffer hit ratio is controlled by the db_block_buffer or db_cache_size parameters. prompt *********************************************************** column logical_reads format 999,999,999 column phys_reads format 999,999,999 column phys_writes format 999,999,999 column "BUFFER HIT RATIO" format 999 select to_char(sn.end_interval_time,'dd Mon HH24:mi:ss') mydate, d.value "phys_writes", round(100 * (((a.value-e.value)+(b.value-f.value))-(c.value-g.value)) / ((a.value-e.value)+(b.value-f.value))) "BUFFER HIT RATIO" from dba_hist_sysstat a, dba_hist_sysstat b, dba_hist_sysstat c, dba_hist_sysstat d, dba_hist_sysstat e, dba_hist_sysstat f, dba_hist_sysstat g, dba_hist_snapshot sn where -- (round(100 * (((a.value-e.value)+(b.value-f.value))-(c.value-g.value)) --/ ((a.value-e.value)+(b.value-f.value))) ) < 90 --and sn.snap_id = (select max(snap_id) from dba_hist_snapshot) and a.snap_id = sn.snap_id and b.snap_id = sn.snap_id and c.snap_id = sn.snap_id and d.snap_id = sn.snap_id and e.snap_id = sn.snap_id-1 and f.snap_id = sn.snap_id-1 and g.snap_id = sn.snap_id-1 and a.stat_name = 'consistent gets' and e.stat_name = 'consistent gets' and b.stat_name = 'db block gets' and f.stat_name = 'db block gets' and c.stat_name = 'physical reads' and g.stat_name = 'physical reads' and d.stat_name = 'physical writes' ; column mydate heading 'Yr. Mo Dy Hr.' format a16 column reloads format 999,999,999 column hit_ratio format 999.99 column pin_hit_ratio format 999.99 break on mydate skip 2; select to_char(sn.end_interval_time,'yyyy-mm-dd HH24') mydate, new.namespace, (new.gethits-old.gethits)/(new.gets-old.gets) hit_ratio, (new.pinhits-old.pinhits)/(new.pins-old.pins) pin_hit_ratio, new.reloads from dba_hist_librarycache old, dba_hist_librarycache new, dba_hist_snapshot sn where new.snap_id = sn.snap_id and old.snap_id = new.snap_id-1 and old.namespace = new.namespace and new.gets-old.gets > 0 and new.pins-old.pins > 0 ; prompt *********************************************************** prompt When there are high disk sorts, you should investigate prompt increasing sort_area_size, or adding indexes to force index_full scans prompt *********************************************************** column sorts_memory format 999,999,999 column sorts_disk format 999,999,999 column ratio format .9999999999999 select to_char(sn.end_interval_time,'dd Mon HH24:mi:ss') mydate, newmem.value-oldmem.value sorts_memory, newdsk.value-olddsk.value sorts_disk, (newdsk.value-olddsk.value)/(newmem.value-oldmem.value) ratio from dba_hist_sysstat oldmem, dba_hist_sysstat newmem, dba_hist_sysstat newdsk, dba_hist_sysstat olddsk, dba_hist_snapshot sn where -- Where there are more than 100 disk sorts per hour -- newdsk.value-olddsk.value > 100 --and sn.snap_id = (select max(snap_id) from dba_hist_snapshot) and newdsk.snap_id = sn.snap_id and olddsk.snap_id = sn.snap_id-1 and newmem.snap_id = sn.snap_id and oldmem.snap_id = sn.snap_id-1 and oldmem.stat_name = 'sorts (memory)' and newmem.stat_name = 'sorts (memory)' and olddsk.stat_name = 'sorts (disk)' and newdsk.stat_name = 'sorts (disk)' and newmem.value-oldmem.value > 0 ; prompt *********************************************************** prompt When there is high I/O waits, disk bottlenecks may exist prompt Run iostats to find the hot disk and shuffle files to prompt remove the contention prompt prompt See p. 191 "High Performance Oracle8 Tuning" by Don Burleson prompt prompt *********************************************************** break on snapdate skip 2 column snapdate format a16 column filename format a40 select to_char(sn.end_interval_time,'dd Mon HH24:mi:ss') mydate, old.filename, new.wait_count -old.wait_count waits from dba_hist_filestatxs old, dba_hist_filestatxs new, dba_hist_snapshot sn where sn.snap_id = (select max(snap_id) from dba_hist_snapshot) and new.wait_count -old.wait_count > 0 and new.snap_id = sn.snap_id and old.filename = new.filename and old.snap_id = sn.snap_id-1 ; prompt *********************************************************** prompt Buffer Bury Waits may signal a high update table with too prompt few freelists. Find the offending table and add more freelists. prompt *********************************************************** column buffer_busy_wait format 999,999,999 select to_char(sn.end_interval_time,'dd Mon HH24:mi:ss') mydate, avg(new.buffer_busy_wait-old.buffer_busy_wait) buffer_busy_wait from dba_hist_buffer_pool_stat old, dba_hist_buffer_pool_stat new, dba_hist_snapshot sn where sn.snap_id = (select max(snap_id) from dba_hist_snapshot) and new.snap_id = sn.snap_id and new.snap_id = sn.snap_id and old.snap_id = sn.snap_id-1 --having -- avg(new.buffer_busy_wait-old.buffer_busy_wait) > 100 group by to_char(sn.end_interval_time,'dd Mon HH24:mi:ss') ; prompt *********************************************************** prompt High redo log space requests indicate a need to increase prompt the log_buffer parameter prompt *********************************************************** column redo_log_space_requests format 999,999,999 select to_char(sn.end_interval_time,'dd Mon HH24:mi:ss') mydate, newmem.value-oldmem.value redo_log_space_requests from dba_hist_sysstat oldmem, dba_hist_sysstat newmem, dba_hist_snapshot sn where sn.snap_id = (select max(snap_id) from dba_hist_snapshot) --and -- newmem.value-oldmem.value > 30 and newmem.snap_id = sn.snap_id and oldmem.snap_id = sn.snap_id-1 and oldmem.stat_name = 'redo log space requests' and newmem.stat_name = 'redo log space requests' and newmem.value-oldmem.value > 0 ; prompt *********************************************************** prompt Table fetch continued row indicates chained rows, or prompt fetches of long datatypes (long raw, blob) prompt prompt Investigate increasing db_block_size or reorganizing tables prompt with chained rows. prompt prompt *********************************************************** column table_fetch_continued_row format 999,999,999 select to_char(sn.end_interval_time,'dd Mon HH24:mi:ss') mydate, avg(newmem.value-oldmem.value) table_fetch_continued_row from dba_hist_sysstat oldmem, dba_hist_sysstat newmem, dba_hist_snapshot sn where sn.snap_id = (select max(snap_id) from dba_hist_snapshot) and newmem.snap_id = sn.snap_id and oldmem.snap_id = sn.snap_id-1 and oldmem.stat_name = 'table fetch continued row' and newmem.stat_name = 'table fetch continued row' --and -- newmem.value-oldmem.value > 0 --having -- avg(newmem.value-oldmem.value) > 10000 group by to_char(sn.end_interval_time,'dd Mon HH24:mi:ss') ; prompt *********************************************************** prompt Enqueue Deadlocks indicate contention within the Oracle prompt shared pool. prompt prompt Investigate increasing shared_pool_size prompt *********************************************************** column enqueue_deadlocks format 999,999,999 select to_char(sn.end_interval_time,'dd Mon HH24:mi:ss') mydate, a.value enqueue_deadlocks from dba_hist_sysstat a, dba_hist_snapshot sn where sn.snap_id = (select max(snap_id) from dba_hist_snapshot) and a.snap_id = sn.snap_id and a.stat_name = 'enqueue deadlocks' ; prompt *********************************************************** prompt Long-table full table scans can indicate a need to: prompt prompt - Make the offending tables parallel query prompt (alter table xxx parallel degree yyy;) prompt - Place the table in the RECYCLE pool prompt - Build an index on the table to remove the FTS prompt prompt To locate the table, run access.sql prompt prompt *********************************************************** column fts format 999,999,999 select to_char(sn.end_interval_time,'dd Mon HH24:mi:ss') mydate, newmem.value-oldmem.value fts from dba_hist_sysstat oldmem, dba_hist_sysstat newmem, dba_hist_snapshot sn where sn.snap_id = (select max(snap_id) from dba_hist_snapshot) and newmem.snap_id = sn.snap_id and oldmem.snap_id = sn.snap_id-1 and oldmem.stat_name = 'table scans (long tables)' and newmem.stat_name = 'table scans (long tables)' ; spool off; Chapter 6 < awr_disk_reads.sql break on begin_interval_time skip 2 column phyrds format 999,999,999 column begin_interval_time format a25 select begin_interval_time, filename, phyrds from dba_hist_filestatxs natural join dba_hist_snapshot; < Creation Script for dba_hist_sysstat create table dba_hist_sysstat as select s.snap_id, s.dbid, s.instance_number, s.statistic#, s.statistic_hash, nm.statistic_name, value from wrm$_snapshot sn, wrh$_sysstat s, dba_hist_stat_name nm where s.statistic_hash = nm.statistic_hash and s.statistic# = nm.statistic# and s.dbid = nm.dbid and s.snap_id = sn.snap_id and s.dbid = sn.dbid and s.instance_number = sn.instance_number and sn.status = 0 and sn.bl_moved = 0 union all select s.snap_id, s.dbid, s.instance_number, s.statistic#, s.statistic_hash, nm.statistic_name, value from WRM$_SNAPSHOT sn, WRH$_SYSSTAT_BL s, DBA_HIST_STAT_NAME nm where s.statistic_hash = nm.statistic_hash and s.statistic# = nm.statistic# and s.dbid = nm.dbid and s.snap_id = sn.snap_id and s.dbid = sn.dbid and s.instance_number = sn.instance_number and sn.status = 0 and sn.bl_moved = 1; select s1.ucomment, w1.event, s1.snap_id, w1.total_waits, lag(w1.total_waits) over (order by s1.snap_id) prev_val, w1.total_waits - lag(w1.total_waits) over (order by s1.snap_id) delta_val from stats$snapshot s1, stats$system_event w1 where s1.snap_id between 313 and 320 and s1.snap_id = w1.snap_id and w1.event = 'db file sequential read' order by w1.event, s1.snap_id; select sy.snap_id, sy.statistic# statistic#, sy.name statname, sy.value - (LAG(sy.value) over (partition by sy.name order by sy.snap_id)) statdelta from stats$sysstat sy where sy.snap_id in (12208,12599,13480,13843) and sy.name IN ('consistent gets','consistent changes', 'db block gets', 'db block changes') order by sy.name, sy.snap_id; < wait_alert_email.ksh #!/bin/ksh # First, we must set the environment . . . . ORACLE_SID=proderp export ORACLE_SID ORACLE_HOME=`cat /var/opt/oracle/oratab|grep \^$ORACLE_SID:|cut -f2 -d':'` export ORACLE_HOME PATH=$ORACLE_HOME/bin:$PATH export PATH SERVER_NAME=`uname -a|awk '{print $2}'` typeset -u SERVER_NAME export SERVER_NAME # sample every 10 seconds SAMPLE_TIME=10 while true do #************************************************************* # Test to see if Oracle is accepting connections #************************************************************* $ORACLE_HOME/bin/sqlplus -s /<<! > /tmp/check_$ORACLE_SID.ora select * from v\$database; exit ! #************************************************************* # If not, exit immediately . . . #************************************************************* check_stat=`cat /tmp/check_$ORACLE_SID.ora|grep -i error|wc -l`; oracle_num=`expr $check_stat` if [ $oracle_num -gt 0 ] then exit 0 fi rm -f /export/home/oracle/statspack/busy.lst $ORACLE_HOME/bin/sqlplus -s perfstat/perfstat<<!> /tmp/busy.lst set feedback off; select sysdate, event, substr(tablespace_name,1,14), p2 from v\$session_wait a, dba_data_files b where a.p1 = b.file_id ; ! var=`cat /tmp/busy.lst|wc -l` echo $var if [[ $var -gt 1 ]]; then echo *******************************************************************" echo "There are waits" cat /tmp/busy.lst|mailx -s "Prod block wait found"\ info@remote-dba.net \ Larry_Ellison@oracle.com echo *******************************************************************" exit fi sleep $SAMPLE_TIME done < event_read_waits.sql select to_char(snap_time,'mm/dd/yyyy hh24:mi:ss') snaptime , max(decode(event,'db file scattered read', nvl(wait_ms,0), null)) wait_ms_dbfscatrd , max(decode(event,'db file sequential read',nvl(wait_ms,0), null)) wait_ms_dbfseqrd , max(decode(event,'db file scattered read', nvl(waits,0), null)) waits_dbfscatrd , max(decode(event,'db file sequential read',nvl(waits,0), null)) waits_dbfseqrd from ( select ps.snap_time , event , case when (total_waits - lag_total_waits > 0) then round(( (time_waited_micro - lag_time_waited_micro) / (total_waits - lag_total_waits)) / 1000) else -1 end wait_ms , (total_waits - lag_total_waits) waits , (time_waited_micro - lag_time_waited_micro) time_waited from ( select se.snap_id , event , se.total_waits , se.total_timeouts , se.time_waited_micro , lag(se.event) over (order by snap_id, event) lag_event , lag(se.snap_id) over (order by snap_id, event) lag_snap_id , lag(se.total_waits) over (order by snap_id, event) lag_total_waits , lag(se.total_timeouts) over (order by snap_id, event) lag_total_timeouts , lag(se.time_waited_micro) over (order by snap_id, event) lag_time_waited_micro from perfstat.stats$system_event se where event = 'db file sequential read' and snap_id in (select snap_id from stats$snapshot where snap_time > trunc(sysdate) - 1 ) union all select se.snap_id , event , se.total_waits , se.total_timeouts , se.time_waited_micro , lag(se.event) over (order by snap_id, event) lag_event , lag(se.snap_id) over (order by snap_id, event) lag_snap_id , lag(se.total_waits) over (order by snap_id, event) lag_total_waits , lag(se.total_timeouts) over (order by snap_id, event) lag_total_timeouts , lag(se.time_waited_micro) over (order by snap_id, event) lag_time_waited_micro from perfstat.stats$system_event se where event = 'db file scattered read' and snap_id in (select snap_id from stats$snapshot where snap_time > trunc(sysdate) -1 ) order by event, snap_id ) a , perfstat.stats$snapshot ss , perfstat.stats$snapshot ps where a.lag_snap_id = ps.snap_id and a.snap_id = ss.snap_id and a.lag_total_waits != a.total_waits and a.event = a.lag_event order by a.snap_id, event ) group by snap_time ; < wt_events_int.sql select event , waits "Waits" , time "Wait Time (s)" , pct*100 "Percent of Tot" , waitclass "Wait Class" from (select e.event_name event , e.total_waits - nvl(b.total_waits,0) waits , (e.time_waited_micro - nvl(b.time_waited_micro,0))/1000000 time , (e.time_waited_micro - nvl(b.time_waited_micro,0))/ (select sum(e1.time_waited_micro - nvl(b1.time_waited_micro,0)) from dba_hist_system_event b1 , dba_hist_system_event e1 where b1.snap_id(+) = b.snap_id and e1.snap_id = e.snap_id and b1.dbid(+) = b.dbid and e1.dbid = e.dbid and b1.instance_number(+) = b.instance_number and e1.instance_number = e.instance_number and b1.event_id(+) = e1.event_id and e1.total_waits > nvl(b1.total_waits,0) and e1.wait_class <> 'Idle' ) pct , e.wait_class waitclass from dba_hist_system_event b , dba_hist_system_event e where b.snap_id(+) = &pBgnSnap and e.snap_id = &pEndSnap and b.dbid(+) = &pDbId and e.dbid = &pDbId and b.instance_number(+) = &pInstNum and e.instance_number = &pInstNum and b.event_id(+) = e.event_id and e.total_waits > nvl(b.total_waits,0) and e.wait_class <> 'Idle' order by time desc, waits desc ); < awr_system_events.sql select event "Event Name", waits "Waits", timeouts "Timeouts", time "Wait Time (s)", avgwait "Avg Wait (ms)", waitclass "Wait Class" from (select e.event_name event , e.total_waits - nvl(b.total_waits,0) waits , e.total_timeouts - nvl(b.total_timeouts,0) timeouts , (e.time_waited_micro - nvl(b.time_waited_micro,0))/1000000 time , decode ((e.total_waits - nvl(b.total_waits, 0)), 0, to_number(NULL), ((e.time_waited_micro - nvl(b.time_waited_micro,0))/1000) / (e.total_waits - nvl(b.total_waits,0)) ) avgwait , e.wait_class waitclass from dba_hist_system_event b , dba_hist_system_event e where b.snap_id(+) = &pBgnSnap and e.snap_id = &pEndSnap and b.dbid(+) = &pDbId and e.dbid = &pDbId and b.instance_number(+) = &pInstNum and e.instance_number = &pInstNum and b.event_id(+) = e.event_id and e.total_waits > nvl(b.total_waits,0) and e.wait_class <> 'Idle' ) order by time desc, waits desc; < show_background_waits.sql select event "Event Name", waits "Waits", timeouts "Timeouts", time "Wait Time (s)", avgwait "Avg Wait (ms)", waitclass "Wait Class" from (select e.event_name event , e.total_waits - nvl(b.total_waits,0) waits , e.total_timeouts - nvl(b.total_timeouts,0) timeouts , (e.time_waited_micro - nvl(b.time_waited_micro,0))/1000000 time , decode ((e.total_waits - nvl(b.total_waits, 0)), 0, to_number(NULL), ((e.time_waited_micro - nvl(b.time_waited_micro,0))/1000) / (e.total_waits - nvl(b.total_waits,0)) ) avgwait , e.wait_class waitclass from dba_hist_bg_event_summary b , dba_hist_bg_event_summary e where b.snap_id(+) = &pBgnSnap and e.snap_id = &pEndSnap and b.dbid(+) = &pDbId and e.dbid = &pDbId and b.instance_number(+) = &pInstNum and e.instance_number = &pInstNum and b.event_id(+) = e.event_id and e.total_waits > nvl(b.total_waits,0) and e.wait_class <> 'Idle' ) order by time desc, waits desc; < wait_stat_int.sql select e.class "E.CLASS" , e.wait_count - nvl(b.wait_count,0) "Waits" , e.time - nvl(b.time,0) "Total Wait Time (cs)" , (e.time - nvl(b.time,0)) / (e.wait_count - nvl(b.wait_count,0)) "Avg Time (cs)" from dba_hist_waitstat b , dba_hist_waitstat e where b.snap_id = &pBgnSnap and e.snap_id = &pEndSnap and b.dbid = &pDbId and e.dbid = &pDbId and b.dbid = e.dbid and b.instance_number = &pInstNum and e.instance_number = &pInstNum and b.instance_number = e.instance_number and b.class = e.class and b.wait_count < e.wait_count order by 3 desc, 2 desc; < enq_stat_int.sql select ety “Enqueue”, reqs "Requests", sreq "Successful Gets", freq "Failed Gets", waits "Waits", wttm "Wait Time (s)", awttm "Avg Wait Time(ms)" from ( select /*+ ordered */ e.eq_type || '-' || to_char(nvl(l.name,' ')) || decode( upper(e.req_reason) , 'CONTENTION', null , '-', null , ' ('||e.req_reason||')') ety , e.total_req# - nvl(b.total_req#,0) reqs , e.succ_req# - nvl(b.succ_req#,0) sreq , e.failed_req# - nvl(b.failed_req#,0) freq , e.total_wait# - nvl(b.total_wait#,0) waits , (e.cum_wait_time - nvl(b.cum_wait_time,0))/1000 wttm , decode( (e.total_wait# - nvl(b.total_wait#,0)) , 0, to_number(NULL) , ( (e.cum_wait_time - nvl(b.cum_wait_time,0)) / (e.total_wait# - nvl(b.total_wait#,0)) ) ) awttm from dba_hist_enqueue_stat e , dba_hist_enqueue_stat b , v$lock_type l where b.snap_id(+) = &pBgnSnap and e.snap_id = &pEndSnap and b.dbid(+) = &pDbId and e.dbid = &pDbId and b.dbid(+) = e.dbid and b.instance_number(+) = &pInstNum and e.instance_number = &pInstNum and b.instance_number(+) = e.instance_number and b.eq_type(+) = e.eq_type and b.req_reason(+) = e.req_reason and e.total_wait# - nvl(b.total_wait#,0) > 0 and l.type(+) = e.eq_type order by wttm desc, waits desc); < metric_summary.sql select metric_name “Metric Name”, metric_unit "Metric Unit", minval "Minimum Value", maxval "Maximum Value", average "Average Value" from dba_hist_sysmetric_summary where snap_id = &pEndSnap and dbid = &pDbId and instance_number = &pInstNum; < sys_time_model_int.sql column "Statistic Name" format A40 column "Time (s)" format 999,999 column "Percent of Total DB Time" format 999,999 select e.stat_name "Statistic Name" , (e.value - b.value)/1000000 "Time (s)" , decode( e.stat_name,'DB time' , to_number(null) , 100*(e.value - b.value) )/ ( select nvl((e1.value - b1.value),-1) from dba_hist_sys_time_model e1 , dba_hist_sys_time_model b1 where b1.snap_id = b.snap_id and e1.snap_id = e.snap_id and b1.dbid = b.dbid and e1.dbid = e.dbid and b1.instance_number = b.instance_number and e1.instance_number = e.instance_number and b1.stat_name = 'DB time' and b1.stat_id = e1.stat_id ) "Percent of Total DB Time" from dba_hist_sys_time_model e , dba_hist_sys_time_model b where b.snap_id = &pBgnSnap and e.snap_id = &pEndSnap and b.dbid = &pDbId and e.dbid = &pDbId and b.instance_number = &pInst_Num and e.instance_number = &pInst_Num and b.stat_id = e.stat_id and e.value - b.value > 0 order by 2 desc; < sys_stat_int.sql select e.stat_name "Statistic Name" , e.value - b.value "Total" , round((e.value - b.value)/ ( select avg( extract( day from (e1.end_interval_time-b1.end_interval_time) )*24*60*60+ extract( hour from (e1.end_interval_time-b1.end_interval_time) )*60*60+ extract( minute from (e1.end_interval_time-b1.end_interval_time) )*60+ extract( second from (e1.end_interval_time-b1.end_interval_time)) ) from dba_hist_snapshot b1 ,dba_hist_snapshot e1 where b1.snap_id = b.snap_id and e1.snap_id = e.snap_id and b1.dbid = b.dbid and e1.dbid = e.dbid and b1.instance_number = b.instance_number and e1.instance_number = e.instance_number and b1.startup_time = e1.startup_time and b1.end_interval_time < e1.end_interval_time ),2) "Per Second" from dba_hist_sysstat b , dba_hist_sysstat e where b.snap_id = &pBgnSnap and e.snap_id = &pEndSnap and b.dbid = &pDbId and e.dbid = &pDbId and b.instance_number = &pInstNum and e.instance_number = &pInstNum and b.stat_id = e.stat_id and e.stat_name not in ( 'logons current' , 'opened cursors current' , 'workarea memory allocated' ) and e.value >= b.value and e.value > 0 order by 1 asc; < latch_int.sql select e.latch_name "Latch Name" , e.gets - b.gets "Get Requests" , to_number(decode(e.gets, b.gets, null, (e.misses - b.misses) * 100/(e.gets - b.gets))) "Percent Get Misses" , to_number(decode(e.misses, b.misses, null, (e.sleeps - b.sleeps)/(e.misses - b.misses))) "Avg Sleeps / Miss" , (e.wait_time - b.wait_time)/1000000 "Wait Time (s)" , e.immediate_gets - b.immediate_gets "No Wait Requests" , to_number(decode(e.immediate_gets, b.immediate_gets, null, (e.immediate_misses - b.immediate_misses) * 100 / (e.immediate_gets - b.immediate_gets))) "Percent No Wait Miss" from dba_hist_latch b , dba_hist_latch e where b.snap_id = &pBgnSnap and e.snap_id = &pEndSnap and b.dbid = &pDbId and e.dbid = &pDbId and b.dbid = e.dbid and b.instance_number = &pInstNum and e.instance_number = &pInstNum and b.instance_number = e.instance_number and b.latch_hash = e.latch_hash and e.gets - b.gets > 0 order by 1, 4; < latch_miss_int.sql select latchname "Latch Name", nwmisses "No Wait Misses", sleeps "Sleeps", waiter_sleeps "Waiter Sleeps" From ( select e.parent_name||' '||e.where_in_code latchname , e.nwfail_count - nvl(b.nwfail_count,0) nwmisses , e.sleep_count - nvl(b.sleep_count,0) sleeps , e.wtr_slp_count - nvl(b.wtr_slp_count,0) waiter_sleeps from dba_hist_latch_misses_summary b , dba_hist_latch_misses_summary e where b.snap_id(+) = &pBgnSnap and e.snap_id = &pEndSnap and b.dbid(+) = &pDbId and e.dbid = &pDbId and b.dbid(+) = e.dbid and b.instance_number(+) = &pInstNum and e.instance_number = &pInstNum and b.instance_number(+) = e.instance_number and b.parent_name(+) = e.parent_name and b.where_in_code(+) = e.where_in_code and e.sleep_count > nvl(b.sleep_count,0) ) order by 1, 3 desc; < lib_cache_int.sql select b.namespace "Name Space" , e.gets - b.gets "Get Requests" , to_number(decode(e.gets,b.gets,null, 100 - (e.gethits - b.gethits) * 100/(e.gets - b.gets))) "Get Pct Miss" , e.pins - b.pins "Pin Requests" , to_number(decode(e.pins,b.pins,null, 100 - (e.pinhits - b.pinhits) * 100/(e.pins - b.pins))) "Pin Pct Miss" , e.reloads - b.reloads "Reloads" , e.invalidations - b.invalidations "Invalidations" from dba_hist_librarycache b , dba_hist_librarycache e where b.snap_id = &pBgnSnap and e.snap_id = &pEndSnap and b.dbid = &pDbId and e.dbid = &pDbId and b.dbid = e.dbid and b.instance_number = &pInstNum and e.instance_number = &pInstNum and b.instance_number = e.instance_number and b.namespace = e.namespace; < rowcache_int.sql select param "Parameter", gets "Get Requests", getm "Pct Miss" From (select lower(b.parameter) param , e.gets - b.gets gets , to_number(decode(e.gets,b.gets,null, (e.getmisses - b.getmisses) * 100/(e.gets - b.gets))) getm , e.scans - b.scans scans , to_number(decode(e.scans,b.scans,null, (e.scanmisses - b.scanmisses) * 100/(e.scans - b.scans))) scanm , e.modifications - b.modifications mods , e.usage usage from dba_hist_rowcache_summary b , dba_hist_rowcache_summary e where b.snap_id = &pBgnSnap and e.snap_id = &pEndSnap and b.dbid = &pDbId and e.dbid = &pDbId and b.dbid = e.dbid and b.instance_number = &pInstNum and e.instance_number = &pInstNum and b.instance_number = e.instance_number and b.parameter = e.parameter and e.gets - b.gets > 0 ) order by param; < buf_pool_int.sql select name , numbufs "Number of Buffers" , buffs "Buffer Gets" , conget "Consistent Gets" , phread "Physical Reads" , phwrite "Physical Writes" , fbwait "Free Buffer Waits" , bbwait "Buffer Busy Waits" , wcwait "Write Complete Waits" , poolhr "Pool Hit %" From (select e.name , e.set_msize numbufs , decode( e.db_block_gets - nvl(b.db_block_gets,0) + e.consistent_gets - nvl(b.consistent_gets,0) , 0, to_number(null) , (100* (1 - ( (e.physical_reads - nvl(b.physical_reads,0)) / ( e.db_block_gets - nvl(b.db_block_gets,0) + e.consistent_gets - nvl(b.consistent_gets,0)) ) ) ) ) poolhr , e.db_block_gets - nvl(b.db_block_gets,0) + e.consistent_gets - nvl(b.consistent_gets,0) buffs , e.consistent_gets - nvl(b.consistent_gets,0) conget , e.physical_reads - nvl(b.physical_reads,0) phread , e.physical_writes - nvl(b.physical_writes,0) phwrite , e.free_buffer_wait - nvl(b.free_buffer_wait,0) fbwait , e.write_complete_wait - nvl(b.write_complete_wait,0) wcwait , e.buffer_busy_wait - nvl(b.buffer_busy_wait,0) bbwait from dba_hist_buffer_pool_stat b , dba_hist_buffer_pool_stat e where b.snap_id(+) = &pBgnSnap and e.snap_id = &pEndSnap and b.dbid(+) = &pDbId and e.dbid = &pDbId and b.dbid(+) = e.dbid and b.instance_number(+) = &pInst_Num and e.instance_number = &pInst_Num and b.instance_number(+) = e.instance_number and b.id(+) = e.id) order by 1; < os_stat_int.sql select e.stat_name "Statistic Name" , decode(e.stat_name, 'NUM_CPUS', e.value, e.value - b.value) "Total" , decode( instrb(e.stat_name, 'BYTES'), 0, to_number(null) , round((e.value - b.value)/( select avg( extract( day from (e1.end_interval_time-b1.end_interval_time) )*24*60*60+ extract( hour from (e1.end_interval_time-b1.end_interval_time) )*60*60+ extract( minute from (e1.end_interval_time-b1.end_interval_time) )*60+ extract( second from (e1.end_interval_time-b1.end_interval_time)) ) from dba_hist_snapshot b1 ,dba_hist_snapshot e1 where b1.snap_id = b.snap_id and e1.snap_id = e.snap_id and b1.dbid = b.dbid and e1.dbid = e.dbid and b1.instance_number = b.instance_number and e1.instance_number = e.instance_number and b1.startup_time = e1.startup_time and b1.end_interval_time < e1.end_interval_time ),2)) "Per Second" from dba_hist_osstat b , dba_hist_osstat e where b.snap_id = &pBgnSnap and e.snap_id = &pEndSnap and b.dbid = &pDbId and e.dbid = &pDbId and b.instance_number = &pInstNum and e.instance_number = &pInstNum and b.stat_id = e.stat_id and e.value >= b.value and e.value > 0 order by 1 asc; < high_sql_buf_gets.sql -- ************************************************* -- Copyright © 2005 by Rampant TechPress -- This script is free for non-commercial purposes -- with no warranties. Use at your own risk. -- -- To license this script for a commercial purpose, -- contact info@rampant.cc -- ************************************************* select sql_id , buffer_gets_total "Buffer Gets" , executions_total "Executions" , buffer_gets_total/executions_total "Gets / Exec" , pct*100 "% Total" , cpu_time_total/1000000 "CPU Time (s)" , elapsed_time_total/1000000 "Elapsed Time (s)" , module "SQL Module" , stmt "SQL Statement" from (select e.sql_id sql_id , e.buffer_gets_total - nvl(b.buffer_gets_total,0) buffer_gets_total , e.executions_total - nvl(b.executions_total,0) executions_total , (e.buffer_gets_total - nvl(b.buffer_gets_total,0))/ ( select e1.value - nvl(b1.value,0) from dba_hist_sysstat b1 , dba_hist_sysstat e1 where b1.snap_id(+) = b.snap_id and e1.snap_id = e.snap_id and b1.dbid(+) = b.dbid and e1.dbid = e.dbid and b1.instance_number(+) = b.instance_number and e1.instance_number = e.instance_number and b1.stat_id = e1.stat_id and e1.stat_name = 'session logical reads' ) pct , e.elapsed_time_total - nvl(b.elapsed_time_total,0) elapsed_time_total , e.cpu_time_total - nvl(b.cpu_time_total,0) cpu_time_total , e.module , t.sql_text stmt from dba_hist_sqlstat e , dba_hist_sqlstat b , dba_hist_sqltext t where b.snap_id(+) = @pBgnSnap and b.dbid(+) = e.dbid and b.instance_number(+) = e.instance_number and b.sql_id(+) = e.sql_id and e.snap_id = &pEndSnap and e.dbid = &pDBId and e.instance_number = &pInstNum and (e.executions_total - nvl(b.executions_total,0)) > 0 and t.sql_id = b.sql_id ) order by 2 desc; < seg_top_logreads.sql -- ************************************************* -- Copyright © 2005 by Rampant TechPress -- This script is free for non-commercial purposes -- with no warranties. Use at your own risk. -- -- To license this script for a commercial purpose, -- contact info@rampant.cc -- ************************************************* select object_name "Object Name" , tablespace_name "Tablespace Name" , object_type "Object Type" , logical_reads_total "Logical Reads" , ratio "%Total" from( select n.owner||'.'||n.object_name||decode(n.subobject_name,null,null,'.'||n.subobject_name) object_name , n.tablespace_name , case when length(n.subobject_name) < 11 then n.subobject_name else substr(n.subobject_name,length(n.subobject_name)-9) end subobject_name , n.object_type , r.logical_reads_total , round(r.ratio * 100, 2) ratio from dba_hist_seg_stat_obj n , (select * from (select e.dataobj# , e.obj# , e.dbid , e.logical_reads_total - nvl(b.logical_reads_total, 0) logical_reads_total , ratio_to_report(e.logical_reads_total - nvl(b.logical_reads_total, 0)) over () ratio from dba_hist_seg_stat e , dba_hist_seg_stat b where b.snap_id = 2694 and e.snap_id = 2707 and b.dbid = 37933856 and e.dbid = 37933856 and b.instance_number = 1 and e.instance_number = 1 and e.obj# = b.obj# and e.dataobj# = b.dataobj# and e.logical_reads_total - nvl(b.logical_reads_total, 0) > 0 order by logical_reads_total desc) d where rownum <= 100) r where n.dataobj# = r.dataobj# and n.obj# = r.obj# and n.dbid = r.dbid ) order by logical_reads_total desc; < db_tbsp_io.sql -- ************************************************* -- Copyright © 2005 by Rampant TechPress -- This script is free for non-commercial purposes -- with no warranties. Use at your own risk. -- -- To license this script for a commercial purpose, -- contact info@rampant.cc -- ************************************************* select tbsp “Tablespace” , ios "I/O Activity" From ( select e.tsname tbsp , sum (e.phyrds - nvl(b.phyrds,0)) + sum (e.phywrts - nvl(b.phywrts,0)) ios from dba_hist_filestatxs e , dba_hist_filestatxs b where b.snap_id(+) = &pBgnSnap and e.snap_id = &pEndSnap and b.dbid(+) = &pDbId and e.dbid = &pDbId and b.dbid(+) = e.dbid and b.instance_number(+) = &pInstNum and e.instance_number = &pInstNum and b.instance_number(+) = e.instance_number and b.file# = e.file# and ( (e.phyrds - nvl(b.phyrds,0) ) + (e.phywrts - nvl(b.phywrts,0)) ) > 0 group by e.tsname union select e.tsname tbsp , sum (e.phyrds - nvl(b.phyrds,0)) + sum (e.phywrts - nvl(b.phywrts,0)) ios from dba_hist_tempstatxs e , dba_hist_tempstatxs b where b.snap_id(+) = &pBgnSnap and e.snap_id = &pEndSnap and b.dbid(+) = &pDbId and e.dbid = &pDbId and b.dbid(+) = e.dbid and b.instance_number(+) = &pInstNum and e.instance_number = &pInstNum and b.instance_number(+) = e.instance_number and b.file# = e.file# and ( (e.phyrds - nvl(b.phyrds,0) ) + (e.phywrts - nvl(b.phywrts,0) ) ) > 0 group by e.tsname ); Chapter 7 < display_sql_captured.sql display_sql_captured.sql select aa, hv from ( select /*+ ordered use_nl (b st) */ decode( st.piece , 0 , lpad(to_char((e.buffer_gets - nvl(b.buffer_gets,0)) ,'99,999,999,999') ,15)||' '|| lpad(to_char((e.executions - nvl(b.executions,0)) ,'999,999,999') ,12)||' '|| lpad((to_char(decode(e.executions - nvl(b.executions,0) ,0, to_number(null) ,(e.buffer_gets - nvl(b.buffer_gets,0)) / (e.executions - nvl(b.executions,0))) ,'999,999,990.0')) ,14) ||' '|| lpad((to_char(100*(e.buffer_gets - nvl(b.buffer_gets,0))/:gets ,'990.0')) , 6) ||' '|| lpad( nvl(to_char( (e.cpu_time - nvl(b.cpu_time,0))/1000000 , '9990.00') , ' '),8) || ' ' || lpad( nvl(to_char( (e.elapsed_time - nvl(b.elapsed_time,0))/1000000 , '99990.00') , ' '),9) || ' ' || lpad(e.old_hash_value,10)||''|| decode(e.module,null,st.sql_text ,rpad('Module: '||e.module,80)||st.sql_text) , st.sql_text) aa , e.old_hash_value hv from stats$sql_summary e , stats$sql_summary b , stats$sqltext st where b.snap_id(+) = :bid and b.dbid(+) = e.dbid and b.instance_number(+) = e.instance_number and b.old_hash_value(+) = e.old_hash_value and b.address(+) = e.address and b.text_subset(+) = e.text_subset and e.snap_id = :eid and e.dbid = :dbid and e.instance_number = :inst_num and e.old_hash_value = st.old_hash_value and e.text_subset = st.text_subset and st.piece <= &&num_rows_per_hash and e.executions > nvl(b.executions,0) and 100*(e.buffer_gets - nvl(b.buffer_gets,0))/:gets > &&top_pct_sql order by (e.buffer_gets - nvl(b.buffer_gets,0)) desc, e.old_hash_value, st.piece ) where rownum < &&top_n_sql; Chapter 8 < Redo_allocation_latches.sql select round( greatest( (sum(decode(ln.name,'redo allocation',misses,0)) /greatest(sum(decode(ln.name,'redo allocation',gets,0)),1)), (sum(decode(ln.name,'redo allocation',immediate_misses,0)) /greatest(sum(decode(ln.name,'redo allocation',immediate_gets,0)) +sum(decode(ln.name,'redo allocation',immediate_misses,0)),1)) )*100,2) from v$latch l, v$latchname ln where l.latch#=ln.latch#; Chapter 9 --************************************************************* -- SKEWONLY option—Detailed analysis -- -- Use this method for a first-time analysis for skewed indexes -- This runs a long time because all indexes are examined --************************************************************* begin dbms_stats.gather_schema_stats( ownname => 'SCOTT', estimate_percent => dbms_stats.auto_sample_size, method_opt => 'for all columns size skewonly', degree => 7 ); end; --************************************************************** -- REPEAT OPTION - Only reanalyze histograms for indexes -- that have histograms -- -- Following the initial analysis, the weekly analysis -- job will use the “repeat” option. The repeat option -- tells dbms_stats that no indexes have changed, and -- it will only reanalyze histograms for -- indexes that have histograms. --************************************************************** begin dbms_stats.gather_schema_stats( ownname => 'SCOTT', estimate_percent => dbms_stats.auto_sample_size, method_opt => 'for all columns size repeat', degree => 7 ); end; -- ******************************** -- Create column histograms -- ******************************** exec dbms_stats.gather_table_stats (null, ‘sales’ method_opt=> ‘for all columns size skewonly); -- ******************************** -- Verify existence of histograms -- ******************************** select column_name, histogram from user_tab_sol_statistics where table_name = ‘SALES’; -- **************************************** - Create the extended optimizer statistics -- **************************************** select dbms_stats.create_extended_stats (NULL, 'sales', '(product_price+sales_tax)') from dual; -- ********************************************************* -- Display extended statistics extensions -- ********************************************************* column extension format a30 select extension_name, extension from dba_stat_extensions where table_name = 'EMP'; EXTENSION_NAME EXTENSION ------------------------------ ------------------------------ SYS_STU3VG629OEYG6FN0EKTGV_HQ6 ("JOB","DEPTNO") SYS_STULPA1A#B6YL4KQ59DQO3OADQ ("JOB","MGR") SYS_STU2JLSDWQAFJHQST7$QK81_YB (LOWER("ENAME")) SYS_STUOK75YSL165W#_X8GUYL0A1X (UPPER("ENAME")) -- ********************************************************* -- Display extended statistics distinct values and histograms -- ********************************************************* column col_group format a30 select e.extension col_group, t.num_distinct, t.histogram from dba_stat_extensions e join dba_tab_col_statistics t on e.extension_name=t.column_name and t.table_name = 'EMP'; COL_GROUP NUM_DISTINCT HISTOGRAM ------------------------------ ------------ --------------- ("JOB","DEPTNO") 9 NONE ("JOB","MGR") 8 NONE (LOWER("ENAME")) 14 NONE (UPPER("ENAME")) 14 NONE -- ********************************************************* -- Display extended statistics distinct values and histograms -- ********************************************************* column col_group format a30 select e.extension col_group, t.num_distinct, t.histogram from dba_stat_extensions e join dba_tab_col_statistics t on e.extension_name=t.column_name and t.table_name = 'EMP'; COL_GROUP NUM_DISTINCT HISTOGRAM ------------------------------ ------------ --------------- ("JOB","DEPTNO") 9 NONE ("JOB","MGR") 8 NONE (LOWER("ENAME")) 14 NONE (UPPER("ENAME")) 14 NONE select sum(a.time_waited_micro)/sum(a.total_waits)/1000000 c1, sum(b.time_waited_micro)/sum(b.total_waits)/1000000 c2, ( sum(a.total_waits) / sum(a.total_waits + b.total_waits) ) * 100 c3, ( sum(b.total_waits) / sum(a.total_waits + b.total_waits) ) * 100 c4, ( sum(b.time_waited_micro) / sum(b.total_waits)) / (sum(a.time_waited_micro)/sum(a.total_waits) ) * 100 c5 from dba_hist_system_event a, dba_hist_system_event b where a.snap_id = b.snap_id and a.event_name = 'db file scattered read' and b.event_name = 'db file sequential read'; Chapter 10 < top_20_sessions.sql -- ************************************************* -- Copyright © 2005 by Rampant TechPress -- This script is free for non-commercial purposes -- with no warranties. Use at your own risk. -- -- To license this script for a commercial purpose, -- contact info@rampant.cc -- ************************************************* select * from (select b.sid sid, decode (b.username,null,e.name,b.username) user_name, d.spid os_id, b.machine machine_name, to_char(logon_time,'dd-mon-yy hh:mi:ss pm') logon_time, (sum(decode(c.name,'physical reads',value,0)) + sum(decode(c.name,'physical writes',value,0)) + sum(decode(c.name,'physical writes direct',value,0)) + sum(decode(c.name,'physical writes direct (lob)',value,0))+ sum(decode(c.name,'physical reads direct (lob)',value,0)) + sum(decode(c.name,'physical reads direct',value,0))) total_physical_io, (sum(decode(c.name,'db block gets',value,0)) + sum(decode(c.name,'db block changes',value,0)) + sum(decode(c.name,'consistent changes',value,0)) + sum(decode(c.name,'consistent gets',value,0)) ) total_logical_io, (sum(decode(c.name,'session pga memory',value,0))+ sum(decode(c.name,'session uga memory',value,0)) ) total_memory_usage, sum(decode(c.name,'parse count (total)',value,0)) parses, sum(decode(c.name,'cpu used by this session',value,0)) total_cpu, sum(decode(c.name,'parse time cpu',value,0)) parse_cpu, sum(decode(c.name,'recursive cpu usage',value,0)) recursive_cpu, sum(decode(c.name,'cpu used by this session',value,0)) - sum(decode(c.name,'parse time cpu',value,0)) - sum(decode(c.name,'recursive cpu usage',value,0)) other_cpu, sum(decode(c.name,'sorts (disk)',value,0)) disk_sorts, sum(decode(c.name,'sorts (memory)',value,0)) memory_sorts, sum(decode(c.name,'sorts (rows)',value,0)) rows_sorted, sum(decode(c.name,'user commits',value,0)) commits, sum(decode(c.name,'user rollbacks',value,0)) rollbacks, sum(decode(c.name,'execute count',value,0)) executions from sys.v_$sesstat a, sys.v_$session b, sys.v_$statname c, sys.v_$process d, sys.v_$bgprocess e where a.statistic#=c.statistic# and b.sid=a.sid and d.addr = b.paddr and e.paddr (+) = b.paddr and c.NAME in ('physical reads', 'physical writes', 'physical writes direct', 'physical reads direct', 'physical writes direct (lob)', 'physical reads direct (lob)', 'db block gets', 'db block changes', 'consistent changes', 'consistent gets', 'session pga memory', 'session uga memory', 'parse count (total)', 'CPU used by this session', 'parse time cpu', 'recursive cpu usage', 'sorts (disk)', 'sorts (memory)', 'sorts (rows)', 'user commits', 'user rollbacks', 'execute count' ) group by b.sid, d.spid, decode (b.username,null,e.name,b.username), b.machine, to_char(logon_time,'dd-mon-yy hh:mi:ss pm') order by 6 desc) where rownum < 21 < high_scan_sql.sql select c.username username, count(a.hash_value) scan_count from sys.v_$sql_plan a, sys.dba_segments b, sys.dba_users c, sys.v_$sql d where a.object_owner (+) = b.owner and a.object_name (+) = b.segment_name and b.segment_type IN ('TABLE', 'TABLE PARTITION') and a.operation like '%TABLE%' and a.options = 'FULL' and c.user_id = d.parsing_user_id and d.hash_value = a.hash_value and b.bytes / 1024 > 1024 group by c.username order by 2 desc ; < find_cartesian_joins.sql select username, count(distinct c.hash_value) nbr_stmts from sys.v_$sql a, sys.dba_users b, sys.v_$sql_plan c where a.parsing_user_id = b.user_id and options = 'CARTESIAN' and operation like '%JOIN%' and a.hash_value = c.hash_value group by username order by 2 desc ; < high_resource_sql.sql -- ************************************************* -- Copyright © 2005 by Rampant TechPress -- This script is free for non-commercial purposes -- with no warranties. Use at your own risk. -- -- To license this script for a commercial purpose, -- contact info@rampant.cc -- ************************************************* select sql_text, username, disk_reads_per_exec, buffer_gets, disk_reads, parse_calls, sorts, executions, rows_processed, hit_ratio, first_load_time, sharable_mem, persistent_mem, runtime_mem, cpu_time, elapsed_time, address, hash_value from (select sql_text , b.username , round((a.disk_reads/decode(a.executions,0,1, a.executions)),2) disk_reads_per_exec, a.disk_reads , a.buffer_gets , a.parse_calls , a.sorts , a.executions , a.rows_processed , 100 - round(100 * a.disk_reads/greatest(a.buffer_gets,1),2) hit_ratio, a.first_load_time , sharable_mem , persistent_mem , runtime_mem, cpu_time, elapsed_time, address, hash_value from sys.v_$sqlarea a, sys.all_users b where a.parsing_user_id=b.user_id and b.username not in ('sys','system') order by 3 desc) where rownum < 21 < cartesian_sum.sql select count(distinct hash_value) carteisan_statements, count(*) total_cartesian_joins from sys.v_$sql_plan where options = 'CARTESIAN' and operation like '%JOIN%' < sql_cartesian.sql select * from sys.v_$sql where hash_value in (select hash_value from sys.v_$sql_plan where options = 'CARTESIAN' and operation LIKE '%JOIN%' ) order by hash_value; < large_scan_count.sql select sql_text, total_large_scans, executions, executions * total_large_scans sum_large_scans from (select sql_text, count(*) total_large_scans, executions from sys.v_$sql_plan a, sys.dba_segments b, sys.v_$sql c where a.object_owner (+) = b.owner and a.object_name (+) = b.segment_name and b.segment_type IN ('TABLE', 'TABLE PARTITION') and a.operation LIKE '%TABLE%' and a.options = 'FULL' and c.hash_value = a.hash_value and b.bytes / 1024 > 1024 group by sql_text, executions) order by 4 desc; < awr_sqlstat_deltas.sql col c1 heading ‘Begin|Interval|time’ format a8 col c2 heading ‘SQL|ID’ format a13 col c3 heading ‘Exec|Delta’ format 9,999 col c4 heading ‘Buffer|Gets|Delta’ format 9,999 col c5 heading ‘Disk|Reads|Delta’ format 9,999 col c6 heading ‘IO Wait|Delta’ format 9,999 col c7 heading ‘Application|Wait|Delta’ format 9,999 col c8 heading ‘Concurrency|Wait|Delta’ format 9,999 break on c1 select to_char(s.begin_interval_time,’mm-dd hh24’) c1, sql.sql_id c2, sql.executions_delta c3, sql.buffer_gets_delta c4, sql.disk_reads_delta c5, sql.iowait_delta c6, sql.apwait_delta c7, sql.ccwait_delta c8 from dba_hist_sqlstat sql, dba_hist_snapshot s where s.snap_id = sql.snap_id order by c1, c2; < awr_sqlstat_deltas_detail.sql col c1 heading ‘Begin|Interval|time’ format a8 col c2 heading ‘Exec|Delta’ format 999,999 col c3 heading ‘Buffer|Gets|Delta’ format 999,999 col c4 heading ‘Disk|Reads|Delta’ format 9,999 col c5 heading ‘IO Wait|Delta’ format 9,999 col c6 heading ‘App|Wait|Delta’ format 9,999 col c7 heading ‘Cncr|Wait|Delta’ format 9,999 col c8 heading ‘CPU|Time|Delta’ format 999,999 col c9 heading ‘Elpsd|Time|Delta’ format 999,999 accept sqlid prompt ‘Enter SQL ID: ‘ ttitle ‘time series execution for|&sqlid’ break on c1 select to_char(s.begin_interval_time,’mm-dd hh24’) c1, sql.executions_delta c2, sql.buffer_gets_delta c3, sql.disk_reads_delta c4, sql.iowait_delta c5, sql.apwait_delta c6, sql.ccwait_delta c7, sql.cpu_time_delta c8, sql.elapsed_time_delta c9 from dba_hist_sqlstat sql, dba_hist_snapshot s where s.snap_id = sql.snap_id and sql_id = ‘&sqlid’ order by c1; < sqlstathist.sql set echo off feed off lines 100 pages 9999 clear col clear break col beginttm head 'Begin|Interval' format a14 col sqlid head 'SQL|ID' format a13 col execsdlt head 'Delta|Execs' format 99990 col bufgetwaitdlt head 'Delta|Buffer|Gets' format 9999990 col dskrdwaitdlt head 'Delta|Disk|Reads' format 999990 col iowaitdlt head 'Delta|IO Wait' format 9999990 col appwaitdlt head 'Delta|Wait|App' format 9999990 col concurwaitdlt head 'Delta|Wait|Concur' format 99990 break on beginttm skip 1 spool sqlstathist.lis set echo off feed off lines 100 pages 9999 clear col clear break col beginttm head 'Begin|Interval' format a14 col sqlid head 'SQL|ID' format a13 col execsdlt head 'Delta|Execs' format 99990 col bufgetwaitdlt head 'Delta|Buffer|Gets' format 9999990 col dskrdwaitdlt head 'Delta|Disk|Reads' format 999990 col iowaitdlt head 'Delta|IO Wait' format 9999990 col appwaitdlt head 'Delta|Wait|App' format 9999990 col concurwaitdlt head 'Delta|Wait|Concur' format 99990 break on beginttm skip 1 spool sqlstathist.lis select to_char(begin_interval_time,'mm-dd hh24:mi:ss') beginttm, sql_id sqlid, executions_delta execsdlt, buffer_gets_delta bufgetwaitdlt, disk_reads_delta dskrdwaitdlt, iowait_delta iowaitdlt, apwait_delta appwaitdlt, ccwait_delta concurwaitdlt from dba_hist_snapshot sn, dba_hist_sqlstat ss where ss.snap_id = sn.snap_id and begin_interval_time > (sysdate - 4/24) order by beginttm, ( executions_delta + buffer_gets_delta + disk_reads_delta + iowait_delta + apwait_delta + ccwait_delta ) desc / spool off clear break clear col < awr_high_cost_sql.sql col c1 heading ‘SQL|ID’ format a13 col c2 heading ‘Cost’ format 9,999,999 col c3 heading ‘SQL Text’ format a200 select p.sql_id c1, p.cost c2, to_char(s.sql_text) c3 from dba_hist_sql_plan p, dba_hist_sqltext s where p.id = 0 and p.sql_id = s.sql_id and p.cost is not null order by p.cost desc ; < awr_sql_object_char.sql col c1 heading ‘Owner’ format a13 col c2 heading ‘Object|Type’ format a15 col c3 heading ‘Object|Name’ format a25 col c4 heading ‘Average|CPU|Cost’ format 9,999,999 col c5 heading ‘Average|IO|Cost’ format 9,999,999 break on c1 skip 2 break on c2 skip 2 select p.object_owner c1, p.object_type c2, p.object_name c3, avg(p.cpu_cost ) c4, avg(p.io_cost ) c5 from dba_hist_sql_plan p where p.object_name is not null and p.object_owner <> 'SYS' group by p.object_owner, p.object_type, p.object_name order by 1,2,4 desc; < awr_sql_object_char_detail.sql accept tabname prompt ‘Enter Table Name:’ col c0 heading ‘Begin|Interval|time’ format a8 col c1 heading ‘Owner’ format a10 col c2 heading ‘Object|Type’ format a10 col c3 heading ‘Object|Name’ format a15 col c4 heading ‘Average|CPU|Cost’ format 9,999,999 col c5 heading ‘Average|IO|Cost’ format 9,999,999 break on c1 skip 2 break on c2 skip 2 select to_char(sn.begin_interval_time,'mm-dd hh24') c0, p.object_owner c1, p.object_type c2, p.object_name c3, avg(p.cpu_cost ) c4, avg(p.io_cost ) c5 from dba_hist_sql_plan p, dba_hist_sqlstat st, dba_hist_snapshot sn where p.object_name is not null and p.object_owner <> 'SYS' and p.object_name = 'CUSTOMER_DETS' and p.sql_id = st.sql_id and st.snap_id = sn.snap_id group by to_char(sn.begin_interval_time,'mm-dd hh24'), p.object_owner, p.object_type, p.object_name order by 1,2,3 desc; < awr_nested_join_alert.sql col c1 heading ‘Date’ format a20 col c2 heading ‘Nested|Loops|Count’ format 99,999,999 col c3 heading ‘Rows|Processed’ format 99,999,999 col c4 heading ‘Disk|Reads’ format 99,999,999 col c5 heading ‘CPU|Time’ format 99,999,999 accept nested_thr char prompt ‘Enter Nested Join Threshold: ‘ ttitle ‘Nested Join Threshold|&nested_thr’ select to_char(sn.begin_interval_time,'yy-mm-dd hh24') c1, count(*) c2, sum(st.rows_processed_delta) c3, sum(st.disk_reads_delta) c4, sum(st.cpu_time_delta) c5 from dba_hist_snapshot sn, dba_hist_sql_plan p, dba_hist_sqlstat st where st.sql_id = p.sql_id and sn.snap_id = st.snap_id and p.operation = ‘NESTED LOOPS’ having count(*) > &hash_thr group by begin_interval_time; < awr_sql_index.sql col c0 heading ‘Begin|Interval|time’ format a8 col c1 heading ‘Index|Name’ format a20 col c2 heading ‘Disk|Reads’ format 99,999,999 col c3 heading ‘Rows|Processed’ format 99,999,999 select to_char(s.begin_interval_time,'mm-dd hh24') c0, p.object_name c1, sum(t.disk_reads_total) c2, sum(t.rows_processed_total) c3 from dba_hist_sql_plan p, dba_hist_sqlstat t, dba_hist_snapshot s where p.sql_id = t.sql_id and t.snap_id = s.snap_id and p.object_type like '%INDEX%' group by to_char(s.begin_interval_time,'mm-dd hh24'), p.object_name order by c0,c1,c2 desc; < awr_sql_index_access.sql col c1 heading ‘Begin|Interval|Time’ format a20 col c2 heading ‘Index|Range|Scans’ format 999,999 col c3 heading ‘Index|Unique|Scans’ format 999,999 col c4 heading ‘Index|Full|Scans’ format 999,999 select r.c1 c1, r.c2 c2, u.c2 c3, f.c2 c4 from ( select to_char(sn.begin_interval_time,'yy-mm-dd hh24') c1, count(1) c2 from dba_hist_sql_plan p, dba_hist_sqlstat s, dba_hist_snapshot sn where p.object_owner <> 'SYS' and p.operation like '%INDEX%' and p.options like '%RANGE%' and p.sql_id = s.sql_id and s.snap_id = sn.snap_id group by to_char(sn.begin_interval_time,'yy-mm-dd hh24') order by 1 ) r, ( select to_char(sn.begin_interval_time,'yy-mm-dd hh24') c1, count(1) c2 from dba_hist_sql_plan p, dba_hist_sqlstat s, dba_hist_snapshot sn where p.object_owner <> 'SYS' and p.operation like '%INDEX%' and p.options like '%UNIQUE%' and p.sql_id = s.sql_id and s.snap_id = sn.snap_id group by to_char(sn.begin_interval_time,'yy-mm-dd hh24') order by 1 ) u, ( select to_char(sn.begin_interval_time,'yy-mm-dd hh24') c1, count(1) c2 from dba_hist_sql_plan p, dba_hist_sqlstat s, dba_hist_snapshot sn where p.object_owner <> 'SYS' and p.operation like '%INDEX%' and p.options like '%FULL%' and p.sql_id = s.sql_id and s.snap_id = sn.snap_id group by to_char(sn.begin_interval_time,'yy-mm-dd hh24') order by 1 ) f where r.c1 = u.c1 and r.c1 = f.c1; < awr_sql_object_avg_dy.sql col c1 heading ‘Object|Name’ format a30 col c2 heading ‘Week Day’ format a15 col c3 heading ‘Invocation|Count’ format 99,999,999 break on c1 skip 2 break on c2 skip 2 select decode(c2,1,'Monday',2,'Tuesday',3,'Wednesday',4,'Thursday',5,'Friday',6,'Saturday',7,'Sunday') c2, c1, c3 from ( select p.object_name c1, to_char(sn.end_interval_time,'d') c2, count(1) c3 from dba_hist_sql_plan p, dba_hist_sqlstat s, dba_hist_snapshot sn where p.object_owner <> 'SYS' and p.sql_id = s.sql_id and s.snap_id = sn.snap_id group by p.object_name, to_char(sn.end_interval_time,'d') order by c2,c1 ) ; < awr_sql_details.sql accept sqlid prompt ‘Please enter SQL ID: ‘ col c1 heading ‘Operation’ format a20 col c2 heading ‘Options’ format a20 col c3 heading ‘Object|Name’ format a25 col c4 heading ‘Search Columns’ format 999,999 col c5 heading ‘Cardinality’ format 999,999 select operation c1, options c2, object_name c3, search_columns c4, cardinality c5 from dba_hist_sql_plan p where p.sql_id = '&sqlid' order by p.id; < awr_full_table_scans.sql ttitle ‘Large Full-table scans|Per Snapshot Period’ col c1 heading ‘Begin|Interval|time’ format a20 col c4 heading ‘FTS|Count’ format 999,999 break on c1 skip 2 break on c2 skip 2 select to_char(sn.begin_interval_time,'yy-mm-dd hh24') c1, count(1) c4 from dba_hist_sql_plan p, dba_hist_sqlstat s, dba_hist_snapshot sn, dba_segments o where p.object_owner <> 'SYS' and p.object_owner = o.owner and p.object_name = o.segment_name and o.blocks > 1000 and p.operation like '%TABLE ACCESS%' and p.options like '%FULL%' and p.sql_id = s.sql_id and s.snap_id = sn.snap_id group by to_char(sn.begin_interval_time,'yy-mm-dd hh24') order by 1; < awr_sql_access_hr.sql ttitle ‘Large Table Full-table scans|Averages per Hour’ col c1 heading ‘Day|Hour’ format a20 col c2 heading ‘FTS|Count’ format 999,999 break on c1 skip 2 break on c2 skip 2 select to_char(sn.begin_interval_time,'hh24') c1, count(1) c2 from dba_hist_sql_plan p, dba_hist_sqlstat s, dba_hist_snapshot sn, dba_segments o where p.object_owner <> 'SYS' and p.object_owner = o.owner and p.object_name = o.segment_name and o.blocks > 1000 and p.operation like '%TABLE ACCESS%' and p.options like '%FULL%' and p.sql_id = s.sql_id and s.snap_id = sn.snap_id group by to_char(sn.begin_interval_time,'hh24') order by 1; < awr_sql_access_day.sql ttitle ‘Large Table Full-table scans|Averages per Week Day’ col c1 heading ‘Week|Day’ format a20 col c2 heading ‘FTS|Count’ format 999,999 break on c1 skip 2 break on c2 skip 2 select to_char(sn.begin_interval_time,'day') c1, count(1) c2 from dba_hist_sql_plan p, dba_hist_sqlstat s, dba_hist_snapshot sn, dba_segments o where p.object_owner <> 'SYS' and p.object_owner = o.owner and p.object_name = o.segment_name and o.blocks > 1000 and p.operation like '%TABLE ACCESS%' and p.options like '%FULL%' and p.sql_id = s.sql_id and s.snap_id = sn.snap_id group by to_char(sn.begin_interval_time,'day') order by 1; < awr_sql_scan_sums.sql col c1 heading ‘Begin|Interval|Time’ format a20 col c2 heading ‘Large|Table|Full Table|Scans’ format 999,999 col c3 heading ‘Small|Table|Full Table|Scans’ format 999,999 col c4 heading ‘Total|Index|Scans’ format 999,999 select f.c1 c1, f.c2 c2, s.c2 c3, i.c2 c4 from ( select to_char(sn.begin_interval_time,'yy-mm-dd hh24') c1, count(1) c2 from dba_hist_sql_plan p, dba_hist_sqlstat s, dba_hist_snapshot sn, dba_segments o where p.object_owner <> 'SYS' and p.object_owner = o.owner and p.object_name = o.segment_name and o.blocks > 1000 and p.operation like '%TABLE ACCESS%' and p.options like '%FULL%' and p.sql_id = s.sql_id and s.snap_id = sn.snap_id group by to_char(sn.begin_interval_time,'yy-mm-dd hh24') order by 1 ) f, ( select to_char(sn.begin_interval_time,'yy-mm-dd hh24') c1, count(1) c2 from dba_hist_sql_plan p, dba_hist_sqlstat s, dba_hist_snapshot sn, dba_segments o where p.object_owner <> 'SYS' and p.object_owner = o.owner and p.object_name = o.segment_name and o.blocks < 1000 and p.operation like '%INDEX%' and p.sql_id = s.sql_id and s.snap_id = sn.snap_id group by to_char(sn.begin_interval_time,'yy-mm-dd hh24') order by 1 ) s, ( select to_char(sn.begin_interval_time,'yy-mm-dd hh24') c1, count(1) c2 from dba_hist_sql_plan p, dba_hist_sqlstat s, dba_hist_snapshot sn where p.object_owner <> 'SYS' and p.operation like '%INDEX%' and p.sql_id = s.sql_id and s.snap_id = sn.snap_id group by to_char(sn.begin_interval_time,'yy-mm-dd hh24') order by 1 ) i where f.c1 = s.c1 and f.c1 = i.c1 ; < awr_sql_full_scans_avg_dy.sql col c1 heading ‘Begin|Interval|Time’ format a20 col c2 heading ‘Index|Table|Scans’ format 999,999 col c3 heading ‘Full|Table|Scans’ format 999,999 select i.c1 c1, i.c2 c2, f.c2 c3 from ( select to_char(sn.begin_interval_time,'day') c1, count(1) c2 from dba_hist_sql_plan p, dba_hist_sqlstat s, dba_hist_snapshot sn where p.object_owner <> 'SYS' and p.operation like '%TABLE ACCESS%' and p.options like '%INDEX%' and p.sql_id = s.sql_id and s.snap_id = sn.snap_id group by to_char(sn.begin_interval_time,'day') order by 1 ) i, ( select to_char(sn.begin_interval_time,'day') c1, count(1) c2 from dba_hist_sql_plan p, dba_hist_sqlstat s, dba_hist_snapshot sn where p.object_owner <> 'SYS' and p.operation like '%TABLE ACCESS%' and p.options = 'FULL' and p.sql_id = s.sql_id and s.snap_id = sn.snap_id group by to_char(sn.begin_interval_time,'day') order by 1 ) f where i.c1 = f.c1; < awr_hash_join_alert.sql col c1 heading ‘Date’ format a20 col c2 heading ‘Hash|Join|Count’ format 99,999,999 col c3 heading ‘Rows|Processed’ format 99,999,999 col c4 heading ‘Disk|Reads’ format 99,999,999 col c5 heading ‘CPU|Time’ format 99,999,999 accept hash_thr char prompt ‘Enter Hash Join Threshold: ‘ ttitle ‘Hash Join Threshold|&hash_thr’ select to_char(sn.begin_interval_time,'yy-mm-dd hh24') c1, count(*) c2, sum(st.rows_processed_delta) c3, sum(st.disk_reads_delta) c4, sum(st.cpu_time_delta) c5 from dba_hist_snapshot sn, dba_hist_sql_plan p, dba_hist_sqlstat st where st.sql_id = p.sql_id and sn.snap_id = st.snap_id and p.operation = 'HASH JOIN' having count(*) > &hash_thr group by begin_interval_time; < monitor_hash_join_ful_ram.sql select tempseg_size from v$sql_workarea_active; Chapter 11 < display_session_process_details.sql select a.sid, a.serial#, b.spid, b.pid, a.username, a.osuser, a.machine from v$session a, v$process b where a.username IS NOT NULL and a.paddr=b.addr; < create_10046_wait_table.sql create directory load_directory as 'c:\oracle\product\admin\BOOKTST\udump'; drop table ext_10046_table_wait_events; create table ext_10046_table_wait_events (event_type varchar2(10), cursor_number number, wait_event_name varchar2(60), total_elapsed_time number, p1 varchar2(100), p2 varchar2(100), p3 varchar2(100), trace_row_num number ) ORGANIZATION EXTERNAL (TYPE oracle_loader DEFAULT DIRECTORY load_directory access parameters ( RECORDS DELIMITED BY NEWLINE badfile load_directory:'bad_10046.log' logfile load_directory:'load_10046.log' skip 24 LOAD WHEN event_type="WAIT" FIELDS RTRIM ( event_type CHAR terminated by '#', cursor_number CHAR terminated by ': nam=', wait_event_name CHAR terminated by 'ela=', total_elapsed_time CHAR terminated by 'p1=', p1 CHAR terminated by 'p2=', p2 CHAR terminated by 'p3=', p3 CHAR terminated by WHITESPACE, trace_row_num recnum ) ) location ('booktst_ora_3640.trc') ) reject limit unlimited; < create_10046_wait_format.sql drop table ext_10046_table_pef_events; create table ext_10046_table_pef_events (event_type varchar2(10), cursor_number number, pef_cpu_time number, pef_elap number, pef_blocks number, pef_blocks_cm number, pef_blocks_curmode number, pef_lib_cache_misses number, pef_rows_returned number, pef_depth number, pef_goal number, pef_tim number, trace_row_num number ) ORGANIZATION EXTERNAL (TYPE oracle_loader DEFAULT DIRECTORY load_directory access parameters ( RECORDS DELIMITED BY NEWLINE badfile load_directory:'bad_10046.log' logfile load_directory:'load_10046.log' skip 24 LOAD WHEN ( event_type="EXEC" or event_type="FETCH" or event_type="PARSE" ) FIELDS RTRIM ( event_type CHAR terminated by '#', cursor_number CHAR terminated by ':c=', pef_cpu_time CHAR terminated by ',e=', pef_elap CHAR terminated by ',p=', pef_blocks CHAR terminated by ',cr=', pef_blocks_cm CHAR terminated by ',cu=', pef_blocks_curmode CHAR terminated by ',mis=', pef_lib_cache_misses CHAR terminated by ',r=', pef_rows_returned CHAR terminated by ',dep=', pef_depth CHAR terminated by ',og=', pef_goal CHAR terminated by ',tim=', pef_tim CHAR terminated by WHITESPACE, trace_row_num recnum ) ) location ('booktst_ora_3640.trc') ) reject limit unlimited; < view_10046_trace.sql create or replace view vw_10046_view as select trace_row_num, event_type, cursor_number, wait_event_name, to_char(total_elapsed_time) wait_time, -1 command from ext_10046_table_wait_events union select trace_row_num, event_type, cursor_number, null, to_char(pef_tim), -1 from ext_10046_table_pef_events union select distinct b.trace_row_num, null, null, null, sql_text, to_number(cur_oct) command from v$sql a, ext_10046_table_cursor_events b where upper(b.cur_ad)=a.address (+) order by 1; create table tab_10046 as select * from vw_10046_view; Create index ix_tab_10046_01 on tab_10046(trace_row_num); Create index ix_tab_10046_02 on tab_10046(command); Create index ix_tab_10046_03 on tab_10046(event_type); Create index ix_tab_10046_04 on tab_10046 (event_type, trace_row_num, cursor_number, wait_time); < trace_exec_parse_fetch_sql.sql column time_between_events heading “Time|Between|Events” column wait_time format 9999999999 select event_type, cursor_number, to_number(wait_time) wait_time, (wait_time/1000000)-lag(wait_time/1000000, 1) over (order by trace_row_num) "time_between_events" from tab_10046 where event_type in ('EXEC','FETCH','PARSE') order by trace_row_num; < filter_trace_file.sql select trace_row_num, event_type, cursor_number, to_number(wait_time) wait_time, event_time from (select trace_row_num, event_type, cursor_number, wait_time, (wait_time/1000000)-lag(wait_time/1000000, 1) over (order by trace_row_num) "EVENT_TIME" from tab_10046 where event_type in ('EXEC','FETCH','PARSE') order by trace_row_num ) where trace_row_num in ( select trace_row_num from (select trace_row_num, (wait_time/1000000)- lag(wait_time/1000000, 1) over (order by trace_row_num) "EVENT_TIME" from tab_10046 where event_type in ('EXEC','FETCH','PARSE') ) where event_time > .25 ); < filter_trace_file_range.sql select ’E’ operation, trace_row_num, event_type, cursor_number, wait_time, event_time from select trace_row_num, event_type, cursor_number, to_number(wait_time) wait_time, (wait_time/1000000)-lag(wait_time/1000000, 1) over (order by trace_row_num) "EVENT_TIME" from tab_10046 where event_type in ('EXEC','FETCH','PARSE') order by trace_row_num ) where trace_row_num in ( select trace_row_num from (select trace_row_num, (wait_time/1000000)- lag(wait_time/1000000, 1) over (order by trace_row_num) "EVENT_TIME" from tab_10046 where event_type in ('EXEC','FETCH','PARSE') ) where event_time > .25 ) UNION select ’B’ Operation, trace_row_num, event_type, cursor_number, to_number(wait_time) wait_time, event_time from (select trace_row_num, event_type, cursor_number, wait_time, (wait_time/1000000)-lag(wait_time/1000000, 1) over (order by trace_row_num) "EVENT_TIME“ from tab_10046 where event_type in ('EXEC','FETCH','PARSE') order by trace_row_num ) where rowid in (select q_rowid from (select trace_row_num, (wait_time/1000000)- lag(wait_time/1000000, 1) over (order by trace_row_num) "EVENT_TIME“, lag(rowid) over (order by trace_row_num) q_rowid from tab_10046 where event_type in ('EXEC','FETCH','PARSE') ) where event_time > .25 ) order by trace_row_num; < trace_detail.sql select trace_row_num, event_type, wait_event_name, cursor_number, wait_time/1000000 wait_time from tab_10046 where trace_row_num between 4851699 and 4851727 order by trace_row_num; create table test ( pkey number(15) not null, pdate date not null, pchar varchar2(4000) not null ); create sequence test_seq; create procedure pop_test(p_pass in number) as begin for i in 1 .. p_pass loop insert into test select test_seq.nextval, sysdate, object_name||'.'||object_type||'.'||owner from sys.dba_objects; commit; end loop; end; / exec pop_test(500); Chapter 12 < statspack_osstats_rollup.sql select * from (select snap_id, osstat_id, value from stats$osstat where snap_id in (10046, 10047)) – filter for desired snapshots pivot sum(value) for osstat; < display_os_stats.sql select sum(a.time_waited_micro)/sum(a.total_waits)/1000000 c1, sum(b.time_waited_micro)/sum(b.total_waits)/1000000 c2, ( sum(a.total_waits) / sum(a.total_waits + b.total_waits) ) * 100 c3, ( sum(b.total_waits) / sum(a.total_waits + b.total_waits) ) * 100 c4, ( sum(b.time_waited_micro) / sum(b.total_waits)) / (sum(a.time_waited_micro)/sum(a.total_waits) ) * 100 c5 from dba_hist_system_event a, dba_hist_system_event b where a.snap_id = b.snap_id and a.event_name = 'db file scattered read' and b.event_name = 'db file sequential read'; < awr_osstats.sql select e.stat_name "Statistic Name" , decode(e.stat_name, 'NUM_CPUS', e.value, e.value - b.value) "Total" , decode( instrb(e.stat_name, 'BYTES'), 0, to_number(null) , round((e.value - b.value)/( select avg( extract( day from (e1.end_interval_time-b1.end_interval_time) )*24*60*60+ extract( hour from (e1.end_interval_time-b1.end_interval_time) )*60*60+ extract( minute from (e1.end_interval_time-b1.end_interval_time) )*60+ extract( second from (e1.end_interval_time-b1.end_interval_time)) ) from dba_hist_snapshot b1 ,dba_hist_snapshot e1 where b1.snap_id = b.snap_id and e1.snap_id = e.snap_id and b1.dbid = b.dbid and e1.dbid = e.dbid and b1.instance_number = b.instance_number and e1.instance_number = e.instance_number and b1.startup_time = e1.startup_time and b1.end_interval_time < e1.end_interval_time ),2)) "Per Second" from dba_hist_osstat b, dba_hist_osstat e where b.snap_id = &pBgnSnap and e.snap_id = &pEndSnap and b.dbid = &pDbId order by 1 asc; < awr_os_stats_load_cpu_ram.sql select s1t0.snap_id, to_char(s0.BEGIN_INTERVAL_TIME,'YYYY-Mon-DD HH24:MI:SS') time, s1t1.value - s1t0.value as busy_time, s2t1.value as load, s3t1.value as num_cpus, s4t1.value as physical_memory_bytes from dba_hist_snapshot s0, dba_hist_snapshot s1, dba_hist_osstat s1t0, dba_hist_osstat s1t1, dba_hist_osstat s2t1, dba_hist_osstat s3t1, dba_hist_osstat s4t1 where s0.dbid = (select dbid from v$database) and s1t0.dbid = s0.dbid and s1t1.dbid = s0.dbid and s2t1.dbid = s0.dbid and s3t1.dbid = s0.dbid and s4t1.dbid = s0.dbid and s0.instance_number = 1 and s1t0.instance_number = s0.instance_number and s1t1.instance_number = s0.instance_number and s2t1.instance_number = s0.instance_number and s3t1.instance_number = s0.instance_number and s4t1.instance_number = s0.instance_number and s1.snap_id = s0.snap_id + 1 and s1t0.snap_id = s0.snap_id and s1t1.snap_id = s0.snap_id + 1 and s2t1.snap_id = s0.snap_id and s3t1.snap_id = s0.snap_id and s4t1.snap_id = s0.snap_id and s1t0.stat_name = 'BUSY_TIME' and s1t1.stat_name = s1t0.stat_name and s2t1.stat_name = 'LOAD' and s3t1.stat_name = 'NUM_CPUS' and s4t1.stat_name = 'PHYSICAL_MEMORY_BYTES' order by snap_id asc; < awr_stats_deltas.sql select to_char(s.end_interval_time,'DD-MON-YYYY HH24:MI') SNAP_TIME, os.stat_name, os.value, lag(os.value,1) over (PARTITION BY os.STAT_NAME order by os.snap_id) prev, case when os.stat_name = 'LOAD' then os.value when os.stat_name = 'PHYSICAL_MEMORY_BYTES' then os.value else os.value - lag(os.value,1) over (PARTITION BY os.STAT_NAME order by os.snap_id) end value from dba_hist_snapshot s, dba_hist_osstat os where s.snap_id = os.snap_id order by os.snap_id,os.stat_name; < cr_vmstat_tab.sql connect perfstat/perfstat; drop table stats$vmstat; create table stats$vmstat ( start_date date, duration number, server_name varchar2(20), runque_waits number, page_in number, page_out number, user_cpu number, system_cpu number, idle_cpu number, wait_cpu number ) tablespace perfstat; < get_vmstat.ksh (Linux version) #!/bin/ksh # This is the Linux version #!/bin/ksh # This is the Linux version ORACLE_HOME=/usr/app/oracle/admin/product/8/1/6 export ORACLE_HOME PATH=$ORACLE_HOME/bin:$PATH export PATH SERVER_NAME=`uname -a|awk '{print $2}'` typeset -u SERVER_NAME export SERVER_NAME # sample every five minutes (300 seconds) . . . . SAMPLE_TIME=300 while true do vmstat ${SAMPLE_TIME} 2 > /tmp/msg$$ # run vmstat and direct the output into the Oracle table . . . cat /tmp/msg$$|sed 1,3d | awk '{ printf("%s %s %s %s %s %s\n", $1, $8, $9, 14, $15, $16) }' | while read RUNQUE PAGE_IN PAGE_OUT USER_CPU SYSTEM_CPU DLE_CPU do $ORACLE_HOME/bin/sqlplus -s perfstat/perfstat@testsys1</dev/null 2>&1 & fi < vmstat_exception_rpt.sql set lines 80; set pages 999; set feedback off; set verify off; column my_date heading 'date hour' format a20 column c2 heading runq format 999 column c3 heading pg_in format 999 column c4 heading pg_ot format 999 column c5 heading usr format 999 column c6 heading sys format 999 column c7 heading idl format 999 column c8 heading wt format 999 ttitle 'run queue > 2|May indicate an overloaded CPU|When runqueue exceeds the number of CPUs| on the server, tasks are waiting for service.'; select server_name, to_char(start_date,'YY/MM/DD HH24') my_date, avg(runque_waits) c2, avg(page_in) c3, avg(page_out) c4, avg(user_cpu) c5, avg(system_cpu) c6, avg(idle_cpu) c7 from perfstat.stats$vmstat WHERE runque_waits > 2 and start_date > sysdate-&&1 group by server_name, to_char(start_date,'YY/MM/DD HH24') ORDER BY server_name, to_char(start_date,'YY/MM/DD HH24') ; ttitle 'page_in > 1|May indicate overloaded memory|Whenever Unix performs a page-in, the RAM memory | on the server has been exhausted and swap pages are being used.'; select server_name, to_char(start_date,'YY/MM/DD HH24') my_date, avg(runque_waits) c2, avg(page_in) c3, avg(page_out) c4, avg(user_cpu) c5, avg(system_cpu) c6, avg(idle_cpu) c7 from perfstat.stats$vmstat WHERE page_in > 1 and start_date > sysdate-&&1 group by server_name, to_char(start_date,'YY/MM/DD HH24') ORDER BY server_name, to_char(start_date,'YY/MM/DD HH24') ; ttitle 'user+system CPU > 70%|Indicates periods with a fully-loaded CPU subssystem.|Periods of 100% utilization are only a | concern when runqueue values exceeds the number of CPs on the server.'; select server_name, to_char(start_date,'YY/MM/DD HH24') my_date, avg(runque_waits) c2, avg(page_in) c3, avg(page_out) c4, avg(user_cpu) c5, avg(system_cpu) c6, avg(idle_cpu) c7 from perfstat.stats$vmstat WHERE (user_cpu + system_cpu) > 70 and start_date > sysdate-&&1 group by server_name, to_char(start_date,'YY/MM/DD HH24') ORDER BY server_name, to_char(start_date,'YY/MM/DD HH24') ; < phys_reads.sql break on begin_interval_time skip 2 column phyrds format 999,999,999 column begin_interval_time format a25 select begin_interval_time, filename, phyrds from dba_hist_filestatxs natural join dba_hist_snapshot where phyrds > 10000; Chapter 13 < display_hot_cold_files.sql select dg.name diskgroup, t.name, t.stripe, t.redundancy, t.primary_region, t.mirror_region from v$asm_diskgroup dg, v$asm_template t where dg.group_number = t.group_number and dg.name = 'DATA' ORDER BY t.name; select dg.name diskgroup, f.file_number, f.primary_region, f.mirror_region, f.hot_reads, f.hot_writes, f.cold_reads, f.cold_writes from v$asm_diskgroup dg, v$asm_file f where dg.group_number = f.group_number and dg.name = 'DATA'; set serveroutput on declare lat integer; iops integer; mbps integer; begin dbms_resource_manager.calibrate_io (2, 10, iops, mbps, lat); dbms_output.put_line ('max_iops = ' || iops); dbms_output.put_line ('latency = ' || lat); dbms_output.put_line('max_mbps = ' || mbps); end; / drop table perfstat.stats$iostat; create table perfstat.stats$iostat ( snap_time date, elapsed_seconds number(4), hdisk varchar2(8), kb_read number(9,0), kb_write number(9,0) ) tablespace perfstat storage (initial 20m next 1m ); create index perfstat.stats$iostat_date_idx on perfstat.stats$iostat (snap_time) tablespace perfstat storage (initial 5m next 1m); create index perfstat.stats$iostat_hdisk_idx on perfstat.stats$iostat (hdisk) tablespace perfstat storage (initial 5m next 1m); < get_iostat_solaris.ksh #!/bin/ksh while true do iostat -x 300 1|\ sed 1,2d|\ awk '{ printf("%s %s %s\n", $1, $4, $5) }' |\ while read HDISK VMSTAT_IO_R VMSTAT_IO_W do if [ $VMSTAT_IO_R -gt 0 ] and [ $VMSTAT_IO_W -gt 0 ] then sqlplus -s perfstat/perfstat <<! insert into perfstat.stats\$iostat values (SYSDATE, 5, '$HDISK', $VMSTAT_IO_R,$VMSTAT_IO_W); exit ! fi done sleep 300 done #!/bin/ksh # First, we must set the environment . . . . ORACLE_SID=prodz1 ORACLE_HOME=`cat /var/opt/oracle/oratab|grep $ORACLE_SID|cut -f2 -d':'` PATH=$ORACLE_HOME/bin:$PATH MON=`echo ~oracle/iostat` #---------------------------------------- # If it is not running, then start it . . . #---------------------------------------- check_stat=`ps -ef|grep get_iostat|wc -l`; oracle_num=`expr $check_stat` if [ $oracle_num -ne 2 ] then nohup $MON/get_iostat_solaris.ksh > /dev/null 2>&1 & fi < rpt_disk.sql column hdisk format a10; column mydate format a15; column sum_kb_read format 999,999; column sum_kb_write format 999,999; set pages 999; break on hdisk skip 1; select hdisk, -- to_char(snap_time,'yyyy-mm-dd HH24:mi:ss') mydate, -- to_char(snap_time,'yyyy-mm-dd HH24') mydate, to_char(snap_time,'day') mydate, sum(kb_read) sum_kb_read, sum(kb_write) sum_kb_write from stats$iostat group by hdisk ,to_char(snap_time,'day') -- ,to_char(snap_time,'yyyy-mm-dd HH24:mi:ss') -- ,to_char(snap_time,'yyyy-mm-dd HH24'); < asm_disk_iostat.sql col instname format a08 heading 'inst|name' col dbname format a08 heading 'db name' col group_name format a08 heading 'disk|group|name' col disk_number format 9999 heading 'asm|disk|#' col reads format 99999999 heading 'disk|reads' col read_time format 999999 heading 'read|time|(s)' col read_errs format 999999 heading 'read|errors' col mb_read format 999999.9 heading 'bytes|read|(mb)' col writes format 99999999 heading 'disk|writes' col write_errs format 999999 heading 'write|errors' col write_time format 999999 heading 'write|time|(s)' col mb_wrtn format 999999.9 heading 'bytes|written|(mb)' ttitle 'ASM disk I/O statistics' select a.dbname, a.instname, b.name group_name, a.disk_number, a.reads, a.read_errs, a.read_time, ( a.bytes_read / (1024*1024)) mb_read, a.writes, a.write_errs, a.write_time, ( a.bytes_written / (1024*1024)) mb_wrtn, -- ************************** -- new data columns in 11gr2 -- ************************** (a.hot_bytes_read / (1024*1024)) hot_mb_read, (a.cold_bytes_read / (1024*1024)) cold_mb_read, (a.hot_bytes_written / (1024*1024)) hot_mb_wrtn, (a.cold_bytes_written / (1024*1024)) cold_mb_wrtn from v$asm_disk_iostat a, v$asm_diskgroup b where a.group_number = b.group_number order by a.dbname, a.instname, adg.name; < blocks_to_buffers.sql select decode( pd.bp_id, 1,'KEEP', 2,'RECYCLE', 3,'DEFAULT', 4,'2K SUBCACHE', 5,'4K SUBCACHE', 6,'8K SUBCACHE', 7,'16K SUBCACHE', 8,'32K SUBCACHE', 'UNKNOWN') subcache, bh.object_name, bh.blocks from x$kcbwds ds, x$kcbwbpd pd, (select /*+ use_hash(x) */ set_ds, o.name object_name, count(*) BLOCKS from obj$ o, x$bh x where o.dataobj# = x.obj and x.state !=0 and o.owner# !=0 group by set_ds,o.name) bh where ds.set_id >= pd.bp_lo_sid and ds.set_id <= pd.bp_hi_sid and pd.bp_size != 0 and ds.addr=bh.set_ds; < ora_phys_io.sql select d.name file_name, c.name tablespace_name, b.phyrds, b.phywrts, b.phyblkrd, b.phyblkwrt, b.readtim, b.writetim from sys.v_$datafile a, sys.v_$filestat b, sys.ts$ c, sys.v_$dbfile d, sys.file$ e where a.file# = b.file# and a.file# = d.file# and e.ts# = c.ts# and e.file# = d.file# union all select v.fnnam file_name, c.name tablespace_name, b.phyrds, b.phywrts, b.phyblkrd, b.phyblkwrt, b.readtim, b.writetim from sys.v_$tempfile a, sys.v_$tempstat b, sys.ts$ c, sys.x$kccfn v, sys.x$ktfthc hc where a.file# = b.file# and a.file# = hc.ktfthctfno and hc.ktfthctsn = c.ts# and v.fntyp = 7 and v.fnnam is not null and v.fnfno = hc.ktfthctfno and hc.ktfthctsn = c.ts# order by 3 desc; < io_overview.sql select name, value from sys.v_$sysstat where name in ('consistent changes', 'consistent gets', 'db block changes', 'db block gets', 'physical reads', 'physical writes', 'sorts (disk)', 'user commits', 'user rollbacks' ) order by 1; < v_dollar_phys_reads_ratio.sql select 100 - 100 * (round ((sum (decode (name, 'physical reads', value, 0)) - sum (decode (name, 'physical reads direct', value, 0)) - sum (decode (name, 'physical reads direct (lob)', value, 0))) / (sum (decode (name, 'session logical reads', value, 1)) ),3)) hit_ratio from sys.v_$sysstat where name in ('session logical reads', 'physical reads direct (lob)', 'physical reads', 'physical reads direct'); < fileio.sql -- ************************************************* -- Copyright © 2005 by Rampant TechPress -- This script is free for non-commercial purposes -- with no warranties. Use at your own risk. -- -- To license this script for a commercial purpose, -- contact info@rampant.cc -- ************************************************* rem rem NAME: fileio.sql rem rem FUNCTION: Reports on the file io status of all of the rem FUNCTION: datafiles in the database. rem column sum_io1 new_value st1 noprint column sum_io2 new_value st2 noprint column sum_io new_value divide_by noprint column Percent format 999.999 heading 'Percent|Of IO' column brratio format 999.99 heading 'Block|Read|Ratio' column bwratio format 999.99 heading 'Block|Write|Ratio' column phyrds heading 'Physical | Reads' column phywrts heading 'Physical | Writes' column phyblkrd heading 'Physical|Block|Reads' column phyblkwrt heading 'Physical|Block|Writes' column name format a45 heading 'File|Name' column file# format 9999 heading 'File' column dt new_value today noprint select to_char(sysdate,'ddmonyyyyhh24miss') dt from dual; set feedback off verify off lines 132 pages 60 sqlbl on trims on rem select nvl(sum(a.phyrds+a.phywrts),0) sum_io1 from sys.v_$filestat a; select nvl(sum(b.phyrds+b.phywrts),0) sum_io2 from sys.v_$tempstat b; select &st1+&st2 sum_io from dual; rem @title132 'File I/O Statistics Report' spool rep_out\&db\fileio&&today select a.file#,b.name, a.phyrds, a.phywrts, (100*(a.phyrds+a.phywrts)/÷_by) Percent, a.phyblkrd, a.phyblkwrt, (a.phyblkrd/greatest(a.phyrds,1)) brratio, (a.phyblkwrt/greatest(a.phywrts,1)) bwratio from sys.v_$filestat a, sys.v_$dbfile b where a.file#=b.file# union select c.file#,d.name, c.phyrds, c.phywrts, (100*(c.phyrds+c.phywrts)/÷_by) Percent, c.phyblkrd, c.phyblkwrt,(c.phyblkrd/greatest(c.phyrds,1)) brratio, (c.phyblkwrt/greatest(c.phywrts,1)) bwratio from sys.v_$tempstat c, sys.v_$tempfile d where c.file#=d.file# order by 1 / spool off pause Press enter to continue set feedback on verify on lines 80 pages 22 clear columns ttitle off < get_io.sql -- ************************************************* -- Copyright © 2005 by Rampant TechPress -- This script is free for non-commercial purposes -- with no warranties. Use at your own risk. -- -- To license this script for a commercial purpose, -- contact info@rampant.cc -- ************************************************* set serveroutput on declare cursor get_io is select nvl(sum(a.phyrds+a.phywrts),0) sum_io1,to_number(null) sum_io2 from sys.gv_$filestat a union select to_number(null) sum_io1, nvl(sum(b.phyrds+b.phywrts),0) sum_io2 from sys.gv_$tempstat b; now date; elapsed_seconds number; sum_io1 number; sum_io2 number; sum_io12 number; sum_io22 number; tot_io number; tot_io_per_sec number; fixed_io_per_sec number; temp_io_per_sec number; begin open get_io; for i in 1..2 loop fetch get_io into sum_io1, sum_io2; if i = 1 then sum_io12:=sum_io1; else sum_io22:=sum_io2; end if; end loop; select sum_io12+sum_io22 into tot_io from dual; select sysdate into now from dual; select ceil((now-max(startup_time))*(60*60*24)) into elapsed_seconds from gv$instance; fixed_io_per_sec:=sum_io12/elapsed_seconds; temp_io_per_sec:=sum_io22/elapsed_seconds; tot_io_per_sec:=tot_io/elapsed_seconds; dbms_output.put_line('Elapsed Sec :'||to_char(elapsed_seconds, '9,999,999.99')); dbms_output.put_line('Fixed IO/SEC:'||to_char(fixed_io_per_sec,'9,999,999.99')); dbms_output.put_line('Temp IO/SEC :'||to_char(temp_io_per_sec, '9,999,999.99')); dbms_output.put_line('Total IO/SEC:'||to_char(tot_io_Per_Sec, '9,999,999.99')); end; / < wait_report.sql -- ************************************************* -- Copyright © 2005 by Rampant TechPress -- This script is free for non-commercial purposes -- with no warranties. Use at your own risk. -- -- To license this script for a commercial purpose, -- contact info@rampant.cc -- ************************************************* col event format a30 heading 'Event Name' col waits format 999,999,999 heading 'Total|Waits' col average_wait format 999,999,999 heading 'Average|Waits' col time_waited format 999,999,999 heading 'Time Waited' col total_time new_value divide_by noprint col value new_value val noprint col percent format 999.990 heading 'Percent|Of|Non-Idle Waits' col duration new_value millisec noprint col p_of_total heading 'Percent|of Total|Uptime' format 999.9999 set lines 132 feedback off verify off pages 50 select to_number(sysdate-startup_time)*86400*1000 duration from v$instance; select sum(time_waited) total_time from v$system_event where total_waits-total_timeouts>0 and event not like 'SQL*Net%' and event not like 'smon%' and event not like 'pmon%' and event not like 'rdbms%' and event not like 'PX%' and event not like 'sbt%' and event not in ('gcs remote message','ges remote message','virtual circuit status','dispatcher timer') ; select value from v$sysstat where name ='CPU used when call started'; @title132 'System Events Percent' break on report compute sum of time_waited on report spool rep_out/&db/sys_events select name event, 0 waits, 0 average_wait, value time_waited, value/(&÷_by+&&val)*100 Percent, value/&&millisec*100 p_of_total from v$sysstat where name ='CPU used when call started' union select event, total_waits-total_timeouts waits, time_waited/(total_waits-total_timeouts) average_wait, time_waited, time_waited/(&÷_by+&&val)*100 Percent, time_waited/&&millisec*100 P_of_total from v$system_event where total_waits-total_timeouts>0 and event not like 'SQL*Net%' and event not like 'smon%' and event not like 'pmon%' and event not like 'rdbms%' and event not like 'PX%' and event not like 'sbt%' and event not in ('gcs remote message','ges remote message','virtual circuit status','dispatcher timer') and time_waited>0 order by percent desc / spool off clear columns ttitle off clear computes clear breaks col instname format a08 heading 'inst|name' col dbname format a08 heading 'db name' col group_name format a08 heading 'disk|group|name' col disk_number format 9999 heading 'asm|disk|#' col reads format 99999999 heading 'disk|reads' col read_time format 999999 heading 'read|time|(s)' col read_errs format 999999 heading 'read|errors' col mb_read format 999999.9 heading 'bytes|read|(mb)' col writes format 99999999 heading 'disk|writes' col write_errs format 999999 heading 'write|errors' col write_time format 999999 heading 'write|time|(s)' col mb_wrtn format 999999.9 heading 'bytes|written|(mb)' ttitle 'ASM disk I/O statistics' select a.dbname, a.instname, b.name group_name, a.disk_number, a.reads, a.read_errs, a.read_time, ( a.bytes_read / (1024*1024)) mb_read, a.writes, a.write_errs, a.write_time, ( a.bytes_written / (1024*1024)) mb_wrtn, -- ************************** -- new data columns in 11gr2 -- ************************** (a.hot_bytes_read / (1024*1024)) hot_mb_read, (a.cold_bytes_read / (1024*1024)) cold_mb_read, (a.hot_bytes_written / (1024*1024)) hot_mb_wrtn, (a.cold_bytes_written / (1024*1024)) cold_mb_wrtn from v$asm_disk_iostat a, v$asm_diskgroup b where a.group_number = b.group_number order by a.dbname, a.instname, adg.name; select event, wait_time_milli, wait_count from v$event_histogram where event in (select name from v$event_name where wait_class not in ('Idle') ) order by 1,2; -- Sequential read wait times select wait_time_milli, wait_count from v$event_histogram where event = 'db file sequential read' order by wait_time_milli; -- Scattered read wait times (full scans) select wait_time_milli, wait_count from v$event_histogram where event = 'db file scattered read' order by wait_time_milli; < awr_waits_over_time.sql col c1 heading 'Average Waits for|Full Scan Read I/O' format 9999.999 col c2 heading 'Average Waits for|Index Read I/O' format 9999.999 col c3 heading 'Percent of| I/O Waits|for scattered|Full Scans' format 9.99 col c4 heading 'Percent of| I/O Waits|for sequential|Index Scans' format 9.99 col c5 heading 'Starting|Value|for|optimizer|index|cost|adj' format 999 select sum(a.time_waited_micro)/sum(a.total_waits)/1000000 c1, sum(b.time_waited_micro)/sum(b.total_waits)/1000000 c2, ( sum(a.total_waits) / sum(a.total_waits + b.total_waits) ) * 100 c3, ( sum(b.total_waits) / sum(a.total_waits + b.total_waits) ) * 100 c4, ( sum(b.time_waited_micro) / sum(b.total_waits)) / (sum(a.time_waited_micro)/sum(a.total_waits) ) * 100 c5 from dba_hist_system_event a, dba_hist_system_event b where a.snap_id = b.snap_id and a.event_name = 'db file scattered read' and b.event_name = 'db file sequential read'; < toptables.sql -- ************************************************* -- Copyright © 2005 by Rampant TechPress -- This script is free for non-commercial purposes -- with no warranties. Use at your own risk. -- -- To license this script for a commercial purpose, -- contact info@rampant.cc -- ************************************************* select table_owner "table owner", table_name "table name", command "command issued", 0 - executions "executions", disk_reads "disk reads", gets "buffer gets", rows_processed "rows processed" from (select distinct executions, command, table_owner, table_name, gets, rows_processed, disk_reads from (select decode (a.command_type , 2, 'insert ' , 3,'select ', 6, 'update ' , 7, 'delete ' , 26,'table lock ') command , c.owner table_owner, c.name table_name , sum(a.disk_reads) disk_reads , sum(0 - a.executions) executions , sum(a.buffer_gets) gets , sum(a.rows_processed) rows_processed from sys.v_$sql a , sys.v_$object_dependency b , sys.v_$db_object_cache c where a.command_type in (2,3,6,7,26)and b.from_address = a.address and b.to_owner = c.owner and b.to_name= c.name and c.type = 'table' and c.owner not in ('SYS','SYSTEM') group by a.command_type , c.owner , c.name ) ) where rownum <= 100; < sql_to_tables.sql select table_owner, table_name, table_type, size_kb, statement_count, reference_count, executions, executions * reference_count total_scans from (select a.object_owner table_owner, a.object_name table_name, b.segment_type table_type, b.bytes / 1024 size_kb, sum(c.executions ) executions, count( distinct a.hash_value ) statement_count, count( * ) reference_count from sys.v_$sql_plan a, sys.dba_segments b, sys.v_$sql c where a.object_owner (+) = b.owner and a.object_name (+) = b.segment_name and b.segment_type IN ('TABLE', 'TABLE PARTITION') and a.operation LIKE '%TABLE%' and a.options = 'FULL' and a.hash_value = c.hash_value and b.bytes / 1024 > 1024 group by a.object_owner, a.object_name, a.operation, b.bytes / 1024, b.segment_type order by 4 desc, 1, 2 ); select name, block_size, (1-(physical_reads/ decode(db_block_gets+consistent_gets, 0, .001, db_block_gets+consistent_gets)))*100 cache_hit_ratio from v$buffer_pool_statistics; < snapfileio_awr.sql -- ************************************************* -- Copyright © 2005 by Rampant TechPress -- This script is free for non-commercial purposes -- with no warranties. Use at your own risk. -- -- To license this script for a commercial purpose, -- contact info@rampant.cc -- ************************************************* rem FUNCTION: Reports on the file io status of all of the rem FUNCTION: datafiles in the database for a single snapshot. column sum_io1 new_value st1 noprint column sum_io2 new_value st2 noprint column sum_io new_value divide_by noprint column Percent format 999.999 heading 'Percent|Of IO' column brratio format 999.99 heading 'Block|Read|Ratio' column bwratio format 999.99 heading 'Block|Write|Ratio' column phyrds heading 'Physical | Reads' column phywrts heading 'Physical | Writes' column phyblkrd heading 'Physical|Block|Reads' column phyblkwrt heading 'Physical|Block|Writes' column filename format a45 heading 'File|Name' column file# format 9999 heading 'File' set feedback off verify off lines 132 pages 60 sqlbl on trims on select nvl(sum(a.phyrds+a.phywrts),0) sum_io1 from dba_hist_filestatxs a where snap_id=&&snap; select nvl(sum(b.phyrds+b.phywrts),0) sum_io2 from dba_hist_tempstatxs b where snap_id=&&snap; select &st1+&st2 sum_io from dual; rem @title132 'Snap&&snap File I/O Statistics Report' spool rep_out\&db\fileio&&snap select a.filename, a.phyrds, a.phywrts, (100*(a.phyrds+a.phywrts)/÷_by) Percent, a.phyblkrd, a.phyblkwrt, (a.phyblkrd/greatest(a.phyrds,1)) brratio, (a.phyblkwrt/greatest(a.phywrts,1)) bwratio from dba_hist_filestatxs a where a.snap_id=&&snap union select c.filename, c.phyrds, c.phywrts, (100*(c.phyrds+c.phywrts)/÷_by) Percent, c.phyblkrd, c.phyblkwrt,(c.phyblkrd/greatest(c.phyrds,1)) brratio, (c.phyblkwrt/greatest(c.phywrts,1)) bwratio from dba_hist_tempstatxs c where c.snap_id=&&snap order by 1 / spool off pause Press enter to continue set feedback on verify on lines 80 pages 22 clear columns ttitle off undef snap < snapdeltafileio_awr.sql -- ************************************************* -- Copyright © 2005 by Rampant TechPress -- This script is free for non-commercial purposes -- with no warranties. Use at your own risk. -- -- To license this script for a commercial purpose, -- contact info@rampant.cc -- ************************************************* rem FUNCTION: Reports on the file io status of all of rem FUNCTION: the datafiles in the database across rem FUNCTION: two snapshots. column sum_io1 new_value st1 noprint column sum_io2 new_value st2 noprint column sum_io new_value divide_by noprint column Percent format 999.999 heading 'Percent|Of IO' column brratio format 999.99 heading 'Block|Read|Ratio' column bwratio format 999.99 heading 'Block|Write|Ratio' column phyrds heading 'Physical | Reads' column phywrts heading 'Physical | Writes' column phyblkrd heading 'Physical|Block|Reads' column phyblkwrt heading 'Physical|Block|Writes' column filename format a45 heading 'File|Name' column file# format 9999 heading 'File' set feedback off verify off lines 132 pages 60 sqlbl on trims on select nvl(sum((b.phyrds-a.phyrds)+(b.phywrts-a.phywrts)),0) sum_io1 from dba_hist_filestatxs a, dba_hist_filestatxs b where a.snap_id=&&first_snap_id and b.snap_id=&&sec_snap_id and a.filename=b.filename; select nvl(sum((b.phyrds-a.phyrds)+(b.phywrts-a.phywrts)),0) sum_io2 from dba_hist_tempstatxs a, dba_hist_tempstatxs b where a.snap_id=&&first_snap_id and b.snap_id=&&sec_snap_id and a.filename=b.filename; select &st1+&st2 sum_io from dual; rem @title132 'Snap &&first_snap_id to &&sec_snap_id File I/O Statistics Report' spool rep_out\&db\fileio'&&first_snap_id'_to_'&&sec_snap_id' select a.filename, b.phyrds -a.phyrds phyrds, b.phywrts-a.phywrts phywrts, (100*((b.phyrds-a.phyrds)+(b.phywrts-a.phywrts))/÷_by) Percent, b.phyblkrd- a.phyblkrd phyblkrd, b.phyblkwrt-a.phyblkwrt phyblgwrt, ((b.phyblkrd-a.phyblkrd)/greatest((b.phyrds-a.phyrds),1)) brratio, ((b.phyblkwrt-a.phyblkwrt)/greatest((b.phywrts-a.phywrts),1)) bwratio from dba_hist_filestatxs a, dba_hist_filestatxs b where a.snap_id=&&first_snap_id and b.snap_id=&&sec_snap_id and a.filename=b.filename union select c.filename, d.phyrds-c.phyrds phyrds, d.phywrts-c.phywrts phywrts, (100*((d.phyrds-c.phyrds)+(d.phywrts-c.phywrts))/÷_by) Percent, d.phyblkrd-c.phyblkrd phyblkrd, d.phyblkwrt-c.phyblkwrt phyblgwrt, ((d.phyblkrd-c.phyblkrd)/greatest((d.phyrds-c.phyrds),1)) brratio, ((d.phyblkwrt-c.phyblkwrt)/greatest((d.phywrts-c.phywrts),1)) bwratio from dba_hist_tempstatxs c, dba_hist_tempstatxs d where c.snap_id=&&first_snap_id and d.snap_id=&&sec_snap_id and c.filename=d.filename order by 1 / spool off pause Press enter to continue set feedback on verify on lines 80 pages 22 clear columns ttitle off undef first_snap_id undef sec_snap_id; < avg_stats_doy.sql prompt Copyright 2004 by Donald K. Burleson prompt prompt prompt This will query the dba_hist_sysstat view to prompt display average values by hour of the day prompt set pages 999 break on snap_time skip 2 accept stat_name char prompt 'Enter Statistics Name: '; col snap_time format a19 col avg_value format 999,999,999 select decode(snap_time1,1,'Monday',2,'Tuesday',3,'Wednesday',4,'Thursday',5,'Friday',6,'Saturday',7,'Sunday') snap_time, avg_value from ( select to_char(begin_interval_time,'d') snap_time1, avg(value) avg_value from dba_hist_sysstat natural join dba_hist_snapshot where stat_name = 'physical reads' group by to_char(begin_interval_time,'d') order by to_char(begin_interval_time,'d') ); Chapter 14 < display_pga_size.sql set pages 999; column pga_size format 999,999,999 select 1048576+a.value+b.value pga_size from v$parameter a, v$parameter b where a.name = 'sort_area_size' and b.name = 'hash_area_size'; set pages 999; column pga_size format 999,999,999 accept hwm number prompt 'Enter high-water mark of connected users:' select &hwm*(2048576+a.value+b.value) pga_size from v$parameter a, v$parameter b where a.name = 'sort_area_size' and b.name = 'hash_area_size'; < track_hash_joins.sql col c1 heading ‘Date’ format a20 col c2 heading ‘Hash|join|Count’ format 99,999,999 col c3 heading ‘Rows|Processed’ format 99,999,999 col c4 heading ‘Disk|Reads’ format 99,999,999 col c5 heading ‘CPU|Time’ format 99,999,999 accept nested_thr char prompt ‘Enter Nested Join Threshold: ‘ ttitle ‘Nested Join Threshold|&nested_thr’ select to_char(sn.begin_interval_time,'yy-mm-dd hh24') c1, count(*) c2, sum(st.rows_processed_delta) c3, sum(st.disk_reads_delta) c4, sum(st.cpu_time_delta) c5 from dba_hist_snapshot sn, dba_hist_sql_plan p, dba_hist_sqlstat st where st.sql_id = p.sql_id and sn.snap_id = st.snap_id and sn.dbid = st.dbid and p.operation like ‘%HASH%’ having count(*) > &hash_thr group by begin_interval_time; select 'alter session set hash_area_size='||trunc(sum(bytes)*1.6)||';' from dba_segments where segment_name = upper('&1'); spool off; @run_hash < ash__view_events.sql select ash.event, sum(ash.wait_time + ash.time_waited) ttl_wait_time from v$active_session_history ash where ash.sample_time between sysdate - 60/2880 and sysdate group by ash.event order by 2; < ash_session_details.sql col wait_time format 999,999,999 select sess.sid, sess.username, sum(ash.wait_time + ash.time_waited) wait_time from v$active_session_history ash, v$session sess where ash.sample_time > sysdate-1 and ash.session_id = sess.sid group by sess.sid, sess.username order by 3; < ash_display_sql_wait_time.sql select ash.user_id, u.username, sqla.sql_text, sum(ash.wait_time + ash.time_waited) wait_time from v$active_session_history ash, v$sqlarea sqla, dba_users u where ash.sample_time > sysdate-1 and ash.sql_id = sqla.sql_id and ash.user_id = u.user_id group by ash.user_id, sqla.sql_text, u.username order by 4; select obj.object_name, obj.object_type, ash.event, sum(ash.wait_time + ash.time_waited) wait_time from v$active_session_history ash, dba_objects obj where ash.sample_time > sysdate -1 and ash.current_obj# = obj.object_id group by obj.object_name, obj.object_type, ash.event order by 4 desc; select 1 - ((a.value - (b.value))/d.value) "Cache Hit Ratio" from v$sysstat a, v$sysstat b, v$sysstat d where a.name='physical reads' and b.name='physical reads direct' and d.name='session logical reads'; < rpt_bhr_all.sql -- ************************************************* -- Copyright © 2005 by Rampant TechPress -- This script is free for non-commercial purposes -- with no warranties. Use at your own risk. -- -- To license this script for a commercial purpose, -- contact info@rampant.cc -- ************************************************* column bhr format 9.99 column mydate heading 'yr. mo dy Hr.' select to_char(snap_time,'yyyy-mm-dd HH24') mydate, new.name buffer_pool_name, (((new.consistent_gets-old.consistent_gets)+ (new.db_block_gets-old.db_block_gets))- (new.physical_reads-old.physical_reads)) / ((new.consistent_gets-old.consistent_gets)+ (new.db_block_gets-old.db_block_gets)) bhr from dba_hist_buffer_pool_stat old, dba_hist_buffer_pool_stat new, dba_hist_sga sn where (((new.consistent_gets-old.consistent_gets)+ (new.db_block_gets-old.db_block_gets))- (new.physical_reads-old.physical_reads)) / ((new.consistent_gets-old.consistent_gets)+ (new.db_block_gets-old.db_block_gets)) < .90 and new.name = old.name and new.snap_id = sn.snap_id and old.snap_id = sn.snap_id-1 ; < rpt_bhr_all_awr.sql -- ************************************************* -- Copyright © 2005 by Rampant TechPress -- This script is free for non-commercial purposes -- with no warranties. Use at your own risk. -- -- To license this script for a commercial purpose, -- contact info@rampant.cc -- ************************************************* column bhr format 9.99 column mydate heading 'yr. mo dy Hr.' select to_char(end_interval_time,'yyyy-mm-dd HH24') mydate, new.name buffer_pool_name, (((new.consistent_gets-old.consistent_gets)+ (new.db_block_gets-old.db_block_gets))- (new.physical_reads-old.physical_reads)) / ((new.consistent_gets-old.consistent_gets)+ (new.db_block_gets-old.db_block_gets)) bhr from dba_hist_buffer_pool_stat old, dba_hist_buffer_pool_stat new, dba_hist_snapshot sn where (((new.consistent_gets-old.consistent_gets)+ (new.db_block_gets-old.db_block_gets))- (new.physical_reads-old.physical_reads)) / ((new.consistent_gets-old.consistent_gets)+ (new.db_block_gets-old.db_block_gets)) < .90 and new.name = old.name and new.snap_id = sn.snap_id and old.snap_id = sn.snap_id-1 ; < display_buffer_cache_advice.sql column c1 heading 'Cache Size (m)' format 999,999,999,999 column c2 heading 'Buffers' format 999,999,999 column c3 heading 'Estd Phys|Read Factor' format 999.90 column c4 heading 'Estd Phys| Reads' format 999,999,999 select size_for_estimate c1, buffers_for_estimate c2, estd_physical_read_factor c3, estd_physical_reads c4 from v$db_cache_advice where name = 'DEFAULT' and block_size = (SELECT value FROM V$PARAMETER WHERE name = 'db_block_size') and advice_status = 'ON'; < rpt_bhr_awr_hr.sql -- ************************************************* -- Copyright © 2005 by Rampant TechPress -- This script is free for non-commercial purposes -- with no warranties. Use at your own risk. -- -- To license this script for a commercial purpose, -- contact info@rampant.cc -- ************************************************* set pages 999; column bhr format 9.99 column mydate heading 'yr. mo dy Hr.' select to_char(snap_time,'HH24') mydate, avg( (((new.consistent_gets-old.consistent_gets)+ (new.db_block_gets-old.db_block_gets))- (new.physical_reads-old.physical_reads)) / ((new.consistent_gets-old.consistent_gets)+ (new.db_block_gets-old.db_block_gets)) ) bhr from dba_hist_buffer_pool_stat old, dba_hist_buffer_pool_stat new, dba_hist_sga sn where new.name in ('DEFAULT','FAKE VIEW') and new.name = old.name and new.snap_id = sn.snap_id and old.snap_id = sn.snap_id-1 and new.consistent_gets > 0 and old.consistent_gets > 0 having avg( (((new.consistent_gets-old.consistent_gets)+ (new.db_block_gets-old.db_block_gets))- (new.physical_reads-old.physical_reads)) / ((new.consistent_gets-old.consistent_gets)+ (new.db_block_gets-old.db_block_gets)) ) < 1 group by to_char(snap_time,'HH24'); set pages 999; column bhr format 9.99 column mydate heading 'yr. mo dy Hr.' select to_char(end_interval_time,'day') mydate, avg( (((new.consistent_gets-old.consistent_gets)+ (new.db_block_gets-old.db_block_gets))- (new.physical_reads-old.physical_reads)) / ((new.consistent_gets-old.consistent_gets)+ (new.db_block_gets-old.db_block_gets)) ) bhr from dba_hist_buffer_pool_stat old, dba_hist_buffer_pool_stat new, dba_hist_snapshot sn where new.name in ('DEFAULT','FAKE VIEW') and new.name = old.name and new.snap_id = sn.snap_id and old.snap_id = sn.snap_id-1 and new.consistent_gets > 0 and old.consistent_gets > 0 having avg( (((new.consistent_gets-old.consistent_gets)+ (new.db_block_gets-old.db_block_gets))- (new.physical_reads-old.physical_reads)) / ((new.consistent_gets-old.consistent_gets)+ (new.db_block_gets-old.db_block_gets)) ) < 1 group by to_char(end_interval_time,'day') ; set lines 80; set pages 999; column mydate heading 'Yr. Mo Dy Hr.' format a16 column c1 heading "execs" format 9,999,999 column c2 heading "Cache Misses|While Executing" format 9,999,999 column c3 heading "Library Cache|Miss Ratio" format 999.99999 break on mydate skip 2; select to_char(snap_time,'yyyy-mm-dd HH24') mydate, sum(new.pins-old.pins) c1, sum(new.reloads-old.reloads) c2, sum(new.reloads-old.reloads)/ sum(new.pins-old.pins) library_cache_miss_ratio from stats$librarycache old, stats$librarycache new, stats$snapshot sn where new.snap_id = sn.snap_id and old.snap_id = new.snap_id-1 and old.namespace = new.namespace group by to_char(snap_time,'yyyy-mm-dd HH24'); SELECT obj object, dbarfil file#, dbablk block#, tch touches FROM x$bh WHERE tch > 10 ORDER BY tch desc; < buf_blocks.sql -- ************************************************* -- Copyright © 2005 by Rampant TechPress -- This script is free for non-commercial purposes -- with no warranties. Use at your own risk. -- -- To license this script for a commercial purpose, -- contact info@rampant.cc -- ************************************************* set pages 999 set lines 92 ttitle 'Contents of Data Buffers' drop table t1; create table t1 as select o.owner owner, o.object_name object_name, o.subobject_name subobject_name, o.object_type object_type, count(distinct file# || block#) num_blocks from dba_objects o, v$bh bh where o.data_object_id = bh.objd and o.owner not in ('SYS','SYSTEM') and bh.status != 'free' group by o.owner, o.object_name, o.subobject_name, o.object_type order by count(distinct file# || block#) desc ; column c0 heading "Owner" format a12 column c1 heading "Object|Name" format a30 column c2 heading "Object|Type" format a8 column c3 heading "Number of|Blocks in|Buffer|Cache" format 99,999,999 column c4 heading "Percentage|of object|blocks in|Buffer" format 999 column c5 heading "Buffer|Pool" format a7 column c6 heading "Block|Size" format 99,999 select t1.owner c0, object_name c1, case when object_type = 'TABLE PARTITION' then 'TAB PART' when object_type = 'INDEX PARTITION' then 'IDX PART' else object_type end c2, sum(num_blocks) c3, (sum(num_blocks)/greatest(sum(blocks), .001))*100 c4, buffer_pool c5, sum(bytes)/sum(blocks) c6 from t1, dba_segments s where s.segment_name = t1.object_name and s.owner = t1.owner and s.segment_type = t1.object_type and nvl(s.partition_name,'-') = nvl(t1.subobject_name,'-') group by t1.owner, object_name, object_type, buffer_pool having sum(num_blocks) > 10 order by sum(num_blocks) desc; < all_vbh_status.sql set pages 50 @title80 'All Buffers Status' spool rep_out\&&db\all_vbh_status select '32k '||status as status, count(*) as num from v$bh where file# in( select file_id from dba_data_files where tablespace_name in ( select tablespace_name from dba_tablespaces where block_size=32768)) group by '32k '||status union select '16k '||status as status, count(*) as num from v$bh where file# in( select file_id from dba_data_files where tablespace_name in ( select tablespace_name from dba_tablespaces where block_size=16384)) group by '16k '||status union select '8k '||status as status, count(*) as num from v$bh where file# in( select file_id from dba_data_files where tablespace_name in ( select tablespace_name from dba_tablespaces where block_size=8192)) group by '8k '||status union select '4k '||status as status, count(*) as num from v$bh where file# in( select file_id from dba_data_files where tablespace_name in ( select tablespace_name from dba_tablespaces where block_size=4096)) group by '4k '||status union select '2k '||status as status, count(*) as num from v$bh where file# in( select file_id from dba_data_files where tablespace_name in ( select tablespace_name from dba_tablespaces where block_size=2048)) group by '2k '||status union select status, count(*) as num from v$bh where status='free' group by status order by 1 / spool off ttitle off < obj_xtab.sql -- Crosstab of object and statistic for an owner -- by Mike Ault www.oracle-script.com col "Object" format a20 set numwidth 12 set lines 132 set pages 50 @title132 'Object Wait Statistics' spool rep_out\&&db\obj_stat_xtab select * from ( select DECODE (GROUPING(a.object_name), 1, 'All Objects', a.object_name) AS "Object", sum(case when a.statistic_name = 'ITL waits' then a.value else null end) "ITL Waits", sum(case when a.statistic_name = 'buffer busy waits' then a.value else null end) "Buffer Busy Waits", sum(case when a.statistic_name = 'row lock waits' then a.value else null end) "Row Lock Waits", sum(case when a.statistic_name = 'physical reads' then a.value else null end) "Physical Reads", sum(case when a.statistic_name = 'logical reads' then a.value else null end) "Logical Reads" from v$segment_statistics a where a.owner like upper('&owner') group by rollup(a.object_name)) b where (b."ITL Waits">0 or b."Buffer Busy Waits">0) / spool off clear columns ttitle off < resize_sga.ksh #!/bin/ksh # First, we must set the environment . . . . ORACLE_SID=$1 export ORACLE_SID ORACLE_HOME=`cat /etc/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'` #ORACLE_HOME=`cat /var/opt/oracle/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'` export ORACLE_HOME PATH=$ORACLE_HOME/bin:$PATH export PATH # ********************************************************* # # This will reduce the size of the data buffer # immediately preceding a large truncate or data purge # # ********************************************************* $ORACLE_HOME/bin/sqlplus –s /nologin<<! connect system/manager as sysdba; alter system set db_cache_size=10m; alter system flush buffer_cache; exit ! # ********************************************************* # Now we can invoke the specialty task. # ********************************************************* nohup purge_job.ksh > /tmp/purge.lst 2>&1 & $ORACLE_HOME/bin/sqlplus –s /nologin<<! connect system/manager as sysdba; alter system set db_cache_size=1500m; exit ! -- You MUST connect as SYS to run this script connect sys/manager; set lines 80; set pages 999; column avg_touches format 999 column myname heading 'Name' format a30 column mytype heading 'Type' format a10 column buffers format 999,999 SELECT object_type mytype, object_name myname, blocks, COUNT(1) buffers, AVG(tch) avg_touches FROM sys.x$bh a, dba_objects b, dba_segments s WHERE a.obj = b.object_id and b.object_name = s.segment_name and b.owner not in ('SYS','SYSTEM') GROUP BY object_name, object_type, blocks, obj HAVING AVG(tch) > 5 AND COUNT(1) > 20; < buf_keep_pool.sql -- ************************************************* -- Copyright © 2005 by Rampant TechPress -- This script is free for non-commercial purposes -- with no warranties. Use at your own risk. -- -- To license this script for a commercial purpose, -- contact info@rampant.cc -- ************************************************* set pages 999 set lines 92 spool keep_syn.lst drop table t1; create table t1 as select o.owner owner, o.object_name object_name, o.subobject_name subobject_name, o.object_type object_type, count(distinct file# || block#) num_blocks from dba_objects o, v$bh bh where o.data_object_id = bh.objd and o.owner not in ('SYS','SYSTEM') and bh.status != 'free' group by o.owner, o.object_name, o.subobject_name, o.object_type order by count(distinct file# || block#) desc ; select 'alter '||s.segment_type||' '||t1.owner||'.'||s.segment_name||' storage (buffer_pool keep);' from t1, dba_segments s where s.segment_name = t1.object_name and s.owner = t1.owner and s.segment_type = t1.object_type and nvl(s.partition_name,'-') = nvl(t1.subobject_name,'-') and buffer_pool <> 'KEEP' and object_type in ('TABLE','INDEX') group by s.segment_type, t1.owner, s.segment_name having (sum(num_blocks)/greatest(sum(blocks), .001))*100 > 80 ; spool off; < get_keep_pool.sql -- ************************************************* -- Copyright © 2005 by Rampant TechPress -- This script is free for non-commercial purposes -- with no warranties. Use at your own risk. -- -- To license this script for a commercial purpose, -- contact info@rampant.cc -- ************************************************* select 'alter table '||p.owner||'.'||p.name||' storage (buffer_pool keep);' from dba_tables t, dba_segments s, dba_hist_sqlstat a, (select distinct pl.sql_id, pl.object_owner owner, pl.object_name name from dba_hist_sql_plan pl where pl.operation = 'TABLE ACCESS' and pl.options = 'FULL') p where a.sql_id = p.sql_id and t.owner = s.owner and t.table_name = s.segment_name and t.table_name = p.name and t.owner = p.owner and t.owner not in ('SYS','SYSTEM') and t.buffer_pool <> 'KEEP' having s.blocks < 50 group by p.owner, p.name, t.num_rows, s.blocks UNION -- *********************************************************** -- Next, get the index names -- *********************************************************** select 'alter index '||owner||'.'||index_name||' storage (buffer_pool keep);' from dba_indexes where owner||'.'||table_name in ( select p.owner||'.'||p.name from dba_tables t, dba_segments s, dba_hist_sqlstat a, (select distinct pl.sql_id, pl.object_owner owner, pl.object_name name from dba_hist_sql_plan pl where pl.operation = 'TABLE ACCESS' and pl.options = 'FULL') p where a.sql_id = p.sql_id and t.owner = s.owner and t.table_name = s.segment_name and t.table_name = p.name and t.owner = p.owner and t.owner not in ('SYS','SYSTEM') and t.buffer_pool <> 'KEEP' having s.blocks < 50 group by p.owner, p.name, t.num_rows, s.blocks ) < keep_syn.sql -- ************************************************* -- Copyright © 2005 by Rampant TechPress -- This script is free for non-commercial purposes -- with no warranties. Use at your own risk. -- -- To license this script for a commercial purpose, -- contact info@rampant.cc -- ************************************************* set pages 999 set lines 92 spool keep_syn.lst drop table t1; create table t1 as select o.owner owner, o.object_name object_name, o.subobject_name subobject_name, o.object_type object_type, count(distinct file# || block#) num_blocks from dba_objects o, v$bh bh where o.data_object_id = bh.objd and o.owner not in ('SYS','SYSTEM') and bh.status != 'free' group by o.owner, o.object_name, o.subobject_name, o.object_type order by count(distinct file# || block#) desc ; select 'alter '||s.segment_type||' '||t1.owner||'.'||s.segment_name||' storage (buffer_pool keep);' from t1, dba_segments s where s.segment_name = t1.object_name and s.owner = t1.owner and s.segment_type = t1.object_type and nvl(s.partition_name,'-') = nvl(t1.subobject_name,'-') and buffer_pool <> 'KEEP' and object_type in ('TABLE','INDEX') group by s.segment_type, t1.owner, s.segment_name having (sum(num_blocks)/greatest(sum(blocks), .001))*100 > 80 ; < size_keep_pool.ksh #!/bin/ksh # First, we must set the environment . . . . ORACLE_SID=mon1 export ORACLE_SID ORACLE_HOME=`cat /etc/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'` export ORACLE_HOME PATH=$ORACLE_HOME/bin:$PATH export PATH $ORACLE_HOME/bin/sqlplus system/manager<<! spool myfile.sql set heading off spool run.sql select ' alter system set db_keep_cache_size = '||trunc(sum(s.bytes)*1.5)||' scope=both;' from dba_segments s where s.buffer_pool = 'KEEP'; spool off; exit ! @run.sql < flash_cache_waits.sql select name, value from v$sysstat where name in ('physical read flash cache hits', 'physical reads', 'consistent gets', 'db block gets', 'flash cache inserts'); select owner||'.'||object_name from v$bh, dba_objects where v$bh.status like ('flash%'); < gen_recycle_syntax.sql -- ************************************************* -- Copyright © 2005 by Rampant TechPress -- This script is free for non-commercial purposes -- with no warranties. Use at your own risk. -- -- To license this script for a commercial purpose, -- contact info@rampant.cc -- ************************************************* set pages 999; set heading off; set feedback off; ttitle off; spool keep_syntax.sql -- *********************************************************** -- First, get the table list -- *********************************************************** select 'alter table '||p.owner||'.'||p.name||' storage (buffer_pool recycle);' from dba_tables t, dba_segments s, v$sqlarea a, (select distinct address, object_owner owner, object_name name from v$sql_plan where operation = 'TABLE ACCESS' and options = 'FULL') p where a.address = p.address and t.owner = s.owner and t.table_name = s.segment_name and t.table_name = p.name and t.owner = p.owner and t.owner not in ('SYS','SYSTEM') and t.buffer_pool <> 'RECYCLE' having s.blocks > 10000 group by p.owner, p.name, t.num_rows, s.blocks UNION -- *********************************************************** -- Next, get the index names -- *********************************************************** select 'alter index '||owner||'.'||index_name||' storage (buffer_pool recycle);' from dba_indexes where owner||'.'||table_name in ( select p.owner||'.'||p.name from dba_tables t, dba_segments s, v$sqlarea a, (select distinct address, object_owner owner, object_name name from v$sql_plan where operation = 'TABLE ACCESS' and options = 'FULL') p where a.address = p.address and t.owner = s.owner and t.table_name = s.segment_name and t.table_name = p.name and t.owner = p.owner and t.owner not in ('SYS','SYSTEM') and t.buffer_pool <> 'RECYCLE' having s.blocks > 10000 group by p.owner, p.name, t.num_rows, s.blocks ) ; spool off; set lines 80; set pages 999; column avg_touches format 999 column myname heading 'Name' format a30 column mytype heading 'Type' format a10 column buffers format 999,999 select object_type mytype, object_name myname, blocks, COUNT(1) buffers, 100*(COUNT(1)/totsize) pct_cache from sys.x$bh a, dba_objects b, dba_segments s, (select value totsize from v$parameter where name = 'db_cache_size') where a.obj = b.object_id and tch=1 -- This line only works in 8.1.6 and above and b.object_name = s.segment_name and b.owner not in ('SYS','SYSTEM') group by object_type, object_name, blocks, totsize having 100*(COUNT(1)/totsize) > 5 ; break on begin_interval_time skip 2 column phyrds format 999,999,999 column begin_interval_time format a25 select begin_interval_time, filename, phyrds from dba_hist_filestatxs natural join dba_hist_snapshot ; set lines 80; set pages 999; column mydate heading 'Yr. Mo Dy Hr.' format a16 column c1 heading "execs" format 9,999,999 column c2 heading "Cache Misses|While Executing" format 9,999,999 column c3 heading "Library Cache|Miss Ratio" format 999.99999 break on mydate skip 2; select to_char(snap_time,'yyyy-mm-dd HH24') mydate, sum(new.pins-old.pins) c1, sum(new.reloads-old.reloads) c2, sum(new.reloads-old.reloads)/ sum(new.pins-old.pins) library_cache_miss_ratio from stats$librarycache old, stats$librarycache new, stats$snapshot sn where new.snap_id = sn.snap_id and old.snap_id = new.snap_id-1 and old.namespace = new.namespace group by to_char(snap_time,'yyyy-mm-dd HH24') ; set lines 80; set pages 999; column mydate heading 'Yr. Mo Dy Hr.' format a16 column c1 heading "execs" format 9,999,999 column c2 heading "Cache Misses|While Executing" format 9,999,999 column c3 heading "Library Cache|Miss Ratio" format 999.99999 break on mydate skip 2; select to_char(sn.end_interval_time,'yyyy-mm-dd HH24') mydate, sum(new.pins-old.pins) c1, sum(new.reloads-old.reloads) c2, sum(new.reloads-old.reloads)/ sum(new.pins-old.pins) library_cache_miss_ratio from dba_hist_librarycache old, dba_hist_librarycache new, dba_hist_snapshot sn where new.snap_id = sn.snap_id and old.snap_id = new.snap_id-1 and old.namespace = new.namespace group by to_char(sn.end_interval_time,'yyyy-mm-dd HH24') ; set lines 100 set pages 999 column c1 heading 'Pool |Size(M)' column c2 heading 'Size|Factor' column c3 heading 'Est|LC(M) ' column c4 heading 'Est LC|Mem. Obj.' column c5 heading 'Est|Time|Saved|(sec)' column c6 heading 'Est|Parse|Saved|Factor' column c7 heading 'Est|Object Hits' format 999,999,999 SELECT shared_pool_size_for_estimate c1, shared_pool_size_factor c2, estd_lc_size c3, estd_lc_memory_objects c4, estd_lc_time_saved c5, estd_lc_time_saved_factor c6, estd_lc_memory_object_hits c7 FROM v$shared_pool_advice; < pga_program_used_ram.sql col c1 heading 'Program|Name' format a30 col c2 heading 'PGA|Used|Memory' format 999,999,999 col c3 heading 'PGA|Allocated|Memory' format 999,999,999 col c4 heading 'PGA|Maximum|Memory' format 999,999,999 select program c1, pga_used_mem c2, pga_alloc_mem c3, pga_max_mem c4 from v$process order by c4 desc; column name format a40 column value format 999,999,999 select name, value from v$pgastat order by value desc; < pga_workareas.sql col c1 heading 'Workarea|Profile' format a35 col c2 heading 'Count' format 999,999,999 col c3 heading 'Percentage' format 99 select name c1, cnt c2, decode(total, 0, 0, round(cnt*100/total)) c3 from ( select name,value cnt,(sum(value) over ()) total from v$sysstat where name like 'workarea exec%' ); < show_pga_ram_details.sql select to_number(decode(SID, 65535, NULL, SID)) sid, operation_type OPERATION, trunc(WORK_AREA_SIZE/1024) WSIZE, trunc(EXPECTED_SIZE/1024) ESIZE, trunc(ACTUAL_MEM_USED/1024) MEM, trunc(MAX_MEM_USED/1024) "MAX MEM", number_passes PASS from v$sql_workarea_active order by 1,2; < track_hash_joins.sql select to_char( sn.begin_interval_time, 'yy-mm-dd hh24' ) snap_time, count(*) ct, sum(st.rows_processed_delta) row_ct, sum(st.disk_reads_delta) disk, sum(st.cpu_time_delta) cpu from dba_hist_snapshot sn, dba_hist_sqlstat st, dba_hist_sql_plan sp where st.snap_id = sn.snap_id and st.dbid = sn.dbid and st.instance_number = sn.instance_number and sp.sql_id = st.sql_id and sp.dbid = st.dbid and sp.plan_hash_value = st.plan_hash_value and sp.operation = 'HASH JOIN' group by to_char(sn.begin_interval_time,'yy-mm-dd hh24') having count(*) > &hash_thr; < show_ram_plan.sql select operation, options, object_name name, trunc(bytes/1024/1024) "input(MB)", trunc(last_memory_used/1024) last_mem, trunc(estimated_optimal_size/1024) opt_mem, trunc(estimated_onepass_size/1024) onepass_mem, decode(optimal_executions, null, null, optimal_executions||'/'||onepass_executions||'/'|| multipasses_exections) "O/1/M" from v$sql_plan p, v$sql_workarea w where p.address=w.address(+) and p.hash_value=w.hash_value(+) and p.id=w.operation_id(+) and p.address='88BB460C'; select * from v$system_event where event like ‘%wait%’; select p1 "File #". p2 "Block #", p3 "Reason Code" from v$session_wait where event = 'buffer busy waits'; select owner, segment_name, segment_type from dba_extents where file_id = &P1 and &P2 between block_id and block_id + blocks -1; select object_name, statistic_name, value from V$SEGMENT_STATISTICS where object_name = 'SOURCE$'; select h.p3 "Reason Code", h.time_waited "Time Waited", o.object_name "Object", f.file_name "Datafile", h.current_block# "Block Waited" from v$active_session_history h, dba_objects o, dba_data_files f where h.event = 'buffer busy waits' and h.current_obj# = o.object_id and h.current_file# = f.file_id and h.session_state = 'WAITING'; Chapter 15 < block_count.sql -- ************************************************* -- Copyright © 2005 by Rampant TechPress -- This script is free for non-commercial purposes -- with no warranties. Use at your own risk. -- -- To license this script for a commercial purpose, -- contact info@rampant.cc -- ************************************************* Set serveroutput on DECLARE v_unformatted_blocks number; v_unformatted_bytes number; v_fs1_blocks number; v_fs1_bytes number; v_fs2_blocks number; v_fs2_bytes number; v_fs3_blocks number; v_fs3_bytes number; v_fs4_blocks number; v_fs4_bytes number; v_full_blocks number; v_full_bytes number; BEGIN dbms_space.space_usage ('SYSTEM', 'TEST', 'TABLE', v_unformatted_blocks, v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes, v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes); dbms_output.put_line('Unformatted Blocks = '||v_unformatted_blocks); dbms_output.put_line('FS1 Blocks = '||v_fs1_blocks); dbms_output.put_line('FS2 Blocks = '||v_fs2_blocks); dbms_output.put_line('FS3 Blocks = '||v_fs3_blocks); dbms_output.put_line('FS4 Blocks = '||v_fs4_blocks); dbms_output.put_line('Full Blocks = '||v_full_blocks); end; The script yields the following output: Unformatted Blocks = 0 FS1 Blocks = 0 FS2 Blocks = 0 FS3 Blocks = 0 FS4 Blocks = 1 Full Blocks = 9 Where: FS1 means 0-25% free space within a block FS2 means 25-50% free space within a block FS3 means 50-75% free space within a block FS4 means 75-100% free space within a block < tsfrag.sql select tablespace_name, count(*) free_chunks, decode(round((max(bytes) / 1024000),2),null,0, round((max(bytes) / 1024000),2)) largest_chunk, nvl(round(sqrt(max(blocks)/sum(blocks))* (100/sqrt(sqrt(count(blocks)) )),2),0) fragmentation _index from sys.dba_free_space group by tablespace_name order by 2 desc, 1; < dffrag.sql select b.file_name, b.tablespace_name, nvl(round(sqrt(max(a.blocks)/ sum(a.blocks))*(100/sqrt(sqrt(count(a.blocks)) )),2),0) fragmentation_index, decode(c.inc,null,'no','yes') autoextend , count (*) free_chunks, decode ( round ((max (a.bytes) / 1024000), 2), null, 0, round ((max (a.bytes) / 1024000), 2)) largest_chunk from sys.dba_free_space a, sys.dba_data_files b, sys.filext$ c where b.tablespace_name = a.tablespace_name (+) and c.file# (+)= a.file_id and b.file_id = a.file_id (+) group by b.file_name, decode(c.inc,null,'no','yes'), b.tablespace_name order by 5 desc, 1; < tsmap.sql select 'free space' object_owner, ' ' object_type, ' ' object_name, file_id, block_id, bytes / 1024 size_kb, blocks from sys.dba_free_space where tablespace_name = '&tablespacename' union all select owner, segment_type, decode (partition_name,null,segment_name,segment_name || '.' || partition_name), file_id, block_id, bytes / 1024, blocks from sys.dba_extents where tablespace_name = '&tablespacename' order by 4,5; Chapter 16 < pctused.sql set heading off; set pages 9999; set feedback off; spool pctused.lst; define spare_rows = 2; define blksz = 8192; select ‘ alter table ‘||owner||’.’||table_name|| ‘ pctused ‘||least(round(100-((&spare_rows*avg_row_len)/(&blksz/10))),95)|| ‘ ‘|| ‘ pctfree ‘||greatest(round((&spare_rows*avg_row_len)/(&blksz/10)),5)|| ‘;’ from dba_tables where avg_row_len > 1 and avg_row_len < 2000 and table_name not in (select table_name from dba_tab_columns b where data_type in (‘RAW’,’LONG RAW’, ‘CLOB’, ‘BLOB’, ‘XMLTYPE’) ) order by owner, table_name ; spool off; column table_fetch_continued_row format 999,999,999 select to_char(snap_time,'yyyy-mm-dd hh24'), avg(newmem.value-oldmem.value)table_fetch_continued_row from perfstat.stats$sysstat oldmem, perfstat.stats$sysstat newmem, perfstat.stats$snapshot sn where snap_time > sysdate-&1 and newmem.snap_id = sn.snap_id and oldmem.snap_id = sn.snap_id-1 and oldmem.name = 'table fetch continued row' and newmem.name = 'table fetch continued row' and newmem.value-oldmem.value > 0 having avg(newmem.value-oldmem.value) > 10000 group by to_char(snap_time,'yyyy-mm-dd hh24'); < chained_row.sql -- ******************************************** -- This script relies on current CBO statistics -- ******************************************** spool chain.lst; set pages 9999; column c1 heading "Owner" format a9; column c2 heading "Table" format a12; column c3 heading "PCTFREE" format 99; column c4 heading "PCTUSED" format 99; column c5 heading "avg row" format 99,999; column c6 heading "Rows" format 999,999,999; column c7 heading "Chains" format 999,999,999; column c8 heading "Pct" format .99; set heading off; select 'Tables with migrated/chained rows and no BLOB columns.' from dual; set heading on; select owner c1, table_name c2, pct_free c3, pct_used c4, avg_row_len c5, num_rows c6, chain_cnt c7, chain_cnt/num_rows c8 from dba_tables where owner not in ('SYS','SYSTEM') and table_name not in (select table_name from dba_tab_columns where data_type in ('RAW','LONG RAW', 'BLOB', 'CLOB') ) and chain_cnt > 0 order by chain_cnt desc ; < awr_list_seg_block_space.sql -- ************************************************* -- Copyright © 2005 by Rampant TechPress -- This script is free for non-commercial purposes -- with no warranties. Use at your own risk. -- -- To license this script for a commercial purpose, -- contact info@rampant.cc -- ************************************************* drop type BlckFreeSpaceSet; drop type BlckFreeSpace; create type BlckFreeSpace as object ( seg_owner varchar2(30), seg_type varchar2(30), seg_name varchar2(100), fs1 number, fs2 number, fs3 number, fs4 number, fb number ); create type BlckFreeSpaceSet as table of BlckFreeSpace; create or replace function BlckFreeSpaceFunc (seg_owner IN varchar2, seg_type in varchar2 default null) return BlckFreeSpaceSet pipelined is outRec BlckFreeSpace := BlckFreeSpace(null,null,null,null,null,null,null,null); fs1_b number; fs2_b number; fs3_b number; fs4_b number; fs1_bl number; fs2_bl number; fs3_bl number; fs4_bl number; fulb number; fulbl number; u_b number; u_bl number; begin for rec in (select s.owner,s.segment_name,s.segment_type from dba_segments s where owner = seg_owner and segment_type = nvl(seg_type,segment_type) ) loop dbms_space.space_usage ( segment_owner => rec.owner, segment_name => rec.segment_name, segment_type => rec.segment_type, fs1_bytes => fs1_b, fs1_blocks => fs1_bl, fs2_bytes => fs2_b, fs2_blocks => fs2_bl, fs3_bytes => fs3_b, fs3_blocks => fs3_bl, fs4_bytes => fs4_b, fs4_blocks => fs4_bl, full_bytes => fulb, full_blocks => fulbl, unformatted_blocks => u_bl, unformatted_bytes => u_b ); outRec.seg_owner := rec.owner; outRec.seg_type := rec.segment_type; outRec.seg_name := rec.segment_name; outRec.fs1 := fs1_bl; outRec.fs2 := fs2_bl; outRec.fs3 := fs3_bl; outRec.fs4 := fs4_bl; outRec.fb := fulbl; Pipe Row (outRec); end loop; return; end; / col seg_owner heading 'Segment|Owner' format a10 col seg_type heading 'Segment|Type' format a10 col seg_name heading 'Segment|Name' format a30 col fs1 heading '0-25%|Free Space' format 9,999 col fs2 heading '25-50%|Free Space' format 9,999 col fs3 heading '50-75%|Free Space' format 9,999 col fs4 heading '75-100%|Free Space' format 9,999 col fb heading 'Full|Blocks' format 9,999 accept user_name prompt ‘Enter Segment Owner: ‘ break on seg_owner select * from Table ( BlckFreeSpaceFunc ('&user_name', 'TABLE' ) ) order by fs4 desc ; create cluster orders_cluster ( ordor_nbr number, ordor_date date, customer_nbr number sort ) hashkeys 10000 hash is ora_hash(customer_nbr) size 256; create table ordor ( ordor_nbr number, ordor_date date, customer_nbr number sort ) cluster ordor_cluster ( customer_nbr, transdate ); create table emp_ext ( empno number(4), ename varchar2(10), job varchar2(9), mgr number(4), hiredate date, sal number(7,2), comm number(7,2), deptno number(2) ) organization external ( type oracle_loader default directory testdir access parameters ( records delimited by newline fields terminated by ‘,’ ) location (‘emp_ext.csv’) ) reject limit 1000; ttitle 'Employee Salary|Cubic Rollup' col deptno heading 'Department|Number' col job heading 'Job|Title' col num_emps heading 'Number of|Employees' format 9,999 col sum_sal heading 'Total|Salary' format $99,999 select deptno, job, count(*) num_emps, sum(sal) sum_sal from emp_ext group by rollup ( deptno, job ); select bp.name pool_name, ob.name object, ob.subname sub_name, sum(buf_count) buffer_blocks from (select set_ds, obj, count(*) buf_count from x$bh group by set_ds, obj) bh, obj$ ob, x$kcbwds ws, v$buffer_pool bp where ob.dataobj# = bh.obj and ob.owner# > 0 and bh.set_ds = ws.addr and ws.set_id between bp.lo_setid and bp.hi_setid group by bp.name, ob.name, ob.subname order by bp.name, ob.name, ob.subname ; Chapter 17 col table_bytes format 999,999,999,999 col index_bytes format 999,999,999,999 select sum(bytes) table_bytes from user_segments where segment_type = 'TABLE'; select sum(bytes) index_bytes from user_segments where segment_type = 'INDEX'; < index_range_scans.sql col c1 heading ‘Object|Name’ format a30 col c2 heading ‘Option’ format a15 col c3 heading ‘Index|Usage|Count’ format 999,999 select p.object_name c1, p.options c2, count(1) c3 from dba_hist_sql_plan p, dba_hist_sqlstat s where p.object_owner <> 'SYS' and p.options like '%RANGE SCAN%' and p.operation like ‘%INDEX%’ and p.sql_id = s.sql_id group by p.object_name, p.operation, p.options order by 1,2,3; < busy_table_io.sql col c0 heading ‘Begin|Interval|time’ format a8 col c1 heading ‘Table|Name’ format a20 col c2 heading ‘Disk|Reads’ format 99,999,999 col c3 heading ‘Rows|Processed’ format 99,999,999 select * from ( select to_char(s.begin_interval_time,'mm-dd hh24') c0, p.object_name c1, sum(t.disk_reads_total) c2, sum(t.rows_processed_total) c3, DENSE_RANK() OVER (PARTITION BY to_char(s.begin_interval_time,'mm-dd hh24') ORDER BY SUM(t.disk_reads_total) desc) AS rnk from dba_hist_sql_plan p, dba_hist_sqlstat t, dba_hist_snapshot s where p.sql_id = t.sql_id and t.snap_id = s.snap_id and p.object_type like '%TABLE%' group by to_char(s.begin_interval_time,'mm-dd hh24'), p.object_name order by c0 desc, rnk ) where rnk <= 5 ; < count_table_access.sql col c1 heading ‘Object|Name’ format a30 col c2 heading ‘Operation’ format a15 col c3 heading ‘Option’ format a15 col c4 heading ‘Object|Count’ format 999,999 break on c1 skip 2 break on c2 skip 2 select p.object_name c1, p.operation c2, p.options c3, count(1) c4 from dba_hist_sql_plan p, dba_hist_sqlstat s where p.object_owner <> 'SYS' and p.sql_id = s.sql_id group by p.object_name, p.operation, p.options order by 1,2,3; Set heading off; Set pages 9999; Spool run_rebuild.sql; select 'alter index sapr3.'|| index_name|| ' rebuild tablespace '|| tablespace_name||';' from dba_indexes where owner = ‘SAPR3’; spool off; @run_rebuild The ALTER INDEX index_name REBUILD command is very safe way to rebuild indexes. Here is the syntax of the command: alter index index_name rebuild tablespace tablespace_name storage (initial new_initial next new_next freelists new_freelist_number ) create table emp_iot ( emp_id number, ename varchar2(20), sal number(9,2), deptno number, constraint pk_emp_iot_index primary key (emp_id) ) organization index tablespace demo_ts_01 pcthreshold 20 including ename ; < find_bif.sql set lines 2000; select sql_text, disk_reads, executions, parse_calls from v$sqlarea where lower(sql_text) like '% substr%' or lower(sql_text) like '% to_char%' or lower(sql_text) like '% decode%' order by disk_reads desc ; < awr_sql_index_access.sql col c1 heading ‘Begin|Interval|Time’ format a20 col c2 heading ‘Index|Range|Scans’ format 999,999 col c3 heading ‘Index|Unique|Scans’ format 999,999 col c4 heading ‘Index|Full|Scans’ format 999,999 select r.c1 c1, r.c2 c2, u.c2 c3, f.c2 c4 from ( select to_char(sn.begin_interval_time,'yy-mm-dd hh24') c1, count(1) c2 from dba_hist_sql_plan p, dba_hist_sqlstat s, dba_hist_snapshot sn where p.object_owner <> 'SYS' and p.operation like '%INDEX%' and p.options like '%RANGE%' and p.sql_id = s.sql_id and s.snap_id = sn.snap_id group by to_char(sn.begin_interval_time,'yy-mm-dd hh24') order by 1 ) r, ( select to_char(sn.begin_interval_time,'yy-mm-dd hh24') c1, count(1) c2 from dba_hist_sql_plan p, dba_hist_sqlstat s, dba_hist_snapshot sn where p.object_owner <> 'SYS' and p.operation like '%INDEX%' and p.options like '%UNIQUE%' and p.sql_id = s.sql_id and s.snap_id = sn.snap_id group by to_char(sn.begin_interval_time,'yy-mm-dd hh24') order by 1 ) u, ( select to_char(sn.begin_interval_time,'yy-mm-dd hh24') c1, count(1) c2 from dba_hist_sql_plan p, dba_hist_sqlstat s, dba_hist_snapshot sn where p.object_owner <> 'SYS' and p.operation like '%INDEX%' and p.options like '%FULL%' and p.sql_id = s.sql_id and s.snap_id = sn.snap_id group by to_char(sn.begin_interval_time,'yy-mm-dd hh24') order by 1 ) f where r.c1 = u.c1 and r.c1 = f.c1 ; < awr_count_index_details.sql col c1 heading ‘Begin|Interval|time’ format a20 col c2 heading ‘Search Columns’ format 999 col c3 heading ‘Invocation|Count’ format 99,999,999 break on c1 skip 2 accept idxname char prompt ‘Enter Index Name: ‘ ttitle ‘Invocation Counts for index|&idxname’ select to_char(sn.begin_interval_time,'yy-mm-dd hh24') c1, p.search_columns c2, count(*) c3 from dba_hist_snapshot sn, dba_hist_sql_plan p, dba_hist_sqlstat st where st.sql_id = p.sql_id and sn.snap_id = st.snap_id and p.object_name = ‘&idxname' group by begin_interval_time,search_columns; < index_range_scans.sql col c1 heading ‘Object|Name’ format a30 col c2 heading ‘Option’ format a15 col c3 heading ‘Index|Usage|Count’ format 999,999 select p.object_name c1, p.options c2, count(1) c3 from dba_hist_sql_plan p, dba_hist_sqlstat s where p.object_owner <> 'SYS' and p.options like '%RANGE SCAN%' and p.operation like ‘%INDEX%’ and p.sql_id = s.sql_id group by p.object_name, p.operation, p.options order by 1,2,3; < busy_table_io.sql col c0 heading ‘Begin|Interval|time’ format a8 col c1 heading ‘Table|Name’ format a20 col c2 heading ‘Disk|Reads’ format 99,999,999 col c3 heading ‘Rows|Processed’ format 99,999,999 select * from ( select to_char(s.begin_interval_time,'mm-dd hh24') c0, p.object_name c1, sum(t.disk_reads_total) c2, sum(t.rows_processed_total) c3, DENSE_RANK() OVER (PARTITION BY to_char(s.begin_interval_time,'mm-dd hh24') ORDER BY SUM(t.disk_reads_total) desc) AS rnk from dba_hist_sql_plan p, dba_hist_sqlstat t, dba_hist_snapshot s where p.sql_id = t.sql_id and t.snap_id = s.snap_id and p.object_type like '%TABLE%' group by to_char(s.begin_interval_time,'mm-dd hh24'), p.object_name order by c0 desc, rnk ) where rnk <= 5 ; < count_table_access.sql col c1 heading ‘Object|Name’ format a30 col c2 heading ‘Operation’ format a15 col c3 heading ‘Option’ format a15 col c4 heading ‘Object|Count’ format 999,999 break on c1 skip 2 break on c2 skip 2 select p.object_name c1, p.operation c2, p.options c3, count(1) c4 from dba_hist_sql_plan p, dba_hist_sqlstat s where p.object_owner <> 'SYS' and p.sql_id = s.sql_id group by p.object_name, p.operation, p.options order by 1,2,3; < awr_sql_index_freq.sql col c1 heading ‘Object|Name’ format a30 col c2 heading ‘Operation’ format a15 col c3 heading ‘Option’ format a15 col c4 heading ‘Index|Usage|Count’ format 999,999 break on c1 skip 2 break on c2 skip 2 select p.object_name c1, p.operation c2, p.options c3, count(1) c4 from dba_hist_sql_plan p, dba_hist_sqlstat s where p.object_owner <> 'SYS' and p.operation like ‘%INDEX%’ and p.sql_id = s.sql_id group by p.object_name, p.operation, p.options order by 1,2,3; < awr_access_counts.sql ttile ‘Table Access|Operation Counts|Per Snapshot Period’ col c1 heading ‘Begin|Interval|time’ format a20 col c2 heading ‘Operation’ format a15 col c3 heading ‘Option’ format a15 col c4 heading ‘Object|Count’ format 999,999 break on c1 skip 2 break on c2 skip 2 select to_char(sn.begin_interval_time,'yy-mm-dd hh24') c1, p.operation c2, p.options c3, count(1) c4 from dba_hist_sql_plan p, dba_hist_sqlstat s, dba_hist_snapshot sn where p.object_owner <> 'SYS' and p.sql_id = s.sql_id and s.snap_id = sn.snap_id group by to_char(sn.begin_interval_time,'yy-mm-dd hh24'), p.operation, p.options order by 1,2,3; < awr_sql_index_access.sql col c1 heading ‘Begin|Interval|Time’ format a20 col c2 heading ‘Index|Range|Scans’ format 999,999 col c3 heading ‘Index|Unique|Scans’ format 999,999 col c4 heading ‘Index|Full|Scans’ format 999,999 select r.c1 c1, r.c2 c2, u.c2 c3, f.c2 c4 from ( select to_char(sn.begin_interval_time,'yy-mm-dd hh24') c1, count(1) c2 from dba_hist_sql_plan p, dba_hist_sqlstat s, dba_hist_snapshot sn where p.object_owner <> 'SYS' and p.operation like '%INDEX%' and p.options like '%RANGE%' and p.sql_id = s.sql_id and s.snap_id = sn.snap_id group by to_char(sn.begin_interval_time,'yy-mm-dd hh24') order by 1 ) r, ( select to_char(sn.begin_interval_time,'yy-mm-dd hh24') c1, count(1) c2 from dba_hist_sql_plan p, dba_hist_sqlstat s, dba_hist_snapshot sn where p.object_owner <> 'SYS' and p.operation like '%INDEX%' and p.options like '%UNIQUE%' and p.sql_id = s.sql_id and s.snap_id = sn.snap_id group by to_char(sn.begin_interval_time,'yy-mm-dd hh24') order by 1 ) u, ( select to_char(sn.begin_interval_time,'yy-mm-dd hh24') c1, count(1) c2 from dba_hist_sql_plan p, dba_hist_sqlstat s, dba_hist_snapshot sn where p.object_owner <> 'SYS' and p.operation like '%INDEX%' and p.options like '%FULL%' and p.sql_id = s.sql_id and s.snap_id = sn.snap_id group by to_char(sn.begin_interval_time,'yy-mm-dd hh24') order by 1 ) f where r.c1 = u.c1 and r.c1 = f.c1 ; < awr_sql_full_scans.sql -- ************************************************* -- Copyright © 2005 by Rampant TechPress -- This script is free for non-commercial purposes -- with no warranties. Use at your own risk. -- -- To license this script for a commercial purpose, -- contact info@rampant.cc -- ************************************************* col c1 heading ‘Begin|Interval|Time’ format a20 col c2 heading ‘Index|Table|Scans’ format 999,999 col c3 heading ‘Full|Table|Scans’ format 999,999 select i.c1 c1, i.c2 c2, f.c2 c3 from ( select to_char(sn.begin_interval_time,'yy-mm-dd hh24') c1, count(1) c2 from dba_hist_sql_plan p, dba_hist_sqlstat s, dba_hist_snapshot sn where p.object_owner <> 'SYS' and p.operation like '%TABLE ACCESS%' and p.options like '%INDEX%' and p.sql_id = s.sql_id and s.snap_id = sn.snap_id group by to_char(sn.begin_interval_time,'yy-mm-dd hh24') order by 1 ) i, ( select to_char(sn.begin_interval_time,'yy-mm-dd hh24') c1, count(1) c2 from dba_hist_sql_plan p, dba_hist_sqlstat s, dba_hist_snapshot sn where p.object_owner <> 'SYS' and p.operation like '%TABLE ACCESS%' and p.options = 'FULL' and p.sql_id = s.sql_id and s.snap_id = sn.snap_id group by to_char(sn.begin_interval_time,'yy-mm-dd hh24') order by 1 ) f where i.c1 = f.c1 ; < find_full_scans.sql To download this script, please do a web search for “plan9i.sql” < get_sql.sql set lines 2000; select sql_text, disk_reads, executions, parse_calls from v$sqlarea where lower(sql_text) like '% page %' order by disk_reads desc ; < get_sub_optimal_cached_sql.sql set linesize 80 pagesize 80 trimspool on ttitle "Top 10 Expensive SQL | Consistent Gets per Rows Fetched" column sql_id heading "SQL ID" column c2 heading "Avg Gets per Row" column c3 heading "Total Gets" column c4 heading "Total Rows" select * from (select sq.sql_id, round(sum(buffer_gets_delta) / decode(sum(rows_processed_delta), 0, 1, sum(rows_processed_delta))) c2, sum(buffer_gets_delta) c3, sum(rows_processed_delta) c4 from dba_hist_snapshot sn, dba_hist_sqlstat sq, dba_hist_sqltext st where sn.snap_id = sq.snap_id and sn.dbid = sq.dbid and sn.instance_number = sq.instance_number and sn.dbid = st.dbid and sq.sql_id = st.sql_id and lower(sql_text) not like '%sum(%' and lower(sql_text) not like '%min(%' and lower(sql_text) not like '%max(%' and lower(sql_text) not like '%avg(%' and lower(sql_text) not like '%count(%' and sn.snap_id between &beginsnap and &endsnap and sq.parsing_schema_name not in ('SYS', 'SYSMAN', 'SYSTEM', 'MDSYS', 'WMSYS', 'TSMSYS', 'DBSNMP', 'OUTLN') group by sq.sql_id order by 2 desc) where rownum < 11 / < awr_expensive_sql.sql set linesize 80 pagesize 80 trimspool on ttitle "Top 10 Expensive SQL | Disk Reads per Rows Fetched" column sql_id heading "SQL ID" column c2 heading "Avg Reads per Row" column c3 heading "Total Reads" column c4 heading "Total Rows" select * from (select sq.sql_id, round(sum(disk_reads_delta) / decode(sum(rows_processed_delta), 0, 1, sum(rows_processed_delta))) c2, sum(disk_reads_delta) c3, sum(rows_processed_delta) c4 from dba_hist_snapshot sn, dba_hist_sqlstat sq, dba_hist_sqltext st where sn.snap_id = sq.snap_id and sn.dbid = sq.dbid and sn.instance_number = sq.instance_number and sn.dbid = st.dbid and sq.sql_id = st.sql_id and lower(sql_text) not like '%sum(%' and lower(sql_text) not like '%min(%' and lower(sql_text) not like '%max(%' and lower(sql_text) not like '%avg(%' and lower(sql_text) not like '%count(%' and sn.snap_id between &beginsnap and &endsnap and sq.parsing_schema_name not in ('SYS', 'SYSMAN', 'SYSTEM', 'MDSYS', 'WMSYS', 'TSMSYS', 'DBSNMP', 'OUTLN') group by sq.sql_id order by 2 desc) where rownum < 11 / < statspack_unused_indexes.sql ttitle "Unused Indexes by Time Period" col owner heading "Index Owner" format a30 col index_name heading "Index Name" format a30 set linesize 95 trimspool on pagesize 80 select * from (select owner, index_name from dba_indexes di where di.index_type != 'LOB' and owner not in ('SYS', 'SYSMAN', 'SYSTEM', 'MDSYS', 'WMSYS', 'TSMSYS', 'DBSNMP', 'OUTLN') minus select index_owner owner, index_name from dba_constraints dc where index_owner not in ('SYS', 'SYSMAN', 'SYSTEM', 'MDSYS', 'WMSYS', 'TSMSYS', 'DBSNMP', 'OUTLN') minus select p.object_owner owner, p.object_name index_name from stats$snapshot sn, stats$sql_plan p, stats$sql_summary st, stats$sql_plan_usage spu where st.sql_id = spu.sql_id and spu.plan_hash_value = p.plan_hash_value and st.hash_value = p.plan_hash_value and sn.snap_id = st.snap_id and sn.dbid = st.dbid and sn.instance_number = st.instance_number and sn.snap_id = spu.snap_id and sn.dbid = spu.snap_id and sn.instance_number = spu.instance_number and sn.snap_id between &begin_snap and &end_snap and p.object_type = 'INDEX' ) where owner not in ('SYS', 'SYSMAN', 'SYSTEM', 'MDSYS', 'WMSYS', 'TSMSYS', 'DBSNMP', 'OUTLN') order by 1, 2 / < awr_unused_indexes.sql ttitle "Unused Indexes by Time Period" col owner heading "Index Owner" format a30 col index_name heading "Index Name" format a30 set linesize 95 trimspool on pagesize 80 select * from (select owner, index_name from dba_indexes di where di.index_type != 'LOB' minus select index_owner owner, index_name from dba_constraints dc minus select p.object_owner owner, p.object_name index_name from dba_hist_snapshot sn, dba_hist_sql_plan p, dba_hist_sqlstat st where st.sql_id = p.sql_id and sn.snap_id = st.snap_id and sn.dbid = st.dbid and sn.instance_number = st.instance_number and sn.snap_id between &begin_snap and &end_snap and p.object_type = 'INDEX' ) where owner not in ('SYS', 'SYSMAN', 'SYSTEM', 'MDSYS', 'WMSYS', 'TSMSYS', 'DBSNMP', 'OUTLN') order by 1, 2 / < awr_infrequent_indexes.sql ttitle "Infrequently-used indexes by month" col c1 heading "Month" format a20 col c2 heading "Index Owner" format a30 col c3 heading "Index Name" format a30 col c4 heading "Invocation|Count" format 99 set linesize 95 trimspool on pagesize 80 select to_char(sn.begin_interval_time,'Month') c1, p.object_owner c2, p.object_name c3, sum(executions_delta) c4 from dba_hist_snapshot sn, dba_hist_sql_plan p, dba_hist_sqlstat st where st.sql_id = p.sql_id and sn.snap_id = st.snap_id and sn.dbid = st.dbid and sn.instance_number = st.instance_number and p.object_type = 'INDEX' and p.object_owner not in ('SYS', 'SYSMAN', 'SYSTEM', 'MDSYS', 'WMSYS', 'TSMSYS', 'DBSNMP') group by to_char(sn.begin_interval_time, 'Month'), p.object_owner, p.object_name having sum(executions_delta) < 50 order by 1, 4 desc, 2, 3 / < Find_duplicate_index_columns.sql set linesize 150 trimspool on pagesize 80 column index_owner format a20 column column_name format a30 column position format 9 column nextcol format a18 heading "Next Column Match?" select a.index_owner, a.column_name, a.index_name index_name1, b.index_name index_name2, a.column_position position, (select 'YES' from dba_ind_columns x, dba_ind_columns y where x.index_owner = a.index_owner and y.index_owner = b.index_owner and x.index_name = a.index_name and y.index_name = b.index_name and x.column_position = 2 and y.column_position = 2 and x.column_name = y.column_name) nextcol from dba_ind_columns a, dba_ind_columns b where a.index_owner not in ('SYS', 'SYSMAN', 'SYSTEM', 'MDSYS', 'WMSYS', 'TSMSYS', 'DBSNMP') and a.index_owner = b.index_owner and a.column_name = b.column_name and a.table_name = b.table_name and a.index_name != b.index_name and a.column_position = 1 and b.column_position = 1; < display_composite_index_columns.sql -- ******************************* -- Display composite indexes -- ******************************* break on index_name skip 1 col index_name format a30 col column_name format a30 select index_name, column_position, column_name from user_ind_columns i where (select count(*) from user_ind_columns u where u.index_name = i.index_name) > 1 group by index_name, column_position, column_name order by index_name, column_position; < display_composite_index_overall_usage.sql -- ********************************* -- Are composite indexes being used? -- ********************************* select to_char(sn.begin_interval_time,'yyyy-mm') c1, p.object_name c2, p.search_columns c3, count(*) from dba_hist_snapshot sn, dba_hist_sql_plan p, dba_hist_sqlstat st where -- ************************************************** -- Only show data for multi-column composite indexes -- ************************************************** (select count(*) from user_ind_columns u where u.index_name = p.object_name) > 1 and st.sql_id = p.sql_id and sn.snap_id = st.snap_id and p.object_owner = 'PUBS' and -- ************************************************ -- This is supposed to just show the last 30 days -- ************************************************ sysdate > sysdate - 30 group by begin_interval_time, object_name, search_columns; Once we see if composite indexes are being used, we can drill-down an look at a list of all multi-column indexes. -- *********************************************** -- Show frequency of use of multi-column indexes -- *********************************************** with composite_index_name_list as (select i.index_name from user_ind_columns i where (select count(*) from user_ind_columns u where u.index_name = i.index_name) > 1 group by index_name order by index_name) select to_char(sn.begin_interval_time,'yyyy-mm') c1, p.object_name c2, p.search_columns c3, count(*) from dba_hist_snapshot sn, dba_hist_sql_plan p, dba_hist_sqlstat st, composite_index_name_list i where -- **************************************************** -- Only show multi-column indexes from the WITH clause -- **************************************************** i.index_name = p.object_name and st.sql_id = p.sql_id and sn.snap_id = st.snap_id and p.object_owner = 'PUBS' and -- ************************************************ -- only for the last 30 days . . . -- ************************************************ sysdate > sysdate - 30 group by begin_interval_time, object_name, search_columns; < display_composite_index_columns_invocation.sql col c1 heading ‘Begin|Interval|time’ format a20 col c2 heading ‘Search Columns’ format 999 col c3 heading ‘Invocation|Count’ format 99,999,999 break on c1 skip 2 accept idxname char prompt ‘Enter Index Name: ‘ ttitle ‘Invocation Counts for index|&idxname’ select to_char(sn.begin_interval_time,'yy-mm-dd hh24') c1, p.search_columns c2, count(*) c3 from dba_hist_snapshot sn, dba_hist_sql_plan p, dba_hist_sqlstat st where st.sql_id = p.sql_id and sn.snap_id = st.snap_id and p.object_name = ‘&idxname' group by begin_interval_time,search_columns; < find_sparse_indexes.sql select ‘exec analyzedb.reorg_a_table4(’||””||rtrim(t.table_owner)||””||’,'||””|| rtrim(t.table_name)||””||’);’, t.table_owner||’.'||t.table_name name, a.num_rows, sum(t.inserts) ins, sum(t.updates) upd, sum(t.deletes) del, sum(t.updates)+sum(t.inserts)+sum(t.deletes) tot_chgs, to_char((sum(t.deletes)/(decode(a.num_rows,0,1,a.num_rows)))*100.0,’999999.99′) per_del, round(((sum(t.updates)+sum(t.inserts)+sum(t.deletes))/(decode(a.num_rows,0,1,a.num_rows)) *100.0),2) per_chg from analyzedb.table_modifications t, all_tables a where t.timestamp >= to_date(’&from_date’,'dd-mon-yyyy’) and t.table_owner = a.owner and t.table_owner not in (’SYS’,'SYSTEM’) and t.table_name=a.table_name having (sum(t.deletes)/(decode(a.num_rows,0,1,a.num_rows)))*100.0 >=5 group by t.table_owner, t.table_name, a.num_rows order by num_rows desc, t.table_owner, t.table_name; select sum(a.time_waited_micro)/sum(a.total_waits)/1000000 c1, sum(b.time_waited_micro)/sum(b.total_waits)/1000000 c2, ( sum(a.total_waits) / sum(a.total_waits + b.total_waits) ) * 100 c3, ( sum(b.total_waits) / sum(a.total_waits + b.total_waits) ) * 100 c4, ( sum(b.time_waited_micro) / sum(b.total_waits)) / (sum(a.time_waited_micro)/sum(a.total_waits) ) * 100 c5 from dba_hist_system_event a, dba_hist_system_event b where a.snap_id = b.snap_id and a.event_name = 'db file scattered read' and b.event_name = 'db file sequential read'; Chapter 18 < session.sql script rem session.sql - displays all connected sessions set echo off; set termout on; set linesize 80; set pagesize 60; set newpage 0; select rpad(c.name||':',11)||rpad(' current logons='|| (to_number(b.sessions_current)),20)||'cumulative logons='|| rpad(substr(a.value,1,10),10)||'highwater mark='|| b.sessions_highwater Information from v$sysstat a, v$license b, v$database c where a.name = 'logons cumulative' ; ttitle "dbname Database|UNIX/Oracle Sessions"; set heading off; select 'Sessions on database '||substr(name,1,8) from v$database; set heading on; select substr(a.spid,1,9) pid, substr(b.sid,1,5) sid, substr(b.serial#,1,5) ser#, substr(b.machine,1,6) box, substr(b.username,1,10) username, -- b.server, substr(b.osuser,1,8) os_user, substr(b.program,1,30) program from v$session b, v$process a where b.paddr = a.addr and type='USER' order by spid; ttitle off; set heading off; select 'To kill, enter SQLPLUS> ALTER SYSTEM KILL SESSION', ''''||'SID, SER#'||''''||';' from dual; spool off; < awr_quick.ksh (partial) -- ************************************************* -- Copyright © 2005 by Rampant TechPress -- This script is free for non-commercial purposes -- with no warranties. Use at your own risk. -- -- To license this script for a commercial purpose, -- contact info@rampant.cc -- ************************************************* spool rpt_last.lst set pages 9999; set feedback on; set verify off; column reads format 999,999,999 column writes format 999,999,999 select to_char(sn.end_interval_time,'yyyy-mm-dd HH24'), (newreads.value-oldreads.value) reads, (newwrites.value-oldwrites.value) writes from dba_hist_sysstat oldreads, dba_hist_sysstat newreads, dba_hist_sysstat oldwrites, dba_hist_sysstat newwrites, dba_hist_snapshot sn where newreads.snap_id = (select max(sn.snap_id) from dba_hist_snapshot) and newwrites.snap_id = (select max(sn.snap_id) from dba_hist_snapshot) and oldreads.snap_id = sn.snap_id-1 and oldwrites.snap_id = sn.snap_id-1 and oldreads.stat_name = 'physical reads' and newreads.stat_name = 'physical reads' and oldwrites.stat_name = 'physical writes' and newwrites.stat_name = 'physical writes' ; prompt *********************************************************** prompt This will identify any single file who's read I/O prompt is more than 10% of the total read I/O of the database. prompt prompt The "hot" file should be examined, and the hot table/index prompt should be identified using STATSPACK. prompt prompt - The busy file should be placed on a disk device with prompt "less busy" files to minimize read delay and channel prompt contention. prompt prompt - If small file has a hot small table, place the table prompt in the KEEP pool prompt prompt - If the file has a large-table full-table scan, place prompt the table in the RECYCLE pool and turn on parallel query prompt for the table. prompt *********************************************************** column mydate format a16 column file_name format a40 column reads format 999,999,999 select to_char(sn.end_interval_time,'yyyy-mm-dd HH24') mydate, new.filename file_name, new.phyrds-old.phyrds reads from dba_hist_filestatxs old, dba_hist_filestatxs new, dba_hist_snapshot snwhere sn.snap_id = (select max(snap_id) from dba_hist_snapshot) and new.snap_id = sn.snap_id and old.snap_id = sn.snap_id-1 and new.filename = old.filename --and -- new.phyrds-old.phyrds > 10000 and (new.phyrds-old.phyrds)*10 > ( select (newreads.value-oldreads.value) reads from dba_hist_sysstat oldreads, dba_hist_sysstat newreads, dba_hist_snapshot sn1 where sn.snap_id = sn1.snap_id and newreads.snap_id = sn.snap_id and oldreads.snap_id = sn.snap_id-1 and oldreads.stat_name = 'physical reads' and newreads.stat_name = 'physical reads' and (newreads.value-oldreads.value) > 0) ; prompt *********************************************************** prompt This will identify any single file who's write I/O prompt is more than 10% of the total write I/O of the database. prompt prompt The "hot" file should be examined, and the hot table/index prompt should be identified using STATSPACK. prompt prompt - The busy file should be placed on a disk device with prompt "less busy" files to minimize write delay and channel prompt channel contention. prompt prompt - If small file has a hot small table, place the table prompt in the KEEP pool prompt prompt *********************************************************** select 100 - 100 * (round((sum (decode (name, 'physical reads', value, 0)) - sum (decode (name, 'physical reads direct', value, 0))) / (sum (decode (name, 'db block gets', value, 1)) + sum (decode (name, 'consistent gets', value, 0))),3)) from sys.v_$sysstat where name in ('db block gets', 'consistent gets', 'physical reads', 'physical reads direct') < syswaits.sql -- ************************************************* -- Copyright © 2005 by Rampant TechPress -- This script is free for non-commercial purposes -- with no warranties. Use at your own risk. -- -- To license this script for a commercial purpose, -- contact info@rampant.cc -- ************************************************* select event, total_waits, round(100 * (total_waits / sum_waits),2) pct_tot_waits, time_wait_sec, round(100 * (time_wait_sec / sum_secs),2) pct_secs_waits, total_timeouts, avg_wait_sec from (select event, total_waits, round((time_waited / 100),2) time_wait_sec, total_timeouts, round((average_wait / 100),2) avg_wait_sec from sys.v_$system_event where event not in ('lock element cleanup ', 'pmon timer ', 'rdbms ipc message ', 'smon timer ', 'SQL*Net message from client ', 'SQL*Net break/reset to client ', 'SQL*Net message to client ', 'SQL*Net more data to client ', 'dispatcher timer ', 'Null event ', 'parallel query dequeue wait ', 'parallel query idle wait - Slaves ', 'pipe get ', 'PL/SQL lock timer ', 'slave wait ', 'virtual circuit status ', 'WMON goes to sleep') and event not like 'DFS%' and event not like 'KXFX%'), (select sum(total_waits) sum_waits, sum(round((time_waited / 100),2)) sum_secs from sys.v_$system_event where event not in ('lock element cleanup ', 'pmon timer ', 'rdbms ipc message ', 'smon timer ', 'SQL*Net message from client ', 'SQL*Net break/reset to client ', 'SQL*Net message to client ', 'SQL*Net more data to client ', 'dispatcher timer ', 'Null event ', 'parallel query dequeue wait ', 'parallel query idle wait - Slaves ', 'pipe get ', 'PL/SQL lock timer ', 'slave wait ', 'virtual circuit status ', 'WMON goes to sleep') and event not like 'DFS%' and event not like 'KXFX%') order by 2 desc; < sesswaits.sql select b.sid, decode(b.username,NULL,c.name,b.username) process_name, event, a.a.total_waits, round((a.time_waited / 100),2) time_wait_sec,a.total_timeouts, round((average_wait / 100),2) average_wait_sec, round((a.max_wait / 100),2) max_wait_sec from sys.v_$session_event a, sys.v_$session b, sys.v_$bgprocess c where a.event not in ('lock element cleanup ', 'pmon timer ', 'rdbms ipc message ', 'smon timer ', 'SQL*Net message from client ', 'SQL*Net break/reset to client ', 'SQL*Net message to client ', 'SQL*Net more data to client ', 'dispatcher timer ', 'Null event ', 'parallel query dequeue wait ', 'parallel query idle wait - Slaves ', 'pipe get ', 'PL/SQL lock timer ', 'slave wait ', 'virtual circuit status ', 'WMON goes to sleep' ) and a.event not like 'DFS%' and a.event not like 'KXFX%' and a.sid = b.sid and b.paddr = c.paddr (+) order by 4 desc; < csesswaits.sql select a.sid, decode(b.username,null,c.name,b.username) process_name, a.event, a.seconds_in_wait, a.wait_time, a.state, a.p1text, a.p1, a.p1raw, a.p2text, a.p2, a.p2raw, a.p3text, a.p3, a.p3raw from sys.v_$session_wait a, sys.v_$session b, sys.v_$bgprocess c where a.event not in ('lock element cleanup ', 'pmon timer ', 'rdbms ipc message ', 'smon timer ', 'SQL*Net message from client ', 'SQL*Net break/reset to client ', 'SQL*Net message to client ', 'SQL*Net more data to client ', 'dispatcher timer ', 'Null event ', 'parallel query dequeue wait ', 'parallel query idle wait - Slaves ', 'pipe get ', 'PL/SQL lock timer ', 'slave wait ', 'virtual circuit status ', 'WMON goes to sleep' ) and a.event not like 'DFS%' and a.event not like 'KXFX%' and a.sid = b.sid and b.paddr = c.paddr (+) order by 4 desc; < objwaits.sql select sid, username, machine, program, b.owner, b.object_type, b.object_name, c.file_name from sys.v_$session a, sys.dba_objects b, sys.dba_data_files c where a.row_wait_obj# > 0 and a.row_wait_obj# = b.object_id and a.row_wait_file# = c.file_id order by sid; < spacesum.sql select tablespace_name, autoextend , round ((total_space / 1024 / 1024), 2) as total_space, round ((total_free_space / 1024 / 1024), 2) as total_free, round (((total_space - total_free_space) / 1024 / 1024), 2) as used_space, to_char ( nvl ( round ( (100 * sum_free_blocks / sum_alloc_blocks),2),0)) || '%' as pct_free from (select tablespace_name, max (autoextensible) autoextend, sum (blocks) sum_alloc_blocks, sum (bytes) as total_space from dba_data_files group by tablespace_name), (select b.tablespace_name fs_ts_name, nvl (sum (bytes), 0) as total_free_space, sum (blocks) as sum_free_blocks from dba_free_space a, dba_tablespaces b where a.tablespace_name (+) = b.tablespace_name group by b.tablespace_name, status) where tablespace_name = fs_ts_name union all select d.tablespace_name, autoextend, round ((a.bytes / 1024 / 1024), 2), round ((a.bytes / 1024 / 1024) - (nvl (t.bytes, 0) / 1024 / 1024), 2), round (nvl (t.bytes, 0) / 1024 / 1024, 2), to_char (100 - (nvl (t.bytes / a.bytes * 100, 0)), '990.00') from sys.dba_tablespaces d, (select tablespace_name, max (autoextensible) autoextend, sum (bytes) bytes from dba_temp_files group by tablespace_name) a, (select tablespace_name, sum (bytes_cached) bytes from sys.v_$temp_extent_pool group by tablespace_name) t where d.tablespace_name = a.tablespace_name (+) and d.tablespace_name = t.tablespace_name (+) and d.extent_management like 'LOCAL' and d.contents like 'TEMPORARY' order by 1; < datafileae.sql select b.file_name, b.tablespace_name, decode(c.inc,null,'no','yes') autoextend from sys.dba_data_files b, sys.filext$ c where c.file# (+)= b.file_id order by 2, 1; < maxext.sql select owner, decode(partition_name,NULL,segment_name,segment_name || '.' || partition_name) segment_name, segment_type, extents, max_extents, initial_extent, next_extent, tablespace_name from sys.dba_segments where max_extents - extents <= 5 and segment_type <> 'CACHE' order by 1,2,3; < objdef.sql select a.owner, a.segment_name, a.segment_type, a.tablespace_name, a.next_extent, max(c.bytes) max_contig_space from sys.dba_segments a, sys.dba_free_space c where a.tablespace_name = c.tablespace_name and a.next_extent > (select max(bytes) from sys.dba_free_space b where a.tablespace_name = b.tablespace_name and b.tablespace_name = c.tablespace_name) group by a.owner, a.segment_name, a.tablespace_name, a.segment_type, a.next_extent; < tabreorg.sql select /*+ RULE */ owner, segment_name table_name, segment_type, round(bytes/1024,2) table_kb, num_rows, blocks, empty_blocks, hwm highwater_mark, avg_used_blocks, greatest(round(100 * (nvl(hwm - avg_used_blocks,0) / greatest(nvl(hwm,1),1) ),2),0) block_inefficiency, chain_pct, max_extent_pct, extents, max_extents, decode(greatest(max_free_space - next_extent,0),0,'n','y') can_extend_space, next_extent, max_free_space, o_tablespace_name tablespace_name from (select a.owner owner, segment_name, segment_type, bytes, num_rows, a.blocks blocks, b.empty_blocks empty_blocks, a.blocks - b.empty_blocks - 1 hwm, decode(round((b.avg_row_len * num_rows * (1 + (pct_free/100))) / c.blocksize,0),0,1,round((b.avg_row_len * num_rows * (1 + (pct_free/100))) / c.blocksize,0)) + 2 avg_used_blocks, round(100 * (nvl(b.chain_cnt,0) / greatest(nvl(b.num_rows,1),1)),2) chain_pct, a.extents extents, round(100 * (a.extents / a.max_extents),2) max_extent_pct, a.max_extents max_extents, b.next_extent next_extent, b.tablespace_name o_tablespace_name from sys.dba_segments a, sys.dba_all_tables b, sys.ts$ c where ( a.owner = b.owner ) and ( segment_name = table_name ) and ( ( segment_type = 'TABLE' ) ) and b.tablespace_name = c.name union all select a.owner owner, segment_name || '.' || b.partition_name, segment_type, bytes, b.num_rows, a.blocks blocks, b.empty_blocks empty_blocks, a.blocks - b.empty_blocks - 1 hwm, decode(round((b.avg_row_len * b.num_rows * (1 + (b.pct_free/100))) / c.blocksize,0),0,1,round((b.avg_row_len * b.num_rows * (1 + (b.pct_free/100))) / c.blocksize,0)) + 2 avg_used_blocks, round(100 * (nvl(b.chain_cnt,0) / greatest(nvl(b.num_rows,1),1)),2) chain_pct, a.extents extents, round(100 * (a.extents / a.max_extents),2) max_extent_pct, a.max_extents max_extents, b.next_extent, b.tablespace_name o_tablespace_name from sys.dba_segments a, sys.dba_tab_partitions b, sys.ts$ c, sys.dba_tables d where ( a.owner = b.table_owner ) and ( segment_name = b.table_name ) and ( ( segment_type = 'TABLE PARTITION' ) ) and b.tablespace_name = c.name and d.owner = b.table_owner and d.table_name = b.table_name and a.partition_name = b.partition_name), ( select tablespace_name f_tablespace_name, max(bytes) max_free_space from sys.dba_free_space group by tablespace_name) where f_tablespace_name = o_tablespace_name and greatest(round(100 * (nvl(hwm - avg_used_blocks,0) / greatest(nvl(hwm,1),1) ),2),0) > 25 order by 10 desc, 1 asc,2 asc; < idxreorg.sql select /*+ RULE */ owner, segment_name index_name, segment_type, round(bytes/1024,2) index_kb, num_rows, clustering_factor, blevel, blocks, max_extent_pct, extents, max_extents, decode(greatest(max_free_space - next_extent,0),0,'n','y') can_extend_space, next_extent, max_free_space, o_tablespace_name from (select a.owner owner, segment_name, segment_type, bytes, num_rows, b.clustering_factor, b.blevel, a.blocks blocks, a.extents extents, round(100 * (a.extents / a.max_extents),2) max_extent_pct, a.max_extents max_extents, b.next_extent next_extent, b.tablespace_name o_tablespace_name from sys.dba_segments a, sys.dba_indexes b, sys.ts$ c where ( a.owner = b.owner ) and ( segment_name = index_name ) and ( ( segment_type = 'INDEX' ) ) and b.tablespace_name = c.name union all select a.owner owner, segment_name || '.' || b.partition_name, segment_type, bytes, b.num_rows, b.clustering_factor, b.blevel, a.blocks blocks, a.extents extents, round(100 * (a.extents / a.max_extents),2) max_extent_pct, a.max_extents max_extents, b.next_extent, b.tablespace_name o_tablespace_name from sys.dba_segments a, sys.dba_ind_partitions b, sys.ts$ c, sys.dba_indexes d where ( a.owner = b.index_owner ) and ( segment_name = b.index_name ) and ( ( segment_type = 'INDEX PARTITION' ) ) and b.tablespace_name = c.name and d.owner = b.index_owner and d.index_name = b.index_name and a.partition_name = b.partition_name), ( select tablespace_name f_tablespace_name, max(bytes) max_free_space from sys.dba_free_space group by tablespace_name) where f_tablespace_name = o_tablespace_name order by 1,2; < sgasize.sql select db_size_in_mb - db_caches db_buffers_in_mb, db_caches db_caches_mb, fixed_size_in_mb, lb_size_in_mb, sp_size_in_mb, lp_size_in_mb, jp_size_in_mb from (select round (max(a.bytes) / 1024 / 1024, 2) db_size_in_mb from sys.v_$sgastat a where (a.name = 'db_block_buffers ' or a.name = 'buffer_cache')), (select nvl(round (sum (b.value) / 1024 / 1024, 2),0) db_caches from sys.v_$parameter b where b.name like '%k_cache_size'), (select round (sum (b.bytes) / 1024 / 1024, 2) fixed_size_in_mb from sys.v_$sgastat b where b.name = 'fixed_sga'), (select round (sum (c.bytes) / 1024 / 1024, 2) lb_size_in_mb from sys.v_$sgastat c where c.name= 'log_buffer ' ), (select round (sum (d.value) / 1024 / 1024, 2) sp_size_in_mb from sys.v_$parameter d where d.name = 'shared_pool_size '), (select round (sum (e.value) / 1024 / 1024, 2) lp_size_in_mb from sys.v_$parameter e where e.name = 'large_pool_size ' ), (select round (sum (f.value) / 1024 / 1024, 2) jp_size_in_mb from sys.v_$parameter f where f.name = 'java_pool_size '); < memsnap.sql select buffer_hit_ratio, percent_shared_pool_free, lib_cache_hit_ratio, object_reloads, dd_cache_hit_ratio, redo_log_space_waits, redo_log_space_wait_time, mem_sort_ratio, parse_execute_ratio, buffer_busy_waits, latch_miss_ratio from (select 100 - 100 * (round ((sum (decode (name, 'physical reads', value, 0)) - sum (decode (name, 'physical reads direct', value, 0)) - sum (decode (name, 'physical reads direct (lob)', value, 0))) / (sum (decode (name, 'session logical reads', value, 1))),3)) buffer_hit_ratio from sys.v_$sysstat where name in ('session logical reads', 'physical reads direct (lob)', 'physical reads', 'physical reads direct')), (select round (100 * (free_bytes / shared_pool_size ), 2) percent_shared_pool_free from (select sum (bytes) free_bytes from sys.v_$sgastat where name = 'free memory' and pool = 'shared pool '), (select value shared_pool_size from sys.v_$parameter where name = 'shared_pool_size')), (select 100 - round ((sum (reloads) / sum (pins)) * 100, 2) lib_cache_hit_ratio from sys.v_$librarycache), (select 100 - round ((sum (getmisses) / (sum (gets) + sum (getmisses)) * 100), 2) dd_cache_hit_ratio from sys.v_$rowcache), (select round ( (100 * b.value) / decode ((a.value + b.value), 0, 1, (a.value + b.value)), 2)mem_sort_ratio from v$sysstat a, v$sysstat b where a.name = 'sorts (disk)' and b.name = 'sorts (memory)'), (select round(100 * (sum (sys.v_$latch.misses) / sum (sys.v_$latch.gets)),2) latch_miss_ratio from sys.v_$latch), (select round (100 * (a.value - b.value) / decode (a.value, 0, 1, a.value), 2) parse_execute_ratio from sys.v_$sysstat a, sys.v_$sysstat b where a.name = 'execute count' and b.name = 'parse count (hard)'), (select nvl(sum(total_waits),0) buffer_busy_waits from sys.v_$system_event a, sys.v_$event_name b where a.event = 'buffer busy waits' and a.event (+) = b.name), (select sum(reloads) object_reloads from sys.v_$librarycache), (select value redo_log_space_waits from sys.v_$sysstat where name = 'redo log space requests'), (select value redo_log_space_wait_time from sys.v_$sysstat where name = 'redo log space wait time'); < poolhit.sql select name, 100 * (1 - (physical_reads / (db_block_gets + consistent_gets))) hit_ratio from sys.v$buffer_pool_statistics where db_block_gets + consistent_gets > 0; < sesshitrate.sql select b.sid sid, decode (b.username,null,e.name,b.username) user_name, d.spid os_id, b.machine machine_name, to_char(logon_time,'mm/dd/yy hh:mi:ss pm') logon_time, 100 - 100 * (round ((sum (decode (c.name, 'physical reads', value, 0)) - sum (decode (c.name, 'physical reads direct', value, 0)) – sum(decode (c.name, 'physical reads direct (lob)', value, 0))) / (sum (decode (c.name, 'db block gets', value, 1)) + sum (decode (c.name, 'consistent gets', value, 0))),3)) hit_ratio from sys.v_$sesstat a, sys.v_$session b, sys.v_$statname c, sys.v_$process d, sys.v_$bgprocess e where a.statistic#=c.statistic# and b.sid=a.sid and d.addr = b.paddr and e.paddr (+) = b.paddr and c.name in ('physical reads', 'physical reads direct', 'physical writes direct (lob)', 'physical reads direct (lob)', 'db block gets', 'consistent gets') group by b.sid, d.spid, decode (b.username,null,e.name,b.username), b.machine, to_char(logon_time,'mm/dd/yy hh:mi:ss pm') order by 6 desc; < sqlhitrate.sql select sql_text , b.username , 100 - round(100 * a.disk_reads/greatest(a.buffer_gets,1),2) hit_ratio from sys.v_$sqlarea a, sys.all_users b where a.parsing_user_id=b.user_id and b.username not in ('SYS','SYSTEM') order by 3 desc; < cacheobjcnt.sql select decode(cachehint, 0, 'default', 1, 'keep', 2, 'recycle', null) cache, count(*) objects from sys.seg$ s where s.user# in (select user# from sys.user$ where name not in ('sys','system')) group by decode(cachehint, 0, 'default', 1, 'keep', 2, 'recycle', null) order by 1; < buffutl.sql select 'free' buffer_state, nvl(sum(blocksize) / 1024 ,0) amt_kb from sys.x$bh a, sys.ts$ b where state = 0 and a.ts# = b.ts# union all select 'read/mod' buffer_state, nvl(sum(blocksize) / 1024 ,0) amt_kb from sys.x$bh a, sys.ts$ b where state = 1 and a.ts# = b.ts# union all select 'read/notmod', nvl(sum(blocksize) / 1024 ,0) amt_kb from sys.x$bh a, sys.ts$ b where state = 2 and a.ts# = b.ts# union all select 'being read' buffer_state, nvl(sum(blocksize) / 1024 ,0) amt_kb from sys.x$bh a, sys.ts$ b where state = 3 and a.ts# = b.ts# order by 1; < libdet.sql select namespace, gets, round(gethitratio*100,2) gethitratio, pins, round(pinhitratio*100,2) pinhitratio, reloads, invalidations from sys.v_$librarycache order by 1; < libwait.sql select b.name, nvl(max(a.total_waits),0) from sys.v_$system_event a, sys.v_$event_name b where a.event (+) = b.name and b.name in ('latch free ','library cache load lock', 'library cache lock','library cache pin') group by b.name < libobj.sql select owner, name, type, sharable_mem, loads, executions, locks, pins, kept from sys.v_$db_object_cache order by type asc; < dictdet.sql select parameter, usage, gets, getmisses, 100 - round((getmisses/ (gets + getmisses) * 100),2) hit_ratio from sys.v_$rowcache where gets + getmisses <> 0 order by 5 desc; < memhog.sql select sid, username, round(total_user_mem/1024,2) mem_used_in_kb, round(100 * total_user_mem/total_mem,2) mem_percent from (select b.sid sid, nvl(b.username,p.name) username, sum(value) total_user_mem from sys.v_$statname c, sys.v_$sesstat a, sys.v_$session b, sys.v_$bgprocess p where a.statistic#=c.statistic# and p.paddr (+) = b.paddr and b.sid=a.sid and c.name in ('session pga memory','session uga memory') group by b.sid, nvl(b.username,p.name)), (select sum(value) total_mem from sys.v_$statname c, sys.v_$sesstat a where a.statistic#=c.statistic# and c.name in ('session pga memory','session uga memory')) order by 3 desc; < bufobjwaits.sql select owner, object_name, object_type, value waits from sys.v_$segment_statistics where (statistic_name = 'buffer busy waits' and value > 0) order by 1,2; < latchdet.sql select name, gets, round(misses*100/decode(gets,0,1,gets),2) misses, round(spin_gets*100/decode(misses,0,1,misses),2) spins, immediate_gets igets, round(immediate_misses*100/ decode(immediate_gets,0,1,immediate_gets),2) imisses, sleeps from sys.v_$latch order by 2 desc; < currlwaits.sql select a.sid, username, a.event, a.p1text, a.p1, a.p2text, a.p2, a.seq#, a.wait_time, a.state from sys.v_$session_wait a, sys.v_$session b, sys.v_$latchname c where a.sid = b.sid and a.p2 = c.latch# and a.event in (select name from sys.v_$event_name where name like '%latch%') order by 1; < globiostats.sql select name, value from sys.v_$sysstat where name in ('consistent changes', 'consistent gets', 'db block changes', 'db block gets', 'physical reads', 'physical writes', 'sorts (disk)', 'user commits', 'user rollbacks' ) order by 1; < syswaits.sql select event, total_waits, round(100 * (total_waits / sum_waits),2) pct_waits, time_wait_sec, round(100 * (time_wait_sec / greatest(sum_time_waited,1)),2) pct_time_waited, total_timeouts, round(100 * (total_timeouts / greatest(sum_timeouts,1)),2) pct_timeouts, average_wait_sec from (select event, total_waits, round((time_waited / 100),2) time_wait_sec, total_timeouts, round((average_wait / 100),2) average_wait_sec from sys.v_$system_event where event not in ('lock element cleanup ', 'pmon timer ', 'rdbms ipc message ', 'rdbms ipc reply', 'smon timer ', 'SQL*Net message from client ', 'SQL*Net break/reset to client ', 'SQL*Net message to client ', 'SQL*Net more data from client', 'dispatcher timer ', 'Null event ', 'parallel query dequeue wait ', 'parallel query idle wait - Slaves ', 'pipe get ', 'PL/SQL lock timer ', 'slave wait ', 'virtual circuit status ', 'WMON goes to sleep') and event not like 'DFS%' and event not like 'KXFX%'), (select sum(total_waits) sum_waits, sum(total_timeouts) sum_timeouts, sum(round((time_waited / 100),2)) sum_time_waited from sys.v_$system_event where event not in ('lock element cleanup ', 'pmon timer ', 'rdbms ipc message ', 'rdbms ipc reply', 'smon timer ', 'SQL*Net message from client ', 'SQL*Net break/reset to client ', 'SQL*Net message to client ', 'SQL*Net more data from client', 'dispatcher timer ', 'Null event ', 'parallel query dequeue wait ', 'parallel query idle wait - Slaves ', 'pipe get ', 'PL/SQL lock timer ', 'slave wait ', 'virtual circuit status ', 'WMON goes to sleep') and event not like 'DFS%' and event not like 'KXFX%') order by 2 desc, 1 asc; < globaccpatt.sql select name, value from sys.v_$sysstat where name in ('table scans (cache partitions)', 'table scans (direct read)', 'table scans (long tables)', 'table scans (rowid ranges)', 'table scans (short tables)', 'table fetch by rowid', 'table fetch continued row ') order by 1; < fileio.sql select d.name file_name, c.name tablespace_name, b.phyrds, b.phywrts, b.phyblkrd, b.phyblkwrt, b.readtim, b.writetim from sys.v_$datafile a, sys.v_$filestat b, sys.ts$ c, sys.v_$dbfile d, sys.file$ e where a.file# = b.file# and a.file# = d.file# and e.ts# = c.ts# and e.file# = d.file# union all select v.fnnam file_name, c.name tablespace_name, b.phyrds, b.phywrts, b.phyblkrd, b.phyblkwrt, b.readtim, b.writetim from sys.v_$tempfile a, sys.v_$tempstat b, sys.ts$ c, sys.x$kccfn v, sys.x$ktfthc hc where a.file# = b.file# and a.file# = hc.ktfthctfno and hc.ktfthctsn = c.ts# and v.fntyp = 7 and v.fnnam is not null and v.fnfno = hc.ktfthctfno and hc.ktfthctsn = c.ts# order by 3 desc; < toptables.sql -- ************************************************* -- Copyright © 2005 by Rampant TechPress -- This script is free for non-commercial purposes -- with no warranties. Use at your own risk. -- -- To license this script for a commercial purpose, -- contact info@rampant.cc -- ************************************************* select table_owner "table owner", table_name "table name", command "command issued", 0 - executions "executions", disk_reads "disk reads", gets "buffer gets", rows_processed "rows processed" from (select distinct executions, command, table_owner, table_name, gets, rows_processed, disk_reads from (select decode (a.command_type , 2, 'insert ' , 3,'select ', 6, 'update ' , 7, 'delete ' , 26,'table lock ') command , c.owner table_owner, c.name table_name , sum(a.disk_reads) disk_reads , sum(0 - a.executions) executions , sum(a.buffer_gets) gets , sum(a.rows_processed) rows_processed from sys.v_$sql a , sys.v_$object_dependency b , sys.v_$db_object_cache c where a.command_type in (2,3,6,7,26)and b.from_address = a.address and b.to_owner = c.owner and b.to_name= c.name and c.type = 'table' and c.owner not in ('SYS','SYSTEM') group by a.command_type , c.owner , c.name ) ) where rownum <= 100; < largescan.sql select table_owner, table_name, table_type, size_kb, statement_count, reference_count, executions, executions * reference_count total_scans from (select a.object_owner table_owner, a.object_name table_name, b.segment_type table_type, b.bytes / 1024 size_kb, sum(c.executions ) executions, count( distinct a.hash_value ) statement_count, count( * ) reference_count from sys.v_$sql_plan a, sys.dba_segments b, sys.v_$sql c where a.object_owner (+) = b.owner and a.object_name (+) = b.segment_name and b.segment_type IN ('TABLE', 'TABLE PARTITION') and a.operation LIKE '%TABLE%' and a.options = 'FULL' and a.hash_value = c.hash_value and b.bytes / 1024 > 1024 group by a.object_owner, a.object_name, a.operation, b.bytes / 1024, b.segment_type order by 4 desc, 1, 2 ); < physpctio.sql -- ************************************************* -- Copyright © 2005 by Rampant TechPress -- This script is free for non-commercial purposes -- with no warranties. Use at your own risk. -- -- To license this script for a commercial purpose, -- contact info@rampant.cc -- ************************************************* select sid, username, round(100 * total_user_io/total_io,2) tot_io_pct from (select b.sid sid, nvl(b.username,p.name) username, sum(value) total_user_io from sys.v_$statname c, sys.v_$sesstat a, sys.v_$session b, sys.v_$bgprocess p where a.statistic#=c.statistic# and p.paddr (+) = b.paddr and b.sid=a.sid and c.name in ('physical reads', 'physical writes', 'physical writes direct', 'physical reads direct', 'physical writes direct (lob)', 'physical reads direct (lob)') group by b.sid, nvl(b.username,p.name)), (select sum(value) total_io from sys.v_$statname c, sys.v_$sesstat a where a.statistic#=c.statistic# and c.name in ('physical reads', 'physical writes', 'physical writes direct', 'physical reads direct', 'physical writes direct (lob)', 'physical reads direct (lob)')) order by 3 desc; < totpctio.sql -- ************************************************* -- Copyright © 2005 by Rampant TechPress -- This script is free for non-commercial purposes -- with no warranties. Use at your own risk. -- -- To license this script for a commercial purpose, -- contact info@rampant.cc -- ************************************************* SELECT SID, USERNAME, ROUND(100 * TOTAL_USER_IO/TOTAL_IO,2) TOT_IO_PCT FROM (SELECT b.SID SID, nvl(b.USERNAME,p.NAME) USERNAME, SUM(VALUE) TOTAL_USER_IO FROM sys.V_$STATNAME c, sys.V_$SESSTAT a, sys.V_$SESSION b, sys.v_$bgprocess p WHERE a.STATISTIC#=c.STATISTIC# and p.paddr (+) = b.paddr and b.SID=a.SID and c.NAME in ('physical reads','physical writes', 'consistent changes','consistent gets', 'db block gets','db block changes', 'physical writes direct', 'physical reads direct', 'physical writes direct (lob)', 'physical reads direct (lob)') GROUP BY b.SID, nvl(b.USERNAME,p.name)), (select sum(value) TOTAL_IO from sys.V_$STATNAME c, sys.V_$SESSTAT a WHERE a.STATISTIC#=c.STATISTIC# and c.NAME in ('physical reads','physical writes', 'consistent changes', 'consistent gets','db block gets', 'db block changes', 'physical writes direct', 'physical reads direct', 'physical writes direct (lob)', 'physical reads direct (lob)')) ORDER BY 3 DESC; < topiousers.sql -- ************************************************* -- Copyright © 2005 by Rampant TechPress -- This script is free for non-commercial purposes -- with no warranties. Use at your own risk. -- -- To license this script for a commercial purpose, -- contact info@rampant.cc -- ************************************************* select b.sid sid, decode (b.username,null,e.name,b.username) user_name, d.spid os_id, b.machine machine_name, to_char(logon_time,'mm/dd/yy hh:mi:ss pm') logon_time, (sum(decode(c.name,'physical reads',value,0)) + sum(decode(c.name,'physical writes',value,0)) + sum(decode(c.name, 'physical writes direct',value,0)) + sum(decode(c.name, 'physical writes direct (lob)',value,0)) + sum(decode(c.name, 'physical reads direct (lob)',value,0)) + sum(decode(c.name, 'physical reads direct',value,0))) total_physical_io, (sum(decode(c.name,'db block gets',value,0)) + sum(decode(c.name, 'db block changes',value,0)) + sum(decode(c.name,'consistent changes',value,0)) + sum(decode(c.name,'consistent gets',value,0)) ) total_logical_io, 100 – 100 *(round ((sum (decode (c.name, 'physical reads', value, 0)) – sum (decode (c.name, 'physical reads direct', value, 0))) / (sum (decode (c.name, 'db block gets', value, 1)) + sum (decode (c.name, 'consistent gets', value, 0))),3)) hit_ratio, sum(decode(c.name,'sorts (disk)',value,0)) disk_sorts, sum(decode(c.name,'sorts (memory)',value,0)) memory_sorts, sum(decode(c.name,'sorts (rows)',value,0)) rows_sorted, sum(decode(c.name,'user commits',value,0)) commits, sum(decode(c.name,'user rollbacks',value,0)) rollbacks, sum(decode(c.name,'execute count',value,0)) executions, sum(decode(c.name,'physical reads',value,0)) physical_reads, sum(decode(c.name,'db block gets',value,0)) db_block_gets, sum(decode(c.name,'consistent gets',value,0)) consistent_gets, sum(decode(c.name,'consistent changes',value,0)) consistent_changes from sys.v_$sesstat a, sys.v_$session b, sys.v_$statname c, sys.v_$process d, sys.v_$bgprocess e where a.statistic#=c.statistic# and b.sid=a.sid and d.addr = b.paddr and e.paddr (+) = b.paddr and c.name in ('physical reads', 'physical writes', 'physical writes direct', 'physical reads direct', 'physical writes direct (lob)', 'physical reads direct (lob)', 'db block gets', 'db block changes', 'consistent changes', 'consistent gets', 'sorts (disk)', 'sorts (memory)', 'sorts (rows)', 'user commits', 'user rollbacks', 'execute count' ) group by b.sid, d.spid, decode (b.username,null,e.name,b.username), b.machine, to_char(logon_time,'mm/dd/yy hh:mi:ss pm') order by 6 desc; < bgact.sql select name, value from sys.v_$sysstat where (name like '%DBWR %' or name in ('dirty buffers inspected', 'summed dirty queue length', 'write requests')) or (name like '%redo%') order by 1; < archhist.sql select to_char(completion_time,'mm/dd/yy') completion_time, count(*) log_count from sys.v_$archived_log where sysdate - completion_time < 31 group by to_char(completion_time,'mm/dd/yy') order by 1 desc; < rolldet.sql select name, round ((rssize / 1024), 2) size_kb, shrinks, extends, gets, waits, writes, xacts, status, round ((hwmsize / 1024), 2) hw_kb from sys.v_$rollstat a, sys.v_$rollname b where (a.usn = b.usn) order by name; < totuserspace.sql select owner, round((byte_count / 1024 / 1024),2) space_used_mb, round(100 * (byte_count / tot_bytes),2) pct_of_database from (select owner , sum(bytes) as byte_count from sys.dba_segments where segment_type not in ('TEMPORARY','CACHE') group by owner order by 2 desc), (select sum(bytes) as tot_bytes from sys.dba_segments); < sortusage.sql select tablespace_name, current_users, total_extents, used_extents, free_extents, max_used_size, max_sort_size from sys.v_$sort_segment order by 1; < sortdet.sql select sql_text, sid, c.username, machine, tablespace, extents, blocks from sys.v_$sort_usage a, sys.v_$sqlarea b, sys.v_$session c where a.sqladdr = b.address and a.sqlhash = b.hash_value and a.session_addr = c.saddr order by sid; < topsess.sql select 'top physical i/o process' category, sid, username, total_user_io amt_used, round(100 * total_user_io/total_io,2) pct_used from (select b.sid sid, nvl(b.username,p.name) username, sum(value) total_user_io from sys.v_$statname c, sys.v_$sesstat a, sys.v_$session b, sys.v_$bgprocess p where a.statistic#=c.statistic# and p.paddr (+) = b.paddr and b.sid=a.sid and c.name in ('physical reads','physical writes', 'physical reads direct', 'physical reads direct (lob)', 'physical writes direct', 'physical writes direct (lob)') group by b.sid, nvl(b.username,p.name) order by 3 desc), (select sum(value) total_io from sys.v_$statname c, sys.v_$sesstat a where a.statistic#=c.statistic# and c.name in ('physical reads','physical writes', 'physical reads direct', 'physical reads direct (lob)', 'physical writes direct', 'physical writes direct (lob)')) where rownum < 2 union all select 'top logical i/o process', sid, username, total_user_io amt_used, round(100 * total_user_io/total_io,2) pct_used from (select b.sid sid, nvl(b.username,p.name) username, sum(value) total_user_io from sys.v_$statname c, sys.v_$sesstat a, sys.v_$session b, sys.v_$bgprocess p where a.statistic#=c.statistic# and p.paddr (+) = b.paddr and b.sid=a.sid and c.name in ('consistent gets','db block gets') group by b.sid, nvl(b.username,p.name) order by 3 desc), (select sum(value) total_io from sys.v_$statname c, sys.v_$sesstat a where a.statistic#=c.statistic# and c.name in ('consistent gets','db block gets')) where rownum < 2 union all select 'top memory process', sid, username, total_user_mem, round(100 * total_user_mem/total_mem,2) from (select b.sid sid, nvl(b.username,p.name) username, sum(value) total_user_mem from sys.v_$statname c, sys.v_$sesstat a, sys.v_$session b, sys.v_$bgprocess p where a.statistic#=c.statistic# and p.paddr (+) = b.paddr and b.sid=a.sid and c.name in ('session pga memory','session uga memory') group by b.sid, nvl(b.username,p.name) order by 3 desc), (select sum(value) total_mem from sys.v_$statname c, sys.v_$sesstat a where a.statistic#=c.statistic# and c.name in ('session pga memory','session uga memory') ) where rownum < 2 union all select 'top cpu process', sid, username, total_user_cpu, round(100 * total_user_cpu/greatest(total_cpu,1),2) from (select b.sid sid, nvl(b.username,p.name) username, sum(value) total_user_cpu from sys.v_$statname c, sys.v_$sesstat a, sys.v_$session b, sys.v_$bgprocess p where a.statistic#=c.statistic# and p.paddr (+) = b.paddr and b.sid=a.sid and c.name = 'CPU used by this session' group by b.sid, nvl(b.username,p.name) order by 3 desc), (select sum(value) total_cpu from sys.v_$statname c, sys.v_$sesstat a where a.statistic#=c.statistic# and c.name = 'CPU used by this session' ) where rownum < 2; < topsessdet.sql -- ************************************************* -- Copyright © 2005 by Rampant TechPress -- This script is free for non-commercial purposes -- with no warranties. Use at your own risk. -- -- To license this script for a commercial purpose, -- contact info@rampant.cc -- ************************************************* select * from (select b.sid sid, decode (b.username,null,e.name,b.username) user_name, d.spid os_id, b.machine machine_name, to_char(logon_time,'mm/dd/yy hh:mi:ss pm') logon_time, (sum(decode(c.name,'physical reads',value,0)) + sum(decode(c.name,'physical writes',value,0)) + sum(decode(c.name,'physical writes direct',value,0)) + sum(decode(c.name,'physical writes direct (lob)',value,0)) + sum(decode(c.name,'physical reads direct (lob)',value,0)) + sum(decode(c.name,'physical reads direct',value,0))) total_physical_io, (sum(decode(c.name,'db block gets',value,0)) + sum(decode(c.name,'db block changes',value,0)) + sum(decode(c.name,'consistent changes',value,0)) + sum(decode(c.name,'consistent gets',value,0)) ) total_logical_io, 100 - 100 * (round ((sum (decode (c.name, 'physical reads', value, 0)) - sum (decode (c.name, 'physical reads direct', value, 0))) / (sum (decode (c.name, 'db block gets', value, 1)) + sum (decode (c.name, 'consistent gets', value, 0)) ),3)) hit_ratio, (sum(decode(c.name,'session pga memory',value,0))+ sum(decode(c.name,'session uga memory',value,0)) ) total_memory_usage, sum(decode(c.name,'parse count (total)',value,0)) parses, sum(decode(c.name,'CPU used by this session',value,0)) total_cpu, sum(decode(c.name,'parse time cpu',value,0)) parse_cpu, sum(decode(c.name,'recursive cpu usage',value,0)) recursive_cpu, sum(decode(c.name,'CPU used by this session',value,0)) - sum(decode(c.name,'parse time cpu',value,0)) - sum(decode(c.name,'recursive cpu usage',value,0)) other_cpu, sum(decode(c.name,'sorts (disk)',value,0)) disk_sorts, sum(decode(c.name,'sorts (memory)',value,0)) memory_sorts, sum(decode(c.name,'sorts (rows)',value,0)) rows_sorted, sum(decode(c.name,'user commits',value,0)) commits, sum(decode(c.name,'user rollbacks',value,0)) rollbacks, sum(decode(c.name,'execute count',value,0)) executions, sum(decode(c.name,'physical reads',value,0)) physical_reads, sum(decode(c.name,'db block gets',value,0)) db_block_gets, sum(decode(c.name,'consistent gets',value,0)) consistent_gets, sum(decode(c.name,'consistent changes',value,0)) consistent_changes from sys.v_$sesstat a, sys.v_$session b, sys.v_$statname c, sys.v_$process d, sys.v_$bgprocess e where a.statistic#=c.statistic# and b.sid=a.sid and d.addr = b.paddr and e.paddr (+) = b.paddr and c.name in ('physical reads', 'physical writes', 'physical writes direct', 'physical reads direct', 'physical writes direct (lob)', 'physical reads direct (lob)', 'db block gets', 'db block changes', 'consistent changes', 'consistent gets', 'session pga memory', 'session uga memory', 'parse count (total)', 'CPU used by this session', 'parse time cpu', 'recursive cpu usage', 'sorts (disk)', 'sorts (memory)', 'sorts (rows)', 'user commits', 'user rollbacks', 'execute count' ) group by b.sid, d.spid, decode (b.username,null,e.name,b.username), b.machine, to_char(logon_time,'mm/dd/yy hh:mi:ss pm') order by 6 desc); < userscans.sql select sid, username, total_user_scans, round(100 * total_user_scans/total_scans,2) pct_scans from (select b.sid sid, nvl(b.username,p.name) username, sum(value) total_user_scans from sys.v_$statname c, sys.v_$sesstat a, sys.v_$session b, sys.v_$bgprocess p where a.statistic#=c.statistic# and p.paddr (+) = b.paddr and b.sid=a.sid and c.name = 'table scans (long tables)' group by b.sid, nvl(b.username,p.name) order by 3 desc), (select sum(value) total_scans from sys.v_$statname c, sys.v_$sesstat a where a.statistic#=c.statistic# and c.name = 'table scans (long tables)'); < scatwait.sql select b.sid, nvl(b.username,c.name) username, b.machine, a.total_waits, round((a.time_waited / 100),2) time_wait_sec,a.total_timeouts, round((average_wait / 100),2) average_wait_sec, round((a.max_wait / 100),2) max_wait_sec from sys.v_$session_event a, sys.v_$session b, sys.v_$bgprocess c where a.event = 'db file scattered read ' and a.sid = b.sid and c.paddr (+) = b.paddr order by 3 desc, 1 asc; < large_scanusers.sql select c.username username, count(a.hash_value) scan_count from sys.v_$sql_plan a, sys.dba_segments b, sys.dba_users c, sys.v_$sql d where a.object_owner (+) = b.owner and a.object_name (+) = b.segment_name and b.segment_type in ('TABLE', 'TABLE PARTITION') and a.operation like '%TABLE%' and a.options = 'FULL' and c.user_id = d.parsing_user_id and d.hash_value = a.hash_value and b.bytes / 1024 > 1024 group by c.username order by 2 desc; < lockcnt.sql select count(*) from sys.v_$session where lockwait is not null; The return of any non-zero number indicates a current blocking lock situation and can be investigated further by running this query: select a.username blocked_user, b.username blocking_user, w.sid waiting_session, h.sid holding_session, w.type, decode(h.lmode, 1,'no lock', 2,'row share', 3,'row exclusive', 4,'share', 5,'share row exclusive', 6,'exclusive','none') lmode, decode(w.request, 1,'no lock', 2,'row share', 3,'row exclusive', 4,'share', 5,'share row exclusive', 6,'exclusive','none') request, a.row_wait_row# row_waited_on, w.id1, w.id2, w.ctime blocked_user_wait_secs, u1.name || '.' || t1.name locked_object from sys.v_$lock w, sys.v_$lock h, sys.v_$session a, sys.v_$session b, sys.v_$locked_object o, sys.user$ u1, sys.obj$ t1 where h.lmode != 0 and w.request != 0 and w.type = h.type and w.id1 = h.id1 and w.id2 = h.id2 and b.sid = h.sid and a.sid = w.sid and h.sid = o.session_id and o.object_id = t1.obj# and u1.user# = t1.owner# order by 4,3; < curriosql.sql select sid, username, sql_text from sys.v_$sqltext a, sys.v_$session b where b.sql_address = a.address and b.sid = (select sid from (select b.sid sid, nvl(b.username,p.name) username, sum(value) total_user_io from sys.v_$statname c, sys.v_$sesstat a, sys.v_$session b, sys.v_$bgprocess p where a.statistic#=c.statistic# and p.paddr (+) = b.paddr and b.sid=a.sid and c.name in ('physical reads','physical writes', 'physical reads direct', 'physical reads direct (lob)', 'physical writes direct', 'physical writes direct (lob)') group by b.sid, nvl(b.username,p.name) order by 3 desc) where rownum < 2) order by a.piece; < cartsession.sql select username, count(distinct c.hash_value) nbr_stmts from sys.v_$sql a, sys.dba_users b, sys.v_$sql_plan c where a.parsing_user_id = b.user_id and options = 'cartesian' and operation like '%join%' and a.hash_value = c.hash_value group by username order by 2 desc; < cartsql.sql select * from sys.v_$sql where hash_value in (select hash_value from sys.v_$sql_plan where options = 'CARTESIAN' and operation LIKE '%JOIN%' ) order by hash_value; < topsql.sql select sql_text , username , disk_reads_per_exec, buffer_gets_per_exec, buffer_gets , disk_reads, parse_calls , sorts , executions , loads, rows_processed , hit_ratio, first_load_time , sharable_mem , persistent_mem , runtime_mem, cpu_time_secs, cpu_time_secs_per_execute, elapsed_time_secs, elapsed_time_secs_per_execute, address, hash_value from (select sql_text , b.username , round((a.disk_reads/ decode(a.executions,0,1,a.executions)),2) disk_reads_per_exec, a.disk_reads , a.buffer_gets , round((a.buffer_gets/ decode(a.executions,0,1,a.executions)),2) buffer_gets_per_exec, a.parse_calls , a.sorts , a.executions , a.loads, a.rows_processed , 100 - round(100 * a.disk_reads/ greatest(a.buffer_gets,1),2) hit_ratio, a.first_load_time , sharable_mem , persistent_mem , runtime_mem, round(cpu_time / 1000000,3) cpu_time_secs, round((cpu_time / 1000000)/ decode(a.executions,0,1,a.executions),3) cpu_time_secs_per_execute, round(elapsed_time / 1000000,3) elapsed_time_secs, round((elapsed_time / 1000000)/decode(a.executions,0,1,a.executions),3) elapsed_time_secs_per_execute, address, hash_value from sys.v_$sqlarea a, sys.all_users b where a.parsing_user_id=b.user_id and b.username not in ('SYS','SYSTEM') order by 3 desc) where rownum < 21; < tabscan.sql select sql_text, total_large_scans, executions, executions * total_large_scans sum_large_scans from (select sql_text, count(*) total_large_scans, executions from sys.v_$sql_plan a, sys.dba_segments b, sys.v_$sql c where a.object_owner (+) = b.owner and a.object_name (+) = b.segment_name and b.segment_type in ('TABLE', 'TABLE PARTITION') and a.operation like '%TABLE%' and a.options = 'FULL' and c.hash_value = a.hash_value and b.bytes / 1024 > 1024 group by sql_text, executions) order by 4 desc; < planstats.sql select operation, options, object_owner, object_name, executions, last_output_rows, last_cr_buffer_gets, last_cu_buffer_gets, last_disk_reads, last_disk_writes, last_elapsed_time from sys.v_$sql_plan a, sys.v_$sql_plan_statistics b where a.sql_id = b.sql_id and a.id = b.operation_id and a.sql_id = '&sql_id' order by a.id; < tabscan.sql select table_owner, table_name, table_type, size_kb, statement_count, reference_count, executions, executions * reference_count total_scans from (select a.object_owner table_owner, a.object_name table_name, b.segment_type table_type, b.bytes / 1024 size_kb, sum(c.executions ) executions, count( distinct a.hash_value ) statement_count, count( * ) reference_count from sys.v_$sql_plan a, sys.dba_segments b, sys.v_$sql c where a.object_owner (+) = b.owner and a.object_name (+) = b.segment_name and b.segment_type in ('TABLE', 'TABLE PARTITION') and a.operation like '%TABLE%' and a.options = 'FULL' and a.hash_value = c.hash_value and b.bytes / 1024 > 1024 group by a.object_owner, a.object_name, a.operation, b.bytes / 1024, b.segment_type order by 4 desc, 1, 2 ); < unused_indx.sql select distinct a.object_owner table_owner, a.object_name table_name, b.segment_type table_type, b.bytes / 1024 size_kb, d.index_name from sys.v_$sql_plan a, sys.dba_segments b, sys.dba_indexes d where a.object_owner (+) = b.owner and a.object_name (+) = b.segment_name and b.segment_type in ('TABLE', 'TABLE PARTITION') and a.operation like '%TABLE%' and a.options = 'FULL' and b.bytes / 1024 > 1024 and b.segment_name = d.table_name and b.owner = d.table_owner order by 1, 2; < ash_spikes.sql select to_char(round(subline.sample_time, ‘HH24′), ‘YYYY-MM-DD HH24:MI’) as sample_hour, round(avg(subline.on_cpu),1) as cpu_avg, round(avg(subline.waiting),1) as wait_avg, round(avg(subline.active_sessions),1) as act_avg, round((variance(subline.active_sessions)/avg(subline.active_sessions)),1) as act_var_mean from ( select sample_id, sample_time, sum(decode(session_state, ‘ON CPU’, 1, 0)) as on_cpu, sum(decode(session_state, ‘WAITING’, 1, 0)) as waiting, count(*) as active_sessions from dba_hist_active_sess_history where sample_time > sysdate – (&hours/24) group by sample_id, sample_time ) subline group by round(subline.sample_time, ‘HH24′) order by round(subline.sample_time, ‘HH24′); < session_waits.sql 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.username = 'SAPR3' and sess.sid = se.sid group by se.event order by 2 DESC; < ash_user_wait_time.sql select s.sid, s.username, sum(h.wait_time + h.time_waited ) "total wait time" from v$active_session_history h, v$session s, v$event_name e where h.session_id = s.sid and e.event_id = h.event_id and e.wait_class <> 'Idle' and s.username IS NOT NULL group by s.sid, s.username order by 3; < ash_event_rollup.sql title 'High waits on events|Rollup by hour' column mydate heading 'Yr. Mo Dy Hr' format a13; column event format a30; column total_waits heading 'tot waits' format 999,999; column time_waited heading 'time wait' format 999,999; column total_timeouts heading 'timeouts' format 9,999; break on to_char(snap_time,'yyyy-mm-dd') skip 1; select to_char(e.sample_time,'yyyy-mm-dd HH24') mydate, e.event, count(e.event) total_waits, sum(e.time_waited) time_waited from v$active_session_history e where e.event not like '%timer' and e.event not like '%message%' and e.event not like '%slave wait%' having count(e.event) > 100 group by to_char(e.sample_time,'yyyy-mm-dd HH24'), e.event order by 1 ; < ash_object_wait_time.sql select o.owner, o.object_name, o.object_type, sum(h.wait_time + h.time_waited ) "total wait time" from v$active_session_history h, dba_objects o, v$event_name e where h.current_obj# = o.object_id and e.event_id = h.event_id and e.wait_class <> 'Idle' group by o.owner, o.object_name, o.object_type order by 4 DESC; < hot_files_ash.sql select f.file_name “Data File”, count(*) “Wait Number”, sum(h.time_waited) “Total Time Waited” from v$active_session_history h, dba_data_files f where h.current_file# = f.file_id group by f.file_name order by 3 desc < events_waits_hr_ash.sql select h.event "Wait Event", sum(h.wait_time + h.time_waited) "Total Wait Time" from v$active_session_history h, v$event_name e where h.sample_time between sysdate - 1/24 and sysdate and h.event_id = e.event_id and e.wait_class <> 'Idle' group by h.event order by 2 desc < ash_sql_counts.sql col c1 heading “invocation|count” format 9,999 col c2 heading “percentage|of|load” format 99 select sql_id, count(*) c1, round(count(*)/sum(count(*)) over (), 2) c2 from v$active_session_history where sample_time > sysdate - 1/24/60 and session_type <> ‘BACKGROUND’ group by sql_id order by count(*) desc; < ash_sql_waiting_io.sql select ash.sql_id, count(*) from v$active_session_history ash, v$event_name evt where ash.sample_time > sysdate – 1/24/60 and ash.session_state = ‘WAITING’ and ash.event_id = evt.event_id and evt.wait_class = ‘User I/O’ group by sql_id order by count(*) desc; < ash_high_wait_events.sql ttitle 'High waits on events|Rollup by hour' column mydate heading 'Yr. Mo Dy Hr' format a13; column event format a30; column total_waits heading 'tot waits' format 999,999; column time_waited heading 'time wait' format 999,999; column total_timeouts heading 'timeouts' format 9,999; break on to_char(snap_time,'yyyy-mm-dd') skip 1; select to_char(e.sample_time,'yyyy-mm-dd HH24') mydate, e.event, count(e.event) total_waits, sum(e.time_waited) time_waited from v$active_session_history e where e.event not like '%timer' and e.event not like '%message%' and e.event not like '%slave wait%' having count(e.event) > 100 group by to_char(e.sample_time,'yyyy-mm-dd HH24'), e.event order by 1 ; < ash_count_table_usage.sql select h.sql_id, count(*) from dba_hist_active_sess_history h, v$sql s where h.sql_id = s.sql_id and s.sql_fulltext like ‘%orders%’ having count(*) > 1 group by h.sql_id order by 2 desc; < session_wait_history_events.sql select swh.seq# seq_nbr, sess.sid sid, sess.username username, swh.event event, swh.p1, swh.p2 from v$session sess, v$session_wait_history swh where sess.sid = 74 and sess.sid = swh.sid order by swh.seq#; < ash_display_table_index_wait_counts.sql set pages 999 set lines 80 break on snap_time skip 2 col snap_time heading 'Snap|Time' format a20 col file_name heading 'File|Name' format a40 col object_type heading 'Object|Type' format a10 col object_name heading 'Object|Name' format a20 col wait_count heading 'Wait|Count' format 999,999 col time heading 'Time' format 999,999 select to_char(begin_interval_time,'yyyy-mm-dd hh24:mi') snap_time, -- file_name, object_type, object_name, wait_count, time from dba_hist_waitstat wait, dba_hist_snapshot snap, dba_hist_active_sess_history ash, dba_data_files df, dba_objects obj where wait.snap_id = snap.snap_id and wait.snap_id = ash.snap_id and df.file_id = ash.current_file# and obj.object_id = ash.current_obj# and wait_count > 50 order by to_char(begin_interval_time,'yyyy-mm-dd hh24:mi'), file_name ; < ash_cpu_foregound_events.sql select TO_CHAR(h.sample_time,'HH24') "Hour", Sum(h.wait_time/100) "Total Wait Time (Sec)" from v$active_session_history h, v$event_name n where h.session_state = 'ON CPU' and h.session_type = 'FOREGROUND' and h.event_id = n.EVENT_ID and n.wait_class <> 'Idle' group by TO_CHAR(h.sample_time,'HH24'); < ash_cpu_foregound_events_dow.sql select TO_CHAR(h.sample_time,'Day') "Hour", sum(h.wait_time/100) "Total Wait Time (Sec)" from v$active_session_history h, v$event_name n where h.session_state = 'ON CPU' and h.session_type = 'FOREGROUND' and h.event_id = n.EVENT_ID and n.wait_class <> 'Idle' group by TO_CHAR(h.sample_time,'Day'); < ash_total_event_wait_time.sql select h.event "Wait Event", SUM(h.wait_time/100) "Wait Time (Sec)" from v$active_session_history h, v$event_name n where h.session_state = 'ON CPU' and h.session_type = 'FOREGROUND' and h.event_id = n.EVENT_ID and to_char(h.sample_time,'HH24') = '12' and n.wait_class <> 'Idle' group by h.event order by 2 DESC; < ash_datafile_waits.sql select f.file_name “Data File”, COUNT(*) “Wait Number”, SUM(h.time_waited) “Total Time Waited” from v$active_session_history h, dba_data_files f where h.current_file# = f.file_id group by f.file_name order by 3 DESC; select e.name "Wait Event", sum(h.wait_time + h.time_waited) "Total Wait Time" from v$active_session_history h, v$event_name e where h.event_id = e.event_id and e.wait_class <> 'Idle' group by e.name order by 2 DESC; select s.sid, s.username, sum(h.wait_time + h.time_waited) "total wait time" from v$active_session_history h, v$session s, v$event_name e where h.session_id = s.sid and e.event_id = h.event_id and e.wait_class <> 'Idle' and s.username IS NOT NULL group by s.sid, s.username order by 3; < ash_object_wait_time.sql select o.owner, o.object_name, o.object_type, SUM(h.wait_time + h.time_waited) "total wait time" from v$active_session_history h, dba_objects o, v$event_name e where h.current_obj# = o.object_id and e.event_id = h.event_id and e.wait_class <> 'Idle' group by o.owner, o.object_name, o.object_type order by 4 DESC;
Search This Blog
Total Pageviews
Wednesday, 30 October 2024
RamPant scripts
From RamPant web
Subscribe to:
Post Comments (Atom)
Oracle DBA
anuj blog Archive
- ► 2011 (362)
No comments:
Post a Comment