Oracle Consulting Oracle Training Oracle Support Development
Home
Catalog
Oracle Books
SQL Server Books
IT Books
Job Interview Books
eBooks
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
 Phone
 800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

Privacy Policy

 

 
 

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.

 

<      awr_sql_index_freq.sql

 

 

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

 

 

   

 Copyright © 1996 -2016 by Burleson. All rights reserved.


Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks