Hot sos Symposium 2005
Using STATSPACK as a Perf ormance DW 06- 09 March 2005 Niklas I veslatt Tim Gorman SageLogix, I nc.
www.SageLogix.com
Agenda • • •
Installing, configuring, and using STATSPACK About the STATSPACK repository Analysis using the STATSPACK repository
www.SageLogix.com
What is STATSPACK? A free application provided in the Oracle RDBMS But not installed by default It is an optional component of the RDBMS
MetaLink note #94224.1 (FAQ: STATSPACK Reference)
STATSPACK samples information from V$ views Uses the DBMS_JOB job-scheduling system within the RDBMS Saves the “snapshots” into tables for subsequent reporting Implemented completely in PL/SQL Portable to every platform supported by Oracle database
Introduced in Oracle8i v8.1.6 New versions introduced in 8.1.7, 9.0.1, 9.2.0, 10.1.0 Each new version provides an upgrade script from earlier versions
MetaLink note #165420.1 (Install/Run STATSPACK for 8.0.x)
www.SageLogix.com
I nst alling STATSPACK MetaLink note #149113.1 (Installing and Configuring STATSPACK) All source and documentation located in directory “$ORACLE_HOME/rdbms/admin”: spdoc.txt - installation and configuration instructions spcreate.sql - installation script Requires one tablespace to be specified as a default tablespace for the PERFSTAT user References scripts spcusr.sql, spctab.sql, and spcpkg.sql Gottcha: spcusr.sql tries to re-create dbms_shared_pool and dbms_job!!!! (comment them out if already installed – IMPORTANT)
spdrop.sql - de-installation script Best approach to installation trouble-shooting is re-install References scripts spdusr.sql and spdtab.sql
spauto.sql - standard script to implement hourly “snapshots” Once per hour at the top of the hour, all the time…
spuexp.par - parameter file for EXP utility
www.SageLogix.com
Conf igur ing STATSPACK What’s missing from the standard installation scripts? Purging Configuration of data sampling levels and thresholds
Purging STATSPACK data Can’t retain data forever, after all… Recommendation: retain at least one major business-cycle’s worth of performance data
Standard script “sppurge.sql” must be run manually… Recommendation: package SPPURPKG is an automated version of “sppurge.sql” intended to be called from DBMS_JOB package Script “sppurpkg.sql” available online http://ocs.sagelogix .com/files/content/AllPublic/Workspaces/Scripts-Public/sql/sppurpkg.sql Accepts number-of-days as a parameter Deletes all STATSPACK data older than the specified parameter value
www.SageLogix.com
Conf igur ing STATSPACK Configuration of data sampling levels and thresholds Snap Level Thresholds
Snap level determines which information is gathered Basic database performance statistics SQL statement activity Segment-level I/O statistics Latch details
Thresholds keep too much information from being gathered Some gathered information can scheduled below specified thresholds SQL statement activity Segment-level I/O statistics
www.SageLogix.com
Snap Level Level 0
Database performance statistics only
Level 5
Level 0 plus SQL execution info Default level of data gathering for all versions of STATSPACK
Recommended level for Oracle8i and below..
Level 6 (introduced in Oracle9i)
Level 5 plus SQL Plan info
Level 7 (introduced in Oracle9i)
Level 6 plus segment-level usage info
Recommended level for Oracle9i and above…
Level 10
Level 5 or 7 plus detailed parent/child latch statistics Use only on advise of Oracle Support MetaLink note #149121.1 Gathering a STATSPACK Snapshot
www.SageLogix.com
Dat a gat her ing t hr esholds SQL statements are gathered if any of these thresholds are exceeded Threshold
Default value
For busy systems, recommended value
Number of executions
100
1,000
Number of parse calls
1,000
same
Number of disk reads
1,000
10,000
Number of buffer gets
10,000
100,000
Size of sharable memory
1 Mbyte
same
Version count
20
same
www.SageLogix.com
Dat a gat her ing t hr esholds Segment-level statistics are gathered if any of these thresholds are exceeded Threshold
Default value
For busy systems, recommended value
Number of physical reads
1,000
Same
Number of logical reads
10,000
Same
Number of “buffer busy waits”
100
Same
Number of row-lock waits
100
Same
Number of ITL waits
100
1
Number of CR/CU blocks shipped (RAC)
1,000
Same
www.SageLogix.com
Dat a gat her ing t hr esholds Metalink note #153507.1 - Oracle Applications and STATSPACK Contains good suggested threshold values for a busy and complex database But the note does not suggest a good method for setting the parameters SQL*Plus script “spparms.sql” (found online at the URL on the last slide of the presentation) provides code for updating SQL thresholds in the STATS$STATSPACK_PARAMETER table Uses settings suggested in the MetaLink note… Also sets SNAP_LEVEL…
www.SageLogix.com
I nit Par amet er s As of 9.2, statistics_level affects amount of data gathered. (ALL, TYPICAL, BASIC) High level parameter Override using timed_statistics, db_cache_advice, + more. Affects content of statspack report.
timed_statistics Statspack little use without.
db_cache_advice 10% – 200% size hit ratio report Powerful tool when recommending memory upgrade/downgrade to your manager.
www.SageLogix.com
That being said… spreport.sql – reads the statspack tables and generates a report a.k.a. “the statspack report” within specified snapshot intervals http://www.oraperf.com -- for response/service time analysis sprepsql.sql – Displays the execution plan(s)
www.SageLogix.com
Using STATSPACK But using STATSPACK isn’t all about the reports provided by Oracle There is an amazing repository of information that can used for broad general analysis as well as specific targeted investigations In general, data in STATSPACK will not provide the solution It is too high-level, too aggregated Generally, it will provide a general idea of what is going on Often, session tracing using 10046 (sql trace) is necessary to understand exactly why a problem exists and how it can be fixed
But sometimes, we can get lucky‌ :-) www.SageLogix.com
STATSPACK r eposit or y 8i V$ view name
Oracle8i STATSPACK
V$SYSTEM_EVENT
STAT$BG_EVENT_SUMMARY
V$SYSTEM_EVENT
STATS$SYSTEM_EVENT
V$BUFFER_POOL_STATISCS
STATS$BUFFER_POOL_STATISTICS
V$LOCK
STATS$ENQUEUESTAT
V$FILESTAT
STATS$FILESTATXS
V$TEMPSTAT
STATS$TEMPSTATXS
V$LATCH
STATS$LATCH
V$LATCH_CHILDREN
STATS$LATCH_CHILDREN
V$LATCH_PARENT
STATS$LATCH_PARENT
V$LIBRARYCACHE
STATS$LIBRARYCACHE
www.SageLogix.com
STATSPACK r eposit or y 8i V$ view name
Oracle8i STATSPACK
V$SYSTEM_EVENT
STAT$BG_EVENT_SUMMARY
V$SYSTEM_EVENT
STATS$SYSTEM_EVENT
V$BUFFER_POOL_STATISCS
STATS$BUFFER_POOL_STATISTICS
V$LOCK
STATS$ENQUEUESTAT
V$FILESTAT
STATS$FILESTATXS
V$TEMPSTAT
STATS$TEMPSTATXS
V$LATCH
STATS$LATCH
V$LATCH_CHILDREN
STATS$LATCH_CHILDREN
V$LATCH_PARENT
STATS$LATCH_PARENT
V$LIBRARYCACHE
STATS$LIBRARYCACHE
www.SageLogix.com
STATSPACK r eposit or y 8i V$ view name
Oracle8i STATSPACK
V$PARAMETER
STATS$PARAMETER
V$ROLLSTAT
STATS$ROLLSTAT
V$ROWCACHE
STATS$ROWCACHE
V$SGA
STATS$SGA
V$SGASTAT
STATS$SGASTAT
V$WAITSTAT
STATS$WAITSTAT
V$SESSTAT
STATS$SESSTAT
V$SESSION_EVENT
STATS$SESSION_EVENT
V$SQLAREA
STATS$SQL_SUMMARY
V$SQLTEXT
STATS$SQLTEXT
V$SYSTAT
STAT$SYSSTAT
www.SageLogix.com
STATSPACK r eposit or y - 9i V$ view name
Added to Oracle9i STATSPACK
V$DB_CACHE_ADVICE
STATS$DB_CACHE_ADVICE
V$DLM_MISC
STATS$DLM_MISC
V$INSTANCE_RECOVERY
STATS$INSTANCE_RECOVERY
V$PGASTAT
STATS$PGASTAT
V$PGA_TARGET_ADVICE
STATS$PGA_TARGET_ADVICE
V$SEG_STAT
STATS$SEG_STAT
DBA_OBJECTS
STATS$SEG_STAT_OBJ
V$SHARED_POOL_ADVICE
STATS$SHARED_POOL_ADVICE
V$SQL_PLAN
STATS$SQL_PLAN & …_USAGE
V$SQL_WORKAREA_HISTOGRAM STATS$SQL_WORKAREA_HISTOGRAM V$UNDOSTAT
STATS$UNDOSTAT
V$RESOURCE_LIMIT
STATS$RESOURCE_LIMIT
www.SageLogix.com
STATSPACK r eposit or y - 10g V$ view name
Added to Oracle10g STATSPACK
V$SYS_TIME_MODEL
STATS$SYS_TIME_MODEL
V$SESS_TIME_MODEL
STATS$SESS_TIME_MODEL
V$STREAMS_CAPTURE
STATS$STREAMS_CAPTURE
V$STREAMS_APPLY_SERVER & …_APPLY_READER
STATS$STREAMS_APPLY_SUM
V$PROPAGATION_SENDER
STATS$PROPAGATION_SENDER
V$PROPAGATION_RECEIVER
STATS$PROPAGATION_RECEIVER
V$BUFFERED_QUEUES
STATS$BUFFERED_QUEUES
V$BUFFERED_SUBSCRIBERS
STATS$BUFFERED_SUBSCRIBERS
V$RULE_SET
STATS$RULE_SET
V$OSSTAT
STATS$OSSTAT
V$CR_BLOCK_SERVER
STATS$CR_BLOCK_SERVER
www.SageLogix.com
STATSPACK r eposit or y - 10g V$ view name
Added to Oracle10g STATSPACK
V$CR_BLOCK_SERVER
STATS$CR_BLOCK_SERVER
V$CURRENT_BLOCK_SERVER
STATS$CURRENT_BLOCK_SERVER
V$CLASS_CACHE_TRANSFER
STATS$CLASS_CACHE_TRANSFER
V$JAVA_POOL_ADVICE
STATS$JAVA_POOL_ADVICE
V$THREAD
STATS$THREAD
V$FILE_HISTOGRAM
STATS$FILE_HISTOGRAM
V$TEMP_HISTOGRAM
STATS$TEMP_HISTOGRAM
V$EVENT_HISTOGRAM
STATS$EVENT_HISTOGRAM
www.SageLogix.com
STATSPACK r eposit or y Number of tables has expanded with each version About 30 tables in Oracle8i About 40 tables in Oracle9i About 55 tables in Oracle10g Not counting “control tables” used by STATSPACK itself
Each of these tables can be considered a FACT table in a subject area of a dimensional data model Lone dimension is STATS$SNAPSHOT “time” dimension
Each of the “fact” tables in the repository are keyed by SNAP_ID, which can be translated to SNAP_TIME by joining to STATS$SNAPSHOT SNAP_ID, DBID, INSTANCE_NUMBER
www.SageLogix.com
“snapshot s” and cumulat ive dat a Each time the packaged procedure STATSPACK.SNAP is run, it captures the current values in the V$ views Stores the current values in the corresponding STATS$ table SNAP_ID
SNAP_TIME
VALUE
4355
10-Feb 2005 13:00
875543322
4356
10-Feb 2005 14:00
875543421
4357
10-Feb 2005 15:00
875648888
4358
10-Feb 2005 16:00
933322178
4359
10-Feb 2005 17:00
933321333
www.SageLogix.com
“snapshot s” and cumulat ive dat a The standard STATSPACK report Calculates the “difference” or “deltas” between any two “snapshots” using PL/SQL logic
But this type of data prevents reporting and analysis across many snapshots Cannot simply summarize The cumulative data is not additive
Cannot analyze across instance restarts All statistics are reset to zero after restart
www.SageLogix.com
“snapshot s” and cumulat ive dat a Cumulative data needs to be converted into “deltas” somehow… SNAP_ID
SNAP_TIME
VALUE
4355
10-FEB 2005 13:00
875543322
4356
10-FEB 2005 14:00
875543421
99
4357
10-FEB 2005 15:00
875648888
105467
4358
10-FEB 2005 16:00
933322178
57673290
4359
10-FEB 2005 17:00
933322333
155
www.SageLogix.com
VALUE_INC
Analyt ic windowing f unct ions “LAG()” function to the rescue! LAG (<expr>, <offset>, <default>) OVER ( PARTITION BY clause ORDER BY clause [ ROWS | RANGE ] windowing clause
) Creates the concept of a “current row” in relationship to preceding rows A set of related rows is created with the PARTITION BY, ORDER BY, and windowing clauses www.SageLogix.com
Analyt ic windowing f unct ions select
from where order by
snap_id, snap_time, value, lag(value, 1, 0) over (partition by dbid, instance_number name order by snap_id) prev_value <table-name> â&#x20AC;Ś â&#x20AC;Ś
www.SageLogix.com
Analyt ic windowing f unct ions If current is greater than (or equal to) previous then use delta else use current If=> decode(greatest(value, lag(value, 1, 0) over (partition by dbid, instance_number, name order by snap_id) Equals => value, Then => value - lag(value, 1, 0) over (partition by dbid, instance_number, name order by snap_id), Else => value)
www.SageLogix.com
sp_ syst ime_ 9i.sql Written to mimic the top-level logic of the YAPP report http://www.oraperf.com/ Response-time = Service-time + Wait-time
Script “sp_systime_9i.sql” uses analytic windowing functions to produce this report LAG() function to calculate “deltas” between snapshots RANK() function to find the “top N” calculated “delta” values RATIO_TO_REPORT() function to calculate percentages on the returned “delta” values on the whole
The intent of the report is to show, day-by-day or hour-byhour, where the database instance is spending the most time www.SageLogix.com
sp_ syst ime_ 9i.sql Daily breakdown (top 10 time consumers) Service,
Non-Idle
Idle, of Day or Wait Total ------ -------------22-AUG Service 67.39 Service 10.47 Wait 8.41 Wait 5.70 Wait 2.56 Wait 2.38
Seconds Name
Spent
----------------------------------- ------------------SQL execution
28,842.92
Recursive SQL execution
4,480.52
db file sequential read
3,598.26
db file parallel write
2,441.00
direct path read
1,096.21
db file scattered read
1,019.07
www.SageLogix.com
%
sp_ syst ime_ 9i.sql Hourly breakdown (top 3 time-consumers) Service, Idle, Day Hour or Wait ------ ------ -------28-AUG 17:00 Service Wait Service 18:00 Service Wait Wait 19:00 Service Wait Wait
Non-Idle Seconds % of Name Spent Total ----------------------------------- ------------------- ------SQL execution 270.65 84.62 log file sync 10.87 3.40 Parsing SQL 7.16 2.24 SQL execution 189.92 82.56 log file sync 9.79 4.26 db file sequential read 5.70 2.48 SQL execution 106.07 81.84 log file sync 4.85 3.74 db file sequential read 4.46 3.44
See listingâ&#x20AC;Ś
www.SageLogix.com
t op_ st mt 4_ 9i.sql Latest in a line of stored procedures to produce a â&#x20AC;&#x153;top N SQL statementsâ&#x20AC;? report Can be sorted by: logical I/Os + (100 * physical I/Os) elapsed time logical I/Os physical I/Os
Each of these can be cumulative for the time period sampled or per execution
www.SageLogix.com
t op_ st mt 4_ 9i.sql Beginning Snap Time: Ending Snap Time : Date of Report : Total Physical Reads:
11/29/04 11:00:03 12/01/04 10:00:02 12/01/04 10:55:30
Page 1 Nbr of Snapshots: 48 Total Logical Reads: 580,110,532
2,816,050
. Module: " ? @ihe3 (TNS V1-V3)" . SQL Statement Text (Hash Value=1397201706) -----------------------------------------0 SELECT level, series_id, parent_id, series_name, type, 1 constraint_flag, facilitator_page, series_text_3, display_order 2 _num, master_series_id, series_keyword, instructor_id FROM c 3 m_series_instance WHERE reg_code = :reg_code AND type = 4 :block_type AND status = 'active' START WITH pa 5 rent_id = 0 CONNECT BY parent_id = PRIOR series_id . : Disk Buffer Cache Hit DR Per BG Per CPU Per : Reads Gets Ratio Runs Run Run Run : ---------- --------------------------: 2,040,353 253,437,801 99.19% 3,071 664 82,526 1.47 : (72.454%) (43.688%)
www.SageLogix.com
Ela Per Run -----8.06
t op_ st mt 4_ 9i.sql . SQL execution plan from "11/29/04 11:00:03" (snap #481) -----------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| -----------------------------------------------------------------------------------------------|* 0 | SELECT STATEMENT | | | | | |* 1 | FILTER | | | | | |* 2 | CONNECT BY WITH FILTERING | | | | | |* 3 | NESTED LOOPS | | | | | |* 4 | INDEX RANGE SCAN | CM_SERIES_INSTANCE_IDX4 | 69 | 207 | 1 (0)| |* 5 | TABLE ACCESS BY USER ROWID | CM_SERIES_INSTANCE | | | | |* 6 | NESTED LOOPS | | | | | |* 7 | BUFFER SORT | | 69 | 8418 | | |* 8 | CONNECT BY PUMP | | | | | |* 9 | TABLE ACCESS BY INDEX ROWID| CM_SERIES_INSTANCE | 69 | 8418 | 2 (50)| |* 10 | INDEX RANGE SCAN | CM_SERIES_INSTANCE_IDX4 | 69 | | 1 (0)| ------------------------------------------------------------------------------------------------
See listing…
www.SageLogix.com
Addit ional scr ipt s Generic reporting enablement sp_delta_views.sql Creates views that use LAG() to substitute “delta” values for all appropriate
Based on STATS$SEG_STAT & STATS$SEG_STAT_OBJ sp_buffer_busy_waits.sql sp_itl_waits.sql sp_row_lock_waits.sql sp_gc_waits.sql
Based on STATS$PARAMETER sp_parm_changes.sql
Based on STATS$SYSSTAT sptrends.sql
Based on STATS$SYSTEM_EVENT sp_evtrends.sql
www.SageLogix.com
(see listing)
Q&A Questions? Niklas@SageLogix.com and Tim@SageLogix.com Website: http://www.SageLogix.com/ Scripts and presentation can be downloaded from http://ocs.sagelogix.com/files/content/AllPublic /Workspaces/Scripts-Public/sql/
www.SageLogix.com