wait event code

-- Code Depot

-- Oracle Wait Event Tuning

-- High Performance with wait event interface analysis

-- Stephen Andert

-- ISBN 0-9745993-7-9

-- Library of Congress Number 2004096474

-- No portions of this code may be distributed without the express consent of Rampant TechPress

-- These scripts are free for non-commercial use without any warranties.

-- Use at your own risk!

-- *********************************************************************************************

-- show_system_events.sql

-- *************************************************

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

-- *************************************************

/* show_system_events.sql */

select

EVENT,

TOTAL_WAITS "TOT WAITS",

TIME_WAITED "TIME WAITED",

AVERAGE_WAIT "AVG"

from

V$SYSTEM_EVENT

where

EVENT not in (

select EVENT from STATS$IDLE_EVENT

)

order by

TIME_WAITED desc;

-- start_system_events.sql

-- *************************************************

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

-- *************************************************

/* start_system_events.sql */

/* Clean-up from any prior runs */

drop table start_system;

/* create working table */

create

table start_system

as select

from

v$system_event XE "v$system_event"

where

1=2;

/* Populate the start table */

insert into start_system

select

from

v$system_event XE "v$system_event" ;

-- finish_system_events.sql

-- *************************************************

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

-- *************************************************

/* finish_system_events.sql */

/* Clean-up from any prior runs */

drop table FINISH_SYSTEM;

/* create working table */

create

table FINISH_SYSTEM

as select

from

V$SYSTEM_EVENT

where

1=2;

/* Populate the finish table */

insert

into FINISH_SYSTEM

select

from

V$SYSTEM_EVENT;

-- difference_system_events.sql

-- *************************************************

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

-- *************************************************

/* difference_system_events.sql */

/* With a little subtraction (finish minus start) we can see what was going on in the database for the time we measured. */

col event form a35

select

a.EVENT,

b.TOTAL_WAITS - a.TOTAL_WAITS "WAITS",

b.TOTAL_TIMEOUTS - a.TOTAL_TIMEOUTS "TIMEOUTS",

b.TIME_WAITED - a.TIME_WAITED "TIME WAITED",

b.AVERAGE_WAIT - a.AVERAGE_WAIT "AVG WAIT"

from

start_system a,

finish_system b

where

a.EVENT = b.EVENT

order by

"AVG WAIT”,

"TIME WAITED";

-- session_identification.sql

-- *************************************************

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

-- *************************************************

/* session_identification.sql */

col USERNAME Form a10

col SID form 99999

select

SID,

USERNAME,

LOGON_TIME,

STATUS

from

V$SESSION

where

USERNAME like UPPER('%&username%');

-- session_events.sql

-- *************************************************

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

-- *************************************************

/* session_events.sql */

column SID format 9999

column TOTAL_WAITS format 999999 heading TOT|WAIT

column AVERAGE_WAIT format 99999 heading AVG|WAIT

column EVENT format a30

select

SID,

EVENT,

TOTAL_WAITS,

TIME_WAITED,

AVERAGE_WAIT,

MAX_WAIT

from

V$SESSION_EVENT

where

SID in (&sid_list) -- sid list generated from above query

order by

TOTAL_WAITS;

-- session_waits.sql

-- *************************************************

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

-- *************************************************

/* session_waits.sql */

column event format a20

column p1text format a20

column p3text format a8

column p2text format a8

column seconds_in_wait format 99999 heading SECONDS|IN_WAIT

column wait_time format 999 heading WAIT|TIME

column state format a20

column p1 format 999999

column p2 format 99

column p3 format 99

select

SID,

EVENT,

P1TEXT,

P1,

P2TEXT,

P2,

P3TEXT,

P3,

WAIT_TIME,

SECONDS_IN_WAIT,

STATE

from

V$SESSION_WAIT

order by

EVENT;

-- event_list.sql

-- *************************************************

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

-- *************************************************

/* event_list.sql */

column NAME format a25

select

from

V$EVENT_NAME

where

NAME like '%&partial_event_name%';

-- event_list.sql

-- *************************************************

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

-- *************************************************

/* event_list.sql */

column NAME format a25

select

from

V$EVENT_NAME

where

NAME like '%&partial_event_name%';

-- prepare_other_session.sql

-- *************************************************

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

-- *************************************************

/* prepare_other_session.sql */

-- This script will make sure timed statistics

-- and max_dump_file_size are set correctly in

-- preparation for tracing a specific session.

accept SID prompt 'Enter the SID: '

accept SERIAL prompt 'Enter the SERIAL#: '

