Re: Not able to fetch execution plan from cursor

Hi Nenad,

Thanks for the quick response.

I will check with the below settings. Does this setting have any overhead to the DB? I guess the trace will be generated only when hard parse happens.

alter system set events 'trace [SQL_Optimizer.*][sql: fjj079nrphmwu]' ;

One another interesting part is that when the SQL was executing and fetching the records (as per SQL monitor) I tried to fetch the plan from cursor, even that time i failed to get the details. This same SQL execution plan hash value is shared by 24 other SQL statements and for all I am not able to fetch from cursor.

Regards,

Rakesh RA

On Wed, May 29, 2019 at 12:03 PM Noveljic Nenad <nenad.noveljic_at_vontobel.com> wrote:

> 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
> *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
>
>
>
> 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_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
> 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
> 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 )
>
>
>
> SQL> set lines 900 pages 900
> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('fjj079nrphmwu'));SQL>
>
> PLAN_TABLE_OUTPUT
>
> ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> SQL_ID: fjj079nrphmwu, child number: 0 cannot be found
>
>
>
>
>
> PLAN_TABLE_OUTPUT
>
> ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> 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.
>
>
> Important Notice
> 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.
> 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.
> 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. 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.
> 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 www.vontobel.com.
>

--
http://www.freelists.org/webpage/oracle-l