7 Replies Latest reply: Aug 26, 2013 9:09 PM by Hemant K Chitale RSS

    query consuming cpu resources.

    LazyOraDBA

      Hi Guys,

       

      query consuming cpu resources.

       

      Can you please suggest to improve the performance ?

       

      Regards,

        • 1. Re: query consuming cpu resources.
          sb92075

          LazyDBA11g wrote:

           

          Hi Guys,

           

          Below query consuming cpu resources.

           

          Can you please suggest to improve the performance ?

           

          Plan hash value: 1210001828

           

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

          | Id  | Operation             | Name                | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

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

          |   0 | SELECT STATEMENT      |                     |  2478K|    30M|       | 14601   (1)| 00:02:56 |

          |   1 |  HASH UNIQUE          |                     |  2478K|    30M|    47M| 14601   (1)| 00:02:56 |

          |   2 |   INDEX FAST FULL SCAN| ID_APP |  2478K|    30M|       |  2784   (1)| 00:00:34 |

          -----------------------------------------------------------------------------------------------------   11:54 AM

           

          Statistics :

           

          Total Per Execution Per Row

          Executions 360,568 1 0.25

          Elapsed Time (sec) 312,261.97 0.87 0.22

          CPU Time (sec) 306,884.41 0.85 0.21

          Buffer Gets 4,024,978,215 11,162.88 2,792.76

          Disk Reads 5,002 0.01 <0.01

          Direct Writes 0 0.00 0.00

          Rows 1,441,220 4.00 1

          Fetches 360,320 1.00 0.25

           

           

           

          Regards,

           

          It takes time to retrieve 2.5 million rows.

          Some SQL can not be improved.

          Accept reality.

          • 2. Re: query consuming cpu resources.
            Antonio Navarro

            Maybe if you reduce selected rows (wide where), reduce selected columns, if use compress feature disable it, ....

             

            HTH

            Antonio NAVARRO

            • 3. Re: query consuming cpu resources.
              JohnWatson2

              What was the query? It is difficult to tune invisible SQL

              Looking at the execution plan, the problem could be that you have an unnecessary DISTINCT in the query: the optimizer is spending most of its time removing duplicates, but it doesn't believe that that there are any duplicates.

              The statistics you provided do not appear to be related, where do they come from?

              • 4. Re: query consuming cpu resources.
                Hemant K Chitale

                It seems that you have 360K executions fetching only 4 rows per execution.  However, the Explain Plan expects to fetch 2.5million rows per execution.  That is a very wide difference between the expected execution (2.5million rows) and the actual execution (4 rows) indicating that your statistics may not be relevant for the query being executed.

                 

                 

                Hemant K Chitale

                • 5. Re: query consuming cpu resources.
                  LazyOraDBA

                  Hi All,

                  Thanks for quick response..

                  • 6. Re: query consuming cpu resources.
                    Hoek

                    Time to gather fresh table statistics

                    • 7. Re: query consuming cpu resources.
                      Hemant K Chitale

                      The Explain Plan shows that Oracle expects 2.7million unique values but there are only 4 unique values.

                      The expected i/o is significant (going by the cost and TempSpace) but the true I/O is only 14,636 blocks (114MB).

                       

                      The statistics on the table and the column are not representative of the actual data that is present.

                       

                      Hemant K Chitale