on 07-28-2013 10:40 AM
Hi,
In one of the production system SYSAUX tablespace is growing rapidly,the statistics retention period for 31 days, but the statistics exists since last year. Optimizer Statistics History size is 77GB.Oracle version is 11.2.0.2.0.
SQL> select trunc(SAVTIME),count(1) from WRI$_OPTSTAT_HISTHEAD_HISTORY group by trunc(SAVTIME) order by 1;
26-JUL-13 | 93263 |
27-JUL-13 | 109740 |
28-JUL-13 | 77648 |
559 rows selected.
The data exists since last year
SQL> select occupant_desc, space_usage_kbytes from v$sysaux_occupants where space_usage_kbytes > 0 order by space_usage_kbytes desc ;
OCCUPANT_DESC SPACE_USAGE_KBYTES
---------------------------------------------------------------- ------------------
Server Manageability - Optimizer Statistics History 81431168
Server Manageability - Automatic Workload Repository 7363072
Server Manageability - Advisor Framework 44288
LogMiner 12544
Server Manageability - Other Components 8896
Transaction Layer - SCN to TIME mapping 3328
SQL Management Base Schema 1728
PL/SQL Identifier Collection 1600
Analytical Workspace Object Table 1536
OLAP API History Tables 1536
Logical Standby 1408
Oracle Streams 1024
Enterprise Manager Monitoring User 896
Unified Job Scheduler 384
Automated Maintenance Tasks 320
15 rows selected.
If we execute the command exec dbms_stats.purge_stats(sysdate-31);
It will delete the statistics older than 31 days.
Please suggest after deleting the old statistics which actions required.
Regards
Chandrashekar
Hi,
I have tried to delete five days statistics files,
but it is taking long time more than a day
Till now only four files deleted.
Please suggest any alternative procedures for quick deletion.
Regards
Chandrashekar
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Chandrashekar,
> Please suggest any alternative procedures for quick deletion.
Are you serious? I already mentioned the three alternative procedures here: http://scn.sap.com/message/14236108#14236108
... you just need to choose one and do it ...
Regards
Stefan
Hi Chandrashekar,
> But i don't have access to oracle support to download the MOS notes.
You don't need MOS access for SQL script awrinfo.sql and parallel DML on the base tables or CTAS (with dependent structures). The script is located in $ORACLE_HOME/rdbms/admin/ and the other stuff has to be done by yourself.
The mentioned enhancement is a patch (also included in SAP SBPs) and can also be verified without MOS access.
Regards
Stefan
Hi,
Check this note for more information on tablespace sysaux
Note 872851 - Oracle Database 10g: SYSAUX Tablespace
This explains the usage and details of sysaux since inception on Oracle 10g.
Available freespace in the tablespace SYSAUX:
SQL> select tablespace_name, total_bytes, total_blocks
from dba_free_space_coalesced
where tablespace_name = 'SYSAUX';
You can use the view V$SYSAUX_OCCUPANTS to monitor the installed components and their current space consumed in the SYSAUX tablespace.
SQL> select OCCUPANT_NAME, SCHEMA_NAME, SPACE_USAGE_KBYTES
from v$SYSAUX_OCCUPANTS
order by space_usage_kbytes;
The largest part of the SYSAUX is usually filled by the Automatic Workload Repository (AWR). The disk space that is required for the AWR depends on various factors, for example
- Snapshot interval
- Historical data retention period
- Number of user sessions
Except for the number of user sessions, you can change these parameters and you can therefore check the space occupied by the AWR and the size of the SYSAUX
Kind Regards,
Johan
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Chandrashekar,
> Please suggest after deleting the old statistics which actions required.
Depends on what you try to achieve? Do you want to reclaim the corresponding space? MOS note #454678.1 describes this procedure for example ... or you can apply bugfix / enhancement #10279045 and use it.
You can also check the mmon trace files for time-outs like "Runtime exceeded 300 seconds". This would explain the very long statistic history, even if the retention period is set to 31 days. (enhancement #14373728 / 11869207).
MOS note #1055547.1 provides even more details about it.
The mmon process is only given 5 minute to complete the automatic purging of optimizer statistics data. If it has not finished in that time then it aborts and does not produce any sort of error message or trace information. Data has built up because excessive amounts have been created due to a rogue programme and the mmon process has spent 5 minutes every day for a year in trying to remove that excess but has failed miserably and not told anybody about it.
So the question should be (if you hit that issue) - why do you generate that amount of data? In my experience it is based on a bad configured statistic job (brconnect) or you collect / maintain a lot of histograms.
Regards
Stefan
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Chandrashekar,
> If we delete the old statistics can we get performance issues
What do you mean? What kind of performance issues? You can try to delete the old data by using the default PL/SQL procedure dbms_stats.purge_stats, but in my experience even the manual purge will never finish until you use the already mentioned enhancement #10279045 or you do it manually by using parallel DML on the base tables or CTAS (with dependent structures).
At first you should find the main driving tables with SQL script awrinfo.sql and then work through this (if the default PL/SQL procedure takes too long).
> What should be the solution for deleting the statistics automatically older than 31days.
Finding the root cause first. You can not apply a solution without knowing the root cause of this issue. The automatism needs to work, but in many cases the application owner or DBA is causing such issues in SAP environments (like already mentioned above).
Regards
Stefan
Hi Chandrashekar,
After deleting old statistics execute the commit statement.
Thanks,
Manas
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
101 | |
13 | |
13 | |
11 | |
11 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.