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

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

 SQL> desc tab3;
 Name                 Null?    Type
 ----------------------------------------------
 COL1                          NUMBER(38)
 COL2                          VARCHAR2(100)
 COL3                          VARCHAR2(36)
 COL4                          VARCHAR2(36)
 COL5                          VARCHAR2(4000)
 COL6                          VARCHAR2(4000)
 MEASURE_0                     VARCHAR2(4000)
 MEASURE_1                     VARCHAR2(4000)
 MEASURE_2                     VARCHAR2(4000)
 MEASURE_3                     VARCHAR2(4000)
 MEASURE_4                     VARCHAR2(4000)
 MEASURE_5                     VARCHAR2(4000)
 MEASURE_6                     VARCHAR2(4000)
 MEASURE_7                     VARCHAR2(4000)
 MEASURE_8                     VARCHAR2(4000)
 MEASURE_9                     VARCHAR2(4000)

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

SQL> select count(*) from (select measure_0 from tab3 group by measure_0 having count(*) = 1) abc;

  COUNT(*)
----------
    403664

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

SQL> set autotrace traceonly;

SQL> SELECT * FROM (
  2     SELECT
  3             (ROWNUM -1) AS COL1,
  4             ft.COL1         AS OLD_COL1,
  5             ft.COL2,
  6             ft.COL3,
  7             ft.COL4,
  8             ft.COL5,
  9             ft.COL6,
 10             ft.MEASURE_0,
 11             ft.MEASURE_1,
 12             ft.MEASURE_2,
 13             ft.MEASURE_3,
 14             ft.MEASURE_4,
 15             ft.MEASURE_5,
 16             ft.MEASURE_6,
 17             ft.MEASURE_7,
 18             ft.MEASURE_8,
 19             ft.MEASURE_9
 20     FROM tab3 ft
 21     WHERE MEASURE_0 IN
 22      (
 23             SELECT MEASURE_0
 24             FROM tab3
 25             GROUP BY MEASURE_0
 26             HAVING COUNT(*) = 1
 27      )
 28  ) ABC WHERE COL1 >= 0 AND COL1 <=449;

450 rows selected.

Elapsed: 00:00:01.90

Execution Plan
----------------------------------------------------------
Plan hash value: 3115757351

------------------------------------------------------------------------------------
| Id  | Operation               | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          |  1243 |    28M|   717K  (1)| 02:23:29 |
|*  1 |  VIEW                   |          |  1243 |    28M|   717K  (1)| 02:23:29 |
|   2 |   COUNT                 |          |       |       |            |          |
|*  3 |    HASH JOIN            |          |  1243 |    30M|   717K  (1)| 02:23:29 |
|   4 |     VIEW                | VW_NSO_1 |  1686K|  3219M|  6274   (2)| 00:01:16 |
|*  5 |      FILTER             |          |       |       |            |          |
|   6 |       HASH GROUP BY     |          |     1 |  3219M|  6274   (2)| 00:01:16 |
|   7 |        TABLE ACCESS FULL| TAB3     |  1686K|  3219M|  6196   (1)| 00:01:15 |
|   8 |     TABLE ACCESS FULL   | TAB3     |  1686K|    37G|  6211   (1)| 00:01:15 |
------------------------------------------------------------------------------------

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

   1 - filter("COL1">=0 AND "COL1"<=449)
   3 - access("MEASURE_0"="MEASURE_0")
   5 - filter(COUNT(*)=1)

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
        354  recursive calls
          0  db block gets
      46518  consistent gets
      45122  physical reads
          0  redo size
      43972  bytes sent via SQL*Net to client
        715  bytes received via SQL*Net from client
         31  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        450  rows processed

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

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

SQL> create index ind_tab3_orgid on tab3(COL1);

Index created.

Elapsed: 00:00:01.68
SQL> create index ind_tab3_msr_0 on tab3(measure_0);

Index created.

Elapsed: 00:00:01.83

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

Это план, когда требуется 2,9 секунды -

450 rows selected.

Elapsed: 00:00:02.92

Execution Plan
----------------------------------------------------------
Plan hash value: 240271480

