ReSQL.ru
     
powered by simpleCommunicator - 2.0.53     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / index range scan in parallel
12 сообщений из 12, страница 1 из 1
index range scan in parallel
    #39920587
mlc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
mlc
Участник
Всем привет.

Такой вопрос - есть миллиардная таблица, из которой запрашиваются данные по индексу, а именно INDEX RANGE SCAN. Объем запрошенных по индексу данных 21 миллион строк, из которых после наложенной фильтрации, остается чуть меньше трех миллионов. Фактор кластеризации индекса близок к количеству строк.
Пруф в виде куска из статистики:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
|* 63 |   TABLE ACCESS BY INDEX ROWID BATCHED|  TCLASS_MIT           |      1 |   2858K|00:01:11.31 |     441K|    309K|
|* 64 |    INDEX RANGE SCAN                  |  TCLASS_MIT_MODDATE   |      1 |     21M|00:00:06.04 |   95749 |  95325 |


Predicate Information (identified by operation id):
--------------------------------------------------- 
   63 - filter("C"."TKPF"=0)
   64 - access("C"."MODDATE">=:B2 AND "C"."STATE"=1 AND "C"."MODDATE"<=:B1)
       filter("C"."STATE"=1)


Из статистики видно, что данные из индекса поднимаются быстро, а вот из таблицы нет, я так полагаю ввиду плохого фактора кластеризации.
Основной вопрос - как можно увеличить скорость поднятия данных из таблицы? Может можно как-то распараллелить?
...
Рейтинг: 0 / 0
30.01.2020, 16:46
| Ответить | Цитировать | Написать  
index range scan in parallel
    #39920594
Dimitry Sibiryakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dimitry Sibiryakov
Участник
mlcкак можно увеличить скорость поднятия данных из таблицы?

Добавить TKPF в индекс.
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
30.01.2020, 16:54
| Ответить | Цитировать | Написать  
index range scan in parallel
    #39920596
mlc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
mlc
Участник
Dimitry Sibiryakov,

Менять структуру таблицы, индексов не могу.
...
Рейтинг: 0 / 0
30.01.2020, 17:04
| Ответить | Цитировать | Написать  
index range scan in parallel
    #39920748
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
mlc,

Версия оракла какая? Есть два трюка в зависимости от версии, но нужно будет менять текст запроса.
...
Рейтинг: 0 / 0
30.01.2020, 20:58
| Ответить | Цитировать | Написать  
index range scan in parallel
    #39920780
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
Пример:
 
тестовый DDL
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
create table TCLASS_MIT
as 
with gen as (select 1 from dual connect by level<=1e4)
select
   mod(rownum,5) as TKPF,
   date'2000-01-01'+rownum/24/60/60 as MODDATE,
   mod(rownum,2) state,
   rpad('x',50,'x') padding
from gen, gen g2
where rownum<=5e6;

create index TCLASS_MIT_MODDATE on TCLASS_MIT(MODDATE,state);


Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
with irs as (
  select/*+ inline */ *
  from (
     select
     rowid rid
     from TCLASS_MIT C
     where "C"."MODDATE">=:B2 AND "C"."STATE"=1 AND "C"."MODDATE"<=:B1
     union all select rowid from TCLASS_MIT where 1=0 
  )
)
select/*+ 
   no_merge(irs)
   leading(irs c)
   parallel(irs 8)
   no_adaptive_plan
*/
 *
from irs, TCLASS_MIT C
where irs.rid = c.rowid 
and "C"."TKPF"=0;


 
план
Код: plsql
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.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
Plan hash value: 3979385509

---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name               | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                    |  1250 | 96250 |   939   (1)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR               |                    |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)         | :TQ10000           |  1250 | 96250 |   939   (1)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    NESTED LOOPS               |                    |  1250 | 96250 |   939   (1)| 00:00:01 |  Q1,00 | PCWP |            |
|   4 |     VIEW                      |                    |  6251 | 75012 |    70   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|   5 |      VIEW                     |                    |  6251 | 75012 |    70   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|   6 |       UNION-ALL               |                    |       |       |            |          |  Q1,00 | PCWP |            |
|   7 |        PX SELECTOR            |                    |       |       |            |          |  Q1,00 | PCWP |            |
|*  8 |         FILTER                |                    |       |       |            |          |  Q1,00 | PCWP |            |
|*  9 |          INDEX RANGE SCAN     | TCLASS_MIT_MODDATE |  6250 |   140K|    70   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|* 10 |        FILTER                 |                    |       |       |            |          |  Q1,00 | PCWC |            |
|  11 |         PX BLOCK ITERATOR     |                    |  5000K|    57M|  1825   (1)| 00:00:01 |  Q1,00 | PCWC |            |
|  12 |          TABLE ACCESS FULL    | TCLASS_MIT         |  5000K|    57M|  1825   (1)| 00:00:01 |  Q1,00 | PCWP |            |
|* 13 |     TABLE ACCESS BY USER ROWID| TCLASS_MIT         |     1 |    65 |     1   (0)| 00:00:01 |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      INDEX(@"SEL$2" "C"@"SEL$2" ("TCLASS_MIT"."MODDATE" "TCLASS_MIT"."STATE"))
      FULL(@"SEL$3" "TCLASS_MIT"@"SEL$3")
      PQ_CONCURRENT_UNION(@"SET$1")
      NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1")
      PQ_DISTRIBUTE(@"SEL$4" "C"@"SEL$4" NONE BROADCAST)
      USE_NL(@"SEL$4" "C"@"SEL$4")
      LEADING(@"SEL$4" "IRS"@"SEL$4" "C"@"SEL$4")
      ROWID(@"SEL$4" "C"@"SEL$4")
      NO_ACCESS(@"SEL$4" "IRS"@"SEL$4")
      OUTLINE_LEAF(@"SEL$4")
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE_LEAF(@"SET$1")
      OUTLINE_LEAF(@"SEL$3")
      OUTLINE_LEAF(@"SEL$2")
      ALL_ROWS
      DB_VERSION('18.1.0')
      OPTIMIZER_FEATURES_ENABLE('18.1.0')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

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

   8 - filter(TO_DATE(:B1)>=TO_DATE(:B2))
   9 - access("C"."MODDATE">=:B2 AND "C"."STATE"=1 AND "C"."MODDATE"<=:B1)
       filter("C"."STATE"=1)
  10 - filter(NULL IS NOT NULL)
  13 - filter("C"."TKPF"=0)

Note
-----
   - Degree of Parallelism is 8 because of table property

...
Рейтинг: 0 / 0
30.01.2020, 21:50
| Ответить | Цитировать | Написать  
index range scan in parallel
    #39920843
booby
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
booby
Участник
xtender,

С этим примером ясно.
Этот трюк очень старый, всем известный, в своей идее описан еще у Dan Tow, "SQL Tuning", O'Reilly 2003 (предыдущее перед этим издание переводилось на русский, если не ошибаюсь),
где он пишет, что трюк этот, для тех, кто с Oracle, существовал всегда, но после появления стоимостного оптимизатора стал требовать внимания к собственному разуму нарождающегося интеллекта, а здесь писатель главный.
От версии Oracle зависит способ борьбы с оптимизатором, чтобы тот не портил задумку создателя запроса
- обычно no_merge, хотя я бы, наверно, предпочел rownum > 0 в факторизованной части.
Вероятно, union all - попытка попреследовать эту же цель.
Здесь добавлен no_adaptive_plan для обрубания искусственного интеллекта современных версий.
До появления TABLE ACCESS BY INDEX ROWID BATCHED
этот трюк был почти единственной панацеей, спасающей от db file sequential read
на доступе к табличным блокам при TABLE ACCESS BY INDEX ROWID.
Параллельность, это хорошо на больших таблицах, и здесь компенсирует потерю вроде как
обещанной кластеризации rowid по табличным блокам при TABLE ACCESS BY INDEX ROWID BATCHED.
В 11м Oracle был самостоятельный хинт cluster_by_rowid(rowsource) для подобной цели.

Вопроса у меня два:
a) Правильно ли я понимаю, что TABLE ACCESS BY INDEX ROWID BATCHED автоматически
обеспечивает всё то, что ранее достигалось использованием cluster_by_rowid, но без явной
промежуточной сортировки по блокам таблицы?
б) какой у тебя второй трюк в рукаве?
...
Рейтинг: 0 / 0
31.01.2020, 00:11
| Ответить | Цитировать | Написать  
index range scan in parallel
    #39920846
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
booby,

Столько написал, а ничего не понял.... Прочитай вопрос тс и Посмотри на план ещё раз...

Зы. Что конкретно у дэна тау ты счёл схожим трюком?
...
Рейтинг: 0 / 0
31.01.2020, 00:24
| Ответить | Цитировать | Написать  
index range scan in parallel
    #39920847
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
booby
В 11м Oracle был самостоятельный хинт cluster_by_rowid
он есть и сейчас

