MTS and Memory

Hi,

Quick question about MTS and memory consumption... We have a
production database unfortunately sitting on NT thus were limited to
1.8 gigs of memory (or somesuch).

Because of the high number of connnections we receive on this database
we had to convert to MTS (that is until we can upgrade the OS and
increase our memory allocation..)

If I show the SGA I see:

Total System Global Area 908580228 bytes
Fixed Size 456068 bytes
Variable Size 629145600 bytes
Database Buffers 268435456 bytes
Redo Buffers 10543104 bytes

My large pool is ~400M and shared pool is ~200M. Since we have the
large pool defined user sessions should be grabbing its memory from
there.

Now my question is, since i'm using MTS how far can I bump up my SGA..
Correct me if i'm wrong but only a portion of the PGA will be allocated
outside of the SGA. So are there any formulas which will help me
determine how large my SGA can be? (i'd like to be able to increase my
pools and cache..)

tnx.

On 9 Sep 2004 10:03:55 -0700, "Dave" <david.best@gmail.com> wrote:

>Now my question is, since i'm using MTS how far can I bump up my SGA..

first of all: why do you think using MTS you *should* 'bump the SGA'

Secondly: Oracle's guidelines tell not to use more than one third to
one half of physical RAM.
Also: Winblows will always use 1 G.
If you want paging, go ahead and 'bump the SGA' as far as you can and
face the consequences.
Alternatively, tune your application or replace Winblows for a real
O/S.


--
Sybrand Bakker, Senior Oracle DBA

Sybrand Bakker <gooiditweg@sybrandb.verwijderdit.demon.nl> wrote in
news:mp41k0pjds927314t0o7ij6l3n02ot2cdk@4ax.com:

> On 9 Sep 2004 10:03:55 -0700, "Dave" <david.best@gmail.com> wrote:
>
>>Now my question is, since i'm using MTS how far can I bump up my SGA..
>
> first of all: why do you think using MTS you *should* 'bump the SGA'

Probably because Oracle recommends it with MTS. Since the UGA is
allocated primarily out of the large pool, and that is part of the SGA,
you need to increase the size of the SGA (via the large pool) to handle
those UGAs.

For the OP, there are other ways to increase the amount of memory Oracle
can access too but I've never found it necessary. Check metalink document
46001.1. It's for Windows NT but holds true for Win2k as well. It details
how to allow Oracle to access up to 3g of memory.

Remember too that Windows will use 1g itself.
--
Chuck
Remove "_nospam" to reply by email

Chuck wrote:

> Sybrand Bakker <gooiditweg@sybrandb.verwijderdit.demon.nl> wrote in
> news:mp41k0pjds927314t0o7ij6l3n02ot2cdk@4ax.com:
>
>> On 9 Sep 2004 10:03:55 -0700, "Dave" <david.best@gmail.com> wrote:
>>
>>>Now my question is, since i'm using MTS how far can I bump up my SGA..
>>
>> first of all: why do you think using MTS you *should* 'bump the SGA'
>
> Probably because Oracle recommends it with MTS.

It does no such thing.

> Since the UGA is
> allocated primarily out of the large pool, and that is part of the SGA,
> you need to increase the size of the SGA (via the large pool) to handle
> those UGAs.

And where do you think those UGAs were before you implemented a large pool
(or before implementing MTS, come to that)?

They were with the PGA, or in the shared pool.

So now they're not and they're in the large pool instead.

So tell me where the memory *increase* is in that, as opposed to the memory
re-distribution?

HJR



>
> For the OP, there are other ways to increase the amount of memory Oracle
> can access too but I've never found it necessary. Check metalink document
> 46001.1. It's for Windows NT but holds true for Win2k as well. It details
> how to allow Oracle to access up to 3g of memory.
>
> Remember too that Windows will use 1g itself.

"Howard J. Rogers" <hjr@dizwell.com> wrote in
news:4140adc5$0$27888$afc38c87@news.optusnet.com.a u:

> Chuck wrote:
>
>> Sybrand Bakker <gooiditweg@sybrandb.verwijderdit.demon.nl> wrote in
>> news:mp41k0pjds927314t0o7ij6l3n02ot2cdk@4ax.com:
>>
>>> On 9 Sep 2004 10:03:55 -0700, "Dave" <david.best@gmail.com> wrote:
>>>
>>>>Now my question is, since i'm using MTS how far can I bump up my
>>>>SGA..
>>>
>>> first of all: why do you think using MTS you *should* 'bump the
>>> SGA'
>>
>> Probably because Oracle recommends it with MTS.
>
> It does no such thing.
>
>> Since the UGA is
>> allocated primarily out of the large pool, and that is part of the
>> SGA, you need to increase the size of the SGA (via the large pool) to
>> handle those UGAs.
>
> And where do you think those UGAs were before you implemented a large
> pool (or before implementing MTS, come to that)?

Prior to implementing MTS they were in the dedicated server's memory
space, not part of the sga at all. Or did I miss something that changed
between 8i and 9i?

> They were with the PGA, or in the shared pool.
>
> So now they're not and they're in the large pool instead.
>
> So tell me where the memory *increase* is in that, as opposed to the
> memory re-distribution?

Because when you switched to MTS, they moved out of the dedicated server
and into the SGA. The SGA therefor needs to be bigger.

On 9 Sep 2004 19:29:51 GMT, Chuck <chuckh_nospam@softhome.net> wrote:

>Because when you switched to MTS, they moved out of the dedicated server
>and into the SGA. The SGA therefor needs to be bigger.

The OP already has a 400 M large pool and a 200 M shared pool, and he
still wants to crank up memory. Or didn't you read that bit?


--
Sybrand Bakker, Senior Oracle DBA

What, you never support a large database?

--
----
david.best@sympatico.ca
"Thanks to the remote control I have the attention span of a gerbil!"
"There are 10 types of people in the world. Those who understand binary,
and those who don't."
"Sybrand Bakker" <sybrandb@hccnet.nl> wrote in message
news:fvj1k0lfupbql2adqr1pfogdvfcb8u8uu2@4ax.com...
> On 9 Sep 2004 19:29:51 GMT, Chuck <chuckh_nospam@softhome.net> wrote:
>
> >Because when you switched to MTS, they moved out of the dedicated server
> >and into the SGA. The SGA therefor needs to be bigger.
>
> The OP already has a 400 M large pool and a 200 M shared pool, and he
> still wants to crank up memory. Or didn't you read that bit?
>
>
> --
> Sybrand Bakker, Senior Oracle DBA

> > Chuck wrote:
> >
> >> Sybrand Bakker <gooiditweg@sybrandb.verwijderdit.demon.nl> wrote in
> >> news:mp41k0pjds927314t0o7ij6l3n02ot2cdk@4ax.com:
> >>
> >>> On 9 Sep 2004 10:03:55 -0700, "Dave" <david.best@gmail.com> wrote:
> >>>
> >>>>Now my question is, since i'm using MTS how far can I bump up my
> >>>>SGA..
> >>>
> >>> first of all: why do you think using MTS you *should* 'bump the
> >>> SGA'
> >>
> >> Probably because Oracle recommends it with MTS.
> >
> > It does no such thing.
> >
> >> Since the UGA is
> >> allocated primarily out of the large pool, and that is part of the
> >> SGA, you need to increase the size of the SGA (via the large pool) to
> >> handle those UGAs.
> >
> > And where do you think those UGAs were before you implemented a large
> > pool (or before implementing MTS, come to that)?
>
> Prior to implementing MTS they were in the dedicated server's memory
> space, not part of the sga at all. Or did I miss something that changed
> between 8i and 9i?

No, I simply missed a bit of typing in the rush to submit a post
before running off to the training room at 5.30am. I read the original
post as saying 'Oracle recommends bumping up MEMORY'. You are, of
course, 100% correct that Oracle points out that the *SGA* will need
to get bigger (via the Large Pool).

My point was that although the SGA will get bigger, the amount of
dedicated PGA will decrease, and therefore the overall memory
allocation should stay more or less the same.

> > They were with the PGA, or in the shared pool.
> >
> > So now they're not and they're in the large pool instead.
> >
> > So tell me where the memory *increase* is in that, as opposed to the
> > memory re-distribution?
>
> Because when you switched to MTS, they moved out of the dedicated server
> and into the SGA. The SGA therefor needs to be bigger.

True, and I apologise for leaping in when you were not actually saying
the overall *memory* allocation needs to be bigger.

PGA down, SGA up. True.
Memory increase? Nope. Memory redistributed, true.

Apologies once again,
HJR

"Dave Best" <david.bestNOSPAM@sympatico.ca> wrote in message news:<s370d.29123$lP4.1818416@news20.bellglobal.co m>...
> What, you never support a large database?
>
> --
> ----
> david.best@sympatico.ca
> "Thanks to the remote control I have the attention span of a gerbil!"
> "There are 10 types of people in the world. Those who understand binary,
> and those who don't."

The fact you seem to think large database should also have large SGAs
clearly show you really don't know what you are talking about. Either
*learn* Oracle, or go somewhere else to insult people.

Sybrand Bakker
Senior Oracle DBA

Sybrand Bakker <sybrandb@hccnet.nl> wrote in
news:fvj1k0lfupbql2adqr1pfogdvfcb8u8uu2@4ax.com:

> On 9 Sep 2004 19:29:51 GMT, Chuck <chuckh_nospam@softhome.net> wrote:
>
>>Because when you switched to MTS, they moved out of the dedicated
>>server and into the SGA. The SGA therefor needs to be bigger.
>
> The OP already has a 400 M large pool and a 200 M shared pool, and he
> still wants to crank up memory.

Which is probably more than sufficient for OLTP users. Now if there are
15 or 20 OLAP users on the same DB that would be a different story.

> Or didn't you read that bit?

I read it but since I don't know all the other variables, like how the
DB is being used, I'm not going to make any assumptions. I was simply
trying to make a general statemment that MTS, SSA, or whatever they're
calling it in his version of the RDBMS, requires additional memory in the
SGA to cover those things that are no longer part of a dedicated server
process'es memory space.

--
Chuck
Remove "_nospam" to reply by email

yahoo.com">sybrandb@yahoo.com wrote in
news:a1d154f4.0409100309.5c93e196@posting.google.c om:

> Either *learn* Oracle, or go somewhere else to insult people.

Chill. I think the comment was made tongue in cheek and not intended as
in insult. If you want insults I'd be happy to oblige. :-)

Actually not. I'm trying to behave myself.

--
Chuck
Remove "_nospam" to reply by emai

hjr@dizwell.com (Howard J. Rogers) wrote in message news:<14a1f766.0409091950.31038142@posting.google. com>...

>
> My point was that although the SGA will get bigger, the amount of
> dedicated PGA will decrease, and therefore the overall memory
> allocation should stay more or less the same.

Perhaps he was just looking for something like this:
http://metalink.oracle.com/metalink/...p_id=1012046.6

> Memory increase? Nope. Memory redistributed, true.

True, but the OP was asking if one _should proactively_ increase
memory for MTS. Both you and the ridiculously old metalink document
effectively say "one should measure, then decide."

I have a vague memory of something apropos in an MTS configuration
class I took circa 1995, if I think of it whilst basement-rooting I'll
find the booklet and see.

jg
--
@home is bogus.
http://www.firedetect.noaa.gov/viewer.htm

On 2004-09-09, Sybrand Bakker <gooiditweg@sybrandb.verwijderdit.demon.nl> wrote:
> On 9 Sep 2004 10:03:55 -0700, "Dave" <david.best@gmail.com> wrote:
>
>>Now my question is, since i'm using MTS how far can I bump up my SGA..
>
> first of all: why do you think using MTS you *should* 'bump the SGA'

MTS alters memory usage patterns. His old SGA is probably not sized
appropriately to accomodate the amount of large pool usage the transition to
MTS will trigger.

>
> Secondly: Oracle's guidelines tell not to use more than one third to
> one half of physical RAM.
> Also: Winblows will always use 1 G.
> If you want paging, go ahead and 'bump the SGA' as far as you can and
> face the consequences.
> Alternatively, tune your application or replace Winblows for a real

That's fine if he's the developer. Otherwise, it's likely nonsense.

> O/S.
>
[deletia]
--

vi isn't easy to use. |||
/ | \
vi is easy to REPLACE.