1 2 Previous Next 29 Replies Latest reply: Nov 22, 2012 1:45 PM by jgarry RSS

    Performance tuning in database side

    683296
      Hi All,

      I have a query which consumes 2 hours to run. Since, it is a vendor query, am not supposed to tune the query part.
      Response time needs to be reduced. So far, i have done the below analysis.
      1) All the indexes are working fine
      2) Cost is high while sorting. Hence, tried to increase the sort_area_size in the session level. no benefit.
      3) cursor_sharing set to similar at session level. But no benefits. Default was exact.

      Anyone could help me what parameters need to be altered in v$parameter

      My DB is 9.2.0.8.0

      Thanks in advance

      Regards

      A.Gopal
        • 1. Re: Performance tuning in database side
          Osama_Mustafa
          Maybe your query need to be Tune .
          • 2. Re: Performance tuning in database side
            683296
            No..It is written by the vendor. We can not tune anything. I wanted to know what are all the parameters to be monitored are altered at the db level or at the session level to get benefits
            • 3. Re: Performance tuning in database side
              Osama_Mustafa
              Enable Trace .
              Generate AWR to see database Reading you can't just increase parameter like this .
              • 4. Re: Performance tuning in database side
                EdStevens
                A.Gopal wrote:
                No..It is written by the vendor. We can not tune anything. I wanted to know what are all the parameters to be monitored are altered at the db level or at the session level to get benefits
                Given that you can't touch the SQL you are very likely out of luck. But you need to start here: http://docs.oracle.com/cd/E11882_01/server.112/e16638/toc.htm

                There is no DATABASEFAST=TRUE parameter.
                • 5. Re: Performance tuning in database side
                  Aman....
                  A.Gopal wrote:
                  No..It is written by the vendor. We can not tune anything. I wanted to know what are all the parameters to be monitored are altered at the db level or at the session level to get benefits
                  So did you raise a ticket to the vendor asking that what you can do? There would be a parameter some time in the later future called godb=fast but at the moment it's not there :) .

                  Aman....
                  • 6. Re: Performance tuning in database side
                    JohnWatson
                    A.Gopal wrote:
                    2) Cost is high while sorting. Hence, tried to increase the sort_area_size in the session level. no benefit.
                    A.Gopal
                    Almost certainly, you are using automatic PGA management so setting the sort_area_size will have effect unless you also:
                    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.

                    Edited by: JohnWatson on Nov 21, 2012 1:15 PM
                    Typo: "effect" above should of course read "no effect"
                    • 7. Re: Performance tuning in database side
                      JohnWatson
                      Osama, AWR is bit difficult with release 9.
                      • 8. Re: Performance tuning in database side
                        Osama_Mustafa
                        Statpack in 9i ;)
                        • 9. Re: Performance tuning in database side
                          683296
                          no such parameters in v$parameter
                          • 10. Re: Performance tuning in database side
                            683296
                            There is pga_aggregate_size is set to 340 MB. Will this sort_area_size setting at session level will be useful?
                            • 11. Re: Performance tuning in database side
                              EdStevens
                              A.Gopal wrote:
                              no such parameters in v$parameter
                              And you thought there would be????

                              I was being facetious.
                              • 12. Re: Performance tuning in database side
                                Dom Brooks
                                Before you do anything, I suggest proper root cause analysis.
                                Will this sort_area_size setting at session level will be useful?
                                See template tuning threads:
                                [url https://forums.oracle.com/forums/thread.jspa?threadID=863295]How to post a SQL tuning request
                                [url https://forums.oracle.com/forums/thread.jspa?messageID=1812597]When your query takes too long

                                Only by knowing where the time is going and why can anyone suggest a course of action that is relevant.

                                Do an extended trace (event 10046) at level 12 and run the trace file through tkprof.
                                Identify waits and any significant differences between estimates and actuals.


                                If your actual execution metrics indicate that you are spending excessive time sorting or on temp space usage THEN you can consider changing sort_area_size parameters, etc.
                                All the indexes are working fine
                                What is this meant to mean?
                                If you don't know anything about your execution plan or where time is spent in the query, how can you judge the effectiveness of the indexes?

                                Edited by: Dom Brooks on Nov 21, 2012 3:02 PM
                                • 13. Re: Performance tuning in database side
                                  Max Seleznev
                                  I haven't touched 9i for a while, but if I'm not mistaken default parameter for OPTIMIZER_MODE parameter is CHOOSE. Unless that parameter was changed the stats play a very significant role in your situation.
                                  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. That along can make a huge difference. If not it will at least allow your to establish a nice baseline for future analysis.
                                  • 14. Re: Performance tuning in database side
                                    Dom Brooks
                                    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
                                    If I were you, I would do nothing until you know that the action is relevant.

                                    Just gathering stats might make things better, might make things worse, might make no difference at all.
                                    We know nothing about the stats situation.
                                    But, for sure, we know that the scope of this proposed solution is bigger than the scope of the problem - a single query.

                                    Ditto parameters and other silver bullets.

                                    Wherever possible - which is not always - the scope of the solution should match the scope of the problem.

                                    Further (i.e. some) root cause analysis required.
                                    1 2 Previous Next