ReSQL.ru
     
powered by simpleCommunicator - 2.0.51     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Oracle не понимает как считать кардиналити для OR и AND
9 сообщений из 9, страница 1 из 1
Oracle не понимает как считать кардиналити для OR и AND
    #39993360
Valergrad
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Valergrad
Участник
Вместо тысячи слов:

(oracle 11.2.0.4)

Код: 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.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
create table test1 ( x number, y number );

insert into test1
with gen as ( select level lev from dual connect by level <= 1000 )
select g1.lev, g2.lev from gen g1, gen g2;

commmt;


explain plan for
select * from test1;


1	Plan hash value: 4122059633
2	 
3	-----------------------------------------------------------------------------------
4	| Id  | Operation                 | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
5	-----------------------------------------------------------------------------------
6	|   0 | SELECT STATEMENT          |       |   816K|    20M|  1756   (1)| 00:00:01 |
7	|   1 |  TABLE ACCESS STORAGE FULL| TEST1 |   816K|    20M|  1756   (1)| 00:00:01 |
8	-----------------------------------------------------------------------------------
9	 
10	Note
11	-----
12	   - dynamic sampling used for this statement (level=2)


explain plan for
select * from test1
where x = 1;


1	Plan hash value: 4122059633
2	 
3	-----------------------------------------------------------------------------------
4	| Id  | Operation                 | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
5	-----------------------------------------------------------------------------------
6	|   0 | SELECT STATEMENT          |       |   111 |  2886 |  1756   (1)| 00:00:01 |
7	|*  1 |  TABLE ACCESS STORAGE FULL| TEST1 |   111 |  2886 |  1756   (1)| 00:00:01 |
8	-----------------------------------------------------------------------------------
9	 
10	Predicate Information (identified by operation id):
11	---------------------------------------------------
12	 
13	   1 - storage("X"=1)
14	       filter("X"=1)
15	 
16	Note
17	-----
18	   - dynamic sampling used for this statement (level=2)




explain plan for
select * from test1
where x != 1;


1	Plan hash value: 4122059633
2	 
3	-----------------------------------------------------------------------------------
4	| Id  | Operation                 | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
5	-----------------------------------------------------------------------------------
6	|   0 | SELECT STATEMENT          |       |   816K|    20M|  1757   (1)| 00:00:01 |
7	|*  1 |  TABLE ACCESS STORAGE FULL| TEST1 |   816K|    20M|  1757   (1)| 00:00:01 |
8	-----------------------------------------------------------------------------------
9	 
10	Predicate Information (identified by operation id):
11	---------------------------------------------------
12	 
13	   1 - storage("X"<>1)
14	       filter("X"<>1)
15	 
16	Note
17	-----
18	   - dynamic sampling used for this statement (level=2)


explain plan for
select * from test1
where x != 1 and y = 5;

   

1	Plan hash value: 4122059633
2	 
3	-----------------------------------------------------------------------------------
4	| Id  | Operation                 | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
5	-----------------------------------------------------------------------------------
6	|   0 | SELECT STATEMENT          |       |   481 | 12506 |  1757   (1)| 00:00:01 |
7	|*  1 |  TABLE ACCESS STORAGE FULL| TEST1 |   481 | 12506 |  1757   (1)| 00:00:01 |
8	-----------------------------------------------------------------------------------
9	 
10	Predicate Information (identified by operation id):
11	---------------------------------------------------
12	 
13	   1 - storage("Y"=5 AND "X"<>1)
14	       filter("Y"=5 AND "X"<>1)
15	 
16	Note
17	-----
18	   - dynamic sampling used for this statement (level=2)


explain plan for
select * from test1
where x = 1 
or ( x != 1 and y = 5 );


1	Plan hash value: 4122059633
2	 
3	-----------------------------------------------------------------------------------
4	| Id  | Operation                 | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
5	-----------------------------------------------------------------------------------
6	|   0 | SELECT STATEMENT          |       |   481 | 12506 |  1758   (1)| 00:00:01 |
7	|*  1 |  TABLE ACCESS STORAGE FULL| TEST1 |   481 | 12506 |  1758   (1)| 00:00:01 |
8	-----------------------------------------------------------------------------------
9	 
10	Predicate Information (identified by operation id):
11	---------------------------------------------------
12	 
13	   1 - storage("X"=1 OR "Y"=5 AND "X"<>1)
14	       filter("X"=1 OR "Y"=5 AND "X"<>1)
15	 
16	Note
17	-----
18	   - dynamic sampling used for this statement (level=2)



Обратите внимание на предикаты у последнего запроса. Оракл тупо потерял скобки, и оценивает совсем другой предикат.
Правда результат запроса правильный, но кардиналити во многих случаях будет совершенно неправильная.
...
Рейтинг: 0 / 0
28.08.2020, 11:51
| Ответить | Цитировать | Написать  
Oracle не понимает как считать кардиналити для OR и AND
    #39993380
Maxim Demenko
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Demenko
Участник
Valergrad,

Так ведь скобки вроде здесь избыточны - https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/About-SQL-Conditions.html#GUID-65B103FE-C00C-46A3-8173-А731ДБФш2Ч80 - или я туплю ?


Regards

Maxim
...
Рейтинг: 0 / 0
28.08.2020, 12:19
| Ответить | Цитировать | Написать  
Oracle не понимает как считать кардиналити для OR и AND
    #39993383
Asmodeus
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Asmodeus
Гость
Valergrad,

"Логическое И" выполняется до "Логического ИЛИ" (скобки для красоты по сути).
...
Рейтинг: 0 / 0
28.08.2020, 12:23
| Ответить | Цитировать | Написать  
Oracle не понимает как считать кардиналити для OR и AND
    #39993391
Valergrad
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Valergrad
Участник
Asmodeus,

вы правы насчет скобок. Но кардиналити все равно считается неправильно. Оно должно складываться из двух случаев ( за минусом их объединения ).
Например, если переписать запрос через union all мы видим что это происходит:

Код: 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.
explain plan for
select * from test1
where x = 1 
union all
select * from test1
where ( x != 1 and y = 5 );

1 Plan hash value: 1519871055
2  
3 ------------------------------------------------------------------------------------
4 | Id  | Operation                  | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
5 ------------------------------------------------------------------------------------
6 |   0 | SELECT STATEMENT           |       |   592 | 15392 |  3513   (1)| 00:00:01 |
7 |   1 |  UNION-ALL                 |       |       |       |            |          |
8 |*  2 |   TABLE ACCESS STORAGE FULL| TEST1 |   111 |  2886 |  1756   (1)| 00:00:01 |
9 |*  3 |   TABLE ACCESS STORAGE FULL| TEST1 |   481 | 12506 |  1757   (1)| 00:00:01 |
10  ------------------------------------------------------------------------------------
11   
12  Predicate Information (identified by operation id):
13  ---------------------------------------------------
14   
15     2 - storage("X"=1)
16         filter("X"=1)
17     3 - storage("Y"=5 AND "X"<>1)
18         filter("Y"=5 AND "X"<>1)
19   
20  Note
21  -----
22     - dynamic sampling used for this statement (level=2)
...
Рейтинг: 0 / 0
28.08.2020, 12:31
| Ответить | Цитировать | Написать  
Oracle не понимает как считать кардиналити для OR и AND
    #39993394
Valergrad
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Valergrad
Участник
Причем проблема судя по всему именно в dynamic_sampling.

Если мы соберем статистику, то оракл начинает применять формулы сложения вероятностей корректно.
Код: 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.
60.
61.
62.
63.
64.
65.
begin
       dbms_stats.gather_table_stats(ownname => user, tabname =>'TEST1'); 
end;
/


explain plan for
select * from test1
where x = 1;

1	Plan hash value: 4122059633
2	 
3	-----------------------------------------------------------------------------------
4	| Id  | Operation                 | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
5	-----------------------------------------------------------------------------------
6	|   0 | SELECT STATEMENT          |       |  1000 |  8000 |  1758   (1)| 00:00:01 |
7	|*  1 |  TABLE ACCESS STORAGE FULL| TEST1 |  1000 |  8000 |  1758   (1)| 00:00:01 |
8	-----------------------------------------------------------------------------------
9	 
10	Predicate Information (identified by operation id):
11	---------------------------------------------------
12	 
13	   1 - storage("X"=1)
14	       filter("X"=1)

explain plan for
select * from test1
where ( x != 1 and y = 5 );


1	Plan hash value: 4122059633
2	 
3	-----------------------------------------------------------------------------------
4	| Id  | Operation                 | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
5	-----------------------------------------------------------------------------------
6	|   0 | SELECT STATEMENT          |       |   999 |  7992 |  1758   (1)| 00:00:01 |
7	|*  1 |  TABLE ACCESS STORAGE FULL| TEST1 |   999 |  7992 |  1758   (1)| 00:00:01 |
8	-----------------------------------------------------------------------------------
9	 
10	Predicate Information (identified by operation id):
11	---------------------------------------------------
12	 
13	   1 - storage("Y"=5 AND "X"<>1)
14	       filter("Y"=5 AND "X"<>1)



explain plan for
select * from test1
where x = 1 or ( x != 1 and y = 5 );

1	Plan hash value: 4122059633
2	 
3	-----------------------------------------------------------------------------------
4	| Id  | Operation                 | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
5	-----------------------------------------------------------------------------------
6	|   0 | SELECT STATEMENT          |       |  1998 | 15984 |  1759   (1)| 00:00:01 |
7	|*  1 |  TABLE ACCESS STORAGE FULL| TEST1 |  1998 | 15984 |  1759   (1)| 00:00:01 |
8	-----------------------------------------------------------------------------------
9	 
10	Predicate Information (identified by operation id):
11	---------------------------------------------------
12	 
13	   1 - storage("X"=1 OR "Y"=5 AND "X"<>1)
14	       filter("X"=1 OR "Y"=5 AND "X"<>1)



В этом случае для OR формула правильная. 1000 для первого условия + 999 для второго условия и вычесть 1 для их объединения = получится 1998.

С Dynamic_sampling у него должно было получиться 481 для первого условия + 111 для второго условия и вычесть 1 для их объединения = 591.
...
Рейтинг: 0 / 0
28.08.2020, 12:38
| Ответить | Цитировать | Написать  
Oracle не понимает как считать кардиналити для OR и AND
    #39993457
Asmodeus
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Asmodeus
Гость
Valergrad
Причем проблема судя по всему именно в dynamic_sampling.
Ну я бы не сказал, что это "проблема". Sampling учитывает только часть данных (для level=2 берется 64 блока таблицы и не учитывает условия), и распределение данных в выбранных блоках вполне может отличаться от распределения всей таблицы.

Что получается после удаления статистики и
Код: plsql
1.
ALTER SESSION SET optimizer_dynamic_sampling=4;

?

Да и вообще с уровнями поиграться.
...
Рейтинг: 0 / 0
28.08.2020, 14:26
| Ответить | Цитировать | Написать  
Oracle не понимает как считать кардиналити для OR и AND
    #39993480
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
Valergrad,

Dynamic sampling не оценивает предикаты по отдельности, только вместе(исключая крайние условия типа разных access paths) , поэтому ему и складывать нечего. Всё ок
...
Рейтинг: 0 / 0
28.08.2020, 15:27
| Ответить | Цитировать | Написать  
Oracle не понимает как считать кардиналити для OR и AND
    #39993634
Valergrad
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Valergrad
Участник
xtender
Valergrad,

Dynamic sampling не оценивает предикаты по отдельности, только вместе(исключая крайние условия типа разных access paths) , поэтому ему и складывать нечего. Всё ок


Все равно какая-то странная фигня.
У меня есть табличка на 26 миллионов строк.
И набор сложных фильтров. В реальности фильтр проходит 24 миллиона из 26.
Динамик сэмплинг ( дефолтный, второго левела ) выдает по ним бред в виде 800 строк удовлетворяющих условию.
При этом если убрать OR и рассматривать условия по отдельности - все норм, оценка в миллионы строк, скажем 6 млн или 13 млн.
Казалось бы с OR должны получаться тоже миллионы - но там получаются вот такие бредовые цифры как будто вероятности перемножаются вместо того чтобы складываться.
...
Рейтинг: 0 / 0
28.08.2020, 19:53
| Ответить | Цитировать | Написать  
Oracle не понимает как считать кардиналити для OR и AND
    #39993662
Alexander Anokhin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexander Anokhin
Гость
Как вариант можешь включить трассиовку SQL и оптимизатора, в SQL трассировке можно будет посмотреть конкретные запросы от dynamic sampling, в трассировке оптимизатора более детальные рассчёты. Особенно интересно посмотреть что конкретно возвращают запросы dynamic sampling.
...
Рейтинг: 0 / 0
28.08.2020, 21:39
| Ответить | Цитировать | Написать  
9 сообщений из 9, страница 1 из 1
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Oracle не понимает как считать кардиналити для OR и AND
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&msg=39993480&tid=1880934]:
 0ms
get settings:
 8ms
get forum list:
 14ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 62ms
get topic data:
 11ms
get forum data:
 2ms
get page messages:
 52ms
get tp. blocked users:
 1ms
others: 314ms
total:  472ms
созд. / загр.: 472ms
x
x
Закрыть


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