Oracle Performance case study on sql_trace being set improperly
My apps system was generating lot of trace files and mount was getting , resulting in I/O issues. we cleaned up the trace files and but again it came backUpon investigation we found that some body has by mistake set the init.ora parameter sql_trace to true .
Because of this every connection to the database was generating trace file.
Once we set it FALSE, trace file creation becomes lessor and it resolve the issue
The sql_trace setting to true also resulted in severe performance issue for the instance. So appropiate care should be taken before setting this parameter. I will always recommend to set this at session level
alter session set sql_trace=true;
If it is still needed to be set at system level then following should be taken care to minimize the performance impact
- Maintaining at least 25% idle CPU capacity
- Maintaining adequate disk space for the USER_DUMP_DEST location
- Striping disk space over sufficient disks
About sql_trace parameter
SQL_TRACE enables or disables the SQL trace facility. Setting this parameter to true provides information on tuning that you can use to improve performance. You can change the value using the DBMS_SYSTEM package