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

From: Timur Akhmadeev <timur.akhmadeev_at_gmail.com>
Date: Mon, 7 Sep 2015 10:25:05 +0300
Message-ID: <CACGsLCKtTvw9HFMEeVB+6b0ynjvMpzPHjS_gWfC5p0xQbqENNw_at_mail.gmail.com>

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_at_gmail.com> 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_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
>>> >
>>> >
>>>
>>
>>
>

-- 
Regards
Timur Akhmadeev

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

Received on Mon Sep 07 2015 - 09:25:05 CEST