Home » RDBMS Server » Performance Tuning » One job is hanging after SGA increase (ORACLE 10.2.0.3 HP UX 11.31)
One job is hanging after SGA increase [message #579290] Mon, 11 March 2013 02:37 Go to next message
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 #579294 is a reply to message #579290] Mon, 11 March 2013 02:40 Go to previous messageGo to next message
Michel Cadot
Messages: 59414
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Too many reasons and too few information to answer.

Regards
Michel

Re: One job is hanging after SGA increase [message #579296 is a reply to message #579294] Mon, 11 March 2013 02:42 Go to previous messageGo to next message
syed.samiullah
Messages: 9
Registered: August 2008
Location: Abu Dhabi
Junior Member
Kindly let me know if you need any additional information.
Re: One job is hanging after SGA increase [message #579297 is a reply to message #579296] Mon, 11 March 2013 02:44 Go to previous messageGo to next message
Michel Cadot
Messages: 59414
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
AWR reports from all steps for both cases (last time and now).

Regards
Michel
Re: One job is hanging after SGA increase [message #579306 is a reply to message #579297] Mon, 11 March 2013 03:58 Go to previous messageGo to next message
syed.samiullah
Messages: 9
Registered: August 2008
Location: Abu Dhabi
Junior Member
What part of AWR you need?
Re: One job is hanging after SGA increase [message #579344 is a reply to message #579290] Mon, 11 March 2013 08:34 Go to previous messageGo to next message
BlackSwan
Messages: 22901
Registered: January 2009
Senior Member
It appears that you simply always believe that bigger is better.
Effectively what you did was to increase the size of your car's gas tank & expecting this would make the car go faster.

Which system resource (CPU, I/O, or RAM) is the primary bottleneck for making the report slow?
It appears that RAM is not the bottleneck.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: One job is hanging after SGA increase [message #579371 is a reply to message #579344] Mon, 11 March 2013 23:20 Go to previous messageGo to next message
syed.samiullah
Messages: 9
Registered: August 2008
Location: Abu Dhabi
Junior Member
Thanks for your reply. But Hourly ADDM Report says increase sga by 20 GB some time for 100% impact.
Re: One job is hanging after SGA increase [message #579372 is a reply to message #579371] Mon, 11 March 2013 23:30 Go to previous messageGo to next message
BlackSwan
Messages: 22901
Registered: January 2009
Senior Member
post formatted results just like below

SQL> select * from V_$SGA_TARGET_ADVICE;

  SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS
---------- --------------- ------------ ------------------- -------------------
       213             .75         9710              2.0673              893373
       284               1         4697                   1              365627
       355            1.25         4113               .8756              251332
       426             1.5         4093               .8714              251332
       497            1.75         4093               .8714              251332
       568               2         4093               .8714              251332

6 rows selected.
Re: One job is hanging after SGA increase [message #579373 is a reply to message #579371] Mon, 11 March 2013 23:37 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #579375 is a reply to message #579374] Mon, 11 March 2013 23:42 Go to previous messageGo to next message
syed.samiullah
Messages: 9
Registered: August 2008
Location: Abu Dhabi
Junior Member
FYI in formated way

[Updated on: Mon, 11 March 2013 23:43]

Report message to a moderator

Re: One job is hanging after SGA increase [message #579376 is a reply to message #579375] Mon, 11 March 2013 23:43 Go to previous messageGo to next message
syed.samiullah
Messages: 9
Registered: August 2008
Location: Abu Dhabi
Junior Member
Instance 2 jpg

[Updated on: Mon, 11 March 2013 23:44]

Report message to a moderator

Re: One job is hanging after SGA increase [message #579377 is a reply to message #579376] Mon, 11 March 2013 23:44 Go to previous messageGo to next message
BlackSwan
Messages: 22901
Registered: January 2009
Senior Member

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: One job is hanging after SGA increase [message #579378 is a reply to message #579377] Tue, 12 March 2013 00:04 Go to previous messageGo to next message
syed.samiullah
Messages: 9
Registered: August 2008
Location: Abu Dhabi
Junior Member
Sorry but I am new to this forum hope the JPG can help you in reading in proper formatted way.
Re: One job is hanging after SGA increase [message #579379 is a reply to message #579378] Tue, 12 March 2013 00:07 Go to previous messageGo to next message
BlackSwan
Messages: 22901
Registered: January 2009
Senior Member
The Posting Guidelines tell you how to FORMAT posted data; so READ them & do as described.
Re: One job is hanging after SGA increase [message #579511 is a reply to message #579379] Tue, 12 March 2013 16:53 Go to previous message
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
Previous Topic: Hybrid Columnar Compression
Next Topic: Analyzing non-sequential snapshots in AWR
Goto Forum:
  


Current Time: Wed Oct 22 01:48:10 CDT 2014

Total time taken to generate the page: 0.24825 seconds