1 2 Previous Next 29 Replies Latest reply: Aug 22, 2014 6:40 AM by Aryacool RSS

    In my AWR report in which section i have to work on performance

    Aryacool

      Hi My AWR report please tell me which are my problematic are ,

       

       

      DB NameDB IdInstanceInst numStartup TimeReleaseRAC
      xxxxxxxx2155480196xxxxxxxxx106-Jun-14 19:0611.2.0.2.0NO

       

       

       

      Host NamePlatformCPUsCoresSocketsMemory (GB)
      xxxxxxxxxxxxxxxxxxxxxxxxxLinux x86 64-bit168294.39

       

       

      Snap IdSnap TimeSessionsCursors/Session
      Begin Snap:2493919-Jun-14 05:00:43775.6
      End Snap:2494119-Jun-14 07:00:52726.2
      Elapsed: 120.15 (mins)
      DB Time: 392.37 (mins)

       

       

      Report Summary

      Cache Sizes

       

      BeginEnd
      Buffer Cache:5,184M5,184MStd Block Size:8K
      Shared Pool Size:736M736MLog Buffer:16,532K

       

      Load Profile

       

      Per SecondPer TransactionPer ExecPer Call
      DB Time(s):3.31.50.023.77
      DB CPU(s):0.40.20.000.47
      Redo size:48,546.922,158.1
      Logical reads:39,852.618,189.8
      Block changes:322.0147.0
      Physical reads:4,248.21,939.0
      Physical writes:1,177.3537.4
      User calls:0.90.4
      Parses:1.50.7
      Hard parses:0.10.0
      W/A MB processed:0.20.1
      Logons:0.00.0
      Executes:153.970.3
      Rollbacks:0.00.0
      Transactions:2.2

       

      Instance Efficiency Percentages (Target 100%)

       

      Buffer Nowait %:99.85Redo NoWait %:100.00
      Buffer Hit %:96.08In-memory Sort %:99.94
      Library Hit %:99.76Soft Parse %:93.98
      Execute to Parse %:99.04Latch Hit %:99.89
      Parse CPU to Parse Elapsd %:46.21% Non-Parse CPU:99.57

       

      Shared Pool Statistics

       

      BeginEnd
      Memory Usage %:66.4968.07
      % SQL with executions>1:89.3291.02
      % Memory for SQL w/exec>1:79.9277.05

       

       

      Top 5 Timed Foreground Events

       

      EventWaitsTime(s)Avg wait (ms)% DB timeWait Class
      db file sequential read2,705,56612,994555.20User I/O
      DB CPU2,95712.56
      direct path read temp724,8972,382310.12User I/O
      read by other session371,7391,62346.89User I/O
      direct path write temp116,2401,445126.14User I/O

      Host CPU (CPUs: 16 Cores: 8 Sockets: 2)

       

      Load Average BeginLoad Average End%User%System%WIO%Idle
      1.622.922.50.210.597.2

      Instance CPU

       

      %Total CPU%Busy CPU%DB time waiting for CPU (Resource Manager)
      2.692.30.0

      Memory Statistics

       

      BeginEnd
      Host Mem (MB):96,657.296,657.2
      SGA use (MB):6,656.06,656.0
      PGA use (MB):1,365.51,872.2
      % Host Mem used for SGA+PGA:8.308.82

       

       

      If anything more required tell please suggest me what should be my approach to performance tuning .

       

       

      Regards,

        • 1. Re: In my AWR report in which section i have to work on performance
          ranit B

          Hi Arya,

           

          Your concern looks exactly similar as mine check my thread - Reg: understanding AWR report content -

           

          By the way, start looking into this - Interpreting AWR Report - Straight to the Goal by Franck Pachot on Prezi

           

          And, I am following this thread now for inputs.

          • 2. Re: In my AWR report in which section i have to work on performance
            MohaAGOU

            Hi Arya,

             

            from the 5 Top events; over 50% of Db time is spent on "db file sequential read" check the section of "SQL Statistics" for queries consuming IOs resources.

             

            Regads

             

             


            • 3. Re: In my AWR report in which section i have to work on performance
              Aryacool

              SQL ordered by Elapsed Time

              • Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
              • % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100
              • %Total - Elapsed Time as a percentage of Total DB time
              • %CPU - CPU Time as a percentage of Elapsed Time
              • %IO - User I/O Time as a percentage of Elapsed Time
              • Captured SQL account for 99.6% of Total DB Time (s): 23,542
              • Captured PL/SQL account for 19.4% of Total DB Time (s): 23,542
              Elapsed Time (s)ExecutionsElapsed Time per Exec (s)%Total%CPU%IOSQL IdSQL ModuleSQL Text
              4,884.7414,884.7420.753.7596.546z0b5fxm42bxqSQL DeveloperSELECT * FROM VW_X60X80 WHERE ...
              4,554.1214,554.1219.344.3395.994zv1z5aq1qkypJDBC Thin ClientBegin DATAMART_DWH_DSCHEMA_3.P...
              4,472.6814,472.6819.0025.1175.10g9drv0w8g5gp3SQL Developerselect dim_ticket.src_sys_code...
              3,655.1213,655.1215.534.5296.068hp86nx4cmp9gSQL DeveloperSELECT * FROM VW_X60X80 WHERE ...
              3,352.81348,8970.0114.241.9398.46c4z9uv14afxmyJDBC Thin ClientUPDATE /*+ index(FC NDX13_FACT...
              1,266.120 5.384.9795.4477xtpds5txwnsJDBC Thin Clientinsert /*+ append */ into DATA...
              1,205.6111,205.615.1248.4751.93d295ptfd3w5r7JDBC Thin Clientinsert /*+ append */ into DATA...
              1,102.96740,8010.004.684.0396.3577fh8665wcnytJDBC Thin ClientUPDATE /*+ index(FC NDX13_FACT...
              882.170 3.7513.4188.147yu6sst6pzzwqSQL DeveloperSELECT dim_flight.flight_numbe...
              419.541419.541.7821.8678.24b4nf8sk4vcf6mSQL Developerselect * from dim_ticket where...
              357.641357.641.521.4598.971csc7xpmgx6u3SQL DeveloperSELECT da.airport_code dep, aa...
              260.451260.451.1116.9383.18d4tsm770ub02gSQL Developerselect * from dim_related_tick...

              Back to SQL Statistics
              Back to Top

              SQL ordered by CPU Time

              • Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
              • %Total - CPU Time as a percentage of Total DB CPU
              • %CPU - CPU Time as a percentage of Elapsed Time
              • %IO - User I/O Time as a percentage of Elapsed Time
              • Captured SQL account for 96.9% of Total CPU Time (s): 2,957
              • Captured PL/SQL account for 6.7% of Total CPU Time (s): 2,957
              CPU Time (s)ExecutionsCPU per Exec (s)%TotalElapsed Time (s)%CPU%IOSQL IdSQL ModuleSQL Text
              1,122.9111,122.9137.984,472.6825.1175.10g9drv0w8g5gp3SQL Developerselect dim_ticket.src_sys_code...
              584.391584.3919.771,205.6148.4751.93d295ptfd3w5r7JDBC Thin Clientinsert /*+ append */ into DATA...
              197.341197.346.674,554.124.3395.994zv1z5aq1qkypJDBC Thin ClientBegin DATAMART_DWH_DSCHEMA_3.P...
              183.051183.056.194,884.743.7596.546z0b5fxm42bxqSQL DeveloperSELECT * FROM VW_X60X80 WHERE ...
              165.101165.105.583,655.124.5296.068hp86nx4cmp9gSQL DeveloperSELECT * FROM VW_X60X80 WHERE ...
              118.310 4.00882.1713.4188.147yu6sst6pzzwqSQL DeveloperSELECT dim_flight.flight_numbe...
              91.73191.733.10419.5421.8678.24b4nf8sk4vcf6mSQL Developerselect * from dim_ticket where...
              68.48168.482.32168.8440.5659.629mfastqxmnvzaSQL DeveloperSELECT df.flight_prefix, fc.* ...
              64.63348,8970.002.193,352.811.9398.46c4z9uv14afxmyJDBC Thin ClientUPDATE /*+ index(FC NDX13_FACT...
              63.21163.212.14129.7448.7251.54950f5uq79gup0SQL Developerselect dim_ticket.src_sys_code...
              62.960 2.131,266.124.9795.4477xtpds5txwnsJDBC Thin Clientinsert /*+ append */ into DATA...
              46.26146.261.56164.4728.1271.449agmxx8uhnmrwSQL Developerselect dim_ticket.src_sys_code...
              44.42740,8010.001.501,102.964.0396.3577fh8665wcnytJDBC Thin ClientUPDATE /*+ index(FC NDX13_FACT...
              44.11144.111.49260.4516.9383.18d4tsm770ub02gSQL Developerselect * from dim_related_tick...
              42.27221.141.43127.5733.1467.035mkcw1cs4vd5wSQL DeveloperSELECT fact_fop.ticket_sid, fa...
              36.47136.471.23202.8617.9882.143brp1mu8fnp59SQL Developerselect * from dim_related_tick...

              Back to SQL Statistics
              Back to Top

              SQL ordered by User I/O Wait Time

              • Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
              • %Total - User I/O Time as a percentage of Total User I/O Wait time
              • %CPU - CPU Time as a percentage of Elapsed Time
              • %IO - User I/O Time as a percentage of Elapsed Time
              • Captured SQL account for 99.8% of Total User I/O Wait Time (s): 20,718
              • Captured PL/SQL account for 21.1% of Total User I/O Wait Time (s): 20,718
              User I/O Time (s)ExecutionsUIO per Exec (s)%TotalElapsed Time (s)%CPU%IOSQL IdSQL ModuleSQL Text
              4,715.8214,715.8222.764,884.743.7596.546z0b5fxm42bxqSQL DeveloperSELECT * FROM VW_X60X80 WHERE ...
              4,371.3714,371.3721.104,554.124.3395.994zv1z5aq1qkypJDBC Thin ClientBegin DATAMART_DWH_DSCHEMA_3.P...
              3,510.9613,510.9616.953,655.124.5296.068hp86nx4cmp9gSQL DeveloperSELECT * FROM VW_X60X80 WHERE ...
              3,358.9513,358.9516.214,472.6825.1175.10g9drv0w8g5gp3SQL Developerselect dim_ticket.src_sys_code...
              3,301.20348,8970.0115.933,352.811.9398.46c4z9uv14afxmyJDBC Thin ClientUPDATE /*+ index(FC NDX13_FACT...
              1,208.440 5.831,266.124.9795.4477xtpds5txwnsJDBC Thin Clientinsert /*+ append */ into DATA...
              1,062.68740,8010.005.131,102.964.0396.3577fh8665wcnytJDBC Thin ClientUPDATE /*+ index(FC NDX13_FACT...
              777.530 3.75882.1713.4188.147yu6sst6pzzwqSQL DeveloperSELECT dim_flight.flight_numbe...
              626.081626.083.021,205.6148.4751.93d295ptfd3w5r7JDBC Thin Clientinsert /*+ append */ into DATA...
              353.961353.961.71357.641.4598.971csc7xpmgx6u3SQL DeveloperSELECT da.airport_code dep, aa...
              328.231328.231.58419.5421.8678.24b4nf8sk4vcf6mSQL Developerselect * from dim_ticket where...
              216.651216.651.05260.4516.9383.18d4tsm770ub02gSQL Developerselect * from dim_related_tick...

              Back to SQL Statistics
              Back to Top

              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 as a percentage of Total Buffer Gets
              • %CPU - CPU Time as a percentage of Elapsed Time
              • %IO - User I/O Time as a percentage of Elapsed Time
              • Total Buffer Gets: 287,307,869
              • Captured SQL account for 99.9% of Total
              Buffer GetsExecutionsGets per Exec%TotalElapsed Time (s)%CPU%IOSQL IdSQL ModuleSQL Text
              162,081,4881162,081,488.0056.411,205.6148.4751.93d295ptfd3w5r7JDBC Thin Clientinsert /*+ append */ into DATA...
              37,150,510137,150,510.0012.934,884.743.7596.546z0b5fxm42bxqSQL DeveloperSELECT * FROM VW_X60X80 WHERE ...
              35,344,416135,344,416.0012.303,655.124.5296.068hp86nx4cmp9gSQL DeveloperSELECT * FROM VW_X60X80 WHERE ...
              16,098,6610 5.601,266.124.9795.4477xtpds5txwnsJDBC Thin Clientinsert /*+ append */ into DATA...
              9,321,19319,321,193.003.24168.8440.5659.629mfastqxmnvzaSQL DeveloperSELECT df.flight_prefix, fc.* ...
              6,883,22416,883,224.002.404,554.124.3395.994zv1z5aq1qkypJDBC Thin ClientBegin DATAMART_DWH_DSCHEMA_3.P...
              5,265,1440 1.83882.1713.4188.147yu6sst6pzzwqSQL DeveloperSELECT dim_flight.flight_numbe...
              4,193,407740,8015.661.461,102.964.0396.3577fh8665wcnytJDBC Thin ClientUPDATE /*+ index(FC NDX13_FACT...
              3,234,47513,234,475.001.13164.4728.1271.449agmxx8uhnmrwSQL Developerselect dim_ticket.src_sys_code...
              2,645,255348,8977.580.923,352.811.9398.46c4z9uv14afxmyJDBC Thin ClientUPDATE /*+ index(FC NDX13_FACT...

              Back to SQL Statistics
              Back to Top

              SQL ordered by Reads

              • %Total - Physical Reads as a percentage of Total Disk Reads
              • %CPU - CPU Time as a percentage of Elapsed Time
              • %IO - User I/O Time as a percentage of Elapsed Time
              • Total Disk Reads: 30,626,477
              • Captured SQL account for 100.0% of Total
              Physical ReadsExecutionsReads per Exec%TotalElapsed Time (s)%CPU%IOSQL IdSQL ModuleSQL Text
              5,485,71315,485,713.0017.91168.8440.5659.629mfastqxmnvzaSQL DeveloperSELECT df.flight_prefix, fc.* ...
              5,381,49915,381,499.0017.574,472.6825.1175.10g9drv0w8g5gp3SQL Developerselect dim_ticket.src_sys_code...
              3,304,9970 10.79882.1713.4188.147yu6sst6pzzwqSQL DeveloperSELECT dim_flight.flight_numbe...
              2,672,08212,672,082.008.721,205.6148.4751.93d295ptfd3w5r7JDBC Thin Clientinsert /*+ append */ into DATA...
              2,224,88421,112,442.007.26127.5733.1467.035mkcw1cs4vd5wSQL DeveloperSELECT fact_fop.ticket_sid, fa...
              1,926,83711,926,837.006.29419.5421.8678.24b4nf8sk4vcf6mSQL Developerselect * from dim_ticket where...
              1,117,06711,117,067.003.65164.4728.1271.449agmxx8uhnmrwSQL Developerselect dim_ticket.src_sys_code...
              1,116,01611,116,016.003.64260.4516.9383.18d4tsm770ub02gSQL Developerselect * from dim_related_tick...
              1,112,57511,112,575.003.6381.4124.9775.165k684px5gwhndSQL DeveloperSELECT FACT_FOP.FOP_AMT, FACT_...
              1,025,36711,025,367.003.35129.7448.7251.54950f5uq79gup0SQL Developerselect dim_ticket.src_sys_code...
              855,5721855,572.002.794,884.743.7596.546z0b5fxm42bxqSQL DeveloperSELECT * FROM VW_X60X80 WHERE ...
              791,1761791,176.002.583,655.124.5296.068hp86nx4cmp9gSQL DeveloperSELECT * FROM VW_X60X80 WHERE ...
              678,5671678,567.002.224,554.124.3395.994zv1z5aq1qkypJDBC Thin ClientBegin DATAMART_DWH_DSCHEMA_3.P...
              575,0771575,077.001.88202.8617.9882.143brp1mu8fnp59SQL Developerselect * from dim_related_tick...
              557,7251557,725.001.8259.5217.8782.26dzu0y0g8j936qSQL Developerselect * from dim_related_tick...
              495,123348,8971.421.623,352.811.9398.46c4z9uv14afxmyJDBC Thin ClientUPDATE /*+ index(FC NDX13_FACT...
              • 4. Re: In my AWR report in which section i have to work on performance
                Mohamed Houri

                db file sequential read (single block read or read from index) is closely related to logical I/O and hence to the SQL ordered by Gets part of the AWR. When you drill down to this part you will find that a single execution of a single sql statement (sql_id = d295ptfd3w5r7) consummes 162 millions of logical I/O

                 

                Look at this insert /*+ append */ into DATA.. statement and get its execution plan.

                 

                Best regards

                Mohamed Houri

                • 5. Re: In my AWR report in which section i have to work on performance
                  MohaAGOU

                  check the execution plans of the top 3 queris in the SQl oredr by gets section.

                   

                  Regards.

                  • 6. Re: In my AWR report in which section i have to work on performance
                    Aryacool

                    will update you tomorrow as my shift finished for today...

                    • 7. Re: In my AWR report in which section i have to work on performance
                      Dom Brooks

                      Do you have a specific performance problem or are you just looking to fill time and try to make something better?

                       

                      If it's generic tuning, then a) you can always make something slightly faster but b) it takes increasing amounts of effort and c) there will always be a top N.

                       

                      Otherwise, the top entries for the various sections point you in the right direction of your biggest hitters.

                       

                      It only accounts for 6% of your db time but "read by other session" is always an amber flag.

                      And I'd always want to sanity check what is reading/writing to temp.

                      • 8. Re: In my AWR report in which section i have to work on performance
                        Aryacool

                        Hi Dom,

                         

                             As told by application end and client side that it is taking lot of time so i am analyzing this, whether its problem from database end or from query end, this is a DSS environment in which i am working.

                         

                        Regards,

                        Arya

                        • 9. Re: In my AWR report in which section i have to work on performance
                          sb92075

                          Aryacool wrote:

                           

                          Hi Dom,

                           

                               As told by application end and client side that it is taking lot of time so i am analyzing this, whether its problem from database end or from query end, this is a DSS environment in which i am working.

                           

                          Regards,

                          Arya

                           

                           

                          HOW To Make TUNING request

                          https://forums.oracle.com/forums/thread.jspa?threadID=2174552#9360003

                          • 10. Re: In my AWR report in which section i have to work on performance
                            jgarry

                            You (the OP) might also look into the various session statistics for this insert.  The append is a quick way to go, but oftentimes since it is coming from a thin client there may be client parameters that can help.  Or they can hurt if you wind up choking out everyone else by making the data come in faster, or have various concurrency issues (perhaps having something to do with Dom's "amber flag").  It may be worthwhile asking the user if he can hold off until the db is less busy before doing that insert.  Is that the same user that is complaining?

                            • 11. Re: In my AWR report in which section i have to work on performance
                              Aryacool

                              Hi All,

                               

                                 My memory is

                               

                              Memory Statistics

                               

                              BeginEnd
                              Host Mem (MB):96,657.296,657.2
                              SGA use (MB):6,656.06,656.0
                              PGA use (MB):1,365.51,872.2
                              % Host Mem used for SGA+PGA:8.308.82

                               

                               

                              I have a doubt my host memory is 96GB and  Memory_target & memory_max_target is 10GB , is this creating problem shall i increase the memory as recommended 40% of host ??

                               

                               

                              Regards,

                              Arya

                              • 12. Re: In my AWR report in which section i have to work on performance
                                sb92075

                                Aryacool wrote:

                                 

                                Hi All,

                                 

                                   My memory is

                                 

                                Memory Statistics

                                 

                                BeginEnd
                                Host Mem (MB): 96,657.2 96,657.2
                                SGA use (MB): 6,656.0 6,656.0
                                PGA use (MB): 1,365.5 1,872.2
                                % Host Mem used for SGA+PGA: 8.30 8.82

                                 

                                 

                                I have a doubt my host memory is 96GB and  Memory_target & memory_max_target is 10GB , is this creating problem shall i increase the memory as recommended 40% of host ??

                                 

                                 

                                Regards,

                                Arya

                                 

                                How do you  decide when the SGA is big enough?

                                • 13. Re: In my AWR report in which section i have to work on performance
                                  Aryacool

                                  Hi,

                                   

                                    As per AWR analyze, 

                                   

                                  SGA Target Advisory

                                   

                                  SGA Target Size (M)SGA Size FactorEst DB Time (s)Est Physical Reads
                                  2,4960.382,409,9082,796,836,385
                                  3,3280.502,279,4242,570,688,282
                                  4,1600.632,064,6482,195,463,595
                                  4,9920.751,894,8841,899,306,303
                                  5,8240.881,815,9871,763,126,148
                                  6,6561.001,685,8401,535,289,227
                                  7,4881.131,591,0961,370,092,106
                                  8,3201.251,488,9361,191,537,969
                                  9,1521.381,438,5291,105,101,186
                                  9,9841.501,349,854949,576,387
                                  10,8161.631,312,091883,558,950
                                  11,6481.751,302,314867,438,413
                                  12,4801.881,301,640867,438,413
                                  13,3122.001,301,640867,438,413

                                   

                                   

                                  As per above SGA targetadvisory  the current SGA size factor is 1.00 so my Est Physical read is 1,535,289,227, but if i increase the SGA target size to 13GB or more my Est physical read comes down to 867,438,413, please advice..


                                  Regards,

                                  Arya

                                  1 2 Previous Next