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 Partitioned Indexes

Oracle Tips by Burleson
 

Partitioned indexes in Oracle8, Oracle8i, and Oracle also will require monitoring by the DBA. The DBA_IND_PARTITIONS view is almost identical to the DBA_TAB_PARTITIONS view, with the exception of the index/table-specific statistics. The scripts shown here are examples that you can modify for your own needs. The first script, ind_part.sql, is shown in Source 10.23, shows partition file parameters.. The output from this script is shown in Listing 10.22.

SOURCE 10.23 Example of script to monitor index partition files.

rem
rem Name: ind_part.sql
rem Function : Report on partitioned index structure
rem History: MRA 6/14/97 Created
rem MRA 5/10/99 Updated for Subpartitions
rem
COLUMN index_owner          FORMAT a10 HEADING 'Owner'
COLUMN index_name           FORMAT a15 HEADING 'Index'
COLUMN partition_name       FORMAT a15 HEADING 'Partition'
COLUMN subpartition_name    FORMAT a15 HEADING 'Sub|Partition'
COLUMN tablespace_name      FORMAT a15 HEADING 'Tablespace'
COLUMN high_value           FORMAT a10 HEADING 'Partition|Value'
COLUMN status               FORMAT a10 Heading 'Status'
SET LINES 130
START title132 'Index Partition Files'
BREAK ON index_owner ON index_name
SPOOL rep_out/&&db/ind_part.lis
SELECT
     a.index_owner,           
     a.index_name,            
     a.partition_name,        
     a.high_value,    
     b.subpartition_name,       
     b.tablespace_name,
     b.logging,
     b.status
FROM sys.dba_ind_partitions a, sys.dba_ind_subpartitions b
WHERE a.owner=b.owner
   AND a.index_name=b.index_name
   And a.partition_name=b.partition_name
ORDER BY a.index_owner,a.index_name,a.partition_name,
         b.subpartition_position
/
SPOOL OFF

LISTING 10.22 Example of output from the index partition file script.

Date: 06/14/97                                        Page:  1
Time: 08:51 PM          Index Partition Files         SYSTEM
                           ORTEST1 database                                                               

                                Partition
Owner   Index         Partition Value    Tablespace      LOG Status
------- ------------- --------- -------- --------------- --- ------
SYSTEM  PART_IND_TEST TEST_P1         10 RAW_DATA        YES VALID
                      TEST_P2         20 RAW_DATA        YES VALID
                      TEST_P3         30 RAW_DATA        YES VALID

3 rows selected.

The DBA_IND_PARTITIONS view also provides the statistics and storage characteristics for partitioned indexes. Source 10.24 is an example of a script to use for monitoring some of these statistics. The output from this script resembles the report in Listing 10.23.

SOURCE 10.24 Example of script to report on partitioned index storage and statistics.

rem
rem NAME:      ind_pstor.sql
rem FUNCTION: Provide data on partitioned index storage charcacteristics
rem HISTORY: MRA 6/13/97 Created
rem     
COLUMN owner             FORMAT a6         HEADING 'Owner'
COLUMN index_name        FORMAT a14        HEADING 'Index'
COLUMN partition_name    FORMAT a9         HEADING 'Partition'
COLUMN tablespace_name   FORMAT a11        HEADING 'Tablespace'
COLUMN pct_free          FORMAT 9999       HEADING '%|Free'
COLUMN ini_trans         FORMAT 9999       HEADING 'Init|Tran'
COLUMN max_trans         FORMAT 9999       HEADING 'Max|Tran'
COLUMN initial_extent    FORMAT 9999999    HEADING 'Init|Extent'
COLUMN next_extent       FORMAT 9999999    HEADING 'Next|Extent'
COLUMN max_extent                          HEADING 'Max|Extents'
COLUMN pct_increase      FORMAT 999        HEADING '%|Inc'
COLUMN distinct_keys     FORMAT 9999999    HEADING '#Keys'
COLUMN clustering_factor FORMAT 999999     HEADING 'Clus|Fact'
SET LINES 130
START title132 'Index Partition File Storage'
BREAK ON index_owner on index_name
SPOOL rep_out/&&db/ind_pstor.lis
SELECT
     index_owner,
     index_name,
     tablespace_name,
     partition_name,
     pct_free,
     ini_trans,
     max_trans,
     initial_extent,
     next_extent,
     max_extent,
     pct_increase,
     distinct_keys,
     clustering_factor
FROM sys.dba_ind_partitions
ORDER BY index_owner,index_name
/
SPOOL OFF

LISTING 10.23 Example of output from the partitioned index storage script.

Date: 06/14/97
Page:   1
Time: 09:25 PM                  Index Partition File Storage                       SYSTEM                               ORTEST1 database

                                          % Init  Max   Init   Next    Max   %
Clus
Owner  Index      Tablespace Partition Free Tran Tran Extent Extent Extents Inc
#Keys  Fac
------ ---------- ---------- --------- ---- ---- ---- ------ ------ ------- --- SYSTEM P_IND_TEST   RAW_DATA   TEST_P1   10    2  255  20480  20480     249  50     0    0      
                    RAW_DATA   TEST_P2   10    2  255  20480  20480     249  50     0    0
                    RAW_DATA   TEST_P3   10    2  255  20480  20480     249  50     0    0              

3 rows selected.

The index subpartitions also have statistics collected against them,  into the DBA_IND_SUBPARTITIONS view. The report in Source 10.25 will generate a listing of some of these statistics. Feel free to modify it as needed to monitor the statistics you feel are important. The output from the script in Source 10.25 resembles the report in Listing 10.24.

SOURCE 10.25 Example of script to report on subpartitioned index storage and statistics.

rem
rem NAME:      ind_subpstor.sql
rem FUNCTION: Get data on subpartitioned index charcacteristics
rem HISTORY: MRA 5/10/99 Created
rem     
COLUMN owner             FORMAT a6         HEADING 'Owner'
COLUMN index_name        FORMAT a14        HEADING 'Index'
COLUMN partition_name    FORMAT a9         HEADING 'Partition'
COLUMN subpartition_name FORMAT a9         HEADING 'Sub|Partition'
COLUMN tablespace_name   FORMAT a11        HEADING 'Tablespace'
COLUMN pct_free          FORMAT 9999       HEADING '%|Free'
COLUMN ini_trans         FORMAT 9999       HEADING 'Init|Tran'
COLUMN max_trans         FORMAT 9999       HEADING 'Max|Tran'
COLUMN initial_extent    FORMAT 9999999    HEADING 'Init|Extent'
COLUMN next_extent       FORMAT 9999999    HEADING 'Next|Extent'
COLUMN max_extent                          HEADING 'Max|Extents'
COLUMN pct_increase      FORMAT 999        HEADING '%|Inc'
COLUMN distinct_keys     FORMAT 9999999    HEADING '#Keys'
COLUMN clustering_factor FORMAT 999999     HEADING 'Clus|Fact'
COLUMN num_rows          FORMAT 9999999    HEADING 'Number|Rows'
SET LINES 130
START title132 'Index SubPartition File Storage'
BREAK ON index_owner on index_name
SPOOL rep_out/&&db/ind_pstor.lis
SELECT
     index_owner,
     index_name,
     partition_name,
     sub_partition_name,
     tablespace_name,
     pct_free,
     ini_trans,
     max_trans,
     initial_extent,
     next_extent,
     max_extent,
     pct_increase,
     distinct_keys,
     clustering_factor,
     num_rows
FROM sys.dba_ind_subpartitions
ORDER BY index_owner,index_name,partition_name,subpartition_position
/
SPOOL OFF

LISTING 10.24  Example of output from the subpartitioned index storage script.

Date: 06/14/97                                                                         Page:   1   

Time: 09:25 PM                     Index SubPartition File Storage                       SYSTEM        

                                        ORTEST1 database                                                          

                                          % Init  Max   Init   Next    Max   %        Clus
Owner  Index      Tablespace Partition Free Tran Tran Extent Extent Extents Inc #Keys  Fac
------ ---------- ---------- --------- ---- ---- ---- ------ ------ ------- --- SYSTEM P_IND_TEST   RAW_DATA   TEST_P1   10    2  255  20480  20480     249  50     0    0
                    RAW_DATA   TEST_P2   10    2  255  20480  20480     249  50     0    0
                    RAW_DATA   TEST_P3   10    2  255  20480  20480     249  50     0    0              

