Category: DATABASE

Oracle’s Cost based optimizer OPTIMIZER_MODE establishes the default behavior for choosing an optimization approach. Choosing the right mode setting can improve many times the average performance of your queries. Optimizer is the mind of the Oracle engine which takes into account many factors like indexes, statistics etc and decide the optimum path to fetch the data and return back to the user session. Oracle provides some parameters which if tweaked can make Optimizer mode to go alternate ways which can be beneficial to your SQL queries. optimizer_mode database parameter is one such useful parameters.

Just like TKPROF, Trace Analyzer (TRCA) tool can help you analyze one or several SQL trace(s) generated by Event 10046 to produce an output diagnostics report which can be used to diagnose a poorly performing SQ statement. TRCA can produce output  in two formats (html and text).

TRCA identifies expensive SQL, generates their explain plan and collects some other useful information like Cost-based Optimizer CBO statistics, metadata, configuration parameters, and similar elements that influence the query performance.

This post is part 4 of the below posts on trace and tkprof. Please refer other posts to read related information.

Part 1: Trace and the methods to generate trace
Part 2: Generating TKPROF from trace file
Part 3: Analyzing TKPROF files
Part 4: Using Trace Analyzer (TRCANLZR) Tool

Analyzing a tkprof file is the key part for a DBA to get to the root of performance issue. This post will describe many useful…

TKPROF output can be generated from a raw SQL Trace. It formats and summarizes the diagnostic information from the raw SQL Trace.TKPROF allows you to analyse a trace file in easy way to determine where time is being spent and what query plans are being used on SQL statements.

This post is part 2 of the below posts on trace and tkprof. Please refer other posts to read related information.

Part 1: Trace and the methods to generate trace
Part 2: Generating TKPROF from trace file
Part 3: Analyzing TKPROF files
Part 4: Using Trace Analyzer (TRCANLZR) Tool

This post will cover Part 2: Generating a tkprof file from trace file.

WHAT DOES A TKPROF FILE CONTAINS:

• SQL Text that were executed
• Timing information: The execution count, elapsed time, CPU time, physical reads (disk), logical reads (query/current) and the number of rows returned for each SQL. This is further broken down into Parse, Execute and Fetch stages.
• Wait information: The times waited, maximum wait and total waited for each database wait event, both for each SQL and for the whole period of the trace. It only does this if the level is 8 (with waits) or 12 (with waits and binds). At least level 8 is recommend because it gives this extra information.
• Execution Plan: The runtime execution plan for each SQL with the actual row source operation counts for each execution plan line. It only does this if the cursor has been closed for the SQL (in 10g and before) or the row source statistics for the SQL have been written to the trace. The execution plan will also contain the actual tables accessed if the SQL uses views or synonyms.

It also contains information like Library cache misses (hard parse) and total SQL and wait statistics for Recursive and Non-recursive SQL Statements

Bind variables are often known as one of the key feature for better SQL query performance. Bind variables as per Oracle documentation is a placeholder in a SQL statement that must be replaced with a valid value or value address for the statement to execute successfully. By using bind variables, you can write a SQL statement that accepts inputs or parameters at run time.

You can think of SQL query as a kind of “function” in any programming language and bind variables as “values” that you pass to the function.

Example:

Select * from EMP where EMP_ID=1;
Select * from EMP where EMP_ID=:a;

First statement uses a literal value (1) to run the query while the second SQL statement uses bind variable (:a) to run the SQL statement. The value of (:a) will be provide to Oracle at run time.

A “cursor” is a memory area in the library cache that is allocated to the SQL statement which users execute. This memory area stores key information about the SQL statement like SQL text, SQL execution plan, statistics etc.

From performance tuning perspectives, it is very important to understand the concept of parsing. Parsing is primary phase in the SQL execution. It is followed by other stages: Execute and Fetch.

PARSING BASICS:

Whenever a SQL statement is executed, Oracle Engine performs below actions on it:

1. Validate the Syntax
2. Validate the objects being referenced in the statement
3. Privileges assigned to user executing the Job
4. Verifying if statement is already available in the shared pool. Oracle engine will calculate the hash value for the SQL statement and look in
5. If statement is not already present then allocate shared memory and create a cursor in shared pool
6. Generate the Execution Plan

Performance tuning is one area where most Junior DBAs face those tough-to-break problems. The kind of problems which require deep understanding of the concepts before…