Tuning .NET Applications in Visual Studio with SQL Tuning Advisor and Oracle Performance Analyzer

Purpose

This tutorial describes how you can use SQL Tuning Advisor to tune ad-hoc SQL statements in Visual Studio. It also shows how you can use Oracle Performance Analyzer to tune your running application's use of the Oracle Database.

Time to Complete

Approximately 20 minutes (Note: 15 of these minutes are simply waiting for performance analysis to complete. During that wait you can work on another lesson)

Topics

This tutorial covers the following topics:

 Overview
 Prerequisites
 Setting Up Your Environment
 Using SQL Tuning Advisor to Tune AdHoc SQL
 Using Performance Analyzer to Analyze Application Performance
 Summary

Viewing Screenshots

 Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: This action loads all screenshots simultaneously, so response time may be slow depending on your Internet connection.)

Note: Alternatively, you can place the cursor over an individual icon in the following steps to load and view only the screenshot associated with that step. You can hide an individual screenshot by clicking it.

Overview

SQL Tuning Advisor and Oracle Performance Analyzer are features of Oracle Developer Tools for Visual Studio. SQL Tuning advisor provides recommendations to improve performance of SQL statements typed into the Oracle Query Window. Oracle Performance Analyzer examines the use of the database over time by a running Visual Studio application and gives recommendations based on the actual workload on the database.

SQL Tuning Advisor requires that the user have the ADVISOR privilege as well as an Oracle Database license for the Oracle Tuning Pack and Oracle Diagnostic Pack.

Oracle Performance Analyzer requires that the user have SYSDBA privileges as well as an Oracle Database license for the Oracle Diagnostic Pack.

Back to Topic List

Prerequisites

Before you perform this tutorial, you should:

1.

Install Microsoft Visual Studio 2005 or 2008

2.

Install Oracle Database 10g or later

3.

Install Oracle 11g Oracle Data Access Components (ODAC) with Oracle Developer Tools for Visual Studio version 11.1.0.7.20 or later from OTN

4.

SQL Tuning Advisor requires that the user have the ADVISOR privilege as well as an Oracle Database license for the Oracle Tuning Pack and Oracle Diagnostic Pack.

5.

Oracle Performance Analyzer requires that the user have SYSDBA privileges as well as an Oracle Database license for the Oracle Diagnostic Pack.

6.

Download and unzip the codesnippets.zip file into your working directory.

Back to Topic List

Setting Up Your Environment

In this section, you will create two data connections for SYS and HR and also add the Advisor privilege to the HR user. Perform the following steps:

1.

Open Visual Studio.

 

2.

Select View > Server Explorer. Right-click Data Connection and select Add Connection.

 

3.

Select ORCL for Data Source Name, enter SYS for Username, <your sys password> for Password and select SYSDBA for Role. Then click OK.

 

4.

Your SYS.ORCL connection was created successfully. You need to make sure that HR user has the ADVISOR privilege so that you can run the SQL Tuning Advisor as the HR user in the next section of this tutorial. Right-click the SYS.ORCL connection and select Privileges

 

5.

Make sure that System Privileges is selected for Object Type. Select HR from the list of Users and select the Grant check box for the ADVISOR privilege and click OK.

 

6.

You want to add one more connection for the HR schema. Right-click Data Connections and select Add Connection.

 

7.

Select ORCL for Data Source Name, enter HR for Username and Password. Then click OK.

 

Back to Topic List

Using SQL Tuning Advisor to Tune AdHoc SQL

In this section, you will tune a SQL statement in the Query Window using the SQL Tuning Advisor. Perform the following steps:

1.

In order to execute SQL, you can use the Query Window. Right-click the HR.ORCL connection and select Query Window.

 

2.

You will create a copy of the EMPLOYEES table. Enter the following SQL statement and click the execute  icon.

create table employees2 as select * from employees;

 

3.

The table was created successfully.

 

4.

You will create a copy of the DEPARTMENTS table. Enter the following SQL statement and click the execute  icon.

create table departments2 as select * from departments;

 

5.

The table was created successfully.

 

6.

In Server Explorer, expand HR.ORCL > Tables to see the new tables in the list.

 

7.

You are now ready to tune some SQL against the EMPLOYEES2 and DEPARTMENTS2 tables. Enter the following query and click the Tune  icon.

select sum(EMPLOYEES2.SALARY) from employees2, departments2
  where employees2.department_id = departments2.department_id;

 

