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@xxxxxxxxx> 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@xxxxxxxxxxxx> 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@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> *On
Behalf Of *Rakesh Ra
*Sent:* Mittwoch, 29. Mai 2019 08:18
*To:* Oracle-L Freelists <oracle-l@xxxxxxxxxxxxx>
*Cc:* Rakesh RA <rakeshra.tr@xxxxxxxxx>
*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.