Tips to tune "Read by Other Session"
Dear all,
SQL> select banner from v$version;
BANNER
-------------------------------------------------------------
Oracle Database 10g Release 10.2.0.4.0 - Production
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
I am getting lots of many " read by other session "wait event (since Oracle will first read the data from disk into the database buffer cache. If two or more sessions request the same information, the first session will read the data into the buffer cache while other sessions wait):
SQL> column event format a22
SQL> select event,
2 total_waits,
3 average_wait,
4 time_waited_micro,
5 wait_class
6 from v$system_event
7 where event in ('read by other session','buffer busy waits')
8 /
EVENT TOTAL_WAITS AVERAGE_WAIT TIME_WAITED_MICRO WAIT_CLASS
---------------------- ----------- ------------ ----------------- ----------------------------------------------------------------
buffer busy waits 4818 2.07 99816991 Concurrency
read by other session 164662030 .1 1.6040E+11 User I/O
SQL> select * from v$system_event
2 where event like '%wait%';
EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT TIME_WAITED_MICRO EVENT_ID WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------------------- ----------- -------------- ----------- ------------ ----------------- ---------- ------------- ----------- --------------
class slave wait 1177 1 507 .43 5072079 1055154682 2723168908 6 Idle
local write wait 7 0 23 3.25 227760 1570123276 1740759767 8 User I/O
buffer busy waits 4824 65 9982 2.07 99817103 2161531084 3875070507 4 Concurrency
cursor: pin S wait on 8908 8858 10894 1.22 108942212 1729366244 3875070507 4 Concurrency
X
jobq slave wait 24396 24353 7305645 299.46 7.3056E+10 782339817 2723168908 6 Idle
Streams AQ: waiting fo 2651 2467 89360002 33708.04 8.9360E+11 3702640206 2723168908 6 Idle
r time management or c
leanup tasks
Streams AQ: qmn coordi 141675 71408 196280182 1385.43 1.9628E+12 989870553 2723168908 6 Idle
nator idle wait
Streams AQ: qmn slave 70332 13 196301805 2791.07 1.9630E+12 1830121438 2723168908 6 Idle
idle wait
wait list latch free 16 0 11 .7 111994 2530878290 1893977003 0 Other
LGWR wait for redo cop 5690 11 72 .01 723614 4266849434 1893977003 0 Other
y
Streams AQ: qmn coordi 7 4 2071 295.87 20710681 1565566389 1893977003 0 Other
nator waiting for slav
e to start
11 rows selected.
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ----------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 1232M
sga_target big integer 1232M
Any suggetion is appreciated ...
Edited by: user12000301 on Jul 22, 2011 4:33 AM
Edited by: user12000301 on Jul 22, 2011 4:36 AM
Edited by: user12000301 on Jul 22, 2011 4:41 AM