Home » RDBMS Server » Performance Tuning » Inlist operator performance
Inlist operator performance [message #153053] Wed, 28 December 2005 14:19 Go to next message
mjschwenger
Messages: 137
Registered: July 2005
Location: US
Senior Member
This is one of the queries causing great wating time due to a lot of IO. It does use the correct index
and the CPU/IO usage doesn't show too high but waits forever.
If I replace the IN clause with equal sign, then everything is perfect and no waits occur...
How could I improve the inlist operator performance?
Thanks a lot,mj

SELECT ENT_ID, NUM_HASH, NUM_TYPE_ID, DSRC_ACCT_ID, NUM_ID, VALID_FROM_DT, VALID_THRU_DT, NUM_LOCATION
FROM NUMS WHERE ENTITY_ID IN (:1, :2, :3, :4, :5, :6)
AND NUM_TYPE_ID IN (:7) AND SYS_DELETE_DT IS NULL ;

1 6 SELECT STATEMENT
INLIST ITERATOR
1 6 TABLE ACCESS BY INDEX ROWID NUMS
1 6 INDEX RANGE SCAN PK_NUM_KEY

ALTER TABLE NUMS ADD CONSTRAINT PK_NUM_KEY PRIMARY KEY
(ENT_ID, NUM_TYPE_ID, DS_AC_ID, HIST_STAT) USING INDEX TABLESPACE TS_NUMS_IX;
Re: Inlist operator performance [message #153064 is a reply to message #153053] Wed, 28 December 2005 16:30 Go to previous messageGo to next message
mchadder
Messages: 224
Registered: May 2005
Location: UK
Senior Member
Well, I assume you mean that if you issue multiple selects with
ENTITY_ID = :1 etc. etc., then the speed is significantly improved? If so, don't forget that the two questions that you're asking are very different.

However, IN list operations (and the way that costs and cards are generated ) differ greatly between different oracle versions. What version are you using? If it's 9i or above, can you run the select for both = and IN mechanisms and post the output using the 9i EXPLAIN PLAN format, i.e. EXPLAIN PLAN FOR ....
and then SELECT * FROM TABLE(dbms_xplan.display), since from your posting, we aren't given cost / card information (assuming that you are indeed running CBO).

Thanks
Re: Inlist operator performance [message #153065 is a reply to message #153053] Wed, 28 December 2005 17:09 Go to previous messageGo to next message
mjschwenger
Messages: 137
Registered: July 2005
Location: US
Senior Member
I'm sorry - I'm using 10.1.0.4 on aix5.2.
My point to mention the = sign behavior was to pint out that I need help with the Inlist clause.
I gave the short execution plan for the statement.
Here are the details:
OPERATION OPTIONS OBJECT_NAME OBJECT_TYPE CPU_COST IO_COST CARDINALITY BYTES
UPDATE STATEMENT 1763 1 1 17
UPDATE ADDRESS
INDEX UNIQUE SCAN PK_ADDR_BUSKEY INDEX (UNIQUE) 17632 2 1 17
SORT AGGREGATE 1 17
FIRST ROW 17632 3 1 17
INDEX RANGE SCAN (MIN/MAX) PK_ADDR_BUSKEY INDEX (UNIQUE) 17632 3 1 17
SELECT STATEMENT 2480 1 6 288
INLIST ITERATOR
TABLE ACCESS BY INDEX ROWID NUMS TABLE 2480 1 6 288
INDEX RANGE SCAN PK_NUM_BUSKEY INDEX (UNIQUE) 9761 1 6

Thanks a lot,mj
Re: Inlist operator performance [message #153066 is a reply to message #153053] Wed, 28 December 2005 17:44 Go to previous messageGo to next message
mchadder
Messages: 224
Registered: May 2005
Location: UK
Senior Member
Hello.

Can you post the whole update statement that's being ran here.

Rgds
Re: Inlist operator performance [message #153171 is a reply to message #153053] Thu, 29 December 2005 12:10 Go to previous messageGo to next message
mjschwenger
Messages: 137
Registered: July 2005
Location: US
Senior Member
I'm sorry I posted a wrong exec plan...here is the correct one:
operations object_name object type CPU IO Cardinality bytes
SELECT STATEMENT 2480 1 6 288
INLIST ITERATOR
TABLE ACCESS BY INDEX ROWID NUMS TABLE 2480 1 6 288
INDEX RANGE SCAN PK_NUM_BUSKEY INDEX (UNIQUE) 9761 1 6

[Updated on: Thu, 29 December 2005 12:11]

Report message to a moderator

Re: Inlist operator performance [message #153191 is a reply to message #153053] Thu, 29 December 2005 17:09 Go to previous messageGo to next message
mchadder
Messages: 224
Registered: May 2005
Location: UK
Senior Member
Hi there.

Can you post the full explain plan output from the results of SELECT * FROM TABLE(dbms_xplan.display), i.e.
SQL> EXPLAIN PLAN FOR
  2  SELECT * FROM dual WHERE dummy = 'X';

Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

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

   1 - filter("DUMMY"='X')

13 rows selected.


And wrap the output in BBCode CODE tags so that it retains the formatting.

Thanks
Re: Inlist operator performance [message #153285 is a reply to message #153053] Fri, 30 December 2005 16:23 Go to previous messageGo to next message
mjschwenger
Messages: 137
Registered: July 2005
Location: US
Senior Member
Th eoutputI gave you is from:
SQL> explain plan for select_statement;
SQL> select ... from plan_table
I did not select the time - on all columns it's 00:00:15.
This is similar to your output from dbms_xplan.display and the same way trustful.
Thanks a lot,mj
Re: Inlist operator performance [message #153370 is a reply to message #153053] Sun, 01 January 2006 15:31 Go to previous message
mchadder
Messages: 224
Registered: May 2005
Location: UK
Senior Member
Hello.

Yes, I don't doubt it, but there are enhancements to the DBMS_XPLAN output which are very useful to have, specifically the predicate enhancements related to the given step, which are important, especially in queries like this one, when trying to understand the path that Oracle has chosen to fulfil the query.

Thanks
Previous Topic: Shrinking Index tablespace
Next Topic: long and short table scans
Goto Forum:
  


Current Time: Thu Mar 28 13:51:23 CDT 2024