All About Indexes
Introduction
Partitioned Indexes
Index-Only Tables (IOT)
Bitmap Indexes
Reverse Key Indexes
Function-Based Indexes
Descending Indexes
When to Rebuild Indexes
Monitoring Index Usage
Index Usage without
"alter index.... monitoring usage"
Get Index Information
Introduction
THE COST OF INDEXES
Every time we change the data in a table, we may have to modify
some related index information. The more indexes we create on a
table, the more work we may have to do as we modify the table.
Clearly we need to be careful when we define the set of indexes we
want on a table. If we are too extravagant with creating indexes,
we may pay a huge penalty as we load and modify the data – and it
doesn't matter how quickly our queries can run if we can’t get the
data into the system in time. But it's not simply the increased
cost of the DML that worries us when we have indexes on our
tables. The very act of changing the data could make our indexes
less effective, and this is an issue that needs a little
consideration. Fortunately, many indexes have a 'natural' steady
state, so we often need only look out for special cases.
Type
of Indexes
1-
Partitioned Indexes
Like tables, indexes can also be partitioned; but with indexes you
have a more options because the underlying table might or might not
also be partitioned. The objective of this type of index is to
separate the index into smaller partitions, just as we do now for a
database table. There are essentially two different types of
partitioned indexes available:
- Global indexes--These are created in a manner different from
the underlying partitioning of the table that is indexed.
- Local indexes--These are partitioned in the same manner as the
underlying table partitioning. Whenever possible, create local
indexes.
Global
Indexes
To create a global partitioned index, use the CREATE
INDEX parameter GLOBAL. This specifies that the
index will be a global index. Further partitioning of the index is
accomplished by using the following parameters:
·
GLOBAL--This
parameter specifies a global partitioned index.
·
PARTITION part_name--This
parameter is used to identify the partition. If you do not specify
the partition name, a default name will be provided. It is not
usually necessary to provide the partition name.
·
VALUES LESS THAT--This
parameter is used to specify the range that is allocated for that
particular partition in the same way as the partition was specified
in the CREATE TABLE statement (discussed yesterday).
NOTE: The last partition
should contain the keyword MAXVALUE for its
range.
Creating a partitioned index.
CREATE INDEX "ETW".dogs_ix1 ON DOGS(ID)
PARTITION BY RANGE (ID)
PARTITION pt1 VALUES LESS THAN
(`1000') TABLESPACE ts1,
PARTITION pt2 VALUES LESS THAN
(MAXVALUE) TABLESPACE ts2);
ANLYSIS:
This create two partitions, the first holding values of ID that are
less than 1,000, the second holding the remaining values of ID. If
you do not specify the partition name, as is the case here, a
default name will be provided.
While this does offer a great deal of flexibility, it is not without
cost. Since global partitioned indexes are not directly
related to any single table partition, operations that affect any
partition in the table can render all global partitioned indexes
unusable. Specifically the operations:
ADD (HASH) - COALESCE (HASH) – DROP – EXCHANGE – MERGE -
MOVE -SPLIT - TRUNCATE
Local
Indexes
In contrast to the global index, a
local partitioned index is individually created on each partition.
If you specify a local partitioned index, Oracle automatically
maintains the index's partitioning along with that of the
underlying table.
Local partitioned indexes are created through the use of the LOCAL
parameter with the CREATE INDEX statement. It is unnecessary to
provide partitioning information because the underlying table
partitioning will be used. A local index can be created with the
following syntax:
CREATE INDEX dogs_ix1 ON DOGS (ID) LOCAL;
Because the index is local, all partition changes to the table will
be automatically reflected on the index partitions as well.
Local partitioned indexes have some inherent advantages that are
similar to the advantages you get from partitioned tables. These
advantages include the following:
• Because the index exists entirely on one
partition, any maintenance operations affect only that one
partition.
• The Oracle optimizer can use the local index to
generate better query plans based on the fact that a local index is
used.
• If a partition is lost and must be recovered,
only the data and index for that particular partition needs to be
recovered. With a global index, the entire index would need
recovery.
2-
Index-Only Tables (IOT)
Many systems contain several small
tables (1 to 3 columns) where all of the elements form the primary
key. However, there exists an extremely efficient way to create
such tables, by using a B*-Tree structure. An index-organized
table, that is a table with the same physical structure as an
index, allows us to do exactly that. In an index-organized table,
the database engine will place the data values in a « table
» segment, but with a B*-tree structure. An index-only table
is similar to an index, but whereas an index contains the primary
key value and a ROWID pointing to where the data is kept, the
index-only table stores the column data in the leaf block of the
index.
Because the leaf blocks of the Oracle index are traditionally very
small and tightly packed, there can be some drawbacks to having
large rows stored there. Oracle has developed a way to compensate
for this: If rows become too large (by a set threshold), the row
data is stored in an overflow area as specified in the CREATE
TABLE statement. This creates storage more like the traditional
index and table relationship.
An index-only table contains the same structure as the Oracle
B*-tree index. Only the leaf blocks have changed. Index-only
tables have many of the attributes of both indexes and tables, but
there are a few exceptions:
• Because it is part index and part table, no
other indexes can be added to the index-only table.
• The UNIQUE constraint is not allowed on an
index-only table.
• A trigger can be added to the index-only
table.
• An index-only table cannot be stored in a
cluster.
• Replication is not supported at this time.
As you can see, there are some restrictions on index-only tables,
but there is also a great deal of benefits.
When performing inserts, there is a performance improvement, since
only a single segment needs to be updated, which is the table
itself. Data retrieval is also faster since the optimizer
processes the table just as if it were an index. Furthermore, one
read operation is saved since ORACLE doesn't have to read the
index prior to performing the read in the table.
When to Use Index-Only Tables
Index-only tables are very useful whenever data is
always accessed via the primary key index. If this is the case
with your data, the index-only table will cut down on the space
used by both the index and the table (by combining them) and
improve performance. Performance is improved because, by the time
the ROWID would have been retrieved, you have the data.
Tables that are not accessed via the primary key value are not
good candidates for index-only tables. Also, tables whose primary
key values are updated and tables that have frequent insertions
are not good candidates for index-only tables.
How to Create Index-Only Tables
Index-only tables are created with the CREATE TABLE command; the
ORGANIZATION INDEXED qualifier is used to identify the table as
index-only. The following qualifiers are used in creating
index-only tables:
• ORGANIZATION INDEXED--This qualifier specifies
an index-only table organization.
• OVERFLOW TABLESPACE ts_name--This qualifier
specifies the overflow tablespace name.
• PCTTHRESHOLD threshold--This qualifier
specifies the percent of a block that a row must be larger than in
order to be offloaded to the overflow tablespace.
Creating an index-only table with the CREATE TABLE command.
CREATE TABLE DOGS (
ID NUMBER,
NAME VARCHAR2(40),
OWNER_ID NUMBER,
BREED_ID NUMBER,
RANK NUMBER NULL,
NOTES VARCHAR2(80)
PRIMARY KEY(ID) )
ORGANIZATION INDEXED PCTTHRESHOLD 40
OVERFLOW TABLESPACE DOGS2
TABLESPACE DOGS;
ANLYSIS:
This specifies that the index-only tablespace be created on the
DOGS tablespace, whereas the overflow tablespace used is DOGS2.
NOTE: It is necessary to specify the PRIMARY KEY value when
creating an index-only table. This is the value on which the index
is created.
Unfortunately, there are also some
disadvantages to using index-organized tables. For example, it is
not possible to create additional indexes since the occurrences of
this type of table do not have a ROWID. It is also impossible to
add new columns or to modify existing columns. The message «
ORA-25182 feature not currently available for index-organized
table » is used to alert us to these restrictions. Another
factor to consider is the case where updating key values is
allowed. ORACLE must delete the old record and do an insert based
on the new key value, that is relocate a leaf of the B*-tree to
another location in the B*-tree. The net result is 2 SQL
operations. Therefore, one should weight the advantages and
disadvantages before choosing this type of table, even though it
is relatively easy to convert an index-organized table back to a
conventional format using create table as and then doing an
Import/Export to that table.
3- Bitmap Indexes
Another type of index available the
bitmap index. With the traditional index you saw earlier, Oracle
uses a B*-tree method to traverse the index to find the leaf
block. With a bitmap index, a bitmap of ROWIDs is kept; this
bitmap indicates which rows correspond to the index item. If the
bit is set, this indicates that the corresponding row contains the
key value; if the bit is not set, the opposite is true.
As you can probably tell, bitmap indexes can probably be quite
useful under the right circumstances and useless overhead
otherwise.
When to Use Bitmap Indexes
As you can probably guess, the bitmap index works
well on items with low cardinality. Low cardinality means there is
a small amount of variance in the possible values stored in that
column. For example, the column representing the sex of the dog is
said to have low cardinality because only two values are possible.
Other column types that might have low cardinality include
• Marital status
• Account status (good or bad)
• Sales region (if there are only a few)
• Rank (if there are only a few)
• Special notes (whether there is a note)
With columns that have low cardinality, the bitmap index can
greatly improve performance. Columns with high cardinality are not
candidates for bitmap indexes.
Locking issues affect data manipulation operations in Oracle. As a
result, bitmapped indexes are not appropriate for OLTP
applications that have a high level of concurrent insert, update
and delete operations. Concurrency is usually not an issue in a
data-warehousing environment where the data is maintained by bulk
loads, inserts and updates.
In addition, bitmapped index maintenance is deferred until the end
of the bulk DML operation. If 100 rows are inserted into a table,
the inserted rows are placed into a sort buffer and the updates of
all 100-index entries are applied as a group. As a result,
bitmapped indexes are appropriate for most decision support
applications (even those that have bulk updates applied on a
regular basis).
Mass updates, inserts and delete will run faster if you drop the
bitmapped indexes, execute the DML and recreate the bitmapped
indexes when the DML completes. Run timings using the straight DML
and compare it to the total time consumed by the drop bitmapped
index/execute DML/recreate bitmapped index process.
How to
Create Bitmapped Indexes
A bitmap index is created with the CREATE INDEX
command with the BITMAP qualifier. To create a bitmap index on
the SEX field in the DOGS table, you can use the following
syntax:
CREATE BITMAP INDEX
dogs_bx1 ON DOGS (SEX);
This simple statement will create the bitmap index on the column
specified.
4- Reverse Key Indexes
When a column that contains an incrementally
increasing value (like those provided by sequences) is indexed,
there is a very poor distribution of that value across the B-tree,
and hot spots can be introduced into the index. Reverse-key
indexes can help eliminate these hot spots by providing a much wider
distribution of values than would occur in a regular B-tree index.
In this situation, simultaneous inserts requested by the users are
done in the same leaf of the index. This will generate contention on
the same Oracle block buffer. This problem is exacerbated when
several tasks are initiated in parallel, all processing updates
against the database. Another problem is the phenomena of «
sliding » when the system deletes older numbers and inserts
new ones. In this case, the index becomes increasingly large as the
space occupied by the deleted index entries never gets reused.
The answer to this situation is the reverse-key index whereby
different index leafs is used, even if the key sequence is
chronological.
There are a few cases where reverse key indexes can help to improve
performance. One is in RAC environments. If you have a column
populated by an increasing sequence the new entries come to the same
blocks when you are inserting rows. If you have many concurrent
sessions inserting rows from different RAC instances then you will
have a contention for the same index blocks between nodes. If you
use reverse key indexes in this case then the new index entries will
go to different blocks and contention will be reduced.
In single instance databases there is also a case where reverse key
indexes can be helpful. If you have a column populated by an
increasing sequence, you delete some old rows from the table and you
do not do range scans on that column and you have contention issues
on index blocks, reverse key indexes can be considered. The reverse
key index will scatter the entries across different blocks during
inserting and your many concurrent sessions will not have index
block contention issues.
If you are deleting some old rows, the blocks from a normal index on
that column will have some used and some empty space in them, but
they will not be put on the freelist because they are not completely
free. That empty space will not be used because the sequence values
are always increasing and they will not go to those old blocks
because of that. You will be able to use that space for different
values with reverse key indexes.
One of the things to be careful about reverse key indexes is that
you cannot perform range scans on them. Because the entries are
stored as reversed you lose the capability to range scan on that
index.
To change an existing index as a reverse key index you can use the
alter index statement.
alter index indexname rebuild
reverse;
After this change the index size was reduced down to 16MB from
250MB. This change got rid of the buffer busy waits on the index
blocks. The program run time was reduced from about 40 minutes to
about 25 minutes.
Example
Let’s say you are using a sequence to populate a primary key
field. Sequences generate numbers like 1, 2, 3, 4, 5,
..... Everyone is hitting the same side of the index -- from
every node. You have a terribly hot spot there where all
insertions need the same part of the index to work on.
A reverse key index is an attempt to spread the load out over the
entire index structure so people are hitting the left, the right,
the middle, all over the index -- not just adding an increasing
number to the one side.
Before the index entry is added to the index structure, all of its
bytes are "reversed" -- so yes, this affects all elements of the
index structure.
It is as simple as that -- instead of all inserts hitting one side
of the index, producing mass contention on one side of the index
-- a reverse key index will tend to distribute the inserts
throughout the entire structure more evenly.
How to Create Reverse Key Indexes
CREATE INDEX my_index ON
my_table (my_fields) REVERSE;
To make an existing index a reverse index use:
ALTER INDEX index_name
REBUILD REVERSE;
Substitute no reverse if you want to rebuild you index as a
non-reversed one.
No options are without a certain cost. While reverse-key
indexes can help in the distribution of data across an index, this
very strength can be a weakness. Since the actual
(non-reversed) values of the index are not stored sequentially in
the index, index range-scans cannot be performed. Only
fetch-by-key and full index scans can be performed on a
reverse-key index. Therefore, it is not a good idea to build
a reverse-key index on a column that might use range-scans.
5- Function Based
Indexes
Function based indexes are really going to
improve the performance of all those queries which use functions
such as SUM, AVG in the select or when the where clause includes
some kind of other function such as some mathematical calculation on
two columns. Suppose you have an application that routinely selects
the sum of the two columns SALARY and BONUS from the PAYMENTS table.
In the past, such a select may have been fairly slow, because of the
calculation being performed on the select. Now, however, the
calculation is stored as part of the index, making the overall
selection process that much quicker.
CREATE INDEX my_ind ON
payments (salary + bonus);
So, this query would now utilize the index:
SELECT employee_id
FROM payments
WHERE (salary + bonus) >
1000;
You need to enable function-based indexes before you can use them,
as follows:
ALTER SESSION SET
QUERY_REWRITE_ENABLED = true
ALTER SESSION SET
QUERY_REWRITE_INTEGRITY = trusted
Or
QUERY_REWRITE_INTEGRITY =
TRUSTED
QUERY_REWRITE_ENABLED = TRUE
Also, to use a function-based index:
They must be analyzed before it can be used
The query must be guaranteed not to need any NULL
values from the indexed expression, since NULL values are not stored
in indexes.
6- Descending Indexes
Descending indexes are a special type of
function-based index, in which index entries are sorted in
descending order. Using the DESC clause does this. When
creating an index, as in the example below:
create index ix_desc on
phonebook2(zip desc);
There are a couple special considerations when using descending
indexes:
• Descending indexes cannot be used on reverse
indexes
• The DESC option is ignored if used in the
creation of bitmap indexes
When to Rebuild Indexes??
Oracle allows you to rebuild your indexes while online. In the past,
creating or rebuilding the index required a full lock on the table.
On a large table, this could mean that an application is unusable
for several hours. Now, however, Oracle allows you to create or
rebuild the index while users can still perform the full range of
data processes. To do this, Oracle creates the index structure
before populating it. While populating, all changes to the table are
recorded in a journal table. As the index is completed, the journal
table changes are then built in.
In order to understand what we must do with the index, we must first
get an idea of the current state of the index. This can be
accomplished by using the ANALYZE INDEX VALIDATE STRUCTURE command.
Normally, the ANALYZE INDEX command creates either computed or
estimated statistics for the index that can be seen in the
DBA_INDEXES view. This action may produce unintentional side
effects, especially if the index has not previously been analyzed.
The VALIDATE STRUCTURE command can be safely executed without
affecting the optimizer. The VALIDATE STRUCTURE command populates
the SYS.INDEX_STATS table only. The SYS.INDEX_STATS table can be
accessed with the public synonym INDEX_STATS. The INDEX_STATS table
will only hold validation information for one index at a time. You
will need to query this table before validating the structure of the
next index.
If it is determined that the index needs to be rebuilt, this can
easily be accomplished by the ALTER INDEX REBUILD command. Although
not necessarily recommended, this command could be executed during
normal operating hours. Rebuilding the index uses the existing index
as a basis. The alternative is to drop and re-create the index.
Creating an index uses the base table as its data source that needs
to put a lock on the table. The index is also unavailable during
creation. Below is an example of ANALYZE INDEX VALIDATE STRUCTURE
and sample output from INDEX_STATS:
NOTE: validate structure tries to lock the table, and
fails with Oracle error 54 (resource busy and acquire with
NOWAIT specified) if anyone is currently updating the table.
If the validation takes a long time, anyone who then tries
to update the table is locked out.
|
ANALYZE INDEX
PK_shopping_basket VALIDATE STRUCTURE;
SELECT name, height, lf_rows,
lf_blks, del_lf_rows, distinct_keys, used_space,
(del_lf_rows/lf_rows)*100 as ratio
FROM INDEX_STATS;
NAME
HEIGHT
LF_ROWS LF_BLKS DEL_LF_ROW
DISTINCT_K USED_SPACE RATIO
-------------------------
--------- ---------- ---------- ---------- ---------- ----------
------
PK_SHOPPING_BASKET
2
1
3
1
1
65
10
The following INDEX_STATS columns are especially useful:
height refers to the
maximum number of levels encountered within the index. An index
could have 90 percent of the nodes at three levels, but excessive
splitting and spawning in one area of the index with heavy DML
operations could make nodes in that area to have more than three
levels.
lf_rows refers to the total
number of leafs nodes in the index.
del_lf_rows refers to the
number of leaf rows that have been marked deleted as a result of
table DELETES.
An index should be considered for rebuilding under any of the
following conditions:
- The deleted leaf rows should be
less than 25% of the total number of leaf rows. An
excessive number of deleted leaf rows indicates that a high number
of deletes or updates have occurred to the index column(s). The
index should be rebuilt to better balance the tree. The INDEX_STATS
table can be queried to determine if there are excessive deleted
leaf rows in relation to the total number of leaf rows. i.e. if
DEL_LF_ROWS / LF_ROWS > 0.25.
- If the 'HEIGHT' is greater than 3. For most indexes, the height of
the index will be quite low, i.e. one or two. An index with height
greater than four may need to be rebuilt as this might indicate a
skewed tree structure. This can lead to unnecessary database block
reads of the index.
- If the number of rows in the index ('LF_ROWS') is significantly
smaller than 'LF_BLKS' this can indicate a large number of deletes,
indicating that the index should be rebuilt.
- BLOCK_GETS greater than 5
Let’s look at an example:
ANALYZE
INDEX PK_item_basket VALIDATE STRUCTURE;
SELECT name, height, lf_rows,
del_lf_rows,
(del_lf_rows/lf_rows)*100
as ratio
FROM INDEX_STATS;
NAME
HEIGHT
LF_ROWS DEL_LF_ROW RATIO
------------------------------
---------- ---------- ---------- ----------
PK_ITEM_BASKET
1
235
74 31.4893617
In this example, the ratio of deleted leaf rows to total leaf rows
is clearly above 25%. This is a good candidate for rebuilding. Let’s
rebuild the index and examine the results.
ALTER INDEX
pk_item_basket REBUILD online;
While the new index is being built, it will exist simultaneously
with the old index in the database. Therefore, there must be enough
space available to store both the old index and the new index in the
database in order to use this command. When the index gets rebuilt,
the new index becomes available and the old index gets dropped. Its
space is reclaimed by the database.
There are some options available with this command. I usually use
the following syntax:
ALTER
INDEX pk_item_basket REBUILD parallel nologging compute statistics
tablespace INDX;
The PARALLEL clause causes Oracle to select a degree of parallelism
equal to the number of CPUs available on all participating instances
multiplied by the value of the PARALLEL_THREADS_PER_CPU
initialization parameter. The default value of
PARALLEL_THREADS_PER_CPU is 2 for Oracle 8i. So, if your database
server is a four-CPU box, your index will be rebuilt by eight
parallel processes, which would lead to a performance gain.
The NOLOGGING clause allows you to rebuild the index faster because
operations will not be logged in the redo log file.
The COMPUTE STATISTICS clause enables you to collect statistics at
relatively little cost during the rebuilding of an index.
The TABLESPACE clause specifies the tablespace where the rebuilt
index, index partition, or index subpartition will be stored. The
default is the default tablespace where the index or partition
resided before you rebuilt it.
ANALYZE INDEX pk_item_basket VALIDATE STRUCTURE;
SELECT
name, height, lf_rows, del_lf_rows,
(del_lf_rows/lf_rows)*100
as ratio
FROM
INDEX_STATS;
NAME
HEIGHT
LF_ROWS DEL_LF_ROW RATIO
------------------------------
---------- ---------- ---------- ----------
PK_ITEM_BASKET
1
161
0
0
The index is rebuilt and validated once again. Examining the
INDEX_STATS table shows that the 74 deleted leaf rows were dropped
from the index. Notice that the total number of leaf rows went from
235 to 161, which is a difference of 74 leaf rows. This index should
provide better performance for the application.
How
Indexes Become Fragmented
The advantages of indexing do not come without a cost. As database
objects, indexes are created for tables only and they must be in
sync with them: indexes must be updated by the database with every
data manipulation language (DML) operation—INSERT, DELETE, or
UPDATE. When a row is deleted from a table, the Oracle database will
not reuse the corresponding index space until you rebuild the index.
Therefore, indexes are always growing and can become very fragmented
over time, especially in tables with dynamic data. As indexes grow,
two things happen: splitting and spawning.
Splitting happens when an index node becomes full with keys and a
new index node is created at the same level as a full node. This
widens the B*-tree horizontally and may degrade performance.
Spawning is the process of adding a new level to an index. As a new
index is populated, it begins life as a single-level index. As keys
are added, a spawning takes place and the first-level node
reconfigures itself to have pointers to lower-level nodes. Spawning
takes place at specific points within the index, not for the entire
index. For example, a three-level index might have a node that
experiences heavy INSERT activity. This node could spawn a fourth
level without the other level-three nodes spawning new levels. That
makes the index unbalanced.
A
PL/SQL Procedure to Detect and Rebuild Out-of-Balance Indexes
This procedure (called RebuildUnbalancedIndexes) takes care of both
global and local indexes. Global index information is extracted into
the csrGlobalIndexes cursor from the USER_INDEXES view WHERE value
in Partitioned column is 'NO':
cursor csrGlobalIndexes is
select
index_name, tablespace_name
from user_indexes
where partitioned = 'NO';
Local indexes are created for partitioned tables. They are
equi_partitioned with the table they are based on. That means that
for each index entry residing in partition CUSTOMER_K, there is a
row of the table CUSTOMER that also resides in that same partition
CUSTOMER_K. Local index information is extracted into the
csrLocalIndexes cursor from the USER_IND_PARTITIONS view WHERE value
in Status column is 'USABLE':
cursor csrLocalIndexes is
select
index_name, partition_name, tablespace_name
from user_ind_partitions
where status = 'USABLE';
If local indexes are UNUSABLE as a result of TRUNCATE or MOVE
partition operations, they need to be rebuilt. Even though it's not
a topic of this 10-Minute Solution, and it's not part of our
procedure, here is the syntax for that case, for your reference:
alter table Customer
modify partition
Customer_K
rebuild unusable
local indexes;
For each index (global or local), the procedure generates statistics
using the ANALYZE INDEX command with the VALIDATE STRUCTURE clause;
extracts it from the INDEX_STATS view; checks for the index height
and number of deleted leaf rows; and decides whether to rebuild the
index or not.
Here is the COMPLETE code:
Create sequence
S_AA_DB_MAINTENANCE
Start
with 1
Increment by 1
/
create table AA_DB_MAINTENANCE
(
Actionid number,
StartTime date,
EndTime date,
Action varchar2(1000)
)
/
CREATE OR REPLACE
Procedure
print_date_diff(p_dte1 IN DATE, p_dte2 IN DATE, v_result OUT
varchar2)
IS
v_diff NUMBER := 0;
v_hrs NUMBER := 0;
v_min NUMBER := 0;
v_sec NUMBER := 0;
BEGIN
v_diff := ABS(p_dte2 -
p_dte1);
v_hrs := TRUNC(v_diff,
0)*24; -- start with days portion if any
v_diff := (v_diff -
TRUNC(v_diff, 0))*24; -- lop off whole days, convert to hrs
v_hrs := v_hrs +
TRUNC(v_diff, 0); -- add in leftover hrs if any
v_diff := (v_diff -
TRUNC(v_diff, 0))*60; -- lop off hrs, convert to mins
v_min := TRUNC(v_diff,
0); -- whole mins
v_sec := TRUNC((v_diff
- TRUNC(v_diff, 0))*60, 0); -- lop off mins, convert to secs
v_result :=
TO_CHAR(v_hrs) || ' HRS ' ||
TO_CHAR(v_min)
|| ' MIN ' ||
TO_CHAR(v_sec)
|| ' SEC';
DBMS_OUTPUT.put_line(v_result);
END print_date_diff;
/
CREATE OR REPLACE
Procedure
DB_REBUILD_INDEXES
is
--
*****************************************
-- PURPOSE: Rebuild Indexes
that are Fragmented
--
This
Procedure is called by DB_MAINTENANCE_WEEKLY
--
This
script will analyze all the indexes under the current Schema
--
After
this the dynamic view INDEX_STATS is consulted to see if an index
--
is
a good candidate for a rebuild.
--
Rebuild
the index when :
--
-
Deleted entries represent 25% or more of the current entries
--
-
The index depth is more then 3 levels (defined in variable
v_MaxHeight)
--
AUTHOR: Diego Pafumi
--
******************************************
v_MaxHeight integer := 3;
v_MaxLeafsDeleted
integer := 25;
v_Count
integer := 0;
v_actionid integer;
v_date
date;
v_error_message
varchar2(255);
v_error_code number(8);
v_error_message2 varchar2(255);
v_finaldiff
varchar2(40);
--Cursor to
Manage NON-Partitioned Indexes
--Select
the Indexes, excluding the ones in the Recycle Bin
cursor
cur_Global_Indexes is
select index_name, tablespace_name
from
user_indexes
where
table_name not like 'BIN$%'
and
partitioned = 'NO';
--Cursor to
Manage Current Index
cursor
cur_IndexStats is
select name, height, lf_rows as leafRows, del_lf_rows as
leafRowsDeleted
from
index_stats;
v_IndexStats
cur_IndexStats%rowtype;
--Cursor to
Manage Partitioned Indexes
-- cursor
cur_Local_Indexes is
--
select
index_name, partition_name, tablespace_name
--
from
user_ind_partitions
--
where
status = 'USABLE';
begin
select
S_AA_DB_MAINTENANCE.nextval into v_actionid from dual;
select sysdate
into v_date from dual;
delete from
AA_DB_MAINTENANCE
where StartTime < sysdate - 20;
insert into
AA_DB_MAINTENANCE (Actionid,StartTime, Action)
values (v_actionid, v_date, 'DB_REBUILD_INDEXES Procedure
Started');
commit;
DBMS_OUTPUT.ENABLE(1000000);
/* Global or
Standard Indexes Section */
for v_IndexRec in
cur_Global_Indexes
loop
begin
dbms_output.put_line('before
analyze ' || v_IndexRec.index_name);
execute
immediate 'analyze index ' || v_IndexRec.index_name || ' validate
structure';
dbms_output.put_line('After
analyze ');
open
cur_IndexStats;
fetch
cur_IndexStats into v_IndexStats;
if
cur_IndexStats%found then
if
(v_IndexStats.height > v_MaxHeight) OR
(v_IndexStats.leafRows
> 0 AND v_IndexStats.leafRowsDeleted > 0 AND
(v_IndexStats.leafRowsDeleted
* 100 / v_IndexStats.leafRows) > v_MaxLeafsDeleted) then
begin
dbms_output.put_line('*****************************************************************');
dbms_output.put_line('Rebuilding
index ' || v_IndexRec.index_name || ' with '
||
to_char(v_IndexStats.height) || ' height and '
||
to_char(trunc(v_IndexStats.leafRowsDeleted * 100 /
v_IndexStats.leafRows)) || ' % LeafRows');
---
Line for Oracle 9i
--
execute immediate 'alter index ' || v_IndexRec.index_name ||
--
'
rebuild parallel nologging online tablespace ' ||
v_IndexRec.tablespace_name;
---
Line for Oracle 10g
--
On 10g Oracle now automatically collects statistics during index
creation and rebuild
execute
immediate 'alter index ' || v_IndexRec.index_name ||
'
rebuild' ||
'
parallel nologging compute statistics' ||
'
tablespace ' || v_IndexRec.tablespace_name;
insert
into AA_DB_MAINTENANCE (Actionid,StartTime, Action)
values
(v_actionid, sysdate, 'The index ' || v_IndexRec.index_name || '
has been rebuilt');
dbms_output.put_line('*****************************************************************');
v_Count
:= v_Count + 1;
exception
when
OTHERS then
dbms_output.put_line('The
index ' || v_IndexRec.index_name || ' WAS NOT rebuilt');
insert
into AA_DB_MAINTENANCE (Actionid,StartTime, Action)
values
(v_actionid, sysdate, 'The index ' || v_IndexRec.index_name || '
WAS NOT rebuilt');
commit;
end;
end
if;
end
if;
close
cur_IndexStats;
exception
when
OTHERS then
dbms_output.put_line('The
index ' || v_IndexRec.index_name || ' WAS NOT ANALYZED');
insert
into AA_DB_MAINTENANCE (Actionid,StartTime, Action)
values
(v_actionid, sysdate, 'The index ' || v_IndexRec.index_name || '
WAS NOT ANALYZED');
commit;
end;
end loop;
print_date_diff(v_date, sysdate, v_finaldiff);
insert into
AA_DB_MAINTENANCE (Actionid,StartTime, Action)
values
(v_actionid, sysdate, 'DB_REBUILD_INDEXES Finished in ' ||
v_finaldiff);
commit;
/*
dbms_output.put_line('Global or Standard Indexes Rebuilt: ' ||
to_char(v_Count));
v_Count := 0;
--Local indexes
Section
for v_IndexRec in
cur_Local_Indexes
loop
execute immediate 'analyze index ' || v_IndexRec.index_name ||
'
partition (' || v_IndexRec.partition_name ||
')
validate structure';
open cur_IndexStats;
fetch cur_IndexStats into v_IndexStats;
if cur_IndexStats%found then
if
(v_IndexStats.height > v_MaxHeight) OR
(v_IndexStats.leafRows
> 0 and v_IndexStats.leafRowsDeleted > 0 AND
(v_IndexStats.leafRowsDeleted
* 100 / v_IndexStats.leafRows) > v_MaxLeafsDeleted) then
v_Count
:= v_Count + 1;
dbms_output.put_line('Rebuilding
Index ' || v_IndexRec.index_name || '...');
--
execute
immediate 'alter index ' || v_IndexRec.index_name ||
--
'
rebuild' ||
--
'
partition ' || v_IndexRec.partition_name ||
--
'
parallel nologging compute statistics' ||
--
'
tablespace ' || v_IndexRec.tablespace_name;
end
if;
end if;
close cur_IndexStats;
end loop;
dbms_output.put_line('Local Indexes Rebuilt: ' ||
to_char(v_Count));
*/
--
EXCEPTION
WHEN
OTHERS THEN
--Show
Error on specific line
--10g
Only
DBMS_OUTPUT.PUT_LINE(
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE );
v_error_code
:= SQLCODE;
v_error_message
:= SQLERRM;
--10g
Only
v_error_message2
:= DBMS_UTILITY.FORMAT_ERROR_BACKTRACE ;
--10g
Only
DBMS_OUTPUT.PUT_LINE('Error:
'|| DBMS_UTILITY.FORMAT_ERROR_BACKTRACE );
dbms_output.put_line('Error:
'||TO_CHAR(v_error_code)||' - '||v_error_message);
insert
into AA_DB_MAINTENANCE (Actionid,StartTime, Action)
values
(v_actionid, sysdate, '*** ERRORS on DB_REBUILD_INDEXES:' ||
TO_CHAR(v_error_code)||' - '||v_error_message);
commit;
end;
/
-- Run it Weekly on Sundays at
5AM
VARIABLE v_jobnum NUMBER;
BEGIN
DBMS_JOB.SUBMIT(:v_jobnum,'DB_MAINTENANCE_WEEKLY;',
TO_DATE('2309200705','DDMMYYYYHH24'),
'trunc(sysdate,''hh'') + 7');
EN;
/
This is Just the PL/SQL
Version of it
spool Rebuild_Indexes.txt
--
*****************************************
-- PURPOSE: Rebuild Indexes
that are Fragmented
--
This
is the PL/SQL Code version
--
This
script will analyze all the indexes under the current Schema
--
After
this the dynamic view INDEX_STATS is consulted to see if an index
--
is
a good candidate for a rebuild.
--
Rebuild
the index when :
--
-
Deleted entries represent 25% or more of the current entries
--
-
The index depth is more then 3 levels (defined in variable
v_MaxHeight)
--
AUTHOR: Diego Pafumi
--
******************************************
set serveroutput on
declare
v_MaxHeight integer := 3;
v_MaxLeafsDeleted
integer := 25;
v_Count
integer := 0;
v_date
date;
v_error_message
varchar2(255);
v_error_code number(8);
v_error_message2 varchar2(255);
v_finaldiff
varchar2(40);
--Cursor to
Manage NON-Partitioned Indexes
--Select
the Indexes, excluding the ones in the Recycle Bin
cursor
cur_Global_Indexes is
select index_name, tablespace_name
from
user_indexes
where
table_name not like 'BIN$%'
and
partitioned = 'NO';
--Cursor to
Manage Current Index
cursor
cur_IndexStats is
select name, height, lf_rows as leafRows, del_lf_rows as
leafRowsDeleted
from
index_stats;
v_IndexStats
cur_IndexStats%rowtype;
--Cursor to
Manage Partitioned Indexes
-- cursor
cur_Local_Indexes is
--
select
index_name, partition_name, tablespace_name
--
from
user_ind_partitions
--
where
status = 'USABLE';
begin
select sysdate
into v_date from dual;
DBMS_OUTPUT.ENABLE(1000000);
/* Global or
Standard Indexes Section */
for v_IndexRec in
cur_Global_Indexes
loop
begin
dbms_output.put_line('before
analyze ' || v_IndexRec.index_name);
execute
immediate 'analyze index ' || v_IndexRec.index_name || ' validate
structure';
dbms_output.put_line('After
analyze ');
open
cur_IndexStats;
fetch
cur_IndexStats into v_IndexStats;
if
cur_IndexStats%found then
if
(v_IndexStats.height > v_MaxHeight) OR
(v_IndexStats.leafRows
> 0 AND v_IndexStats.leafRowsDeleted > 0 AND
(v_IndexStats.leafRowsDeleted
* 100 / v_IndexStats.leafRows) > v_MaxLeafsDeleted) then
begin
dbms_output.put_line('*****************************************************************');
dbms_output.put_line('Rebuilding
index ' || v_IndexRec.index_name || ' with '
||
to_char(v_IndexStats.height) || ' height and '
||
to_char(trunc(v_IndexStats.leafRowsDeleted * 100 /
v_IndexStats.leafRows)) || ' % LeafRows');
---
Line for Oracle 9i
--
execute immediate 'alter index ' || v_IndexRec.index_name ||
--
'
rebuild parallel nologging online tablespace ' ||
v_IndexRec.tablespace_name;
---
Line for Oracle 10g
--
On 10g Oracle now automatically collects statistics during index
creation and rebuild
execute
immediate 'alter index ' || v_IndexRec.index_name ||
'
rebuild' ||
'
parallel nologging compute statistics' ||
'
tablespace ' || v_IndexRec.tablespace_name;
dbms_output.put_line('The
index ' || v_IndexRec.index_name || ' has been rebuilt');
dbms_output.put_line('*****************************************************************');
v_Count
:= v_Count + 1;
exception
when
OTHERS then
dbms_output.put_line('The
index ' || v_IndexRec.index_name || ' WAS NOT rebuilt');
commit;
end;
end
if;
end
if;
close
cur_IndexStats;
exception
when
OTHERS then
dbms_output.put_line('The
index ' || v_IndexRec.index_name || ' WAS NOT ANALYZED');
commit;
end;
end loop;
print_date_diff(v_date, sysdate, v_finaldiff);
dbms_output.put_line ('Rebuild INDEXES Finished in ' ||
v_finaldiff);
dbms_output.put_line('# of Indexes Rebuilt: ' ||
to_char(v_Count));
/*
v_Count := 0;
--Local indexes
Section
for v_IndexRec in
cur_Local_Indexes
loop
execute immediate 'analyze index ' || v_IndexRec.index_name ||
'
partition (' || v_IndexRec.partition_name ||
')
validate structure';
open cur_IndexStats;
fetch cur_IndexStats into v_IndexStats;
if cur_IndexStats%found then
if
(v_IndexStats.height > v_MaxHeight) OR
(v_IndexStats.leafRows
> 0 and v_IndexStats.leafRowsDeleted > 0 AND
(v_IndexStats.leafRowsDeleted
* 100 / v_IndexStats.leafRows) > v_MaxLeafsDeleted) then
v_Count
:= v_Count + 1;
dbms_output.put_line('Rebuilding
Index ' || v_IndexRec.index_name || '...');
--
execute
immediate 'alter index ' || v_IndexRec.index_name ||
--
'
rebuild' ||
--
'
partition ' || v_IndexRec.partition_name ||
--
'
parallel nologging compute statistics' ||
--
'
tablespace ' || v_IndexRec.tablespace_name;
end
if;
end if;
close cur_IndexStats;
end loop;
dbms_output.put_line('Local Indexes Rebuilt: ' ||
to_char(v_Count));
*/
--
EXCEPTION
WHEN
OTHERS THEN
--Show
Error on specific line
--10g
Only
DBMS_OUTPUT.PUT_LINE(
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE );
v_error_code
:= SQLCODE;
v_error_message
:= SQLERRM;
--10g
Only
v_error_message2
:= DBMS_UTILITY.FORMAT_ERROR_BACKTRACE ;
--10g
Only
DBMS_OUTPUT.PUT_LINE('Error:
'|| DBMS_UTILITY.FORMAT_ERROR_BACKTRACE );
dbms_output.put_line('Error:
'||TO_CHAR(v_error_code)||' - '||v_error_message);
end;
/
spool off
And
Another Method of Index Rebuilder with Job Scheduler:
An index should be considered for rebuilding under any of the
following conditions:
- The percentage of deleted rows exceeds 30% of the total, i.e. if
DEL_LF_ROWS / LF_ROWS > 0.3.
- If the 'HEIGHT' is greater than 3.
- If the number of rows in the index ('LF_ROWS') is significantly
smaller than 'LF_BLKS' this can indicate a large number of deletes,
indicating that the index should be rebuilt.
- BLOCK_GETS greater than 5
We considered job scheduler for index rebuilding.
Steps:
1. Create a table TEMP_INDEX_STAT, which is similar to INDEX_STATS
2. Create a procedure GEN_INDEX_STAT. It will analyze an index and
store that data in TEMP_INDEX_STAT
3. Create a job class LOW_PRIORITY_CLASS with LOW_GROUP resource
consumer group, so that the job might not hamper production
transaction.
4. Create a job GET_INDEX_STAT_JOB
5. Create a table RUNNING_CMDS
6. Create a procedure REBUILD_INDEX, which will REBUILD the indexes
7. Create another job REBUILD_INDEX_JOB. This job will call the
above procedure to rebuild the indexes.
Please be sure that the load in the database is not at the peak. If
yes, you may get resource timeout error during the job execution
period and the job may be failed.
Step 1. Create table
TEMP_INDEX_STAT
CREATE TABLE TEMP_INDEX_STAT
AS SELECT * FROM INDEX_STATS WHERE 1=2;
Step 2. Create the
Procedure GEN_INDEX_STAT
CREATE OR REPLACE PROCEDURE
GEN_INDEX_STAT (PRM_SCHEMA VARCHAR2)
IS
CURSOR INDEX_CUR IS
SELECT INDEX_NAME
FROM
DBA_INDEXES d
WHERE
OWNER = prm_schema
AND
NOT EXISTS (SELECT 1
FROM
temp_index_stat b
WHERE
d.index_Name = b.NAME
)
AND
INDEX_TYPE = 'NORMAL';
v_str
VARCHAR2(500);
BEGIN
FOR INDEX_REC IN
INDEX_CUR LOOP
v_str := 'ANALYZE INDEX
'||PRM_SCHEMA||'.'||INDEX_REC.INDEX_NAME||' VALIDATE STRUCTURE ';
EXECUTE IMMEDIATE v_str;
v_str := 'insert into TEMP_INDEX_STAT select * from index_stats';
EXECUTE IMMEDIATE v_str;
COMMIT;
END LOOP; --
END GEN_INDEX_STAT;
/
Step 3. Create a Job Class
with low Pirority
begin
dbms_scheduler.create_job_class(
job_class_name
=> 'LOW_PRIORITY_CLASS',
resource_consumer_group => 'LOW_GROUP',
logging_level
=> DBMS_SCHEDULER.LOGGING_FULL,
log_history =>
60,
comments =>
'LOW PRIORITY JOB CLASS');
end;
/
Step 4. Create a Job to
Call Pricedure
You may set START_DATE if you want to schedule the following job.
BEGIN
dbms_scheduler.create_job
(
job_name=>
'GEN_INDEX_STAT_JOB',
job_type=> 'PLSQL_BLOCK',
job_action=>'begin
GEN_INDEX_STAT(''SCHEMA_NAME''); end;',
START_DATE=>NULL,
REPEAT_INTERVAL=>NULL,
ENABLED=>TRUE,
AUTO_DROP=>FALSE,
COMMENTS=>'Generate Index
Stat',
job_class=>'LOW_PRIORITY_CLASS'
);
END;
Step 5. Create table
RUNNING_CMDS
Upto this point, we have collected statistics for all indexes in our
schema.
Now we need to REBUILD the indexes according to the conditions
described above.
This table will store the commands so that we could easily identify
which index is in rebuild process during the running of the
following job.
CREATE TABLE RUNNING_CMDS CMD
VARCHAR2(200);
Step 6. Create the
Procedure to Rebuild Reported Indexes
CREATE OR REPLACE PROCEDURE
REBUILD_INDEX(PRM_SCHEMA VARCHAR2)
IS
CURSOR CUR IS
SELECT NAME
FROM
TEMP_INDEX_STAT a
WHERE
(HEIGHT >= 4
OR
(del_lf_rows/lf_rows > 0.3 AND lf_rows > 0)
OR
(blks_gets_per_access > 4)
)
AND
EXISTS (SELECT 1
FROM
dba_indexes d
WHERE
a.NAME = D.index_name
AND
d.index_type <> 'LOB'
);
BEGIN
execute immediate
'truncate table RUNNING_CMDS';
FOR REC IN CUR
LOOP
v_str := 'ALTER INDEX '||PRM_SCHEMA||'.'||REC.NAME||' REBUILD
ONLINE';
INSERT INTO RUNNING_CMDS VALUES(V_STR);
COMMIT;
EXECUTE IMMEDIATE v_str;
END LOOP;
END REBUILD_INDEX;
/
Step 7. Create a Job to
call the previous procedure
You may set START_TIME according to your requirement.
BEGIN
dbms_scheduler.create_job
(
job_name=>
'REBUILD_INDEX_JOB',
job_type=> 'PLSQL_BLOCK',
job_action=>'begin
REBUILD_INDEX(''SCHEMA_NAME''); end;',
START_DATE=>NULL,
REPEAT_INTERVAL=>NULL,
ENABLED=>TRUE,
AUTO_DROP=>FALSE,
COMMENTS=>'Rebuild Index',
job_class=>'LOW_PRIORITY_CLASS'
);
END;
Rebuilding Indexes
Beginning in 9i, you can rebuild your indexes online and compute
statistics at the same time. Online index rebuild is also available
for reverse key indexes, function-based indexes and key compressed
indexes on both regular tables and index organized tables (including
secondary indexes). In previous releases, you could either issue one
of the following:
alter index index_name rebuild online
or
alter index index_name rebuild compute
statistics
However it was not possible to combine these statements into one
operation. In 9i, you can issue:
alter index index_name rebuild compute
statistics online;
This allows your current users to be able to access the original
index while it is being rebuilt and having statistics get
generated against it. When you rebuild your indexes online, the
new index is built within your index tablespace while the old
index is still being used. The online option specifies that DML
operations on the table or partition are allowed during rebuilding
of the index. After the new index is completed the old index is
dropped. When you rebuild your indexes and do not specify the
online option, the database locks the entire table from being used
while the index is being rebuilt. This option can also be combined
with the nologging attribute to avoid generating redo when
rebuilding your indexes (ex: alter index index_name rebuild
compute statistics online nologging;)
Monitoring
Index Usage
By using the 'alter index … monitoring usage' statement you
can see if a particular index is being used.
Query v$object_usage as the owner of those indexes to see if the
index has been used and during what time period it was monitored, it
will show you just whether the index has been used or not; it will
not show you how many times the index was used or when it was last
used.
To stop monitoring an index, type:
ALTER INDEX index_name NOMONITORING USAGE;
This script activates index monitoring on an entire schema (must
be run as System)
set echo off
set feedback off
set pages 0
set verify off
accept idx_owner prompt "Enter the schema name: "
spool monitor_idx.tmp
select 'alter index '||owner||'.'||index_name||' monitoring
usage;'
from dba_indexes
where owner = UPPER('&idx_owner');
spool off
set echo on
set feedback on
set pages 60
spool monitor_idx.log
@monitor_idx.tmp
spool off
A sample query on V$OBJECT_USAGE confirms that monitoring has been
activated (must be run as the
OWNER of indexes)
set linesize 92
set pagesize 9999
select substr(index_name,1,25) index_name,
substr(table_name,1,15)
table_name,
MONITORING, USED,
START_MONITORING, END_MONITORING
from v$object_usage
order by used;
INDEX_NAME
TABLE_NAME
MON
USED
START_MONITORING
END_MONITORING
------------- -------------------- --- ----
------------------- --------------
ITEM_ORDER_IX
ORDER_ITEMS
YES NO 08/15/2001 11:23:10
INVENTORY_PK
INVENTORIES
YES YES 08/15/2001 16:51:32
PROD_NAME_IX PRODUCT_DESCRIPTIONS
YES NO 08/15/2001 16:50:32
ORDER_P
ORDERS
YES
YES
08/15/2001 17:10:32
PRD_DESC_PK PRODUCT_DESCRIPTIONS
YES YES 08/15/2001 17:13:32
Another useful index, is this one, to check ALL the indexes on the
DB
select
u.name "OWNER",
io.name "INDEX_NAME",
t.name "TABLE_NAME",
decode(bitand(i.flags,
65536),
0, 'NO', 'YES') "MONITORING",
decode(bitand(nvl(ou.flags,0),
1),
0, 'NO', 'YES') "USED",
ou.start_monitoring "START_MONITORING",
ou.end_monitoring "END_MONITORING"
from
sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou,
sys.user$ u
where
t.obj# =i.bo#
and io.owner# = u.user#
and io.obj# = i.obj#
and u.name not in
('SYS','SYSTEM','XDB','WMSYS','ORDSYS','OUTLN','MDSYS','CTXSYS')
and i.obj# =ou.obj#(+)
order by
u.name, t.name, io.name;
This script stops index monitoring on an entire schema.(must be run
as System)
set echo off
set feedback off
set pages 0
set verify off
accept idx_owner prompt "Enter the schema name: "
spool stop_monitor_idx.tmp
select 'alter index '||owner||'.'||index_name||' nomonitoring
usage;'
from dba_indexes
where owner = UPPER('&idx_owner');
spool off
set echo on
set feedback on
set pages 60
spool stop_monitor_idx.log
@stop_monitor_idx.tmp
spool off
Index Usage without "alter index.... monitoring usage"
To get a solution for this, there is one pre requirement-AWR. AWR
(Automatic Workload Repository), which is part of "Tuning and
Diagnostic Packs", must be installed, what require additional
licensing. AWR is snapshot repository of important database
activity, so AWR should able to keep data with retention of minimum
of 30 days (46 days are perfect) and those snapshots must be taken
in appropriate interval.
After that you are able to use this script that shows all indexes in
schema (defined with first parameter-&&1) which are bigger
then a value (defined with scripts's second parameter-&&2).
Remember that this will work over your "AWR Monitor Period". Save
the file to some location.
/*
---------------------------------------------------------------------------
Purpose : Shows index usage by execution (find problematic
indexes)
Date : 22.01.2008.
Author : Damir Vadas, damir.vadas@gmail.com
Remarks : Run as privileged user
Must
have AWR run because sql joins data from there works on 10g
>
@index_usage SCHEMA MIN_INDEX_SIZE
Changes (DD.MM.YYYY, Name,
CR/TR#):
25.11.2010,
Damir Vadas added index size as parameter
30.11.2010,
Damir Vadas fixed bug in query
---------------------------------------------------------------------------
*/
ACCEPT OWNER PROMPT 'Schema_Name: '
ACCEPT SIZE_MB PROMPT 'SIZE in MB: '
set linesize 140
set pagesize 160
clear breaks
clear computes
break on TABLE_NAME skip 2 ON INDEX_NAME ON INDEX_TYPE ON MB
compute sum of NR_EXEC on TABLE_NAME SKIP 2
compute sum of MB on TABLE_NAME SKIP 2
SET TIMI OFF
set linesize 140
set pagesize 10000
col OWNER noprint
col TABLE_NAME for a20 heading 'Table name'
col INDEX_NAME for a25 heading 'Index name'
col INDEX_TYPE for a10 heading 'Index|type'
col INDEX_OPERATION for a21 Heading 'Index operation'
col NR_EXEC for 9G999G990 heading 'Executions'
col MB for 999G990 Heading 'Index|Size MB' justify right
WITH Q AS (
SELECT
S.OWNER
A_OWNER,
TABLE_NAME
A_TABLE_NAME,
INDEX_NAME
A_INDEX_NAME,
INDEX_TYPE
A_INDEX_TYPE,
trunc(SUM(S.bytes) / 1048576) A_MB
FROM DBA_SEGMENTS S,
DBA_INDEXES I
WHERE S.OWNER = upper('&&OWNER')
AND I.OWNER = upper('&&OWNER')
AND INDEX_NAME = SEGMENT_NAME
GROUP BY S.OWNER, TABLE_NAME, INDEX_NAME, INDEX_TYPE
HAVING SUM(S.BYTES) > 1048576 * &&SIZE_MB
)
SELECT /*+
NO_QUERY_TRANSFORMATION(S) */
A_OWNER
OWNER,
A_TABLE_NAME
TABLE_NAME,
A_INDEX_NAME
INDEX_NAME,
A_INDEX_TYPE
INDEX_TYPE,
A_MB
MB,
DECODE (OPTIONS, null, '
-',OPTIONS) INDEX_OPERATION,
COUNT(OPERATION)
NR_EXEC
FROM Q,
DBA_HIST_SQL_PLAN d
WHERE
D.OBJECT_OWNER(+)= q.A_OWNER
AND
D.OBJECT_NAME(+) = q.A_INDEX_NAME
GROUP BY A_OWNER,
A_TABLE_NAME, A_INDEX_NAME, A_INDEX_TYPE, A_MB,
DECODE (OPTIONS, null, '
-',OPTIONS)
ORDER BY A_OWNER,
A_TABLE_NAME, A_INDEX_NAME, A_INDEX_TYPE, A_MB DESC, NR_EXEC DESC;
PROMPT Showed only indexes in &OWNER schema whose size >
&SIZE_MB MB in period:
SET HEAD OFF;
select to_char (min(BEGIN_INTERVAL_TIME), 'DD.MM.YYYY')
|| '-' ||
to_char
(max(END_INTERVAL_TIME), 'DD.MM.YYYY')
from dba_hist_snapshot;
SET HEAD ON
SET TIMI ON
This will show:
Index
Table
name
Index
name
type Size MB Index
operation Executions
-------------------- ------------------------- ---------- --------
--------------------- ----------
EXCEPTIONITEM
IN_EXCEPITEM_IMPDATE_INFS
NORMAL
13
-
0
OURID
******************** ************************* **********
--------
----------
sum
13
0
ISSUEDATA
IN_ISSUEDATA_IMPORTEDDATE
NORMAL
43
-
0
PK_ISSUEDATA
NORMAL
22
-
0
UN_ISSUEDATA_BANKIDACCTSE
NORMAL
265
-
0
RIAL
******************** ************************* **********
--------
----------
sum
330
0
ISSUEDATA_ARCH
IN_ISSUEDATAARCH_ISSUEID
NORMAL
1,984
-
0
******************** ************************* **********
--------
----------
sum
1,984
0
Showed only indexes in FG836_PNC_PROD schema whose size > 10 MB
in period:
16.08.2011-24.08.2011
What is interesting here (keep in mind that monitoring period is 46
days!):
1- All indexes with "Executions" value=0 has never been used in
monitored period. Reason may be poor quality (against underlying
data) or change of CBO plans. These indexes are highly on the list
for purge-especially if they are big or they are under table with
big use (on other indexes i.e. NSO_PROD_I)!
2- Big indexes with small number of executions, especially those
with just one execution, are really candidate to be purged and
created/dropped for each execution
3- Indexes where FULL SCAN is using mode are candidates for rebuild
or checking it's quality*
4- Tables which have many indexes with small index usage are
candidates for relation model redesign
5- Indexes with huge number of executions should be observed a lot.
Rebuild it when usage in next period decrease for more then 15%-25%.
Get Index Information
The idea of this script is to measure index quality.
If index has bad ratio of index_clustering_factor/table_rows it's
usage may be worse then full table scan!
There are mainly two reasons why index has bad quality (without
deeper coverage any of these terms):
- Index is fragmented-solution is to rebuild index
- Underlying table data are in that order that usage of index is
really doubt-able. Solution is to drop it (make invisible in
11g!) and try to execute query to see performance. In a case of
huge performance downgrade think about creation of index before
query and drop after is the best solution for rarely run
queries.
/**********************************************************************
*
File: Index_Info.sql
*
Type: SQL*Plus script
*
Author: Dan Hotka
*
Date: 04-16-2009
*
* Description:
*
SQL*Plus script to display Index Statistics in relation to
clustering factor
*
Script originated from Jonathan Lewis
* Modifications:
*
*********************************************************************/
set linesize 300
set pagesize 200
spool Index_Info.txt
SELECT i.table_name,
i.index_name, t.num_rows, t.blocks, i.clustering_factor,
case when
nvl(i.clustering_factor,0) =
0
then 'No Stats'
when
nvl(t.num_rows,0) =
0
then 'No Stats'
when
(round(i.clustering_factor / t.num_rows * 100)) < 6 then
'Excellent '
when
(round(i.clustering_factor / t.num_rows * 100)) between 7 and 11
then 'Good'
when
(round(i.clustering_factor / t.num_rows * 100)) between 12 and 21
then 'Fair'
else
'Poor'
end Index_Quality,
i.avg_data_blocks_per_key, i.avg_leaf_blocks_per_key,
to_char(o.created,'MM/DD/YYYY HH24:MI:SSSSS') Created
from user_indexes i,
user_objects o, user_tables t
where i.index_name =
o.object_name
and i.table_name =
t.table_name
order by 1;
spool off
-- exit
And here some details on how to read and analyze each case:
Table
Table
Index Data Blks Leaf
Blks Clust Index
Table
Rows Blocks Index Size
MB per Key per
Key Factor Quality
---------------------
----------- ---------- ------ --------- --------- ---------
------------ -----------
NC_SUBSCRIPTION_SALDO
22,461,175 415103
NSO_I
560.00
9 1
21,749,007 1-Poor
This is classical representative of index with enormous clustering
factor who may be a candidate for rebuild (very doubtful on first
look) and if that doesn't help then recreate/drop should eb an
option if not and pure drop!
Table
Table
Index Data Blks Leaf
Blks Clust Index
Table
Rows Blocks Index Size
MB per Key per
Key Factor Quality
---------------------
----------- ---------- ------ --------- --------- ---------
------------ -----------
NC_TRANSACTION_ARZ_T
5,815,708 137742
TRA_I
184.00
12
1 222,104 5-Excellent
This is representative of high quality index who is unused (used
only twice). For such an index I'll recommend deeper analyze for
involved SQLs. But this is not a candidate for purge in any mean.
Table
Table
Index Data Blks Leaf
Blks Clust Index
Table
Rows Blocks Index
Size MB per Key per
Key Factor Quality
---------------------
---------- ---------- ------- --------- --------- ---------
------------ -----------
NC_TRANSACTION_OK_T 24,492,333
851796 TROK_I
536.00
1 1
21,977,784 1-Poor
This index has never been used-highly recommended for purge!
Table
Table
Index Data Blks Leaf
Blks Clust Index
Table
Rows Blocks Index
Size MB per Key per
Key Factor Quality
---------------------
---------- ---------- ------- --------- --------- ---------
------------ -----------
NC_TRANSACTION_ULAZ_TT
840,179 27437
TRUT_I
774.00
1
1 731,609 1-Poor
This index is pretty big (774 MB), with low index quality, and use
only twice. Recommendation rebuild and, if that doesn't help, purge.
More
information on Indexes
http://psoug.org/reference/indexes.html