Auditing in Oracle 10g Release 2

This article presents an overview of auditing in Oracle 10g Release 2. Many of the topics presented here have been covered in previous articles, but this serves to bring them all together.

• Server Setup
• Audit Options
• View Audit Trail
• Maintenance and Security
• Fine Grained Auditing

 

Server Setup

Auditing is a default feature of the Oracle server. The initialization parameters that influence its behaviour can be displayed using the SHOW PARAMETERSQL*Plus command.

SQL> SHOW PARAMETER AUDIT

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      C:\ORACLE\PRODUCT\10.2.0\ADMIN
                                                 \DB10G\ADUMP
audit_sys_operations                 boolean     FALSE
audit_trail                          string      NONE
SQL>

Auditing is disabled by default, but can enabled by setting the AUDIT_TRAIL static parameter, which has the following allowed values.

AUDIT_TRAIL = { none | os | db | db,extended | xml | xml,extended }

The following list provides a description of each setting:

• none or false – Auditing is disabled.
• db or true – Auditing is enabled, with all audit records stored in the database audit trial (SYS.AUD$).
• db,extended – As db, but the SQL_BIND and SQL_TEXT columns are also populated.
• xml- Auditing is enabled, with all audit records stored as XML format OS files.
• xml,extended – As xml, but the SQL_BIND and SQL_TEXT columns are also populated.
• os- Auditing is enabled, with all audit records directed to the operating system’s audit trail.

Note. In Oracle 10g Release 1, db_extended was used in place of db,extended. The XML options are new to Oracle 10g Release 2.

The AUDIT_SYS_OPERATIONS static parameter enables or disables the auditing of operations issued by users connecting with SYSDBA or SYSOPER privileges, including the SYS user. All audit records are written to the OS audit trail.

The AUDIT_FILE_DEST parameter specifies the OS directory used for the audit trail when the os, xml and xml,extended options are used. It is also the location for all mandatory auditing specified by the AUDIT_SYS_OPERATIONS parameter.

To enable auditing and direct audit records to the database audit trail, we would do the following.

SQL> ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;

System altered.

SQL> SHUTDOWN
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP
ORACLE instance started.

Total System Global Area  289406976 bytes
Fixed Size                  1248600 bytes
Variable Size              71303848 bytes
Database Buffers          213909504 bytes
Redo Buffers                2945024 bytes
Database mounted.
Database opened.
SQL>

Audit Options

One look at the AUDIT command syntax should give you an idea of how flexible Oracle auditing is. There is no point repeating all this information, so instead we will look at a simple example.

First we create a new user called AUDIT_TEST.

CONNECT sys/password AS SYSDBA

CREATE USER audit_test IDENTIFIED BY password
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp
  QUOTA UNLIMITED ON users;

GRANT connect TO audit_test;
GRANT create table, create procedure TO audit_test;

Next we audit all operations by the AUDIT_TEST user.

CONNECT sys/password AS SYSDBA

AUDIT ALL BY audit_test BY ACCESS;
AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY audit_test BY ACCESS;
AUDIT EXECUTE PROCEDURE BY audit_test BY ACCESS;

These options audit all DDL and DML, along with some system events.

• DDL (CREATE, ALTER & DROP of objects)
• DML (INSERT UPDATE, DELETE, SELECT, EXECUTE).
• SYSTEM EVENTS (LOGON, LOGOFF etc.)

Next, we perform some operations that will be audited.

CONN audit_test/password

CREATE TABLE test_tab (
  id  NUMBER
);

INSERT INTO test_tab (id) VALUES (1);
UPDATE test_tab SET id = id;
SELECT * FROM test_tab;
DELETE FROM test_tab;

DROP TABLE test_tab;

In the next section we will look at how we view the contents of the audit trail.

View Audit Trail

The audit trail is stored in the SYS.AUD$ table. Its contents can be viewed directly or via the following views:

SELECT view_name
FROM   dba_views
WHERE  view_name LIKE 'DBA%AUDIT%'
ORDER BY view_name;

VIEW_NAME
------------------------------
DBA_AUDIT_EXISTS
DBA_AUDIT_OBJECT
DBA_AUDIT_POLICIES
DBA_AUDIT_POLICY_COLUMNS
DBA_AUDIT_SESSION
DBA_AUDIT_STATEMENT
DBA_AUDIT_TRAIL
DBA_COMMON_AUDIT_TRAIL
DBA_FGA_AUDIT_TRAIL
DBA_OBJ_AUDIT_OPTS
DBA_PRIV_AUDIT_OPTS
DBA_REPAUDIT_ATTRIBUTE
DBA_REPAUDIT_COLUMN
DBA_STMT_AUDIT_OPTS

14 rows selected.

SQL>

The three main views are:

• DBA_AUDIT_TRAIL – Standard auditing only (from AUD$).
• DBA_FGA_AUDIT_TRAIL – Fine-grained auditing only (from FGA_LOG$).
• DBA_COMMON_AUDIT_TRAIL – Both standard and fine-grained auditing.

The most basic view of the database audit trail is provided by the DBA_AUDIT_TRAIL view, which contains a wide variety of information. The following query displays the some of the information from the database audit trail.

COLUMN username FORMAT A10
COLUMN owner    FORMAT A10
COLUMN obj_name FORMAT A10
COLUMN extended_timestamp FORMAT A35

SELECT username,
       extended_timestamp,
       owner,
       obj_name,
       action_name
FROM   dba_audit_trail
WHERE  owner = 'AUDIT_TEST'
ORDER BY timestamp;

USERNAME   EXTENDED_TIMESTAMP                  OWNER      OBJ_NAME   ACTION_NAME
---------- ----------------------------------- ---------- ---------- ----------------------------
AUDIT_TEST 16-FEB-2006 14:16:55.435000 +00:00  AUDIT_TEST TEST_TAB   CREATE TABLE
AUDIT_TEST 16-FEB-2006 14:16:55.514000 +00:00  AUDIT_TEST TEST_TAB   INSERT
AUDIT_TEST 16-FEB-2006 14:16:55.545000 +00:00  AUDIT_TEST TEST_TAB   UPDATE
AUDIT_TEST 16-FEB-2006 14:16:55.592000 +00:00  AUDIT_TEST TEST_TAB   SELECT
AUDIT_TEST 16-FEB-2006 14:16:55.670000 +00:00  AUDIT_TEST TEST_TAB   DELETE
AUDIT_TEST 16-FEB-2006 14:17:00.045000 +00:00  AUDIT_TEST TEST_TAB   DROP TABLE

6 rows selected.

SQL>

When the audit trail is directed to an XML format OS file, it can be read using a text editor or via the V$XML_AUDIT_TRAIL view, which contains similar information to the DBA_AUDIT_TRAIL view.

COLUMN db_user       FORMAT A10
COLUMN object_schema FORMAT A10
COLUMN object_name   FORMAT A10
COLUMN extended_timestamp FORMAT A35

SELECT db_user,
       extended_timestamp,
       object_schema,
       object_name,
       action
FROM   v$xml_audit_trail
WHERE  object_schema = 'AUDIT_TEST'
ORDER BY extended_timestamp;

DB_USER    EXTENDED_TIMESTAMP                  OBJECT_SCH OBJECT_NAM     ACTION
---------- ----------------------------------- ---------- ---------- ----------
AUDIT_TEST 16-FEB-2006 14:14:33.417000 +00:00  AUDIT_TEST TEST_TAB            1
AUDIT_TEST 16-FEB-2006 14:14:33.464000 +00:00  AUDIT_TEST TEST_TAB            2
AUDIT_TEST 16-FEB-2006 14:14:33.511000 +00:00  AUDIT_TEST TEST_TAB            6
AUDIT_TEST 16-FEB-2006 14:14:33.542000 +00:00  AUDIT_TEST TEST_TAB            3
AUDIT_TEST 16-FEB-2006 14:14:33.605000 +00:00  AUDIT_TEST TEST_TAB            7
AUDIT_TEST 16-FEB-2006 14:14:34.917000 +00:00  AUDIT_TEST TEST_TAB           12

6 rows selected.

SQL>

Several fields were added to both the standard and fine-grained audit trails in Oracle 10g, including:

• EXTENDED_TIMESTAMP – A more precise value than the exising TIMESTAMP column.
• PROXY_SESSIONID – Proxy session serial number when an enterprise user is logging in via the proxy method.
• GLOBAL_UID – Global Universal Identifier for an enterprise user.
• INSTANCE_NUMBER – The INSTANCE_NUMBER value from the actioning instance.
• OS_PROCESS – Operating system process id for the oracle process.
• TRANSACTIONID – Transaction identifier for the audited transaction. This column can be used to join to the XID column on the FLASHBACK_TRANSACTION_QUERY view.
• SCN – System change number of the query. This column can be used in flashback queries.
• SQL_BIND – The values of any bind variables if any.
• SQL_TEXT – The SQL statement that initiated the audit action.

The SQL_BIND and SQL_TEXT columns are only populated when the AUDIT_TRAIL parameter is set to db,extended or xml,extended.

Maintenance and Security

Auditing should be planned carefully to control the quantity of audit information. Only audit specific operations or objects of interest. Over time you can refine the level of auditing to match your requirements.

The database audit trail must be deleted, or archived, on a regular basis to prevent the SYS.AUD$ table growing to an unnacceptable size.Only DBAs should have maintenance access to the audit trail. Auditing modifications of the data in the audit trail itself can be achieved using the following statement:

AUDIT INSERT, UPDATE, DELETE ON sys.aud$ BY ACCESS;

The OS and XML audit trails are managed through the OS. These files should be secured at the OS level by assigning the correct file permissions.

Fine Grained Auditing (FGA)

Fine grained auditing extends Oracle standard auditing capabilities by allowing the user to audit actions based on user-defined predicates. It is independant of the AUDIT_TRAIL parameter setting and all audit records are stored in the FGA_LOG$ table, rather than the AUD$ table. The following example illustrates how fine grained auditing is used.

First, create a test table.

CONN audit_test/password

CREATE TABLE emp (
 empno     NUMBER(4) NOT NULL,
 ename     VARCHAR2(10),
 job       VARCHAR2(9),
 mgr       NUMBER(4),
 hiredate  DATE,
 sal       NUMBER(7,2),
 comm      NUMBER(7,2),
 deptno    NUMBER(2)
);

INSERT INTO emp (empno, ename, sal) VALUES (9999, 'Tim', 1);
INSERT INTO emp (empno, ename, sal) VALUES (9999, 'Larry', 50001);
COMMIT;

The following policy audits any queries of salaries greater than £50,000.

CONN sys/password AS sysdba

BEGIN
  DBMS_FGA.add_policy(
    object_schema   => 'AUDIT_TEST',
    object_name     => 'EMP',
    policy_name     => 'SALARY_CHK_AUDIT',
    audit_condition => 'SAL > 50000',
    audit_column    => 'SAL');
END;
/

Querying both employees proves the auditing policy works as expected.

CONN audit_test/password
SELECT sal FROM emp WHERE ename = 'Tim';
SELECT sal FROM emp WHERE ename = 'Larry';

CONN sys/password AS SYSDBA
SELECT sql_text
FROM   dba_fga_audit_trail;

SQL_TEXT
------------------------------------------
SELECT sal FROM emp WHERE ename = 'Larry'

1 row selected.

SQL>

Extra processing can be associated with an FGA event by defining a database procedure and associating this to the audit event. The following example assumes the FIRE_CLERK procedure has been defined:

BEGIN
  DBMS_FGA.add_policy(
    object_schema   => 'AUDIT_TEST',
    object_name     => 'EMP',
    policy_name     => 'SALARY_CHK_AUDIT',
    audit_condition => 'SAL > 50000',
    audit_column    => 'SAL',
    handler_schema  => 'AUDIT_TEST',
    handler_module  => 'FIRE_CLERK',
    enable          => TRUE);
END;
/

The DBMS_FGA package contains the following procedures:

• ADD_POLICY
• DROP_POLICY
• ENABLE_POLICY
• DISABLE_POLICY

In Oracle9i fine grained auditing was limited queries, but in Oracle 10g it has been extended to include DML statements, as shown by the following example.

-- Clear down the audit trail.
CONN sys/password AS SYSDBA
TRUNCATE TABLE fga_log$;
SELECT sql_text FROM dba_fga_audit_trail;

no rows selected.

-- Apply the policy to the SAL column of the EMP table.
BEGIN
  DBMS_FGA.add_policy(
    object_schema   => 'AUDIT_TEST',
    object_name     => 'EMP',
    policy_name     => 'SAL_AUDIT',
    audit_condition => NULL, -- Equivalent to TRUE
    audit_column    => 'SAL',
    statement_types => 'SELECT,INSERT,UPDATE,DELETE');
END;
/

-- Test the auditing.
CONN audit_test/password
SELECT * FROM emp WHERE empno = 9998;
INSERT INTO emp (empno, ename, sal) VALUES (9998, 'Bill', 1);
UPDATE emp SET sal = 10 WHERE empno = 9998;
DELETE emp WHERE empno = 9998;
ROLLBACK;

-- Check the audit trail.
CONN sys/password AS SYSDBA
SELECT sql_text FROM dba_fga_audit_trail;

SQL_TEXT
--------------------------------------
SELECT * FROM emp WHERE empno = 9998
INSERT INTO emp (empno, ename, sal) VALUES (9998, 'Bill', 1)
UPDATE emp SET sal = 10 WHERE empno = 9998
DELETE emp WHERE empno = 9998

4 rows selected.

-- Drop the policy.
CONN sys/password AS SYSDBA
BEGIN
  DBMS_FGA.drop_policy(
    object_schema   => 'AUDIT_TEST',
    object_name     => 'EMP',
    policy_name     => 'SAL_AUDIT');
END;
/

 

Automatic Database Diagnostic Monitor (ADDM) in Oracle Database 10g

This article describes several methods for producing reports from the Automatic Database Diagnostic Monitor (ADDM) in Oracle 10g.

• Overview
• Enterprise Manager
• addmrpt.sql Script
• DBMS_ADVISOR
• Related Views

Overview

The Automatic Database Diagnostic Monitor (ADDM) analyzes data in the Automatic Workload Repository (AWR) to identify potential performance bottlenecks. For each of the identified issues it locates the root cause and provides recommendations for correcting the problem. An ADDM analysis task is performed and its findings and recommendations stored in the database every time an AWR snapshot is taken provided the STATISTICS_LEVEL parameter is set to TYPICAL or ALL. The ADDM analysis includes the following.

• CPU load
• Memory usage
• I/O usage
• Resource intensive SQL
• Resource intensive PL/SQL and Java
• RAC issues
• Application issues
• Database configuration issues
• Concurrency issues
• Object contention

There are several ways to produce reports from the ADDM analysis which will be explained later, but all follow the same format. The findings (problems) are listed in order of potential impact on database performance, along with recommendations to resolve the issue and the symptoms which lead to it’s discovery. An example from my test instance is shown below.

FINDING 1: 59% impact (944 seconds)
-----------------------------------
The buffer cache was undersized causing significant additional read I/O.

   RECOMMENDATION 1: DB Configuration, 59% benefit (944 seconds)
      ACTION: Increase SGA target size by increasing the value of parameter
         "sga_target" by 28 M.

   SYMPTOMS THAT LED TO THE FINDING:
      Wait class "User I/O" was consuming significant database time. (83%
      impact [1336 seconds])

The recommendations may include:

• Hardware changes
• Database configuration changes
• Schema changes
• Application changes
• Using other advisors

The analysis of I/O performance is affected by the DBIO_EXPECTED parameter which should be set to the average time (in microseconds) it takes to read a single database block from disk. Typical values range from 5000 to 20000 microsoconds. The parameter can be set using the following.

EXECUTE DBMS_ADVISOR.set_default_task_parameter('ADDM', 'DBIO_EXPECTED', 8000);

Enterprise Manager

The obvious place to start viewing ADDM reports is Enterprise Manager. The “Performance Analysis” section on the “Home” page is a list of the top five findings from the last ADDM analysis task.

Specific reports can be produced by clicking on the “Advisor Central” link, then the “ADDM” link. The resulting page allows you to select a start and end snapshot, create an ADDM task and display the resulting report by clicking on a few links.

addmrpt.sql Script

The addmrpt.sql script can be used to create an ADDM report from SQL*Plus. The script is called as follows.

-- UNIX
@/u01/app/oracle/product/10.1.0/db_1/rdbms/admin/addmrpt.sql

-- Windows
@d:oracleproduct10.1.0db_1rdbmsadminaddmrpt.sql

It then lists all available snapshots and prompts you to enter the start and end snapshot along with the report name.

DBMS_ADVISOR

The DBMS_ADVISOR package can be used to create and execute any advisor tasks, including ADDM tasks. The following example shows how it is used to create, execute and display a typical ADDM report.

BEGIN
  -- Create an ADDM task.
  DBMS_ADVISOR.create_task (
    advisor_name      => 'ADDM',
    task_name         => '970_1032_AWR_SNAPSHOT',
    task_desc         => 'Advisor for snapshots 970 to 1032.');

  -- Set the start and end snapshots.
  DBMS_ADVISOR.set_task_parameter (
    task_name => '970_1032_AWR_SNAPSHOT',
    parameter => 'START_SNAPSHOT',
    value     => 970);

  DBMS_ADVISOR.set_task_parameter (
    task_name => '970_1032_AWR_SNAPSHOT',
    parameter => 'END_SNAPSHOT',
    value     => 1032);

  -- Execute the task.
  DBMS_ADVISOR.execute_task(task_name => '970_1032_AWR_SNAPSHOT');
END;
/

-- Display the report.
SET LONG 100000
SET PAGESIZE 50000
SELECT DBMS_ADVISOR.get_task_report('970_1032_AWR_SNAPSHOT') AS report
FROM   dual;
SET PAGESIZE 24

The value for the SET LONG command should be adjusted to allow the whole report to be displayed.

The relevant AWR snapshots can be identified using the DBA_HIST_SNAPSHOT view.

Related Views

The following views can be used to display the ADDM output without using Enterprise Manager or the GET_TASK_REPORT function.

• DBA_ADVISOR_TASKS – Basic information about existing tasks.
• DBA_ADVISOR_LOG – Status information about existing tasks.
• DBA_ADVISOR_FINDINGS – Findings identified for an existing task.
• DBA_ADVISOR_RECOMMENDATIONS – Recommendations for the problems identified by an existing task.

Automatic SQL Tuning in Oracle Database 10g

This article the discusses the new features which automate the tuning of SQL statements in Oracle 10g:

• Overview
• SQL Tuning Advisor
• Managing SQL Profiles
• SQL Tuning Sets
• Useful Views

Overview

In its normal mode the query optimizer needs to make decisions about execution plans in a very short time. As a result it may not always be able to obtain enough information to make the best decision. Oracle 10g allows the optimizer to run in tuning mode where it can gather additional information and make recommendations about how specific statements can be tuned further. This process may take several minutes for a single statement so it is intended to be used on high-load resource-intensive statements.

In tuning mode the optimizer performs the following analysis:

• Statistics Analysis – The optimizer recommends the gathering of statistics on objects with missing or stale statistics. Additional statistics for these objects are stored in an SQL profile.
• SQL Profiling – The optimizer may be able to improve performance by gathering additional statistics and altering session specific parameters such as the OPTIMIZER_MODE. If such improvements are possible the information is stored in an SQL profile. If accepted this information can then used by the optimizer when running in normal mode. Unlike a stored outline which fixes the execution plan, an SQL profile may still be of benefit when the contents of the table alter drastically. Even so, it’s sensible to update profiles periodically. The SQL profiling is not performed when the tuining optimizer is run in limited mode.
• Access Path Analysis – The optimizer investigates the effect of new or modified indexes on the access path. It’s index recommendations relate to a specific statement so where necessary it will also suggest the use of the SQL Access Advisor to check the impact of these indexes on a representative SQL workload.
• SQL Structure Analysis – The optimizer suggests alternatives for SQL statements that contain structures that may impact on performance. The implementation of these suggestions requires human intervention to check their validity.

The automatic SQL tuning features are accessible from Enterprise Manager on the “Advisor Central” page these or from PL/SQL using the DBMS_SQLTUNE package. This article will focus on the PL/SQL API as the Enterprise Manager interface is reasonably intuative.

SQL Tuning Advisor

In order to access the SQL tuning advisor API a user must be granted the ADVISOR privilege:

CONN sys/password AS SYSDBA
GRANT ADVISOR TO scott;
CONN scott/tiger

The first step when using the SQL tuning advisor is to create a new tuning task using the CREATE_TUNING_TASK function. The statements to be analyzed can be retrieved from the Automatic Workload Repository (AWR), the cursor cache, an SQL tuning set or specified manually:

SET SERVEROUTPUT ON

-- Tuning task created for specific a statement from the AWR.
DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          begin_snap  => 764,
                          end_snap    => 938,
                          sql_id      => '19v5guvsgcd1v',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 60,
                          task_name   => '19v5guvsgcd1v_AWR_tuning_task',
                          description => 'Tuning task for statement 19v5guvsgcd1v in AWR.');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

-- Tuning task created for specific a statement from the cursor cache.
DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          sql_id      => '19v5guvsgcd1v',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 60,
                          task_name   => '19v5guvsgcd1v_tuning_task',
                          description => 'Tuning task for statement 19v5guvsgcd1v.');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

-- Tuning task created from an SQL tuning set.
DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          sqlset_name => 'test_sql_tuning_set',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 60,
                          task_name   => 'sqlset_tuning_task',
                          description => 'Tuning task for an SQL tuning set.');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

-- Tuning task created for a manually specified statement.
DECLARE
  l_sql               VARCHAR2(500);
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql := 'SELECT e.*, d.* ' ||
           'FROM   emp e JOIN dept d ON e.deptno = d.deptno ' ||
           'WHERE  NVL(empno, ''0'') = :empno';

  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          sql_text    => l_sql,
                          bind_list   => sql_binds(anydata.ConvertNumber(100)),
                          user_name   => 'scott',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 60,
                          task_name   => 'emp_dept_tuning_task',
                          description => 'Tuning task for an EMP to DEPT join query.');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

If the TASK_NAME parameter is specified it’s value is returned as the SQL tune task identifier. If ommitted a system generated name like “TASK_1478″ is returned. If the SCOPE parameter is set to scope_limited the SQL profiling analysis is omitted. The TIME_LIMIT parameter simply restricts the time the optimizer can spend compiling the recommendations.

The following examples will reference the last tuning set as it has no external dependancies other than the SCOTT schema. The NVL in the SQL statement was put in to provoke a reaction from the optimizer. In addition we can delete the statistics from one of the tables to provoke it even more:

EXEC DBMS_STATS.delete_table_stats('SCOTT','EMP');

With the tuning task defined the next step is to execute it using the EXECUTE_TUNING_TASK procedure:

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'emp_dept_tuning_task');

During the execution phase you may wish to pause and restart the task, cancel it or reset the task to allow it to be re-executed:

-- Interrupt and resume a tuning task.
EXEC DBMS_SQLTUNE.interrupt_tuning_task (task_name => 'emp_dept_tuning_task');
EXEC DBMS_SQLTUNE.resume_tuning_task (task_name => 'emp_dept_tuning_task');

-- Cancel a tuning task.
EXEC DBMS_SQLTUNE.cancel_tuning_task (task_name => 'emp_dept_tuning_task');

-- Reset a tuning task allowing it to be re-executed.
EXEC DBMS_SQLTUNE.reset_tuning_task (task_name => 'emp_dept_tuning_task');

The status of the tuning task can be monitored using the DBA_ADVISOR_LOG view:

SELECT task_name, status FROM dba_advisor_log WHERE owner = 'SCOTT';

TASK_NAME                      STATUS
------------------------------ -----------
emp_dept_tuning_task           COMPLETED

1 row selected.

Once the tuning task has executed successfully the recommendations can be displayed using the REPORT_TUNING_TASK function:

SET LONG 10000;
SET PAGESIZE 1000
SET LINESIZE 200
SELECT DBMS_SQLTUNE.report_tuning_task('emp_dept_tuning_task') AS recommendations FROM dual;
SET PAGESIZE 24

In this case the output looks like this:

RECOMMENDATIONS
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : emp_dept_tuning_task
Scope              : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status  : COMPLETED
Started at         : 05/06/2004 09:29:13
Completed at       : 05/06/2004 09:29:15

-------------------------------------------------------------------------------
SQL ID  : 0wrmfv2yvswx1
SQL Text: SELECT e.*, d.* FROM   emp e JOIN dept d ON e.deptno = d.deptno
          WHERE  NVL(empno, '0') = :empno

-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------

1- Statistics Finding
---------------------
  Table "SCOTT"."EMP" and its indices were not analyzed.

  Recommendation
  --------------
    Consider collecting optimizer statistics for this table and its indices.
    execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname =>
            'EMP', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
            method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE)

  Rationale
  ---------
    The optimizer requires up-to-date statistics for the table and its indices
    in order to select a good execution plan.

2- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
  The predicate NVL("E"."EMPNO",0)=:B1 used at line ID 2 of the execution plan
  contains an expression on indexed column "EMPNO". This expression prevents
  the optimizer from selecting indices on table "SCOTT"."EMP".

  Recommendation
  --------------
    Rewrite the predicate into an equivalent form to take advantage of
    indices. Alternatively, create a function-based index on the expression.

  Rationale
  ---------
    The optimizer is unable to use an index if the predicate is an inequality
    condition or if there is an expression or an implicit data type conversion
    on the indexed column.

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original
-----------
Plan hash value: 1863486531

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     1 |   107 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |         |     1 |   107 |     4   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL          | EMP     |     1 |    87 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)| 00:00:01 |
|   4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

-------------------------------------------------------------------------------

1 row selected.

Once the tuning session is over the tuning task can be dropped using the DROP_TUNING_TASK procedure:

BEGIN
  DBMS_SQLTUNE.drop_tuning_task (task_name => '19v5guvsgcd1v_AWR_tuning_task');
  DBMS_SQLTUNE.drop_tuning_task (task_name => '19v5guvsgcd1v_tuning_task');
  DBMS_SQLTUNE.drop_tuning_task (task_name => 'sqlset_tuning_task');
  DBMS_SQLTUNE.drop_tuning_task (task_name => 'emp_dept_tuning_task');
END;
/

Managing SQL Profiles

To manage SQL profiles a user needs the following privileges:

CONN sys/password AS SYSDBA
GRANT CREATE ANY SQL PROFILE TO scott;
GRANT DROP ANY SQL PROFILE TO scott;
GRANT ALTER ANY SQL PROFILE TO scott;
CONN scott/tiger

If the recommendations of the SQL tuning advisor include a suggested profile you can choose to accept it using the ACCEPT_SQL_PROFILE procedure:

SET SERVEROUTPUT ON
DECLARE
  l_sql_tune_task_id  VARCHAR2(20);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.accept_sql_profile (
                          task_name => 'emp_dept_tuning_task',
                          name      => 'emp_dept_profile');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/