MARKS TUTORIAL

Various Ways to SQL_Trace   SQL_TRACE enables the logging of all the SQL your application performs, performance statistics regarding the execution of that SQL, and the query plans your SQL actually used. Check the Environment Before tracing using SQL_TRACE, the environment must first be configured by performing the following steps: • Enable Timed Statistics – This parameter enables the collection of certain vital statistics such as CPU execution time, wait events, and elapsed times. The resulting trace output is more meaningful with these statistics. The command to enable timed statistics is: ALTER SESSION SET TIMED_STATISTICS = TRUE; OR ALTER SYSTEM SET TIMED_STATISTICS = TRUE; • Check the User Dump Destination Directory – The trace files generated by Oracle can be numerous and large. These files are placed by Oracle in the user_dump_dest directory as specified in the init.ora. The user dump destination can also be specified for a single session using the alter session command.

Using SQL_Trace and TKPROF SQL_TRACE, TIMED_STATISTICS, and TKPROF are some of many tools that Oracle provides to know where the performance issue in a system lies. In a nutshell, SQL_TRACE enables the logging of all the SQL your application performs, performance statistics regarding the execution of that SQL, and the query plans your SQL actually used. Check the Environment Before tracing using SQL_TRACE, the environment must first be configured by performing the following steps: Enable Timed Statistics – This parameter enables the collection of certain vital statistics such as CPU execution time, wait events, and elapsed times. The resulting trace output is more meaningful with these statistics. The command to enable timed statistics is: ALTER SESSION SET TIMED_STATISTICS = TRUE; OR ALTER SYSTEM SET TIMED_STATISTICS = TRUE; Check the User Dump Destination Directory – The trace files generated by Oracle can be numerous and large. These files are placed by Oracle in the user

TKProf Utility TKProf ( an acronym for the T ransient K ernel PROF ile ) is an Oracle  database utility used to format SQL Trace output into human  readable format. The TKProf executable is located in  the ORACLE_HOME/bin directory. TKPROF reports each statement executed with the resources it  has consumed, the number of times it was called, and the number  of rows which it processed. This information lets you easily  locate those statements that are using the greatest resource.  experience or with baselines available, you can assess whether  the resources used are reasonable given the work done. You can ran a query to get your SPID, (server process ID) –  this is very useful in identifying the trace file.  Finally you can get the trace file in the directory on  the database server specified by the USER_DUMP_DEST init.ora parameter. You can retrieve the value of this parameter online by querying  the V$PARAMETER view or by issuing sh