Index Monitoring

For those not familiar with it, index monitoring is Oracle's way to track whether an index is being used, letting you know if it is needed.

Two things to keep in mind:
1) It doesn't always mark an index as used even if it is used. If it isn't used in an execution plan but is used to enforce a foreign key or unique constraint, it won't get flagged as used.
2) The view used to look at index usage is schema specific. You may be monitoring indexes, but the indexes won't show up in v$object_usage unless you log in as the schema owner. It's better to go directly to the underlying query to view all monitored indexes (query below).

Since index monitoring is very low cost, it makes sense to turn it on for all candidate indexes. Indexes on FKs and unique indexes are doing work even if not used in execution plans, so they are not candidates to drop. Here's the query to get all non-unique, non-FK indexes (assumes no concatenated PK's - if you have that, the query gets more complicated):

SELECT 'ALTER INDEX '||ic.index_name||' MONITORING USAGE;'
  FROM all_ind_columns ic, all_indexes i
 WHERE i.uniqueness = 'NONUNIQUE' --don't monitor unique indexes
   AND i.table_owner = 'SCHEMA_OWNER_HERE'
   AND ic.index_owner = i.owner
   AND ic.index_name = i.index_name
   AND ic.position = 1
   AND NOT EXISTS (SELECT 'x' --Don't monitor indexes on FK's
                     FROM all_cons_columns cc, all_constraints c
                    WHERE ic.table_name = cc.table_name
                      AND ic.column_name = cc.column_name
                      AND c.constraint_name = cc.constraint_name
                      AND c.constraint_type IN ('R'));

Here's the query to look at monitored objects if you're not logged in as the schema owner:

select d.username, io.name, t.name,
       decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),
       decode(bitand(ou.flags, 1), 0, 'NO', 'YES'),
       ou.start_monitoring,
       ou.end_monitoring 
from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou,
     dba_users d
where io.owner# = d.user_id
  AND d.username = 'SCHEMA_OWNER_HERE'
  and i.obj# = ou.obj#
  and io.obj# = ou.obj#
  and t.obj# = i.bo#;

And here's an example of index monitoring in action, including the Unique index usage not being flagged:

CREATE TABLE test_monitoring AS SELECT level id, dbms_random.value(1,1000) value FROM dual CONNECT BY LEVEL <= 5000;

Table created.

CREATE UNIQUE INDEX test_monitoring_idx ON test_monitoring(id);

Index created.

ALTER INDEX test_monitoring_idx MONITORING USAGE;

Index altered.

--Using index for PK enforcement - does not flag the index as used:
INSERT INTO test_monitoring VALUES (100,0);
INSERT INTO test_monitoring VALUES (100,0)
*
ERROR at line 1:
ORA-00001: unique constraint (BAYPAS.TEST_MONITORING_IDX) violated 

SELECT index_name, used FROM v$object_usage WHERE index_name = UPPER('test_monitoring_idx');

INDEX_NAME                     USE                                              
------------------------------ ---                                              
TEST_MONITORING_IDX            NO                                               

--But we run a select that will use the index
SELECT * FROM test_monitoring WHERE id = 100;

        ID      VALUE                                                           
---------- ----------                                                           
       100   255.5571                                                           

--And now the index shows up as used:
SELECT index_name, used FROM v$object_usage WHERE index_name = 'TEST_MONITORING_IDX';

INDEX_NAME                     USE                                              
------------------------------ ---                                              
TEST_MONITORING_IDX            YES      


Granville Bonyata

Advertisement

Dbms_xplan by Granville Bonyata

I  am extremly busy these days and do not have any time to blog.That’s why my friend Granville Bonyate decided to write few small posts.Here is the first on in series.

 

Having worked with Oracle for many (twenty!) years, I sometimes find new features released several years ago that I didn’t catch at the time. Most recently, it would be that 10g added the display_cursor function to dbms_xplan. Most usefully, you can pass in the Sql-Id (from v$sql) and get the formatted execution plan for a SQL statement: Select * from table(dbms_xplan.display_cursor(‘d5dj7zctjwy1y’)); More information here:

http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_xplan.htm

 

Very useful when you’re at a site that doesn’t have TOAD or OEM.

Granville Bonyata

 

OEM 11g Grid Control Release 1

Oracle is releasing OEM 11g  on April 22nd.However OEM 11g R 1 is available to download from Oracle E-Delivery site ( 3 files total 4.2 Gb – Linux x86-64 )

http://edelivery.oracle.com

Also you can register for Webcast to watch this live event:

http://www.oracle.com/enterprisemanager11g/index.html

http://www.oracle.com/webapps/events/EventsDetail.jsp?p_eventId=110011&src=6773871&src=6773871&Act=45

Oracle originally  had OEM 11g R1 documentation available online but it’s removed .It will be back after live event on April 22nd.

Documentation is back:

http://download.oracle.com/docs/cd/E11857_01/nav/overview.htm

ORION (Oracle I/O Calibration Tool) included in 11g R2

Apparently Oracle  included this tool into 11g release 2 database.Orion binary is located under  $ORACLE_HOME/bin .

Quick run with help command will give very detail explanation how to use this tool.Also

Kevin Closson’s Oracle blog got post about Orion:

http://kevinclosson.wordpress.com/2006/12/11/a-tip-about-the-orion-io-generator-tool/

-bash-3.2$ orion -help
ORION: ORacle IO Numbers — Version 11.2.0.1.0

ORION runs IO performance tests that model Oracle RDBMS IO workloads.
It measures the performance of small (2-32K) IOs and large (128K+) IOs
at various load levels.

Direct path reads and serial table scans in 11g

To answer comment by Frits about Doug Burns observation of using full table scans that resulted in direct path  reads.

 Here is explanation from Oracle itself:

There have been changes in 11g in the heuristics to choose between direct path reads or reads through buffer cache for serial table scans.
In 10g, serial table scans for “large” tables used to go through cache (by default) which is not the case anymore.  In 11g, this decision to read via direct path or through cache is based on the size of the table, buffer cache size and various other stats.

Direct path reads are faster than scattered reads and have less impact on other processes because they avoid latches.

References: High ‘direct path read’ waits in 11g    Note: 793845.1