How to Find Out How Much Space an Index is Using
PURPOSE
This article provides select statements to show the actual usage of blocks
within an index. This gives an idea of how 'full' an index is and allows
a dba to adjust next extent sizes etc.
SCOPE & APPLICATION
For DBA's requiring to understand how much space is currently being used
within an index.
In order to gather the necessary index statistics, you need to 'validate
the structure' of the index. This is achieved using the following command:
analyze index validate structure; [@more@]PURPOSE
This article provides select statements to show the actual usage of blocks
within an index. This gives an idea of how 'full' an index is and allows
a dba to adjust next extent sizes etc.
SCOPE & APPLICATION
For DBA's requiring to understand how much space is currently being used
within an index.
In order to gather the necessary index statistics, you need to 'validate
the structure' of the index. This is achieved using the following command:
analyze index validate structure;
NOTE: In Oracle V6 the validate index command performed the same function.
Analyze index validate structure checks the structure of the index and
populates a table called index_stats. The command (in this form) does not
gather statistics for use by the Cost Based Optimizer. The index_stats table
can only hold 1 row at a time. So, if you are planning to store index data for
multiple indexes or for historical comparison purposes, you will need to
insert the data into a more permanent table. Statistics are also lost at the
end of each session.
This analyze command only checks the structure of the index and populates the
index_stats table. It does not gather statistics for use by the Cost Based
Optimizer.
Once you have gathered the statistics, they can be retrieved as follows:
column name format a15
column blocks heading "ALLOCATED|BLOCKS"
column lf_blks heading "LEAF|BLOCKS"
column br_blks heading "BRANCH|BLOCKS"
column Empty heading "UNUSED|BLOCKS"
select name,
blocks,
lf_blks,
br_blks,
blocks-(lf_blks+br_blks) empty
from index_stats;
Example output
==============
SQL> analyze index draw1 validate structure;
Index analyzed.
SQL> select name, blocks, lf_blks, br_blks, blocks-(lf_blks+br_blks) empty
2 from index_stats;
ALLOCATED LEAF BRANCH UNUSED
NAME BLOCKS BLOCKS BLOCKS BLOCKS
--------------- ---------- ---------- ---------- ----------
DRAW1 433 426 6 1
It is also possible to gather statistics on the use of space within the
btree itself:
select name,
btree_space,
used_space,
pct_used
from index_stats;
Example output
==============
SQL> select name, btree_space, used_space, pct_used
2 from index_stats;
NAME BTREE_SPACE USED_SPACE PCT_USED
--------------- ----------- ---------- ----------
DRAW1 810624 236284 30
--------------------------------------------------------------------------
Oracle Worldwide Customer Support
This article provides select statements to show the actual usage of blocks
within an index. This gives an idea of how 'full' an index is and allows
a dba to adjust next extent sizes etc.
SCOPE & APPLICATION
For DBA's requiring to understand how much space is currently being used
within an index.
In order to gather the necessary index statistics, you need to 'validate
the structure' of the index. This is achieved using the following command:
analyze index
This article provides select statements to show the actual usage of blocks
within an index. This gives an idea of how 'full' an index is and allows
a dba to adjust next extent sizes etc.
SCOPE & APPLICATION
For DBA's requiring to understand how much space is currently being used
within an index.
In order to gather the necessary index statistics, you need to 'validate
the structure' of the index. This is achieved using the following command:
analyze index
NOTE: In Oracle V6 the validate index command performed the same function.
Analyze index validate structure checks the structure of the index and
populates a table called index_stats. The command (in this form) does not
gather statistics for use by the Cost Based Optimizer. The index_stats table
can only hold 1 row at a time. So, if you are planning to store index data for
multiple indexes or for historical comparison purposes, you will need to
insert the data into a more permanent table. Statistics are also lost at the
end of each session.
This analyze command only checks the structure of the index and populates the
index_stats table. It does not gather statistics for use by the Cost Based
Optimizer.
Once you have gathered the statistics, they can be retrieved as follows:
column name format a15
column blocks heading "ALLOCATED|BLOCKS"
column lf_blks heading "LEAF|BLOCKS"
column br_blks heading "BRANCH|BLOCKS"
column Empty heading "UNUSED|BLOCKS"
select name,
blocks,
lf_blks,
br_blks,
blocks-(lf_blks+br_blks) empty
from index_stats;
Example output
==============
SQL> analyze index draw1 validate structure;
Index analyzed.
SQL> select name, blocks, lf_blks, br_blks, blocks-(lf_blks+br_blks) empty
2 from index_stats;
ALLOCATED LEAF BRANCH UNUSED
NAME BLOCKS BLOCKS BLOCKS BLOCKS
--------------- ---------- ---------- ---------- ----------
DRAW1 433 426 6 1
It is also possible to gather statistics on the use of space within the
btree itself:
select name,
btree_space,
used_space,
pct_used
from index_stats;
Example output
==============
SQL> select name, btree_space, used_space, pct_used
2 from index_stats;
NAME BTREE_SPACE USED_SPACE PCT_USED
--------------- ----------- ---------- ----------
DRAW1 810624 236284 30
--------------------------------------------------------------------------
Oracle Worldwide Customer Support
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/421207/viewspace-1003515/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- how much are ghd hair straightenersAI
- How to find dependency
- How much coca搭配 僅大寫
- ejb object too much ,how server working??ObjectServer
- [轉]How to release space from databaseDatabase
- How to find Current open Cursors in OracleOracle
- how much 搭配用法 coca 大寫小寫混合
- How to Determine When an Index Should be Rebuilt?IndexUI
- How to find Master Node in Oracle RAC(zt)ASTOracle
- How to find the UDID for an iPhone/iPod touchiPhone
- DB2 PL/SQL How much reorganisations are recommended on a specific schemaDB2SQL
- How to Specify an INDEX Hint oracle官方文件IndexOracle
- How does SAP freelancer find a project that is not tough?Project
- How do you find that an operation mode switch occurred?
- How to get the description of blast hit using blastdbcmd?AST
- How to develop locally a Laravel app using LaragondevLaravelAPPGo
- Run Out Of Space On UNDO Tablespace Using DataPump Import/ExportImportExport
- How To Using Flashback Data Archive (Oracle Total Recall)HiveOracle
- How to Restore the Database Using AMDU after Diskgroup CorruptionRESTDatabase
- How to check whether the current database in using Oracle optionsDatabaseOracle
- How to release space from database( in other words: resize datafile ) (zt)Database
- How to release space from database( in other words: resize datafile ) 【zt】Database
- Python, pandas: how to sort dataframe by index// Merge two dataframes by indexPythonIndex
- How to Find Sessions Generating Lots of Redo or Archive logsSessionHive
- How to Quickly Create a Copy of a Table using Transact-SQLUISQL
- how to run demo city bars using sencha architect
- How to copy a datafile from ASM to a file system not using RMANASM
- How To Export a Concurrent Program and Executable Using Fndload ?Export
- How to free space from an ASM diskgroup? (Doc ID 1553744.1)ASM
- SQL: How to Find Sessions Generating Lots of Redo or Archive logsSQLSessionHive
- How to Set Device Name Using UDEV on Oracle Linux 7.1devOracleLinux
- How to serve uncommon file in WebDAV using IIS in Win ServerWebServer
- How To Monitor Remote Windows Machine Using Nagios on LinuxREMWindowsMaciOSLinux
- How to Monitor and Log Network Traffic on Linux Using vnStatLinux
- How To Recreate A Database Using TTS (Transportable TableSpace) [ID 733824.1]DatabaseTTS
- How to Find which Session is Holding a Particular Library Cache LockSession
- How to See Supplier Contact Information Using SQL in R12ORMSQL
- How to create the Gold gold using RGB color values All In OneGo