exec sys.dbms_system XE "dbms_system" .set_bool_param_in_session XE "set_bool_param_in_session" ( &SID, &SERIAL, 'timed_statistics', TRUE);

exec sys.dbms_system XE "dbms_system" .set_int_param_in_session XE "set_int_param_in_session" ( &SID, &SERIAL, 'max_dump_file_size', 2000000000);

-- prepare_current_session.sql

-- *************************************************

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

-- *************************************************

/* prepare_current_session.sql */

-- This script will make sure timed statistics

-- and max_dump_file_size are set correctly

-- in the current session in preparation

-- for tracing.

alter session set max_dump_file_size = UNLIMITED;

alter session set timed_statistics = TRUE;

-- TRCA_grants.sql

-- *************************************************

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

-- *************************************************

/* This script will prompt for a username and

then grant select privilege on all of the

objects required for Trace Analyzer. */

accept USER_NAME prompt 'Enter the username for TRCA: '

GRANT SELECT

ON dba_indexes

TO &USER_NAME;

GRANT SELECT

ON dba_ind_columns

TO &USER_NAME;

GRANT SELECT

ON dba_objects

TO &USER_NAME;

GRANT SELECT

ON dba_tables

TO &USER_NAME;

GRANT SELECT

ON dba_temp_files

TO &USER_NAME;

GRANT SELECT

ON dba_users XE "dba_users"

TO &USER_NAME;

GRANT SELECT

ON v_$instance

TO &USER_NAME;

GRANT SELECT

ON v_$latchname

TO &USER_NAME;

GRANT SELECT

ON v_$parameter

TO &USER_NAME;

-- sorts_ratio.sql

-- *************************************************

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

-- *************************************************

/* sorts_ratio.sql */

select

NAME,

VALUE

from

V$SYSSTAT

where

NAME in (

'sorts (memory)',

'sorts (disk)'

);

-- sp_sorts_ratio.sql

-- *************************************************

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

-- *************************************************

/* sp_sorts_ratio.sql */

column SNAP_DATE heading "Year Mo Day Hour" format a16

column MEMORY_SORTS format 999,999,999

column DISK_SORTS format 999,999,999

column "DISK/MEMORY RATIO" format 999.99

select

to_char(SNAP_TIME, 'yyyy-mm-dd HH24') SNAP_DATE,

MEMNEW.VALUE-MEMOLD.VALUE MEMORY_SORTS,

DISKNEW.VALUE-DISKOLD.VALUE DISK_SORTS,

(((DISKNEW.VALUE-DISKOLD.VALUE) /

(MEMNEW.VALUE-MEMOLD.VALUE)) * 100)

"DISK/MEMORY RATIO"

from

PERFSTAT XE "PERFSTAT" .STATS$SYSSTAT MEMOLD,

PERFSTAT XE "PERFSTAT" .STATS$SYSSTAT MEMNEW,

PERFSTAT XE "PERFSTAT" .STATS$SYSSTAT DISKNEW,

PERFSTAT XE "PERFSTAT" .STATS$SYSSTAT DISKOLD,

PERFSTAT XE "PERFSTAT" .STATS$SNAPSHOT sp

where

DISKNEW.SNAP_ID = sp.SNAP_ID

and

DISKOLD.SNAP_ID = sp.SNAP_ID-1

and

MEMNEW.SNAP_ID = sp.SNAP_ID

and

MEMOLD.SNAP_ID = sp.SNAP_ID-1

and

MEMOLD.NAME = 'sorts (memory)'

and

MEMNEW.NAME = 'sorts (memory)'

and

DISKOLD.NAME = 'sorts (disk)'

and

DISKNEW.NAME = 'sorts (disk)'

and

MEMNEW.VALUE - MEMOLD.VALUE > 0

and

DISKNEW.VALUE - DISKOLD.VALUE > 0

and

trunc(sp.SNAP_TIME) > '&Start_Time_dd_mon_yy'

order by

SNAP_DATE

;

-- buffer_pool_ratio.sql

-- *************************************************

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

-- *************************************************

/* buffer_pool_ratio.sql */

column RATIO format 999.99

column NAME format a15

select

NAME,

100 *

(1 –

(PHYSICAL_READS/

(DB_BLOCK_GETS + CONSISTENT_GETS)

)) “RATIO”

from

V$BUFFER_POOL_STATISTICS;

-- io_report.sql

-- *************************************************

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

-- *************************************************

/* io_report.sql */

column FILE_NAME format a30

column PHYRDS format 999,999

column PHYWRTS format 999,999

column READTIM format 999,999

select

FILE_NAME,

PHYRDS,

READTIM,

PHYWRTS,

READTIM / (PHYRDS + 1) "READ AVG (ms)",

PHYRDS + PHYWRTS "TOTAL I/O"

from

V$FILESTAT a,

DBA_DATA_FILES b

where

a.FILE# = b.FILE_ID

and

READTIM/(PHYRDS + 1) > 0.5

order by

-- io_stat_analysis.pl

-- *************************************************

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

-- *************************************************

#!/usr/bin/perl -w

#This script will return an average per device.

# Author: Kevin Hedger

# Included here with permission

# Get arguments in

$inputfile = $ARGV[0];

#Run test for input

testInput();

sub testInput

{

# my($inputfile);

# If no arguments are entered show Usage and exit

if(! $inputfile)

{

Usage();

exit;

}

sub Usage

{

print "\nUsage:\n";

print "Please enter the command in the following order\n";

print "io_stat_analisys.pl [input file]\n";

print "input file = /path/inputfile\n";

}

open (STATDATA, "$inputfile") || die("can't open file $inputfile\n");

#Outout Header Format

printf "%-9s %12s %12s %12s %10s %10s\n", "", Average, Average, Average, Average, Average;

printf "%-9s %12s %12s %12s %10s %10s\n", Device, Tps, Blk_read_s, Blk_wrtn_s, Blk_read, Blk_wrtn;

#Read in input file and loop through.

while(<STATDATA>)

{ chomp;

if(/^dev/)

{

($device, $tps, $blkrs, $blkws, $blkr, $blkw) = split(/\s+/, $_);

# Skips first device listings.

if (! (defined $save{$device}))

{

$save{$device} = {};

next;

}

#Populates Hash %save

push @{$save{$device}{col1}}, $tps;

push @{$save{$device}{col2}}, $blkrs;

push @{$save{$device}{col3}}, $blkws;

push @{$save{$device}{col4}}, $blkr;

push @{$save{$device}{col5}}, $blkw;

}

close(STATDATA);

foreach $device (sort keys %save)

{

#Returns the # of elements in the hash

$num_values_col1 = scalar(@{$save{$device}{col1}});

$num_values_col2 = scalar(@{$save{$device}{col2}});

$num_values_col3 = scalar(@{$save{$device}{col3}});

$num_values_col4 = scalar(@{$save{$device}{col4}});

$num_values_col5 = scalar(@{$save{$device}{col5}});

$total_col1 = 0;

$total_col2 = 0;

$total_col3 = 0;

$total_col4 = 0;

$total_col5 = 0;

#Sums the values of the hash

foreach (@{$save{$device}{col1}})

{

$total_col1 += $_;

}

foreach (@{$save{$device}{col2}})

{

$total_col2 += $_;

}

foreach (@{$save{$device}{col3}})

{

$total_col3 += $_;

}

foreach (@{$save{$device}{col4}})

{

$total_col4 += $_;

}

foreach (@{$save{$device}{col5}})

{

$total_col5 += $_;

}

#Devides the value of the hash by the number of elements for and average.

$ave_col1 = $total_col1 / $num_values_col1;

$ave_col2 = $total_col2 / $num_values_col2;

$ave_col3 = $total_col3 / $num_values_col3;

$ave_col4 = $total_col4 / $num_values_col4;

$ave_col5 = $total_col5 / $num_values_col5;

#Formats the output data.

printf "%-9s %12.2f %12.2f %12.2f %10.0f %10.0f\n", $device, $ave_col1, $ave_col2, $ave_col3, $ave_col4, $ave_col5;

}

-- session_waits.sql

-- *************************************************

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

-- *************************************************

/* session_waits.sql */

column SID format 999

column EVENT format a28

column P1TEXT format a15

column P3TEXT format a8

column P2TEXT format a8

column SECONDS_IN_WAIT format 99999 heading SECONDS|IN_WAIT

column WAIT_TIME format 999 heading WAIT|TIME

column STATE format a18

column P1 format 999999999999

column P2 format 9999

column P3 format 99

select

SID,

EVENT,

P1TEXT,

P1,

P2TEXT,

P2,

P3TEXT,

P3,

WAIT_TIME,

SECONDS_IN_WAIT,

STATE

from

V$SESSION_WAIT

where

EVENT not in (

select

EVENT

from

STATS$IDLE_EVENT)

order by

WAIT_TIME,

SECONDS_IN_WAIT;

-- session_event_users.sql

-- *************************************************

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

-- *************************************************

/* session_event_users.sql */

column USERNAME format a8

column EVENT format a30

column SID format 9999

column AVERAGE_WAIT format 99999 head "AVG|WAIT"

column TOTAL_WAITS format 99999 head "TOTAL|WAITS"

column TOTAL_TIMEOUTS format 9999 head "TOTAL|TIME|OUTS"

column TIME_WAITED format 999999 head "TIME|WAITED"

column MAX_WAIT format 99999 head "MAX|WAIT"

column TIME_WAITED_MICRO format 9999999 head "TIME|WAITED|MICRO"

set pagesize 100

select

b.USERNAME,

a.*

from

V$SESSION_EVENT a,

V$SESSION b

where

a.SID = b.SID

and

b.USERNAME is not null

order by

AVERAGE_WAIT DESC;

-- show_session_sql.sql

-- *************************************************

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

-- *************************************************

/* show_session_sql.sql */

select

SQL_TEXT

from

V$SQLTEXT

where

HASH_VALUE in (

select

SQL_HASH_VALUE

from

V$SESSION

where

SID = '&sid')

order by

PIECE;

-- active_session_waits.sql

-- *************************************************

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

-- *************************************************

/* active_session_waits.sql */

column EVENT format a24

column STATE format a24

column USERNAME format a10

column SID format 9999

column TIME format 9999

column SEC format 9999

column SERIAL# format 999999

column WAIT_TIME format 99 HEAD TIME

select

b.SID,

a.SERIAL#,

a.USERNAME,

b.EVENT,

b.WAIT_TIME TIME,

b.STATE,

b.SECONDS_IN_WAIT SEC

from

V$SESSION a,

V$SESSION_WAIT b

where

a.USERNAME is not null

and

b.SID = a.SID

order by

b.WAIT_TIME desc;

-- specific_session_waits.sql

-- *************************************************

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

-- *************************************************

/* specific_session_waits.sql */

column P1 format 999999

column P2 format 999999

column P3 format 999

column P1TEXT format a10

column P2TEXT format a8

column P3TEXT format a8

select

SID,

EVENT,

P1TEXT,

P1,

P2TEXT,

P2,

P3TEXT,

from

V$SESSION_WAIT

where

SID in (&sid);

-- cursor_usage.sql

-- *************************************************

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

-- *************************************************

/* cursor_usage.sql */

select

'session_cached_cursors XE "session_cached_cursors" ' parameter,

lpad(value, 5) value,

decode(value, 0, ' n/a', to_char(100 * used / value, '990') || '%') usage

from

( select

max(s.value) used

from

sys.v_$statname n,

sys.v_$sesstat s

where

n.name = 'session cursor cache count' and

s.statistic# = n.statistic#

( select

value

from

sys.v_$parameter

where

name = 'session_cached_cursors XE "session_cached_cursors" '

)

union all

select

'open_cursors',

lpad(value, 5),

to_char(100 * used / value, '990') || '%'

from

( select

max(sum(s.value)) used

from

sys.v_$statname n,

sys.v_$sesstat s

where

n.name in ('opened cursors current', 'session cursor cache count') and

s.statistic# = n.statistic#

group by

s.sid

( select

value

from

sys.v_$parameter

where

name = 'open_cursors'

);

-- io_report_volume.sql

-- *************************************************

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

-- *************************************************

/* io_report_volume.sql */

column FILE_NAME format a30

column PHYRDS format 999,999

column PHYWRTS format 999,999

column READTIM format 999,999

column "READ AVG (ms)" format 999.99

column "TOTAL I/O" format 999,999

select

FILE_NAME,

PHYRDS,

READTIM,

PHYWRTS,

READTIM / (PHYRDS + 1) "READ AVG (ms)",

PHYRDS + PHYWRTS "TOTAL I/O"

from

V$FILESTAT a,

DBA_DATA_FILES b

where

a.FILE# = b.FILE_ID

order by

6 DESC;

-- find_tbs.sql

-- *************************************************

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

-- *************************************************

/* find_tbs.sql */

-- Find the name of a tablespace that

-- a particular datafile belongs to.

-- Prompts user for at least a partial file name.

column TABLESPACE_NAME format a15

column FILE_NAME format a30

column STATUS format a10

select

TABLESPACE_NAME,

FILE_NAME,

STATUS

from

DBA_DATA_FILES

where

FILE_NAME like '%&partial_file_name%'

order by

FILE_NAME;

-- find_db_user.sql

-- *************************************************

-- 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 spid format 999999

column sid format 99999

column serial# format 99999

column username format a15

accept unix_pid prompt 'Enter the UNIX process ID: '

select

spid,

sid,

a.serial#,

a.username "User in DB",

b.username "User in OS"

from

v$session a, v$process b

where

paddr = addr

and

spid = '&unix_pid';

-- show_session_sql.sql

-- *************************************************

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

-- *************************************************

/* show_session_sql.sql */

select

sql_text

from

v$sqltext

where

hash_value in (

select

sql_hash_value

from

v$session

where

sid = '&sid')

order by

piece;

-- trace_other_12.sql

-- *************************************************

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

-- *************************************************

/* trace_other_12.sql */

-- This script will make sure timed statistics

-- and max_dump_file_size are set correctly in

-- preparation for tracing a specific session.

-- Then this script will start level 12 tracing

accept sid prompt 'Enter the SID: '

accept serial prompt 'Enter the SERIAL#: '

execute sys.dbms_system.set_bool_param_in_session (&sid, &serial, 'timed_statistics', TRUE);

execute .dbms_system.set_int_param_in_session (&sid, &serial, 'max_dump_file_size', 2000000000);

execute dbms_support.start_trace_in_session (&sid, &serial, waits=>TRUE, binds=>TRUE);

-- parse_v_execute.sql

-- *************************************************

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

-- *************************************************

/* parse_v_execute.sql */

column sql_text format a50

column parse_calls format 9,999,999

column executions format 9,999,999

column ratio format 999.99 heading "Parse-Execute|Ratio"

column loaded_versions XE "loaded_versions" format 999,999 heading "Loaded|Versions"

set pages 1000

select

sql_text,

parse_calls,

executions,

parse_calls/executions "Ratio",

loaded_versions XE "loaded_versions"

from

v$sql XE "v$sql"

where

parsing_user_id !=0

and

parse_calls > 500

and

executions > 500

and

rownum < 21

order by

4 desc,

3 desc;

-- parse_v_execute.sql

-- *************************************************

-- 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 sql_text format a50

column parse_calls format 9,999,999

column executions format 9,999,999

column ratio format 999.99 heading "Parse-Execute|Ratio"

column loaded_versions XE "loaded_versions" format 999,999 heading "Loaded|Versions"

set pages 1000

select

sql_text,

parse_calls,

executions,

parse_calls/executions "Ratio"

from

v$sql XE "v$sql"

where

parsing_user_id !=0

and

parse_calls > 500

and

executions > 500

and

rownum < 21

order by

4 desc,

3 desc;

-- sp_bhr_trend.sql

-- *************************************************

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

-- *************************************************

/* sp_bhr_trend.sql */

column bhr format 9.99

column mydate heading 'Year Mo Dy Ht'

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

perfstat.stats$buffer_pool_statistics old,

perfstat.stats$buffer_pool_statistics new,

perfstat.stats$snapshot sn

where

new.name = old.name

and

new.snap_id = sn.snap_id

and

old.snap_id = sn.snap_id - 1

and

sn.snap_time >= (sysdate -7)

order by

1 asc;

-- sp_sorts_ratio_trend.sql

-- *************************************************

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

-- *************************************************

/* sp_sorts_ratio_trend.sql */

column SNAP_DATE heading "Year Mo Day Hour" format a16

column MEMORY_SORTS format 999,999,999

column DISK_SORTS format 999,999,999

column "DISK/MEMORY RATIO" format 999.99

select

to_char(snap_time, 'yyyy-mm-dd HH24') SNAP_DATE,

memnew.value-memold.value MEMORY_SORTS,

disknew.value-diskold.value DISK_SORTS,

(((disknew.value-diskold.value) /

(memnew.value-memold.value)) * 100)

"DISK/MEMORY RATIO"

from

perfstat.stats$sysstat MEMOLD,

perfstat.stats$sysstat MEMNEW,

perfstat.stats$sysstat DISKNEW,

perfstat.stats$sysstat DISKOLD,

perfstat.stats$snapshot sp

where

disknew.snap_id = sp.snap_id

and

diskold.snap_id = sp.snap_id-1

and

memnew.snap_id = sp.snap_id

and

memold.snap_id = sp.snap_id-1

and

memold.name = 'sorts (memory)'

and

memnew.name = 'sorts (memory)'

and

diskold.name = 'sorts (disk)'

and

disknew.name = 'sorts (disk)'

and

memnew.value - memold.value > 0

and

disknew.value - diskold.value > 0

and

trunc(sp.snap_time) > '&Start_Time_dd_mon_yy'

order by

snap_date

;

-- segment_info.sql

-- *************************************************

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

segment_name,

partition_name,

segment_type,

tablespace_name,

sum(bytes)/1024/1024 "MBytes"

from

dba_extents

where

segment_name = upper('&Segment_Name')

and

owner = upper ('&Owner')

group by

segment_name,

partition_name,

segment_type,

tablespace_name;

-- db_file_wait_histograms.sql

-- *************************************************

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

-- *************************************************

/* db_file_wait_histograms.sql */

column event form a30

column wait_time_milli HEAD "WAIT|TIME|(ms)"

column wait_count HEAD "WAIT|COUNT"

select

event,

wait_time_milli,

wait_count

from

v$event_histogram

where

event like 'db%';

-- eventmetric.sql

-- *************************************************

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

-- *************************************************

/* eventmetric.sql */

column INTSIZE_CSEC format 99999 heading INTERVAL|SIZE

column EVENT# format 9999

column NUM_SESS_WAITING format 9999 heading NUMBER|SESSIONS|WAITING

column TIME_WAITED format 999999 heading TIME|WAITED

column WAIT_COUNT format 9999 heading WAIT|COUNT

select

to_char(begin_time, 'DD/MON/YYYY HH24:MI:SS') "BEGIN",

to_char(end_time, 'DD/MON/YYYY HH24:MI:SS') "END",

intsize_csec,

event#,

event_id,

num_sess_waiting,

time_waited,

wait_count

from

v$eventmetric

where

num_sess_waiting > 0

time_waited > 0

wait_count > 0

order by

time_waited DESC;

-- event_info.sql

-- *************************************************

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

-- *************************************************

/* event_info.sql */

column event# format 9999

column name format a35

column wait_class_id format 99999999999

column wait_class format a15

select

event#,

name ,

wait_class_id,

wait_class

from

v$event_name

where

event# = &event;

-- waitclassmetric.sql

-- *************************************************

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

-- *************************************************

/* waitclassmetric.sql */

column INTSIZE_CSEC format 99999 heading INTERVAL|SIZE

column WAIT_CLASS# format 99999 heading WAIT|CLASS

column DBTIME_IN_WAIT format 999999 heading "DBTIME|IN WAIT"

column AVERAGE_WAITER_COUNT format 9999 heading "AVG #|WAITERS"

column TIME_WAITED format 999999 heading TIME|WAITED

column WAIT_COUNT format 99999 heading WAIT|COUNT

select

to_char(begin_time, 'DD/MON/YYYY HH24:MI:SS') "BEGIN",

to_char(end_time, 'DD/MON/YYYY HH24:MI:SS') "END",

intsize_csec,

wait_class#,

wait_class_id,

average_waiter_count,

time_waited,

wait_count

from

v$waitclassmetric

where

average_waiter_count > 0

time_waited > 0

wait_count > 0

order by

time_waited DESC;

-- wait_class_info.sql

-- *************************************************

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

wait_class# ,

wait_class

from

v$system_wait_class

where

wait_class# in (&class);

-- session_ash.sql

-- *************************************************

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

-- *************************************************

/* session_ash.sql */

/* This reports information on object being waited on */

/* when the wait is I/O related */

select a.CURRENT_OBJ#, a.CURRENT_FILE#, a.CURRENT_BLOCK#,

b.SID, b.SERIAL#, b.USERNAME, b.OSUSER

from v$active_session_history a, v$session b

where

a.CURRENT_OBJ# = b.row_wait_obj#

and a.CURRENT_FILE# = b.row_wait_file#

and a.CURRENT_BLOCK# = b.row_wait_block#

and a.SESSION_SERIAL# = b.SERIAL#

and a.SESSION_ID = b.SID;

-- find_object_name.sql

-- *************************************************

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

-- *************************************************

/* find_object_name.sql */

column OWNER format a10

column OBJECT_NAME format a25

column OBJECT_ID format 999999

column OBJECT_TYPE format a25

select

OWNER,

OBJECT_NAME,

OBJECT_ID,

OBJECT_TYPE,

CREATED

from

dba_objects

where

OBJECT_ID = &object_id;

-- ash_top_10.sql

-- *************************************************

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

-- *************************************************

/* ash_top_10.sql */

column sql_text format a120 WRAP

column session_id form 9999 head SID

column session_serial# form 9999 head SERIAL#

column sql_text format a120 WRAP

column sample_time format a10

column username format a10

column session_state format a10 head SESSION|STATE

column event format a30

column p1 format 999999999

column p2 format 9999999

column p3 format 9999999

select

trunc(SAMPLE_TIME),

SESSION_ID,

SESSION_SERIAL#,

USERNAME,

EVENT,

P1,

P2,

P3,

WAIT_TIME,

TIME_WAITED,

SESSION_STATE,

SQL_TEXT

from

v$active_session_history a,

dba_users b,

v$sql c

where

a.user_id = b.user_id

and

a.sql_id = c.sql_id

and

rownum < 11

and

a.user_id != 0

order by

wait_time DESC,

time_waited DESC;

-- awr_list_snaps.sql

-- *************************************************

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

-- *************************************************

/* awr_list_snaps.sql */

column SNAP_ID format 999999

column BEGIN_INTERVAL_TIME format a25

column END_INTERVAL_TIME format a25

column SNAP_LEVEL format 99 head LEVEL

select

snap_id,

begin_interval_time,

end_interval_time,

snap_level

from

dba_hist_snapshot

order by 1;

-- awr_settings.sql

-- *************************************************

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

-- *************************************************

/* awr_settings.sql */

column snap_interval format a25

column retention format a25

select

snap_interval,

retention

from

dba_hist_wr_control;

-- awr_settings_change.sql

-- *************************************************

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

-- *************************************************

/* awr_change_settings.sql */

accept retention_minutes prompt "Enter retention in minutes (10080=7 days)"

BEGIN

DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(

retention => &retention_minutes,

interval => &interval_minutes

);

END;

@awr_settings.sql

-- show_addm_objects.sql

-- *************************************************

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

-- *************************************************

/* show_addm_objects.sql */

/* list objects that have been reference by an advisor */

set linesize 132

column attr4 format a50 WRAP

column type format a15 WRAP

column task_name format a25 WRAP

column object_id format 99999 head OBJ|ID

select

object_id,

type,

task_name,

attr4

from

dba_advisor_objects;

-- show_addm_actions.sql

-- *************************************************

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

-- *************************************************

/* show_addm_actions.sql */

/* list actions that have been recommended by an advisor */

set linesize 132

column message format a60 WRAP

column command format a15 WRAP

column task_name format a25 WRAP

column object_id format 99999 head OBJ|ID

column action_id format 99999 head ACTION|ID

select

object_id,

action_id,

command,

message

from

dba_advisor_actions;

-- show_addm_findings.sql

-- *************************************************

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

-- *************************************************

/* show_addm_findings.sql */

set lines 132

set pages 99

column task_name format a23

column impact_type format a40

column message format a100 WRAP

select

task_name,

type,

impact_type,

impact,

message

from

dba_advisor_findings

where

message not like 'There was no significant database%'

and

type = 'PROBLEM'

and

impact > 1000

order by

impact;

-- show_addm_recommendations.sql

-- *************************************************

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

-- *************************************************

/* show_addm_recommendations.sql */

set lines 132

set pages 99

column task_name format a25

column type format a25

column rank format 9999

column benefit format 999999999

select

task_name,

type,

rank,

benefit

from

dba_advisor_recommendations

where

task_name = ‘&task_name’

order by

task_name,

rank;

-- show_addm_rationale.sql

-- *************************************************

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

-- *************************************************

/* show_addm_rationale.sql */

column MESSAGE format a55

column IMPACT_TYPE format a30

column IMPACT format 9,999,999,999

select

task_name,

impact_type,

impact,

message

from

dba_advisor_rationale

where

task_name = '&task_name';

-- show_addm_tasks_in_error.sql

-- *************************************************

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

-- *************************************************

/* show_addm_tasks_in_error.sql */

column ADVISOR_NAME format a10 WRAP head ADVISOR|NAME

column DESCRIPTION format a40

column STATUS_MESSAGE format a40

select

task_name,

description,

advisor_name,

status,

status_message

from

dba_advisor_tasks

where

status like '%ERROR%';

-- session_waits.sql

-- *************************************************

-- Free for non-commercial use!

-- To license, e-mail info@rampant.cc

-- ************************************************

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 = 'PCS'

and

sess.sid = se.sid

group by

se.event

order by 2 DESC

-- ash_event_hist.sql

-- *************************************************

-- Free for non-commercial use!

-- To license, e-mail info@rampant.cc

-- ************************************************

select

swh.seq#,

sess.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_sql.sql

-- *************************************************

-- Free for non-commercial use!

-- To license, e-mail info@rampant.cc

-- ************************************************

select

h.sql_id,

s.sql_text

from

v$active_session_history h,

v$sql s

where

h.session_id = 74 AND

h.sql_id = s.sql_id AND

TRUNC(h.sample_time) = TRUNC(SYSDATE) AND

s.sql_fulltext like ‘%ORDERS%’

-- ash_event_count.sql

-- *************************************************

-- Free for non-commercial use!

-- To license, e-mail info@rampant.cc

-- ************************************************

select

h.sql_id,

count(*)

from

v$active_session_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

-- ash_wait_time.sql

-- *************************************************

-- Free for non-commercial use!

-- To license, e-mail info@rampant.cc

-- ************************************************

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_total_wait_time_dy.sql

-- *************************************************

-- Free for non-commercial use!

-- To license, e-mail info@rampant.cc

-- ************************************************

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_wait_time.sql

-- *************************************************

-- Free for non-commercial use!

-- To license, e-mail info@rampant.cc

-- ************************************************

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_file_wait_time_dy.sql

-- *************************************************

-- Free for non-commercial use!

-- To license, e-mail info@rampant.cc

-- ************************************************

select

f.file_name “Data File”,

COUNT(*) “Wait Number”,

SUM(h.time_waited) “Total Time Waited”

from

dba_hist_active_sess_history h,

dba_data_files f

where

h.current_file# = f.file_id

group by

f.file_name

order by 3 DESC

-- ash_wait_time_sum.sql

-- *************************************************

-- Free for non-commercial use!

-- To license, e-mail info@rampant.cc

-- ************************************************

select

e.name "Wait Event",

SUM(h.wait_time + h.time_waited) "Total Wait Time"

from

dba_hist_active_sess_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

-- ash_total_wait_time_dy.sql

-- *************************************************

-- Free for non-commercial use!

-- To license, e-mail info@rampant.cc

-- ************************************************

select

s.sid,

s.username,

SUM(h.wait_time + h.time_waited) "total wait time"

from

dba_hist_active_sess_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_hot_objects.sql

-- **************************************************

select

o.owner,

o.object_name,

o.object_type,

SUM(h.wait_time + h.time_waited) "total wait time"

from

dba_hist_active_sess_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

-- ash_waitclass_waits.sql

-- *************************************************

break on begin_time skip 1

column wait_class format a15

select

begin_time,

wait_class,

average_waiter_count,

dbtime_in_wait

from

dba_hist_waitclassmet_history

where

dbtime_in_wait >10

order by

begin_time,

wait_class,

average_waiter_count DESC;

-- ash_list_events.sql

-- ************************************************

break on wait_class skip 1

column event_name format a40

column wait_class format a20

select

event_name,

wait_class

from

dba_hist_event_name

order by

wait_class,

event_name;

-- ash_physrds.sql

-- ************************************************

break on begin_interval_time skip 1

column phyrds format 999,999,999

column begin_interval_time format a25

column file_name format a45

select

begin_interval_time,

filename,

phyrds

from

dba_hist_filestatxs

natural join

dba_hist_snapshot

order by

begin_interval_time

;

-- ash_latch.sql

-- ***********************************************

break on begin_interval_time skip 1

column begin_interval_time format a25

column latch_name format a40

select

begin_interval_time,

latch_name,

gets,

misses,

sleeps

from

dba_hist_latch

natural join

dba_hist_snapshot

where

(misses + sleeps ) > 0

order by

begin_interval_time,

misses DESC,

sleeps DESC

;

-- ash_trend.sql

-- ***********************************************

break on begin_interval_time skip 0

column stat_name format a25

select

begin_interval_time,

new.stat_name,

(new.value - old.value) “Difference”

from

dba_hist_sys_time_model old,

dba_hist_sys_time_model new,

dba_hist_snapshot ss

where

new.stat_name = old.stat_name

and

new.snap_id = ss.snap_id

and

old.snap_id = ss.snap_id - 1

and

new.stat_name like '%&stat_name%'

order by

begin_interval_time;

-- ash_enqueues.sql

-- **********************************************

column begin_interval_time format a25

column req_reason format a25

column cum_wait_time head CUM|WAIT|TIME

column total_req# head TOTAL|REQ#

column total_wait# head TOTAL|WAIT#

column failed_req# head FAILED|REQ#

select

begin_interval_time,

eq_type,

req_reason,

total_req#,

total_wait#,

succ_req#,

failed_req#,

cum_wait_time

from

dba_hist_enqueue_stat

natural join

dba_hist_snapshot

where

cum_wait_time > 0

order by

begin_interval_time;

Page updated

Google Sites

Report abuse