This discussion is archived
1 2 Previous Next 29 Replies Latest reply: Nov 22, 2012 11:45 AM by jgarry RSS

Performance tuning in database side

683296 Newbie
Currently Being Moderated
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 Oracle ACE
    Currently Being Moderated
    Maybe your query need to be Tune .
  • 2. Re: Performance tuning in database side
    683296 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    Enable Trace .
    Generate AWR to see database Reading you can't just increase parameter like this .
  • 4. Re: Performance tuning in database side
    EdStevens Guru
    Currently Being Moderated
    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.... Oracle ACE
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    Osama, AWR is bit difficult with release 9.
  • 8. Re: Performance tuning in database side
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Statpack in 9i ;)
  • 9. Re: Performance tuning in database side
    683296 Newbie
    Currently Being Moderated
    no such parameters in v$parameter
  • 10. Re: Performance tuning in database side
    683296 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points