Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Какую статистику не собрал? Пляшет estimated rows  [new]
Pavel_PV
Member

Откуда:
Сообщений: 51
Всем привет.
Столкнулся со странным поведением оптимизатора, хотя может сам где-то туплю.
Создадим:
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;


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

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); 

Получим такой план:
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":
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); 

получим такой план:
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 создал для примера, наверное можно было и без него.
20 окт 17, 10:00    [20884803]     Ответить | Цитировать Сообщить модератору
 Re: Какую статистику не собрал? Пляшет estimated rows  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 16420
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.
На счет статистики... Попробуйте поиграть с гранулярностью (статистика может быть как глобальная на уровне таблицы, так и на уровне секции), но не уверен, что это что-либо изменит в указанном случае.
20 окт 17, 10:32    [20884968]     Ответить | Цитировать Сообщить модератору
 Re: Какую статистику не собрал? Пляшет estimated rows  [new]
Sergey_Korolev
Member

Откуда:
Сообщений: 121
Pavel_PV,

в первом запрос за B.day_id не нужно было идти в таблицу
поскольку он в inner join provar.D_TIME B on A.DATE_ID=B.day_id
20 окт 17, 10:34    [20884977]     Ответить | Цитировать Сообщить модератору
 Re: Какую статистику не собрал? Пляшет estimated rows  [new]
Pavel_PV
Member

Откуда:
Сообщений: 51
[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 и мне сейчас ткнут в доку - вот написано как надо. В общем хочется разобраться, на системе такая ошибка приводит к кривым планам.
20 окт 17, 10:51    [20885043]     Ответить | Цитировать Сообщить модератору
 Re: Какую статистику не собрал? Пляшет estimated rows  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 16420
Подробнее про гранулярность тут: 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 где только не написано, включая заборы и подзаборья.
20 окт 17, 11:07    [20885101]     Ответить | Цитировать Сообщить модератору
 Re: Какую статистику не собрал? Пляшет estimated rows  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 16420
Ну и, само собой, наиболее подробный ответ на тему "как оно получилось" можно найти в трассе 10053
20 окт 17, 11:09    [20885106]     Ответить | Цитировать Сообщить модератору
 Re: Какую статистику не собрал? Пляшет estimated rows  [new]
Pavel_PV
Member

Откуда:
Сообщений: 51
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 снять не проблема, но там формулу вроде не пишут.

За ссылку спасибо, прочту и попробую
20 окт 17, 11:34    [20885197]     Ответить | Цитировать Сообщить модератору
 Re: Какую статистику не собрал? Пляшет estimated rows  [new]
Pavel_PV
Member

Откуда:
Сообщений: 51
Сразу не отписался.
Про гранулярность конечно красиво написано, но не взлетело. Решил сразу сделать с
granularity => 'ALL'


Странно это конечно, особенно странно что оракл использует fk как будто не на таблицу целиком, а на поле в ней.
26 окт 17, 11:30    [20901398]     Ответить | Цитировать Сообщить модератору
 Re: Какую статистику не собрал? Пляшет estimated rows  [new]
adaptive plan
Guest
Pavel_PV
Note
-----
   - this is an adaptive plan

27 окт 17, 10:18    [20905184]     Ответить | Цитировать Сообщить модератору
 Re: Какую статистику не собрал? Пляшет estimated rows  [new]
Pavel_PV
Member

Откуда:
Сообщений: 51
Не в этом корень проблемы, проверял.
3 ноя 17, 12:33    [20924493]     Ответить | Цитировать Сообщить модератору
 Re: Какую статистику не собрал? Пляшет estimated rows  [new]
Rudyshin Sergey
Member

Откуда: Владивосток
Сообщений: 59
проблема видимо в некорректном распределении данных по партициям
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.


если данные сгенерировать равномерно
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;



то планы начинают выглядеть более адекватно (но дату в запросах нужно другую указывать в таком случае )
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.
4 ноя 17, 13:05    [20927005]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить