We have a new reporting instance [Solaris 8, 8GB RAM, 4 CPUs, 9.2.0.4]
where initially SGA_MAX_SIZE was 2GB, DB_CACHE_SIZE was 32MB,
SHARED_POOL_SIZE was 200MB and PGA_AGGREGATE_TARGET was 100MB.
There are approx 10 to 15 active users at any time.
Without really having any "baseline" we've gone to
DB_CACHE_SIZE=800MB
and PGA_AGGREGATE_TARGET=1GB just about a day ago.
On the OS side, I currently see about 2.6GB free RAM yet.
On the Oracle side :
SQL>
1* select * from v$pgastat
SQL> /
NAME
VALUE UNIT
---------------------------------------- ---------- ------------
aggregate PGA target
parameter
1073741824 bytes
aggregate PGA auto
target
949662720 bytes
global memory
bound
53686272 bytes
total PGA
inuse
18585600 bytes
total PGA
allocated
65071104 bytes
maximum PGA
allocated
237120512 bytes
total freeable PGA
memory
6684672 bytes
PGA memory freed back to
OS
6182862848 bytes
total PGA used for auto
workareas
28672 bytes
maximum PGA used for auto workareas
119459840 bytes
total PGA used for manual
workareas
0 bytes
maximum PGA used for manual
workareas
0 bytes
over allocation
count
0
bytes
processed
2.9389E+10 bytes
extra bytes
read/written
3742183424 bytes
cache hit
percentage
88.7 percent
16 rows selected.
SQL>
SQL> desc v$pga_target_advice
Name
Null? Type
----------------------------------------- --------
----------------------------
PGA_TARGET_FOR_ESTIMATE
NUMBER
PGA_TARGET_FACTOR
NUMBER
ADVICE_STATUS
VARCHAR2(3)
BYTES_PROCESSED
NUMBER
ESTD_EXTRA_BYTES_RW
NUMBER
ESTD_PGA_CACHE_HIT_PERCENTAGE
NUMBER
ESTD_OVERALLOC_COUNT
NUMBER
SQL> set numformat 99,999,999,990.00
SQL> l
1* select * from v$pga_target_advice
SQL> /
PGA_TARGET_FOR_ESTIMATE PGA_TARGET_FACTOR ADV
BYTES_PROCESSED
----------------------- ------------------ --- ------------------
ESTD_EXTRA_BYTES_RW ESTD_PGA_CACHE_HIT_PERCENTAGE
ESTD_OVERALLOC_COUNT
------------------- -----------------------------
--------------------
134,217,728.00
0.13 ON 29,766,417,408.00
20,737,393,664.00
59.00
0.00
268,435,456.00
0.25 ON 29,766,417,408.00
19,436,601,344.00
60.00
0.00
536,870,912.00
0.50 ON 29,766,417,408.00
5,331,615,744.00
85.00
0.00
805,306,368.00
0.75 ON 29,766,417,408.00
4,051,345,408.00
88.00
0.00
1,073,741,824.00
1.00 ON 29,766,417,408.00
3,716,258,816.00
89.00
0.00
1,288,489,984.00
1.20 ON 29,766,417,408.00
3,690,334,208.00
89.00
0.00
1,503,238,144.00
1.40 ON 29,766,417,408.00
3,690,334,208.00
89.00
0.00
1,717,986,304.00
1.60 ON 29,766,417,408.00
3,690,334,208.00
89.00
0.00
1,932,734,464.00
1.80 ON 29,766,417,408.00
3,690,334,208.00
89.00
0.00
2,147,483,648.00
2.00 ON 29,766,417,408.00
3,690,334,208.00
89.00
0.00
3,221,225,472.00
3.00 ON 29,766,417,408.00
3,690,334,208.00
89.00
0.00
4,294,967,296.00
4.00 ON 29,766,417,408.00
3,690,334,208.00
89.00
0.00
6,442,450,944.00
6.00 ON 29,766,417,408.00
3,690,334,208.00
89.00
0.00
8,589,934,592.00
8.00 ON 29,766,417,408.00
3,690,334,208.00
89.00
0.00
14 rows selected.
SQL>
SQL> l
1 SELECT LOW_OPTIMAL_SIZE/1024
low_kb,(HIGH_OPTIMAL_SIZE+1)/1024 high_kb,
2
optimal_executions, onepass_executions, multipasses_executions
3 FROM v$sql_workarea_histogram
4* WHERE total_executions != 0
SQL> /
LOW_KB
HIGH_KB OPTIMAL_EXECUTIONS ONEPASS_EXECUTIONS
------------------ ------------------ ------------------
------------------
MULTIPASSES_EXECUTIONS
----------------------
4.00
8.00
91,590.00
0.00
0.00
8.00
16.00
4,133.00
0.00
0.00
16.00
32.00
3,632.00
0.00
0.00
32.00
64.00
8,038.00
0.00
0.00
64.00
128.00
3,208.00
0.00
0.00
128.00
256.00
2,362.00
0.00
0.00
256.00
512.00
1,015.00
0.00
0.00
512.00
1,024.00
844.00
0.00
0.00
1,024.00
2,048.00
890.00
0.00
0.00
2,048.00
4,096.00
902.00
0.00
0.00
4,096.00
8,192.00
760.00
10.00
0.00
8,192.00
16,384.00
354.00
0.00
0.00
16,384.00
32,768.00
286.00
0.00
0.00
32,768.00
65,536.00
84.00
0.00
0.00
1,048,576.00
2,097,152.00
0.00
2.00
0.00
15 rows selected.
SQL>
Referencing Note 223730.1, do I seem to have hit the "right"
value
for PGA_AGGREGATE_TARGET using my hit-or-miss method without any
baseline figures ?
Hemant
At 01:04 PM 21-01-04 -0800, you wrote:
I think it depends on your
applications.
In DSS type environments we are still stuggling to figure out if P_A_T is
helping or not. Initial
tests are not in P_A_T's favor.
But in another Application, that is 80% OLTP, P_A_T was the only choice
to avoid swapping. This
9.2.0.3 database had the S_A_S set to 2MB (S_A_R_S = 1MB)at the instance
level. It has over 600
persistent users. No MTS in use.
- Kirti
--- ryan.gaffuri@cox.net wrote:
> kirti-- would you recommend avoiding pga_aggregate_target for now?
> >
> > From: Kirtikumar Deshpande
<kirtikumar_deshpande@yahoo.com>
> > Date: 2004/01/21 Wed PM 02:44:31 EST
> > To: Multiple recipients of list ORACLE-L
<ORACLE-L@fatcity.com>
> > Subject: Re: pga_aggregate_target and a memory leak
> >
> > Replies in line...
> >
> > - Kirti
> >
> > --- Jared.Still@radisys.com wrote:
> > > Kirti, you're back!
> >
> > Thanks. Found some slack time from routine DBA work!
> >
> > >
> > > Must have finished the book. :)
> >
> > Not yet.. Its tough..
> >
> >
> >
> > >
> > > Re the PGA problems, what was the value for 'over
allocation count' in
> > > v$pgastat?
> >
> > Actually, I never bothered to look at v$pgastat. Should have..
and will, when we do some more
> > testing next week..
> >
> >
> > >
> > > Did you try increasing P_A_T to a larger number?
> >
> > Yes...
> >
> >
> > >
> > > Oracle is supposed to grab the memory it needs, if
available, regardless
> > > of
> > > the P_A_T setting.
> > >
> > > Also, did your system go in to excessive paging or
swapping?
> >
> > Yes, it did with a large P_A_T.
> >
> >
> > >
> > > I've been curious as to what the effects would be of
having P_A_T too low.
> >
> > I saw more disk sorts..
> >
> > As time permits, I will play with event 10032, 10033 trace for
sorts to see what's going on..
> >
> >
> > >
> > > Oracle is supposed to grab whatever memory it needs.
I'm assuming at this
> > > point that doing so involves a different code path as it
needs to alloc
> > > the memory.
> > >
> > > Don't know what the cost of that is, haven't tried to test
it.
> > >
> > > It seems likely that the OS was out of memory, regardless
of the P_A_T
> > > value.
> > >
> > No. The system has 4 GB of physical memory. Over 2GB was free.
> >
> > > Jared
> > >
> > >
> > >
> > >
> > >
> > >
> > > Kirtikumar Deshpande
<kirtikumar_deshpande@yahoo.com>
> > > Sent by: ml-errors@fatcity.com
> > > 01/21/2004 06:09 AM
> > > Please respond to ORACLE-L
> > >
> > >
> > >
To: Multiple recipients of list ORACLE-L
<ORACLE-L@fatcity.com>
> > > cc:
> > >
Subject: Re:
pga_aggregate_target and a memory leak
> > >
> > >
> > > Setting P_A_T to a 1GB limit with over 2GB of *available
memory* on AIX
> > > 4.3.3 and 9.2.0.4 caused
> > > ORA-4030, till we turned off hash joins. OS level
resources (ulimit -a)
> > > were all set to
> > > 'unlimited'. In a very limited testing, setting P_A_T to
less than S_A_S
> > > (and S_A_R_S) worked,
> > > however, the disk sorts increased. Finally, Developers
chose no hash
> > > joins, 1GB P_A_T and 'AUTO'
> > > workarea_size_policy... seems to run okay...
> > >
> > > - Kirti
> > >
> > >
> > > --- Stephane Faroult <sfaroult@oriole.com>
wrote:
> > > > ryan.gaffuri@cox.net wrote:
> > > > >
> > > > > One of our production DBAs does not want to use
pga_aggregate_target
> > > on a 9.2.0.3 instance due
> > > > to a possible memory leak. The only note on memory
leaks and
> > > pga_aggregate_target I can find on
> > > > metalink is: 334427.995
> > > > >
> > > > > doesnt seem to apply to pga_aggregate_target. We
are on sun solaris.
> > > Dont know version
> > > > offhand.
> > > > >
> > > > > he is under the impression that if we patch to
9.2.0.4 this goes away.
> > > not sure about that
> > > > either...
> > > > >
> > > >
> > > > Be careful with pga_aggregate_target. I have very
recently seen a case
> > > > (Solaris + 9.2 but I cant't tell you exactly which
patch level -
> > > > probably the most recent) where two (by the way
atrocious) queries
> > > > generated by a DSS tool were responding very
differently - and in a way
> > > > that differences in the queries couldn't explain.
>From an Oracle
> > > > standpoint, stats were roughly the same. Tracing
proved that we were
> > > > waiting for CPU, and truss that a call to mmap() was
the culprit. Why,
> > > > no idea. We first switched it (pga_thing) off, no
more slow call to
> > > > mmap(). However, it was still slow because we hadn't
checked
> > > > sort_area_size which was ridiculously small. We set
sort_area_size to
> > > > 10M, still with pga_aggregate_target unset, and once
again the same very
> > > > slow calls to mmap(). Memory misalignment? Anything
else? Not much time
> > > > to enquire but it looks like a mine field.
> > > >
> > > > --
> > > > Regards,
> > > >
> > > > Stephane Faroult
> > > > Oriole Software
> > > > --
> > >
> > >
> > >
> > >
> > >
> >
> >
> > __________________________________
> > Do you Yahoo!?
> > Yahoo! Hotjobs: Enter the "Signing Bonus"
Sweepstakes
> >
http://hotjobs.sweepstakes.yahoo.com/signingbonus
> > --
> > Please see the official ORACLE-L FAQ:
http://www.orafaq.net
> > --
> > Author: Kirtikumar Deshpande
> > INET: kirtikumar_deshpande@yahoo.com
> >
> > Fat City Network Services -- 858-538-5051
http://www.fatcity.com
> > San Diego, California
-- Mailing list and web hosting services
> >
---------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail
message
> > to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru')
and in
> > the message BODY, include a line containing: UNSUB
ORACLE-L
> > (or the name of mailing list you want to be removed
from). You may
> > also send the HELP command for other information (like
subscribing).
> >
>
> --
> Please see the official ORACLE-L FAQ:
http://www.orafaq.net
> --
> Author: <ryan.gaffuri@cox.net
> INET: ryan.gaffuri@cox.net
>
> Fat City Network Services -- 858-538-5051
http://www.fatcity.com
> San Diego, California --
Mailing list and web hosting services
>
---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail
message
> to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and
in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You
may
> also send the HELP command for other information (like
subscribing).
__________________________________
Do you Yahoo!?
Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes
http://hotjobs.sweepstakes.yahoo.com/signingbonus
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.net
--
Author: Kirtikumar Deshpande
INET: kirtikumar_deshpande@yahoo.com
Fat City Network Services -- 858-538-5051
http://www.fatcity.com
San Diego, California --
Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You
may
also send the HELP command for other information (like subscribing).
Hemant K Chitale
Oracle 9i Database Administrator Certified Professional
http://hkchital.tripod.com
{last updated 05-Jan-04}
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hemant K Chitale
INET: hkchital@singnet.com.sg
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Wed Jan 21 2004 - 22:44:25 CST
