一條簡單的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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20181114]一條sql語句的優化.txtSQL優化
- SQL語句優化SQL優化
- SQL優化案例-單表分頁語句的優化（八）SQL優化
- 對一條基於分割槽的簡單SQL的優化SQL優化
- Mysql 52條SQL語句效能優化策略彙總MySql優化
- MYSQL SQL語句優化MySql優化
- sql語句效能優化SQL優化
- 簡單的SQL語句學習SQL
- MySql和簡單的sql語句MySql
- 優化 SQL 語句的步驟優化SQL
- MySQL之SQL語句優化MySql優化
- [20201210]sql語句優化.txtSQL優化
- [20200320]SQL語句優化的困惑.txtSQL優化
- DBeaver如何快速格式化sql語句，真簡單！SQL
- 一條sql語句的執行過程SQL
- 一條簡單的更新語句，MySQL是如何加鎖的？MySql
- SQL Server-簡單查詢語句SQLServer
- 第45期：一條 SQL 語句最佳化的基本思路SQL
- 一條update SQL語句是如何執行的SQL
- 一條SQL更新語句是如何執行的SQL
- 一條SQL更新語句是如何執行的？SQL
- 優化 JS 條件語句的 5 個技巧優化JS
- Sql語句本身的優化-定位慢查詢SQL優化
- [20200324]SQL語句優化的困惑2.txtSQL優化
- SQL語句優化的原則與方法QOSQL優化
- 一條更新的SQL語句是如何執行的？SQL
- PL/SQL 條件控制語句SQL
- 一條SQL語句在MySQL中如何執行的MySql
- 一條 SQL 查詢語句是如何執行的？SQL
- 織夢CMS最簡單實用的SQL語句SQL
- Oracle優化案例-單表分頁語句的優化（八）Oracle優化
- 《MySQL慢查詢優化》之SQL語句及索引優化MySql優化索引
- Java中如何解析SQL語句、格式化SQL語句、生成SQL語句？JavaSQL
- MySQL 52個SQL效能優化策略SQL語句彙總MySql優化
- mybatis 得sql語句對應簡單型別MyBatisSQL型別
- 一條sql語句在mysql中是如何執行的MySql
- 一條 SQL 語句在 MySQL 中是如何執行的？MySql
- 資料庫介紹--認識簡單的SQL語句資料庫SQL
- 最簡單的sql語句（增刪改查統計）SQL