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.

What is Tkprof and how is it used?

TKPROF accepts as input a trace file produced by the SQL Trace facility, and it produces a formatted output file. TKPROF can also be used to generate execution plans.

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.

How do I run Tkprof?

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.

How do I Analyse a Tkprof trace file in Oracle?

1. Identify SQL statements that do not use bind variables. …
2. Identify statements that perform full table scans.
3. Identify statements that are doing multiple disk reads or have high CPU consumption.
4. Compare the number of parses and executions.

Where is Tkprof trace file in Oracle Apps?

1. TKPROF Trace File.
2. Once you have obtained the Raw trace file you need to format the file using TKPROF.
3. $tkprof raw_trace_file.trc output_file explain=apps/apps sort=(exeela,fchela) sys=no.
4. output_file: tkprof out file.
5. explain: This option provides the explain plan for the sql statements.

What is TRC file in Oracle?

Database Administrator’s Reference for Microsoft Windows Oracle Database creates a different trace file for each foreground and background thread. The name of the trace file contains the name of the thread, followed by the extension “. trc” .

What is SQL trace?

SQL Trace is SQL Server’s built-in utility that monitors and records SQL Server 6.5 database activity. This utility can display server activity; create filters that focus on the actions of particular users, applications, or workstations; and filter at the SQL command level.

Where is Tkprof located?

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.

Which columns should be indexed in Oracle?

Although you can specify columns in any order in the CREATE INDEX command, the order of columns in the CREATE INDEX statement can affect query performance. In general, you should put the column expected to be used most often first in the index.

Article first time published on

What is fetch in Tkprof?

Fetch count is 2 when there are few number of rows processed by a query. It is 1 when none of the rows get processed by the same query.

How do you check if any trace is enabled in Oracle?

1. sql_trace—Shows (TRUE/FALSE) if SQL tracing has been enabled in the session.
2. sql_trace_waits—If session tracing is enabled, you can have the trace write wait information to the trace file; very useful in diagnosing performance issues.

How do I enable a trace in Oracle session?

You can enable SQL Trace for your own session with the following SQL statement: ALTER SESSION SET SQL_TRACE = TRUE; Alternately, from PL/SQL, you can make the following procedure call: DBMS_SESSION.

How do I use Tkprof in Toad?

This is easiest if you use TOAD. In the TOAD toolbar, click on View. In the drop-down menu, click on “Kill/Trace Session.” Read the program names in the middle of the grid to find the right session. Click on it, and then click on the “Start Trace” button.

What does autotrace do in Oracle?

The autotrace utility is a very underutilized feature of SQL*Plus. It offers statement tracing and instant feedback on any successful SELECT, INSERT, UPDATE or DELETE statement. The autotrace provides instantaneous feedback including the returned rows, execution plan, and statistics.

Where are Oracle trace files located?

Trace files are stored in the Automatic Diagnostic Repository (ADR), in the trace directory under each ADR home.

What is TRM and TRC files in Oracle?

trm files describe the trace records stored inside of . trc trace files. The Trace metadata allows tools, such as Adrci, to post process the trace information. … trc file unusable for post processing from ADR using ADRCI tool.

Can I delete TRC files?

trc files are safe to delete. . trc files generated by SQL Server in process of saving events to a physical file without using the Profiler client tool.

How do I read a TRC file?

To open a TRC file, select File → Open → Trace File, select your Trace file, and click Open.

How do I create a trace file?

1. Run the MS SQL Server Management Studio.
2. Go to Tools > SQL Server Profiler.
3. Provide a name under Trace name.
4. Use the “Standard (default)” template.
5. Click Save to File.
6. Provide the path and filename for the file to be saved.

What is Oracle performance tuning?

Performance tuning is the process of optimizing Oracle performance by streamlining the execution of SQL statements. In other words, performance tuning simplifies the process of accessing and altering information contained by the database with the intention of improving query response times and application operations.

What is Explain Plan in Oracle?

The EXPLAIN PLAN statement displays execution plans chosen by the Oracle optimizer for SELECT , UPDATE , INSERT , and DELETE statements. A statement’s execution plan is the sequence of operations Oracle performs to run the statement.

How do I enable Plustrace roles in Oracle?

Run the following commands from your SQL*Plus session to create the PLUSTRACE role and grant it to the DBA: CONNECT / AS SYSDBA @$ORACLE_HOME/SQLPLUS/ADMIN/PLUSTRCE. SQL drop role plustrace; Role dropped. create role plustrace; Role created. . . . grant plustrace to dba with admin option; Grant succeeded.

What is Oracle trace file analyzer TFA?

Oracle Trace File Analyzer (TFA) provides a number of diagnostic tools in a single bundle, making it easy to gather diagnostic information about the Oracle database and clusterware, which in turn helps with problem resolution when dealing with Oracle Support.

What does NVL function do?

NVL lets you replace null (returned as a blank) with a string in the results of a query. If expr1 is null, then NVL returns expr2 .

Why we use SQL Profiler?

Use SQL Server Profiler Microsoft SQL Server Profiler is a graphical user interface to SQL Trace for monitoring an instance of the Database Engine or Analysis Services. You can capture and save data about each event to a file or table to analyze later.

Why do we use SQL trace?

To collect and monitor events in an instance of SQL Server. A collection of event classes, data columns and filters that identify the types of events to be collected during a trace.

How do I run SQL Profiler?

1. Click on Tools.
2. Click on SQL Server Profiler.
3. Connect to the server on which we need to perform profiling.
4. On the Trace Properties window, under General tab, select the blank template.
5. On the Events Selection tab, select Deadlock graph under Locks leaf.

What is Oracle Bitmap Index?

A bitmap index is a special kind of database index which uses bitmaps or bit array. In a bitmap index, Oracle stores a bitmap for each index key. Each index key stores pointers to multiple rows. For example, if you create a bitmap index on the gender column of the members table.

What is SQL Indexing?

A SQL index is used to retrieve data from a database very fast. Indexing a table or view is, without a doubt, one of the best ways to improve the performance of queries and applications. A SQL index is a quick lookup table for finding records users need to search frequently.

What is B-tree index in Oracle?

The B-tree index is the default index type in Oracle. This index type is known as B-tree because the table row identifier (ROWID) and associated column values are stored within index blocks in a balanced tree- like structure.