SQL Performance Tuning with reference to Oracle

SQL Performance Tuning with reference to Oracle

Developed by Hardeep Singh
Copyright	© Hardeep Singh, 2009
EMail	seeingwithc@hotmail.com
Website	mm.2cent.me
Released under Creative Commons Attribution Non-commercial No Derivative Works license.
The code may not be used commercially without permission.
The code does not come with any warranties, explicit or implied.
The code cannot be distributed without this header.

Problem: We are required to tune a SQL query written for Oracle.

Further reading:

Creating indexes:

CREATE INDEX book_author_index ON book (book_id, author_id);

Generating plan:

(Using SQL Plus)

set autotrace on;

To see the plan without executing the query:

set autotrace traceonly explain;

(Using SQL Developer)

On the query press F6.

Explain plan interpretation:

expain plan

'Table access full' means that the complete table needs to be searched in order to find the information. ‘Index range scan’ means that index will be used, however the filters provided in the WHERE clause may not be ‘complete’.

'Index full scan' means the entire index will be searched.

'Table access by index rowid' means the rows are being located using an index.

Hash, Nested loops and Merge are all different types of joins.

These are the most commonly seen operations in an explain plan.

Statistics:

This is how you can generate statistics on a table called BOOK owned by SYSADM:

EXEC DBMS_STATS.gather_table_stats('SYSADM', 'BOOK');

Hints:

This is how the leading hint is applied:

SELECT /*+ LEADING(b) */ author_name FROM author a, book b WHERE a.author_id=b.author_id AND reference_book='Y';

Further references:

Indexes: