SQL Performance Analyzer Homework Help, Assignment Help, Online Tutor, Tutoring

Submit Assignment

* *

Payment Online Assignment Help

Asp dot net Project Homework

Homework Help

sfsf

SQL Performance Analyzer

In a different installment of this series you learned about Database Replay, that great tool for capturing the real workload in your database and replay them at will. Database Replay is part of what Oracle calls the Real Application Testing option, with the emphasis on the word "real"—the workload replayed is actually what occurred in your database.

In Database Replay, the entire captured workload is replayed against the database. But what if you don't want to do that? For example, you may be interested in understanding how SQL execution plans and therefore SQL performance might be affected by a change, because they can severely affect application performance and availability. In addition, Database Replay replays only what has been captured; not anything else. You may be interested to know the impact of parameter changes on some SQLs that has not been executed in production yet.

This area is where the other important component of the Real Application Testing family—SQL Performance Analyzer (SPA)—shines. SPA allows you to play out some specific SQL or your entire SQL workload against various types of changes such as initialization parameter changes, optimizer statistics refresh, and database upgrades, and then produces a comparison report to help you assess their impact. In this installment, you will learn how to use this tool to answer that important question.

A Sample Problem

Let's go on a test drive. First, let's define the problem you are trying to solve.

The problem is a typical one: Oracle is not using an index, and you want to know why not. To answer that question, I turned to the classic paper by the Oracle guru Tim Gorman, "Searching for Intelligent Life in Oracle's CBO." (You will find versions of this paper in many forms all over the Web.)

One of Tim's suggestions is to change the value of the parameter optimizer_index_cost_adj from the default 100 to something smaller. The paper also gives a formula to calculate what the value should be. Based on that formula, I calculated the value to be 10, in my case. But here comes a difficult question: Is that change going to be beneficial for every SQL statement?

In releases prior to Oracle Database 11g, I have to capture all SQL statements, run them through tracing, and get execution plans—a task not only enormously time consuming but error prone as well. With the new release, I don't need to do that; instead, I have the very simple and effective SQL Performance Analyzer.

First, for the purpose of illustration, run the following queries in the database:

select /* CONTROL_QUERY11 */ sum(QUANTITY_SOLD) from sales where PROMO_ID = 33;
select /* CONTROL_QUERY12 */ sum(QUANTITY_SOLD) from sales where PROMO_ID = 350;
select /* CONTROL_QUERY13 */ sum(QUANTITY_SOLD) from sales where PROMO_ID = 351;
select /* CONTROL_QUERY14 */ sum(QUANTITY_SOLD) from sales where PROMO_ID = 534;
select /* CONTROL_QUERY15 */ sum(QUANTITY_SOLD) from sales where PROMO_ID = 999;

select /* CONTROL_QUERY21 */ sum(QUANTITY_SOLD) from sales where channel_id = 2;
select /* CONTROL_QUERY22 */ sum(QUANTITY_SOLD) from sales where channel_id = 3;
select /* CONTROL_QUERY23 */ sum(QUANTITY_SOLD) from sales where channel_id = 4;
select /* CONTROL_QUERY24 */ sum(QUANTITY_SOLD) from sales where channel_id = 5;
select /* CONTROL_QUERY25 */ sum(QUANTITY_SOLD) from sales where channel_id = 9;

Assume these queries are issued by your application. By pacing the comments in the SQL, you can search for them later. After the statements are executed, you can use SPA on them.