Oracle 19c | How to Enable SQL Trace in Oracle Database Performance Diagnostics | SQL Trace Overview

#oraclef1 #oracle19c #oracledatabase #sql #performance

In this video explained, how to enable SQL trace for own user session, different user session, service level, module level and database instance level in oracle database and how to read trace file using through TKPROF utility.

Introduction:

The SQL trace facility and TKPROF enable you to accurately assess the efficiency of the SQL statements your application runs. For best results, use these tools together rather than using EXPLAIN PLAN alone.

What is SQL Trace and Benefits?

The SQL Trace facility provides performance information on individual SQL statements. It generates the following statistics for each statement:

Parse, execute, and fetch counts
CPU and elapsed times
Physical reads and logical reads
Number of rows processed
Misses on the library cache
Username under which each parse occurred
Each commit and rollback

You can enable the SQL trace facility for a session or for an instance. When the SQL trace facility is enabled, performance statistics for all SQL statements executed in a user session or in an instance are placed into a trace file.

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.

Hope this helps!

Cheers!
Ramesh.

Follow Me On:

WhatsApp Channel: Oracle F1