Analytics


Google

Monday, December 1, 2008

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)

No comments: