My Oracle Support Banner

Change in Execution Plan when huge number of rows loaded into the table (Doc ID 1284117.1)

Last updated on JANUARY 23, 2018

Applies to:

Oracle TimesTen In-Memory Database - Version 11.2.1.6.6 and later
Information in this document applies to any platform.
***Checked for relevance on 25-SEP-2013***
***Checked for relevance on 05-MAR-2015***

Symptoms

After ttbulkcp is executed on the table the execution plan generated for the below query was changed and query become very slow.

Steps Followed
---------------

1) Inserted records into the table using ttbulkcp

   ttbulkcp -i dsn_shuhn_all MSMCGYUMYU.BD258 /home/timesten/rajesh/BD258.csv
   ttbulkcp -i dsn_shuhn_all MSMCGYUMYU.DQ122 /home/timesten/rajesh/DQ122.csv


Total 450k records were inserted into BD258 table while 14K records got inserted into DQ122 table.

2) Checked the explain Plan

Command> explain SELECT BD258.COUNTRY_C
, BD258.MARKET_C
, BD258.INS_GROUP_C
, BD258.MODIFIER_C
, BD258.COMMODITY_N
, BD258.EXP_DATE_N
, BD258.STP_I
, BD258.BUSINESS_DATE_S
, MAX(BD258.RECORD_NO) AS RECORD_NO
, MAX(DQ122.DEC_IN_PREMIUM_N) AS DEC_IN_PREMIUM_N
FROM BD258, DQ122
WHERE BD258.BUSINESS_DATE_S = '20110113'
AND DQ122.BUSINESS_DATE_S = BD258.BUSINESS_DATE_S
AND BD258.COUNTRY_C = DQ122.COUNTRY_C
AND BD258.MARKET_C = DQ122.MARKET_C
AND BD258.INS_GROUP_C = DQ122.INSTRUMENT_GROUOP_C
AND BD258.MODIFIER_C = DQ122.MODIFIER_C
AND BD258.COMMODITY_N = DQ122.COMMODITY_N
AND BD258.EXP_DATE_N = DQ122.EXPIRATION_DATE_N
AND BD258.STP_I = DQ122.STP_I
AND DQ122.DERIVATE_LEVEL_N = 0
GROUP BY
BD258.COUNTRY_C
, BD258.MARKET_C
, BD258.INS_GROUP_C
, BD258.MODIFIER_C
, BD258.COMMODITY_N
, BD258.EXP_DATE_N
, BD258.STP_I
, BD258.BUSINESS_DATE_S;

 

Query Optimizer Plan:

STEP: 1
LEVEL: 3
OPERATION: TblLkTtreeScan
TBLNAME: BD258
IXNAME: PK_BD258
INDEXED CONDITION: BD258.BUSINESS_DATE_S = '20110113'
NOT INDEXED: <NULL>


STEP: 2
LEVEL: 3
OPERATION: TblLkTtreeScan
TBLNAME: DQ122
IXNAME: PK_DQ122
INDEXED CONDITION: DQ122.BUSINESS_DATE_S >= BD258.BUSINESS_DATE_S
NOT INDEXED: DQ122.DERIVATE_LEVEL_N = 0


STEP: 3
LEVEL: 2
OPERATION: MergeJoin
TBLNAME: <NULL>
IXNAME: <NULL>
INDEXED CONDITION: BD258.BUSINESS_DATE_S = DQ122.BUSINESS_DATE_S
NOT INDEXED: BD258.STP_I = DQ122.STP_I AND BD258.EXP_DATE_N = DQ122.EXPIRATION_DATE_N AND BD258.COMMODITY_N = DQ122.COMMODITY_N AND BD258.MODIFIER_C = DQ122.MODIFIER_C AND BD258.INS_GROUP_C = DQ122.INSTRUMENT_GROUOP_C AND BD258.MARKET_C = DQ122.MARKET_C AND BD258.COUNTRY_C = DQ122.COUNTRY_C


STEP: 4
LEVEL: 1
OPERATION: GroupBy
TBLNAME: <NULL>
IXNAME: <NULL>
INDEXED CONDITION: <NULL>
NOT INDEXED: <NULL>


Here the optimizer is using the TblLkTtreeScan along with DQ122.BUSINESS_DATE_S >= BD258.BUSINESS_DATE_S condition in the execution plan. The exucution time was very high. It was taking 241 seconds.

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.