SQL, SQL*Plus
Access Privileges:
Requires SELECT on DBA_IND_COLUMNS, DBA_INDEXES and INDEX_STATS.
Usage:
sqlplus user/
Instructions:
Copy the script to a file and execute it from SQL*Plus.
PROOFREAD THIS SCRIPT BEFORE USING IT! Due to differences in the way text
editors, e-mail packages, and operating systems handle text formatting (spaces,
tabs, and carriage returns), this script may not be in an executable state
when you first receive it. Check over the script to ensure that errors of
this type are corrected.
Description
There are 3 scripts available to report various information on indexes.
Script 1: Index Fragmentation
TFSIFRAG.SQL provides information critical in determining whether an
index is a candidate for rebuilding. An index is a candidate for
rebuilding when a relatively high number of index leaf row deletes have
occured.
Please note: This report does not indicate the actual index "balance."
Script 2: Index Statistics
TFSISTAT.SQL prints a variety of statistics about the given index.
Statistics include B*tree height, the number of distinct and repeated
keys, the number of branch rows and blocks, the number of leaf rows
and blocks and information about space utilization.
Please note: You will receive an "ORA-01476: divisor is equal to
zero" for an index on a table into which no rows have been inserted.
Script 3: Index Keys for a Table
TFSINKEY.SQL prints uniqueness, name and column information of indexes for the
given table and owner. The arguments are not case-sensitive, but may not
include wildcards. The script will behave unreliably for an index not owned
by the owner of its base table.
References
Script
==========
Script #1:
==========
SET ECHO off
REM NAME: TFSIFRAG.SQL
REM USAGE:"@path/tfsifrag schema_name index_name"
REM ------------------------------------------------------------------------
REM REQUIREMENTS:
REM SELECT on INDEX_STATS
REM ------------------------------------------------------------------------
REM PURPOSE:
REM Reports index fragmentation statistics
REM ------------------------------------------------------------------------
REM EXAMPLE:
REM Index Fragmentation Statistic
REM
REM index name S_EMP_USERID_UK
REM leaf rows deleted 0
REM leaf rows in use 25
REM index badness 0.000
REM
REM ------------------------------------------------------------------------
REM Main text of script follows:
set verify off
def ownr = &&1
def name = &&2
ttitle -
center 'Index Fragmentation Statistic' skip 2
set heading off
col name newline
col lf_blk_rows newline
col del_lf_rows newline
col ibadness newline
validate index &ownr..&name;
select
'index name '||name,
'leaf rows deleted '||to_char(del_lf_rows,'999,999,990') del_lf_rows,
'leaf rows in use '||to_char(lf_rows-del_lf_rows,'999,999,990') lf_blk_rows,
'index badness '||to_char(del_lf_rows/(lf_rows+0.00001),'999,990.999') ibadness
from
index_stats
/
undef ownr
undef name
set verify on
==============
Sample Output:
==============
Index Fragmentation Statistic
index name S_EMP_USERID_UK
leaf rows deleted 0
leaf rows in use 25
index badness 0.000
==========
Script #2:
==========
SET ECHO off
REM NAME: TFSISTAT.SQL
REM USAGE:"@path/tfsistat schema_name index_name"
REM ------------------------------------------------------------------------
REM REQUIREMENTS:
REM SELECT on INDEX_STATS
REM ------------------------------------------------------------------------
REM PURPOSE:
REM Report index statistics.
REM ------------------------------------------------------------------------
REM EXAMPLE:
REM Index Statistics
REM
REM S_EMP_USERID_UK
REM ----------------------------------------------------------
REM height 1
REM blocks 5
REM del_lf_rows 0
REM del_lf_rows_len 0
REM distinct_keys 25
REM most_repeated_key 1
REM btree_space 1,876
REM used_space 447
REM pct_used 24
REM rows_per_key 1
REM blks_gets_per_access 2
REM lf_rows 25 br_rows 0
REM lf_blks 1 br_blks 0
REM lf_rows_len 447 br_rows_len 0
REM lf_blk_len 1,876 br_blk_len 0
REM
REM ------------------------------------------------------------------------
REM Main text of script follows:
set verify off
def ownr = &&1
def name = &&2
ttitle -
center 'Index Statistics' skip 2
set heading off
col name newline
col headsep newline
col height newline
col blocks newline
col lf_rows newline
col lf_blks newline
col lf_rows_len newline
col lf_blk_len newline
col br_rows newline
col br_blks newline
col br_rows_len newline
col br_blk_len newline
col del_lf_rows newline
col del_lf_rows_len newline
col distinct_keys newline
col most_repeated_key newline
col btree_space newline
col used_space newline
col pct_used newline
col rows_per_key newline
col blks_gets_per_access newline
validate index &ownr..&name;
select
name,
'----------------------------------------------------------' headsep,
'height '||to_char(height, '999,999,990') height,
'blocks '||to_char(blocks, '999,999,990') blocks,
'del_lf_rows '||to_char(del_lf_rows,'999,999,990') del_lf_rows,
'del_lf_rows_len '||to_char(del_lf_rows_len,'999,999,990') del_lf_rows_len,
'distinct_keys '||to_char(distinct_keys,'999,999,990') distinct_keys,
'most_repeated_key '||to_char(most_repeated_key,'999,999,990') most_repeated_key,
'btree_space '||to_char(btree_space,'999,999,990') btree_space,
'used_space '||to_char(used_space,'999,999,990') used_space,
'pct_used '||to_char(pct_used,'990') pct_used,
'rows_per_key '||to_char(rows_per_key,'999,999,990') rows_per_key,
'blks_gets_per_access '||to_char(blks_gets_per_access,'999,999,990') blks_gets_per_access,
'lf_rows '||to_char(lf_rows, '999,999,990')||' '||+
'br_rows '||to_char(br_rows, '999,999,990') br_rows,
'lf_blks '||to_char(lf_blks, '999,999,990')||' '||+
'br_blks '||to_char(br_blks, '999,999,990') br_blks,
'lf_rows_len '||to_char(lf_rows_len,'999,999,990')||' '||+
'br_rows_len '||to_char(br_rows_len,'999,999,990') br_rows_len,
'lf_blk_len '||to_char(lf_blk_len, '999,999,990')||' '||+
'br_blk_len '||to_char(br_blk_len, '999,999,990') br_blk_len
from
index_stats
/
undef ownr
undef name
set verify on
==============
Sample Output:
==============
Index Statistics
S_EMP_USERID_UK
----------------------------------------------------------
height 1
blocks 5
del_lf_rows 0
del_lf_rows_len 0
distinct_keys 25
most_repeated_key 1
btree_space 1,876
used_space 447
pct_used 24
rows_per_key 1
blks_gets_per_access 2
lf_rows 25
br_rows 0
lf_blks 1
br_blks 0
lf_rows_len 447
br_rows_len 0
lf_blk_len 1,876
br_blk_len 0
==========
Script #3:
==========
SET ECHO off
REM NAME: TFSIKEYS.SQL
REM USAGE:"@path/tfsikeys idx_owner table_name"
REM ------------------------------------------------------------------------
REM REQUIREMENTS:
REM SELECT on DBA_IND_COLUMNS and DBA_INDEXES
REM ------------------------------------------------------------------------
REM PURPOSE:
REM Shows the index keys for a particular table.
REM ------------------------------------------------------------------------
REM EXAMPLE:
REM Index Keys Summary
REM
REM Uniqueness Index Name Column Name
REM ---------- ---------------------------------------- ------------------
REM UNIQUE SCOTT.S_EMP_ID_PK ID
REM
REM UNIQUE SCOTT.S_EMP_USERID_UK USERID
REM
REM ------------------------------------------------------------------------
REM Main text of script follows:
set verify off
def ixowner = &&1
def tabname = &&2
ttitle -
center 'Index Keys Summary' skip 2
col uniq format a10 heading 'Uniqueness' justify c trunc
col indname format a40 heading 'Index Name' justify c trunc
col colname format a25 heading 'Column Name' justify c trunc
break -
on indname skip 1 -
on uniq
select
ind.uniqueness uniq,
ind.owner||'.'||col.index_name indname,
col.column_name colname
from
dba_ind_columns col,
dba_indexes ind
where
ind.owner = upper('&ixowner')
and
ind.table_name = upper('&tabname')
and
col.index_owner = ind.owner
and
col.index_name = ind.index_name
order by
col.index_name,
col.column_position
/
undef ixowner
undef tabname
set verify on
==============
Sample Output:
==============
Index Keys Summary
Uniqueness Index Name Column Name
---------- ---------------------------------------- ----------------------
UNIQUE SCOTT.S_EMP_ID_PK ID
UNIQUE SCOTT.S_EMP_USERID_UK USERID
Disclaimer :
EXCEPT WHERE EXPRESSLY PROVIDED OTHERWISE, THE INFORMATION, SOFTWARE,
PROVIDED ON AN "AS IS" AND "AS AVAILABLE" BASIS. ORACLE EXPRESSLY DISCLAIMS
ALL WARRANTIES OF ANY KIND, WHETHER EXPRESS OR IMPLIED, INCLUDING, BUT NOT
LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR
PURPOSE AND NON-INFRINGEMENT. ORACLE MAKES NO WARRANTY THAT: (A) THE RESULTS
THAT MAY BE OBTAINED FROM THE USE OF THE SOFTWARE WILL BE ACCURATE OR
RELIABLE; OR (B) THE INFORMATION, OR OTHER MATERIAL OBTAINED WILL MEET YOUR
EXPECTATIONS. ANY CONTENT, MATERIALS, INFORMATION OR SOFTWARE DOWNLOADED OR
OTHERWISE OBTAINED IS DONE AT YOUR OWN DISCRETION AND RISK. ORACLE SHALL HAVE
NO RESPONSIBILITY FOR ANY DAMAGE TO YOUR COMPUTER SYSTEM OR LOSS OF DATA THAT
RESULTS FROM THE DOWNLOAD OF ANY CONTENT, MATERIALS, INFORMATION OR SOFTWARE.
ORACLE RESERVES THE RIGHT TO MAKE CHANGES OR UPDATES TO THE SOFTWARE AT ANY
TIME WITHOUT NOTICE.
Limitation of Liability :
IN NO EVENT SHALL ORACLE BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
SPECIAL OR CONSEQUENTIAL DAMAGES, OR DAMAGES FOR LOSS OF PROFITS, REVENUE,
DATA OR USE, INCURRED BY YOU OR ANY THIRD PARTY, WHETHER IN AN ACTION IN
CONTRACT OR TORT, ARISING FROM YOUR ACCESS TO, OR USE OF, THE SOFTWARE.
SOME JURISDICTIONS DO NOT ALLOW THE LIMITATION OR EXCLUSION OF LIABILITY.
ACCORDINGLY, SOME OF THE ABOVE LIMITATIONS MAY NOT APPLY TO YOU.
No comments:
Post a Comment