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!

Sql quries and sql tuning

Discussion in 'SQL PL/SQL' started by laxman, Oct 26, 2010.

  1. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    Hi all,
    once again need your suggestion/help to improve my skills specially in writing good sql queries and sql tuning.i may expect your good advice that what type of appraoch should be there so that i can improve consistently.frankly speaking i am facing lot of difficulties in real time scenario.

    Thanks n regards
    Laxman
     
  2. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    Hi Laxman. That's an open ended question. You should be more specific as to with what code you are facing problems...

    And Congrats on the Genius Tag! ;)
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    As Sadik mentioned without seeing examples of your troublesome code and execution plans those queries generate it's nearly impossible to give out advice.

    Please provide examples and execution plans.
     
  4. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    Thanks Sadik and also i would like to thanks club-oracle for designated me with the genius tag.Well sadik what i wanted to get clarify is that irrespective of any kind of query should we need to have any common plan ready to improve the performance of the sql query.as you said without knowing the execution plan it is hard to predict about tuning the sql queru.let say i have a query in pl/sql code mentioned below and customer has mandatary requirement not to filter any row i.e without using where clause.In this case query will take lot of time to execute.some of my team member has with opinion like this query need to pinned in memory or we can use bind variable but i am not sure ,so i wanted to know that without disturbing the business logic can i able to tune it perfectly and here n no of approaches will come into our mind which may lead to confusion.
    Ultimately as a developer can we have better plan ready in our hand to fine tune those troublesome queries which effecting the performance.

    Code (SQL):
    BEGIN
    SELECT * FROM emp;
    END;
    Thanks n regards
    Laxman
     
  5. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    I agree to your point sir,Well what i wanted to get clarify is that irrespective of any kind of query should we need to have any common plan ready to improve the performance of the sql query.as you said without knowing the execution plan it is hard to predict about tuning the sql query.let say i have a query in pl/sql code mentioned below and customer has mandatary requirement not to filter any row i.e without using where clause.In this case query will take lot of time to execute.some of my team member has with opinion like this query need to pinned in memory or we can use bind variable but i am not sure ,so i wanted to know that without disturbing the business logic can i able to tune it perfectly and here n no of approaches will come into our mind which may lead to confusion.
    Ultimately as a developer can we have better plan ready in our hand to fine tune those troublesome queries which effecting the performance.

    Code (SQL):
    BEGIN
    SELECT * FROM emp;
    END;
    Thanks n regards
    Laxman
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    There is nothing to 'tune' with the query you posted. Such queries will always reequre a full table scan so without filtering any data tuning is a simple task. Posibly parallel query could speed things up; possibly it won't. Know that a select such as you've posted cannot be tuned as there is no possible way any index could be used, no possible way partitioning would help.
     
  7. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    Right Sir,i think i need to drill down the basics then only i can able to find a right appraoch to tune the sql query. Thanks a lot Sir.


    Regards
    Laxman