I see that in a comment on the AWR Dilemma that Alex Gorbachev has pointed out the existence of the parameter _awr_restrict_mode as a possible attack on disabling AWR.
For a few more options to disable the monitoring overheads of 10g when you aren’t licensed to use the diagnostic and performance packs, you might also want to investigate the effect of the following parameters – quoted here with their descriptions from x$ksppi.
_addm_auto_enable governs whether ADDM gets run automatically after every AWR snapshot _ash_enable To enable or disable Active Session sampling and flushing _ash_disk_write_enable To enable or disable Active Session History flushing _swrf_mmon_flush Enable/disable SWRF MMON FLushing _swrf_mmon_metrics Enable/disable SWRF MMON Metrics Collection
There are several more related parameters but the selection above appears to enough to eliminate ASH, AWR, and ADDM.
As always, you need to remember that you should not set hidden parameters on production systems without approval from Oracle support. Note especially, I have only run a couple of small tests on the effects of these parameters – so I haven’t looked for any side-effects to changing their defaults.
Oracle Support suggesting us to recreate the AWR using the below steps since our SYSAUX tablespace is keep growing:
alter system set sga_target=0 scope=spfile;
alter system set statistics_level = basic scope=both;
alter system set cluster_database=false;
shutdown immediate
startup restrict
@?/rdbms/admin/catnoawr.sql
alter system flush shared_pool;
@?/rdbms/admin/catsvrm.sql
Did anybody tried this before?
Appriciate any feedback.
Thanks
Padhu
Comment by Padhu — July 7, 2009 @ 4:42 pm UTC Jul 7,2009 |
Padhu,
I’ve not tried it – but if Oracle support is telling you to do it we can only assume that it is considered to be safe.
However, you might want to run a query against dba_extents to check what other objects might be in the tablespace. Presumably they are also telling you to do this so that you can shrink the data files after rebuilding the AWR – but if there is something higher up the tablespace then you won’t be able to shrink the AWR. (And the AWR is larger partitioned tables anyway which drop and create partitions, so will be reusing cleared space over time anyway).
Check the results of this (potentially very slow) query:
select
/*+ rule */
file_id, block_id, segment_name
from
dba_extents
where
tablespace_name = 'SYSAUX'
order by
file_id, block_id
/
Another idea, anyway – one of the biggest objects I have seen in SYSAUX is the optimizer histogram histogram table: wri$_optstat_histgrm_history (I think I’ve mentioned it on the blog or on the OTN forum somewhere). It’s possible that your problem lies with (a) having too many histograms generated by the automatic stats collection, and (b) having a long stats history retention.
Comment by Jonathan Lewis — July 9, 2009 @ 6:54 pm UTC Jul 9,2009 |
Padhu,
You can check the V$SYSAUX_OCCUPANTS View to find out who/what is occupying space in SYSAUX.
Regards,
John
Comment by John Kanagaraj — July 10, 2009 @ 12:45 am UTC Jul 10,2009 |