Глюк оптимизатора. / IBM DB2, WebSphere, IMS, U2

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / IBM DB2, WebSphere, IMS, U2 [игнор отключен] [закрыт для гостей] / Глюк оптимизатора.

9 сообщений из 9, страница 1 из 1

Глюк оптимизатора.

#39430039

Честный чайник

Гость

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

Выбираем:

Код: sql

1.
2.

Select * from changes.man
where fa='Иванов' and im='Иван' and ot='Иванович'

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

Выбираем:

Код: sql

1.
2.
3.

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;

...

Рейтинг:

0 / 0

30.03.2017, 13:08

| Ответить | Цитировать | Написать

Глюк оптимизатора.

#39430089

Mark Barinstein

Гость

Честный чайник,

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

Ну и планы запроса от db2extfmt покажите.

...

Рейтинг:

0 / 0

30.03.2017, 13:43

| Ответить | Цитировать | Написать

Глюк оптимизатора.

#39430824

Честный чайник

Гость

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

...

Рейтинг:

0 / 0

31.03.2017, 12:50

| Ответить | Цитировать | Написать

Глюк оптимизатора.

#39431139

Mark Barinstein

Гость

Честный чайник,

Да, выглядит очень странно.
Открывайте PMR в поддержку.

...

Рейтинг:

0 / 0

31.03.2017, 19:25

| Ответить | Цитировать | Написать

Глюк оптимизатора.

#39431154

Victor Metelitsa

Участник

Откуда: Тюмень

Сообщения: 2 626

Рейтинг: 0 / 0

Пока не дочитал. Вижу, что имеем

Код: sql

1.
2.
3.

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

Код: sql

1.
2.
3.
4.

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

и получаем

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

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

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

   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. А это ничто иное, как количество строк, которое должно было возвратиться. Если к обоим индексам приложены одни и те же предикаты с одним и теми же параметрами, оптимизатор должен давать одинаковую оценку...

...

Рейтинг:

0 / 0

31.03.2017, 20:07

| Ответить | Цитировать | Написать

Глюк оптимизатора.

#39431166

Victor Metelitsa

Участник

Откуда: Тюмень

Сообщения: 2 626

Рейтинг: 0 / 0

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.

		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

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.

			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 как бы давно не рекомендуется?

...

Рейтинг:

0 / 0

31.03.2017, 20:37

| Ответить | Цитировать | Написать

Глюк оптимизатора.

#39431167

Victor Metelitsa

Участник

Откуда: Тюмень

Сообщения: 2 626

Рейтинг: 0 / 0

Victor Metelitsa И всё равно, я ожидал бы, что RETURN на верхушке показывал бы одинаковое количество строк.
Чёто глючу.

...

Рейтинг:

0 / 0

31.03.2017, 20:40

| Ответить | Цитировать | Написать

Глюк оптимизатора.

#39431170

Victor Metelitsa

Участник

Откуда: Тюмень

Сообщения: 2 626

Рейтинг: 0 / 0

Вот почему

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

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

при

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.

		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 такие маленькие?

...

Рейтинг:

0 / 0

31.03.2017, 20:48

| Ответить | Цитировать | Написать

Глюк оптимизатора.

#39431187

Victor Metelitsa

Участник

Откуда: Тюмень

Сообщения: 2 626

Рейтинг: 0 / 0

Page size для таблицы не могу определить. Но надо думать, что 4096.

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

Сортировать в таких условиях такие строки на диске должно быть очень дорого. Но оптимизатор считает, что их должно остаться всего 16. Может, перестраховывается?

...

Рейтинг:

0 / 0

31.03.2017, 21:48

| Ответить | Цитировать | Написать

9 сообщений из 9, страница 1 из 1

Форумы / IBM DB2, WebSphere, IMS, U2 [игнор отключен] [закрыт для гостей] / Глюк оптимизатора.

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=43&fpage=10&tid=1600466]:	0ms
get settings:	11ms
get forum list:	12ms
check forum access:	3ms
check topic access:	3ms
track hit:	38ms
get topic data:	12ms
get forum data:	3ms
get page messages:	45ms
get tp. blocked users:	2ms
others:	312ms

total:	441ms