RE: Execution plan: Suddenly no predicate information displayed anymore
- From: "Brooks, Dominic (London)(c)" <dbrooks@xxxxxxxxxxxxxxxxxx>
- To: <Martin.Klier@xxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 4 Mar 2010 10:44:50 -0000
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
--
//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
**********************************************************************
--
//www.freelists.org/webpage/oracle-l
Other related posts:
