Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

Evaluate the Legitimacy of the Full-Table Scan
Oracle Tips by Burleson
 

As you should know by now, a full-table scan may be faster than an index range scan if a minority of the table blocks are retrieved by the query. We say "table blocks" instead of the number of rows, because it is the number of data blocks, and not the number of table rows, that most influences the decision to perform a full-table scan over an index range scan. For an un-sequenced table with small row lengths and a large block size, a random retrieval of 10 percent of the rows can access well over 50 percent of the table blocks in the table.

Since the number of rows returned is heavily dependent on the row length and the block size, there is no firm percentage that can be used to determine the threshold for a full-table scan. If the query retrieves fewer data blocks using an index range scan than the full-table scan, then the index access will generally be a faster alternative.

The goal is to compare the number of blocks returned by the full-table scan with the number of data blocks returned by the index range scan. Computing the number of blocks accessed by a full-table scan can be done by going to the Oracle data dictionary and summing the number of blocks from the dba_segments view:

select
   sum(bytes)
from
   dba_segments
where
   table_name = ‘CUSTOMER’;

SUM(BYTES)
----------------------
152392

Now, we need to compare the number of blocks retrieved by the full-table scan with the blocks retrieved from an index range scan. Since the number of blocks retrieved by the query is the most important, the following equation can be used to determine the approximate number of blocks retrieved by the query. To use this equation, you need the following information:

  • The number of rows returned by the query. This is determined by executing the query

  • The database blocksize (the db_block_size initialization parameter)

  • The average row length (the avg_row_len in the dba_tables view)

  • The clustering_factor in the dba_indexes view for the index you plan to use

Let’s look at two methods for comparing an indexed SQL query against the block I/O for a full-table scan.

Estimating Blocks Retrieved by an Indexed Table Query

This is an equation to compute the number of blocks retrieved by a row-sequenced table. We discussed the method for using CTAS to resequence rows. You can tell if your table is row-sequenced by checking the clustering_factor column of the dba_indexes view for the index that you are using to retrieve your rows.

  • If clustering_factor is close to the number of data blocks in your table (the blocks column of dba_segments), then your table is row-sequenced.

  • If clustering_factor is close to the number of rows (the num_rows column of the dba_tables view), then your table rows are not in index sequence. Hence, we must perform lots of additional block accesses to retrieve our rows.

Next, let’s look at a way to estimate the number of data blocks retrieved by a query.

If your clustering_factor is close to the number of blocks in the table, then you can use this method to estimate the number of blocks retrieved by the query.

Here is a great way to estimate the number of blocks retrieved by your query:

                                                   number of rows retrieved
Number of blocks retrieved =---------------------------------------------------
                                      db_block_size      dba.segments.blocks
                                      -------------- *  -----------------------
                                         avg_row_len     
dba_indexes.clustering_factor       

To see how this works, assume that you have collected the following information about the table and index in your query.

  • number of rows retrieved = 600

  • db_block_size = 16K (rounded to 16,000 for estimation purposes)

  • dba_tables.avg_row_len = 80

  • dba_segments.blocks = 1000

  • dba_indexes.clustering_factor = 20,000

When we plug numbers into the equation, we see that this index range scan causes about 60 data blocks to be retrieved by Oracle. Of course, a block request does not always result in a disk I/O, because of the buffering in the DEFAULT, KEEP, and RECYCLE pools.

600               600
------------ = ---------- = 60 data blocks retrieved
200*.05           10

Note that as the clustering_factor increases from the number of blocks in the table to the number of rows in the table, the number of disk accesses will increase. For example, when we change the clustering factor from 1000 to 2000, we see that Oracle must retrieve twice the number of blocks.

NOTE: Be sure to completely reanalyze the table and the index to get accurate dictionary statistics for this equation.

Now that you know the number of data blocks retrieved by the query, we can accurately compare the number of blocks retrieved as a percentage of the number of blocks in the table.


This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

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

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter