Re: Predicates are not being pushed if casting PL/SQL table
Date: Sat, 5 Sep 2015 09:13:06 +0800
Message-ID: <CABx0cSVt8-ZgJmUnCA09kdAJwr-6Gch9hRJk2skbumHEETzTqA_at_mail.gmail.com>
Ah, OK, just realized trace line is referring to the un-nesting of the "SELECT FROM TABLE()", not the SELECT DISTINCT OBJECT_ID.
On 5 September 2015 at 07:46, Patrick Jolliffe <jolliffe_at_gmail.com> wrote:
> Thanks a lot, so seems it's not about predicates being pushed into
> subquery, but subquery cannot be un-nested.
> I wonder why that is, what is it about the outer query having TABLE
> expression would prevent un-nesting this subquery?
> The two things seem un-related to me.
> So I could consider attempting to un-nest the subquery manually myself (of
> course with real-life example, logic is much more complex)?
> Any other ideas? Otherwise may need to consider allowing them to dump
> contents to GTT to join against.
> Note interestingly test-case does run fine on my system, (maybe database
> doesn't check column alias of TABLE or something else I've missed)
>
> On 4 September 2015 at 22:51, Dominic Brooks <dombrooks_at_hotmail.com>
> wrote:
>
>> 10053 trace says "CBQT: copy not possible ... because TABLE expression".
>>
>> So subquery does not get unnested.
>>
>> Sent from my Windows Phone
>> ------------------------------
>> From: Dominic Brooks <dombrooks_at_hotmail.com>
>> Sent: 04/09/2015 13:22
>>
>> To: jolliffe_at_gmail.com; oracle-l <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 <dombrooks_at_hotmail.com>
>> Sent: 04/09/2015 13:08
>> To: jolliffe_at_gmail.com; oracle-l <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 <jolliffe_at_gmail.com>
>> Sent: 04/09/2015 11:01
>> To: oracle-l <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 Sat Sep 05 2015 - 03:13:06 CEST