Чем дальше, тем меньше понимаю.
Есть запрос, выполняемый веб-приложением (скриптом на PHP). Запрос возвращает несколько строк (не более пары десятков).
Выполняется очень долго, веб-сервер закрывает скрипт по таймауту.
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.
select * from (
select
C.CUSTOMER_ID as ID
, CT.NAME as TYPE
, CC.VALUE as CLIENT
, CP.VALUE as PHONE
, CA.VALUE as ADDRESS
, S.SERVICE_ID as SERVICE_ID
, S.LOGIN as SERVICE_LOGIN
, SR.VALUE as SERVICE_IP
, Z.CURSESSIONS_ID as SESSION_ID
, R.USERNAME as SESSION_USERNAME
, R.ACCTSESSIONID as SESSION_KEY
, R.ACCTSTARTTIME as SESSION_START
, null as SESSION_STOP
, 86400 * (sysdate - R.ACCTSTARTTIME) as SESSION_TIME
, R.ACCTINPUTOCTETS as SESSION_DL
, R.ACCTOUTPUTOCTETS as SESSION_UL
, R.ACCTTERMINATECAUSE as SESSION_TERMINATE
, R.CALLINGSTATIONID as PORT
, R.FRAMEDIPADDRESS as IP
, R.CLIENT_MAC_ADDRESS as MAC
from CUSTOMERS C
join BM_GROUP CG on (CG.GROUP_ID = C.GROUP_ID)
join BM_CUSTOMER_TYPE CT on (CT.CUSTOMER_TYPE_ID = C.CUSTOMER_TYPE_ID)
join BM_CUSTOMER_CONTACT CC on (CC.CUSTOMER_ID = C.CUSTOMER_ID and CC.CONTACT_DICT_ID = 100)
left join BM_CUSTOMER_CONTACT CP on (CP.CUSTOMER_ID = C.CUSTOMER_ID and CP.CONTACT_DICT_ID = 6)
left join BM_CUSTOMER_CONTACT CA on (CA.CUSTOMER_ID = C.CUSTOMER_ID and CA.CONTACT_DICT_ID = 3)
join SERVICES S on (S.CUSTOMER_ID = C.CUSTOMER_ID and S.TYPE_ID = 14 and S.STATUS != -20 and not exists (select TARIFFEL_ID from BM_TARIFFEL where TARIFF_ID = S.TARIFF_ID and TARIFFEL_TYPE_ID = 908))
left join RADREPLY SR on (SR.SERVICE_ID = S.SERVICE_ID and SR.ATTRIBUTE = 'Framed-IP-Address' and SR.DATE_BEG <= sysdate and SR.DATE_END is null)
left join CURSESSIONS Z on (Z.SERVICE_ID = S.SERVICE_ID)
left join RADACCT R on (R.RADACCTID = Z.RADACCTID)
where C.GROUP_ID in (select ID from TEMP.IDGROUPS where SCOPE = 'client-groups-sms')
and C.STATUS > 0
and coalesce(R.FRAMEDIPADDRESS,SR.VALUE,'-') = :ip
and CA.CONTACT_DICT_ID = 3 and upper(CA.VALUE) like upper(:address)
and R.ACCTSTARTTIME >= to_date(:period_beg,'yyyy-mm-dd hh24:mi:ss')
and R.ACCTSTARTTIME <= to_date(:period_end,'yyyy-mm-dd hh24:mi:ss')
union all
select
C.CUSTOMER_ID as ID
, CT.NAME as TYPE
, CC.VALUE as CLIENT
, CP.VALUE as PHONE
, CA.VALUE as ADDRESS
, S.SERVICE_ID as SERVICE_ID
, S.LOGIN as SERVICE_LOGIN
, SR.VALUE as SERVICE_IP
, null as SESSION_ID
, R.USERNAME as SESSION_USERNAME
, R.ACCTSESSIONID as SESSION_KEY
, R.ACCTSTARTTIME as SESSION_START
, R.ACCTSTOPTIME as SESSION_STOP
, R.ACCTSESSIONTIME as SESSION_TIME
, R.ACCTINPUTOCTETS as SESSION_DL
, R.ACCTOUTPUTOCTETS as SESSION_UL
, R.ACCTTERMINATECAUSE as SESSION_TERMINATE
, R.CALLINGSTATIONID as PORT
, R.FRAMEDIPADDRESS as IP
, R.CLIENT_MAC_ADDRESS as MAC
from (
select --/*+ USE_NL(S B R T) */
R.RADACCTID
, S.SERVICE_ID
, R.USERNAME
, R.ACCTSESSIONID
, R.ACCTSTARTTIME
, R.ACCTSTOPTIME
, R.ACCTSESSIONTIME
, R.ACCTINPUTOCTETS
, R.ACCTOUTPUTOCTETS
, R.ACCTTERMINATECAUSE
, R.CALLINGSTATIONID
, R.FRAMEDIPADDRESS
, R.CLIENT_MAC_ADDRESS
from RADACCT R
join BM_SERVICE_MONEY B on (B.RADACCTID = R.RADACCTID)
join BM_TARIFFEL T on (T.TARIFFEL_ID = B.TARIFFEL_ID)
join SERVICES S on (S.SERVICE_ID = B.SERVICE_ID)
where S.TYPE_ID = 14
and R.ACCTSTARTTIME >= to_date(:period_beg,'yyyy-mm-dd hh24:mi:ss')
and R.ACCTSTARTTIME <= to_date(:period_end,'yyyy-mm-dd hh24:mi:ss')
group by
R.RADACCTID
, S.SERVICE_ID
, R.USERNAME
, R.ACCTSESSIONID
, R.ACCTSTARTTIME
, R.ACCTSTOPTIME
, R.ACCTSESSIONTIME
, R.ACCTINPUTOCTETS
, R.ACCTOUTPUTOCTETS
, R.ACCTTERMINATECAUSE
, R.CALLINGSTATIONID
, R.FRAMEDIPADDRESS
, R.CLIENT_MAC_ADDRESS
) R
join SERVICES S on (S.SERVICE_ID = R.SERVICE_ID)
join CUSTOMERS C on (C.CUSTOMER_ID = S.CUSTOMER_ID)
join BM_GROUP CG on (CG.GROUP_ID = C.GROUP_ID)
join BM_CUSTOMER_TYPE CT on (CT.CUSTOMER_TYPE_ID = C.CUSTOMER_TYPE_ID)
join BM_CUSTOMER_CONTACT CC on (CC.CUSTOMER_ID = C.CUSTOMER_ID and CC.CONTACT_DICT_ID = 100)
left join BM_CUSTOMER_CONTACT CP on (CP.CUSTOMER_ID = C.CUSTOMER_ID and CP.CONTACT_DICT_ID = 6)
left join BM_CUSTOMER_CONTACT CA on (CA.CUSTOMER_ID = C.CUSTOMER_ID and CA.CONTACT_DICT_ID = 3)
left join RADREPLY SR on (SR.SERVICE_ID = S.SERVICE_ID and SR.ATTRIBUTE = 'Framed-IP-Address' and SR.DATE_BEG <= sysdate and SR.DATE_END is null)
where C.GROUP_ID in (select ID from TEMP.IDGROUPS where SCOPE = 'client-groups-sms')
and C.STATUS > 0
and R.FRAMEDIPADDRESS = :ip
and CA.CONTACT_DICT_ID = 3 and upper(CA.VALUE) like upper(:address)
)
Этот же запрос я сегодня запускал в TOAD — в первый раз он выполнялся около полутора минут, в следующие разы отрабатывал быстро (секунды), даже если я изменял некоторые параметры.
Затем я этот же запрос запустил в SQL*Plus, предварительно задав параметры (такие же как на сайте и в TOAD):
1.
2.
3.
4.
5.
6.
7.
8.
var ip varchar2(20)
exec :ip:='11.22.33.44'
var address varchar2(40)
exec :address:='Международная%'
var period_beg varchar2(20)
exec :period_beg:='2016-10-01 00:00:00'
var period_end varchar2(20)
exec :period_end:='9999-01-01 23:59:59'
В первый раз запрос выполнялся больше минуты, в следующие разы выполнялся за секунды.
С сайта запрос по прежнему выполнялся очень долго (точнее его прерывал сервер по таймауту).
Сейчас решил снова проверить, запустил его в SQL*Plus — и он выполняется уже почти 10 минут.
Запустил его же в TOAD, с теми же параметрами — запрос выполнился за 31 секунду.
Повторные запуски — около 400мс.
Остановил запрос в SQL*Plus (прошло более 10 минут, он так и не завершился).
Запустил его еще раз, прошло уже минуты четыре, а он так и не завершился.
Почему в TOAD запрос выполняется быстро? Даже если изменять параметры (:ip или :address), время выполнения не превышает десятка секунд.
Почему в SQL*Plus днем он выполнялся быстро, а сейчас долго? SQL*Plus один и тот же, на одной и той же машине (она же сервер СУБД), никаких настроек или окружение среды я не изменял (да и не умею).
Почему из PHP (используется библиотека OCI) запрос выполняется долго? В PHP я первоначально использовал хинт RESULT_CACHE, но я пробовал его убирать, ничего не менялось.
Вот текущий план из TOAD:
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.
113.
114.
115.
116.
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 3284 | 151K (1)| 00:30:22 |
| 1 | VIEW | | 2 | 3284 | 151K (1)| 00:30:22 |
| 2 | UNION-ALL | | | | | |
|* 3 | FILTER | | | | | |
| 4 | NESTED LOOPS | | 1 | 437 | 202 (2)| 00:00:03 |
| 5 | NESTED LOOPS | | 3 | 852 | 196 (2)| 00:00:03 |
| 6 | NESTED LOOPS OUTER | | 4 | 1012 | 188 (2)| 00:00:03 |
| 7 | NESTED LOOPS OUTER | | 4 | 888 | 180 (2)| 00:00:03 |
| 8 | NESTED LOOPS | | 4 | 648 | 172 (2)| 00:00:03 |
|* 9 | HASH JOIN ANTI | | 4 | 580 | 167 (2)| 00:00:03 |
|* 10 | TABLE ACCESS BY INDEX ROWID | SERVICES | 2 | 82 | 7 (0)| 00:00:01 |
| 11 | NESTED LOOPS | | 35 | 4760 | 155 (2)| 00:00:02 |
|* 12 | HASH JOIN | | 18 | 1710 | 66 (5)| 00:00:01 |
| 13 | TABLE ACCESS BY INDEX ROWID | BM_CUSTOMER_CONTACT | 310 | 9610 | 46 (0)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | CUSTOMER_CONTACT_VAL_IDX | 56 | | 3 (0)| 00:00:01 |
| 15 | NESTED LOOPS | | 544 | 34816 | 19 (11)| 00:00:01 |
|* 16 | HASH JOIN | | 9607 | 431K| 18 (6)| 00:00:01 |
| 17 | TABLE ACCESS FULL | BM_CUSTOMER_TYPE | 9 | 270 | 3 (0)| 00:00:01 |
|* 18 | TABLE ACCESS FULL | CUSTOMERS | 9607 | 150K| 14 (0)| 00:00:01 |
|* 19 | INDEX UNIQUE SCAN | IDGROUPS_PK | 1 | 18 | 0 (0)| 00:00:01 |
|* 20 | INDEX RANGE SCAN | SERV_CUSTOMER_IDX | 7 | | 1 (0)| 00:00:01 |
| 21 | TABLE ACCESS BY INDEX ROWID | BM_TARIFFEL | 254 | 2286 | 12 (0)| 00:00:01 |
|* 22 | INDEX RANGE SCAN | TAREL_TYPE_IDX | 254 | | 1 (0)| 00:00:01 |
| 23 | TABLE ACCESS BY INDEX ROWID | CURSESSIONS | 1 | 17 | 2 (0)| 00:00:01 |
|* 24 | INDEX RANGE SCAN | CURSESS_SERVICE_IDX | 1 | | 1 (0)| 00:00:01 |
|* 25 | TABLE ACCESS BY INDEX ROWID | RADREPLY | 1 | 60 | 3 (0)| 00:00:01 |
|* 26 | INDEX RANGE SCAN | RADREPLY_SERVICE_IDX | 8 | | 1 (0)| 00:00:01 |
| 27 | TABLE ACCESS BY INDEX ROWID | BM_CUSTOMER_CONTACT | 1 | 31 | 2 (0)| 00:00:01 |
|* 28 | INDEX RANGE SCAN | CUSTOMER_CONTACT_IDX | 1 | | 1 (0)| 00:00:01 |
| 29 | TABLE ACCESS BY INDEX ROWID | BM_CUSTOMER_CONTACT | 1 | 31 | 2 (0)| 00:00:01 |
|* 30 | INDEX RANGE SCAN | CUSTOMER_CONTACT_IDX | 1 | | 1 (0)| 00:00:01 |
|* 31 | TABLE ACCESS BY INDEX ROWID | RADACCT | 1 | 153 | 2 (0)| 00:00:01 |
|* 32 | INDEX UNIQUE SCAN | RADACCT_PK | 1 | | 1 (0)| 00:00:01 |
| 33 | HASH GROUP BY | | 1 | 524 | 151K (1)| 00:30:19 |
|* 34 | FILTER | | | | | |
| 35 | NESTED LOOPS OUTER | | 1 | 524 | 151K (1)| 00:30:19 |
| 36 | NESTED LOOPS | | 1 | 462 | 151K (1)| 00:30:19 |
| 37 | NESTED LOOPS | | 1 | 446 | 151K (1)| 00:30:19 |
| 38 | NESTED LOOPS | | 1 | 407 | 151K (1)| 00:30:19 |
| 39 | NESTED LOOPS OUTER | | 1 | 364 | 151K (1)| 00:30:19 |
|* 40 | HASH JOIN | | 1 | 321 | 151K (1)| 00:30:19 |
| 41 | NESTED LOOPS | | 38 | 10564 | 151K (1)| 00:30:19 |
|* 42 | HASH JOIN | | 665 | 168K| 151K (1)| 00:30:19 |
|* 43 | HASH JOIN | | 665 | 150K| 151K (1)| 00:30:19 |
|* 44 | HASH JOIN | | 663 | 123K| 151K (1)| 00:30:16 |
|* 45 | TABLE ACCESS BY INDEX ROWID | BM_SERVICE_MONEY | 14 | 224 | 6 (0)| 00:00:01 |
| 46 | NESTED LOOPS | | 662 | 117K| 151K (1)| 00:30:14 |
|* 47 | TABLE ACCESS BY INDEX ROWID| RADACCT | 47 | 7802 | 150K (1)| 00:30:12 |
|* 48 | INDEX RANGE SCAN | RADACCT_ACCTSTARTTIME_IDX | 236K| | 1191 (1)| 00:00:15 |
|* 49 | INDEX RANGE SCAN | SVRM_RADACCT_IDX | 14 | | 3 (0)| 00:00:01 |
|* 50 | TABLE ACCESS FULL | SERVICES | 23960 | 210K| 194 (1)| 00:00:03 |
| 51 | TABLE ACCESS FULL | SERVICES | 47772 | 1912K| 194 (1)| 00:00:03 |
|* 52 | TABLE ACCESS FULL | CUSTOMERS | 9607 | 262K| 14 (0)| 00:00:01 |
|* 53 | INDEX UNIQUE SCAN | IDGROUPS_PK | 1 | 18 | 0 (0)| 00:00:01 |
| 54 | TABLE ACCESS BY INDEX ROWID | BM_CUSTOMER_CONTACT | 310 | 13330 | 46 (0)| 00:00:01 |
|* 55 | INDEX RANGE SCAN | CUSTOMER_CONTACT_VAL_IDX | 56 | | 3 (0)| 00:00:01 |
| 56 | TABLE ACCESS BY INDEX ROWID | BM_CUSTOMER_CONTACT | 1 | 43 | 2 (0)| 00:00:01 |
|* 57 | INDEX RANGE SCAN | CUSTOMER_CONTACT_IDX | 1 | | 1 (0)| 00:00:01 |
| 58 | TABLE ACCESS BY INDEX ROWID | BM_CUSTOMER_CONTACT | 1 | 43 | 2 (0)| 00:00:01 |
|* 59 | INDEX RANGE SCAN | CUSTOMER_CONTACT_IDX | 1 | | 1 (0)| 00:00:01 |
| 60 | TABLE ACCESS BY INDEX ROWID | BM_CUSTOMER_TYPE | 1 | 39 | 1 (0)| 00:00:01 |
|* 61 | INDEX UNIQUE SCAN | BM_CUSTOMER_TYPE_PK | 1 | | 0 (0)| 00:00:01 |
|* 62 | INDEX UNIQUE SCAN | GROUP_PK | 1 | 16 | 0 (0)| 00:00:01 |
|* 63 | TABLE ACCESS BY INDEX ROWID | RADREPLY | 1 | 62 | 3 (0)| 00:00:01 |
|* 64 | INDEX RANGE SCAN | RADREPLY_SERVICE_IDX | 8 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(TO_DATE(:PERIOD_BEG,'yyyy-mm-dd hh24:mi:ss')<=TO_DATE(:PERIOD_END,'yyyy-mm-dd hh24:mi:ss'))
9 - access("TARIFF_ID"="S"."TARIFF_ID")
10 - filter("S"."TYPE_ID"=14 AND "S"."STATUS"<>(-20))
12 - access("CA"."CUSTOMER_ID"="C"."CUSTOMER_ID")
14 - access("CA"."CONTACT_DICT_ID"=3 AND UPPER("VALUE") LIKE UPPER(:ADDRESS))
filter(UPPER("VALUE") LIKE UPPER(:ADDRESS))
16 - access("CT"."CUSTOMER_TYPE_ID"="C"."CUSTOMER_TYPE_ID")
18 - filter("C"."STATUS">0)
19 - access("SCOPE"='client-groups-sms' AND "C"."GROUP_ID"="ID")
20 - access("S"."CUSTOMER_ID"="C"."CUSTOMER_ID")
22 - access("TARIFFEL_TYPE_ID"=908)
24 - access("Z"."SERVICE_ID"="S"."SERVICE_ID")
25 - filter("SR"."ATTRIBUTE"(+)='Framed-IP-Address' AND "SR"."DATE_END"(+) IS NULL AND
"SR"."DATE_BEG"(+)<=SYSDATE@!)
26 - access("SR"."SERVICE_ID"(+)="S"."SERVICE_ID")
28 - access("CP"."CUSTOMER_ID"(+)="C"."CUSTOMER_ID" AND "CP"."CONTACT_DICT_ID"(+)=6)
30 - access("CC"."CUSTOMER_ID"="C"."CUSTOMER_ID" AND "CC"."CONTACT_DICT_ID"=100)
31 - filter("R"."ACCTSTARTTIME">=TO_DATE(:PERIOD_BEG,'yyyy-mm-dd hh24:mi:ss') AND
"R"."ACCTSTARTTIME"<=TO_DATE(:PERIOD_END,'yyyy-mm-dd hh24:mi:ss') AND
COALESCE("R"."FRAMEDIPADDRESS","SR"."VALUE",'-')=:IP)
32 - access("R"."RADACCTID"="Z"."RADACCTID")
34 - filter(TO_DATE(:PERIOD_BEG,'yyyy-mm-dd hh24:mi:ss')<=TO_DATE(:PERIOD_END,'yyyy-mm-dd hh24:mi:ss'))
40 - access("CA"."CUSTOMER_ID"="C"."CUSTOMER_ID")
42 - access("C"."CUSTOMER_ID"="S"."CUSTOMER_ID")
43 - access("S"."SERVICE_ID"="S"."SERVICE_ID")
44 - access("S"."SERVICE_ID"="B"."SERVICE_ID")
45 - filter("B"."TARIFFEL_ID" IS NOT NULL)
47 - filter("R"."FRAMEDIPADDRESS"=:IP)
48 - access("R"."ACCTSTARTTIME">=TO_DATE(:PERIOD_BEG,'yyyy-mm-dd hh24:mi:ss') AND
"R"."ACCTSTARTTIME"<=TO_DATE(:PERIOD_END,'yyyy-mm-dd hh24:mi:ss'))
49 - access("B"."RADACCTID"="R"."RADACCTID")
50 - filter("S"."TYPE_ID"=14)
52 - filter("C"."STATUS">0)
53 - access("SCOPE"='client-groups-sms' AND "C"."GROUP_ID"="ID")
55 - access("CA"."CONTACT_DICT_ID"=3 AND UPPER("VALUE") LIKE UPPER(:ADDRESS))
filter(UPPER("VALUE") LIKE UPPER(:ADDRESS))
57 - access("CP"."CUSTOMER_ID"(+)="C"."CUSTOMER_ID" AND "CP"."CONTACT_DICT_ID"(+)=6)
59 - access("CC"."CUSTOMER_ID"="C"."CUSTOMER_ID" AND "CC"."CONTACT_DICT_ID"=100)
61 - access("CT"."CUSTOMER_TYPE_ID"="C"."CUSTOMER_TYPE_ID")
62 - access("CG"."GROUP_ID"="C"."GROUP_ID")
63 - filter("SR"."ATTRIBUTE"(+)='Framed-IP-Address' AND "SR"."DATE_END"(+) IS NULL AND
"SR"."DATE_BEG"(+)<=SYSDATE@!)
64 - access("SR"."SERVICE_ID"(+)="S"."SERVICE_ID")
С SQL*Plus план срисовать не смог, из-за переноса строк он совершенно нечитаем.
