Docstoc

Using Statspack As A Performance Datawarehouse

Document Sample
Using Statspack As A Performance Datawarehouse Powered By Docstoc
					RMOUG Training Days 2005


       Using STATSPACK
      as a Performance DW
           10-February 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      100                Same
waits”
Number of row-lock waits    100                Same
Number of ITL waits         100                1

Number of CR/CU blocks      1,000              Same
shipped (RAC)
                           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” is found online at
     http://ocs.sagelogix.com/files/content/AllPublic/Workspaces/Sc
     ripts-Public/sql/spparms.sqlprovides 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
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 &      STATS$STREAMS_APPLY_SUM
…_APPLY_READER
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       VALUE_INC

   4355     10-FEB 2005       875543322
            13:00
   4356     10-FEB 2005       875543421   99
            14:00
   4357     10-FEB 2005       875648888   105467
            15:00
   4358     10-FEB 2005       933322178   57673290
            16:00
   4359     10-FEB 2005       933322333   155
            17:00
                    www.SageLogix.com
 Analytic windowing functions
“LAG()” function to the rescue!

             , fse >,     l >)
  LAG (<expr> <of t <defau t
  O VER (
      PARTITION BY c   lause
      O R D E R BY clause
                                      l
      [ RO W S | RAN G E ] windowing c ause
  )

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_t ime,
      value,
            lue      )
      lag(va , 1, 0 over
            tt         id
        (pariion by db ,
                  ns
                 i tance_number
                 name
          r
         o der by snap_ id)
from     tab
        < le-name>
where …
order by …

                      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-by-
hour, where the database instance is spending the most
time
                     www.SageLogix.com
           sp_systime_9i.sql
        Daily breakdown (top 10 time consumers)

               ice
           Serv ,                                           Id
                                                        Non- le
       le
     Id ,                                           Seconds % o        f
         r    t
Day o W ai Name                                                Spent To l   ta
 - - -- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - -- - - - - - - - - - - - - - - - - -- - - - - - - - - -- - - -
22-AU G Serv  ice S QL execut       ion                               ,
                                                                   28 842.92 67.39
          i
      Serv ce Recurs    ive SQL execut      ion                     4,480.52 10.47
      Wai         i
          t db fle sequent read ial                           3,598.26 8     .41
      Wai         i         l l i
          t db fle para le wrte                            2,441.00 5      .70
      Wai     i t
          t d rec path read                                1,096.21 2      .56
      Wai         i        te
          t db fle scat red read                              1,019.07 2     .38
          i          i
      Serv ce Pars ng SQL                                      382 .46 0    .89
          t        i        l l i
      Wai log fle para le wrte                               343 .28 0    .80
          t        i
      Wai log fle sync                                     179.09 0.42
      Wai         t l ie         le i
          t con ro fl para l l wrte                            111 .12 0   .26



                                                 www.SageLogix.com
             sp_systime_9i.sql
          Hourly breakdown (top 3 time-consumers)

                         ice
                     Serv ,                                            Id
                                                                   Non- le
             l
           Id e,                                         Seconds % o        f
                 r
Day Hour o Wai Na me  t                                                Spent Tota   l
 - - -- - - - - - -- - - - - - - - - - - - - - - - - -- - - - - - - - - -- - - -
- - - - - -- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
28-AU G 17:00 Serv     ice SQL execut       ion                                  .
                                                                             270 65 84.62
               i    og ie
            Wa t l fl sync                                         .
                                                                10 87 3     .40
           Serv ice Pars ing SQL                                      7.16 2   .24
      18:00 Serv  ice SQL executon    i                                 189 .92 82.56
               i    og ie
            Wa t l fl sync                                       9.79 4    .26
               i       ie
            Wa t db fl sequent lread ia                               5.70 2    .48
      19:00 Serv  ice SQL executon    i                                 106 .07 81.84
               i    og ie
            Wa t l fl sync                                       4.85 3    .74
               i       ie
            Wa t db fl sequent lread ia                               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
   inn
Beg ing Snap T ime:          11         :00 03
                               /29/04 11 :                          Page 1
Ending Snap Time :          12/01/04 10 :02
                                       :00                      f         :
                                                           Nbr o Snapshots 48
      f     t
Date o Repor :             /01      :55
                         12 /04 10 :30         To l Logi l Reads
                                                 ta     ca        :   580,110,532
                                                         ta       ica
                                                       To l Phys l Reads   :    2,816,050
.
        :
Module " ? @ihe3 (      TNS V1-V3)    "
.
S QL Sta tement Tex (t Hash Va    lue=1397201706)
 ---------------------
---------------------
0                     l
        SELECT leve ,ser         id      t_ , ies_name, type
                            ies_ , paren id ser                 ,
1         t a t_ lag ac lta r_page, ser
     cons r in f , f ii to                         t_3 isp
                                            ies_tex , d lay_order
                         es_ , i                     t tor_
2 _num, master_seri id seres_keyword, ins ruc id FRO M c
3          i ins
     m_ser es_ tance           WHERE reg_code = :  reg_code     AND type =
4    :block_type              ta        t ve
                      AN D s tus = 'ac i '           START WITH pa
        t_                                 t_
5 ren id = 0 CON NE CT BY paren id = PRIOR ser             ies_id
.
:     Disk        f r
               Bu fe Cache H t   i        DR Per BG Per CPU Per E Per   la
:     Reads        Gets      Ratio Runs       Run      Run     Run     Run
:     --
     -- -     - - -- - - - - - - - - - - - - - - - - - -
              -- - - - - - -- -- - -- - -- - -- -
: 2,040,353 253,437,801 99 % 3     .19     ,071     664 82,526     1.47    8.06
: (72 .454%) (43    .688 %)




                                                 www.SageLogix.com
               top_stmt4_9i.sql
             ion lan rom "11
. SQL execut p f                     :00 03" (snap #481)
                            /29/04 11 :
 ------------------------------------------------
------------------------------------------------
|Id | Operation            | Name                          tes
                                               | Rows | By |Cost (%CPU)  |
 ------------------------------------------------
------------------------------------------------
 *
| 0 | SELECT STATE M E NT           |               |    |     |   |
 *
| 1| F ILTER               |               |     |    |       |
 *
| 2 | CON NE CT BY WITH FILTERING |                      |     | |     |
 *
| 3 | NESTED LO O PS             |                |    |    |    |
 *
| 4 | INDEX RANG E SCAN            | CM_SERIES_INSTAN CE_IDX4 | 69 | 207 | 1 (0 |   )
 *
| 5 | TABLE ACCESS BY USER RO WID | CM_SERIES_INSTAN CE              |    |  |       |
 *
| 6 | NESTED LO O PS             |                |    |    |    |
 *
| 7 | BUFFER SORT               |                | 69 | 8418 |     |
 *
| 8|     CO N NE CT BY PU MP       |                |    |    |    |
 *
| 9 | TABLE ACCESS BY INDEX RO WID| CM_SERIES_INSTAN CE              | 69 | 8418 | 2 (50)|
 *        N
| 10 | I DEX RAN G E SCAN           | CM_SERIES_INSTANC E_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
Based on STATS$SYSSTAT
   sptrends.sql
Based on STATS$SYSTEM_EVENT
   spevtrends.sql

                            www.SageLogix.com
                          Q&A


                     Questions?
    Niklas@SageLogix.com and Tim@SageLogix.com
          Website: http://www.SageLogix.com/

        This presentation can be downloaded from
                  http://www.rmoug.org/

              Scripts can be downloaded from
http://ocs.sagelogix.com/files/content/AllPublic/Workspaces/
                        Scripts-Public/sql/
                        www.SageLogix.com

				
DOCUMENT INFO