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.
