Fix unnecessary full-table scans

While not all large-table full-table scans are problematic, a large-table full-table scan is a common symptom of a SQL execution  problem.  Large-table full-table scans in an explain plan (TABLE ACCESS FULL) should always be examined to verify that it is not due to a database problem, such as a missing index.

Unnecessary large-table full-table scans "full-table scans"  (FTS) are an important symptom of sub-optimal SQL and cause unnecessary I/O that can drag down an entire database.

The first step in validating a full-table scan is to evaluate the SQL based on the number of rows returned by the query and your own guess about the number of trips to the data blocks that would be needed to fetch the rows.
 
Oracle says that if the query returns less than 40 percent of the table rows in an ordered table or seven percent of the rows in an unordered table, the query can be tuned to use an index in lieu of the full-table scan, but in reality there is no fixed number because it depends on many factors like the db_block_size and db_file_multiblock_read_count.
 
This decision is partly based on the index key value described by clustering_factor  in the dba_indexes view. However, it is not a simple process.
 
The choice of a full table scan vs. index access as the "best" access plan for a SQL statement depends on many factors.  The most common cause of unnecessary full-table scans is a optimizer_mode that favors full-table scans (like all_rows) or a missing index, especially a function-based indexes.
 
Follow the link below for more on these factors and their importance:

http://www.dba-oracle.com/t_fix_unnecessary_full_table_scans.htm 

*****************************************
Try Ion for Oracle database trends!

Using the principles of decision support systems technology, BC has developed an important new Oracle tuning tool, Ion for Oracle. Ion quickly isolates the reports on the important optimization issues, separating the wheat from the chaff like no other performance tuning software.

After several years of hard work, Ion for Oracle is finally available for general release. Ion is unlike any other Oracle tool on the market, a tool that encapsulates expert techniques to allow a DBA to quickly visualize important trends and signatures.

Predictive analytics is the key to repairing Oracle problems before they cripple your database, and I created Ion for Oracle to allow any DBA to quickly identify changing workloads and unobtrusive patterns of data access. To try Ion for free, see here:

http://www.ion-dba.com 

*****************************************

Need a Health Check?

Oracle is the world's most complex and robust database and there are hundreds of sub-optimal settings that can cripple your database performance.

Burleson Consulting has a great Oracle health check where we identify all database bottlenecks to ensure that your mission critical system is running at optimal speeds

Just call 800-766-1884 to schedule your health check.

*****************************************

Need Oracle Training?

The very best Oracle training comes from Burleson Consulting, where you get an on-site visit by an experienced Oracle expert and author. Whether it's one-on-one mentoring or getting a customized on-site Oracle training class, there is no substitute for BC Oracle training. Just call 800-766-1884 for details, and check out our on-site Oracle training catalog at the following link:

http://www.dba-oracle.com/bc-catalog.pdf 

*****************************************

There's Always Room at the Top

Burleson Consulting continues to grow as the Oracle community recognizes that high quality Oracle support and training is a cost effective option in these tough economic times.

If you have distinguished yourself in your Oracle career and are ready to step up and enjoy the rewards of joining the ranks of America's top tier support consultants we would like to hear from you.

Evidence of high achievement as an Oracle professional might include publication of books and articles for the Oracle community, achieving an advanced degree from a competitive university and seeking out hands-on experience with complex databases. See here for details and a full qualifications list:

http://www.dba-oracle.com/jobs.htm