-
I am getting Oracle error while I try to execute 18-20 jobs simultaneously thru maestro scheduler at one time (3am).
The error is - ORA-01575: timeout waiting for space management resource. Where can I look for the parameters to be changed in session waits or in utlbstat/estat? I am working on 8.0.5.2.1
I tried to get the Session waits the results are like this -
SID SEQ# EVENT P1TEXT P1 P1RAW P2TEXT P2 P2RAW P3TEXT P3 P3RAW WAIT_TIME SECONDS_IN STATE
---------- ---------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------- -------- ---------------------------------------------------------------- ---------- -------- ---------------------------------------------------------------- ---------- -------- ---------- ---------- -------------------
1 1137 pmon timer duration 300 0000012C 0 00 0 00 0 63 WAITING
3 62081 rdbms ipc message timeout 27790 00006C8E 0 00 0 00 0 2 WAITING
7 797 rdbms ipc message timeout 180000 0002BF20 0 00 0 00 0 73835 WAITING
5 23223 rdbms ipc message timeout 177 000000B1 0 00 0 00 0 0 WAITING
2 44788 io done msg ptr 0 00 0 00 0 00 -1 0 WAITED SHORT TIME
10 16975 slave wait msg ptr 3473263292 CF05CABC 0 00 0 00 0 0 WAITING
12 17063 slave wait msg ptr 3473320132 CF06A8C4 0 00 0 00 0 0 WAITING
14 17265 slave wait msg ptr 3473769096 CF0D8288 0 00 0 00 0 0 WAITING
16 17466 slave wait msg ptr 3474095680 CF127E40 0 00 0 00 0 0 WAITING
15 17339 slave wait msg ptr 3474005100 CF111C6C 0 00 0 00 0 0 WAITING
17 17733 slave wait msg ptr 3473493176 CF094CB8 0 00 0 00 0 0 WAITING
13 17195 slave wait msg ptr 3473467416 CF08E818 0 00 0 00 0 0 WAITING
11 16844 slave wait msg ptr 3473463356 CF08D83C 0 00 0 00 1 0 WAITED KNOWN TIME
27 1185 write complete waits file# 32 00000020 block# 59370 0000E7EA id 1029 00000405 0 0 WAITING
4 58930 log file parallel write files 2 00000002 blocks 626 00000272 requests 2 00000002 0 0 WAITING
8 19394 log file sync buffer# 1924 00000784 0 00 0 00 0 0 WAITING
46 53729 log file sync buffer# 2050 00000802 0 00 0 00 0 0 WAITING
54 1271 log file sync buffer# 1744 000006D0 0 00 0 00 0 0 WAITING
58 1509 log file sync buffer# 1898 0000076A 0 00 0 00 0 0 WAITING
55 15291 log file sync buffer# 1926 00000786 0 00 0 00 0 0 WAITING
25 22050 log file sync buffer# 1923 00000783 0 00 0 00 0 0 WAITING
6 13051 smon timer sleep time 300 0000012C failed 0 00 0 00 0 88 WAITING
9 11903 SQL*Net message from client driver id 1650815232 62657100 #bytes 1 00000001 0 00 0 54 WAITING
19 5003 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 1637 WAITING
26 60 SQL*Net message from client driver id 1650815232 62657100 #bytes 1 00000001 0 00 0 15 WAITING
30 261 SQL*Net message from client driver id 1650815232 62657100 #bytes 1 00000001 0 00 0 113 WAITING
32 78 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 49 WAITING
38 7709 SQL*Net message from client driver id 1650815232 62657100 #bytes 1 00000001 0 00 0 236 WAITING
35 26 SQL*Net message from client driver id 1650815232 62657100 #bytes 1 00000001 0 00 -1 0 WAITED SHORT TIME
50 15830 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 1622 WAITING
52 4424 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 1625 WAITING
29 87 SQL*Net message from client driver id 1650815232 62657100 #bytes 1 00000001 0 00 0 35 WAITING
20 2026 SQL*Net more data from client driver id 1650815232 62657100 #bytes 19 00000013 0 00 -1 0 WAITED SHORT TIME
39 11761 SQL*Net more data from client driver id 1650815232 62657100 #bytes 14 0000000E 0 00 -1 0 WAITED SHORT TIME
51 3627 SQL*Net more data from client driver id 1650815232 62657100 #bytes 1 00000001 0 00 -1 0 WAITED SHORT TIME
49 700 SQL*Net more data from client driver id 1650815232 62657100 #bytes 1 00000001 0 00 -1 0 WAITED SHORT TIME
34 1226 SQL*Net more data from client driver id 1650815232 62657100 #bytes 1 00000001 0 00 -1 0 WAITED SHORT TIME
22 843 SQL*Net more data from client driver id 1650815232 62657100 #bytes 1 00000001 0 00 -1 0 WAITED SHORT TIME
End Time
-------------------
03-12-2001 03:01:04
Start Time
-------------------
03-12-2001 03:02:04
SID SEQ# EVENT P1TEXT P1 P1RAW P2TEXT P2 P2RAW P3TEXT P3 P3RAW WAIT_TIME SECONDS_IN STATE
---------- ---------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------- -------- ---------------------------------------------------------------- ---------- -------- ---------------------------------------------------------------- ---------- -------- ---------- ---------- -------------------
1 1167 pmon timer duration 300 0000012C 0 00 0 00 0 17 WAITING
3 62129 rdbms ipc message timeout 21630 0000547E 0 00 0 00 0 1 WAITING
7 797 rdbms ipc message timeout 180000 0002BF20 0 00 0 00 0 73896 WAITING
5 24308 rdbms ipc message timeout 274 00000112 0 00 0 00 0 3 WAITING
2 48295 io done msg ptr 0 00 0 00 0 00 0 0 WAITING
10 17422 slave wait msg ptr 3474038700 CF119FAC 0 00 0 00 0 0 WAITING
12 17510 slave wait msg ptr 3474230064 CF148B30 0 00 0 00 0 0 WAITING
14 17713 slave wait msg ptr 3473310332 CF06827C 0 00 0 00 1 0 WAITED KNOWN TIME
16 17915 slave wait msg ptr 3473114892 CF03870C 0 00 0 00 2 0 WAITED KNOWN TIME
15 17787 slave wait msg ptr 3473312852 CF068C54 0 00 0 00 3 0 WAITED KNOWN TIME
17 18183 slave wait msg ptr 3473460556 CF08CD4C 0 00 0 00 5 0 WAITED KNOWN TIME
13 17642 slave wait msg ptr 3474102540 CF12990C 0 00 0 00 2 0 WAITED KNOWN TIME
11 17296 slave wait msg ptr 3473108032 CF036C40 0 00 0 00 0 0 WAITING
8 22507 write complete waits file# 31 0000001F block# 2 00000002 id 1029 00000405 0 0 WAITING
46 56361 write complete waits file# 32 00000020 block# 827 0000033B id 1029 00000405 0 0 WAITING
4 60563 log file parallel write files 2 00000002 blocks 722 000002D2 requests 2 00000002 0 0 WAITING
20 6341 log file sync buffer# 150 00000096 0 00 0 00 0 0 WAITING
27 3372 log file sync buffer# 4043 00000FCB 0 00 0 00 0 0 WAITING
34 3844 log file sync buffer# 4055 00000FD7 0 00 0 00 0 0 WAITING
26 1521 log file sync buffer# 237 000000ED 0 00 0 00 0 0 WAITING
55 17935 log file sync buffer# 3861 00000F15 0 00 0 00 0 0 WAITING
51 6504 log file sync buffer# 290 00000122 0 00 0 00 0 0 WAITING
49 3261 log file sync buffer# 3987 00000F93 0 00 0 00 0 0 WAITING
39 14886 log file sync buffer# 139 0000008B 0 00 0 00 0 0 WAITING
22 2820 log file sync buffer# 415 0000019F 0 00 0 00 0 0 WAITING
25 26280 log file sync buffer# 3960 00000F78 0 00 0 00 0 0 WAITING
6 13051 smon timer sleep time 300 0000012C failed 0 00 0 00 0 149 WAITING
9 11903 SQL*Net message from client driver id 1650815232 62657100 #bytes 1 00000001 0 00 0 115 WAITING
19 5003 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 1698 WAITING
29 96 SQL*Net message from client driver id 1650815232 62657100 #bytes 1 00000001 0 00 0 35 WAITING
30 261 SQL*Net message from client driver id 1650815232 62657100 #bytes 1 00000001 0 00 0 174 WAITING
58 26 SQL*Net message from client driver id 1650815232 62657100 #bytes 1 00000001 0 00 -1 0 WAITED SHORT TIME
52 4424 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 1686 WAITING
50 15830 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 1683 WAITING
32 87 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 50 WAITING
End Time
-------------------
03-12-2001 03:02:04
Start Time
-------------------
03-12-2001 03:03:05
SID SEQ# EVENT P1TEXT P1 P1RAW P2TEXT P2 P2RAW P3TEXT P3 P3RAW WAIT_TIME SECONDS_IN STATE
---------- ---------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------- -------- ---------------------------------------------------------------- ---------- -------- ---------------------------------------------------------------- ---------- -------- ---------- ---------- -------------------
1 1196 pmon timer duration 300 0000012C 0 00 0 00 0 16 WAITING
3 62171 rdbms ipc message timeout 15776 00003DA0 0 00 0 00 0 2 WAITING
7 797 rdbms ipc message timeout 180000 0002BF20 0 00 0 00 0 73956 WAITING
5 25393 rdbms ipc message timeout 251 000000FB 0 00 0 00 0 0 WAITING
2 51345 io done msg ptr 0 00 0 00 0 00 0 0 WAITING
10 17809 slave wait msg ptr 3473253212 CF05A35C 0 00 0 00 0 0 WAITING
12 17899 slave wait msg ptr 3473302212 CF0662C4 0 00 0 00 0 0 WAITING
14 18099 slave wait msg ptr 3473171872 CF0465A0 0 00 0 00 2 0 WAITED KNOWN TIME
16 18302 slave wait msg ptr 3473253212 CF05A35C 0 00 0 00 -1 0 WAITED SHORT TIME
15 18176 slave wait msg ptr 3473307952 CF067930 0 00 0 00 0 0 WAITING
17 18574 slave wait msg ptr 3474252044 CF14E10C 0 00 0 00 0 0 WAITING
13 18030 slave wait msg ptr 3474027500 CF1173EC 0 00 0 00 0 0 WAITING
11 17682 slave wait msg ptr 3473457056 CF08BFA0 0 00 0 00 1 0 WAITED KNOWN TIME
4 62144 log file parallel write files 2 00000002 blocks 868 00000364 requests 2 00000002 0 0 WAITING
22 4467 log file switch completion 0 00 0 00 0 00 0 0 WAITING
34 6040 log file switch completion 0 00 0 00 0 00 0 0 WAITING
49 5483 log file switch completion 0 00 0 00 0 00 0 0 WAITING
51 9011 log file switch completion 0 00 0 00 0 00 0 0 WAITING
27 5350 log file switch completion 0 00 0 00 0 00 0 0 WAITING
8 25306 log file sync buffer# 3647 00000E3F 0 00 0 00 0 0 WAITING
55 20360 log file sync buffer# 4052 00000FD4 0 00 0 00 0 0 WAITING
46 58802 log file sync buffer# 3570 00000DF2 0 00 0 00 0 0 WAITING
26 3593 log file sync buffer# 3671 00000E57 0 00 0 00 0 0 WAITING
20 10387 log file sync buffer# 3641 00000E39 0 00 0 00 0 0 WAITING
25 30097 log file sync buffer# 4073 00000FE9 0 00 0 00 0 0 WAITING
6 13051 smon timer sleep time 300 0000012C failed 0 00 0 00 0 209 WAITING
9 11903 SQL*Net message from client driver id 1650815232 62657100 #bytes 1 00000001 0 00 0 175 WAITING
19 5003 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 1758 WAITING
28 26 SQL*Net message from client driver id 1650815232 62657100 #bytes 1 00000001 0 00 -1 0 WAITED SHORT TIME
30 261 SQL*Net message from client driver id 1650815232 62657100 #bytes 1 00000001 0 00 0 234 WAITING
52 4424 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 1746 WAITING
50 15830 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 1743 WAITING
48 15 SQL*Net message from client driver id 1650815232 62657100 #bytes 1 00000001 0 00 0 35 WAITING
39 15460 SQL*Net message from client driver id 1650815232 62657100 #bytes 1 00000001 0 00 0 43 WAITING
32 96 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 50 WAITING
End
-
Checkout http://metalink.oracle.com/metalink/...atabase_id=NOT
You've probably got a lot of TEMP usage during this time. You may want to look at increasing your temp segments in your temporary tablespace, using different temporary tablespaces for the different jobs (assuming they are different users), or use locally managed tablespaces for your temporary tablespace.
-
SMON could be causing that problem:
1) pctincrease for temporary tablespace as zero.
2)use temporary option for temporary tablespace.
The session waits stated below, did those occur during that 3am process, enable trace for that process and find out where the wait is, could be because of sorting or could be lots of deletes and inserts ,release of free space and usage of free space , make sure pctincrease of data tablespace is also zero.
The stated waits shows more problems:
log file sync wait and log file switch completion, this indicates two things
1) log_buffer is very high and log files are small
2) lot of modifications are happening.
Reduce log_buffer and increase log files.
Also find out if there are any dirty buffers inspected in v$sysstat.
Then u may need to increase db_writer_processes.
Take Care
GP
