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

Откуда: Град Петра на реке Неве
Сообщений: 459
Коллеги, приветствую!
Подскажите чего-нибудь please!
Клиенты стонут!

Проблема:

Запрос

select clv.clnt_clnt_id clnt_clnt_id, client_account, client_name, client_status, client_type,
func_bal_for_clients(clv.clnt_clnt_id) real_balance, func_bal_v_for_clients(clv.clnt_clnt_id) virtual_balance,
NVL((select b.in_balance_$ from clnt_in_balances b where clv.clnt_clnt_id=b.clnt_clnt_id),0) in_balance_$,
ASSC_NAME, assc_contract, clv.def group_def, clv.clnt_clnt_id_1 group_main
from client_list_view clv
where upper(client_name) like upper(:vclient_name) order by to_number(client_account)


Поск клиента по имени. Вроде нормально работал.
И вдруг стал безумно тормозить

Rows Row Source Operation
------- ---------------------------------------------------
11 SORT ORDER BY
389 NESTED LOOPS OUTER
389 NESTED LOOPS
389 NESTED LOOPS OUTER
389 NESTED LOOPS
389 NESTED LOOPS OUTER
389 TABLE ACCESS BY GLOBAL INDEX ROWID CLIENT_HISTORIES PARTITION: ROW LOCATION ROW LOCATION
8067158 INDEX RANGE SCAN CLNH_CTYP_I (object id 16536980)
0 TABLE ACCESS BY INDEX ROWID GROUP_HISTORIES
0 INDEX RANGE SCAN GRHS_CLNT_FK_I (object id 698393)
389 INDEX UNIQUE SCAN CLIS_PK (object id 697185)
0 INDEX UNIQUE SCAN GRST_PK (object id 697438)
389 INDEX UNIQUE SCAN CTYP_PK (object id 697189)
2 TABLE ACCESS BY INDEX ROWID ASSOCIATIONS
2 INDEX UNIQUE SCAN ASSC_PK (object id 698095)


По данному плану видно, что совершенно не используется специально для этого некогда созданный функциональный индекс CLNH_UNAM_I = CLIENT_HISTORIES (UPPER(NAME))

В свою очередь, client_list_view - это view на основе нескольких связанных между собой таблиц,
одна из которых огромная CLIENT_HISTORIES почти 30 млн. записей, остальные 5 таблиц ничтожно маленькие от 0 до 2 тыс. записей.

Гигантская таблица партиционирована, а этот индекс CLNH_UNAM_I нет:


INDEX_NAME INDEX_TYPE UNIQUENESS COMPRESSION INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE PCT_THRESHOLD FREELISTS FREELIST_GROUPS PCT_FREE LOGGING BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY PARTITIONED
CLNH_UNAME_I FUNCTION-BASED NORMAL NONUNIQUE DISABLED 12 255 8388608 8388608 1 2147483645 0 16 1 5 YES 3 159650 403899 1 NO


Вопрос: то, что этот индекс совсем не используется при выполнении запроса может как-то быть связано с тем, что индекс не партиционирован.

Тут же: Коллеги, у меня совсем нет опыта в написании OUTLINE, нужно что-то срочно написать подсабите please.

P.S.
Клиент говорит, что статистику по таблице собрал:

begin
DBMS_STATS.gather_table_stats(ownname => ...
tabname => 'CLIENT_HISTORIES',
estimate_percent => 40,
degree => 16,
method_opt => 'FOR ALL INDEXED COLUMNS SIZE 225',
cascade => true);
end;
16 авг 06, 12:19    [3009691]     Ответить | Цитировать Сообщить модератору
 Re: В запросе не используется я индекс!  [new]
contr
Member

Откуда:
Сообщений: 1909
Ниасилил, многа нефарматированых букф.
Но если читать по диагонали:

OCA-Ламер
По данному плану видно, что совершенно не используется специально для этого некогда созданный функциональный индекс CLNH_UNAM_I = CLIENT_HISTORIES (UPPER(NAME))


Применение такого индекса может зависеть от NLS-окружения сессии.

Если не угадал то загляните в FAQ, там была статейка "Почему ошибается CBO" с рядом интересных версий :)
16 авг 06, 12:24    [3009747]     Ответить | Цитировать Сообщить модератору
 Re: В запросе не используется я индекс!  [new]
OCA-Ламер
Member

Откуда: Град Петра на реке Неве
Сообщений: 459
contr


Применение такого индекса может зависеть от NLS-окружения сессии.




Contr, подкинь еще хоть какую-то зацепку.

Я нашёл эту статью. "Когда ошибается CBO?" http://www.oradba.com.ru/tuning/optimizer/articles/a1_cbomistake.shtml

