RE: Large IN LIST in an OBIEE query
- From: Michael Dinh <mdinh@xxxxxxxxx>
- To: "mwf@xxxxxxxx" <mwf@xxxxxxxx>, "Hemant.Chitale@xxxxxx" <Hemant.Chitale@xxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 29 Oct 2011 08:49:47 -0700
a. Have you come across performance issues with very large IN LISTS
(1000 entries)
YES!
Developers are smart and what they do when the maximum 1000 entries are
exceeded is to use OR
Example: IN (1..1000) OR IN (1..1000) OR IN (1..1000)
Absolutely kills the system.
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] On Behalf
Of Mark W. Farnham [mwf@xxxxxxxx]
Sent: Saturday, October 29, 2011 7:23 AM
To: Hemant.Chitale@xxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: Large IN LIST in an OBIEE query
a. Yes. Sometimes IN LISTS generate a filter of ORs very late in the pruning
of the result set. Seeing your full query and the plan would be useful to
see if this or some other pathology is creating your problem.
b. People vary. If modifications seem unlikely you can encapsulate toss the
bind variable that holds the in_list into a pipeline function and read it as
a table. This *may* transform the query plan in similar fashion as having
the values in a temporary table without the fuss of stashing the in list
values into a table.
Here is an example (using a hard wired '1,2,3,4' for the in list instead of
a bind variable
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
--------------------------------------------------------
SQL_ID 6321k5byufgb0, child number 0
-------------------------------------
select --+ gather_plan_statistics t1.* from t1, (select column_value id
from table(val_list.f_trans('1,2,3,4'))) inlist where t1.id = inlist.id
Plan hash value: 244326803
----------------------------------------------------------------------------
-------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |
A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------
-------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | |
4 |00:00:00.01 | 18194 | | | |
|* 1 | HASH JOIN | | 1 | 8168 |
4 |00:00:00.01 | 18194 | 1306K| 1306K| 1034K (0)|
| 2 | COLLECTION ITERATOR PICKLER FETCH| F_TRANS | 1 | 8168 |
4 |00:00:00.01 | 0 | | | |
| 3 | TABLE ACCESS FULL | T1 | 1 | 1000K|
1000K|00:00:00.15 | 18194 | | | |
----------------------------------------------------------------------------
-------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."ID"=VALUE(KOKBF$))
21 rows selected.
In place of
select t1.* from t1 where id in (1,2,3,4);
and val_list.f_trans is a packaged pipeline function that turns a single
column separated set of numbers into a result set of numbers. Now please do
notice that when you do this sort of thing Oracle uses rules rather than
facts to take a stab at the cardinality as expressed by the E-Rows value
being 8168 but the actual value being 4 for cardinality (the number of
entries in the in list). Of course there is no way for Oracle to interpret
what your function is going to produce, so it has to use a rule. If you're
up to a release where cardinality feedback is operational, a second run of
the query would produce the correct estimate (4). On my todo list is
figuring out when that feedback gets produced. If it is preserved early in
row source projections, it might be possible to use a cancel query on a
lousy plan and then resubmit and get feedback cardinality for the row
sources that have been produced (which might produce a better plan.) That
last is mostly a research reminder to me.
Good luck,
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Chitale, Hemant Krishnarao
Sent: Friday, October 28, 2011 4:50 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Large IN LIST in an OBIEE query
I have a few OBIEE queries that "perform poorly". Apparently, the users are
allowed to "insert" a list of values to query for. OBIEE then constructs
the query with a large IN LIST.
If I move the IN LIST values into a temporary table and then join the
temporary table, I get better performance. However, making this change in
OBIEE requires a change to the OBIEE data model.
My questions :
a. Have you come across performance issues with very large IN
LISTS (1000 entries)
b. If you raise a request to change the OBIEE data model, what is
the level of resistance you face ? Are the OBIEE designers comfortable with
making changes to add an interim table and join OR is this difficult ?
Hemant K Chitale
This email and any attachments are confidential and may also be privileged.
If you are not the addressee, do not disclose, copy, circulate or in any
other way use or rely on the information contained in this email or any
attachments. If received in error, notify the sender immediately and delete
this email and any attachments from your system. Emails cannot be
guaranteed to be secure or error free as the message and any attachments
could be intercepted, corrupted, lost, delayed, incomplete or amended.
Standard Chartered PLC and its subsidiaries do not accept liability for
damage caused by this email or any attachments and may monitor email
traffic.
Standard Chartered PLC is incorporated in England with limited liability
under company number 966425 and has its registered office at 1 Aldermanbury
Square, London, EC2V 7SB.
Standard Chartered Bank ("SCB") is incorporated in England with limited
liability by Royal Charter 1853, under reference ZC18. The Principal Office
of SCB is situated in England at 1 Aldermanbury Square, London EC2V 7SB. In
the United Kingdom, SCB is authorised and regulated by the Financial
Services Authority under FSA register number 114276.
If you are receiving this email from SCB outside the UK, please click
http://www.standardchartered.com/global/email_disclaimer.html to refer to
the information on other jurisdictions.
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
