1. No Flash area
Stop wasting performance and disk space on the flash recovery option. There are other and better working ways to restore the accidently deleted or damaged data.
a) Run this command from sqlplus (if on RAC run for each instance):
alter database flashback off; b) reset the DB_RECOVERY_FILE_DEST initialzation parameter to a null string:
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='' SCOPE=BOTH SID='*'; c) and change archive log destination:
alter system set log_archive_dest_1='LOCATION=+FLASH';
or pointing to file system location right away:
alter system set log_archive_dest_1='LOCATION=/cdb/archives';
2. Disable automatic stats
There are database environment where stats and other automated stats related jobs are the only ones showing in top sql. The actual application SQL is not even making it up to the top. Stats gathering jobs could be heavy on the database and cost a lot of performance. Here is what and where to disable or reduce.
a) Set STATISTICS_LEVEL parameter to BASIC. (carefull as you may still want to keep it in TYPICAL instead) If set to BASIC this will disables the collections of:
Automatic Workload Repository (AWR) Snapshots
Automatic Database Diagnostic Monitor (ADDM)
All server-generated alerts
Automatic SGA Memory Management
Automatic optimizer statistics collection
Object level statistics
End to End Application Tracing (V$CLIENT_STATS)
Database time distribution statistics (V$SESS_TIME_MODEL and V$SYS_TIME_MODEL)
Service level statistics
Buffer cache advisory
MTTR advisory
Shared pool sizing advisory
Segment level statistics
PGA Target advisory
Timed statistics
Monitoring of statistics
however some of them (listed above) could be turned back on manually, while keeping general switch at BASIC value. Also before set STATISTICS_LEVEL = BASIC please make sure you disable the Automatic Shared Memory Management (ASMM) feature by unsetting the SGA_TARGET parameter first, otherwise you will get the following error ORA-00830: cannot set statistics_level to BASIC with auto-tune SGA enabled
b) Disable Automatic Statistics Collection
check what you got:
SELECT STATE FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'GATHER_STATS_JOB';
STATE
---------------
SCHEDULED (or could be RUNNING)
To Disable the automatic statistics collection , execute the following procedure:
EXEC DBMS_SCHEDULER.STOP_JOB('GATHER_STATS_JOB');
EXEC DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');
3. Reduce Automatic Workload Repository retention.
a) change AWR retention from default (10 minute snapshots retained for 30 days) to hourly snapshots retained for 1 day:
exec dbms_workload_repository.modify_snapshot_settings(1440, 60);
check with: select * from DBA_HIST_WR_CONTROL;
b) on 11g first do:
exec DBMS_WORKLOAD_REPOSITORY.modify_baseline_window_size(window_size => 1);
after that the statement
exec dbms_workload_repository.modify_snapshot_settings(1440, 60); will work OK.
4. Disable recycle bin.
Run these, while connected as sys:
a) purge dba_recyclebin;
b) alter system set recyclebin =off; (in 10.1 use _recyclebin=FALSE instead)
c) add recyclebin=off to init/spfile.ora
5. Disable auditing
Oracle's audit is enabled by default. this generates lots of audit files on the disk and
eats the space. To enable/disable auditing set: audit_trail=db_extended/none
6. Convert from spfile to init.ora
Some people still think that vi editor is a better way to view active/non-default oracle parameters than using sqlplus from command line. So, if spfile is sitting on +ASM first
a) unlink ifile= from spfile and repoint to init.ora to use local init.ora instead.
this will create init.ora from ASM shared spfile:
echo "SPFILE='+DATA/BOSTON/spfileBOSTON.ora'" > initBOSTON.ora
or:
CREATE PFILE='/appdata1/achpd/initachpd.ora' FROM SPFILE;
7. Get rid of sga_target and replace with regular memory parameters.
any DBA knows how to go manual from automatic when performance gain is crusial.
8. Stop autoextension of datafiles
autoextending of oracle files could be justified only by one's amount of lazziness to maintain static or dynamic-limited file structure with advanced space pre-allocation.
Run this to generate the runnable syntax. And than run generated script:
select 'alter database datafile '||chr(39)||file_name||chr(39)||' autoextend off;'||chr(10)
from dba_data_files where AUTOEXTENSIBLE = 'YES' order by tablespace_name;
Questions/Comments? - feel free to contact me on http://www.sats.net
