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

Откуда:
Сообщений: 124
Не могу заставить CBO использовать индекс!
Есть маленькая табличка, 1000 строк
По ней построен уникальный индекс
CREATE UNIQUE INDEX FEE_ID_AMT_DATE_METHOD_PK ON FEE_TAB
(FEE_ID, METHOD, START_DATE, END_DATE, START_AMT)

вот этот Запрос:
SELECT ft.cur_code, ft.perc_value, ft.start_amt, ft.end_amt, ft.method,
ft.fixed_value, ft.inst_id
FROM vista.fee_tab ft
WHERE (ft.fee_id = :b3)
AND ((ft.start_amt IS NOT NULL) AND (ft.end_amt IS NOT NULL))
AND (:b2 BETWEEN start_date AND end_date)
AND (method <> :b1)
ORDER BY ft.start_amt, ft.end_amt
никак не хочет использовать индекс.
Если индекс указывать явно(в хинтах), то все нормально. Если добавить /*+RULE */ - тоже использует индекс.

Собираю статистику так :
begin
dbms_stats.gather_table_stats(ownname => 'VISTA',tabname => 'FEE_TAB',degree => 4,cascade => true,method_opt => 'FOR ALL INDEXED COLUMNS');
end;


Возможности править код (добавлять хинты и тд. ) НЕТ.
Как можно его заставить использовать индекс , кроме случая удаления статистики по таблице?
И почему в плане при FULL SCAN CArdinality=1?

Статью https://www.sql.ru/faq/faq_topic.aspx?fid=344 перечитал несколько раз.
14 мар 07, 14:24    [3897557]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с CBO  [new]
pailex
Member

Откуда:
Сообщений: 124
Даже если убрать бинды, индекс не используется
14 мар 07, 14:24    [3897563]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с CBO  [new]
tru55
Member

Откуда: СПб
Сообщений: 19790
маленький вопрос.
А method_opt используется сознательно или просто откуда-то скопировал?
14 мар 07, 14:26    [3897578]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с CBO  [new]
pailex
Member

Откуда:
Сообщений: 124
Сознательно. На сколько я понимаю, данный метод не испортит ситуации, даже если данные равномерно распределены.
14 мар 07, 14:28    [3897596]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с CBO  [new]
evostr
Member

Откуда: Екатеринбург
Сообщений: 1278
pailex
Не могу заставить CBO использовать индекс!
Есть маленькая табличка, 1000 строк

Почему вы решили, что использовать индекс эффективнее?
Сколько логических чтений в каждом случае?
14 мар 07, 14:28    [3897599]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с CBO  [new]
pailex
Member

Откуда:
Сообщений: 124
6 против 3. те в два раза меньше.
Этот запрос выполняется в пакете больше 100 000


evostr
pailex
Не могу заставить CBO использовать индекс!
Есть маленькая табличка, 1000 строк

Почему вы решили, что использовать индекс эффективнее?
Сколько логических чтений в каждом случае?
14 мар 07, 14:33    [3897638]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с CBO  [new]
pailex
Member

Откуда:
Сообщений: 124
раз
14 мар 07, 14:33    [3897641]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с CBO  [new]
Splain
Member

Откуда: Череповец
Сообщений: 924
У нас для 8.1.7 такой же план.

Зато в тестовой 9.2.0.8 уже

 
-------------------------------------------------------------------------------------------
| Id  | Operation                    |  Name                      | Rows  | Bytes | Cost  |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                            |     1 |    43 |     6 |
|   1 |  SORT ORDER BY               |                            |     1 |    43 |     6 |
|   2 |   TABLE ACCESS BY INDEX ROWID| FEE_TAB                    |     1 |    43 |     2 |
|   3 |    INDEX RANGE SCAN          | FEE_ID_AMT_DATE_METHOD_PK  |     1 |       |     1 |
-------------------------------------------------------------------------------------------

А на 8-ке долго не разбирался, но First_ROWS ситуацию исправил при analyze table compute statistics for all indexed column size 100.
14 мар 07, 14:39    [3897675]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с CBO  [new]
evostr
Member

Откуда: Екатеринбург
Сообщений: 1278
pailex
6 против 3. те в два раза меньше.
Этот запрос выполняется в пакете больше 100 000

Версия оракла?
Системная статистика собрана?
Параметры optimizer_index_cost_adj, optimizer_index_caching чему равны?
14 мар 07, 14:39    [3897680]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с CBO  [new]
pailex
Member

Откуда:
Сообщений: 124
Системной статистики нет.
optimizer_index_cost_adj стандарт = 100
optimizer_index_caching тоже дефолт = 0
Системную статистику побаиваюсь собирать, как бы это не отразилось негативно на другие запросы.

Версия Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production

evostr
pailex
6 против 3. те в два раза меньше.
Этот запрос выполняется в пакете больше 100 000

Версия оракла?
Системная статистика собрана?
Параметры optimizer_index_cost_adj, optimizer_index_caching чему равны?
14 мар 07, 14:45    [3897716]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с CBO  [new]
pailex
Member

