Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com
http://www.free-online-exams.com
Problem Description: Process m000 died, see its trace file
ksvcreate: Process(m001) creation failed
After migrating database from 10.2.0.3 to 10.2.0.5 on a new server the database hang due to "Read By Other Session" Wait Event.
Shutting down instance: further logons disabled
Sun Oct 24 10:57:40 GST 2010
kkjcre1p: unable to spawn jobq slave process, error 1089
Sun Oct 24 10:57:45 GST 2010
kkjcre1p: unable to spawn jobq slave process, error 1089
AWR Report shows
WORKLOAD REPOSITORY report for
DB Name DB Id Instance Inst num Release RAC Host
ADP2 2590586986 adp 1 10.2.0.3.0 NO adsun4
Snap Id Snap Time Sessions Cursors/Session
Begin Snap: 32881 21-Oct-10 08:00:01 589 24.9
End Snap: 32882 21-Oct-10 09:00:38 1114 28.0
Elapsed: 60.63 (mins)
DB Time: 2,811.04 (mins)
Load Profile
Per Second Per Transaction
Redo size: 136,602.58 4,704.12
Logical reads: 379,351.64 13,063.54
Block changes: 2,353.50 81.05
Physical reads: 3,602.48 124.06
Physical writes: 170.68 5.88
User calls: 1,040.09 35.82
Parses: 1,179.47 40.62
Hard parses: 81.71 2.81
Sorts: 6,499.61 223.82
Logons: 7.25 0.25
Executes: 5,728.18 197.26
Transactions: 29.04
Top 5 Timed Events
Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
db file sequential read 7,213,522 64,295 9 38.1 User I/O
enq: TM - contention 13,134 38,495 2,931 22.8 Application
CPU time 32,364 19.2
enq: TX - row lock contention 2,249 5,588 2,485 3.3 Application
read by other session 742,678 4,061 5 2.4 User I/O
SQL ordered by Elapsed Time
Elapsed Time (s) CPU Time (s) Executions Elap per Exec (s) % Total DB Time SQL Id SQL Module SQL Text
22,100 10,526 26,874 0.82 13.10 5vbrgk6bwp0c7 Apache.exe declare rc__ number; simple_...
9,343 2,131 859 10.88 5.54 ay2vxw27bf5c8 Apache.exe declare rc__ number; simple_...
9,189 1,740 4,279 2.15 5.45 bfpduwnsaw4h8 Apache.exe declare rc__ number; simple_...
7,508 330 1,223 6.14 4.45 cbycbjq2zs8cf Apache.exe declare rc__ number; simple_...
6,340 368 583 10.88 3.76 7sw991yq7a7km Apache.exe declare rc__ number; simple_...
6,065 170 434 13.98 3.60 2dgpnj1mgd736 Apache.exe declare rc__ number; simple_...
4,288 934 2,403 1.78 2.54 525frh6ufz9ax Apache.exe declare rc__ number; simple_...
3,986 340 956 4.17 2.36 189b4k6uj3vx6 Apache.exe declare rc__ number; simple_...
SQL ordered by Gets
Buffer Gets Executions Gets per Exec %Total CPU Time (s) Elapsed Time (s) SQL Id SQL Module SQL Text
182,094,373 4,518 40,304.20 13.20 1521.15 2724.38 bbjw2jhdps5un Apache.exe declare rc__ number; simple_...
179,664,227 7,952 22,593.59 13.02 1390.57 2493.95 62ad3m07n545d Apache.exe SELECT * FROM CS_WFL_PROPERTY ...
167,175,797 0 12.12 1223.43 3421.54 cu8c0144khjb3 javaw.exe SELECT COUNT (EMP_ID) FROM (SE...
156,952,712 322,266 487.03 11.37 2479.81 3784.42 6f6rhrrwp2vv7 Apache.exe SELECT DECODE(:B2 , 'A', USERN...
130,770,628 26,874 4,866.06 9.48 10525.67 22099.85 5vbrgk6bwp0c7 Apache.exe declare rc__ number; simple_...
115,109,914 4,279 26,901.13 8.34 1740.12 9188.74 bfpduwnsaw4h8 Apache.exe declare rc__ number; simple_...
95,604,273 859 111,297.17 6.93 2130.59 9342.75 ay2vxw27bf5c8 Apache.exe declare rc__ number; simple_...
67,010,609 2,403 27,886.23 4.86 933.60 4288.38 525frh6ufz9ax Apache.exe declare rc__ number; simple_...
Tablespace IO Stats
Tablespace Reads Av Reads/s Av Rd(ms) Av Blks/Rd Writes Av Writes/s Buffer Waits Av Buf Wt(ms)
UNDOTBS1 4,296 1 10.11 1.00 141,772 39 95 2.95
Explanation:
When information is requested from the database, 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. In previous versions, this wait was classified under the "buffer busy waits" event. However, in Oracle 10.1 and higher, this wait time is now broken out into the "read by other session" wait event. Excessive waits for this event are typically due to several processes repeatedly reading the same blocks, e.g. many sessions scanning the same index or performing full table scans on the same table. Tuning this issue is a matter of finding and eliminating this contention.
Solution:
The reason why the performance is improved is because you increased the buffer cache and also because some processes now are not waiting for the same blocks any more ( a slight shift in the load can have caused this).
You'll need to increase the shared_pool to 2.5Gb
The pga_aggregate_target_parameter is set to high lower it to 3G and use a part of the memory that became available to increase the shared_pool.
ksvcreate: Process(m001) creation failed
After migrating database from 10.2.0.3 to 10.2.0.5 on a new server the database hang due to "Read By Other Session" Wait Event.
Shutting down instance: further logons disabled
Sun Oct 24 10:57:40 GST 2010
kkjcre1p: unable to spawn jobq slave process, error 1089
Sun Oct 24 10:57:45 GST 2010
kkjcre1p: unable to spawn jobq slave process, error 1089
AWR Report shows
WORKLOAD REPOSITORY report for
DB Name DB Id Instance Inst num Release RAC Host
ADP2 2590586986 adp 1 10.2.0.3.0 NO adsun4
Snap Id Snap Time Sessions Cursors/Session
Begin Snap: 32881 21-Oct-10 08:00:01 589 24.9
End Snap: 32882 21-Oct-10 09:00:38 1114 28.0
Elapsed: 60.63 (mins)
DB Time: 2,811.04 (mins)
Load Profile
Per Second Per Transaction
Redo size: 136,602.58 4,704.12
Logical reads: 379,351.64 13,063.54
Block changes: 2,353.50 81.05
Physical reads: 3,602.48 124.06
Physical writes: 170.68 5.88
User calls: 1,040.09 35.82
Parses: 1,179.47 40.62
Hard parses: 81.71 2.81
Sorts: 6,499.61 223.82
Logons: 7.25 0.25
Executes: 5,728.18 197.26
Transactions: 29.04
Top 5 Timed Events
Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
db file sequential read 7,213,522 64,295 9 38.1 User I/O
enq: TM - contention 13,134 38,495 2,931 22.8 Application
CPU time 32,364 19.2
enq: TX - row lock contention 2,249 5,588 2,485 3.3 Application
read by other session 742,678 4,061 5 2.4 User I/O
SQL ordered by Elapsed Time
Elapsed Time (s) CPU Time (s) Executions Elap per Exec (s) % Total DB Time SQL Id SQL Module SQL Text
22,100 10,526 26,874 0.82 13.10 5vbrgk6bwp0c7 Apache.exe declare rc__ number; simple_...
9,343 2,131 859 10.88 5.54 ay2vxw27bf5c8 Apache.exe declare rc__ number; simple_...
9,189 1,740 4,279 2.15 5.45 bfpduwnsaw4h8 Apache.exe declare rc__ number; simple_...
7,508 330 1,223 6.14 4.45 cbycbjq2zs8cf Apache.exe declare rc__ number; simple_...
6,340 368 583 10.88 3.76 7sw991yq7a7km Apache.exe declare rc__ number; simple_...
6,065 170 434 13.98 3.60 2dgpnj1mgd736 Apache.exe declare rc__ number; simple_...
4,288 934 2,403 1.78 2.54 525frh6ufz9ax Apache.exe declare rc__ number; simple_...
3,986 340 956 4.17 2.36 189b4k6uj3vx6 Apache.exe declare rc__ number; simple_...
SQL ordered by Gets
Buffer Gets Executions Gets per Exec %Total CPU Time (s) Elapsed Time (s) SQL Id SQL Module SQL Text
182,094,373 4,518 40,304.20 13.20 1521.15 2724.38 bbjw2jhdps5un Apache.exe declare rc__ number; simple_...
179,664,227 7,952 22,593.59 13.02 1390.57 2493.95 62ad3m07n545d Apache.exe SELECT * FROM CS_WFL_PROPERTY ...
167,175,797 0 12.12 1223.43 3421.54 cu8c0144khjb3 javaw.exe SELECT COUNT (EMP_ID) FROM (SE...
156,952,712 322,266 487.03 11.37 2479.81 3784.42 6f6rhrrwp2vv7 Apache.exe SELECT DECODE(:B2 , 'A', USERN...
130,770,628 26,874 4,866.06 9.48 10525.67 22099.85 5vbrgk6bwp0c7 Apache.exe declare rc__ number; simple_...
115,109,914 4,279 26,901.13 8.34 1740.12 9188.74 bfpduwnsaw4h8 Apache.exe declare rc__ number; simple_...
95,604,273 859 111,297.17 6.93 2130.59 9342.75 ay2vxw27bf5c8 Apache.exe declare rc__ number; simple_...
67,010,609 2,403 27,886.23 4.86 933.60 4288.38 525frh6ufz9ax Apache.exe declare rc__ number; simple_...
Tablespace IO Stats
Tablespace Reads Av Reads/s Av Rd(ms) Av Blks/Rd Writes Av Writes/s Buffer Waits Av Buf Wt(ms)
UNDOTBS1 4,296 1 10.11 1.00 141,772 39 95 2.95
Explanation:
When information is requested from the database, 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. In previous versions, this wait was classified under the "buffer busy waits" event. However, in Oracle 10.1 and higher, this wait time is now broken out into the "read by other session" wait event. Excessive waits for this event are typically due to several processes repeatedly reading the same blocks, e.g. many sessions scanning the same index or performing full table scans on the same table. Tuning this issue is a matter of finding and eliminating this contention.
Solution:
The reason why the performance is improved is because you increased the buffer cache and also because some processes now are not waiting for the same blocks any more ( a slight shift in the load can have caused this).
You'll need to increase the shared_pool to 2.5Gb
The pga_aggregate_target_parameter is set to high lower it to 3G and use a part of the memory that became available to increase the shared_pool.
No comments:
Post a Comment