|
Note: Oracle technology is
always evolving, so make sure to review the latest notes on the
benefits of large blocksizes for indexes.
See
The 2008 consensus on multiple blocksizes. for more current tips
and techniques.
Not all Indexes are used in Range Scans
When deciding to segregate indexes into larger blocksizes it is
important to understand that those indexes that are subject to
frequent index range scans and fast-full
scans will benefit the most from a larger blocksize.
When Oracle joins two tables together with a nested loop, only
one of the indexes may be accessed as a range. The optimizer
always performs an index range scan on one index, gathers the
rowid values, and does fetch by rowid on the matching
rows in the other table. For example:
select
customer_name,
order_date
from
customer
orders
where
customer.cust_key = orders.cust_key;
The Oracle documentation notes “In a nested loop join, for
every row in the outer row set, the inner row set is accessed to
find all the matching rows to join. Therefore, in a nested loop
join, the inner row set is accessed as many times as the number
of rows in the outer row set.”
Oracle will only scan one index, build a set of keys, and then
probe the rows from the other table (Figure 13.1).
Figure 13.3:
Table joins include index
range scans and index unique scans
So, if this nested loop never uses the customer index, why is it
there? The answer is, for index unique scans. In an index
unique scan, a single row is accessed within the index, as seen
in this query:
select
customer_last_name,
customer_address
from
customer
where
cust_key = 123;
In sum, the DBA must find out “how” their indexes are being used
by the SQL. Remember, an index that never experiences range
scans would not benefit from a larger blocksize. The question
becomes one of finding those indexes that experience lots of
range scans, and AWR can help.
It is possible to identify those indexes with the most index
range scans with the following simple AWR script.
col c1 heading ‘Object|Name’ format a30
col c2 heading ‘Option’ format a15
col c3 heading ‘Index|Usage|Count’ format 999,999
select
p.object_name c1,
p.options c2,
count(1) c3
from
dba_hist_sql_plan p,
dba_hist_sqlstat s
where
p.object_owner <> 'SYS'
and
p.options like '%RANGE SCAN%'
and
p.operation like ‘%INDEX%’
and
p.sql_id = s.sql_id
group by
p.object_name,
p.operation,
p.options
order by
1,2,3;
The following is the output showing overall total counts for
each object and table access method.
Index
Object Usage
Name Option Count
----------------------------- --------------- --------
CUSTOMER_CHECK RANGE SCAN 4,232
AVAILABILITY_PRIMARY_KEY RANGE SCAN 1,783
CON_UK RANGE SCAN 473
CURRENT_SEVERITY RANGE SCAN 323
CWM$CUBEDIMENSIONUSE_IDX RANGE SCAN 72
ORDERS_FK RANGE SCAN 20
This will quickly identify indexes that will benefit the most
from a 32k blocksize.
This index list can be double verified by using the AWR to
identify indexes with high disk reads during each AWR snapshot
period. The sample script below,
awr_top_tables_phyrd.sql,
exposes the top five tables accessed mostly heavily by
physical disk reads for every snapshot interval:
<
awr_top_tables_phyrd.sql
col c0 heading ‘Begin|Interval|time’ format a8
col c1 heading ‘Table|Name’ format a20
col c2 heading ‘Disk|Reads’ format 99,999,999
col c3 heading ‘Rows|Processed’ format 99,999,999
select
*
from (
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,
DENSE_RANK() OVER (PARTITION BY
to_char(s.begin_interval_time,'mm-dd hh24') ORDER BY
SUM(t.disk_reads_total) desc) AS rnk
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 '%TABLE%'
group by
to_char(s.begin_interval_time,'mm-dd hh24'),
p.object_name
order by
c0 desc, rnk
)
where rnk <= 5
;
The following is the sample output from the above script:
Begin
Interval Table Disk Rows
time Name Reads Processed RNK
-------- -------------------- ----------- ----------- ----------
10-29 15 CUSTOMER_CHECK 55,732 498,056 1
10-29 15 CON_UK 18,368 166,172 2
10-29 15 CURRENT_SEVERITY 11,727 102,545 3
10-29 15 ORDERS_FK 5,876 86,671 4
10-29 15 SYN$ 2,624 23,674 5
10-29 14 CUSTOMER_CHECK 47,756 427,762 1
10-29 14 CON_UK 15,939 142,878 2
10-29 14 CURRENT_SEVERITY 6,976 113,649 3
10-29 14 X$KZSRO 4,772 119,417 4
10-29 14 ORDERS_FK 2,274 20,292 5
10-29 13 CUSTOMER_CHECK 25,704 213,786 1
10-29 13 CON_UK 8,568 71,382 2
10-29 13 OBJ$ 3,672 30,474 3
10-29 13 X$KZSRO 2,448 20,328 4
10-29 13 SYN$ 1,224 10,146 5
This report shows the tables with the highest disk reads, very
important information for disk tuning.
The dba_hist_sql_plan table can also be used to gather counts about the frequency of
participation of objects inside queries. This is a great query
to quickly see what’s going on between the tables and the SQL
that accesses them.
<
awr_sql_object_freq.sql
col c1 heading ‘Object|Name’ format a30
col c2 heading ‘Operation’ format a15
col c3 heading ‘Option’ format a15
col c4 heading ‘Object|Count’ format 999,999
break on c1 skip 2
break on c2 skip 2
select
p.object_name c1,
p.operation c2,
p.options c3,
count(1) c4
from
dba_hist_sql_plan p,
dba_hist_sqlstat s
where
p.object_owner <> 'SYS'
and
p.sql_id = s.sql_id
group by
p.object_name,
p.operation,
p.options
order by
1,2,3;
The following output shows overall total counts for each object
and table access method.
Object
Object
Name
Operation Option Count
------------------------------
--------------- --------------- --------
CUSTOMER
TABLE ACCESS FULL 305
CUSTOMER _CHECK
INDEX RANGE SCAN 2
CUSTOMER_ORDERS
TABLE ACCESS BY INDEX ROWID 311
CUSTOMER_ORDERS
FULL 1
CUSTOMER_ORDERS_PRIMARY
INDEX FULL SCAN 2
CUSTOMER_ORDERS_PRIMARY UNIQUE
SCAN 311
AVAILABILITY_PRIMARY_KEY RANGE
SCAN 4
CON_UK RANGE
SCAN 3
CURRENT_SEVERITY_PRIMARY_KEY RANGE SCAN
1
CWM$CUBE
TABLE ACCESS BY INDEX ROWID 2
CWM$CUBEDIMENSIONUSE BY INDEX
ROWID 2
CWM$CUBEDIMENSIONUSE_IDX
INDEX RANGE SCAN 2
CWM$CUBE_PK
UNIQUE SCAN 2
CWM$DIMENSION_PK FULL
SCAN 2
MGMT_INV_VERSIONED_PATCH
TABLE ACCESS BY INDEX ROWID 3
MGMT_JOB BY INDEX ROWID
458
MGMT_JOB_EMD_STATUS_QUEUE
FULL 181
MGMT_JOB_EXECUTION BY INDEX
ROWID 456
MGMT_JOB_EXEC_IDX01
INDEX RANGE SCAN 456
MGMT_JOB_EXEC_SUMMARY
TABLE ACCESS BY INDEX ROWID 180
MGMT_JOB_EXEC_SUMM_IDX04
INDEX RANGE SCAN 180
MGMT_JOB_HISTORY
TABLE ACCESS BY INDEX ROWID 1
MGMT_JOB_HIST_IDX01
INDEX RANGE SCAN 1
MGMT_JOB_PK UNIQUE
SCAN 458
MGMT_METRICS
TABLE ACCESS BY INDEX ROWID 180
Using the output above, it is easy to monitor object
participation, especially indexes, in the SQL queries and the
mode with which an object was accessed by Oracle.
Why is it important to know how your tables and indexes are
accessed? Objects that experience multi-block reads may perform
faster in a larger blocksize and also reduce SGA overhead.
SEE CODE DEPOT FOR FULL SCRIPTS
 |
This is an excerpt from my latest book "Oracle
Tuning: The Definitive Reference".
You can buy it direct from the publisher for 50%-off and get
instant access to the code depot of Oracle tuning scripts: |
http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm
|
