Wednesday, June 26, 2013

Use Stored Outlines in Oracle 10.2

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.

Wednesday, June 19, 2013

ORA-8102 - Traces generated with no error in alert.log, OER 8102 reported in the trace

In alert log appears the message that a trace file was created, but no oracle error was reported:

bash-3.2$ less alert_db11.log

Wed Jun 19 10:00:38 2013
Errors in file /u02/app/oracle/diag/rdbms/db11/db11/trace/db11_j000_24568.trc:
Wed Jun 19 10:00:42 2013
Dumping diagnostic data in directory=[cdmp_20130619100042], requested by (instance=1, osid=24568 (J000)), summary=[abnormal process termination].

In trace file following error appears:

bash-3.2$ less /u02/app/oracle/diag/rdbms/db11/db11/trace/db11_j000_24568.trc

oer 8102.2 - obj# 70688, rdba: 0x0081a2ea(afn 2, blk# 107242)

kdk key 8102.2:

ncol: 4, len: 64

....

Details for ORA-8102:

Error: ORA-08102 (ORA-8102)
Text: index key not found, obj# %s, file %s, block %s (%s)
---------------------------------------------------------------------------
Cause: Internal error: possible inconsistency in index
Action: Send trace file to your customer support representative, along
with information on reproducing the error

Bellow there is also the current sql running:

dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=3, mask=0x0)

----- Error Stack Dump -----

----- Current SQL Statement for this session (sql_id=11p815z8hkfms) -----

DELETE MGMT_SYSTEM_PERFORMANCE_LOG WHERE TIME < :B2 AND ROWNUM <= :B1

----- PL/SQL Stack -----

----- PL/SQL Call Stack -----

object line object

handle number name

The recommendation from Oracle support is to rebuild indexes for affected tables.

Select affected index:

SELECT *
FROM dba_objects
WHERE object_id = 70688;

MGMT_SYSTEM_PERF_LOG_IDX_01

If there is an inconsistency between table and index, the next analyze command produces an error ORA-01499:

ANALYZE TABLE MGMT_SYSTEM_PERFORMANCE_LOG VALIDATE STRUCTURE CASCADE;

ORA-01499: table/index cross reference failure - see trace file

bash-3.2$ less db11_ora_25102.trc

....

row not found in index tsn: 1 rdba: 0x00809d52

env [0xfffffd7fffde70a0]: (scn: 0x0857.ee95b7d8 xid: 0x00a6.011.00000198 uba: 0x00000000.0000.00 statement num=0 parent xid

: 0x0000.000.00000000 st-scn: 0x0000.00000000 hi-scn: 0x0000.00000000 ma-scn: 0x0857.ee95b7c3 flg: 0x00000060)

col 0; len 36; (36):

4d 47 4d 54 5f 43 4f 4c 4c 45 43 54 49 4f 4e 2e 43 6f 6c 6c 65 63 74 69 6f

6e 20 53 75 62 73 79 73 74 65 6d

col 1; len 7; (7): 78 71 06 0e 10 0d 02

col 2; len 11; (11): 43 6f 6c 6c 65 63 74 69 6f 6e 73

col 3; len 6; (6): 00 81 01 ed 00 32

Block header dump: 0x008101ed

Object id on Block? Y

seg/obj: 0x11190 csc: 0x857.ee8b62d4 itc: 2 flg: E typ: 1 - DATA

brn: 0 bdba: 0x8101e8 ver: 0x01 opc: 0

inc: 0 exflg: 0

....
kdgDumpRedo: dump redo on table/index mismatch:
table block tsn=1 rdba=0x008101ed index objn=70688
head rowid 0x008101ed.0032

Detect if the corruption is at table or at index level:

ANALYZE TABLE MGMT_SYSTEM_PERFORMANCE_LOG VALIDATE STRUCTURE;

ANALYZE INDEX MGMT_SYSTEM_PERF_LOG_IDX_01 VALIDATE STRUCTURE;

Recreate index:

DROP INDEX SYSMAN.MGMT_SYSTEM_PERF_LOG_IDX_01;

CREATE INDEX SYSMAN.MGMT_SYSTEM_PERF_LOG_IDX_01 ON SYSMAN.MGMT_SYSTEM_PERFORMANCE_LOG
(JOB_NAME, TIME, NAME)
LOGGING
TABLESPACE SYSAUX;

Now, analyzing table with cascade options give no errors:

ANALYZE TABLE MGMT_SYSTEM_PERFORMANCE_LOG VALIDATE STRUCTURE CASCADE;

No errors are reported in alert log, no trace file are created.

Friday, June 14, 2013

EXPLAIN PLAN from SGA and from AWR (using SNAPID)

select
s.elapsed_time_delta,
s.buffer_gets_delta,
s.disk_reads_delta,
cursor(select * from table(dbms_xplan.display_awr(t.sql_id, s.plan_hash_value)))
from
dba_hist_sqltext t,
dba_hist_sqlstat s
where
t.dbid = s.dbid
and t.sql_id = s.sql_id
and s.snap_id between &v_snap_id-2 and &v_snap_id
and t.sql_id = '8w5j38ruybcjz';

select * from table(dbms_xplan.display_cursor('8w5j38ruybcjz',null,'ADVANCED'));

ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []

After the recovery of database:

SQL> alter database open resetlogs;

Database altered.

SQL>

SQL> shutdown immediate

ORA-03135: connection lost contact

SQL>

SQL>

bash-3.2$ less alert_tetris11.log

Dumping diagnostic data in directory=[cdmp_20130614165402], requested by (instance=1, osid=1193 (MMON)), summary=[incident=2314801].

Errors in file /u02/app/oracle/diag/rdbms/tetris11/tetris11/trace/tetris11_mmon_1193.trc (incident=2314803):

ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []

ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []

ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []

Incident details in: /u02/app/oracle/diag/rdbms/tetris11/tetris11/incident/incdir_2314803/tetris11_mmon_1193_i2314803.trc

Use ADRCI or Support Workbench to package the incident.

See Note 411.1 at My Oracle Support for error and packaging details.

Errors in file /u02/app/oracle/diag/rdbms/tetris11/tetris11/trace/tetris11_mmon_1193.trc (incident=2314804):

ORA-00603: ORACLE server session terminated by fatal error

ORA-24557: error 600 encountered while handling error 600; exiting server process

ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []

ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []

ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []

Incident details in: /u02/app/oracle/diag/rdbms/tetris11/tetris11/incident/incdir_2314804/tetris11_mmon_1193_i2314804.trc

Dumping diagnostic data in directory=[cdmp_20130614165404], requested by (instance=1, osid=1193 (MMON)), summary=[incident=2314802].

opidrv aborting process MMON ospid (1193) as a result of ORA-603

Dumping diagnostic data in directory=[cdmp_20130614165405], requested by (instance=1, osid=1193 (MMON)), summary=[incident=2314804].

Fri Jun 14 16:54:07 2013

Errors in file /u02/app/oracle/diag/rdbms/tetris11/tetris11/trace/tetris11_pmon_1165.trc (incident=2314697):

ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []

Use ADRCI or Support Workbench to package the incident.

See Note 411.1 at My Oracle Support for error and packaging details.

Errors in file /u02/app/oracle/diag/rdbms/tetris11/tetris11/trace/tetris11_pmon_1165.trc:

ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []

PMON (ospid: 1165): terminating the instance due to error 472

System state dump requested by (instance=1, osid=1165 (PMON)), summary=[abnormal instance termination].

System State dumped to trace file /u02/app/oracle/diag/rdbms/tetris11/tetris11/trace/tetris11_diag_1175.trc

Dumping diagnostic data in directory=[cdmp_20130614165408], requested by (instance=1, osid=1165 (PMON)), summary=[abnormal instance termination].

Instance terminated by PMON, pid = 1165

Solution

Basic Steps to be Followed While Solving ORA-00600 [4194]/[4193] Errors Without Using Unsupported parameter [ID 281429.1]

bash-3.2$ cd /u02/app/oracle/fast_recovery_area/TETRIS11/backupset/

bash-3.2$ cp -R 2013_06_14 2013_06_14_old

bash-3.2$

bash-3.2$ sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Fri Jun 14 16:19:00 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.

SQL> conn / as sysdba

Connected.

SQL> select open_mode from v$database;

OPEN_MODE

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

MOUNTED

SQL> show parameter undo;

NAME TYPE VALUE

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