Откуда:
Сообщений: 124
у меня FIRST_ROWS тоже заставляет индекс работать

Splain
У нас для 8.1.7 такой же план.

Зато в тестовой 9.2.0.8 уже

 
-------------------------------------------------------------------------------------------
| Id  | Operation                    |  Name                      | Rows  | Bytes | Cost  |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                            |     1 |    43 |     6 |
|   1 |  SORT ORDER BY               |                            |     1 |    43 |     6 |
|   2 |   TABLE ACCESS BY INDEX ROWID| FEE_TAB                    |     1 |    43 |     2 |
|   3 |    INDEX RANGE SCAN          | FEE_ID_AMT_DATE_METHOD_PK  |     1 |       |     1 |
-------------------------------------------------------------------------------------------

А на 8-ке долго не разбирался, но First_ROWS ситуацию исправил при analyze table compute statistics for all indexed column size 100.
14 мар 07, 14:48    [3897736]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с CBO  [new]
evostr
Member

Откуда: Екатеринбург
Сообщений: 1278
pailex
Системной статистики нет.
optimizer_index_cost_adj стандарт = 100
optimizer_index_caching тоже дефолт = 0

Возможно, оптимизатор думает, что ему дешевле прочитать за один раз все таблицу, чем 3 одноблочных чтения делать для индекса и таблицы.
Поиграйтесь с помощью alter session с этими параметрами - посмотрите, что будет...
14 мар 07, 14:49    [3897746]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с CBO  [new]
NightGhost
Member

Откуда:
Сообщений: 629
Добрый день
не особо разбираюсь
может быть 6 логических чтений означает что таблица умещается в 6 блоков => читается с диска
за 1 раз.
использование индекса и таблица дейтвительно дает 3 логических чтения
(Хидер индекса + лист индекса) + блок таблицы => 2 чтения с диска

1 чтение < 2 чтения

вроде оптимизатор предполагает что изначально блоки в буфере не находятся
14 мар 07, 14:55    [3897789]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с CBO  [new]
pailex
Member

Откуда:
Сообщений: 124
Играл, изменений не было.

evostr
pailex
Системной статистики нет.
optimizer_index_cost_adj стандарт = 100
optimizer_index_caching тоже дефолт = 0

Возможно, оптимизатор думает, что ему дешевле прочитать за один раз все таблицу, чем 3 одноблочных чтения делать для индекса и таблицы.
Поиграйтесь с помощью alter session с этими параметрами - посмотрите, что будет...
14 мар 07, 15:03    [3897852]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с CBO  [new]
ORA-01403
Member

Откуда: Москва
Сообщений: 60
pailex
Играл, изменений не было.


Можно попробовать создать Stored Outline
14 мар 07, 15:05    [3897865]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с CBO  [new]
evostr
Member

Откуда: Екатеринбург
Сообщений: 1278
pailex
Играл, изменений не было.

Да? странно...

Тогда, поставьте
optimizer_index_cost_adj = 25
optimizer_index_caching = 90
и трассировку 10053 включите.

Результат трассировки сюда запостите
14 мар 07, 15:07    [3897881]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с CBO  [new]
Splain
Member

Откуда: Череповец
Сообщений: 924
NightGhost
Добрый день
не особо разбираюсь
может быть 6 логических чтений означает что таблица умещается в 6 блоков => читается с диска
за 1 раз.
использование индекса и таблица дейтвительно дает 3 логических чтения
(Хидер индекса + лист индекса) + блок таблицы => 2 чтения с диска

1 чтение < 2 чтения

вроде оптимизатор предполагает что изначально блоки в буфере не находятся


Нет. Это особенности вычисления стоимости order by
14 мар 07, 15:14    [3897941]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с CBO  [new]
pailex
Member

Откуда:
Сообщений: 124
*** 2007-03-14 15:13:35.079
*** SESSION ID:(19.27451) 2007-03-14 15:13:35.056
QUERY
begin :id := sys.dbms_transaction.local_transaction_id; end;
QUERY
begin
  sys.dbms_output.get_line(line => :line, status => :status);
end;
QUERY
begin :id := sys.dbms_transaction.local_transaction_id; end;
QUERY
select 'x' from dual
QUERY
begin
  if :enable = 0 then
    sys.dbms_output.disable;
  else
    sys.dbms_output.enable(:size);
  end if;
end;
QUERY
begin :id := sys.dbms_transaction.local_transaction_id; end;
QUERY
SELECT     ft.cur_code, ft.perc_value, ft.start_amt, ft.end_amt, ft.method,
         ft.fixed_value, ft.inst_id
    FROM vista.fee_tab ft
   WHERE ft.fee_id = 55346
***************************************
SINGLE TABLE ACCESS PATH
  TABLE: FEE_TAB     ORIG CDN: 1052  ROUNDED CDN: 1052  CMPTD CDN: 1052
  Access path: tsc  Resc:  2  Resp:  2
  BEST_CST: 3.00  PATH: 2  Degree:  1
***************************************
GENERAL PLANS
***********************
Join order[1]:  FEE_TAB[FEE_TAB]#0
Best so far: TABLE#: 0  CST:          3  CDN:       1052  BYTES:      81004
Final - All Rows Plan:
  JOIN ORDER: 1
  CST: 3  CDN: 1052  RSC: 2  RSP: 2  BYTES: 81004
  IO-RSC: 2  IO-RSP: 2  CPU-RSC: 518357  CPU-RSP: 518357
QUERY
SELECT * FROM vista.fee_tab
QUERY
begin :id := sys.dbms_transaction.local_transaction_id; end;
QUERY
begin
  sys.dbms_output.get_line(line => :line, status => :status);
end; 
14 мар 07, 15:16    [3897962]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с CBO  [new]
Splain
Member

Откуда: Череповец
Сообщений: 924
evostr

Тогда, поставьте
optimizer_index_cost_adj = 25
optimizer_index_caching = 90
и трассировку 10053 включите.

Результат трассировки сюда запостите


Не поможет мне кажется. Здесь просто стоимость доступа FULL равна стоимости INDEX RANGE SCAN + ACCESS BY INDEX ROWID
14 мар 07, 15:19    [3897978]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с CBO  [new]
Splain
Member

Откуда: Череповец
Сообщений: 924
pailex
Не могу заставить CBO использовать индекс!


Кстати, Вы уверены, что это ускорит Ваш запрос?

Потому как в пакете у Вас будет на каждый запрос soft parsing + switch context. Надо проверить - будет ли в этом случае разница - 6 или 3 логических чтения.
14 мар 07, 15:37    [3898118]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с CBO  [new]
pailex
Member

Откуда:
Сообщений: 124
А можно поподробнее?
какая разница в пакете он выполняется либо вручную?

Splain
pailex
Не могу заставить CBO использовать индекс!


Кстати, Вы уверены, что это ускорит Ваш запрос?

Потому как в пакете у Вас будет на каждый запрос soft parsing + switch context. Надо проверить - будет ли в этом случае разница - 6 или 3 логических чтения.
14 мар 07, 15:45    [3898175]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с CBO  [new]
Splain
Member

Откуда: Череповец
Сообщений: 924
В трассировке soft парсинги могут выглядеть примерно так:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse    30834     10.22     243.98          0          0          0           0
Execute  63105      1.18       1.06          0          0          0           0
Fetch    63105      1.08       0.64          1     158505          0       63105
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   157044     12.48     245.68          1     158505          0       63105

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 39     (recursive depth: 3)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE 
      1   INDEX UNIQUE SCAN (object id 3898)

Я воспроизвел ситуацию так:

CREATE OR REPLACE procedure test_prc1(par1 number) is
 type TCur_Ref IS REF CURSOR;
 val2 number;
 cur_ref TCur_Ref;
begin
 open cur_ref for
  select max(id) from test;
 fetch cur_ref into val2;
 close cur_ref;
end test_prc1;

Сравни с

create or replace procedure test_prc2(par1 number) is
 val2 number;
 cursor cur_ref_1 is 
 select max(id) from test;
begin
 open cur_ref_1;
 fetch cur_ref_1 into val2;
 close cur_ref_1;
end test_prc2;

Я сравнивал конструкциями типа

declare 
  i integer;
begin
  -- Test statements here
for i in 1..5000
 loop
  test_prc1(i);                    
 end loop;
end;

плюс профилирование.

Вот в этом простом случае выигрышь был процентов 40. В реальной системе все будет выглядеть иначе, но на такие тесты у меня терпения не хватило :)

Это я к тому, что выход в твоей ситуации конечно есть. И даже без Stored outlines можно решить проблему, например, пересоздав таблицу с pctfree 99. Но меня берут сомнения, поможет ли это.
14 мар 07, 16:16    [3898366]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с CBO  [new]
Splain
Member

Откуда: Череповец
Сообщений: 924
Splain
open cur_ref for


Такую конструкцию я взял не случайно, поскольку видел одним глазом текст этих пакетов ...
14 мар 07, 16:18    [3898375]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с CBO  [new]
Журавлев Денис
Member

Откуда: St.John,NB,CA
Сообщений: 5532
у бпц куча запросов захинтованы rule, захинтуйте сами или их попросите, только пользы от этого будет мало.
14 мар 07, 17:19    [3898700]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с CBO  [new]
sql-plus
Guest
Чему равны
select count(*) from vista.fee_tab;

и

select count(*)
from
(
SELECT ft.cur_code, ft.perc_value, ft.start_amt, ft.end_amt, ft.method,
ft.fixed_value, ft.inst_id
FROM vista.fee_tab ft
WHERE (ft.fee_id = :b3)
AND ((ft.start_amt IS NOT NULL) AND (ft.end_amt IS NOT NULL))
AND (:b2 BETWEEN start_date AND end_date)
AND (method <> :b1)
ORDER BY ft.start_amt, ft.end_amt
)
;

Для типичных значений :b3 и :b1.
14 мар 07, 17:24    [3898731]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Oracle Ответить