Oracle SQL Tuning Goals

Oracle SQL tuning goals

Oracle SQL tuning is a phenomenally complex subject. Entire books have been written about the nuances of Oracle SQL tuning; however, there are some general guidelines that every Oracle DBA follows in order to improve the performance of their systems.  Again, see the book “Oracle Tuning: The Definitive Reference“, for complete details.

The goals of SQL tuning focus on improving the execution plan to fetch the rows with the smallest number of database “touches” (LIO buffer gets and PIO physical reads). 

  • Remove unnecessary large-table full-table scans—Unnecessary full-table scans cause a huge amount of unnecessary I/O and can drag-down an entire database. The tuning expert first evaluates the SQL based on the number of rows returned by the query. The most common tuning remedy for unnecessary full-table scans is adding indexes. Standard b-tree indexes can be added to tables, and bitmapped and function-based indexes can also eliminate full-table scans. In some cases, an unnecessary full-table scan can be forced to use an index by adding an index hint to the SQL statement.
     
  • Cache small-table full-table scans—In cases where a full-table scan is the fastest access method, the administrator should ensure that a dedicated data buffer is available for the rows.  In Oracle8 and beyond, a small table can be cached by forcing it into the KEEP pool.
     
  • Verify optimal index usage—Oracle sometimes has a choice of indexes, and the tuning professional must examine each index and ensure that Oracle is using the proper index.
     
  • Materialize your aggregations and summaries for static tables – One features of the Oracle 10g SQLAccess advisor is recommendations for new indexes and suggestions for materialized views.  Materialized views pre-join tables and pre-summarize data, a real silver bullet for data mart reporting databases where the data is only updated daily. Again, see the book “Oracle Tuning: The Definitive Reference“, for complete details on SQL tuning with materialized views.

These are the goals of SQL tuning in a nutshell. However, they are deceptively simple, and to effectively meet them, we need to have a through understanding of the internals of Oracle SQL. Let’s begin with an overview of the Oracle SQL optimizers.

Advertisements

About Oracle Technology Blogs
Oracle Application Development

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

%d bloggers like this: