-- Thanks for the question regarding "Shared Server connection", version 10.2
Submitted on 1-Sep-2009 1:08 Central time zone
Last updated 2-Sep-2009 12:57
You Asked
Hello Tom,
Using shared server conection
where do *_AREA (i.e. Hash Area, Sort Area) located? in UGA or PGA
if it is located in Process PGA then each call to server may be serve by different server process then who would the new process access the PGA of
the previosly serving process?
Thanks
Regard's
and we said...
Well, you are using Oracle 10g
You should not be using *_area parameters, you should be using automatic pga memory management. That works dandy with shared and dedicated servers.
But, in short, look at sort_area_size(SAS) and sort_area_retained_size (SARS).
SARS is the amount of sorted data to keep in memory after the sort is done.
SAS is the amount of memory to use to sort.
SARS <= SAS
Then, for SHARED SERVER
Say SARS = 1mb and SAS = 2mb. During the sort, up to 1mb (SARS) would be allocated in the UGA in the SGA. Up to 1mb (SAS-SARS) would be allocated in the PGA. Only the retained area - which needs to be available after the sort is done and the client starts fetching - needs to be in memory available to the session from call to call.
So, the answer is "a bit in the UGA in the SGA and a bit in the PGA in the process"
But, in short, do not use them - go for automatic pga memory management.
September 1, 2009 - 3pm Central time zone
Reviewer: A reader
Thanks!! for great explanation Sir
One more doubt since we have SARS and SRS for sorting which define which part goes where i.e. (a
bit in UGA and a bit in PGA ) who about Hash/Bitmap Join? it is also partially located in UGA (SGA)
and partially located in PGA for shared server?
Is the same parameter SARS and SRS define the location during Hash/bitmap join?
Many Thanks
Regard's
Followup September 1, 2009 - 6pm Central time zone:
stop going down the manual path - just use automatic pga memory management - seriously...
those without the retained are in the UGA in the SGA
September 2, 2009 - 11am Central time zone
Reviewer: A reader
Thanks Sir for quick reply
<quote>
those without the retained are in the UGA in the SGA
<quote>
So you mean to say hash_area_size is in UGA(i.e. SGA Large pool)
but as per below
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:10837399101938
you said that
<quote>
Now, there are some operations -- like a hash join for example -- that prevent migration.
It used to be that these operations were not permitted via shared server -- but now they
are.
If anything remains in the PGA after a call -- that session will be non-migratable, we
need THAT specific shared server. If the remaining bits of data are safely in the UGA,
that'll be in the SGA and we can migrate.
It is for that reason for example that PGA_AGGREGATE_TARGET is *not* used with shared
server in 9i with shared server, sort_area_size/hash_area_size are, regardless of the
workarea size policy setting.
<quote>
So I am confuse could you please clarify in this regard?
Millions Thanks.
Followup September 2, 2009 - 12pm Central time zone:
exactly, the as per below:
It used to be that these operations were not permitted via shared server --
but now they are.
lends evidence to the placement of the hash area, if it were in the pga, we could NOT use shared server.
IT USED TO BE (past tense) that shared server could not use a hash join - because it was pga based.
It is no longer true (current) hence the data must be available in a place that a migrated session (something you get with shared server all of the time) can have access to - the UGA, in the SGA.
And in 9i, the first release of automatic memory management, shared server was not supported - it uses the sort/hash/bitmap area sizes.
However, you are in 10g, whereby automatic memory management is fully supported
and hence, you should not be using manual.
September 2, 2009 - 1pm Central time zone
Reviewer: A reader
Thanks Tom thats clarify my confusion
|