Errors in the optimizer or the parser lead to the fact that some date formats reduce the static partitioning of dynamic partitioning. Partition trimming changes lead to different capacities and costs, which then lead to significant changes in many other parts of the plan.
This answer only partially explains the problem and contains some assumptions. I hope he at least shed some light on what the problem is and no. This is at least a good starting point if you really need a full explanation and you want to send an Oracle service request.
Terminology and some background readings
Trimming a static section is when the optimizer determines at compile time which section will be used. Statistics refer to sections, which leads to higher power ratings, which leads to better plans. For example, think of a table broken down by status, where the partition for CANCELED is tiny and the partition for ACTIVE is large. Knowing which section is used can completely change the connection order and access methods to the optimal plan. Pstart and Pstop will be numeric values when using static section trimming.
Partition dynamic cropping is when the optimizer cannot determine the partition before runtime. Data is extracted only from the necessary sections, but the execution plan is built without much knowledge of which section is used. Some statistical section estimates will be a simple average of all available sections. In the example of a table broken down by status, the average value of a tiny section and a large section also does not accurately reflect. Either Pstart or Pstop will include the word KEY when dynamic section cropping is used.
The Oracle Database Database VLDB and Partitioning Guide contains a section on Data Type Conversions that are worth reading. For example, one relevant quote from the manual:
Only the correctly applied TO_DATE function ensures that the database is able to uniquely determine the date value and use it for static trimming, which is especially useful for accessing a single section.
Schema and data example
This simple test case demonstrates the problem. It also eliminates common performance issues, such as a lack of statistics.
First, create a sample table with two sections, one large and one small.
create table gprs_history_import(id number, start_call_date_time date) partition by range (start_call_date_time) ( partition p_large values less than (date '2014-06-01'), partition p_small values less than (date '2014-07-01') ); insert into gprs_history_import select level, date '2014-05-01' from dual connect by level <= 1000; insert into gprs_history_import select level, date '2014-06-01' from dual connect by level <= 10; begin dbms_stats.gather_table_stats(user, 'GPRS_HISTORY_IMPORT'); end; / select count(*) from gprs_history_import partition (p_large);
Static dynamics cause poor power ratings
The static power rating is ideal 1000. The extra space in the second date format changes Pstop from 1 to KEY . The plan changes from static to dynamic cropping of partitions. Dynamic score is inaccurate 505, average 1000 and 10
For simplicity, this example shows only a poor rating of power. There is no need to show a slow query, as evaluating bad strings inevitably leads to poor execution plans for many reasons.
explain plan for select * from gprs_history_import where start_call_date_time < to_date('20140601 000000','yyyymmdd hh24miss'); select * from table(dbms_xplan.display); Plan hash value: 452971246 -------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | -------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1000 | 12000 | 16 (0)| 00:00:01 | | | | 1 | PARTITION RANGE SINGLE| | 1000 | 12000 | 16 (0)| 00:00:01 | 1 | 1 | | 2 | TABLE ACCESS FULL | GPRS_HISTORY_IMPORT | 1000 | 12000 | 16 (0)| 00:00:01 | 1 | 1 | -------------------------------------------------------------------------------------------------------------- explain plan for select * from gprs_history_import where start_call_date_time < to_date('20140601 ','yyyymmdd '); select * from table(dbms_xplan.display); Plan hash value: 2464174375 ---------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ---------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 505 | 6060 | 29 (0)| 00:00:01 | | | | 1 | PARTITION RANGE ITERATOR| | 505 | 6060 | 29 (0)| 00:00:01 | 1 | KEY | |* 2 | TABLE ACCESS FULL | GPRS_HISTORY_IMPORT | 505 | 6060 | 29 (0)| 00:00:01 | 1 | KEY | ---------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("START_CALL_DATE_TIME"<TO_DATE('20140601 ','yyyymmdd '))
Date format parsing issues
Now about some assumptions about why the request moves from static to dynamic partitioning.
It is not always obvious when the optimizer can use static and dynamic splitting. In general, literals allow static cropping, while variables require dynamic cropping.
--#1: Obviously static: It uses an unambiguous ANSI date literal. select * from gprs_history_import where start_call_date_time = date '2000-11-01'; --
When you look at all the issues of productivity and internationalization in Case No. 4, it becomes clear how difficult it is to parse dates. The value of to_date('01-FEB-2000', 'DD-MON-YYYY') depends on several NLS parameters, such as NLS_DATE_LANGUAGE . The request is valid for English, but not for German. And if NLS_CALENDAR not set to GREGORIAN , than the numeric date format may be wrong. The to_date string to_date not a binding value, but it is also not literal.
The difference between true date literals and formatted strings is more obvious if hard parses are counted. Request number 1 will not force a hard parsing, even if the language is changed, but request number 4 will be. This can be demonstrated by performing several options for each of them, changing the language, and then running select value from v$sesstat natural join v$statname where name = 'parse count (hard)' and sid = userenv('SID'); .
Oracle should have a variable somewhere to indicate "it is not a binding variable, but may lead to different plans based on NLS settings." This variable does not always truncate the dynamic section, but there should be some errors that sometimes interrupt it.