Wednesday, January 16, 2008

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.


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.


A very simple example to tune a SELECT statement:
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');
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.


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


Anonymous said...


Thanks for the nice post! I did run into bug 6005525 (V$SQL PARSING_SCHEMA_NAME is not populated for schema owners) when running the SQL Tuning Advisor on a statement that was run by an APEX session.

The error I saw in OEM is as follows.
There was a problem creating a SQL tuning task. ORA-13644: The user "" is invalid. ORA-06512: at "SYS.DBMS_SQLTUNE", line 791 ORA-06512: at line 1

See Metalink Note:6005525.8 for the full explanation of that error. In summary the v$sql view does not have the parsing_schema_name column populated when running queries from APEX sessions. The following query showed this was true for my query.
select parsing_schema_name
from v$sql
where sql_id = 'cdjy1aspj45xa';

My work around was to run the same statement from SQL Developer, then run the SQL Tuning Advisor on my statement.


Dimitri Gielis said...

Thanks Todd.

mathewbutler said...

Just leaving a note, as I came across this page whilst researching a received ORA-13644 when using dbms_sqltune on This was bug 605525.

I worked around by flushing the shared pool to obtain a cursor re-parse which left parsing_schema_name populated. This approach was satisfactory as I was using a test system.

It looks like a single cursor may be flushed using the information in Doc ID: 457309.1 which may also result in the population of parsing_schema_name. Although I haven't tried/tested this.

Bug 605525 is stated as fixed in