1 2 3 4 Previous Next 98 Replies Latest reply: Dec 15, 2009 1:01 AM by 695836 Go to original post RSS
      • 15. Re: Is it possible to tune this query.....
        JustinCave
        If the query really returns more than half a million rows, the index based plan is horrifically underestimating the number of rows that will be returned.

        Exactly how are you gathering statistics on the various objects? What histograms do you have? Is the data in any of the columns in any of the tables skewed?

        Justin
        • 16. Re: Is it possible to tune this query.....
          Maran Viswarayar
          EXEC DBMS_STATS.gather_table_stats('SCHEMA', 'TABLE_NAME', estimate_percent => 75);.....for all the relevant tables...
          What histograms do you have
          jsut the default settings
          Is the data in any of the columns in any of the tables skewed?
          Not sure...
          • 17. Re: Is it possible to tune this query.....
            JustinCave
            How do you gather statistics on the indexes?
            Have you disabled the default statistics gathering job? Or are you also using that?
            Have you changed any of the DBMS_STATS default parameters?
            Can you determine whether any of the data is skewed?

            Justin
            • 18. Re: Is it possible to tune this query.....
              Maran Viswarayar
              How do you gather statistics on the indexes?
              Its left to default..
              Have you changed any of the DBMS_STATS default parameters?
              nO
              Can you determine whether any of the data is skewed?
              i can check with the team...
              • 19. Re: Is it possible to tune this query.....
                Maran Viswarayar
                Have you disabled the default statistics gathering job? Or are you also using that?
                No and yes to the second question
                • 20. Re: Is it possible to tune this query.....
                  JustinCave
                  Maran Viswarayar wrote:
                  How do you gather statistics on the indexes?
                  Its left to default..
                  So you are waiting for the overnight batch job to gather statistics on the indexes? Do the query plans reflect the statistics after the overnight batch job has had a chance to gather statistics?

                  Justin
                  • 21. Re: Is it possible to tune this query.....
                    Maran Viswarayar
                    I will gather stats on the indexes now will post the plan again...
                    • 22. Re: Is it possible to tune this query.....
                      447336
                      Hi

                      Why you not using sql tuning advisor to tune this query.


                      Tinku
                      • 23. Re: Is it possible to tune this query.....
                        Maran Viswarayar
                        I tried gatheing stats on index again but the plan hasnt changed at all..but on the otherhand by if i create the indexes the cost becomes dramatically..low

                        One thing is i am creating some function based indexes becuase some of the predicates uses SUBSTR...

                        Could this be a factor which is deciding the cost of the plan...looks more like it..
                        • 24. Re: Is it possible to tune this query.....
                          683218
                          So are the cardinality estimates correct (or at least reasonable)? Your query only returns 2 rows and processes less than 1 kb of data?
                          --How can i find the processess took less than 1 kb of data?                                                                                                                                                                                                                                                                                                                                                                                                           
                          • 25. Re: Is it possible to tune this query.....
                            Maran Viswarayar
                            This is a question from Justin...so...whats you question...

                            You can get that from the BYTES coumn from the Explain Plan's first select stmt.
                            • 26. Re: Is it possible to tune this query.....
                              Maran Viswarayar
                              Thanks justin for your continuous inputs...

                              I just read this article from Chris Antongini..Ah its brilliant and this is best article i have read about interpreting Explain Plan..

                              http://antognini.ch/papers/InterpretingExecutionPlans_20091017.pdf

                              Hope this helps everyone who have not read and i am going to buy this book ...

                              I read 3 to 4 times and it became very clear on execution order of the query...
                              • 27. Re: Is it possible to tune this query.....
                                Joze Senegacnik
                                Just a short question regarding your base query which uses the function: Is this function used in the "select list" of the statement or in the "where clause"? You have edited the initial post and I could not figure out this fact. If the function is used in the where clause of the original statement, are there any other conditions in the where clause? If the function is used in "where clause" and there are some other selective predicates then it is possible to influence how many times this function will be executed. But I wouldn't like to start to discuss this possibility before I get your reply.

                                Second thing what I would like to mention is that you are using all the time the output of autotrace which also displays the execution plan, but this might be not the actual run time execution plan as it is produced by the explain plan command and not by the dbms_xplan.display_cursor which shows the actual run time plan from v$sql_plan.
                                Therefore I would suggest you to gather the actual runtime statistics by adding /*+ gather_plan_statistics */ hint into the problematic query and then you can use dbms_xplan.display_cursor to see not just the estimated but also the actual number of rows retrieved in each step of the execution plan what might give you a clue where the most of the work is performed. Then you will get the output like that one in Christian Antognini's article that you mention in your last post.

                                HTH, Joze
                                • 28. Re: Is it possible to tune this query.....
                                  Maran Viswarayar
                                  Edited

                                  Edited by: Maran Viswarayar on Nov 17, 2009 3:45 PM
                                  • 29. Re: Is it possible to tune this query.....
                                    Maran Viswarayar
                                    Execution Plan
                                    ----------------------------------------------------------
                                    Plan hash value: 3713220770
                                    
                                    ----------------------------------------------------------------------------------------------------
                                    | Id  | Operation                         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
                                    ----------------------------------------------------------------------------------------------------
                                    |   0 | SELECT STATEMENT                  |                |  8168 | 16336 |    24   (0)| 00:00:01 |
                                    |   1 |  COLLECTION ITERATOR PICKLER FETCH| DISPLAY_CURSOR |       |       |            |          |
                                    ----------------------------------------------------------------------------------------------------
                                    
                                    
                                    Statistics
                                    ----------------------------------------------------------
                                             24  recursive calls
                                              4  db block gets
                                            120  consistent gets
                                              0  physical reads
                                              0  redo size
                                            455  bytes sent via SQL*Net to client
                                            400  bytes received via SQL*Net from client
                                              2  SQL*Net roundtrips to/from client
                                              0  sorts (memory)
                                              0  sorts (disk)
                                              1  rows processed
                                    And this is what i am getting ...this is for the view which is called by the function....This plan is with the hint...
                                    1 2 3 4 Previous Next