Wednesday, 7 December 2011

Using the Oracle Developer Days VirtualBox Image

As many of you know @krisrice put together a great VM for the Oracle Developer Days and while the content is great for education, I find us using it more and more a default scratch database on laptops.  As usual, being 'Networkly challenged', it took me some time to figure out which network adapters did what and why.  This is as much a note for me as it is to share with you :).

First of all, you need to install VirtualBox, which is found here.  Then download one of the prebuilt VirtualBox images for the Developer Days.  You can choose your one here.  Download the ova file and then import your VM into VirtualBox.

To import, choose File> Import Appliance in VirtualBox and click choose to select the directory where you want the VM to live, then select your ova file.  In this example, I'm using @krisrice's Oracle DeveloperDays VM. Once you click finish, and agree to the licenses, you'll see the 'Oracle Developer Days' VM in VirtualBox with a powered off state.

Great.  We're ready to fire up the box and play inside it.  All passwords for this are 'oracle', so you cant go wrong. In this, the database is all setup automatically, as is this list of stuff.

  • Oracle Linux 5
  • Oracle Database 11g Release 2 Enterprise Edition
  • Oracle TimesTen In-Memory Database Cache
  • Oracle XML DB
  • Oracle SQL Developer
  • Oracle SQL Developer Data Modeler
  • Oracle Application Express
  • Oracle JDeveloper
  • Hands-On-Labs (accessed via the Toolbar Menu in Firefox)

So now, login to the vm with oracle/oracle and a terminal window will show you whats available and the network profiles you setup in the VM settings.  The network settings I setup for this is here
These two adapters do separate things.  The bridged adapter will assign an IP address from the wireless NIC.  This is setup by default when you install the VM and allows you to get access to the internet from the VM.  The NAT adapter will allow you to access the VM from the host machine when you have no physical NIC or internet available.  This happened me yesterday when presenting some SQLDeveloper functionality.  By enabling the NAT adapter and setting up a few port forwarding rules, we can ssh into the VM and connect SQL Developer to the normal LISTENER port.
The NAT adapter looks like this.

and setting up two port forwarding rules, gives us this
When you login to the VM, you'll see this on the terminal window.
This setup will allow you to spark up firefox in the VM and connect to the internet using the local network ip  Now, if we switch off the Airport and disable the bridging adapter, we should still be able to connect to the VM from outside.  Restarting the VM, gives us this terminal window on login to the oracle account
Now, we have no external IP, but we have our port forwarding rules set up to get access to the VM from outside.  Now, we have two rules, one which maps anything on port 2222 to port 22 on the guest.  this means we can ssh into the VM on port 2222 on the host. So, connecting with this
and we get the login terminal from the VM
Brilliant.  Now, Lets see SQL Developer connect to the VM too.  We setup the connection like a connection to xe on localhost, except the SID is orcl on the VM.
and we can look at the database version report which shows us what database we are connected to
And now, you're connected and good to go.  This is great for doing demos in a canned environment, especially when you dont have access to an internet connection when doing a demo or showing something off.

Monday, 5 December 2011

Explain Plan, Autotrace and Diff

A SQL statement can be executed in many different ways, such as full table scans, index scans, nested loops, and hash joins. The query optimizer determines the most efficient way to execute a SQL statement after considering many factors related to the objects referenced and the conditions specified in the query. This determination is an important step in the processing of any SQL statement and can greatly affect execution time.

The EXPLAIN PLAN results let you determine whether the optimizer selects a particular execution plan, such as, nested loops join. It also helps you to understand the optimizer decisions, such as why the optimizer chose a nested loops join instead of a hash join, and lets you understand the performance of a query.

You can examine the execution plan chosen by the optimizer for a SQL statement by using the EXPLAIN PLAN button on the worksheet. When the statement is issued, the optimizer chooses an execution plan and then inserts data describing the plan into a database table. SQL Developer looks at the table and displays the tree operations.  Looking at the example, we can see the query is doing a Cartesian product

Similary, when you use autotrace, which, on SQL Developer, is the button beside the explain plan. AND, you can diff two plans to  see what the difference is between two plans, as you can see below.

However, even though we can show you what a plan looks like, it cannot differentiate between well-tuned statements and those that perform poorly.

For example, an EXPLAIN PLAN output that shows that a statement uses an index does not necessarily mean that the statement runs efficiently. Sometimes indexes can be extremely inefficient. In this case, you should examine the following:
  • The columns of the index being used
  • Their selectivity (fraction of table being accessed)
It is best to use EXPLAIN PLAN to determine an access plan, and then later prove that it is the optimal plan through testing. When evaluating a plan, examine the statement's actual resource consumption.

And from the doc!: 

In addition to running the EXPLAIN PLAN command and displaying the plan, you can use the V$SQL_PLAN views to display the execution plan of a SQL statement. After the statement has executed, you can display the plan by querying the V$SQL_PLAN view. V$SQL_PLAN contains the execution plan for every statement stored in the cursor cache. 

The V$SQL_PLAN_STATISTICS view provides the actual execution statistics for every operation in the plan, such as the number of output rows and elapsed time. All statistics, except the number of output rows, are cumulative. For example, the statistics for a join operation also includes the statistics for its two inputs. The statistics in V$SQL_PLAN_STATISTICS are available for cursors that have been compiled with the STATISTICS_LEVEL initialization parameter set toALL.

The V$SQL_PLAN_STATISTICS_ALL view enables side by side comparisons of the estimates that the optimizer provides for the number of rows and elapsed time. This view combines both V$SQL_PLAN and V$SQL_PLAN_STATISTICS information for every cursor.

Anyway, don't take my word for all this, try it on SQLDeveloper today and you can follow Tom Kyte's process for tuning sql statements.