booby
До появления TABLE ACCESS BY INDEX ROWID BATCHED
этот трюк был почти единственной панацеей, спасающей от db file sequential read
ни BATCHED, ни SORT CLUSTER BY ROWID не "спасают от db file sequential read". Они лишь упорядочивают доступ. Если бы я акцентировался на этом, я бы добавил сортировку в IRS по rowid, но вопрос был не в этом и я не хочу акцентироваться на сравнении скорости случайного чтения с последовательным - у всех железо разное.

booby
a) Правильно ли я понимаю, что TABLE ACCESS BY INDEX ROWID BATCHED автоматически
обеспечивает всё то, что ранее достигалось использованием cluster_by_rowid, но без явной
промежуточной сортировки по блокам таблицы?
нет, BATCHED работает не так как SORT CLUSTER BY ROWID: https://blog.pythian.com/batched-table-access/

booby
б) какой у тебя второй трюк в рукаве?
этот же метод, но в варианте, который пойдет для версий до 12.2 - т.е. materialize + fts parallel.
...
Рейтинг: 0 / 0
31.01.2020, 00:35
| Ответить | Цитировать | Написать  
index range scan in parallel
    #39920848
booby
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
booby
Участник
xtender
booby,

Столько написал, а ничего не понял.... Прочитай вопрос тс и Посмотри на план ещё раз...

Зы. Что конкретно у дэна тау ты счёл схожим трюком?

Он там несколько раз упоминается , прямой поиск по rowid в тексте дает, например,
пример ранее объясненного принципа для случая "сложного соединения" (3х таблиц(!)):

Chapter 6. Deducing the Best Execution Plan
6.4.A Special Cases
6.4.1 The Oracle Solution

Где рецепт приводится как специфический для Oracle способ отделения индексного поиска от доступа к таблице.


PS
С Oracle я впервые столкнулся в 2007, была уже 9ка, все отлично работало по заветам Дена,
без всяких материалайз, если мерж подзапроса во from подавить,
и мнение оптимизатора о порядке соединения таблиц нейтрализовать.
...
Рейтинг: 0 / 0
31.01.2020, 00:53
| Ответить | Цитировать | Написать  
index range scan in parallel
    #39920852
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
booby
Он там несколько раз упоминается , прямой поиск по rowid в тексте дает, например,
пример ранее объясненного принципа для случая "сложного соединения" (3х таблиц(!)):

Chapter 6. Deducing the Best Execution Plan
6.4.A Special Cases
6.4.1 The Oracle Solution

Где рецепт приводится как специфический для Oracle способ отделения индексного поиска от доступа к таблице.
ясно прочто что ты говоришь, но к контексту этого топика это не имеет никакого отношения. Более того, в то время, насколько я помню, оракл даже не умел еще выполнять фильтр по подзапросам между IRS и table access by rowid.


booby
С Oracle я впервые столкнулся в 2007, была уже 9ка, все отлично работало по заветам Дена,
без всяких материалайз, если мерж подзапроса во from подавить,
и мнение оптимизатора о порядке соединения таблиц нейтрализовать.
мнение о том, что оптимизатор стал хуже я категорически не разделяю. Особенно в данном топике, учитывая что parallel к RBO вообще никаким боком.

зы. Кому сдался RBO? https://blogs.oracle.com/optimizer/why-was-the-rule-hint-ignored
...
Рейтинг: 0 / 0
31.01.2020, 01:13
| Ответить | Цитировать | Написать  
index range scan in parallel
    #39920854
booby
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
booby
Участник
xtender,

ясно.
Успехов в труде.
...
Рейтинг: 0 / 0
31.01.2020, 01:28
| Ответить | Цитировать | Написать  
index range scan in parallel
    #39920905
mlc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
mlc
Участник
xtender,

Сорян, забыл указать - 12.2
...
Рейтинг: 0 / 0
31.01.2020, 09:37
| Ответить | Цитировать | Написать  
12 сообщений из 12, страница 1 из 1
Форумы / Oracle [игнор отключен] [закрыт для гостей] / index range scan in parallel
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&msg=39920905&tid=1881602]:
 0ms
get settings:
 12ms
get forum list:
 16ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 45ms
get topic data:
 11ms
get forum data:
 2ms
get page messages:
 50ms
get tp. blocked users:
 2ms
others: 275ms
total:  419ms
созд. / загр.: 419ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]