Shulou(Shulou.com)05/31 Report--

This article focuses on "how to understand the Oracle response time Analysis report". Interested friends may wish to take a look at it. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to understand the Oracle response time Analysis report.

The Oracle response time analysis report is divided into system-level session level. Compared with the awr report, the report is more intuitive and clear and helps to quickly analyze and locate performance problems. Here, the OSM tool is used to generate these two types of reports, which is written by Craig Shallahamer.

Objects needed to create osm users in the database and install osm scripts

[oracle@db1] $sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Wed Feb 27 15:43:54 2019Copyright (c) 1982, 2013, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit ProductionWith the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,Data Mining and Real Application Testing optionsSQL > create user osm identified by "osm" default tablespace sx temporary tablespace temp;User created.SQL > grant connect,resource,dba to osm;Grant succeeded.SQL > conn osm/osmConnected.SQL > exec sys.dbms_lock.sleep (5) PL/SQL procedure successfully completed.SQL > @ osmprep.sqlOraPub System Monitor-Interactive (OSM-I) installation script. (C) 1998 by OraPub, Inc.There is absolutely no guarantee with this software. You mayuse this software at your own risk, not OraPub's risk.No value is implied or stated.You may need to run $ORACLE_HOME/rdbms/admin/catblock.sqlConnect as the user who will be using the OSM.Press ENTER to continue.To categorize wait events for OSM reports, run:For pre-10g systems, run event_type_nc.sqlFor 10g and beyond, run event_type.sqlOnce you cateogrize the wait events The installation is complete.Menu is OSM. SqlENJOYSQL > @ event_type.sqlfile: event_type.sql for Oracle 10g and beyond...About to categorize wait events for OSM reports.Press ENTER to re-create the o$event_type table.OraPub Categorization Summary---TYPE COUNT (*)-bogus 126ior 20iow 59other 1162 COUNT (*)-1367Oracle Categorization Summary- -WAIT_CLASS COUNT (*)-Administrative 55Application 17Cluster 50Commit 2Concurrency 33Configuration 24Idle 96Network 35Other 958Queueing 9Scheduler 8WAIT_CLASS COUNT (*)-- System Igamo 32User I/O 4813 rows selected. COUNT (*)-1367

Rtsess.sql in the OSM toolkit is used to generate session-level reports, and rtsysx.sql,rtpctx.sql is used to generate instance-level reports.

The instance-level Oracle response time analysis report is based on the rtsysx.sql script, which captures details about the response time within the scope of the instance within the specified interval. This script will take a snapshot of the instance-level statistics (instance-level statistics) and instance-level waiting event statistics (v$system_event). The following example wakes up the script every 10 seconds during a 120-second interval to query the active SQL from the v$session view and store the currently running SQL_ID. At the end of the report life cycle, other statistical residence snapshots are generated, the time difference is calculated and the report is generated. Almost all the information in the report can be collected from Statspack or AWR reports. Use the rtsysx.sql pin to generate formatted output that can quickly perform Oracle response time analysis. Reports generated using the script rtsysx.sql script include the following components:

. The first part is to pay attention to the workload.

. The second part is the high-level response time classification information.

. The third part is the situation of IO and non-IO.

. The fourth part is the SQL statement that does not use binding variables.

. For Oracle 10g and above, the fifth part is about operating system CPU utilization

Let's execute the rtsysx.sql script, the execution life cycle is 120 seconds, and the script is awakened every 10 seconds.

