Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / IBM DB2, WebSphere, IMS, U2, etc Новый топик    Ответить
 Глюк оптимизатора.  [new]
Честный чайник
Member

Откуда:
Сообщений: 87
DB2 9.7
Имеем таблицу миллион записей, 170 полей, поля без фанатизма т.е. BLOB и т.п. нет, длина записи 1400.

Выбираем:
Select * from changes.man
where fa='Иванов' and im='Иван' and ot='Иванович'

Выбирает десяток строчек мгновенно.

Выбираем:
Select * from changes.man
where fa='Иванов' and im='Иван' and ot='Иванович'
order by ChangeDate

1й раз думает минут 7, последующие секунд 40-50. Без order by - мгновенно, причём, этот факт до оптимизатора не доходит и он продолжает упорствовать. Если подождать минут 10-15 начинает думать больше 50 секунд.

При попытке сгенерить план запроса ругается:
[IBM][CLI Driver][DB2/NT64] SQL0220N Столбец "DB2ADMIN.EXPLAIN_OBJECT" таблицы объяснения "PAGES" не содержит подходящего определения или же отсутствует. SQLSTATE=55002

Т.е., я так понимаю, нарушилось что-то в данных, на основании которых оптимизируются запросы.

Очень хочется понять, куда копать.

На всякий случай индексы:
(Почему именно так, даже не догадываюсь - не моё)
+
ALTER TABLE "CHANGES "."MAN"
ADD PRIMARY KEY
("ID",
"CHANGEDATE");

CREATE INDEX "CHANGES "."MANFIO" ON "CHANGES "."MAN"
("FA" ASC,
"IM" ASC,
"OT" ASC,
"POL" ASC,
"RDAT" ASC,
"NPERS" ASC,
"CHANGEDATE" ASC,
"ID" ASC)

COMPRESS YES ALLOW REVERSE SCANS;

CREATE INDEX "CHANGES "."MANSNILS" ON "CHANGES "."MAN"
("NPERS" ASC,
"CHANGEDATE" ASC,
"ID" ASC)

COMPRESS YES ALLOW REVERSE SCANS;

CREATE INDEX "DB2ADMIN"."IDX_MAN_RE" ON "CHANGES "."MAN"
("RE" ASC,
"ID" ASC,
"CHANGEDATE" ASC)
PCTFREE 10
COLLECT SAMPLED DETAILED STATISTICS
COMPRESS YES ALLOW REVERSE SCANS;

CREATE UNIQUE INDEX "DB2ADMIN"."IDX1602020502141" ON "CHANGES "."MAN"
("CHANGEDATE" ASC,
"ID" ASC)

COLLECT SAMPLED DETAILED STATISTICS
COMPRESS YES ALLOW REVERSE SCANS;
30 мар 17, 13:08    [20350542]     Ответить | Цитировать Сообщить модератору
 Re: Глюк оптимизатора.  [new]
Mark Barinstein
Member

Откуда: Москва
Сообщений: 4694
Честный чайник,

Оптимизатор при построении плана запроса не использует explain таблицы.
Эти таблицы используются для получения пользователем плана запроса. У вас, видимо, они как-то не так создались.
Привести их в чувство можно так:
-- удалить
call SYSINSTALLOBJECTS('EXPLAIN', 'D', NULL, 'DB2ADMIN');
-- создать
call SYSINSTALLOBJECTS('EXPLAIN', 'C', NULL, 'DB2ADMIN');

Ну и планы запроса от db2extfmt покажите.
30 мар 17, 13:43    [20350750]     Ответить | Цитировать Сообщить модератору
 Re: Глюк оптимизатора.  [new]
Честный чайник
Member

Откуда:
Сообщений: 87
Mark Barinstein,

call SYSINSTALLOBJECTS('EXPLAIN', 'C', NULL, 'DB2ADMIN');
Помогло. Стало рисовать планы.

Поскольку табличка относительно большая, а со средневековыми интерфейсами я не в ладу, вывод получился великоват. Поэтому я избранные места процитирую, а архив с файликами приложу.

Без order by
+
******************** EXPLAIN INSTANCE ********************

DB2_VERSION: 09.07.6
SOURCE_NAME: SQLC2H23
SOURCE_SCHEMA: NULLID
SOURCE_VERSION:
EXPLAIN_TIME: 2017-03-31-12.36.26.322001
EXPLAIN_REQUESTER: DB2ADMIN

Database Context:
----------------
Parallelism: None
CPU Speed: 2.243635e-007
Comm Speed: 100
Buffer Pool size: 757056
Sort Heap size: 112539
Database Heap size: 60000
Lock List size: 871344
Maximum Lock List: 87
Average Applications: 1
Locks Available: 24258216

Package Context:
---------------
SQL Type: Dynamic
Optimization Level: 5
Blocking: Block All Cursors
Isolation Level: Cursor Stability



---------------- STATEMENT 1 SECTION 203 ----------------
QUERYNO: 1
QUERYTAG:
Statement Type: Select
Updatable: No
Deletable: No
Query Degree: 1

Original Statement:
------------------
SELECT *
FROM CHANGES."MAN"
where fa=' ' and Im=' ' and Ot=' '


Optimized Statement:
-------------------
SELECT Q1.ID AS "ID", Q1.RE AS "RE", Q1.RA AS "RA", Q1.FA AS "FA", Q1.IM AS
"IM", Q1.OT AS "OT", Q1.RDAT AS "RDAT", Q1.POL AS "POL", Q1.PUNKT AS
....................
Q1.KOL_FAKTRAB AS "KOL_FAKTRAB"
FROM CHANGES.MAN AS Q1
WHERE (Q1.OT = ' ') AND (Q1.IM = ' ') AND (Q1.FA = '
')

Access Plan:
-----------
Total Cost: 129.45
Query Degree: 1

Rows
RETURN
( 1)
Cost
I/O
|
16.3092
FETCH
( 2)
129.45
37.384
/---+----\
16.3092 1.05938e+007
RIDSCN TABLE: CHANGES
( 3) MAN
25.7303 Q1
2
|
16.3092
SORT
( 4)
25.73
2
|
16.3092
IXSCAN
( 5)
25.7284
2
|
1.05938e+007
INDEX: CHANGES
MANFIO
Q1


С order by

+
******************** EXPLAIN INSTANCE ********************

DB2_VERSION: 09.07.6
SOURCE_NAME: SQLC2H23
SOURCE_SCHEMA: NULLID
SOURCE_VERSION:
EXPLAIN_TIME: 2017-03-31-15.08.54.521001
EXPLAIN_REQUESTER: DB2ADMIN

Database Context:
----------------
Parallelism: None
CPU Speed: 2.243635e-007
Comm Speed: 100
Buffer Pool size: 757056
Sort Heap size: 112539
Database Heap size: 60000
Lock List size: 871344
Maximum Lock List: 87
Average Applications: 1
Locks Available: 24258216

Package Context:
---------------
SQL Type: Dynamic
Optimization Level: 5
Blocking: Block All Cursors
Isolation Level: Cursor Stability



---------------- STATEMENT 1 SECTION 203 ----------------
QUERYNO: 1
QUERYTAG:
Statement Type: Select
Updatable: No
Deletable: No
Query Degree: 1

Original Statement:
------------------
SELECT *
FROM CHANGES."MAN"
where fa=' ' and Im=' ' and Ot=' '
order by changedate


Optimized Statement:
-------------------
SELECT Q1.ID AS "ID", Q1.RE AS "RE", Q1.RA AS "RA", Q1.FA AS "FA", Q1.IM AS
"IM", Q1.OT AS "OT", Q1.RDAT AS "RDAT", Q1.POL AS "POL", Q1.PUNKT AS
.................................
Q1.KOL_FAKTRAB AS "KOL_FAKTRAB"
FROM CHANGES.MAN AS Q1
WHERE (Q1.OT = ' ') AND (Q1.IM = ' ') AND (Q1.FA = '
')
ORDER BY Q1.CHANGEDATE

Access Plan:
-----------
Total Cost: 3.39691e+007
Query Degree: 1

Rows
RETURN
( 1)
Cost
I/O
|
16.3092
FETCH
( 2)
3.39691e+007
2.64444e+006
/----+-----\
1.05938e+007 1.05938e+007
IXSCAN TABLE: CHANGES
( 3) MAN
126591 Q1
13530.7
|
1.05938e+007
INDEX: DB2ADMIN
IDX1602020502141
Q1


К сообщению приложен файл (order_no_order.zip - 66Kb) cкачать
31 мар 17, 12:50    [20354202]     Ответить | Цитировать Сообщить модератору
 Re: Глюк оптимизатора.  [new]
Mark Barinstein
Member

Откуда: Москва
Сообщений: 4694
Честный чайник,

Да, выглядит очень странно.
Открывайте PMR в поддержку.
31 мар 17, 19:25    [20355852]     Ответить | Цитировать Сообщить модератору
 Re: Глюк оптимизатора.  [new]
Victor Metelitsa
Member

Откуда: Тюмень
Сообщений: 2516
Пока не дочитал. Вижу, что имеем
SELECT * 
FROM CHANGES."MAN" 
where fa='      ' and Im='         ' and Ot='            '


SELECT * 
FROM CHANGES."MAN" 
where fa='      ' and Im='         ' and Ot='            ' 
order by changedate


и получаем

16.3092
IXSCAN
( 5)
25.7284
2
|
1.05938e+007
INDEX: CHANGES
MANFIO
Q1


1.05938e+007 1.05938e+007
IXSCAN TABLE: CHANGES
( 3) MAN
126591 Q1
13530.7
|
1.05938e+007
INDEX: DB2ADMIN
IDX1602020502141
Q1

Видим, что в одном случае над IXSCAN стоит 16.3092, а во втором 1.05938e+007. А это ничто иное, как количество строк, которое должно было возвратиться. Если к обоим индексам приложены одни и те же предикаты с одним и теми же параметрами, оптимизатор должен давать одинаковую оценку...
31 мар 17, 20:07    [20355918]     Ответить | Цитировать Сообщить модератору
 Re: Глюк оптимизатора.  [new]
Victor Metelitsa
Member

Откуда: Тюмень
Сообщений: 2516

Input Streams:
-------------
1) From Object CHANGES.MANFIO

Estimated number of rows: 1.05938e+007
Number of columns: 9
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q1.POL(A)+Q1.RDAT(A)+Q1.NPERS(A)
+Q1.CHANGEDATE(A)+Q1.ID(A)+Q1.$RID$+Q1.OT
+Q1.IM+Q1.FA

и


1) From Object DB2ADMIN.IDX1602020502141

Estimated number of rows: 1.05938e+007
Number of columns: 3
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q1.CHANGEDATE(A)+Q1.ID(A)+Q1.$RID$
Как я понимаю, в первый раз был полность. просканирован индекс, оттуда взяты $RID'ы, отсортированы, затем взяты нужные предположительные 16-17 строк. Во втором случае индекс был использован только для сортировки, и это странное поведениеЮ которое могло бы быть оправдано OPTIMIZE FOR (немного) ROWS. И всё равно, я ожидал бы, что RETURN на верхушке показывал бы одинаковое количество строк. Проблемы со сбором статистики? Или параметры реально не одинаковые?

Как бы то ни было, глюк-неглюк, а напрашивается что-то вроде
CREATE INDEX changes.man_fa_im_ot on changes.man(fa,im,ot);
REORG TABLE changes.man;
-- Number of Rows: 10593817
-- Table Overflow Record Count: 2387904
RUNSTATS ON TABLE changes.man WITH DISTRIBUTION AND DETAILED INDEXES ALL; -- SET PROFILE?

ну, и SMS tablespace как бы давно не рекомендуется?
31 мар 17, 20:37    [20355974]     Ответить | Цитировать Сообщить модератору
 Re: Глюк оптимизатора.  [new]
Victor Metelitsa
Member

Откуда: Тюмень
Сообщений: 2516
Victor Metelitsa
И всё равно, я ожидал бы, что RETURN на верхушке показывал бы одинаковое количество строк.

Чёто глючу.
31 мар 17, 20:40    [20355976]     Ответить | Цитировать Сообщить модератору
 Re: Глюк оптимизатора.  [new]
Victor Metelitsa
Member

Откуда: Тюмень
Сообщений: 2516
Вот почему

16.3092
IXSCAN
( 5)
25.7284
2
|
1.05938e+007
INDEX: CHANGES
MANFIO
Q1
при

Input Streams:
-------------
1) From Object CHANGES.MANFIO

Estimated number of rows: 1.05938e+007
Number of columns: 9
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q1.POL(A)+Q1.RDAT(A)+Q1.NPERS(A)
+Q1.CHANGEDATE(A)+Q1.ID(A)+Q1.$RID$+Q1.OT
+Q1.IM+Q1.FA

миллионов строк, а стоимость и I/O такие маленькие?
31 мар 17, 20:48    [20355986]     Ответить | Цитировать Сообщить модератору
 Re: Глюк оптимизатора.  [new]
Victor Metelitsa
Member

Откуда: Тюмень
Сообщений: 2516
Page size для таблицы не могу определить. Но надо думать, что 4096.

А TEMPSIZE: (Temporary Table Page Size) 4096.
и Width of Rows: 1387

Сортировать в таких условиях такие строки на диске должно быть очень дорого. Но оптимизатор считает, что их должно остаться всего 16. Может, перестраховывается?
31 мар 17, 21:48    [20356104]     Ответить | Цитировать Сообщить модератору
Все форумы / IBM DB2, WebSphere, IMS, U2, etc Ответить