Два индекса / Oracle

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

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

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

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / Oracle [игнор отключен] [закрыт для гостей] / Два индекса

8 сообщений из 8, страница 1 из 1

Два индекса

#39285864

2индекса

Гость

Привет,

Такая вот проблема. На таблице есть два уникальных индекса - one_idx(column1, column2) и two_idx(column2, column1). one_idx - первичный ключ к тому же. Т.е. индексы отличаются только порядком полей. column1 - varchar2, column2 - number.

На продакшн БД (12.1.0.2) оптимайзер выбирает индекс one_idx и запрос выполняется на примерно 30% быстрее, чем в тестовой БД (та же версия), где оптимайзер выбирает индекс two_idx. По статистике и там и там 12 consistent gets. Clustering factor у индексов практически одинаковый в обеих БД: продакшен - 340433(one) и 333182(two), тестовая - 322195(one) и 322224(two).

У меня два вопроса - по каким причинам оптимайзер может выбирать другой индекс в тестовой БД?
И почему может быть такая заметная разница во времени выполнения запроса если оба индекса уникальные и количество conistent gets одинаковое?

Из 10053 трассировки:

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.

  Index: TWO_IDX  Col#: 2 1
  LVLS: 2  #LB: 3233  #DK: 1196279  LB/K: 1.00  DB/K: 1.00  CLUF: 322224.00  NRW: 1196279.00 SSZ: 0.00 LGR: 0.00 CBK: 0.00 GQL: 0.00 CHR: 0.00 KQDFLG: 1 BSZ: 1
  KKEISFLG: 1 
  Index: ONE_IDX  Col#: 1 2
  LVLS: 2  #LB: 3233  #DK: 1196279  LB/K: 1.00  DB/K: 1.00  CLUF: 322195.00  NRW: 1196279.00 SSZ: 0.00 LGR: 0.00 CBK: 0.00 GQL: 0.00 CHR: 0.00 KQDFLG: 1 BSZ: 1
  KKEISFLG: 1 

 ****** Costing Index ONE_IDX
  SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_SCAN
  ColGroup Usage:: PredCnt: 2  Matches Full: #4  Partial:  Sel: 8.3593e-07
  SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
  ColGroup Usage:: PredCnt: 2  Matches Full: #4  Partial:  Sel: 8.3593e-07
  Access Path: index (UniqueScan)
    Index: ONE_IDX
    resc_io: 3.000000  resc_cpu: 23464
    ix_sel: 8.3593e-07  ix_sel_with_filters: 8.3593e-07 
    Cost: 1.000241  Resp: 1.000241  Degree: 1
 ****** Costing Index TWO_IDX
  SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_SCAN
  ColGroup Usage:: PredCnt: 2  Matches Full: #4  Partial:  Sel: 8.3593e-07
  SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
  ColGroup Usage:: PredCnt: 2  Matches Full: #4  Partial:  Sel: 8.3593e-07
  Access Path: index (AllEqUnique)
    Index: TWO_IDX
    resc_io: 3.000000  resc_cpu: 23464
    ix_sel: 8.3593e-07  ix_sel_with_filters: 8.3593e-07 
    Cost: 1.000241  Resp: 1.000241  Degree: 1
 ****** Costing Index ONE_IDX
  SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_SCAN
  ColGroup Usage:: PredCnt: 2  Matches Full: #4  Partial:  Sel: 8.3593e-07
  SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
  ColGroup Usage:: PredCnt: 2  Matches Full: #4  Partial:  Sel: 8.3593e-07
  Access Path: index (AllEqUnique)
    Index: ONE_IDX
    resc_io: 3.000000  resc_cpu: 23464
    ix_sel: 8.3593e-07  ix_sel_with_filters: 8.3593e-07 
    Cost: 1.000241  Resp: 1.000241  Degree: 1
  ****** trying bitmap/domain indexes ******
  ****** finished trying bitmap/domain indexes ******
 One row Card: 1.000000
  Best:: AccessPath: IndexUnique
  Index: TWO_IDX
         Cost: 1.000241  Degree: 1  Resp: 1.000241  Card: 1.000000  Bytes: 0.000000

...

Рейтинг:

0 / 0

04.08.2016, 09:09:55

| Ответить | Цитировать | Написать

Два индекса

#39285877

Elic

Участник

Сообщения: 30 360

Рейтинг: 0 / 0

2индексаИ почемуА где предикативная часть запроса?

...

Рейтинг:

0 / 0

04.08.2016, 09:21:10

| Ответить | Цитировать | Написать

Два индекса

#39285894

2индекса

Гость

Elic,

Весь запрос:

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.

    SELECT 
      FROM TABLE1 P, TABLE1 O
     WHERE     P.COLUMN3 = O.COLUMN2(+)
           AND P.ORIGIN_SITEID = O.COLUMN1(+)
           AND P.COLUMN4 = 'N'
           AND (NVL(O.COLUMN4, 'Y') = 'Y' OR P.COLUMN3 = P.COLUMN2)
START WITH P.COLUMN1 = :B2 AND P.COLUMN2 = :B1
CONNECT BY     PRIOR P.COLUMN1 = P.COLUMN1
           AND PRIOR P.COLUMN3 = P.COLUMN2
           AND NVL(PRIOR O.COLUMN4, 'Y') = 'N'
           AND PRIOR P.COLUMN3 != PRIOR P.COLUMN2

План тест

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.

-----------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                   |     2 |    92 |     8  (25)| 00:00:01 |
|*  1 |  FILTER                         |                   |       |       |            |          |
|*  2 |   CONNECT BY WITH FILTERING     |                   |       |       |            |          |
|   3 |    NESTED LOOPS OUTER           |                   |     1 |    32 |     2   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID | TABLE1            |     1 |    20 |     1   (0)| 00:00:01 |
|*  5 |      INDEX UNIQUE SCAN          | TWO_IDX           |     1 |       |     1   (0)| 00:00:01 |
|   6 |     TABLE ACCESS BY INDEX ROWID | TABLE1            |     1 |    12 |     1   (0)| 00:00:01 |
|*  7 |      INDEX UNIQUE SCAN          | TWO_IDX           |     1 |       |     1   (0)| 00:00:01 |
|   8 |    NESTED LOOPS OUTER           |                   |     1 |    63 |     4   (0)| 00:00:01 |
|   9 |     NESTED LOOPS                |                   |     1 |    51 |     3   (0)| 00:00:01 |
|* 10 |      CONNECT BY PUMP            |                   |       |       |            |          |
|  11 |      TABLE ACCESS BY INDEX ROWID| TABLE1            |     1 |    20 |     1   (0)| 00:00:01 |
|* 12 |       INDEX UNIQUE SCAN         | TWO_IDX           |     1 |       |     1   (0)| 00:00:01 |
|  13 |     TABLE ACCESS BY INDEX ROWID | TABLE1            |     1 |    12 |     1   (0)| 00:00:01 |
|* 14 |      INDEX UNIQUE SCAN          | TWO_IDX           |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

План продакшн

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.

-----------------------------------------------------------------------------------------------
| Id  | Operation                       | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |             |     2 |    92 |     8  (25)| 00:00:01 |
|*  1 |  FILTER                         |             |       |       |            |          |
|*  2 |   CONNECT BY WITH FILTERING     |             |       |       |            |          |
|   3 |    NESTED LOOPS OUTER           |             |     1 |    32 |     2   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID | TABLE1      |     1 |    20 |     1   (0)| 00:00:01 |
|*  5 |      INDEX UNIQUE SCAN          | ONE_IDX     |     1 |       |     1   (0)| 00:00:01 |
|   6 |     TABLE ACCESS BY INDEX ROWID | TABLE1      |     1 |    12 |     1   (0)| 00:00:01 |
|*  7 |      INDEX UNIQUE SCAN          | ONE_IDX     |     1 |       |     1   (0)| 00:00:01 |
|   8 |    NESTED LOOPS OUTER           |             |     1 |    63 |     4   (0)| 00:00:01 |
|   9 |     NESTED LOOPS                |             |     1 |    51 |     3   (0)| 00:00:01 |
|* 10 |      CONNECT BY PUMP            |             |       |       |            |          |
|  11 |      TABLE ACCESS BY INDEX ROWID| TABLE1      |     1 |    20 |     1   (0)| 00:00:01 |
|* 12 |       INDEX UNIQUE SCAN         | ONE_IDX     |     1 |       |     1   (0)| 00:00:01 |
|  13 |     TABLE ACCESS BY INDEX ROWID | TABLE1      |     1 |    12 |     1   (0)| 00:00:01 |
|* 14 |      INDEX UNIQUE SCAN          | ONE_IDX     |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