Примерно про это же пишет и клиент. Из Web всё плохо, а если из sqlplus, то всё хорошо, индекс использует (клиент подставляет в запрос литерал вместо bind).
Я в статье ничего не нашёл про NLS-окружения сессии.... :(
16 авг 06, 12:56    [3010005]     Ответить | Цитировать Сообщить модератору
 Re: В запросе не используется я индекс!  [new]
contr
Member

Откуда:
Сообщений: 1909
OCA-Ламер
подкинь еще хоть какую-то зацепку.
Из Web всё плохо, а если из sqlplus, то всё хорошо, индекс использует (клиент подставляет в запрос литерал вместо bind).

1) Как на счет выполнения с биндом под sqlplus - все опять плохо или где?
2) "Web" - это подключение через пул коннектов? Не так давно пробегала схожая тема - под dedicated (sqlplus) все хорошо, под MTS (приложение) все плохо. Дело было в различном окружении dedicated и MTS.
3) По поводу влияния NLS на выбор индексов - загляните в globalization support guide, вроде там рассматривалось. Попробуйте alter session set nls_sort=binary и alter session set nls_sort=<нац. сортировка>, сравните результаты.
16 авг 06, 13:02    [3010066]     Ответить | Цитировать Сообщить модератору
 Re: В запросе не используется я индекс!  [new]
Apex
Member

Откуда: Made in USSR
Сообщений: 3909
OCA-Ламер
contr


Применение такого индекса может зависеть от NLS-окружения сессии.




Contr, подкинь еще хоть какую-то зацепку.

Я нашёл эту статью. "Когда ошибается CBO?" http://www.oradba.com.ru/tuning/optimizer/articles/a1_cbomistake.shtml

Примерно про это же пишет и клиент. Из Web всё плохо, а если из sqlplus, то всё хорошо, индекс использует (клиент подставляет в запрос литерал вместо bind).
Я в статье ничего не нашёл про NLS-окружения сессии.... :(

Коллега, если заменить like на =, то по крайней мере у меня на тестовой базе план меняется.
16 авг 06, 13:05    [3010089]     Ответить | Цитировать Сообщить модератору
 Re: В запросе не используется я индекс!  [new]
Apex
Member

Откуда: Made in USSR
Сообщений: 3909
На счет outline напиши (Alexander dot Egorov dog billing dot ru) или позвони - расскажу.
-------------------------------------------------------
Автор благодарит алфавит за любезно предоставленные ему буквы.
16 авг 06, 13:11    [3010134]     Ответить | Цитировать Сообщить модератору
 Re: В запросе не используется я индекс!  [new]
contr
Member

Откуда:
Сообщений: 1909
Apex
На счет outline напиши (Alexander dot Egorov dog billing dot ru) или позвони - расскажу.

Александр, Вы уж давайте прямо здесь - а то ведь автор даже версии СУБД не указал, а техника работы с outlines 8i разительно отличается от таковой в 9i и тем более в 10g :)
16 авг 06, 13:15    [3010164]     Ответить | Цитировать Сообщить модератору
 Re: В запросе не используется я индекс!  [new]
OCA-Ламер
Member

Откуда: Град Петра на реке Неве
Сообщений: 459
contr

1) Как на счет выполнения с биндом под sqlplus - все опять плохо или где?


Попробовал на собственной БД, запрос с bind из sqlplus, всё плохо, индекс не подхватывается.
16 авг 06, 13:40    [3010371]     Ответить | Цитировать Сообщить модератору
 Re: В запросе не используется я индекс!  [new]
contr
Member

Откуда:
Сообщений: 1909
OCA-Ламер
contr

1) Как на счет выполнения с биндом под sqlplus - все опять плохо или где?

Попробовал на собственной БД, запрос с bind из sqlplus, всё плохо, индекс не подхватывается.

Ок, а если с биндом и /*+ index(table_alias index_name)*/ - подхватит?
И будет выполняться быстрее/дешевле (по LIO)?
Неформатированный план тяжело читать - если я правильно понял, функциональному индексу oracle предпочитает CLNH_CTYP_I ?

Снимите 10053 - как oracle оценивает стоимости доступа по этим индексам? Может, ему надо ненавязчиво подсказать?

И Бога ради, форматируйте планы dbms_xplan.display и заключайте в теги [src], равно как и тексты запросов.
16 авг 06, 13:52    [3010461]     Ответить | Цитировать Сообщить модератору
 Re: В запросе не используется я индекс!  [new]
Lehan
Member

Откуда: Ниоткуда
Сообщений: 2
А у тебя в представлении client_list_view /*+FIRST_ROWS*/ стоит?
16 авг 06, 13:58    [3010510]     Ответить | Цитировать Сообщить модератору
 Re: В запросе не используется я индекс!  [new]
contr
Member

Откуда:
Сообщений: 1909
Кстати, посмотрите-ка сюда
16 авг 06, 13:58    [3010516]     Ответить | Цитировать Сообщить модератору
 Re: В запросе не используется я индекс!  [new]
contr
Member

Откуда:
Сообщений: 1909
И сюда
16 авг 06, 14:01    [3010533]     Ответить | Цитировать Сообщить модератору
 Re: В запросе не используется я индекс!  [new]
Apex
Member

Откуда: Made in USSR
Сообщений: 3909
contr

Александр, Вы уж давайте прямо здесь - а то ведь автор даже версии СУБД не указал, а техника работы с outlines 8i разительно отличается от таковой в 9i и тем более в 10g :)

:-)
Ну, в данном случае имелась в виду 9i, т.к. на 10-ку еще только будем переходить, а на 8-ке клиентов нет (по крайней мере я не знаютаких).

По поводу outline для Вас ничего нового у меня нет:
- можно создать два outline: один для исходного запроса, второй для прохинченного, а затем поменить исходному хинты от прохинченного;
- создать outline с нужным лпаном доступа через EM - этим я сам никогда не пользовался, только видел как это делают.
:)
Lehan
А у тебя в представлении client_list_view /*+FIRST_ROWS*/ стоит?

У меня стоит, а что не должно? :-)
16 авг 06, 14:01    [3010539]     Ответить | Цитировать Сообщить модератору
 Re: В запросе не используется я индекс!  [new]
contr
Member

Откуда:
Сообщений: 1909
Apex
По поводу outline для Вас ничего нового у меня нет

Это для меня персонально? А откуда Вы знаете что это для меня не новое? :)
16 авг 06, 14:06    [3010575]     Ответить | Цитировать Сообщить модератору
 Re: В запросе не используется я индекс!  [new]
Lehan
Member

Откуда: Ниоткуда
Сообщений: 2
Lehan
А у тебя в представлении client_list_view /*+FIRST_ROWS*/ стоит?

У меня стоит, а что не должно? :-)[/quot]

А ты пробовал его убирать?
Я вот попробовал... план меняется... может это тебе чем-то поможет :)
16 авг 06, 14:07    [3010583]     Ответить | Цитировать Сообщить модератору
 Re: В запросе не используется я индекс!  [new]
contr
Member

Откуда:
Сообщений: 1909
Lehan
А ты пробовал его убирать?
Я вот попробовал... план меняется... может это тебе чем-то поможет :)

alter session set optimizer_{mode|goal} крутить не пробовали? :)
16 авг 06, 14:08    [3010594]     Ответить | Цитировать Сообщить модератору
 Re: В запросе не используется я индекс!  [new]
Apex
Member

Откуда: Made in USSR
Сообщений: 3909
contr

Это для меня персонально? А откуда Вы знаете что это для меня не новое? :)

Интуиция:)
Lehan

А ты пробовал его убирать?
Я вот попробовал... план меняется... может это тебе чем-то поможет :)

Мне помогло вот это

заменить like на =

Разбираться почему так, пока не охота... потом может гляну в трейс, если что напишу.
автор

alter session set optimizer_{mode|goal} крутить не пробовали? :)

А разве хинт не приоритетнее?
16 авг 06, 14:31    [3010789]     Ответить | Цитировать Сообщить модератору
 Re: В запросе не используется я индекс!  [new]
contr
Member

Откуда:
Сообщений: 1909
Apex
А разве хинт не приоритетнее?

Приоритетнее. Только при all_rows оптимизатор более оптимистично смотрит на не-индексные методы доступа, так что план-то, конечно, меняется, вот только в лучшую ли сторону - вопрос открытый.
Что касается "=" vs "like" - тут как раз NLS-тематика имеет место быть. Поищите по форуму организацию регистронезависимого поиска посредством установки nls_comp, nls_sort - он работает только с "=" и не работает с like.
Т.е. несмотря на то, что "=" и like без макросов логически эквивалентны, физически реализуются они немного по-разному.
16 авг 06, 14:37    [3010841]     Ответить | Цитировать Сообщить модератору
 Re: В запросе не используется я индекс!  [new]
Apex
Member

Откуда: Made in USSR
Сообщений: 3909
contr

Что касается "=" vs "like" - тут как раз NLS-тематика имеет место быть. Поищите по форуму организацию регистронезависимого поиска посредством установки nls_comp, nls_sort - он работает только с "=" и не работает с like.
Т.е. несмотря на то, что "=" и like без макросов логически эквивалентны, физически реализуются они немного по-разному.

А вот это уже интересно, спасибо:)
16 авг 06, 14:42    [3010891]     Ответить | Цитировать Сообщить модератору
 Re: В запросе не используется я индекс!  [new]
OCA-Ламер
Member

Откуда: Град Петра на реке Неве
Сообщений: 459
contr
[quot OCA-Ламер][quot contr]

Ок, а если с биндом и /*+ index(table_alias index_name)*/ - подхватит?



Сам запрос тянет из view, я view пересоздал вместе /*+ index(table_alias index_name)*/, протестировал,
запрос отрабатывает моментально, индекс подхватывается. Спасибо. :)

Решение не подходит клиенту, эта вьюха используется десятками других приложений, клиент в неё своих специальных хинтов понаставил ранее.
Требует срочно outline, а я до этого блин ни разу outline не писал.... :(
16 авг 06, 14:58    [3011034]     Ответить | Цитировать Сообщить модератору
 Re: В запросе не используется я индекс!  [new]
contr
Member

Откуда:
Сообщений: 1909
OCA-Ламер
Сам запрос тянет из view, я view пересоздал вместе /*+ index(table_alias index_name)*/,

Если не во view, а в запросе:
/*+ index(view_alias.table_alias index_name)*/
16 авг 06, 15:02    [3011066]     Ответить | Цитировать Сообщить модератору
 Re: В запросе не используется я индекс!  [new]
OCA-Ламер
Member

Откуда: Град Петра на реке Неве
Сообщений: 459
contr
[
Если не во view, а в запросе:
/*+ index(view_alias.table_alias index_name)*/



Нет, так индекс не подхватывается.
16 авг 06, 17:05    [3012348]     Ответить | Цитировать Сообщить модератору
 Re: В запросе не используется я индекс!  [new]
Vint
Member

Откуда: Москва
Сообщений: 4564
OCA-Ламер
contr
[
Если не во view, а в запросе:
/*+ index(view_alias.table_alias index_name)*/



Нет, так индекс не подхватывается.

Ну а если вы вместо вьюхи подставите её тескт и запихнете туда хинт???
16 авг 06, 17:20    [3012493]     Ответить | Цитировать Сообщить модератору
 Re: В запросе не используется я индекс!  [new]
OCA-Ламер
Member

Откуда: Град Петра на реке Неве
Сообщений: 459
contr

Что касается "=" vs "like" - тут как раз NLS-тематика имеет место быть. Поищите по форуму организацию регистронезависимого поиска посредством установки nls_comp, nls_sort - он работает только с "=" и не работает с like.
Т.е. несмотря на то, что "=" и like без макросов логически эквивалентны, физически реализуются они немного по-разному.


Да, подтверждаю. План меняется. Спасибо :)
16 авг 06, 17:31    [3012601]     Ответить | Цитировать Сообщить модератору
 Re: В запросе не используется я индекс!  [new]
contr
Member

Откуда:
Сообщений: 1909
OCA-Ламер
contr
Если не во view, а в запросе:
/*+ index(view_alias.table_alias index_name)*/

Нет, так индекс не подхватывается.

Вы что-то делаете не так:
SQL> create table test as select rownum X, 'val' val from dual connect by level < 1000;

Table created

SQL> alter table test modify x not null;

Table altered

SQL> create index test$x on test(nvl(x,0));

Index created

SQL> create view test_v as select /*+ all_rows full(t)*/ * from test t where val = 'val';

View created

SQL> alter session set optimizer_mode=all_rows;

Session altered

SQL> explain plan for select * from test_v where nvl(x,0) > :1;

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     1 |    18 |     2 |
|*  1 |  TABLE ACCESS FULL   | test        |     1 |    18 |     2 |
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(NVL("T"."X",0)>TO_NUMBER(:Z) AND "T"."VAL"='val')
Note: cpu costing is off

14 rows selected

SQL> rollback;

Rollback complete

SQL> explain plan for select /*+ index(v.t test$x)*/ * from test_v v where nvl(x,0) > :1;

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------------------------------
| Id  | Operation                   |  Name       | Rows  | Bytes | Cost  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |    18 |     1 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| test        |     1 |    18 |     1 |
|*  2 |   INDEX RANGE SCAN          | test$X      |     3 |       |     2 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("T"."VAL"='val')
   2 - access(NVL("T"."X",0)>TO_NUMBER(:Z) AND NVL("T"."X",0) IS NOT NULL)
Note: cpu costing is off

16 rows selected

SQL> rollback;

Rollback complete

SQL> 
16 авг 06, 17:36    [3012636]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Oracle Ответить