13.3 STATSPACK
|
| < Day Day Up > |
|
Oracle officially introduced a new utility for capturing performance data in Oracle Version 8.1.6 called STATSPACK. STATSPACK is a successor to the UTLBSTAT/UTLESTAT processes and works on a similar concept of capturing snapshots of the various performance views and comparing them against previous snapshots. UTLBSTAT/UTLESTAT capture statistics into temporary tables at the beginning, and after the UTLESTAT process reports on the differences between the two snapshots in the report.txt file, it deletes the snapshot data. STATSPACK does not delete the snapshot collection, thus providing access to historical information.
STATSPACK fundamentally differs from UTLBSTAT/UTLESTAT tuning scripts by collecting more information, and also by storing the performance statistics data permanently in Oracle tables, which can later be used for reporting and analysis. The data collected can be analyzed using the report provided, which includes an ''instance health and load'' summary page, high-resource SQL statements, as well as the traditional wait events and initialization parameters.
Permanent tables owned by PERFSTAT (a user created by the STATSPACK utility) store performance statistics. Instead of creating/ dropping tables each time, data is inserted into the pre-existing tables. This makes historical data comparisons easier. STATSPACK also separates the data collection from the report generation. Data is collected when a ''snapshot'' is taken; viewing the data collected is in the hands of the performance engineer when he/she runs the script to generate the performance report.
There is a fundamental difference between the ways the two utilities compute certain information. For example, the STATSPACK utility increments a transaction counter on a commit or a rollback operation to calculate the number of transactions:
'user commits' ) 'user rollbacks'
UTLBSTAT/UTLESTAT considers a transaction to complete with a commit only, and so assumes that transactions = ''user commits.'' For this reason, comparing per transaction statistics between STATSPACK and UTLBSTAT/UTLESTAT may result in significantly different transaction ratios.
With STATSPACK, Oracle separated the data collection phase from the reporting phase to allow for greater flexibility to establish performance baselines as well as to conduct trend reporting. Apart from this basic enhancement, STATSPACK provides visibility into various areas of database performance that is not provided by the UTLBSTAT/UTLESTAT utility. The reporting capability provides comparative views between two snapshot periods, and provides a high-level summary page indicating the overall health of the database for the given snapshot period followed by details on the performance characteristics.
Over the various releases of Oracle, the STATSPACK report has been considerably enhanced, providing even greater detail of information and insight to the actual performance problem.
13.3.1 Installing STATSPACK
With Oracle providing all the required scripts, STATSPACK installation has been made an easy task. All scripts are located in the ORACLE_HOME/ rdbms/admin directory and the scripts are created when Oracle is installed on the server. STATSPACK scripts can be identified easily as they are prefixed with sp*.sql.
Prerequisites
Before the actual installation process, it is necessary to ensure that the basic requirements of installation have been identified and verified. It should be ensured that the STATSPACK scripts are available in the appropriate directories:
-
On a Unix platform, use the following command:
ls -ltr $ORACLE_HOME/rdbms/admin/sp*.sql
-
On a Windows platform they should be available in a similar location at
%ORACLE_HOME/rdbms/admin/sp*.sql
As we have discussed earlier, the STATSPACK captures snapshots of performance statistics and stores them in permanent tables. Hence considerable effort should be provided in sizing the appropriate tablespaces based on the period of time that the data should be retained for historical purposes. For easy maintenance, it would be advisable to create tablespaces, to allow dropping and re-creation without affecting regular business data.
SQL>SPACK_DATA_P001 create tablespace SPACK_DATA_P001 datafile '/dev/vx/rdsk/oraracdg/partition_500m1a' size 450M extent management local uniform size 1M segment space management auto;
During the process of gathering these snapshots and during subsequent reporting, Oracle performs a considerable number of sort operations. Based on the intervals at which the various snapshots are captured and how busy the system has been during this period, the amount of sort activity could be very high. In order to isolate the sort activity performed by STATSPACK from the sort activity performed by the regular application, it is advisable to create a separate TEMP tablespace exclusively for STATSPACK:
SQL>SPACK_TEMP_P001
Understanding the STATSPACK scripts
STATSPACK scripts are modularized. The main driver script called spcreate.sql controls the installation of the STATSPACK utility and this script calls the spcusr.sql, spctab.sql, and spcpkg.sql scripts. In order to report any errors during the installation, these scripts generate .lis files, which are verified to ensure that the installation has completed successfully.
Figure 13.1 illustrates the script interface and their dependency matrix. While the straight lines indicate how each of the scripts are called, the numbers indicate the order in which they are executed from the spcreate.sql script. The order also indicates the dependency of each script on the next.
Figure 13.1: STATSPACK scripts.
There are few other scripts that do not have any direct dependency on the above scripts but require the previous set of scripts to be completed successfully before they can be run.
Table 13.1 provides a list of certain important STATSPACK scripts and their purposes. The script that is commonly used is spreport.sql, which provides a report on the overall database performance between two snapshot periods.
| Script | Purpose |
|---|---|
| spurge.sql | Deletes data pertaining to specific snapshots or a range of snapshots |
| spreport.sql | Generates a report on the overall database performance between two snapshot periods |
| spauto.sql | Creates a DBMS_JOB scheduled at regular intervals and collects snapshots of the database performance |
| sptrunc.sql | Truncates all the tables, moving the high water mark to the beginning |
| spdrop.sql | Used to uninstall STATSPACK |
| sprepins.sql | Generates a report on information from a different instance. This is helpful in a RAC environment where reports from multiple instances are required |
| sprepsql.sql | Generates a report providing SQL run time statistics for a specific SQL statement |
Installation
STATSPACK installation requires that the user running the scripts has the SYSDBA privileges. It is idle to run the scripts as user sys.
-
Connect using SQL*Plus as user sys:
sqlplus SQL> connect as sys/<password> as sysdba SQL>
-
Execute the following script:
SQL> @$ORACLE_HOME/rdbms/admin/spcreate.sql
This script will in turn run the three other scripts, spcusr.sql, spctab.sql, and spcpack.sql to create the user, tables, and the packages that constitute the STATSPACK utility.
During the execution process, the only user inputs that are required is the name of the default tablespace where the STATSPACK- specific tables are to be created and the temporary tablespace required for the temp sort area.
Note spcusr.sql will create the PERFSTAT user with a default password of PERFSTAT. After the user is created, the script auto matically connects as the PERFSTAT user before executing the remaining scripts.
In a RAC implementation, since the underlying database is the same for all instances, the spcreate.sql script should only be executed once. However, for gathering statistics, the required spauto.sql script could be executed individually on all instances participating in the clustered configuration. This is because the data relevant to the instance performance needs to be gathered individually by each instance.
13.3.2 Configuration
After the successful execution of the scripts provided, the STATSPACK utility is installed and after verification of all the .lis files to ensure that the installation did not generate any errors, the next step is configuring STATSPACK to collect statistics.
The configuration process is to ensure that the STATSPACK scripts are executed to collect snapshots of the database performance. Having the correct settings will prove extremely valuable to establish effective performance baselines or troubleshooting a performance issue.
To determine the amount of data that a STATSPACK snapshot will collect, Oracle has divided this data gathering process based on two different conditions:
-
Snapshot level, which will determine the granularity and volume of snapshot information to be collected.
-
SQL threshold, which will determine the amount of SQL to be captured when snapshot level 5 or greater is used.
Snapshot level
Oracle provides up to five levels of information gathering, depending on the granularity of the data that is to be collected. The higher the level, the more information is gathered. The data gathered is later analyzed using a report generated by the spreport.sql script.
Level 0: General performance
This level can be used to gather general performance information about the database. General performance information pertaining to the following areas is collected:
-
Wait statistics
-
System events
-
System statistics
-
Rollback segment data
-
Row cache
-
SGA
-
Latch statistics
-
Background events
-
Session events
-
Lock statistics
-
Buffer pool statistics
-
Resource limit
-
Enqueue statistics
If enabled, statistics will also be gathered for the following:
-
Automatic undo management
-
Buffer cache advisory data
-
Auto PGA memory management
-
Cluster DB statistics
Level 5: Level 0 + SQL statements
Level 5 will gather all the information from the previous level, plus it will collect performance data on high-resource SQL statements. This is also the default snapshot level.
When this snapshot level is chosen, there are additional SQL-related configuration parameter settings that can be adjusted. These parameters are called SQL thresholds. These parameters and their allowable settings are discussed later.
The STATSPACK information gatherer at this level and beyond collects high-performance SQL statements, which are retrieved by probing the shared pool of the instance. The larger the size of the pool, the greater the number of statements likely to be cached, and the STATSPACK information gatherer has to browse through more areas to capture all the complex queries.
The duration of a snapshot generation at this level is directly affected by the size assigned to the SHARED_POOL_SIZE initialization parameter setting for the database. A low value assigned to the SHARED_POOL_SIZE will result in a shorter duration of this snapshot, whereas a high value will result in a longer duration.
Level 6: Level 5 + SQL plans and plan usage
Level 6 will gather all the information from the previous level, plus it will collect execution plans and the plan usage as they relate to the high- performing SQL queries captured in the previous level.
The higher the level (in order to capture the performance statistics and store them in the STATSPACK tables) the more resources are required by the information gatherer. Also the more data that is to be stored, the more the space and insert time to store the data is needed; hence unless information with this much detail is required most of the time, basic level 5 information should be sufficient. Higher levels could be utilized to drill down after a specific problem has been identified.
Level 7: Level 6 + segment level statistics
Level 7 will gather all the information from the previous level, plus it will collect performance data on highly used segments. At this level any RAC-specific segment-level statistics are also collected.
Segment-level information collected at this level helps make decisions about modification of physical layout on certain segments or tablespaces.
Top 5 CR Blocks Served (RAC) per Segment for DB: PRODDB Instance: RAC1 Snaps: -> End Global Cache CR Blocks Served Threshold: 1000 CU Subobject Obj. Blocks Owner Tablespace Object Name Name Type Served %Total -------- ---------- ---------- ---------- ------ ------ ------ MVALLATH USPRL_PK_ PK_USPRL _INDX_P003 INDEX 1,065 12.9 MVALLATH INDX_TBS20 COMP_INDX7 INDEX 963 11.72 MVALLATH USERS PK_USEC INDEX 704 8.57 MVALLATH COMP_DATA_ COMPANY _DATA_P003 TABLE 525 6.39 MVALLATH USRLI_DATA USER_LOGIN _DATA_P001 TABLE 437 5.32 ---------------------------------------------------------------------
Top 5 CU Blocks Served (RAC) per Segment for DB: PRODDB Instance: RAC Snaps: -> End Global Cache CU Blocks Served Threshold: 1000 CU Subobject Obj. Blocks Owner Tablespace Object Name Name Type Served %Total -------- ---------- ---------- ---------- ------ ------ ------ MVALLATH COMP_DATA_ COMPANY _DATA_P002 TABLE 21,536 58.40 MVALLATH COMP_DATA_ COMPANY _DATA_P003 TABLE 5,755 15.61 MVALLATH USPRL_DATA USER_PROFILE _DATA_P002 TABLE 2,537 6.88 MVALLATH USERS PK_USRLI INDEX 662 1.80 MVALLATH INDX_TBS30 USRLI_INDX1 _INDX_P001 INDEX 631 1.71 ---------------------------------------------------------------------
Level 10: Level 7 + parent+ child latches
Level 10 will gather all the information from the previous level, plus it will collect parent–child latch information.
Due to the extensive detail of statistics collected at this level, gathering snapshot information at this level should be done with great caution.
SQL threshold
While capturing high-resource SQL queries, certain additional criteria or threshold values can be defined, for example, capture SQL queries with number of executions greater than 500.
Defining threshold values for various criteria provides additional levels of filtering when capturing SQL queries that have performance issues.
Table 13.2 provides a list of threshold descriptions and their default values. Thresholds can be set during snapshot collection. If no value is specified for the threshold parameters, the default values shown are applied.
| Threshold Parameter Description | Default Value |
|---|---|
| Number of executions of the SQL statement | 100 |
| Number of disk reads performed by the SQL statement | 1000 |
| Number of parse calls performed by the SQL statement | 1000 |
| Number of buffer gets performed by the SQL statement | 10,000 |
| Size of shareable memory used by the SQL statement | 1 MB |
| Version count for the SQL statement | 20 |
| Number of global cache consistent read blocks served (RAC) | 1000 |
| Number of global cache current blocks served (RAC) | 1000 |
Table 13.3 provides a list of parameters that can be used with the STATSPACK utility during snapshot collection. If the parameters and a corresponding value are not used, the default values shown are applied during snapshot gathering.
| Parameter | Description | Range of Values | Default Values |
|---|---|---|---|
| I_snap_level | Snapshot level | 0, 5, 6, 7, 10 | 5 |
| I_uncomment | Comment to accompany snapshot | Text | Blank |
| I_executions_th | Number of times SQL statement executed | Integer>0 | 100 |
| I_disk_reads_th | Number of disk reads that a statement makes | Integer>0 | 1000 |
| I_parse_calls_th | Number of parse calls the statement makes | Integer>0 | 1000 |
| I_buffer_gets_th | Number of buffer gets the statement makes | Integer>0 | 10,000 |
| I_sharable_mem_th | Amount of shareable memory | Integer>0 | 1 MB |
| I_session_id | Session ID to capture session granular statistics | Valid SID from V$SESSION | 0 |
| I_modify_parameter | Save the parameters specified for future | True, False | False |
13.3.3 Execution
Snapshots to collect statistics can be gathered in one of two ways:
-
Interactively, by executing the following command:
SQL> EXECUTE STATSPACK.SNAP();
or by passing in a value to one of the parameters listed in Table 13.3. For example using the I_snap_level parameter to collect snapshot at a specific level (0, 5, 6, 7, 10)
SQL> EXECUTE STATSPACK.SNAP(I_snap_level =>n)
where 'n' could have a value of 0, 5, 6, 7, or 10.
-
Through an automated script that is executed at fixed intervals either through the Oracle provided DBMS_JOB package or through an operating-system-based utility such as cron shell or perl script. Oracle provides, along with the other STATSPACK scripts, a script spauto.sql to automate the snapshot collection using the DBMS_JOB package. If the spauto.sql script is executed for automated snapshot collection, the data is gathered every hour by the hour:
SQL>$ORACLE_HOME/rdbms/admin/spauto.sql
13.3.4 Reporting
The STATSPACK utility has been installed and configured, and snapshots have been taken at various intervals. Now the data gathered needs to be analyzed after extracting the data through a report generator. Oracle provides a report generator with STATSPACK. This script will provide the overall health of the database between two snapshot periods.
To generate this report, the spreport.sql script is executed, and the script requests for snapshot information: basically the starting and ending snapshot information that needs to be compared and the interval during which the performance summary of the instance is required.
SQL> @$ORACLE_HOME/rdbms/admin/spreport.sql Current Instance ~~~~~~~~~~~~~~~~~~~~~ 3598885999 PRODDB 2 RAC1 Instances in this STATSPACK schema ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 3598885999 1 PRODDB RAC1 ora-db1.summerskyus.com 3598885999 2 PRODDB RAC2 ora-db2.summerskyus.com Using 3598885999 for database Id Using 2 for instance number Completed Snapshots Snap Snap Instance DB Name Id Snap Started Level Comment -------- ------- ----- ---------------- ------ ------- RAC2 PRODDB 3509 22 Nov 2002 12:02 10 3515 22 Nov 2002 14:03 10 3520 22 Nov 2002 15:04 10 3526 22 Nov 2002 16:04 10 3527 22 Nov 2002 17:01 10
Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter value for begin_snap: 3509 Begin Snapshot Id specified: 3509 Enter value for end_snap: 3526 End Snapshot Id specified: 3526 Specify the Report Name ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ The default report file name is sp_3509_3526. To use this name, press <return> to continue, otherwise enter an alternative. Enter value for report_name:
Analysing the report
If no value is specified for the report a default name is used.
The output shown below is the first page of the STATSPACK report and provides the overall health of the instance, which includes:
-
Instance characteristics: This includes the database name, instance name, Oracle version, and the snapshot information.
-
Cache sizes: This section shows the various cache sizes based on the initRAC1.ora parameters.
-
Load profile: This section provides the database load characteristics during the snapshot period. It provides insight into potential problems with the instance. For example, a high hard parse could indicate that the SQL queries may be using literals and requires a parse operation because of using different values during every execution. There are a large number of physical reads per second, which could also mean that the queries have not been tuned well or the queries are performing full table scans instead of index-based retrieval.
-
Instance efficiency percentages (ratios): The various performance ratios and their respective values during this snapshot period are listed.
-
Shared pool statistics: Indicates the amount of shared pool consumed during the snapshot period.
-
Top 5 Timed (wait) events: This is a very important section of the STATSPACK report and provides the top 5 reasons why an instance is behaving the way it is. The top 5 waits indicate the overall health of the database. This section also provides the CPU information for this snapshot period; inclusion of CPU statistics in the STATSPACK report is new in Oracle 9i.
STATSPACK report for DB Name DB Id Instance Inst Num Release Cluster Host ------ ----- -------- -------- ------- ------- ---- PRODDB 3598885999 RAC1 1 9.2.0.2.0 YES ora-db1.
summerskyus.com
summerskyus.com Snap Id Snap Time Sessions Curs/Sess Comment ------ --------- ----------------- --------- ------ Begin Snap: 3509 22-Nov-02 12:00:02 ####### .0 End Snap: 3526 22-Nov-02 16:00:04 ####### .0 Elapsed: 240.03 (mins)
Cache Sizes (end) ~~~~~~~~~~~~~~~~~~ Buffer Cache: 1,024M Std Block Size: 8K Shared Pool Size: 256M Log Buffer: 976K Load Profile ~~~~~~~~~~~~ Per Second Per Transaction -------------- --------------- Redo size: 3,103.00 1,360.33 Logical reads: 16,323.94 2,772.35 Block changes: 119.86 18.70 Physical reads: 3,495.09 121.35 Physical writes: 28.42 11.50 User calls: 114.31 50.11 Parses: 168.06 73.68 Hard parses: 3958.01 199.00 Sorts: 1287.61 126.09 Logons: 10,348.00 98.76 Executes: 174.20 76.37 Transactions: 2.28
% Blocks changed per Read: 0.31 Recursive Call %: 87.42 Rollback per transaction% : 13.50 Rows per Sort : 3.79 Instance Efficiency Percentages (Target 100%) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait %: 100.00 Redo NoWait %: 100.00 Buffer Hit %: 100.00 In-memory Sort % : 100.00 Library Hit %: 100.00 Soft Parse %: 100.00 Execute to Parse %: 3.52 Latch Hit % : 99.98 Parse CPU to Parse Elapsd %:92.06 % Non-Parse CPU: 97.95 Shared Pool Statistics Begin End ------ ------ --- Memory Usage %: 40.38 41.62 % SQL with executions>1: 66.67 66.39 % Memory for SQL w/exec>1: 63.38 64.26 Top 5 Timed Events ~~~~~~~~~~~~~~~~~~~~~ %Total Event Waits Time (s) Ela Time ------------------------ ------ -------- -------- CPU time 5,085 49.68 enqueue 17,618 3,442 33.63 global cache null to x 30,079 399 3.90 global cache cr request 105,492 353 3.45 log file sync 36,619 272 2.66 ------------------------------------------------------
-
Workload characteristics: The output section below provides RAC- specific information; this information is not found in a STATSPACK report generated for a stand-alone configuration.
Cluster Statistics for DB: PRODDB Instance: RAC1 Snaps: 3509 -3526 Global Cache Service - Workload Characteristics ----------------------------------------------- Ave global cache get time (ms): 2.7 Ave global cache convert time (ms): 10.2 Ave build time for CR block (ms): 0.1 Ave flush time for CR block (ms): 2.5 Ave send time for CR block (ms): 0.2 Ave time to process CR block request (ms): 2.8 Ave receive time for CR block (ms): 3.7 Ave pin time for current block (ms): 9.1 Ave flush time for current block (ms): 0.9 Ave send time for current block (ms): 0.2 Ave time to process current block request (ms): 10.2 Ave receive time for current block (ms): 10.6 Global cache hit ratio: 0.2 Ratio of current block defers: 0.1 % of messages sent for buffer gets: 0.2 % of remote buffer gets: 0.2 Ratio of I/O for coherence: 0.2 Ratio of local vs remote work: 0.4 Ratio of fusion vs physical writes: 0.0 Global Enqueue Service Statistics --------------------------------- Ave global lock get time (ms): 1.9 Ave global lock convert time (ms): 48.8 Ratio of global lock gets vs global lock releases: 1.1 GCS and GES Messaging statistics -------------------------------- Ave message sent queue time (ms): 0.1 Ave message sent queue time on ksxp (ms): 0.6 Ave message received queue time (ms): 0.0 Ave GCS message process time (ms): 0.2 Ave GES message process time (ms): 0.0 % of direct sent messages: 94.2 % of indirect sent messages: 5.1 % of flow controlled messages: 0.7 -------------------------------------------------------
GES Statistics for DB: PRODDB Instance: RAC1 Snaps: 3509-3526 Statistic Total per per Second Trans ------------------------------- ----- ------ ------ dynamically allocated gcs resourc 0 0.0 0.0 dynamically allocated gcs shadows 0 0.0 0.0 flow control messages received 0 0.0 0.0 flow control messages sent 0 0.0 0.0 gcs ast xid 11 0.0 0.0 gcs blocked converts 34,077 2.4 1.0 gcs blocked cr converts 64,259 4.5 2.0 gcs compatible basts 12 0.0 0.0 gcs compatible cr basts (global) 3,595 0.2 0.1 gcs compatible cr basts (local) 21 0.0 0.0 gcs cr basts to PIs 0 0.0 0.0 gcs cr serve without current lock 0 0.0 0.0 gcs error msgs 0 0.0 0.0 gcs flush pi msgs 1,358 0.1 0.0 gcs forward cr to pinged instance 0 0.0 0.0 gcs immediate (compatible) conver 2,272 0.2 0.1 gcs immediate (null) converts 3,857 0.3 0.1 gcs immediate cr (compatible) con 24 0.0 0.0 gcs immediate cr (null) converts 32,803 2.3 1.0 gcs msgs process time(ms) 35,540 2.5 1.1 gcs msgs received 157,776 11.0 4.8 gcs out-of-order msgs 0 0.0 0.0 gcs pings refused 18 0.0 0.0 gcs queued converts 4 0.0 0.0 gcs recovery claim msgs 0 0.0 0.0 gcs refuse xid 3 0.0 0.0 gcs retry convert request 0 0.0 0.0 gcs side channel msgs actual 1,636 0.1 0.0 gcs side channel msgs logical 144,217 10.0 4.4 gcs write notification msgs 2 0.0 0.0 gcs write request msgs 1,954 0.1 0.1 gcs writes refused 32 0.0 0.0 ges msgs process time(ms) 8,398 0.6 0.3 ges msgs received 211,190 14.7 6.4 implicit batch messages received 905 0.1 0.0 implicit batch messages sent 431 0.0 0.0 lmd msg send time(ms) 3,394 0.2 0.1 lms(s) msg send time(ms) 112 0.0 0.0 messages flow controlled 2,052 0.1 0.1 messages received actual 365,272 25.4 11.1 messages received logical 368,964 25.6 11.2 messages sent directly 277,620 19.3 8.5 messages sent indirectly 14,960 1.0 0.5 msgs causing lmd to send msgs 62,250 4.3 1.9 msgs causing lms(s) to send msgs 14,573 1.0 0.4 msgs received queue time ms) 8,999 0.6 0.3 msgs received queued 368,963 25.6 11.2 msgs sent queue time ms) 1,363 0.1 0.0 msgs sent queue time on ksxp ms) 174,191 12.1 5.3 msgs sent queued 15,004 1.0 0.5 msgs sent queued on ksxp 292,726 20.3 8.9 process batch messages received 474 0.0 0.0 process batch messages sent 211 0.0 0.0 -------------------------------------------------------------
Wait Events for DB: PRODDB Instance: RAC1 Snaps: 3509 -3526 -> s - second -> cs - centisecond - 100th of a second -> ms - millisecond - 1000th of a second -> us - microsecond - 1000000th of a second -> ordered by wait time desc, waits desc (idle events last) Avg Total Wait wait Waits Event Waits Timeouts Time (s) (ms) /txn --------------------- ------- ------- -------- ---- ---- enqueue 17,618 1,775 3,442 195 0.5 global cache null to x 30,079 23 399 13 0.9 global cache cr request 105,492 101 353 3 3.2 log file sync 36,619 49 272 7 1.1 DFS lock handle 92,013 29 235 3 2.8 control file parallel write 4,659 0 53 11 0.1 buffer busy global cache 1,152 0 44 38 0.0 log file parallel write 36,209 36,201 42 1 1.1 control file sequential read 18,309 0 35 2 0.6 PX Deq: Execute Reply 10,797 0 31 3 0.3 PX Deq: Parse Reply 13,099 0 30 2 0.4 PX qref latch 29 29 28 981 0.0 direct path read 2,625 0 26 10 0.1 PX Deq: Join ACK 13,793 6,592 26 2 0.4 buffer busy global CR 1,617 0 19 12 0.0 global cache open x 2,592 0 13 5 0.1 global cache s to x 8,153 3 13 2 0.2 SQL*Net more data to client 164,396 0 10 0 5.0 PX Deq: Signal ACK 3,990 1,357 10 3 0.1 PX Deq: reap credit 210,299 195,058 8 0 6.4 IPC send completion sync 6,707 0 8 1 0.2 global cache busy 193 0 7 35 0.0 wait for master scn 9,528 0 6 1 0.3 buffer busy waits 289 0 5 18 0.0 db file parallel write 1,803 0 5 3 0.1 db file sequential read 424 0 4 9 0.0 direct path write 6,032 0 4 1 0.2 global cache open s 1,944 0 4 2 0.1 row cache lock 126 0 3 27 0.0 process startup 12 1 3 261 0.0 global cache null to s 875 0 3 3 0.0 library cache lock 4,903 0 2 0 0.1 latch free 519 228 1 3 0.0 CGS wait for IPC msg 50,310 46,897 1 0 1.5 name-service call wait 4 1 1 304 0.0 ksxr poll remote instances 37,777 23,639 1 0 1.1 SQL*Net break/reset to clien 240 0 0 2 0.0 KJC: Wait for msg sends to c 939 0 0 0 0.0 PX Deq Credit: send blkd 27 0 0 2 0.0 library cache pin 39 0 0 1 0.0 LGWR wait for redo copy 43 0 0 0 0.0 buffer deadlock 84 83 0 0 0.0 cr request retry 47 47 0 0 0.0 lock escalate retry 18 18 0 0 0.0 SQL*Net message from client 1,611,345 0 201,600 125 49.0 PX Idle Wait 33,509 26,541 72,502 2164 1.0 gcs remote message 443,378 288,380 27,927 63 13.5 ges remote message 487,520 268,165 14,028 29 14.8 PX Deq: Execution Msg 28,974 1,101 7,113 246 0.9
Wait Events for DB: PRODDB Instance: RAC1 Snaps: 3509 -3526 -> s - second -> cs - centisecond - 100th of a second -> ms - millisecond - 1000th of a second -> us - microsecond - 1000000th of a second -> ordered by wait time desc, waits desc (idle events last) Avg Total Wait wait Waits Event Waits Timeouts Time (s) (ms) /txn --------------------- ------- ------- -------- ---- ---- jobq slave wait 172 166 512 2975 0.0 SQL*Net more data from clien 3,786 0 224 59 0.1 SQL*Net message to client 1,611,349 0 3 0 49.0 ---------------------------------------------
Background Wait Events for DB: PRODDB Instance: RAC1 Snaps: 3509 -3526 -> ordered by wait time desc, waits desc (idle events last) Avg Total Wait wait Waits Event Waits Timeouts Time (s) (ms) /txn --------------------- ------- ------- --------- ---- ---- log file sync 2,952 27 71 24 0.1 control file parallel write 4,659 0 53 11 0.1 log file parallel 36,209 36,201 42 1 1.1 write control file 17,281 0 35 2 0.5 sequential read DFS lock handle 830 0 4 5 0.0 row cache lock 25 0 3 118 0.0 db file parallel write 925 0 2 3 0.0 enqueue 1,388 0 1 1 0.0 process startup 8 0 1 164 0.0 CGS wait for IPC msg 50,310 46,897 1 0 1.5 global cache null to x 11 0 0 1 0.0 latch free 15 1 0 1 0.0 global cache cr request 8 0 0 1 0.0 rdbms ipc reply 13 0 0 0 0.0 buffer busy waits 6 0 0 1 0.0 LGWR wait for redo copy 43 0 0 0 0.0 global cache null to s 2 0 0 1 0.0 library cache lock 2 0 0 1 0.0 global cache s to x 1 0 0 1 0.0 Chapter13 global cache open x 1 0 0 1 0.0 KJC: Wait for msg 2 0 0 0 0.0 sends to c rdbms ipc message 206,156 126,240 95,578 464 6.3 ges remote message 487,521 268,166 14,028 29 14.8 gcs remote message 208,763 146,147 13,966 67 6.4 smon timer 52 43 13,265 ###### 0.0 ------------------------------------------------------------------------ 534 13.4 Oracle EnterpriseManager
| Note | For more discussions about many of the above statistics and wait events, please refer to the subsequent chapters on performance tuning. |
|
| < Day Day Up > |
|
EAN: 2147483647
Pages: 174