Thursday, 5 May 2011

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 on statistics 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.

3 comments :

  1. There are mainly two ways to do performance tuning.
    1. Using Explain plan: it is an method of getting how our query will execute without actually executing the query. It is used for only sql. (but not pl/sql).
    Steps: 1. Here first Create query by following syntax
    EXPLAIN PLAN FOR
    SELECT A.ename,A.sal,nvl(A.comm,0),B.dname,B.loc
    FROM emp A ,dept B
    WHERE A.deptno=B.deptno;

    Steps: 2. DBMS_XPLAN is a inbuilt package provided by oracle we just call that package to get explain plan for that query.
    select * from table(DBMS_xplan.display());
    the output looks like
    here we has to observe two things cost and operation, if it is taken TABLA ACCESS FULL then we has to make indexes for columns mentioned in where clause. And note down the cost before and check after indexing.
    2. Sql Tracing and TKprof:
    Sql Tracing: is used to generate execution plan and time related statistics after executing query or program.
    Steps: 1. Enable Trace:
    ALTER SESSION SET SQL_TRACE = TRUE;
    Steps: 2. Run some pl sql program
    Steps: 3. Disable trace
    ALTER SESSION SET SQL_TRACE = FALSE;

    (orcl_ora_7176.trc file name has three parts orcl is database name, ora default in all trace files and 7176 is session id its keep on changing. We can not get file directly.. first we has to login as system/manager (your password). Check for USER_DUMP_DEST using command
    Sql> SHOW PARAMETER USER_DUMP_DEST;
    Then we get file source folder. We can get using latest time.

    Use TKPROF Utility:
    The trace file generated from previous step will be in non- readable format. TKPROF is a command line utility (os level) converts non-readable trace file into readable text file.
    C:\users\server> tkprof orcl_ora_7176.trc newfile.txt
    Analyze output 1.CPU & Elapsed time should be less
    2. Disk reads should be less
    3. plan should have lesser cost , path should not be full table scan.

    ReplyDelete
  2. You can go through below link for some some PL/SQL advanced concepts

    http://www.oracle-tf-world.in/p/oracle-plsql_20.html

    Your comments are most welcome...!!!

    ReplyDelete
  3. above mentioned site is not availble

    ReplyDelete