Docstoc

STATPACK8i

Document Sample
STATPACK8i Powered By Docstoc
					Diagnosing Performance with Statspack
New with Oracle8i Release 8.1.6, Statspack gives administrators and tuning specialists a tool for diagnosing system performance. / By Connie Dialeris and Graham Wood

F

or many years, the only performance-diagnostic tool included with Oracle Database Server software was a set of two scripts: UTLBSTAT.SQL and UTLESTAT.SQL. Together, BSTAT/ESTAT, as they’ve come to be known, form a very simple performance-diagnosis tool that captures a single snapshot of performance data between specified start and end times. However, the organization of the data in the resulting BSTAT/ESTAT report is difficult to read and interpret. In addition, the scripts haven’t been updated to reflect the growing number of features available in the Oracle database server product. To address these issues, Oracle is including Statspack, a new package of SQL scripts, with Release 8.1.6 of Oracle8i. The package not only addresses new database features but also captures additional performance data while providing more flexible reporting. The report output is also significantly easier to interpret. This article—part 1 of a two-part series—provides an overview of Statspack as well as an introduction to its installation and use. Part 2 will cover the analysis and interpretation of the data Statspack gathers.
W H AT IS S TAT S PA C K ?

Typically, there are two general approaches to tuning: proactive and reactive. Proactive tuning usually occurs during software analysis and design. Focusing on tuning at these early stages provides the biggest performance gains. In practice, however, most tuning is performed reactively, on either preproduction or production

systems. Statspack is specifically designed to simplify reactive tuning. To perform reactive tuning effectively, it is vital to have an established baseline or benchmark from when the system is running well to use to compare when the system is running poorly. Without baseline information, it is difficult to identify the source of the problem: Has the system’s transaction volume increased? Has the transaction profile changed? Has the number of users increased? Statspack collects statistical data according to your specifications and stores the data so that baseline information is always available for comparison to current information when performance problems arise. (Note: The statistics referenced in this article are gathered from the V$ performance views and not those the optimizer uses to choose SQL execution plans.) Because Statspack has been tailored to include new Release 8.1.6 features, you can’t use it with earlier releases; however, you can download pre-8.1.6compatible versions for trial. Statspack is also able to capture data about Oracle Parallel Server (OPS) instances; you can capture data about each instance of an OPS database simply by connecting to the instance you wish to monitor and

You can download pre-8.1.6compatible versions of Statspack from www.oracle.com/oramag/ for trial use. Note that these trial releases will not be shipped and are not supported. You can also download the Statspack 8.1.6 production release.

ORACLE

MA GAZINE

–

MARCH/APRIL 2000

|

1

taking a snapshot (see the “Data Collection” section on the next page). (Future releases of Statspack may include additional OPS-aware features.)
C O M PARI S ON W IT H B STAT / E S TAT

Statspack differs fundamentally from the well-known BSTAT/ESTAT tuning scripts in that it collects more information and stores the performance-statistics data permanently in Oracle tables, which can be used for later reporting and analysis. You can analyze the data collected by reviewing the Statspack report, which includes an “instance health and load” summary page, high-resource SQL statements, and the
TABL E 1 : S ta tsp ack and BSTAT / E S TAT Com pare d

Feature
Instance summary page Normalization of instance statistics by time and number of transactions Wait events High-resource SQL Instance-activity statistics Tablespace and file I/O statistics Buffer-wait breakdown by type Enqueue statistics Rollback-segment activity and storage data Latch activity Latch-sleep breakdown Latch children Buffer-pool statistics Dictionary-cache activity Library-cache activity SGA memory summary SGA memory breakdown Nondefault init.ora parameters Configurable output name Ability to move performance data Configurable amount of data collected Ability to run in multiple instances for Oracle Parallel Server
*Level 5 (default) data collection **Level 10 data collection

Statspack
Y Y Y Y* Y Y Y Y Y Y Y Y** Y Y Y Y Y Y Y Y Y Y

BSTAT/ESTAT
N N Y N Y Y Y N Y Y N N N Y Y N N Y N N N N

traditional wait events and initialization parameters. Table 1 shows a comparison of the data that Statspack and BSTAT/ESTAT collect and report. Another major difference is the manner in which the tools collect data. For example, BSTAT/ESTAT doesn’t separate the collection of data from the creation of the report: the ESTAT script automatically produces a report and then drops the transitory tables that held the collected data. But with Statspack, the datacollection and -reporting phases are totally distinct. You can initiate data collection on demand, or automate the collection process. Regardless of the approach, Statspack stores data in permanent tables, which you can then view at your convenience. The separation of these two phases and the storage of data in permanent tables enables you to base a report on specified data points. For example, you may wish to use the supplied automation script (statsauto.sql) to collect data hourly. If a performance issue arises later for which you’d rather look at a three-hour rather than a one-hour data window, simply specify the start and end points when you run the report. The Statspack report also looks very different from the ESTAT report. The first page is designed for easy identification of the key data about the system, which helps identify where to look in order to analyze performance. Listing 1 shows the first page of a Statspack report taken from a production database. The Load Profile area simplifies making comparisons with other data points, because it normalizes the load data by time and by the number of transactions. The Instance Efficiency area includes computed statistics such as the buffercache hit, soft-parse, and latch-hit ratios. The summary page also includes Top-5 Wait Events, which lists the top-five events waited for during the data-collection period. If timed_statistics is set to true, the

2

|

M A R C H / A P R I L 2 0 0 0 – W W W. O R A C L E . C O M / O R A M A G /

events are listed in order of the time spent waiting for the event. If timed_statistics is not set or is set to false, the events are listed in order of the number of waits. (In Listing 1, timed_statistics is set to true.)
LISTING 1
First page of a typical Statspack report highlights key data about the system. STATSPACK report for DB Name ------MAIL DB Id ---------4221457278 Instance -------MAIL Inst Num ----------1 Release --------8.1.6.0.0 OPS --YES Host -------mailhost

For performance diagnosis, it is always best to set timed_statistics to true in the init.ora parameter file; doing so lets you quickly identify what is slowing the instance and the server processes attached to the instance.
DATA C OL LE C T IO N : TAK IN G A SNAP SH OT

Start Id -------2327 Cache Sizes

End Id ------2329

Start Time ------------------24-Nov-99 11:00:26

Snap Length End Time (Minutes) ------------------------24-Nov-99 13:00:37 120.18

db_block_buffers: db_block_size: log_buffer: shared_pool_size: Load Profile

38000 8192 1048576 150M

Redo size: Logical reads: Block changes: Physical reads: Physical writes: User calls: Parses: Hard parses: Sorts: Transactions: Rows per Sort: Pct Blocks changed / Read: Recursive Call Pct: Rollback / transaction Pct:

Per Second -----------15,689.18 21,549.53 76.07 12.53 3.64 210.33 8.13 0.00 6.18 4.00 11.53 0.35 14.94 2.52

Per Transaction ---------------3,922.02 5,387.01 19.02 3.13 0.91 52.58 2.03 0.00 1.54

Instance Efficiency Percentages (Target 100%) Buffer Nowait Buffer Hit Library Hit Redo NoWait In-memory Sort Soft Parse Latch Hit Top 5 Wait Events Event -------------------------db file sequential read log file sync log file parallel write db file parallel write global cache cr request Waits -----67,254 28,252 28,310 2,681 32,825 Wait Time (cs) --------73,087 30,550 26,304 9,430 5,586 % Total Wt Time ------44.77 18.71 16.11 5.78 3.42 Ratio: Ratio: Ratio: Ratio: Ratio: Ratio: Ratio: 100.00 99.94 100.00 100.00 99.95 99.94 97.86

Statspack users will become familiar with the concept of a snapshot, which is a single collection of performance data for a database instance. (As used with Statspack, snapshot relates to performance-data collection and should not be confused with the snapshot feature in Oracle’s replication technology.) A data snapshot is identified by a snapshot ID, a unique number generated at the time Statspack takes a snapshot; each time Statspack produces a new collection, it generates a new snapshot ID. Choosing a Statistics Level DBAs can change the amount of information or detail of statistics Statspack gathers by specifying a snapshot level. The level you choose dictates how much data Statspack collects. Level 5 is the default. x Level 0: Statspack collects general performance statistics such as wait statistics, system events, system statistics, rollback-segment data, row cache, SGA, background events, session events, lock statistics, buffer-pool statistics, and parent latch data. x Level 5: Statspack collects all the statistics it gathers at level 0 plus performance data about high-resourceusage SQL statements. x Level 10: Statspack collects all the statistics from level 0 and level 5 as well as child-latch information. At level 10, the snapshot can sometimes take longer to gather data because level 10 can be resource-intensive. You should use it only on the advice of Oracle personnel. Levels 5 and 10 capture high-resource SQL statements that exceed any of the following four threshold parameters: x the number of executions of the SQL

3

|

M A R C H / A P R I L 2 0 0 0 – W W W. O R A C L E . C O M / O R A M A G /

