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.

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.

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.

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.

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.