|
The scope of this article is to disable some features –mainly related to recoverability and logging, in order to speed up Test databases that are being used to testing application functionality or database features. What we are doing here is similar to what mechanics do when converting a regular car to a race car; they start with getting rid of heavy items and luxuries car parts like passenger seats, AC, anti theft devices in order to make the car lighter in weight and hence faster.
Warning: You should NOT implement the following tips on production databases at any cost, as this will stop the database recoverability features which may lead to data loss in case of system crash or will disable logging mechanisms which can undermine the troubleshooting process of future problems !
Disable Flashback: [Flashback log generation is accounted for 2% to 10% of the overhead on the DB]
SQL> ALTER DATABASE FLASHBACK OFF;
Disable Archivelog mode: [Disabling archivelog mode can boost the overall DB performance significantly]
SQL> shutdown immediate
SQL> startup mount
SQL> alter database noarchivelog;
SQL> alter database open;
Disable Force Logging: [Force logging can put a huge load on Redo Log files even if the database in NOARCHIVELOG mode:
SQL> ALTER DATABASE no force logging;
Disable auditing:
SQL> ALTER SYSTEM SET audit_trail=’NONE’ scope=spfile;
Disable Recycle bin: [Objects will be permanently dropped even when you don’t specify PURGE keyword]
SQL> ALTER SYSTEM SET recyclebin=off deferred SCOPE=BOTH;
Disable block corruption checking mechanism: [Checks database blocks for corruption, it adds extra I/O during DML operations].
SQL> ALTER SYSTEM SET DB_BLOCK_CHECKING=FALSE;
SQL> ALTER SYSTEM SET db_block_checksum=OFF;
SQL> ALTER SYSTEM SET “_dlm_stats_collect”=0 SCOPE=SPFILE;
Disable Block Change Tracking: [A feature that help RMAN incremental level 1 backups run faster]
SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
Disable Automatic Shared Memory Management (ASMM): [Applicable to production as well 👍]
If you know the optimal size for each memory component, then it’s better to set them manually rather than having ASMM to do it for you, trust me; even if don’t figure out the optimal size for memory pools you still can adjust them better than ASMM.
Set the following parameter to the optimal size (as possible): You can consult these views to figure out the optimal sizes (V$DB_CACHE_ADVICE, V$JAVA_POOL_ADVICE, V$STREAMS_POOL_ADVICE, V$SHARED_POOL_ADVICE)
SQL> ALTER SYSTEM SET shared_pool_size=nnnn scope=both;
SQL> ALTER SYSTEM SET “_shared_pool_reserved_pct”=10 scope=spfile;
SQL> ALTER SYSTEM SET large_pool_size=nnnn scope=both;
SQL> ALTER SYSTEM SET java_pool_size=nnnn scope=both;
SQL> ALTER SYSTEM SET db_cache_size=nnnn scope=both;
SQL> ALTER SYSTEM SET streams_pool_size=nnnn scope=both;
Then disable ASMM:
SQL> ALTER SYSTEM SET sga_target=0 scope=both;
Disable STATISTICS LEVEL:
SQL> ALTER SYSTEM SET STATISTICS_LEVEL=BASIC;
Disabling statistics level is very powerful as it will disable the following feature as well:
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
Disable DLM Statistics Collection: [Applicable to production as well 👍]
It’s known to cause ora_scm* process to consume high CPU & Memory: [Doc ID 2373451.1]
SQL> ALTER SYSTEM SET “_dlm_stats_collect”=0 SCOPE=SPFILE;
Maintain only 1 big Redo Log member in each Redo Log group:
Having only one member inside each group reduces the I/Os on the disk, in addition, increasing the size of the REDO LOG file will reduce the frequent Redo Log switches.
Example:
SQL> ALTER DATABASE add logfile group 4 ‘/oradata/ORCL/redo4.log’ size 10g;
SQL> ALTER DATABASE add logfile group 5 ‘/oradata/ORCL/redo5.log’ size 10g;
SQL> ALTER DATABASE add logfile group 6 ‘/oradata/ORCL/redo6.log’ size 10g;
SQL> ALTER DATABASE drop logfile group 1;
SQL> ALTER DATABASE drop logfile group 2;
SQL> ALTER DATABASE drop logfile group 3;
Increase log_buffer parameter: [Applicable to production as well 👍]
SQL> ALTER SYSTEM SET log_buffer=52428800 scope=spfile;
Increase fast_start_mttr_target:
Although increasing fast_start_mttr_target will increase the crash recovery time (instance startup time) when a crash happen, but it can boost the database write activities as it doesn’t need to flush the changes from Redo Logs to the datafiles immediately.
Example: setting fast_start_mttr_target to 5 minutes:
SQL> ALTER SYSTEM SET FAST_START_MTTR_TARGET=300;
Use BIGFILE tablespace instead of default small file:
The performance advantage of Bigfile tablespace over the default small file tablespace is that it decreases the reserved memory which manages the datafiles inside the SGA; in addition, it decreases the I/Os and space inside the controfile as well, especially if the machine that hosts your test database has humble resources.
SQL > CREATE BIGFILE TABLESPACE bigtbs DATAFILE ‘/oradata/ORCL/App_data.dbf’ SIZE 500g;
Set datafiles to AUTOEXTEND OFF: [Applicable to production as well 👍]
Make sure that datafiles have sufficient free space to accommodate new data before setting them to AUTOEXTEND OFF.
Example:
SQL> ALTER DATABASE datafile ‘/oradata/ORCL/users01.dbf’ autoextend off ;
Use only one controlfile:
Avoid multiplexing the controlfile as this will add more I/O overhead to the hosting disk.
Example:
SQL> ALTER SYSTEM SET control_files=’/oradata/ORCL/control1.ctl’ scope=spfile;
Reduce the UNDO RETENTION to the minimum required to support DML operations:
SQL> ALTER SYSTEM SET undo_retention=nnn scope=both;
Disable trace_enabled parameter:
This will stop tracing system problems which is required by Oracle support for debugging.
SQL> ALTER SYSTEM SET trace_enabled=FALSE SCOPE=BOTH;
Turn OFF Listener’s logging:
This supposes to stop writing log entries to the listener’s logfile to reduce the I/O overhead on the underlying filesystem:
Immediate stop of listener log: [doesn’t require to restart the listener but will not be considered if the listener restart]
# lsnrctl set log_status off
Turn OFF Listener’s logging: [permanent, requires a listener restart]
# vi listener.ora
logging_listener = off
# lsnrctl listener stop
# lsnrctl listener start
Turn OFF SQLNET logging:
This supposes to stop writing log entries to the sqlnet’s logfile to reduce the I/O overhead on the underlying filesystem:
# vi sqlnet.ora
LOG_DIRECTORY_CLIENT = /dev/null
LOG_FILE_CLIENT = /dev/null
Stop TFA:
TFA logs all Oracle RAC and OS activities and performance data, it’s mainly getting used by Oracle support to help them understand the status of a RAC node at a specific period of time.
You can stop TFA by executing this command as root user:
# /etc/init.d/init.tfa stop
Finally, If you think that I missed something “and of course there are still tons of tips that can boost the performance” please write it in the comment section.
Remember: Use the above mentioned tips wisely and do NOT implement them on production databases, otherwise you may be required to keep your CV updated 😁
References:
https://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams246.htm#REFRN10219
https://docs.oracle.com/cd/E18283_01/server.112/e17110/initparams129.htm
SQL Tuning Script