Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question.

Asked: March 29, 2003 - 9:40 am UTC

Last updated: November 29, 2024 - 1:58 pm UTC

Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

Hi Tom,

Some questions about the SQL tuning.

1) I found that when using "IN" in the where clause , INLIST ITERATOR is shown on the explain plan in a cost-based database (and using the index correctly, the response is fast). However, no such INLIST ITERATOR in rule-based (and using the full table scan, the response is slow). Is INLIST ITERATOR only occur on cost-based? Is it possible to force the optimizer to use INLIST ITERATOR in a rule-based database (without any hints added to the SQL statement or using alter session set optimizer_mode = choose)? Or is it possible to rewrite the "IN" to other forms such that the index can be used in rule-based database? I have tried to rewrite "IN" to "OR" but the index still cannot be used. The only way the index can be used is using UNION ALL the values of "IN".

2) If the database is rule-based (optimizer_mode=rule), and the table has statistics, will Oracle use cost-based to answer the query? I rememeber that Oracle will use rule-based if the optimizer_mode is set to rule (from Oracle documentation), no matter whether the table has statistics. But I found that in some situation Oracle will use cost-based.


Thanks,
David

and Tom said...

1) the CBO is the only optimizer to be using, especially since in the very next release of Oracle -- the RBO is gone, non-existent.

There are literrally hundreds of things the CBO can do (in-list iterator being one) that the RBO has no idea about. The Performance guide lists all that the RBO knows of (very simple stuff).

You will not see a plan using in-list iterator without using the CBO.

but -- the RBO is index crazy -- it will use an index with an inlist.


1 select /*+ RULE */ * from emp
2* where empno in ( 1,2,3,4 )
scott@ORA920> /

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 CONCATENATION
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
3 2 INDEX (UNIQUE SCAN) OF 'EMP_PK' (UNIQUE)

4 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
5 4 INDEX (UNIQUE SCAN) OF 'EMP_PK' (UNIQUE)
6 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
7 6 INDEX (UNIQUE SCAN) OF 'EMP_PK' (UNIQUE)
8 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
9 8 INDEX (UNIQUE SCAN) OF 'EMP_PK' (UNIQUE)


1 select /*+ RULE */ * from emp
2* where empno in ( select user_id from all_users )
scott@ORA920> /

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 NESTED LOOPS
2 1 VIEW OF 'VW_NSO_1'
3 2 SORT (UNIQUE)
4 3 NESTED LOOPS
5 4 NESTED LOOPS
6 5 TABLE ACCESS (FULL) OF 'USER$'
7 5 TABLE ACCESS (CLUSTER) OF 'TS$'
8 7 INDEX (UNIQUE SCAN) OF 'I_TS#' (NON-UNIQUE)
9 4 TABLE ACCESS (CLUSTER) OF 'TS$'
10 9 INDEX (UNIQUE SCAN) OF 'I_TS#' (NON-UNIQUE)
11 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
12 11 INDEX (UNIQUE SCAN) OF 'EMP_PK' (UNIQUE)





2) The "some situation" is when the underlying object is not even visible to the RBO -- such as

o IOT's index organized tables
o partitioned tables
o tables with non-default degrees of parallelism set
o tables with domain (text, spatial, etc) indexes

for example. Those tables, regardless of the presence of statistics, can ONLY be processed by the CBO.



Rating

  (30 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Full table scan of large table may be good?

A reader, April 11, 2003 - 9:44 am UTC

Hi Tom,

My existing database is rule-based and this database cannot change to cost-based in the near future. I found that when using inlist operator, the performance is so bad. Only include the CHOOSE hint can solve the problem. I want to ask, should I use CHOOSE hint if the where clause has the inlist operator (assume the database is rule-based)?

Another question:

Table : Records
buyer : 2500000
stock : 400000
unit : 2500000
stock_code : 100
other_stock : 500
order : 400000

select count(*)
from buyer BY,stock ST,UNIT UT,stock_code SC,other_stock OS,order OD
where BY_ID = UT_ID AND
ST_ID = UT_ID AND
OD_ID = BY_ID AND
OD_ID = ST_ID AND
SC_code_1 = BY_code_1 AND
SC_code_2 = BY_code_2 AND
UT_CODE = OS_CODE(+) AND
OD_id in (1,2,3);

Execution Plan (4 seconds)
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE
1 0 SORT (AGGREGATE)
2 1 COUNT (STOPKEY)
3 2 NESTED LOOPS
4 3 NESTED LOOPS
5 4 NESTED LOOPS (OUTER)
6 5 NESTED LOOPS
7 6 NESTED LOOPS
8 7 TABLE ACCESS (FULL) OF 'order'
9 7 TABLE ACCESS (BY INDEX ROWID) OF 'stock'
10 9 INDEX (RANGE SCAN) OF 'ST_INDEX_7' (NON-UNIQUE)
11 6 TABLE ACCESS (BY INDEX ROWID) OF 'UNIT'
12 11 INDEX (RANGE SCAN) OF 'UT_INDEX_2' (NON-UNIQUE)
13 5 INDEX (UNIQUE SCAN) OF 'OS_PRIMARY_KEY' (UNIQUE)
14 4 TABLE ACCESS (BY INDEX ROWID) OF 'buyer'
15 14 INDEX (UNIQUE SCAN) OF 'BY_PRIMARY_KEY' (UNIQUE)
16 3 INDEX (UNIQUE SCAN) OF 'SC_PRIMARY_KEY' (UNIQUE)

But now I change the order of the FROM table list as follow. The reason I move ORDER to the left is in rule-based, the right-most table in the FROM list is resolve first. (I want OTHER_STOCK is resolve first since it contains only about 500 records)

select count(*)
from buyer BY,stock ST,order OD, UNIT UT,stock_code SC,other_stock OS
where BY_ID = UT_ID AND
ST_ID = UT_ID AND
OD_ID = BY_ID AND
OD_ID = ST_ID AND
SC_code_1 = BY_code_1 AND
SC_code_2 = BY_code_2 AND
UT_CODE = OS_CODE(+) AND
OD_id in (1,2,3);


Execution Plan (over 10 mins)
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE
1 0 SORT (AGGREGATE)
2 1 COUNT (STOPKEY)
3 2 NESTED LOOPS
4 3 NESTED LOOPS
5 4 NESTED LOOPS (OUTER)
6 5 NESTED LOOPS
7 6 NESTED LOOPS
8 7 TABLE ACCESS (FULL) OF 'stock_code'
9 7 TABLE ACCESS (BY INDEX ROWID) OF 'buyer'
10 9 INDEX (RANGE SCAN) OF 'BY_INDEX_4' (NON-UNIQUE)
11 6 TABLE ACCESS (BY INDEX ROWID) OF 'UNIT'
12 11 INDEX (UNIQUE SCAN) OF 'UT_PRIMARY_KEY' (UNIQUE)
13 5 INDEX (UNIQUE SCAN) OF 'OS_PRIMARY_KEY' (UNIQUE)
14 4 INDEX (UNIQUE SCAN) OF 'OD_PRIMARY_KEY' (UNIQUE)
15 3 TABLE ACCESS (BY INDEX ROWID) OF 'stock'
16 15 INDEX (UNIQUE SCAN) OF 'ST_PRIMARY_KEY' (UNIQUE)

The result is so bad. I don't know why in case 1, the full table scan of the table ORDER (340000 records) is faster than the full scan of the table STOCK_CODE (100 records only). Is the inlist operator (OD_ID in table ORDER) makes the full table scan of ORDER faster?

Thanks
David



Tom Kyte
April 13, 2003 - 7:06 pm UTC

You are apparently using the CBO with a mixture of analyzed and unanalyzed tables which is like playing with a loaded gun. Someone will be very sorry in the end. In your database, it makes sense to use RBO only.

The RBO is very sensitive to the order of tables in the from clause (right to left) and the order of predicates (bottom up). If you want to use the RBO, you need to write your queries to that optimizer specifically.

Execution Plan...

A reader, April 14, 2003 - 9:05 am UTC

Hi Tom,

I have a question regarding the SQL tuning in Oracle. I found that I cannot predict the execution plan that Oracle is going to generate for the SQL statement. My tuning "habit" is, for the poor SQL, look the execution plan, then look whether the index has been used, what is the value of the cost value (for cost-based) etc. But I cannot explain why Oracle generate the execution plan A for SQL A, execution plan B for SQL B...... is this a normal practice for SQL tuning? For example, in rule-based, the last table in the FROM is the driving table. But I found that this is not always the true...

Another question. What is the meaning of the cost value (and its unit) in the execution plan? (I sometimes found that the cost is over 1000 for some SQLs)
Thanks,
David

Tom Kyte
April 14, 2003 - 10:17 am UTC

It is the job of the optimizer to develop a query plan.

It is our job to read it.

To understand how it works, you need to read the performance guide, the way the optimizer does what it does is documented in there.



Ignore the cost for now. It is just a number. It doesn't have direct relevance to time, space, anything you or I care about.

choose is not a "hint"?

A reader, April 15, 2003 - 8:27 am UTC

Hi Tom,

You mentioned that choose is not a hint. Do you mean there is no /*+ choose */? But I found that it is valid to use /*+ choose */.

David

Tom Kyte
April 15, 2003 - 10:01 am UTC

so there is, never noticed it before.

how does in-list work

PINGU, May 07, 2003 - 10:38 am UTC

Hi

Have a question about in-list operator, the following example if from Oracle 9.2 docu (Database Performance Tuning Guide and Reference)

SELECT header_id, line_id, revenue_amount
FROM so_lines_all
WHERE header_id IN (1011,1012,1013);

SELECT header_id, line_id, revenue_amount
FROM so_lines_all
WHERE header_id = 1011
OR header_id = 1012
OR header_id = 1013;
Plan
-------------------------------------------------
SELECT STATEMENT
INLIST ITERATOR
TABLE ACCESS BY INDEX ROWID SO_LINES_ALL
INDEX RANGE SCAN SO_LINES_N1

If we use RBO here we would see

TABLE ACCESS BY INDEX ROWID SO_LINES_ALL
INDEX RANGE SCAN SO_LINES_N1

3 times and a final concatenation, so it´s like running the query

SELECT header_id, line_id, revenue_amount
FROM so_lines_all
WHERE header_id = :b1

3 times, am I right?

Using IN-List Iterator how does Oracle perform? I mean it does not run the query 3 times as RBO but then how does it do it?

From the docu it says:

"extra step occurs when the IN-list iterator feeds the equality clause with unique values from the IN-list"

If I have three values in the list, Oracle binds three times but does it run three times as RBO...? This part the docu doesnt explain fully in my opinion...

Finally, in the docu it also says

"The IN-list operator is especially useful if there is an expensive first step that you do not want to repeat for every IN-list element"

It then shows an example (example 2.5), in that example I dont understand which part is the expensive first part


cheers

Tom Kyte
May 07, 2003 - 11:10 am UTC

yes on the RBO discussion....


It (the cbo) is conceptually the same but it has greater abilities to merge and optimize more complex queries -- optimizing the union all (concat stuff) wouldn't be trivial. considering it a single query and doing stuff that way -- is.

That last part is what I'm talking about -- instead of the query being physically processed AS IF it were 3 queries (in your case) with whatever work each of the three queries needs to do -- the CBO would do it once.

more examples...

A reader, May 07, 2003 - 11:49 am UTC

Hi again

Thanks for your prompt reply. I have a further question after read a bit further the documentation.

In example 2-11, 2-12, 2-13

It says 2-12 is the worst, I can understand that but which is better between 2-11 and 2-13? I suspect 2-11 is the best but in the documentation it seems that it´s saying 2-13 is better, I dont get it... :-(


EXAMPLE 2-11
--------------------------------------------------------
SELECT h.customer_id, l.line_id, l.revenue_amount
FROM so_lines_all l, so_headers_all h
WHERE l.s7 = 20
AND h.original_system_reference = l.attribute5
AND h.original_system_source_code IN (1013,1014);

Plan
--------------------------------------------------
SELECT STATEMENT
NESTED LOOPS
TABLE ACCESS FULL SO_LINES_ALL
TABLE ACCESS BY INDEX ROWID SO_HEADERS_ALL
INDEX RANGE SCAN SO_HEADERS_N5

EXAMPLE 2-13
------------------------------------------------------
SELECT /*+INDEX(h so_headers_n9 */ h.customer_id, l.line_id, l.revenue_amount
FROM so_lines_all l, so_headers_all h
WHERE l.s7 = 20
AND h.original_system_reference = l.attribute5
AND h.original_system_source_code IN (1013,1014);

Plan
--------------------------------------------------
SELECT STATEMENT
NESTED LOOPS
TABLE ACCESS FULL SO_LINES_ALL
INLIST ITERATOR
TABLE ACCESS BY INDEX ROWID SO_HEADERS_ALL
INDEX RANGE SCAN SO_HEADERS_N9

Tom Kyte
May 07, 2003 - 1:44 pm UTC

hows about a link into otn.oracle.com to the documentation you are referencing.

sorry here it is

PINGU, May 07, 2003 - 2:40 pm UTC

IN list as a subquery

Arun Gupta, May 07, 2003 - 7:48 pm UTC

Tom
I know that I am not comparing similar elements, but please see the SQL statements and their plans. In first statement, I see the INLIST ITERATOR but in the second SQL I am simulating the list through a query and getting a cartesian. The table in queries is a global temporary table for session.
Thanks


SQL> ed
Wrote file afiedt.buf
 
  1  DELETE FROM t_refl_search_work_sess
  2      WHERE ROWID IN ('AAQHaJAABAAAHaKAAB', 'AAQHaJAABAAAHaKAAC',
  3*             'AAQHaJAABAAAHaKAAF')
SQL> /
 
0 rows deleted.
 

Execution Plan
----------------------------------------------------------
   0      DELETE STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=7)
   1    0   DELETE OF 'T_REFL_SEARCH_WORK_SESS'
   2    1     INLIST ITERATOR
   3    2       TABLE ACCESS (BY USER ROWID) OF 'T_REFL_SEARCH_WORK_SE
          SS' (Cost=1 Card=1 Bytes=7)
 
 
 

SQL>
DELETE      
FROM t_refl_search_work_sess
WHERE ROWID IN (
      SELECT e.ROWID
      FROM   t_refl_search_work_sess e
      WHERE  e.ROWID >
      (SELECT MIN (x.ROWID)
       FROM   t_refl_search_work_sess x
       WHERE   
             x.idn_entity_legal_provr = e.idn_entity_legal_provr
         AND    x.idn_loc_provr = e.idn_loc_provr
         ND    x.txt_value_field_2 = e.txt_value_field_2) )


/

Execution Plan
----------------------------------------------------------
   0      DELETE STATEMENT Optimizer=CHOOSE (Cost=11 Card=1 Bytes=377)
   1    0   DELETE OF 'T_REFL_SEARCH_WORK_SESS'
   2    1     NESTED LOOPS (Cost=11 Card=1 Bytes=377)
   3    2       MERGE JOIN (CARTESIAN) (Cost=10 Card=1 Bytes=192)
   4    3         TABLE ACCESS (FULL) OF 'T_REFL_SEARCH_WORK_SESS' (Co
          st=2 Card=1 Bytes=7)
 
   5    3         BUFFER (SORT) (Cost=8 Card=1 Bytes=185)
   6    5           VIEW OF 'VW_SQ_1' (Cost=8 Card=1 Bytes=185)
   7    6             SORT (GROUP BY) (Cost=8 Card=1 Bytes=185)
   8    7               TABLE ACCESS (FULL) OF 'T_REFL_SEARCH_WORK_SES
          S' (Cost=2 Card=1 Bytes=185)
 
   9    2       TABLE ACCESS (BY USER ROWID) OF 'T_REFL_SEARCH_WORK_SE
          SS' (Cost=1)
 

in-list iterator in oracle 7

A reader, June 03, 2003 - 10:54 am UTC

Hi

is in-list iterator possible in oracle 7?

Tom Kyte
June 03, 2003 - 11:25 am UTC

I believe that access path was added in 8.0, so no. I cannot get it to use one but in 8.0 and up -- it uses it naturally.

why concatenation in this query

A reader, November 04, 2003 - 12:49 pm UTC

Hi

We recently rebuilt our database, using hash partitioning and facing heavy performance problems


SELECT *
FROM INVENTARIOS_SERVICIO_HOTEL
WHERE ISH_CODEMP = :b1
AND ISH_CODEMPPROREC = NVL(:b2,'00000')
AND ISH_CODREC = NVL(:b3,'000')
AND ISH_TIPCON = :b4
AND ISH_REFSER = :b5
AND ISH_TIPSER = :b6
AND ISH_FECINV >= TRUNC(to_date(:b7, 'MM/DD/YYYY hh24:mi:ss'))
AND ISH_FECINV <= TRUNC(to_date(:b8, 'MM/DD/YYYY hh24:mi:ss'))
AND INSTR(:b9,DIA_SEMANA(TRUNC(ISH_FECINV))) != 0
AND ISH_CONPRE = :b10
AND ISH_THAGEN = :b11
AND ISH_CHAGEN = :b12
AND ISH_RESCON = 0
AND ISH_RESPEN = 0

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=2 Bytes=184)
1 0 CONCATENATION
2 1 FILTER
3 2 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'INVENTARIOS_SERVICIO_HOTEL' (Cost=3 Card=1 Bytes=92)
4 3 INDEX (RANGE SCAN) OF 'ISH_PK' (UNIQUE) (Cost=6 Card=1)
5 1 FILTER
6 5 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'INVENTARIOS_SERVICIO_HOTEL' (Cost=3 Card=1 Bytes=92)
7 6 INDEX (RANGE SCAN) OF 'ISH_PK' (UNIQUE) (Cost=6 Card=1)


Extremely slow!

I dont understand why ther is concatenation, if I change

AND ISH_CODEMPPROREC = NVL(:b2,'00000') to
AND ISH_CODEMPPROREC = NVL('','00000') or
AND ISH_CODEMPPROREC = NVL('00000','00000')

Plan becomes

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=1 Bytes=92)
1 0 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'INVENTARIOS_SERVICIO_HOTEL' (Cost=7 Card=1 Bytes=9
2 1 INDEX (RANGE SCAN) OF 'ISH_PK' (UNIQUE) (Cost=6 Card=1)

Extremely fast!

I dont understand, I even used 10053 event but cant see why

Please help

Tom Kyte
November 04, 2003 - 5:22 pm UTC

well, given i have NO CLUE what your table, indexes, statistics, have no tkprof with the bad and the good, yadda yadda yadda look like, i guess we are in the same boat?

I have these statistics

A reader, November 04, 2003 - 6:00 pm UTC

Hi

Version 8.1.7.4 on Sun Solaris.

The table has 3500000 rows, hash partitioned in 8 partitions, the primary key is globally partitioned (was first locally partitioned but was even slower).

ISH_CODEMPPROREC is part of primary key (not the leading column).

I have following statistics, the funny thing is both query reads same amount of blocks but the one with bind variable is much much slower. With bind variable takes 9 minutes, using literals for ISH_CODEMPPROREC takes 30 seconds, dont understand why. The query runs fast when I get rid of NVL() function or use literals for ISH_CODEMPPROREC....

var b1 varchar2(5)
var b2 varchar2(5)
var b3 varchar2(3)
var b4 varchar2(1)
var b5 number
var b6 varchar2(10)
var b7 varchar2(30)
var b8 varchar2(30)
var b9 number
var b10 varchar2(10)
var b11 varchar2(4)
var b12 varchar2(14)

exec :b1 := 'VVT'
exec :b2 := '00000'
exec :b3 := '000'
exec :b4 := 'V'
exec :b5 := 1565
exec :b6 := 'HOTEL'
exec :b7 := '1/3/2003 0:0:0'
exec :b8 := '1/31/2003 0:0:0'
exec :b9 := 1234567
exec :b10 := 'GENERAL'
exec :b11 := 'A1/3'
exec :b12 := 'ES'

SELECT *
FROM INVENTARIOS_SERVICIO_HOTEL
WHERE ISH_CODEMP = :b1
AND ISH_CODEMPPROREC = NVL(:b2,'00000')
AND ISH_CODREC = NVL(:b3,'000')
AND ISH_TIPCON = :b4
AND ISH_REFSER = :b5
AND ISH_TIPSER = :b6
AND ISH_FECINV >= TRUNC(to_date(:b7, 'MM/DD/YYYY hh24:mi:ss'))
AND ISH_FECINV <= TRUNC(to_date(:b8, 'MM/DD/YYYY hh24:mi:ss'))
AND INSTR(:b9,DIA_SEMANA(TRUNC(ISH_FECINV))) != 0
AND ISH_CONPRE = :b10
AND ISH_THAGEN = :b11
AND ISH_CHAGEN = :b12
AND ISH_RESCON = 0
AND ISH_RESPEN = 0

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=2 Bytes=184)
1 0 CONCATENATION
2 1 FILTER
3 2 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'INVENTARIOS_SERVICIO_HOTEL' (Cost=3 Card=1 Bytes=92)
4 3 INDEX (RANGE SCAN) OF 'ISH_PK' (UNIQUE) (Cost=6 Card=1)
5 1 FILTER
6 5 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'INVENTARIOS_SERVICIO_HOTEL' (Cost=3 Card=1 Bytes=92)
7 6 INDEX (RANGE SCAN) OF 'ISH_PK' (UNIQUE) (Cost=6 Card=1)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1230 consistent gets
0 physical reads
0 redo size
859 bytes sent via SQL*Net to client
873 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
7 sorts (memory)
0 sorts (disk)
0 rows processed

SELECT *
FROM INVENTARIOS_SERVICIO_HOTEL
WHERE ISH_CODEMP = :b1
AND ISH_CODEMPPROREC = NVL('00000','00000')
AND ISH_CODREC = NVL(:b3,'000')
AND ISH_TIPCON = :b4
AND ISH_REFSER = :b5
AND ISH_TIPSER = :b6
AND ISH_FECINV >= TRUNC(to_date(:b7, 'MM/DD/YYYY hh24:mi:ss'))
AND ISH_FECINV <= TRUNC(to_date(:b8, 'MM/DD/YYYY hh24:mi:ss'))
AND INSTR(:b9,DIA_SEMANA(TRUNC(ISH_FECINV))) != 0
AND ISH_CONPRE = :b10
AND ISH_THAGEN = :b11
AND ISH_CHAGEN = :b12
AND ISH_RESCON = 0
AND ISH_RESPEN = 0

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=1 Bytes=92)
1 0 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'INVENTARIOS_SERVICIO_HOTEL' (Cost=7 Card=1 Bytes=9
2 1 INDEX (RANGE SCAN) OF 'ISH_PK' (UNIQUE) (Cost=6 Card=1)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1230 consistent gets
0 physical reads
0 redo size
858 bytes sent via SQL*Net to client
21 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

Tom Kyte
November 05, 2003 - 7:54 am UTC

sorry -- something else is afoot here, not that i don't believe you -- but -- well, i'm not believing something.

TKPROF please

I only have the bad query tkprof

A reader, November 05, 2003 - 8:20 am UTC

Hi

I only have the awful query tkprof... hope it can give you more information. The other one I dont have, but it was lightning fast. As you can see it´s in fact a DELETE but they work the same way, without bind variable or the NVL() very fast, with one of these two deadly slow !


DELETE
FROM INVENTARIOS_SERVICIO_HOTEL
WHERE ISH_CODEMP = :b1
AND ISH_CODEMPPROREC = NVL(:b2,'00000')
AND ISH_CODREC = NVL(:b3,'000')
AND ISH_TIPCON = :b4
AND ISH_REFSER = :b5
AND ISH_TIPSER = :b6
AND ISH_FECINV >= TRUNC(:b7)
AND ISH_FECINV <= TRUNC(:b8)
AND INSTR(:b9,DIA_SEMANA(TRUNC(ISH_FECINV))) != 0
AND ISH_CONPRE = :b10
AND ISH_THAGEN = :b11
AND ISH_CHAGEN = :b12
AND ISH_RESCON = 0
AND ISH_RESPEN = 0


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 5 452.73 478.34 0 17838 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 452.73 478.34 0 17838 0 0

Tom Kyte
November 05, 2003 - 9:43 am UTC

you are the same guy as above right?

can you give me what i asked for? a tkprof that compares the two. (query plans AND ALL)

all of a sudden, a SELECT is now a delete?

lets keep it consistent please. one problem at a time.

inlist-iterator

kuldeep, February 08, 2005 - 5:59 am UTC

Dear Tom, I am executing a query but it is strange for me that the presence of an index is
making the difference in result set of the same query.

In the absance of the index query using "inlist-iterator" is returning duplicate rows,
while when using index or using RBO query is not using "inlist-iterator" and returning
corrent result.


QUERY=1
=======
ns@dlfscg> ;
1 select a.cheque_number,a.cheque_date,a.cash_bank_gl,a.amount,a.amt_flag,b.gl_desc ,
2 decode(a.tax_perc , 1,'Checked',0,'Unchecked','Unchecked' ),c.sl_desc
3 from voucher_transaction a,gl_master b ,sl_master c
4 where a.cash_bank_gl = '1533'
5 and a.voucher_type in ('BRV','BPV')
6 and a.cheque_date between '01-JUN-2004' and '31-DEC-2004'
7 and nvl(a.tax_perc,0) like decode(9,9,'%',1,1,0,0)
8 and a.cash_bank_gl = b.gl_code
9 and a.gl_code = c.gl_code
10 and a.sl_code = c.sl_code
11* order by a.cash_bank_gl,a.cheque_number;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=207 Card=1 Bytes=75)
1 0 SORT (ORDER BY) (Cost=207 Card=1 Bytes=75)
2 1 FILTER
3 2 NESTED LOOPS (Cost=205 Card=1 Bytes=75)
4 3 NESTED LOOPS (Cost=204 Card=1 Bytes=48)
5 4 TABLE ACCESS (BY INDEX ROWID) OF 'GL_MASTER' (Cost=1 Card=1 Bytes=25)
6 5 INDEX (UNIQUE SCAN) OF 'PK_GL_CODE' (UNIQUE)
7 4 INLIST ITERATOR
8 7 TABLE ACCESS (BY INDEX ROWID) OF 'VOUCHER_TRANSACTION' (Cost=203 Card=1 Bytes=23)
9 8 INDEX (FULL SCAN) OF 'VT_IND' (NON-UNIQUE) (Cost=26 Card=82935)
10 3 TABLE ACCESS (BY INDEX ROWID) OF 'SL_MASTER' (Cost=1 Card=16758 Bytes=452466)
11 10 INDEX (UNIQUE SCAN) OF 'PK_GL_SL_CODE' (UNIQUE)


CHEQUE_NUMBER CHEQUE_DA CASH_BANK_GL AMOUNT A GL_DESC DECODE(A. SL_DESC
-------------------- --------- ------------ ---------- - ---------------------------------------- --------- ------------------------------
158836 05-JUN-04 1533 4071 D CITI BANK Unchecked NARESH KUMAR RAMPAL
158836 05-JUN-04 1533 4071 D CITI BANK Unchecked NARESH KUMAR RAMPAL
158838 05-JUN-04 1533 16730 D CITI BANK Checked RAM DEV
158838 05-JUN-04 1533 16730 D CITI BANK Checked RAM DEV
158839 05-JUN-04 1533 20093 D CITI BANK Checked 0.328 ACRE COMPLEX PH-III
158839 05-JUN-04 1533 20093 D CITI BANK Checked 0.328 ACRE COMPLEX PH-III
.
.
.

AFTER CREATE INDEX :

ns@dlfscg> CREATE INDEX TEMP ON voucher_transaction(cash_bank_gl);

Index created.

QUERY=3
=======
ns@dlfscg>

1 select a.cheque_number,a.cheque_date,a.cash_bank_gl,a.amount,a.amt_flag,b.gl_desc ,
2 decode(a.tax_perc , 1,'Checked',0,'Unchecked','Unchecked' ),c.sl_desc
3 from voucher_transaction a,gl_master b ,sl_master c
4 where a.cash_bank_gl = '1533'
5 and a.voucher_type in ('BRV','BPV')
6 and a.cheque_date between '01-JUN-2004' and '31-DEC-2004'
7 and nvl(a.tax_perc,0) like decode(9,9,'%',1,1,0,0)
8 and a.cash_bank_gl = b.gl_code
9 and a.gl_code = c.gl_code
10 and a.sl_code = c.sl_code
11* order by a.cash_bank_gl,a.cheque_number;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=24 Card=1 Bytes=75)
1 0 SORT (ORDER BY) (Cost=24 Card=1 Bytes=75)
2 1 FILTER
3 2 NESTED LOOPS (Cost=22 Card=1 Bytes=75)
4 3 NESTED LOOPS (Cost=21 Card=1 Bytes=48)
5 4 TABLE ACCESS (BY INDEX ROWID) OF 'GL_MASTER' (Cost=1 Card=1 Bytes=25)
6 5 INDEX (UNIQUE SCAN) OF 'PK_GL_CODE' (UNIQUE)
7 4 TABLE ACCESS (BY INDEX ROWID) OF 'VOUCHER_TRANSACTION' (Cost=20 Card=1 Bytes=23)
8 7 INDEX (RANGE SCAN) OF 'TEMP' (NON-UNIQUE) (Cost=1 Card=8630)
9 3 TABLE ACCESS (BY INDEX ROWID) OF 'SL_MASTER' (Cost=1 Card=16758 Bytes=452466)
10 9 INDEX (UNIQUE SCAN) OF 'PK_GL_SL_CODE' (UNIQUE)


CHEQUE_NUMBER CHEQUE_DA CASH_BANK_GL AMOUNT A GL_DESC DECODE(A. SL_DESC
-------------------- --------- ------------ ---------- - ---------------------------------------- --------- ------------------------------
158836 05-JUN-04 1533 4071 D CITI BANK Unchecked NARESH KUMAR RAMPAL
158838 05-JUN-04 1533 16730 D CITI BANK Checked RAM DEV
158839 05-JUN-04 1533 20093 D CITI BANK Checked 0.328 ACRE COMPLEX PH-III
.
.
.


QUERY=3
=======
SELECT /*+ RULE */a.cheque_number,a.cheque_date,a.cash_bank_gl,a.amount,a.amt_flag,b.gl_desc ,
DECODE(a.tax_perc , 1,'Checked',0,'Unchecked','Unchecked' ),c.sl_desc
FROM voucher_transaction a,gl_master b ,sl_master c
WHERE a.cash_bank_gl = '1533'
AND a.voucher_type IN ('BRV','BPV')
AND a.cheque_date BETWEEN '01-JUN-2004' AND '31-DEC-2004'
AND NVL(a.tax_perc,0) LIKE DECODE(9,9,'%',1,1,0,0)
AND a.cash_bank_gl = b.gl_code
AND a.gl_code = c.gl_code
AND a.sl_code = c.sl_code
ORDER BY a.cash_bank_gl,a.cheque_number;

THIS QUERY 3 IS RETURNING THE SAME RESULT AS QUERY 2.

Regards,

Tom Kyte
February 08, 2005 - 6:49 am UTC

the presence or lack thereof of an index should not alter the result set and if it does, that is call for "please contact support"

But what, exactly, is an *INLIST ITERATOR* ?

Richard, May 14, 2005 - 9:57 am UTC

What is an INLIST ITERATOR? I see it often when using local indexes with partitioned tables.

Tom Kyte
May 14, 2005 - 10:02 am UTC

It is a more compact looping construct than "concatenation" that is available to the CBO.

It "iterates over a set of IN-LIST values", instead of concatenating a bunch of index probes.  It is a loop in the plan.

Consider the RBO vs CBO given the same query:

ops$tkyte@ORA9IR2> create table t ( x int, y int );
Table created.
 
ops$tkyte@ORA9IR2> create index t_idx on t(x);
Index created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> set autotrace traceonly explain
ops$tkyte@ORA9IR2> select * from t where x in ( 1, 2, 3 );
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   CONCATENATION
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'T'
   3    2       INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE)
   4    1     TABLE ACCESS (BY INDEX ROWID) OF 'T'
   5    4       INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE)
   6    1     TABLE ACCESS (BY INDEX ROWID) OF 'T'
   7    6       INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE)
 
 
 
ops$tkyte@ORA9IR2> select /*+ FIRST_ROWS */ * from t where x in ( 1, 2, 3 );
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=1 Card=1 Bytes=26)
   1    0   INLIST ITERATOR
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=1 Card=1 Bytes=26)
   3    2       INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=2 Card=1)
 
 
 
ops$tkyte@ORA9IR2> set autotrace off


The inlist iterator is a much more efficient way to represent that plan (and results in considerably smaller query plans in the shared pool by far) 

Thanks!

Richard, May 15, 2005 - 7:19 am UTC


One more kills the query!

A reader, April 17, 2007 - 10:51 pm UTC

Oracle 9.2.0.6, CBO

I have a query with a simple predicate like
and substr(some_col,1,1) in ('A','B')


This performs fine.

Changing *nothing* else, if I simply change the predicate to
and substr(some_col,1,1) in ('A','B','C')


the query tanks! Takes 10 times longer. All tables are analyzed (using dbms_stats), no histograms on any of the tables.

If I rewrite the predicate to
and instr(',A,B,C,',','||substr(some_col,1,1)||',') > 0
it again works fine.

Any idea why?
Tom Kyte
April 18, 2007 - 12:01 pm UTC

what are the estimated card= values, do they change radically.

how to force INLIST ITERATOR over CONCATENATION?

George, August 06, 2008 - 5:06 pm UTC

Hi Tom,

Is there a way to force optimizer to use INLIST ITERATOR over CONCATENATION?

Below is the reason I'm asking this.

SQL> explain plan for
  2  select x.DOC_ID
  3    from DOC_KEYWORD x
  4   where (x.KEYWORD_NAME_TX, lower(x.KEYWORD_VALUE_TX)) in
  5         ( ('24','ab4320001417')
  6          ,('9','cd4320001417'));

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------
| Id  | Operation            |  Name              | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                    |   768 | 38400 |  1311   (1)|
|   1 |  INLIST ITERATOR     |                    |       |       |            |
|*  2 |   INDEX RANGE SCAN   | DOC_KEYWORD_IDX02  |   768 | 38400 |  1311   (1)|
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("X"."KEYWORD_NAME_TX"='24' OR "X"."KEYWORD_NAME_TX"='9')
       filter(LOWER("X"."KEYWORD_VALUE_TX")='4320001417')

14 rows selected.

SQL> 

So, the explain plan shows INLIST ITERATOR - that's what I need. The tkprof, however, shows what's happening indeed:

select x.DOC_ID
  from DOC_KEYWORD x
 where (x.KEYWORD_NAME_TX, lower(x.KEYWORD_VALUE_TX)) in
       ( (:"SYS_B_0",:"SYS_B_1")
        ,(:"SYS_B_2",:"SYS_B_3"))

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      6.08       5.97          0      77167          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      6.08       5.97          0      77167          0           0

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 332

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  CONCATENATION  (cr=77167 r=0 w=0 time=5975380 us)
      0   INDEX RANGE SCAN DOC_KEYWORD_IDX02 (cr=27309 r=0 w=0 time=2593856 us)(object id 115138)
      0   INDEX RANGE SCAN DOC_KEYWORD_IDX02 (cr=49858 r=0 w=0 time=3381474 us)(object id 115138)

So, it's double the work. Actually, I've got 18 pairs of values in the IN list and it's becoming wery slow and very expensive.

So the question is: Is there a way to force optimizer to use INLIST ITERATOR over CONCATENATION?

PS. Index definition: 
    CREATE INDEX DOC_KEYWORD_IDX02 ON DOC_KEYWORD (KEYWORD_NAME_TX, KEYWORD_VALUE_TX, DOC_ID);

As you can see I'm using lower() in the query, but the index is not function based.
If I remove the lower() from the query it does indeed use INLIST OPERATOR in the plan and obviously it's cheaper and faster. 
Can lower() be the reason why optimizer refuses to use the INLIST and uses CONCATENATION instead? 
And even if that's so, can we somehow force it to go for INLIST?

PPS. I tried to use NO_EXPAND hint but instead of CONCATENATION optimizer now uses 
     BITMAP CONVERSION TO ROWIDS/BITMAP OR/BITMAP CONVERSION FROM ROWIDS, still accessing 
     the index multiple times.

Thank you.

Tom Kyte
August 07, 2008 - 12:21 pm UTC

do you know what an inlist iterator does?

it is just an abbreviation for "range scan over and over and concatenate"

it iterates over the inlist items, does the range scan for each and concatenates the results and returns them.

I see no "double work" here - you have

where (a,b) in ((l,m),(n,o))

it has to range scan twice. think about it - you start once with 24 and then with 9 - TWO scans. And if you have 18 inputs - 18 scans.


Now, that said, I would expect:
Rows     Row Source Operation
-------  ---------------------------------------------------
      0  CONCATENATION  (cr=77167 r=0 w=0 time=5975380 us)
      0   INDEX RANGE SCAN DOC_KEYWORD_IDX02 (cr=27309 r=0 w=0 time=2593856 
us)(object id 115138)
      0   INDEX RANGE SCAN DOC_KEYWORD_IDX02 (cr=49858 r=0 w=0 time=3381474 
us)(object id 115138)



that the cr= values would be 3 or maybe 4 - indicating your index is not correctly created here. If you get zero rows, it should just do "root,branch,leaf - STOP"

So, you must have an index on just one of the two things - create the index on:

x.KEYWORD_NAME_TX, lower(x.KEYWORD_VALUE_TX), doc_id)

You use the words "obviously", and yes it is obvious - but only because your index is totally wrong - it indexes the data in whatever case it is in. You search for it in LOWER CASE. Hence, you might as well just have an index on keyword_value_tx as you have it now - that is all the index can be used for - you have lots of 24's and 9's in there and we are range scanning like mad looking at all of them.

forget the "inlist" stuff for a moment, it won't matter - concatenate + many range scans = inlist iterator - they are the same (inlist interator is just a more "compact" way of saying it - else - they are the SAME when executed, each will hit the index N times for a range scan)


USE binds, you have a big nasty, ugly security hole in your application called sql injection - that you have to use cursor_sharing = force or similar is really bad - you can be sql injected - you get this input from end users probably and then can do things to your sql you did not intend





but your solution is "index correctly". Consider:

ops$tkyte%ORA10GR2> create table t ( keyword_name_tx varchar2(10), keyword_value_tx varchar2(1000) , doc_id number );

Table created.

ops$tkyte%ORA10GR2> insert into t select '24', rpad('*',1000,'*'), object_id from all_objects where rownum <= 5000;

5000 rows created.

ops$tkyte%ORA10GR2> insert into t select '9', rpad('*',1000,'*'), object_id  from all_objects where rownum <= 5000;

5000 rows created.

ops$tkyte%ORA10GR2> insert into t select rownum, 'x', object_id  from all_objects;

49830 rows created.

ops$tkyte%ORA10GR2> exec dbms_stats.gather_table_stats( user, 'T' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> variable b1 varchar2(20)
ops$tkyte%ORA10GR2> variable b2 varchar2(20)
ops$tkyte%ORA10GR2> variable b3 varchar2(20)
ops$tkyte%ORA10GR2> variable b4 varchar2(20)
ops$tkyte%ORA10GR2> exec :b1 := '24'; :b2 := 'ab4320001417'; :b3 := '9'; :b4 := 'cd4320001417';

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> drop index t_idx;
drop index t_idx
           *
ERROR at line 1:
ORA-01418: specified index does not exist


ops$tkyte%ORA10GR2> create index t_idx on t(keyword_name_tx,keyword_value_tx,doc_id);

Index created.

ops$tkyte%ORA10GR2> @trace
ops$tkyte%ORA10GR2> alter session set events '10046 trace name context forever, level 12';

Session altered.

ops$tkyte%ORA10GR2> select /* bad index */ x.DOC_ID from T x where (x.KEYWORD_NAME_TX, lower(x.KEYWORD_VALUE_TX)) in ( (:"B1",:"B2") ,(:"B3",:"B4"));

no rows selected

ops$tkyte%ORA10GR2> drop index t_idx;

Index dropped.

ops$tkyte%ORA10GR2> create index t_idx on t(keyword_name_tx,LOWER(keyword_value_tx),doc_id);

Index created.

ops$tkyte%ORA10GR2> select /* good index */ x.DOC_ID from T x where (x.KEYWORD_NAME_TX, lower(x.KEYWORD_VALUE_TX)) in ( (:"B1",:"B2") ,(:"B3",:"B4"));

no rows selected



select /* bad index */ x.DOC_ID from
 T x where (x.KEYWORD_NAME_TX, lower(x.KEYWORD_VALUE_TX)) in ( (:"B1",:"B2") ,
  (:"B3",:"B4"))

<b>
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.10       0.10       1436       1438          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.11       0.11       1436       1438          0           0
</b>
Rows     Row Source Operation
-------  ---------------------------------------------------
      0  CONCATENATION  (cr=1438 pr=1436 pw=0 time=107455 us)
      0   INDEX RANGE SCAN T_IDX (cr=719 pr=718 pw=0 time=59509 us)(object id 162266)
      0   INDEX RANGE SCAN T_IDX (cr=719 pr=718 pw=0 time=47932 us)(object id 162266)
********************************************************************************
select /* good index */ x.DOC_ID from
 T x where (x.KEYWORD_NAME_TX, lower(x.KEYWORD_VALUE_TX)) in ( (:"B1",:"B2") ,
  (:"B3",:"B4"))

<b>
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          8         10          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          8         10          0           0
</b>
Rows     Row Source Operation
-------  ---------------------------------------------------
      0  INLIST ITERATOR  (cr=10 pr=8 pw=0 time=574 us)
      0   INDEX RANGE SCAN T_IDX (cr=10 pr=8 pw=0 time=541 us)(object id 162267)



I want to stress *it is not concatenate vs inlist iterate* that makes a difference here.

The difference is that the first one can only compare keyword_name_tx to the index value - it has to look at EVERY SINGLE 24 and 9 value - all 10,002 of them.

the second one navigates the index and figures out using keyword_name_tx AND keyword_value_tx in a range scan that - there is nothing to see here - it does NOT look at and lowercase 10,002 keyword_value_tx values.

INLIST ITERATOR

A reader, November 30, 2011 - 4:36 am UTC

Hi Tom, 

I am having production table MOF.MOF_COMMON_OBJECTS  with 2 million rows and I have unique index on CO_DN column. 

When I use WHERE CO_DN  IN ( :1, :2, :3 ), plan is generating as "TABLE ACCESS FULL"

But when is use WHERE CO_DN  = :1, plan is generating "INDEX UNIQUE SCAN"

Please find auto trace for the same: 
[omc@zielios ~]$ sqlplus omc/omc

SQL*Plus: Release 11.2.0.2.0 Production on Wed Nov 30 12:11:16 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> SELECT  MOF.CO_GID, MOF.CO_DN, MOF.CO_OCV_SYS_VERSION
FROM MOF.MOF_COMMON_OBJECTS MOF
WHERE  CO_DN = 'NSNNetwork-1/ONENDS-0/DIR-0/DSA-10/DIRN-297'
ORDER BY MOF.CO_DN;  

Execution Plan
----------------------------------------------------------
Plan hash value: 439528632

------------------------------------------------------------
| Id  | Operation                   | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------
|   0 | SELECT STATEMENT            |                      |     1 |    56 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| MOF_COMMON_OBJECTS   |     1 |    56 |     3   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | MOF_I_MCO_CO_DN_ONLY |     1 |       |     2   (0)| 00:00:01 |
------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CO_DN"='NSNNetwork-1/ONENDS-0/DIR-0/DSA-10/DIRN-297')
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        730  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
    

Test  SQL with IN clause :

 SELECT  MOF.CO_GID, MOF.CO_DN, MOF.CO_OCV_SYS_VERSION
    FROM MOF.MOF_COMMON_OBJECTS MOF
    WHERE  CO_DN IN ('NSNNetwork-1/ONENDS-0/DIR-0/DSA-10/DIRN-297' ,'NSNNetwork-1/ONENDS-0/DIR-0/DSA-10/DIRN-296' ,
'NSNNetwork-1/ONENDS-0/DIR-0/DSA-10/DIRN-295')
   ORDER BY MOF.CO_DN;

 
Execution Plan
----------------------------------------------------------
Plan hash value: 70471207

------------------------------------------------------------
| Id  | Operation          | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------
|   0 | SELECT STATEMENT   |                    |   119K|  6513K|       |  4153   (3)| 00:00:38 |
|   1 |  SORT ORDER BY     |                    |   119K|  6513K|  7952K|  4153   (3)| 00:00:38 |
|*  2 |   TABLE ACCESS FULL| MOF_COMMON_OBJECTS |   119K|  6513K|       |  2811   (4)| 00:00:26 |
------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("CO_DN"='NSNNetwork-1/ONENDS-0/DIR-0/DSA-10/DIRN-295' OR
              "CO_DN"='NSNNetwork-1/ONENDS-0/DIR-0/DSA-10/DIRN-296' OR
              "CO_DN"='NSNNetwork-1/ONENDS-0/DIR-0/DSA-10/DIRN-297')


Statistics
----------------------------------------------------------
          1  recursive calls
          1  db block gets
      43521  consistent gets
      43505  physical reads
          0  redo size
        902  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          3  rows processed


But  when I try to  create simillare table and test in  both the case getting  "INDEX RANGE SCAN".
How can I find what is wrong in my production table 

DROP table OMC.T;
 CREATE TABLE OMC.T ( 
  NAME VARCHAR(300),
  ID NUMBER(10),
  prop VARCHAR(200)
);

INSERT INTO OMC.T SELECT OWNER||'\'||OBJECT_NAME||'\'||OBJECT_TYPE||ROWNUM, ROWNUM, OBJECT_NAME 
FROM ALL_OBJECTS ;
COMMIT;

create unique index omc.Ti on  omc.t (NAME,ID) ;

EXEC DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME => 'OMC', ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, GRANULARITY => 'ALL', CASCADE => TRUE, OPTIONS => 'GATHER', DEGREE => 4);

SELECT * FROM  OMC.T WHERE NAME = 'SYS\I_SNAP_SITE1\INDEX617' ORDER BY NAME;

SELECT * FROM  OMC.T WHERE NAME IN (  'SYS\I_SNAP_SITE1\INDEX617', 'PUBLIC\GV$BACKUP_DEVICE\SYNONYM2286','SYS\GV_$RSRC_PLAN\VIEW2382')
ORDER BY NAME;

SELECT * FROM  OMC.T WHERE NAME = 'SYS\I_SNAP_SITE1\INDEX617';

Autotrace for my sample table:


Execution Plan
----------------------------------------------------------
Plan hash value: 3686052240

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |     1 |    78 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T    |     1 |    78 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TI   |     1 |       |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("NAME"='SYS\I_SNAP_SITE1\INDEX617')

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        696  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
    
    
 SQL> SELECT * FROM  OMC.T WHERE NAME IN (  'SYS\I_SNAP_SITE1\INDEX617', 'PUBLIC\GV$BACKUP_DEVICE\SYNONYM2286','SYS\GV_$RSRC_PLAN\VIEW2382')
ORDER BY NAME; 


Execution Plan
----------------------------------------------------------
Plan hash value: 1348465988

-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |     3 |   234 |     5   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR             |      |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T    |     3 |   234 |     5   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | TI   |     3 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("NAME"='PUBLIC\GV$BACKUP_DEVICE\SYNONYM2286' OR
              "NAME"='SYS\GV_$RSRC_PLAN\VIEW2382' OR "NAME"='SYS\I_SNAP_SITE1\INDEX617')

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         12  consistent gets
          3  physical reads
          0  redo size
        858  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed


can you help me to find why my production table is not doing INDEX RANGE SCAN ? 

Tom Kyte
November 30, 2011 - 7:52 am UTC

I cannot reproduce - HOW are you gathering statistics - exactly and precisely how. with a unique index, I get the right cardinality estimates.

I suspect statistics are in play somehow here.

Here is my guess, looking at your strings:


filter("CO_DN"='NSNNetwork-1/ONENDS-0/DIR-0/DSA-10/DIRN-295' OR
       "CO_DN"='NSNNetwork-1/ONENDS-0/DIR-0/DSA-10/DIRN-296' OR
       "CO_DN"='NSNNetwork-1/ONENDS-0/DIR-0/DSA-10/DIRN-297')


the first 40 characters are IDENTICAL. Now, we only use 15-32 characters, LEADING characters, in our statistics. Things that have the same leading characters look *the same* to us.


read:
http://www.oracle.com/technetwork/issue-archive/2011/11-jan/o11asktom-195084.html


So, I'm guessing - no there is no a unqiue constraint, the database only sees the leading characters. Based on the estimated cardinality of 119k rows retrieved, it sees only 50 UNIQUE values in the table.


Now, that said, what can you do?

a) create a function based index on REVERSE(column) and look for

where reverse(column) in ( reverse(:b1), reverse(:b2), ... );


put the stuff that changes at the BEGINNING of the string


b) store this field as separate fields since it looks like you are stuff many attributes into a single field here.




I see this problem all of the time with URLS for example - people have a table with urls:

http://the.same.hostname.over.and.over:80/path/that/is/used/alot/interesting.html


and they are looking for interesting.html - and finding the optimizer cannot estimate the cardinality correctly.


The solution: break that into

host, port, path, file

and store the four components... Makes the data VERY compressable as well (where it would not be if it were stored as a single string)

DBMS_STATS.AUTO_SAMPLE_SIZE

A reader, November 30, 2011 - 12:47 pm UTC

Hi Tom,

I did  following things 
Create OMC.MOF2  table , data for MOF2 is filled from my production table.

Tets 1 :  GATHER_SCHEMA_STATS with ESTIMATE_PERCENT => 10   this is what used in  predication system
Tets 2 :  GATHER_SCHEMA_STATS with ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE

In Test2  Plan says Index is well followed  & Note    - dynamic sampling used for this statement (level=2)

ESTIMATE_PERCENT => 10  is wrong ?  Can you explain me what happening here 

Tets : 

DROP TABLE OMC.MOF2;
CREATE TABLE OMC.MOF2 ( 
  
  CO_GID NUMBER NOT NULL,
  CO_DN VARCHAR(400),
  CO_NAME VARCHAR(200)
);

INSERT INTO OMC.MOF2 SELECT CO_GID,CO_DN, CO_NAME 
FROM MOF.MOF_COMMON_OBJECTS ; -- data from production table 
COMMIT;

CREATE UNIQUE INDEX OMC.CO_DN_IDX ON OMC.MOF2 (CO_DN,CO_GID) ;

Tets 1 :  GATHER_SCHEMA_STATS with ESTIMATE_PERCENT => 10   this is what used in  prducation system

EXEC DBMS_STATS.DELETE_SCHEMA_STATS(OWNNAME => 'OMC' );
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME => 'OMC', ESTIMATE_PERCENT => 10,  GRANULARITY => 'ALL', CASCADE => TRUE, OPTIONS => 'GATHER', DEGREE => 4);


SQL> SELECT * FROM  OMC.MOF2 WHERE CO_DN = 'NSNNetwork-1/ONENDS-0/DIR-0/DSA-10/DIRN-297' ORDER BY CO_DN;

Execution Plan
----------------------------------------------------------
Plan hash value: 3193933395

------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 39594 |  1855K|   985   (5)| 00:00:09 |
|*  1 |  TABLE ACCESS FULL| MOF2 | 39594 |  1855K|   985   (5)| 00:00:09 |
-------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("CO_DN"='NSNNetwork-1/ONENDS-0/DIR-0/DSA-10/DIRN-297')

SQL> SELECT * FROM  OMC.MOF2 WHERE CO_DN IN ('NSNNetwork-1/ONENDS-0/DIR-0/DSA-10/DIRN-297' ,'NSNNetwork-1/ONENDS-0/DIR-0/DSA-10/DIRN-296' ,'NSNNetwork-1/ONENDS-0/DIR-0/DSA-10/DIRN-295')
ORDER BY CO_DN;  2


Execution Plan
----------------------------------------------------------
Plan hash value: 3306333252

------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   118K|  5567K|       |  2174   (4)| 00:00:20 |
|   1 |  SORT ORDER BY     |      |   118K|  5567K|  7000K|  2174   (4)| 00:00:20 |
|*  2 |   TABLE ACCESS FULL| MOF2 |   118K|  5567K|       |  1001   (7)| 00:00:09 |
---------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("CO_DN"='NSNNetwork-1/ONENDS-0/DIR-0/DSA-10/DIRN-295' OR
              "CO_DN"='NSNNetwork-1/ONENDS-0/DIR-0/DSA-10/DIRN-296' OR
              "CO_DN"='NSNNetwork-1/ONENDS-0/DIR-0/DSA-10/DIRN-297')


Tets 2 :  GATHER_SCHEMA_STATS with ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE

EXEC DBMS_STATS.DELETE_SCHEMA_STATS(OWNNAME => 'OMC' );
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME => 'MOF', ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, GRANULARITY => 'ALL', CASCADE => TRUE, OPTIONS => 'GATHER', DEGREE => 4);


SQL> SELECT * FROM  OMC.MOF2 WHERE CO_DN = 'NSNNetwork-1/ONENDS-0/DIR-0/DSA-10/DIRN-297' ORDER BY CO_DN;


Execution Plan
----------------------------------------------------------
Plan hash value: 1864916439

--------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |   317 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| MOF2      |     1 |   317 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | CO_DN_IDX |     1 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CO_DN"='NSNNetwork-1/ONENDS-0/DIR-0/DSA-10/DIRN-297')

Note
-----
   - dynamic sampling used for this statement (level=2)


SQL> SELECT * FROM  OMC.MOF2 WHERE CO_DN IN ('NSNNetwork-1/ONENDS-0/DIR-0/DSA-10/DIRN-297' ,'NSNNetwork-1/ONENDS-0/DIR-0/DSA-10/DIRN-296' ,'NSNNetwork-1/ONENDS-0/DIR-0/DSA-10/DIRN-295')
ORDER BY CO_DN;

Execution Plan
----------------------------------------------------------
Plan hash value: 2462475929

-------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |   167 | 52939 |     6   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR             |           |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| MOF2      |   167 | 52939 |     6   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | CO_DN_IDX |  9052 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("CO_DN"='NSNNetwork-1/ONENDS-0/DIR-0/DSA-10/DIRN-295' OR
              "CO_DN"='NSNNetwork-1/ONENDS-0/DIR-0/DSA-10/DIRN-296' OR
              "CO_DN"='NSNNetwork-1/ONENDS-0/DIR-0/DSA-10/DIRN-297')

Note
-----
   - dynamic sampling used for this statement (level=2)

Tom Kyte
December 01, 2011 - 7:33 am UTC

In 11g, I would recommend *never* using estimate_percent.

There are new, massively improved, much faster routines in dbms_stats - that only work if you *use the defaults*.


I would suggest just gathering stats with username, table_name, and degree of parallel at most.

also, for partitioned tables, enable incremental statistics and use that - that way you ONLY gather stats on *partitions* that have changed - never global stats.

DBMS_STATS.AUTO_SAMPLE_SIZE

A reader, November 30, 2011 - 10:56 pm UTC

Sorry i made a mistake in Test 2, please ignore my update.

EXEC DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME => 'MOF', ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, GRANULARITY => 'ALL', CASCADE => TRUE, OPTIONS => 'GATHER', DEGREE => 4);

OWNNAME => 'MOF' this should be OMC

INLIST ITERATOR

A reader, December 01, 2011 - 3:45 am UTC

Hi Tom,

When I used  hint  /*+ index( MOF , MOF_I_MCO_CO_DN_ONLY )  */ cost is high compare to full table scan.

Cost for TABLE ACCESS FULL : 4798
Cost for TABLE ACCESS BY INDEX ROWID| : 19471

Could this be the reason for doing Full table scan ?  
Why index scan is costly.

SQL> SELECT  MOF.CO_GID, MOF.CO_DN, MOF.CO_OCV_SYS_VERSION
  2      FROM MOF.MOF_COMMON_OBJECTS MOF
  3      WHERE  CO_DN IN ('NSNNetwork-1/ONENDS-0/DIR-0/DSA-10/DIRN-297'
  4  ,'NSNNetwork-1/ONENDS-0/DIR-0/DSA-10/DIRN-296' ,
  5  'NSNNetwork-1/ONENDS-0/DIR-0/DSA-10/DIRN-295')
  6     ORDER BY MOF.CO_DN;

Execution Plan
----------------------------------------------------------
Plan hash value: 70471207

------------------------------------------------------------------
| Id  | Operation          | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                    |   119K|  6513K|       |  4798   (4)| 00:00:28 |
|   1 |  SORT ORDER BY     |                    |   119K|  6513K|  7952K|  4798   (4)| 00:00:28 |
|*  2 |   TABLE ACCESS FULL| MOF_COMMON_OBJECTS |   119K|  6513K|       |  3390   (5)| 00:00:20 |
------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("CO_DN"='NSNNetwork-1/ONENDS-0/DIR-0/DSA-10/DIRN-295' OR
              "CO_DN"='NSNNetwork-1/ONENDS-0/DIR-0/DSA-10/DIRN-296' OR
              "CO_DN"='NSNNetwork-1/ONENDS-0/DIR-0/DSA-10/DIRN-297')
Statistics
----------------------------------------------------------
          0  recursive calls
          1  db block gets
      43530  consistent gets
       3823  physical reads
          0  redo size
        902  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          3  rows processed

----------------------------------------------------------
Index Hint:
    
SQL> SELECT  /*+ index( MOF , MOF_I_MCO_CO_DN_ONLY )  */  MOF.CO_GID, MOF.CO_DN, MOF.CO_OCV_SYS_VERSION
  2      FROM MOF.MOF_COMMON_OBJECTS MOF
    WHERE  CO_DN IN ('NSNNetwork-1/ONENDS-0/DIR-0/DSA-10/DIRN-297'
  3    4  ,'NSNNetwork-1/ONENDS-0/DIR-0/DSA-10/DIRN-296' ,
  5  'NSNNetwork-1/ONENDS-0/DIR-0/DSA-10/DIRN-295')
  6     ORDER BY MOF.CO_DN;


Execution Plan
----------------------------------------------------------
Plan hash value: 1433229166

------------------------------------------------------------------
| Id  | Operation                    | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------
|   0 | SELECT STATEMENT             |                      |   119K|  6513K| 19471   (1)| 00:01:51 |
|   1 |  INLIST ITERATOR             |                      |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| MOF_COMMON_OBJECTS   |   119K|  6513K| 19471   (1)| 00:01:51 |
|*  3 |    INDEX UNIQUE SCAN         | MOF_I_MCO_CO_DN_ONLY |   120K|       |    84   (4)| 00:00:01 |
-----------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("CO_DN"='NSNNetwork-1/ONENDS-0/DIR-0/DSA-10/DIRN-295' OR
              "CO_DN"='NSNNetwork-1/ONENDS-0/DIR-0/DSA-10/DIRN-296' OR
              "CO_DN"='NSNNetwork-1/ONENDS-0/DIR-0/DSA-10/DIRN-297')
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         10  consistent gets
          0  physical reads
          0  redo size
        902  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed 

Tom Kyte
December 06, 2011 - 9:58 am UTC

the *reason* is the estimated cardinality - that is the reason. When you have those strings with repeated values at the beginning - as I stated above and linked to an article with more details on - the optimizer only sees the first part of the string.

please see my original response - reverse, putting the various attributes you have hidden in the string into their own fields...

INLIST ITERATOR

A reader, December 01, 2011 - 6:20 am UTC

Hi Tom,

sorry i am filling this page ;-(

I did following test,

1. when rpad('x',15,'x') TABLE ACCESS BY INDEX ROWID
2. when rpad('x',50,'x') TABLE ACCESS FULL

Why optimizer is ignoring unique index omc.t_ind_01 when there is 50+ Char ? Should stats gather should be improved for such a table.


Test :

drop table omc.t ;
create table omc.t
nologging
as
select mod(rownum,10) as x,
SYSDATE AS Y,
rpad('x',15,'x')||rownum as z,
A.*
FROM ALL_OBJECTS A , ALL_OBJECTS B where rownum < 1000000;

create unique index omc.t_ind_01 on omc.t(z) nologging;

EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'OMC', tabname=>'T',ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt=>'for all indexed columns size 254');

select * FROM omc.T where z in ( 'xxxxxxxxxx2xxxxxxxxx','xxxxxxxxxx3xxxxxxxxx''xxxxxxxxxx32xxxxxxxxx');

INLIST ITERATOR in execution plan

Paul, September 13, 2021 - 5:58 am UTC

Greetings!

I am noticing slow execution of query (query with in condition up-to 1000 values, this is developer query) in Dev database, this query takes 40+ seconds whereas same query executes in less than a second in UAT database. Volume and stats are almost same.

Both database are on Oracle 12.1.0.2.

Attaching the execution plan from 10046 trace below.

As you can see, in Dev, the execution plan is not using "INLIST ITERATOR" whereas in UAT, the execution plan is using "INLIST ITERATOR" .

Any reason, why Oracle is not doing "INLIST ITERATOR" in Dev ?

I did copy stats from UAT to Dev for the tables involved, its still no change in the plan.

Can you please suggest what could be the issue.

Note that query is running against views of views under the cover.

==

SELECT "DataItem"."CURRENT_RRI" AS "DataItem_CURRENT_RRI"
FROM "MSGBT"."RSKBYMSGORG_ID" "DataItem"
WHERE "DataItem"."ORG_ID" IN (22162968,7925424,8015948,22193263,55783513,7922947,7924894,7970117,8564782,8074629,7925061,8074669,8030228,8192997,8075042,.......)


Slow (Dev):
===========

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.11 0.11 0 89 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 34 39.93 40.07 205 4212512 0 485
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 36 40.05 40.19 205 4212601 0 485

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 132
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
485 485 485 VIEW RSKBYMSGORG_ID (cr=4212512 pr=205 pw=0 time=40077833 us cost=557 size=13 card=1)
485 485 485 HASH UNIQUE (cr=4212512 pr=205 pw=0 time=40077763 us cost=557 size=142 card=1)
485 485 485 NESTED LOOPS OUTER (cr=4212512 pr=205 pw=0 time=40071448 us cost=556 size=142 card=1)
485 485 485 NESTED LOOPS (cr=31743 pr=0 pw=0 time=500532 us cost=428 size=29 card=1)
485 485 485 TABLE ACCESS STORAGE FULL ORG_XREF (cr=30772 pr=0 pw=0 time=494842 us cost=427 size=22 card=1)
485 485 485 INDEX UNIQUE SCAN ORG_MAST_PK (cr=971 pr=0 pw=0 time=4878 us cost=1 size=7 card=1)(object id 1097436)
339 339 339 VIEW PUSHED PREDICATE RISK_MET_LATEST (cr=4180769 pr=205 pw=0 time=39569833 us cost=128 size=113 card=1)
339 339 339 NESTED LOOPS (cr=4180769 pr=205 pw=0 time=39566074 us cost=128 size=98 card=1)
339 339 339 NESTED LOOPS (cr=4180443 pr=205 pw=0 time=39562886 us cost=128 size=98 card=1)
339 339 339 VIEW (cr=4179246 pr=0 pw=0 time=39398914 us cost=124 size=26 card=1)
339 339 339 SORT GROUP BY (cr=4179246 pr=0 pw=0 time=39398118 us cost=124 size=29 card=1)
560 560 560 TABLE ACCESS STORAGE FULL RISK_MET (cr=4179246 pr=0 pw=0 time=39387712 us cost=124 size=145 card=5)
339 339 339 INDEX RANGE SCAN RISK_MET_PK (cr=1197 pr=205 pw=0 time=162407 us cost=3 size=0 card=1)(object id 10748126)
339 339 339 TABLE ACCESS BY INDEX ROWID RISK_MET (cr=326 pr=0 pw=0 time=2201 us cost=4 size=72 card=1)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
Disk file operations I/O 158 0.00 0.00
SQL*Net message to client 34 0.00 0.00
gc current grant 2-way 1 0.00 0.00
cell single block physical read 205 0.02 0.11
gc cr grant 2-way 70 0.00 0.00
SQL*Net message from client 34 183.19 183.19
********************************************************************************


Fast (UAT):
===========

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.17 0.17 0 25 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 34 0.11 0.11 0 86532 0 485
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 36 0.29 0.29 0 86557 0 485

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 459
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
485 485 485 VIEW RSKBYMSGORG_ID (cr=86532 pr=0 pw=0 time=117984 us cost=774 size=13 card=1)
485 485 485 HASH UNIQUE (cr=86532 pr=0 pw=0 time=117905 us cost=774 size=142 card=1)
485 485 485 NESTED LOOPS OUTER (cr=86532 pr=0 pw=0 time=115410 us cost=773 size=142 card=1)
485 485 485 NESTED LOOPS (cr=2305 pr=0 pw=0 time=5283 us cost=527 size=29 card=1)
485 485 485 INLIST ITERATOR (cr=1364 pr=0 pw=0 time=3482 us)
485 485 485 TABLE ACCESS BY INDEX ROWID ORG_XREF (cr=1364 pr=0 pw=0 time=3254 us cost=526 size=22 card=1)
485 485 485 INDEX UNIQUE SCAN ORG_XREF_PK (cr=1013 pr=0 pw=0 time=1639 us cost=525 size=0 card=1)(object id 1067775)
485 485 485 INDEX UNIQUE SCAN ORG_MAST_PK (cr=941 pr=0 pw=0 time=1619 us cost=1 size=7 card=1)(object id 1067776)
339 339 339 VIEW PUSHED PREDICATE RISK_MET_LATEST (cr=84227 pr=0 pw=0 time=109843 us cost=246 size=113 card=1)
339 339 339 NESTED LOOPS (cr=84227 pr=0 pw=0 time=108933 us cost=246 size=98 card=1)
339 339 339 NESTED LOOPS (cr=83907 pr=0 pw=0 time=107941 us cost=246 size=98 card=1)
339 339 339 VIEW (cr=82455 pr=0 pw=0 time=101015 us cost=241 size=26 card=1)
339 339 339 SORT GROUP BY (cr=82455 pr=0 pw=0 time=100779 us cost=241 size=29 card=1)
1916 1916 1916 TABLE ACCESS BY INDEX ROWID BATCHED RISK_MET (cr=82455 pr=0 pw=0 time=98145 us cost=241 size=145 card=5)
81197 81197 81197 INDEX RANGE SCAN RISK_MET_PK (cr=1764 pr=0 pw=0 time=15121 us cost=5 size=0 card=246)(object id 4334658)
339 339 339 INDEX RANGE SCAN RISK_MET_PK (cr=1452 pr=0 pw=0 time=6557 us cost=4 size=0 card=1)(object id 4334658)
339 339 339 TABLE ACCESS BY INDEX ROWID RISK_MET (cr=320 pr=0 pw=0 time=632 us cost=5 size=72 card=1)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
library cache lock 4 0.00 0.00
library cache pin 6 0.00 0.00
Disk file operations I/O 200 0.00 0.00
SQL*Net message to client 34 0.00 0.00
gc cr block 2-way 1 0.00 0.00
SQL*Net message from client 34 6.87 6.87

Chris Saxon
September 13, 2021 - 12:57 pm UTC

On the (dev?) database the query is full scanning these tables:

485 485 485 TABLE ACCESS STORAGE FULL ORG_XREF (cr=30772 pr=0 pw=0 time=494842 us cost=427 size=22 card=1)
...
560 560 560 TABLE ACCESS STORAGE FULL RISK_MET (cr=4179246 pr=0 pw=0 time=39387712 us cost=124 size=145 card=5)


The second of these (RISK_MET) seems to be the main reason it's taking longer. This full scan on its own takes over 39s! (time=39387712 us).

Are you sure all the indexes and constraints are the same in both databases?

In particular, are these indexes enabled in both:

ORG_XREF_PK
RISK_MET_PK

INLIST ITERATOR in execution plan

Paul, September 15, 2021 - 6:38 am UTC

Hi Connor,

>>Are you sure all the indexes and constraints are the same in both databases?
In particular, are these indexes enabled in both:

ORG_XREF_PK
RISK_MET_PK
>>

Yes, Primary Key constraint and indexe status are VALID.

I have also tried reorg the table and rebuilt the index but no changes in execution time. If i make the RISK_MET_PK index invisible, query comes back fast. RISK_MET table still go for full table scan but its quick. Execution plan is below-


==
1* select owner,table_name, constraint_name, status, validated from dba_constraints where owner='BTM' and constraint_name='ORG_XREF_PK'
SQL> /
de
OWNER TABLE_NAME CONSTRAINT_NAME STATUS VALIDATED
-------------------- ------------------------------ ------------------------------ -------- -------------
BTM ORG_XREF ORG_XREF_PK ENABLED VALIDATED

SQL
SQL> select owner,table_name, constraint_name, status, validated from dba_constraints where owner='BTM' and constraint_name='RISK_MET_PK' ;

OWNER TABLE_NAME CONSTRAINT_NAME STATUS VALIDATED
-------------------- ------------------------------ ------------------------------ -------- -------------
BTM RISK_MET RISK_MET_PK ENABLED VALIDATED

SQL> select owner,table_name, num_rows, last_analyzed, stale_stats from dba_tab_statistics where owner='BTM' and table_name='RISK_MET' ;

OWNER TABLE_NAME NUM_ROWS LAST_ANALYZED STA
-------------------- ------------------------------ ---------- ------------------- ---
BTM RISK_MET 529329 09/13/2021 10:38:57 NO


SQL> select owner,table_name, num_rows, last_analyzed, stale_stats from dba_tab_statistics where owner='BTM' and table_name='ORG_XREF' ;

OWNER TABLE_NAME NUM_ROWS LAST_ANALYZED STA
-------------------- ------------------------------ ---------- ------------------- ---
BTM ORG_XREF 3510563 09/13/2021 10:42:28 NO

SQL> select owner,table_name, constraint_name, status, validated from dba_constraints where owner='BTM' and constraint_name='ORG_XREF_PK'
2 /

OWNER TABLE_NAME CONSTRAINT_NAME STATUS VALIDATED
-------------------- ------------------------------ ------------------------------ -------- -------------
BTM ORG_XREF ORG_XREF_PK ENABLED VALIDATED



SQL>
===

SQL> alter index BTM.RISK_MET_PK invisible ;

Index altered.

With index invisible, execution plan looks like below

Execution Plan
----------------------------------------------------------
Plan hash value: 11551336

------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 715 (17)| 00:00:01 |
| 1 | VIEW | RSKBYMSGORG_ID | 1 | 13 | 715 (17)| 00:00:01 |
| 2 | HASH UNIQUE | | 1 | 91 | 715 (17)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 91 | 714 (17)| 00:00:01 |
|* 4 | HASH JOIN OUTER | | 1 | 84 | 713 (17)| 00:00:01 |
|* 5 | TABLE ACCESS STORAGE FULL | ORG_XREF | 1 | 22 | 427 (13)| 00:00:01 |
| 6 | VIEW | RISK_MET_LATEST | 11 | 682 | 286 (24)| 00:00:01 |
|* 7 | HASH JOIN | | 11 | 1078 | 286 (24)| 00:00:01 |
| 8 | VIEW | | 1695 | 44070 | 144 (24)| 00:00:01 |
| 9 | HASH GROUP BY | | 1695 | 49155 | 144 (24)| 00:00:01 |
|* 10 | TABLE ACCESS STORAGE FULL| RISK_MET | 3488 | 98K| 143 (24)| 00:00:01 |
|* 11 | TABLE ACCESS STORAGE FULL | RISK_MET | 3488 | 245K| 143 (24)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | ORG_MAST_PK | 1 | 7 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
Chris Saxon
September 15, 2021 - 2:56 pm UTC

It's Chris here, but hey!

The point is something's different between the databases that is causing it to choose a full table scan in one and an index range scan in the other.

I just guessed at the indexes being different - check the table's data, stats, indexes, and constraints carefully on each to ensure they are the same.

Look to see if there are any SQL plan baselines or profiles in either database that are forcing a particular plan.

INLIST INTERATOR

Paul, September 15, 2021 - 3:15 pm UTC

Hey Chris,

Thanks for your suggestion. I will look into.

So far, i have checked the table/index/stats and could not find any difference. Even copied stats from UAT to Dev for those tables, still its going to full table scan.

1) Also, curious on why Oracle not choosing "INLIST ITERATOR optimization" in the plan in Dev database for this query ?

2) also as updated earlier, If i make the index invisible BTM.RISK_MET_PK (primary key index), the execution plan goes for full table scan and query completes very fast.

Are there any other performance methods will help me to further troubleshoot the issue. ?


Chris Saxon
September 16, 2021 - 3:41 pm UTC

I'm not sure why it choose INLIST in one DB and not the other.

I think this is the wrong place to focus though.

The full scan of RISK_MET accounts most of the extra runtime. So getting to the bottom of why the optimizer's choosing this (instead of RISK_MET_PK) should improve the runtime.

To re-iterate, this line

560 560 560 TABLE ACCESS STORAGE FULL RISK_MET (cr=4179246 pr=0 pw=0 time=39387712 us cost=124 size=145 card=5)


Tells you that the database is doing 4 million gets & taking 39 seconds when reading this table to return just 560 rows. That is an excessive amount of work for so few rows.

What exactly is the fast plan with the disabled PK?

INLIST Iteration

Paul, September 17, 2021 - 5:04 am UTC

Hey Chris,

>> What exactly is the fast plan with the disabled PK?

Below is the same query's execution plan after made RISK_MET_PK index invisible.


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.13 0.14 1 25 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 34 0.72 0.82 9077 49980 0 485
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 36 0.85 0.96 9078 50005 0 485

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 132
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
485 485 485 VIEW RSKBYMSGORG_ID (cr=49980 pr=9077 pw=0 time=821616 us cost=712 size=13 card=1)
485 485 485 HASH UNIQUE (cr=49980 pr=9077 pw=0 time=821533 us cost=712 size=91 card=1)
485 485 485 NESTED LOOPS (cr=49980 pr=9077 pw=0 time=820142 us cost=711 size=91 card=1)
485 485 485 HASH JOIN OUTER (cr=49008 pr=9077 pw=0 time=806871 us cost=710 size=84 card=1)
485 485 485 TABLE ACCESS STORAGE FULL ORG_REF (cr=30836 pr=0 pw=0 time=491531 us cost=427 size=22 card=1)
2100 2100 2100 VIEW RISK_MET_LATEST (cr=18172 pr=9077 pw=0 time=313308 us cost=283 size=496 card=8)
2100 2100 2100 HASH JOIN (cr=18172 pr=9077 pw=0 time=312380 us cost=283 size=784 card=8)
2100 2100 2100 VIEW (cr=9086 pr=9077 pw=0 time=236178 us cost=142 size=38948 card=1498)
2100 2100 2100 HASH GROUP BY (cr=9086 pr=9077 pw=0 time=235776 us cost=142 size=43442 card=1498)
3478 3478 3478 TABLE ACCESS STORAGE FULL RISK_MET (cr=9086 pr=9077 pw=0 time=233050 us cost=141 size=76502 card=2638)
3478 3478 3478 TABLE ACCESS STORAGE FULL RISK_MET (cr=9086 pr=0 pw=0 time=72462 us cost=141 size=189936 card=2638)
485 485 485 INDEX UNIQUE SCAN ORG_MAST_PK (cr=972 pr=0 pw=0 time=13094 us cost=1 size=7 card=1)(object id 1097436)

Chris Saxon
September 17, 2021 - 8:22 am UTC

Interesting.

The time for the full scan has dropped drastically.

There's also a big jump in how many rows the optimizer estimates this will return (from card=5 up to card=2638).

Making the PK invisible isn't a viable strategy - you'll break all your PK lookups.

Get the plans with dbms_xplan like this so we can see all the predicates, etc.

alter session set statistics_level = all;
set serveroutput off

select * from ...

select * 
from   table(dbms_xplan.display_cursor( format => 'ALLSTATS LAST'));


And please wrap the plans with the CODE tags to preserve the formatting!

Paul, September 17, 2021 - 3:44 pm UTC

Hey Chris,

>>Get the plans with dbms_xplan like this so we can see all the predicates, etc.

Please find below-


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 1k062p07x8v0z, child number 0
-------------------------------------
SELECT "RepRiskDataItem"."CURRENT_RRI" AS "RepRiskDataItem_CURRENT_RRI"
FROM "MSGBT"."RSKBYMSGORG_ID" "RepRiskDataItem" WHERE
"RepRiskDataItem"."ORG_ID" IN
(22162968,7925424,8015948,22193263,55783513,7922947,7924894,7970117,8564
782,8074629,7925061,8074669,8030228,8192997,8075042,8378381,8218011,8074
531,8132460,8051159,8018963,190674788,10633559,7944336,8474132,10733064,
22013562,8691809,22179043,22185361,8260501,22214939,22183222,8564827,150
016252,10908258,22205023,22182472,69327272,22197672,22186740,8061293,795
6477,8710682,22185995,22200504,8102345,9869203,8181688,7979620,8217843,7
945338,47967732,8131827,8161028,9817316,11547290,22029988,8086562,792325
1,50455937,8142453,120746446,361185843,23408383,22213563,7923993,2215602
8,22202157,346444819,316315139,251722914,8016319,22204229,22205829,72655
093,8120213,8560192,7922816,7983069,7955849,7957923,7945161,22037671,810
2072,23244811,22162997,7979535,8181981,8074997,8413703,8721442,22192421,
8653884,11552735,22185198,8292185,22

Plan hash value: 11551336

-------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 485 |00:00:00.63 | 49980 | | | |
| 1 | VIEW | RSKBYMSGORG_ID | 1 | 1 | 485 |00:00:00.63 | 49980 | | | |
| 2 | HASH UNIQUE | | 1 | 1 | 485 |00:00:00.63 | 49980 | 1949K| 1949K| 1373K (0)|
| 3 | NESTED LOOPS | | 1 | 1 | 485 |00:00:00.62 | 49980 | | | |
|* 4 | HASH JOIN OUTER | | 1 | 1 | 485 |00:00:00.62 | 49008 | 2701K| 2701K| 1577K (0)|
|* 5 | TABLE ACCESS STORAGE FULL | ORG_XREF | 1 | 1 | 485 |00:00:00.51 | 30836 | 1025K| 1025K| |
| 6 | VIEW | RISK_MET_LATEST | 1 | 12 | 2100 |00:00:00.11 | 18172 | | | |
|* 7 | HASH JOIN | | 1 | 12 | 2100 |00:00:00.11 | 18172 | 3248K| 3248K| 1397K (0)|
| 8 | VIEW | | 1 | 1729 | 2100 |00:00:00.05 | 9086 | | | |
| 9 | HASH GROUP BY | | 1 | 1729 | 2100 |00:00:00.05 | 9086 | 2828K| 2828K| 1334K (0)|
|* 10 | TABLE ACCESS STORAGE FULL| RISK_MET | 1 | 3679 | 3476 |00:00:00.05 | 9086 | 1025K| 1025K| |
|* 11 | TABLE ACCESS STORAGE FULL | RISK_MET | 1 | 3679 | 3476 |00:00:00.06 | 9086 | 1025K| 1025K| |
|* 12 | INDEX UNIQUE SCAN | ORG_MAST_PK | 485 | 1 | 485 |00:00:00.01 | 972 | 1025K| 1025K| |
-------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("T3"."ORG_ID"=TO_NUMBER("IDENTIFIER"))
5 - storage(("IDENTIFIER_TYPE"=11 AND INTERNAL_FUNCTION("ORG_ID")))
filter(("IDENTIFIER_TYPE"=11 AND INTERNAL_FUNCTION("ORG_ID")))
7 - access("T1"."ORG_ID"="T2"."ORG_ID" AND "T1"."EFFECTIVE_DATE_START"="T2"."EDS_MAX")
10 - storage("RECORDED_DATE_END">SYS_EXTRACT_UTC(SYSTIMESTAMP(6)))
filter("RECORDED_DATE_END">SYS_EXTRACT_UTC(SYSTIMESTAMP(6)))
11 - storage("T1"."RECORDED_DATE_END">SYS_EXTRACT_UTC(SYSTIMESTAMP(6)))
filter("T1"."RECORDED_DATE_END">SYS_EXTRACT_UTC(SYSTIMESTAMP(6)))
12 - access("ORG_ID"="ORG_ID")
filter(("ORG_ID"=7922771 OR "ORG_ID"=7922781 OR "ORG_ID"=7922788 OR "ORG_ID"=7922796 OR
"ORG_ID"=7922797 OR "ORG_ID"=7922816 OR "ORG_ID"=7922818 OR "ORG_ID"=7922819 OR
"ORG_ID"=7922892 OR "ORG_ID"=7922908 OR "ORG_ID"=7922919 OR "ORG_ID"=7922946 OR
"ORG_ID"=7922947 OR "ORG_ID"=7922959 OR "ORG_ID"=7923038 OR "ORG_ID"=7923060 OR
"ORG_ID"=7923093 OR "ORG_ID"=7923128 OR "ORG_ID"=7923177 OR "ORG_ID"=7923190 OR
"ORG_ID"=7923251 OR "ORG_ID"=7923281 OR "ORG_ID"=7923301 OR "ORG_ID"=7923302 OR
"ORG_ID"=7923381 OR "ORG_ID"=7923454 OR "ORG_ID"=7923485 OR "ORG_ID"=7923493 OR
"ORG_ID"=7923612 OR "ORG_ID"=7923664 OR "ORG_ID"=7923960 OR "ORG_ID"=7923993 OR
"ORG_ID"=7924894 OR "ORG_ID"=7925000 OR "ORG_ID"=7925026 OR "ORG_ID"=7925061 OR
"ORG_ID"=7925255 OR "ORG_ID"=7925424 OR "ORG_ID"=7925631 OR "ORG_ID"=7925721 OR
"ORG_ID"=7925834 OR "ORG_ID"=7926366 OR "ORG_ID"=7926444 OR "ORG_ID"=7926506 OR
"ORG_ID"=7927109 OR "ORG_ID"=7942304 OR "ORG_ID"=7942440 OR "ORG_ID"=7942733 OR
"ORG_ID"=7943814 OR "ORG_ID"=7943865 OR "ORG_ID"=7944294 OR "ORG_ID"=7944328 OR
"ORG_ID"=7944336 OR "ORG_ID"=7944605 OR "ORG_ID"=7944613 OR "ORG_ID"=7944697 OR
"ORG_ID"=7944734 OR "ORG_ID"=7944766 OR "ORG_ID"=7944842 OR "ORG_ID"=7944850 OR
"ORG_ID"=7944884 OR "ORG_ID"=7944994 OR "ORG_ID"=7945083 OR "ORG_ID"=7945161 OR
"ORG_ID"=7945338 OR "ORG_ID"=7955729 OR "ORG_ID"=7955849 OR "ORG_ID"=7956477 OR
"ORG_ID"=7957556 OR "ORG_ID"=7957728 OR "ORG_ID"=7957923 OR "ORG_ID"=7958011 OR
"ORG_ID"=7958237 OR "ORG_ID"=7958367 OR "ORG_ID"=7959982 OR "ORG_ID"=7960271 OR
"ORG_ID"=7960292 OR "ORG_ID"=7960330 OR "ORG_ID"=7960331 OR "ORG_ID"=7960355 OR
"ORG_ID"=7960564 OR "ORG_ID"=7960572 OR "ORG_ID"=7960607 OR "ORG_ID"=7960790 OR
"ORG_ID"=7960856 OR "ORG_ID"=7960985 OR "ORG_ID"=7969983 OR "ORG_ID"=7970117 OR
"ORG_ID"=7970410 OR "ORG_ID"=7970511 OR "ORG_ID"=7979453 OR "ORG_ID"=7979535 OR
"ORG_ID"=7979620 OR "ORG_ID"=7983018 OR "ORG_ID"=7983069 OR "ORG_ID"=8002767 OR
"ORG_ID"=8002773 OR "ORG_ID"=8003003 OR "ORG_ID"=8003025 OR "ORG_ID"=8003194 OR
"ORG_ID"=8003249 OR "ORG_ID"=8003253 OR "ORG_ID"=8003322 OR "ORG_ID"=8003479 OR
"ORG_ID"=8003632 OR "ORG_ID"=8003822 OR "ORG_ID"=8003895 OR "ORG_ID"=8015948 OR
"ORG_ID"=8015995 OR "ORG_ID"=8016067 OR "ORG_ID"=8016319 OR "ORG_ID"=8018869 OR
"ORG_ID"=8018940 OR "ORG_ID"=8018963 OR "ORG_ID"=8018984 OR "ORG_ID"=8027286 OR
"ORG_ID"=8029813 OR "ORG_ID"=8030228 OR "ORG_ID"=8038586 OR "ORG_ID"=8051147 OR
"ORG_ID"=8051159 OR "ORG_ID"=8051523 OR "ORG_ID"=8061144 OR "ORG_ID"=8061293 OR
"ORG_ID"=8061342 OR "ORG_ID"=8061462 OR "ORG_ID"=8061549 OR "ORG_ID"=8071433 OR
"ORG_ID"=8071511 OR "ORG_ID"=8071907 OR "ORG_ID"=8074531 OR "ORG_ID"=8074569 OR
"ORG_ID"=8074629 OR "ORG_ID"=8074661 OR "ORG_ID"=8074669 OR "ORG_ID"=8074997 OR
"ORG_ID"=8074999 OR "ORG_ID"=8075038)


Chris Saxon
September 17, 2021 - 5:06 pm UTC

CODE TAGS! Remember the CODE TAGS!

Like a standard HTML tag with the word code.

And post the fast & slow plans where you haven't made the PK invisible - that's what we're trying to figure out what's different, right?

INLIST Iteration

Paul, September 21, 2021 - 6:55 am UTC

Hey Chris,

Greetings!

>>And post the fast & slow plans where you haven't made the PK invisible - that's what we're trying to figure out what's different, right?

Please find below-

Slow plan (RISK_MET_PK index visible):
======================================

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  1k062p07x8v0z, child number 0
-------------------------------------
SELECT "DataItem"."CURRENT_RRI" AS "DataItem_CURRENT_RRI"
FROM "BTGSM"."RSKBYMSGORG_ID" "DataItem" WHERE
"DataItem"."ORG_ID" IN
(22162968,7925424,8015948,22193263,55783513,7922947,7924894,7970117,8564
782,8074629,7925061,8074669,8030228,8192997,8075042,8378381,8218011,8074
531,8132460,8051159,8018963,190674788,10633559,7944336,8474132,10733064,
22013562,8691809,22179043,22185361,8260501,22214939,22183222,8564827,150
016252,10908258,22205023,22182472,69327272,22197672,22186740,8061293,795
6477,8710682,22185995,22200504,8102345,9869203,8181688,7979620,8217843,7
945338,47967732,8131827,8161028,9817316,11547290,22029988,8086562,792325
1,50455937,8142453,120746446,361185843,23408383,22213563,7923993,2215602
8,22202157,346444819,316315139,251722914,8016319,22204229,22205829,72655
093,8120213,8560192,7922816,7983069,7955849,7957923,7945161,22037671,810
2072,23244811,22162997,7979535,8181981,8074997,8413703,8721442,22192421,
8653884,11552735,22185198,8292185,22

Plan hash value: 327314901

----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                   | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                        |      1 |        |    485 |00:00:40.59 |    4440K|    750 |       |       |          |
|   1 |  VIEW                             | RSKBYMSGORG_ID         |      1 |      1 |    485 |00:00:40.59 |    4440K|    750 |       |       |          |
|   2 |   HASH UNIQUE                     |                        |      1 |      1 |    485 |00:00:40.59 |    4440K|    750 |  1949K|  1949K| 1332K (0)|
|   3 |    NESTED LOOPS OUTER             |                        |      1 |      1 |    485 |00:00:40.58 |    4440K|    750 |       |       |          |
|   4 |     NESTED LOOPS                  |                        |      1 |      1 |    485 |00:00:00.52 |    31823|      0 |       |       |          |
|*  5 |      TABLE ACCESS STORAGE FULL    | ORG_XREF               |      1 |      1 |    485 |00:00:00.51 |    30852|      0 |  1025K|  1025K|          |
|*  6 |      INDEX UNIQUE SCAN            | ORG_MASTER_PK          |    485 |      1 |    485 |00:00:00.01 |      971|      0 |  1025K|  1025K|          |
|   7 |     VIEW PUSHED PREDICATE         | RISK_MET_LATEST        |    485 |      1 |    339 |00:00:40.06 |    4408K|    750 |       |       |          |
|   8 |      NESTED LOOPS                 |                        |    485 |      1 |    339 |00:00:40.06 |    4408K|    750 |       |       |          |
|   9 |       NESTED LOOPS                |                        |    485 |      1 |    339 |00:00:40.05 |    4407K|    750 |       |       |          |
|  10 |        VIEW                       |                        |    485 |      1 |    339 |00:00:39.63 |    4406K|      0 |       |       |          |
|  11 |         SORT GROUP BY             |                        |    485 |      1 |    339 |00:00:39.63 |    4406K|      0 |  2048 |  2048 | 2048  (0)|
|* 12 |          TABLE ACCESS STORAGE FULL| RISK_MET               |    485 |      2 |    560 |00:00:39.62 |    4406K|      0 |  1025K|  1025K|          |
|* 13 |        INDEX RANGE SCAN           | RISK_MET_PK            |    339 |      1 |    339 |00:00:00.42 |     1201|    750 |  1025K|  1025K|          |
|* 14 |       TABLE ACCESS BY INDEX ROWID | RISK_MET               |    339 |      1 |    339 |00:00:00.01 |      322|      0 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - storage(("IDENTIFIER_TYPE"=11 AND INTERNAL_FUNCTION("ORG_ID")))
       filter(("IDENTIFIER_TYPE"=11 AND INTERNAL_FUNCTION("ORG_ID")))
   6 - access("ORG_ID"="ORG_ID")
       filter(("ORG_ID"=7922771 OR "ORG_ID"=7922781 OR "ORG_ID"=7922788 OR "ORG_ID"=7922796 OR
              "ORG_ID"=7922797 OR "ORG_ID"=7922816 OR "ORG_ID"=7922818 OR "ORG_ID"=7922819 OR "ORG_ID"=7922892 OR
              "ORG_ID"=7922908 OR "ORG_ID"=7922919 OR "ORG_ID"=7922946 OR "ORG_ID"=7922947 OR "ORG_ID"=7922959 OR
              "ORG_ID"=7923038 OR "ORG_ID"=7923060 OR "ORG_ID"=7923093 OR "ORG_ID"=7923128 OR "ORG_ID"=7923177 OR
              "ORG_ID"=7923190 OR "ORG_ID"=7923251 OR "ORG_ID"=7923281 OR "ORG_ID"=7923301 OR "ORG_ID"=7923302 OR
              "ORG_ID"=7923381 OR "ORG_ID"=7923454 OR "ORG_ID"=7923485 OR "ORG_ID"=7923493 OR "ORG_ID"=7923612 OR
              "ORG_ID"=7923664 OR "ORG_ID"=7923960 OR "ORG_ID"=7923993 OR "ORG_ID"=7924894 OR "ORG_ID"=7925000 OR
              "ORG_ID"=7925026 OR "ORG_ID"=7925061 OR "ORG_ID"=7925255 OR "ORG_ID"=7925424 OR "ORG_ID"=7925631 OR
              "ORG_ID"=7925721 OR "ORG_ID"=7925834 OR "ORG_ID"=7926366 OR "ORG_ID"=7926444 OR "ORG_ID"=7926506 OR
              "ORG_ID"=7927109 OR "ORG_ID"=7942304 OR "ORG_ID"=7942440 OR "ORG_ID"=7942733 OR "ORG_ID"=7943814 OR
              "ORG_ID"=7943865 OR "ORG_ID"=7944294 OR "ORG_ID"=7944328 OR "ORG_ID"=7944336 OR "ORG_ID"=7944605 OR
              "ORG_ID"=7944613 OR "ORG_ID"=7944697 OR "ORG_ID"=7944734 OR "ORG_ID"=7944766 OR "ORG_ID"=7944842 OR
              "ORG_ID"=7944850 OR "ORG_ID"=7944884 OR "ORG_ID"=7944994 OR "ORG_ID"=7945083 OR "ORG_ID"=7945161 OR
              "ORG_ID"=7945338 OR "ORG_ID"=7955729 OR "ORG_ID"=7955849 OR "ORG_ID"=7956477 OR "ORG_ID"=7957556 OR
              "ORG_ID"=7957728 OR "ORG_ID"=7957923 OR "ORG_ID"=7958011 OR "ORG_ID"=7958237 OR "ORG_ID"=7958367 OR
              "ORG_ID"=7959982 OR "ORG_ID"=7960271 OR "ORG_ID"=7960292 OR "ORG_ID"=7960330 OR "ORG_ID"=7960331 OR
              "ORG_ID"=7960355 OR "ORG_ID"=7960564 OR "ORG_ID"=7960572 OR "ORG_ID"=7960607 OR "ORG_ID"=7960790 OR
              "ORG_ID"=7960856 OR "ORG_ID"=7960985 OR "ORG_ID"=7969983 OR "ORG_ID"=7970117 OR "ORG_ID"=7970410 OR
              "ORG_ID"=7970511 OR "ORG_ID"=7979453 OR "ORG_ID"=7979535 OR "ORG_ID"=7979620 OR "ORG_ID"=7983018 OR
              "ORG_ID"=7983069 OR "ORG_ID"=8002767 OR "ORG_ID"=8002773 OR "ORG_ID"=8003003 OR "ORG_ID"=8003025 OR
              "ORG_ID"=8003194 OR "ORG_ID"=8003249 OR "ORG_ID"=8003253 OR "ORG_ID"=8003322 OR "ORG_ID"=8003479 OR
              "ORG_ID"=8003632 OR "ORG_ID"=8003822 OR "ORG_ID"=8003895 OR "ORG_ID"=8015948 OR "ORG_ID"=8015995 OR
              "ORG_ID"=8016067 OR "ORG_ID"=8016319 OR "ORG_ID"=8018869 OR "ORG_ID"=8018940 OR "ORG_ID"=8018963 OR
              "ORG_ID"=8018984 OR "ORG_ID"=8027286 OR "ORG_ID"=8029813 OR "ORG_ID"=8030228 OR "ORG_ID"=8038586 OR
              "ORG_ID"=8051147 OR "ORG_ID"=8051159 OR "ORG_ID"=8051523 OR "ORG_ID"=8061144 OR "ORG_ID"=8061293 OR
              "ORG_ID"=8061342 OR "ORG_ID"=8061462 OR "ORG_ID"=8061549 OR "ORG_ID"=8071433 OR "ORG_ID"=8071511 OR
              "ORG_ID"=8071907 OR "ORG_ID"=8074531 OR "ORG_ID"=8074569 OR "ORG_ID"=8074629 OR "ORG_ID"=8074661 OR
              "ORG_ID"=8074669 OR "ORG_ID"=8074997 OR "ORG_ID"=8074999 OR "ORG_ID"=8075038)
  12 - filter(("ORG_ID"=TO_NUMBER("IDENTIFIER") AND "RECORDED_DATE_END">SYS_EXTRACT_UTC(SYSTIMESTAMP(6))))
  13 - access("T1"."ORG_ID"=TO_NUMBER("IDENTIFIER") AND "T1"."EFFECTIVE_DATE_START"="T2"."EDS_MAX")
       filter(("T1"."EFFECTIVE_DATE_START"="T2"."EDS_MAX" AND "T1"."ORG_ID"="T2"."ORG_ID"))
  14 - filter("T1"."RECORDED_DATE_END">SYS_EXTRACT_UTC(SYSTIMESTAMP(6)))


79 rows selected.



Fast plan (made RISK_MET_PK invisible):
=======================================

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  1k062p07x8v0z, child number 0
-------------------------------------
SELECT "DataItem"."CURRENT_RRI" AS "DataItem_CURRENT_RRI"
FROM "BTGSM"."RSKBYMSGORG_ID" "DataItem" WHERE
"DataItem"."ORG_ID" IN
(22162968,7925424,8015948,22193263,55783513,7922947,7924894,7970117,8564
782,8074629,7925061,8074669,8030228,8192997,8075042,8378381,8218011,8074
531,8132460,8051159,8018963,190674788,10633559,7944336,8474132,10733064,
22013562,8691809,22179043,22185361,8260501,22214939,22183222,8564827,150
016252,10908258,22205023,22182472,69327272,22197672,22186740,8061293,795
6477,8710682,22185995,22200504,8102345,9869203,8181688,7979620,8217843,7
945338,47967732,8131827,8161028,9817316,11547290,22029988,8086562,792325
1,50455937,8142453,120746446,361185843,23408383,22213563,7923993,2215602
8,22202157,346444819,316315139,251722914,8016319,22204229,22205829,72655
093,8120213,8560192,7922816,7983069,7955849,7957923,7945161,22037671,810
2072,23244811,22162997,7979535,8181981,8074997,8413703,8721442,22192421,
8653884,11552735,22185198,8292185,22

Plan hash value: 11551336

-------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                        |      1 |        |    485 |00:00:00.60 |   49996 |       |       |          |
|   1 |  VIEW                             | RSKBYMSGORG_ID         |      1 |      1 |    485 |00:00:00.60 |   49996 |       |       |          |
|   2 |   HASH UNIQUE                     |                        |      1 |      1 |    485 |00:00:00.60 |   49996 |  1949K|  1949K| 1353K (0)|
|   3 |    NESTED LOOPS                   |                        |      1 |      1 |    485 |00:00:00.60 |   49996 |       |       |          |
|*  4 |     HASH JOIN OUTER               |                        |      1 |      1 |    485 |00:00:00.60 |   49024 |  2701K|  2701K| 1563K (0)|
|*  5 |      TABLE ACCESS STORAGE FULL    | ORG_XREF               |      1 |      1 |    485 |00:00:00.49 |   30852 |  1025K|  1025K|          |
|   6 |      VIEW                         | RISK_MET_LATEST        |      1 |     13 |   2101 |00:00:00.11 |   18172 |       |       |          |
|*  7 |       HASH JOIN                   |                        |      1 |     13 |   2101 |00:00:00.11 |   18172 |  3248K|  3248K| 1717K (0)|
|   8 |        VIEW                       |                        |      1 |   1784 |   2101 |00:00:00.05 |    9086 |       |       |          |
|   9 |         HASH GROUP BY             |                        |      1 |   1784 |   2101 |00:00:00.05 |    9086 |  2828K|  2828K| 1331K (0)|
|* 10 |          TABLE ACCESS STORAGE FULL| RISK_MET               |      1 |   4025 |   3476 |00:00:00.04 |    9086 |  1025K|  1025K|          |
|* 11 |        TABLE ACCESS STORAGE FULL  | RISK_MET               |      1 |   4025 |   3476 |00:00:00.06 |    9086 |  1025K|  1025K|          |
|* 12 |     INDEX UNIQUE SCAN             | ORG_MASTER_PK          |    485 |      1 |    485 |00:00:00.01 |     972 |  1025K|  1025K|          |
-------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("T3"."ORG_ID"=TO_NUMBER("IDENTIFIER"))
   5 - storage(("IDENTIFIER_TYPE"=11 AND INTERNAL_FUNCTION("ORG_ID")))
       filter(("IDENTIFIER_TYPE"=11 AND INTERNAL_FUNCTION("ORG_ID")))
   7 - access("T1"."ORG_ID"="T2"."ORG_ID" AND "T1"."EFFECTIVE_DATE_START"="T2"."EDS_MAX")
  10 - storage("RECORDED_DATE_END">SYS_EXTRACT_UTC(SYSTIMESTAMP(6)))
       filter("RECORDED_DATE_END">SYS_EXTRACT_UTC(SYSTIMESTAMP(6)))
  11 - storage("T1"."RECORDED_DATE_END">SYS_EXTRACT_UTC(SYSTIMESTAMP(6)))
       filter("T1"."RECORDED_DATE_END">SYS_EXTRACT_UTC(SYSTIMESTAMP(6)))
  12 - access("ORG_ID"="ORG_ID")
       filter(("ORG_ID"=7922771 OR "ORG_ID"=7922781 OR "ORG_ID"=7922788 OR "ORG_ID"=7922796 OR
              "ORG_ID"=7922797 OR "ORG_ID"=7922816 OR "ORG_ID"=7922818 OR "ORG_ID"=7922819 OR
              "ORG_ID"=7922892 OR "ORG_ID"=7922908 OR "ORG_ID"=7922919 OR "ORG_ID"=7922946 OR
              "ORG_ID"=7922947 OR "ORG_ID"=7922959 OR "ORG_ID"=7923038 OR "ORG_ID"=7923060 OR
              "ORG_ID"=7923093 OR "ORG_ID"=7923128 OR "ORG_ID"=7923177 OR "ORG_ID"=7923190 OR
              "ORG_ID"=7923251 OR "ORG_ID"=7923281 OR "ORG_ID"=7923301 OR "ORG_ID"=7923302 OR
              "ORG_ID"=7923381 OR "ORG_ID"=7923454 OR "ORG_ID"=7923485 OR "ORG_ID"=7923493 OR
              "ORG_ID"=7923612 OR "ORG_ID"=7923664 OR "ORG_ID"=7923960 OR "ORG_ID"=7923993 OR
              "ORG_ID"=7924894 OR "ORG_ID"=7925000 OR "ORG_ID"=7925026 OR "ORG_ID"=7925061 OR
              "ORG_ID"=7925255 OR "ORG_ID"=7925424 OR "ORG_ID"=7925631 OR "ORG_ID"=7925721 OR
              "ORG_ID"=7925834 OR "ORG_ID"=7926366 OR "ORG_ID"=7926444 OR "ORG_ID"=7926506 OR
              "ORG_ID"=7927109 OR "ORG_ID"=7942304 OR "ORG_ID"=7942440 OR "ORG_ID"=7942733 OR
              "ORG_ID"=7943814 OR "ORG_ID"=7943865 OR "ORG_ID"=7944294 OR "ORG_ID"=7944328 OR
              "ORG_ID"=7944336 OR "ORG_ID"=7944605 OR "ORG_ID"=7944613 OR "ORG_ID"=7944697 OR
              "ORG_ID"=7944734 OR "ORG_ID"=7944766 OR "ORG_ID"=7944842 OR "ORG_ID"=7944850 OR
              "ORG_ID"=7944884 OR "ORG_ID"=7944994 OR "ORG_ID"=7945083 OR "ORG_ID"=7945161 OR
              "ORG_ID"=7945338 OR "ORG_ID"=7955729 OR "ORG_ID"=7955849 OR "ORG_ID"=7956477 OR
              "ORG_ID"=7957556 OR "ORG_ID"=7957728 OR "ORG_ID"=7957923 OR "ORG_ID"=7958011 OR
              "ORG_ID"=7958237 OR "ORG_ID"=7958367 OR "ORG_ID"=7959982 OR "ORG_ID"=7960271 OR
              "ORG_ID"=7960292 OR "ORG_ID"=7960330 OR "ORG_ID"=7960331 OR "ORG_ID"=7960355 OR
              "ORG_ID"=7960564 OR "ORG_ID"=7960572 OR "ORG_ID"=7960607 OR "ORG_ID"=7960790 OR
              "ORG_ID"=7960856 OR "ORG_ID"=7960985 OR "ORG_ID"=7969983 OR "ORG_ID"=7970117 OR
              "ORG_ID"=7970410 OR "ORG_ID"=7970511 OR "ORG_ID"=7979453 OR "ORG_ID"=7979535 OR
              "ORG_ID"=7979620 OR "ORG_ID"=7983018 OR "ORG_ID"=7983069 OR "ORG_ID"=8002767 OR
              "ORG_ID"=8002773 OR "ORG_ID"=8003003 OR "ORG_ID"=8003025 OR "ORG_ID"=8003194 OR
              "ORG_ID"=8003249 OR "ORG_ID"=8003253 OR "ORG_ID"=8003322 OR "ORG_ID"=8003479 OR
              "ORG_ID"=8003632 OR "ORG_ID"=8003822 OR "ORG_ID"=8003895 OR "ORG_ID"=8015948 OR
              "ORG_ID"=8015995 OR "ORG_ID"=8016067 OR "ORG_ID"=8016319 OR "ORG_ID"=8018869 OR
              "ORG_ID"=8018940 OR "ORG_ID"=8018963 OR "ORG_ID"=8018984 OR "ORG_ID"=8027286 OR
              "ORG_ID"=8029813 OR "ORG_ID"=8030228 OR "ORG_ID"=8038586 OR "ORG_ID"=8051147 OR
              "ORG_ID"=8051159 OR "ORG_ID"=8051523 OR "ORG_ID"=8061144 OR "ORG_ID"=8061293 OR
              "ORG_ID"=8061342 OR "ORG_ID"=8061462 OR "ORG_ID"=8061549 OR "ORG_ID"=8071433 OR
              "ORG_ID"=8071511 OR "ORG_ID"=8071907 OR "ORG_ID"=8074531 OR "ORG_ID"=8074569 OR
              "ORG_ID"=8074629 OR "ORG_ID"=8074661 OR "ORG_ID"=8074669 OR "ORG_ID"=8074997 OR
              "ORG_ID"=8074999 OR "ORG_ID"=8075038)


86 rows selected.


Chris Saxon
September 21, 2021 - 2:48 pm UTC

OK, so the full scan of RISK_MET is taking so long because it runs 485 times!

This is because the optimizer has chosen nested loops instead of a hash join. Which appears to be because it's pushed:

"ORG_ID"=TO_NUMBER("IDENTIFIER")


to the table, leading it to think it'll only get two rows from the table. Which - to be fair - is broadly accurate (485 starts * 2 estimated rows = 970; a higher than the 560 it fetches but not excessively so).

I'm not sure why it's pushed this predicate in one situation but not the other. But at this point I think it's better to focus on how to make the query faster instead of focusing on why it's slow.

An index on

RISK_MET ( "ORG_ID", "RECORDED_DATE_END")


is likely to help here.

The query also accesses RISK_MET (and ORG_XREF?) twice. I'd check the query to see if you can rewrite to only reference each table once. This will hopefully resolve the issue and lead to a faster query overall.

INLIST INTERATOR

Paul, September 22, 2021 - 6:32 am UTC

Hey Chris,

Thanks for your recommendations on the query performance and indexes. I will look into further.

Chris Saxon
September 22, 2021 - 10:23 am UTC

You're welcome

Query tuning with large INLIST

Morgan, November 29, 2024 - 6:05 am UTC

Hi,
Thanks for your suggestions.

This query is taking bit longer to complete.
Bind values passed are in the range of up to 1000 values (SNP_ID, INSTR_ID) combination per table in the query below.
SQL monitor report attached below. Need your help or suggestions to rewrite the query in any optimal way. thx

SQL Monitoring Report

SQL Text
------------------------------
SELECT /*+ PARALLEL(8) */
*
FROM
(
SELECT
e.cid,
e.bid_spread,
e.bid_spread_curve,
e.i_spread,
e.g_spread,
e.snp_id,
e.asid,
e.bid_price,
e.ask_price,
e.instr_id,
e.snp_dt
FROM
eop.ffinc_prc e
WHERE
e.snp_id IN (
:bind_1,
:bind_2,
:bind_3,
:bind_4,
:bind_5,
:bind_6,
:bind_7,
:bind_8,
:bind_9,
:bind_10,
:bind_11,
:bind_12,
:bind_13,
:bind_14,
:bind_15,
:bind_16,
:bind_17,
:bind_18,
:bind_19,
:bind_20,
:bind_21,
:bind_22
)
AND instr_id IN (
:bind_23,
:bind_24,
:bind_25,
:bind_26,
:bind_27,
:bind_28,
:bind_29,
:bind_30,
:bind_31,
:bind_32,
:bind_33,
:bind_34,
:bind_35,
:bind_36,
:bind_37,
:bind_38,
:bind_39,
:bind_40,
:bind_41,
:bind_42,
:bind_43,
:bind_44,
:bind_45,
:bind_46,
:bind_47,
:bind_48,
:bind_49,
:bind_50
)
AND e.prc_typ = 5
AND e.eff_dt_start <= sys_extract_utc(systimestamp)
AND e.rec_dt_end > sys_extract_utc(systimestamp)
) g
LEFT JOIN (
SELECT
instr_id,
tranche_factor,
given_type,
asid,
snp_dt AS snp_dtx,
snp_id,
benchmark_name,
benchmark_id,
pricing_spread_type,
benchmark_alias,
table_methodology,
payup
FROM
eop.f_prc_asmp
WHERE
snp_id IN (
:bind_51,
:bind_52,
:bind_53,
:bind_54,
:bind_55,
:bind_56,
:bind_57,
:bind_58,
:bind_59,
:bind_60
)
AND instr_id IN (
:bind_61,
:bind_62,
:bind_63,
:bind_64,
:bind_65,
:bind_66,
:bind_67,
:bind_68,
:bind_69,
:bind_70
)
AND eff_dt_start <= sys_extract_utc(systimestamp)
AND rec_dt_end > sys_extract_utc(systimestamp)
) h ON g.snp_id = h.snp_id
AND g.snp_dt = h.snp_dtx
AND g.asid = h.asid
AND g.instr_id = h.instr_id
;

Global Information
------------------------------
 Status              :  DONE (ALL ROWS)
 Instance ID         :  3
 Session             :  SVCQRY (8897:10188)
 SQL ID              :  43p7k3rd5mcur
 SQL Execution ID    :  50331649
 Execution Started   :  11/28/2024 23:19:05
 First Refresh Time  :  11/28/2024 23:19:05
 Last Refresh Time   :  11/28/2024 23:20:14
 Duration            :  69s
 Module/Action       :  SQL*Plus/-
 Service             :  SYS$USERS
 Program             :  sqlplus
 Fetch Calls         :  27



Global Stats
============================================================
| Elapsed |   Cpu   | Cluster  |  Other   | Fetch | Buffer |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls |  Gets  |
============================================================
|     351 |     350 |     0.00 |     1.15 |    27 |   248M |
============================================================

Parallel Execution Details (DOP=8 , Servers Allocated=16)
=====================================================================================================
|      Name      | Type  | Server# | Elapsed |   Cpu   | Cluster  |  Other   | Buffer | Wait Events |
|                |       |         | Time(s) | Time(s) | Waits(s) | Waits(s) |  Gets  | (sample #)  |
=====================================================================================================
| PX Coordinator | QC    |         |    0.17 |    0.17 |          |          |   125K |             |
| p000           | Set 1 |       1 |      21 |      21 |          |     0.12 |    15M |             |
| p001           | Set 1 |       2 |      22 |      22 |          |     0.12 |    15M |             |
| p002           | Set 1 |       3 |      23 |      23 |          |     0.11 |    16M |             |
| p003           | Set 1 |       4 |      22 |      22 |          |     0.10 |    16M |             |
| p004           | Set 1 |       5 |      22 |      22 |     0.00 |     0.05 |    16M |             |
| p005           | Set 1 |       6 |      23 |      23 |          |     0.07 |    16M |             |
| p006           | Set 1 |       7 |      21 |      21 |          |     0.06 |    15M |             |
| p007           | Set 1 |       8 |      22 |      22 |          |     0.07 |    16M |             |
| p008           | Set 2 |       1 |    1.04 |    1.04 |          |          |     1M |             |
| p009           | Set 2 |       2 |      25 |      25 |          |     0.08 |    17M |             |
| p00a           | Set 2 |       3 |      26 |      26 |          |     0.05 |    17M |             |
| p00b           | Set 2 |       4 |      25 |      24 |          |     0.06 |    17M |             |
| p00c           | Set 2 |       5 |      26 |      26 |          |     0.03 |    17M |             |
| p00d           | Set 2 |       6 |      25 |      25 |          |     0.11 |    17M |             |
| p00e           | Set 2 |       7 |      23 |      23 |          |     0.09 |    18M |             |
| p00f           | Set 2 |       8 |      25 |      25 |          |     0.05 |    17M |             |
===================================================

SQL Plan Monitoring Details (Plan Hash Value=310315011)
=======================================================================================================================================================================================
| Id |                      Operation                      |            Name            |  Rows   | Cost |   Time    | Start  | Execs |   Rows   |  Mem  | Activity | Activity Detail  |
|    |                                                     |                            | (Estim) |      | Active(s) | Active |       | (Actual) | (Max) |   (%)    |   (# samples)    |
======================================================================================================================================================================================= 
|  0 | SELECT STATEMENT                                    |                            |         |      |         1 |    +69 |    17 |      387 |     . |       |                  |
|  1 |   PX COORDINATOR                                    |                            |         |      |         1 |    +69 |    17 |      387 |     . |       |                  |
|  2 |    PX SEND QC (RANDOM)                              | :TQ10001                   |     692 | 3118 |         1 |    +69 |     8 |      387 |     . |       |                  |
|  3 |     HASH JOIN OUTER BUFFERED                        |                            |     692 | 3118 |        42 |    +28 |     8 |      387 |  40MB |       |                  |
|  4 |      INLIST ITERATOR                                |                            |         |      |        23 |    +28 |     8 |      387 |     . |       |                  |
|  5 |       PX PARTITION HASH ITERATOR                    |                            |     692 | 1595 |        23 |    +28 |  3984 |      387 |     . |     1.15 | Cpu (4)          |
|  6 |        INLIST ITERATOR                              |                            |         |      |        23 |    +28 |   10M |      387 |     . |     2.31 | Cpu (8)          |
|  7 |         TABLE ACCESS BY LOCAL INDEX ROWID BATCHED   | FFINC_PRC                  |     692 | 1595 |        23 |    +28 |  228M |      387 |     . |     7.20 | Cpu (25)         |
|  8 |          INDEX RANGE SCAN                           | FF_INC_PRC_UK1         |     692 | 1523 |        23 |    +28 |  228M |      387 |     . |    39.19 | Cpu (136)        |
|  9 |      PX RECEIVE                                     |                            |       1 | 1523 |         1 |    +69 |     8 |      774 |     . |       |                  |
| 10 |       PX SEND BROADCAST                             | :TQ10000                   |       1 | 1523 |        20 |    +49 |     8 |     3096 |     . |       |                  |
| 11 |        INLIST ITERATOR                              |                            |         |      |        20 |    +49 |     8 |      387 |     . |       |                  |
| 12 |         PX PARTITION HASH AND                       |                            |       1 | 1523 |        48 |    +21 |  3984 |      387 |     . |     0.29 | Cpu (1)          |
| 13 |          INLIST ITERATOR                            |                            |         |      |        53 |    +16 |   10M |      387 |     . |     0.58 | Cpu (2)          |
| 14 |           TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | F_PRC_ASMP          |       1 | 1523 |        66 |     +3 |  228M |      387 |     . |     3.75 | Cpu (13)         |
| 15 |            INDEX RANGE SCAN                         | F_PRC_ASMP_UK1      |       1 | 1523 |        69 |     +1 |  228M |      387 |     . |    45.53 | Cpu (158)        |
=======================================================================================================================================================================================

Indexes on the tables are:

Table name: FFINC_PRC

INDEX_OWNER     INDEX_NAME                               COLUMN_NAME                    COLUMN_POSITION
--------------- ---------------------------------------- ------------------------------ ---------------
EOP             FF_INC_PRC_IDX1                       INSTR_ID                             1
EOP         FF_INC_PRC_IDX1                       EFF_DT_START                         2
EOP         FF_INC_PRC_IDX1                       ASID                              3
EOP         FF_INC_PRC_UK1                        SNP_ID                               1
EOP         FF_INC_PRC_UK1                        INSTR_ID                             2
EOP         FF_INC_PRC_UK1                        PRC_TYP                              3
EOP         FF_INC_PRC_UK1                        REC_DT_START                         4
EOP         FF_INC_PRC_UK1                        EFF_DT_START                         5
EOP         FF_INC_PRC_PK                       PRC_ID                               1
EOP         FF_INC_PRC_PK                       EFF_DT_START                         2
EOP         FF_INC_PRC_PK                       INSTR_ID                             3


Table_name: F_PRC_ASMP

INDEX_OWNER     INDEX_NAME                               COLUMN_NAME                    COLUMN_POSITION
--------------- ---------------------------------------- ------------------------------ ---------------
EOP         F_PRC_ASMP_PK                      ASID                               1
EOP         F_PRC_ASMP_PK                      EFF_DT_START                    2
EOP         F_PRC_ASMP_PK                      INSTR_ID                           3
EOP         F_PRC_ASMP_UK1                           SNP_ID                             1
EOP         F_PRC_ASMP_UK1                           INSTR_ID                           2
EOP         F_PRC_ASMP_UK1                           REC_DT_START                       3
EOP         F_PRC_ASMP_UK1                           EFF_DT_START                       4
SQL>




Chris Saxon
November 29, 2024 - 1:58 pm UTC

Whoa, both the index range scans are run 228 MILLION times!. Yet they only return a few hundred rows.

I'm unsure why this is, but this is the thing to address.

One thing to consider: none of the indexes include the end date columns. Creating indexes with these (before the start dates) is worth trying, e.g.:

f_prc_asmp ( snp_id,  instr_id, rec_dt_end, eff_dt_start )
ffinc_prc ( snp_id, instr_id, prc_typ, rec_dt_end, eff_dt_start )


I'm not sure it'll solve the huge execution issue, but it's somewhere to start.

Query tuning with large INLIST

Morgan, November 30, 2024 - 7:22 am UTC

Hi Chris,

Thanks for the inputs. I hope query wise no further optimization possible.

Btw, the table is composite partitioned using range partition key on EFF_DT_START column followed by Sub partition key on INSTR_ID column.

I am thinking that since the query filter columns are on (SNP_ID, INSTR_ID) columns, query seems to be doing lot of partition scans rather than specific partition scans.

I will try it out with adding REC_DT_END column to the index.

Thanks!

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.