Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Oracle не понимает как считать кардиналити для OR и AND / 9 сообщений из 9, страница 1 из 1
28.08.2020, 11:51
    #39993360
Valergrad
Valergrad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Oracle не понимает как считать кардиналити для OR и AND
Вместо тысячи слов:

(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, 12:19
    #39993380
Maxim Demenko
Maxim Demenko
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Oracle не понимает как считать кардиналити для OR и AND
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:23
    #39993383
Asmodeus
Asmodeus
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Oracle не понимает как считать кардиналити для OR и AND
Valergrad,

"Логическое И" выполняется до "Логического ИЛИ" (скобки для красоты по сути).
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.08.2020, 12:31
    #39993391
Valergrad
Valergrad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Oracle не понимает как считать кардиналити для OR и AND
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:38
    #39993394
Valergrad
Valergrad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Oracle не понимает как считать кардиналити для OR и AND
Причем проблема судя по всему именно в 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, 14:26
    #39993457
Asmodeus
Asmodeus
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Oracle не понимает как считать кардиналити для OR и AND
Valergrad
Причем проблема судя по всему именно в dynamic_sampling.
Ну я бы не сказал, что это "проблема". Sampling учитывает только часть данных (для level=2 берется 64 блока таблицы и не учитывает условия), и распределение данных в выбранных блоках вполне может отличаться от распределения всей таблицы.

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

?

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

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

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


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


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