A CS software, database is 10 g, users reflect the recent open list and slow to respond to the form system, three to four seconds before, now need to 20 or 30 seconds, the largest amount of data to do, obviously not normal, were yet to find a way to solve, please experienced ace to give some Suggestions how to optimize, thank you!
SQL statements:
-- -- -- 1, find the disk read high statement
The Select sql_text.
Disk_reads,
Executions,
Disk_reads/decode (executions, 0, 1, executions) reads_per_exec
The from v $sqlarea
The order by reads_per_exec desc;
Results:
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
98485.43 SELECT COUNT (*) FROM InstanceAddinational WHERE ParentID='{67 ff783b - d2d - b594 d85c - 4-8 fc48beb7150}' and SAFETYORCONTENT=0 196861 2 98430.5
4 SELECT id FROM INSTANCEADDINATIONAL WHERE ParentID='{1 e0cf5d0-1626-4632 - bc5a - d2b69ef3eb56}' and SAFETYORCONTENT=0 order by CREATEDATETIME 196853 2 98426.5
5 SELECT COUNT (*) FROM InstanceAddinational WHERE ParentID='{0 ac3 f91 c4d447c - 1-4 - a3ea - bc101cd390a7}' and SAFETYORCONTENT=0 196825 2 98412.5
6 SELECT COUNT (*) FROM InstanceAddinational WHERE ParentID='{591 d26fc - fbe8-40 df ce - 96-8 b2bc4d6934d}' and SAFETYORCONTENT=0 196811 2 98405.5
7 SELECT id FROM INSTANCEADDINATIONAL WHERE ParentID='{a1a c787 f4e4-3304-4 - bcb8-710 c3d6fcaaf}' and SAFETYORCONTENT=0 order by CREATEDATETIME 196732 2 98366
8 SELECT COUNT (*) FROM InstanceAddinational WHERE ParentID='{8 c1c2863 c45 a44-2-4 - aaaf - 9746691 e1433}' and SAFETYORCONTENT=0 196700 2 98350
9 SELECT ID, TemplateID FROM InstanceAddinational WHERE ParentID='{a1a c787 f4e4-3304-4 - bcb8-710 c3d6fcaaf}' and SAFETYORCONTENT=0 294583 3 98194.3333333333
10 select count (*) from the instance I, instanceaddinational where a i.i d=Amy polumbo arentid and i.i d='{bbfc9d14-4416-4373-9570 - d96085f5ed2d}' and a.s afetyorcontent=1 783625 8 97953.125
11 select count (*) from the instance I, instanceaddinational where a i.i d=Amy polumbo arentid and i.i d='{a1a c787 f4e4-3304-4 - bcb8-710 c3d6fcaaf}' and a.s afetyorcontent=1 1075375 11, 97761.3636363636
SELECT ID, 12 TemplateID FROM InstanceAddinational WHERE ParentID='{12737 d80 ABC - 9314-4 - a403 - cc7b5910418a}' and SAFETYORCONTENT=0 195451 2 97725.5
13 select count (*) from the instance I, instanceaddinational where a i.i d=Amy polumbo arentid and i.i d='{8 c1c2863 c45 a44-2-4 - aaaf - 9746691 e1433}' and a.s afetyorcontent=1 194098 2 97049
14 select count (*) from the instance I, instanceaddinational where a i.i d=Amy polumbo arentid and i.i d='{1 e0cf5d0-1626-4632 - bc5a - d2b69ef3eb56}' and a.s afetyorcontent=1, 2, 96869.5 193739
15 select count (*) from the instance I, instanceaddinational where a i.i d=Amy polumbo arentid and i.i d='{069 f1c a055f - 6-4384-8 e95980 f55-214259}' and a.s afetyorcontent=1 387464 4 96866
16 the SELECT id FROM INSTANCEADDINATIONAL WHERE ParentID='{8 d1b8248 e4-7406-444 - a - 80 - cebd78d14cb1}' and SAFETYORCONTENT=0 order by CREATEDATETIME 96837 1 96837
17 select count (*) from the instance I, instanceaddinational where a i.i d=Amy polumbo arentid and i.i d='{f8a d2fc7 127-1-490 - b - 94 - c2 - bd99f4140188}' and a.s afetyorcontent=1, 2, 96737.5 193475
18 select count (*) from the instance I, instanceaddinational where a i.i d=Amy polumbo arentid and i.i d='{9 a2e01c3 bb83-47-82 a1 - ec983058e309 f8}' and a.s afetyorcontent=1, 2, 96576.5 193153
19 select count (*) from the instance I, instanceaddinational where a i.i d=Amy polumbo arentid and i.i d='{a181b123 - Ed e81b - 44-8 be7a0ffde01 ba0-1}' and a.s afetyorcontent=1 96506 1 96506
The SELECT * FROM 20 INSTANCEADDINATIONAL WHERE ParentID='{8 d1b8248 e4-7406-444 - a - 80 - cebd78d14cb1}' and SAFETYORCONTENT=1 order by CREATEDATETIME 96501 1 96501
21 SELECT COUNT (*) FROM InstanceAddinational WHERE ParentID='{d6782ec6 d0e db3f - 4-9 dfe - 88 baae2cf998}' and SAFETYORCONTENT=0 96492 1 96492
22 the SELECT ID, TemplateID FROM InstanceAddinational WHERE ParentID='{b2505f24 - f0c5-4315-9 c0b71061b b73-739}' and SAFETYORCONTENT=0 96488 1 96488
SELECT ID, 23 TemplateID FROM InstanceAddinational WHERE ParentID='{3 b14c622 d7f - b09a - 5930059-7 a25-4 a09b3}' and SAFETYORCONTENT=0 96478 1 96478
SELECT ID, 24 TemplateID FROM InstanceAddinational WHERE ParentID='{538 ce67c - a00f - 427 - f - bec5 - b47905a25fad}' and SAFETYORCONTENT=0 96324 1 96324
25 select count (*) from the instance I, instanceaddinational where a i.i d=Amy polumbo arentid and i.i d='{591 d26fc - fbe8-40 df ce - 96-8 b2bc4d6934d}' and a.s afetyorcontent=1 288860 3 96286.6666666667
26 SELECT COUNT (*) FROM InstanceAddinational WHERE ParentID='{b551acba - d39 d7 b2f bf87-4-95-754730101}' and SAFETYORCONTENT=0 96068 1 96068
SELECT ID, 27 TemplateID FROM InstanceAddinational WHERE ParentID='{3848 b4f9 - c35d - 4306 - b8c4-4 a9f0b46c3c6}' and SAFETYORCONTENT=0 96064 1 96064
SELECT ID, 28 TemplateID FROM InstanceAddinational WHERE ParentID='{c12dac65-765 - c - 811 - d - 0-48 f1 fb91a7e9966}' and SAFETYORCONTENT=0 95959 1 95959
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull