Mine works fine see below demonstration:
Note: CBO will only consider up to "select * from emp where empno=" as the
value of the where is binded and can be anything.
in your to use the recorded plan your SQL statement must match the one of
the outline(space to space, character to character etc..)
SQL> select * from emp where empno=1;
no rows selected
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=32)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=1 Card=1 Byte
s=32)
2 1 INDEX (UNIQUE SCAN) OF 'PK_EMP' (UNIQUE)
SQL> CREATE OR REPLACE OUTLINE test1 for category batch on select * from emp
where empno=:D
2 /
Outline created.
SQL> select OL_NAME, SQL_TEXT from ol$ where category='BATCH';
OL_NAME SQL_TEXT
------------------------------ --------------------------------------
TEST1 select * from emp where empno=:D
Here's my demontration
SQL> select * from emp where empno=7782;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
---------- ---------- --------- ---------- --------- ---------- ----------
----------
7782 CLARK MANAGER 7839 09-JUN-81 2450
10
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=1 Card=1 Bytes=3
2)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=1 Card=1 Byte
s=32)
2 1 INDEX (UNIQUE SCAN) OF 'PK_EMP' (UNIQUE)
select ol_name, hint_type, hint_text from outln.ol$hints;
OL_NAME HINT_TYPE HINT_TEXT
------------------------------ ---------- -----------------------
TEST1 28 NO_EXPAND
TEST1 31 ORDERED
TEST1 37 NO_FACT(EMP)
TEST1 6 INDEX(EMP PK_EMP)
TEST1 30 NOREWRITE
TEST1 30 NOREWRITE
Regards,
Regis
*********************************************************************
This electronic transmission is strictly confidential and intended solely
for the addressee. It may contain information which is covered by legal,
professional or other privilege. If you are not the intended addressee,
you must not disclose, copy or take any action in reliance of this
transmission. If you have received this transmission in error,
please notify the sender as soon as possible.
This footnote also confirms that this message has been swept
for computer viruses.
**********************************************************************
Oracle LazyDBA home page