-------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                |  1243 |    28M|   711K  (1)| 02:22:15 |
|*  1 |  VIEW                          |                |  1243 |    28M|   711K  (1)| 02:22:15 |
|   2 |   COUNT                        |                |       |       |            |          |
|   3 |    NESTED LOOPS                |                |       |       |            |          |
|   4 |     NESTED LOOPS               |                |  1243 |    30M|   711K  (1)| 02:22:15 |
|   5 |      VIEW                      | VW_NSO_1       |  1686K|  3219M|  6274   (2)| 00:01:16 |
|*  6 |       FILTER                   |                |       |       |            |          |
|   7 |        HASH GROUP BY           |                |     1 |  3219M|  6274   (2)| 00:01:16 |
|   8 |         TABLE ACCESS FULL      | TAB3           |  1686K|  3219M|  6196   (1)| 00:01:15 |
|*  9 |      INDEX RANGE SCAN          | IND_TAB3_MSR_0 |  1243 |       |     2   (0)| 00:00:01 |
|  10 |     TABLE ACCESS BY INDEX ROWID| TAB3           |  1243 |    28M|    44   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

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

   1 - filter("COL1">=0 AND "COL1"<=449)
   6 - filter(COUNT(*)=1)
   9 - access("MEASURE_0"="MEASURE_0")

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     660054  consistent gets
      22561  physical reads
          0  redo size
      44358  bytes sent via SQL*Net to client
        715  bytes received via SQL*Net from client
         31  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        450  rows processed

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

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

SQL> EXECUTE dbms_stats.gather_table_stats (ownname=>'EQUBE67DP', tabname=>'TAB3',estimate_percent=>null,cascade=>true);

PL/SQL procedure successfully completed.

Elapsed: 00:01:02.47
SQL> set autotrace off;
SQL> select COLUMN_NAME,NUM_DISTINCT,SAMPLE_SIZE,HISTOGRAM,LAST_ANALYZED from dba_tab_cols where table_name = 'TAB3' ;

COLUMN_NAME                    NUM_DISTINCT SAMPLE_SIZE HISTOGRAM       LAST_ANALYZED
------------------------------ ------------ ----------- --------------- ---------
COL1                                1502257     1502257 NONE            27-JUN-12
COL2                                      0             NONE            27-JUN-12
COL3                                      1     1502257 NONE            27-JUN-12
COL4                                      0             NONE            27-JUN-12
COL5                                1502257     1502257 NONE            27-JUN-12
COL6                                1502257     1502257 NONE            27-JUN-12
MEASURE_0                            405609     1502257 HEIGHT BALANCED 27-JUN-12
MEASURE_1                            128570     1502257 NONE            27-JUN-12
MEASURE_2                           1502257     1502257 NONE            27-JUN-12
MEASURE_3                            185657     1502257 NONE            27-JUN-12
MEASURE_4                               901     1502257 NONE            27-JUN-12
MEASURE_5                                17     1502257 NONE            27-JUN-12
MEASURE_6                              2202     1502257 NONE            27-JUN-12
MEASURE_7                              2193     1502257 NONE            27-JUN-12
MEASURE_8                                21     1502257 NONE            27-JUN-12
MEASURE_9                             27263     1502257 NONE            27-JUN-12

Я снова выполнил запрос

450 rows selected.

Elapsed: 00:00:02.95

Execution Plan
----------------------------------------------------------
Plan hash value: 240271480

-------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                |    31M|   718G|  8046   (2)| 00:01:37 |
|*  1 |  VIEW                          |                |    31M|   718G|  8046   (2)| 00:01:37 |
|   2 |   COUNT                        |                |       |       |            |          |
|   3 |    NESTED LOOPS                |                |       |       |            |          |
|   4 |     NESTED LOOPS               |                |    31M|    62G|  8046   (2)| 00:01:37 |
|   5 |      VIEW                      | VW_NSO_1       |  4057 |  7931K|  6263   (2)| 00:01:16 |
|*  6 |       FILTER                   |                |       |       |            |          |
|   7 |        HASH GROUP BY           |                |     1 | 20285 |  6263   (2)| 00:01:16 |
|   8 |         TABLE ACCESS FULL      | TAB3           |  1502K|  7335K|  6193   (1)| 00:01:15 |
|*  9 |      INDEX RANGE SCAN          | IND_TAB3_MSR_0 |     4 |       |     2   (0)| 00:00:01 |
|  10 |     TABLE ACCESS BY INDEX ROWID| TAB3           |   779K|    75M|     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

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

   1 - filter("COL1">=0 AND "COL1"<=449)
   6 - filter(COUNT(*)=1)
   9 - access("MEASURE_0"="MEASURE_0")


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     660054  consistent gets
      22561  physical reads
          0  redo size
      44358  bytes sent via SQL*Net to client
        715  bytes received via SQL*Net from client
         31  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        450  rows processed

На этот раз запрос вернулся в 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.

Ответ 1

Ответ 2