Gorman - Using Statspack As A Performance Data Warehouse
This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA
Overview
Download & View Gorman - Using Statspack As A Performance Data Warehouse as PDF for free.
More details
- Words: 2,039
- Pages: 34
Hotsos Symposium 2005
Using STATSPACK as a Performance DW 06-09 March 2005 Niklas Iveslatt Tim Gorman SageLogix, Inc. 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
Installing 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
Configuring 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
Configuring 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
Data gathering thresholds 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
Data gathering thresholds 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
Data gathering thresholds 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
Init Parameters 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 repository 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 repository 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 repository 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 repository 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 repository - 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 repository - 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 repository 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
“snapshots” and cumulative data 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
“snapshots” and cumulative data 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
“snapshots” and cumulative data 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
Analytic windowing functions “LAG()” function to the rescue! LAG (<expr>,, <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
Analytic windowing functions select snap_id, snap_time, value, lag(value, 1, 0) over (partition by dbid, instance_number name order by snap_id) prev_value from where … order by … www.SageLogix.com
Analytic windowing functions 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_systime_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_systime_9i.sql Daily breakdown (top 10 time consumers) Service, NonIdle Idle, Seconds % of Day or Wait Name Spent Total 22AUG Service SQL execution 28,842.92 67.39 Service Recursive SQL execution 4,480.52 10.47 Wait db file sequential read 3,598.26 8.41 Wait db file parallel write 2,441.00 5.70 Wait direct path read 1,096.21 2.56 Wait db file scattered read 1,019.07 www.SageLogix.com 2.38
sp_systime_9i.sql Hourly breakdown (top 3 time-consumers) Service, NonIdle Idle, Seconds % of Day Hour or Wait Name Spent Total 28AUG 17:00 Service SQL execution 270.65 84.62 Wait log file sync 10.87 3.40 Service Parsing SQL 7.16 2.24 18:00 Service SQL execution 189.92 82.56 Wait log file sync 9.79 4.26 Wait db file sequential read 5.70 2.48 19:00 Service SQL execution 106.07 81.84 Wait log file sync 4.85 3.74 Wait db file sequential read 4.46 3.44
See listing… www.SageLogix.com
top_stmt4_9i.sql Latest in a line of stored procedures to produce a “top N SQL statements” 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
top_stmt4_9i.sql Beginning Snap Time: 11/29/04 11:00:03 Page 1 Ending Snap Time : 12/01/04 10:00:02 Nbr of Snapshots: 48 Date of Report : 12/01/04 10:55:30 Total Logical Reads: 580,110,532 Total Physical Reads: 2,816,050 . Module: " ? @ihe3 (TNS V1V3)" . 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 Ela Per : Reads Gets Ratio Runs Run Run Run Run : : 2,040,353 253,437,801 99.19% 3,071 664 82,526 1.47 8.06 : (72.454%) (43.688%)
www.SageLogix.com
top_stmt4_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
Additional scripts 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
(see listing)
Based on STATS$SYSSTAT sptrends.sql
Based on STATS$SYSTEM_EVENT sp_evtrends.sql www.SageLogix.com
Q&A Questions? [email protected] and [email protected] 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
Using STATSPACK as a Performance DW 06-09 March 2005 Niklas Iveslatt Tim Gorman SageLogix, Inc. 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
Installing 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
Configuring 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
Configuring 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
Data gathering thresholds 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
Data gathering thresholds 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
Data gathering thresholds 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
Init Parameters 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 repository 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 repository 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 repository 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 repository 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 repository - 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 repository - 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 repository 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
“snapshots” and cumulative data 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
“snapshots” and cumulative data 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
“snapshots” and cumulative data 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
Analytic windowing functions “LAG()” function to the rescue! LAG (<expr>,
)
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
Analytic windowing functions select snap_id, snap_time, value, lag(value, 1, 0) over (partition by dbid, instance_number name order by snap_id) prev_value from
Analytic windowing functions 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_systime_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_systime_9i.sql Daily breakdown (top 10 time consumers) Service, NonIdle Idle, Seconds % of Day or Wait Name Spent Total 22AUG Service SQL execution 28,842.92 67.39 Service Recursive SQL execution 4,480.52 10.47 Wait db file sequential read 3,598.26 8.41 Wait db file parallel write 2,441.00 5.70 Wait direct path read 1,096.21 2.56 Wait db file scattered read 1,019.07 www.SageLogix.com 2.38
sp_systime_9i.sql Hourly breakdown (top 3 time-consumers) Service, NonIdle Idle, Seconds % of Day Hour or Wait Name Spent Total 28AUG 17:00 Service SQL execution 270.65 84.62 Wait log file sync 10.87 3.40 Service Parsing SQL 7.16 2.24 18:00 Service SQL execution 189.92 82.56 Wait log file sync 9.79 4.26 Wait db file sequential read 5.70 2.48 19:00 Service SQL execution 106.07 81.84 Wait log file sync 4.85 3.74 Wait db file sequential read 4.46 3.44
See listing… www.SageLogix.com
top_stmt4_9i.sql Latest in a line of stored procedures to produce a “top N SQL statements” 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
top_stmt4_9i.sql Beginning Snap Time: 11/29/04 11:00:03 Page 1 Ending Snap Time : 12/01/04 10:00:02 Nbr of Snapshots: 48 Date of Report : 12/01/04 10:55:30 Total Logical Reads: 580,110,532 Total Physical Reads: 2,816,050 . Module: " ? @ihe3 (TNS V1V3)" . 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 Ela Per : Reads Gets Ratio Runs Run Run Run Run : : 2,040,353 253,437,801 99.19% 3,071 664 82,526 1.47 8.06 : (72.454%) (43.688%)
www.SageLogix.com
top_stmt4_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
Additional scripts 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
(see listing)
Based on STATS$SYSSTAT sptrends.sql
Based on STATS$SYSTEM_EVENT sp_evtrends.sql www.SageLogix.com
Q&A Questions? [email protected] and [email protected] 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
Related Documents
Data Warehouse
November 2019 23
Data Warehouse
November 2019 18
Data Warehouse
October 2019 19
Data Warehouse
November 2019 23
Data Warehouse
June 2020 7More Documents from "rajashekarpula"
Adams - Hash Joins Oracle
May 2020 6
Hotsos_2005_owi_10g
May 2020 5
Sql Techniques
May 2020 5
Schrag - Oracle 8i To 10g Upgrade Case Study
May 2020 7
Josepsson - Performance Accountability
May 2020 4