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

Откуда: Екатеринбург
Сообщений: 45
Версия Oracle:
Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production

НА предприятии существуют две БД тестовая и рабочая, в рабочей БД перестал работать составной индекс на таблицу. Скрипт таблицы:
CREATE TABLE <Схема1>.<Таблица1>
( ID             NUMBER(12)                     NOT NULL,
  DATE_BEGIN     DATE,
  DATE_END       DATE,
  FLAGCLOSE      NUMBER(1)                      DEFAULT 0,
  FLAGOCVOLUM    NUMBER(1)                      DEFAULT 0,
  FLAGOCAMORT    NUMBER(1)                      DEFAULT 0,
  FLAGOCACCOUNT  NUMBER(1)                      DEFAULT 0,
  DEFFLAG        NUMBER(1)                      DEFAULT 0,
  BLC_ID         NUMBER(12),
  IS_SO_BLOCK    NUMBER(1),
  CHECK (FlagClose IN (0, 1)),CHECK (FlagOCVolum IN (0, 1)),
  CHECK (FlagOCAmort IN (0, 1)),CHECK (FlagOCAccount IN (0, 1)),
  CHECK (FlagClose IN (0,1)),CHECK (FlagOCVolum IN (0,1)),
  CHECK (FlagOCAmort IN (0,1)),CHECK (FlagOCAccount IN(0, 1)),
  CHECK (FlagClose IN (0,1)),CHECK (FlagOCVolum IN (0,1)),
  CHECK (FlagOCAmort IN (0,1)),CHECK (FlagOCAccount IN (0, 1)),
  CHECK (FlagClose IN (0,1)),CHECK (FlagOCVolum IN (0,1)),
  CHECK (FlagOCAmort IN (0,1)),CHECK (FlagOCAccount IN (0, 1)),
  CHECK (FlagClose IN (0,1)),CHECK (FlagOCVolum IN (0,1)),
  CHECK (FlagOCAmort IN (0,1)),CHECK (FlagOCAccount IN (0, 1)),
  CHECK (FlagClose IN (0,1)),CHECK (FlagOCVolum IN (0,1)),
  CHECK (FlagOCAmort IN (0,1)),CHECK (FlagOCAccount IN (0, 1)),
  CHECK (FlagClose IN (0,1)),CHECK (FlagOCVolum IN (0,1)),
  CHECK (FlagOCAmort IN (0,1)),CHECK (FlagOCAccount IN (0, 1)),
  CHECK (FlagClose IN (0,1)),CHECK (FlagOCVolum IN (0,1)),
  CHECK (FlagOCAmort IN (0,1)),CHECK (FlagOCAccount IN (0, 1)),
  CHECK (FlagClose IN (0,1)),CHECK (FlagOCVolum IN (0,1)),
  CHECK (FlagOCAmort IN (0,1)),CHECK (FlagOCAccount IN(0, 1)),
  CHECK (FlagClose IN (0,1)),CHECK (FlagOCVolum IN (0,1)),
  CHECK (FlagOCAmort IN (0,1)),CHECK (FlagOCAccount IN (0, 1)),
  CHECK (FlagClose IN (0,1)),CHECK (FlagOCVolum IN (0,1)),
  CHECK (FlagOCAmort IN (0,1)),CHECK (FlagOCAccount IN (0, 1)),
  PRIMARY KEY (ID))
NOLOGGING NOCACHE NOPARALLEL;

Скрипт индекса:
CREATE UNIQUE INDEX <Схема1>.<Индекс1> ON <Схема1>.<Таблица1>
(DATE_BEGIN, DATE_END, BLC_ID)
LOGGING
TABLESPACE INDX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          32K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;

Листинг запроса при выполнении которого в рабочей БД индекс не используется:
 SELECT id
  FROM <Схема1>.<Таблица1>
 WHERE (date_begin) <= TRUNC (SYSDATE) AND (date_end) >= TRUNC (SYSDATE)
       AND blc_id = <ИД запсии, число, константа>

План выполнения запроса в рабочей БД:
Operation	 OBJECT Name	                                ROWS    Bytes	Cost	

SELECT STATEMENT Optimizer MODE=CHOOSE		7  	 	2  	 	      	             	 
  TABLE ACCESS FULL	<Схема1>.<Таблица1>	7  	140  	2

В тестовой БД предприятия у этого же запроса другой план выполнения:
Operation	                              OBJECT Name                   ROWS Bytes Cost
SELECT STATEMENT Optimizer MODE=CHOOSE		          6  	       2 
 TABLE ACCESS BY INDEX ROWID	<Схема1>.<Таблица1>6  	156  2
  INDEX RANGE SCAN	<Схема1>.<Индекс1>                          6  	 	 1   


В случае выполнения запроса в рабочей БД с использованием hint'а план выполнения меняется на такой же как в тестовой, но хинт добавить в запрос нет возможности т.к. запрос выполняется из интерфейса закрытого приложения. Попытки проанализировать индекс не помогли. <Таблица1> в рабочей базе отличается от <таблица1> в тестовой базе только 2мя строками которые были добавлены после полного копирования данных из одной БД в другую.
Количество строк в <Таблица1> в рабочей БД 163, в тестовой 161. Пример данных из <таблица1> :
ID=2175 DATE_BEGIN=01.07.2005 DATE_END=31.07.2005 FLAGCLOSE=0 FLAGOCVOLUM=0 FLAGOCAMORT=0 FLAGOCACCOUNT=0 DEFFLAG=0 BLC_ID=<Фиксированный Ид, Число, константа> IS_SO_BLOCK=0
Поле BLC_ID в каждой строке таблицы принимает одно из двух значений, всегда заполнено.
Подскажите пожалуйста, как можно исправить план выполнения запроса в рабочей БД ?
29 июн 10, 10:04    [9015479]     Ответить | Цитировать Сообщить модератору
 Re: Перестал применяться составной индекс  [new]
JaRo
Member

Откуда:
Сообщений: 1659
А зачем Вам на таких крошечных объемах индекс?
29 июн 10, 10:07    [9015500]     Ответить | Цитировать Сообщить модератору
 Re: Перестал применяться составной индекс  [new]
env
Member

Откуда: Россия, Москва
Сообщений: 6729
которые были добавлены после полного копирования данных из одной БД в другую

0. Статистику после этого собрали?
1. На таких объёмах индекс не нужен
29 июн 10, 10:09    [9015523]     Ответить | Цитировать Сообщить модератору
 Re: Перестал применяться составной индекс  [new]
R_Only
Member

Откуда: Екатеринбург
Сообщений: 45
0. Статистика собрана.
1. Запрос является одним из множества запросов выполняемых процедурой, до того как индекс перестал использоваться, процедура выполнялась около получаса (нормально время работы т.к. большой объем данных для обработки), после того как индекс перестал работать, время возросло в несколько раз. То, что проблема стала заключаться в смене плана выполнения именно для данного запроса было выявлено при помощи мониторинга сессии. Данный запрос стал обрабатываться значительно дольше.
29 июн 10, 10:20    [9015628]     Ответить | Цитировать Сообщить модератору
 Re: Перестал применяться составной индекс  [new]
JaRo
Member

Откуда:
Сообщений: 1659
R_Only
Данный запрос стал обрабатываться значительно дольше.
Интересно - "долго" - это как? На самом деле как раз если бы Оракл использовал индекс, это бы означало, что он "ошибается"
29 июн 10, 10:23    [9015664]     Ответить | Цитировать Сообщить модератору
 Re: Перестал применяться составной индекс  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
Для этого запроса принципиален порядок полей в индексе. BLC_ID должен быть первым.
29 июн 10, 10:29    [9015723]     Ответить | Цитировать Сообщить модератору
 Re: Перестал применяться составной индекс  [new]
R_Only
Member

Откуда: Екатеринбург
Сообщений: 45
Поясню, что я имею в виду под словом «Долго». После того как от пользователя поступила жалоба на то, что операция стало выполняться за большее количество времени. Я в при помощи Session Browser программы TOAD нахожу в списке сессий, сессию именно этого пользователя и просматриваю выполняемый в данный момент запрос. В течении нескольких десятков секунд обновляю поле для sql запроса выполняемого из интерфейса пользователя и замечаю, что дольше всего из всех замеченных там запросов находится именно тот о котором я написал в первом сообщении ветки.
29 июн 10, 10:30    [9015731]     Ответить | Цитировать Сообщить модератору
 Re: Перестал применяться составной индекс  [new]
R_Only
Member

Откуда: Екатеринбург
Сообщений: 45
wurdu,
В тестовой БД индекс и таблица не чем не отличаются от рабочей, порядок полей тот же, но explain plan другой. Поэтому, как мне кажется ваша версия не решит моей проблемы. Поясните пожалуйста почему BLC_ID должно быть первым ?
29 июн 10, 10:34    [9015760]     Ответить | Цитировать Сообщить модератору
 Re: Перестал применяться составной индекс  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
R_Only
wurdu,
В тестовой БД индекс и таблица не чем не отличаются от рабочей, порядок полей тот же, но explain plan другой. Поэтому, как мне кажется ваша версия не решит моей проблемы. Поясните пожалуйста почему BLC_ID должно быть первым ?
Моя "версия" позволит сделать индекс более селективным для оптимизатора и скорее всего улучшить производительность на обеих базах. Почему первым? Сделай тест кейс и померь логические чтения.
R_Only
Поясню, что я имею в виду под словом «Долго». После того как от пользователя поступила жалоба на то, что операция стало выполняться за большее количество времени. Я в при помощи Session Browser программы TOAD нахожу в списке сессий, сессию именно этого пользователя и просматриваю выполняемый в данный момент запрос. В течении нескольких десятков секунд обновляю поле для sql запроса выполняемого из интерфейса пользователя и замечаю, что дольше всего из всех замеченных там запросов находится именно тот о котором я написал в первом сообщении ветки.
Так производительность не измеряют. Для этих целей служит Trace 10046.
29 июн 10, 10:39    [9015801]     Ответить | Цитировать Сообщить модератору
 Re: Перестал применяться составной индекс  [new]
R_Only
Member

Откуда: Екатеринбург
Сообщений: 45
wurdu,
По какой именно причине ваша версия порядка полей в индексе будет более селективной ? Поле BLC_ID во всей таблице как я и написал принимает 2 значения, 50% строк в таблице с первым значением, 50% со вторым. Вы написали, что я не правильно измеряю производительность, значит вы считаете что проблема не в том запросе который я указал ?
29 июн 10, 10:43    [9015842]     Ответить | Цитировать Сообщить модератору
 Re: Перестал применяться составной индекс  [new]
Vladimir_
Member

Откуда: Санкт-Петербург
Сообщений: 771
R_Only
wurdu,
Поле BLC_ID во всей таблице как я и написал принимает 2 значения, 50% строк в таблице с первым значением, 50% со вторым.
При таком раскладе забудьте про индекс. Зачем он Вам. Пара блоков всегда будет в кэше. А индекс даст пару лишних логических чтений. Чтобы найти реальную проблему используя тотже тоад, включите трассировку на сессию и анализируйте трейс опять таки, тоад предоставляет такую возможность.
29 июн 10, 10:49    [9015904]     Ответить | Цитировать Сообщить модератору
 Re: Перестал применяться составной индекс  [new]
JaRo
Member

Откуда:
Сообщений: 1659
R_Only
Вы написали, что я не правильно измеряю производительность, значит вы считаете что проблема не в том запросе который я указал ?
Надо очень постараться, чтобы FTS 163 записей - это было долго, хотя, конечно, нет ничего не невозможного.
Что касается вашего метода замера производительности - первый же пример (которы приходит в голову):
for i in 1..1000 loop <ваш запрос> end loop;
Будет ли постоянное наблюдение этого запроса говорить именно о неоптимальном плане запроса?
29 июн 10, 10:51    [9015915]     Ответить | Цитировать Сообщить модератору
 Re: Перестал применяться составной индекс  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
R_Only
wurdu,
По какой именно причине ваша версия порядка полей в индексе будет более селективной ? Поле BLC_ID во всей таблице как я и написал принимает 2 значения, 50% строк в таблице с первым значением, 50% со вторым. Вы написали, что я не правильно измеряю производительность, значит вы считаете что проблема не в том запросе который я указал ?
Ok, в данном случае и на данных объемах индекс скорее всего будет из одного блока и разницы не будет. Производительность мерять надо с помощью trace 10046. В этом случае будет видно на какую операцию сколько времени уходит. И совсем не факт что виноват этот запрос.
29 июн 10, 10:57    [9015964]     Ответить | Цитировать Сообщить модератору
 Re: Перестал применяться составной индекс  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
R_Only
Надо очень постараться, чтобы FTS 163 записей - это было долго, хотя, конечно, нет ничего не невозможного.
Угу, мож у него HWM задран.
29 июн 10, 10:58    [9015976]     Ответить | Цитировать Сообщить модератору
 Re: Перестал применяться составной индекс  [new]
Vladimir_
Member

Откуда: Санкт-Петербург
Сообщений: 771
wurdu
Угу, мож у него HWM задран.
вот тут я полностью согласен. и скорее всего так оно и есть. возможно analyze спасёт отца русской демократии.
29 июн 10, 11:30    [9016345]     Ответить | Цитировать Сообщить модератору
 Re: Перестал применяться составной индекс  [new]
R_Only
Member

Откуда: Екатеринбург
Сообщений: 45
Vladimir_,
Вы думаете что если проблема в том запросе который я указал в первом сообщении, то она пропадет после analyze <Таблица1>, я правильно понимаю ? Или вы предлагаете проанализировать индексы таблицы ? В первом сообщении в топике я написал, что индекс был проанализирован, но это не повлияло на план выполнения запроса. Возможно я вас не правильно понял, что нужно проанализировать ?
29 июн 10, 13:45    [9017868]     Ответить | Цитировать Сообщить модератору
 Re: Перестал применяться составной индекс  [new]
makitka
Member

Откуда: FROM dual
Сообщений: 886
а зачем столько одинаковых чеков??
29 июн 10, 14:18    [9018316]     Ответить | Цитировать Сообщить модератору
 Re: Перестал применяться составной индекс  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18367
R_Only
Vladimir_,
Вы думаете что если проблема в том запросе который я указал в первом сообщении, то она пропадет после analyze <Таблица1>, я правильно понимаю ?

alter table move
29 июн 10, 14:32    [9018454]     Ответить | Цитировать Сообщить модератору
 Re: Перестал применяться составной индекс  [new]
R_Only
Member

Откуда: Екатеринбург
Сообщений: 45
andrey_anonymous,
Спасибо вам большое, после применения вашего варианта индексы снова начали применяться. Видимо ваш вариант решения мой проблемы основан на предположении что таблица была значительно дефрагментирована по блокам ? Если вам не трудно поясните, как вы это определили ?
29 июн 10, 16:34    [9018839]     Ответить | Цитировать Сообщить модератору
 Re: Перестал применяться составной индекс  [new]
R_Only
Member

Откуда: Екатеринбург
Сообщений: 45
makitka,
На счет чеков точно сказать не могу т.к. я не проектировал БД.
29 июн 10, 16:39    [9018892]     Ответить | Цитировать Сообщить модератору
 Re: Перестал применяться составной индекс  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18367
R_Only
Видимо ваш вариант решения мой проблемы основан на предположении что таблица была значительно дефрагментирована по блокам ? Если вам не трудно поясните, как вы это определили ?

Я убивал двух зайцев:
- если проблема в HWM - вернуть HWM на родину и ускорить FTS
- если проблема в bind variables peeking - перепарсить курсоры

Судя по эффекту, стрельнул второй "заяц"
29 июн 10, 17:46    [9019676]     Ответить | Цитировать Сообщить модератору
 Re: Перестал применяться составной индекс  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18367
andrey_anonymous
Судя по эффекту, стрельнул второй "заяц"

...если бы знал точно - посоветовал бы
comment on table <Таблица1> is 'fast=true'
:)
29 июн 10, 17:49    [9019700]     Ответить | Цитировать Сообщить модератору
 Re: Перестал применяться составной индекс  [new]
R_Only
Member

Откуда: Екатеринбург
Сообщений: 45
andrey_anonymous,
Спасибо еще раз, за подробное объяснение.
30 июн 10, 07:25    [9021656]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить