Oracle instance tuning techniques

Alexey Danchenkov and Donald Burleson
This is an excerpt from the best-selling book Oracle Tuning: The Definitive Reference by Alexey Danchenkov and Donald Burleson, technical editor Mladen Gogala. Click here to read

    Requires Free Membership to View

the full chapter.

Oracle professionals know that you must optimize your database by tuning global parameters before detailed application tuning can proceed. This excerpt reviews proven techniques for tuning any Oracle instance and has scripts to ensure that your database is optimized for its application load.

Viewing table and index access with AWR

One of the problems in Oracle9i was the single bit-flag that was used to monitor index usage. The flag can be set with the alter index xxx monitoring usage command, and see if the index was accessed by querying the v$object_usage view.

The goal of any index access is to use the most selective index for a query. This would be the one that produces the smallest number of rows. The Oracle data dictionary is usually quite good at this, but it is up to the DBA to define the index. Missing function-based indexes are a common source of suboptimal SQL execution because Oracle will not use an indexed column unless the WHERE clause matches the index column exactly.

The WISE tool is a great way to quickly plot Oracle time series data and gather signatures for Oracle metrics. WISE is also able to plot performance data on a daily or monthly average basis. See the WISE Web site for details.

Tracking SQL nested loop joins

As a review, nested loop joins are the most common method for Oracle to match rows in multiple tables. Nested loop joins always invoke an index and they are never parallelized. The following awr_nested_join_alert.sql script to count nested loop joins per hour:

col c1 heading 'Date'                                format a20
col c2 heading 'Nested|Loops|Count'   format 99,999,999
col c3 heading 'Rows|Processed'         format 99,999,999
col c4 heading 'Disk|Reads'                   format 99,999,999
col c5 heading 'CPU|Time'                     format 99,999,999

accept nested_thr char prompt 'Enter Nested Join Threshold: '

ttitle 'Nested Join Threshold|&nested_thr'

   to_char(sn.begin_interval_time,'yy-mm-dd hh24') c1,
   count(*) c2,
   sum(st.rows_processed_delta) c3,
   sum(st.disk_reads_delta) c4,
   sum(st.cpu_time_delta) c5
   dba_hist_snapshot sn,
   dba_hist_sql_plan p,
   dba_hist_sqlstat st
   st.sql_id = p.sql_id
   sn.snap_id = st.snap_id
   p.operation = 'NESTED LOOPS'
   count(*) > &hash_thr
group by

The output below shows the number of total nested loop joins during the snapshot period along with a count of the rows processed and the associated disk I/O. This report is useful where the DBA wants to know if increasing pga_aggregate_target will improve performance.

In the report above, nested loops are favored by SQL that returns a small number of rows_processed than hash joins, which tend to return largest result sets.

The following awr_sql_index.sql script exposes the cumulative usage of database indexes:

col c0 heading 'Begin|Interval|time'  format a8
col c1 heading 'Index|Name'              format a20
col c2 heading 'Disk|Reads'              format 99,999,999
col c3 heading 'Rows|Processed'    format 99,999,999
   to_char(s.begin_interval_time,'mm-dd hh24') c0,
   p.object_name c1,
   sum(t.disk_reads_total) c2,
   sum(t.rows_processed_total) c3
     dba_hist_sql_plan p,
     dba_hist_sqlstat t,
     dba_hist_snapshot s
     p.sql_id = t.sql_id
     t.snap_id = s.snap_id
     p.object_type like '%INDEX%'
group by
     to_char(s.begin_interval_time,'mm-dd hh24'),
order by
     c0,c1,c2 desc

The following is a sample of the output where the stress on every important index is shown over time. This information is important for placing index blocks into the KEEP pool to reduce disk reads and for determining the optimal setting for the important optimizer_index_caching parameter.

The above report shows the highest impact tables.

Click here to read the rest of this chapter.

This was first published in March 2007

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.