Oracle professionals know that you must optimize your database by tuning global parameters before detailed application tuning can proceed. This excerpt from the bestselling book Oracle tuning: The definitive reference by Alexey Danchenkov and Donald Burleson reviews proven techniques for tuning any Oracle instance and has scripts to ensure that your database is optimized for its application load. Click here to read the full chapter.
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 http://www.wise-oracle.com
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'
select
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
from
dba_hist_snapshot sn,
dba_hist_sql_plan p,
dba_hist_sqlstat st
where
st.sql_id = p.sql_id
and
sn.snap_id = st.snap_id
and
p.operation = 'NESTED LOOPS'
having
count(*) > &hash_thr
group by
begin_interval_time;
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.
Nested Loop Join Thresholds
Nested
Loops Rows Disk CPU
Date Count Processed Reads Time
-------------------- ----------- ----------- -----------
04-10-10 16 22 750 796 4,017,301
04-10-10 17 25 846 6 3,903,560
04-10-10 19 26 751 1,430 4,165,270
04-10-10 20 24 920 3 3,940,002
04-10-10 21 25 782 5 3,816,152
04-10-11 02 26 905 0 3,935,547
04-10-11 03 22 1,001 0 3,918,891
04-10-11 04 29 757 8 3,939,071
In the report above, nested loops are favored by SQL that returns a smaller 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
select
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
from
dba_hist_sql_plan p,
dba_hist_sqlstat t,
dba_hist_snapshot s
where
p.sql_id = t.sql_id
and
t.snap_id = s.snap_id
and
p.object_type like '%INDEX%'
group by
to_char(s.begin_interval_time,'mm-dd hh24'),
p.object_name
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.
Begin
Interval Index Disk Rows
time Name Reads Processed
-------- -------------------- ----------- -----------
10-14 12 I_CACHE_STATS_1 114
10-14 12 I_COL_USAGE$ 201 8,984
10-14 12 I_FILE1 2 0
10-14 12 I_IND1 93 604
10-14 12 I_JOB_NEXT 1 247,816
10-14 11 I_KOPM1 4 2,935
10-14 11 I_MON_MODS$_OBJ 12 28,498
10-14 11 I_OBJ1 72,852 604
10-14 11 I_PARTOBJ$ 93 604
10-14 11 I_SCHEDULER_JOB2 4 0
10-14 11 SYS_C002433 302 4,629
10-14 11 SYS_IOT_TOP_8540 0 75,544
10-14 11 SYS_IOT_TOP_8542 1 4,629
10-14 11 WRH$_DATAFILE_PK 2 0
10-14 10 WRH$_SEG_STAT_OBJ_PK 93 604
10-14 10 WRH$_TEMPFILE_PK 0
10-14 10 WRI$_ADV_ACTIONS_PK 38 1,760
The above report shows the highest impact tables.
Click here to read the full chapter.
