Oracle Execution Plans

Oracle Execution Plans

I have the privilege of working with a really great DB architect who taught me a superior method for viewing the detailed execution plan used to satisfy an Oracle query. I'm posting the details here for anyone else that needs to get such a detailed execution plan.

-- configure sqlplus output
set linesize 120
set pagesize 100
set serveroutput off

-- alter session to grab all stats for execution plan
alter session set statistics_level = 'ALL';

-- run your query of interest here
-- SELECT * FROM ???

-- revert your session
alter session set statistics_level = 'TYPICAL';

-- output the execution plan
select * from table (dbms_xplan.display_cursor(null, 0, 'ALLSTATS'));

Here's a sample of what you'll see when viewing execution plans using the strategy outline above:

SQL> -- output the execution plan
SQL> select * from table (dbms_xplan.display_cursor(null, 0, 'ALLSTATS'));

PLAN_TABLE_OUTPUT                                                                                                       
------------------------------------------------------------------------------------------------------------------------
SQL_ID  bj0mxk7kf0dyn, child number 0                                                                                   
-------------------------------------                                                                                   
WARNING: User has no SELECT privileges on V$SQL                                                                         
                                                                                                                        
Plan hash value: 2024250754                                                                                             
                                                                                                                        
---------------------------------------------------------------------------------------------------                     
| Id  | Operation             | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |                     
---------------------------------------------------------------------------------------------------                     
|   1 |  NESTED LOOPS         |                 |      1 |      2 |      2 |00:00:00.01 |       6 |                     
|   2 |   INDEX FAST FULL SCAN| SYS_C0046475723 |      1 |      2 |      2 |00:00:00.01 |       4 |                     
|*  3 |   INDEX UNIQUE SCAN   | SYS_C0046475720 |      2 |      1 |      2 |00:00:00.01 |       2 |                     
---------------------------------------------------------------------------------------------------                     
                                                                                                                        
Predicate Information (identified by operation id):                                                                     
---------------------------------------------------                                                                     
                                                                                                                        
   3 - access("B"."FOO_ID"="F"."FOO_ID")                                                                     

There are a few things you should note in the execution plan above:

• The Operation column shows you, at a high level, what sorts of operations were used to satisfy your query. You'll want to be on the look out for slow operations, like full table scans.
• A large discrepancy between the estimated (E-Rows) and actual (A-Rows) rows can often reveal that oracle selected a poor execution plan because of stale statistics. In such cases, you may get some relief by running the ANALYZE TABLE command to recalculate table statistics.

Hello, jQuery Form Plugin

Hello, jQuery Form Plugin

Lately I've been working on a lot of ajaxy projects and have found the jQuery Form Plugin to be indispensable for such work. It's a super clean plugin for applying ajaxy behaviors to a regular ol' HTML form. To showcase the plugin here on my blog, I wrote a little "Hello, World" snippet that uses the plugin to pull images from Flickr's image feed.

HTML Markup

First, let's get our markup on. We'll build a vanilla HTML form that asks the user for a tag to be searched on Flickr. After the form, we'll also declare an empty <div> that will eventually be updated with the results of our Flickr tag search.

<form id="flickrSearchForm" method="get" action="">
    <label for="tags">Photos tagged with </label>
    <input id="tags" name="tags" placeholder="any Flickr tag" />
    <input type="submit" value="Find" />
</form>

<div id="flickrSearchResults"></div>

jQuery Dependencies

Next we need to include jQuery and the jQuery Form Plugin. The base jQuery library is available via Google's CDN. The form plugin is hosted on github.

<script type="text/javascript" 
 src="https://ajax.googleapis.com/ajax/libs/jquery/1.4.4/jquery.min.js"></script>
<script type="text/javascript" 
 src="http://malsup.github.com/jquery.form.js"></script>

Unobtrusive Goodness

Now we can finally get down to the business of unobtrusively wiring some ajax operations into the otherwise unremarkable HTML form.

// apply form plugin
jQuery('#flickrSearchForm').ajaxForm({
 url: 'http://api.flickr.com/services/feeds/photos_public.gne',
 data: { 
  tagmode: 'any', 
  format: 'json', 
  jsoncallback: 'displayFlickrSearchResults'
 },
 type: 'get',
 dataType: 'script',
 beforeSubmit: function(formDataArray, form, options) { 
  disableSubmitAndClearPreviousResults(); 
 }
});

How easy was that? With just a few lines of code we decorated the form to fire off an ajax request to Flickr when the user presses the submit button. The form plugin took care of a lot of little details like ...

• providing a pre-submit hook for clearing out previous search results
• serializing the form's inputs into the ajax request's query string
• appending some constant data values to the ajax request's query string
• automatically invoking the result of the JSONP response as a JavaScript function

End Result

Here's the end result once the code snippets above are put together.

Photos tagged with

What Have You Done for Me, Latency?

When developing web applications, it's not uncommon to develop and deploy the application on different network topoligies. During development, for example, you might be running a database server, web server and web browser all on localhost. Your application is running well and the performance is snappy. But user experience starts to lag when the application is deployed to production where web server and database reside on different boxes. What's to blame? Latency.

But fear not, intrepid programmer! Here's a simple command line hack that you can use to make network performance on localhost more closely resemble WAN or LAN performance.

#!/bin/bash

# This script adds a tc rule to the loopback device to 
# help developers approximate WAN performance on their 
# loopback device.

# add a delay rule to the loopback device
sudo tc qdisc replace dev lo root handle 1:0 netem delay 10msec 5msec 10%

# list current state of the loopback device
echo 'Loopback device is now configured to simulate a high latency network route.'
echo ''
echo "Current dev lo rules ... `tc qdisc show dev lo`"
echo ''
echo ''

# demonstrate delay by pinging localhost
ping -c 3 localhost

I run the above commands on login so that overly chatty code doesn't sneak past.