RE: Not able to fetch execution plan from cursor

From: Noveljic Nenad <nenad.noveljic_at_vontobel.com>
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
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<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 )

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.
<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-l
Received on Wed May 29 2019 - 08:33:16 CEST

Original text of this message