11.2.0.4 DB rewrites query rendering remote index useless in distributed SQL

cj.travis

Background:

Since upgrading from 10.2.0.4 (SPARC, Solaris 10) to 11.2.0.4 (SPARC, Solaris 11) in November 2013, we've noticed degradation when going across database links for many of our nightly ETLs.  We have decommissioned the 10G database but still leave it running until the hardware reaches its impending end of life.

Just recently I decided to run the trace the sessions and run the ETL in the 10 and 11g environments and review the TKPROF output.  On the old system, the ETL ran in about 10 seconds.  On the new system, it regularly runs around 10-12 minutes.

Here is the now "problematic" SQL taken straight from our ETL procedure:

         INSERT /*+ APPEND NOLOGGING PARALLEL(T,4) */

         INTO STG.STG_PS_FG_SO_LINE_AT T

         SELECT

               AUDIT_OPRID,AUDIT_STAMP,AUDIT_ACTN,BUSINESS_UNIT,SO_ID,SO_LINE,ACTIVITY_CODE,SOACTIVITY_STATUS,SOACT_PRIORITY,PRICE,FGAS_SO_ORIG_PRICE,FGAS_PRC_REASON               _CD,FGAS_QTY_ORDERED,FGAS_TTL_TAX_RATE,FGAS_TAX_PER_GAL

         FROM SYSADM.PS_FG_SO_LINE_AT @ REMOTE_CRM S

         WHERE 1=1

           AND S.AUDIT_ACTN = 'A' 

           AND S.AUDIT_STAMP > TRUNC(SYSDATE) -2;

As you can see this is an incremental load where we truncate and reload 2 days worth of data.  The index on the remote database is on AUDIT_STAMP and AUDIT_ACTN.

Here is a the explain plan of the 10g (good performance) from the traced session/TKPROF:

Plan hash value: 3186031151

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

| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |

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

|   0 | SELECT STATEMENT REMOTE     |                  |   538K|    40M|  3190   (1)| 00:00:39 |        |

|   1 |  TABLE ACCESS BY INDEX ROWID| PS_FG_SO_LINE_AT |   538K|    40M|  3190   (1)| 00:00:39 |  CRPRD |

|*  2 |   INDEX RANGE SCAN          | PSAFG_SO_LINE_AT | 96987 |       |   219   (0)| 00:00:03 |  CRPRD |

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

Predicate Information (identified by operation id):

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

   2 - access("A1"."AUDIT_STAMP">TRUNC(TO_NUMBER(:1))-2 AND "A1"."AUDIT_ACTN"='A' AND

              "A1"."AUDIT_STAMP" IS NOT NULL)

       filter("A1"."AUDIT_ACTN"='A')

And here is the explain on the 11g (bad performance) taken from TKPROF:

Plan hash value: 2117389654

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

| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |

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

|   0 | SELECT STATEMENT REMOTE     |                  |   538K|    40M| 27297   (1)| 00:05:28 |        |

|   1 |  TABLE ACCESS BY INDEX ROWID| PS_FG_SO_LINE_AT |   538K|    40M| 27297   (1)| 00:05:28 |  CRPRD |

|*  2 |   INDEX SKIP SCAN           | PSAFG_SO_LINE_AT | 96987 |       | 24326   (1)| 00:04:52 |  CRPRD |

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

Predicate Information (identified by operation id):

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

   2 - access("A1"."AUDIT_ACTN"='A')

       filter("A1"."AUDIT_ACTN"='A' AND SYS_EXTRACT_UTC(INTERNAL_FUNCTION("A1"."AUDIT_STAMP"))>SY

              S_EXTRACT_UTC(CAST(TRUNC([email protected]!)-2 AS TIMESTAMP WITH TIME ZONE)))

What stands out is that Oracle rewrote the query like this (extracted from TKPROF) and the optimizer on 10g allows for an INDEX RANGE SCAN and on 11g it becomes an INDEX SKIP SCAN:

SELECT /*+ OPAQUE_TRANSFORM */

      "AUDIT_OPRID",

       "AUDIT_STAMP",

       "AUDIT_ACTN",

       "BUSINESS_UNIT",

       "SO_ID",

       "SO_LINE",

       "ACTIVITY_CODE",

       "SOACTIVITY_STATUS",

       "SOACT_PRIORITY",

       "PRICE",

       "FGAS_SO_ORIG_PRICE",

       "FGAS_PRC_REASON_CD",

       "FGAS_QTY_ORDERED",

       "FGAS_TTL_TAX_RATE",

       "FGAS_TAX_PER_GAL"

  FROM "SYSADM"."PS_FG_SO_LINE_AT"@REMOTE_CRM "S"

