[20221128]dg資料庫最佳化問題.txt
[20221128]dg資料庫最佳化問題.txt
--//前幾天我才知道,開發設定的一些查詢在dg上執行,據說這些查詢很慢.我想看看這些是什麼語句.在主庫自然無法查詢.
--//不過實際上這些sql語句在備庫的共享池記憶體裡還是存在,可以使用tpt 的ashtop查詢獲得,透過測試說明問題.
--//順便提一下我覺得備庫查詢應該是一些報表之類的語句,而不是要求實時性很高的sql語句,開發或者同事不應該輕率地把這些語句移
--//到備庫執行,不能僅僅因為慢而轉移到備庫,而是要定位問題在那裡,目前備庫的磁碟IO效能很差.
1.環境:
SYS@192.168.100.237:1521/orcldg> @ pr
==============================
PORT_STRING : x86_64/Linux 2.4.xx
VERSION : 19.0.0.0.0
BANNER : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
BANNER_FULL : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
BANNER_LEGACY : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
CON_ID : 0
PL/SQL procedure successfully completed.
2.分析:
SYS@192.168.100.237:1521/orcldg> @ ashtop sql_id 1=1 &100day
Total Distinct Distinct
Seconds AAS %This SQL_ID FIRST_SEEN LAST_SEEN Execs Seen Tstamps
--------- ------- ------- ------------- ------------------- ------------------- ---------- --------
22078 .0 28% | 2022-11-27 11:35:46 2022-11-28 09:47:11 1 10864
15154 .0 19% | 623b841u978k2 2022-11-27 11:35:53 2022-11-28 09:47:05 9677 12895
13389 .0 17% | cscm97g90y7rx 2022-11-27 17:30:08 2022-11-28 08:36:04 19 2271
11619 .0 15% | a91d0rd8qvu21 2022-11-27 11:35:43 2022-11-28 09:46:54 216 6974
10233 .0 13% | bu48z014njcg4 2022-11-27 11:36:06 2022-11-28 09:46:09 9268 4554
1468 .0 2% | 67b206yz6h2p5 2022-11-28 08:44:44 2022-11-28 09:03:27 4 1124
1341 .0 2% | 91zy2kh7pzs0j 2022-11-28 09:23:56 2022-11-28 09:36:12 2 737
...
--//sql_id= cscm97g90y7rx ,67b206yz6h2p5,91zy2kh7pzs0j 執行次數很少,耗時很多.
--//僅僅分析其中一條cscm97g90y7rx.
3.繼續:
SYS@192.168.100.237:1521/orcldg> @ tpt/sqlid cscm97g90y7rx %
CH# PLAN_HASH PARSES H_PARSES EXECUTIONS FETCHES ROWS_PROCESSED ROWS_PER_FETCH CPU_SEC_EXEC ELA_SEC_EXEC LIOS_PER_EXEC PIOS_PER_EXEC TOTAL_CPU_SEC TOTAL_ELA_SEC TOTAL_IOWAIT_SEC TOTAL_LIOS TOTAL_PIOS SORTS USERS_EXECUTING LAST_ACTIVE_TIME PARENT_HANDLE OBJECT_HANDLE
----- ---------- ---------- ---------- ---------- ---------- -------------- -------------- ------------ ------------ ------------- ------------- ------------- ------------- ---------------- ---------- ---------- ---------- --------------- ------------------- ---------------- ----------------
0 1711169267 18 5 15 19 110 5.78947368 75.482 886.633 16648216 237484 1132.235 13299.49 12237.5192 249723237 3562262 0 0 2022-11-28 08:32:05 000000008191E680 000000008191CF28
1 1711169267 1 1 1 1 0 0 1.433 1.440 701770 0 1.433 1.44 0 701770 0 0 0 2022-11-28 08:21:22 000000008191E680 000000007EE88B90
2 884840938 0 1 1 1 0 0 .029 .060 140 0 .029 .06 0 140 0 2 0 2022-11-28 08:21:22 000000008191E680 0000000070288CF8
3 884840938 1 1 2 2 0 0 .013 .013 136 0 .026 .027 0 272 0 4 0 2022-11-28 08:31:05 000000008191E680 000000007E3A2D50
4 884840938 66 1 67 71 178 2.50704225 .001 .004 102 0 .075 .277 .193281 6818 14 134 0 2022-11-28 09:39:15 000000008191E680 0000000085A93518
--//實際上僅僅child_number=0,1的時間有點長.邏輯IO有點大.
SYS@192.168.100.237:1521/orcldg> @ dpcx cscm97g90y7rx '' 0
-------------------------------------
SQL_ID cscm97g90y7rx, child number 0
-------------------------------------
...
--//sql語句太長,省略....
..
Plan hash value: 1711169267
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
---------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 21 (100)| |
| 1 | UNION-ALL | | | | | |
|* 2 | FILTER | | | | | |
| 3 | NESTED LOOPS | | 1 | 103 | 10 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 10 | 103 | 10 (0)| 00:00:01 |
|* 5 | TABLE ACCESS BY INDEX ROWID BATCHED| LIS_TEST | 1 | 73 | 5 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | I_LIS_TEST_AUDIT_TIME | 2 | | 3 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | PK_LIS_RESULT | 10 | | 3 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID | LIS_RESULT | 10 | 300 | 5 (0)| 00:00:01 |
|* 9 | FILTER | | | | | |
| 10 | NESTED LOOPS OUTER | | 1 | 163 | 11 (0)| 00:00:01 |
| 11 | NESTED LOOPS | | 1 | 117 | 7 (0)| 00:00:01 |
|* 12 | TABLE ACCESS BY INDEX ROWID BATCHED| LIS_TEST | 1 | 76 | 5 (0)| 00:00:01 |
|* 13 | INDEX RANGE SCAN | I_LIS_TEST_AUDIT_TIME | 2 | | 3 (0)| 00:00:01 |
|* 14 | TABLE ACCESS BY INDEX ROWID BATCHED| MICRO_RESULT_GERM | 1 | 41 | 2 (0)| 00:00:01 |
|* 15 | INDEX RANGE SCAN | IX_MICRO_RESULT_GERM_TEST_ID | 1 | | 1 (0)| 00:00:01 |
|* 16 | TABLE ACCESS BY INDEX ROWID BATCHED | MICRO_RESULT_DRUG_SENS | 20 | 920 | 4 (0)| 00:00:01 |
|* 17 | INDEX RANGE SCAN | IX_MICRO_RESULT_DRU_1650258890 | 21 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - :1 (CHAR(30), CSID=852): '60518644'
2 - :2 (CHAR(30), CSID=852): '431122198608110031'
3 - (CHAR(30), CSID=852): '2022/11/13'
4 - (CHAR(30), CSID=852): '2022-11-29'
5 - :1 (CHAR(30), CSID=852, Primary=1)
6 - :2 (CHAR(30), CSID=852, Primary=2)
7 - (CHAR(30), CSID=852, Primary=3)
8 - (CHAR(30), CSID=852, Primary=4)
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_DATE(:STR_TO,'yyyy-mm-dd')>=TO_DATE(:STR_DTFROM,'yyyy-mm-dd'))
5 - filter(("T"."REPORT_TYPE"=1 AND ("T"."IDENTITY_ID"=SYS_OP_C2C(:STR_SFZH) OR
"T"."PAT_ID"=SYS_OP_C2C(:STR_MZHM))))
6 - access("T"."AUDIT_TIME">=TO_DATE(:STR_DTFROM,'yyyy-mm-dd') AND
"T"."AUDIT_TIME"<=TO_DATE(:STR_TO,'yyyy-mm-dd'))
7 - access("T"."ID"="R"."ID")
9 - filter(TO_DATE(:STR_TO,'yyyy-mm-dd')>=TO_DATE(:STR_DTFROM,'yyyy-mm-dd'))
12 - filter(("T"."REPORT_TYPE"=1 AND ("T"."IDENTITY_ID"=SYS_OP_C2C(:STR_SFZH) OR
"T"."PAT_ID"=SYS_OP_C2C(:STR_MZHM)) AND "T"."STATE">=90))
13 - access("T"."AUDIT_TIME">=TO_DATE(:STR_DTFROM,'yyyy-mm-dd') AND
"T"."AUDIT_TIME"<=TO_DATE(:STR_TO,'yyyy-mm-dd'))
14 - filter("G"."IS_DELETED"=0)
15 - access("T"."ID"="G"."TEST_ID")
16 - filter("S"."IS_DELETED"=0)
17 - access("G"."ID"="S"."GERM_RESULT_ID")
Note
-----
- this is an adaptive plan
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
--//注意看id=5,6,12,13就很容易定位了,實際上用錯了索引導致的問題,T.IDENTITY_ID,T.PAT_ID索引都存在,出現SYS_OP_C2C函式是因
--//為表欄位定義是nvarchar2型別,而帶入的引數是char型別導致的問題,發生在繫結變數那邊,不存在隱式轉換問題.而執行計劃選擇
--//I_LIS_TEST_AUDIT_TIME日期類索引,查詢範圍2022/11/13,2022-11-29(操作員奇葩,竟然使用兩種格式)有點大,導致大量的邏輯讀.
--//實際上oracle 的acs 糾正這個執行計劃後面的child_number =2,3,4執行都很快.只要使用sql_profile文件該執行計劃就ok了.
4.再繼續:
--//67b206yz6h2p5 ,91zy2kh7pzs0j:
SYS@192.168.100.237:1521/orcldg> @ bind_cap 67b206yz6h2p5 ''
C200
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
select TO_CHAR(a.test_date,'YYYY-MM') 日期,OFFICE_NAME 檢驗組,item.id 專案ID,item.combine_name 專案名稱, count(distinct a.barcode) 專案數量,count(b.ITEM_ID) 結果數量,item.fee 單價,count(distinct a.ba
rcode)*item.fee 應收總金額 from lis_test a, lis_result b,com_order_item item where a.id = b.id and b.order_item_id=item.id(+) and test_date between :StartDate and :EndDate and a.inst_id in (:Instid
s1) and a.office_id in (:Officeids1) group by TO_CHAR(a.test_date,'YYYY-MM'),OFFICE_NAME,item.id,item.combine_name,item.fee
SQL_ID CHILD_NUMBER WAS NAME POSITION MAX_LENGTH LAST_CAPTURED DATATYPE_STRING VALUE_STRING C30
------------- ------------ --- ----------- -------- ---------- ------------------- --------------- ------------ ------------------------------
67b206yz6h2p5 0 YES :STARTDATE 1 11 2022-11-28 09:00:44 TIMESTAMP 2022-10-01 00:00:00.000000000
YES :ENDDATE 2 11 2022-11-28 09:00:44 TIMESTAMP 2022-10-30 00:00:00.000000000
YES :INSTIDS1 3 22 2022-11-28 09:00:44 NUMBER 2890
YES :OFFICEIDS1 4 22 2022-11-28 09:00:44 NUMBER 241
SYS@192.168.100.237:1521/orcldg> @ bind_cap 91zy2kh7pzs0j ''
C200
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
select TO_CHAR(a.test_date,'YYYY') 日期,inst_name 檢驗儀器,item.id 專案ID,item.combine_name 專案名稱, count(distinct a.barcode) 專案數量,count(b.ITEM_ID) 結果數量,item.fee 單價,count(distinct a.barco
de)*item.fee 應收總金額 from lis_test a, lis_result b,com_order_item item where a.id = b.id and b.order_item_id=item.id(+) and test_date between :StartDate and :EndDate and a.inst_id in (:Instids1,
:Instids2) and a.office_id in (:Officeids1) group by TO_CHAR(a.test_date,'YYYY'),inst_name,item.id,item.combine_name,item.fee
SQL_ID CHILD_NUMBER WAS NAME POSITION MAX_LENGTH LAST_CAPTURED DATATYPE_STRING VALUE_STRING C30
------------- ------------ --- ------------ -------- ---------- ------------------- --------------- ------------ ------------------------------
91zy2kh7pzs0j 0 YES :STARTDATE 1 11 2022-11-28 09:23:56 TIMESTAMP 2022-01-01 00:00:00.000000000
YES :ENDDATE 2 11 2022-11-28 09:23:56 TIMESTAMP 2022-10-31 00:00:00.000000000
YES :INSTIDS1 3 22 2022-11-28 09:23:56 NUMBER 1816
YES :INSTIDS2 4 22 2022-11-28 09:23:56 NUMBER 1553
YES :OFFICEIDS1 5 22 2022-11-28 09:23:56 NUMBER 241
--//這兩個查詢類似,不過日期範圍很大,慢很正常.這樣的查詢才比較適合在備庫完成.
5.看看623b841u978k2,a91d0rd8qvu21,bu48z014njcg4:
--//這些與cscm97g90y7rx類似,
SYS@192.168.100.237:1521/orcldg> @ bind_cap 623b841u978k2 ''
SQL_ID CHILD_NUMBER WAS NAME POSITION MAX_LENGTH LAST_CAPTURED DATATYPE_STRING VALUE_STRING
------------- ------------ --- --------------------- ---------- ------------------- --------------- ------------------
623b841u978k2 5 YES :STR_MZHM 1 32 2022-11-28 10:07:19 CHAR(32) 02202927
YES :STR_SFZH 2 128 2022-11-28 10:07:19 CHAR(128) 45262419830628281X
YES :STR_DTFROM 3 32 2022-11-28 10:07:19 CHAR(32) 2022-11-28
YES :STR_DTTO 4 32 2022-11-28 10:07:19 CHAR(32) 2022-11-29
7 YES :STR_MZHM 1 32 2022-11-28 09:57:29 CHAR(32) 90970466
YES :STR_SFZH 2 128 2022-11-28 09:57:29 CHAR(128) 45010319580511201X
YES :STR_DTFROM 3 32 2022-11-28 09:57:29 CHAR(32) 2000-01-01
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
YES :STR_DTTO 4 32 2022-11-28 09:57:29 CHAR(32) 2022-11-29
10 YES :STR_MZHM 1 32 2022-11-28 10:05:59 CHAR(32) 91209141
YES :STR_SFZH 2 128 2022-11-28 10:05:59 CHAR(128) 452730194812250516
YES :STR_DTFROM 3 32 2022-11-28 10:05:59 CHAR(32) 2022-08-30
YES :STR_DTTO 4 32 2022-11-28 10:05:59 CHAR(32) 2022-11-29
12 rows selected.
SYS@192.168.100.237:1521/orcldg> @ bind_cap a91d0rd8qvu21 ''
SQL_ID CHILD_NUMBER WAS NAME POSITION MAX_LENGTH LAST_CAPTURED DATATYPE_STRING VALUE_STRING C30
------------- ------------ --- ----------- -------- ---------- ------------------- --------------- ------------ ------------------------------
a91d0rd8qvu21 1 YES :STATE 1 22 2022-11-28 10:04:32 NUMBER 50
YES :TENANT_ID 2 22 2022-11-28 10:04:32 NUMBER 1
YES :STARTDATE 3 11 2022-11-28 10:04:32 TIMESTAMP 2022-11-25 00:00:00.000000000
YES :ENDDATE 4 11 2022-11-28 10:04:32 TIMESTAMP 2022-11-29 00:00:00.000000000
YES :PAT_ID 5 32 2022-11-28 10:04:32 VARCHAR2(32) 91225574
SYS@192.168.100.237:1521/orcldg> @ bind_cap bu48z014njcg4 ''
SQL_ID CHILD_NUMBER WAS NAME POSITION MAX_LENGTH LAST_CAPTURED DATATYPE_STRING VALUE_STRING
------------- ------------ --- ------------ -------- ---------- ------------------- --------------- -----------------
bu48z014njcg4 0 YES :STR_MZHM 1 32 2022-11-28 10:04:39 CHAR(32) 90544724
YES :STR_SFZH 2 128 2022-11-28 10:04:39 CHAR(128) 452425194007051226
YES :STR_DTFROM 3 32 2022-11-28 10:04:39 CHAR(32) 2022-11-26
YES :STR_DTTO 4 32 2022-11-28 10:04:39 CHAR(32) 2022-11-29
--//也都是選擇了錯誤的索引,執行計劃不再貼出.這樣的情況就是對應表越來越大,要存在10%的變化才會啟用分析表.導致oracle認為該
--//日期範圍的記錄很少,導致選擇日期索引作為access條件.
--//還可以做一個猜測,開始可能操作員查詢的範圍很小,也就是當天的日期,這樣由於表"一段"時間內沒有分析過,導致oracle最佳化器認為
--//這段日期範圍內記錄很小,優先選擇日期索引作為access條件,而沒有選擇門診號碼,身份證號,pad_id的索引作為access條件.還有就
--//是一些操作員偷懶.輸入一個2000-01-01作為開始查詢日子(看下劃線),這樣再走日期索引,邏輯讀就很大.慢就很自然了.
--//出現這樣的情況,快速解決的方法就是分析表.也使用sql profile穩定執行計劃就ok了.不過我執行sql profile遇到一些問題另外寫
--//blog說明問題.
--//這類問題我感覺oracle應該找到更好的解決方法,不能透過acs來糾正這個錯誤,有時候根本不可行.這樣確實可能導致生產系統效能出
--//現抖動.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2926307/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20221130]最佳化備庫dg遇到的問題2.txt
- 【DG】備庫斷檔問題
- 資料庫伺服器的NUMA最佳化問題資料庫伺服器
- Oracle效能最佳化:資料庫配置和IO問題Oracle資料庫
- ORACLE資料庫Dataguard dg brokerOracle資料庫
- [20180718]拷貝資料檔案從dg庫.txt
- 資料庫同步問題資料庫
- 最佳化資料庫前需考慮的十個問題（轉）資料庫
- Oracle DG管理資料庫屬性Oracle資料庫
- [20181128]toad連線資料庫的問題.txt資料庫
- [20121108]關於克隆資料庫的問題.txt資料庫
- 大型網站資料庫及資料訪問最佳化(轉)網站資料庫
- 資料庫常見問題資料庫
- 資料庫被鎖問題資料庫
- jbuilder 與資料庫問題UI資料庫
- 資料庫中文問題，急資料庫
- jbuilder資料庫中文問題UI資料庫
- 配置資料庫問題？求救資料庫
- 資料庫關聯問題資料庫
- 資料庫同步更新問題？資料庫
- 資料庫問題求證資料庫
- sybase iq 資料庫 問題資料庫
- 資料庫併發問題資料庫
- Domino 資料庫超大問題資料庫
- 資料庫欄位問題資料庫
- 資料庫啟動問題資料庫
- jboss訪問資料庫的問題資料庫
- 安裝資料庫和資料庫解決問題資料庫
- [20200102]資料庫安裝問題.txt資料庫
- dg_閃回資料庫實驗資料庫
- Oracle DG資料庫狀態轉換Oracle資料庫
- 遷移資料庫資料考慮問題資料庫
- 求助：資料庫併發訪問問題資料庫
- openGauss資料庫分析問題資料庫
- 資料庫事物相關問題資料庫
- 資料庫索引分裂 問題分析資料庫索引
- 多資料庫設計問題資料庫
- SessionBean呼叫資料庫的問題SessionBean資料庫