Execution plan: Suddenly no predicate information displayed anymore

From: Martin Klier <Martin.Klier_at_klug-is.de>
Date: Thu, 4 Mar 2010 09:33:56 +0100
Message-ID: <OF7AF3BAA5.D4BBF215-ONC12576DC.002DF527-C12576DC.002F0C74_at_klug-is.de>


Dear list,

on one of my 10.2.0.4 databases, I suddenly can't display predicate informations of an execution plan any more.

I tried with this script, as I always did:



set timing on;
set serveroutput off;
set termout off;
SELECT /*+ gather_plan_statistics */ table_name from user_tables; set termout on;
select * from table(dbms_xplan.display_cursor(null,null,'COST IOSTATS LAST'));

Sadly, the result is:

SQL_ID cjgkchwa4vpnm, child number 0



SELECT /*+ gather_plan_statistics */ table_name from user_tables Plan hash value: 4190597607

| Id | Operation | Name | Starts | E-Rows |
Cost (%CPU)| A-Rows | A-Time | Buffers | Reads |


| 1 | HASH JOIN RIGHT OUTER | | 1 | 1147 |

3172   (1)|     99 |00:00:00.20 |   12197 |    367 |

| 2 | TABLE ACCESS FULL | USER$ | 1 | 123 |
3 (0)| 123 |00:00:00.01 | 9 | 0 |
| 3 | HASH JOIN OUTER | | 1 | 1147 |
3169 (1)| 99 |00:00:00.20 | 12188 | 367 |
| 4 | NESTED LOOPS OUTER | | 1 | 1147 |
2241 (1)| 99 |00:00:00.15 | 7970 | 367 |
| 5 | HASH JOIN OUTER | | 1 | 1147 |
2241 (1)| 99 |00:00:00.15 | 7970 | 367 |
| 6 | HASH JOIN | | 1 | 1147 |
761 (1)| 99 |00:00:00.05 | 1231 | 15 |
| 7 | TABLE ACCESS FULL | TS$ | 1 | 25 |
8 (0)| 25 |00:00:00.01 | 31 | 0 |
| 8 | NESTED LOOPS | | 1 | 1147 |
753 (1)| 99 |00:00:00.05 | 1200 | 15 |
| 9 | MERGE JOIN CARTESIAN | | 1 | 1147 |
634 (1)| 656 |00:00:00.04 | 299 | 0 |
| 10 | HASH JOIN | | 1 | 1 |
1 (100)| 1 |00:00:00.04 | 0 | 0 |
| 11 | FIXED TABLE FULL | X$KSPPI | 1 | 1 |
0 (0)| 1 |00:00:00.01 | 0 | 0 |
| 12 | FIXED TABLE FULL | X$KSPPCV | 1 | 100 |
0 (0)| 1495 |00:00:00.01 | 0 | 0 |
| 13 | BUFFER SORT | | 1 | 1147 |
634 (1)| 656 |00:00:00.01 | 299 | 0 |
| 14 | TABLE ACCESS BY INDEX ROWID| OBJ$ | 1 | 1147 |
633 (0)| 656 |00:00:00.01 | 299 | 0 |
| 15 | INDEX RANGE SCAN | I_OBJ2 | 1 | 1147 |
11 (0)| 656 |00:00:00.01 | 12 | 0 |
| 16 | TABLE ACCESS CLUSTER | TAB$ | 656 | 1 |
1 (0)| 99 |00:00:00.01 | 901 | 15 |
| 17 | INDEX UNIQUE SCAN | I_OBJ# | 656 | 1 |
0 (0)| 206 |00:00:00.01 | 658 | 0 |
| 18 | TABLE ACCESS FULL | SEG$ | 1 | 32975 |
1479 (1)| 32527 |00:00:00.03 | 6739 | 352 |
| 19 | INDEX UNIQUE SCAN | I_OBJ1 | 99 | 1 |
0 (0)| 0 |00:00:00.01 | 0 | 0 |
| 20 | TABLE ACCESS FULL | OBJ$ | 1 | 91731 |
927 (1)| 91727 |00:00:00.01 | 4218 | 0 | --------------------------------------------------------------------------------------------------------------------------------

As you can see, no leading asterisks, no following predicate infromation.

On all other machines (of this release and later), the result looks like:

SQL_ID cjgkchwa4vpnm, child number 0



SELECT /*+ gather_plan_statistics */ table_name from user_tables

Plan hash value: 2241718361


| Id | Operation | Name | Starts | E-Rows | Cost
(%CPU)| A-Rows | A-Time | Buffers |


|*  1 |  HASH JOIN RIGHT OUTER     |          |      1 |   1129 |   284
(5)|    233 |00:00:00.05 |    3816 |

| 2 | TABLE ACCESS FULL | USER$ | 1 | 41 | 2
(0)| 41 |00:00:00.01 | 5 | |* 3 | HASH JOIN OUTER | | 1 | 1129 | 281 (5)| 233 |00:00:00.05 | 3811 |
| 4 | NESTED LOOPS OUTER | | 1 | 1129 | 230
(5)| 233 |00:00:00.04 | 3614 | |* 5 | HASH JOIN RIGHT OUTER | | 1 | 1129 | 230 (5)| 233 |00:00:00.04 | 3614 |
| 6 | TABLE ACCESS FULL | SEG$ | 1 | 4723 | 37
(3)| 4723 |00:00:00.01 | 146 | |* 7 | HASH JOIN | | 1 | 1129 | 192 (5)| 233 |00:00:00.03 | 3468 |
| 8 | TABLE ACCESS FULL | TS$ | 1 | 16 | 7
(0)| 16 |00:00:00.01 | 23 |
| 9 | NESTED LOOPS | | 1 | 1129 | 185
(5)| 233 |00:00:00.02 | 3445 |
| 10 | MERGE JOIN CARTESIAN| | 1 | 1129 | 54
(12)| 1950 |00:00:00.01 | 196 | |* 11 | HASH JOIN | | 1 | 1 | 1 (100)| 1 |00:00:00.01 | 0 | |* 12 | FIXED TABLE FULL | X$KSPPI | 1 | 1 | 0 (0)| 1 |00:00:00.01 | 0 |
| 13 | FIXED TABLE FULL | X$KSPPCV | 1 | 100 | 0
(0)| 1495 |00:00:00.01 | 0 |
| 14 | BUFFER SORT | | 1 | 1129 | 54
(12)| 1950 |00:00:00.01 | 196 | |* 15 | TABLE ACCESS FULL | OBJ$ | 1 | 1129 | 53 (10)| 1950 |00:00:00.01 | 196 | |* 16 | TABLE ACCESS CLUSTER| TAB$ | 1950 | 1 | 1 (0)| 233 |00:00:00.01 | 3249 | |* 17 | INDEX UNIQUE SCAN | I_OBJ# | 1950 | 1 | 0 (0)| 763 |00:00:00.01 | 1952 | |* 18 | INDEX UNIQUE SCAN | I_OBJ1 | 233 | 1 | 0 (0)| 0 |00:00:00.01 | 0 |
| 19 | TABLE ACCESS FULL | OBJ$ | 1 | 14712 | 50
(4)| 14855 |00:00:00.01 | 197 | --------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):


   1 - access("CX"."OWNER#"="CU"."USER#")
   3 - access("T"."DATAOBJ#"="CX"."OBJ#")
   5 - access("T"."FILE#"="S"."FILE#" AND "T"."BLOCK#"="S"."BLOCK#" AND
"T"."TS#"="S"."TS#")
   7 - access("T"."TS#"="TS"."TS#")
  11 - access("KSPPI"."INDX"="KSPPCV"."INDX")
  12 - filter("KSPPI"."KSPPINM"='_dml_monitoring_enabled')   15 - filter(("O"."OWNER#"=USERENV('SCHEMAID') AND BITAND ("O"."FLAGS",128)=0))
  16 - filter(BITAND("T"."PROPERTY",1)=0)
  17 - access("O"."OBJ#"="T"."OBJ#")
  18 - access("T"."BOBJ#"="CO"."OBJ#")

.. which is what I want.

So my question:
What does affect the fact whether predicate info is displayed or not? The client is the same both times (tried with native SQL*plus and SQL Developer 1.5.5).

Any idea is greatly appreciated, thanks in advance!

--
Mit freundlichem Gruß


Martin Klier
Senior Oracle Database Administrator
------------------------------------------------------------------------------

Klug GmbH integrierte Systeme
Lindenweg 13, D-92552 Teunz
Tel.:  +49 9671/9216-245
Fax.: +49 9671/9216-112
mailto: martin.klier_at_klug-is.de
www.klug-is.de
------------------------------------------------------------------------------

Geschäftsführer: Johann Klug, Roman Sorgenfrei
Sitz der Gesellschaft: Teunz, USt-ID-Nr. DE175481608,
HRB Nr. 2037, Amtsgericht Amberg

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Mar 04 2010 - 02:33:56 CST

Original text of this message