Oracle query plan efficiency problem

Question

The following query is given in a PL/SQL procedure.

SELECT e.data FROM extra e WHERE e.external_id in
    (SELECT * FROM TABLE (p_external_ids)).

The type of p_external_ids is create or replace type "VARCHAR2TABLE" as table of VARCHAR2(4000 CHAR).

Oracle inefficiently executes the query using a full table scan. Hints on query did not help and necessary indexes are in place. Replacing the SELECT * part with hardcoded ids reduce query running time by a factor of 20, when the number of rows in the table is 200 000.

For reference it takes about 0.3 sec to execute with SELECT * FROM TABLE clause, and around 0.015 ms for a single hardcoded id.

What are the suggested efficient ways (key search) to write a stored procedure to extract the data from the table for multiple ids? The provided collection type must be used to pass in the list of ids to a stored procedure.

Answer 1

This question is answered in a very satisfying way on setting cardinality for pipelined and table functions, so please go and read the full article!

---

Summary:

method: extensible optimiser

The Extensible Optimiser is implemented by an Oracle Data Cartridge (which is essentially an object type, known as an interface type, that contains one or more well-defined and structured methods). This feature enables us to design our own cardinality calculations (as a prescribed method in the interface type) and then associate them with our table or pipelined functions. The type's cardinality method is invoked by the CBO during query optimisation to determine the rowcount for the pipelined or table function.

The following quotes and examples come from the article but are somewhat adapted to answer the question consistently.

1) Make a wrapper function

We will create a small function that will receive and return a collection of our generic VARCHAR2TABLE type. This function does nothing with the collection itself; it is merely a wrapper over it.

SQL> CREATE FUNCTION card_varchar2(
  2                  p_collection IN varchar2table
  3                  ) RETURN varchar2table IS
  4  BEGIN
  5     RETURN p_collection;
  6  END card_varchar2;
  7  /

Function created.

2) Make an interface type

Second, we will create an interface type specification to be associated with our simple card_varchar2 function, as follows.

SQL> CREATE TYPE card_varchar2_ot AS OBJECT (
  2
  3     dummy_attribute NUMBER,
  4
  5     STATIC FUNCTION ODCIGetInterfaces (
  6                     p_interfaces OUT SYS.ODCIObjectList
  7                     ) RETURN NUMBER,
  8
  9     STATIC FUNCTION ODCIStatsTableFunction (
 10                     p_function   IN  SYS.ODCIFuncInfo,
 11                     p_stats      OUT SYS.ODCITabFuncStats,
 12                     p_args       IN  SYS.ODCIArgDescList,
 13                     p_collection IN varchar2table
 14                     ) RETURN NUMBER
 15
 16  );
 17  /

Type created.

and the body

SQL> CREATE TYPE BODY card_varchar2_ot AS
  2
  3     STATIC FUNCTION ODCIGetInterfaces (
  4                     p_interfaces OUT SYS.ODCIObjectList
  5                     ) RETURN NUMBER IS
  6     BEGIN
  7        p_interfaces := SYS.ODCIObjectList(
  8                           SYS.ODCIObject ('SYS', 'ODCISTATS2')
  9                           );
 10        RETURN ODCIConst.success;
 11     END ODCIGetInterfaces;
 12
 13     STATIC FUNCTION ODCIStatsTableFunction (
 14                     p_function   IN  SYS.ODCIFuncInfo,
 15                     p_stats      OUT SYS.ODCITabFuncStats,
 16                     p_args       IN  SYS.ODCIArgDescList,
 17                     p_collection IN  varchar2table
 18                     ) RETURN NUMBER IS
 19     BEGIN
 20        p_stats := SYS.ODCITabFuncStats(p_collection.COUNT);
 21        RETURN ODCIConst.success;
 22     END ODCIStatsTableFunction;
 23
 24  END;
 25  /

Type body created.

3) Associate the function with the interface type, as follows.

SQL> ASSOCIATE STATISTICS WITH FUNCTIONS card_varchar2 USING card_varchar2_ot;

Statistics associated.

4) Use this function now like this:

SQL> SELECT *
  2  FROM   TABLE(card_varchar2('A','B','C'));

Answer 2

What hints did you try? Can you post the fast and the slow query plan?

One of the general issues with using PL/SQL collections in SQL is that the CBO often guesses incorrectly at the number of elements in the collection and chooses the wrong plan as a result. It is often helpful in those cases to use the CARDINALITY hint, i.e.

SELECT e.data 
  FROM extra e
 WHERE e.external_id IN (
    SELECT /*+ cardinality(ids 10) */ *
      FROM TABLE( p_external_ids ) ids
  )

tells the optimizer to expect 10 elements in P_EXTERNAL_IDS.

Tom Kyte has a more in depth discussion about the cardinality hint and PL/SQL collections on askTom as well.

What is the data type of the EXTERNAL_ID column? Your collection is a collection of strings but EXTERNAL_ID tends to imply a NUMBER. Is there really a data type mismatch here?

Copying the collection into a temporary table would only be expected to help if the problem was that the optimizer couldn't get an accurate cardinality estimate when you referenced the collection but it could get an accurate estimate when you referenced the temporary table. If you are correctly specifying the CARDINALITY hint and that doesn't change performance, that would imply that the problem is not with the optimizer's cardinality estimates.

Can you post the fast and the slow query plans? Can you post the exact SQL statement you are using that includes the CARDINALITY hint (perhaps there is a syntax error)

Answer 3

I believe it is doing a full scan because it can't predict if the p_external_ids is going to be larger or smaller than the break even point.

What I mean:

If it costs 200 to do a single index lookup, and 100000 to do a full table scan, if you are looking up 20 values, total cost will be 4000 (less than 100000). But if you are looking up 1000 values, the total cost using the indices would be 200000.