For specific inquiries please contact SATS RDBA Services or call 650 288-1701.
With recent versions of Oracle software (starting back from 10.1 and all the way to current 11.2),
Oracle delivers more and more automation in different areas of database techology. While the general
idea is not bad, quite often, it is implemented wrongly on the end-user sites and over-automation
is taxing performance I have seen multiple automated options turned on where they where not needed,
and actually hurting performance. This document summarizes the most common options and talk about
how to unwind them back, delivering control back to DBA.
Stop wasting performance and disk space on the flash recovery option. There are other and better working
ways to restore the accidentally deleted or damaged data.
alter database flashback off;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='' SCOPE=BOTH SID='*';
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';
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.
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
Shared pool sizing advisory
Segment level statistics
PGA Target advisory
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
Disable Automatic Statistics Collection
check what you got:
SELECT STATE FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'GATHER_STATS_JOB';
SCHEDULED (or could be RUNNING)
To Disable the automatic statistics collection , execute the following procedure:
exec dbms_workload_repository.modify_snapshot_settings(1440, 60);
select * from DBA_HIST_WR_CONTROL;
after that the statement
exec DBMS_WORKLOAD_REPOSITORY.modify_baseline_window_size(window_size => 1);
exec dbms_workload_repository.modify_snapshot_settings(1440, 60); will work OK.
Run these, while connected as sys:
alter system set recyclebin =off; (in 10.1 use _recyclebin=FALSE instead)
add recyclebin=off to init/spfile.ora
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
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
unlink ifile= from spfile and repoint to init <SID>.ora to use local init <SID>.ora instead.
this will create init.ora from ASM shared spfile:
echo "SPFILE='+DATA/BOSTON/spfileBOSTON.ora'" > initBOSTON.ora
CREATE PFILE='/appdata1/achpd/initachpd.ora' FROM SPFILE;
any DBA knows how to go manual from automatic when performance gain is crusial.
autoextending of oracle files could be justified only by one's amount of laziness 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 Cerberus (which is me) on
RDBA.net forums under Oracle DBA corner.