Frequently Asked Topics in Performance Tuning

 SQL Trace & TKPROF

Summary :
Checklist of items to watch for in tkprof formatted files
Understanding the SQL Trace Facility
Using the SQL Trace Facility and TKPROF
Step 1: Setting Initialization Parameters for Trace File Management
Step 2: Enabling the SQL Trace Facility
Step 3: Formatting Trace Files with TKPROF

• Sample TKPROF Output
• Syntax of TKPROF
• TKPROF Example 1
• TKPROF Example 2

Step 4: Interpreting TKPROF Output

• Tabular Statistics in TKPROF
• Row Source Operations
• Wait Event Information
• Interpreting the Resolution of Statistics
• Understanding Recursive Calls
• Library Cache Misses in TKPROF
• Statement Truncation in SQL Trace
• Identification of User Issuing the SQL Statement in TKPROF
• Execution Plan in TKPROF

Step 5: Storing SQL Trace Facility Statistics
5.1 Generating the TKPROF Output SQL Script
================================================
EXPLAIN PLAN

Summary :
-----------------------------------------------------------------------

Source : Link
 

EXPLAIN PLAN Usage

• Plan Table
• AUTOTRACE - The Easy Option?
• EXPLAIN PLAN
• Statement ID

TRACEONLY option

TRACEONLY EXPLAIN option

EXPLAIN PLAN

utlxplp.sql

 utlxpls.sql.
Statement ID
----------------------------------------------------------
Source :Link

Understanding EXPLAIN PLAN

How Execution Plans Can Change

Different Schemas

Different Costs

Minimizing Throw-Away

Looking Beyond Execution Plans

It is best to use EXPLAIN PLAN to determine an access plan,

Using V$SQL_PLAN Views

EXPLAIN PLAN Restrictions

The PLAN_TABLE Output Table

Example: Creating a PLAN_TABLE

PLAN_TABLE Columns (only important columns)

·         STATEMENT_ID

·         PLAN_ID       

·         TIMESTAMP

·         OPERATION   

·         OBJECT_OWNER

·         OBJECT_NAME

·         OPTIMIZER

·         COST

Displaying PLAN_TABLE Output

·         UTLXPLS.SQL

·         UTLXPLP.SQL

·         DBMS_XPLAN.DISPLAY procedure

==============================================