Using Hints in oracle pl/sql

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

Who is Participating?
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.

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?
slightwv (䄆 Netminder) Commented:
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.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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
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...

KanigiAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.