...

Рейтинг:

0 / 0

04.08.2016, 09:34:53

| Ответить | Цитировать | Написать

Два индекса

#39285944

kinky cat

Участник

Откуда: с дивана23

Сообщения: 1 251

Рейтинг: 0 / 0

2индекса,

авторУ меня два вопроса - по каким причинам оптимайзер может выбирать другой индекс в тестовой БД?
И почему может быть такая заметная разница во времени выполнения запроса если оба индекса уникальные и количество conistent gets одинаковое?

в 12с на это 1001 причина может быть , начиная с различий в обычной dbms_stats статистики, и далее autocapture outline и кучи adaptive фич. Смори планы курсоров c DBMS_XPLAN.DISPLAY_CURSOR c ALLSTATS там внизу в Note самое интересное.

...

Рейтинг:

0 / 0

04.08.2016, 10:21:07

| Ответить | Цитировать | Написать

Два индекса

#39285969

2индекса

Гость

kinky cat,

optimizer_adaptive_features=FALSE в обеих БД потому что с ним у нас вообще ничего не работало после апгрейда.

Делал я с ALLSTATS, ничего не выдаёт особенного.

Там порядок полей в джойне меняется, а почему я не понимаю:

Прод:

Код: plsql

   5 - access("P"."SITEID"=:B2 AND "P"."PARCELNO"=:B1)

Тест

Код: plsql

  5 - access("P"."PARCELNO"=:B1 AND "P"."SITEID"=:B2)

И соответственно, разные индексы используются.

Вся статистика практически идентична в обеих базах.

...

Рейтинг:

0 / 0

04.08.2016, 10:39:00

| Ответить | Цитировать | Написать

Два индекса

#39285972

2индекса

Гость

В последнем сообщении поля из реальных таблиц, с которых я тест кейс делал. SITEID - COLUMN1, PARCELNO - COLUMN2

...

Рейтинг:

0 / 0

04.08.2016, 10:42:14

| Ответить | Цитировать | Написать

Два индекса

#39286004

AlexFF__|

Участник

Сообщения: 2 776

Рейтинг: 0 / 0

2индекса,

Индексы у тебя одинаковые, системе все равно какой из них использовать.
Фактор кластеризации при UniqueScan не должен иметь никакого значения.
Выбор индекса зависит тут от порядка их рассмотрения при работе оптимизатора.
авторИ почему может быть такая заметная разница во времени выполнения запроса если оба индекса уникальные и количество conistent gets одинаковое?
Сними 10046 и посмотри, чего гадать?

...

Рейтинг:

0 / 0

04.08.2016, 10:59:22

| Ответить | Цитировать | Написать

Два индекса

#39286005

Fogel

Участник

Сообщения: 400

Рейтинг: 0 / 0

предположу
если сделать

Код: plsql

1.
2.

select count(distinct COLUMN1) from table
select count(distinct COLUMN2) from table

то на проме и тесте результаты будут отличаться

поскольку
2индексаClustering factor у индексов практически одинаковый в обеих БД: продакшен - 340433(one) и 333182(two), тестовая - 322195(one) и 322224(two)
всё же отличаются: на проме у первого индекса CF больше, на тесте для обоих индексов они, можно сказать, равны, но для второго всё же больше - поэтому его оптимизатор и выбирает.

наборы данных на проме и тесте разные.

...

Рейтинг:

0 / 0

04.08.2016, 10:59:56

| Ответить | Цитировать | Написать

8 сообщений из 8, страница 1 из 1

Форумы / Oracle [игнор отключен] [закрыт для гостей] / Два индекса

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=52&fpage=208&tid=1887729]:	0ms
get settings:	9ms
get forum list:	17ms
check forum access:	3ms
check topic access:	3ms
track hit:	42ms
get topic data:	10ms
get forum data:	3ms
get page messages:	37ms
get tp. blocked users:	1ms
others:	225ms

total:	350ms

	Необходимые cookie
	Cookie для сбора статистики
	Cookie для маркетинга и рекламы