Re: Not able to fetch execution plan from cursor

Hi Ls Cheng,

Yeah the child_number is 0.

SQL> select SQL_ID,CHILD_NUMBER from v$sql where sql_id='fjj079nrphmwu';

SQL_ID CHILD_NUMBER
------------- ------------
fjj079nrphmwu 0

BR

On Wed, May 29, 2019 at 12:26 PM Ls Cheng <exriscer_at_gmail.com> wrote:

> Is the child number 0? May be it is not 0
>
>
> BR
>
> On Wed, May 29, 2019 at 8:49 AM Rakesh Ra <rakeshra.tr_at_gmail.com> wrote:
>
>> 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