Опять про игнорирование индексов / IBM DB2, WebSphere, IMS, U2

ReSQL.ru

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

Гость

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

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

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

Цитировать

Написать

Автор*:

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

Тема*:

Сообщение

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

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

Автор:

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

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

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

Вложение:

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

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

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

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

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

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

31 сообщений из 31, показаны все 2 страниц

все

Опять про игнорирование индексов

#35234466

uchuDB2

Гость

Всем привет!
Разбираюсь с DB2 и простая вещь меня ставит в тупик :-(
Есть две таблички мастер-деталь (хранилище чеков):

Код: plaintext

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.

CREATE TABLE WAREHOUSE.CHEQUEHEAD (
  SHOPID INTEGER NOT NULL,
  ID INTEGER NOT NULL,
  DATEOPERATION TIMESTAMP NOT NULL,
  CK_NUMBER INTEGER NOT NULL,
  SUMMA DECIMAL( 13 ,  4 ) DEFAULT  0 ,
  DISC_SUMM DECIMAL( 13 ,  4 ) DEFAULT  0 ,
  CASSIR VARCHAR( 50 ),
  CARDNUMBER VARCHAR( 20 )
)
--был просто индекс по DateOperation, но
--советчик "насоветовал" сделать такой:
CREATE INDEX WAREHOUSE.IDX_CHEQUEHEAD ON WAREHOUSE.CHEQUEHEAD
  (DATEOPERATION, ID, SHOPID) ALLOW REVERSE SCANS; 
--Типа ПК
CREATE UNIQUE INDEX WAREHOUSE.PK_CHEQUEHEAD
  ON WAREHOUSE.CHEQUEHEAD (ID , SHOPID ) ALLOW REVERSE SCANS;

CREATE TABLE WAREHOUSE.CHEQUEPOS (
  SHOPID INTEGER NOT NULL,
  ID INTEGER NOT NULL,
  CHEQUEID INTEGER NOT NULL,
  CH_POS INTEGER DEFAULT  0 ,
  QUANT DECIMAL( 10 ,  3 ) DEFAULT  0 ,
  PRICE DECIMAL( 13 ,  4 ) DEFAULT  0 ,
  CODE INTEGER NOT NULL,
  SUMMA DECIMAL( 13 ,  4 ) DEFAULT  0 ,
  DISC_SUMM DECIMAL( 13 ,  4 ) DEFAULT  0 
)

--Внешний ключ
ALTER TABLE WAREHOUSE.CHEQUEPOS ADD CONSTRAINT FK_CHEQUEPOS
FOREIGN KEY (CHEQUEID, SHOPID) REFERENCES WAREHOUSE.CHEQUEHEAD(ID, SHOPID)
ON DELETE CASCADE ON UPDATE NO ACTION
--индекс для FK
CREATE INDEX WAREHOUSE.IDX_CHEQUEPOS ON WAREHOUSE.CHEQUEPOS
(CHEQUEID, SHOPID) ALLOW REVERSE SCANS;
--Типа ПК
CREATE UNIQUE INDEX WAREHOUSE.PK_CHEQUEPOS
ON WAREHOUSE.CHEQUEPOS (ID , SHOPID ) ALLOW REVERSE SCANS;

Количество записей:
ChequeHead ~11млн
ChtquePos ~60млн
Статистика полностью собрана
Имею простейший запрос:

Код: plaintext

1.
2.
3.

select sum(p.summa) as summa
from warehouse.chequehead h
join warehouse.chequepos p on h.shopid = p.shopid and h.id = p.chequeid
where h.dateoperation between '2008-03-24-00.00.00.00' and '2008-03-31-00.00.00.00'

Если период задавать сутки или меньше - все классно и задействуются все индексы (по DateOperation для ChequeHead и по FK для ChequePos) - запрос отрабатывает мгновенно.
Стоит увеличить период и все, по ChequePos идет TBSCAN и все виснет намертво :-(((
db2advis выдает какую-ту хрень и обещает увеличение производительности на 3.2%, что меня ни как не устроит.
Хочу отметить, что с DB2 только разбираюсь. Плотно же работаю с Firebird и MSSQL, но обе эти СУБД позволяют "указать" оптимизатору подхватить индекс.
Что же мне сделать в DB2???

...

Рейтинг:

0 / 0

03.04.2008, 23:43

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

Опять про игнорирование индексов

#35234538

gardenman

Участник

Откуда: С-Петербург

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

Рейтинг: 0 / 0

На вскидку:
Я бы поменял порядок колонок в индексах

Код: plaintext

1.
2.
3.
4.
5.

CREATE INDEX WAREHOUSE.IDX_CHEQUEHEAD ON WAREHOUSE.CHEQUEHEAD
  (DATEOPERATION, SHOPID, ID) ALLOW REVERSE SCANS; 
...
CREATE INDEX WAREHOUSE.IDX_CHEQUEPOS ON WAREHOUSE.CHEQUEPOS
(SHOPID, CHEQUEID) ALLOW REVERSE SCANS;

А запрос переписал бы с помощью WITH.

Код: plaintext

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

 WITH 
     CHK AS (SELECT .... FROM WAREHOUSE.CHEQUEHEAD WHERE 
           dateoperation between '2008-03-24-00.00.00.00' 
            and '2008-03-31-00.00.00.00' 
            SELECTIVITY  0 . 0001 
     ),
    POS AS (SELECT ... FROM CHK, WAREHOUSE.CHEQUEPOS P WHERE ...)
 )
SELECT SUM(SUMMA) FROM POS

Причем до кучи указал бы селективность.
Поищите SELECTIVITY тут на форуме.

И еще, когда вы указываете явно параметры запроса - это одно.
А когда вы будите связывать их в будущем через переменные - это другое.
Имейте ввиду что можете получить разные вещи.

...

Рейтинг:

0 / 0

04.04.2008, 01:08

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

Опять про игнорирование индексов

#35235268

Mark Barinstein

Гость

Добрый день.
uchuDB2Хочу отметить, что с DB2 только разбираюсь. Плотно же работаю с Firebird и MSSQL, но обе эти СУБД позволяют "указать" оптимизатору подхватить индекс.
Что же мне сделать в DB2???Какая у вас версия db2 и фикспак?

...

Рейтинг:

0 / 0

04.04.2008, 11:52

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

Опять про игнорирование индексов

#35235418

uchuDB2

Гость

Mark BarinsteinКакая у вас версия db2 и фикспак?[/quot]
Добрый день.
У меня установлен DB2 Express-C 9.1.2

...

Рейтинг:

0 / 0

04.04.2008, 12:23

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

Опять про игнорирование индексов

#35235628

Mark Barinstein

Гость

uchuDB2У меня установлен DB2 Express-C 9.1.2К сожалению не уверен, что на express-c будет работать, но вы можете попробовать использовать optimization profiles .
Это те же указания оптимизатору.
Вкратце:
1.
Создаете таблицу SYSTOOLS.OPT_PROFILE:
db2 "call sysinstallobjects('opt_profiles', 'c', '', '')"
2.
У себя на диске создаете somefile.xml:

Код: plaintext

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

<?xml version="1.0" encoding="UTF-8"?>
<OPTPROFILE VERSION="9.1.0.0">
  <STMTPROFILE ID="Guidelines for my simplest query">
    <STMTKEY>
      <![CDATA[select sum(p.summa) as summa
from warehouse.chequehead h
join warehouse.chequepos p on h.shopid = p.shopid and h.id = p.chequeid
where h.dateoperation between ? and ?]]>
    </STMTKEY>
    <OPTGUIDELINES>
      <IXSCAN TABLE="h" INDEX="IDX_CHEQUEHEAD"/>
    </OPTGUIDELINES>
  </STMTPROFILE>
</OPTPROFILE>

И файл somefile.txt для импорта:

Код: plaintext

"MY_SCHEMA","MY_NAME","somefile.xml"

3.
Из командного окна db2cmd, соединившесь с базой, вставляете 1 запись в SYSTOOLS.OPT_PROFILE:

Код: plaintext

db2 IMPORT FROM somefile.txt OF DEL MODIFIED BY LOBSINFILE INSERT INTO SYSTOOLS.OPT_PROFILE

4.
В command center при соединении с базой выполняем:

Код: plaintext

SET CURRENT OPTIMIZATION PROFILE = MY_SCHEMA.MY_NAME

и смотрим план запроса

Код: plaintext

1.
2.
3.

select sum(p.summa) as summa
from warehouse.chequehead h
join warehouse.chequepos p on h.shopid = p.shopid and h.id = p.chequeid
where h.dateoperation between ? and ?

5. Сообщите, что получилось.

...

Рейтинг:

0 / 0

04.04.2008, 13:07

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

Опять про игнорирование индексов

#35235709

TORT

Участник

Сообщения: 1 000

Рейтинг: 0 / 0

На мой субъективный взгляд хранить дата_время в одном поле timestamp не есть очень хорошо... Всегда пытаюсь разбить на 2 поля... Мне кажется так ищется гораздо быстрее... Кстати, предметная облать та же: чеки, товары в них... ну и т.д.

...

Рейтинг:

0 / 0

04.04.2008, 13:28

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

Опять про игнорирование индексов

#35235733

gardenman

Участник

Откуда: С-Петербург

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

Рейтинг: 0 / 0

TORTНа мой субъективный взгляд хранить дата_время в одном поле timestamp не есть очень хорошо... Всегда пытаюсь разбить на 2 поля... Мне кажется так ищется гораздо быстрее... Кстати, предметная облать та же: чеки, товары в них... ну и т.д.

+1

У меня еще есть вопрос к uchuDB2 . Ну и ко всемв том числе.
Почему на этапе создания чека общую сумму по всем позициям не поместить в WAREHOUSE.CHEQUEHEAD? Почему сделано именно так как сделано?

...

Рейтинг:

0 / 0

04.04.2008, 13:34

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

Опять про игнорирование индексов

#35236160

uchuDB2

Гость

Mark Barinstein
Марк, спасибо! Обязательно попробую, когда добирусь до базы.

TORT, gardenman
На мой субъективный взгляд хранить дата_время в одном поле timestamp не есть очень хорошо... Всегда пытаюсь разбить на 2 поля... Мне кажется так ищется гораздо быстрее...
А на мой - без разницы :) (тем более, что я храню и дату и таймштамп)
И, должен заметить, что индек по полю DateOperation задействован. У меня в голове не укладывается почему не берется индекс по внешнему ключу. Причем, задаю период сутки - отрабатывает в момент, задаю период сутки + 1мин (кол-во чеков увеличивается на 3!) - индекс отваливается и идет таблскан на 20мин!!!!!

gardenmanУ меня еще есть вопрос к uchuDB2. Ну и ко всемв том числе.
Почему на этапе создания чека общую сумму по всем позициям не поместить в WAREHOUSE.CHEQUEHEAD? Почему сделано именно так как сделано?
Как нету? А поле Summa и Disc_Summ в табл. WAREHOUSE.CHEQUEHEAD
Просто я показал упращенный вариант запроса, а мне нада сделать НДС, Sum(Сумма) group by НДС, а для этого мне необходимо перебрать позиции чеков :-(

...

Рейтинг:

0 / 0

04.04.2008, 15:26

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

Опять про игнорирование индексов

#35236329

mitek

Участник

Сообщения: 589

Рейтинг: 0 / 0

Имхо, но строить DWH на Express-C идея по меньшей мере сомнительная.

...

Рейтинг:

0 / 0

04.04.2008, 16:03

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

Опять про игнорирование индексов

#35236356

gardenman

Участник

Откуда: С-Петербург

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

Рейтинг: 0 / 0

mitekИмхо, но строить DWH на Express-C идея по меньшей мере сомнительная.
Жить вообще глупо. От этого даже умирают.

...

Рейтинг:

0 / 0

04.04.2008, 16:10

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

Опять про игнорирование индексов

#35236732

uchuDB2

Гость

Mark Barinstein
Mark Barinstein5. Сообщите, что получилось.

Я добавил в xml-файл строку, надеюсь я правильно все понял :)

Код: plaintext

<IXSCAN TABLE="p" INDEX="WAREHOUSE.IDX_CHEQUEPOS"/>

Получился план с индексными чтениями! Но если вместо знаков "?" я подставляю период, то все как и прежде ;-(

Оппа. Попробовал запустить "свежий" редактор (Command Editor), подсоединился к базе без указания

Код: plaintext

SET CURRENT OPTIMIZATION PROFILE = CTO.CTO

Эффект тот же. Т.е. с "?" - индекс, с периодом - таблскан.
:-(((

...

Рейтинг:

0 / 0

04.04.2008, 17:47

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

Опять про игнорирование индексов

#35236736

uchuDB2

Гость

Да, и если со знаками "?" план удачный, значит ли это, что используя, например, в Delphi в запросе :dt1 and :dt2 будет такой же план. Или DB2 будет перестроивать план в зависимости от конкретных значений dt1 и dt2

...

Рейтинг:

0 / 0

04.04.2008, 17:49

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

Опять про игнорирование индексов

#35236777

Mark Barinstein

Гость

uchuDB2Да, и если со знаками "?" план удачный, значит ли это, что используя, например, в Delphi в запросе :dt1 and :dt2 будет такой же план. Или DB2 будет перестроивать план в зависимости от конкретных значений dt1 и dt2Никогда не работал с дельфи, но мне кажется, что оно должно делать prepare c маркерами, т.е. должно работать.
Оптимизатор db2 ищет точное совпадение текстов запросов, исключая лишние пробелы и управляющие символы.
Т.е. если вы будете использовать константы, то оно не будет оптимизироваться по профилю.
Другой способ, как вам выше советовали, сделать так (из db2cmd):

Код: plaintext

1.
2.
3.

db2set DB2_SELECTIVITY=ALL
db2stop
db2start

и писАть так:

Код: plaintext

1.
2.
3.

select sum(p.summa) as summa
from warehouse.chequehead h
join warehouse.chequepos p on h.shopid = p.shopid and h.id = p.chequeid
where h.dateoperation between '2008-03-24-00.00.00.00' and '2008-03-31-00.00.00.00' selectivity  0 . 000001

Оптимизатор, увидев такое, поймет, что это весьма удобный фильтр - по h.dateoperation, т.к. он имеет очень маленькую selectivity и, скорее всего, будет использовать индекс по этому полю.

...

Рейтинг:

0 / 0

04.04.2008, 18:06

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

Опять про игнорирование индексов

#35236879

uchuDB2

Гость

Mark Barinstein
Еще раз спасибо.
Небольшое уточнение: индекс по

Код: plaintext

where h.dateoperation between '2008-03-24-00.00.00.00' and '2008-03-31-00.00.00.00'

и так используется, я хочу что бы использовался индекс по warehouse.chequepos p

Код: plaintext

join warehouse.chequepos p on h.shopid = p.shopid and h.id = p.chequeid

Т.е. мне надо написать

Код: plaintext

join warehouse.chequepos p on h.shopid = p.shopid and h.id = p.chequeid selectivity  0 . 000001

???

...

Рейтинг:

0 / 0

04.04.2008, 18:53

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

Опять про игнорирование индексов

#35236971

gardenman

Участник

Откуда: С-Петербург

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

Рейтинг: 0 / 0

Код: plaintext

...warehouse.chequepos p on (h.shopid,h.id) = (p.shopid,p.chequeid) selectivity  0 . 000001

...

Рейтинг:

0 / 0

04.04.2008, 19:55

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

Опять про игнорирование индексов

#35237843

OlegA67

Гость

Mark Barinstein Какая у вас версия db2 и фикспак?
Добрый день Mark, не покажите как это должно выглядеть, для версии 8.2 FixPack 15. Заранее спасибо

...

Рейтинг:

0 / 0

06.04.2008, 05:42

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

Опять про игнорирование индексов

#35238712

uchuDB2

Гость

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

...

Рейтинг:

0 / 0

07.04.2008, 09:56

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

Опять про игнорирование индексов

#35239629

Mark Barinstein

Гость

Добрый день.
OlegA67Добрый день Mark, не покажите как это должно выглядеть, для версии 8.2 FixPack 15. Заранее спасибоК сожалению, мне не удалось заставить v8 подхватывать optimization profile, несмотря на то, что, вроде бы, это возможно, как описано здесь .
C selectivity должно работать.

...

Рейтинг:

0 / 0

07.04.2008, 14:04

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

Опять про игнорирование индексов

#35240141

gardenman

Участник

Откуда: С-Петербург

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

Рейтинг: 0 / 0

мне кажется была какая-то статья примерно на эту тему, как правильно собирать статистику.

...

Рейтинг:

0 / 0

07.04.2008, 15:44

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

Опять про игнорирование индексов

#35242183

Mark Barinstein

Гость

OlegA67 Mark Barinstein Какая у вас версия db2 и фикспак?
Добрый день Mark, не покажите как это должно выглядеть, для версии 8.2 FixPack 15. Заранее спасибоДобрался до v8.1.14 на linux.
При установленной переменной db2_optprofile, т.е. после
db2set db2_optprofile=YES
db2stop
db2start
похоже, что точно такой же оптимизационный профиль подхватывается, т.к. я на одних и тех же тестовых запросах получаю разные планы в зависимости от того, выполняю я команду
SET CURRENT OPTIMIZATION PROFILE=MY_SCHEMA.MY_NAME
или нет перед построением плана.

...

Рейтинг:

0 / 0

08.04.2008, 12:38

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

Опять про игнорирование индексов

#35242755

OlegA67

Гость

Спасибо буду пробовать

...

Рейтинг:

0 / 0

08.04.2008, 15:03

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

Период между сообщениями больше года.

Опять про игнорирование индексов

#38919813

ASukhov1986

Гость

Mark Barinstein,
У меня вот такой запрос:

Код: sql

SELECT * FROM METR.CSM_CUSTOMER WHERE DELETE_DATE IS NULL

Я добавил индекс IDX_DELETE_DATE, но так как по результатам выполнения выдаются примерно 90% записей, то выполняется TBLSCAN. Я разбираюсь с профилями оптимизации и хочу принудить оптимизатор использовать в этом запросе индекс IDX_DELETE_DATE, для этого я написал следующий профиль:

Код: xml

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

<?xml version="1.0" encoding="UTF-8"?>
<OPTPROFILE VERSION="9.5.0.808">
  <STMTPROFILE ID="Guidelines for my simplest query">
    <STMTKEY>
	<![CDATA[SELECT * FROM METR.CSM_CUSTOMER WHERE DELETE_DATE IS NULL]]>
    </STMTKEY>
    <OPTGUIDELINES>
      <IXSCAN TABLE="METR.CSM_CUSTOMER" INDEX="IDX_DELETE_DATE"/>
    </OPTGUIDELINES>
  </STMTPROFILE>
</OPTPROFILE>

Я выполнил приведенные выше рекомендации:
Mark Barinstein uchuDB2У меня установлен DB2 Express-C 9.1.2К сожалению не уверен, что на express-c будет работать, но вы можете попробовать использовать optimization profiles .
Это те же указания оптимизатору.
Вкратце:
1.
Создаете таблицу SYSTOOLS.OPT_PROFILE:
db2 "call sysinstallobjects('opt_profiles', 'c', '', '')"
2.
У себя на диске создаете somefile.xml:

Код: sql

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

<?xml version="1.0" encoding="UTF-8"?>
<OPTPROFILE VERSION="9.1.0.0">
  <STMTPROFILE ID="Guidelines for my simplest query">
    <STMTKEY>
      <![CDATA[select sum(p.summa) as summa
from warehouse.chequehead h
join warehouse.chequepos p on h.shopid = p.shopid and h.id = p.chequeid
where h.dateoperation between ? and ?]]>
    </STMTKEY>
    <OPTGUIDELINES>
      <IXSCAN TABLE="h" INDEX="IDX_CHEQUEHEAD"/>
    </OPTGUIDELINES>
  </STMTPROFILE>
</OPTPROFILE>

И файл somefile.txt для импорта:

Код: sql

"MY_SCHEMA","MY_NAME","somefile.xml"

3.
Из командного окна db2cmd, соединившесь с базой, вставляете 1 запись в SYSTOOLS.OPT_PROFILE:

Код: sql

db2 IMPORT FROM somefile.txt OF DEL MODIFIED BY LOBSINFILE INSERT INTO SYSTOOLS.OPT_PROFILE

4.
В command center при соединении с базой выполняем:

Код: sql

SET CURRENT OPTIMIZATION PROFILE = MY_SCHEMA.MY_NAME

и смотрим план запроса

Код: sql

1.
2.
3.
4.

select sum(p.summa) as summa
from warehouse.chequehead h
join warehouse.chequepos p on h.shopid = p.shopid and h.id = p.chequeid
where h.dateoperation between ? and ?

5. Сообщите, что получилось.
и
Mark Barinstein OlegA67пропущено...

Добрый день Mark, не покажите как это должно выглядеть, для версии 8.2 FixPack 15. Заранее спасибоДобрался до v8.1.14 на linux.
При установленной переменной db2_optprofile, т.е. после
db2set db2_optprofile=YES
db2stop
db2start
похоже, что точно такой же оптимизационный профиль подхватывается, т.к. я на одних и тех же тестовых запросах получаю разные планы в зависимости от того, выполняю я команду
SET CURRENT OPTIMIZATION PROFILE=MY_SCHEMA.MY_NAME
или нет перед построением плана.
Но запрос все равно не использует профиль оптимизации, а выполняет TBLSCAN.
Вопрос : Как мне сделать, чтобы этот профиль оптимизации использовался при выполнении моего запроса???
P.S. при добавлении

Код: sql

selectivity 0.000001

используется индекс IDX_DELETE_DATE, но я хочу этого добиться с помощью профиля оптимизации.

...

Рейтинг:

0 / 0

28.03.2015, 14:15

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

Опять про игнорирование индексов

#38920003

Mark Barinstein

Гость

ASukhov1986,

Control Center по какой-то причине не реагирует на текущие установки профиля для сессии. Но он реагирует на переменную CURRENTOPTIMIZATIONPROFILE в db2cli.ini. Т.е. вы можете выставить этот профиль на клиенте (там, где, скажем, база зарегистрирована под алиасом mydbalias ) так:

Код: plaintext

update cli cfg for section  mydbalias  using CURRENTOPTIMIZATIONPROFILE  MY_SCHEMA.MY_NAME

Либо получать план не в гарфическом виде, а при установленной в сессии CURRENT OPTIMIZATION PROFILE (в т.ч. и в сессии DB2 Command Center) командой:

Код: plaintext

explain all for  my_query

а потом получить план вручную, например, утилитой db2exfmt.

IBM Data Studio лишена этого недостатка.

...

Рейтинг:

0 / 0

28.03.2015, 21:12

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

Опять про игнорирование индексов

#38920225

ASukhov1986

Гость

Исходя из рекомендаций:
Mark BarinsteinASukhov1986,

Control Center по какой-то причине не реагирует на текущие установки профиля для сессии. Но он реагирует на переменную CURRENTOPTIMIZATIONPROFILE в db2cli.ini. Т.е. вы можете выставить этот профиль на клиенте (там, где, скажем, база зарегистрирована под алиасом mydbalias ) так:

Код: plaintext

update cli cfg for section  mydbalias  using CURRENTOPTIMIZATIONPROFILE  MY_SCHEMA.MY_NAME

Код: plaintext

explain all for  my_query

а потом получить план вручную, например, утилитой db2exfmt.

IBM Data Studio лишена этого недостатка.

выполнил следующие шаги:
1. в командной строке подключился к БД с помощью команды

Код: sql

db2 connect to CU242176

;
2. в командной строке установил для текущей сессии профиль оптимизации с помощью команды

Код: sql

db2 SET CURRENT OPTIMIZATION PROFILE = METR.PROFIL1

;
3. в командной строке получил план в не графическом виде с помощью команды:

Код: sql

db2 explain all for SELECT * FROM METR.CSM_CUSTOMER WHERE DELETE_DATE IS NULL

;
4. в командной строке получил отображение плана с помощью команды:

Код: sql

db2expln -database CU242176 -terminal -statement "SELECT * FROM METR.CSM_CUSTOMER WHERE DELETE_DATE IS NULL"

.
В результате индекс опять не использовался. Журнал моих действий:

Код: xml

C:\Program Files (x86)\IBM\SQLLIB\BIN>db2 connect to CU242176

   Информация соединения с базой данных

 Сервер баз данных           = DB2/NT 9.5.0
 ID авторизации SQL          = ANTON
 Алиас локальной базы данных = CU242176


C:\Program Files (x86)\IBM\SQLLIB\BIN>db2 SET CURRENT OPTIMIZATION PROFILE = METR.PROFIL1
DB20000I  Команда SQL выполнена успешно.

C:\Program Files (x86)\IBM\SQLLIB\BIN>db2 explain all for SELECT * FROM METR.CSM_CUSTOMER WHERE DELE
TE_DATE IS NULL
DB20000I  Команда SQL выполнена успешно.

C:\Program Files (x86)\IBM\SQLLIB\BIN>db2expln -database CU242176 -terminal -statement "SELECT * FRO
M METR.CSM_CUSTOMER WHERE DELETE_DATE IS NULL"

DB2 Universal Database Version 9.5, 5622-044 (c) Copyright IBM Corp. 1991, 2007
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL and XQUERY Explain Tool

DB2 Universal Database Version 9.5, 5622-044 (c) Copyright IBM Corp. 1991, 2007
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL and XQUERY Explain Tool

******************** DYNAMIC ***************************************

==================== STATEMENT ==========================================

        Isolation Level          = Cursor Stability
        Blocking                 = Block Unambiguous Cursors
        Query Optimization Class = 5

        Partition Parallel       = No
        Intra-Partition Parallel = No

        SQL Path                 = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM",
                                   "ANTON"


Statement:

  SELECT *
  FROM METR.CSM_CUSTOMER
  WHERE DELETE_DATE IS NULL


Section Code Page = 1208

Estimated Cost = 3389,229736
Estimated Cardinality = 19020,000000

Access Table Name = METR.CSM_CUSTOMER  ID = 2,21
|  #Columns = 48
|  Avoid Locking Committed Data
|  Evaluate Block/Data Predicates Before Locking Committed Row
|  Relation Scan
|  |  Prefetch: Eligible
|  Lock Intents
|  |  Table: Intent Share
|  |  Row  : Next Key Share
|  Sargable Predicate(s)
|  |  #Predicates = 1
|  |  Return Data to Application
|  |  |  #Columns = 48
Return Data Completion

End of section

...

Рейтинг:

0 / 0

29.03.2015, 10:00

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

Опять про игнорирование индексов

#38920240

ASukhov1986

Гость

Mark Barinstein,

Также выполнил шаги исходя их первой рекомендации с db2cli.ini, в результате выполнения действий индекс опять не использовался.
Вот шаги которые я выполнял:

Код: xml

C:\Program Files (x86)\IBM\SQLLIB\BIN>db2 connect to Cu242176

   Информация соединения с базой данных

 Сервер баз данных           = DB2/NT 9.5.0
 ID авторизации SQL          = ANTON
 Алиас локальной базы данных = CU242176


C:\Program Files (x86)\IBM\SQLLIB\BIN>db2 update cli cfg for section CU242176 using CURRENTOPTIMIZAT
IONPROFILE METR.PROFIL1
DB20000I  Команда UPDATE CLI CONFIGURATION выполнена успешно.

C:\Program Files (x86)\IBM\SQLLIB\BIN>db2 GET cli cfg for section CU242176

 Раздел:  CU242176
 -------------------------------------------------
   CURRENTOPTIMIZATIONPROFILE=METR.PROFIL1

C:\Program Files (x86)\IBM\SQLLIB\BIN>db2expln -database CU242176 -terminal -statement "SELECT * FRO
M METR.CSM_CUSTOMER WHERE DELETE_DATE IS NULL"

DB2 Universal Database Version 9.5, 5622-044 (c) Copyright IBM Corp. 1991, 2007
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL and XQUERY Explain Tool

DB2 Universal Database Version 9.5, 5622-044 (c) Copyright IBM Corp. 1991, 2007
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL and XQUERY Explain Tool

******************** DYNAMIC ***************************************

==================== STATEMENT ==========================================

        Isolation Level          = Cursor Stability
        Blocking                 = Block Unambiguous Cursors
        Query Optimization Class = 5

        Partition Parallel       = No
        Intra-Partition Parallel = No

        SQL Path                 = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM",
                                   "ANTON"


Statement:

  SELECT *
  FROM METR.CSM_CUSTOMER
  WHERE DELETE_DATE IS NULL


Section Code Page = 1208

Estimated Cost = 3389,229736
Estimated Cardinality = 19020,000000

Access Table Name = METR.CSM_CUSTOMER  ID = 2,21
|  #Columns = 48
|  Avoid Locking Committed Data
|  Evaluate Block/Data Predicates Before Locking Committed Row
|  Relation Scan
|  |  Prefetch: Eligible
|  Lock Intents
|  |  Table: Intent Share
|  |  Row  : Next Key Share
|  Sargable Predicate(s)
|  |  #Predicates = 1
|  |  Return Data to Application
|  |  |  #Columns = 48
Return Data Completion

End of section

...

Рейтинг:

0 / 0

29.03.2015, 10:39

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

Опять про игнорирование индексов

#38920289

Mark Barinstein

Гость

ASukhov1986,

После команды explain надо запускать db2exfmt, а не db2expln.
Команда ниже выводит в файл exfmt.txt последний план запроса из explain таблиц.

Код: plaintext

db2exfmt -d CU242176 -1 -o exfmt.txt

Покажите содержимое этого файла.

...

Рейтинг:

0 / 0

29.03.2015, 12:19

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

Опять про игнорирование индексов

#38920295

ASukhov1986

Гость

Mark Barinstein,
да все работает

Код: xml

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.

DB2 Universal Database Version 9.5, 5622-044 (c) Copyright IBM Corp. 1991, 2007
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool



******************** EXPLAIN INSTANCE ********************

DB2_VERSION: 		09.05.0
SOURCE_NAME: 		SQLC2G13
SOURCE_SCHEMA: 		NULLID  
SOURCE_VERSION: 	
EXPLAIN_TIME: 		2015-03-29-12.29.01.991002 
EXPLAIN_REQUESTER: 	ANTON   

Database Context:
----------------
	Parallelism: 		None
	CPU Speed: 		9,446885e-008
	Comm Speed: 		0
	Buffer Pool size: 	149408
	Sort Heap size: 	4246
	Database Heap size: 	1333
	Lock List size: 	29802
	Maximum Lock List: 	60
	Average Applications: 	1
	Locks Available: 	1519902

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

Profile Information:
--------------------
OPT_PROF: (Optimization Profile Name)
	METR.PROFIL1
STMTPROF: (Statement Profile Name)
	Guidelines for my simplest query

Original Statement:
------------------
SELECT * 
FROM METR.CSM_CUSTOMER 
WHERE DELETE_DATE IS NULL


Optimized Statement:
-------------------
SELECT Q1.CSM_CODE AS "CSM_CODE", Q1.CUSTOMER_ID AS "CUSTOMER_ID", 
        Q1.ENTITY_ID AS "ENTITY_ID", Q1.CREATE_DATE AS "CREATE_DATE", 
        Q1.DELETE_DATE AS "DELETE_DATE", Q1.CREATION_ID AS "CREATION_ID", 
        Q1.DELETION_ID AS "DELETION_ID", Q1.RECSTATE AS "RECSTATE", 
        Q1.CUSTOMER_TYPE_ID AS "CUSTOMER_TYPE_ID", Q1.CUSTOMER_NAME AS 
        "CUSTOMER_NAME", Q1.INN AS "INN", Q1.OKPO AS "OKPO", Q1.OKONH AS 
        "OKONH", Q1.OKATO AS "OKATO", Q1.POSTCODE AS "POSTCODE", Q1.REGION AS 
        "REGION", Q1.ADDRESS AS "ADDRESS", Q1.POSTCODE_FACT AS 
        "POSTCODE_FACT", Q1.REGION_FACT AS "REGION_FACT", Q1.ADDRESS_FACT AS 
        "ADDRESS_FACT", Q1.PHONE AS "PHONE", Q1.E_MAIL AS "E_MAIL", Q1.FAX AS 
        "FAX", Q1.DIRECTOR AS "DIRECTOR", Q1.DIRECTOR_POSITION AS 
        "DIRECTOR_POSITION", Q1.GLAVBUH AS "GLAVBUH", Q1.LOCAL_CODE AS 
        "LOCAL_CODE", Q1.BANK AS "BANK", Q1.BIK AS "BIK", Q1.BANK_ACCOUNT AS 
        "BANK_ACCOUNT", Q1.LORO_ACCOUNT AS "LORO_ACCOUNT", Q1.OFK_NAME AS 
        "OFK_NAME", Q1.OFK_INN AS "OFK_INN", Q1.OFK_KPP AS "OFK_KPP", 
        Q1.OFK_LACCOUNT AS "OFK_LACCOUNT", Q1.OFK_OKONH AS "OFK_OKONH", 
        Q1.OFK_OKPO AS "OFK_OKPO", Q1.OFK_BANK AS "OFK_BANK", Q1.OFK_BIK AS 
        "OFK_BIK", Q1.OFK_BANK_ACCOUNT AS "OFK_BANK_ACCOUNT", 
        Q1.OFK_LORO_ACCOUNT AS "OFK_LORO_ACCOUNT", Q1.OFK_REQUISITES AS 
        "OFK_REQUISITES", Q1.BANK_BALANCE AS "BANK_BALANCE", Q1.CONTACT_PHONE 
        AS "CONTACT_PHONE", Q1.CONTACT_PERSON AS "CONTACT_PERSON", Q1.KPP AS 
        "KPP", Q1.OKATO_5 AS "OKATO_5", Q1.ARCHSTATE AS "ARCHSTATE" 
FROM METR.CSM_CUSTOMER AS Q1 
WHERE Q1.DELETE_DATE IS NULL 

Access Plan:
-----------
	Total Cost: 		3566,44
	Query Degree:		1

            Rows 
           RETURN
           (   1)
            Cost 
             I/O 
             |
            19020 
           FETCH 
           (   2)
           3566,44 
           3453,57 
          /---+---\
      19020        19092 
     IXSCAN   TABLE: METR    
     (   3)    CSM_CUSTOMER
     127,098 
     66,4052 
       |
      19092 
 INDEX: METR    
 IDX_DELETE_DATE

...

Рейтинг:

0 / 0

29.03.2015, 12:32

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

Опять про игнорирование индексов

#38920310

Mark Barinstein

Гость

ASukhov1986,

Для того, чтобы db2expln сообщить о профиле, вы можете использовать т.н. setup файл expln_setup.sql с содержимым:

Код: plaintext

SET CURRENT OPTIMIZATION PROFILE = METR.PROFIL1;

Вызывать надо так:

Код: plaintext

db2expln -d CU242176 -setup expln_setup.sql -g -i -z ; -terminal -statement "SELECT * FROM METR.CSM_CUSTOMER WHERE DELETE_DATE IS NULL"

...

Рейтинг:

0 / 0

29.03.2015, 13:09

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

Опять про игнорирование индексов

#38920334

ASukhov1986

Гость

Mark Barinstein,
у меня еще возник один вопрос:
А как удалить сформированный и сохраненный БД с помощью команды

Код: sql

db2 explain all for SELECT * FROM METR.CSM_CUSTOMER WHERE DELETE_DATE IS NULL

(с учетом профиля оптимизации

Код: sql

 SET CURRENT OPTIMIZATION PROFILE = METR.PROFIL1

) план моего запроса

Код: sql

 SELECT * FROM METR.CSM_CUSTOMER WHERE DELETE_DATE IS NULL

?

Также я заметил следующую особенность:
если удалить профиль оптимизации METR.PROFIL1 из таблицы OPT_PROFILE то при получении плана запроса с помощью команды

Код: sql

db2exfmt -d CU242176 -1 -o exfmt.txt

в файле плана есть следующие строчки:

Код: xml

1.
2.
3.
4.
5.
6.

Profile Information:
--------------------
OPT_PROF: (Optimization Profile Name)
	METR.PROFIL1
STMTPROF: (Statement Profile Name)
	Guidelines for my simplest query

т.е. как я понял инфа о профиле сохраняется в какой то таблице, где храниться информация о плане выполнения запроса?

...

Рейтинг:

0 / 0

29.03.2015, 14:07

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

Опять про игнорирование индексов

#38920426

Mark Barinstein

Гость

ASukhov1986,

db2exfmt только форматирует и показывает содержимое explain таблиц. Сама она не изменяет их содержимое.
В этих таблицах могут содержатся несколько планов запросов, которые будут находится там до тех пор, пока вы их оттуда не удалите. Каждому плану запроса соответствует одна запись в EXPLAIN_INSTANCE. Остальные EXPLAIN_* таблицы связаны с ней внешними ключами с каскадным правилом удаления, поэтому, удалив запись из EXPLAIN_INSTANCE, вы удалите и все связанные записи из дочерних.
Если вы не заставляете db2 помещать планы запросов в эти таблицы другими средствами, db2exfmt с теми же параметрами будет вам всегда возвращать одно и то же.
Если вы удалили профиль METR.PROFIL1, то, чтобы изменения подействовали на оптимизатор, надо сбросить кэш профиля командой:

Код: sql

FLUSH OPTIMIZATION PROFILE CACHE METR.PROFIL1

и повторно получить план запроса (например, командой EXPLAIN). После этого вытащить план запроса (например, утилитой db2exfmt).

...

Рейтинг:

0 / 0

29.03.2015, 16:33

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

Опять про игнорирование индексов

#38920450

ASukhov1986

Гость

Mark Barinstein,
большое спасибо!

...

Рейтинг:

0 / 0

29.03.2015, 16:57

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

31 сообщений из 31, показаны все 2 страниц

все

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

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

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

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

start [/forum/topic.php?all=1&fid=43&tid=1600852]:	0ms
get settings:	9ms
get forum list:	12ms
check forum access:	4ms
check topic access:	4ms
track hit:	62ms
get topic data:	9ms
get forum data:	2ms
get page messages:	64ms
get tp. blocked users:	1ms
others:	284ms

total:	451ms