Index usage monitoring in Oracle

Indices (or indexes, if you will) are always good, right? Well, Tom Kyte would disagree with you and so would I. In fact, indices will always impede data manipulation performance, so they should be used only where they are useful in queries. But determining if an index is useful can be sometimes pretty difficult, mainly in a complex application with thousands of different queries, some even dynamically generated.

In this case, one of the approaches is to create indices on all columns where it possibly may make sense and monitor, if they are actually ever used by the CBO planner. I will show you how to do that. This method was tested in 11g, but should work in 10g as well.

The magic is done by an ALTER INDEX indexname MONITORING USAGE.

Starting the monitoring

You will need an ALTER INDEX privilege on te indices you want to monitor, so preferrably you do it as a SYSDBA. Fire up your SQL*Plus and connect to the database. First, we will create a script to enable the monitoring. Replace the MYUSER with whatever user you want to monitor the indices for.

set heading off
set echo off
set feedback off
set pages 10000
spool startmonitor.sql
select 'alter index '||owner||'.'||index_name
 ||' monitoring usage;'
from dba_indexes
where owner ='MYUSER';
spool off

Then we simply run the script from within the console.

SQL> @startmonitor.sql

You might see some error messages when the script tries to alter a LOB index, which is not possible, just ignore that.

Now you want to keep your application running for some time. At least for it to perform all kinds of work and jobs it normally does. So for example if you have jobs planned for every hour, leave it running like that for at least an hour. If you have daily jobs, leave it on for a whole day.

Stop the monitoring

In the meantime you can prepare a script to turn monitoring off.

set heading off
set echo off
set feedback off
set pages 10000
spool stopmonitor.sql
select 'alter index '||owner||'.'||index_name
 ||' nomonitoring usage;'
from dba_indexes
where owner ='MYUSER';
spool off

After you reach your desired monitoring period, again, simply run the off-script:

SQL> @stopmonitor.sql

Results

Now, for the results. The index usage overview is stored in the v$object_usage system view. The tricky part here is, that you only see the indices for a currently logged in user. So, even if you were doing the monitoring under a SYS or some other user, now you must login as the user whose indices you were monitoring, in our example MYUSER.

When you do that, identifying those harmful indices is as simple as this:

SELECT INDEX_NAME, TABLE_NAME
  FROM V$OBJECT_USAGE
 WHERE USED='NO';

Some of those indices might be primary keys, so you don't want to be dropping those, but all other unused indices should go. Unless, of course, you know you will need them in a very near future.

UPDATE:
You can query the monitoring results for all users too, but you have to build the query yourself from system tables:

SELECT U.NAME OWNER
,      IO.NAME INDEX_NAME
,      T.NAME TABLE_NAME
,      DECODE(BITAND(I.FLAGS, 65536), 0, 'NO', 'YES') MONITORING
,      decode(bitand(ou.flags, 1), 0, 'NO', 'YES') used
,      ou.start_monitoring
,      ou.end_monitoring
from
sys.user$ u
,   sys.obj$ io
,   sys.obj$ t
,   sys.ind$ i
,   sys.object_usage ou
where
i.obj# = ou.obj#
and io.obj# = ou.obj#
AND T.OBJ# = I.BO#
and u.user# = io.owner#

The hunt for the UNKNOWN SQL in Oracle

If you are looking at your AWR report or browsing thru performance views in your Oracle Enterprise Manager console, chances are, that some of those SQLs, eating at your CPU or I/O bandwidth are marked as UNKNOWN. This is frustrating, to say the least, but in cases when these mysterious queries are killing your database, it may become a matter of life and death to find out just what does the Oracle do.

Most commonly, these queries are LOB operations. That is, they represent reading and writing the actual CLOB or BLOB data by the appropriate oci functions. The question then stands: what table? And this is where this little post comes to the rescue.

We will start with the only thing we are able to get from the report--the SQL ID, which is a hashed fingerprint of our culprit. It looks something like "2v82uk48x55vt". Regular SQL queries can be viewed by querying the system view V$SQL (or GV$SQL in RAC environments).

SELECT SQL_TEXT
  FROM GV$SQL
 WHERE SQL_ID='2v82uk48x55vt';

This, however, will not work for those unknown SQLs. The query will come up empty. All hope is not lost yet, though. There are other places to look. One of them is the X$KGLOB table, which is part of the library cache.

SELECT KGLNAOBJ
  FROM X$KGLOB
 WHERE KGLOBT03 = '2v82uk48x55vt';

This query will yield a value, which on the first look, looks like gibberish. It will read something like "table_4_9_15f8c_0_0_". On the second look, this string containts one interesting part and that is the fourth underscore separated segment: 15f8c. This, in fact, is the hexadecimal representation of the object_id of the object involved in the operation. Now remains only to query the DBA_OBJECTS view to find out, which table is being operated on.

SELECT OWNER, OBJECT_NAME, OBJECT_TYPE
  FROM DBA_OBJECTS
 WHERE OBJECT_ID=TO_NUMBER('15f8c','XXXXXXXX');

And there you have it, the schema and the name of the table, which was causing that unknown SQL in your stats.

Performance analysis reports in Oracle

Sometimes you wonder why your database is slow or you just want to know what those oompa-loompas inside the Oracle black box are up to. One of the tools in your investigation can be the so-called Automatic Workload Repository, which contains an overload of information about what is going on in your database.

Some information from AWR can be seen inside the Enterprise manager console, but for serious analysis you will want to generate an AWR report in plaintext or more probabaly HTML format.

The report can be generated by running a SQL*Plus script. Logon to your database server as usual and run a following command:


sqlplus / as sysdba @$ORACLE_HOME/rdbms/admin/awrrpti.sql


The script will prompt you for your choices about how you want your report.

1. First, you must choose an output format. You probably will want a html version, but sometimes Oracle support team will ask you for a plaintext version.
2. You must enter a database ID. You most likely have only one database and the available choices are listed, so just copy the ten digit DB ID here.
3. If you run more than one instance, enter the number of the instance you want your report from. Unfortunately, you cannot generate a cumulative report for all your instances in RAC environment, so just pick one.
4. The script asks you, how far back in days you want to look for AWR snapshots. If you want just the recent activity, enter 1.
5. The script lists all available snapshots for the number of days specified in the previous step. If you entered 1, it will list just today's snapshots. Most commonly, the snapshot is done every hour. First, you must select a beginning snapshot and in the second step you select the ending snapshot. If you want the report for previous whole hour, enter the last two snapshot numbers, for example 2873 and 2874.
6. At last, you get prompted for a file name. The default is probably OK for most people, so just hit enter and the system gets to work on your report.

The generated report HTML file is located in the directory you ran the script from. Transfer the file to your computer or somewhere, where you can access it and open it in your favorite browser.

Your developers will most likely be interested in the section reporting the most frequently and most costly SQL statements. Your DBA will find interesting information about I/O activity, memory advisory tables etc. etc.

In a future article, I will try to point out some interesting sections in this report. Until then, self-study time.