本帖最后由 qr8345776 于 2016-12-1 17:18 编辑
SELECT a.subscription_id,
a.service_type,
a.account_id,
a.region_id,
a.service_num
FROM ucs_subs_gsm a, ucs_bank_contract b
WHERE a.account_id = b.account_id
AND a.service_num = b.serial_number
AND SYSDATE > a.active_date
AND a.active_date < a.inactive_date
AND b.exp_dt > SYSDATE
AND b.remark IS NULL
AND b.agr_dt BETWEEN to_date(20121210, 'yyyymmdd') AND
to_date(20141231, 'yyyymmdd')
AND NOT EXISTS (SELECT *
FROM ucs_subs_scheme
WHERE subscription_id IN
(SELECT subscription_id
FROM ucs_subs_gsm
WHERE inactive_date > active_date
AND SYSDATE < inactive_date
AND account_id = a.account_id)
AND scheme_id = 113692)
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4097 | 396K| 81447 (1)| 00:16:18 | | |
|* 1 | HASH JOIN RIGHT ANTI | | 4097 | 396K| 81447 (1)| 00:16:18 | | |
| 2 | VIEW | VW_SQ_1 | 3706 | 48178 | 36104 (1)| 00:07:14 | | |
|* 3 | TABLE ACCESS BY LOCAL INDEX ROWID | UCS_SUBS_GSM | 1 | 30 | 10 (0)| 00:00:01 | | |
| 4 | NESTED LOOPS | | 3706 | 152K| 36104 (1)| 00:07:14 | | |
| 5 | PARTITION RANGE ALL | | 3537 | 42444 | 724 (0)| 00:00:09 | 1 | 9 |
| 6 | TABLE ACCESS BY LOCAL INDEX ROWID| UCS_SUBS_SCHEME | 3537 | 42444 | 724 (0)| 00:00:09 | 1 | 9 |
|* 7 | INDEX RANGE SCAN | UCS_SUBS_SCHEME_IDX1 | 3537 | | 15 (0)| 00:00:01 | 1 | 9 |
| 8 | PARTITION RANGE ALL | | 2 | | 9 (0)| 00:00:01 | 1 | 9 |
|* 9 | INDEX RANGE SCAN | UCS_SUBS_GSM_IDX1 | 2 | | 9 (0)| 00:00:01 | 1 | 9 |
|* 10 | TABLE ACCESS BY LOCAL INDEX ROWID | UCS_SUBS_GSM | 1 | 48 | 10 (0)| 00:00:01 | 1 | 1 |
| 11 | NESTED LOOPS | | 4098 | 344K| 45343 (1)| 00:09:05 | | |
|* 12 | TABLE ACCESS FULL | UCS_BANK_CONTRACT | 4508 | 167K| 249 (1)| 00:00:03 | | |
| 13 | PARTITION RANGE ALL | | 2 | | 9 (0)| 00:00:01 | 1 | 9 |
|* 14 | INDEX RANGE SCAN | UCS_SUBS_GSM_IDX2 | 2 | | 9 (0)| 00:00:01 | 1 | 9 |
-------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ACCOUNT_ID"="A"."ACCOUNT_ID")
3 - filter("INACTIVE_DATE">SYSDATE@! AND "INACTIVE_DATE">"ACTIVE_DATE")
7 - access("SCHEME_ID"=113692)
9 - access("SUBSCRIPTION_ID"="SUBSCRIPTION_ID")
10 - filter("A"."ACTIVE_DATE"
"A"."SERVICE_NUM"="B"."SERIAL_NUMBER")
12 - filter("B"."REMARK" IS NULL AND "B"."EXP_DT">SYSDATE@! AND "B"."AGR_DT">=TO_DATE(' 2012-12-10 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND "B"."AGR_DT"<=TO_DATE(' 2014-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
14 - access("A"."ACCOUNT_ID"="B"."ACCOUNT_ID")
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
2060416 consistent gets
0 physical reads
0 redo size
64171 bytes sent via SQL*Net to client
1559 bytes received via SQL*Net from client
190 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2831 rows processed
这语句还有优化的空间吗?
能给点思路吗,sql调优现在毫无头绪哎
一般调优的步骤是什么样的
改什么可以优化什么
求大神指点迷津