Skip to Main Content
  • Questions

  • Oracle not using literal value for partition pruning during query execution

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Robert.

Asked: February 22, 2017 - 9:52 pm UTC

Last updated: February 25, 2017 - 12:36 am UTC

Version: 11.2.0.4.0

Viewed 1000+ times

You Asked

Hi Tom,

We have a table with about 700 partitions.

Our test query is
select count(*) from fs_1d f where f.fs_date = '03-JAN-2017' and f.feed_code = 'E4F1'


When we explain plan for this query, the plan was correct. It goes directly to partition #697 (pruning), and then uses the index FS_1D_IDX2.

SQL> explain plan for
  2  select count(*)
  3  from fs_1d f
  4  where f.fs_date  = '03-JAN-2017'
  5  and f.feed_code  = 'E4F1'
  6  ;

Explained.

Elapsed: 00:00:00.02

SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name       | Rows  | Bytes | Cost (%CPU)| Pstart| Pstop |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |            |     1 |    14 |  1112   (0)|       |       |
|   1 |  SORT AGGREGATE                     |            |     1 |    14 |            |       |       |
|   2 |   PARTITION RANGE SINGLE            |            |  1189 | 16646 |  1112   (0)|   697 |   697 |
|*  3 |    TABLE ACCESS BY LOCAL INDEX ROWID| FS_1D      |  1189 | 16646 |  1112   (0)|   697 |   697 |
|*  4 |     INDEX RANGE SCAN                | FS_1D_IDX2 |  7392 |       |   109   (0)|   697 |   697 |
-------------------------------------------------------------------------------------------------------

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

   3 - filter("F"."FS_DATE"=TO_DATE(' 2017-01-03 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   4 - access("F"."FEED_CODE"='E4F1')

20 rows selected.

Elapsed: 00:00:00.07



But when we actually execute the query, Oracle did not use the explained plan above.

SQL> select count(*)
 2  from fs_1d f
  3  where f.fs_date  = '03-JAN-2017'
  4  and f.feed_code  = 'E4F1'
  5  ;

  COUNT(*)
----------
    240345

Elapsed: 00:09:49.21

SQL> SELECT sql_id, piece, sql_text
  2  FROM v$sqltext
  3  WHERE sql_id = 'fx1ugkqm3hx5q'
  4  ORDER BY piece
  5  ;

SQL_ID             PIECE SQL_TEXT
------------- ---------- ----------------------------------------------------------------
fx1ugkqm3hx5q          0 select count(*) from fs_1d f where f.fs_date  = :"SYS_B_0" and f
fx1ugkqm3hx5q          1 .feed_code  = :"SYS_B_1"

Elapsed: 00:00:00.04

SQL> SELECT
  2  id, depth,
  3  operation, options,
  4  object_type, object_owner, object_name,
  5  cardinality, bytes, cost
  6  FROM v$sql_plan_statistics_all
  7  WHERE sql_id = 'fx1ugkqm3hx5q'
  8  ORDER BY id
  9  ;

  ID DEPTH OPERATION            OPTIONS    OBJECT_TYP OBJECT_OWN OBJECT_NAM CARDINALITY      BYTES    COST
---- ----- -------------------- ---------- ---------- ---------- ---------- ----------- ---------- ----------
   0     0 SELECT STATEMENT                                                                           1096697
   1     1 SORT                 AGGREGATE                                             1         14
   2     2 VIEW                            VIEW                  VW_TE_2            125               1096697
   3     3 UNION-ALL
   4     4 PARTITION RANGE      SINGLE                                              119       1666    1096697
   5     5 TABLE ACCESS         FULL       TABLE      OCFGMR_P   FS_1D              119       1666    1096697
   6     4 PARTITION RANGE      SINGLE                                                6         84    1096696
   7     5 TABLE ACCESS         FULL       TABLE      OCFGMR_P   FS_1D                6         84    1096696

8 rows selected.

Elapsed: 00:00:00.04



The actual execution plan is as if no actual literal value was provided.

SQL> explain plan for
  2  select count(*)
  3  from fs_1d f
  4  where f.fs_date  = :d1
  5  and f.feed_code  = 'E4F1'
  6  ;

Explained.

Elapsed: 00:00:00.04

SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------
| Id  | Operation                 | Name    | Rows  | Bytes | Cost (%CPU)| Pstart| Pstop |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |         |     1 |    12 | 23544   (1)|       |       |
|   1 |  SORT AGGREGATE           |         |     1 |    12 |            |       |       |
|   2 |   VIEW                    | VW_TE_2 |  1309 |       | 23544   (1)|       |       |
|   3 |    UNION-ALL              |         |       |       |            |       |       |
|   4 |     PARTITION RANGE SINGLE|         |  1247 | 14964 | 23544   (1)|   KEY |   KEY |
|*  5 |      TABLE ACCESS FULL    | FS_1D   |  1247 | 14964 | 23544   (1)|   KEY |   KEY |
|   6 |     PARTITION RANGE SINGLE|         |    62 |   744 | 23544   (1)|   KEY |   KEY |
|*  7 |      TABLE ACCESS FULL    | FS_1D   |    62 |   744 | 23544   (1)|   KEY |   KEY |
------------------------------------------------------------------------------------------

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

   5 - filter("F"."FEED_CODE"='E4F1' AND ("F"."FS_DATE"<TO_DATE(' 2016-07-19
              00:00:00', 'syyyy-mm-dd hh24:mi:ss') OR "F"."FS_DATE">=TO_DATE(' 2016-07-30
              00:00:00', 'syyyy-mm-dd hh24:mi:ss') OR "F"."FS_DATE">=TO_DATE(' 2016-07-23
              00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "F"."FS_DATE"<TO_DATE(' 2016-07-25
              00:00:00', 'syyyy-mm-dd hh24:mi:ss')) AND "F"."FS_DATE"=:D1)
   7 - filter("F"."FEED_CODE"='E4F1' AND ("F"."FS_DATE">=TO_DATE(' 2016-07-19
              00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "F"."FS_DATE"<TO_DATE(' 2016-07-23
              00:00:00', 'syyyy-mm-dd hh24:mi:ss') OR "F"."FS_DATE">=TO_DATE(' 2016-07-25
              00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "F"."FS_DATE"<TO_DATE(' 2016-07-30
              00:00:00', 'syyyy-mm-dd hh24:mi:ss')) AND "F"."FS_DATE"=:D1)

31 rows selected.

Elapsed: 00:00:00.11



The questions we have from this test-case:

(1) why would Oracle explain plan and actual plan be different (shared pool was clean as we did a fresh flush just before this test)
(2) why didn’t Oracle peek and use the literal value provided in the query but instead use a bind for the actual plan.

Any insights and pointers on those two questions?

Thanks a lot

and Connor said...

1) When I see

f.fs_date = :"SYS_B_0"

it tells me you have cursor sharing enabled. So all of your literals are going to replaced by binds.

2) We peek at binds during "genuine" execution. If you run an "explain plan" command then we do *not* peek at binds. This is why explain plan commands can "lie" to you.

But cursor sharing.... that's not a fun place to be :-)


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Follow up

A reader, February 23, 2017 - 7:58 am UTC

Reading your answer I wonder what you think the value of cusor sharing is ? And what you recommend as value for this case?
Connor McDonald
February 25, 2017 - 12:36 am UTC

The query that was run was:

select count(*)
from fs_1d f
where f.fs_date = '03-JAN-2017'
and f.feed_code = 'E4F1'

yet looked like this in v$sqltext

select count(*)
from fs_1d f
where f.fs_date = :"SYS_B_0"
and f.feed_code = :"SYS_B_1"

so cursor_sharing is the most likely culprit.

My recommendations for cursor_sharing are pretty simple:

Either dont use it, or if you *have* to, start making steps toward a solution where you *eventually* wont have to use it

Because if literals are floating around in your system....then its very likely that security holes are also floating around.

A reader, February 24, 2017 - 1:56 pm UTC

Thanks
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

DBMS_XPLAN

More on PL/SQL routine DBMS_XPLAN here