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

  • From: Timur Akhmadeev <timur.akhmadeev@xxxxxxxxx>
  • To: jolliffe@xxxxxxxxx
  • Date: Mon, 7 Sep 2015 10:25:05 +0300

Oracle can't do complex view merging with TABLE expressions for some reason
- probably a bug and you should ask Support about it. Easy to re-write
manually if possible. Not sure how you are supposed to allow developers to
"fix" it with GTT but don't allow to re-write SQL.
By the way if you are binding an array in run time you most likely don't
need cardinality hint if array size is usually reasonable - with bind
peeking Oracle sees its elements count and uses it for building a plan.

On Mon, Sep 7, 2015 at 9:53 AM, Patrick Jolliffe <jolliffe@xxxxxxxxx> wrote:

With named query blocks for ease of reading, here is the 10053 trace from
11.2.0.4.

SELECT /*+ gather_plan_statistics QB_NAME(OUTER_QB) */ null
FROM (SELECT /*+ QB_NAME(DISTINCT_QB) */ DISTINCT OBJECT_ID FROM
TEST_OBJECTS)
WHERE OBJECT_ID IN (SELECT /*+ CARDINALITY(GTT_TEST_OBJECTS 1)
QB_NAME(PARAMETER_QB) */ column_value FROM GTT_TEST_OBJECTS);

CBQT: Validity checks passed for f0mm8yy66dn4k.


SELECT /*+ gather_plan_statistics QB_NAME(OUTER_QB) */ null
FROM (SELECT /*+ QB_NAME(DISTINCT_QB) */ DISTINCT OBJECT_ID FROM
TEST_OBJECTS)
WHERE OBJECT_ID IN (SELECT /*+ CARDINALITY(GTT_TEST_OBJECTS 1)
QB_NAME(PARAMETER_QB) */ column_value FROM TABLE(NUMBER_TABLE(1))
GTT_TEST_OBJECTS);

CBQT: copy not possible on query block PARAMETER_QB (#0) because TABLE
expression
CBQT: copy not possible on query block OUTER_QB (#0) because inner query
block constraints
CBQT bypassed for query block OUTER_QB (#0): Cannot copy query block.
CBQT: Validity checks failed for 7cm0hvh8n5m94.

On 7 September 2015 at 13:48, Patrick Jolliffe <jolliffe@xxxxxxxxx> wrote:

Thanks both, and sorry to waste your time on a bad test case. For you
information, I have fixed (hopefully) test case, and still get same
behaviour.
Yes, will drill into 10053 trace and see if I can get any further hints.
By the way, and sorry for not mentioning before, this is on 11.2.0.4.


SQL> DROP TABLE
TEST_OBJECTS;



Table
dropped.



SQL> DROP TABLE
GTT_TEST_OBJECTS;



Table
dropped.



SQL> CREATE TABLE TEST_OBJECTS AS SELECT * FROM
DBA_OBJECTS;



Table
created.



SQL> CREATE INDEX TEST_OBJECTS_IDX ON
TEST_OBJECTS(OBJECT_ID);



Index
created.



SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(NULL,
'TEST_OBJECTS');



PL/SQL procedure successfully
completed.



SQL>

SQL> CREATE OR REPLACE TYPE NUMBER_TABLE IS TABLE OF
NUMBER;

2
/



Type
created.



SQL> CREATE GLOBAL TEMPORARY TABLE GTT_TEST_OBJECTS (column_value
number);



Table
created.



SQL> INSERT INTO GTT_TEST_OBJECTS2 VALUES
(1);



1 row
created.



SQL>

SQL>

SQL> SELECT /*+ gather_plan_statistics */
null

2 FROM (SELECT DISTINCT OBJECT_ID FROM
TEST_OBJECTS)

3 WHERE OBJECT_ID IN (SELECT /*+ CARDINALITY(GTT_TEST_OBJECTS 1) */
column_value FROM
GTT_TEST_OBJECTS);



no rows
selected



SQL>

SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format=>'ALLSTATS
LAST'));



PLAN_TABLE_OUTPUT


------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------

SQL_ID 00ggxzya0832u, child number
0

-------------------------------------

SELECT /*+ gather_plan_statistics */ null FROM (SELECT
DISTINCT

OBJECT_ID FROM TEST_OBJECTS) WHERE OBJECT_ID IN (SELECT
/*+

CARDINALITY(GTT_TEST_OBJECTS 1) */ column_value FROM
GTT_TEST_OBJECTS)



Plan hash value:
1576474484



---------------------------------------------------------------------------------------------------------------------

| Id | Operation | Name | Starts | E-Rows |
A-Rows | A-Time | OMem | 1Mem | Used-Mem
|

---------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | |
0 |00:00:00.01 | | |
|

| 1 | VIEW | VM_NWVW_1 | 1 | 1 |
0 |00:00:00.01 | | |
|

| 2 | SORT UNIQUE NOSORT | | 1 | 1 |
0 |00:00:00.01 | | |
|

| 3 | NESTED LOOPS | | 1 | 1 |
0 |00:00:00.01 | | |
|

| 4 | SORT UNIQUE | | 1 | 1 |
0 |00:00:00.01 | 1024 | 1024 |
|

| 5 | TABLE ACCESS FULL| GTT_TEST_OBJECTS | 1 | 1 |
0 |00:00:00.01 | | |
|

|* 6 | INDEX RANGE SCAN | TEST_OBJECTS_IDX | 0 | 1 |
0 |00:00:00.01 | | |
|

---------------------------------------------------------------------------------------------------------------------



Predicate Information (identified by operation
id):

---------------------------------------------------



6 -
access("OBJECT_ID"="COLUMN_VALUE")





25 rows
selected.



SQL>

SQL> SELECT /*+ gather_plan_statistics */
null

2 FROM (SELECT DISTINCT OBJECT_ID FROM
TEST_OBJECTS)

3 WHERE OBJECT_ID IN (SELECT /*+ CARDINALITY(GTT_TEST_OBJECTS 1) */
column_value FROM TABLE(NUMBER_TABLE(1))
GTT_TEST_OBJECTS);



no rows
selected



SQL>

SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format=>'ALLSTATS
LAST'));



PLAN_TABLE_OUTPUT


------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------

SQL_ID 33cgqar810vch, child number
0

-------------------------------------

SELECT /*+ gather_plan_statistics */ null FROM (SELECT
DISTINCT

OBJECT_ID FROM TEST_OBJECTS) WHERE OBJECT_ID IN (SELECT
/*+

CARDINALITY(GTT_TEST_OBJECTS 1) */ column_value
FROM

TABLE(NUMBER_TABLE(1))
GTT_TEST_OBJECTS)



Plan hash value:
1401649423



---------------------------------------------------------------------------------------------------------------------------------------------

| Id | Operation | Name | Starts |
E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem
|
---------------------------------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1
| | 0 |00:00:00.18 | 627 | | |
|
| 1 | MERGE JOIN SEMI | | 1
| 1 | 0 |00:00:00.18 | 627 | | |
|
| 2 | SORT JOIN | | 1
| 119K| 1 |00:00:00.18 | 627 | 2887K| 760K| 2566K
(0)|

| 3 | VIEW | | 1
| 119K| 119K|00:00:00.18 | 627 | | |
|
| 4 | HASH UNIQUE | | 1
| 119K| 119K|00:00:00.13 | 627 | 6695K| 2749K| 4623K
(0)|

| 5 | TABLE ACCESS FULL | TEST_OBJECTS | 1
| 119K| 119K|00:00:00.04 | 627 | | |
|
|* 6 | SORT UNIQUE | | 1
| 1 | 0 |00:00:00.01 | 0 | 2048 | 2048 | 2048
(0)|

| 7 | COLLECTION ITERATOR CONSTRUCTOR FETCH| | 1
| 1 | 1 |00:00:00.01 | 0 | | |
|
---------------------------------------------------------------------------------------------------------------------------------------------



Predicate Information (identified by operation
id):

---------------------------------------------------



6 -
access("OBJECT_ID"=VALUE(KOKBF$))


filter("OBJECT_ID"=VALUE(KOKBF$))





28 rows
selected.



On 5 September 2015 at 23:25, Dominic Brooks <dombrooks@xxxxxxxxxxx>
wrote:

Stefan explained it a lot better than I did.
Even with the column reference corrected, I believe that the TABLE
subquery still won't be unnested.
e.g.

SELECT null
FROM (SELECT DISTINCT OBJECT_ID FROM TEST_OBJECTS)
WHERE OBJECT_ID IN (SELECT VALUE(t) FROM TABLE(NUMBER_TABLE(1)) t);

But it could be rewritten to avoid the subquery.
Something like (untested):

SELECT DISTINCT obj.OBJECT_ID
FROM TEST_OBJECTS obj
, TABLE(NUMBER_TABLE(1)) t
WHERE obj.OBJECT_ID = VALUE(t) ;

Date: Sat, 5 Sep 2015 12:18:17 +0200
From: contact@xxxxxxxx
To: oracle-l@xxxxxxxxxxxxx; dombrooks@xxxxxxxxxxx; jolliffe@xxxxxxxxx
Subject: RE: Predicates are not being pushed if casting PL/SQL table


Hi Patrick,
picking up Dominic's comment and your replies. Both test cases are not
equivalent (once i get 90131 rows and with the other 0 rows).


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?

Here we go with a 12.1.0.1 CBO trace for your "TABLE(NUMBER_TABLE(1)"
example.
--------------------8<------------------------------------

SU - subquery unnesting
FPD - filter push-down
CVM - complex view merging
SPJ - select-project-join

CVM: Merging SPJ view SEL$4 (#0) into SEL$3 (#0)
Registered qb: SEL$07BDC5B4 0x6f6581a8 (VIEW MERGE SEL$3; SEL$4)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$07BDC5B4 nbfros=1 flg=0
fro(0): flg=0 objn=0 hint_alias="KOKBF$0"@"SEL$4"

CBQT: copy not possible on query block SEL$07BDC5B4 (#0) because
nested table
CBQT: copy not possible on query block SEL$1 (#0) because inner query
block constraints
CBQT bypassed for query block SEL$1 (#0): Cannot copy query block.
CBQT: Validity checks failed for 0u15kkytnwaqm.

SU: Checking validity of unnesting subquery SEL$07BDC5B4 (#0)
SU: SU bypassed: Invalid correlated predicates.
SU: Validity checks failed.

FPD: Considering simple filter push in query block SEL$1 (#0)
EXISTS (SELECT 0 FROM TABLE("NUMBER_TABLE"(1)) "KOKBF$0")
FPD: Considering simple filter push in query block SEL$07BDC5B4 (#0)
:B1=:B2
try to generate transitive predicate from check constraints for query
block SEL$07BDC5B4 (#0)
finally: :B1=:B2
try to generate transitive predicate from check constraints for query
block SEL$1 (#0)
finally: EXISTS (SELECT 0 FROM TABLE("NUMBER_TABLE"(1)) "KOKBF$0")

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT NULL "NULL" FROM (SELECT DISTINCT "TEST_OBJECTS"."OBJECT_ID"
"OBJECT_ID" FROM "TEST"."TEST_OBJECTS" "TEST_OBJECTS") "from$_subquery$_001"
WHERE EXISTS (SELECT 0 FROM TABLE("NUMBER_TABLE"(1)) "KOKBF$0" WHERE
"from$_subquery$_001"."OBJECT_ID"="from$_subquery$_001"."OBJECT_ID")

--------------------8<------------------------------------

You can see the reason for it ("SU: SU bypassed: Invalid correlated
predicates.") and if you check the predicate section you gonna see that
your IN
query was transformed to an EXISTS with the correlated predicate
"from$_subquery$_001"."OBJECT_ID"="from$_subquery$_001"."OBJECT_ID". This
is also
what Dominic means with "Therefore object_id resolves to object_id
column from your distinct inline view".


In contrast to that here is the snippet from your GTT_TEST_OBJECTS
example (also on 12.1.0.1).
--------------------8<------------------------------------

SELECT NULL "NULL" FROM (SELECT DISTINCT "TEST_OBJECTS"."OBJECT_ID"
"OBJECT_ID" FROM "TEST"."TEST_OBJECTS" "TEST_OBJECTS") "from$_subquery$_001"
WHERE "from$_subquery$_001"."OBJECT_ID"=ANY (SELECT /*+ OPT_ESTIMATE
(TABLE "GTT_TEST_OBJECTS"@"SEL$3" ROWS=1.000000 ) */
"GTT_TEST_OBJECTS"."OBJECT_ID" "OBJECT_ID" FROM
"TEST"."GTT_TEST_OBJECTS" "GTT_TEST_OBJECTS")

SU: Unnesting query blocks in query block SEL$1 (#1) that are valid to
unnest.
Subquery removal for query block SEL$3 (#2)
RSW: Not valid for subquery removal SEL$3 (#2)
Subquery unchanged.
Subquery Unnesting on query block SEL$1 (#1)SU: Performing unnesting
that does not require costing.
SU: Considering subquery unnest on query block SEL$1 (#1).
SU: Checking validity of unnesting subquery SEL$3 (#2)
SU: Passed validity checks.
SU: Transforming ANY subquery to a join.

--------------------8<------------------------------------

Best Regards
Stefan Koehler

Freelance Oracle performance consultant and researcher
Homepage: http://www.soocs.de
Twitter: @OracleSK


Dominic Brooks <dombrooks@xxxxxxxxxxx> hat am 4. September 2015 um
14:08 geschrieben:

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
--
//www.freelists.org/webpage/oracle-l








--
Regards
Timur Akhmadeev

Other related posts: