• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Techgoeasy

Techgoeasy

Learn Oracle, PHP, HTML,CSS,Perl,UNIX shell scripts

  • Home
  • Oracle
    • Oracle database
    • Oracle Ebusiness Suite
    • Oracle weblogic
    • Oracle Performance Tuning
    • Oracle Hyperion
    • Oracle Cloud Tutorials
  • SQL
  • interview questions
  • Linux
  • PHP and HTML
  • Downloads
  • General
Home » Oracle » Oracle sql_trace parameter and its impact

Oracle sql_trace parameter and its impact

August 29, 2014 by techgoeasy Leave a Comment

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

Filed Under: Oracle, Oracle Database Tagged With: About sql_trace parameter and its impact, sql_trace

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar



Subscribe to our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email

Recent Posts

  • How to generate sql monitor report in Oracle
  • How to find the long running (longops session) in Oracle
  • How to find concurrent request id from Sid or spid
  • how to check recycle bin in oracle: Purge, enable/disable
  • how to check fra size in oracle: Location, usage, change size

Copyright © 2023 : TechGoEasy

  • Hire me
  • Privacy Policy
  • Contact Us
  • New? Start Here
  • About Us