Re: Not able to fetch execution plan from cursor

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