Oracle Query Tuning

If query taking long time then First will run the query in Explain Plan, The explain plan process stores data in the PLAN_TABLE.

it will give us execution plan of the query like whether the query is using the relevant indexes on the joining columns or indexes to support the query are missing.

If joining columns doesn’t have index then it will do the full table scan if it is full table scan the cost will be more then will create the indexes on the joining columns and will run the query it should give better performance and also needs to analyze the tables if analyzation happened long back. The ANALYZE statement can be used to gather statistics for a specific table, index or cluster using

ANALYZE TABLE employees COMPUTE STATISTICS;

If still have performance issue then will use HINTS, hint is nothing but a clue. We can use hints like

• ALL_ROWS
  One of the hints that 'invokes' the Cost based optimizer
  ALL_ROWS is usually used for batch processing or data warehousing systems.

(/*+ ALL_ROWS */)

• FIRST_ROWS
  One of the hints that 'invokes' the Cost based optimizer
  FIRST_ROWS is usually used for OLTP systems.

(/*+ FIRST_ROWS */)

• CHOOSE One of the hints that 'invokes' the Cost based optimizer
  This hint lets the server choose (between ALL_ROWS and FIRST_ROWS, based onstatistics gathered.
• HASH Hashes one table (full scan) and creates a hash index for that table. Then hashes other table and uses hash index to find corresponding records. Therefore not suitable for < or > join conditions.

/*+ use_hash */

Hints are most useful to optimize the query performance.