1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Please help on Performance tuning on the sql/pl/sql coding

Discussion in 'SQL PL/SQL' started by reddyharnath, Nov 12, 2012.

  1. reddyharnath

    reddyharnath Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    75
    Hello All,

    I would like to know the what is the best way to do the Performance tuning on the Sql or pl/sql queries and how to identify those things which query is taking much time and what needs to take the action to avoid for long running queries.

    Thanks
    Harinath
     
  2. reddyharnath

    reddyharnath Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    75
    Please update as soon as possible
     
  3. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260
  4. reddyharnath

    reddyharnath Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    75
    Could you please send me the clear understanding document so that it will clear my requirement
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    It's online and the link has been posted. You need to read the suggested material before you attempt to tune anything; simply providing scripts/specialized documents won't help you with your particular situation. The key to a proper tuning methodology is understanding.
     
  6. raju

    raju Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    I will upload soon
     
  7. ac.arijit

    ac.arijit Forum Advisor

    Messages:
    217
    Likes Received:
    22
    Trophy Points:
    280
    Location:
    Kolkata, India
    Hi Harinath,

    Well !! There are a lot of it. Its a big topic, which u just posted as if a GK question. The relevant doc library on this topic has been posted above.
    This is a subjective matter, you gotta study for this. Or else post something more acute and specific problem u're facing.
    However, for a short tip: SQL queries with data transitions, sorting, grouping, looping, post-processing etc usually take longer than a normal one.

    Regards
    Ari
     
  8. Hamza Bukhari

    Hamza Bukhari Active Member

    Messages:
    25
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    Pakistan
    Hi reddyharnath,

    For Better performance of My Oracle Applications, I always Use "Indexing" in My SQL scripts.
    As a a Result, My Customers Get Quick & Fast Reporting.
     
  9. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    @ Hamza,

    Index is just one of the many things you have to take care to solve the perfomance issue.
     
  10. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You've been lucky with that technique as it doesn't always work -- poor/missing statistics, old statistics, release-dependent issues with the optimizer and poor index design can all contribute to Oracle using the wrong index or not using one at all. http://dfitzjarrell.wordpress.com/2008/04/09/a-tale-of-two-indexes/ discusses why one index works and another doesn't. Also other issues, such as poor coding techniques and lack of knowledge of the data, also contribute to poorly running SQL statements. As Rajavu mentioned before I did indexes are only part of the performance equation.
     
  11. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
  12. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Which does contain errors; EXPLAIN PLAN may not produce the correct plan due to the heuristics in use to generate that plan. The most reliable plans are in V$SQL_PLAN, from an event 10046 trace or from setting autotrace on. It appears that in 11.2.0.3 explain plan does produce 'correct' plans for queries I've tested but I wouldn't extend that to all queries as there may be examples where explain plan and the trace file don't agree. Other, earlier releases are known to generate differing plans between EXPLAIN PLAN and a 10046 trace/set autotrace on output.

    I would use actual plans generated at run time to ensure an accurate evaluation of the execution path.