RE: Execution plan: Suddenly no predicate information displayed anymore

As an aside, presumably bugs around selecting these columns from v$sql_plan are 
the historic reason why the access_predicates and filter_predicated information 
is deliberately not carried across into dba_hist_sql_plan?

-----Original Message-----
From: Brooks, Dominic (London)(c) 
Sent: 04 March 2010 10:45
To: 'Martin.Klier@xxxxxxxxxx'; oracle-l@xxxxxxxxxxxxx
Subject: RE: Execution plan: Suddenly no predicate information displayed anymore

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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Martin Klier
Sent: 04 March 2010 08:34
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxx
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 to 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


Other related posts: