RE: Large IN LIST in an OBIEE query

From: Michael Dinh <mdinh_at_XIFIN.Com>
Date: Sat, 29 Oct 2011 08:49:47 -0700
Message-ID: <D29F9902E534D5478F2E83FD6A44B30648ED20D507_at_mail02.mba.xifin.com>


  1. 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_at_freelists.org [oracle-l-bounce_at_freelists.org] On Behalf Of Mark W. Farnham [mwf_at_rsiz.com] Sent: Saturday, October 29, 2011 7:23 AM To: Hemant.Chitale_at_sc.com; oracle-l_at_freelists.org Subject: RE: Large IN LIST in an OBIEE query
  1. 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.
  2. 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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Chitale, Hemant Krishnarao
Sent: Friday, October 28, 2011 4:50 AM
To: oracle-l_at_freelists.org
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.

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


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


--
http://www.freelists.org/webpage/oracle-l
Received on Sat Oct 29 2011 - 10:49:47 CDT

Original text of this message