Oracle internals, debugging and undocumented features
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
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:
Very useful when you’re at a site that doesn’t have TOAD or OEM.
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 )
Also you can register for Webcast to watch this live event:
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:
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:
-bash-3.2$ orion -help
ORION: ORacle IO Numbers — Version 18.104.22.168.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.
Read more of this post
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