There are three ways to look at the SQL Tuning Advisor. We can use Enterprise Manager in a central site and analyze historic data from days, weeks, and months back. Unfortunately, we can not use this in conjunction with Amazon RDS. We can use the Enterprise Manager Express which is part of the database and gives you three hours of history of database performance. Again, we can not use this in conjunction with Amazon RDS. These features are disabled and turned off as part of the Amazon installation. We can use SQL Developer to connect to the database on all platforms. This allows us to pull down real time diagnostics and look at live database performance. We will go through an Oracle by Example SQL Tuning Advisor Tutorial that details how to enable and use the tuning advisor packs. The database version that we will be using is the 11g version of the database. These same steps should work with 12c because the features have not changed and SQL Developer knows what to do between the two versions of the database and present a common user interface to do SQL Tuning.
The first step that we have to do is find out the ip address of our 11g database. We do this by going to the database console and looking at our instance detail.
We then create a connection to the database with SQL Developer. This is done first as the sys user as sysdba connecting to the ORCL instance at the ip address of the database. We can verify that we are connected to a High Performance Edition by issuing a select statement against the v$version table.
select * from v$version;
Before we can execute step 8 in the Tuning Advisor Tutorial we must enable the user scott and set a password for the account. To do this we expand the Other Users selection at the bottom left of the screen, find the user scott, and enable the account while setting the password.
We can now connect to the 11g instance and give user scott permission to attach to the sql resources with the commands
grant advisor to scott;
grant administer sql tuning set to scott;
We then clear the existing statistics to make sure we are not looking at old artifacts but what we are going to execute. This is done by exeucting
exec DBMS_STATS.DELETE_SCHEMA_STATS ('scott');
At this point we switch over to the user scott and execute a select statement
select sum(e.sal), avg(e.sal), count(1), e.deptno from dept d, emp e group by e.deptno order by e.deptno;
We can launch the SQL Tuning Advisor from the icon at the top of the screen. This opens a new tab next to the resulting output from the select statement.
The output from the tuning advisor has four parts. We can look at the statistics that were gathered, look at suggested indexes, sql profile, and restructuring statement recommendations. The index output did not say anything but the other three had recommendations.
The restructuring statement suggests that we remove the dept d definition since we really are not using it in the select statement. We then execute the following modified command
select sum(e.sal), avg(e.sal), count(1), e.deptno from emp e group by e.deptno order by e.deptno;
When we rerun the command without the dept d in the select statement we get a clean output from the SQL Advisor.
In summary, we can use Enterprise Manager, Enterprise Manager Express, or SQL Developer to run the tuning advisor. We walked through a simple example of how to do this with SQL Developer on a single select statement. We walked through the SQL Developer because it works on all cloud platforms and the Enterprise Manager solutions do not work well with Amazon RDS. With these tools we can dive into SQL performance issues, tune the database, and optimize the cloud system to utilize fewer resources and cost us less money. If we can reduce the processor count by a couple of processors that more than pays for the cost of the High Performance Edition incremental cost over the Enterprise Edition.