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

 

 

   
 

Monitoring External Tables

Oracle Tips by Burleson
 

By Mike Ault

In Oracle, the concept of external tables was introduced. These external tables combine the aspects of an Oracle table, SQLLOADER, and a BFILE, to allow the DBA to access data from a file external to the Oracle database files as if it where an unindexed normal database table.

SOURCE 10.19 Script to monitor external tables.

REM EXT_TAB.SQL
REM MRA 10/08/01 Initial Creation
REM Script to monitor external tables
REM
COLUMN owner FORMAT a8 HEADING 'Owner'
COLUMN table_name FORMAT a15 Heading 'Table'
COLUMN type_owner FORMAT a8 HEADING 'Type|Owner'
COLUMN type_name FORMAT a13 HEADING 'Type|Name'
COLUMN default_directory_owner FORMAT a10 HEADING 'Dir|Owner'
COLUMN default_directory_name FORMAT a10 HEADING 'Dir|Name'
COLUMN reject_limit FORMAT a9 HEADING 'Reject|Limit'
COLUMN access_type FORMAT a6 HEADING 'Access|Type'
COLUMN access_parameters FORMAT a35 WORD_WRAPPED HEADING 'Access Parameters'
SET LINES 132 PAGES 55
START title132 'External Tables'
SPOOL rep_out/&db/ext_tab
SELECT
owner,
table_name,
type_owner,
type_name,
default_directory_owner,
default_directory_name,
reject_limit,
access_type,
access_parameters
from dba_external_tables
/
SPOOL OFF
SET lines 80 Pages 22

LISTING 10.18 Example output from external table script.

date: 10/09/01                                                                                                      Page: 1 
Time: 12:16 AM                                             External Tables                                           SYSTEM
                                                        galinux1 database
 
                         Type     Type          Dir        Dir        Reject    Access
Owner    Table           Owner    Name          Owner      Name       Limit     Type   Access Parameters
-------- --------------- -------- ------------- ---------- ---------- --------- ------ -----------------------------------
SYSTEM   SQL_SCRIPTS     SYS      ORACLE_LOADER SYS        SQL_DIR    0         CLOB   fields terminated by ';' optionally
                                                                                       enclosed by "'"
                                                                                       (permissions,
                                                                                       filetype,owner,group_name,size_in_b
                                                                                       ytes, date_edited DATE(19) "Mon dd
                                                                                       2001 hh24:mi",
                                                                                       script_name)
 
DBAUTIL  SQL_SCRIPTS     SYS      ORACLE_LOADER SYS        SQL_DIR    0         CLOB   fields terminated by ';' optionally
                                                                                       enclosed by "'"
                                                                                       (permissions,
                                                                                       filetype,owner,group_name,size_in_b
                                                                                       ytes, date_edited DATE(19) "Mon dd
                                                                                       2001 hh24:mi",
                                                                                       script_name)
 
DBAUTIL  SQL_SCRIPTS2    SYS      ORACLE_LOADER SYS        SQL_DIR    0         CLOB   fields terminated by ';' optionally
                                                                                       enclosed by "'"
                                                                                       (permissions,
                                                                                       filetype,owner,group_name,size_in_b
                                                                                       ytes, date_edited DATE(19) "Mon dd
                                                                                       2001 hh24:mi",
                                                                                       script_name)
 
SH       SALES_TRANSACTI SYS      ORACLE_LOADER SYS        DATA_FILE_ UNLIMITED CLOB   RECORDS DELIMITED BY NEWLINE
         ONS_EXT                                           DIR                         CHARACTERSET US7ASCII
                                                                                       BADFILE
                                                                                       log_file_dir:'sh_sales_ext.bad'
                                                                                       LOGFILE
                                                                                       log_file_dir:'sh_sales_ext.log'
                                                                                       FIELDS TERMINATED BY "|" LDRTRIM

Using the V$ and DB_ Views for Monitoring Indexes

In Oracle7 (7.3.2), the bitmapped index was added. Under Oracle8, indexes were expanded to include the concept of partitions, in addition to the old monitoring requirements. In Oracle8i, partitioning was expanded to include subpartitions and the additional functionality of function-based indexes, as well as indextypes. Oracle8i also added support for descending indexes. To those capabilities, Oracle added the bitmap join index and the skip scan index.

As for prior versions, in Oracle 9i, the DBA will have to monitor table indexes to verify uniqueness, determine if the appropriate columns have been indexed, and determine proper ownership of indexes for a given application. The DBA also needs a convenient reference to show which tables have indexes, as well as what is indexed, in case of the loss of a table or for use during table maintenance. The diagram in Figure 10.3 shows the cluster of DBA_ views that a DBA needs to use for monitoring indexes.

Figure 10.3 DBA_View cluster for monitoring indexes.

The report in Source 10.20 provides a convenient format for the DBA to use to review indexed tables and columns; it is selective down to the single-table, single-owner level. The report should be run after database maintenance that involves table rebuilds, exports and imports, or database rebuilds. Listing 10.19 is an example of the output from Source 10.20.

SOURCE 10.20 SQL script to generate index report.

rem
rem NAME: ind_rep.sql
rem FUNCTION: Report on indexes
rem HISTORY: MRA 6/14/97 Creation
rem
COLUMN owner                FORMAT a8      HEADING 'Index|Owner'
COLUMN index_name           FORMAT a27     HEADING 'Index'
COLUMN index_type           FORMAT a6      HEADING 'Type|Index'
COLUMN table_owner          FORMAT a8      HEADING 'Table|Owner'
COLUMN table_name           FORMAT a24     HEADING 'Table Name'
COLUMN table_type           FORMAT a10     HEADING 'Table|Type'
COLUMN uniqueness           FORMAT a1      HEADING 'U|n|i|q|u|e'
COLUMN tablespace_name      FORMAT a13     HEADING 'Tablespace'
COLUMN column_name          FORMAT a25      HEADING 'Col. Name'
SET PAGES 58 LINES 130 FEEDBACK OFF VERIFY OFF
BREAK ON owner
START title132 'Expandeded Index Report'
SPOOL rep_out\&db\ind_exp.lis
SELECT
     a.owner,
     a.index_name,
     a.index_type,
     a.table_owner,
     a.table_name,
     a.table_type,
     DECODE
     (a.uniqueness, 'UNIQUE', 'U','NONUNIQUE','N') uniqueness,
      a.tablespace_name,
      b.column_name
FROM
     dba_indexes a, dba_ind_columns b
WHERE
     owner LIKE UPPER('%&owner%')
     AND a.owner=b.index_owner(+)
     AND a.index_name=b.index_name(+)
ORDER BY
     owner, index_type;
SPOOL OFF

LISTING 10.19 Example of output from index report.

Date: 06/14/97                                                                   Page:   1   
Time: 04:35 PM                    Expanded Index Report                         SYSTEM        
                                      ORTEST1 database                                                           
                                                                                                                                 
                                                                   U                                        
                                                                   n                                       
                                                                   i                                       
                                                                   q                                       
Index                          Type   Table                Table   u                                       
Owner  Index                   Index  Owner  Table Name    Type    e Tablespace  Col. Name
------ ----------------------- ------ ------ ------------- ------- - ----------- ---------
SYSTEM PK_TEST_IOT             IOT -  SYSTEM TEST_IOT      TABLE   U RAW_DATA    TEST1
                               TOP                                                  
                                                                                                                                 
       SYS_IL0000001562C00035$ LOB    SYSTEM DEF$_AQCALL   TABLE   U SYSTEM                                 
       SYS_IL0000001571C00035$ LOB    SYSTEM DEF$_AQERROR  TABLE   U SYSTEM                                
       SYS_IL0000001588C00005$ LOB    SYSTEM DEF$_LOB      TABLE   U SYSTEM                                
       SYS_IL0000001597C00002$ LOB    SYSTEM DEF$_TEMP$LOB TABLE   U SYSTEM                                
       SYS_IL0000001597C00001$ LOB    SYSTEM DEF$_TEMP$LOB TABLE   U SYSTEM                                
       SYS_IL0000001588C00006$ LOB    SYSTEM DEF$_LOB      TABLE   U SYSTEM                                
       SYS_IL0000001588C00004$ LOB    SYSTEM DEF$_LOB      TABLE   U SYSTEM                                
       SYS_IL0000001597C00003$ LOB    SYSTEM DEF$_TEMP$LOB TABLE   U SYSTEM                                 
       AQ$_QUEUES_CHECK        NORMAL SYSTEM AQ$_QUEUES    TABLE   U SYSTEM     NAME
       AQ$_QUEUES_PRIMARY      NORMAL SYSTEM AQ$_QUEUES    TABLE   U SYSTEM      OID                     
       BM_TEST_BITMAP          BITMAP SYSTEM TEST_BITMAP   TABLE   N SYSTEM     TEST_COL1

Monitoring Index Statistics  

Under Oracle7, the DBA_INDEXES view was extended to include B-tree level, number of leaf blocks, number of distinct keys, average number of leaf blocks per key, average number of data blocks per key, and the index clustering factor. Under Oracle8 and Oracle8i, columns covering partitions, domain indexes, and function-based indexes where added. Under Oracle, columns for index types and join indexes where added, along with a column to show the index status, either DIRECT LOAD or VALID. The TYPE column specifies whether the index is NORMAL, an IOT, an LOB, or a BITMAP index. This is essentially the only indicator for BITMAP-type indexes.

Index statistics generated from the ANALYZE command are stored in the INDEX_STATS view. The major limitation of the INDEX_STATS view is that it shows only the most currently analyzed index. Run the script in Source 10.21 if you want results from all the indexes in a particular schema. An example of a report from the script in Source 10.21 is shown in Listing 10.20.

SOURCE 10.21 Script to produce index statistics reports from the ANALYZE INDEX command.

rem
rem NAME: brown.sql
rem FUNCTION: Analyze indexes and produce stat report
rem FUNCTION: Including browning indicator
rem
rem HISTORY: MRA 6/15/97 Created
rem
COL del_lf_rows_len FORMAT 999,999,999 HEADING 'Deleted Bytes'
COL lf_rows_len     FORMAT 999,999,999 HEADING 'Filled Bytes'
COL browning        FORMAT 999.90      HEADING 'Percent|Browned'
COL height          FORMAT 999,999     HEADING 'Height'
COL blocks          FORMAT 999,999     HEADING 'Blocks'
COL disti                                        
                             nct_keys   FORMAT 999,999,999 HEADING '#|Keys'
COL most_repeated_key FORMAT 999999999 HEADING 'Most|Repeated|Key'
COL used_space      FORMAT 999999999   HEADING 'Used|Space'
COL rows_per_key    FORMAT 999999      HEADING 'Rows|Per|Key'
ACCEPT owner PROMPT 'Enter table owner name: '
SET HEADING OFF FEEDBACK OFF VERIFY OFF ECHO OFF RECSEP OFF
SET PAGES 0
TTITLE OFF
DEFINE cr='CHR(10)'
SPOOL index_sz.sql
SELECT
  'CREATE TABLE stat_temp AS SELECT * FROM index_stats;'||&&cr||
  'TRUNCATE TABLE stat_temp;' 
FROM dual;
SELECT    
'ANALYZE INDEX '||owner||'.'||index_name||
' VALIDATE STRUCTURE;'||&&cr||
     'INSERT INTO stat_temp SELECT * FROM index_stats;'||&&cr||
     'COMMIT;'
FROM
     dba_indexes
WHERE
     owner=UPPER('&owner');
SPOOL OFF
PROMPT 'Analyzing Indexes'
SET FEEDBACK OFF TERMOUT OFF LINES 132 VERIFY OFF
START index_sz.sql
SET TERMOUT ON FEEDBACK ON VERIFY ON LINES 132 PAGES 58
START title132 "Index Statistics Report"
SPOOL rep_out/&db/browning.lst
SELECT
     name,
     del_lf_rows_len,
     lf_rows_len,
(del_lf_rows_len/
DECODE((lf_rows_len+del_lf_rows_len),0,1,lf_rows_len+
del_lf_rows_len))*100 browning,
     height,
     blocks,
     distinct_keys,
     most_repeated_key,
     used_space,
     rows_per_key
FROM
     stat_temp
WHERE rows_per_key>0;
SPOOL OFF
SET FEEDBACK ON TERMOUT ON LINES 80 VERIFY ON
HOST del stat_temp

LISTING 10.20 Example of output from index statistics from ANALYZE command report.

Date: 06/15/97
Page:   1
Time: 10:31 AM                    Index Statistics Report         SYSTEM
                                      ORTEST1 database
                                                                           Most           Rows                                         Percent                   #     Repeat    Used    Per         
NAME               Deleted   Filled      Browned  Height Blocks   Keys     Key
Space   Key
----------------- --------- ----------- -------- ------ -------- ------ -------- FK_ADDRESSES_2           0   10,126,346     .00        3  12800  583996      2 10159315     1         
FK_ADDRESSES_3           0   12,115,956     .00        3  12800  758357      1 12153926     1         
FK_FRANC_CDS_1           0    1,880,298     .00        3   2560   19619      6  1888613     6         
FK_SIC_CODES_1           0   15,896,017     .00        3  12800  875966      3 15948812     1         
LI_LOAD_TEST             0   22,568,301     .00        3  10240  875966   8461 22676759     1         
PK_ADDRESSES             0   21,249,760     .00        3  25600 1392036      1 21312498     1         
PK_CLIENTS               0   13,121,655     .00        3  25600  875966      1 13159342     1         
PK_EARNINGS              0   11,357,779     .00        3  25600  758369      1 11390423     1         
PK_FRANC_CDS             0    2,340,249     .00        3  12800  117714      1  2349540     1         
PK_SIC_CODES             0   19,856,433     .00        3  12800  994826      1 19921338     1         
PK_USERS                 0           13     .00        1  25600       1      1       13     1         
SYS_C00800               0           27     .00        1      5       1      1       27     1         
TEST_INDEX               0           17     .00        1      5       1      1       17     1         
UI_EARNINGS_1            0   18,200,856     .00        3  12800  758369      1 18295755     1         
UK_CLIENTS               0   17,519,320     .00        3  12800  875966      1 17584123     1         
16 rows selected

If the rows-per-key column in the report in Listing 10.20 exceeds 100, you should consider making the index a bitmap index (post-7.3.2). If the index shows excessive "browning" (30 percent maximum) then a rebuild is in order.

The clustering factor column, in listing 10.21, shows how well the index is ordered in comparison to the base table. If the value for the clustering factor is near the number of table blocks, it means the index is well ordered; conversely, if the value is near the number of rows in the table, the index is not well ordered (unless the row size is close to blocksize).

For high use indexes with high clustering factors, consider rebuilding the table in the same sequence as the index, as a high clustering factor indicates that, under index scan conditions, the same blocks will be read numerous times.

A script for reporting some of the statistics stored in the DBA_INDEXES view is shown in Source 10.22. Note that these statistics are not dynamic; they are 100 percent valid only at the time the ANALYZE command is run—which is why I am pleased that Oracle included the last-date-analyzed field in Oracle8. Corresponding sample output is shown in Listing 10.21.

SOURCE 10.22 Example of statistics report for Oracle8, Oracle8i, and Oracle indexes.

rem  NAME: IN_STAT.sql
rem
rem  FUNCTION: Report on index statistics
rem  INPUTS:    1 = Index owner    2 = Index name
rem
DEF iowner = '&OWNER'
DEF iname  = '&INDEX'
SET PAGES 56 LINES 130 VERIFY OFF FEEDBACK OFF
COLUMN owner                   FORMAT a8          HEADING "Owner"
COLUMN index_name              FORMAT a25         HEADING "Index"
COLUMN status                  FORMAT a7          HEADING "Status"
COLUMN blevel                  FORMAT 9,999       HEADING "Tree|Level"
COLUMN leaf_blocks             FORMAT 999,999,999 HEADING "Leaf Blk"
COLUMN distinct_keys           FORMAT 999,999,999 HEADING "# Keys"
COLUMN avg_leaf_blocks_per_key FORMAT 9,999       HEADING "Avg.|LB/Key"
COLUMN avg_data_blocks_per_key FORMAT 9,999       HEADING "Avg.|DB/Key"
COLUMN clustering_factor       FORMAT 999,999     HEADING "Clstr|Factor"
COLUMN num_rows                FORMAT 999,999,999 HEADING "Number|Rows"
COLUMN sample_size             FORMAT 99,999      HEADING "Sample|Size"
COLUMN last_analyzed           HEADING 'Analysis|Date'
rem
BREAK ON owner
START title132 "Index Statistics Report"
SPOOL rep_out\&db\ind_stat
rem
SELECT
     owner, index_name, status, blevel, leaf_blocks,
     distinct_keys, avg_leaf_blocks_per_key,
     avg_data_blocks_per_key, clustering_factor,
     num_rows, sample_size, last_analyzed
FROM
     dba_indexes
WHERE
     owner LIKE UPPER('&&iowner')
     AND index_name LIKE UPPER('&&iname')
     AND num_rows>0
ORDER BY
     1,2;
rem
SPOOL OFF
SET PAGES 22 LINES 80 VERIFY ON FEEDBACK ON
CLEAR COLUMNS
UNDEF iowner
UNDEF iname
UNDEF owner
UNDEF name
TTITLE OFF

LISTING 10.21 Example of report output from the script in Source 10.22.

Date: 06/14/97                                                                       Page:   1
Time: 08:22 PM                        Index Statistics Report                           SYSTEM
                                          ORTEST1 database
 
                               Tr. Lf.            Avg.   Avg.   Clstr   Number  Sam. Anl.                    
Owner    Index         Status Lev  Blk   # Keys  LB/Key DB/Key Factor     Rows  Size Date                        
 
TELE_DBA FK_ADDRESS_2  VALID   2  2650   583996      1      1   14191   633679     0 14-JUN-97
TELE_DBA FK_ADDRESS_3  VALID   2  3171   758357      1      1   18637   758357     0 14-JUN-97
TELE_DBA FK_FRAN_CD_1  VALID   2   492    19619      1      1     803   117714     0 14-JUN-97
TELE_DBA FK_SIC_CDS_1  VALID   2  4160   875966      1      1   16765   994826     0 14-JUN-97
TELE_DBA LI_LOAD_TEST  VALID   2  6474   875966      1      1  140442  1074681     0 14-JUN-97
TELE_DBA PK_ADDRESSES  VALID   2  5560  1392036      1      1   32827  1392036     0 14-JUN-97
TELE_DBA PK_CLIENTS    VALID   2  3433   875966      1      1   61587   875966     0 14-JUN-97
TELE_DBA PK_EARNINGS   VALID   2  2972   758369      1      1   28485   758369     0 14-JUN-97
TELE_DBA PK_FRAN_CDS   VALID   2   613   117714      1      1     803   117714     0 14-JUN-97
TELE_DBA PK_SIC_CODES  VALID   2  5204   994826      1      1   16765   994826     0 14-JUN-97
TELE_DBA PK_USERS      VALID   0     1        1      1      1       1        1     0 14-JUN-97
TELE_DBA SYS_C00800    VALID   0     1        1      1      1       1        1     0 14-JUN-97
TELE_DBA TEST_INDEX    VALID   0     1        1      1      1       1        1     0 14-JUN-97
TELE_DBA UI_EARNINGs_1 VALID   2  6738   758369      1      1  727251   758369     0 14-JUN-97
TELE_DBA UK_CLIENTS    VALID   2  4493   875966      1      1   61587   875966     0 14-JUN-97
TELE_DBA UK_LOAD_TEST  VALID   2  5456   758369      1      1  733393   758369     0 14-JUN-97

The various values in the report in Listing 10.21 are interpreted as follows:

Tr. Lev. The depth, or number of levels, from the root block of the index to its leaf blocks. A depth of 1 indicates that they are all on the same level.

LFBLK. The number of leaf blocks in the index.

AVG_LB/KEY.  Indicates a nonunique index if its value is greater than 1.

BLOCKS_PER_KEY. If greater than 1, indicates the key has duplicate values.

AVG DB/Key-. Indicates the average number of data blocks in the BLOCKS_PER_KEY indexed table that are pointed to by a distinct value in the index.

CLSTR FACTOR. Indicates the orderliness of the table being indexed. If it is near the number of blocks in table, it indicates a well-ordered table; if it is near the number of rows, it indicates a disorganized table.

SAM. SIZE. Tells the sample size specified if the index was analyzed using the estimate clause.

ANL. DATE. The last date on which the index was analyzed.

 

See Code Depot for Full Scripts


This is an excerpt from Mike Ault, bestselling author of "Oracle 10g Grid and Real Application Clusters".

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.
  
 

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