statement (default = 100) x the number of disk reads the SQL statement performs (default = 1,000) x the number of parse calls the SQL statement performs (default = 1,000) x the number of buffer gets the SQL statement performs (default = 10,000) If a SQL statement’s resource usage exceeds any one of these threshold values, Statspack captures the statement when it takes a snapshot. The level and threshold values are stored in the table stats$statspack_ parameters. You can change or temporarily override the default values by specifying new values for the thresholds and levels when Statspack takes the snapshot.
I N S TA L LI N G S TAT S PA CK AN D CO LL E CTIN G DATA

Statspack is a set of SQL, PL/SQL, and SQL*Plus scripts that allow the collection, automation, storage, and viewing of performance data (see Table 2). The installation script (statscre.sql) calls several other scripts in order to create the entire Statspack environment. (Note: You should run only the installation script, not the base scripts that statscre.sql invokes.) All the scripts you need for installing and running Statspack are in the $ORACLE_HOME/rdbms/admin directory for
TA BL E 2 : Scr i pts Sup plie d w ith Stat sp ack

UNIX platforms and in %ORACLE_HOME%\ rdbms\admin for Microsoft Windows NT systems. Documentation for Statspack is in the file statspack.doc, which explains the steps required for installing and using Statspack. The installation script creates a PL/SQL package called statspack, which is the core of the system and contains the procedures for collecting statistics. When you run the report, the report package—a combination of SQL, PL/SQL, and SQL*Plus commands—calls the statspack package to perform data calculations. To install Statspack, connect to the Oracle database as internal and run the statscre.sql script. This step creates the user perfstat, who owns all the PL/SQL code and database objects created as well as the Statspack tables, constraints, and the statspack package. This user has limited, query-only privileges on the V$ views required for performance tuning. The script prompts you to specify the user perfstat’s default tablespace and temporary tablespace. It then asks you to specify the tablespace for the Statspack data-collection tables and indexes— Statspack does not assume you want to use the default tablespace. The simplest interactive way to take a snapshot is to log in to SQL*Plus as the

The files that comprise the Statspack performance tool are located in the $ORACLE_HOME/rdbms/admin directory.

Filename
statspack.doc

Script Type
documentation installation

Function
Contains instructions and information about Statspack Creates entire Statspack environment (calls statscusr.sql, statsctab.sql, statspack.sql) Drops entire Statspack environment (calls statsdtab.sql and statsdusr.sql) Generates a Statspack report Automates Statspack statistics collection, using dbms_job A sample export parameter file that you can use to export the perfstat user

Usage
Read all documentation before running install script Connect as

statscre.sql

SYS or INTERNAL to install

statsdrp.sql

installation

Connect as

SYS or INTERNAL to run

statsrep.sql statsauto.sql statsuexp.par

reporting automation performance-data moving

PERFSTAT to run Connect as PERFSTAT to run
Connect as Connect as

PERFSTAT to run

4

|

M A R C H / A P R I L 2 0 0 0 – W W W. O R A C L E . C O M / O R A M A G /

LISTING 2
Running a Statspack report. SQL> connect perfstat/perfstat Connected. SQL> @statsrep DB Id ----------4221457278 DB Name -------MAIL Instance# --------1 Instance -------MAIL

Completed Snapshots Instance -------MAIL SnapId Snap Started Snap Level -------------------------- ----------2326 24 Nov 1999 10:00:53 5 2327 24 Nov 1999 11:00:26 5 2328 24 Nov 1999 12:00:01 5 2329 24 Nov 1999 13:00:37 5 2330 24 Nov 1999 14:00:11 5 Enter beginning Snap Id: 2327 Enter ending Snap Id: 2329 Enter name of output file [st_2327_2329] : <enter name or return> DB Name ------MAIL

Use the Oracle dbms_job procedure within the database to schedule the snapshots. (Statspack includes the statsauto.sql script to assist you with this configuration. See “Using dbms_job to Automate Statistics Collection,” on the following page, for more information.) x Use an operating-system utility outside of the database, such as UNIX’s cron, to take the snapshot.
CON FI GU RI NG SNAP SH O T L EV EL AN D S QL THR E SH OLDS

x

owner perfstat and execute the statspack.snap procedure:
SQL> connect perfstat/perfstat SQL> execute statspack.snap;

This stores the current performance statistics in the Statspack tables; you can compare the data with other snapshots and begin to look for differences that account for performance problems. In order to compare performance from one day, week, or year to the next, you need multiple snapshots taken over a period of time. The simplest way to collect a series of snapshots is to automate data collection at regular times. You can do this in one of two ways:
TAB LE 3 : st ats pac k.s na p Pr o c e d u r e Pa r ame ter s

