Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog

January 6, 2007

Database Performance and SQL Tuning Checklist

Filed under: Oracle Performance — H.Tonguç Yılmaz @ 1:50 pm

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.

SQL Tuning Checklist
1. Identify Statements to tune using:
• ADDM
• AWR
• EM Top SQL
• V$SQL_AREA
• V$SQL_TEXT
• Statspack

2. View execution statistics using:
• ADDM
• SQL*trace
• TKPROF
• TRCCESS
• DBMS_MONITOR
• Make note of
• CPU time
• Elapsed time
• Disk reads
• Disk sorts

3. Tune SQL automatically using SQL Tuning Advisor.
• Optimizer stats analysis
• SQL Profiling
• Index analysis
• SQL restructure

4. Tune SQL manually:
a. Gather information about the underlying objects used in the SQL statements
• Obtaind table, index and column definitions
• Obtain view definitions
• Understand column data distribution
o Uniqueness
o Nulls
o Skew
• Identify if data from more than one table is required by a statement resulting in joins
• Verify the join predicates to avoid cartesian joins
• Verify presence of indexes
• Verify presence of Materialized views
• Verify type of indexes
b. Verify execution plans by using:
• Explain plan
• SQL*Plus Auto trace
c. Verify statistics in:
• User_tab_columns
• User_indexes
• User_tables
• Look for :
• Last analyzed
• Existence of histograms where appropriate

5. Verify tha statistics are current:
• Use Automatic statistics gatheringset to appropriate intervals
• Backup existing statistics before gathering new ones
• Use DBMS_STATS package to gather statistics where statistics are stale or absent
• Use Dynamic sampling on volatile objects if needed

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.

8. Use hints to influence the optimizer in choosing:
• Query transformation
• Join orders
• Join methods
• Access paths

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.

Refences Used :
Oracle® Database Performance Tuning Guide 10g Release 2 (10.2)

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: