Use COMMENT command to invalidate cursors

I create a table which has in column LOCATION the value 'ATHENS' 99,998 times, the values 'PATRAS' and 'TRIPOLI' once.
I also create an index on column LOCATION.

CREATE TABLE EMPLOYEES
(
   EMPID      NUMBER (6),
   LOCATION   VARCHAR2 (10)
);

CREATE INDEX EMPLOYEES_IND
   ON EMPLOYEES (LOCATION);

BEGIN
   FOR I IN 1 .. 99998
   LOOP
      INSERT INTO EMPLOYEES
           VALUES (I, 'ATHENS');
   END LOOP;

   INSERT INTO EMPLOYEES
        VALUES (99999, 'PATRAS');

   INSERT INTO EMPLOYEES
        VALUES (100000, 'TRIPOLI');

   COMMIT;
END;
/

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'HELPDESK', TABNAME => 'EMPLOYEES', METHOD_OPT => 'For All Indexed Columns Size Auto', CASCADE => TRUE);

I run the following query.

SQL> CONN HELPDESK@ECDBTST
Enter password: ********
Connected.

SQL> VAR B1 VARCHAR2(10)
SQL> EXEC :B1 := 'ATHENS'
PL/SQL procedure successfully completed.

SQL> SELECT *
2 FROM EMPLOYEES
3 WHERE LOCATION=:B1;

EMPID LOCATION
--------------
9087 ATHENS
9088 ATHENS
9089 ATHENS
...
89414 ATHENS
89415 ATHENS
89416 ATHENS

99998 rows selected.

B1 has value 'ATHENS', so CBO should have favored a Full Table Scan (FTS), since I request 99,998 of 100,000 rows to be returned.

SELECT SQL_TEXT, SQL_ID
  FROM V$SQL
 WHERE SQL_TEXT LIKE 'SELECT * FROM EMPLOYEES%';

 

SQL_TEXT	SQL_ID
SELECT * FROM EMPLOYEES WHERE LOCATION=:B1	ffyj5j3gjjcpj

SELECT SQL_ID,
       NAME,
       DATATYPE_STRING,
       VALUE_STRING
  FROM V$SQL_BIND_CAPTURE
 WHERE SQL_ID = 'ffyj5j3gjjcpj';

SQL_ID	NAME	DATATYPE_STRING	VALUE_STRING
ffyj5j3gjjcpj	:B1	VARCHAR(32)	ATHENS

SELECT *
  FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR ('ffyj5j3gjjcpj', NULL, 'ADVANCED'));

SQL_ID ffyj5j3gjjcpj, child number 0
-------------------------------------
SELECT * FROM EMPLOYEES WHERE LOCATION=:B1

Plan hash value: 1342275408

---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 98418 | 1057K| 7 |
---------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1 / EMPLOYEES@SEL$1

Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
OPT_PARAM('_b_tree_bitmap_plans' 'false')
OPT_PARAM('_optimizer_cost_model' 'io')
OPT_PARAM('_fast_full_scan_enabled' 'false')
OPT_PARAM('_optimizer_skip_scan_enabled' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "EMPLOYEES"@"SEL$1")
END_OUTLINE_DATA
*/

Peeked Binds (identified by position):
--------------------------------------

1 - :B1 (VARCHAR2(30), CSID=37): 'ATHENS'

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("LOCATION"=:B1)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - "EMPLOYEES"."EMPID"[NUMBER,22], "LOCATION"[VARCHAR2,10]

Note
-----
- cpu costing is off (consider enabling it)

CBO was correct and chose a FTS.
Now I run the same query, setting first B1 equal to 'PATRAS':

SQL> EXEC :B1 := 'PATRAS'
PL/SQL procedure successfully completed.

SQL> SELECT *
2 FROM EMPLOYEES
3 WHERE LOCATION=:B1;

EMPID LOCATION
--------------
99999 PATRAS

We would expect CBO to choose an Index Range Scan, since I request only 1 of 100,000 rows to be returned.

SELECT SQL_ID,
       NAME,
       DATATYPE_STRING,
       VALUE_STRING
  FROM V$SQL_BIND_CAPTURE
 WHERE SQL_ID = 'ffyj5j3gjjcpj';

SQL_ID	NAME	DATATYPE_STRING	VALUE_STRING
ffyj5j3gjjcpj	:B1	VARCHAR(32)	ATHENS

SELECT *
  FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR ('ffyj5j3gjjcpj', NULL, 'ADVANCED'));

SQL_ID ffyj5j3gjjcpj, child number 0
-------------------------------------
SELECT * FROM EMPLOYEES WHERE LOCATION=:B1

Plan hash value: 1342275408

---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 98418 | 1057K| 7 |
---------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1 / EMPLOYEES@SEL$1

Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
OPT_PARAM('_b_tree_bitmap_plans' 'false')
OPT_PARAM('_optimizer_cost_model' 'io')
OPT_PARAM('_fast_full_scan_enabled' 'false')
OPT_PARAM('_optimizer_skip_scan_enabled' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "EMPLOYEES"@"SEL$1")
END_OUTLINE_DATA
*/

Peeked Binds (identified by position):
--------------------------------------

1 - :B1 (VARCHAR2(30), CSID=37): 'ATHENS'

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("LOCATION"=:B1)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - "EMPLOYEES"."EMPID"[NUMBER,22], "LOCATION"[VARCHAR2,10]

Note
-----
- cpu costing is off (consider enabling it)

Unfortunately, even now a FTS is chosen.
You may also noticed B1 peek bind remains 'ATHENS'.
According to Oracle Note 430208.1: "Bind Peeking By Example":

    The query optimizer peeks at the values of user-defined bind variables on the first invocation of a cursor. This feature lets the optimizer determine the selectivity of any WHERE clause condition, as well as if literals have been used instead of bind variables.
    On subsequent invocations of the cursor, no peeking takes place, and the cursor is shared, based on the standard cursor-sharing criteria, even if subsequent invocations use different bind values.

So, the cursor should be invalidated, if you want a new execution plan to be chosen.
According to the same note:

    To invalidate any cursor, one of the followings can be done:

    1. Issue the command Alter System Flush Shared_Pool;
    2. Delete or change the cursor underlying objects statistics
    3. Grant and/or revoke privileges on any of the cursor underlying objects
    4. Alter cursor underlying objects
    5. Bounce the instance

All these choices are usually very difficult to be used in a 24/7 production system.
A much easier way to invalidate cursors is to insert a simple comment in one of the tables used by your query.

SQL> COMMENT ON TABLE EMPLOYEES IS 'TEST';
Comment created.

Now, the cursor is invalidated:

SELECT SQL_TEXT, SQL_ID
  FROM V$SQL
 WHERE SQL_TEXT LIKE 'SELECT * FROM EMPLOYEES%';
no rows selected

SELECT *
  FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR ('ffyj5j3gjjcpj', NULL, 'ADVANCED'));
SQL_ID: ffyj5j3gjjcpj cannot be found

I run again the query with B1='PATRAS':

SQL> EXEC :B1 := 'PATRAS'
PL/SQL procedure successfully completed.

SQL> SELECT *
2 FROM EMPLOYEES
3 WHERE LOCATION=:B1;

EMPID LOCATION
--------------
99999 PATRAS

Let's see the execution plan:

SELECT *
  FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR ('ffyj5j3gjjcpj', NULL, 'ADVANCED'));

SQL_ID ffyj5j3gjjcpj, child number 0
-------------------------------------
SELECT * FROM EMPLOYEES WHERE LOCATION=:B1

Plan hash value: 211055403

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 18 | 198 | 2 |
|* 2 | INDEX RANGE SCAN | EMPLOYEES_IND | 18 | | 1 |
-----------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1 / EMPLOYEES@SEL$1
2 - SEL$1 / EMPLOYEES@SEL$1

Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
OPT_PARAM('_b_tree_bitmap_plans' 'false')
OPT_PARAM('_optimizer_cost_model' 'io')
OPT_PARAM('_fast_full_scan_enabled' 'false')
OPT_PARAM('_optimizer_skip_scan_enabled' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "EMPLOYEES"@"SEL$1" ("EMPLOYEES"."LOCATION"))
END_OUTLINE_DATA
*/

Peeked Binds (identified by position):
--------------------------------------

1 - :B1 (VARCHAR2(30), CSID=37): 'PATRAS'

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("LOCATION"=:B1)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - "EMPLOYEES"."EMPID"[NUMBER,22], "LOCATION"[VARCHAR2,10]
2 - "EMPLOYEES".ROWID[ROWID,10], "LOCATION"[VARCHAR2,10]

Note
-----
- cpu costing is off (consider enabling it)

We can see CBO used an Index Range Scan this time.
The good thing is, although COMMENT is a DDL statement, no invalidations will happen to objects dependent on the table you just inserted the comment.

10g & 11g: In new patchsets auditing by session is "obsolete"

We create a table:

CREATE TABLE HELPDESK.AUD_TEST(TEST NUMBER);

We start auditing user helpdesk "insert table" statements by session:

AUDIT INSERT TABLE BY HELPDESK BY SESSION;

We verify it:

SELECT *
  FROM DBA_STMT_AUDIT_OPTS A
 WHERE A.USER_NAME = 'HELPDESK';

  

USER_NAME	PROXY_NAME	AUDIT_OPTION	SUCCESS	FAILURE
HELPDESK		INSERT TABLE	BY SESSION	BY SESSION

We open a sqlplus, connect as helpdesk and insert 6 rows in aud_test table:

SQL> CONN HELPDESK@ECDB1
Enter password: ********
Connected.

SQL> INSERT INTO AUD_TEST VALUES (1);
1 row created.

SQL> COMMIT;
Commit complete.

SQL> INSERT INTO AUD_TEST VALUES (1);
1 row created.

SQL> INSERT INTO AUD_TEST VALUES (1);
1 row created.

SQL> INSERT INTO AUD_TEST VALUES (1);
1 row created.

SQL> INSERT INTO AUD_TEST VALUES (1);
1 row created.

SQL> INSERT INTO AUD_TEST VALUES (1);
1 row created.

SQL> COMMIT;
Commit complete.

We query aud$:

SELECT SESSIONID,
       USERID,
       ACTION#,
       OBJ$CREATOR,
       OBJ$NAME,
       SES$ACTIONS,
       NTIMESTAMP#
  FROM SYS.AUD$
 WHERE USERID = 'HELPDESK';

SESSIONID	USERID	ACTION#	OBJ$CREATOR	OBJ$NAME	SES$ACTIONS	NTIMESTAMP#
290823972	HELPDESK	103	HELPDESK	AUD_TEST	------S---------	11/2/2011 08:48:17
290823972	HELPDESK	103	HELPDESK	AUD_TEST	------S---------	11/2/2011 08:48:19
290823972	HELPDESK	103	HELPDESK	AUD_TEST	------S---------	11/2/2011 08:48:20
290823972	HELPDESK	103	HELPDESK	AUD_TEST	------S---------	11/2/2011 08:48:21
290823972	HELPDESK	103	HELPDESK	AUD_TEST	------S---------	11/2/2011 08:48:21
290823972	HELPDESK	103	HELPDESK	AUD_TEST	------S---------	11/2/2011 08:47:49

You would expect to see only one row, for the 6 insert statements executed by one session in the same table.
But, instead, we get 6 rows in the AUD$ table, as if we had chosen the "by access" auditing method.
According to Oracle Note 6023472.8:

    Bug 6023472 Collection of changes to auditing

    This issue is fixed in:
    10.2.0.5 (Server Patch Set)
    11.1.0.7 (Server Patch Set)
    11.2.0.1 (Base Release)

    This fix covers several changes to audit behaviour:

    1. The LOGON (action# = 100) audit records are no longer updated. So, when a session ends and if sessions are audited, a LOGOFF (action# = 101) audit record will be written as a separate audit record.
    2. Auditing configured as 'By Session' will no longer produce one audit record per session. Each occurrence of the audit event will result in newer audit records since no audit record is updated.
    3. The index SYS.I_AUD1 on the AUD$ table is dropped since there are no longer any updates to AUD$ with this fix.
    4. FGA_LOG$ will not have PLHOL column.
    5. OS/XML Audit filenames will have a filename format of the form:
    ___.
    6. The default value for Max Audit FileSize is 1000KB and Max Audit FileAge is 1 Day. This means that once one of these max limits are reached, the audit records will be written in a new audit file with same "___" part, but with a new "".

According to (2), auditing by session is, in essence, no longer supported for these patchsets and onwards.