8.

You receive a dialog box asking you if you have a license for the Tuning and Diagnostic Pack. Select the Don't Show this Message again check box and click Yes.

 

9.

A couple of findings are displayed asking you to collect optimizer statistics for the 2 tables in the query.

 

10.

Select the first finding and click Implement Recommendation.

 

11.

Select the second finding and click Implement Recommendation.

 

12.

You want to try to tune the SQL again. Click the Tune  icon.

 

13.

You now receive a recommendation indicating that creating an index on the table would improve the execution plan of the statement. Click View Report.

 

14.

The report is displayed. About half way down, you see the create index statement. Highlight the statement and right-click. Select Copy.

 

15.

Paste the statement in the query window and click the Execute  icon. Make note of the name of the index. You will need this for a later step.

 

16.

The index was created successfully.

 

17.

You can now tune the SQL statement to see if there are any more recommendations. Enter the following SQL in the query window again and click the Tune  icon.

                               
select sum(EMPLOYEES2.SALARY) from employees2, departments2 
  where employees2.department_id = departments2.department_id;
                            

 

18.

This time the Tuning Advisor did not find any recommendations. In other words, the SQL is now tuned.

 

19.

In the next section of this tutorial, you will use the Oracle Performance Analyzer to evaluate the same SQL statement running within an application. In order for the Performance Analyzer to identify some recommendations, you want to delete the index you just created. Enter the following statement in the query window and click the Execute  button.

drop index <name of the index you created earlier>;

 

20.

The index was dropped successfully.

 

Back to Topic List

Using Performance Analyzer to Analyze Application Performance

In this section, you will use the Oracle Performance Analyzer to analyze the application and implement recommendations it finds. Perform the following steps:

1.

Select File > Open Project. Browse to the codesnippets folder that you unzipped from the Prerequisites, and then open the oracleperformanceanalyzer directory inside that. Select the OraclePerformanceAnalyzer.sln file.

 

2.

Double-click program.cs and review the code. Note that it is executing 2 sql statements repeatedly.

 

3.

From the Solutions Explorer window, right-click OraclePerformanceAnalyzer and select Rebuild.

 

4.

The project has been built successfully.

 

5.

You can now run the application. Select Debug > Start Without Debugging.

 

6.

An empty command window appears (the application does not output to the window). Leave this window and switch back to Visual Studio.

 

7.

You run Oracle Performance Analyzer as the SYS user. Right-click SYS.ORCL connection and select Oracle Performance Analyzer.

 

8.

In order to get some recommendations, you need to run the Performance Analyzer for a significant period of time. Change the minutes to 14 and click Start.

 

9.

The timer starts. (While waiting for the timer to count down, you can work on another lesson)

 

10.

When the time is up the results are displayed.

 

11.

Expand Performance Analysis > Findings > Top SQL By DB Time.

 

12.

Expand Recommendation1 and select Action1. Notice that the SQL is one of the SQL statements used in the running application (and is the same SQL that you tuned in the previous section.) Click Tune SQL.

 

13.

The recommendation is displayed. Click View Report.

 

14.

The report indicates that you should create an index. You would perform this in the same way as in the previous section of this tutorial and then run the Oracle Performance Analyzer again. Close the report.

 

15.

You see that there is another Recommendation. Expand Recommendation2 and select Action1. Notice that there is another poorly performing SQL Statement. You want to tune this one as well. Click Tune SQL.

 

16.

The recommendation is displayed. Click View Report.

 

17.

The report indicates that an expensive "UNION" operation was found in the execution plan and you should consider changing it to a UNION ALL. Close the Oracle Performance Analyzer window.

 

18.

The Performance Analyzer results are stored in the database as ADDM Tasks. From the Server Explorer window, expand SYS.ORCL > Schemas > SYS > ADDM Tasks.

 

19.

Select the ADDM task that was most recently created (at the bottom of the list). You can view the analysis you were just viewing by right-clicking the ADDM Task and select View Analysis.

 

20.

The analysis is displayed again.

 

Back to Topic List

Summary

In this tutorial, you learned how to:

 Use SQL Tuning Advisor to tun adhoc SQL
 Use Performance Analyzer to analyze application performance

Back to Topic List

 Move your mouse over this icon to hide all screenshots.

 

Left Curve
Popular Downloads
Right Curve
Untitled Document