I am new to oracle and i got an AWR report to analyze,till now i got to understand below things like,
1) How to analyze Top 5 timed events and instance performance efficiency. The expected ratios to match in AWR report.
2) For analyzing performance of SQL queries, i got an info like,
Understand the waits and Wait classes in Top 5 Timed Events section. If number of waits is more, wait class is I/O, we need to check the queries from SQL Ordered by Gets, SQL Ordered by reads.
In the below section of AWR report, cn58bhkggqvj4 sql id is displayed with the below data,
CPU time: 6 Secs
Elapsed Time: 197
No of executions: 1
DB time: 29.55%
For only 1 execution, the execution time is 197 secs and DB time is also 29.55%. What i suspect is , the performance of this sql query might be slow.
Some of the sections of AWR report are:
Per Second Per Transaction
Redo size: 41,512.05 53,525.95
Logical reads: 964.02 1,243.01
Block changes: 207.36 267.37
Physical reads: 19.15 24.70
Physical writes: 5.68 7.32
User calls: 109.37 141.03
Parses: 30.61 39.46
Hard parses: 0.04 0.06
Sorts: 2.95 3.80
Logons: 0.03 0.03
Executes: 98.58 127.11
% Blocks changed per Read: 21.51 Recursive Call %: 61.44
Rollback per transaction %: 1.81 Rows per Sort: 118.98
Instance Efficiency Percentages (Target 100%)
Buffer Nowait %: 100.00
Redo NoWait %: 100.00
Buffer Hit %: 98.07
In-memory Sort %: 100.00
Library Hit %: 99.92
Soft Parse %: 99.86
Execute to Parse %: 68.95
Latch Hit %: 99.98
Parse CPU to Parse Elapsd %: 121.37
% Non-Parse CPU: 96.14
Shared Pool Statistics
Memory Usage %: 70.73 67.25
% SQL with executions>1: 96.19 94.95
% Memory for SQL w/exec>1: 91.11 87.93
Top 5 Timed Events
Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
db file sequential read 40,042 244 6 36.6 User I/O
enq: TX - row lock contention 91 236 2,591 35.4 Application
CPU time 151 22.7
db file scattered read 6,917 38 5 5.7 User I/O
log file parallel write 11,007 37 3 5.6 System I/O
SQL ordered by Gets
* Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
* Total Buffer Gets: 6,943,461
* Captured SQL account for 87.2% of Total
Buffer Gets Executions Gets per Exec %Total CPU Time (s) Elapsed Time (s) SQL Id SQL Module SQL Text
1,574,672 30,097 52.32 22.68 11.96 12.53 4unrasu6b9z7m JDBC Thin Client update T_CLOSINGCOSTVALUEITEM ...
424,554 4,998 84.94 6.11 5.93 6.71 8v9uf69cc5kkm JDBC Thin Client insert into T_PERSON (VERSION,...
421,007 66 6,378.89 6.06 2.69 14.48 3uu5j4hqkqjy9 JDBC Thin Client select distinct loanapplic0_.l...
386,850 5,358 72.20 5.57 3.93 3.93 8jdcjpsx2wr2q JDBC Thin Client SELECT COUNT(*) FROM T_LOANPIP...
358,959 2,204 162.87 5.17 7.38 10.99 drx8btm4rcvzq JDBC Thin Client insert into T_CLOSINGCOSTVALUE...
237,262 1,029 230.58 3.42 5.12 9.43 1u64jhs7sdw5n JDBC Thin Client insert into T_AUDITCOMMENT (VE...
231,512 5,793 39.96 3.33 1.85 2.24 67k5c81uwnaz8 JDBC Thin Client update T_ACTIVITYCONTEXT set V...
186,432 8,444 22.08 2.69 0.88 0.93 fx1n6d8yrzgvv JDBC Thin Client select address0_.ADDRID as ADD...
177,719 1 177,719.00 2.56 1.95 9.46 49rr72c9uuyyq firstname.lastname@example.org (TNS V1-V3) /* OracleOEM */ select...
119,931 1 119,931.00 1.73 6.49 196.72 cn58bhkggqvj4 email@example.com (TNS V1-V3) /* OracleOEM */ SELECT d...
105,024 4 26,256.00 1.51 0.92 0.92 3j9yx7t5abcyg firstname.lastname@example.org (TNS V1-V3) /* OracleOEM */ SELECT m.tabl...
100,515 33,505 3.00 1.45 1.08 1.08 7wy0tyu0w4w2m JDBC Thin Client select permission0_.Permission...
99,146 6,654 14.90 1.43 2.28 4.38 4sqmubrcakkxa JDBC Thin Client insert into T_ACTIVITYCONTEXT ...
87,648 3,974 22.06 1.26 1.43 1.43 0p717q46t2pww JDBC Thin Client select permission0_.orgRoleId ...
86,769 2,799 31.00 1.25 1.03 1.03 3n4yv6ad9da2b JDBC Thin Client select extraprope0_.orgEntityI...
86,527 915 94.57 1.25 0.41 0.69 d38d0vy8apxkz JDBC Thin Client select closingcos0_.loanApplic...
85,491 28,497 3.00 1.23 1.12 1.14 2ym6hhaq30r73 select type#, blocks, extents,...
83,073 6,281 13.23 1.20 0.56 0.66 0f5z5u9c3uhjt JDBC Thin Client select person0_.personId as pe...
81,428 3,913 20.81 1.17 0.36 0.36 602sk6pw3uwrv JDBC Thin Client select servicepro0_.id as id29...
81,358 618 131.65 1.17 1.51 4.05 367kcdumy4jmm JDBC Thin Client insert into T_SPEVENT_SPLISTEN...
74,603 3,888 19.19 1.07 0.35 0.37 2anqsss38q0z3 JDBC Thin Client select servicepro0_.sp_id as s...
73,599 30,491 2.41 1.06 3.57 5.07 awsxxcs3xwpz3 JDBC Thin Client INSERT INTO CM511PE_OWNER.S_CL...
70,521 6,830 10.33 1.02 2.03 3.73 fub6jdvnpzkj1 JDBC Thin Client insert into T_SP_DS (VERSION, ...
Could you please tell me if my understandings are correct. and other ways to check the performance of SQL queries
Thanks in advance
In you have the Diagnostic Pack license to use AWR then you also have the license to use ADDM: did you try to do it ? (see http://download.oracle.com/docs/cd/E11882_01/server.112/e16638/diag.htm#i37241 for example).
In short this is what ADDM can do:
The Automatic Database Diagnostic Monitor (ADDM):
Analyzes the AWR data on a regular basis
Diagnoses the root causes of performance problems
Provides recommendations for correcting any problems
Identifies non-problem areas of the system
The mentioned query is maybe too slow but it looks like that it is a query started by Oracle Entreprise Manager (because of the comment /* OracleOEM */): even if you try to fix this query this may not really improve application response time.
If you have application performance issue, you can try to identify slow SQL using the AWR SQL sections but you need to know which SQL statement is used by application feature that is slow. A better way is to use SQL trace and TKPROF on slow database session to identify SQL statements that slow down application.
Try to modify your post to format AWR report the right way (see FAQ link in the upper right corner) so that it can be easily read.
Edited by: P. Forstmann on 14 févr. 2011 08:30
Edited by: P. Forstmann on 14 févr. 2011 08:32
I'd address that TX enqueue contention.
Assuming you're licensed for ASH (as you're looking at AWR report), you might want to run an ASH report and use DBA_HIST_ACTIVE_SESS_HISTORY to help figure out the SQL affected, the table, the lock mode and therefore the cause.
Thanks alot for a quick reply from you all. I am new to performance testing. I got AWR report to see possible bottlenecks in the application which are visible by looking at the report. As i dont have DB admin rights, i cant try using TKPROF and SQL Trace. I wanted more understanding on ways to identify poor performance of SQL queries just by looking at report. So that i can document the same with the help of report which is available with me and use the same once i work on that :)
It would be a great help for quick replies on the same. :) :)
Thanks once again,
Edited by: 836402 on Feb 14, 2011 2:25 AM
AWR is largely the same as Statspack and so interpretation is pretty much the same, and on which there is a good collection of resources here:
What you've got to remember is that AWR is a system-wide report.
And significant session-level problems might be not significant in a system-wide report.
Another point is that there will always be a top N in the various sections.
So, whilst they might identify some low-hanging fruit, some easy gains, it's easy to get sucked into compulsive tuning disorder.
But if you can identify some easy SQL gains, you're going to need that TKPROF and SQL trace ability.
Note that this does not require DB admin rights.
The ability to generate sql trace files is not restricted - there are various ways to do it.
In some organisations, getting access to the trace files can be bureaucratic but there's no reason for this to be restricted.
TKPROF just requires access to a trace file and an Oracle client (you can also open the raw trace file in SQL Developer and get similar aggregations).