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

Откуда: E-burg
Сообщений: 108
вот выдержка из TKPROF:

select cd.id, cd.contact_id,  cd.name_f||' '||cd.name_i||' '||cd.name_o name, rl.role_type
from contact_details cd, roles rl
where (cd.contact_id = rl.contact_id)
and (rl.role_type not in (1,5))
and cd.contact_id not in (select contact_id from group_contacts where group_id=101)
and ((select is_active from contacts ct where ct.id=cd.contact_id)=1)
order by name

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 5 434.86 425.38 0 8584048 0 55
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 434.86 425.39 0 8584048 0 55

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 31

Rows Row Source Operation
------- ---------------------------------------------------
55 SORT ORDER BY (cr=8584048 pr=0 pw=0 time=425387390 us)
55 MERGE JOIN (cr=8584048 pr=0 pw=0 time=14462056 us)
68 TABLE ACCESS BY INDEX ROWID CONTACT_DETAILS (cr=8583864 pr=0 pw=0 time=17932569 us)
68 INDEX FULL SCAN CONTACT_DETAILS_CONTACT_ID (cr=8583847 pr=0 pw=0 time=9577 us)(object id 17220)
25 TABLE ACCESS FULL GROUP_CONTACTS (cr=137781 pr=0 pw=0 time=1241029 us)
45902 TABLE ACCESS FULL CONTACTS (cr=8445968 pr=0 pw=0 time=423855177 us)
55 SORT JOIN (cr=184 pr=0 pw=0 time=6451 us)
119 TABLE ACCESS FULL ROLES (cr=184 pr=0 pw=0 time=380 us)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 5 0.00 0.00
SQL*Net message from client 5 0.00 0.00


Oracle Database 10g Release 10.2.0.2.0 - 64bit Production
17 янв 07, 12:48    [3653525]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Бабичев Сергей
Member

Откуда:
Сообщений: 2498
utyf

select cd.id, cd.contact_id,  cd.name_f||' '||cd.name_i||' '||cd.name_o name, rl.role_type
from contact_details  cd,          roles  rl
where (cd.contact_id = rl.contact_id)
  and (rl.role_type not in (1,5))
  and cd.contact_id not in (select contact_id from group_contacts where group_id=101)
  and ((select is_active from contacts  ct where ct.id=cd.contact_id)=1)
order by name

Выделенное желтым переписать через JOIN.
Либо заставить оптимизатор в NOT IN подзапросе использовать HASH ANTI JOIN.
17 янв 07, 13:00    [3653660]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
utyf
Member

Откуда: E-burg
Сообщений: 108
ТАК?
select cd.id, cd.contact_id,  cd.name_f||' '||cd.name_i||' '||cd.name_o name, rl.role_type
from contact_details  cd, roles  rl, contacts ct, group_contacts gc
where (cd.contact_id = rl.contact_id)
  and (rl.role_type not in (1,5))
  and gc.group_id<>101
  and ct.id = cd.contact_id
  and is_active = 1
order by name
17 янв 07, 13:04    [3653699]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Бабичев Сергей
Member

Откуда:
Сообщений: 2498
utyf
ТАК?
select cd.id, cd.contact_id,  cd.name_f||' '||cd.name_i||' '||cd.name_o name, rl.role_type
from contact_details  cd, roles  rl, contacts ct, group_contacts gc
where (cd.contact_id = rl.contact_id)
  and (rl.role_type not in (1,5))
  and gc.group_id<>101
  and ct.id = cd.contact_id
  and is_active = 1
order by name
У тебя так декарт получится... Ведь нет уловий связки таблицы cd и gc
17 янв 07, 13:11    [3653783]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Бабичев Сергей
Member

Откуда:
Сообщений: 2498
А вообще, вот так попробуй:
select cd.id, cd.contact_id,  cd.name_f||' '||cd.name_i||' '||cd.name_o name, rl.role_type
from contact_details  cd,          roles  rl,   contacts ct
where (cd.contact_id = rl.contact_id)
  and (rl.role_type not in (1,5))
  and cd.contact_id not in (select /*+ hash_aj*/ contact_id from group_contacts where group_id=101)
  and ct.id = cd.contact_id
  and ct.is_active = 1
order by name
17 янв 07, 13:14    [3653817]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
utyf
Member

Откуда: E-burg
Сообщений: 108
Ошибку уже сам заметил!

Спасибо за помощь!
17 янв 07, 13:16    [3653835]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Vanoo
Member

Откуда: Краснодар
Сообщений: 180
Извиняюсь... Я пока мало понимаю в тонкостях SQL и Oracle.
Если не сложно объясните почему этот запрос эфективнее....
Заранее благодарен!
17 янв 07, 13:22    [3653899]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Andrew Max
Member

Откуда:
Сообщений: 1045
Бабичев Сергей
А вообще, вот так попробуй:
select ...
  ...
 and cd.contact_id not in (select /*+ hash_aj*/ contact_id from group_contacts where group_id=101)
  ...

В 10g хинт HASH_AJ "is deprecated" .
17 янв 07, 13:25    [3653934]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
YAP
Member

Откуда: Киев
Сообщений: 2116
А можете запустить запрос с таким хинтом:

/*+gather_plan_statistics*/

а потом результаты этого запроса сюда:

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
17 янв 07, 13:27    [3653959]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
utyf
Member

Откуда: E-burg
Сообщений: 108
YAP
А можете запустить запрос с таким хинтом:

/*+gather_plan_statistics*/

а потом результаты этого запроса сюда:

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));


ТАК?

select /*+gather_plan_statistics*/ cd.id, cd.contact_id,  cd.name_f||' '||cd.name_i||' '||cd.name_o name, rl.role_type 
from contact_details  cd,          roles  rl 
where (cd.contact_id = rl.contact_id) 
  and (rl.role_type not in (1,5)) 
  and cd.contact_id not in (select contact_id from group_contacts where group_id=101)
  and ((select is_active from contacts  ct where ct.id=cd.contact_id)=1) 
order by name

вот результат:

SQL_ID  6cqfw5ktg794y, child number 0                                           
-------------------------------------
select /*+gather_plan_statistics*/ cd.id, cd.contact_id, cd.name_f||' '||cd.name_i||' '||cd.name_o name, rl.role_type from
contact_details cd, roles rl where (cd.contact_id = rl.contact_id) and (rl.role_type not in (1,5)) and cd.contact_id not
in (select contact_id from group_contacts where group_id=101) and ((select is_active from contacts ct where ct.id=cd.contact_id)=1)


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
order by name

Plan hash value: 3540426134

--------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |

| 1 | SORT ORDER BY | | 1 | 42347 | 39 |00:07:08.12 | 8581K| 4096 | 4096 | 4096 (0)|
| 2 | MERGE JOIN | | 1 | 42348 | 39 |00:00:10.13 | 8581K| | | |
| 3 | TABLE ACCESS BY INDEX ROWID| CONTACT_DETAILS | 1 | 60981 | 54 |00:00:14.11 | 8580K| | | |
|* 4 | INDEX FULL SCAN | CONTACT_DETAILS_CONTACT_ID | 1 | 30 | 54 |00:00:00.01 | 8580K| | | |
|* 5 | TABLE ACCESS FULL | GROUP_CONTACTS | 45927 | 1 | 41 |00:00:01.42 | 137K| | | |
|* 6 | TABLE ACCESS FULL | CONTACTS | 45886 | 1 | 45886 |00:07:06.38 | 8443K| | | |
|* 7 | SORT JOIN | | 54 | 42348 | 39 |00:00:00.01 | 184 | 4096 | 4096 | 4096 (0)|
|* 8 | TABLE ACCESS FULL | ROLES | 1 | 42348 | 119 |00:00:00.01 | 184 | | | |



Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter(( IS NULL AND =1))
5 - filter(("GROUP_ID"=101 AND LNNVL("CONTACT_ID"<>:B1)))
6 - filter("CT"."ID"=:B1)
7 - access("CD"."CONTACT_ID"="RL"."CONTACT_ID")
filter("CD"."CONTACT_ID"="RL"."CONTACT_ID")
8 - filter(("RL"."ROLE_TYPE"<>1 AND "RL"."ROLE_TYPE"<>5))
17 янв 07, 13:59    [3654274]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
YAP
Member

Откуда: Киев
Сообщений: 2116
да, именно,

а теперь если можно вот такой запрос

select /*+gather_plan_statistics*/ cd.id, cd.contact_id, cd.name_f||' '||cd.name_i||' '||cd.name_o name, rl.role_type
from contact_details cd, roles rl ,contacts ct
where (cd.contact_id = rl.contact_id)
and (rl.role_type not in (1,5))
and cd.contact_id not in (select contact_id from group_contacts where group_id=101)
and ct.id=cd.contact_id and is_active=1
order by name

ну и вывод select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST')); сюда же
17 янв 07, 14:11    [3654390]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
utyf
Member

Откуда: E-burg
Сообщений: 108
SQL_ID  9h06bjtr2g2xv, child number 0                                                                                               
-------------------------------------
select /*+gather_plan_statistics*/ cd.id, cd.contact_id, cd.name_f||' '||cd.name_i||' '||cd.name_o name, rl.role_type from
contact_details cd, roles rl ,contacts ct where (cd.contact_id = rl.contact_id) and (rl.role_type not in (1,5)) and cd.contact_id no
t in (select contact_id from group_contacts where group_id=101) and ct.id=cd.contact_id and is_active=1 order by name

Plan hash value: 3638041659

| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |

| 1 | SORT ORDER BY | | 1 | 21174 | 39 |00:00:01.52 | 141K| 4096 | 4096 | 4096 (0)|
|* 2 | HASH JOIN | | 1 | 21174 | 39 |00:00:00.05 | 141K| 1236K| 1236K| 1220K (0)|
|* 3 | TABLE ACCESS FULL | ROLES | 1 | 42348 | 119 |00:00:00.01 | 184 | | | |
| 4 | MERGE JOIN | | 1 | 30491 | 54 |00:00:00.06 | 140K| | | |
| 5 | TABLE ACCESS BY INDEX ROWID| CONTACT_DETAILS | 1 | 60981 | 45887 |00:00:01.42 | 140K| | | |
|* 6 | INDEX FULL SCAN | CONTACT_DETAILS_CONTACT_ID | 1 | 3049 | 45887 |00:00:01.24 | 137K| | | |
|* 7 | TABLE ACCESS FULL | GROUP_CONTACTS | 45928 | 1 | 41 |00:00:01.14 | 137K| | | |
|* 8 | SORT JOIN | | 45887 | 30491 | 54 |00:00:00.09 | 184 | 4096 | 4096 | 4096 (0)|
|* 9 | TABLE ACCESS FULL | CONTACTS | 1 | 30491 | 95 |00:00:00.04 | 184 | | | |

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("CD"."CONTACT_ID"="RL"."CONTACT_ID")
3 - filter(("RL"."ROLE_TYPE"<>1 AND "RL"."ROLE_TYPE"<>5))
6 - filter( IS NULL)
7 - filter(("GROUP_ID"=101 AND LNNVL("CONTACT_ID"<>:B1)))
8 - access("CT"."ID"="CD"."CONTACT_ID")
filter("CT"."ID"="CD"."CONTACT_ID")
9 - filter("IS_ACTIVE"=1)
17 янв 07, 14:21    [3654487]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
YAP
Member

Откуда: Киев
Сообщений: 2116
даже после перепысывания уже лучше
было 00:07:08.12 , стало 00:00:01.52,

если есть желание и возможность обновите статистику по таблицам(и их индексам), участвующим в этом запросе, соберите гистограммы по:
CONTACT.IS_ACTIVE
RL.ROLE_TYPE

ну и еще раз запрос с хинтом и план в студию.
17 янв 07, 14:32    [3654603]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
utyf
Member

Откуда: E-burg
Сообщений: 108
Статистики обновлялась по схеме сегодня утром. Гистограммы можно собрать, но вопрос был в переписывании запроса, в чем мне и помогли)

Я не совсем понял к чему вы меня попросили показать планы... думал что это для ответа на вопрос

Vanoo
Извиняюсь... Я пока мало понимаю в тонкостях SQL и Oracle.
Если не сложно объясните почему этот запрос эфективнее....
Заранее благодарен!


а то что запрос стал быстрее работать после переписывания - это без вопросов. Мне изначально не совсем понятно было как его переписать, чтобы уменьшить кол-во запросов к таблице CONTACTS и GROUP_CONTACTS, но сейчас все стало ясно! Жаль что сам не догадался...
17 янв 07, 14:54    [3654805]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
YAP
Member

Откуда: Киев
Сообщений: 2116
utyf
Статистики обновлялась по схеме сегодня утром. Гистограммы можно собрать, но вопрос был в переписывании запроса, в чем мне и помогли)

Я не совсем понял к чему вы меня попросили показать планы... думал что это для ответа на вопрос

Vanoo
Извиняюсь... Я пока мало понимаю в тонкостях SQL и Oracle.
Если не сложно объясните почему этот запрос эфективнее....
Заранее благодарен!


а то что запрос стал быстрее работать после переписывания - это без вопросов. Мне изначально не совсем понятно было как его переписать, чтобы уменьшить кол-во запросов к таблице CONTACTS и GROUP_CONTACTS, но сейчас все стало ясно! Жаль что сам не догадался...


планы - это информация к размышлению.

кстати, после перепысывания еще, как мне кажется, есть потенциал для ускорения (это может проясниться после сбора гистограмм)
17 янв 07, 14:57    [3654842]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
10053
Guest
utyf
Статистики обновлялась по схеме сегодня утром. Гистограммы можно собрать, но вопрос был в переписывании запроса, в чем мне и помогли)

Я не совсем понял к чему вы меня попросили показать планы... думал что это для ответа на вопрос

Человек только что гистограммы изучил, дайте ему потренироваться :)
17 янв 07, 16:05    [3655384]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
----------------
Guest
а если так попробовать?
select cd.id, cd.contact_id,  cd.name_f||' '||cd.name_i||' '||cd.name_o name, rl.role_type
from contact_details  cd, roles  rl, group_contacts gc, contacts ct
where (cd.contact_id = rl.contact_id)
  and (rl.role_type not in (1,5))
  and gc.contact_id = cd.contact_id
  and gc.group_id <> 101
  and ct.id=cd.contact_id
  and ct.is_active=1
order by name
17 янв 07, 16:30    [3655573]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Бабичев Сергей
Member

Откуда:
Сообщений: 2498
Andrew Max
В 10g хинт HASH_AJ "is deprecated" .
ОК, учту на будущее...
18 янв 07, 08:23    [3657659]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить