Using Hints in oracle pl/sql

Posted on 2011-04-28
Last Modified: 2013-12-07
Hi Experts,

so currently / i am working on tuning of oracle pl/sql using hints .
In many of my pl/sql statements full table scan is happening
For avoiding the full table scan i tried to use index hint on particular table but stil it is using full table scan.I was unable to understand how the index hint works.a;lso can you pls provide some inputs for getting into the use of hints which will be helpful in optimising the performance of pl/sql query

Question by:Kanigi
    LVL 73

    Expert Comment

    Hint's only apply to SQL,  not to PL/SQL.

    Are you sure you want to do an index scan?  They are not automatically better than a full table scan.  Frequently worse.

    What is your query and explain plan currently?

    Also, are your statistics up to date?
    LVL 76

    Assisted Solution

    by:slightwv (䄆 Netminder)
    To add: hints are just that, a hint.  Normally Oracle will obey but at times, it has the option to ignore them.

    Also, only in rare instances will hints actually increase performance.  I encourage people to not use them.
    LVL 14

    Expert Comment

    Agree with sdstuber and slightwv

    More questions to your question :) so that we can assit you better

    How many records exist in the table and how many are you trying to fetch?

    Can you post the query?

    It will be helpful if you can post the explain plan
    LVL 7

    Assisted Solution

    Hi Kanigi,

    Here's also my grain of sand...

    - have to be used as a last resort 'workaround'.
    - are 'only' hints and not orders, as indicated by slightwv above.
    - mean the DBA is smarter than the CBO ! (I have always looked for someone who would be able to demonstrate that...)

    You really need to figure out why you index is not used... There IS a reason.

    Go for a hint only if you have no alternative and the businees is stuck because of your db...

    LVL 6

    Accepted Solution

    As mentioned above, they are just hints and the DB can ignore them.

    You should try looking at other ways to speed up the SQL the PL/SQL processes.

    1. Add more indexes to the tables if possible
    2. I have gotten significant performance improvement using a sub-select instead of a table join
    3. Check the WHERE clause to ensure that the table joins have no datatype convertions (implicit or explicit)
    4. remove functions on columns when joining tables.
    ...and so on.

    A sample of a faulty would come in handy to possibly identify any obvious tuning issues.

    In general, I have found that each query that needs tuning requires its own specific tuning requirements. There is no one solution to fix all efficiency problems.


    Author Closing Comment

    Sorry for getting the late response,so i withdrawn my idea of adding hints tried to add indexes on the tables so the now problem got resolved all your inputs are very much helpful to me

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
    Introduction A previously published article on Experts Exchange ("Joins in Oracle", makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
    This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
    This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now