Блог

Я использую индексированный столбец, используемый в качестве фильтра, помещая его «между» двумя литеральными значениями. (Столбец находится на второй позиции индекса и фактически замедляет выполнение; я разберусь с этим позже).

Что меня смущает, так это то, что Oracle (11.2.0.3.0) использует или игнорирует указанный индекс в зависимости от формата строк значения и формата, предоставленных to_date:

При этом игнорируется индекс:

 SQL> SELECT *
  2  FROM gprs_history_import  gh
  3  WHERE start_call_date_time BETWEEN
  4      to_date('20140610 000000','yyyymmdd hh24miss') AND
  5      to_date('20140610 235959','yyyymmdd hh24miss')
  6  /

Execution Plan
----------------------------------------------------------
Plan hash value: 990804809

--------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name                | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                     |   350 |   219K|   242K  (1)| 00:56:42 |       |       |
|   1 |  PARTITION RANGE SINGLE|                     |   350 |   219K|   242K  (1)| 00:56:42 |    74 |    74 |
|   2 |   PARTITION LIST ALL   |                     |   350 |   219K|   242K  (1)| 00:56:42 |     1 |     3 |
|*  3 |    TABLE ACCESS FULL   | GPRS_HISTORY_IMPORT |   350 |   219K|   242K  (1)| 00:56:42 |   220 |   222 |
--------------------------------------------------------------------------------------------------------------

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

   3 - filter("START_CALL_DATE_TIME"<=TO_DATE(' 2014-06-10 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))
  

В этом используется индекс (обратите внимание на пробел после части даты в строке 4):

 SQL> SELECT *
  2  FROM gprs_history_import  gh
  3  WHERE start_call_date_time BETWEEN
  4      to_date('20140610 ','yyyymmdd ') AND
  5      to_date('20140610 235959','yyyymmdd hh24miss')
  6  /

Execution Plan
----------------------------------------------------------
Plan hash value: 464458373

---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                          |   350 |   219K|  2795K  (1)| 10:52:15 |       |       |
|*  1 |  FILTER                              |                          |       |       |            |          |       |       |
|   2 |   PARTITION RANGE ITERATOR           |                          |   350 |   219K|  2795K  (1)| 10:52:15 |   KEY |    74 |
|   3 |    PARTITION LIST ALL                |                          |   350 |   219K|  2795K  (1)| 10:52:15 |     1 |     3 |
|   4 |     TABLE ACCESS BY LOCAL INDEX ROWID| GPRS_HISTORY_IMPORT      |   350 |   219K|  2795K  (1)| 10:52:15 |   KEY |   222 |
|*  5 |      INDEX SKIP SCAN                 | GPRS_HISTORY_IMPORT_IDX1 |     1 |       |  2795K  (1)| 10:52:15 |   KEY |   222 |
---------------------------------------------------------------------------------------------------------------------------------

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

   1 - filter(TO_DATE('20140610 ','yyyymmdd ')<=TO_DATE(' 2014-06-10 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))
   5 - access("START_CALL_DATE_TIME">=TO_DATE('20140610 ','yyyymmdd ') AND "START_CALL_DATE_TIME"<=TO_DATE(' 2014-06-10
              23:59:59', 'syyyy-mm-dd hh24:mi:ss'))
       filter("START_CALL_DATE_TIME">=TO_DATE('20140610 ','yyyymmdd ') AND "START_CALL_DATE_TIME"<=TO_DATE(' 2014-06-10
              23:59:59', 'syyyy-mm-dd hh24:mi:ss'))
  

(Фильтр в (1) кажется немного глупым, как будто Oracle не поняла выражение)

Опять же, это не так (я удалил конечный пробел):

 SQL> SELECT *
  2  FROM gprs_history_import  gh
  3  WHERE start_call_date_time BETWEEN
  4      to_date('20140610','yyyymmdd') AND
  5      to_date('20140610 235959','yyyymmdd hh24miss')
  6  /

Execution Plan
----------------------------------------------------------
Plan hash value: 990804809

--------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name                | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                     |   350 |   219K|   242K  (1)| 00:56:42 |       |       |
|   1 |  PARTITION RANGE SINGLE|                     |   350 |   219K|   242K  (1)| 00:56:42 |    74 |    74 |
|   2 |   PARTITION LIST ALL   |                     |   350 |   219K|   242K  (1)| 00:56:42 |     1 |     3 |
|*  3 |    TABLE ACCESS FULL   | GPRS_HISTORY_IMPORT |   350 |   219K|   242K  (1)| 00:56:42 |   220 |   222 |
--------------------------------------------------------------------------------------------------------------

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

   3 - filter("START_CALL_DATE_TIME"<=TO_DATE(' 2014-06-10 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))
  

Помещение кавычек вокруг пробела исключает возможность использования индекса.

Что дает?

Хорошо — я попробую, в основном это вычет из доступной информации:

Почему Oracle выбирает другой план выполнения?

Похоже, что в вашем втором запросе с необычным форматом даты оптимизатор понятия не имеет, каково значение результирующей даты. Вы видите предикат фильтра:

1 - filter(TO_DATE('20140610 ','yyyymmdd ')<=TO_DATE(' 2014-06-10 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))

Это означает, что оптимизатор даже не уверен, что первая дата меньше второй! Это означает, что оптимизатор не имеет представления о количестве возвращаемых строк и будет просто использовать общий план без учета конкретной статистики. Было бы то же самое, если бы у вас была определенная пользователем функция xyt(), которая возвращала бы дату для диапазона. Оптимизатор не имеет возможности узнать, какое значение даты приведет — это означает, что вы получаете общий универсальный план, который должен быть довольно приличным для любого указанного диапазона дат.

В первом и третьем случаях оптимизатор, похоже, понимает дату напрямую и может угадать количество строк, которые находятся в диапазоне дат, используя статистику. Итак, в то время как второй запрос был к оптимизатору BETWEEN X AND 3 , подобный этому запросу BETWEEN 1 AND 3 , он оптимизирует план запроса для прогнозируемого количества возвращаемых строк!

Кажется странным, что оптимизатор запросов имеет такие проблемы со странным форматом даты, что может быть подано как ошибка / запрос на улучшение…

Но важный момент:

1. Полное сканирование таблицы не обязательно должно быть ПЛОХИМ планом… А также использование индекса не всегда быстрее!
2. Стоимость в плане запроса никоим образом не связана напрямую с фактическим временем выполнения или производительностью — это внутреннее измерение для сравнения разных планов для ОДНОГО и ТОГО ЖЕ ЗАПРОСА (поэтому вы не можете сравнивать стоимость разных запросов, таких как ваши запросы 1,2 и 3)

В принципе, если вы возвращаете большое количество строк из таблицы, полное сканирование таблицы без доступа к индексу во многих случаях будет намного быстрее, особенно при работе с определенными разделами! — Сканирование таблицы будет получать доступ только к заголовку для соответствующего диапазона дат — то есть только для рассматриваемой даты и возвращает все строки из этого раздела. Это намного быстрее, чем запрашивать индекс для каждой отдельной строки, а затем извлекать строку с помощью индексного доступа… Попробуйте профилировать запросы — полное сканирование таблицы в разделе должно быть в 3 раза быстрее с гораздо меньшим вводом-выводом

Ошибки в оптимизаторе или анализаторе приводят к тому, что некоторые форматы дат понижают статическое сокращение разделов до динамического сокращения разделов. Изменения в сокращении разделов приводят к разной мощности и затратам, что затем приводит к значительным изменениям во многих других частях плана.

Этот ответ лишь частично объясняет проблему и содержит некоторые предположения. Надеюсь, это хотя бы немного прольет свет на то, в чем проблема, а в чем нет. Это, по крайней мере, хорошая отправная точка, если вам действительно нужно полное объяснение и вы хотите отправить запрос на обслуживание в Oracle.

Терминология и некоторые справочные сведения

Статическое сокращение разделов — это когда оптимизатор определяет во время компиляции, какой раздел будет использоваться. Статистика для каждого раздела, что приводит к лучшим оценкам мощности, что приводит к лучшим планам. Например, представьте таблицу, разделенную по статусу, где раздел для CANCELLED является крошечным, а раздел для ACTIVE — большим. Знание того, какой раздел используется, может полностью изменить порядок соединения и методы доступа оптимального плана. Pstart и Pstop будут числовыми значениями, когда используется статическое сокращение разделов.

Динамическое сокращение раздела — это когда оптимизатор не может определить раздел до выполнения. Данные извлекаются только из требуемых разделов, но план выполнения строится без специальных знаний о том, какой раздел используется. Некоторые оценки статистики разделов будут простым средним значением для всех доступных разделов. В приведенном выше примере таблицы, разделенной по статусу, среднее значение крошечного раздела и большого раздела неточно представляют либо. Либо Pstart или Pstop будет включать слово KEY при использовании динамической обрезки разделов.

Руководство Oracle® Database VLDB и по разделению включает раздел о преобразованиях типов данных, который стоит прочитать. Например, одна соответствующая цитата из руководства:

Только правильно примененная функция TO_DATE гарантирует, что база данных способна однозначно определять значение даты и потенциально использовать его для статической обрезки, что особенно полезно для доступа к одному разделу.

Пример схемы и данных

Этот простой тестовый пример демонстрирует проблему. Это также исключает общие проблемы с производительностью, такие как отсутствие статистики.

Сначала создайте пример таблицы с 2 разделами, одним большим и одним маленьким.

 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); -- 1000
select count(*) from gprs_history_import partition (p_small); --   10
  

Преобразование статического в динамическое приводит к неправильным оценкам мощности

Оценка статической мощности равна 1000. Дополнительное пространство во втором формате даты изменяется Pstop с 1 на KEY . План меняется со статического на динамическое сокращение разделов. Динамическая оценка — неточная 505, среднее значение 1000 и 10

Для простоты этот пример показывает только плохую оценку мощности. Нет необходимости показывать, что запрос выполняется медленно, поскольку неверные оценки строк неизбежно приводят к неправильным планам выполнения по многим причинам.

 explain plan for select /* static partition pruning */ *
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 /* dybnamic partition pruning */ *
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 '))
  

Проблемы с синтаксическим анализом формата даты

Теперь о некоторых предположениях относительно того, почему запрос переходит от статической к динамической обрезке разделов.

Не всегда очевидно, когда оптимизатор может использовать статическое и динамическое разделение. В общем, литералы допускают статическую обрезку, а переменные требуют динамической обрезки.

 --#1: Obviously static: It uses an unambiguous ANSI date literal.
select * from gprs_history_import where start_call_date_time = date '2000-11-01';

--#2: Obviously dyanmic: It uses a bind variable.
select * from gprs_history_import where start_call_date_time = :date;

--#3: Probably dynamic: The optimizer cannot always infer the literal value. 
select * from gprs_history_import where start_call_date_time = 
    (select date '2000-11-01' from dual);

--#4: Probably static: FEB is not always valid, but Oracle can figure that out.
select * from gprs_history_import where start_call_date_time = 
    to_date('01-FEB-2000', 'DD-MON-YYYY');
  

Если учесть все проблемы с производительностью и интернационализацией в случае № 4, становится ясно, насколько сложно анализировать даты. Значение to_date('01-FEB-2000', 'DD-MON-YYYY') зависит от нескольких параметров NLS, таких как NLS_DATE_LANGUAGE . Запрос действителен для английского, но не для немецкого. И если NLS_CALENDAR не установлено GREGORIAN значение, то даже полностью числовой формат даты может быть неправильным. to_date Строка не является значением привязки, но и явно не является литералом.

Разница между литералами с истинной датой и отформатированными строками более очевидна, если учитываются жесткие синтаксические анализы. Запрос № 1 не будет принудительно выполнять жесткий синтаксический анализ, даже если язык изменен, но запрос № 4 будет. Это можно продемонстрировать, запустив несколько вариантов каждого, изменив язык, а затем запустив select value from v$sesstat natural join v$statname where name = 'parse count (hard)' and sid = userenv('SID'); .

У Oracle должна быть где-то переменная для обозначения «это не переменная привязки, но может привести к различным планам на основе настроек NLS». Эта переменная не всегда приводит к динамической обрезке разделов, но где-то должны быть какие-то ошибки, которые иногда нарушают ее.