Re: Inmemory table on Exadata and indexes

From: Sanjay Mishra <"Sanjay>
Date: Wed, 11 Apr 2018 15:54:10 +0000 (UTC)
Message-ID: <198320359.1195002.1523462050685_at_mail.yahoo.com>

 Andy
Here is the details and thanks for the help. ECKACCT table is completely populated in INMEMORY

select distinct sqrno from eckacct where boxId IN(<here Values are dynamic for all SQL with 100 to 500>);-----------------------------------------------------------------------------------| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |-----------------------------------------------------------------------------------|   0 | SELECT STATEMENT   |              |   390 |  4490 |     5  (25)| 00:00:01 ||   1 |  HASH UNIQUE       |              |   390 |  4490 |     5  (25)| 00:00:01 ||   2 |   INLIST ITERATOR  |              |       |       |            |          ||*  3 |    INDEX RANGE SCAN| ECKACCT_IDX2 |   390 |  4490 |     3   (0)| 00:00:01 |-----------------------------------------------------------------------------------
select /*+ full */ distinct sqrno from eckacct where boxId IN(<here Values are dynamic for all SQL with 100 to 500>);-------------------------------------------------------------------------------------------| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |-------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT            |             |   390 |  4490 |  1881K (79)| 00:01:20 ||   1 |  HASH UNIQUE                |             |   390 |  4490 |  1881K (79)| 00:01:20 ||*  2 |   TABLE ACCESS INMEMORY FULL| ECKACCT     |   390 |  4490 |  1881K (79)| 00:01:20 |-------------------------------------------------------------------------------------------

select count(*) from eckacct;count(*)-------------6389233191

TxSanjay

    On Wednesday, April 11, 2018, 3:59:38 AM EDT, Andy Sayer <andysayer_at_gmail.com> wrote:  

 When you say the query is called 10k times in 10 mins, is the 10k different business process calls calling it or are you doing silly row-by-row calls of the query multiple times in one business process? Think about how many partitions your table has, how many different partitions will need to be hit for your 100 values? Are you really just asking every execution of the SQL to read the entire table partition by partition, sometimes multiple times? If this has been written as a slow-by-slow process then that’s an easy fix. Include the complete list of values required in the in list or do it as a join from wherever these values are being generated. Doing one full scan of the entire table and then applying the filter (either as a filter or as a hash join type condition) will probably be considerably faster that repeatedly scanning the same partitions. If this is legitimately calling the statement this many times then what’s really wrong with having the supporting indexes? How selective is your filter column within the partition? Is it worth increasing the number of hash partitions?  I strongly suggest you copy and paste the full execution plans (including the predicates section) rather than a brief comment about them.  Andrew

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Apr 11 2018 - 17:54:10 CEST