January 6, 2007

Database Performance and SQL Tuning Checklist

Database Performance Checklist
• Set the minimal number of initialization parameters. Ideally, most initialization parameters should be left at default. If there is more tuning to perform, this shows up when the system is under load. Set storage options for tables and indexes in appropriate tablespaces.
• Verify that all SQL statements are optimal and understand their resource usage.
• Validate that middleware and programs that connect to the database are efficient in their connection management and do not log on and log off repeatedly.
• Validate that the SQL statements use cursors efficiently. Each SQL statement should be parsed once and then executed multiple times. The most common reason this does not happen is because bind variables are not used properly and WHERE clause predicates are sent as string literals.
• Validate that all schema objects have been correctly migrated from the development environment to the production database. This includes tables, indexes, sequences, triggers, packages, procedures, functions, Java objects, synonyms, grants, and views. Ensure that any modifications made in testing are made to the production system.
• As soon as the system is rolled out, establish a baseline set of statistics from the database and operating system. This first set of statistics validates or corrects any assumptions made in the design and rollout process.

6. Change access paths.
• Use SQL Access Advisor
• Use SQL Tuning Advisor
• Create B*tree indexes on highly selective data.
• Create bitmap indexes on low cardinality columns.
• Bitmap indexes help in queries using OR or aggregates.
• Create bitmap join indexes to facilitate joins.
• Create concatenated indexes to facilitate full index scans.
• Create histograms on skewed data.
• Create materialized views on queries involving joins and aggregates.
• Keep in mind that :
• Full table scans on small tables or queries retrieving a large percentage of rows are OK
• A full index scan may be faster than a full table scan
• An index skip scan may be faster to a full index scan
• An index access by rowid may be faster to an index range scan
• Look for distinct or GROUP By as this may indicate a mising predicate

7. Restructure queries keeping the following in mind:
• Use SQL Tuning advisor.
• Inequality conditions cannot use indexes.
• Distinct causes sorts.
• Group by causes sorts.
• Aggregates can use indexes.
• Applying functions on indexed columns prevents the index from being used.
• Low selectivity queries do not use indexes.
• Use UNION ALL instead of UNION (wherever possible).
• Nesting queries too deeply causes poor performance.
• Use EXISTS instead of IN for subqueries to check for TRUE or FALSE values(wherever possible).
• Use NOT EXISTS instead of NOT IN whenever possible.
• Implicit or explicit conversions may cause an index not to be used.
• OR and IN lists conditions are not performance efficient.
• If possible = or AND conditions are preferable.

9. Verify the new code improves performance
• From a user perspective such response time, timre taken to run a report etc.
• Check that the execution statistics (step 2) reflect the performance gain from the changes you have made in CPU time, elapsed time etc. from a resource uage perspective.