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

From: Patrick Jolliffe <jolliffe_at_gmail.com>
Date: Mon, 7 Sep 2015 14:53:28 +0800
Message-ID: <CABx0cSX_6z2vHPh3foVXpxX-aX-tbMP0T=jEgQNO+25PHQriJQ_at_mail.gmail.com>

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_at_gmail.com> 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_at_hotmail.com>
> 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_at_soocs.de
>> > To: oracle-l_at_freelists.org; dombrooks_at_hotmail.com; jolliffe_at_gmail.com
>> > 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"_at_"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"_at_"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: _at_OracleSK
>> >
>> >
>> > > Dominic Brooks <dombrooks_at_hotmail.com> 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
>> > --
>> > http://www.freelists.org/webpage/oracle-l
>> >
>> >
>>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Sep 07 2015 - 08:53:28 CEST