Further reading: SQL performance tuning

Watch the video tutorial here if you haven’t already.

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:

http://lc.leidenuniv.nl/awcourse/oracle/server.920/a96533/ex_plan.htm

http://www.orafaq.com/node/1420

Statistics:

http://www.oradev.com/create_statistics.jsp

Hints:

http://www.adp-gmbh.ch/ora/sql/hints/index.html

http://tinyurl.com/5ck3qm

Share

5 thoughts on “Further reading: SQL performance tuning”

  1. Hello Hardeep, This khalid from Hyderabad,India,just I have found your blog,its quite amazing… keep it up… Thnaks a lot for keeping good info.

  2. Hi Hardeep,

    Greetings to you!.

    I think i have heared about you through one of my friend, though not sure its the same person :-).. Hope you are…!!
    Here is my concerns..Was just curious to go through the vedio link regarding performance tuning, but i see am not able to access it. It just shows up a blank page when i click on the link. Anyways to help me out to get the access ?

    Thank you.

    Sreeraj

Leave a Reply to Khalid Cancel reply

Your email address will not be published. Required fields are marked *


Licensing and information about the blog available here.