Home » SQL & PL/SQL » SQL & PL/SQL » Interesting explain plan output/puzzle (11.2.0.4.0)
Interesting explain plan output/puzzle [message #639356] Mon, 06 July 2015 07:46 Go to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Hi all,

After extensive deliberation and deciding that no, I'm not going mad after all, I gift you this puzzle:

Toggle Spoiler


It's a big explain plan under the spoiler.

Look at predicate information, 4...then contrast with line 4 of the sql query.


Please ignore the fact it's an obvious many:many product. I've trimmed down the main block to just this one join field.

Things to note:
Field is nullable in both tables
No referential integrity exists
No applicable check constraints exist
They are heap tables
The index FFS is allowable because it is a composite featuring begin_dt with other columns which are not nullable.

I do seem to get the right data out, but the plan is...well...see for yourselves.


Has anyone seen this? I'm quite prepared to accept it's an inspired rewrite/bugs in dbms_xplan but it's very curious regardless.

[Updated on: Mon, 06 July 2015 08:13]

Report message to a moderator

Re: Interesting explain plan output/puzzle [message #639468 is a reply to message #639356] Wed, 08 July 2015 05:01 Go to previous messageGo to next message
Alien
Messages: 292
Registered: June 1999
Senior Member
Hi,

I think it is a smart rewrite.
Since you are not yet joining, but only generating the rowsource. The a.begin_date cannot be null, because it will not result in a match on the join.

For instance: 
Table a
absv_accrual_dt   begin_dt
1-jan-15          <NULL>
2-jan-15          3-jan-15

Table l
begin_dt
4-jan-15
3-jan-15


The first record in table a will not be in the output. So there is no use getting it from the index.
We will only get null values for the a.begin_dt on the outer join, which is row 1.

Regards,

Arian

Report message to a moderator

Re: Interesting explain plan output/puzzle [message #639470 is a reply to message #639468] Wed, 08 July 2015 05:14 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Yeah I considered that, however didn't think it was that obvious because you never see this on regular outer joins, it's unfamiliar which was why my eye caught it.

And to further complicate it, it appears field specific.

Note the changed join field - but the same index is viable. This field meets all the same criteria in terms of nullability/datatype as the original, yet the predicate isn't shoved across.

This first plan is what I'd expect from a standard outer join.

11:06:36 SQL> l
  1  explain plan for
  2    select a.absv_accrual_dt
  3    from publisher_cdc.ps_sky_hol_ee_rqst_ct l
  4    left join sysadm.ps_audit_hol_ee_rq a
  5*   on l.absv_accrual_dt = a.absv_accrual_dt
11:06:37 SQL> /

Explained.

Elapsed: 00:00:00.00
11:06:38 SQL> @x

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1137895957

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name                  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                       |    42T|   616T|       |   206M(100)|688:43:14 |       |       |
|*  1 |  HASH JOIN OUTER        |                       |    42T|   616T|    80M|   206M(100)|688:43:14 |       |       |
|   2 |   PARTITION RANGE SINGLE|                       |  4239K|    32M|       | 95482   (1)| 00:19:06 |     1 |     1 |
|   3 |    TABLE ACCESS FULL    | PS_SKY_HOL_EE_RQST_CT |  4239K|    32M|       | 95482   (1)| 00:19:06 |     1 |     1 |
|   4 |   INDEX FAST FULL SCAN  | AUDIT_HOL_EE_RQ1      |    88M|   672M|       |   198K  (1)| 00:39:43 |       |       |
-------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$9E43CB6E
   3 - SEL$9E43CB6E / L@SEL$2
   4 - SEL$9E43CB6E / A@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      USE_HASH(@"SEL$9E43CB6E" "A"@"SEL$1")
      LEADING(@"SEL$9E43CB6E" "L"@"SEL$2" "A"@"SEL$1")
      INDEX_FFS(@"SEL$9E43CB6E" "A"@"SEL$1" ("PS_AUDIT_HOL_EE_RQ"."EMPLID" "PS_AUDIT_HOL_EE_RQ"."EMPL_RCD"
              "PS_AUDIT_HOL_EE_RQ"."COMPANY" "PS_AUDIT_HOL_EE_RQ"."PLAN_TYPE" "PS_AUDIT_HOL_EE_RQ"."ABSV_ACCRUAL_DT"
              "PS_AUDIT_HOL_EE_RQ"."BEGIN_DT" "PS_AUDIT_HOL_EE_RQ"."SEQNUM"))
      FULL(@"SEL$9E43CB6E" "L"@"SEL$2")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      MERGE(@"SEL$1")
      OUTLINE(@"SEL$58A6D7F6")
      OUTLINE(@"SEL$3")
      MERGE(@"SEL$58A6D7F6")
      OUTLINE_LEAF(@"SEL$9E43CB6E")
      ALL_ROWS
      DB_VERSION('11.2.0.4')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("L"."ABSV_ACCRUAL_DT"="A"."ABSV_ACCRUAL_DT"(+))

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=1) "A"."ABSV_ACCRUAL_DT"[DATE,7]
   2 - "L"."ABSV_ACCRUAL_DT"[DATE,7]
   3 - "L"."ABSV_ACCRUAL_DT"[DATE,7]
   4 - "A"."ABSV_ACCRUAL_DT"[DATE,7]

56 rows selected.

Elapsed: 00:00:00.51



If we add BOTH predicates in, we see the optimizer will still only push the strange predicate over on begin_dt.

11:07:33 SQL> l
  1  explain plan for
  2    select a.absv_accrual_dt
  3    from publisher_cdc.ps_sky_hol_ee_rqst_ct l
  4    left join sysadm.ps_audit_hol_ee_rq a
  5    on l.absv_accrual_dt = a.absv_accrual_dt
  6*   and l.begin_dt = a.begin_dt
11:07:33 SQL> /

Explained.

Elapsed: 00:00:00.00
11:07:35 SQL> @x

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1137895957

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name                  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                       |  2294M|    57G|       |   373K  (4)| 01:14:39 |       |       |
|*  1 |  HASH JOIN OUTER        |                       |  2294M|    57G|   101M|   373K  (4)| 01:14:39 |       |       |
|   2 |   PARTITION RANGE SINGLE|                       |  4239K|    52M|       | 95487   (1)| 00:19:06 |     1 |     1 |
|   3 |    TABLE ACCESS FULL    | PS_SKY_HOL_EE_RQST_CT |  4239K|    52M|       | 95487   (1)| 00:19:06 |     1 |     1 |
|*  4 |   INDEX FAST FULL SCAN  | AUDIT_HOL_EE_RQ1      |    50M|   680M|       |   198K  (1)| 00:39:44 |       |       |
-------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$9E43CB6E
   3 - SEL$9E43CB6E / L@SEL$2
   4 - SEL$9E43CB6E / A@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      USE_HASH(@"SEL$9E43CB6E" "A"@"SEL$1")
      LEADING(@"SEL$9E43CB6E" "L"@"SEL$2" "A"@"SEL$1")
      INDEX_FFS(@"SEL$9E43CB6E" "A"@"SEL$1" ("PS_AUDIT_HOL_EE_RQ"."EMPLID" "PS_AUDIT_HOL_EE_RQ"."EMPL_RCD"
              "PS_AUDIT_HOL_EE_RQ"."COMPANY" "PS_AUDIT_HOL_EE_RQ"."PLAN_TYPE" "PS_AUDIT_HOL_EE_RQ"."ABSV_ACCRUAL_DT"
              "PS_AUDIT_HOL_EE_RQ"."BEGIN_DT" "PS_AUDIT_HOL_EE_RQ"."SEQNUM"))
      FULL(@"SEL$9E43CB6E" "L"@"SEL$2")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      MERGE(@"SEL$1")
      OUTLINE(@"SEL$58A6D7F6")
      OUTLINE(@"SEL$3")
      MERGE(@"SEL$58A6D7F6")
      OUTLINE_LEAF(@"SEL$9E43CB6E")
      ALL_ROWS
      DB_VERSION('11.2.0.4')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("L"."BEGIN_DT"="A"."BEGIN_DT"(+) AND "L"."ABSV_ACCRUAL_DT"="A"."ABSV_ACCRUAL_DT"(+))
   4 - filter("A"."BEGIN_DT"(+) IS NOT NULL)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=2) "A"."ABSV_ACCRUAL_DT"[DATE,7]
   2 - "L"."ABSV_ACCRUAL_DT"[DATE,7], "L"."BEGIN_DT"[DATE,7]
   3 - "L"."ABSV_ACCRUAL_DT"[DATE,7], "L"."BEGIN_DT"[DATE,7]
   4 - "A"."ABSV_ACCRUAL_DT"[DATE,7], "A"."BEGIN_DT"[DATE,7]

57 rows selected.

Elapsed: 00:00:00.03


We can see the predicate pushed over is affecting the row estimates (which is where I found this whole can of worms in the first place).

It's a bit weird and it will be a clever rewrite I'm sure, what annoys me is both the seeming inconsistency about how it is applied and my inability to explain it. I don't like falling back on the "jedi handwave" approach Smile

I've dumped the 10053 too and the predicate just appears in there (under access path analysis for the table "kkofmx: index filter:"A"."BEGIN_DT"(+) IS NOT NULL", although the rewrite internally uses the LATERAL function/join which I've not come across before so it may be that. Interestingly the same predicate is NOT there when it considered an index full scan in the 10053.


10053 snippet

Access path analysis for PS_AUDIT_HOL_EE_RQ
***************************************
SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for PS_AUDIT_HOL_EE_RQ[A]
  Table: PS_AUDIT_HOL_EE_RQ  Alias: A
    Card: Original: 88162354.000000  Rounded: 50958836  Computed: 50958836.00  Non Adjusted: 50958836.00
  Access Path: TableScan
    Cost:  343403.94  Resp: 343403.94  Degree: 0
      Cost_io: 341549.00  Cost_cpu: 38074417683
      Resp_io: 341549.00  Resp_cpu: 38074417683
  Access Path: index (index (FFS))
    Index: AUDIT_HOL_EE_RQ1
    resc_io: 197748.00  resc_cpu: 17553481003
    ix_sel: 0.000000  ix_sel_with_filters: 1.000000
  Access Path: index (FFS)
    Cost:  198603.18  Resp: 198603.18  Degree: 1
      Cost_io: 197748.00  Cost_cpu: 17553481003
      Resp_io: 197748.00  Resp_cpu: 17553481003
kkofmx: index filter:"A"."BEGIN_DT"(+) IS NOT NULL

  Access Path: index (FullScan)
    Index: AUDIT_HOL_EE_RQ1
    resc_io: 730144.00  resc_cpu: 22847999087
    ix_sel: 1.000000  ix_sel_with_filters: 1.000000
 ***** Logdef predicate Adjustment ******
 Final IO cst 0.00 , CPU cst 20.00
 ***** End Logdef Adjustment ******
    Cost: 731343.03  Resp: 731343.03  Degree: 1
  Best:: AccessPath: IndexFFS
  Index: AUDIT_HOL_EE_RQ1
         Cost: 198603.18  Degree: 1  Resp: 198603.18  Card: 50958836.00  Bytes: 0


Thanks for responding Smile

[Updated on: Wed, 08 July 2015 05:18]

Report message to a moderator

Re: Interesting explain plan output/puzzle [message #639473 is a reply to message #639470] Wed, 08 July 2015 05:30 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Actually just noticed the later rewrite was discarded anyway, having trouble with the edit function

Report message to a moderator

Re: Interesting explain plan output/puzzle [message #639501 is a reply to message #639356] Wed, 08 July 2015 13:19 Go to previous messageGo to next message
Alien
Messages: 292
Registered: June 1999
Senior Member
Hmmm... ok. That makes it interesting. I'm especially baffled by the difference between absv_accrual_dt and begin_date.
Since there are no constraints involved, I can only think of the selectivity or the position in the index.
What position are begin_dt and absv_accrual_dt in the index? Would you have a chance to exchange them for a test?

Regards,

Arian

Report message to a moderator

Re: Interesting explain plan output/puzzle [message #639567 is a reply to message #639501] Fri, 10 July 2015 03:30 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Unfortunately no, it's in production so I can't alter the index orders.

Metadata for all below:

  CREATE TABLE "PUBLISHER_CDC"."PS_SKY_HOL_EE_RQST_CT"
   (    "OPERATION$" CHAR(2 CHAR),
        "CSCN$" NUMBER,
        "COMMIT_TIMESTAMP$" DATE,
        "RSID$" NUMBER,
        "USERNAME$" VARCHAR2(30 CHAR),
        "TIMESTAMP$" DATE,
        "TARGET_COLMAP$" RAW(128),
        "EMPLID" VARCHAR2(33 CHAR),
        "EMPL_RCD" NUMBER(38,0),
        "COMPANY" VARCHAR2(9 CHAR),
        "PLAN_TYPE" VARCHAR2(6 CHAR),
        "ABSV_ACCRUAL_DT" DATE,
        "BEGIN_DT" DATE,
        "SEQNUM" NUMBER(38,0),
        "ABSV_REQUEST_DT" DATE,
        "ABS_BEGIN_TM" DATE,
        "ABSENCE_TYPE" VARCHAR2(9 CHAR),
        "RETURN_DT" DATE,
        "ABS_RETURN_TM" DATE,
        "SKY_DURATION" NUMBER(7,3),
        "SKY_HOLIDAY_UNIT" VARCHAR2(3 CHAR),
        "SKY_HOLIDAY_STATUS" VARCHAR2(3 CHAR),
        "SKY_DENY_REASON" VARCHAR2(3 CHAR),
        "ABSV_APPROVED_BY" VARCHAR2(33 CHAR),
        "ABSV_APPROVED_DT" DATE,
        "SKY_AM_PM" VARCHAR2(3 CHAR),
        "COMMENTS_256" VARCHAR2(762 CHAR)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PSIMAGE"
  PARTITION BY RANGE ("CSCN$")
 (PARTITION "P1"  VALUES LESS THAN (281474976710656) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PSIMAGE" )


  CREATE TABLE "SYSADM"."PS_AUDIT_HOL_EE_RQ"
   (    "AUDIT_OPRID" VARCHAR2(90 CHAR) NOT NULL ENABLE,
        "AUDIT_STAMP" DATE,
        "AUDIT_ACTN" VARCHAR2(3 CHAR) NOT NULL ENABLE,
        "EMPLID" VARCHAR2(33 CHAR) NOT NULL ENABLE,
        "EMPL_RCD" NUMBER(*,0) NOT NULL ENABLE,
        "COMPANY" VARCHAR2(9 CHAR) NOT NULL ENABLE,
        "PLAN_TYPE" VARCHAR2(6 CHAR) NOT NULL ENABLE,
        "ABSV_ACCRUAL_DT" DATE,
        "BEGIN_DT" DATE,
        "SEQNUM" NUMBER(*,0) NOT NULL ENABLE,
        "ABSENCE_TYPE" VARCHAR2(9 CHAR) NOT NULL ENABLE,
        "RETURN_DT" DATE,
        "SKY_DURATION" NUMBER(7,3) NOT NULL ENABLE,
        "SKY_HOLIDAY_UNIT" VARCHAR2(3 CHAR) NOT NULL ENABLE,
        "SKY_HOLIDAY_STATUS" VARCHAR2(3 CHAR) NOT NULL ENABLE,
        "SKY_DENY_REASON" VARCHAR2(3 CHAR) NOT NULL ENABLE,
        "ABSV_REQUEST_DT" DATE,
        "ABSV_APPROVED_BY" VARCHAR2(33 CHAR) NOT NULL ENABLE,
        "ABSV_APPROVED_DT" DATE,
        "SKY_AM_PM" VARCHAR2(3 CHAR) NOT NULL ENABLE,
        "COMMENTS_256" VARCHAR2(762 CHAR) NOT NULL ENABLE,
         CHECK (LENGTH(AUDIT_OPRID)<=30) ENABLE,
         CHECK (LENGTH(AUDIT_ACTN)<=1) ENABLE,
         CHECK (LENGTH(EMPLID)<=11) ENABLE,
         CHECK (LENGTH(COMPANY)<=3) ENABLE,
         CHECK (LENGTH(PLAN_TYPE)<=2) ENABLE,
         CHECK (LENGTH(ABSENCE_TYPE)<=3) ENABLE,
         CHECK (LENGTH(SKY_HOLIDAY_UNIT)<=1) ENABLE,
         CHECK (LENGTH(SKY_HOLIDAY_STATUS)<=1) ENABLE,
         CHECK (LENGTH(SKY_DENY_REASON)<=1) ENABLE,
         CHECK (LENGTH(ABSV_APPROVED_BY)<=11) ENABLE,
         CHECK (LENGTH(SKY_AM_PM)<=1) ENABLE,
         CHECK (LENGTH(COMMENTS_256)<=254) ENABLE
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 80 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 40960 NEXT 106496 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "HRAPP"



  CREATE INDEX "SYSADM"."AUDIT_HOL_EE_RQ1" ON "SYSADM"."PS_AUDIT_HOL_EE_RQ" ("EMPLID", "EMPL_RCD", "COMPANY", "PLAN_TYPE", "ABSV_ACCRUAL_DT", "BEGIN_DT", "SEQNUM")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 MAXSIZE UNLIMITED
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PSINDEX"


I don't get the same result creating these objects empty with different names as a test case, however given this is the optimizer, I'm relatively ok with that because it'll be related to the data/content itself.

Report message to a moderator

Re: Interesting explain plan output/puzzle [message #639568 is a reply to message #639567] Fri, 10 July 2015 03:47 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Roachcoach wrote on Fri, 10 July 2015 09:30
Unfortunately no, it's in production so I can't alter the index orders.
[/code]

I don't get the same result creating these objects empty with different names as a test case, however given this is the optimizer, I'm relatively ok with that because it'll be related to the data/content itself.

Could you not copy the table stats to your test case tables to give you a better chance at replicating the explan?

Report message to a moderator

Re: Interesting explain plan output/puzzle [message #639579 is a reply to message #639568] Fri, 10 July 2015 09:36 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
I'll take a stab at that. Updates in a week because I'm about to disappear from the office in a puff of smoke Very Happy

Report message to a moderator

Re: Interesting explain plan output/puzzle [message #639585 is a reply to message #639579] Fri, 10 July 2015 14:27 Go to previous message
Alien
Messages: 292
Registered: June 1999
Senior Member
Enjoy the break! (Or at least the puff Smile

Report message to a moderator

Previous Topic: Get data from various partitions with having one extra column
Next Topic: ORA-2014 on RLS-enabled table with double FGA policy
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Aug 10 05:59:50 CDT 2026
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.