Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Какую статистику не собрал? Пляшет estimated rows / 11 сообщений из 11, страница 1 из 1
20.10.2017, 10:00
    #39539341
Pavel_PV
Pavel_PV
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Какую статистику не собрал? Пляшет estimated rows
Всем привет.
Столкнулся со странным поведением оптимизатора, хотя может сам где-то туплю.
Создадим:
Код: 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.
create table MAIN_T
(
 ID        NUMBER    NOT NULL,
 DATE_ID   DATE NOT NULL,
 DATE1_ID DATE)
PARTITION BY RANGE (DATE_ID)
INTERVAL(numtodsinterval(1,'DAY'))
(PARTITION VALUES LESS THAN (TO_DATE('2012-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')))
PARALLEL 8; 

declare 
 v$date date;
begin
for i in 1..20000
loop
   v$date := trunc(SYSDATE) + round(i/1000);
  insert into MAIN_T(ID,DATE_ID, DATE1_ID)
  values(i,v$date,trunc(SYSDATE)-90+i);
end loop;  
commit;
end;

alter table MAIN_T add constraint MAIN_PK primary key(ID);


create table D_TIME
(
  day_id DATE not null,
  my_test varchar2(10));
alter table D_TIME  add constraint DTI_PK primary key(DAY_ID);

insert into D_TIME(day_id)
select DATE_ID
from MAIN_T
UNION
select DATE1_ID
from MAIN_T;
commit;

alter table MAIN_T   add constraint MAIN_FK foreign key (DATE_ID)  references D_TIME(DAY_ID);


begin
 dbms_stats.gather_table_stats(ownname => '*****',tabname => 'MAIN_T',method_opt => 'FOR ALL COLUMNS SIZE 254');
 dbms_stats.gather_index_stats(ownname => '*****',indname => 'MAIN_PK');
 dbms_stats.gather_table_stats(ownname => '*****',tabname => 'D_TIME',method_opt => 'FOR ALL COLUMNS SIZE 254');
 dbms_stats.gather_index_stats(ownname => '*****',indname => 'DTI_PK'); 
end;



Теперь сделаем так:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
explain plan for
select A.*, B.day_id
from provar.MAIN_T A
inner join provar.D_TIME B on A.DATE_ID=B.day_id
where A.DATE_ID between trunc(SYSDATE) and trunc(SYSDATE)+10;
select *
from table(dbms_xplan.display);


Получим такой план:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
Plan hash value: 3214451937
 
----------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |        | 10500 |   205K|  5090   (1)| 00:00:01 |       |       |
|*  1 |  FILTER                   |        |       |       |            |          |       |       |
|   2 |   PARTITION RANGE ITERATOR|        | 10500 |   205K|  5090   (1)| 00:00:01 |   KEY |   KEY |
|*  3 |    TABLE ACCESS FULL      | MAIN_T | 10500 |   205K|  5090   (1)| 00:00:01 |   KEY |   KEY |
----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(TRUNC(SYSDATE@!)+10>=TRUNC(SYSDATE@!))
   3 - filter("A"."DATE_ID"<=TRUNC(SYSDATE@!)+10 AND "A"."DATE_ID">=TRUNC(SYSDATE@!))
 
Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold



Теперь сделаем так(в вывод добавили только одну колонку "B.my_test":
Код: plsql
1.
2.
3.
4.
5.
6.
7.
explain plan for
select A.*, B.day_id, B.my_test
from provar.MAIN_T A
inner join provar.D_TIME B on A.DATE_ID=B.day_id
where A.DATE_ID between trunc(SYSDATE) and trunc(SYSDATE)+10;
select *
from table(dbms_xplan.display);


получим такой план:
Код: 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.
Plan hash value: 1795835293
 
----------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |        |  5000 |   136K|  2315   (1)| 00:00:01 |       |       |
|*  1 |  FILTER                               |        |       |       |            |          |       |       |
|   2 |   NESTED LOOPS                        |        |  5000 |   136K|  2315   (1)| 00:00:01 |       |       |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| D_TIME |    10 |    80 |     3   (0)| 00:00:01 |       |       |
|*  4 |     INDEX RANGE SCAN                  | DTI_PK |    10 |       |     2   (0)| 00:00:01 |       |       |
|   5 |    PARTITION RANGE AND                |        |   500 | 10000 |   231   (1)| 00:00:01 |KEY(AP)|KEY(AP)|
|*  6 |     TABLE ACCESS FULL                 | MAIN_T |   500 | 10000 |   231   (1)| 00:00:01 |KEY(AP)|KEY(AP)|
----------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(TRUNC(SYSDATE@!)+10>=TRUNC(SYSDATE@!))
   4 - access("B"."DAY_ID">=TRUNC(SYSDATE@!) AND "B"."DAY_ID"<=TRUNC(SYSDATE@!)+10)
   6 - filter("A"."DATE_ID"="B"."DAY_ID" AND "A"."DATE_ID"<=TRUNC(SYSDATE@!)+10 AND 
              "A"."DATE_ID">=TRUNC(SYSDATE@!))
 
Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
   - this is an adaptive plan



Проблема. В первом случае rows-10500(корректно!), во втором 5000. Видно, что во втором случае план конечно меняется и кол-во строк в партиции оракл оценивает некорректно - 500 в одной => сумма тоже некорректна. Если уменьшать период, то кол-во строк в одной партиции оценивается всё хуже. Не могу сообразить какую статистику я не собрал?

P.S> FK создал для примера, наверное можно было и без него.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.10.2017, 10:32
    #39539369
andrey_anonymous
andrey_anonymous
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Какую статистику не собрал? Пляшет estimated rows
Pavel_PVПроблема. В первом случае rows-10500(корректно!), во втором 5000. Видно, что во втором случае план конечно меняется и кол-во строк в партиции оракл оценивает некорректно - 500 в одной => сумма тоже некорректна. Если уменьшать период, то кол-во строк в одной партиции оценивается всё хуже. Не могу сообразить какую статистику я не собрал?

Первый план не требует доступа к D_TIME, оценивается количество строк main_t за 10-дневный интервал.
Когда добавили в select-list поле из D_TIME - то исключить эту таблицу оптимизатор уже не может и строит план по соединению.
Ведущая D_TIME, оттуда ожидается 10 строк.
Далее заход NL-ем в MAIN_T на каждый отдельный ключ, найденный в D_TIME оптимизатор ожидает 500 строк из MAIN_T.
Это НЕ количество строк в разделе MAIN_T, это количество строк, которое, как полагает оптимизатор, вернет rowsource по ключу за один заход из NL.
На счет статистики... Попробуйте поиграть с гранулярностью (статистика может быть как глобальная на уровне таблицы, так и на уровне секции), но не уверен, что это что-либо изменит в указанном случае.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.10.2017, 10:34
    #39539371
Sergey_Korolev
Sergey_Korolev
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Какую статистику не собрал? Пляшет estimated rows
Pavel_PV,

в первом запрос за B.day_id не нужно было идти в таблицу
поскольку он в inner join provar.D_TIME B on A.DATE_ID=B.day_id
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.10.2017, 10:51
    #39539390
Pavel_PV
Pavel_PV
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Какую статистику не собрал? Пляшет estimated rows
[quot andrey_anonymous].
Далее заход NL-ем в MAIN_T на каждый отдельный ключ, найденный в D_TIME оптимизатор ожидает 500 строк из MAIN_T.
Это НЕ количество строк в разделе MAIN_T, это количество строк, которое, как полагает оптимизатор, вернет rowsource по ключу за один заход из NL.[quot]
Это я понимаю прекрасно, может написал как-то криво конечно:-" оракл оценивает некорректно - 500 в одной ". Ну дальше понятно 500*10=5000. Вопрос, откуда взялись эти 500? Как оракл получил эту цифру?
Ещё до темы я проверил статистику по партициям/таблице/гистограммы и там всё корректно, кол-во написано ровно то которое и должно быть. Не совсем понял причем тут гранулярность, можно подробнее?
Если так мысли в слух. Конечно понятно, что виной всему "join" и нежелание оракла глянуть в FK. В первом случае он использует FK и поэтому ошибки нет(благодаря FK получается нужный план), во втором же случае он уже опирается на другую статистику и начинает сильно ошибаться. На таких объемах, а ошибка уже в 2 раза. При этом - всё собрано, гистограммы есть. Я сначала подумал, что не всё знаю про FK и мне сейчас ткнут в доку - вот написано как надо. В общем хочется разобраться, на системе такая ошибка приводит к кривым планам.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.10.2017, 11:07
    #39539405
andrey_anonymous
andrey_anonymous
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Какую статистику не собрал? Пляшет estimated rows
Подробнее про гранулярность тут: http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_stats.htm#i1036461
По поводу как получилась оценка...
попробуйте посмотреть (explain plan) планчики для запросов:
select A.* from provar.MAIN_T A where A.DATE_ID = date'2017-10-20'; -- может использовать гистограмму для оценки селективности
select A.* from provar.MAIN_T A where A.DATE_ID = :1 ; -- затруднительно использовать гистограмму

Ведомый rowsource в NL похож на запрос с биндом.

Что касается рассуждений на тему FK и его использования... проиндексируйте хотя бы свой FK. О вреде неиндексированных FK где только не написано, включая заборы и подзаборья.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.10.2017, 11:09
    #39539407
andrey_anonymous
andrey_anonymous
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Какую статистику не собрал? Пляшет estimated rows
Ну и, само собой, наиболее подробный ответ на тему "как оно получилось" можно найти в трассе 10053
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.10.2017, 11:34
    #39539427
Pavel_PV
Pavel_PV
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Какую статистику не собрал? Пляшет estimated rows
andrey_anonymousПодробнее про гранулярность тут: http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_stats.htm#i1036461
По поводу как получилась оценка...
попробуйте посмотреть (explain plan) планчики для запросов:
select A.* from provar.MAIN_T A where A.DATE_ID = date'2017-10-20'; -- может использовать гистограмму для оценки селективности
select A.* from provar.MAIN_T A where A.DATE_ID = :1 ; -- затруднительно использовать гистограмму

Ведомый rowsource в NL похож на запрос с биндом.

Что касается рассуждений на тему FK и его использования... проиндексируйте хотя бы свой FK. О вреде неиндексированных FK где только не написано, включая заборы и подзаборья.
План для первого скорее всего будет использовать статистику по 1 партиции, там всё хорошо будет. Во втором думаю что-то похожее, нужно брать несколько партиций чтобы использовалась общая статистика по таблице.
10053 снять не проблема, но там формулу вроде не пишут.

За ссылку спасибо, прочту и попробую
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.10.2017, 11:30
    #39542257
Pavel_PV
Pavel_PV
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Какую статистику не собрал? Пляшет estimated rows
Сразу не отписался.
Про гранулярность конечно красиво написано, но не взлетело. Решил сразу сделать с
Код: sql
1.
granularity => 'ALL'



Странно это конечно, особенно странно что оракл использует fk как будто не на таблицу целиком, а на поле в ней.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.10.2017, 10:18
    #39543031
adaptive plan
adaptive plan
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Какую статистику не собрал? Пляшет estimated rows
Pavel_PV
Код: plsql
1.
2.
3.
Note
-----
   - this is an adaptive plan
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.11.2017, 12:33
    #39547324
Pavel_PV
Pavel_PV
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Какую статистику не собрал? Пляшет estimated rows
Не в этом корень проблемы, проверял.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.11.2017, 13:05
    #39547757
Rudyshin Sergey
Rudyshin Sergey
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Какую статистику не собрал? Пляшет estimated rows
проблема видимо в некорректном распределении данных по партициям
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
SQL> select PARTITION_NAME, NUM_ROWS   from user_tab_statistics where table_name = 'MAIN_T';

PARTITION_   NUM_ROWS
---------- ----------
		20000
SYS_P2346	    0
SYS_P2347	  499
SYS_P2348	 1000
...
SYS_P2366	 1000
SYS_P2367	  501

23 rows selected.



если данные сгенерировать равномерно
Код: plsql
1.
2.
insert into MAIN_T(ID,DATE_ID, DATE1_ID)
select rownum, date '2012-12-01' + mod (rownum, 20) - 1 d, sysdate from dual connect by rownum <= 2e4 order by d;




то планы начинают выглядеть более адекватно (но дату в запросах нужно другую указывать в таком случае )
Код: 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.
SQL> explain plan for
select A.*, B.day_id
from MAIN_T A
inner join D_TIME B on A.DATE_ID=B.day_id
where A.DATE_ID between trunc(date '2012-12-01') and trunc(date '2012-12-01')+10;

select *
from table(dbms_xplan.display);
  2    3    4    5  
Explained.

SQL> SQL>   2  
Plan hash value: 1101736113

---------------------------------------------------------------------------------------------------
| Id  | Operation		 | Name   | Rows  | Bytes | Cost (%CPU)| Time	  | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	 |	  | 11000 |   214K|  3001   (1)| 00:00:01 |	  |	  |
|   1 |  PARTITION RANGE ITERATOR|	  | 11000 |   214K|  3001   (1)| 00:00:01 |	2 |    12 |
|*  2 |   TABLE ACCESS FULL	 | MAIN_T | 11000 |   214K|  3001   (1)| 00:00:01 |	2 |    12 |
---------------------------------------------------------------------------------------------------

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

   2 - filter("A"."DATE_ID"<=TO_DATE(' 2012-12-11 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
	      "A"."DATE_ID">=TO_DATE(' 2012-12-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

15 rows selected.

SQL> explain plan for
select A.*, B.day_id, B.my_test
from MAIN_T A
inner join D_TIME B on A.DATE_ID=B.day_id
where A.DATE_ID between trunc(date '2012-12-01') and trunc(date '2012-12-01')+10;
select *
from table(dbms_xplan.display);   2    3    4    5  
Explained.

SQL>   2  
Plan hash value: 3552288420

---------------------------------------------------------------------------------------------------------------
| Id  | Operation			     | Name   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		     |	      | 11000 |   300K|  3001	(1)| 00:00:01 |       |       |
|   1 |  NESTED LOOPS			     |	      | 11000 |   300K|  3001	(1)| 00:00:01 |       |       |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| D_TIME |    11 |    88 |     2	(0)| 00:00:01 |       |       |
|*  3 |    INDEX RANGE SCAN		     | DTI_PK |    11 |       |     1	(0)| 00:00:01 |       |       |
|   4 |   PARTITION RANGE AND		     |	      |  1000 | 20000 |   273	(1)| 00:00:01 |KEY(AP)|KEY(AP)|
|*  5 |    TABLE ACCESS FULL		     | MAIN_T |  1000 | 20000 |   273	(1)| 00:00:01 |KEY(AP)|KEY(AP)|
---------------------------------------------------------------------------------------------------------------

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

   3 - access("B"."DAY_ID">=TO_DATE(' 2012-12-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
	      "B"."DAY_ID"<=TO_DATE(' 2012-12-11 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   5 - filter("A"."DATE_ID"="B"."DAY_ID" AND "A"."DATE_ID"<=TO_DATE(' 2012-12-11 00:00:00',
	      'syyyy-mm-dd hh24:mi:ss') AND "A"."DATE_ID">=TO_DATE(' 2012-12-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

20 rows selected.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Какую статистику не собрал? Пляшет estimated rows / 11 сообщений из 11, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 487ms
Закрыть
start [/forum/topic.php?fid=52&mobile=1&tid=1884976]:
 0ms
get settings:
 9ms
get forum list:
 17ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 159ms
get topic data:
 11ms
get forum data:
 3ms
get page messages:
 59ms
get tp. blocked users:
 1ms
others: 222ms
total:  487ms
x
x
Закрыть


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