Skip to Main Content
  • Questions
  • Performance problem passing nested table arrays to SELECT sub queries

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, alan.

Asked: September 01, 2015 - 9:20 pm UTC

Last updated: September 21, 2015 - 3:19 am UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

Hi,

In PL/SQL we have procedures that get passed a nested table array. In some cases we want to use the values in this array in a SELECT statement. This technique works but we have found in some cases that the performance can be terrible and can degrade with time. We have changed some of these queries to do the same thing but to use global temporary tables and the performance is very good and consistent. I worked with Oracle support and they believe that we are hitting BUG 14726604 which, briefly, says that Cost-Based Query Transformation is unsupported in queries or sub-queries containing TABLE() expressions, making many more efficient plans unavailable.

My question - is there any "good" way to pass a nested table array of values to a sub-query or what is the best way to pass a "list or array" of values to a sub-query?

We have experimented with:
1 - Dynamic SQL. It works but not practical for large arrays of values.
2 - Permanent tables. Also work but more overhead when loading the table and you have to consider concurrent usage.
3 - Global Temporary Tables. Also work and seem to be the best choice that we can think of given the above mentioned bug.

For an example: the problem usually happened in more complex queries using large data sets where the CBO would need to be able to consider more advanced execution plans. But a very simple example is:

CREATE TYPE id_array AS TABLE OF NUMBER;

DECLARE
ids id_array := id_array();
hits NUMBER;

BEGIN
ids.EXTEND(5);
ids(1) := 356;
ids(2) := 357;
ids(3) := 6530;
ids(4) := 6531;
ids(5) := 7562;

SELECT COUNT(*)
INTO hits
FROM all_objects
WHERE object_id IN (SELECT * FROM TABLE(ids));

DBMS_OUTPUT.PUT_LINE('count is ' || TO_NUMBER(hits));
END;

Any suggestions?

Thanks,
Alan

and Connor said...

Hi Alan,

Cardinality costing for TABLE() has always been a challenge. In most versions of Oracle, you'll often see a default cardinality chosen, for example:

SQL> select * from table(cast(:b1 as id_array));

------------------------------------------------------------------------------------------
| Id  | Operation                         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |      |  8168 | 16336 |    29   (0)| 00:00:01 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH|      |  8168 | 16336 |    29   (0)| 00:00:01 |
------------------------------------------------------------------------------------------


You'll see "8168" pop up often in such queries. Hence when those queries become involved with joins to other structures, the optimizer decisions can be skewed.

Global temporary tables (GTT) are a common (and good) means to try tackle it, but it does still leave you with a costing issue, eg, one session using a GTT might put 1000 rows in there, whilst another session might be only 2 rows in there. Hence if you persist some statistics on the GTT in the dictionary, then someone will be getting the wrong stats. An alternative is to look at dynamic sampling for such queries.

Ultimately, a good place you really want to be for this is on version 12. For example, the execution plan for your query in 12c looks like this:

SELECT COUNT(*) 
FROM
 ALL_OBJECTS A WHERE OBJECT_ID IN (SELECT * FROM TABLE(:B1 ))


Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  SORT AGGREGATE (cr=30 pr=0 pw=0 time=1491 us)
         5          5          5   FILTER  (cr=30 pr=0 pw=0 time=1481 us)
         5          5          5    HASH JOIN  (cr=12 pr=0 pw=0 time=1310 us cost=34 size=123 card=1)
         5          5          5     NESTED LOOPS  (cr=11 pr=0 pw=0 time=1059 us cost=34 size=123 card=1)
         5          5          5      STATISTICS COLLECTOR  (cr=11 pr=0 pw=0 time=1056 us)
         5          5          5       HASH JOIN  (cr=11 pr=0 pw=0 time=940 us cost=33 size=119 card=1)
         5          5          5        NESTED LOOPS  (cr=10 pr=0 pw=0 time=233 us cost=33 size=119 card=1)
         5          5          5         STATISTICS COLLECTOR  (cr=10 pr=0 pw=0 time=230 us)
         5          5          5          HASH JOIN  (cr=10 pr=0 pw=0 time=120 us cost=32 size=106 card=1)
         5          5          5           NESTED LOOPS  (cr=10 pr=0 pw=0 time=112 us cost=32 size=106 card=1)
         5          5          5            STATISTICS COLLECTOR  (cr=0 pr=0 pw=0 time=40 us)
         5          5          5             SORT UNIQUE (cr=0 pr=0 pw=0 time=26 us cost=29 size=10 card=5)
         5          5          5              COLLECTION ITERATOR PICKLER FETCH (cr=0 pr=0 pw=0 time=8 us cost=29 size=10 card=5)
         5          5          5            TABLE ACCESS BY INDEX ROWID BATCHED OBJ$ (cr=10 pr=0 pw=0 time=64 us cost=2 size=104 card=1)
[snip]


See the new "STATISTICS COLLECTOR" line - at run time, we are making a decision based on the number of rows we *really* found in the collection, and using that to try make a better optimizer decision.

Similarly, if you went the way of GTT's, 12c can let you associate session-level statistics for GTT which helps a lot.

In the interim, Adrian Billington wrote a nice little utility to tinker with cardinalities with collections. Check it out here:

http://www.oracle-developer.net/content/utilities/ccard.sql

Rating

  (1 rating)

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

Comments

Do you think there will be the same bug in 12c?

alan rosenthal, September 09, 2015 - 9:04 pm UTC

Hi,

Thanks for the great answer. My understanding is that my use of the TABLE() function in 11g had two issues.

First, the statistics were wrong for the function. As you said, this could be fixed with the extensible optimizer or with the STATISTICS COLLECTION step in 12c.

But the second issue involved BUG 14726604 wherein the TABLE() function causes the CBO to ignore many possibilities when building the plan. For complex queries this was causing poor plans to be developed.

Do you think this problem with the CBO will not happen in 12c - possible because of the improved statistics? I looked on MOS and saw no indication that this bug was fixed in any release. In fact, it seemed that this wasn't so much of a bug as a known limitation in functionality of the CBO.

Thanks,
Alan
Connor McDonald
September 21, 2015 - 3:19 am UTC

Sorry, I couldnt see any details on this bug.

All I could suggest is that (if possible) you could materialize the TABLE() output either explicitly into a global temporary table, or using WITH and a 'materialize' hint, and see if the CBO can then do a better job with that.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library