1 2 3 4 Previous Next 47 Replies Latest reply on Apr 30, 2014 6:17 PM by Franck Pachot

    Reg: Analyzing a query for performance issue-

    ranit B

      Hi Experts,

       

      I have a query which was working previously fine but in yesterday's production run it took much more than usual. So, I believe there is something wrong with the database like - might be some resource contention, or locks, or network issue, or might also be

       

      select  distinct
             m.lno,
             m.ltyp,
             m.col_3,
             times,
             m.led_code
      from
             schema_x.table_stm m
      where
            m.col_3 = 'r'
            and m.led_code in ('1000','1001', . . . 70 more values)
            and m.baln > 0;

       

       

      Indexes on table - schema_x. table_stm


      index_1 -->-- lno                     
      index_2 -->-- led_code                      
      index_3 -->-- led_code, lno          
      index_4 -->-- col_1                     
      index_5 -->-- lno, baln, col_2
      index_6 -->-- col_4

       

      So, my plan is -

      1) To get the last production run's trace file (10046 Level 8) or the tkprof output.

      2) To get the query plan of the last produciton run.

       

      My idea is to first see the trace file and identify if there were any waits. (but I don't know how to identify that)

       

      Is my approach proper? Could you please advise on this?

       

      Thanks and Regards,

      -- Ranit

      (on Oracle 11.2.0.3.0)

        • 1. Re: Reg: Analyzing a query for performance issue-
          saratpvv

          Take single AWR report with certain interval will contain entire information - instead of going trace file/tkproof etc.,

          If the query alone is taking more time compare to yesterday - just provide explain plan with all stats

          • 2. Re: Reg: Analyzing a query for performance issue-
            krishan

            Check execution plan for any changes.

            • 3. Re: Reg: Analyzing a query for performance issue-
              Martin Preiss

              if you are allowed to use AWR I would also suggest to take a look at the plans shown by dbms_xplan.display_awr for the given sql_id. This should tell you if there was a plan change. You could also adapt Kerry Osborne's unstable plans script to check the differences in resource usage: http://kerryosborne.oracle-guy.com/scripts/unstable_plans.sql (assuming that there was indeed a plan change).

              1 person found this helpful
              • 4. Re: Reg: Analyzing a query for performance issue-
                top.gun

                You have alot of indexes on 1 table.

                The best one may be (col_3, led_code, baln) but you don't have this one created.

                So try that one.

                • 5. Re: Reg: Analyzing a query for performance issue-
                  cary-millsap

                  I like the work plan you laid out. The best information you can have is a 10046 level 8 trace file from when the query performed quickly, and the same kind of trace file from when the query misbehaved. tkprof can help you visualize the trace data better than looking at the raw trace files, but tkprof leaves out a lot of information. If you want to see the results on your files that our Method R software would give you, contact me offline.

                  Until you see these two files, we'll only be able to guess at what's really going on.

                  I will echo @Top_Gun's comment that an index on (col_3, led_code, baln) will probably help your query; however, without examining the other queries on your system, you won't know what the optimal order for listing the columns in the index should be. See Tapio Lahdenmäki's book "Relational Database Index Design and the Optimizers" for details on that topic. If you take a look at that book, I think you'll find you can reduce the total number of indexes on your table without losing anything.

                  1 person found this helpful
                  • 6. Re: Reg: Analyzing a query for performance issue-
                    Lothar Flatz

                    Well, the question is iuf the query used to be fine, is this bad plan

                    1.) a one time issue?

                    2.) Constant issue from now on?

                     

                    In any case it shoud pay of to get: as much as possible information on the bad behavior: The trace would be ideal. Apart from that there is moe information available you might want to secure in case the trace can not be found.

                    THis includes the plan from awr, an awr sql report  and an ash report for the statement..

                    Based on that you can decide if the issue reproduces.

                     

                    I see the query does not have bind variables. That sees a bit strange. Is this a generated query - dynamic sql? Is the in list always the same?

                    • 7. Re: Reg: Analyzing a query for performance issue-
                      rp0428
                      If you want to see the results on your files that our Method R software would give you, contact me offline.

                      Directing people to your own commercial web site for software that you sell on that site could be considered a violation of the forum's terms of use.

                       

                      contains a solicitation of funds, goods or services, or promotes or advertises goods or services;
                      • 8. Re: Reg: Analyzing a query for performance issue-
                        top.gun

                        So if you have the plan of the last execution, post it so we can have a look.....

                        • 9. Re: Reg: Analyzing a query for performance issue-
                          ranit B

                          I don't have the plan of last execution but the current one:

                          -------------------------------------------------------------------------------------------------------------------------------------------------------------------
                          | Id  | Operation           | Name                       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
                          -------------------------------------------------------------------------------------------------------------------------------------------------------------------
                          |   0 | SELECT STATEMENT    |                            |      1 |        |    476K|00:00:22.88 |     310K|   2170 |   2170 |       |       |          |         |
                          |   1 |  RESULT CACHE       | 3r6829c07yqwhcjrp3w427r7yc |      1 |        |    476K|00:00:22.88 |     310K|   2170 |   2170 |       |       |          |         |
                          |   2 |   HASH UNIQUE       |                            |      1 |  41494 |    476K|00:00:22.28 |     310K|   2170 |   2170 |    26M|  4881K| 5263K (1)|   18432 |
                          |*  3 |    TABLE ACCESS FULL| table_stm                  |      1 |    248K|    476K|00:00:06.27 |     310K|      0 |      0 |       |       |          |         |
                          -------------------------------------------------------------------------------------------------------------------------------------------------------------------


                          -------------------------------------------------------------------------------------------------------------------------------------------------------------------
                          Predicate Information (identified by operation id):
                          ---------------------------------------------------

                             3 - filter(("m"."col_3"='R' AND "m"."baln">0 AND INTERNAL_FUNCTION("m"."led_code")))

                           

                          Can you suggest something regarding this?

                          Also, just wondering - how the Result Cache is getting invoked here because AFAIK it is only used when forced using the Result Cache hint. Please advise.

                           

                          I have got the production AWR report for last run. Please let me know which sections I need to particularly look into. I don't know how to read/interpret an AWR report.

                           

                           

                          • 10. Re: Reg: Analyzing a query for performance issue-
                            Richard Foote-Oracle

                            If the RESULT_CACHE_MODE parameter is set to FORCE, then it doesn't matter about not having the hint.

                             

                            And reading a result from cache vs. performing a FTS on the table_stm table and reading 476K of rows can likely make a big difference to how this query performs.

                             

                            If you perform an simple SQL*PLUS autotrace and look at the number of consistent gets, a value of 0 will tell whether you're getting the data from cache.

                             

                            Cheers

                             

                            Richard Foote

                            http://richardfoote.wordpress.com/

                            1 person found this helpful
                            • 11. Re: Reg: Analyzing a query for performance issue-
                              ranit B

                              Thanks Richard for your response. (and I am a great fan of you )

                               

                              If the RESULT_CACHE_MODE parameter is set to FORCE, then it doesn't matter about not having the hint.

                               

                              And reading a result from cache vs. performing a FTS on the table_stm table and reading 476K of rows can likely make a big difference to how this query performs.

                              Setting the parameter to FORCE, does this have any side-effects?

                               

                              Say, if the Result Cache gets flushed (somehow), then the performance (time/cost) will again get a hit right?

                               

                              Also, I have the AWR report. Can we interpret something from it?

                               

                              The Foreground Wait Class section says -

                               

                              Wait ClassWaits%Time -outsTotal Wait Time (s)Avg wait (ms)%DB time
                              Scheduler37,736,10501,045,1452860.41
                              DB CPU 271,637 15.70
                              User I/O35,964,9860215,081612.43
                              Application10,219087,65885785.07
                              Network14,044,796011,60510.67
                              Commit68,07402,050300.12
                              Concurrency756,57102,04130.12
                              Other120,1191888470.05
                              Configuration1,5101142940.01
                              System I/O36,60203310.00
                              Administrative7012050.00
                              • 12. Re: Reg: Analyzing a query for performance issue-
                                ranit B

                                Top.Gun wrote:

                                 

                                You have alot of indexes on 1 table.

                                The best one may be (col_3, led_code, baln) but you don't have this one created.

                                So try that one.

                                Thanks for the response, TopGun.

                                 

                                Again, this raises a concern on the philosophy of creating Indexes. In our DataWarehousing environment, we can have various adhoc reporting queries and so creating Indexes for each query purpose - will that be worth it?

                                 

                                Please advise. (and even Richard Foote is also here today )

                                • 13. Re: Reg: Analyzing a query for performance issue-
                                  Martin Preiss

                                  I don't think that the global AWR information will be very helpful in this situation. I would take a look at the statistics in v$sql (and its AWR counterpart DBA_HIST_SQLSTAT) and the corresponding plans (accessible via dbms_xplan.display_cursor - for plans in the library cache - or dbms_xplan.display_awr - for plans in the workload repository).

                                  • 14. Re: Reg: Analyzing a query for performance issue-
                                    ranit B

                                    Hi Martin,

                                     

                                    My original query which suffered the performance-issue actually ran for a duration of 1hour (09:35 - 10:36). And the AWR report I took is for a time slot of (08:30 - 11:30), but still I couldn't see my query in the report.

                                     

                                    Might be only the top-n costiest queries are listed and my query was not very resource-intensive but took long to complete actually waiting for some other resource or latch-concention or some other n/wk-related issue.

                                     

                                    But, my guess is - the global AWR report might be useful to understand the DB statistics at that particular time frame.

                                     

                                    Just my 2 cents. Please advise.

                                    1 2 3 4 Previous Next