RE: Max SGA size

RE: Max SGA size

 

  

I will post part of statspack report for your info
Pls let me know if you need more info
Many thanks
Kirpal Chana


Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 2,560M Std Block Size: 8K
Shared Pool Size: 512M Log Buffer: 2,048K

Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 88,032.72 8,464.36
Logical reads: 8,700.67 836.57
Block changes: 212.29 20.41
Physical reads: 1,494.89 143.73
Physical writes: 26.09 2.51
User calls: 1,577.75 151.70
Parses: 455.58 43.80
Hard parses: 9.15 0.88
Sorts: 7.38 0.71
Logons: 0.10 0.01
Executes: 463.24 44.54
Transactions: 10.40

% Blocks changed per Read: 2.44 Recursive Call %: 6.79
Rollback per transaction %: 7.02 Rows per Sort: 1637.40

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.29 Redo NoWait %: 99.99
Buffer Hit %: 82.97 In-memory Sort %: 99.98
Library Hit %: 98.22 Soft Parse %: 97.99
Execute to Parse %: 1.65 Latch Hit %: 99.69
Parse CPU to Parse Elapsd %: 83.92 % Non-Parse CPU: 92.16

Shared Pool Statistics Begin End
------ ------
Memory Usage %: 96.95 94.96
% SQL with executions>1: 37.77 79.79
% Memory for SQL w/exec>1: 4.34 83.72

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~
%Total
Event Waits Time (s)
ElaTime
-------------------------------------------- ------------
-------------------
CPU time 69,248
44.72
db file sequential read 66,628,606 58,466
37.76
buffer busy waits 5,229,724 6,432
4.15
db file scattered read 6,867,927 5,889
3.80
async disk IO 355,778 4,066
2.63
-------------------------------------------------------------






-----Original Message-----
From: caffeine-addict
[mailto:oracledba-ezmlmshield-x32066528.[Email address protected]
Sent: 06 September 2007 04:52
To: LazyDBA Discussion
Subject: Re: Max SGA size


I'll need more info to determine really if that's truly the performance
problem. Probably the SQL (85% of the time it is!)
LS

"640KB ought to be enough for anybody." - Bill Gates, 1981



----- Original Message ----
From: Chana Kirpal <oracledba-ezmlmshield-x41485822.[Email address
protected]
To: LazyDBA Discussion <[Email address protected]
Sent: Wednesday, September 5, 2007 10:16:20 AM
Subject: Max SGA size


Hi Gurus

Users on one of my databases are complaining about the performance. The
spec is 4 processor Solaris with 8GB memory. Oracle 9206 (32 bit)
Database buffer = 2560M SGA = 3.3 GB Unix kernel parameter shmmax =
4096MB

Facts as I know Max shhmax = 4096 MB
Max SGA size for 32bit is between 3.5- 3.75GB

In your expert opinion - What is the maximum size for SGA for this
instance.? Many thanks Kirpal


************************************************************************
*************
The message is intended for the named addressee only and may not be
disclosed to or used by anyone else, nor may it be copied in any way.

The contents of this message and its attachments are confidential and
may also be subject to legal privilege. If you are not the named
addressee and/or have received this message in error, please advise us
by e-mailing [Email address protected] and delete the message and any
attachments without retaining any copies.

Internet communications are not secure and COLT does not accept
responsibility for this message, its contents nor responsibility for any
viruses.

No contracts can be created or varied on behalf of COLT
Telecommunications, its subsidiaries or affiliates ("COLT") and any
other party by email Communications unless expressly agreed in writing
with such other party.

Please note that incoming emails will be automatically scanned to
eliminate potential viruses and unsolicited promotional emails. For more
information refer to www.colt.net or contact us on +44(0)20 7390 3900.



---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html


---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html




*************************************************************************************
The message is intended for the named addressee only and may not be disclosed to or used by anyone else, nor may it be copied in any way.

The contents of this message and its attachments are confidential and may also be subject to legal privilege. If you are not the named addressee and/or have received this message in error, please advise us by e-mailing [Email address protected] and delete the message and any attachments without retaining any copies.

Internet communications are not secure and COLT does not accept responsibility for this message, its contents nor responsibility for any viruses.

No contracts can be created or varied on behalf of COLT Telecommunications, its subsidiaries or affiliates ("COLT") and any other party by email Communications unless expressly agreed in writing with such other party.

Please note that incoming emails will be automatically scanned to eliminate potential viruses and unsolicited promotional emails. For more information refer to www.colt.net or contact us on +44(0)20 7390 3900.


Oracle LazyDBA home page