Get cursors from shared pool
SQL> select * from
table(dbms_xplan.display_cursor('3sdab0awk8g3c',null));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 3sdab0awk8g3c,
child number 0
-------------------------------------
select fp_id from ftable where ftable.fp_name like
:"SYS_B_0" and rownum=:"SYS_B_1"
Plan hash value: 3326224603
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | | |
| 622K(100)| | |
| |
|* 1 | COUNT STOPKEY | | |
| | | |
| |
| 2 | PX COORDINATOR | | |
| | | |
| |
| 3 | PX SEND QC (RANDOM) | :TQ10002 |
1353K| 81M| 622K (12)| 00:04:05 | Q1,02 | P->S | QC (RAND) |
|* 4 | COUNT STOPKEY | | |
| | |
Q1,02 | PCWC | |
| 5 | VIEW | index$_join$_001 | 1353K|
81M| 622K (12)| 00:04:05 | Q1,02 | PCWP | |
|* 6 | HASH JOIN BUFFERED | | |
| | |
Q1,02 | PCWP | |
| 7 | BUFFER SORT | | |
| | |
Q1,02 | PCWC | |
| 8 | PX RECEIVE | | 1353K|
81M| 196K (12)| 00:01:17 | Q1,02 | PCWP | |
| 9 | PX SEND HASH | :TQ10000 |
1353K| 81M| 196K (12)| 00:01:17 | | S->P | HASH |
| 10 | INDEX FAST FULL SCAN| PK_FTABLE |
1353K| 81M| 196K (12)| 00:01:17 | |
| |
| 11 | PX RECEIVE | | 1353K|
81M| 439K (7)| 00:02:53 | Q1,02 | PCWP | |
| 12 | PX SEND HASH | :TQ10001 |
1353K| 81M| 439K
(7)| 00:02:53 | Q1,01 | P->P |
HASH |
| 13 | PX BLOCK ITERATOR | | 1353K|
81M| 439K (7)| 00:02:53 | Q1,01 | PCWC | |
|* 14 |
INDEX FAST FULL SCAN| UNIQUE_FP_NAME
| 1353K| 81M|
439K (7)| 00:02:53 | Q1,01 | PCWP | |
-------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 -
filter(ROWNUM=:SYS_B_1)
4 -
filter(ROWNUM=:SYS_B_1)
6 -
access(ROWID=ROWID)
14 -
access(:Z>=:Z AND :Z<=:Z)
filter("FTABLE"."FP_NAME"
LIKE :SYS_B_0)
SQL_ID 3sdab0awk8g3c,
child number 1
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
select fp_id from ftable where ftable.fp_name like
:"SYS_B_0" and rownum=:"SYS_B_1"
NOTE: cannot fetch plan for SQL_ID: 3sdab0awk8g3c,
CHILD_NUMBER: 1
Please verify
value of SQL_ID and CHILD_NUMBER;
It could also be
that the plan is no longer in cursor cache (check v$sql_plan)
SQL_ID 3sdab0awk8g3c,
child number 2
-------------------------------------
select fp_id from ftable where ftable.fp_name like
:"SYS_B_0" and rownum=:"SYS_B_1"
Plan hash value: 3326224603
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | | |
| 622K(100)| | |
| |
|* 1 | COUNT STOPKEY | | |
| | | |
| |
| 2 | PX COORDINATOR | | |
| | | |
| |
| 3 | PX SEND QC (RANDOM) | :TQ10002 |
1353K| 81M| 622K (12)| 00:04:05 | Q1,02 | P->S | QC (RAND) |
|* 4 | COUNT STOPKEY | | |
| | |
Q1,02 | PCWC | |
| 5 | VIEW | index$_join$_001 | 1353K|
81M| 622K (12)| 00:04:05 | Q1,02 | PCWP | |
|* 6 | HASH JOIN BUFFERED | | |
| | |
Q1,02 | PCWP | |
| 7 | BUFFER SORT | | |
| | |
Q1,02 | PCWC | |
| 8 | PX RECEIVE | | 1353K|
81M| 196K (12)| 00:01:17 | Q1,02 | PCWP | |
| 9 | PX SEND HASH | :TQ10000 |
1353K| 81M| 196K (12)| 00:01:17 | | S->P | HASH |
| 10 | INDEX FAST FULL SCAN| PK_FTABLE |
1353K| 81M| 196K (12)| 00:01:17 | |
| |
| 11 | PX RECEIVE | | 1353K|
81M| 439K (7)| 00:02:53 | Q1,02 | PCWP | |
| 12 | PX SEND HASH | :TQ10001 |
1353K| 81M| 439K
(7)| 00:02:53 | Q1,01 | P->P |
HASH |
| 13 | PX BLOCK ITERATOR | | 1353K|
81M| 439K (7)| 00:02:53 | Q1,01 | PCWC | |
|* 14 |
INDEX FAST FULL SCAN| UNIQUE_FP_NAME
| 1353K| 81M|
439K (7)| 00:02:53 | Q1,01 | PCWP | |
-------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=:SYS_B_1)
4 -
filter(ROWNUM=:SYS_B_1)
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
6 -
access(ROWID=ROWID)
14 - access(:Z>=:Z
AND :Z<=:Z)
filter("FTABLE"."FP_NAME"
LIKE :SYS_B_0)
SQL_ID 3sdab0awk8g3c,
child number 3
-------------------------------------
select fp_id from ftable where ftable.fp_name like
:"SYS_B_0" and rownum=:"SYS_B_1"
Plan hash value: 3326224603
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | | |
| 622K(100)| | |
| |
|* 1 | COUNT STOPKEY | | |
| | | |
| |
| 2 | PX COORDINATOR | | |
| | | |
| |
| 3 | PX SEND QC (RANDOM) | :TQ10002 |
1353K| 81M| 622K (12)| 00:04:05 | Q1,02 | P->S | QC (RAND) |
|* 4 | COUNT STOPKEY | | |
| | |
Q1,02 | PCWC | |
| 5 | VIEW | index$_join$_001 | 1353K|
81M| 622K (12)| 00:04:05 | Q1,02 | PCWP | |
|* 6 | HASH JOIN BUFFERED | | |
| | |
Q1,02 | PCWP | |
| 7 | BUFFER SORT | | |
| | |
Q1,02 | PCWC | |
| 8 | PX RECEIVE | | 1353K|
81M| 196K (12)| 00:01:17 | Q1,02 | PCWP | |
| 9 | PX SEND HASH | :TQ10000 |
1353K| 81M| 196K (12)| 00:01:17 | | S->P | HASH |
| 10 | INDEX FAST FULL SCAN| PK_FTABLE |
1353K| 81M| 196K (12)| 00:01:17 | |
| |
| 11 | PX RECEIVE | | 1353K|
81M| 439K (7)| 00:02:53 | Q1,02 | PCWP | |
| 12 | PX SEND HASH | :TQ10001 |
1353K| 81M| 439K
(7)| 00:02:53 | Q1,01 | P->P |
HASH |
| 13 | PX BLOCK ITERATOR | | 1353K|
81M| 439K (7)| 00:02:53 | Q1,01 | PCWC | |
|* 14 |
INDEX FAST FULL SCAN| UNIQUE_FP_NAME
| 1353K| 81M|
439K (7)| 00:02:53 | Q1,01 | PCWP | |
-------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=:SYS_B_1)
4 -
filter(ROWNUM=:SYS_B_1)
6 -
access(ROWID=ROWID)
14 -
access(:Z>=:Z AND :Z<=:Z)
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
filter("FTABLE"."FP_NAME"
LIKE :SYS_B_0)
SQL_ID 3sdab0awk8g3c,
child number 4
-------------------------------------
select fp_id from ftable where ftable.fp_name like
:"SYS_B_0" and rownum=:"SYS_B_1"
Plan hash value: 1013486222
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | | |
| 66952 (100)| | |
|
| 1 | COUNT | | |
| | | |
|
|* 2 | FILTER | | |
| | | |
|
| 3 | TABLE ACCESS BY GLOBAL INDEX ROWID| FTABLE |
1353K| 81M| 66952 (6)| 00:00:27 | ROW L | ROW L |
|* 4 | INDEX RANGE SCAN | UNIQUE_FP_NAME | 243K|
| 3165 (7)| 00:00:02 | |
|
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 -
filter(ROWNUM=TO_NUMBER(:SYS_B_1))
4 - access("FTABLE"."FP_NAME"
LIKE :SYS_B_0)
filter("FTABLE"."FP_NAME"
LIKE :SYS_B_0)
SQL_ID 3sdab0awk8g3c,
child number 8
-------------------------------------
select fp_id from ftable where ftable.fp_name like
:"SYS_B_0" and rownum=:"SYS_B_1"
Plan hash value: 3326224603
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | | |
| 622K(100)| |
| | |
|* 1 | COUNT STOPKEY | | |
| | | |
| |
| 2 | PX COORDINATOR | | |
| | |
| | |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
| 3 | PX SEND QC (RANDOM) | :TQ10002 |
1353K| 81M| 622K (12)| 00:04:05 | Q1,02 | P->S | QC (RAND) |
|* 4 | COUNT STOPKEY | | |
| | |
Q1,02 | PCWC | |
| 5 | VIEW | index$_join$_001 | 1353K|
81M| 622K (12)| 00:04:05 | Q1,02 | PCWP | |
|* 6 | HASH JOIN BUFFERED | | |
| | |
Q1,02 | PCWP | |
| 7 | BUFFER SORT | |
| | | |
Q1,02 | PCWC | |
| 8 | PX RECEIVE | | 1353K|
81M| 196K (12)| 00:01:17 | Q1,02 | PCWP | |
| 9 | PX SEND HASH | :TQ10000 |
1353K| 81M| 196K (12)| 00:01:17 | | S->P | HASH |
| 10 | INDEX FAST FULL SCAN| PK_FTABLE |
1353K| 81M| 196K (12)| 00:01:17 | |
| |
| 11 | PX RECEIVE | | 1353K|
81M| 439K (7)| 00:02:53 | Q1,02 | PCWP | |
| 12 | PX SEND HASH | :TQ10001 |
1353K| 81M| 439K
(7)| 00:02:53 | Q1,01 | P->P |
HASH |
| 13 | PX BLOCK ITERATOR | | 1353K|
81M| 439K (7)| 00:02:53 | Q1,01 | PCWC | |
|* 14 |
INDEX FAST FULL SCAN| UNIQUE_FP_NAME
| 1353K| 81M|
439K (7)| 00:02:53 | Q1,01 | PCWP | |
-------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 -
filter(ROWNUM=:SYS_B_1)
4 -
filter(ROWNUM=:SYS_B_1)
6 -
access(ROWID=ROWID)
14 -
access(:Z>=:Z AND :Z<=:Z)
filter("FTABLE"."FP_NAME"
LIKE :SYS_B_0)
SQL_ID 3sdab0awk8g3c,
child number 9
-------------------------------------
select fp_id from ftable where ftable.fp_name like
:"SYS_B_0" and rownum=:"SYS_B_1"
Plan hash value: 3326224603
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | | |
| 622K(100)| | |
| |
|* 1 | COUNT STOPKEY | | |
| | | |
| |
| 2 | PX COORDINATOR | | |
| | | |
| |
| 3 | PX SEND QC (RANDOM) | :TQ10002 |
1353K| 81M| 622K (12)| 00:04:05 | Q1,02 | P->S | QC (RAND) |
|* 4 | COUNT STOPKEY | | |
| | |
Q1,02 | PCWC | |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
| 5 | VIEW | index$_join$_001 | 1353K|
81M| 622K (12)| 00:04:05 | Q1,02 | PCWP | |
|* 6 | HASH JOIN BUFFERED | | |
| |
| Q1,02 | PCWP | |
| 7 | BUFFER SORT | | |
| | |
Q1,02 | PCWC | |
| 8 | PX RECEIVE | | 1353K|
81M| 196K (12)| 00:01:17 | Q1,02 | PCWP | |
| 9 | PX SEND HASH | :TQ10000 |
1353K| 81M| 196K (12)| 00:01:17 | | S->P | HASH |
| 10 | INDEX FAST FULL SCAN| PK_FTABLE |
1353K| 81M| 196K (12)| 00:01:17 | |
| |
| 11 | PX RECEIVE | | 1353K|
81M| 439K (7)| 00:02:53 | Q1,02 | PCWP | |
| 12 | PX SEND HASH | :TQ10001 |
1353K| 81M| 439K
(7)| 00:02:53 | Q1,01 | P->P |
HASH |
| 13 | PX BLOCK ITERATOR | | 1353K|
81M| 439K (7)| 00:02:53 | Q1,01 | PCWC | |
|* 14 |
INDEX FAST FULL SCAN| UNIQUE_FP_NAME
| 1353K| 81M|
439K (7)| 00:02:53 | Q1,01 | PCWP | |
-------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 -
filter(ROWNUM=:SYS_B_1)
4 -
filter(ROWNUM=:SYS_B_1)
6 -
access(ROWID=ROWID)
14 -
access(:Z>=:Z AND :Z<=:Z)
filter("FTABLE"."FP_NAME"
LIKE :SYS_B_0)
SQL_ID 3sdab0awk8g3c,
child number 10
--------------------------------------
select fp_id from ftable where ftable.fp_name like
:"SYS_B_0" and rownum=:"SYS_B_1"
Plan hash value: 2456616034
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 6 (100)| | |
|
|* 1 | COUNT STOPKEY | | |
| | | |
|
| 2 | TABLE ACCESS BY GLOBAL INDEX ROWID| FTABLE |
2 | 126 | 6
(0)| 00:00:01 | ROW L | ROW L |
|* 3 | INDEX RANGE SCAN | UNIQUE_FP_NAME | |
| 4 (0)| 00:00:01 | |
|
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------
1 -
filter(ROWNUM=:SYS_B_1)
3 - access("FTABLE"."FP_NAME"
LIKE :SYS_B_0)
filter("FTABLE"."FP_NAME"
LIKE :SYS_B_0)
SQL_ID 3sdab0awk8g3c,
child number 13
select fp_id from ftable where ftable.fp_name like
:"SYS_B_0" and rownum=:"SYS_B_1"
NOTE: cannot fetch plan for SQL_ID: 3sdab0awk8g3c,
CHILD_NUMBER: 13
Please verify
value of SQL_ID and CHILD_NUMBER;
It could also be
that the plan is no longer in cursor cache (check v$sql_plan)
236 rows selected.
Get sql details from shared pool
SQL> select sql_text, sql_id, plan_hash_value,
HASH_VALUE, CHILD_NUMBER
from V$SQL
where sql_text
like 'select fp_id from ftable%'; 2 3
SQL_TEXT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID
PLAN_HASH_VALUE HASH_VALUE CHILD_NUMBER
------------- --------------- ---------- ------------
select fp_id from ftable where ftable.fp_name like
:"SYS_B_0" and rownum=:"SYS_B_1"
3sdab0awk8g3c
3326224603 3106159724 0
select fp_id from ftable where ftable.fp_name like
:"SYS_B_0" and rownum=:"SYS_B_1"
3sdab0awk8g3c
3326224603 3106159724 1
select fp_id from ftable where ftable.fp_name like
:"SYS_B_0" and rownum=:"SYS_B_1"
3sdab0awk8g3c
3326224603 3106159724 2
select fp_id from ftable where ftable.fp_name like :"SYS_B_0"
and rownum=:"SYS_B_1"
3sdab0awk8g3c
3326224603 3106159724 3
select fp_id from ftable where ftable.fp_name like
:"SYS_B_0" and rownum=:"SYS_B_1"
3sdab0awk8g3c
1013486222 3106159724 4
select fp_id from ftable where ftable.fp_name like
:"SYS_B_0" and rownum=:"SYS_B_1"
3sdab0awk8g3c
3326224603 3106159724 8
select fp_id from ftable where ftable.fp_name like
:"SYS_B_0" and rownum=:"SYS_B_1"
3sdab0awk8g3c
3326224603 3106159724 9
select fp_id from ftable where ftable.fp_name like
:"SYS_B_0" and rownum=:"SYS_B_1"
3sdab0awk8g3c
2456616034 3106159724 10
select fp_id from ftable where ftable.fp_name like
:"SYS_B_0" and rownum=:"SYS_B_1"
3sdab0awk8g3c 3326224603
3106159724 13
9 rows selected.
Get cursor with BEST PLAN details
SQL> select * from
table(dbms_xplan.display_cursor('3sdab0awk8g3c',10,'ADVANCED'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 3sdab0awk8g3c,
child number 10
--------------------------------------
select fp_id from ftable where ftable.fp_name like
:"SYS_B_0" and rownum=:"SYS_B_1"
Plan hash value: 2456616034
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | | |
| 6 (100)| | |
|
|* 1 | COUNT STOPKEY | | |
| | | |
|
| 2 | TABLE ACCESS BY GLOBAL INDEX ROWID| FTABLE |
2 | 126 | 6
(0)| 00:00:01 | ROW L | ROW L |
|* 3 | INDEX RANGE SCAN | UNIQUE_FP_NAME | |
| 4 (0)| 00:00:01 | |
|
----------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation
id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / FTABLE@SEL$1
3 - SEL$1 / FTABLE@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.5')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "FTABLE"@"SEL$1"
("FTABLE"."FP_NAME" "FTABLE"."FP_SOURCE_APP"
"FTABLE"."FP_DEST_APP"
"FTABLE"."FP_SOURCE_FP_ID"))
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------
1 - :SYS_B_0
(VARCHAR2(30), CSID=871): '%BSR_GTR14425673.FILE%'
2 - :SYS_B_1
(NUMBER): 1
Predicate Information (identified by operation id):
---------------------------------------------------
1 -
filter(ROWNUM=:SYS_B_1)
3 - access("FTABLE"."FP_NAME"
LIKE :SYS_B_0)
filter("FTABLE"."FP_NAME"
LIKE :SYS_B_0)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 -
"FP_ID"[NUMBER,22]
2 -
"FP_ID"[NUMBER,22]
3 - "FTABLE".ROWID[ROWID,10]
56 rows selected.
Grant outline privileges
SQL> conn / as sysdba
Connected.
SQL> grant create any outline to fuser;
Grant succeeded.
SQL> grant alter any outline to fuser;
Grant succeeded.
SQL> grant drop any outline to fuser;
Grant succeeded.
Create outline for cursor with best execution plan
SQL> conn fuser/fuser
Connected.
SQL> EXEC DBMS_OUTLN.CREATE_OUTLINE(3106159724,10,'FUSER_OUTLINES');
PL/SQL procedure successfully completed.
SQL> select * from user_outlines;
SYS_OUTLINE_13062618005000302 FUSER_OUTLINES UNUSED 26.06.2013 18:00:50
10.2.0.5.0
select fp_id from ftable where ftable.fp_name like
:"SYS_B_0" and rownum=:"S E3F801A3532476555971DEFCE0EA1F6E
COMPATIBLE ENABLED NORMAL
SQL>
Change outline name
SQL> alter outline SYS_OUTLINE_13062618005000302 rename
to FTABLE_OUTLINE;
Outline altered.
Use and test outline
As sys
SQL> alter system set use_stored_outlines = FUSER_OUTLINES;
As user
SQL> variable SYS_B_0 varchar2(200);
SQL> exec :SYS_B_0 :=
'%cat.IN00102.20130110010000.20130110010502.0607%';
PL/SQL procedure successfully completed.
SQL> variable SYS_B_1 number;
SQL> exec :SYS_B_1 := 1;
PL/SQL procedure successfully completed.
SQL> select fp_id from ftable where ftable.fp_name like
:"SYS_B_0" and rownum=:"SYS_B_1";
no rows selected
SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 3sdab0awk8g3c,
child number 5
-------------------------------------
select fp_id from ftable where ftable.fp_name like
:"SYS_B_0" and rownum=:"SYS_B_1"
Plan hash value: 2456616034
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | |
| | 993K(100)| | |
|
|* 1 | COUNT STOPKEY | | |
| | | |
|
| 2 | TABLE ACCESS BY GLOBAL INDEX ROWID| FTABLE |
1353K| 81M| 993K
(3)| 00:06:30 | ROW L | ROW L |
|* 3 | INDEX RANGE SCAN | UNIQUE_FP_NAME | 1353K|
| 17563 (7)| 00:00:07 | |
|
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 -
filter(ROWNUM=:SYS_B_1)
3 - access("FTABLE"."FP_NAME"
LIKE :SYS_B_0)
filter("FTABLE"."FP_NAME"
LIKE :SYS_B_0)
Note
-----
- outline "FTABLE_OUTLINE" used
for this statement
26 rows selected.
SQL> select * from user_outlines;
NAME CATEGORY USED TIMESTAMP VERSION
-----------------------------------
------------------------------ ------ -------------------
----------------------------------------------------------------
SQL_TEXT
SIGNATURE COMPATIBLE ENABLED FORMAT
--------------------------------------------------------------------------------
-------------------------------- ------------ -------- ------
FTABLE_OUTLINE FUSER_OUTLINES USED 26.06.2013 18:13:27 10.2.0.5.0
select fp_id from ftable where ftable.fp_name like
:"SYS_B_0" and rownum=:"S E3F801A3532476555971DEFCE0EA1F6E
COMPATIBLE ENABLED NORMAL
Drop outlines
SQL> exec dbms_outln.drop_by_cat('FUSER_OUTLINES');
PL/SQL procedure successfully completed.
SQL> select * from user_outlines;
no rows selected
Revoke user privileges
SQL> conn / as sysdba
Connected.
SQL> revoke drop any outline from fuser;
Revoke succeeded.
SQL> revoke alter any outline from fuser;
Revoke succeeded.
SQL> revoke create any outline from fuser;
Revoke succeeded.
No comments:
Post a Comment