undo_management string AUTO

undo_retention integer 900

undo_tablespace string UNDOTBS1

SQL>

bash-3.2$ sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Fri Jun 14 16:31:11 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.

SQL> conn / as sysdba

Connected.

SQL>

SQL>

SQL> create pfile from spfile;

File created.

SQL> show parameter undo;

NAME TYPE VALUE

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

undo_management string AUTO

undo_retention integer 900

undo_tablespace string UNDOTBS1

SQL> desc dba_data_files;

ERROR:

ORA-04043: object dba_data_files does not exist

SQL> alter system set undo_management='MANUAL' scope=spfile;

System altered.

SQL> shutdown immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 1068937216 bytes

Fixed Size 2232840 bytes

Variable Size 864030200 bytes

Database Buffers 197132288 bytes

Redo Buffers 5541888 bytes

Database mounted.

Database opened.

SQL> create undo tablespace UNDOTBS02 datafile '/u02/app/oracle/oradata/tetris11/undotbs02.dbf' size 100m autoextend on maxsize 1G;

Tablespace created.

SQL> alter system set undo_tablespace='UNDOTBS02' scope=spfile;

System altered.

SQL> alter system set undo_management='AUTO' scope=spfile;

System altered.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 1068937216 bytes

Fixed Size 2232840 bytes

Variable Size 864030200 bytes

Database Buffers 197132288 bytes

Redo Buffers 5541888 bytes

Database mounted.

Database opened.

SQL>

The old undo tablespace can be now dropped.

ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1], [169], [61273], [61294], [], [], [], [], [], [], []

bash-3.2$ sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Fri Jun 14 16:40:47 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.

SQL> conn / as sysdba

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 1068937216 bytes

Fixed Size 2232840 bytes

Variable Size 864030200 bytes

Database Buffers 197132288 bytes

Redo Buffers 5541888 bytes

Database mounted.

ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1],

[169], [61273], [61294], [], [], [], [], [], [], []

Solution

Alter database open fails with ORA-00600 kcratr_nab_less_than_odr [ID 1296264.1]

SQL> startup mount

ORACLE instance started.

Total System Global Area 1068937216 bytes

Fixed Size 2232840 bytes

Variable Size 864030200 bytes

Database Buffers 197132288 bytes

Redo Buffers 5541888 bytes

Database mounted.

SQL> show parameter control_files;

NAME TYPE VALUE

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

control_files string /u02/app/oracle/oradata/tetris

11/control01.ctl, /u02/app/ora

cle/fast_recovery_area/tetris1

1/control02.ctl

SQL> select a.member,a.group#,b.status from v$logfile a ,v$log b where a.group#=b.group# and b.status='CURRENT';

MEMBER

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

GROUP# STATUS

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

/u02/app/oracle/oradata/redo/redo01_2.log

1 CURRENT

/u02/app/oracle/oradata/tetris11/redo01.log

1 CURRENT

bash-3.2$ cd /u02/app/oracle/oradata/tetris11

bash-3.2$ cp control01.ctl control01.ctl_20130613

bash-3.2$ cd /u02/app/oracle/fast_recovery_area/tetris11/

bash-3.2$ cp control02.ctl control02.ctl_20130613

bash-3.2$ sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Fri Jun 14 16:52:34 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.

SQL> conn / as sysdba

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.

Total System Global Area 1068937216 bytes

Fixed Size 2232840 bytes

Variable Size 864030200 bytes

Database Buffers 197132288 bytes

Redo Buffers 5541888 bytes

Database mounted.

SQL> recover database using backup controlfile until cancel ;

ORA-00279: change 9173756576002 generated at 06/14/2013 13:07:32 needed for

thread 1

ORA-00289: suggestion :

/u02/app/oracle/fast_recovery_area/TETRIS11/archivelog/2013_06_14/o1_mf_1_169_%u

_.arc

ORA-00280: change 9173756576002 for thread 1 is in sequence #169

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

/u02/app/oracle/oradata/tetris11/redo01.log

Log applied.

Media recovery complete.

SQL> alter database open resetlogs;

Database altered.

SQL>

SQL> shutdown immediate

ORA-03135: connection lost contact

SQL>

SQL>

There was a second issue, that you will find in next post:

ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []

Subscribe to: Posts (Atom)