When "No", the statistics will be listed each time the statement is executed. Choose this option if you want to perform any advanced analysis of the tkprof output. The default is to enable. The user must specify the schema and table name for the plan table. If the table exists all rows will be deleted otherwise tkprof will create the table and use it. In the example earlier, the contents of the Allsql. Explain Plan is less useful when used in conjunction with tkprof than it is when used alone.
Explain Plan provides the predicted optimizer execution path without actually executing the statement. In addition, running Explain Plan against SQL statements that were captured and saved is always problematic given dependencies and changes in the database environment. This option allows the DBA to view the SQL statements that consume the most resources at the top of the file, rather than searching the entire file contents for the poor performers.
The following are the data elements available for sorting:. Many sort options exist, however some are more useful than others. Execnt , execpu , exedsk and prscnt are the most useful sort parameters when formatting trace output with tkprof because they are more indicative of most SQL performance issues. The execution counts are most indicative of performance issues and therefore should bubble to the top.
The prscnt parameter is important because it shows the SQL statements that are parsed most, usually a result of not using bind variables. The SQL tuning process prior to Oracle 9. This is a very time-consuming and burdensome process. This is a vast improvement over prior versions. These views should be used to periodically check SQL statistics and full-table scans, alerting the DBA to problem areas requiring corrective action.
Best Practices for Using tkprof. Enable tracing only on those sessions that are having problems. Be selective to minimize the performance burden on the sessions and to retain more free space in the user dump destination directory.
Rename trace files after tracing is disabled. The new file name should be something more meaningful that will be recognizable at a later date. Delete trace files that are no longer needed to reduce clutter and free disk space. Explain Plan is not as useful when used in conjunction with tkprof since the trace file contains the actual execution path of the SQL statement.
Use Explain Plan when anticipated execution statistics are desired without actually executing the statement. Therefore, trace with caution and remember to disable tracing after an adequate amount of trace data has been generated.
Oracle provides multiple ways to actually generate the trace file. Feel free to ask questions on our Oracle forum. Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. I prefer to constrain batch jobs to run on a specific node using dedicated connections. For example, lets say the hostname is dwhost Now, let me pause here for a second and bring your attention to how much ground we already covered!
This just shows how much additional know-how you need before you can even touch this tool. Ok, lets continue to the next step of enabling SQL Trace. And now we can finally enable SQL Trace for this session. After the SQL Trace is turned on — Oracle will write out all the waits and calls this session is doing to a trace file on the database server.
In the previous step we turned on the SQL Trace and Oracle is already writing out all the waits and calls this session is doing to a trace file on the database server. The additional overhead of running the SQL Trace facility against an application with performance problems is normally insignificant compared with the inherent overhead caused by the application's inefficiency.
Try to enable SQL Trace only for statistics collection and on specific sessions. If you must enable the facility on an entire production environment, then you can minimize performance impact with the following:.
You can run the TKPROF program to format the contents of the trace file and place the output into a readable output file. 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. With experience or with baselines available, you can assess whether the resources used are reasonable given the work done. When the SQL Trace facility is enabled for an instance, Oracle creates a separate trace file for each process.
Before enabling the SQL Trace facility:. This enables and disables the collection of timed statistics, such as CPU and elapsed times, by the SQL Trace facility, as well as the collection of various statistics in the dynamic performance tables.
The default value of false disables timing. A value of true enables timing. Enabling timing causes extra timing calls for low-level operations. This is a dynamic parameter. It is also a session parameter. When the SQL Trace facility is enabled at the instance level, every call to the server produces a text line in a file in the operating system's file format.
The maximum size of these files in operating system blocks is limited by this initialization parameter. The default is If you find that the trace output is truncated, then increase the value of this parameter before generating another trace file.
This must fully specify the destination for the trace file according to the conventions of the operating system. The default value is the default destination for system dumps on the operating system. Be sure you know how to distinguish the trace files by name.
However, this directory can soon contain many hundreds of files, usually with generated names. It might be difficult to match trace files back to the session or process that created them. You can then trace each file back to the process that created it.
Because running the SQL Trace facility increases system overhead, enable it only when tuning SQL statements, and disable it when you are finished.
The SQL Trace facility is automatically disabled for the session when the application disconnects from Oracle. After the instance has been restarted with the updated initialization parameter file, SQL Trace is enabled for the instance and statistics are collected for all sessions. For more information, see Oracle9i Database Reference. Specifies the input file, a trace file containing statistics produced by the SQL Trace facility. This file can be either a trace file produced for a single session, or a file produced by concatenating individual trace files from multiple sessions.
Specifies whether to record summary for any wait events found in the trace file. Sorts traced SQL statements in descending order of specified sort option before listing them into the output file.
If more than one option is specified, then the output is sorted in descending order by the sum of the values specified in the sort options. Sort options are listed as follows:. Lists only the first integer sorted SQL statements from the output file. This parameter does not affect the optional SQL script.
Choose your interests Get the latest news, expert insights and market research, sent straight to your inbox. Newsletter Topics Select minimum 1 topic. Anonymous June 28, 0 Comments. All, I have got a query which is taking 18 Seconds to get the result. Anonymous Posted June 28, 0 Comments. This file will contain the formatted trace output. All rights reserved. Register or Login.
0コメント