Auditing DML operation on tables

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 USER_LOG table owned by FTEST:

SQL> AUDIT SELECT, INSERT, UPDATE, DELETE ON FTEST.USER_LOG;

From this point on, any DML access to the USER_LOG 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;

**********************************************

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 INV_MGMT.EMP;

How It Works

Sometimes it’s handy when troubleshooting disk space or performance issues to know which tables in
the database are actually being used by the application. If you’ve inherited a database that contains a
large number of tables, it may not be obvious which objects are being accessed. Enabling auditing allows
you to identify which types of SQL statements are accessing a table of interest.
Once you have identified tables that are not being used, you can simply rename the tables and see if
this breaks the application or if any users complain. If there are no complaints, then after some
time you can consider dropping the tables. Make sure you take a good backup of your database with both
RMAN and Data Pump before you drop any tables you might have to later recover.

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();

When the Segment Advisor executes, it uses the Automatic Workload Repository (AWR) for the
source of information for its analysis. For example, the Segment Advisor examines usage and growth
statistics in the AWR to generate segment advice. When the Segment Advisor runs, it generates advice
and stores the output in internal database tables. The advice and recommendations can be viewed via
data dictionary views such as the following:
• DBA_ADVISOR_EXECUTIONS
• DBA_ADVISOR_FINDINGS
• DBA_ADVISOR_OBJECTS
There are three different tools for retrieving the Segment Advisor’s output:
• Executing DBMS_SPACE.ASA_RECOMMENDATIONS
• Manually querying DBA_ADVISOR_* views
• Viewing Enterprise Manager’s graphical screens
In the “Solution” section, we described how to use the DBMS_SPACE.ASA_RECOMMENDATIONS procedure
to retrieve the Segment Advisor advice. The ASA_RECOMMENDATIONS output can be modified via three input
parameters, which are described in Table 1-5. For example, you can instruct the procedure to show
information generated when you have manually executed the Segment Advisor.

Query segment advisor advices

SELECT
'Task Name : ' || f.task_name || chr(10) ||
'Segment Name : ' || o.attr2 || chr(10) ||
'Segment Type : ' || o.type || chr(10) ||
'Partition Name : ' || o.attr3 || chr(10) ||
'Message : ' || f.message || chr(10) ||
'More Info : ' || f.more_info TASK_ADVICE
FROM dba_advisor_findings f
,dba_advisor_objects o


Rebuilding Rows Spanning Multiple Blocks

You have a table in which individual rows are stored in more than one block. That situation leads to
higher rates of I/O, and causes queries against the table to run slowly. You want to rebuild the spanned
rows such that each row fits into a single block.