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

Install Microsoft Visual Studio 2010

Install Oracle Database 10g or later

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

Extract these files into your working directory

Open Visual Studio.

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

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

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.

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.

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

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

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

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;

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;

The table was created successfully.

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

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;

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.

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

Select the first finding and click Implement Recommendation.

Select the second finding and click Implement Recommendation.

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

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

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

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.

The index was created successfully.

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;

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

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>;

The index was dropped successfully.

Select File > Open Project. Browse to the OraclePerformanceAnalyzer directory that you have downloaded onto your working directory. Select OraclePerformanceAnalyzer.sln file.

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

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

The project has been built successfully.

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

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

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

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.

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

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

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.

The recommendation is displayed. Click View Report.

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.

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.

The recommendation is displayed. Click View Report.

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.

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

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.

The analysis is displayed again.