Tune the Oracle for Performance improvements during Performance testing

Oracle data base Performance improvements during Performance testing.

Let’s assume an instance that you observed multiple execution plans, during performance testing of your application SQL’s, wondering why & how to resolve?  Here I will try to explain why oracle SQL will have multiple execution plans and how it will impact overall performance (response times of your transactions, server throughput, etc...) of your data base.

What is execution plan?

When a SQL statement is executed, the database must convert the query into an execution plan and choose the best way to retrieve the data. For Oracle, each SQL query has many choices for execution plans, including which index to use to retrieve table row, what order in which to join multiple tables together, and which internal join methods to use (Oracle has nested loop joins, hash joins, star joins, and sort merge join methods). These execution plans are computed by the Oracle cost-based SQL optimizer commonly known as the CBO.

The choice of executions plans made by the Oracle SQL optimizer is only as good as the Oracle statistics. To always choose the best execution plan for a SQL query, Oracle relies on information about the tables and indexes in the query.

Once the optimizer has done its job, it provides an execution plan to Oracle. An execution plan is like a set of instructions that tells Oracle how to go and get the data

What is database gather stats and why should I do gather stats frequently on my data base?

Oracle has its own feature to generate the statistics on each table and indexes, CBO (cost based optimizer uses this statistics and make better execution plans for your SQL query so that SQL can be executed quicker).

We should gather stats on data base as and when there are major changes on data base like production data was populated on your test data base, major changes happened to tables and indexes, etc… that time we should gather stats so that better execution plans can be created by CBO. So that we will have better performance.

How to gather data base stats?

Oracle provides a stored procedure (or program) for you to run that will generate the statistics is needs.

Example:

declare

Begin

DBinstancename.wcsgather_Stats('Tablename');

End;

Example2:

From SQL Developer, check when table’s statics are gathered, click on the table. You will be able to see statistics tab.  Check when is last analyzed. Make sure tables are analyzed when there are major changes on your test data base.

 If you want to gather stats how?

Just run the above procedure or from SQL developer below are the steps.

From OEM (Oracle enterprise manager), we can monitor where your SQL has any multiple execution plans.

Snapshot from OEM for Multiple execution plans found for an SQL.

So if you see like this you should first check whether gather stats are done properly, if yes and still you see multiple execution plans than start analysis from SQL perspective.

A snapshot for without any multiple execution plans:

 So finally what I wanted to share on this post is , gather stats are important on database as it will affect your performance test results in terms of SQL speed.

If SQL is retrieving the data slowly because of bad execution plans obviously your transactions response times will effect.

In the next post, I will share more on AWR, ADDM and ASH reports, how to analyze bad SQL’s and fix… wait for my next post J