Although the default snapshot level is level 5, DBAs can change the default parameters to tailor them to the instance’s workload. To change the parameters, simply specify the new defaults that Statspack should use. To save your new parameter values permanently, use statspack.snap to set the i_modify_parameter input variable to true; this will save the new values in the stats$statspack_parameter table, as follows:
SQL> execute statspack.snap (i_snap_level=>10, i_modify_parameter=> ↵ ‘true’);

(Table 3 shows the list of parameters you can set with the snap procedure.) Alternatively, you can change the defaults without taking a snapshot immediately, using the statspack.modify_ statspack_parameter procedure. For example, to change the snapshot level to

Parameter
i_snap_level i_ucomment i_executions_th i_disk_reads_th i_parse_calls_th i_buffer_gets_th i_session_id i_modify_parameter

Valid Values
0, 5, 10 text integer >=0 integer >=0 integer >=0 integer >=0 valid SID from v$session true, false

Default Value
5 blank 50 1,000 1,000 10,000 0 (no session) true

Meaning
Snapshot level Comment to be stored with snapshot SQL threshold: number of executions SQL threshold: number of disk reads SQL threshold: number of parse calls SQL threshold: number of buffer gets Session ID; capture session granular statistics Save specified parameters for future snapshots

5

|

M A R C H / A P R I L 2 0 0 0 – W W W. O R A C L E . C O M / O R A M A G /

10 and the SQL thresholds for buffer_gets and disk_reads without actually taking a snapshot, issue the following statement:
SQL> execute statspack.modify_statspack_↵ parameter (i_snap_level=>10, i_buffer_gets_↵ th=>10000, i_disk_reads_th=>1000);

In addition to collecting instance statistics, you can collect all the session statistics for a single session when you take a snapshot:
SQL> execute statspack.snap(i_session_id=>3);

Gathering session-level statistics is not the default behavior, however. Once you have multiple snapshots on hand, you can examine instance activity by running the report created by the statsrep.sql script. Listing 2 shows the SQL*Plus session executing this script. Be sure to log on to the Oracle database as user perfstat. You’ll be prompted for the beginning and ending snapshot IDs and the name of the report output file. (The default name includes the beginning and ending snapshot IDs.)
I T G E TS BET T E R

performance statistics. Two key benefits over BSTAT/ESTAT include Statspacks’ ability to store the statistical data permanently in Oracle database tables— enabling easy reporting and analysis—and the collection of far more data. And because Statspack stores statistics data in tables, you can copy performance data simply by using the Oracle export utility. This is especially useful should you ever need to send data to Oracle Support for assistance with problem diagnosis, for example. (The Statspack package includes an export parameter file—statsuexp.par— for this purpose.) Oracle8i Release 8.1.6 contains the first production release of Statspack. Oracle will enhance this product with each new release of Oracle Database Server in order to provide an efficient, easy-to-use tool for capturing performance data and diagnosing performance problems. Part 2 of this article will give an introduction to analyzing the statistics Statspack reports.
Connie Dialeris (cdialeri@us.oracle.com) and Graham Wood (gwood@us.oracle.com) are members of the Oracle Server Technology Performance Group and together have more than 25 years of Oracle-software expertise.

Statspack gives DBAs a sophisticated new tool for collecting and analyzing databaseFor more information on dbms_job, see the Supplied Packages Reference Manual, available online at Oracle Technology Network (http://technet.oracle.com).

U SI NG D B M S _ J O B T O AU T O M ATE S TAT I S T I C S CO LL E CTI O N

You can use dbms_job to automate statistics collection. The file statsauto.sql contains an example of how to do this, scheduling a snapshot every hour. When you create a job by using dbms_job, Oracle assigns the job a unique number that you can use for changing or removing the job. In order to use dbms_job to schedule snapshots automatically, you must set the job_queue_processes initialization parameter to greater than 0 in the init.ora file:
# Set to enable the job-queue process to start. This allows dbms_job # to schedule automatic statistics collection, using Statspack job_queue_processes=1

Change the interval of statistics collection by using the dbms_job.interval procedure:
execute dbms_job.interval(<job number>,’SYSDATE+(1/48)’);

In this case, ‘SYSDATE+(1/48)’ causes the statistics to be gathered each 1/48 day—every half hour. To stop and remove the automatic-collection job:
execute dbms_job.remove(<job number>);

6

|

MARCH/APRIL 2000 –

W W W. O R A C L E . C O M / O R A M A G /


				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:29
posted:10/23/2009
language:English
pages:6