1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Tips for improving Oracle Database Performance

Discussion in 'Server Administration and Options' started by Arju, Oct 24, 2008.

  1. Arju

    Arju Forum Expert

    Likes Received:
    Trophy Points:
    Tips for Improving Oracle Database Performance

    1. Make statistics up-to-date. This is use automatic statistics gathering or periodically gather statistics. Automatic statistics gathering is done if STATISTICS_LEVEL initialization parameter is set to TYPICAL or you can gather manual statistics using ANALYZE command or using DBMS_STATS package.

    2. Consider to create index that is used in WHERE clause or used in JOIN. If multiple columns are used in WHERE clause then you can also consider COMPOSITE INDEX.

    3. Don't create any bitmap index on your OLTP server.

    4. Consider to use index where quries run most frequenly.

    5. Never use hints in queries.

    6. Try to use bind variable within queries.

    7. Consider not to do queries in a loop.

    8. Make sure that your WHERE clauses that compare to numbers are really NUMBER data types. If they are VARCHAR2 data types, Oracle will cast the column to a number and stop index usage.

    9. Avoid to use function with the column that is used with WHERE clause. This avoid of using index unless you have function based index.

    10. Use COMPRESS attribute while creating table if you do mostly queries on the table.

    11. Never join with complex views.

    12. Consider to use index organized table if applicable.

    13. Consider to create partition.

    14. Consider to create materialized view.