|
|
| |
 |
|
Automating Oracle
Oracle Tips by Mike Ault |
Automating the Shared Pool Flush
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 Remote DBA_running_stats VALUES (
'Flush of Shared Pool',mem_ratio,35,todays_date,0);
COMMIT;
END IF;
END flush_it;
Figure 3: Flush_it Procedure
The flush_it procedure accepts one input value,
the percentage full at which to flush the pool. When submitted, the
flush_it procedure calculates the amount of shared pool being used
based on values extracted from the V$SGASTAT view and compares this
to the overall size specified for the shared pool in V$PARAMETER. If
the ratio between these two values exceeds the specified percent
full, the DBMS_SQL package is used to issue the ALTER SYSTEM FLUSH
SHARED_POOL command to the database. Once the pool is flushed an
entry is appended into the Remote DBA_RUNNING_STATS table to show that a
flush was initiated.
SEE CODE DEPOT FOR FULL SCRIPTS
 |
For more information on this topic, I recommend Don
Burleson's latest book "Oracle
Tuning: The Definitive Reference".
You can buy it direct from the publisher for 50%-off and get
instant access to the code depot of Oracle tuning scripts: |
http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm
 |
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today. |
 |
|
|
|
|
