My Top 15 Oracle DB 11g NF for APEX Developers: 14

This post is part of the series of blog posts about "My Top 15 Oracle Database 11g New Features for APEX Developers". Already published articles: 15-Virtual Columns.

14 - SQL Access Advisor

Why would you like to use this feature?

For some people APEX is a first touch in the Oracle world. People who just start to develop applications may not be that experienced in Oracle yet. They start creating schema's, tables, indexes etc. through the wizard or by copy/pasting from things they have seen.

Unless they've a senior person looking at their code or they hire an external company to get them to the next level, it's not that easy to get recommendations. You can read lots of books or search on the internet, or you could use some features of the Oracle database to help you. Oracle 11g provides a lot of "Advisors", one of them is the SQL Access Advisor.

Maybe you have used SQL Tuning Advisor before? That's nice to tune individual SQL statements, but SQL Access Advisor is even nicer as it looks at a lot more to give you the "right" advice.

What does Oracle say about it?

SQL Access Advisor evaluates an entire workload of SQL and recommend indexes, partitioning, materialized views that will improve the collective performance of the SQL workload.

In other words, where SQL Tuning Advisor looks at one statement, SQL Access Advisor looks at the complete picture. It may be possible that the SQL Tuning Advisor recommends creating an index, but SQL Access Advisor would recommend to not create the index, but to create a materialized view or partition as it looked at the entire workload, including considering the cost of creating and maintaining the index.

Syntax

You would need to create a plsql script which calls the dbms_advisor package or use Enterprise Manager to guide you through the steps of defining the workload and creating for ex. SQL Tuning Sets. More information can be found here.

Example

A very simple example to tune a SELECT statement:

BEGIN
DBMS_ADVISOR.quick_tune(
   advisor_name => DBMS_ADVISOR.SQLACCESS_ADVISOR,
   task_name    => 'emp_dept_quick_tune',
   attr1        => 'SELECT e.ename, d.dname, e.sal FROM emp e, dept d WHERE e.deptno = d.deptno AND e.sal >= 2300 ORDER BY e.sal DESC');
END;
/

I would encourage you to use Enterprise Manager to access SQL Access Advisor. The wizards will guide you to all the necessary steps.

The SQL Access Advisor is located in the Advisor Central - SQL Advisors section of EM.

As I already configured a Quick Tune for my SELECT statement through dbms_advisor, it's listed in the screen of Enterprise Manager.

When you click on the outcome of SQL Advisor Quick Tune, you get a nice graph with what the initial cost was for the select statement and what it could be like if you would implement the recommendation.

Especially the above screen makes it worth to use Enterprise Manager to tune your schema. In my example I only used the SQL Access Advisor to tune one statement (I could also have done it with the SQL Tuning Advisor), but tuning your entire schema isn't that different. You would specify the workload (even an hypothetical workload), the advisor would look at all that information and would give you of course more information as I had with one statement, but the principle is the same.

Behind the Scenes

The following views can be used to display the SQL Access Advisor output without using Enterprise Manager or the get_task_script function: DBA_ADVISOR_TASKS, DBA_ADVISOR_LOG, DBA_ADVISOR_FINDINGS, DBA_ADVISOR_RECOMMENDATIONS

Other useful information

There's another nice example of using SQL Access Advisor written by Arup Nanda here.

Conclusion

Having SQL Access Advisor available doesn't mean a good database design isn't important anymore! I would recommend taking time to get the design right from the start. As the database gets more and more complex, and you're having more and more data, it's nice to have a feature like SQL Access Advisor which can help you to look at the performance.

Maybe this features is more used by DBAs, but I think it's also nice for a developer to sometimes have a look at this. And let's face it, some of us are responsible for everything (dba/development) ;-)