程序師世界是廣大編程愛好者互助、分享、學習的平台，程序師世界有你更精彩！
設為首頁 加入收藏
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> Oracle的SQL優化一

Oracle的SQL優化一

編輯：Oracle教程

Oracle的SQL優化一


兩個SCHEMA，開發反饋數據量相差無幾，但一模一樣的SQL，A Schema要跑6分鐘，B Schema只5秒就出來了。SQL語句如下：
SELECT *
FROM (SELECT A.*, ROWNUM RN
FROM (select t3.check_show,
t1.*,
t2.storesid,
to_char(rdate, 'yyyy-mm-dd') as to_rdate,
to_char(submitdate, 'yyyy-mm-dd') as to_submitdate,
to_char(expected_shipdate, 'yyyy-mm-dd') as to_expected_shipdate,
to_char(expected_shipdate, 'yyyy-mm-dd') as to_expected_shipdate2,
to_char(billingdate, 'yyyy-mm-dd hh24:mi:ss') as to_billingdate,
to_char(dealtotal, 'fm9999999990.00') as to_dealtotal,
(nvl(cashreceiptssum, 0) + nvl(received, 0)) as to_cashreceiptssum,
to_char((nvl(debtsum, 0) - nvl(received, 0)),
'fm9999999990.00') to_debtsum,
nvl(amounttotal, 0) as to_amounttotal,
(select a.reduce_points
from SchemaB.m_mempoint_logs a
where a.billno = t1.billno
and a.billtype = 4) as reduce_points
from SchemaB.v0bill t1,
SchemaB.tbl_stores t2,
SchemaB.TBL_BILL_CHECKSTATE_SHOW t3
where ((posbillno is not null and BCOMPLETE = 1) or
posbillno is null)
and t1.StoreRoomID = t2.storesid
and t1.billsubcase = t3.billsubcase
and t1.check_status = t3.check_status
and (instoreroomid in
(select storesid
from SchemaB.tbl_user_stores
where employeeid = 4352) or
outstoreroomid in
(select storesid
from SchemaB.tbl_user_stores
where employeeid = 4352))
and servicStatus = 1
and rdate >= to_date('2015-06-04', 'yyyy-mm-dd')
and rdate <=
to_date('2015-06-11 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
and (t1.billsubcase = '75' or t1.billsubcase = '711' or
(instoreroomid in
(select storesid
from SchemaB.tbl_stores
where areacode like '001%') or
outstoreroomid in
(select storesid
from SchemaB.tbl_stores
where areacode like '001%')))
ORDER by rdate desc, billingdate desc, billno desc) A
WHERE ROWNUM <= (1 * 20))
WHERE RN > ((1 - 1) * 20);

B Schema的執行計劃：
-------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 152K| | 16718 (1)| 00:03:21 | | |
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID | TBL_BILL | 1 | 25 | | 3 (0)| 00:00:01 | ROWID | ROWID |
|* 2 | INDEX UNIQUE SCAN | XPK_BILL_P | 1 | | | 2 (0)| 00:00:01 | | |
|* 3 | TABLE ACCESS BY INDEX ROWID | M_MEMPOINT_LOGS | 1 | 24 | | 4 (0)| 00:00:01 | | |
|* 4 | INDEX RANGE SCAN | IDX_MEMPOINT_LOGS_BILLNO | 1 | | | 3 (0)| 00:00:01 | | |
|* 5 | VIEW | | 20 | 152K| | 16718 (1)| 00:03:21 | | |
|* 6 | COUNT STOPKEY | | | | | | | | |
| 7 | VIEW | | 917 | 6989K| | 16718 (1)| 00:03:21 | | |
|* 8 | SORT ORDER BY STOPKEY | | 917 | 522K| 57M| 16718 (1)| 00:03:21 | | |
|* 9 | FILTER | | | | | | | | |
|* 10 | HASH JOIN | | 96250 | 53M| | 4844 (1)| 00:00:59 | | |
| 11 | TABLE ACCESS FULL | TBL_BILL_CHECKSTATE_SHOW | 200 | 9600 | | 3 (0)| 00:00:01 | | |
|* 12 | HASH JOIN | | 57750 | 29M| | 4840 (1)| 00:00:59 | | |
| 13 | INDEX FULL SCAN | XPK_TBL_STORES | 85 | 340 | | 1 (0)| 00:00:01 | | |
|* 14 | HASH JOIN RIGHT OUTER | | 57750 | 29M| | 4838 (1)| 00:00:59 | | |
| 15 | TABLE ACCESS FULL | TBL_STORES | 85 | 2550 | | 4 (0)| 00:00:01 | | |
|* 16 | HASH JOIN RIGHT OUTER | | 57750 | 27M| | 4834 (1)| 00:00:59 | | |
| 17 | TABLE ACCESS FULL | TBL_STORES | 85 | 2550 | | 4 (0)| 00:00:01 | | |
|* 18 | HASH JOIN RIGHT OUTER | | 57750 | 25M| 4200K| 4829 (1)| 00:00:58 | | |
| 19 | TABLE ACCESS FULL | M_MEMDETAIL | 89469 | 3145K| | 380 (1)| 00:00:05 | | |
|* 20 | HASH JOIN RIGHT OUTER | | 57750 | 24M| | 3020 (1)| 00:00:37 | | |
| 21 | TABLE ACCESS FULL | TBL_BILLCASE | 40 | 840 | | 4 (0)| 00:00:01 | | |
|* 22 | HASH JOIN RIGHT OUTER | | 57750 | 22M| 3176K| 3015 (1)| 00:00:37 | | |
| 23 | TABLE ACCESS FULL | M_MEMDETAILCARD | 90213 | 2114K| | 310 (1)| 00:00:04 | | |
|* 24 | HASH JOIN RIGHT OUTER | | 57750 | 21M| | 1450 (1)| 00:00:18 | | |
| 25 | TABLE ACCESS FULL | TBL_CUSTOM | 537 | 17184 | | 6 (0)| 00:00:01 | | |
|* 26 | HASH JOIN RIGHT OUTER | | 57750 | 19M| | 1443 (1)| 00:00:18 | | |
| 27 | TABLE ACCESS FULL | TBL_BUSINESS_TYPE | 38 | 760 | | 3 (0)| 00:00:01 | | |
| 28 | PARTITION RANGE ITERATOR| | 57750 | 18M| | 1439 (1)| 00:00:18 | 181 | 180 |
|* 29 | TABLE ACCESS FULL | TBL_BILL | 57750 | 18M| | 1439 (1)| 00:00:18 | 181 | 180 |
|* 30 | TABLE ACCESS FULL | TBL_USER_STORES | 1 | 8 | | 7 (0)| 00:00:01 | | |
|* 31 | TABLE ACCESS FULL | TBL_USER_STORES | 1 | 8 | | 7 (0)| 00:00:01 | | |
|* 32 | TABLE ACCESS BY INDEX ROWID | TBL_STORES | 1 | 12 | | 1 (0)| 00:00:01 | | |
|* 33 | INDEX UNIQUE SCAN | XPK_TBL_STORES | 1 | | | 0 (0)| 00:00:01 | | |
|* 34 | TABLE ACCESS BY INDEX ROWID | TBL_STORES | 1 | 12 | | 1 (0)| 00:00:01 | | |
|* 35 | INDEX UNIQUE SCAN | XPK_TBL_STORES | 1 | | | 0 (0)| 00:00:01 | | |
-------------------------------------------------------------------------------------------------------------------------------------------

在B schema執行該SQL耗費1萬多次邏輯讀。

A Schema的執行計劃：
----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 152K| | 50624 (1)| 00:10:08 | | |
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID | TBL_BILL | 1 | 31 | | 3 (0)| 00:00:01 | ROWID | ROWID |
|* 2 | INDEX UNIQUE SCAN | XPK_BILL_P | 1 | | | 2 (0)| 00:00:01 | | |
|* 3 | TABLE ACCESS BY INDEX ROWID | M_MEMPOINT_LOGS | 1 | 47 | | 1 (0)| 00:00:01 | | |
|* 4 | INDEX RANGE SCAN | IDX_MEMPOINT_LOGS_BILLNO | 1 | | | 1 (0)| 00:00:01 | | |
|* 5 | VIEW | | 20 | 152K| | 50624 (1)| 00:10:08 | | |
|* 6 | COUNT STOPKEY | | | | | | | | |
| 7 | VIEW | | 3600 | 26M| | 50624 (1)| 00:10:08 | | |
|* 8 | SORT ORDER BY STOPKEY | | 3600 | 2000K| 210M| 50624 (1)| 00:10:08 | | |
|* 9 | FILTER | | | | | | | | |
|* 10 | HASH JOIN | | 378K| 205M| | 5203 (1)| 00:01:03 | | |
| 11 | TABLE ACCESS FULL | TBL_BILL_CHECKSTATE_SHOW | 200 | 9600 | | 3 (0)| 00:00:01 | | |
|* 12 | HASH JOIN | | 151K| 75M| | 5199 (1)| 00:01:03 | | |
| 13 | INDEX FAST FULL SCAN | XPK_TBL_STORES | 616 | 3080 | | 2 (0)| 00:00:01 | | |
|* 14 | HASH JOIN RIGHT OUTER | | 151K| 74M| | 5196 (1)| 00:01:03 | | |
| 15 | TABLE ACCESS FULL | TBL_STORES | 616 | 20944 | | 8 (0)| 00:00:01 | | |
|* 16 | HASH JOIN RIGHT OUTER | | 151K| 69M| | 5187 (1)| 00:01:03 | | |
| 17 | TABLE ACCESS FULL | TBL_STORES | 616 | 20944 | | 8 (0)| 00:00:01 | | |
|* 18 | HASH JOIN RIGHT OUTER | | 151K| 64M| | 5178 (1)| 00:01:03 | | |
| 19 | TABLE ACCESS FULL | TBL_CUSTOM | 93 | 2139 | | 4 (0)| 00:00:01 | | |
|* 20 | HASH JOIN RIGHT OUTER | | 151K| 61M| | 5173 (1)| 00:01:03 | | |
| 21 | TABLE ACCESS FULL | TBL_BILLCASE | 40 | 800 | | 4 (0)| 00:00:01 | | |
|* 22 | HASH JOIN RIGHT OUTER | | 151K| 58M| | 5168 (1)| 00:01:03 | | |
| 23 | TABLE ACCESS FULL | TBL_BUSINESS_TYPE | 33 | 660 | | 3 (0)| 00:00:01 | | |
|* 24 | HASH JOIN RIGHT OUTER | | 151K| 55M| | 5164 (1)| 00:01:02 | | |
| 25 | TABLE ACCESS FULL | M_MEMDETAIL | 5 | 120 | | 4 (0)| 00:00:01 | | |
| 26 | NESTED LOOPS OUTER | | 151K| 52M| | 5159 (1)| 00:01:02 | | |
| 27 | PARTITION RANGE ITERATOR | | 151K| 50M| | 5158 (1)| 00:01:02 | 181 | 180 |
|* 28 | TABLE ACCESS FULL | TBL_BILL | 151K| 50M| | 5158 (1)| 00:01:02 | 181 | 180 |
| 29 | TABLE ACCESS BY INDEX ROWID| M_MEMDETAILCARD | 1 | 14 | | 1 (0)| 00:00:01 | | |
|* 30 | INDEX UNIQUE SCAN | XPK_TBL_MEMDETAIL | 1 | | | 0 (0)| 00:00:01 | | |
|* 31 | TABLE ACCESS FULL | TBL_USER_STORES | 1 | 10 | | 175 (1)| 00:00:03 | | |
|* 32 | TABLE ACCESS FULL | TBL_USER_STORES | 1 | 10 | | 175 (1)| 00:00:03 | | |
|* 33 | TABLE ACCESS BY INDEX ROWID | TBL_STORES | 1 | 18 | | 2 (0)| 00:00:01 | | |
|* 34 | INDEX UNIQUE SCAN | XPK_TBL_STORES | 1 | | | 1 (0)| 00:00:01 | | |
|* 35 | TABLE ACCESS BY INDEX ROWID | TBL_STORES | 1 | 18 | | 2 (0)| 00:00:01 | | |
|* 36 | INDEX UNIQUE SCAN | XPK_TBL_STORES | 1 | | | 1 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
14 recursive calls
0 db block gets
27716859 consistent gets
1 physical reads
0 redo size
16407 bytes sent via SQL*Net to client
531 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
20 rows processed

2771萬次邏輯讀。
在該SQL涉及到的所有表中，TBL_BILL是主要的一張大表，此處對TBL_BILL的訪問都做了分區裁剪，兩個執行計劃的主要差異是A SCHEMA以TBL_BILL為驅動表進行NESTED LOOPS OUTER，
而B Schema則以TBL_BUSINESS_TYPE表作為驅動表對TBL_BILL進行HASH JOIN，懷疑是因為A走了錯誤的執行計劃導致，於是使用hint使A對TBL_BILL走HASH JOIN，發現沒有任何改善，
於是看看時間具體消耗在哪：
SELECT /*+gather_plan_statistics */ *
FROM (SELECT A.*, ROWNUM RN
FROM (select
t3.check_show,
t1.*,
t2.storesid,
to_char(rdate, 'yyyy-mm-dd') as to_rdate,
to_char(submitdate, 'yyyy-mm-dd') as to_submitdate,
to_char(expected_shipdate, 'yyyy-mm-dd') as to_expected_shipdate,
to_char(expected_shipdate, 'yyyy-mm-dd') as to_expected_shipdate2,
to_char(billingdate, 'yyyy-mm-dd hh24:mi:ss') as to_billingdate,
to_char(dealtotal, 'fm9999999990.00') as to_dealtotal,
(nvl(cashreceiptssum, 0) + nvl(received, 0)) as to_cashreceiptssum,
to_char((nvl(debtsum, 0) - nvl(received, 0)),
'fm9999999990.00') to_debtsum,
nvl(amounttotal, 0) as to_amounttotal,
t4.reduce_points
from SchemaA.v0bill t1,
SchemaA.tbl_stores t2
,SchemaA.TBL_BILL_CHECKSTATE_SHOW t3,
SchemaA.m_mempoint_logs t4
where ((posbillno is not null and BCOMPLETE = 1) or
posbillno is null)
and t1.StoreRoomID = t2.storesid
and t1.billsubcase = t3.billsubcase
and t1.check_status = t3.check_status
and (instoreroomid in
(select storesid
from SchemaA.tbl_user_stores
where employeeid = 3945) or
outstoreroomid in
(select storesid
from SchemaA.tbl_user_stores
where employeeid = 3945))
and servicStatus = 1
and rdate >= to_date('2015-06-04', 'yyyy-mm-dd')
and rdate <=
to_date('2015-06-11 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
and (t1.billsubcase in ('75' ,'711') or
(instoreroomid in
(select storesid
from SchemaA.tbl_stores
where areacode like '001022%') or
outstoreroomid in
(select storesid
from SchemaA.tbl_stores
where areacode like '001022%')))
and t1.billno=t4.billno(+)
and T4.billtype (+)= 4
ORDER by rdate desc, billingdate desc, t1.billno desc) A
WHERE ROWNUM <= (1 * 20))
WHERE RN > ((1 - 1)* 20);

發現主要時間都消耗在對TBL_USER_STORES上（對該表會有2次filter操作（全表掃描），占總耗時的99%），如下圖：

\

\

再看看2家企業該表（TBL_USER_STORES）的數據量不是一個量級，如下：

  1. 上一頁:
  2. 下一頁:
Oracle教程
CentOS6.2上安裝Oracle10g報ins_emdb.mk錯誤處理方法，centos6.2oracle10g

CentOS6.2上安裝Oracle10g報ins_emdb
設置plsqldev默認格式化（關鍵字大寫，其它小寫），plsqldev關鍵字

設置plsqldev默認格式化（關鍵字大寫，其它小寫），pl
Oracle數據庫體系結構總結

Oracle數據庫體系結構總結   Orac
Oracle 存儲過程加密之wrap工具

Oracle 存儲過程加密之wrap工具 《Oracle存儲
plsql連接oracle時，不同ip上數據庫名字相同連接的辦法

plsql連接oracle時，不同ip上數據庫名字相同連接的
Oracle升級_oracle10g版本由10.2.0.1.0升級為10.2.0.4.0（即CPU升級）

Oracle升級_oracle10g版本由10.2.0.1.
相關文章
閱讀排行榜
在Oracle中導入dmp文件的方法，oracle導入dmp文件 Atitit.數據庫分區的設計&#160;attilax&#160;&#160;總結，atititattilax oracle 中 sqlplus命令大全，sqlplus命令大全 [terry筆記]11gR2_DataGuard搭建_拷貝數據文件 oracle跨庫查詢dblink的用法實例詳解，oracledblink [獨孤九劍]Oracle知識點梳理（一）表空間、用戶，oracle知識點 探索Oracle之數據庫升級五PSU11.2.0.4.3rollback11.2.0.4.0 oracleRACclient-sideTAF&amp;amp;&amp;amp;server-sideTAF oracle中dbms_sql的使用 oraclejob RMAN前奏
熱門圖文
Access 2000教程：13.9 補充二：為什麼不能建立頁 DB2連接服務器出錯的解決方法(1) Oracle創建自增字段方法-ORACLESEQUENCE的簡單介紹 如何讀取從某個位置到某個位置的不連續的數據 MySql性能調優（三）之表的設計 SQL Server Compact的DLL文件介紹 啟用oracle11g自動收集統計信息 ASP+Access莫名奇妙的sql語句錯誤解決
欄目導航
關於Oracle數據庫Oracle數據庫基礎Oracle教程
Copyright © 程式師世界 All Rights Reserved