ORACLE Performance Tuning

Application --> Database Server --> Memory --> Hard Disk

Check item:

1. Application code

2. SQL code

3. Contention

4. Memory

5. Disk IO

Check Steps:

1. Tune SQL and application code to reduce the logical demand 

    A. Choose the best data model

    B. Reduce the load on the database

    C. Tune the SQL statements

2. Eliminate contention points and bottlenecks

    A. Avoid contention for locks and latches

3. Configure Memory to avoid as much IO as possible

4. Tune disk IO system to reduce waits for IO

Tuning application:

1. Design your tables and indexes

    A. Third Normal Form (3NF): "The key, the whole key and nothing bu the key"

    B. Primary key: use artificial key instead of natural key

    C. Use as less tables as possible

    D. Null has significant performance implications:

        a. NULLs don't take any space

        b. NULLs can't be indexed

    E. Index is used to find one or few things, slow down the DML

    F. Concatenated index can be used when you query together; and you query the leading column;        

        Example: create index myindex on mytable(lastname, firstname, dateofbirth)

        when you search for (lastname, firstname, dateofbirth), it will use the index

        when you search for lastname, it will use the index

        when you search for firstname, it will not use the index

    G. Normaly create 3-4 index on one table

2. Tune your appilcation code

    A. Don't call the database unless you have to - cache data instead.

    B. Reduce "hard" parsing using bind variables 

    C. Minimize lock duration using optimisitc instead of perssimistic locking strategies

    D. Reduce Network Traffic 

        a. array processing - 

            i.fetch: get more records per time instead of one per time

            ii. insert: insert more records per time instead of one per time

        b. store procedure: using procedure instead of jave or other program

3. Tune your SQL

    A. Help optimizer: Explain plan / Query plan

    B. Indexes are only good for getting small amounts of the table: using histogram

    C. Find bad sql and fix it

        a. update statistics and histogram

        b. add indexes

        c. use hints

        d. force the plan

    E. Using Hints

Contention    

1. locks: protect table row

2. latch: protect memory buffers