Oracle Hints

Every so often, when you have an application which is working fine and suddenly the applications slows down for no apparent reason. I have seen this on many occasions and found that majority of the time is somehow Oracle decided the fastest way to perform my sql query is to do a full table scan on some huge tables because the database has many extends.

In those situations, I use hints to "advice" Oracle to use the index for example:

select /*+ index(e iempmaster) */ empno, title, extension from empmaster e where empno = 'XXX123';

Note the the hints starts with /*+, it needs a space immediately after that prior to the hint. Index is not the only options there are many other hints such as hash etc. You can find reference to the rest in the following sites:

http://www.psoug.org/reference/hints.html
http://www.dba-oracle.com/t_sql_hints_tuning.htm

To test if your hint is working or not, perform an explain plan without the hint and run the explain plan with the hint. You should see the difference. You can perform explain plan from the sqlplus (dos prompt) or use SQL Developer, there is a button for execute Explain plan. However, to do that you need to have a table called plan_table with the following structure:

Column Name	Data type
STATEMENT_ID	VARCHAR2(30 BYTE)
TIMESTAMP	DATE
REMARKS	VARCHAR2(80 BYTE)
OPERATION	VARCHAR2(30 BYTE)
OPTIONS	VARCHAR2(30 BYTE)
OBJECT_NODE	VARCHAR2(128 BYTE)
OBJECT_OWNER	VARCHAR2(30 BYTE)
OBJECT_NAME	VARCHAR2(30 BYTE)
OBJECT_INSTANCE	NUMBER
OBJECT_TYPE	VARCHAR2(30 BYTE)
OPTIMIZER	VARCHAR2(255 BYTE)
SEARCH_COLUMNS	NUMBER
ID	NUMBER
PARENT_ID	NUMBER
POSITION	NUMBER
COST	NUMBER
CARDINALITY	NUMBER
BYTES	NUMBER
OTHER_TAG	VARCHAR2(255 BYTE)
PARTITION_START	VARCHAR2(255 BYTE)
PARTITION_STOP	VARCHAR2(255 BYTE)
PARTITION_ID	NUMBER
OTHER	LONG
DISTRIBUTION	VARCHAR2(30 BYTE)