Skip to content

Expert Oracle Posts

Performance Tuning Basics 5 – Trace and TKPROF – Part 1: Trace

Tracing a SQL statement, generating TKPROF of the trace and understanding it carefully are some key steps that you will have to do when faced with a tough to break performance issue. This post will describe the methodology of this process.

Tkprof is very useful for diagnosing performance issues. It essentially formats a trace file into a more readable format for performance analysis. The DBA can then identify and resolve performance issues such as poor SQL, indexing, and wait events.

[Post Views: 128]

Leave a Comment

Performance Tuning Basics 4 : Bind Variables

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.

[Post Views: 55]

Leave a Comment

Performance Tuning Basics 3: Parent and Child Cursors

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.

Each SQL statement has one Parent cursor and one or more child cursors.Let us understand what a parent and child cursors are.
Please keep in mind Cursor = Memory Area

[Post Views: 55]

Leave a Comment

Performance Tuning Basics 2: Parsing

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

[Post Views: 96]

1 Comment

Oracle EXPDP/IMPDP (DATAPUMP) Monitoring Scripts

Usually we monitor the EXPDP/IMPDP jobs by monitoring the log files generated by expdp/impdp process.  Also we monitor alert log too just in case some error pops up. This helps most of the time. If you have a long running expdp/impdp sessions as you are exporting/importing huge GBs then it helps to have a more detailed monitoring of the expdp/impdp jobs. Some of the useful queries which can be used to monitor the  Data Pump Jobs are mentioned below.

To start with some of the important tables/views that you should refer to monitor Data Pump Jobs are:

[Post Views: 353]

Leave a Comment

EBS Database Parameter Settings Analyzer

All Oracle Apps DBA must have referred below Note id at least once in their career. Most of us refer it  during installation and upgrade of Oracle EBS
application to verify if we have right set if database parameters defined for the optimum performance of EBS application.

Database Initialization Parameters for Oracle E-Business Suite Release 12 (Doc ID 396009.1)

This document has all the required database parameters which are common to all the database release and which are required by EBS application. It also contains release specific database initialization parameters for different releases like 10g, 11g, 12c etc.

[Post Views: 69]

Leave a Comment

Connecting Oracle Database and SQL Server – Basic Concepts

It is required sometime to connect oracle database to SQL Server database using uni-direction or bi-direction methods. Since these are two different database so something as simple as direct DBLINK will not work here. Below are the basic concepts of making connection from SQL SERVER to Oracle and vice-versa.

[Post Views: 59]

Leave a Comment

“Wait for a undo record” OR “Wait for stopper event to be increased” database wait events

We faced a scenario in our database where users were complaining about database performance. Database seems to be hanging and queries were either stuck or not running at all.

It was also seen that UNDO tablespace consumption was increasing.

[Post Views: 259]

Leave a Comment