Hi, I am using Oracle 12.1.0.2. Could you please guide me why index hint is not being used for
where activity_pk In (select activity_pk from activity where owner_pk in (7059, 1763) and entity_pk in (2308887, 2068114))
here is the 10053 trace. Without the index, this query is taking 1:18 minutes. When I used precompute_subquery, query completes in 1 second and does the index on activity_state table. As precompute_subquery is not documented hint, I wanted to use other hints to achieve sample plan but could not.
*** 2017-07-28 20:52:29.502
*** SESSION ID:(2014.35606) 2017-07-28 20:52:29.502
*** CLIENT ID:() 2017-07-28 20:52:29.502
*** SERVICE NAME:(SYS$USERS) 2017-07-28 20:52:29.502
*** MODULE NAME:(TOAD 12.6.0.53) 2017-07-28 20:52:29.502
*** CLIENT DRIVER:() 2017-07-28 20:52:29.502
*** ACTION NAME:() 2017-07-28 20:52:29.502
*** TRACE FILE RECREATED AFTER BEING REMOVED ***
Registered qb: SEL$027D0DF9 0xec6b6918 (PARSER)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$027D0DF9 nbfros=1 flg=0
fro(0): flg=5 objn=0 hint_alias="from$_subquery$_003"@"SEL$027D0DF9"
Registered qb: SET$027D0DF9 0xec08c2e8 (PARSER)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SET$027D0DF9 nbfros=1 flg=0
fro(0): flg=0 objn=0 hint_alias="NULL_HALIAS"@"SET$027D0DF9"
Registered qb: SEL$027D0DFA 0xec6b3bc0 (PARSER)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$027D0DFA nbfros=1 flg=0
fro(0): flg=4 objn=0 hint_alias="SYS_FBA_HIST_12103951"@"SEL$027D0DFA"
Registered qb: SEL$027D0DFB 0xec6b1428 (PARSER)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$027D0DFB nbfros=2 flg=0
fro(0): flg=5 objn=0 hint_alias="F"@"SEL$027D0DFB"
fro(1): flg=4 objn=0 hint_alias="T"@"SEL$027D0DFB"
Registered qb: SEL$027D0DFC 0xec08fca0 (PARSER)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$027D0DFC nbfros=1 flg=0
fro(0): flg=4 objn=0 hint_alias="SYS_FBA_TCRV_12103951"@"SEL$027D0DFC"
Registered qb: SEL$1 0xec6bcb00 (PARSER)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$1 nbfros=1 flg=0
fro(0): flg=5 objn=0 hint_alias="A"@"SEL$1"
Registered qb: SEL$2 0xec6b8ce8 (PARSER)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$2 nbfros=1 flg=0
fro(0): flg=4 objn=12103949 hint_alias="ACTIVITY"@"SEL$2"
SPM: statement not found in SMB
SPM: capture of plan baseline is OFF
**************************
Automatic degree of parallelism (AUTODOP)
**************************
Automatic degree of parallelism is disabled: Parameter.
kkopqSetForceParallelProperties: Hint:no
Query: compute:yes forced:no forceDop:0
kkopqSetDopReason: Reason why we chose this DOP is: table property.
table property forces parallelism
Global Manual DOP: 1 - Rounded?: no
PM: Considering predicate move-around in query block SEL$1 (#0)
**************************
Predicate Move-Around (PM)
**************************
OPTIMIZER INFORMATION
******************************************
----- Current SQL Statement for this session (sql_id=c87fbv5s41srv) -----
select
/*+ index (a)*/ * from activity_state as of TIMESTAMP to_timestamp(sysdate) a
where activity_pk In (select activity_pk from activity where owner_pk in (7059, 1763) and entity_pk in (2308887, 2068114))
*******************************************
Legend
The following abbreviations are used by optimizer trace.
CBQT - cost-based query transformation
JPPD - join predicate push-down
OJPPD - old-style (non-cost-based) JPPD
FPD - filter push-down
PM - predicate move-around
CVM - complex view merging
SPJ - select-project-join
SJC - set join conversion
SU - subquery unnesting
OBYE - order by elimination
OST - old style star transformation
ST - new (cbqt) star transformation
CNT - count(col) to count(*) transformation
JE - Join Elimination
JF - join factorization
CBY - connect by
SLP - select list pruning
DP - distinct placement
VT - vector transformation
qb - query block
LB - leaf blocks
DK - distinct keys
LB/K - average number of leaf blocks per key
DB/K - average number of data blocks per key
CLUF - clustering factor
NDV - number of distinct values
Resp - response cost
Card - cardinality
Resc - resource cost
NL - nested loops (join)
SM - sort merge (join)
HA - hash (join)
CPUSPEED - CPU Speed
IOTFRSPEED - I/O transfer speed
IOSEEKTIM - I/O seek time
SREADTIM - average single block read time
MREADTIM - average multiblock read time
MBRC - average multiblock read count
MAXTHR - maximum I/O system throughput
SLAVETHR - average slave I/O throughput
dmeth - distribution method
1: no partitioning required
2: value partitioned
4: right is random (round-robin)
128: left is random (round-robin)
8: broadcast right and partition left
16: broadcast left and partition right
32: partition left using partitioning of right
64: partition right using partitioning of left
256: run the join in serial
0: invalid distribution method
sel - selectivity
ptn - partition
AP - adaptive plans
***************************************
PARAMETERS USED BY THE OPTIMIZER
********************************
*************************************
PARAMETERS WITH ALTERED VALUES
******************************
Compilation Environment Dump
_pga_max_size = 1218960 KB
optimizer_index_cost_adj = 20
db_file_multiblock_read_count = 8
_optimizer_use_feedback = false
deferred_segment_creation = false
_px_adaptive_dist_method = off
optimizer_adaptive_reporting_only = true
_optimizer_dsdir_usage_control = 0
_optimizer_adaptive_plans = false
_optimizer_strans_adaptive_pruning = false
_optimizer_gather_feedback = false
optimizer_adaptive_features = false
_optimizer_nlj_hj_adaptive_join = false
Bug Fix Control Environment
***************************************
PARAMETERS IN OPT_PARAM HINT
****************************
***************************************
Column Usage Monitoring is ON: tracking level = 1
***************************************
Considering Query Transformations on query block SEL$1 (#0)
**************************
Query transformations (QT)
**************************
CBQT: copy not possible on query block SEL$027D0DFB (#0) because versioned table
CBQT: copy not possible on query block SET$027D0DF9 (#0) because set query block constraints
CBQT: copy not possible on query block SEL$027D0DF9 (#0) because view
CBQT: copy not possible on query block SEL$1 (#0) because view
CBQT bypassed for query block SEL$1 (#0): Cannot copy query block.
CBQT: Validity checks failed for c87fbv5s41srv.
CSE: Considering common sub-expression elimination in query block SEL$1 (#0)
*************************
Common Subexpression elimination (CSE)
*************************
CSE: Considering common sub-expression elimination in query block SEL$2 (#0)
*************************
Common Subexpression elimination (CSE)
*************************
CSE: CSE not performed on query block SEL$2 (#0).
CSE: Considering common sub-expression elimination in query block SEL$027D0DF9 (#0)
*************************
Common Subexpression elimination (CSE)
*************************
CSE: CSE not performed on query block SEL$027D0DF9 (#0).
CSE: Considering common sub-expression elimination in query block SET$027D0DF9 (#0)
*************************
Common Subexpression elimination (CSE)
*************************
CSE: CSE not performed on query block SET$027D0DF9 (#0).
CSE: Considering common sub-expression elimination in query block SEL$027D0DFC (#0)
*************************
Common Subexpression elimination (CSE)
*************************
CSE: CSE not performed on query block SEL$027D0DFC (#0).
CSE: CSE not performed on query block SEL$1 (#0).
OBYE: Considering Order-by Elimination from view SEL$1 (#0)
***************************
Order-by elimination (OBYE)
***************************
OBYE: OBYE bypassed: no order by to eliminate.
JE: Considering Join Elimination on query block SEL$027D0DFB (#0)
*************************
Join Elimination (JE)
*************************
SQL:******* UNPARSED QUERY IS *******
SELECT /*+ USE_MERGE ("F") USE_MERGE ("T") */ "T"."ACTIVITY_PK" "ACTIVITY_PK","T"."ACTIVITY_CATEGORY_TYPE_ST_CD" "ACTIVITY_CATEGORY_TYPE_ST_CD","T"."EFFECTIVE_DT" "EFFECTIVE_DT","T"."EXPIRATION_DT" "EXPIRATION_DT","T"."CREATE_TS" "CREATE_TS","T"."LAST_UPDATE_TS" "LAST_UPDATE_TS","T"."LAST_UPDATE_USER_ID" "LAST_UPDATE_USER_ID","T"."VISIBLE_CD" "VISIBLE_CD" FROM "ACTIVITY_OWNER"."ACTIVITY_STATE" VERSIONS BETWEEN SCN (6013800185409) AND MAXVALUE "T", (SELECT "SYS_FBA_TCRV_12103951"."RID" "RID","SYS_FBA_TCRV_12103951"."STARTSCN" "STARTSCN","SYS_FBA_TCRV_12103951"."ENDSCN" "ENDSCN","SYS_FBA_TCRV_12103951"."XID" "XID","SYS_FBA_TCRV_12103951"."OP" "OP" FROM "ACTIVITY_OWNER"."SYS_FBA_TCRV_12103951" "SYS_FBA_TCRV_12103951" WHERE ("SYS_FBA_TCRV_12103951"."ENDSCN">6013800185409 OR "SYS_FBA_TCRV_12103951"."ENDSCN" IS NULL) AND ("SYS_FBA_TCRV_12103951"."STARTSCN"<6013800185409 OR "SYS_FBA_TCRV_12103951"."STARTSCN" IS NULL)) "F" WHERE ("T"."VERSIONS_STARTSCN"<="SYS"."TIMESTAMP_TO_SCN"(TO_TIMESTAMP(SYSDATE@!)) OR "T"."VERSIONS_STARTSCN" IS NULL) AND ("T"."VERSIONS_ENDSCN">"SYS"."TIMESTAMP_TO_SCN"(TO_TIMESTAMP(SYSDATE@!)) OR "T"."VERSIONS_ENDSCN" IS NULL) AND ("T"."VERSIONS_OPERATION" IS NULL OR "T"."VERSIONS_OPERATION"<>'D') AND ROWIDTOCHAR("T".ROWID)="F"."RID"(+) AND ("F"."STARTSCN"<="SYS"."TIMESTAMP_TO_SCN"(TO_TIMESTAMP(SYSDATE@!)) OR "F"."STARTSCN" IS NULL)
EQUIV: equivalence check not possible SEL$027D0DFB (#5) because versioned table
EQUIV: equivalence check not possible SEL$027D0DFB (#5) because versioned table
SQL:******* UNPARSED QUERY IS *******
SELECT /*+ USE_MERGE ("F") USE_MERGE ("T") */ "T"."ACTIVITY_PK" "ACTIVITY_PK","T"."ACTIVITY_CATEGORY_TYPE_ST_CD" "ACTIVITY_CATEGORY_TYPE_ST_CD","T"."EFFECTIVE_DT" "EFFECTIVE_DT","T"."EXPIRATION_DT" "EXPIRATION_DT","T"."CREATE_TS" "CREATE_TS","T"."LAST_UPDATE_TS" "LAST_UPDATE_TS","T"."LAST_UPDATE_USER_ID" "LAST_UPDATE_USER_ID","T"."VISIBLE_CD" "VISIBLE_CD" FROM "ACTIVITY_OWNER"."ACTIVITY_STATE" VERSIONS BETWEEN SCN (6013800185409) AND MAXVALUE "T", (SELECT "SYS_FBA_TCRV_12103951"."RID" "RID","SYS_FBA_TCRV_12103951"."STARTSCN" "STARTSCN","SYS_FBA_TCRV_12103951"."ENDSCN" "ENDSCN","SYS_FBA_TCRV_12103951"."XID" "XID","SYS_FBA_TCRV_12103951"."OP" "OP" FROM "ACTIVITY_OWNER"."SYS_FBA_TCRV_12103951" "SYS_FBA_TCRV_12103951" WHERE ("SYS_FBA_TCRV_12103951"."ENDSCN">6013800185409 OR "SYS_FBA_TCRV_12103951"."ENDSCN" IS NULL) AND ("SYS_FBA_TCRV_12103951"."STARTSCN"<6013800185409 OR "SYS_FBA_TCRV_12103951"."STARTSCN" IS NULL)) "F" WHERE ("T"."VERSIONS_STARTSCN"<="SYS"."TIMESTAMP_TO_SCN"(TO_TIMESTAMP(SYSDATE@!)) OR "T"."VERSIONS_STARTSCN" IS NULL) AND ("T"."VERSIONS_ENDSCN">"SYS"."TIMESTAMP_TO_SCN"(TO_TIMESTAMP(SYSDATE@!)) OR "T"."VERSIONS_ENDSCN" IS NULL) AND ("T"."VERSIONS_OPERATION" IS NULL OR "T"."VERSIONS_OPERATION"<>'D') AND ROWIDTOCHAR("T".ROWID)="F"."RID"(+) AND ("F"."STARTSCN"<="SYS"."TIMESTAMP_TO_SCN"(TO_TIMESTAMP(SYSDATE@!)) OR "F"."STARTSCN" IS NULL)
Query block SEL$027D0DFB (#0) unchanged
CVM: Considering view merge in query block SEL$1 (#0)
OJE: Begin: find best directive for query block SEL$1 (#0)
OJE: End: finding best directive for query block SEL$1 (#0)
CVM: Checking validity of merging in query block SEL$027D0DF9 (#0)
CVM: Considering view merge in query block SEL$027D0DF9 (#0)
OJE: Begin: find best directive for query block SEL$027D0DF9 (#0)
OJE: End: finding best directive for query block SEL$027D0DF9 (#0)
CVM: Checking validity of merging in query block SET$027D0DF9 (#0)
CVM: Considering view merge in query block SET$027D0DF9 (#0)
CVM: Considering view merge in query block SEL$027D0DFA (#0)
OJE: Begin: find best directive for query block SEL$027D0DFA (#0)
OJE: End: finding best directive for query block SEL$027D0DFA (#0)
CVM: Considering view merge in query block SEL$027D0DFB (#0)
OJE: Begin: find best directive for query block SEL$027D0DFB (#0)
OJE: Considering outer-join elimination on query block SEL$027D0DFB (#0)
OJE: considering predicateROWIDTOCHAR("T".ROWID)="F"."RID"(+)
rejected
OJE: outer-join not eliminated
OJE: End: finding best directive for query block SEL$027D0DFB (#0)
CVM: Checking validity of merging in query block SEL$027D0DFC (#0)
CVM: Considering view merge in query block SEL$027D0DFC (#0)
OJE: Begin: find best directive for query block SEL$027D0DFC (#0)
OJE: End: finding best directive for query block SEL$027D0DFC (#0)
CVM: Merging SPJ view SEL$027D0DFC (#0) into SEL$027D0DFB (#0)
Registered qb: SEL$72425F55 0xec6b1428 (VIEW MERGE SEL$027D0DFB; SEL$027D0DFC)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$72425F55 nbfros=2 flg=0
fro(0): flg=0 objn=12103951 hint_alias="T"@"SEL$027D0DFB"
fro(1): flg=0 objn=12103988 hint_alias="SYS_FBA_TCRV_12103951"@"SEL$027D0DFC"
OJE: Begin: find best directive for query block SEL$72425F55 (#0)
OJE: Considering outer-join elimination on query block SEL$72425F55 (#0)
OJE: considering predicateROWIDTOCHAR("T".ROWID)="SYS_FBA_TCRV_12103951"."RID"(+)
rejected
OJE: outer-join not eliminated
OJE: End: finding best directive for query block SEL$72425F55 (#0)
SVM: SVM bypassed: SET (union, intersect, etc.) operator.
CVM: Merging SPJ view SEL$027D0DF9 (#0) into SEL$1 (#0)
CVM: Considering view merge in query block SEL$2 (#0)
OJE: Begin: find best directive for query block SEL$2 (#0)
OJE: End: finding best directive for query block SEL$2 (#0)
OJE: Begin: find best directive for query block SEL$2 (#0)
OJE: End: finding best directive for query block SEL$2 (#0)
Registered qb: SEL$3B78C2A7 0xec6bcb00 (VIEW MERGE SEL$1; SEL$027D0DF9)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$3B78C2A7 nbfros=1 flg=0
fro(0): flg=1 objn=0 hint_alias="from$_subquery$_003"@"SEL$027D0DF9"
OJE: Begin: find best directive for query block SEL$3B78C2A7 (#0)
OJE: End: finding best directive for query block SEL$3B78C2A7 (#0)
JE: Considering Join Elimination on query block SEL$72425F55 (#0)
*************************
Join Elimination (JE)
*************************
SQL:******* UNPARSED QUERY IS *******
SELECT /*+ USE_MERGE ("SYS_FBA_TCRV_12103951") USE_MERGE ("T") */ "T"."ACTIVITY_PK" "ACTIVITY_PK","T"."ACTIVITY_CATEGORY_TYPE_ST_CD" "ACTIVITY_CATEGORY_TYPE_ST_CD","T"."EFFECTIVE_DT" "EFFECTIVE_DT","T"."EXPIRATION_DT" "EXPIRATION_DT","T"."CREATE_TS" "CREATE_TS","T"."LAST_UPDATE_TS" "LAST_UPDATE_TS","T"."LAST_UPDATE_USER_ID" "LAST_UPDATE_USER_ID","T"."VISIBLE_CD" "VISIBLE_CD" FROM "ACTIVITY_OWNER"."ACTIVITY_STATE" VERSIONS BETWEEN SCN (6013800185409) AND MAXVALUE "T","ACTIVITY_OWNER"."SYS_FBA_TCRV_12103951" "SYS_FBA_TCRV_12103951" WHERE ("T"."VERSIONS_STARTSCN"<="SYS"."TIMESTAMP_TO_SCN"(TO_TIMESTAMP(SYSDATE@!)) OR "T"."VERSIONS_STARTSCN" IS NULL) AND ("T"."VERSIONS_ENDSCN">"SYS"."TIMESTAMP_TO_SCN"(TO_TIMESTAMP(SYSDATE@!)) OR "T"."VERSIONS_ENDSCN" IS NULL) AND ("T"."VERSIONS_OPERATION" IS NULL OR "T"."VERSIONS_OPERATION"<>'D') AND ROWIDTOCHAR("T".ROWID)="SYS_FBA_TCRV_12103951"."RID"(+) AND ("SYS_FBA_TCRV_12103951"."STARTSCN"<="SYS"."TIMESTAMP_TO_SCN"(TO_TIMESTAMP(SYSDATE@!)) OR "SYS_FBA_TCRV_12103951"."STARTSCN" IS NULL) AND ("SYS_FBA_TCRV_12103951"."ENDSCN"(+)>6013800185409 OR "SYS_FBA_TCRV_12103951"."ENDSCN"(+) IS NULL) AND ("SYS_FBA_TCRV_12103951"."STARTSCN"(+)<6013800185409 OR "SYS_FBA_TCRV_12103951"."STARTSCN"(+) IS NULL)
EQUIV: equivalence check not possible SEL$72425F55 (#5) because versioned table
EQUIV: equivalence check not possible SEL$72425F55 (#5) because versioned table
SQL:******* UNPARSED QUERY IS *******
SELECT /*+ USE_MERGE ("SYS_FBA_TCRV_12103951") USE_MERGE ("T") */ "T"."ACTIVITY_PK" "ACTIVITY_PK","T"."ACTIVITY_CATEGORY_TYPE_ST_CD" "ACTIVITY_CATEGORY_TYPE_ST_CD","T"."EFFECTIVE_DT" "EFFECTIVE_DT","T"."EXPIRATION_DT" "EXPIRATION_DT","T"."CREATE_TS" "CREATE_TS","T"."LAST_UPDATE_TS" "LAST_UPDATE_TS","T"."LAST_UPDATE_USER_ID" "LAST_UPDATE_USER_ID","T"."VISIBLE_CD" "VISIBLE_CD" FROM "ACTIVITY_OWNER"."ACTIVITY_STATE" VERSIONS BETWEEN SCN (6013800185409) AND MAXVALUE "T","ACTIVITY_OWNER"."SYS_FBA_TCRV_12103951" "SYS_FBA_TCRV_12103951" WHERE ("T"."VERSIONS_STARTSCN"<="SYS"."TIMESTAMP_TO_SCN"(TO_TIMESTAMP(SYSDATE@!)) OR "T"."VERSIONS_STARTSCN" IS NULL) AND ("T"."VERSIONS_ENDSCN">"SYS"."TIMESTAMP_TO_SCN"(TO_TIMESTAMP(SYSDATE@!)) OR "T"."VERSIONS_ENDSCN" IS NULL) AND ("T"."VERSIONS_OPERATION" IS NULL OR "T"."VERSIONS_OPERATION"<>'D') AND ROWIDTOCHAR("T".ROWID)="SYS_FBA_TCRV_12103951"."RID"(+) AND ("SYS_FBA_TCRV_12103951"."STARTSCN"<="SYS"."TIMESTAMP_TO_SCN"(TO_TIMESTAMP(SYSDATE@!)) OR "SYS_FBA_TCRV_12103951"."STARTSCN" IS NULL) AND ("SYS_FBA_TCRV_12103951"."ENDSCN"(+)>6013800185409 OR "SYS_FBA_TCRV_12103951"."ENDSCN"(+) IS NULL) AND ("SYS_FBA_TCRV_12103951"."STARTSCN"(+)<6013800185409 OR "SYS_FBA_TCRV_12103951"."STARTSCN"(+) IS NULL)
Query block SEL$72425F55 (#0) unchanged
query block SEL$1 transformed to SEL$3B78C2A7 (#0)
Considering Query Transformations on query block SEL$3B78C2A7 (#0)
**************************
Query transformations (QT)
**************************
CBQT: copy not possible on query block SEL$72425F55 (#0) because versioned table
CBQT: copy not possible on query block SET$027D0DF9 (#0) because set query block constraints
CBQT: copy not possible on query block SEL$3B78C2A7 (#0) because view
CBQT bypassed for query block SEL$3B78C2A7 (#0): Cannot copy query block.
CBQT: Validity checks failed for c87fbv5s41srv.
CSE: Considering common sub-expression elimination in query block SEL$3B78C2A7 (#0)
*************************
Common Subexpression elimination (CSE)
*************************
CSE: Considering common sub-expression elimination in query block SEL$2 (#0)
*************************
Common Subexpression elimination (CSE)
*************************
CSE: CSE not performed on query block SEL$2 (#0).
CSE: Considering common sub-expression elimination in query block SET$027D0DF9 (#0)
*************************
Common Subexpression elimination (CSE)
*************************
CSE: CSE not performed on query block SET$027D0DF9 (#0).
CSE: CSE not performed on query block SEL$3B78C2A7 (#0).
Query after View Removal
******* UNPARSED QUERY IS *******
SELECT /*+ USE_MERGE ("SYS_FBA_TCRV_12103951") USE_MERGE ("T") */ "T"."ACTIVITY_PK" "ACTIVITY_PK","T"."ACTIVITY_CATEGORY_TYPE_ST_CD" "ACTIVITY_CATEGORY_TYPE_ST_CD","T"."EFFECTIVE_DT" "EFFECTIVE_DT","T"."EXPIRATION_DT" "EXPIRATION_DT","T"."CREATE_TS" "CREATE_TS","T"."LAST_UPDATE_TS" "LAST_UPDATE_TS","T"."LAST_UPDATE_USER_ID" "LAST_UPDATE_USER_ID","T"."VISIBLE_CD" "VISIBLE_CD" FROM "ACTIVITY_OWNER"."ACTIVITY_STATE" VERSIONS BETWEEN SCN (6013800185409) AND MAXVALUE "T","ACTIVITY_OWNER"."SYS_FBA_TCRV_12103951" "SYS_FBA_TCRV_12103951" WHERE ("T"."VERSIONS_STARTSCN"<="SYS"."TIMESTAMP_TO_SCN"(TO_TIMESTAMP(SYSDATE@!)) OR "T"."VERSIONS_STARTSCN" IS NULL) AND ("T"."VERSIONS_ENDSCN">"SYS"."TIMESTAMP_TO_SCN"(TO_TIMESTAMP(SYSDATE@!)) OR "T"."VERSIONS_ENDSCN" IS NULL) AND ("T"."VERSIONS_OPERATION" IS NULL OR "T"."VERSIONS_OPERATION"<>'D') AND ROWIDTOCHAR("T".ROWID)="SYS_FBA_TCRV_12103951"."RID"(+) AND ("SYS_FBA_TCRV_12103951"."STARTSCN"<="SYS"."TIMESTAMP_TO_SCN"(TO_TIMESTAMP(SYSDATE@!)) OR "SYS_FBA_TCRV_12103951"."STARTSCN" IS NULL) AND ("SYS_FBA_TCRV_12103951"."ENDSCN"(+)>6013800185409 OR "SYS_FBA_TCRV_12103951"."ENDSCN"(+) IS NULL) AND ("SYS_FBA_TCRV_12103951"."STARTSCN"(+)<6013800185409 OR "SYS_FBA_TCRV_12103951"."STARTSCN"(+) IS NULL)
Subquery removal for query block SEL$2 (#0)
RSW: Not valid for subquery removal SEL$2 (#0)
Subquery unchanged.
SU: Considering subquery unnesting in query block SEL$3B78C2A7 (#0)
********************
Subquery Unnest (SU)
********************
SU: Checking validity of unnesting subquery SEL$2 (#0)
SU: Transforming ANY subquery to a join.
Registered qb: SEL$617C5F34 0xec6bcb00 (SUBQUERY UNNEST SEL$3B78C2A7; SEL$2)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$617C5F34 nbfros=2 flg=0
fro(0): flg=1 objn=0 hint_alias="from$_subquery$_003"@"SEL$027D0DF9"
fro(1): flg=0 objn=12103949 hint_alias="ACTIVITY"@"SEL$2"
SJC: Considering set-join conversion in query block SEL$617C5F34 (#0)
*************************
Set-Join Conversion (SJC)
*************************
SJC: Considering set-join conversion in query block SET$027D0DF9 (#0)
*************************
Set-Join Conversion (SJC)
*************************
SJC: Checking validity of SJC on query block SET$027D0DF9 (#0)
SJC: SJC bypassed: Not enabled by hint/parameter.
SJC: Considering set-join conversion in query block SEL$72425F55 (#0)
*************************
Set-Join Conversion (SJC)
*************************
SJC: not performed
SJC: Considering set-join conversion in query block SEL$027D0DFA (#0)
*************************
Set-Join Conversion (SJC)
*************************
SJC: not performed
SJC: not performed
SJC: not performed
DCL: Checking validity of group-by elimination SEL$027D0DFA (#0)
DCL: Checking validity of group-by elimination SEL$72425F55 (#0)
DCL: Checking validity of group-by elimination SET$027D0DF9 (#0)
DCL: Checking validity of group-by elimination SEL$617C5F34 (#0)
JE: Considering Join Elimination on query block SEL$72425F55 (#0)
*************************
Join Elimination (JE)
*************************
SQL:******* UNPARSED QUERY IS *******
SELECT /*+ USE_MERGE ("SYS_FBA_TCRV_12103951") USE_MERGE ("T") */ "T"."ACTIVITY_PK" "ACTIVITY_PK","T"."ACTIVITY_CATEGORY_TYPE_ST_CD" "ACTIVITY_CATEGORY_TYPE_ST_CD","T"."EFFECTIVE_DT" "EFFECTIVE_DT","T"."EXPIRATION_DT" "EXPIRATION_DT","T"."CREATE_TS" "CREATE_TS","T"."LAST_UPDATE_TS" "LAST_UPDATE_TS","T"."LAST_UPDATE_USER_ID" "LAST_UPDATE_USER_ID","T"."VISIBLE_CD" "VISIBLE_CD" FROM "ACTIVITY_OWNER"."ACTIVITY_STATE" VERSIONS BETWEEN SCN (6013800185409) AND MAXVALUE "T","ACTIVITY_OWNER"."SYS_FBA_TCRV_12103951" "SYS_FBA_TCRV_12103951" WHERE ("T"."VERSIONS_STARTSCN"<="SYS"."TIMESTAMP_TO_SCN"(TO_TIMESTAMP(SYSDATE@!)) OR "T"."VERSIONS_STARTSCN" IS NULL) AND ("T"."VERSIONS_ENDSCN">"SYS"."TIMESTAMP_TO_SCN"(TO_TIMESTAMP(SYSDATE@!)) OR "T"."VERSIONS_ENDSCN" IS NULL) AND ("T"."VERSIONS_OPERATION" IS NULL OR "T"."VERSIONS_OPERATION"<>'D') AND ROWIDTOCHAR("T".ROWID)="SYS_FBA_TCRV_12103951"."RID"(+) AND ("SYS_FBA_TCRV_12103951"."STARTSCN"<="SYS"."TIMESTAMP_TO_SCN"(TO_TIMESTAMP(SYSDATE@!)) OR "SYS_FBA_TCRV_12103951"."STARTSCN" IS NULL) AND ("SYS_FBA_TCRV_12103951"."ENDSCN"(+)>6013800185409 OR "SYS_FBA_TCRV_12103951"."ENDSCN"(+) IS NULL) AND ("SYS_FBA_TCRV_12103951"."STARTSCN"(+)<6013800185409 OR "SYS_FBA_TCRV_12103951"."STARTSCN"(+) IS NULL)
EQUIV: equivalence check not possible SEL$72425F55 (#5) because versioned table
EQUIV: equivalence check not possible SEL$72425F55 (#5) because versioned table
SQL:******* UNPARSED QUERY IS *******
SELECT /*+ USE_MERGE ("SYS_FBA_TCRV_12103951") USE_MERGE ("T") */ "T"."ACTIVITY_PK" "ACTIVITY_PK","T"."ACTIVITY_CATEGORY_TYPE_ST_CD" "ACTIVITY_CATEGORY_TYPE_ST_CD","T"."EFFECTIVE_DT" "EFFECTIVE_DT","T"."EXPIRATION_DT" "EXPIRATION_DT","T"."CREATE_TS" "CREATE_TS","T"."LAST_UPDATE_TS" "LAST_UPDATE_TS","T"."LAST_UPDATE_USER_ID" "LAST_UPDATE_USER_ID","T"."VISIBLE_CD" "VISIBLE_CD" FROM "ACTIVITY_OWNER"."ACTIVITY_STATE" VERSIONS BETWEEN SCN (6013800185409) AND MAXVALUE "T","ACTIVITY_OWNER"."SYS_FBA_TCRV_12103951" "SYS_FBA_TCRV_12103951" WHERE ("T"."VERSIONS_STARTSCN"<="SYS"."TIMESTAMP_TO_SCN"(TO_TIMESTAMP(SYSDATE@!)) OR "T"."VERSIONS_STARTSCN" IS NULL) AND ("T"."VERSIONS_ENDSCN">"SYS"."TIMESTAMP_TO_SCN"(TO_TIMESTAMP(SYSDATE@!)) OR "T"."VERSIONS_ENDSCN" IS NULL) AND ("T"."VERSIONS_OPERATION" IS NULL OR "T"."VERSIONS_OPERATION"<>'D') AND ROWIDTOCHAR("T".ROWID)="SYS_FBA_TCRV_12103951"."RID"(+) AND ("SYS_FBA_TCRV_12103951"."STARTSCN"<="SYS"."TIMESTAMP_TO_SCN"(TO_TIMESTAMP(SYSDATE@!)) OR "SYS_FBA_TCRV_12103951"."STARTSCN" IS NULL) AND ("SYS_FBA_TCRV_12103951"."ENDSCN"(+)>6013800185409 OR "SYS_FBA_TCRV_12103951"."ENDSCN"(+) IS NULL) AND ("SYS_FBA_TCRV_12103951"."STARTSCN"(+)<6013800185409 OR "SYS_FBA_TCRV_12103951"."STARTSCN"(+) IS NULL)
Query block SEL$72425F55 (#0) unchanged
JE: Considering Join Elimination on query block SEL$617C5F34 (#0)
*************************
Join Elimination (JE)
*************************
SQL:******* UNPARSED QUERY IS *******
SELECT /*+ INDEX ("from$_subquery$_003") */ "from$_subquery$_003"."ACTIVITY_PK" "ACTIVITY_PK","from$_subquery$_003"."ACTIVITY_CATEGORY_TYPE_ST_CD" "ACTIVITY_CATEGORY_TYPE_ST_CD","from$_subquery$_003"."EFFECTIVE_DT" "EFFECTIVE_DT","from$_subquery$_003"."EXPIRATION_DT" "EXPIRATION_DT","from$_subquery$_003"."CREATE_TS" "CREATE_TS","from$_subquery$_003"."LAST_UPDATE_TS" "LAST_UPDATE_TS","from$_subquery$_003"."LAST_UPDATE_USER_ID" "LAST_UPDATE_USER_ID","from$_subquery$_003"."VISIBLE_CD" "VISIBLE_CD" FROM "ACTIVITY_OWNER"."ACTIVITY" "ACTIVITY", ( (SELECT "SYS_FBA_HIST_12103951"."ACTIVITY_PK" "ACTIVITY_PK","SYS_FBA_HIST_12103951"."ACTIVITY_CATEGORY_TYPE_ST_CD" "ACTIVITY_CATEGORY_TYPE_ST_CD","SYS_FBA_HIST_12103951"."EFFECTIVE_DT" "EFFECTIVE_DT","SYS_FBA_HIST_12103951"."EXPIRATION_DT" "EXPIRATION_DT","SYS_FBA_HIST_12103951"."CREATE_TS" "CREATE_TS","SYS_FBA_HIST_12103951"."LAST_UPDATE_TS" "LAST_UPDATE_TS","SYS_FBA_HIST_12103951"."LAST_UPDATE_USER_ID" "LAST_UPDATE_USER_ID","SYS_FBA_HIST_12103951"."VISIBLE_CD" "VISIBLE_CD" FROM "ACTIVITY_OWNER"."SYS_FBA_HIST_12103951" "SYS_FBA_HIST_12103951" WHERE ("SYS_FBA_HIST_12103951"."STARTSCN"<="SYS"."TIMESTAMP_TO_SCN"(TO_TIMESTAMP(SYSDATE@!)) OR "SYS_FBA_HIST_12103951"."STARTSCN" IS NULL) AND "SYS_FBA_HIST_12103951"."ENDSCN">"SYS"."TIMESTAMP_TO_SCN"(TO_TIMESTAMP(SYSDATE@!)) AND "SYS_FBA_HIST_12103951"."ENDSCN"<=6013800185409 AND ("SYS_FBA_HIST_12103951"."OPERATION" IS NULL OR "SYS_FBA_HIST_12103951"."OPERATION"<>'D')) UNION ALL (SELECT /*+ USE_MERGE ("SYS_FBA_TCRV_12103951") USE_MERGE ("T") */ "T"."ACTIVITY_PK" "ACTIVITY_PK","T"."ACTIVITY_CATEGORY_TYPE_ST_CD" "ACTIVITY_CATEGORY_TYPE_ST_CD","T"."EFFECTIVE_DT" "EFFECTIVE_DT","T"."EXPIRATION_DT" "EXPIRATION_DT","T"."CREATE_TS" "CREATE_TS","T"."LAST_UPDATE_TS" "LAST_UPDATE_TS","T"."LAST_UPDATE_USER_ID" "LAST_UPDATE_USER_ID","T"."VISIBLE_CD" "VISIBLE_CD" FROM "ACTIVITY_OWNER"."ACTIVITY_STATE" VERSIONS BETWEEN SCN (6013800185409) AND MAXVALUE "T","ACTIVITY_OWNER"."SYS_FBA_TCRV_12103951" "SYS_FBA_TCRV_12103951" WHERE ("T"."VERSIONS_STARTSCN"<="SYS"."TIMESTAMP_TO_SCN"(TO_TIMESTAMP(SYSDATE@!)) OR "T"."VERSIONS_STARTSCN" IS NULL) AND ("T"."VERSIONS_ENDSCN">"SYS"."TIMESTAMP_TO_SCN"(TO_TIMESTAMP(SYSDATE@!)) OR "T"."VERSIONS_ENDSCN" IS NULL) AND ("T"."VERSIONS_OPERATION" IS NULL OR "T"."VERSIONS_OPERATION"<>'D') AND ROWIDTOCHAR("T".ROWID)="SYS_FBA_TCRV_12103951"."RID"(+) AND ("SYS_FBA_TCRV_12103951"."STARTSCN"<="SYS"."TIMESTAMP_TO_SCN"(TO_TIMESTAMP(SYSDATE@!)) OR "SYS_FBA_TCRV_12103951"."STARTSCN" IS NULL) AND ("SYS_FBA_TCRV_12103951"."ENDSCN"(+)>6013800185409 OR "SYS_FBA_TCRV_12103951"."ENDSCN"(+) IS NULL) AND ("SYS_FBA_TCRV_12103951"."STARTSCN"(+)<6013800185409 OR "SYS_FBA_TCRV_12103951"."STARTSCN"(+) IS NULL))) "from$_subquery$_003" WHERE "from$_subquery$_003"."ACTIVITY_PK"="ACTIVITY"."ACTIVITY_PK" AND ("ACTIVITY"."OWNER_PK"=7059 OR "ACTIVITY"."OWNER_PK"=1763) AND ("ACTIVITY"."ENTITY_PK"=2308887 OR "ACTIVITY"."ENTITY_PK"=2068114)
EQUIV: equivalence check not possible SEL$72425F55 (#5) because versioned table
EQUIV: equivalence check not possible on QB SET$027D0DF9 (#0) because set query branch not okay
EQUIV: equivalence check not possible SEL$617C5F34 (#3) because view not okay
EQUIV: equivalence check not possible SEL$72425F55 (#5) because versioned table
EQUIV: equivalence check not possible on QB SET$027D0DF9 (#0) because set query branch not okay
EQUIV: equivalence check not possible SEL$617C5F34 (#3) because view not okay
SQL:******* UNPARSED QUERY IS *******
SELECT /*+ INDEX ("from$_subquery$_003") */ "from$_subquery$_003"."ACTIVITY_PK" "ACTIVITY_PK","from$_subquery$_003"."ACTIVITY_CATEGORY_TYPE_ST_CD" "ACTIVITY_CATEGORY_TYPE_ST_CD","from$_subquery$_003"."EFFECTIVE_DT" "EFFECTIVE_DT","from$_subquery$_003"."EXPIRATION_DT" "EXPIRATION_DT","from$_subquery$_003"."CREATE_TS" "CREATE_TS","from$_subquery$_003"."LAST_UPDATE_TS" "LAST_UPDATE_TS","from$_subquery$_003"."LAST_UPDATE_USER_ID" "LAST_UPDATE_USER_ID","from$_subquery$_003"."VISIBLE_CD" "VISIBLE_CD" FROM "ACTIVITY_OWNER"."ACTIVITY" "ACTIVITY", ( (SELECT "SYS_FBA_HIST_12103951"."ACTIVITY_PK" "ACTIVITY_PK","SYS_FBA_HIST_12103951"."ACTIVITY_CATEGORY_TYPE_ST_CD" "ACTIVITY_CATEGORY_TYPE_ST_CD","SYS_FBA_HIST_12103951"."EFFECTIVE_DT" "EFFECTIVE_DT","SYS_FBA_HIST_12103951"."EXPIRATION_DT" "EXPIRATION_DT","SYS_FBA_HIST_12103951"."CREATE_TS" "CREATE_TS","SYS_FBA_HIST_12103951"."LAST_UPDATE_TS" "LAST_UPDATE_TS","SYS_FBA_HIST_12103951"."LAST_UPDATE_USER_ID" "LAST_UPDATE_USER_ID","SYS_FBA_HIST_12103951"."VISIBLE_CD" "VISIBLE_CD" FROM "ACTIVITY_OWNER"."SYS_FBA_HIST_12103951" "SYS_FBA_HIST_12103951" WHERE ("SYS_FBA_HIST_12103951"."STARTSCN"<="SYS"."TIMESTAMP_TO_SCN"(TO_TIMESTAMP(SYSDATE@!)) OR "SYS_FBA_HIST_12103951"."STARTSCN" IS NULL) AND "SYS_FBA_HIST_12103951"."ENDSCN">"SYS"."TIMESTAMP_TO_SCN"(TO_TIMESTAMP(SYSDATE@!)) AND "SYS_FBA_HIST_12103951"."ENDSCN"<=6013800185409 AND ("SYS_FBA_HIST_12103951"."OPERATION" IS NULL OR "SYS_FBA_HIST_12103951"."OPERATION"<>'D')) UNION ALL (SELECT /*+ USE_MERGE ("SYS_FBA_TCRV_12103951") USE_MERGE ("T") */ "T"."ACTIVITY_PK" "ACTIVITY_PK","T"."ACTIVITY_CATEGORY_TYPE_ST_CD" "ACTIVITY_CATEGORY_TYPE_ST_CD","T"."EFFECTIVE_DT" "EFFECTIVE_DT","T"."EXPIRATION_DT" "EXPIRATION_DT","T"."CREATE_TS" "CREATE_TS","T"."LAST_UPDATE_TS" "LAST_UPDATE_TS","T"."LAST_UPDATE_USER_ID" "LAST_UPDATE_USER_ID","T"."VISIBLE_CD" "VISIBLE_CD" FROM "ACTIVITY_OWNER"."ACTIVITY_STATE" VERSIONS BETWEEN SCN (6013800185409) AND MAXVALUE "T","ACTIVITY_OWNER"."SYS_FBA_TCRV_12103951" "SYS_FBA_TCRV_12103951" WHERE ("T"."VERSIONS_STARTSCN"<="SYS"."TIMESTAMP_TO_SCN"(TO_TIMESTAMP(SYSDATE@!)) OR "T"."VERSIONS_STARTSCN" IS NULL) AND ("T"."VERSIONS_ENDSCN">"SYS"."TIMESTAMP_TO_SCN"(TO_TIMESTAMP(SYSDATE@!)) OR "T"."VERSIONS_ENDSCN" IS NULL) AND ("T"."VERSIONS_OPERATION" IS NULL OR "T"."VERSIONS_OPERATION"<>'D') AND ROWIDTOCHAR("T".ROWID)="SYS_FBA_TCRV_12103951"."RID"(+) AND ("SYS_FBA_TCRV_12103951"."STARTSCN"<="SYS"."TIMESTAMP_TO_SCN"(TO_TIMESTAMP(SYSDATE@!)) OR "SYS_FBA_TCRV_12103951"."STARTSCN" IS NULL) AND ("SYS_FBA_TCRV_12103951"."ENDSCN"(+)>6013800185409 OR "SYS_FBA_TCRV_12103951"."ENDSCN"(+) IS NULL) AND ("SYS_FBA_TCRV_12103951"."STARTSCN"(+)<6013800185409 OR "SYS_FBA_TCRV_12103951"."STARTSCN"(+) IS NULL))) "from$_subquery$_003" WHERE "from$_subquery$_003"."ACTIVITY_PK"="ACTIVITY"."ACTIVITY_PK" AND ("ACTIVITY"."OWNER_PK"=1763 OR "ACTIVITY"."OWNER_PK"=7059) AND ("ACTIVITY"."ENTITY_PK"=2068114 OR "ACTIVITY"."ENTITY_PK"=2308887)
Query block SEL$617C5F34 (#0) unchanged
PM: Considering predicate move-around in query block SEL$617C5F34 (#0)
**************************
Predicate Move-Around (PM)
**************************
PM: Passed validity checks.
PM: PM bypassed: checking.
query block SEL$3B78C2A7 transformed to SEL$617C5F34 (#0)
FPD: Considering simple filter push in query block SEL$617C5F34 (#0)
"from$_subquery$_003"."ACTIVITY_PK"="ACTIVITY"."ACTIVITY_PK" AND ("ACTIVITY"."OWNER_PK"=1763 OR "ACTIVITY"."OWNER_PK"=7059) AND ("ACTIVITY"."ENTITY_PK"=2068114 OR "ACTIVITY"."ENTITY_PK"=2308887)
try to generate transitive predicate from check constraints for query block SEL$617C5F34 (#0)
finally: "from$_subquery$_003"."ACTIVITY_PK"="ACTIVITY"."ACTIVITY_PK" AND ("ACTIVITY"."OWNER_PK"=1763 OR "ACTIVITY"."OWNER_PK"=7059) AND ("ACTIVITY"."ENTITY_PK"=2068114 OR "ACTIVITY"."ENTITY_PK"=2308887)
JPPD: JPPD bypassed: View not on right-side of outer-join.
FPD: Considering simple filter push in query block SEL$72425F55 (#0)
("T"."VERSIONS_STARTSCN"<="SYS"."TIMESTAMP_TO_SCN"(TO_TIMESTAMP(TO_CHAR(SYSDATE@!))) OR "T"."VERSIONS_STARTSCN" IS NULL) AND ("T"."VERSIONS_ENDSCN">"SYS"."TIMESTAMP_TO_SCN"(TO_TIMESTAMP(TO_CHAR(SYSDATE@!))) OR "T"."VERSIONS_ENDSCN" IS NULL) AND ("T"."VERSIONS_OPERATION" IS NULL OR "T"."VERSIONS_OPERATION"<>'D') AND "SYS_FBA_TCRV_12103951"."RID"(+)=ROWIDTOCHAR("T".ROWID) AND ("SYS_FBA_TCRV_12103951"."STARTSCN"<="SYS"."TIMESTAMP_TO_SCN"(TO_TIMESTAMP(TO_CHAR(SYSDATE@!))) OR "SYS_FBA_TCRV_12103951"."STARTSCN" IS NULL) AND ("SYS_FBA_TCRV_12103951"."ENDSCN"(+)>6013800185409 OR "SYS_FBA_TCRV_12103951"."ENDSCN"(+) IS NULL) AND ("SYS_FBA_TCRV_12103951"."STARTSCN"(+)<6013800185409 OR "SYS_FBA_TCRV_12103951"."STARTSCN"(+) IS NULL)
try to generate transitive predicate from check constraints for query block SEL$72425F55 (#0)
constraint: "T"."VISIBLE_CD"='Y' OR "T"."VISIBLE_CD"='N' OR "T"."VISIBLE_CD"='X'
finally: ("T"."VERSIONS_STARTSCN"<="SYS"."TIMESTAMP_TO_SCN"(TO_TIMESTAMP(TO_CHAR(SYSDATE@!))) OR "T"."VERSIONS_STARTSCN" IS NULL) AND ("T"."VERSIONS_ENDSCN">"SYS"."TIMESTAMP_TO_SCN"(TO_TIMESTAMP(TO_CHAR(SYSDATE@!))) OR "T"."VERSIONS_ENDSCN" IS NULL) AND ("T"."VERSIONS_OPERATION" IS NULL OR "T"."VERSIONS_OPERATION"<>'D') AND "SYS_FBA_TCRV_12103951"."RID"(+)=ROWIDTOCHAR("T".ROWID) AND ("SYS_FBA_TCRV_12103951"."STARTSCN"<="SYS"."TIMESTAMP_TO_SCN"(TO_TIMESTAMP(TO_CHAR(SYSDATE@!))) OR "SYS_FBA_TCRV_12103951"."STARTSCN" IS NULL) AND ("SYS_FBA_TCRV_12103951"."ENDSCN"(+)>6013800185409 OR "SYS_FBA_TCRV_12103951"."ENDSCN"(+) IS NULL) AND ("SYS_FBA_TCRV_12103951"."STARTSCN"(+)<6013800185409 OR "SYS_FBA_TCRV_12103951"."STARTSCN"(+) IS NULL)
FPD: Considering simple filter push in query block SEL$027D0DFA (#0)
("SYS_FBA_HIST_12103951"."STARTSCN"<="SYS"."TIMESTAMP_TO_SCN"(TO_TIMESTAMP(TO_CHAR(SYSDATE@!))) OR "SYS_FBA_HIST_12103951"."STARTSCN" IS NULL) AND "SYS_FBA_HIST_12103951"."ENDSCN">"SYS"."TIMESTAMP_TO_SCN"(TO_TIMESTAMP(TO_CHAR(SYSDATE@!))) AND "SYS_FBA_HIST_12103951"."ENDSCN"<=6013800185409 AND ("SYS_FBA_HIST_12103951"."OPERATION" IS NULL OR "SYS_FBA_HIST_12103951"."OPERATION"<>'D')
try to generate transitive predicate from check constraints for query block SEL$027D0DFA (#0)
finally: ("SYS_FBA_HIST_12103951"."STARTSCN"<="SYS"."TIMESTAMP_TO_SCN"(TO_TIMESTAMP(TO_CHAR(SYSDATE@!))) OR "SYS_FBA_HIST_12103951"."STARTSCN" IS NULL) AND "SYS_FBA_HIST_12103951"."ENDSCN">"SYS"."TIMESTAMP_TO_SCN"(TO_TIMESTAMP(TO_CHAR(SYSDATE@!))) AND "SYS_FBA_HIST_12103951"."ENDSCN"<=6013800185409 AND ("SYS_FBA_HIST_12103951"."OPERATION" IS NULL OR "SYS_FBA_HIST_12103951"."OPERATION"<>'D') AND "SYS"."TIMESTAMP_TO_SCN"(TO_TIMESTAMP(TO_CHAR(SYSDATE@!)))<6013800185409
FPD: transitive predicates are generated in query block SEL$027D0DFA (#0)
("SYS_FBA_HIST_12103951"."STARTSCN"<="SYS"."TIMESTAMP_TO_SCN"(TO_TIMESTAMP(TO_CHAR(SYSDATE@!))) OR "SYS_FBA_HIST_12103951"."STARTSCN" IS NULL) AND "SYS_FBA_HIST_12103951"."ENDSCN">"SYS"."TIMESTAMP_TO_SCN"(TO_TIMESTAMP(TO_CHAR(SYSDATE@!))) AND "SYS_FBA_HIST_12103951"."ENDSCN"<=6013800185409 AND ("SYS_FBA_HIST_12103951"."OPERATION" IS NULL OR "SYS_FBA_HIST_12103951"."OPERATION"<>'D') AND "SYS"."TIMESTAMP_TO_SCN"(TO_TIMESTAMP(TO_CHAR(SYSDATE@!)))<6013800185409
OJE: Begin: find best directive for query block SEL$72425F55 (#0)
OJE: Considering outer-join elimination on query block SEL$72425F55 (#0)
OJE: considering predicate"SYS_FBA_TCRV_12103951"."RID"(+)=ROWIDTOCHAR("T".ROWID)
rejected
OJE: outer-join not eliminated
OJE: End: finding best directive for query block SEL$72425F55 (#0)
OJE: Begin: find best directive for query block SEL$027D0DFA (#0)
OJE: End: finding best directive for query block SEL$027D0DFA (#0)
apadrv-start sqlid=14131675340510126843
CSE: Considering common sub-expression elimination in query block SEL$617C5F34 (#0)
*************************
Common Subexpression elimination (CSE)
*************************
CSE: Considering common sub-expression elimination in query block SET$027D0DF9 (#0)
*************************
Common Subexpression elimination (CSE)
*************************
CSE: CSE not performed on query block SET$027D0DF9 (#0).
CSE: CSE not performed on query block SEL$617C5F34 (#0).
:
call(in-use=41376, alloc=49080), compile(in-use=178136, alloc=182024), execution(in-use=32600, alloc=36480)
*******************************************
Peeked values of the binds in SQL statement
*******************************************
=====================================
SPD: BEGIN context at statement level
=====================================
Stmt: ******* UNPARSED QUERY IS *******
SELECT /*+ INDEX ("from$_subquery$_003") */ "from$_subquery$_003"."ACTIVITY_PK" "ACTIVITY_PK","from$_subquery$_003"."ACTIVITY_CATEGORY_TYPE_ST_CD" "ACTIVITY_CATEGORY_TYPE_ST_CD","from$_subquery$_003"."EFFECTIVE_DT" "EFFECTIVE_DT","from$_subquery$_003"."EXPIRATION_DT" "EXPIRATION_DT","from$_subquery$_003"."CREATE_TS" "CREATE_TS","from$_subquery$_003"."LAST_UPDATE_TS" "LAST_UPDATE_TS","from$_subquery$_003"."LAST_UPDATE_USER_ID" "LAST_UPDATE_USER_ID","from$_subquery$_003"."VISIBLE_CD" "VISIBLE_CD" FROM "ACTIVITY_OWNER"."ACTIVITY" "ACTIVITY", ( (SELECT "SYS_FBA_HIST_12103951"."ACTIVITY_PK" "ACTIVITY_PK","SYS_FBA_HIST_12103951"."ACTIVITY_CATEGORY_TYPE_ST_CD" "ACTIVITY_CATEGORY_TYPE_ST_CD","SYS_FBA_HIST_12103951"."EFFECTIVE_DT" "EFFECTIVE_DT","SYS_FBA_HIST_12103951"."EXPIRATION_DT" "EXPIRATION_DT","SYS_FBA_HIST_12103951"."CREATE_TS" "CREATE_TS","SYS_FBA_HIST_12103951"."LAST_UPDATE_TS" "LAST_UPDATE_TS","SYS_FBA_HIST_12103951"."LAST_UPDATE_USER_ID" "LAST_UPDATE_USER_ID","SYS_FBA_HIST_12103951"."VISIBLE_CD" "VISIBLE_CD" FROM "ACTIVITY_OWNER"."SYS_FBA_HIST_12103951" "SYS_FBA_HIST_12103951" WHERE ("SYS_FBA_HIST_12103951"."STARTSCN"<="SYS"."TIMESTAMP_TO_SCN"(TO_TIMESTAMP(TO_CHAR(SYSDATE@!))) OR "SYS_FBA_HIST_12103951"."STARTSCN" IS NULL) AND "SYS_FBA_HIST_12103951"."ENDSCN">"SYS"."TIMESTAMP_TO_SCN"(TO_TIMESTAMP(TO_CHAR(SYSDATE@!))) AND "SYS_FBA_HIST_12103951"."ENDSCN"<=6013800185409 AND ("SYS_FBA_HIST_12103951"."OPERATION" IS NULL OR "SYS_FBA_HIST_12103951"."OPERATION"<>'D') AND "SYS"."TIMESTAMP_TO_SCN"(TO_TIMESTAMP(TO_CHAR(SYSDATE@!)))<6013800185409) UNION ALL (SELECT /*+ USE_MERGE ("SYS_FBA_TCRV_12103951") USE_MERGE ("T") */ "T"."ACTIVITY_PK" "ACTIVITY_PK","T"."ACTIVITY_CATEGORY_TYPE_ST_CD" "ACTIVITY_CATEGORY_TYPE_ST_CD","T"."EFFECTIVE_DT" "EFFECTIVE_DT","T"."EXPIRATION_DT" "EXPIRATION_DT","T"."CREATE_TS" "CREATE_TS","T"."LAST_UPDATE_TS" "LAST_UPDATE_TS","T"."LAST_UPDATE_USER_ID" "LAST_UPDATE_USER_ID","T"."VISIBLE_CD" "VISIBLE_CD" FROM "ACTIVITY_OWNER"."ACTIVITY_STATE" VERSIONS BETWEEN SCN (6013800185409) AND MAXVALUE "T","ACTIVITY_OWNER"."SYS_FBA_TCRV_12103951" "SYS_FBA_TCRV_12103951" WHERE ("T"."VERSIONS_STARTSCN"<="SYS"."TIMESTAMP_TO_SCN"(TO_TIMESTAMP(TO_CHAR(SYSDATE@!))) OR "T"."VERSIONS_STARTSCN" IS NULL) AND ("T"."VERSIONS_ENDSCN">"SYS"."TIMESTAMP_TO_SCN"(TO_TIMESTAMP(TO_CHAR(SYSDATE@!))) OR "T"."VERSIONS_ENDSCN" IS NULL) AND ("T"."VERSIONS_OPERATION" IS NULL OR "T"."VERSIONS_OPERATION"<>'D') AND "SYS_FBA_TCRV_12103951"."RID"(+)=ROWIDTOCHAR("T".ROWID) AND ("SYS_FBA_TCRV_12103951"."STARTSCN"<="SYS"."TIMESTAMP_TO_SCN"(TO_TIMESTAMP(TO_CHAR(SYSDATE@!))) OR "SYS_FBA_TCRV_12103951"."STARTSCN" IS NULL) AND ("SYS_FBA_TCRV_12103951"."ENDSCN"(+)>6013800185409 OR "SYS_FBA_TCRV_12103951"."ENDSCN"(+) IS NULL) AND ("SYS_FBA_TCRV_12103951"."STARTSCN"(+)<6013800185409 OR "SYS_FBA_TCRV_12103951"."STARTSCN"(+) IS NULL))) "from$_subquery$_003" WHERE "from$_subquery$_003"."ACTIVITY_PK"="ACTIVITY"."ACTIVITY_PK" AND ("ACTIVITY"."OWNER_PK"=1763 OR "ACTIVITY"."OWNER_PK"=7059) AND ("ACTIVITY"."ENTITY_PK"=2068114 OR "ACTIVITY"."ENTITY_PK"=2308887)
Return code in qosdInitDirCtx: USEDISBLD
===================================
SPD: END context at statement level
===================================
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT /*+ INDEX ("from$_subquery$_003") */ "from$_subquery$_003"."ACTIVITY_PK" "ACTIVITY_PK","from$_subquery$_003"."ACTIVITY_CATEGORY_TYPE_ST_CD" "ACTIVITY_CATEGORY_TYPE_ST_CD","from$_subquery$_003"."EFFECTIVE_DT" "EFFECTIVE_DT","from$_subquery$_003"."EXPIRATION_DT" "EXPIRATION_DT","from$_subquery$_003"."CREATE_TS" "CREATE_TS","from$_subquery$_003"."LAST_UPDATE_TS" "LAST_UPDATE_TS","from$_subquery$_003"."LAST_UPDATE_USER_ID" "LAST_UPDATE_USER_ID","from$_subquery$_003"."VISIBLE_CD" "VISIBLE_CD" FROM "ACTIVITY_OWNER"."ACTIVITY" "ACTIVITY", ( (SELECT "SYS_FBA_HIST_12103951"."ACTIVITY_PK" "ACTIVITY_PK","SYS_FBA_HIST_12103951"."ACTIVITY_CATEGORY_TYPE_ST_CD" "ACTIVITY_CATEGORY_TYPE_ST_CD","SYS_FBA_HIST_12103951"."EFFECTIVE_DT" "EFFECTIVE_DT","SYS_FBA_HIST_12103951"."EXPIRATION_DT" "EXPIRATION_DT","SYS_FBA_HIST_12103951"."CREATE_TS" "CREATE_TS","SYS_FBA_HIST_12103951"."LAST_UPDATE_TS" "LAST_UPDATE_TS","SYS_FBA_HIST_12103951"."LAST_UPDATE_USER_ID" "LAST_UPDATE_USER_ID","SYS_FBA_HIST_12103951"."VISIBLE_CD" "VISIBLE_CD" FROM "ACTIVITY_OWNER"."SYS_FBA_HIST_12103951" "SYS_FBA_HIST_12103951" WHERE ("SYS_FBA_HIST_12103951"."STARTSCN"<="SYS"."TIMESTAMP_TO_SCN"(TO_TIMESTAMP(TO_CHAR(SYSDATE@!))) OR "SYS_FBA_HIST_12103951"."STARTSCN" IS NULL) AND "SYS_FBA_HIST_12103951"."ENDSCN">"SYS"."TIMESTAMP_TO_SCN"(TO_TIMESTAMP(TO_CHAR(SYSDATE@!))) AND "SYS_FBA_HIST_12103951"."ENDSCN"<=6013800185409 AND ("SYS_FBA_HIST_12103951"."OPERATION" IS NULL OR "SYS_FBA_HIST_12103951"."OPERATION"<>'D') AND "SYS"."TIMESTAMP_TO_SCN"(TO_TIMESTAMP(TO_CHAR(SYSDATE@!)))<6013800185409) UNION ALL (SELECT /*+ USE_MERGE ("SYS_FBA_TCRV_12103951") USE_MERGE ("T") */ "T"."ACTIVITY_PK" "ACTIVITY_PK","T"."ACTIVITY_CATEGORY_TYPE_ST_CD" "ACTIVITY_CATEGORY_TYPE_ST_CD","T"."EFFECTIVE_DT" "EFFECTIVE_DT","T"."EXPIRATION_DT" "EXPIRATION_DT","T"."CREATE_TS" "CREATE_TS","T"."LAST_UPDATE_TS" "LAST_UPDATE_TS","T"."LAST_UPDATE_USER_ID" "LAST_UPDATE_USER_ID","T"."VISIBLE_CD" "VISIBLE_CD" FROM "ACTIVITY_OWNER"."ACTIVITY_STATE" VERSIONS BETWEEN SCN (6013800185409) AND MAXVALUE "T","ACTIVITY_OWNER"."SYS_FBA_TCRV_12103951" "SYS_FBA_TCRV_12103951" WHERE ("T"."VERSIONS_STARTSCN"<="SYS"."TIMESTAMP_TO_SCN"(TO_TIMESTAMP(TO_CHAR(SYSDATE@!))) OR "T"."VERSIONS_STARTSCN" IS NULL) AND ("T"."VERSIONS_ENDSCN">"SYS"."TIMESTAMP_TO_SCN"(TO_TIMESTAMP(TO_CHAR(SYSDATE@!))) OR "T"."VERSIONS_ENDSCN" IS NULL) AND ("T"."VERSIONS_OPERATION" IS NULL OR "T"."VERSIONS_OPERATION"<>'D') AND "SYS_FBA_TCRV_12103951"."RID"(+)=ROWIDTOCHAR("T".ROWID) AND ("SYS_FBA_TCRV_12103951"."STARTSCN"<="SYS"."TIMESTAMP_TO_SCN"(TO_TIMESTAMP(TO_CHAR(SYSDATE@!))) OR "SYS_FBA_TCRV_12103951"."STARTSCN" IS NULL) AND ("SYS_FBA_TCRV_12103951"."ENDSCN"(+)>6013800185409 OR "SYS_FBA_TCRV_12103951"."ENDSCN"(+) IS NULL) AND ("SYS_FBA_TCRV_12103951"."STARTSCN"(+)<6013800185409 OR "SYS_FBA_TCRV_12103951"."STARTSCN"(+) IS NULL))) "from$_subquery$_003" WHERE "from$_subquery$_003"."ACTIVITY_PK"="ACTIVITY"."ACTIVITY_PK" AND ("ACTIVITY"."OWNER_PK"=1763 OR "ACTIVITY"."OWNER_PK"=7059) AND ("ACTIVITY"."ENTITY_PK"=2068114 OR "ACTIVITY"."ENTITY_PK"=2308887)
kkoqbc: optimizing query block SEL$027D0DFA (#0)
:
call(in-use=43584, alloc=49080), compile(in-use=183128, alloc=186168), execution(in-use=32600, alloc=36480)
kkoqbc-subheap (create addr=0x7fa7ec0c8b60)
****************
QUERY BLOCK TEXT
****************
n (7059, 1763) and entity_pk in (2308887, 2068114))
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (optimizer): qb_name=SEL$027D0DFA nbfros=1 flg=0
fro(0): flg=0 objn=12103985 hint_alias="SYS_FBA_HIST_12103951"@"SEL$027D0DFA"
-----------------------------
SYSTEM STATISTICS INFORMATION
-----------------------------
Using dictionary system stats.
Using NOWORKLOAD Stats
CPUSPEEDNW: 2501 millions instructions/sec (default is 100)
IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
IOSEEKTIM: 10 milliseconds (default is 10)
MBRC: NO VALUE blocks (default is 8)
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: SYS_FBA_HIST_12103951 Alias: SYS_FBA_HIST_12103951 (Using composite stats)
#Rows: 17282 SSZ: 0 LGR: 0 #Blks: 502 AvgRowLen: 116.00 NEB: 0 ChainCnt: 0.00 SPC: 0 RFL: 0 RNF: 0 CBK: 0 CHR: 0 KQDFLG: 1
#IMCUs: 0 IMCRowCnt: 0 IMCJournalRowCnt: 0 #IMCBlocks: 0 IMCQuotient: 0.000000
=======================================
SPD: BEGIN context at query block level
=======================================
Query Block SEL$027D0DFA (#0)
Return code in qosdSetupDirCtx4QB: NOCTX
=====================================
SPD: END context at query block level
=====================================
Access path analysis for SYS_FBA_HIST_12103951
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for SYS_FBA_HIST_12103951[SYS_FBA_HIST_12103951]
SPD: Return code in qosdDSDirSetup: NOCTX, estType = TABLE
No statistics type defined for function TIMESTAMP_TO_SCN
No default cost defined for function TIMESTAMP_TO_SCN
Column (#2):
NewDensity:0.000420, OldDensity:0.000430 BktCnt:17282.000000, PopBktCnt:3419.000000, PopValCnt:1, NDV:1911
Column (#2): STARTSCN(NUMBER)
AvgLen: 9 NDV: 1911 Nulls: 0 Density: 0.000420
Histogram: Hybrid #Bkts: 254 UncompBkts: 17282 EndPtVals: 254 ActualVal: yes
No statistics type defined for function TIMESTAMP_TO_SCN
No default cost defined for function TIMESTAMP_TO_SCN
Column (#3):
NewDensity:0.000029, OldDensity:0.000029 BktCnt:17282.000000, PopBktCnt:17274.000000, PopValCnt:113, NDV:121
Column (#3): ENDSCN(NUMBER)
AvgLen: 9 NDV: 121 Nulls: 0 Density: 0.000029
Histogram: Freq #Bkts: 121 UncompBkts: 17282 EndPtVals: 121 ActualVal: yes
Column (#5):
NewDensity:0.249884, OldDensity:0.000029 BktCnt:17282.000000, PopBktCnt:17282.000000, PopValCnt:2, NDV:2
Column (#5): OPERATION(VARCHAR2)
AvgLen: 2 NDV: 2 Nulls: 0 Density: 0.249884
Histogram: Freq #Bkts: 2 UncompBkts: 17282 EndPtVals: 2 ActualVal: no
Table: SYS_FBA_HIST_12103951 Alias: SYS_FBA_HIST_12103951
Card: Original: 17282.000000 Rounded: 22 Computed: 21.612500 Non Adjusted: 21.612500
Scan IO Cost (Disk) = 190.000000
Scan CPU Cost (Disk) = 8869305.760000
Total Scan IO Cost = 190.000000 (scan (Disk))
+ 0.000000 (io filter eval) (= 0.000000 (per row) * 17282.000000 (#rows))
= 190.000000
Total Scan CPU Cost = 8869305.760000 (scan (Disk))
+ 28731745.875000 (cpu filter eval) (= 1662.524353 (per row) * 17282.000000 (#rows))
= 37601051.635000
Access Path: TableScan
Cost: 191.074008 Resp: 191.074008 Degree: 0
Cost_io: 190.000000 Cost_cpu: 37601052
Resp_io: 190.000000 Resp_cpu: 37601052
Best:: AccessPath: TableScan
Cost: 191.074008 Degree: 1 Resp: 191.074008 Card: 21.612500 Bytes: 0.000000
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
PJE: Bypassed; QB has object SEL$027D0DFA (#0)
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
Permutations for Starting Table :0
Join order[1]: SYS_FBA_HIST_12103951[SYS_FBA_HIST_12103951]#0
***********************
Best so far: Table#: 0 cost: 191.074008 card: 21.612500 bytes: 1980.000000
***********************
(newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:2000
*********************************
Number of join permutations tried: 1
*********************************
kkopqIsSerialAP: correlated serial PL/SQL function or external opn
Enumerating distribution method (advanced)
kkopqIsSerialAP: correlated serial PL/SQL function or external opn
kkopqIsSerialAP: correlated serial PL/SQL function or external opn
Trying or-Expansion on query block SEL$027D0DFA (#0)
Or-expansion bypassed: No index driver found.
Or-expansion bypassed: No index driver found.
Transfer Optimizer annotations for query block SEL$027D0DFA (#0)
kkopqIsSerialAP: correlated serial PL/SQL function or external opn
AP: Adaptive joins bypassed for query block SEL$027D0DFA due to disabled by adaptive join parameter
id=0 fptconst predicate="SYS"."TIMESTAMP_TO_SCN"(TO_TIMESTAMP(TO_CHAR(SYSDATE@!)))<6013800185409
kkopqIsSerialAP: correlated serial PL/SQL function or external opn
AutoDOP: Consider caching for SYS_FBA_HIST_12103951[SYS_FBA_HIST_12103951](obj#12103985)
cost:191.074008 blkSize:16384 objSize:502.00 marObjSize:476.90 bufSize:465508.00 affPercent:80 smallTab:YES affinitized:NO
kkecComputeAPDop: IO Dop: 0.000000 - CPU Dop: 0.000000
id=0 frofand predicate=("SYS_FBA_HIST_12103951"."OPERATION"<>'D' OR "SYS_FBA_HIST_12103951"."OPERATION" IS NULL) AND "SYS_FBA_HIST_12103951"."ENDSCN">"SYS"."TIMESTAMP_TO_SCN"(TO_TIMESTAMP(TO_CHAR(SYSDATE@!))) AND ("SYS_FBA_HIST_12103951"."STARTSCN"<="SYS"."TIMESTAMP_TO_SCN"(TO_TIMESTAMP(TO_CHAR(SYSDATE@!))) OR "SYS_FBA_HIST_12103951"."STARTSCN" IS NULL) AND "SYS_FBA_HIST_12103951"."ENDSCN"<=6013800185409
Transfer optimizer annotations for SYS_FBA_HIST_12103951[SYS_FBA_HIST_12103951]
Final cost for query block SEL$027D0DFA (#0) - All Rows Plan:
Best join order: 1
Cost: 191.074008 Degree: 1 Card: 22.000000 Bytes: 1980.000000
Resc: 191.074008 Resc_io: 190.000000 Resc_cpu: 37601052
Resp: 191.074008 Resp_io: 190.000000 Resc_cpu: 37601052
kkoqbc-subheap (delete addr=0x7fa7ec0c8b60, in-use=41600, alloc=65704)
kkoqbc-end:
:
call(in-use=142568, alloc=213176), compile(in-use=237040, alloc=241576), execution(in-use=32600, alloc=36480)
kkoqbc: finish optimizing query block SEL$027D0DFA (#0)
kkoqbc: optimizing query block SEL$72425F55 (#0)
:
call(in-use=142568, alloc=213176), compile(in-use=237136, alloc=241576), execution(in-use=32600, alloc=36480)
kkoqbc-subheap (create addr=0x7fa7ec0c8b60)
****************
QUERY BLOCK TEXT
****************
Not available.
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (optimizer): qb_name=SEL$72425F55 nbfros=2 flg=0
fro(0): flg=0 objn=12103951 hint_alias="T"@"SEL$027D0DFB"
fro(1): flg=0 objn=12103988 hint_alias="SYS_FBA_TCRV_12103951"@"SEL$027D0DFC"
-----------------------------
SYSTEM STATISTICS INFORMATION
-----------------------------
Using dictionary system stats.
Using NOWORKLOAD Stats
CPUSPEEDNW: 2501 millions instructions/sec (default is 100)
IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
IOSEEKTIM: 10 milliseconds (default is 10)
MBRC: NO VALUE blocks (default is 8)
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: SYS_FBA_TCRV_12103951 Alias: SYS_FBA_TCRV_12103951
#Rows: 3036770 SSZ: 0 LGR: 0 #Blks: 11167 AvgRowLen: 39.00 NEB: 0 ChainCnt: 0.00 SPC: 0 RFL: 0 RNF: 0 CBK: 0 CHR: 0 KQDFLG: 1
#IMCUs: 0 IMCRowCnt: 0 IMCJournalRowCnt: 0 #IMCBlocks: 0 IMCQuotient: 0.000000
Index Stats::
Index: SYS_FBA_TCRV_IDX_12103951 Col#: 1 2
LVLS: 2 #LB: 13099 #DK: 3017298 LB/K: 1.00 DB/K: 1.00 CLUF: 23332.00 NRW: 3017298.00 SSZ: 0.00 LGR: 0.00 CBK: 0.00 GQL: 0.00 CHR: 0.00 KQDFLG: 1 BSZ: 1
KKEISFLG: 1
***********************
Table Stats::
Table: ACTIVITY_STATE Alias: T
#Rows: 3036770 SSZ: 0 LGR: 0 #Blks: 15757 AvgRowLen: 72.00 NEB: 0 ChainCnt: 0.00 SPC: 0 RFL: 0 RNF: 0 CBK: 0 CHR: 0 KQDFLG: 1
#IMCUs: 0 IMCRowCnt: 0 IMCJournalRowCnt: 0 #IMCBlocks: 0 IMCQuotient: 0.000000
Index Stats::
Index: PK_ACTIVITY_STATE Col#: 1 2 5
LVLS: 2 #LB: 11553 #DK: 2901784 LB/K: 1.00 DB/K: 1.00 CLUF: 2744168.00 NRW: 2901784.00 SSZ: 0.00 LGR: 0.00 CBK: 0.00 GQL: 0.00 CHR: 0.00 KQDFLG: 1 BSZ: 1
KKEISFLG: 1
=======================================
SPD: BEGIN context at query block level
=======================================
Query Block SEL$72425F55 (#0)
Return code in qosdSetupDirCtx4QB: NOCTX
=====================================
SPD: END context at query block level
=====================================
Access path analysis for ACTIVITY_STATE
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for ACTIVITY_STATE[T]
SPD: Return code in qosdDSDirSetup: NOCTX, estType = TABLE
No statistics type defined for function TIMESTAMP_TO_SCN
No default cost defined for function TIMESTAMP_TO_SCN
No statistics type defined for function TIMESTAMP_TO_SCN
No default cost defined for function TIMESTAMP_TO_SCN
Table: ACTIVITY_STATE Alias: T
Card: Original: 3036770.000000 Rounded: 2815 Computed: 2814.658744 Non Adjusted: 2814.658744
Scan IO Cost (Disk) = 15758.000000
Scan CPU Cost (Disk) = 548749060.160000
Total Scan IO Cost = 15758.000000 (scan (Disk))
+ 0.000000 (io filter eval) (= 0.000000 (per row) * 3036770.000000 (#rows))
= 15758.000000
Total Scan CPU Cost = 548749060.160000 (scan (Disk))
+ 1153033431.427969 (cpu filter eval) (= 379.690734 (per row) * 3036770.000000 (#rows))
= 1701782491.587969
Access Path: TableScan
Cost: 15806.608409 Resp: 15806.608409 Degree: 0
Cost_io: 15758.000000 Cost_cpu: 1701782492
Resp_io: 15758.000000 Resp_cpu: 1701782492
****** trying bitmap/domain indexes ******
****** Costing Index PK_ACTIVITY_STATE
Access Path: index (FullScan)
Index: PK_ACTIVITY_STATE
resc_io: 11555.000000 resc_cpu: 692935778
ix_sel: 1.000000 ix_sel_with_filters: 1.000000
Cost: 2314.958497 Resp: 2314.958497 Degree: 0
****** finished trying bitmap/domain indexes ******
Best:: AccessPath: TableScan
Cost: 15806.608409 Degree: 1 Resp: 15806.608409 Card: 2814.658744 Bytes: 0.000000
Access path analysis for SYS_FBA_TCRV_12103951
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for SYS_FBA_TCRV_12103951[SYS_FBA_TCRV_12103951]
SPD: Return code in qosdDSDirSetup: NOCTX, estType = TABLE
Column (#3): ENDSCN(NUMBER)
AvgLen: 22 NDV: 0 Nulls: 3036770 Density: 0.000000
Column (#2):
NewDensity:0.000563, OldDensity:0.000000 BktCnt:3036770.000000, PopBktCnt:3036770.000000, PopValCnt:4, NDV:4
Column (#2): STARTSCN(NUMBER)
AvgLen: 9 NDV: 4 Nulls: 0 Density: 0.000563
Histogram: Freq #Bkts: 4 UncompBkts: 3036770 EndPtVals: 4 ActualVal: yes
Table: SYS_FBA_TCRV_12103951 Alias: SYS_FBA_TCRV_12103951
Card: Original: 3036770.000000 Rounded: 3036770 Computed: 3036770.000000 Non Adjusted: 3036770.000000
Scan IO Cost (Disk) = 4189.000000
Scan CPU Cost (Disk) = 685785040.960000
Total Scan IO Cost = 4189.000000 (scan (Disk))
+ 0.000000 (io filter eval) (= 0.000000 (per row) * 3036770.000000 (#rows))
= 4189.000000
Total Scan CPU Cost = 685785040.960000 (scan (Disk))
+ 212573900.000000 (cpu filter eval) (= 70.000000 (per row) * 3036770.000000 (#rows))
= 898358940.960000
Access Path: TableScan
Cost: 4214.660035 Resp: 4214.660035 Degree: 0
Cost_io: 4189.000000 Cost_cpu: 898358941
Resp_io: 4189.000000 Resp_cpu: 898358941
****** trying bitmap/domain indexes ******
****** Costing Index SYS_FBA_TCRV_IDX_12103951
Access Path: index (FullScan)
Index: SYS_FBA_TCRV_IDX_12103951
resc_io: 13101.000000 resc_cpu: 731101071
ix_sel: 1.000000 ix_sel_with_filters: 1.000000
Cost: 2624.376522 Resp: 2624.376522 Degree: 0
****** finished trying bitmap/domain indexes ******
Best:: AccessPath: TableScan
Cost: 4214.660035 Degree: 1 Resp: 4214.660035 Card: 3036770.000000 Bytes: 0.000000
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
PJE: Bypassed; QB has object SEL$72425F55 (#0)
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
Permutations for Starting Table :0
Join order[1]: ACTIVITY_STATE[T]#0 SYS_FBA_TCRV_12103951[SYS_FBA_TCRV_12103951]#1
***************
Now joining: SYS_FBA_TCRV_12103951[SYS_FBA_TCRV_12103951]#1
***************
NL Join
Outer table: Card: 2814.658744 Cost: 15806.608409 Resp: 15806.608409 Degree: 1 Bytes:
Access path analysis for SYS_FBA_TCRV_12103951
Scan IO Cost (Disk) = 4187.625577
Scan CPU Cost (Disk) = 685785040.960000
Total Scan IO Cost = 4187.625577 (scan (Disk))
+ 0.000000 (io filter eval) (= 0.000000 (per row) * 3036770.000000 (#rows))
= 4187.625577
Total Scan CPU Cost = 685785040.960000 (scan (Disk))
+ 911031070.000000 (cpu filter eval) (= 300.000023 (per row) * 3036770.000000 (#rows))
= 1596816110.960000
Inner table: SYS_FBA_TCRV_12103951 Alias: SYS_FBA_TCRV_12103951
Access Path: TableScan
NL Join: Cost: 11932365.405499 Resp: 11932365.405499 Degree: 1
Cost_io: 11803924.000000 Cost_cpu: 4496739134844
Resp_io: 11803924.000000 Resp_cpu: 4496739134844
****** Costing Index SYS_FBA_TCRV_IDX_12103951
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_SCAN
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
Access Path: index (RangeScan)
Index: SYS_FBA_TCRV_IDX_12103951
resc_io: 3.000000 resc_cpu: 30539
ix_sel: 3.3142e-07 ix_sel_with_filters: 3.3142e-07
NL Join : Cost: 17496.099504 Resp: 17496.099504 Degree: 1
Cost_io: 17447.000000 Cost_cpu: 1718975746
Resp_io: 17447.000000 Resp_cpu: 1718975746
****** trying bitmap/domain indexes ******
****** finished trying bitmap/domain indexes ******
Best NL cost: 17496.099504
resc: 17496.099504 resc_io: 17447.000000 resc_cpu: 1718975746
resp: 17496.099504 resp_io: 17447.000000 resc_cpu: 1718975746
SPD: Return code in qosdDSDirSetup: NOCTX, estType = JOIN
Outer Join Card: 2814.658744 = max ( outer (2814.658744),(outer (2814.658744) * inner (3036770.000000) * sel (3.2930e-07)))
Join Card - Rounded: 2815 Computed: 2814.658744
kkopqIsSerialAP: correlated serial PL/SQL function or external opn
Outer table: ACTIVITY_STATE Alias: T
resc: 15806.608409 card 2814.658744 bytes: deg: 1 resp: 15806.608409
Inner table: SYS_FBA_TCRV_12103951 Alias: SYS_FBA_TCRV_12103951
resc: 4214.660035 card: 3036770.000000 bytes: deg: 1 resp: 4214.660035
using dmeth: 2 #groups: 1
SORT ressource