問題：
一個CS的軟體，資料庫是10g。用戶近期反映打開串列和表單時系統反應遲鈍，以前三到四秒，現在需要二三十秒，當前資料量最大到千萬級，很顯然不正常，目前還未找到徹底解決的辦法，煩請有經驗的高手給點建議如何優化。謝謝！
SQL陳述句：
---1、找出磁盤讀高的陳述句
Select sql_text,
disk_reads,
executions,
disk_reads / decode(executions, 0, 1, executions) reads_per_exec
from v$sqlarea
order by reads_per_exec desc;
查詢結果：
SQL_TEXT DISK_READS EXECUTIONS READS_PER_EXEC
1 SELECT ID,TemplateID FROM InstanceAddinational WHERE ParentID='{bbfc9d14-4416-4373-9570-d96085f5ed2d}' and SAFETYORCONTENT =0 295460 3 98486.6666666667
2 SELECT COUNT(*) FROM InstanceAddinational WHERE ParentID='{bbfc9d14-4416-4373-9570-d96085f5ed2d}' and SAFETYORCONTENT =0 492427 5 98485.4
3 SELECT COUNT(*) FROM InstanceAddinational WHERE ParentID='{67ff783b-d85c-4d2d-b594-8fc48beb7150}' and SAFETYORCONTENT =0 196861 2 98430.5
4 SELECT id FROM INSTANCEADDINATIONAL WHERE ParentID='{1e0cf5d0-1626-4632-bc5a-d2b69ef3eb56}' and SAFETYORCONTENT =0 order by CREATEDATETIME 196853 2 98426.5
5 SELECT COUNT(*) FROM InstanceAddinational WHERE ParentID='{0c4d447c-1f91-4ac3-a3ea-bc101cd390a7}' and SAFETYORCONTENT =0 196825 2 98412.5
6 SELECT COUNT(*) FROM InstanceAddinational WHERE ParentID='{591d26fc-fbe8-40ce-96df-8b2bc4d6934d}' and SAFETYORCONTENT =0 196811 2 98405.5
7 SELECT id FROM INSTANCEADDINATIONAL WHERE ParentID='{3304c787-f4e4-4a1a-bcb8-710c3d6fcaaf}' and SAFETYORCONTENT =0 order by CREATEDATETIME 196732 2 98366
8 SELECT COUNT(*) FROM InstanceAddinational WHERE ParentID='{8c1c2863-2a44-4c45-aaaf-9746691e1433}' and SAFETYORCONTENT =0 196700 2 98350
9 SELECT ID,TemplateID FROM InstanceAddinational WHERE ParentID='{3304c787-f4e4-4a1a-bcb8-710c3d6fcaaf}' and SAFETYORCONTENT =0 294583 3 98194.3333333333
10 select count(*) from instance i,instanceaddinational a where i.id=a.parentid and i.id='{bbfc9d14-4416-4373-9570-d96085f5ed2d}' and a.safetyorcontent=1 783625 8 97953.125
11 select count(*) from instance i,instanceaddinational a where i.id=a.parentid and i.id='{3304c787-f4e4-4a1a-bcb8-710c3d6fcaaf}' and a.safetyorcontent=1 1075375 11 97761.3636363636
12 SELECT ID,TemplateID FROM InstanceAddinational WHERE ParentID='{12737abc-9314-4d80-a403-cc7b5910418a}' and SAFETYORCONTENT =0 195451 2 97725.5
13 select count(*) from instance i,instanceaddinational a where i.id=a.parentid and i.id='{8c1c2863-2a44-4c45-aaaf-9746691e1433}' and a.safetyorcontent=1 194098 2 97049
14 select count(*) from instance i,instanceaddinational a where i.id=a.parentid and i.id='{1e0cf5d0-1626-4632-bc5a-d2b69ef3eb56}' and a.safetyorcontent=1 193739 2 96869.5
15 select count(*) from instance i,instanceaddinational a where i.id=a.parentid and i.id='{069a055f-6f1c-4384-8f55-214259e95980}' and a.safetyorcontent=1 387464 4 96866
16 SELECT id FROM INSTANCEADDINATIONAL WHERE ParentID='{8d1b8248-7406-444a-80e4-cebd78d14cb1}' and SAFETYORCONTENT =0 order by CREATEDATETIME 96837 1 96837
17 select count(*) from instance i,instanceaddinational a where i.id=a.parentid and i.id='{127d2fc7-1f8a-490b-94c2-bd99f4140188}' and a.safetyorcontent=1 193475 2 96737.5
18 select count(*) from instance i,instanceaddinational a where i.id=a.parentid and i.id='{9a2e01c3-bb83-47f8-82a1-ec983058e309}' and a.safetyorcontent=1 193153 2 96576.5
19 select count(*) from instance i,instanceaddinational a where i.id=a.parentid and i.id='{a181b123-e81b-44ed-8ba0-1be7a0ffde01}' and a.safetyorcontent=1 96506 1 96506
20 SELECT * FROM INSTANCEADDINATIONAL WHERE ParentID='{8d1b8248-7406-444a-80e4-cebd78d14cb1}' and SAFETYORCONTENT =1 order by CREATEDATETIME 96501 1 96501
21 SELECT COUNT(*) FROM InstanceAddinational WHERE ParentID='{d6782ec6-db3f-4d0e-9dfe-88baae2cf998}' and SAFETYORCONTENT =0 96492 1 96492
22 SELECT ID,TemplateID FROM InstanceAddinational WHERE ParentID='{b2505f24-f0c5-4315-9b73-739c0b71061b}' and SAFETYORCONTENT =0 96488 1 96488
23 SELECT ID,TemplateID FROM InstanceAddinational WHERE ParentID='{3b14c622-7a25-4d7f-b09a-5930059a09b3}' and SAFETYORCONTENT =0 96478 1 96478
24 SELECT ID,TemplateID FROM InstanceAddinational WHERE ParentID='{538ce67c-a00f-427f-bec5-b47905a25fad}' and SAFETYORCONTENT =0 96324 1 96324
25 select count(*) from instance i,instanceaddinational a where i.id=a.parentid and i.id='{591d26fc-fbe8-40ce-96df-8b2bc4d6934d}' and a.safetyorcontent=1 288860 3 96286.6666666667
26 SELECT COUNT(*) FROM InstanceAddinational WHERE ParentID='{b551acba-bf87-4b2f-95d7-754730101d39}' and SAFETYORCONTENT =0 96068 1 96068
27 SELECT ID,TemplateID FROM InstanceAddinational WHERE ParentID='{3848b4f9-c35d-4306-b8c4-4a9f0b46c3c6}' and SAFETYORCONTENT = 0 96064 1 96064
28 SELECT ID,TemplateID FROM InstanceAddinational WHERE ParentID='{c12dac65-765c-48f1-811d-0fb91a7e9966}' and SAFETYORCONTENT = 0 95959 1 95959
29 SELECT ID,TemplateID FROM InstanceAddinational WHERE ParentID='{1ac3f513-8015-48a5-a6c9-c5372bc07ebd}' and SAFETYORCONTENT = 0 95903 1 95903
30 select count(*) from instance i,instanceaddinational a where i.id=a.parentid and i.id='{eba21a39-0279-4454-9c14-39314957381e}' and a.safetyorcontent=1 95837 1 95837
31 SELECT ID,TemplateID FROM InstanceAddinational WHERE ParentID='{b551acba-bf87-4b2f-95d7-754730101d39}' and SAFETYORCONTENT = 0 95836 1 95836
32 SELECT ID,TemplateID FROM InstanceAddinational WHERE ParentID='{c12dac65-765c-48f1-811d-0fb91a7e9966}' and SAFETYORCONTENT = 1 order by CREATEDATETIME 95797 1 95797
33 SELECT ID,TemplateID FROM InstanceAddinational WHERE ParentID='{3848b4f9-c35d-4306-b8c4-4a9f0b46c3c6}' and SAFETYORCONTENT =0 191571 2 95785.5
34 SELECT id FROM INSTANCEADDINATIONAL WHERE ParentID='{539bcb8b-64a6-489b-9f53-71baee595aa1}' and SAFETYORCONTENT =0 order by CREATEDATETIME 191540 2 95770
35 SELECT * FROM INSTANCEADDINATIONAL WHERE ParentID='{539bcb8b-64a6-489b-9f53-71baee595aa1}' and SAFETYORCONTENT =1 order by CREATEDATETIME 95770 1 95770
。。。。。。
uj5u.com熱心網友回復：
把 TOP 10 陳述句，單獨跑一下，看看執行計劃是什么樣的。uj5u.com熱心網友回復：
從sql文本和物理讀上看，至少有兩個問題：1、未使用系結變數，不過這個在并發不高的情況下可能不會有太大問題；
2、該建的索引沒有建，每次都是表掃描，因為表越來越大，sql性能也越來越差了～
uj5u.com熱心網友回復：
大神們...繼續關注uj5u.com熱心網友回復：
mark來看看uj5u.com熱心網友回復：
逐個分析，比如:SELECT ID,TemplateID FROM InstanceAddinational WHERE ParentID='{bbfc9d14-4416-4373-9570-d96085f5ed2d}' and SAFETYORCONTENT =0
表的資料量是多少，查看其執行計劃，是否有合理的索引，是否需要磁區。
uj5u.com熱心網友回復：
如果知道了是什么操作引起的性能問題，可以直接找開發人員，由開發人員提供SQL。如果找不到了，需要使用AWR，把出現問題最明顯的時段的AWR報告拿出來基本上能確認問題了，ADDM也可以幫助分析性能問題。uj5u.com熱心網友回復：
可以找開發讓提供對應邏輯的sql，分析執行計劃，看是不是有該建的索引沒建，是不是有該走的索引沒走。有明確的sql，又不容易分析的話也可以做個10046事件看耗時在哪里，針對性優化。資料庫整體性能的話，可以跑個awr報告，分析優化下topsql。
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/74961.html
標籤：高級技術