RE: Execution plan: Suddenly no predicate information displayed anymore

From: Brooks, Dominic (London)(c) <"Brooks,>
Date: Thu, 4 Mar 2010 10:44:50 -0000
Message-ID: <882B6289F381484AA6FA6B7FD2E4D5D1062AD08A_at_mildnpexmb01.maninvestments.ad.man.com>



There were bugs around access to the predicates column in v$sql_plan in certain versions (2791172,763607.1,3267299) One of the recommendations was to alter the setting of

        _cursor_plan_unparse_enabled
E.g.
ALTER SESSION set "_cursor_plan_unparse_enabled"=FALSE

I just tried setting this parameter above and my predicates section disappears, set it to true and that section is back.

Relevant?

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Martin Klier Sent: 04 March 2010 08:34
To: oracle-l_at_freelists.org
Subject: Execution plan: Suddenly no predicate information displayed anymore

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



**********************************************************************
 Please consider the environment before printing this email or its attachments.
The contents of this email are for the named addressees only.  It contains information which may be confidential and privileged.  If you are not the intended recipient, please notify the sender immediately, destroy this email and any attachments and do not otherwise disclose or use them. Email transmission is not a secure method of communication and Man Investments cannot accept responsibility for the completeness or accuracy of this email or any attachments. Whilst Man Investments makes every effort to keep its network free from viruses, it does not accept responsibility for any computer virus which might be transferred by way of this email or any attachments. This email does not constitute a request, offer, recommendation or solicitation of any kind to buy, subscribe, sell or redeem any investment instruments or to perform other such transactions of any kind. Man Investments reserves the right to monitor, record and retain all electronic communications through its network t
 o ensure the integrity of its systems, for record keeping and regulatory purposes. 
Visit us at: www.maninvestments.com 
TG0908
**********************************************************************

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Mar 04 2010 - 04:44:50 CST

Original text of this message