5 Replies Latest reply: Feb 14, 2011 4:39 AM by Dom Brooks RSS

    analyzing database performance using AWR report

    839405
      Hi,

      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:

      Load Profile

                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
      Transactions:          0.78     

      % 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

                          Begin     End
      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      emagent@db3-qa.dorado.com (TNS V1-V3)      /* OracleOEM */ select...
      119,931      1      119,931.00      1.73      6.49      196.72      cn58bhkggqvj4      emagent@db3-qa.dorado.com (TNS V1-V3)      /* OracleOEM */ SELECT d...
      105,024      4      26,256.00      1.51      0.92      0.92      3j9yx7t5abcyg      emagent@db3-qa.dorado.com (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
      -Preeti
        • 1. Re: analyzing database performance using AWR report
          P.Forstmann
          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
          • 2. Re: analyzing database performance using AWR report
            744052
            In addition to the above post you can also review the Oracle My Support Document, *How To Understand AWR Report / Statspack Report [ID 842884.1]*
            • 3. Re: analyzing database performance using AWR report
              Dom Brooks
              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.
              • 4. Re: analyzing database performance using AWR report
                839405
                Hi All,

                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,
                -Preeti

                Edited by: 836402 on Feb 14, 2011 2:25 AM
                • 5. Re: analyzing database performance using AWR report
                  Dom Brooks
                  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:
                  http://jonathanlewis.wordpress.com/statspack-examples/

                  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).