 |
|
Oracle Concepts -
General Guidelines for Shared Pool Sizing
Oracle Tips by Burleson Consulting |
Putting it All in
Perspective
So what have we seen so far? We have examined
reports that show both gross and detailed shared pool usage and
whether or not shared areas are being reused. What can we do with this
data? Ideally we will use the results to size our shared pool
properly. Let's set out a few general guidelines for shared pool
sizing:
* Guideline 1: If gross usage of the shared
pool in a non-ad-hoc environment exceeds 95% (rises to 95% or greater
and stays there) establish a shared pool size large enough to hold the
fixed size portions, pin reusable packages and procedures. Increase
shared pool by 20% increments until usage drops below 90% on the
average.
* Guideline 2: If the shared pool shows a
mixed ad-hoc and reuse environment establish a shared pool size large
enough to hold the fixed size portions, pin reusable packages and
establish a comfort level above this required level of pool fill.
Establish a routine flush cycle to filter non-reusable code from the
pool.
* Guideline 3: If the shared pool shows that
no reusable SQL is being used establish a shared pool large enough to
hold the fixed size portions plus a few megabytes (usually not more
than 40) and allow the shared pool modified least recently used (LRU)
algorithm to manage the pool.
In guidelines 1, 2 and 3, start at around 40
megabytes for a standard size system. Notice in guideline 2 it is
stated that a routine flush cycle should be instituted. This flies in
the face of what Oracle Support pushes in their shared pool white
papers, however, they work from the assumption that proper SQL is
being generated and you want to reuse the SQL present in the shared
pool. In a mixed environment where there is a mixture of reusable and
non-reusable SQL the non-reusable SQL will act as a drag against the
other SQL (I call this shared pool thrashing) unless it is
periodically removed by flushing. Source 16 shows a PL/SQL package
that can be used by the DBMS_JOB job queues to periodically flush the
shared pool only when it exceeds a specified percent full.
PROCEDURE
flush_it(p_free IN NUMBER) IS
--
CURSOR get_share IS
SELECT
SUM(a.bytes)
FROM
v$sgastat a
WHERE
a.pool = 'shared pool' AND
a.name <> 'free memory';
--
CURSOR get_var IS
SELECT
value
FROM
v$parameter
WHERE
name = 'shared_pool_size';
--
CURSOR get_time IS
SELECT
sysdate
FROM
dual;
--
todays_date DATE;
mem_ratio NUMBER;
share_mem NUMBER;
variable_mem NUMBER;
cur INTEGER;
sql_com VARCHAR2(60);
row_proc NUMBER;
--
BEGIN
OPEN get_share;
OPEN get_var;
FETCH get_share INTO share_mem;
DBMS_OUTPUT.PUT_LINE('share_mem: '||TO_CHAR(share_mem));
FETCH get_var INTO variable_mem;
DBMS_OUTPUT.PUT_LINE('variable_mem: '||TO_CHAR(variable_mem));
mem_ratio:=share_mem/variable_mem;
DBMS_OUTPUT.PUT_LINE(TO_CHAR(mem_ratio,'99.999')||
' '||TO_CHAR(p_free/100,'99.999'));
IF mem_ratio>p_free/100 THEN
cur:=DBMS_SQL.OPEN_CURSOR;
sql_com:='ALTER SYSTEM FLUSH SHARED_POOL';
DBMS_SQL.PARSE(cur,sql_com,dbms_sql.v7);
row_proc:=DBMS_SQL.EXECUTE(cur);
DBMS_SQL.CLOSE_CURSOR(cur);
OPEN get_time;
FETCH get_time INTO todays_date;
INSERT INTO dba_running_stats VALUES (
'Flush of Shared Pool',mem_ratio,35,todays_date,0);
COMMIT;
END IF;
END flush_it;
Source 16: Example Script to Create a Shared
Pool Flush Routine
The command set to perform a flush on a once
every 30 minute cycle when the pool reaches 95% full would be:
VARIABLE x
NUMBER;
BEGIN
dbms_job.submit(
:X,'BEGIN flush_it(95); END;',SYSDATE,'SYSDATE+(30/1440)?);
END;
/
COMMIT;
(Always commit after assigning a job or the
job will not be run and queued)
There is always a discussion as to whether
this really does help performance so I set up a test on a production
instance where on day 1 I did no automated flushing and on day 2 I
instituted the automated flushing. Figure 7 shows the graphs of
performance indicators and users.
Figure 7: Graphs Showing Effects of Flushing
The thing to notice about the graphs in figure
7 is the overall trend of the performance indicator between day 1 and
day 2. On day 1 (the day with an initial flush as indicated by the
steep plunge on the pool utilization graph followed by the buildup to
maximum and the flattening of the graph) the performance indicator
shows an upward trend. The performance indicator is a measure of how
long the database takes to do a specific set of tasks (from the Q
Diagnostic tool from Savant Corporation). Therefore an increase in the
performance indicator indicates a net decrease in performance. On day
2 the overall trend is downward with the average value less than the
average value from day 1. Overall the flushing improved the
performance as indicated by the performance indicator by 10 to 20
percent. Depending on the environment I have seen improvements of up
to 40-50 percent. At a recent job site the shared pool was sized at
210 megabytes and was filled to 170 megabytes. The response time for
an internally stored complex PL/SQL routine (the running_stats
procedure) was 30 minutes. I flushed the shared pool and response time
dropped to a little over a minute.
One thing that made the analysis difficult was
that on day 2 there were several large batch jobs run which weren?t
run on day 1. The results still show that flushing has a positive
effect on performance when the database is a mixed SQL environment
with a large percentage of non-reusable SQL areas.
Guideline 3 also brings up an interesting
point, you may already have over allocated the shared pool, and in
this case guideline 3 may result in you decreasing the size of the
shared pool. In this situation the shared pool has become a cesspool
filled with nothing but garbage SQL. After allocating enough memory
for dictionary objects and other fixed areas and ensuring that the
standard packages and such are pinned, you should only maintain a few
megabytes above and beyond this level of memory for SQL statements.
Since none of the code is being reused you want to reduce the hash
search overhead as much as possible, you do this by reducing the size
of the available SQL area memory so as few a number of statements are
kept as possible.
This is an excerpt from
the eBook "Oracle
DBA made Simple".
For more details on Oracle
database administration, see the "Easy
Oracle Jumpstart" by Robert Freeman and Steve Karam. It?s
only $19.95 when you buy it directly from the publisher
here.
|