1 2 Previous Next 29 Replies Latest reply: Nov 22, 2012 1:45 PM by jgarry Go to original post RSS
      • 15. Re: Performance tuning in database side
        jgarry
        Sometimes with sql that old an additional plan of attack is to set the session to use rbo - the general idea being the correct index will be used because the old sql was written that way, so you can find out what the plan should be. This is in addition to what Dom said, which is the correct answer, as it will allow you to modify the statistics if necessary to convince the cbo to do the right thing. Which could affect other sql, for good or bad - that's why we have plan stability. See http://docs.oracle.com/cd/B10500_01/server.920/a96533/outlines.htm The idea there is to find a circumstance that gets a plan with the characteristics you want, and make the vendor supplied sql use it. So this is a way to add hints to a plan when the vendor won't let you. Of course, the vendor should have done this, so maybe you could ask them why they didn't, years ago.
        • 16. Re: Performance tuning in database side
          Max Seleznev
          At this moment we can safely assume that the problem is one isolated query and not the whole database. If it's the latter the whole thread is irrelevant. So I assume the database as a whole is pretty much OK.
          The first step of tuning an individual query is generating its execution plan or better yet a trace file. With the default optimizer mode stats play a vital role in building an optimal execution plan that in turn provides a basis for further analysis. Do we have to build an index or maybe drop one? Force FTS by adjusting index costs? Force parallel execution by modifying table/index properties. To correct optimizer behavior we need to know where it's wrong (if it is). Stats is a basis of optimizer behavior and must be collected (with preserving old ones first). Once an optimal plan is determined there's a number of ways to force it for the query that cannot be modified including views with hints, stored outlines etc.

          Thanks for your opinion, though.
          • 17. Re: Performance tuning in database side
            Nikolay Savvinov
            Hi Max,

            if you gather stats, you may or may not get a better plan -- but in either case, how will you establish the root cause of the issue? Was it a bind peeking issue? Non-representative stats? Stale stats? Maybe a histogram wasn't gathered where it should've? Or maybe the other way round, it was gathered where not appropriate?

            And without knowing the root cause, how can you know if the problem won't happen again? Gathering stats can destroy the only evidence you have to resolve the issue. Depending on your settings, you may be able to restore the stats to investigate, but then again, what was the point of gathering it in the first place?

            Best regards,
            Nikolay
            • 18. Re: Performance tuning in database side
              Dom Brooks
              The first step of tuning an individual query is generating its execution plan or better yet a trace file
              Great.
              If you had said that I couldn't have disagreed.

              However that wasn't the first thing you were going to do earlier:
              If I were you the first thing I would do is to collect fresh stats on all the objects that the statements tries to access during its execution.
              • 19. Re: Performance tuning in database side
                Max Seleznev
                The point is to get some progress and a baseline. The question is pretty much how to approach tuning a single query. If it were later version of Oracle we would have more tools on our belt, but it's not. It also goes without saying that nothing's done in the production database that cannot be rolled back (dbms_stats.import/export is our friend).

                With Oracle optimizer (or any other optimizer for that matter) not being ideal it's still does pretty good job of building a decent plan in the majority of the situations. In order to have that plan we need stats. Now when we have it we can read it an compare to actual data distribution, index selectivity etc (assuming we're still not getting desired performance). This might give us an idea if Oracle makes an incorrect assumption of binds values, value distribution for the purpose of range scan or anything else.
                I'm not saying that it's not possible to analyze and tune the query without generating "close to the ideal from the database standpoint" execution plan, but we're losing a valuable tool and the one of only few we have. The plan generated without the relevant stats is useless for the analysis since it's built on incorrect assumptions. It's better to use RBO as was suggested above than to try working with stale plan.

                Anyway, happy TG to everyone!
                • 20. Re: Performance tuning in database side
                  Max Seleznev
                  Agreed. Stand corrected.
                  • 21. Re: Performance tuning in database side
                    jgarry
                    Max Seleznev wrote:
                    The point is to get some progress and a baseline. The question is pretty much how to approach tuning a single query. If it were later version of Oracle we would have more tools on our belt, but it's not. It also goes without saying that nothing's done in the production database that cannot be rolled back (dbms_stats.import/export is our friend).
                    Given the original post, it doesn't go without saying. I'd say that is a problem in a great percentage of answers that say "gather statistics" with no qualifications.

                    >
                    With Oracle optimizer (or any other optimizer for that matter) not being ideal it's still does pretty good job of building a decent plan in the majority of the situations. In order to have that plan we need stats. Now when we have it we can read it an compare to actual data distribution, index selectivity etc (assuming we're still not getting desired performance). This might give us an idea if Oracle makes an incorrect assumption of binds values, value distribution for the purpose of range scan or anything else.
                    I'm not saying that it's not possible to analyze and tune the query without generating "close to the ideal from the database standpoint" execution plan, but we're losing a valuable tool and the one of only few we have. The plan generated without the relevant stats is useless for the analysis since it's built on incorrect assumptions. It's better to use RBO as was suggested above than to try working with stale plan.
                    I guess you've never seen the many posts here and elsewhere that ask about "my query was fast yesterday and it's slow today." How many of those are due to new statistics? How many are bind variable peeking? The bad plan needs to be generated anyways to see what might be better, and to compare to a good plan when that can be generated. Simply removing all stats could be relevant too, eh? And at some point we get to the conversations about uneven distributions and histograms.

                    >
                    Anyway, happy TG to everyone!
                    Ditto! Even to those in the world who don't have Lincoln to thank for it.
                    • 22. Re: Performance tuning in database side
                      683296
                      stats already collected.
                      No improvements
                      • 23. Re: Performance tuning in database side
                        683296
                        please find the plan herewith..In the plan, you can see the sorting takes bigger cost...Ofcourse, there is a cartesian product, which consumes more cost..But, since, it is a vendor query, i can't touch the query for tuning

                        PLAN_TABLE_OUTPUT
                        -----------------------------------------------------------------------------------------

                        ---------------------------------------------------------------------------------------
                        | Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
                        ---------------------------------------------------------------------------------------
                        | 0 | SELECT STATEMENT | | 3540K| 425M| | 3295G|
                        | 1 | SORT GROUP BY | | 3540K| 425M| 10252| 3295G|
                        | 2 | NESTED LOOPS | | 84545| 94615| | 3282G|
                        |* 3 | HASH JOIN SEMI | | 21482 | 2265K| 4408K| 160 |
                        | 4 | TABLE ACCESS FULL | DFUMAP | 42964 | 3902K| | 62 |
                        | 5 | VIEW | VW_NSO_1 | 40272 | 589K| | 12 |
                        | 6 | NESTED LOOPS | | 40272 | 1179K| | 12 |
                        |* 7 | INDEX UNIQUE SCAN | MAP_PK | 1 | 15 | | |
                        |* 8 | INDEX FAST FULL SCAN | XIF3DFUMAP | 40272 | 589K| | 11 |
                        |* 9 | VIEW | | 3935T| 62P| | 152M|
                        | 10 | SORT GROUP BY | | 526P| 8410P| | 152M|
                        | 11 | MERGE JOIN | | 526P| 8410P| | 152M|
                        | 12 | SORT JOIN | | 3243M| 27G| 97G| 76M|
                        | 13 | VIEW | | 3243M| 27G| | 59M|
                        | 14 | SORT UNIQUE | | 3243M| 160G| 363G| 59M|
                        | 15 | UNION-ALL | | | | | |
                        | 16 | MERGE JOIN CARTESIAN | | 1621M| 80G| | 443K|
                        | 17 | NESTED LOOPS | | 40272 | 1494K| | 18 |
                        |* 18 | INDEX UNIQUE SCAN | MAP_PK | 1 | 15 | | |
                        |* 19 | INDEX FAST FULL SCAN| DFUMAP1 | 40272 | 904K| | 17 |
                        | 20 | BUFFER SORT | | 40272 | 589K| | 442K|
                        |* 21 | INDEX FAST FULL SCAN| XIF3DFUMAP | 40272 | 589K| | 11 |
                        | 22 | MERGE JOIN CARTESIAN | | 1621M| 80G| | 443K|
                        | 23 | NESTED LOOPS | | 40272 | 1494K| | 18 |
                        |* 24 | INDEX UNIQUE SCAN | MAP_PK | 1 | 15 | | |
                        |* 25 | INDEX FAST FULL SCAN| DFUMAP1 | 40272 | 904K| | 17 |
                        | 26 | BUFFER SORT | | 40272 | 589K| | 442K|
                        |* 27 | INDEX FAST FULL SCAN| XIF3DFUMAP | 40272 | 589K| | 11 |
                        |* 28 | SORT JOIN | | 3243M| 27G| 97G| 76M|
                        | 29 | VIEW | | 3243M| 27G| | 59M|
                        | 30 | SORT UNIQUE | | 3243M| 160G| 363G| 59M|
                        | 31 | UNION-ALL | | | | | |
                        | 32 | MERGE JOIN CARTESIAN | | 1621M| 80G| | 443K|
                        | 33 | NESTED LOOPS | | 40272 | 1494K| | 18 |
                        |* 34 | INDEX UNIQUE SCAN | MAP_PK | 1 | 15 | | |
                        |* 35 | INDEX FAST FULL SCAN| DFUMAP1 | 40272 | 904K| | 17 |
                        | 36 | BUFFER SORT | | 40272 | 589K| | 442K|
                        |* 37 | INDEX FAST FULL SCAN| XIF3DFUMAP | 40272 | 589K| | 11 |
                        | 38 | MERGE JOIN CARTESIAN | | 1621M| 80G| | 443K|
                        | 39 | NESTED LOOPS | | 40272 | 1494K| | 18 |
                        |* 40 | INDEX UNIQUE SCAN | MAP_PK | 1 | 15 | | |
                        |* 41 | INDEX FAST FULL SCAN| DFUMAP1 | 40272 | 904K| | 17 |
                        | 42 | BUFFER SORT | | 40272 | 589K| | 442K|
                        |* 43 | INDEX FAST FULL SCAN| XIF3DFUMAP | 40272 | 589K| | 11 |
                        • 24. Re: Performance tuning in database side
                          683296
                          alter session set workarea_size_policy=manual
                          then give your session a lot of memory, perhaps
                          alter session set sort_area_size=1000000000;
                          alter session set hash_area_size=1000000000;
                          but of course this complete guess work without any analysis of your problem.

                          Tried the above..But, 'end of communication channel' error came
                          • 25. Re: Performance tuning in database side
                            Nikolay Savvinov
                            Hi,

                            1) according to the plan posted, the query should take a couple of years to run, not couple of hours
                            2) the optimizer claims that the query returns 425M rows -- is this in the right ballpark? if yes, what do you need this many rows for? who could possibly be able to find anything useful in half a billion rows?
                            3) you haven't posted the predicate section of the plan
                            4) more importantly, you haven't posted the text of the query
                            5) can you trace the query? if not, can you use other indirect methods to see where it's spending most time -- e.g. V$SESSION_LONGOPS or using p1 and p2 for I/O-related waits to find which object is being read/written to? it's nearly impossible to tune a query on an unfamiliar database with only explain plan and query text (and we don't even have the latter)
                            6) the query looks quite complex -- looks like a join of several views. it would make sense to use "divide-and-conquer" approach, i.e. chose a few views or subqueries that show high cost and take a close look at them

                            Best regards,
                            Nikolay
                            • 26. Re: Performance tuning in database side
                              683296
                              Hi Nikolay,

                              Thanks. For the updates. Yes, if we modify the query, it would run faster. But, the objective is, not to touch the query as it is vendor specific code.
                              I checked in V$SESSION_LONGOPS and has join takes the majot 'TOTAL WORK'.
                              Hence, kindly let me know whether any v$parameter side changes is required to get a betterment for this issue.

                              Thanks & Regards

                              A.Gopal
                              • 27. Re: Performance tuning in database side
                                Nikolay Savvinov
                                Hi
                                A.Gopal wrote:
                                Yes, if we modify the query, it would run faster.
                                I never suggested that you modify the query. I know this is vendor code. My suggestion was to understand the root cause of the problem before doing anything (one way to do that is to break query into smaller pieces and look at them separately -- not as a solution, but as means to obtain diagnostic information). And so far you have posted very little information for a proper root cause analysis.
                                But, the objective is, not to touch the query as it is vendor specific code.
                                I checked in V$SESSION_LONGOPS and has join takes the majot 'TOTAL WORK'.
                                Hence, kindly let me know whether any v$parameter side changes is required to get a betterment for this issue.
                                ALTER DATABASE SET FAST = TRUE

                                Best regards,
                                Nikolay
                                • 28. Re: Performance tuning in database side
                                  Dom Brooks
                                  This is an estimate, not actual metrics.
                                  At some point, you should trace it.

                                  When posting plans etc use the code tags to preserve formatting and indentation.


                                  But, I'm curious about this repeated pattern in the SQL:
                                  32  MERGE JOIN CARTESIAN    1621M 80G   443K 
                                  33  NESTED LOOPS    40272  1494K   18  
                                  * 34  INDEX UNIQUE SCAN  MAP_PK  1  15      
                                  * 35  INDEX FAST FULL SCAN DFUMAP1  40272  904K   17  
                                  36  BUFFER SORT    40272  589K   442K 
                                  * 37  INDEX FAST FULL SCAN XIF3DFUMAP  40272  589K   11  
                                  Can you show us the SQL, a nicely formatted execution plan PLUS the predicates section please?
                                  • 29. Re: Performance tuning in database side
                                    jgarry
                                    When people tell you to format your posts, they mean to surround code or output with
                                     tags, then use the preview tab to see that it looks right.  The thread about "how to make a sql tuning request" also tells you about how to post predicate information.
                                    
                                    Others are guiding you on this, so I'll just make a couple of observations:
                                    
                                    In my experience, those VW_NSO views are Oracle building views on the fly, which often result in severe cpu usage.  This may or may not be a good thing as compared to i/o, but for my vendor-generated code, it's a bad sign for my situation.  You might want to google Jonathan Lewis push predicate, to understand one of the issues you may see when following Nikolay's advice to break down the query into its component parts.  It an oversimplified nutshell, the optimizer may not see ways to order the operations to exclude data early.  For one situation, the answer was to tell the users "don't do that there" (it was just a list of values available for an update, they could look at another report to get the values).  For another, it meant a project that went on for a couple of years, essentially creating a DW on the fly - in fact, that was a performance problem I was called in to fix, eventually deemed important enough to replace the vendor solution, which simply didn't scale, and the customer didn't want a data warehouse.  If I had the same problem now, I might simply say "suck it up, you need a reporting instance or database."  Just the fact you are on such an old version says something about your management situation.  Kind of like eating two week old leftover turkey with green fuzz on it.
                                    
                                    Jonathan also has blog entries explaining about buffer sort, well worth the mental effort.
                                    
                                    As far as the end of file on device or whatever that error was when trying manual work areas, that could be simply running into OS configuration limitations.  Please let us know your OS and hardware, it could be a simple kernel configuration issue, or you could just need to use smaller values in the set commands.  Remember to copy and paste errors and output, important details can be lost in translation.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
                                    1 2 Previous Next