Prev: Any recommendations for Enterprise password management tool?
Next: Business Analyst / New York, New York
From: zigzagdna on 25 Jun 2010 08:58


I have setup sql plan management using sqlmanagement base. Set
following parameters to control space:
exec dbms_spm.configure('plan_retention_weeks', 5);
exec DBMS_SPM.configure('space_budget_percent', 5);

Second parameter as far as I know only gives warning messages in alert
log, but does not reduce space.
First parameter I found cannot be set lower than 5 weeks. Is there
any way to reduce it to purge space?
Is there anyway to purge plans?



My sysaux tablespace keeps growing, it is 11GB now. Most of the space
is occupied by SQL Management Base. How can I free up this space?
Fri Jun 25 08:33:32 2010
SQL Management Base Schema
9089.9375
SYS

Server Manageability - Automatic Workload Repository
321.1875
SYS

Enterprise Manager Repository
182.6875
SYSMAN

XDB
97.25
XDB

Server Manageability - Advisor Framework
80.1875
SYS

Server Manageability - Optimizer Statistics History
28.8125
SYS

Oracle Spatial
22.75
MDSYS

Oracle interMedia ORDSYS Components
13.8125
ORDSYS

LogMiner
7.625
SYSTEM
*** number of plans ***
732467

From: Mark D Powell on 26 Jun 2010 14:07
On Jun 25, 8:58 am, zigzagdna <zigzag...(a)yahoo.com> wrote:
> I have setup  sql  plan management using  sqlmanagement base. Set
> following parameters to control space:
> exec dbms_spm.configure('plan_retention_weeks', 5);
> exec DBMS_SPM.configure('space_budget_percent', 5);
>
> Second parameter as far as I know only gives warning messages in alert
> log, but does not reduce space.
>  First parameter I found cannot be set lower than 5 weeks. Is there
> any way to reduce it to purge space?
> Is there anyway to purge plans?
>
> My sysaux tablespace keeps growing, it is 11GB now. Most of the space
> is occupied by SQL Management Base. How can I free up this space?
> Fri Jun 25 08:33:32 2010
> SQL Management Base Schema
> 9089.9375
> SYS
>
> Server Manageability - Automatic Workload Repository
> 321.1875
> SYS
>
> Enterprise Manager Repository
> 182.6875
> SYSMAN
>
> XDB
> 97.25
> XDB
>
> Server Manageability - Advisor Framework
> 80.1875
> SYS
>
> Server Manageability - Optimizer Statistics History
> 28.8125
> SYS
>
> Oracle Spatial
> 22.75
> MDSYS
>
> Oracle interMedia ORDSYS Components
> 13.8125
> ORDSYS
>
> LogMiner
> 7.625
> SYSTEM
> *** number of plans ***
>     732467

The SQL Management Base Schema size can be controlled via the
dbms_spm.configure procedure.

Oracle support note "General Guidelines for SYSAUX Space Issues
#552880.1" may also be of use.

This same question was posted to OTN:
http://forums.oracle.com/forums/message.jspa?messageID=4388072#4388072

HTH -- Mark D Powell --
 | 
Pages: 1
Prev: Any recommendations for Enterprise password management tool?
Next: Business Analyst / New York, New York