How to Statspack

Need to crontab your statspack?

10g

Statspack replaced by Automatic Workload Repository. NO MORE NEED OF PERFSTAT
execute dbms_workload_repository.create_snapshot    
 
begin
  #default is once every hour, retention 7 days
  dbms_workload_repository.modify_snapshot_settings (interval => 60 /* minutes */, retention => 30*24*60 /* 30 days */);
end;

Report
sqlplus /
@$ORACLE_HOME/rdbms/admin/awrrpt.sql
Analyze your text report http://www.statspackanalyzer.com/analyze.asp

9i

Installing
select to_char(snap_time, 'dd/mm/yyyy hh24:mi') from perfstat.STATS$SNAPSHOT order by snap_time;
SQL> CONNECT / AS SYSDBA

SQL> @?/rdbms/admin/spdrop

SQL>@?/rdbms/admin/spcreate

[stats.sql]
begin
statspack.snap(i_snap_level=>6);
end;
/
exit;

[stats.ksh] then: chmod +x stats.ksh
#!/bin/ksh
export ORACLE_SID=$1
export ORACLE_BASE=/u01/app/oracle
export TNS_ADMIN=$ORACLE_BASE/admin/$ORACLE_SID/network/admin
export ORACLE_HOME=$ORACLE_BASE/product/9.2.0
export PATH=$ORACLE_HOME/bin:$ORACLE_BASE/admin/scripts:$PATH
export TMPDIR=/tmp
export TMP=$TMPDIR
export TMP_DIR=$TMPDIR
sqlplus "perfstat/perfstat" @/u01/app/oracle/local/PERFSTAT/stats.sql

[crontab]
52 * * * * /u01/app/oracle/local/PERFSTAT/stats.ksh DBSPINT> /u01/app/oracle/local/PERFSTAT/cronDBSPINT.log 2>&1

 

Take a snap

sqlplus>EXECUTE STATSPACK.SNAP(i_snap_level=>6);

 

Report SPREPORT
sqlplus "perfstat/perfstat"

select snap_id, snap_time  from STATS$SNAPSHOT
where 
--snap_time >= to_date('05/04/2008 13:20', 'dd/mm/yyyy hh24:mi')
--and snap_time <= to_date('06/04/2008 13:20', 'dd/mm/yyyy hh24:mi')
trunc(snap_time)=trunc(sysdate)
order by snap_time desc

@?/rdbms/admin/spreport.sql
oppure
sqlplus>define begin_snap=1
sqlplus>define end_snap=2
sqlplus>define report_name=batch_run
sqlplus>@spreport.sql

 

Report SPREPSQL

Use

sqlplus>EXECUTE STATSPACK.SNAP(i_snap_level=>6);

to take snap(no less than 6).

Once you have a report select an hash value from a costly sql (suppose 1988538571). Now take the plan of that statement.

sqlplus>define begin_snap=39

sqlplus>define end_snap=40

sqlplus>define hash_value=1988538571

sqlplus>define report_name=batch_sql_run

sqlplus>@sprepsql

 

Statistics on PERFSTAT schema

EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>'PERFSTAT',CASCADE=>TRUE);

or

EXECUTE DBMS_UTILITY.ANALYZE_SCHEMA('PERFSTAT','COMPUTE');

 

Save new parameters

Taking a snap:

SQL> EXECUTE STATSPACK.SNAP(i_snap_level=>10, i_modify_parameter=>'true');

Just save

EXECUTE STATSPACK.MODIFY_STATSPACK_PARAMETER (i_snap_level=>10, i_buffer_gets_th=>10000, i_disk_reads_th=>1000);

 

Take a snapshot for a session

SQL> EXECUTE STATSPACK.SNAP(i_session_id=>3);

 

Parameters

Parameter Name Range of Valid Values Default Value Meaning

i_snap_level 0, 5, 6, 7, 10 5 Snapshot level

i_ucomment Text Blank Comment to be stored with snapshot

i_executions_th Integer >=0 100 SQL threshold: number of times statement was executed

i_disk_reads_th Integer >=0 1000 SQL threshold: number of disk reads the statement made

i_parse_calls_th Integer >=0 1000 SQL threshold: number of parse calls the statement made

i_buffer_gets_th Integer >=0 10000 SQL threshold: number of buffer gets the statement made

i_sharable_mem_th Integer >=0 1048576 SQL threshold: amount of sharable memory

i_version_count_th Integer >=0 20 SQL threshold: number of versions of a SQL statement

i_seg_phy_reads_th Integer >=0 1000 Segment statistic threshold: number of physical reads on a segment

i_seg_log_reads_th Integer >=0 10000 Segment statistic threshold: number of logical reads on a segment

i_seg_buff_busy_th Integer >=0 100 Segment statistic threshold: number of buffer busy waits for a segment

i_seg_rowlock_w_th Integer >=0 100 Segment statistic threshold: number of row lock waits for a segment

i_seg_itl_waits_th Integer >=0 100 Segment statistic threshold: number of ITL waits for a segment

i_seg_cr_bks_sd_th Integer >=0 1000 Segment statistic threshold: number of consistent reads blocks served by the instance for the segment (RAC)

i_seg_cu_bks_sd_th Integer >=0 1000 Segment statistic threshold: number of current blocks served by the instance for the segment (RAC)

i_session_id Valid SID from V$SESSION 0 (no session) Session ID of the Oracle session for which to capture session granular statistics

i_modify_parameter TRUE, FALSE FALSE Determines whether the parameters specified are used for future snapshots

 

Export PERFSTAT user

Use the official way

>cd $ORACLE_HOME/rdbms/admin

>exp userid=perfstat/perfstat parfile=spuexp.par

or this one, with parfile on single line

>exp userid=perfstat/perfstat file=spuexp.dmp log=spuexp.log compress=y grants=y indexes=y rows=y constraints=y owner=PERFSTAT consistent=y

 

Purge old data

>cd $ORACLE_HOME/rdbms/admin

Login as perfstat user

Delete

sqlplus>@sppurge.sql

or

SQL> DEFINE losnapid=1

SQL> DEFINE hisnapid=2

sql>@sppurge.sql

Truncate

login as perfstat

sql>@sptrunc.sql

 

Remove statspack

SQL> CONNECT / AS SYSDBA

SQL> @?/rdbms/admin/spdrop

Query over Perfstat

select stats$snapshot.snap_id, stats$snapshot.snap_time, stats$snapshot.snap_level
, elapsed_time, cpu_time
, stats$sql_summary.module, stats$sql_summary.executions, 
stats$sql_summary.disk_reads, decode(stats$sql_summary.executions, 0, stats$sql_summary.disk_reads
, stats$sql_summary.disk_reads/stats$sql_summary.executions)disk_reads_on_executions 
, stats$sql_summary.buffer_gets, stats$sql_summary.rows_processed 
, perfstat.getStatement(stats$sql_summary.hash_value) clob_statement 
, stats$sql_summary.hash_value, stats$sql_summary.address  
, stats$sql_summary.rowid sql_summary_rowid
, (select cost from stats$sql_plan_usage where snap_id = stats$snapshot.snap_id and dbid = stats$snapshot.dbid
   and hash_value = stats$sql_summary.hash_value and rownum=1) cost 
, stats$snapshot.dbid
from stats$sql_summary, stats$snapshot 
where stats$snapshot.snap_id (+)= stats$sql_summary.snap_id
--and stats$snapshot.snap_id = 1365
order by elapsed_time desc

 

Oracle 9i - Total waits over last period

Remember that value increasing is normal in the event of no instance restart

select trunc(snap_time) day
--, stats$system_event.event
, to_char(avg(stats$system_event.time_waited_micro), 'FM99999999999999999') avg_time_waited 
from stats$system_event, stats$snapshot, stats$idle_event
where stats$snapshot.snap_id (+)= stats$system_event.snap_id
and stats$idle_event.event (+)= stats$system_event.event
and stats$idle_event.event is null
--and stats$system_event.event in ('SQL*Net message from dblink')
group by trunc(snap_time)
--, stats$system_event.event
order by trunc(snap_time) desc, avg(stats$system_event.time_waited_micro) desc

 

Oracle 8i - Total waits over last period

select trunc(snap_time) day
--, stats$system_event.event
, to_char(avg(stats$system_event.time_waited), 'FM99999999999999999') avg_time_waited 
from stats$system_event, stats$snapshot, stats$idle_event
where stats$snapshot.snap_id (+)= stats$system_event.snap_id
and stats$idle_event.event (+)= stats$system_event.event
and stats$idle_event.event is null
--and stats$system_event.event in ('SQL*Net message from dblink')
group by trunc(snap_time)
--, stats$system_event.event
order by trunc(snap_time) desc, avg(stats$system_event.time_waited) desc

 

System event

You can paste results to this template System_event.xls

select event, avg_time_waited from (
	select 
	a.*
	,  decode(stats$idle_event.event, null, '', 'IDLE') event_type
	from (
		select stats$system_event.event, avg(stats$system_event.total_waits) avg_total_waits, 
		avg(stats$system_event.total_timeouts) avg_total_timeouts
		--, avg(stats$system_event.time_waited) avg_time_waited
		, to_char(avg(stats$system_event.time_waited_micro), 'FM99999999999999999') avg_time_waited
		from stats$system_event, stats$snapshot 
		where stats$snapshot.snap_id (+)= stats$system_event.snap_id
		-- CHANGE THE DATE HERE 
		and snap_time > trunc(sysdate - 5)
		group by event
	) a , stats$idle_event
	where stats$idle_event.event (+)= a.event
	order by to_number(avg_time_waited) desc
)where event_type is null

 

Waitstat

select * from ( 
	select stats$waitstat.class WAIT_CLASS, avg(wait_count) avg_wait_count, avg(time) avg_time
	from stats$waitstat, stats$snapshot 
	where stats$snapshot.snap_id (+)= stats$waitstat.snap_id
	and snap_time > trunc(sysdate - 7)
	group by class
) order by avg_time desc

Operational Statspack

grant create trigger to perfstat;
grant create view to perfstat;

Install WriteToFile.java.sql

Allow users to get complete statement by hash
    
CREATE OR REPLACE function PERFSTAT.getStatement(mhash number) return clob is
		 result clob;
		 cursor c(m_hash number) is
		 select sql_text from stats$sqltext
		 where hash_value = mhash
		 order by piece;
		 sqlline c%rowtype;
		 lengthLine number;
begin
	 dbms_lob.createtemporary(result,true);
	 open c(mhash);
      fetch c into sqlline;
      while (c%found) loop
	     lengthLine := length(sqlline.sql_text);
         dbms_lob.writeappend(result, lengthLine, sqlline.sql_text);
         fetch c into sqlline;
      end loop;
      close c;
	  
	  return result;
end;
/

grant execute on PERFSTAT.getStatement to public;

--With another user
select PERFSTAT.getStatement(12345678) from dual;

--Set long 999999 on sqlplus, TOAD recommended

 

Oracle 9i
CREATE OR REPLACE VIEW VSTATS$COMPLETE_SUMMARY AS 
select stats$snapshot.snap_id, stats$snapshot.snap_time, stats$snapshot.snap_level
, stats$sql_summary.module, stats$sql_summary.executions, 
stats$sql_summary.disk_reads, decode(stats$sql_summary.executions, 0, stats$sql_summary.disk_reads
, stats$sql_summary.disk_reads/stats$sql_summary.executions)disk_reads_on_executions 
, stats$sql_summary.buffer_gets, stats$sql_summary.rows_processed 
, perfstat.getStatement(stats$sql_summary.hash_value) clob_statement 
, stats$sql_summary.hash_value, stats$sql_summary.address 
, 'insert into perfstat.stats$signalled_statement(SNAP_ID, SNAP_TIME, SNAP_LEVEL, MODULE, EXECUTIONS, DISK_READS, DISK_READS_ON_EXECUTIONS, BUFFER_GETS,'||
  'ROWS_PROCESSED, CLOB_STATEMENT, HASH_VALUE, ADDRESS) (select SNAP_ID, SNAP_TIME, SNAP_LEVEL, MODULE, EXECUTIONS,'|| 
  'DISK_READS, DISK_READS_ON_EXECUTIONS, BUFFER_GETS, ROWS_PROCESSED, CLOB_STATEMENT,'|| 
  'HASH_VALUE, ADDRESS from perfstat.vstats$complete_summary where sql_summary_rowid = ''' 
  || stats$sql_summary.rowid || ''');' || chr(13)||chr(10) || chr(13)||chr(10) || 
  'update perfstat.stats$signalled_statement set progressive = :progressive where hash_value = ' || stats$sql_summary.hash_value insert_signalled 
, stats$sql_summary.rowid sql_summary_rowid
, stats$signalled_statement.signal_date
, stats$signalled_statement.progressive progressive_signalled
, (select cost from stats$sql_plan_usage where snap_id = stats$snapshot.snap_id and dbid = stats$snapshot.dbid
   and hash_value = stats$sql_summary.hash_value) cost 
, getTableAccessDescByHash(stats$snapshot.snap_id, stats$snapshot.dbid, stats$sql_summary.address, 
    stats$sql_summary.hash_value) full_table_accesses
from stats$sql_summary, stats$snapshot, stats$signalled_statement 
where stats$snapshot.snap_id (+)= stats$sql_summary.snap_id
and stats$signalled_statement.hash_value(+)= stats$sql_summary.hash_value

 

Oracle 8i
CREATE OR REPLACE VIEW VSTATS$COMPLETE_SUMMARY AS 
select stats$snapshot.snap_id, stats$snapshot.snap_time, stats$snapshot.snap_level
, stats$sql_summary.module, stats$sql_summary.executions, 
stats$sql_summary.disk_reads, decode(stats$sql_summary.executions, 0, stats$sql_summary.disk_reads
, stats$sql_summary.disk_reads/stats$sql_summary.executions)disk_reads_on_executions 
, stats$sql_summary.buffer_gets, stats$sql_summary.rows_processed 
, perfstat.getStatement(stats$sql_summary.hash_value) clob_statement 
, stats$sql_summary.hash_value, stats$sql_summary.address 
, 'insert into perfstat.stats$signalled_statement(SNAP_ID, SNAP_TIME, SNAP_LEVEL, MODULE, EXECUTIONS, DISK_READS, DISK_READS_ON_EXECUTIONS, BUFFER_GETS,'||
  'ROWS_PROCESSED, CLOB_STATEMENT, HASH_VALUE, ADDRESS) (select SNAP_ID, SNAP_TIME, SNAP_LEVEL, MODULE, EXECUTIONS,'|| 
  'DISK_READS, DISK_READS_ON_EXECUTIONS, BUFFER_GETS, ROWS_PROCESSED, CLOB_STATEMENT,'|| 
  'HASH_VALUE, ADDRESS from perfstat.vstats$complete_summary where sql_summary_rowid = ''' 
  || stats$sql_summary.rowid || ''');' || chr(13)||chr(10) || chr(13)||chr(10) || 
  'update perfstat.stats$signalled_statement set progressive = :progressive where hash_value = ' || stats$sql_summary.hash_value insert_signalled 
, stats$sql_summary.rowid sql_summary_rowid
, stats$signalled_statement.signal_date
, stats$signalled_statement.progressive progressive_signalled 
from stats$sql_summary, stats$snapshot, stats$signalled_statement 
where stats$snapshot.snap_id (+)= stats$sql_summary.snap_id
and stats$signalled_statement.hash_value(+)= stats$sql_summary.hash_value

 

CREATE TABLE STATS$SIGNALLED_STATEMENT
(
  SNAP_ID                   NUMBER(6),
  SNAP_TIME                 DATE,
  SNAP_LEVEL                NUMBER,
  MODULE                    VARCHAR2(64),
  EXECUTIONS                NUMBER,
  DISK_READS                NUMBER,
  DISK_READS_ON_EXECUTIONS  NUMBER,
  BUFFER_GETS               NUMBER,
  ROWS_PROCESSED            NUMBER,
  CLOB_STATEMENT            CLOB,
  HASH_VALUE                NUMBER,
  ADDRESS                   RAW(8),
  SIGNAL_DATE               DATE                DEFAULT sysdate               NOT NULL,
  PROGRESSIVE               NUMBER unique
)
CREATE UNIQUE INDEX SIGNALLED_UNIQUE ON STATS$SIGNALLED_STATEMENT
(HASH_VALUE);

 

Oracle 8i
CREATE OR REPLACE VIEW VSTATS$SIGNALLED_STATEMENT
AS 
select a.* 
, (sysdate - last_date) days_not_running 
from ( 
	select stats$signalled_statement.* 
	, (select max(stats$snapshot.snap_time) from stats$sql_summary, stats$snapshot 
	   where stats$snapshot.snap_id = stats$sql_summary.snap_id and 
	   hash_value = stats$signalled_statement.hash_value) last_date 
	from stats$signalled_statement 
) a;

 

Oracle 9i
CREATE OR REPLACE function getTableAccessDesc(mplan_hash_value number) return varchar2 is
  cursor c is
    select 
    object_owner||'.'||object_name||' cost='||cost line
    from stats$sql_plan
    where operation = 'TABLE ACCESS' and options = 'FULL'
    and plan_hash_value = mplan_hash_value;
  cr c%rowtype;
  result varchar2(4000);
begin
	 open c;
	 fetch c into cr;
      while (c%found) loop
	  	 if length(result) > 0 then result := result || ', ';
		 end if;
         result := result || cr.line;
         fetch c into cr;
      end loop;
      close c;
	  if((result is null)or(length(trim(result)) = 0)) then
	  			return 'No fulls';
	  else return result;
	  end if;
end;
/
CREATE OR REPLACE function getTableAccessDescByHash(msnap_id number, mdbid number,
	   maddress raw, mhash_value number) return varchar2 is
	   mplan_hash_value number;
begin
	 begin
		 select plan_hash_value into mplan_hash_value 
		 from stats$sql_plan_usage
		 where snap_id = msnap_id and dbid = mdbid and address = maddress 
		 and hash_value = mhash_value;
	 exception
	 		  when no_data_found then
			    null;
	 end;
	 
	 if(mplan_hash_value is not null) then
	   return getTableAccessDesc(mplan_hash_value);
	 else return 'No plan';
	 end if;
end;
/

 

Oracle 9i
CREATE OR REPLACE VIEW VSTATS$SIGNALLED_STATEMENT AS 
select a.* 
, (sysdate - last_date) days_not_running 
from ( 
	select stats$signalled_statement.* 
	, (select max(stats$snapshot.snap_time) from stats$sql_summary, stats$snapshot 
	   where stats$snapshot.snap_id = stats$sql_summary.snap_id and 
	   hash_value = stats$signalled_statement.hash_value) last_date
	, (select cost from stats$sql_plan_usage where snap_id = stats$signalled_statement.snap_id 
	   and dbid = (select dbid from stats$snapshot where snap_id = stats$signalled_statement.snap_id)
       and hash_value = stats$signalled_statement.hash_value
	   ) cost 
  , getTableAccessDescByHash(stats$signalled_statement.snap_id
  , (select dbid from stats$snapshot where snap_id = stats$signalled_statement.snap_id)
  , stats$signalled_statement.address, 
    stats$signalled_statement.hash_value) full_table_accesses 
	from stats$signalled_statement 
) a; 


select plan_hash_value
from stats$sql_plan_usage
where 
--snap_id = 1 and 
hash_value = 3590221687

select 
    object_owner||'.'||object_name||' cost='||cost line
    from stats$sql_plan
    where operation = 'TABLE ACCESS' and options = 'FULL'
    and plan_hash_value = 1866792406 
	--and snap_id = 9;

select getTableAccessDesc(1866792406) from dual

 

select SNAP_ID, SNAP_TIME, SNAP_LEVEL, MODULE, EXECUTIONS, 
 DISK_READS, DISK_READS_ON_EXECUTIONS, BUFFER_GETS, ROWS_PROCESSED
 --, CLOB_STATEMENT
 , HASH_VALUE, ADDRESS, INSERT_SIGNALLED, SQL_SUMMARY_ROWID, SIGNAL_DATE, 
 PROGRESSIVE_SIGNALLED
from VSTATS$COMPLETE_SUMMARY
where VSTATS$COMPLETE_SUMMARY.snap_time > sysdate - 1
order by VSTATS$COMPLETE_SUMMARY.disk_reads desc