Monitoring Table Usage

You’ve recently inherited a database that contains hundreds of tables. The application is experiencing
performance issues. As part of your overall tuning strategy, you want to obtain a better understanding of the application by determining which tables are being used by what types of SQL statements. Tables that aren’t being used can be renamed and later dropped. By removing unused tables, you can free up space, reduce the clutter, and focus your performance analysis on actively used tables.

Use Oracle’s standard auditing feature to determine which tables are being used. Auditing is enabled as follows:

1. Set the AUDIT_TRAIL parameter.
2. Stop and start your database to enable the setting of AUDIT_TRAIL.
3. Use the AUDIT statement to enable auditing of specific database operations.

Oracle’s standard auditing feature is enabled through setting the AUDIT_TRAIL initialization
parameter. When you set the AUDIT_TRAIL parameter to DB, this specifies that Oracle will write audit records to an internal database table named AUD$. For example, when using an spfile, here’s how to set the AUDIT_TRAIL parameter:

SQL> alter system set audit_trail=db scope=spfile;

If you are using an init.ora file, open it with a text editor and set the AUDIT_TRAIL value to DB. After you’ve set the AUDIT_TRAIL parameter, you’ll need to stop and restart your database for it to take effect.

Now you can enable auditing for a specific database operation. For example, the following
statement enables auditing on all DML statements on the EMP table owned by INV_MGMT:

SQL> audit select, insert, update, delete on farhat.user_log;

From this point on, any DML access to the EMP table will be recorded in the SYS.AUD$ table. Oracle provides several auditing views based on the AUD$ table, such as DBA_AUDIT_TRAIL or DBA_AUDIT_OBJECT.
You can query these views to report on auditing actions—for example:

select
username
,obj_name
,to_char(timestamp,'dd-mon-yy hh24:mi') event_time
,substr(ses_actions,4,1) del
,substr(ses_actions,7,1) ins
,substr(ses_actions,10,1) sel
,substr(ses_actions,11,1) upd

from dba_audit_object;

USERNAME OBJ_NAME EVENT_TIME DEL INS SEL UPD

FARHAT USER_LOG1 12-jul-15 16:36 - S - -
FARHAT USER_LOG1 12-jul-15 16:43 - S - -

FARHAT USER_LOG1 12-jul-15 16:44 - - S -

In the prior SQL statement, notice the use of the SUBSTR function to reference the SES_ACTIONS

column of the DBA_AUDIT_OBJECT view. That column contains a 16-character string in which each

character means that a certain operation has occurred. The 16 characters represent the following

operations in this order: ALTER, AUDIT, COMMENT, DELETE, GRANT, INDEX, INSERT, LOCK, RENAME, SELECT, UPDATE,

REFERENCES, and EXECUTE. Positions 14, 15, and 16 are reserved by Oracle for future use. The character of S represents success, F represents failure, and B represents both success and failure.

To turn off auditing on an object, use the NOAUDIT statement:

SQL> noaudit select, insert, update, delete on farhat.user_log1;

If you simply need to know whether a table is being inserted, updated, or deleted from, you can use

the DBA/ALL/USER_TAB_MODIFICATIONS view to report on that type of activity. This view has columns, such as INSERTS, UPDATES, DELETES, and TRUNCATED, that will provide information as to how data in the table is being modified—for example:

select table_name, inserts, updates, deletes, truncated

from user_tab_modifications;

In normal conditions, this view is not instantly updated by Oracle. If you need to immediately view table modifications, then use the DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO procedure to update the view:

SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO();