ORACLE DBA: 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.

ORACLE DBA

Wednesday, February 16, 2011

Use COMMENT command to invalidate cursors

No comments:

Post a Comment