一條簡單的SQL語句優化-新年新氣象
資料庫CPU被耗光了，前臺操作有點偏慢，檢視了一下會話情況，資料庫壓力基本都在一條SQL上。
SQL> select * from v$version ;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
CORE 9.2.0.8.0 Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production
SQL> EXPLAIN PLAN FOR
2 SELECT count(*) FROM T_SR_SERVICEREQUEST T
3 WHERE :"SYS_B_0"= :"SYS_B_1"
4 AND T.SUBMITTIME >= SYSDATE - :1/:"SYS_B_2"
5 AND T.SRTYPEID = :2
6 AND T.SUBSNUMBER = :3
7 and (t.SERVICEID like to_char(sysdate,:"SYS_B_3")||:"SYS_B_4"
8 or t.SERVICEID like to_char(sysdate-:"SYS_B_5",:"SYS_B_6")||:"SYS_B_7") ;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 100 | 13 | | |
| 1 | SORT AGGREGATE | | 1 | 100 | | | |
| 2 | CONCATENATION | | | | | | |
|* 3 | FILTER | | | | | | |
| 4 | PARTITION RANGE ITERATOR | | | | | KEY | KEY |
|* 5 | TABLE ACCESS BY LOCAL INDEX ROWID| T_SR_SERVICEREQUEST | 1 | 100 | 3 | KEY | KEY |
|* 6 | INDEX RANGE SCAN | PK_SR_SERVICEREQUEST | 3214 | | 2 | KEY | KEY |
|* 7 | FILTER | | | | | | |
| 8 | PARTITION RANGE ITERATOR | | | | | KEY | KEY |
|* 9 | TABLE ACCESS BY LOCAL INDEX ROWID| T_SR_SERVICEREQUEST | 1 | 100 | 3 | KEY | KEY |
|* 10 | INDEX RANGE SCAN | PK_SR_SERVICEREQUEST | 3214 | | 2 | KEY | KEY |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(:Z=:Z)
5 - filter("T"."SRTYPEID"=:Z AND "T"."SUBSNUMBER"=:Z AND
"T"."SUBMITTIME">=SYSDATE@!-TO_NUMBER(:Z)/TO_NUMBER(:Z))
6 - access("T"."SERVICEID" LIKE TO_CHAR(SYSDATE@!-TO_NUMBER(:Z),:Z)||:Z)
filter("T"."SERVICEID" LIKE TO_CHAR(SYSDATE@!-TO_NUMBER(:Z),:Z)||:Z)
7 - filter(:Z=:Z)
9 - filter("T"."SUBSNUMBER"=:Z AND "T"."SRTYPEID"=:Z AND
"T"."SUBMITTIME">=SYSDATE@!-TO_NUMBER(:Z)/TO_NUMBER(:Z))
10 - access("T"."SERVICEID" LIKE TO_CHAR(SYSDATE@!,:Z)||:Z)
filter("T"."SERVICEID" LIKE TO_CHAR(SYSDATE@!,:Z)||:Z AND LNNVL("T"."SERVICEID" LIKE
TO_CHAR(SYSDATE@!-TO_NUMBER(:Z),:Z)||:Z))
Note: cpu costing is off
33 rows selected.
從這個執行計劃上看，使用了PK進行INDEX RANGE SCAN，然後在FILTER，效率並不一定有多高
使用10046的trace來去得繫結變數的值，全部替換之後，語句如下：
SELECT count(*) FROM T_SR_SERVICEREQUEST T
WHERE 1= 1
AND T.SUBMITTIME >= SYSDATE - 2/24
AND T.SRTYPEID = '004002007001'
AND T.SUBSNUMBER = '020815520617299'
AND (t.SERVICEID like to_char(sysdate,'yyMMdd')||'%'
OR t.SERVICEID like to_char(sysdate-1,'yyMMdd')||'%');
現在sqlplus下直接用set autotrace on來測試語句效果
SQL> SELECT count(*) FROM T_SR_SERVICEREQUEST T
2 WHERE 1= 1
AND T.SUBMITTIME >= SYSDATE - 2/24
AND T.SRTYPEID = '004002007001'
AND T.SUBSNUMBER = '020815520617299'
AND (t.SERVICEID like to_char(sysdate,'yyMMdd')||'%'
OR t.SERVICEID like to_char(sysdate-1,'yyMMdd')||'%'); 3 4 5 6 7
COUNT(*)
----------
1
Elapsed: 00:00:01.17
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=13 Card=1 Bytes=100)
1 0 SORT (AGGREGATE)
2 1 CONCATENATION
3 2 PARTITION RANGE (ITERATOR)
4 3 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'T_SR_SERVICE
REQUEST' (Cost=3 Card=1 Bytes=100)
5 4 INDEX (RANGE SCAN) OF 'PK_SR_SERVICEREQUEST' (UNIQ
UE) (Cost=2 Card=3214)
6 2 PARTITION RANGE (ITERATOR)
7 6 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'T_SR_SERVICE
REQUEST' (Cost=3 Card=1 Bytes=100)
8 7 INDEX (RANGE SCAN) OF 'PK_SR_SERVICEREQUEST' (UNIQ
UE) (Cost=2 Card=3214)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
96775 consistent gets
0 physical reads
0 redo size
518 bytes sent via SQL*Net to client
656 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
放棄使用PK，強制一下時間索引，也許效果更理想
SQL> SELECT /*+index(t IX_SERVICEREQUEST_SUBMITTIME)*/ count(*) FROM T_SR_SERVICEREQUEST T
2 WHERE 1= 1
3 AND T.SUBMITTIME >= SYSDATE - 2/24
4 AND T.SRTYPEID = '004002007001'
5 AND T.SUBSNUMBER = '020815520617299'
6 AND (t.SERVICEID like to_char(sysdate,'yyMMdd')||'%'
7 OR t.SERVICEID like to_char(sysdate-1,'yyMMdd')||'%');
COUNT(*)
----------
1
Elapsed: 00:00:00.09
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=271 Card=1 Bytes=100
)
1 0 SORT (AGGREGATE)
2 1 PARTITION RANGE (ALL)
3 2 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'T_SR_SERVICERE
QUEST' (Cost=271 Card=3 Bytes=300)
4 3 INDEX (RANGE SCAN) OF 'IX_SERVICEREQUEST_SUBMITTIME'
(NON-UNIQUE) (Cost=263 Card=64274)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7128 consistent gets
7 physical reads
4540 redo size
518 bytes sent via SQL*Net to client
656 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
上面的語句效率提高了10幾倍，consistent gets從96775降到了7128，這可以極大的緩解CPU壓力了
但存在一個風險，PARTITION RANGE (ALL)，當前按時間的分割槽資料量還不大，當分割槽充盈又未及時清退的時候，災難也許也就來了
所以考慮更換一下語句的寫法，能使用上分割槽
這個分割槽指定在serviceid上，表設計不合理，這裡就先不管這一茬了
SQL> SELECT count(*) FROM T_SR_SERVICEREQUEST T
2 WHERE 1= 1
3 AND T.SUBMITTIME >= SYSDATE - 2/24
4 AND T.SRTYPEID = '004002007001'
5 AND T.SUBSNUMBER = '020815520617299'
6 AND t.SERVICEID between to_char(sysdate-1,'yyMMdd') and to_char(sysdate+1,'yyMMdd');
COUNT(*)
----------
1
Elapsed: 00:00:00.07
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=1 Card=1 Bytes=100)
1 0 SORT (AGGREGATE)
2 1 FILTER
3 2 PARTITION RANGE (ITERATOR)
4 3 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'T_SR_SERVICE
REQUEST' (Cost=1 Card=1 Bytes=100)
5 4 INDEX (RANGE SCAN) OF 'IX_SERVICEREQUEST_SUBMITTIM
E' (NON-UNIQUE) (Cost=263 Card=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5300 consistent gets
0 physical reads
0 redo size
518 bytes sent via SQL*Net to client
656 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/10867315/viewspace-714334/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 一條sql語句的優化SQL優化
- 一條SQL語句的優化過程SQL優化
- 一條簡單SQL語句的構成及語句解析SQL
- SQL語句優化－－十條經驗SQL優化
- 一條sql語句優化不出來, 哭了一鼻子SQL優化
- 一條update語句的優化探索優化
- 一個SQL語句的優化SQL優化
- 一條簡單的sql語句導致的系統問題SQL
- 一條簡單的sql語句執行15天的原因分析SQL
- SQL語句的優化SQL優化
- 一條sql語句的建議調優分析SQL
- 34條簡單的SQL優化準則SQL優化
- sql語句的簡化SQL
- SQL語句優化SQL優化
- 總結出10條SQL語句優化精髓SQL優化
- SQL優化案例-單表分頁語句的優化（八）SQL優化
- 對一條基於分割槽的簡單SQL的優化SQL優化
- SQL Server優化之SQL語句優化SQLServer優化
- [20181114]一條sql語句的優化.txtSQL優化
- [20131025]一條sql語句的優化.txtSQL優化
- [20151203]一條sql語句的優化.txtSQL優化
- [20150715]一條sql語句的優化.txtSQL優化
- [20120319]一條sql語句的優化.txtSQL優化
- [20130319]一條sql語句的優化.txtSQL優化
- SQL 語句的優化方法SQL優化
- 一次sql語句優化的反思SQL優化
- 一條"簡單"的sql語句和小兔子買麵包的故事SQL
- Mysql 52條SQL語句效能優化策略彙總MySql優化
- 通過_optimizer_rownum_pred_based_fkr優化一條sql語句優化SQL
- MYSQL SQL語句優化MySql優化
- sql語句效能優化SQL優化
- 求助：SQL語句優化SQL優化
- 一句簡單的SQL查詢語句的背後...SQL
- SQL語句優化的34條建議(轉-浪花七八朵 )SQL優化
- 一條SQL語句的書寫SQL
- 一條很 巧妙的 SQL 語句SQL
- 一條SQL語句的旅行之路SQL
- 簡單的SQL語句學習SQL