RE: Not able to fetch execution plan from cursor
Date: Wed, 29 May 2019 06:33:16 +0000
Message-ID: <6179_1559111604_5CEE27B4_6179_3440_1_c0b6b6440daf40b896d2a482e8dc9eed_at_vontobel.com>
Hi Rakesh,
In my opinion, the error message “SQL_ID: fjj079nrphmwu, child number: 0 cannot be found “ is telling us that the plan is not in shared pool any more when you try to retrieve it.
It’s a pain in the neck that the predicates aren’t stored in AWR.
But you could use event propagation to generate the optimizer trace, whenever the SQL is optimized:
alter system set events 'trace [SQL_Optimizer.*][sql: fjj079nrphmwu]' ;
You can find the execution plan with the predicates in the “Plan Table” section.
Best regards,
Nenad
https://nenadnoveljic.com/blog/
From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> On Behalf Of Rakesh Ra
Sent: Mittwoch, 29. Mai 2019 08:18
Hi All,
This is gonna be a lengthier mail and apologize for the same.
Environment Details: Oracle Database EE ,11.2.0.3 on SUSE Linux Enterprise Server 11 (x86_64) VERSION = 11.
For one of the SQL I am not able to fetch the SQL execution plan to see the predicate information. I am able to fetch the execution plan from AWR report and SQL monitor which again doesn't provide the predicate information.
The below is SQL that is getting fired from application end. From application side the SQL_ID is fjj079nrphmwu. When I execute the same SQL (sql_id g63c0rgawafdn), I am able to fetch the plan with predicates however, there is change in SQL execution plan from what application SQL is getting executed and what my SQL is using.
SQL> select sql_id,sql_fulltext,parsing_schema_name from gv$sqlarea where sql_id='fjj079nrphmwu';
SQL> set lines 900 pages 900
PLAN_TABLE_OUTPUT
PLAN_TABLE_OUTPUT
To: Oracle-L Freelists <oracle-l_at_freelists.org>
Cc: Rakesh RA <rakeshra.tr_at_gmail.com>
Subject: Not able to fetch execution plan from cursor
SQL_ID SQL_FULLTEXT PARSING_SCHEMA_NAME
------------- -------------------------------------------------------------------------------- ------------------------------
fjj079nrphmwu SELECT CCI.DOCLOC.BEGIN_STAGE_ID, CCI.DOCLOC.COLLECTION_NAM CCIR
E, CCI.DOCLOC.DOCLOC_CHECK_TYPE, CCI.DOC
LOC.DOCLOC_ID, CCI.DOCLOC.LAST_UPD_DATET
IME, CCI.DOCLOC.LAST_UPD_INITS, CCI.DOCL
OC.REPLICATE_DONE, CCI.DOCLOC.STAGE_ID,
CCI.COLLECTION.ACCESS_PASSWORD, CCI.COLL
ECTION.AUTO_RECLAIM_ENABLED, CCI.COLLECT
ION.AVAILABLE_FLAG, CCI.COLLECTION.CC_CO
LLECTION_DEST, CCI.COLLECTION.COLLECTION
_FAMILY, CCI.COLLECTION.COLLECTION_ID, C
CI.COLLECTION.COLLECTION_NAME, CCI.COLLE
CTION.COLLECTION_TYPE, CCI.COLLECTION.CO<http://CCI.COLLECTION.CO>
NTENT_TIMEZONE, CCI.COLLECTION.EMAIL_ADD
RESS, CCI.COLLECTION.LAST_UPD_DATETIME,
CCI.COLLECTION.LAST_UPD_INITS, CCI.COLLE
CTION.L_STAGE, CCI.COLLECTION.MIC_GROUP,
CCI.COLLECTION.MIC_TYPE, CCI.COLLECTION
.OWNER_NAME, CCI.COLLECTION.PARTNER_COLL
_NAME, CCI.COLLECTION.PARTNER_ID, CCI.CO<http://CCI.CO>
LLECTION.PASSWORD, CCI.COLLECTION.P_STAG
E, CCI.COLLECTION.RELATION2PARTNER, CCI.
COLLECTION.RELOAD_FLAG, CCI.COLLECTION.R
ETRIEVAL_PASSWORD, CCI.COLLECTION.RETRIE
VAL_SOURCE, CCI.COLLECTION.TOKEN_TYPE, C
CI.COLLECTION.T_STAGE FROM CCI.DOCLOC, C
CI.COLLECTION WHERE CCI.COLLECTION.COLL
ECTION_ID IN (:1 , :2 , :3 , :4 , :5 , :
6 , :7 , :8 ) AND CCI.COLLECTION.COLLECT
ION_NAME=CCI.DOCLOC.COLLECTION_NAME AND
CCI.DOCLOC.STAGE_ID=(SELECT MAX (STAGE_I
D) FROM CCI.COLLECTION_PIT WHERE CCI.COL
LECTION_PIT.COLLECTION_NAME=CCI.DOCLOC.C
OLLECTION_NAME AND CCI.COLLECTION_PIT.PI
T_ID<=:9 AND CCI.COLLECTION_PIT.STAGE_C
ODE=:10 )
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('fjj079nrphmwu'));SQL>
SQL_ID: fjj079nrphmwu, child number: 0 cannot be found
SQL_ID fjj079nrphmwu
SELECT CCI.DOCLOC.BEGIN_STAGE_ID, CCI.DOCLOC.COLLECTION_NAME,
..... < Trimming the SQL for better brevity
Plan hash value: 2653752761<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< Plan hash value for app executing the same SQL
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 841 (100)| |
| 1 | FILTER | | | | | |
| 2 | HASH GROUP BY | | 1 | 329 | 841 (2)| 00:00:11 |
| 3 | HASH JOIN | | 29950 | 9622K| 838 (2)| 00:00:11 |
| 4 | NESTED LOOPS | | | | | |
| 5 | NESTED LOOPS | | 1141 | 310K| 72 (0)| 00:00:01 |
| 6 | INLIST ITERATOR | | | | | |
| 7 | TABLE ACCESS BY INDEX ROWID| COLLECTION | 8 | 1640 | 10 (0)| 00:00:01 |
| 8 | INDEX UNIQUE SCAN | XAK1COLLECTION | 8 | | 6 (0)| 00:00:01 |
| 9 | INDEX RANGE SCAN | XPKDOCLOC | 140 | | 4 (0)| 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID | DOCLOC | 140 | 10360 | 33 (0)| 00:00:01 |
| 11 | TABLE ACCESS FULL | COLLECTION_PIT | 114K| 5606K| 765 (2)| 00:00:10 |
----------------------------------------------------------------------------------------------------
My execution of SQL as below:
SQL> SELECT
cci.docloc.begin_stage_id,
2 cci.docloc.collection_name, 3 4 cci.docloc.docloc_check_type, cci.docloc.docloc_id, cci.docloc.last_upd_datetime, cci.docloc.last_upd_inits, cci.docloc.replicate_done, cci.docloc.stage_id, cci.collection.access_password, cci.collection.auto_reclaim_enabled, cci.collection.available_flag, cci.collection.cc_collection_dest, cci.collection.collection_family, cci.collection.collection_id, cci.collection.collection_name, cci.collection.collection_type, cci.collection.content_timezone, cci.collection.email_address, cci.collection.last_upd_datetime, cci.collection.last_upd_inits, cci.collection.l_stage, cci.collection.mic_group, cci.collection.mic_type, cci.collection.owner_name, cci.collection.partner_coll_name, cci.collection.partner_id, cci.collection.password, cci.collection.p_stage, cci.collection.relation2partner, cci.collection.reload_flag, cci.collection.retrieval_password, cci.collection.retrieval_source,
cci.collection.token_type,
cci.collection.t_stage
FROM
cci.docloc,
cci.collection
WHERE
cci.collection.collection_id IN (
19799, 19800, 19801, 20427, 20428, 20429, 5 6 7 21807, 21978
)
AND cci.collection.collection_name = cci.docloc.collection_name AND cci.docloc.stage_id = (
8 SELECT MAX(stage_id) FROM 9 10 11 12 13 14 cci.collection_pit WHERE 15 16 17 18 19 20 21 22 cci.collection_pit.collection_name = cci.docloc.collection_name AND cci.collection_pit.pit_id <= 2147483647 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 AND cci.collection_pit.stage_code = 'F' 44 ); 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60
SQL> set lines 900 pages 900
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(''));SQL>
PLAN_TABLE_OUTPUT
SQL_ID g63c0rgawafdn, child number 1
SELECT cci.docloc.begin_stage_id,
Plan hash value: 468515438<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< Plan change -----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 210 (100)| |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 1 | 273 | 28 (0)| 00:00:01 |
| 3 | INLIST ITERATOR | | | | | |
| 4 | TABLE ACCESS BY INDEX ROWID| COLLECTION | 8 | 1632 | 11 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | XAK1COLLECTION | 8 | | 7 (0)| 00:00:01 | |* 6 | INDEX RANGE SCAN | XPKDOCLOC | 1 | | 2 (0)| 00:00:01 |
| 7 | SORT AGGREGATE | | 1 | 49 | | |
|* 8 | INDEX RANGE SCAN | XAK1COLLECTION_PIT | 8 | 392 | 4 (0)| 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID | DOCLOC | 1 | 69 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
5 - access(("COLLECTION"."COLLECTION_ID"=19799 OR "COLLECTION"."COLLECTION_ID"=19800 OR
"COLLECTION"."COLLECTION_ID"=19801 OR "COLLECTION"."COLLECTION_ID"=20427 OR "COLLECTION"."COLLECTION_ID"=20428 OR "COLLECTION"."COLLECTION_ID"=20429 OR "COLLECTION"."COLLECTION_ID"=21807 OR "COLLECTION"."COLLECTION_ID"=21978)) 6 - access("COLLECTION"."COLLECTION_NAME"="DOCLOC"."COLLECTION_NAME") filter("DOCLOC"."STAGE_ID"=) 8 - access("COLLECTION_PIT"."COLLECTION_NAME"=:B1 AND "COLLECTION_PIT"."STAGE_CODE"='F' AND "COLLECTION_PIT"."PIT_ID"<=2147483647)
I have tried to check if application is setting any specific session level parameters from V$SES_OPTIMIZER_ENV and all I can see is the below.
INST_ID SID ID NAME SQL_FEATURE ISDEFAULT VALUE ---------- ---------- ---------- ---------------------------------------- ---------------------------------------------------------------- ------------ ------------------------- 1 7551 25 _pga_max_size QKSFM_ALL NO 2097152 KB 1 7551 70 query_rewrite_enabled QKSFM_TRANSFORMATION NO false 1 7551 264 db_file_multiblock_read_count QKSFM_ALL NO 8
I tried to get the 10053 trace when the application SQL got executed using the below, but that also failed.
execute DBMS_SQLDIAG.DUMP_TRACE(p_sql_id=>'fjj079nrphmwu', p_child_number=>0, p_component=>'Optimizer',p_file_id=>'ABCDE');
*
ERROR at line 1:
ORA-20002: statement with sql_id=fjj079nrphmwu child_number=0 not found. ORA-06512: at "SYS.DBMS_SQLDIAG", line 1243 ORA-06512: at line 1
Can anyone shed some light as to why I am not able to fetch the execution plan details and how would i proceed to understand why the SQL is using a different execution plan when comared to my execution. I also tried with Mauro Pagano's "pathfinder" tool to see if I can reproduce the same execution what application is using but the report didn't have the plan hash value what application is using.
Please consider the environment before printing this e-mail. Bitte denken Sie an die Umwelt, bevor Sie dieses E-Mail drucken.
<html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> <style type="text/css">p { font-family: Arial;font-size:9pt }</style> </head> <body> <p> <br>Important Notice</br> <br />
This message is intended only for the individual named. It may contain confidential or privileged information. If you are not the named addressee you should in particular not disseminate, distribute, modify or copy this e-mail. Please notify the sender immediately by e-mail, if you have received this message by mistake and delete it from your system.<br /> Without prejudice to any contractual agreements between you and us which shall prevail in any case, we take it as your authorization to correspond with you by e-mail if you send us messages by e-mail. However, we reserve the right not to execute orders and instructions transmitted by e-mail at any time and without further explanation.<br /> E-mail transmission may not be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete. Also processing of incoming e-mails cannot be guaranteed. All liability of Vontobel Holding Ltd. and any of its affiliates (hereinafter collectively referred to as "Vontobel Group") for any damages resulting from e-mail use is excluded. You are advised that urgent and time sensitive messages should not be sent by e-mail and if verification is required please request a printed version.</br> Please note that all e-mail communications to and from the Vontobel Group are subject to electronic storage and review by Vontobel Group. Unless stated to the contrary and without prejudice to any contractual agreements between you and Vontobel Group which shall prevail in any case, e-mail-communication is for informational purposes only and is not intended as an offer or solicitation for the purchase or sale of any financial instrument or as an official confirmation of any transaction.<br /> The legal basis for the processing of your personal data is the legitimate interest to develop a commercial relationship with you, as well as your consent to forward you commercial communications. You can exercise, at any time and under the terms established under current regulation, your rights. If you prefer not to receive any further communications, please contact your client relationship manager if you are a client of Vontobel Group or notify the sender. Please note for an exact reference to the affected group entity the corporate e-mail signature. For further information about data privacy at Vontobel Group please consult <a href="https://www.vontobel.com">www.vontobel.com</a>.<br />
</p> </body> </html>
-- http://www.freelists.org/webpage/oracle-lReceived on Wed May 29 2019 - 08:33:16 CEST