Some vendors have developed a stored procedure that we have been looking to
tune.
Vendor has identified that rather than casting PL/SQL collection using
SELECT * FROM TABLE() syntax, that if they dump the contents to temporary
table then performance is better (uses a suitable index).
I have simplified their construct to the simplest possible test case I can
at the moment. Unfortunately re-writing their SQL construct is not a
possibility at the moment.
It seems somehow to me that when casting the PL/SQL collection, then the
predicate is not getting passed to the subquery.
Does anybody have any ideas about what is going on. Is this expected
behviour, or a known bug?
Any ideas how to work around it? Very hesitant to allow them to be dumping
data into temporary tables for getting better explain plans, but a little
difficult to argue against their apparent peformance benefits.
Thanks in advance, paste below steps to reproduce.
Note the cardinality hint makes it simpler to reproduce, otherwise it takes
a couple of executions before cardinality feedback kicks in to reduce
initial estimated cardinality of 8192 (from either the collection or GTT).
CREATE TABLE TEST_OBJECTS AS SELECT * FROM DBA_OBJECTS;
CREATE INDEX TEST_OBJECTS_IDX ON TEST_OBJECTS(OBJECT_ID);
EXEC DBMS_STATS.GATHER_TABLE_STATS(NULL, 'TEST_OBJECTS');
CREATE GLOBAL TEMPORARY TABLE GTT_TEST_OBJECTS (object_id number);
CREATE OR REPLACE TYPE NUMBER_TABLE IS TABLE OF NUMBER;
INSERT INTO GTT_TEST_OBJECTS VALUES (1);
COMMIT;
EXPLAIN PLAN FOR
SELECT /*+ gather_plan_statistics */ null
FROM (SELECT DISTINCT OBJECT_ID FROM TEST_OBJECTS)
WHERE OBJECT_ID IN (SELECT /*+ CARDINALITY(GTT_TEST_OBJECTS 1) */ object_id
FROM GTT_TEST_OBJECTS);
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time
|
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 27
(8)| 00:00:01
|
| 1 | VIEW | VM_NWVW_1 | 1 | | 27
(8)| 00:00:01
|
| 2 | SORT UNIQUE NOSORT | | 1 | 19 | 27
(8)| 00:00:01
|
| 3 | NESTED LOOPS | | 1 | 19 | 26
(4)| 00:00:01
|
| 4 | SORT UNIQUE | | 1 | 13 | 24
(0)| 00:00:01
|
| 5 | TABLE ACCESS FULL| GTT_TEST_OBJECTS | 1 | 13 | 24
(0)| 00:00:01
|
|* 6 | INDEX RANGE SCAN | TEST_OBJECTS_IDX | 1 | 6 | 1
(0)| 00:00:01
|
------------------------------------------------------------------------------------------
Predicate Information (identified by operation
id):
---------------------------------------------------
6 - access("OBJECT_ID"="OBJECT_ID")
EXPLAIN PLAN FOR
SELECT /*+ gather_plan_statistics */ null
FROM (SELECT DISTINCT OBJECT_ID FROM TEST_OBJECTS)
WHERE OBJECT_ID IN (SELECT /*+ CARDINALITY(GTT_TEST_OBJECTS 1) */ object_id
FROM TABLE(NUMBER_TABLE(1)));
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows |
Bytes |TempSpc| Cost (%CPU)| Time
|
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5967 |
35802 | | 220K (1)| 00:44:08
|
|* 1 | FILTER | |
| | | |
|
| 2 | VIEW | | 119K|
699K| | 542 (3)| 00:00:07
|
| 3 | HASH UNIQUE | | 119K|
699K| 1416K| 542 (3)| 00:00:07
|
| 4 | TABLE ACCESS FULL | TEST_OBJECTS | 119K|
699K| | 145 (3)| 00:00:02
|
|* 5 | FILTER | |
| | | |
|
| 6 | COLLECTION ITERATOR CONSTRUCTOR FETCH| | 1
| | | 2 (0)| 00:00:01
|
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation
id):
---------------------------------------------------
1 - filter( EXISTS (SELECT 0 FROM TABLE() "KOKBF$0" WHERE
:B1=:B2))
5 -
filter(:B1=:B2)
