TKPROF determines execution plans by issuing the EXPLAIN PLAN statement after connecting to Oracle with the user and password specified in this parameter. The specified user must have CREATE SESSION system privileges. TKPROF takes longer to process a large trace file if the EXPLAIN option is used. RECORD.

What is Tkprof in Oracle with example?

TKPROF determines execution plans by issuing the EXPLAIN PLAN statement after connecting to Oracle with the user and password specified in this parameter. The specified user must have CREATE SESSION system privileges. TKPROF takes longer to process a large trace file if the EXPLAIN option is used. RECORD.

How do you read a Tkprof output?

  1. count: It is the number of times a call (parse/execute/fetch) was performed.
  2. cpu: Total CPU time in seconds.
  3. elapsed: Total elapsed time in seconds.
  4. disk: Total number of data blocks physically read from the data files on disk.
  5. query: Total number of buffers retrieved in consistent mode.

What is an explain plan in Oracle?

The Oracle explain plan is a statement that returns execution plans for the requested SELECT, UPDATE, INSERT and DELETE statements. The execution plan for any given statement shows the operations in sequence used by Oracle to run that statement.

What is reduce cost in Explain plan in Oracle?

The cost of the plan is reduced by rewriting the SQL statement to use an EXISTS . This plan is more effective, because two indexes are used to satisfy the predicates in the parent query, thus returning only a few employee_ids . The employee_ids are then used to access the orders table through an index.

What is Tkprof utility?

TKProf 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.

How do I run a Tkprof command?

Oracle : TKPROF simple steps

  1. Set the following parameters. TIMED_STATISTICS = TRUE.
  2. Enable SQL TRACE for a session. alter session set SQL_TRACE true;
  3. Run the query.
  4. obtain the number included in the trace filename.
  5. Run TKPROF at the command line to put the TRACE file into readable format.

What is query in Tkprof?

Query – The number of buffers retrieved for all parse, execute, or fetch calls. ยท Current – The number of buffers retrieved in current mode (INSERT, UPDATE, or DELETE statements). Observe from the tkprof output above that the SQL statement performed a TABLE ACCESS FULL, meaning a full-table scan.

Where is Tkprof trace file in Oracle Apps?

  1. Responsibility: System Administrator.
  2. Navigate: Concurrent > Program > Define.
  3. Query Concurrent Program.
  4. Select the Enable Trace Checkbox.

How do you use explain plan?

To explain a SQL statement, use the EXPLAIN PLAN FOR clause immediately before the statement. For example: EXPLAIN PLAN FOR SELECT last_name FROM employees; This explains the plan into the PLAN_TABLE table.

What is explain plan and execution plan in Oracle?

An explain plan predicts how Oracle will process your query. An execution plan describes the steps it actually took. Just as in the driving example above, Oracle may use a different route than the one it predicted.

What is explain plan cost?

Cost is the estimated amount of work the plan will do. A higher cardinality => you’re going to fetch more rows => you’re going to do more work => the query will take longer. Thus the cost is (usually) higher. All other things being equal, a query with a higher cost will use more resources and thus take longer to run.

What is cardinality in Explain plan Oracle?

the estimated number of rows
The cardinality is the estimated number of rows that will be returned by each operation. The Optimizer determines the cardinality for each operation based on a complex set of formulas that use both table and column level statistics as input (or the statistics derived by dynamic sampling).

What is tkprof in Oracle?

TKProf 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. Click to see full answer.

How can I use tkprof to analyze SQL statements?

You can run the TKPROF program to format the contents of the trace file and place the output into a readable output file. Optionally, TKPROF can also determine the execution plans of SQL statements create a SQL script that stores the statistics in the database

What are the components of a tkprof output file?

Each tkprof output file contains a header, body, and summary section. The header simply displays the trace file name, definitions, and sort options selected. The body contains the performance metrics for SQL statements. The summary section contains an aggregate of performance statistics for all SQL statements in the file.

How do I get the execution plan from tkprof?

If you specify the EXPLAIN parameter on the TKPROF command line, TKPROF uses the EXPLAIN PLAN command to generate the execution plan of each SQL statement traced. TKPROF also displays the number of rows processed by each step of the execution plan.