Performance Diagnostics using STATSPACK data

1
Hotsos Symposium 2006

• Performance Diagnostics using STATSPACK data
• 08-March 2006
• Tim Gorman
• SageLogix, Inc.

2
Agenda

• Configuring STATSPACK optimally
• About the STATSPACK repository
• Analysis using the STATSPACK repository

3
Configuring STATSPACK

• Whats missing from the standard installation
  script spcreate.sql in ORACLE_HOME/rdbms/admin
  ?
• Purging
• Configuration of data sampling levels and
  thresholds
• Purging STATSPACK data
• Cant retain data forever, after all
• Recommendation retain at least one major
  business-cycle of performance data
• Standard script sppurge.sql must be run
  manually
• Recommendation Script sppurpkg.sql available
  at http//www.EvDBT.com/tools.htm
• EXEC SPPURPKG.PURGE(14)
• Oracle10g finally includes a procedure providing
  similar functionality
• EXEC STATSPACK.PURGE(TRUNC(SYSDATE - 14), TRUE)

4
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
• SQL execution plans
• 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

5
Snap Level

• Level 0 (i.e. any value lt 5)
• 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, 6, and 7 plus detailed parent/child
  latch statistics
• MetaLink note 149121.1 Gathering a STATSPACK
  Snapshot

6
Data gathering thresholds

• SQL statements are gathered if any of these
  thresholds are exceeded

7
Data gathering thresholds

• Segment-level statistics are gathered if any of
  these thresholds are exceeded

8
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
• SQLPlus script spparms.sql (found online at
  http//www.EvDBT.com/tools.htm) provides code for
  updating SQL thresholds in the STATSSTATSPACK_PAR
  AMETER table
• Uses settings suggested in the MetaLink note
• Also sets SNAP_LEVEL

9
Using STATSPACK

• But using STATSPACK isnt all about the reports
  provided by Oracle
• The single main report requires some advance
  knowledge of a time period in which problems are
  occurring
• Keep in mind that there is an amazing repository
  of information that can used for broad general
  analysis as well as specific targeted
  investigations
• Think of some interesting questions that arise
  during a troubleshooting session
• What changed between then and now?
• Is the current behavior an anomaly or normal for
  this environment?
• How much of resource XXX are we using? How much
  have we used over time?

10
Using STATSPACK

• In general, STATSPACK data will not provide a
  specific answer to a specific solution
• It is too high-level, too aggregated
• However, it can provide a general idea
• Enough to zero in on what should be examined more
  closely
• So..
• Use STATSPACK data to
• Generalize the problem(s)
• Eliminate irrelevancies (!!!!)
• View trends from a high level
• Use extended SQL Tracing (event 10046, level gt 1)
  to
• Examine individual processes minutely
• Determine exactly what is happening in a specific
  process

11
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 STATSSNAPSHOT
• 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 STATSSNAPSHOT
• SNAP_ID, DBID, INSTANCE_NUMBER

12
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

13
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

14
snapshots and cumulative data

• Cumulative data needs to be converted into
  deltas somehow

15
Analytic windowing functions

• LAG() function to the rescue!
• LAG (ltexprgt, ltoffsetgt, ltdefaultgt)
• 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

16
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 lttable-namegt
• where
• order by

17
Analytic windowing functions

• If current is greater than (or equal to) previous
• then use delta
• else use current
• Ifgt decode(greatest(value,
• lag(value, 1, 0) over
• (partition by dbid,
• 
  instance_number,
• name
• order by snap_id)
• Equals gt value,
• Then gt value - lag(value, 1, 0) over
• (partition by dbid,
• 
  instance_number,
• name
• order by snap_id),
• Else gt value)

18
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-by-hour, where the database instance is
  spending the most time

19
sp_systime_9i.sql

• Daily breakdown (top 10 time consumers)
• Service,
  Non-Idle
• Idle,
  Seconds of
• Day or Wait Name
  Spent Total
• ------ -------- ----------------------------------
  - ------------------- -------
• 22-AUG 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 2.38
• Service Parsing SQL
  382.46 0.89
• Wait log file parallel write
  343.28 0.80
• Wait log file sync
  179.09 0.42
• Wait control file parallel write
  111.12 0.26

20
sp_systime_9i.sql

• Hourly breakdown (top 3 time-consumers)
• Service,
  Non-Idle
• Idle,
  Seconds of
• Day Hour or Wait Name
  Spent Total
• ------ ------ -------- ---------------------------
  -------- ------------------- -------
• 28-AUG 1700 Service SQL execution
  270.65 84.62
• Wait log file sync
  10.87 3.40
• Service Parsing SQL
  7.16 2.24
• 1800 Service SQL execution
  189.92 82.56
• Wait log file sync
  9.79 4.26
• Wait db file sequential read
  5.70 2.48
• 1900 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

21
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

22
top_stmt4_9i.sql

• Beginning Snap Time 11/29/04 110003
  Page 1
• Ending Snap Time 12/01/04 100002
  Nbr of Snapshots 48
• Date of Report 12/01/04 105530
  Total Logical Reads 580,110,532
  Total
  Physical Reads 2,816,050
• .
• Module " ? _at_ihe3 (TNS V1-V3)"
• .
• SQL Statement Text (Hash Value1397201706)
  
• ------------------------------------------
• 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

23
top_stmt4_9i.sql

• . SQL execution plan from "11/29/04 110003"
  (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

24
Additional scripts

• Generic reporting enablement
• sp_delta_views.sql
• Based on STATSSQL_SUMMARY and STATSSQL_PLAN
• top_stmt4_10g.sql, top_stmt4_9i.sql,
  top_stmt4_8i.sql, run_top_stmt4.sql
• sphistory.sql
• Based on STATSSEG_STAT STATSSEG_STAT_OBJ
• sp_buffer_busy_waits.sql, sp_itl_waits.sql,
  sp_row_lock_waits.sql, sp_gc_waits.sql
• Based on STATSPARAMETER
• sp_parm_changes.sql
• Based on STATSSYSSTAT
• sptrends.sql
• Based on STATSSYSTEM_EVENT
• sp_evtrends.sql
• Based on STATSLATCH_MISSES
• sp_latch_misses.sql

25
General methods

• Start with sp_systime report for initial
  high-level analysis on a day-by-day basis, with
  hour-by-hour detail
• Whatever consumes the most time, follow up with
  other analysis scripts
• If SQL execution, Recursive SQL execution,
  db file read events are significant, find
  top N SQL using TOP_STMT4 report
• If latch free, drill down with
  sp_latch_misses.sql
• If buffer busy waits, drill down with
  sp_buffer_busy_waits.sql
• If row lock waits, drill down with
  sp_row_lock_waits.sql
• If RAC (GC) waits, drill down with
  sp_gc_waits.sql
• If ITL waits, drill down with sp_itl_waits.sql
• If redo wait-events, check out
  sp_avg_redo_write_time.sql
• For trending on other wait events, use
  sp_evtrends.sql script
• For trends on statistics, use sptrends.sql
  script

26
QA

• Questions?
• Tim_at_SageLogix.com
• Website http//www.SageLogix.com/
• Scripts and presentation can be downloaded from
• http//www.EvDBT.com/papers.htm
• http//www.EvDBT.com/tools.htm
• Else, email me -)