3 rows selected.

Monitoring Functional Indexes

The concept of a functional index was added to Oracle8i. A functional index uses a function or collection of functions to operate on its column, thus allowing the same function or collection of functions to be applied in the WHERE clause of a SELECT that uses the index. A simple example would be a functional index using UPPER on a name field. This would allow selects of the form:

SELECT * FROM emp WHERE UPPER(last_name)='AULT';

The new functionality will simplify table and application design tremendously in applications that retrieve data based on UPPER, LOWER, SOUNDEX, and other function-based queries.

The DBA will want to know about, and track, the use of function-based indexes in his or her database. The DBA_INDEXES view has a new column that makes this easier, the FUNCIDX_STATUS column. The FUNCIDX_STATUS column contains NULL if the index is not a function-based index, ENABLED if it is a function-based index and is ready for use, and DISABLED if it is a function-based index that is disabled and can’t be used. If the value in FUNCIDX_STATUS is not NULL, a join to the DBA_IND_EXPRESSIONS view will provide the information on the expression used to create the function-based column in the index. The script in Source 10.26 demonstrates this type of report; an example of the output from this script is shown in Listing 10.25.

SOURCE 10.26 Example of script to report on functional indexes.

rem
rem NAME:      ind_func.sql
rem FUNCTION: Get data on functional index charcacteristics
rem HISTORY: MRA 5/12/99 Created
rem     
COLUMN owner             FORMAT a6         HEADING 'Owner'
COLUMN index_name        FORMAT a14        HEADING 'Index'
COLUMN table_name        FORMAT a20        HEADING 'Table'
COLUMN column_expression FORMAT a80 WORD_WRAPPED HEADING 'Expression'
SET LINES 130
START title132 'Functional Index Report'
BREAK ON index_owner on index_name
SPOOL rep_out/&&db/ind_func.lis
SELECT
     Index_owner,
     index_name,
     table_name,
     column_expression
FROM
     Dba_ind_expressions
WHERE
     Index_owner LIKE '%&&owner%'
     And index_name like '%&&index%'
ORDER BY
     Index_owner,index_name,column_position;
SPOOL OFF
TTITLE OFF

LISTING 10.25 Example of functional index report.

Date: 06/14/97
Page:   1
Time: 09:45 PM            Functional Index Report                       SYSTEM
                            ORTEST1 database

Owner      Index            Table             Expression
---------- ---------------- ----------------- -------------------------------
TELE_DBA   DEC_CLIENTSV8i   CLIENTSV8i        DECODE ("CREATION_SY_USER",1,'B
                                              OSS',2,'Manager',3,'Clerk','Ev
                                              eryone else')
 

Monitoring Domain Indexes

Also introduced in Oracle8i was the concept of extensible indexing, also known as domain indexing. A domain index is usually used in cartridge development. In fact, a domain index is called so because it is used only within the domain of its parent cartridge. A domain index extends the basic types of hash, bitmapped, and B-tree indexes by allowing the developer to create his or her own index methods and apply them to a specific type of data set.

An example of the use of domain indexing would be the use of R-tree indexes for spatial data. A domain index is based on the concept of an INDEXTYPE, which, like a User Defined Type (UDT), is created and maintained by the user. In order to use a domain index, a data cartridge that implements its structures, methods, and types must be created.

Note: The domain indexes in Oracle8i are indicated by a non-NULL value in the DOMIDX_STATUS and DOMIDX_OPSTATUS columns in the DBA_INDEXES view. I assume a join can be based on either the INDEX_NAME and INDEXTYPE_NAME in the DBA_INDEXES and DBA_INDEXTYPES table supplemented by the OWNER columns in each or the INDEX_TYPE and INDEXTYPE_NAME columns (even though they don’t match in size). I say “assume,” because this topic is beyond the scope of this book, so I leave it to the DBA who is involved in a cartridge development effort to actually create the reports required based on the supplied join data.

 

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