Not able to fetch execution plan from cursor
Date: Wed, 29 May 2019 11:47:32 +0530
Message-ID: <CAOGpvWoV2dnKhtrYRSNnynyvP5n_EySiTdaUN9Y-TwzQn2T3HQ_at_mail.gmail.com>
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 hashvalue for app executing the same SQL
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |5606K| 765 (2)| 00:00:10 |
----------------------------------------------------------------------------------------------------
| 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|
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.
-- http://www.freelists.org/webpage/oracle-lReceived on Wed May 29 2019 - 08:17:32 CEST