Запрос выполняется медленнее после создания индексов и используется вычисление dbms_stats

У меня есть таблица с 1,5 миллионами строк. Я запускаю запрос, который извлекает записи, не имеющие повторяющихся значений в столбце. Я наблюдаю поведение, при котором после создания индексов производительность запроса ухудшается. Я также использовал dbms_stats со 100% процентной оценкой (режим вычисления) для сбора статистики, чтобы CBO оракула 11g принимала более обоснованное решение для плана запроса, но оно не улучшает время выполнения запроса.

Столбец measure_0 имеет 0,4 миллиона уникальных значений.

Ниже приведен запрос с планом выполнения, обратите внимание, что в таблице нет индексов.

Запрос занимает 1,90 секунд. Если я снова запустил запрос, он займет 1.66 секунд. Почему в первом запуске требуется больше времени?

Чтобы ускорить работу, я создал индексы для двух столбцов, используемых в запросе.

Когда я впервые запустил запрос после этого, ему потребовалось время прокрутки 21. В то время как последующие прогоны купили его до 2.9 секунд. Почему оракул занимает так много времени в первом туре, это разогревается или что-то... меня озадачивает!

Я ожидал, что время будет ниже, чем когда таблица не была проиндексирована. Почему индексированная версия таблицы занимает больше времени для получения результатов, чем не индексированная версия? Если я не ошибаюсь, это ТАБЛИЦА ДОСТУПА ПО ИНДЕКСУ ROWID, который занимает время. Могу ли я использовать Oracle для использования TABLE ACCESS FULL?

Затем я собрал статистику по таблице, чтобы CBO улучшил план с помощью опции вычисления. Итак, теперь статистика будет точной.

На этот раз запрос вернулся в 2.9 секунды (иногда потребовалось 3,9 секунд).

Моя цель - максимально сократить время выполнения запроса. Но после добавления индексов или после вычисления статистики время запроса просто увеличивалось. Почему это происходит и как я могу улучшить, даже сохраняя индексы?

Ответы

Ответ 1

Прежде всего, позвольте мне процитировать Tom Kyte:

просто продолжайте говорить себе снова и снова

"полные сканы не злые, индексы не очень хороши"
"полное сканирование не является злом, индексы не хороши"
"полное сканирование не является злом, индексы не хороши"
"полное сканирование не является злом, индексы не хороши"
"полное сканирование не является злом, индексы не хороши"
"полное сканирование не является злом, индексы не хороши"

Индексы будут не всегда улучшать производительность, это не волшебная серебряная пуля (как будто такая вещь когда-либо существовала)

Теперь вы спрашиваете , почему требуется больше времени с вашим индексом. Ответ довольно прост:

с полным сканированием таблицы: 46518 соответствует
с вашим индексом: 660054 соответствует

Другими словами: Oracle выполняет больше операций чтения с вашим индексом, чем с полным сканированием таблицы. Это происходит потому, что:

FULL TABLE SCAN reads - это массовые операции (много блоков одновременно) и, следовательно, эффективный способ чтения большого количества данных.
иногда, когда вы читаете из индекса, вы заканчиваете чтение одного и того же блока данных более одного раза.

В связи с тем, что оптимизатор решил использовать этот явно неэффективный индекс, это связано с тем, что даже при esimate_percent=100 и полных гистограммах (которые вы собрали в столбце MEASURE_0) некоторое распределение данных по-прежнему не может быть надежно выраженный простым анализом оптимизатора. В частности, анализатор не понимает зависимость между столбцами и кросс-таблицами. Это приводит к неправильным оценкам, которые приводят к плохим выборам плана.

Изменить: кажется, что рабочая гипотеза CBO вообще не работает для этого самосоединения (ваш последний запрос ожидает 31 миллион строк, тогда как только 450 выбрано!). Это довольно озадачивает, так как таблица имеет всего 1,5 М строк. Какую версию Oracle вы используете?

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

SELECT * FROM (
   SELECT (ROWNUM -1) AS COL1, ABC.*
     FROM (
      SELECT
              ft.COL1 AS OLD_COL1,
              [...],
              COUNT(*) OVER (PARTITION BY MEASURE_O) nb_0
      FROM tab3 ft
      ) ABC 
    WHERE nb_0 = 1
      AND ROWNUM - 1 <= 449
   ) v
 WHERE COL1 >= 0;

Вы также спрашивали, почему при первом запуске запроса требуется больше времени. Это связано с тем, что на работе работают кеши. На уровне базы данных есть SGA, где все блоки сначала копируются с диска, а затем могут быть прочитаны несколько раз (при первом запросе блока всегда выполняется физическое чтение). Затем некоторые системы также имеют независимый системный кеш, который будет быстрее возвращать данные, если он был недавно прочитан.

Для дальнейшего чтения:

a объяснение несоответствия данных, которое может привести к неправильным оценкам, и решение, включающее профили SQL.

Ответ 2

Как работает этот код?

SELECT ROWNUM - 1      AS col1
,      ft.col1         AS old_col1
,      ft.col2
,      ft.col3
,      ft.col4
,      ft.col5
,      ft.col6
,      ft.measure_0
,      ft.measure_1
,      ft.measure_2
,      ft.measure_3
,      ft.measure_4
,      ft.measure_5
,      ft.measure_6
,      ft.measure_7
,      ft.measure_8
,      ft.measure_9
FROM   tab3 ft
WHERE  NOT EXISTS (SELECT NULL
                   FROM   tab3   ft_prime
                   WHERE  ft_prime.measure_0 = ft.measure_0
                   AND    ft_prime.ROWID <> ft.ROWID)
AND    ROWNUM <= 450;