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

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

I thought it a good idea to write a series of blog posts about some new features of the Oracle 11g database I find useful in an APEX project.

I decided to do a "Top 15". It's very difficult to order them, as for one person or project a feature would be number 1, but for another, it would only be found on place 10. So don't see it as I like the feature I put on place 15 less than the one on place 10. I like all new features ;-)

My research... this top 15 came together after reading:

• The Oracle 11g Database New Features Guide
• Oracle Database 11g: The Top New Features for DBAs and Developers by Arup Nanda
• Tim Hall's articles on Oracle Database 11g
• and Tom Kyte's Top 11 things about 11g.

That was the background...

15 - Virtual Columns

Why would you like to use this feature?

If you've an orders table with the unit price and the quantity, wouldn't it be useful to have the total (= unit price * quantity)?

Or when a store has a price list of items they buy, they add 30% to it to get the price they sell the item too. Or getting the initials of a name. Or seeing the date in a specific format. Or... Having that kind of information already in the table may be of help.

What does Oracle say about it?

Virtual columns enable application developers to define computations and transformations as the column (metadata) definition of tables without space consumption. This makes application development easier and less error-prone, as well as enhances query optimization by providing additional statistics to the optimizer for these virtual columns.

Syntax

Part of the create/alter table statement:

column [datatype] [GENERATED ALWAYS] AS (column_expression) [VIRTUAL] 

[ inline_constraint [inline_constraint]... ]

Example

SQL> CREATE TABLE EMP_WITH_VC (
EMPNO NUMBER(4,0) NOT NULL ENABLE,
ENAME VARCHAR2(10 BYTE),JOB VARCHAR2(9 BYTE),
MGR NUMBER(4,0),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2,0),
NEW_SAL AS (ROUND(sal*1.10,2)),
TOTAL_SAL_COMM NUMBER GENERATED ALWAYS AS (ROUND(sal*1.10+NVL(comm,0),2)) VIRTUAL,
HIRE_YEAR AS (TO_CHAR(hiredate, 'YYYY')),
PRIMARY KEY (EMPNO)
);

SQL> INSERT INTO EMP_WITH_VC (empno, ename, job, mgr, 

hiredate, sal, comm, deptno)
SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno FROM EMP; 

If we query the new EMP with Virtual Column table, we'll see all data of the normal EMP table + the new virtual columns (red): the new salary (10% higher), the total package of the salary and commission and the hire year.

SQL> SELECT * FROM EMP_WITH_VC

I wanted to put the examples online on the new APEX 3.1 instance, but it doesn't seem to run on 11g... (as my creation of my table failed). But as you see on the screenshot, it's running on my local machines.

Behind the Scenes

If you look at the user_tab_columns view you'll notice a column called data_default, which contains the expression of the virtual column.

Other useful information

• An index can be created on a Virtual Column (this would be a function based index)
• You can not do DML (insert, update, delete) on a Virtual Column, but you can use it in a WHERE clause
• Virtual Columns are only available on relational heap tables
• Virtual Column expression work only on the same table, so not cross table and they can't contain other Virtual Columns
• You can use Vitual Columns in Constraints (PK, FK, Check)
  
• You can partition on Virtual Columns !

The full documentation about Virtual Columns can be found here.