SQL > @ rtsysx.sql 120 10OraPub's Response Time Analysis (RTA) interactive system level delta reportInitializing response time delta objects...Sleeping and probing active SQL for next 120 seconds...Done sleeping...gathering and storing current values...*** Response Time Ratio and Workload MetricsRT Ratio Ora Trx/s Block Changes/s User Calls/s Execs/s-- -- 0.325 0.54 90.63 27.41 100.00yuan * Response Time System Summary (delta-interactive-system level) Tot CPU CPU SP CPU BG CPU Parse CPU Recur Tot Wait IO Wait Other Wait Time Ora CPU Time% % (sec) (sec) Util% (sec) (sec) (sec) IO Wait Other Wait-- -34 26 8 2 15 0.4 17 15 1 92 8 Wait Time Summary w/Event Details (delta-interactive-system level) IO Wait IO WRITE IO READ Time Wait Time Wait Time% IO% IO (sec) (sec) (sec) Write Read--15 10 5 66 34 Tot Call Avg Call Wait Time Wait TimeIO Wait Event R W% (sec) (ms) Tot Waits -LGWR real time apply sync W 57 8.69 65.83 132db file sequential read R 31 4.67 4.97 940LGWR-LNS wait on channel W 50. 71 1.74 408 Other Wait Time (non-I/O) Event Detail (delta-interactive-system level) Tot Call Avg Call Wait Time Wait TimeNon IO (other) Wait Event % (sec) (ms) Tot Waits -- gc current block 2-way 26 0.35 0.62 569gc cr grant 2-way 17 0.23 0.48 484reliable message 10 0.14 1.21 116os thread startup 10 0.14 23.33 6enq: US-contention 6 0.08 0.49 163library cache pin 6 0.08 0.56 144library cache lock 4 0.06 0.61 98gc current grant 2-way 4 0.05 0.45 111 * SQL Activity Details During Probe Phys Rds Log Rds Tot Time CPU Time Rows StmtSQL ID Sec/EXE (k) (k) (sec) (sec) Sec/PIO Sec/LIO Runs (k) Sorts Type-- -gz5bfrcjq060u 0.010000.3 0.3 # # # 0.001 24 0 23 INSERc77k33u5u7zgc 0.06 0 17 0.1 0.1 # 0.000 2 0 2 SELEC8fb44rrg8a5rh 0.13 0 15 0.1 0.1 # 0.000 1 0 2 SELEC98564h4vavfcm -25.78-0-0-51.6-0.6 51.552 0.276 200 inser*** SQL Similar Statements During DeltaSQL Statement (shown if first 10 chars) Count-- -SELECT NVL 2 million * OS CPU Breakdown During DeltaCategory Percent- -- Idle 96.51IO Wait 0.44Nice 0.00System 0.38User 2.47Delta is 123.53 secondsNumber of CPU cores is 80

The first part of the report: Response Time Ratio and Workload Metrics

The first part of the report provides the same Workload Metrics as the Load Profile section of Statspack and AWR. This information is useful when comparing the differences between response time snapshots. If the workload is reduced, then the response time can be expected to decrease.

* Response Time Ratio and Workload MetricsRT Ratio Ora Trx/s Block Changes/s User Calls/s Execs/s- 0.325 0.54 90.63 27.41 100.00

The second part of the report: Response Time System Summary

This part of the information shows that the total CPU time is 34 seconds, and the total waiting time is 17 seconds. It can also be said that in the interval of 120 seconds, the CPU time consumed by the Oracle process is only 34 seconds, and the waiting time is only 17 seconds. You can also see that of the 17 seconds wait time, the IO wait time is 15 seconds, and the non-IO wait time is 1 second. During the interval of 120 seconds, the Oracle process consumed only 0.4% of the total CPU available time, which is the total CPU time consumed by the Oracle process divided by the host available CPU time. The CPU available time of the host in the 120s interval is the number of cores of CPU multiplied by the reporting interval. Here, the number of CPU cores of the host is 80, and the reporting interval is 120 seconds, so the CPU time consumed by Oracle is 34 / (12080) = 0.4%. If only this instance is running on the host, it also provides us with operating system CPU utilization, so there is no need to execute operating system commands to check CPU utilization.

* Response Time System Summary (delta-interactive-system level) Tot CPU CPU SP CPU BG CPU Parse CPU Recur Tot Wait IO Wait Other Wait Time Ora CPU Time% (sec) (sec) Util% (sec) (sec) Sec) IO Wait Other Wait- 34 26 8 2 15 0.4 17 15 1 92 8

Part III of the report: iCompact O Wait Time Summary w/Event Details

If there is a problem with IO, then you must want to know if there is a problem with reading or writing, and the administrator can give the relevant solution by knowing the type of IO load. For example, an IO read problem can minimize the impact of using IO reads by saving frequently accessed data blocks in Oracle Cache, and if an IO write problem can be configured, such as the number and size of online redo log files, the impact of IO writes can be minimized. You can see from the report that the total wait time for IO is 15 seconds, with 10 seconds for IO writes and 5 seconds for IO reads. The average waiting time for LGWR real time apply sync events is 65.83 milliseconds, which is due to the configuration of ADG, which is normal for disaster recovery in different places in the same city, and the average waiting time for db file sequential read events is 4.97ms.

* interactive O Wait Time Summary w/Event Details (delta-interactive-system level) IO Wait IO WRITE IO READ Time Wait Time Wait Time% IO% IO (sec) (sec) (sec) Write Read--15 10 5 66 34 Tot Call Avg Call Wait Time Wait TimeIO Wait Event R W% (sec) (ms) Tot Waits -LGWR real time apply sync W 57 8.69 65.83 132db file sequential read R 31 4.67 4.97 940LGWR-LNS wait on channel W 5 0.71 1.74 408

Part IV of the report: Other Wait Time (non-I/O) Event Detail

This section shows the summary of non-IO wait events related to the underlying wait event details, because the total non-IO wait time is only 1 second, which does not affect performance. So we don't have to look at the relevant waiting events.

* * Other Wait Time (non-I/O) Event Detail (delta-interactive-system level) Tot Call Avg Call Wait Time Wait TimeNon IO (other) Wait Event % (sec) (ms) Tot Waits -gc current block 2-way 26 0.35 0.62 569gc cr grant 2-way 17 0.23 0.48 484reliable message 10 0.14 1.21 116os thread startup 10 0.14 23.33 6enq: US-contention 6 0.08 0.49 163library cache pin 6 0.08 0.56 144library cache lock 4 0.06 0.61 98gc current grant 2-way 4 0.05 0.45 111

Part V of the report: SQL Activity Details During Probe

To help analyze the application, the report captures SQL statements that directly affect response time and shows resource consumption. According to the following data, the resources consumed by captured SQL statements are very small and do not affect performance, in which the physical read of the statement is 0 and the total logical read size is only 32K.

* * SQL Activity Details During Probe Phys Rds Log Rds Tot Time CPU Time Rows StmtSQL ID Sec/EXE (k) (k) (sec) (sec) Sec/PIO Sec/LIO Runs (k) Sorts Type-- -- gz5bfrcjq060u 0.010000.3 # # 0.001 24 0 23 INSERc77k33u5u7zgc 0.06 0 17 0.1 0.1 # 0.000 2 0 2 SELEC8fb44rrg8a5rh 0.13 0 15 0.1 0.1 # 0.000 1 0 2 SELEC98564h4vavfcm-25.78-0-51.6-0.6 51.552 0.276 200 inser

Part VI of the report: SQL Similar Statements During Delta

The second parameter specified in the execution of the rtsysx.sql script is related to finding similar SQL statements, which are identical statements except for the filter and join conditions in the where clause. The second parameter we specify is 10, which means that similar statements will be counted and the first 10 characters of statements with statistics greater than 1 will be displayed.

* SQL Similar Statements During DeltaSQL Statement (shown if first 10 chars) Count-----SELECT NVL two

Part VII of the report: Operating System CPU Utilization

This section shows the details of the operating system usage. Starting with Oracle 10g, Oracle captures details of the usage of the operating system CPU and this information can be viewed through the v$osstat view.

* OS CPU Breakdown During DeltaCategory Percent---Idle 96.51IO Wait 0.44Nice 0.00System 0.38User 2.47Delta is 123.53 secondsNumber of CPU cores is 80

Session level Oracle response time Analysis report

Execute the script rtsess9.sql to generate a session-level Oracle response time analysis report for the specified session 1110. From the report below, you can see that the response time of the session is 699.29 seconds, of which the queue time is 608.20 seconds, the non-counting time is 91.09 seconds, while the IO queue time is only 0.3 seconds, and the Net+Client queue time is 607.73 seconds. This indicates that the session has been waiting for the client program to call.

SQL > @ rtsess9 1110=Session Level Response Time ProfileOracle session 1110CPU statistics number is 12.Session level response time details for SID 1110 employees * Response Time Summary Response Service Queue Unaccount% CPU% Queue% UAT Time (sec) RT [rt=st+qt+uat] [st] [qt] [uat] [st/rt] [qt/rt] [uat/rt] -699.29 0.00 608.20 91.09 0.00 86.97 13.03 Queue Time Summary QT QT QTQueue Time (sec) I am O (sec) Net+Client (sec) Other (sec) [qio+qnc+qot] [qio] [qnc] [qot]-608.20 0.03 607.73 0.44 Queue Time IO Timing Detail QT Write O (sec) Read I Read O (sec)% Writes Time% Read Time [tio=wio+rio] [wio] [rio] [wio/tio] [rio/tio]- -0.03 0.03 0.00 99.97 0.005 Queue Time IO Event Timing Detail Wait TimeWait Event Name (sec)-direct path write 0.01log file sync 0.02 Queue Time Other Event Timing Detail Wait TimeWait Event Name (sec)-gc cr block 2-way 0.08library cache pin 0.01gc current block congested 0.01gc current block 2-way 0.31row cache lock 0.01events in waitclass Other 0.01library cache lock 0.01 Wait Event Time Not Categorized (for QA).

It is not normal if both the application user and the Oracle server process are waiting. If the user has executed the command and is waiting for the command to finish, and the relevant Oracle server process is waiting to receive information from the client process, there is a problem between the two. Then probably the problem area is the network and client processes.

At this point, I believe you have a deeper understanding of "how to understand the Oracle response time Analysis report". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.