12 Replies Latest reply: Jul 1, 2014 10:25 PM by sb92075 RSS

    In AWR report my DB CPU is high

    Aryacool

      Hi.

       

        In my AWR report DB CPU time high please tell me what to do,

       

      DB Name         DB Id    Instance     Inst Num Startup Time         Release          RAC

      ------------ ----------- ------------ -------- --------------- ----------- ---

      ########        #######  #######        1         07-Jun-14 10:32    11.2.0.2.0      YES

       

       

      Host Name        Platform                         CPUs Cores Sockets Memory(GB)

      ---------------- -------------------------------- ---- ----- ------- ----------

      lddtmyy001q      Linux x86 64-bit                   16     8       2      94.39

       

       

                    Snap Id      Snap Time      Sessions Curs/Sess

                  --------- ------------------- -------- ---------

      Begin Snap:     24520 26-Jun-14 11:00:00        92       2.7

        End Snap:     24521 26-Jun-14 12:00:11        91       2.6

         Elapsed:               60.18 (mins)

         DB Time:              281.32 (mins)

       

       

      Cache Sizes                       Begin        End

      ~~~~~~~~~~~                  ---------- ----------

                     Buffer Cache:    15,744M    15,744M  Std Block Size:         8K

                 Shared Pool Size:     2,688M     2,688M      Log Buffer:    35,256K

       

       

      Load Profile              Per Second    Per Transaction   Per Exec   Per Call

      ~~~~~~~~~~~~         ---------------    --------------- ---------- ----------

            DB Time(s):                4.7               43.2                  1.44       2.55

             DB CPU(s):                4.0               37.3                  1.24       2.20

             Redo size:            1,598.2           14,758.5

         Logical reads:          819,474.1        7,567,294.4

         Block changes:                6.2               57.4

        Physical reads:            2,247.2           20,751.4

      Physical writes:               41.8              386.2

            User calls:                1.8               16.9

                Parses:                2.0               18.1

           Hard parses:                0.1                0.7

      W/A MB processed:                2.1               19.3

                Logons:                0.1                0.8

              Executes:                3.3               30.1

             Rollbacks:                0.0                0.0

          Transactions:                0.1

       

       

      Instance Efficiency Percentages (Target 100%)

      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

                  Buffer Nowait %:  100.00       Redo NoWait %:  100.00

                  Buffer  Hit   %:   99.95    In-memory Sort %:   99.95

                  Library Hit   %:   96.92        Soft Parse %:   96.34

               Execute to Parse %:   39.89         Latch Hit %:   99.99

      Parse CPU to Parse Elapsd %:   86.59     % Non-Parse CPU:   99.94

       

       

      Shared Pool Statistics        Begin    End

                                    ------  ------

                   Memory Usage %:   69.71   70.88

          % SQL with executions>1:   91.04   88.25

        % Memory for SQL w/exec>1:   81.96   83.70

       

       

      Top 5 Timed Foreground Events

      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

                                                                                                           

      Event                                     Waits        Time(s)      Avg wait(ms)     % DB time       Wait Class

      ------------------------------             ------------       -----------          ------                    ------             ----------

      DB CPU                                                   14,566          86.3          

      db file sequential read             648,259       1,944               3                   11.5              User I/O 

      direct path read                       34,210         168                 5                   1.0                User I/O 

      db file parallel read                15,222              74                5                     .4                 User I/O 

      db file scattered read                8,330          48                  6                      .3                 User I/O 

       

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

      ~~~~~~~~         Load Average

                     Begin       End     %User   %System      %WIO     %Idle

                 --------- --------- --------- --------- --------- ---------

                      4.25      4.76      25.5       0.2       3.3      74.2

       

       

      Instance CPU

      ~~~~~~~~~~~~

                    % of total CPU for Instance:      25.3

                    % of busy  CPU for Instance:      98.1

        %DB time waiting for CPU - Resource Mgr:       0.0

       

       

       

      SQL ordered by CPU Time         DB/Inst: DTMAI1Q/DTMAI1Q1  Snaps: 24520-24521

      -> 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  100.0% of Total CPU Time (s):          14,566

      -> Captured PL/SQL account for    0.0% of Total CPU Time (s):          14,566

       

       

          CPU                   CPU per           Elapsed                            

        Time (s)  Executions    Exec (s) %Total   Time (s)   %CPU    %IO    SQL Id   

      ---------- ------------ ---------- ------ ---------- ------ ------ -------------

         3,607.2            0        N/A   24.8    3,607.6  100.0     .0 c47vwr1yrdj0b

       

      Module: SQL Developer

      select count(distinct case when Dim_Class.CLASS_CODE = 'Y' then Fact_Coup

      on.TICKET_SID else NULL end) as c1, dim_flight.flight_number, dep.airport_

      code, arr.airport_code from fact_coupon, dim_date,

      dim_flight, fact_flight_ops, dim_airport dep, dim_airport arr, d

       

       

         3,607.1            0        N/A   24.8    3,607.6  100.0     .0 4j4dsx1k8wa2s

      Module: SQL Developer

      select dep.airport_code, arr.airport_code, dim_flight.flight_number, count(d

      istinct case when Dim_Class.CLASS_CODE ='Y' and Fact_Passenger.ACCEPTED_FLAG

      = 'Y' then Fact_Passenger.PAX_SID else NULL end) as d from fact_passenge

      r, dim_date, dim_flight, dim_class, dim_airport dep, dim_Airport arr, fa

       

       

         3,372.6            0        N/A   23.2    3,607.6   93.5    6.4 3d0qym0s1suqt

      Module: nqsserver@ladtmyy001q.atl2.dc.sita.aero (TNS V1-

      WITH SAWITH0 AS (select count(distinct case when (T386.CALENDAR_YEAR in ('2014

      ') or TO_CHAR(T37832.LOCAL_SCHEDULE_DEPARTURE_TIME,'YYYY') in ('')) and (substr(

      T386.MONTH_NAME , 1, 3) in ('APR') or TO_CHAR(T37832.LOCAL_SCHEDULE_DEPARTURE_TI

      ME,'MON') in ('')) then case when T6214.CLASS_CODE = 'C' then T51886.TICKET_SID

       

       

         1,988.3            0        N/A   13.7    2,893.3   68.7   31.5 1fyv9mav9x3k2

      Module: nqsserver@ladtmyy001q.atl2.dc.sita.aero (TNS V1-

      WITH SAWITH0 AS (select sum(case when TO_NUMBER(TO_CHAR(T99753.LOCAL_SCHEDULE_

      ARRIVAL_TIME, 'yyyy'), '9999') = 2014 and RTRIM(TO_CHAR(T99753.LOCAL_SCHEDULE_AR

      RIVAL_TIME, 'MONTH')) = 'MAY' then case when T263.AIRPORT_SID = T51886.ARRIVAL_

      AIRPORT_SID and T51886.COUPON_STATUS = 'F' then T51886.COUPON_PRORATE_FUEL_SURCH

       

       

         1,748.4            1   1,748.39   12.0    1,976.8   88.4   11.5 0pwbh6f6u6hu0

      Module: SQL Developer

      WITH SAWITH0 AS (SELECT DISTINCT COUNT(DISTINCT CASE WHEN TO_NUMB

      ER(TO_CHAR(T99753.LOCAL_SCHEDULE_ARRIVAL_TIME, 'yyyy'), '9999') = 2014 AN

      D RTRIM(TO_CHAR(T99753.LOCAL_SCHEDULE_ARRIVAL_TIME, 'MONTH')) = 'APRIL'

      THEN CASE WHEN T6472.CHILD_FLAG = 'N' AND T64

       

       

            46.9            1      46.91    0.3       89.1   52.6   47.2 cdgf1m1dt9p6y

      Module: SQL Developer

      SELECT * FROM DIM_TICKET WHERE TKT_NUMBER IN ( SELECT ETLR.TKT_NUMBER FROM ( SE

      LECT * FROM DIM_TICKET WHERE SRC_SYS_CODE='ETLR' AND TKT_NUMBER IN ( select TKT_

      NUMBER from dim_ticket where src_sys_code='ETLR' INTERSECT select TKT_NUMBER fro

      m dim_ticket where src_sys_code='RES' INTERSECT select TKT_NUMBER from dim_ticke

       

       

            42.7            0        N/A    0.3       85.5   49.9   50.3 4k68pfyv5fbg7

      Module: SQL Developer

      create table TEMP_TICKET as SELECT DISTINCT * FROM ( SELECT DISTINCT DIM_TICKET.

      TICKET_SID TICKET_SID, DIM_TICKET.TKT_NUMBER TKT_NUMBER, DIM_TICKET.TKT_ISSUE_DA

      TE TKT_ISSUE_DATE, DIM_TICKET.SRC_SYS_CODE SRC_SYS_CODE FROM DIM_TICKET, (SELECT

      TKT_NUMBER FROM DIM_TICKET GROUP BY TKT_NUMBER HAVING COUNT(*)>1) DT WHERE DIM_

       

       

            40.1            1      40.05    0.3      100.7   39.8   60.2 4ky2cys45a6n5

      Module: SQL Developer

      select * from ( select * from ( SELECT DISTINCT * FROM ( SELECT DISTINCT DIM_TI

      CKET.TICKET_SID TICKET_SID, DIM_TICKET.TKT_NUMBER TKT_NUMBER, DIM_TICKET.TKT_I

      SSUE_DATE TKT_ISSUE_DATE, DIM_TICKET.SRC_SYS_CODE SRC_SYS_CODE FROM DIM_TICKET,

      (SELECT TKT_NUMBER FROM DIM_TICKET GROUP BY TKT_NUMBER HAVING COUNT(*)>1) DT

       

       

            36.2            1      36.16    0.2      105.5   34.3   65.8 6ayvar4mma7dk

      Module: SQL Developer

      select * from ( select * from ( SELECT DISTINCT * FROM ( SELECT DISTINCT DIM_TI

      CKET.TICKET_SID TICKET_SID, DIM_TICKET.TKT_NUMBER TKT_NUMBER, DIM_TICKET.TKT_I

      SQL ordered by CPU Time         DB/Inst: DTMAI1Q/DTMAI1Q1  Snaps: 24520-24521

      -> 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  100.0% of Total CPU Time (s):          14,566

      -> Captured PL/SQL account for    0.0% of Total CPU Time (s):          14,566

       

       

          CPU                   CPU per           Elapsed                            

        Time (s)  Executions    Exec (s) %Total   Time (s)   %CPU    %IO    SQL Id   

      ---------- ------------ ---------- ------ ---------- ------ ------ -------------

      SSUE_DATE TKT_ISSUE_DATE, DIM_TICKET.SRC_SYS_CODE SRC_SYS_CODE FROM DIM_TICKET,

      (SELECT TKT_NUMBER FROM DIM_TICKET GROUP BY TKT_NUMBER HAVING COUNT(*)>1) DT

       

       

            20.3            1      20.35    0.1      358.6    5.7   93.2 6dz0waf2v27yb

      Module: nqsserver@ladtmyy001q.atl2.dc.sita.aero (TNS V1-

      WITH SAWITH0 AS (select count(case when TO_NUMBER(TO_CHAR(T99753.LOCAL_SCHEDUL

      E_ARRIVAL_TIME, 'yyyy'), '9999') = 2014 and RTRIM(TO_CHAR(T99753.LOCAL_SCHEDULE_

      ARRIVAL_TIME, 'MONTH')) = 'FEBRUARY' then case when T6472.REV_FLAG = 'N' and T6

      472.XRES_FLAG = 'N' and T6472.ACCEPTED_FLAG = 'Y' and T263.AIRPORT_SID = T6472.A

       

       

                ------------------------------------------------------------- 

       

       

       

       

       

      Regards,

      Arya

        • 1. Re: In AWR report my DB CPU is high
          Hemant K Chitale

          When you have 16 CPUs,  4 CPU seconds per second is only 25% usage.

           

           

          Hemant K Chitale


          • 2. Re: In AWR report my DB CPU is high
            sb92075

            >lddtmyy001q      Linux x86 64-bit                   16     8       2      94.39

                          Snap Id      Snap Time      Sessions Curs/Sess

                        --------- ------------------- -------- ---------

            Begin Snap:     24520 26-Jun-14 11:00:00        92       2.7

              End Snap:     24521 26-Jun-14 12:00:11        91       2.6

               Elapsed:               60.18 (mins)

               DB Time:              281.32 (mins)

             

             

            16 core * 60 minutes = 960 CPU minute

             

            so system is only about 25% busy.

             

            Do NOTHING

            • 3. Re: In AWR report my DB CPU is high
              Aryacool

              Hi ,

               

              As above the AWR report ,the DB time is too more rather than elapsed timing ,I think the elapsed time should be high in compare to db time ,so as per the above comment ,there is no scope in database level tuning .please suggest ,how can be optimized the db time .

               

               

              Regards

              Arya

              • 4. Re: In AWR report my DB CPU is high
                Hemant K Chitale

                You have misunderstood the statistics.

                 

                "elapsed time" is wall-clock time.  Thus, between 11:00 and 12:00 on 26-Jun, 60minutes (i.e. 3600seconds) have elapsed.

                "total cpu time" is elapsed time X number of CPUs.  In 60minutes elapsed time, 1 CPU can do 3600CPUseconds. 4  CPUs can do 14400CPUseconds because all 4 can work concurrently. 16 CPUs can do 57,600CPUseconds.
                Your Database CPU usage is 14,566seconds which is is only 25% of the total available CPUseconds.

                 

                 

                Hemant K Chitale

                • 5. Re: In AWR report my DB CPU is high
                  Aryacool

                  Hi,

                   

                      Thanks for your reply i got it, Is there any need to change in db level for performance or i have to look on sql query for tuning please suggest.

                   

                   

                  Regards,

                  Arya

                  • 6. Re: In AWR report my DB CPU is high
                    Aman....

                    The actual question would be that do you really have an issue with the database that you are trying to resolve or it's just an attempt to make the db run "more faster" ?

                     

                    Aman....

                    • 7. Re: In AWR report my DB CPU is high
                      Hemant K Chitale

                      Do you have performance problem ?  Do you *think* that you have a performance problem ?

                      Take action only if there is really a problem to be solved.  CPU at 25% usage is not a problem to be solved (in fact, it is underutilisation of CPU)

                       

                       

                      Maybe .... maybe.... a user or manager has told you about a specific form or report that is not performing to expectations.  Aggregate data from AWR doesn't help you address such a problem.  Look at the SQLs, the execution plans, the wait events  for that form / report.

                       

                      Hemant K Chitale

                      • 8. Re: In AWR report my DB CPU is high
                        Aryacool

                        Hi Aman,

                         

                           Thanks for your reply, main concern is that they have given me some query for tune which are mainly used in reports in pre-production environment if it works fine then it will be deploy on production. That's why  i am concern about any DB level performance tuning required or query level tuning. So for that reason i have generated AWR report 1st and analysed and asking the query.

                         

                         

                        Regards,

                        Arya

                        • 9. Re: In AWR report my DB CPU is high
                          Hemant K Chitale

                          If you have a specific query to tune, an AWR report doesn't help.  Look at the  execution plan, look at the execution statistics, look at the wait events, consider concurrency.

                           

                          Hemant K Chitale

                          • 10. Re: In AWR report my DB CPU is high
                            Aman....

                            Aryacool wrote:

                             

                            Hi Aman,

                             

                               Thanks for your reply, main concern is that they have given me some query for tune which are mainly used in reports in pre-production environment if it works fine then it will be deploy on production. That's why  i am concern about any DB level performance tuning required or query level tuning. So for that reason i have generated AWR report 1st and analysed and asking the query.

                             

                             

                            Regards,

                            Arya

                            It's  a wrong approach to look into the AWR when actually what you have a specific query to tune. And with what you have posted, it doesn't seem as if that query is also having any issue. See if you can get specific details about why the query needs tuning and if it is indeed problematic , do you have any history of it' previous execution available ?

                             

                            Aman....

                            • 11. Re: In AWR report my DB CPU is high
                              Aryacool

                              Hi Aman,

                               

                                I have one query aman, then at what time we will go for awr report or addm report, if you clarify then i will get some more idea on tuning.

                              • 12. Re: In AWR report my DB CPU is high
                                sb92075

                                Aryacool wrote:

                                 

                                Hi Aman,

                                 

                                  I have one query aman, then at what time we will go for awr report or addm report, if you clarify then i will get some more idea on tuning.

                                 

                                 

                                HOW To Make TUNING request

                                HOW TO: Post a SQL statement tuning request - template posting