My Oracle Support Banner

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

Last updated on MAY 20, 2020

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 <schema>.<table01> /home/timesten/XXX/<table01>.csv
   ttbulkcp -i dsn_shuhn_all <schema>.<table02> /home/timesten/XXX/<table02>.csv


Total 450k records were inserted into <table01> table while 14K records got inserted into <table02> table.

2) Checked the explain Plan

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

 

Query Optimizer Plan:

STEP: 1
LEVEL: 3
OPERATION: TblLkTtreeScan
TBLNAME: <table01>
IXNAME: PK_<table01>
INDEXED CONDITION: <table01>.XXX_DATE_S = '20110113'
NOT INDEXED: <NULL>


STEP: 2
LEVEL: 3
OPERATION: TblLkTtreeScan
TBLNAME: <table02>
IXNAME: PK_<table02>
INDEXED CONDITION: <table02>.XXX_DATE_S >= <table01>.XXX_DATE_S
NOT INDEXED: <table02>.DERIVATE_LEVEL_N = 0


STEP: 3
LEVEL: 2
OPERATION: MergeJoin
TBLNAME: <NULL>
IXNAME: <NULL>
INDEXED CONDITION: <table01>.XXX_DATE_S = <table02>.XXX_DATE_S
NOT INDEXED: <table01>.STP_I = <table02>.STP_I AND <table01>.EXP_DATE_N = <table02>.EXPIRATION_DATE_N AND <table01>.COMMODITY_N = <table02>.COMMODITY_N AND <table01>.MODIFIER_C = <table02>.MODIFIER_C AND <table01>.INS_GROUP_C = <table02>.INSTRUMENT_GROUOP_C AND <table01>.MARKET_C = <table02>.MARKET_C AND <table01>.COUNTRY_C = <table02>.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 <table02>.XXX_DATE_S >= <table01>.XXX_DATE_S condition in the execution plan. The exucution time was very high. It was taking 241 seconds.

Changes

 

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!


In this Document
Symptoms
Changes
Cause
Solution


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