| Inlist operator performance [message #153053] |
Wed, 28 December 2005 14:19  |
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  |
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 |
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 #153171 is a reply to message #153053] |
Thu, 29 December 2005 12:10 |
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 |
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 |
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 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
