Re: Predicates are not being pushed if casting PL/SQL table

  • From: Patrick Jolliffe <jolliffe@xxxxxxxxx>
  • To: Dominic Brooks <dombrooks@xxxxxxxxxxx>
  • Date: Sat, 5 Sep 2015 07:46:43 +0800

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@xxxxxxxxxxx> 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@xxxxxxxxxxx>
Sent: ‎04/‎09/‎2015 13:22

To: jolliffe@xxxxxxxxx; oracle-l <oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxxxx>
Sent: ‎04/‎09/‎2015 13:08
To: jolliffe@xxxxxxxxx; oracle-l <oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxx>
Sent: ‎04/‎09/‎2015 11:01
To: oracle-l <oracle-l@xxxxxxxxxxxxx>
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)






Other related posts: