| One job is hanging after SGA increase [message #579290] |
Mon, 11 March 2013 02:37  |
syed.samiullah
Messages: 9
Registered: August 2008
Location: Abu Dhabi
|
Junior Member |
|
|
Additional Information
Step 1: Increased Physical Memory on one Node from 32 G to 48 G.
Step 1 Impact : DB was running same as before
Step 2 : Increased SGA from 12 G to 15 G.
Step2 Impact : DB was running same as before for 1 day next day one reporting job was hanging.
Step 3 : Increased DB_CACHE_SIZE from 5G to 7G.
Step 3 Impact : Over all CPU Utilization was high and no effect on reporting job.
Step 4 : Decreased DB_CACHE_SIZE from 7 G to 5 G.
Step 4 Impact : CPU Utilization came down little bit but no effect on reporting job.
Now our main concern is why CPU Utilization is going high. Because same thing we did last time and we got positive results.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: One job is hanging after SGA increase [message #579373 is a reply to message #579371] |
Mon, 11 March 2013 23:37  |
syed.samiullah
Messages: 9
Registered: August 2008
Location: Abu Dhabi
|
Junior Member |
|
|
Instance 1
1 15360 1 1963198 1 380552497
2 7680 0.5 2147353 1.0938 500045981
3 11520 0.75 2019744 1.0288 420396343
4 30720 2 1813217 0.9236 294319301
5 23040 1.5 1857388 0.9461 306040318
6 26880 1.75 1820874 0.9275 294319301
7 19200 1.25 1905878 0.9708 345275281
Instance 2
1 15360 1 2028685 1 383220172
2 7680 0.5 2271924 1.1199 553293284
3 11520 0.75 2137220 1.0535 424416340
4 30720 2 1910212 0.9416 223072462
5 23040 1.5 1910210 0.9416 275075439
6 26880 1.75 1910210 0.9416 223072462
7 19200 1.25 1933743 0.9532 345051443
|
|
|
|
| Re: One job is hanging after SGA increase [message #579374 is a reply to message #579373] |
Mon, 11 March 2013 23:39 |
syed.samiullah
Messages: 9
Registered: August 2008
Location: Abu Dhabi
|
Junior Member |
|
|
Instance 2
SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS
1 15360 1 2028685 1 383220172
2 7680 0.5 2271924 1.1199 553293284
3 11520 0.75 2137220 1.0535 424416340
4 30720 2 1910212 0.9416 223072462
5 23040 1.5 1910210 0.9416 275075439
6 26880 1.75 1910210 0.9416 223072462
7 19200 1.25 1933743 0.9532 345051443
Instance 1
SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS
1 15360 1 1964138 1 380688461
2 7680 0.5 2148178 1.0937 500224638
3 11520 0.75 2020706 1.0288 420546543
4 30720 2 1814086 0.9236 294386387
5 23040 1.5 1858272 0.9461 306149660
6 26880 1.75 1821740 0.9275 294386387
7 19200 1.25 1906786 0.9708 345398641
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: One job is hanging after SGA increase [message #579511 is a reply to message #579379] |
Tue, 12 March 2013 16:53 |
 |
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
|
Senior Member |
|
|
The number one thing that I would do would to be to watch the queries in action by watching the event waits in another session with the following query.
select sid,serial#,machine,
to_char(s.logon_time,'DD-MON-RR HH24:MI') login,i.instance_name db,
s.seconds_in_wait sec_wait,s.username,s.event,s.status,
s.program,s.machine,s.module,s.terminal
from gv$session s, gv$instance i where i.inst_id=s.inst_id
and s.status='ACTIVE' and s.username is not null
order by seconds_in_wait;
The Second thing that I would do would be to look at the time model to see what the database is spending its time on.
ENDOCP1P > @v$sys_time_model_top_Waits.sql
WAIT_CLASS NAME TIME_SECS PCT
-------------------- ------------------------------ ------------------- ------
Other log switch/archive 50.35 .72
Other enq: WF - contention 52.31 .74
System I/O log file parallel write 68.07 .97
User I/O db file scattered read 78.82 1.12
User I/O direct path read 82.38 1.17
Other reliable message 92.55 1.32
System I/O control file sequential read 106.26 1.51
User I/O db file sequential read 277.91 3.95
System I/O RMAN backup & recovery I/O 2,672.65 38.00
CPU server CPU 3,019.11 42.92
ENDOCP1P > list
1 SELECT
2 wait_class,
3 NAME,
4 ROUND (time_secs, 2) time_secs,
5 ROUND (time_secs * 100 / SUM (time_secs) OVER (), 2) pct
6 FROM
7 (SELECT
8 n.wait_class,
9 e.event NAME,
10 e.time_waited / 100 time_secs
11 FROM
12 v$system_event e,
13 v$event_name n
14 WHERE
15 n.NAME = e.event AND n.wait_class <> 'Idle'
16 AND
17 time_waited > 0
18 UNION
19 SELECT
20 'CPU',
21 'server CPU',
22 SUM (VALUE / 1000000) time_secs
23 FROM
24 v$sys_time_model
25 WHERE
26 stat_name IN ('background cpu time', 'DB CPU'))
27 ORDER BY
28* time_secs
ENDOCP1P > @v$sys_time_model_top_Waits_fore_ground.sql
WAIT_CLASS NAME TIME_SECS PCT
-------------------- ------------------------------ ------------------- ------
User I/O db file scattered read 26.23 .75
System I/O control file sequential read 31.14 .89
Other log switch/archive 50.35 1.44
User I/O direct path read 82.38 2.35
User I/O db file sequential read 91.68 2.62
CPU server CPU 3,019.97 86.20
ENDOCP1P > list
1 SELECT
2 wait_class,
3 NAME,
4 ROUND (time_secs, 2) time_secs,
5 ROUND (time_secs * 100 / SUM (time_secs) OVER (), 2) pct
6 FROM
7 (SELECT
8 n.wait_class,
9 e.event NAME,
10 e.time_waited_fg / 100 time_secs
11 FROM
12 v$system_event e,
13 v$event_name n
14 WHERE
15 n.NAME = e.event AND n.wait_class <> 'Idle'
16 AND
17 time_waited_fg > 0
18 UNION
19 SELECT
20 'CPU',
21 'server CPU',
22 SUM (VALUE / 1000000) time_secs
23 FROM
24 v$sys_time_model
25 WHERE
26 stat_name IN ('background cpu time', 'DB CPU'))
27 ORDER BY
28* time_secs
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
