Home » RDBMS Server » Performance Tuning » db sequential read (merged) (oracle 10g R2)
db sequential read (merged) [message #490468] |
Tue, 25 January 2011 04:13 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
sahadba
Messages: 59 Registered: September 2009 Location: Pune
|
Member |
![getme.maddy@gmail.com](/forum/theme/orafaq/images/google.png)
|
|
Hi Experts,
I am troubleshooting a performance issue where the users says that the database is slow...
I generated two AWR reports
a) when the performance was good...
b) when the performance was bad...
In both cases the Top wait event was "db sequential read", in the case bad performance the db sequential read was much higher than the normal...
Below are the chunks from the AWR report..
Good time : -
Quote:Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
db file sequential read 288,580 4,629 16 97.9 User I/O
control file parallel write 11,927 327 27 6.9 System I/O
CPU time 89 1.9
log file parallel write 2,552 29 11 0.6 System I/O
db file scattered read 337 29 86 0.6 User I/O
-------------------------------------------------------------
Time Model Statistics DB/Inst: LVN/LVN Snaps: 2328-2338
-> Total time in database user-calls (DB Time): 4730.1s
-> Statistics including the word "background" measure background process
time, and so do not contribute to the DB time statistic
-> Ordered by % or DB time desc, Statistic name
Statistic Name Time (s) % of DB Time
------------------------------------------ ------------------ ------------
sql execute elapsed time 4,725.7 99.9
DB CPU 89.5 1.9
parse time elapsed 29.4 .6
hard parse elapsed time 27.6 .6
connection management call elapsed time 26.6 .6
PL/SQL compilation elapsed time 6.1 .1
PL/SQL execution elapsed time 5.6 .1
hard parse (sharing criteria) elapsed time 3.7 .1
hard parse (bind mismatch) elapsed time 3.5 .1
repeated bind elapsed time 0.1 .0
sequence load elapsed time 0.0 .0
DB time 4,730.1 N/A
background elapsed time 525.7 N/A
background cpu time 91.2 N/A
-------------------------------------------------------------
Wait Class DB/Inst: LVN/LVN Snaps: 2328-2338
-> s - second
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc
Avg
%Time Total Wait wait Waits
Wait Class Waits -outs Time (s) (ms) /txn
-------------------- ---------------- ------ ---------------- ------- ---------
User I/O 288,950 .0 4,659 16 314.4
System I/O 39,321 .0 391 10 42.8
Commit 475 .4 12 25 0.5
Concurrency 143 .0 11 76 0.2
Application 7,648 .0 2 0 8.3
Other 461 7.6 2 3 0.5
Network 33,404 .0 0 0 36.3
Configuration 1 .0 0 1 0.0
-------------------------------------------------------------
Wait Events DB/Inst: LVN/LVN Snaps: 2328-2338
-> s - second
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc (idle events last)
Avg
%Time Total Wait wait Waits
Event Waits -outs Time (s) (ms) /txn
---------------------------- -------------- ------ ----------- ------- ---------
db file sequential read 288,580 .0 4,629 16 314.0
control file parallel write 11,927 .0 327 27 13.0
log file parallel write 2,552 .0 29 11 2.8
db file scattered read 337 .0 29 86 0.4
db file parallel write 9,599 .0 26 3 10.4
log file sync 475 .4 12 25 0.5
os thread startup 142 .0 11 76 0.2
control file sequential read 15,243 .0 8 1 16.6
SQL*Net break/reset to clien 7,648 .0 2 0 8.3
SGA: allocation forcing comp 74 47.3 1 11 0.1
latch free 12 .0 1 59 0.0
direct path write 23 .0 1 24 0.0
direct path read 10 .0 0 15 0.0
SQL*Net more data to client 5,692 .0 0 0 6.2
SQL*Net more data from clien 2,301 .0 0 0 2.5
SQL*Net message to client 25,411 .0 0 0 27.7
rdbms ipc reply 69 .0 0 0 0.1
LGWR wait for redo copy 306 .0 0 0 0.3
latch: shared pool 1 .0 0 3 0.0
log buffer space 1 .0 0 1 0.0
SQL*Net message from client 25,433 .0 36,021 1416 27.7
Streams AQ: qmn coordinator 2,572 50.1 35,086 13642 2.8
Streams AQ: qmn slave idle w 1,284 .0 35,086 27326 1.4
Streams AQ: waiting for time 8 100.0 27,297 ####### 0.0
jobq slave wait 2,692 94.9 7,749 2878 2.9
PL/SQL lock timer 120 100.0 3,516 29304 0.1
Streams AQ: waiting for mess 2,424 100.0 2,772 1144 2.6
class slave wait 21 .0 0 0 0.0
Bad time : -
Quote:Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
db file sequential read 5,399,600 46,498 9 58.2 User I/O
CPU time 22,459 28.1
db file scattered read 442,754 7,991 18 10.0 User I/O
db file parallel write 1,033,952 2,888 3 3.6 System I/O
read by other session 225,841 2,471 11 3.1 User I/O
-------------------------------------------------------------
Time Model Statistics DB/Inst: LVN/LVN Snaps: 2700-2710
-> Total time in database user-calls (DB Time): 79888s
-> Statistics including the word "background" measure background process
time, and so do not contribute to the DB time statistic
-> Ordered by % or DB time desc, Statistic name
Statistic Name Time (s) % of DB Time
------------------------------------------ ------------------ ------------
sql execute elapsed time 78,140.1 97.8
DB CPU 22,459.3 28.1
parse time elapsed 6,177.8 7.7
hard parse elapsed time 5,970.0 7.5
hard parse (sharing criteria) elapsed time 1,513.0 1.9
hard parse (bind mismatch) elapsed time 1,508.8 1.9
PL/SQL execution elapsed time 298.9 .4
connection management call elapsed time 11.8 .0
PL/SQL compilation elapsed time 6.9 .0
sequence load elapsed time 6.5 .0
repeated bind elapsed time 0.4 .0
failed parse elapsed time 0.1 .0
DB time 79,888.0 N/A
background elapsed time 4,674.2 N/A
background cpu time 346.4 N/A
-------------------------------------------------------------
Wait Class DB/Inst: LVN/LVN Snaps: 2700-2710
-> s - second
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc
Avg
%Time Total Wait wait Waits
Wait Class Waits -outs Time (s) (ms) /txn
-------------------- ---------------- ------ ---------------- ------- ---------
User I/O 6,128,820 .0 57,107 9 45.8
System I/O 1,174,898 .0 4,209 4 8.8
Commit 87,609 .1 720 8 0.7
Application 355,354 .0 92 0 2.7
Network 16,940,065 .0 86 0 126.5
Concurrency 12,551 35.4 67 5 0.1
Other 6,428 .7 48 7 0.0
Configuration 76,058 99.7 13 0 0.6
-------------------------------------------------------------
Wait Events DB/Inst: LVN/LVN Snaps: 2700-2710
-> s - second
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc (idle events last)
Avg
%Time Total Wait wait Waits
Event Waits -outs Time (s) (ms) /txn
---------------------------- -------------- ------ ----------- ------- ---------
db file sequential read 5,399,600 .0 46,498 9 40.3
db file scattered read 442,754 .0 7,991 18 3.3
db file parallel write 1,033,952 .0 2,888 3 7.7
read by other session 225,841 .0 2,471 11 1.7
log file parallel write 101,477 .0 758 7 0.8
log file sync 87,609 .1 720 8 0.7
control file parallel write 16,611 .0 492 30 0.1
direct path read temp 27,413 .0 99 4 0.2
SQL*Net break/reset to clien 355,308 .0 92 0 2.7
control file sequential read 22,754 .0 71 3 0.2
cursor: pin S wait on X 4,452 99.9 48 11 0.0
buffer exterminate 78 32.1 44 560 0.0
SQL*Net more data to client 2,643,888 .0 43 0 19.8
SQL*Net more data from clien 1,700,019 .0 29 0 12.7
direct path read 6,796 .0 27 4 0.1
os thread startup 189 .0 15 80 0.0
direct path write temp 24,442 .0 14 1 0.2
SQL*Net message to client 12,596,158 .0 14 0 94.1
write complete waits 8 100.0 8 978 0.0
direct path write 1,872 .0 6 3 0.0
log file switch completion 44 6.8 5 123 0.0
rdbms ipc reply 3,724 .0 3 1 0.0
enq: TX - index contention 65 .0 1 21 0.0
buffer busy waits 3,607 .0 1 0 0.0
latch free 188 .0 1 5 0.0
local write wait 94 .0 0 5 0.0
latch: cache buffers chains 2,531 .0 0 0 0.0
latch: library cache 183 .0 0 2 0.0
enq: RO - fast object reuse 34 .0 0 10 0.0
log file single write 52 .0 0 6 0.0
latch: shared pool 203 .0 0 1 0.0
log file sequential read 52 .0 0 4 0.0
undo segment extension 75,995 99.7 0 0 0.6
enq: TX - contention 7 .0 0 24 0.0
latch: row cache objects 1,271 .0 0 0 0.0
SGA: allocation forcing comp 11 90.9 0 11 0.0
kksfbc child completion 2 100.0 0 49 0.0
library cache load lock 1 .0 0 97 0.0
enq: TX - row lock contentio 12 .0 0 8 0.0
LGWR wait for redo copy 2,142 .0 0 0 0.0
db file parallel read 8 .0 0 5 0.0
latch: session allocation 17 .0 0 1 0.0
latch: enqueue hash chains 19 .0 0 1 0.0
latch: object queue header o 90 .0 0 0 0.0
latch: In memory undo latch 36 .0 0 0 0.0
latch: cache buffers lru cha 74 .0 0 0 0.0
latch: parallel query alloc 4 .0 0 1 0.0
reliable message 22 .0 0 0 0.0
latch: library cache pin 4 .0 0 0 0.0
latch: redo allocation 7 .0 0 0 0.0
latch: library cache lock 3 .0 0 0 0.0
latch: undo global data 6 .0 0 0 0.0
cursor: pin S 6 .0 0 0 0.0
row cache lock 4 .0 0 0 0.0
latch: messages 15 .0 0 0 0.0
latch: checkpoint queue latc 4 .0 0 0 0.0
latch: redo writing 8 .0 0 0 0.0
enq: FB - contention 2 .0 0 0 0.0
enq: HW - contention 3 .0 0 0 0.0
cursor: mutex S 1 .0 0 0 0.0
Wait Events DB/Inst: LVN/LVN Snaps: 2700-2710
-> s - second
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc (idle events last)
How should i proceed further...
Since User I/O is the issue, should i concentrate on SQL query tuning??
Could this be an issue with the storage devices or an OS issue??
Please Guide me...
|
|
|
|
Goto Forum:
Current Time: Mon Sep 09 10:03:45 CDT 2024
|