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
