Change in Execution Plan when huge number of rows loaded into the table

(Doc ID 1284117.1)

Last updated on AUGUST 15, 2016

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

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 hundreds of Community platforms