WHERE     "AUDIT_ACTN" = 'A'

       AND "AUDIT_STAMP" >

              CAST (TRUNC (SYSDATE) - 2 AS TIMESTAMP WITH TIME ZONE);

Because the database casted the TRUNC(SYSDATE) as a TIMESTAMP WITH TIME ZONE, the remote index is not being used.  I've never seen this occur before.  Is there a way to hint or instruct the optimizer not to do this?

Thanks in advance for the help.

Franck Pachot

Hi,

What is the datatype of AUDIT_STAMP ? And what is you database timezone ? It seems that in the 11g plan it has converted it to

  filter("A1"."AUDIT_ACTN"='A' AND SYS_EXTRACT_UTC(INTERNAL_FUNCTION("A1"."AUDIT_STAMP"))>SY

              S_EXTRACT_UTC(CAST(TRUNC([email protected]!)-2 AS TIMESTAMP WITH TIME ZONE)))

Besides that, NOLOGGING is not a hint. And because it is a reserved word, all hints that follow are ignored. this is why PARALLEL(T,4) is ignored.

Regards,

Franck.

Sergei Krasnoslobodtsev

Hi.

Attempt to apply other hint, for example INDEX_RS, or others INDEX_*  with very high probability, will be ignored by the optimizer.

Therefore, pay attention to the essential difference:

   2 - access("A1"."AUDIT_STAMP">TRUNC(TO_NUMBER(:1))-2 AND "A1"."AUDIT_ACTN"='A' AND 
              "A1"."AUDIT_STAMP" IS NOT NULL)
       filter("A1"."AUDIT_ACTN"='A')

 2 - access("A1"."AUDIT_ACTN"='A')
       filter("A1"."AUDIT_ACTN"='A' AND SYS_EXTRACT_UTC(INTERNAL_FUNCTION("A1"."AUDIT_STAMP"))>SY
              S_EXTRACT_UTC(CAST(TRUNC([email protected]!)-2 AS TIMESTAMP WITH TIME ZONE)))

Check again the column AUDIT_STAMP type and apply the corresponding transformation that will tap the column to use the index

Mohamed Houri

In my honest opinion you should better change your index so that the predicate on which you are applying an equality (AUDIT_ACTN) would be at the leading edge of the index (the index would start by AUDIT_ACTN column). As such, even if your query is rewritten, you will still be using an index range scan operation which is less costly then the index skip scan operation.

SQL> select /*+ index_ss(t1) */ * from t1

  2  where

  3       id= 15

  4  and    start_date > CAST (TRUNC (SYSDATE) - 2 AS TIMESTAMP WITH TIME ZONE) ;

SQL_ID  gsb310xmf3n5f, child number 0

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

select /*+ index_ss(t1) */ * from t1 where      id= 15 and

start_date > CAST (TRUNC (SYSDATE) - 2 AS TIMESTAMP WITH TIME ZONE)

Plan hash value: 1708672272

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

| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |      |       |       |   823 (100)|          |

|   1 |  TABLE ACCESS BY INDEX ROWID| T1   |   165 |  4785 |   823   (1)| 00:00:03 |

|*  2 |   INDEX SKIP SCAN           | IND1 |    12 |       |   822   (1)| 00:00:03 |

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

Predicate Information (identified by operation id):

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

   2 - access("ID"=15)

       filter(("ID"=15 AND SYS_EXTRACT_UTC(INTERNAL_FUNCTION("START_DATE"))>

              SYS_EXTRACT_UTC(CAST(TRUNC([email protected]!)-2 AS TIMESTAMP WITH TIME ZONE))))

SQL> drop index ind1;

Index dropped.

SQL> create index ind2 on t1(id, start_date);

Index created.

SQL> select  * from t1

  2  where

  3       id= 15

  4  and    start_date > CAST (TRUNC (SYSDATE) - 2 AS TIMESTAMP WITH TIME ZONE) ;

SQL_ID  c85gq2nhnghnn, child number 0

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

select  * from t1 where      id= 15 and    start_date > CAST (TRUNC

(SYSDATE) - 2 AS TIMESTAMP WITH TIME ZONE)

Plan hash value: 3688005278

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

| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |      |       |       |     7 (100)|          |

|   1 |  TABLE ACCESS BY INDEX ROWID| T1   |   165 |  4785 |     7  (15)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IND2 |    66 |       |     6  (17)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   2 - access("ID"=15)

       filter(SYS_EXTRACT_UTC(INTERNAL_FUNCTION("START_DATE"))>SYS_EXTRACT_U

              TC(CAST(TRUNC([email protected]!)-2 AS TIMESTAMP WITH TIME ZONE)))

Best regards

Mohamed Houri