oracle 10g 性能调整-statspack(p23)

I
■ ■ ■ ■ ■ ■ ■ ■ ■ ■ ■ ■ ■ ■ ■ ■ ■
What's new in 10gR2 and 10gR1 Finding the files needed to create, manage, and drop the STATSPACK objects Creating a tablespace to hold the STATSPACK data apart from your application and SYSTEM objects Changing the PERFSTAT password and locking the account when it is no longer in use Selecting the proper level for your reporting Times to avoid running STATSPACK reports Using Grid Control to run the AWR Report Running the AWR Report and STATSPACK together: cautionary notes The top 10 things to look for in the AWR Report and STATSPACK output Managing the STATSPACK data, analyzing and purging it as needed Tuning the top wait events Tuning latches Issues revealed using the Segment Statistics sections of the reports Monitoring STATSPACK space usage Including STATSPACK data in your backup and upgrade plans Tuning at the block level to find hot blocks and ITL problems Using the ADDM Report (text) as a stand-alone tool
Chapter 14:
Using STATSPACK and the AWR Report to Tune Waits and Latches
695
What's New in 10gR2 (10.2) STATSPACK
There are many new features in Oracle 10g Release 2. I have listed the ones here that I've seen as important and helpful. Please see the Oracle document listed in the References at the end of the chapter for a complete STATSPACK reference. New features in 10gR2 include
New Features in 10gR2 (10.2) STATSPACK
There are also many new features in Oracle 10g Release 1. I have listed the ones here that I think are important or helpful. Please see the Oracle document listed in the References at the end of the chapter for a complete list. New features in 10gR1 include
■ ■ ■ ■ ■
You can identify "baseline snapshots" that will not be purged. The algorithm to get HASH_VALUE from V$SQL is changed from 9i to 10g. STATSPACK shows both the old value and the new value for backward compatibility. When you make an error running STATSPACK, you now see the error message in the output and the actual values that caused the errors. SQL ordered by Gets, Reads, and Parse Calls all only show when they exceed 1 percent of total resources for the interval reported on (and only if above report thresholds set). The title for SQL ordered by Gets now includes the percentage of all gets that the listing has. If the SQL listed in this section includes 80 percent of all Gets for the period, then it will let you know in the title that "Captured SQL accounts for 80% of total Buffer Gets" (sweet!). Top SQL by Cluster Wait Time has been added for RAC. A new report, sprsqins.sql, has been added to report on a single SQL statement for an instance that includes the SQL stats and optimizer execution plan.
■ ■ ■ ■ ■ ■ ■ ■ ■ ■ ■
Added Average (Ave) Wait (ms) for Top 5 Timed Events section Added OS statistics (not all features on all platforms) to identify if the OS is CPU bound and if it's due to Oracle File I/O histogram (added in 10.1) has new bucket of <= 2 ms bucket Buffer Pool Stats now shows K, M, or G instead of all the zeros Rollstat section omitted if using Auto Undo unless specified in sprepcon.sql SQL ordered by CPU and SQL ordered by Elapsed Time added SGA Target Advisory (from v$sga_target) added RAC Stats section now computes interconnect traffic in KB/s Sleeps 1–3 are made obsolete for latches (you can still get this by going to v$latch_parent, v$latch_children, v$latch) You use spup92.sql and spup101.sql to upgrade to 10.2 from 9.2 You use spup101.sql to upgrade only from 10.1 to 10.2
CHAPTER
14
Using STATSPACK and the AWR Report to Tune Waits and Latches
694
Fra Baidu bibliotek
Oracle Database 10g Performance Tuning Tips & Techniques
f you could choose just two Oracle utilities to monitor and find performance problems of your system, those two utilities would be Enterprise Manager (Chapter 5) and the new AWR (Automatic Workload Repository) Report and/or STATSPACK (both covered in this chapter). In Oracle 10gR2, the new AWR Report has everything that is in STATSPACK and a whole lot more! STATSPACK also remains in 10g and has a few great improvements. The STATSPACK utility has been available since Oracle 8.1.6 to monitor the performance of your database. STATSPACK originally replaced the UTLBSTAT/UTLESTAT scripts available with earlier versions of Oracle. The AWR Report leverages the Automatic Workload Repository statistics and can be executed within Enterprise Manager Grid Control if desired and will probably replace STATSPACK for good in the future. Although the AWR Report has some very nice things in it that STATSPACK doesn't have, you must license the Oracle Diagnostics Pack to access the AWR dictionary views necessary for the AWR Report (STATSPACK is still a free utility).
In this chapter, you will see how to install the AWR Report and STATSPACK, how to manage them, and how to run and interpret the reports generated by the AWR Report and/or STATSPACK. STATSPACK includes data for both proactive and reactive tuning and is probably the best way to query most of the relevant V$ views and X$ tables and view the results in a single report. The AWR Report is the great way to mine the AWR for aggregate performance data in a report similar to STATSPACK. Tips covered in this chapter include the following: