Did some More testing
I had tried to use the INMEMORY Hint but still it was using External Index
I then Tried to using FULL Hint and found that Exection plan is doing Full
Inmemory Scan and as the table has 7000K Blocks and so saw was doing long
operation and doing 100K blocks a minute.
Which make me believe that something is not good in the Setup.
Forgot to Mentioned that this is 12.1.0.2 with Jan Patch
Sanjay
On Tuesday, April 10, 2018, 9:15:36 PM EDT, Sanjay Mishra
<dmarc-noreply@xxxxxxxxxxxxx> wrote:
Hi
I had strange issue and not sure if I am doing wrongly as I had populated table
in memory and using 200G for In Memory. Table is Compressed on Exadata with
size of 30G. It has only three column
Eckacct( scnid number(20), Sqrno Number(10), BoxId nuber (20)) and it is
compress for query low
Tables is completely populated in Memory Table has index (Eckacct_idx1
(scnid,boxid)Partiioned with Boxid and Another index eckacct_idx2(boxid)
Running Queryselect distinct sqrno from exkacct where boxId in (around 100
values);
Tables has aorund 10Mill plus Records
It is using Inlist Iterator ==> Index Range Scan ==> eckacct_idx2 ==Elapsed:
00:00:00:18
I made this index invisible and ran again
It is then using Inlist Iterator ==> Partion Hash Iterator ==> Index
Eckacct_idx1 ==Elapsed: 00:00:00:12
If I also made this index invisible so as to use In memory data, It never
returned the data and had to cancel the query after few min
Can someone point as why it is not using Oracle inmemory as this query is
called 10K time in 10min and traget is reduce the time to meet the SLA. Query
is supposed to be using inmemory or if I missing anything
Thanks for your help and suggestionSanjay
