RE: Predicates are not being pushed if casting PL/SQL table
Date: Fri, 4 Sep 2015 15:51:00 +0100
Message-ID: <DUB402-EAS334794168A187381231D4F0A1570_at_phx.gbl>
10053 trace says "CBQT: copy not possible ... because TABLE expression".
So subquery does not get unnested.
Sent from my Windows Phone
From: Dominic Brooks<mailto:dombrooks_at_hotmail.com> Sent: 04/09/2015 13:22
To: jolliffe_at_gmail.com<mailto:jolliffe_at_gmail.com>; oracle-l<mailto:oracle-l_at_freelists.org> Subject: RE: Predicates are not being pushed if casting PL/SQL table
Once the test case error is addressed, try a 10053 trace to see if there's any explanation.
Sent from my Windows Phone
From: Dominic Brooks<mailto:dombrooks_at_hotmail.com> Sent: 04/09/2015 13:08
To: jolliffe_at_gmail.com<mailto:jolliffe_at_gmail.com>; oracle-l<mailto:oracle-l_at_freelists.org> Subject: RE: Predicates are not being pushed if casting PL/SQL table
Does the test case have an error?
Column name from collection is not "object_id" but value(alias) or column_value.
Therefore object_id resolves to object_id column from your distinct inline view
Sent from my Windows Phone
From: Patrick Jolliffe<mailto:jolliffe_at_gmail.com> Sent: 04/09/2015 11:01
To: oracle-l<mailto:oracle-l_at_freelists.org> Subject: Predicates are not being pushed if casting PL/SQL table
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)
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Sep 04 2015 - 16:51:00 CEST