BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 

 

 

The Full-Table Scan Report

Oracle Tips by Burleson Consulting

As I noted in Chapter 6, this is one of the most valuable SQL tuning reports. Here we see all of the SQL statements that performed full-table scans, and the number of times that a full-table scan was performed. We also see the number of rows and blocks in each table, and a flag that indicates whether the table is cached or in the KEEP pool.

We will go into greater detail on checking full-table scans on Chapter 10, but the main focus of this report is to ensure that the small tables are in the KEEP pool, and that the queries against the large tables are not “false” full-table scans that could be changed into an index range scan.

Mon Jan 29                                                   page    1
                          full table scans and counts
                 Note that "C" indicates in the table is cached.
                  “K” indicates the table is in the KEEP Pool

OWNER          NAME                     NUM_ROWS C K   BLOCKS  NBR_FTS
-------------- -------------------- ------------ - - -------- --------
SYS            DUAL                              N          2   97,237
SYSTEM         SQLPLUS_PRODUCT_PRO               N  K       2   16,178
DONALD         PAGER                   3,450,209 N    932,120    9,999
DONALD         RWU_PAGE                      434 N          8    7,355
DONALD         PAGER_IMAGE                18,067 N      1,104    5,368
DONALD         SUBSTANT                      476 N   K    192    2,087
DONALD         PRINT_PAGER_RANGE              10 N   K     32      874
ARSD           JANET_BOOKS                    20 N          8       64
PERFSTAT       STATS$TAB_STATS                   N         65       10

The access_report.sql script also provides some great statistics on index usage. Next, let’s look at the index range scan report.

The Index Range Scan Report

Next we see the report for index range scans. The most common method of index access in Oracle is the index range scan. An index range scan is used when the SQL statement contains a restrictive clause that requires a sequential range of values that are indexes for the table. Tables with a high number of index range scans may benefit from row-resequencing to reduce the amount of physical disk I/O.

Mon Jan 29                                                   page    1
                          Index range scans and counts

OWNER     TABLE_NAME           INDEX_NAME       TBL_BLOCKS    NBR_SCANS
--------- -------------------- -------------- ------------ ------------
DONALD    ANNO_HIGHLIGHT       HL_PAGE_USER_IX          16        7,975
DONALD    ANNO_STICKY          ST_PAGE_USER_IX           8        7,296
DONALD    PAGE                 ISBN_SEQ_IDX            120        3,859
DONALD    TOC_ENTRY            ISBN_TOC_SEQ_I           40        2,830
DONALD    PRINT_HISTORY        PH_KEY_IDX               32        1,836
DONALD    SUBSCRIPTION         SUBSC_ISBN_USEX         192          210
ARSD      JANET_BOOK_RANGES    ROV_BK_RNG_BOO            8          170
PERFSTAT  STATS$SYSSTAT        STATS$SYSSTAT_P         845           32

These index reports are critical for several areas of Oracle SQL tuning.

  • Index usage   Ensuring that the application is actually using a new index. Remote DBAs can now obtain empirical evidence that an index is actually being used after it has been created. Indexes that are not being used cause additional overhead for SQL insert and update statements and also waste valuable disk space.

  • Row resequencing The index range scan report is great for finding out which tables might benefit from row resequencing. Tables that have a large amount of index range scan activity will benefit from having the rows resequenced into the same order as the index. Resequencing can result in a tenfold performance improvement, depending on the average length of the rows in the table.

The Index Unique Scan Report

Here is a report that lists index unique scans, which occur when the Oracle database engine uses an index to retrieve a specific row from a table. The Oracle database commonly uses these types of “probe” accesses when it performs a JOIN and probes another table for the JOIN key from the driving table. This report is also useful for finding out those indexes that are used to identify distinct table rows as opposed to indexes that are used to fetch a range of rows.

Mon Jan 29                                             page    1
                         Index unique scans and counts

OWNER     TABLE_NAME           INDEX_NAME              NBR_SCANS
--------- -------------------- -------------------- ------------
DONALD    BOOK                 BOOK_ISBN                  44,606
DONALD    PAGE                 ISBN_SEQ_IDX               39,973
DONALD    BOOK                 BOOK_UNIQUE_ID              6,450
DONALD    ANNO_DOG_EAR         DE_PAGE_USER_IDX            5,339
DONALD    TOC_ENTRY            ISBN_TOC_SEQ_IDX            5,186
DONALD    PRINT_PERMISSIONS    PP_KEY_IDX                  1,836
DONALD    RDRUSER              USER_UNIQUE_ID_IDX          1,065
DONALD    CURRENT_LOGONS       USER_LOGONS_UNIQUE_I          637
ARSD      JANET_BOOKS          BOOKS_BOOK_ID_PK               54
DONALD    ERROR_MESSAGE        ERROR_MSG_IDX                  48

The Full-Index Scan Report

The next report shows all index full scans. As you will recall, the Oracle optimizer will sometimes perform an index full scan in lieu of a large sort in the TEMP tablespace. You will commonly see full-index scans in SQL that have the order by clause.

Mon Jan 29                                             page    1
                         Index full scans and counts

OWNER     TABLE_NAME           INDEX_NAME              NBR_SCANS
--------- -------------------- -------------------- ------------
DONALD    BOOK                 BOOK_ISBN                   2,295
DONALD    PAGE                 ISBN_SEQ_IDX                  744

Limitations of the access.sql Reports

The technique for generating these reports is not as flawless as it may appear. Because the “raw” SQL statements must be explained in order to obtain the execution plans, you may not know the owner of the tables. One problem with native SQL is that the table names are not always qualified with the table owner. To ensure that all the SQL statements are completely explained, many Remote DBAs sign on to Oracle and run the reports as the schema owner.

A future enhancement would be to issue the following undocumented command immediately before each SQL statement is explained so that any Oracle database user could run the reports:

ALTER SESSION SET current_schema = ‘tableowner’;

This would change the schema owner immediately before explaining the SQL statement.

Conclusion

Oracle provides a wealth of tools that quickly enable you to examine the run-time details of Oracle SQL execution. We can use the standard explain plan utility, TKPROF, or more advanced techniques such as Oracle’s COE script and access.sql to give us detailed information about the physical access path to our table rows.

Next, let’s turn our attention to tools that can be used for locating the most significant SQL statements.


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


Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

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

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter