Using Hints in oracle pl/sql

Posted on 2011-04-28
Medium Priority
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 74

Expert Comment

ID: 35488361
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 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 600 total points
ID: 35488432
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

ID: 35493595
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

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.


Assisted Solution

Piloute earned 600 total points
ID: 35503682
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...


Accepted Solution

ianmills2002 earned 800 total points
ID: 35783267
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

ID: 35966898
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example, show how to take different types of Oracle backups using RMAN.
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.
Suggested Courses
Course of the Month16 days, 5 hours left to enroll

850 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