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.

Hello, CSS3

Hello, CSS3

This week I'm all about embracing new-ish web standards. In a prior post, I marked up an HTML5 "Hello, World!" document using new semantic tags. Today I'm going to apply some CSS2 and CSS3 styles to that document to demonstrate the awesome new design features that are available in CSS3.

First, let's start by checking out how the HTML5 "Hello, World!" document renders without any styling applied:

No surprises in the screenshot above. The document gets rendered with just a bit of formatting for headers, lists & links. Let's add some CSS2 styles to spruce things up a bit.

article,aside,figure,footer,header,hgroup,nav,section {
 /* setup for browsers that don't yet recognize HTML5 
    block elements */
 display:block
}
p {
 /* add a bit of margin above and 
    below all paragraphs */
 margin: 0.5em 0em;
}
html {
 /* stretch body to fill extra browser height */
 height: 98%;
 /* define color for dead space on 
    left and right of page body */
 background-color: #D0D3A6;
}
body {
 /* stretch body to fill extra browser height */
 min-height: 98%;
 /* define content width */
 width:46em;
 /* center content in the browser window */
 margin: 0 auto;
 /* background color for body content */
 background-color: #FFFFFF;
 /* and a wee bit of padding for all 
    top-level elements in the body */
 padding: 0.5em 1.5em;
}
body > header {
 /* keep the header from being crowded */
 margin-bottom: 0.25em;
}
body > header > hgroup { 
 /* style the page's header */
 padding: 0.25em;
 margin-bottom: 0.5em;
 background-color: #BC9C73;
 text-align: center;
}
body > header > nav { 
 /* style the page's navigation content */
 background-color: #486997;
 /* define bottom border to force render of 
   child ul's bottom margin */
 border-bottom: 0.1em solid #486997;
}
body > header > nav a {
 /* style the page's navigation content */
 color: #FFFFFF;
 text-decoration: none;
}
body > header > nav ul { 
 /* style the page's navigation content */
 background-color: #688AB6;
 margin: 0em 0em 0.3em 0em;
 padding: 1em 0.75em 0em 0.75em;
}
body > header > nav ul li {
 /* style the page's navigation content */
 background-color: #486997;
 padding: 0.3em 1em;
 margin: 0em 0.15em;
 display: inline;
}
body > footer {
 /* render the footer in the center, below 
    all other content */
 text-align: center;
 clear: both;
}
h1 {
 text-transform: capitalize;
}
h1,h2 {
 font-size:medium;
 margin: 0.5em 0em 0.25em 0em;
}
div#main {
 /* float main content to the left */
 float: left;
 width: 74%;
}
div#sidebar {
 /* float sidebar content to the right */
 float: right;
 width: 24%;
}

body aside,
body section {
 /* keep content containers from feeling crowded */
 padding: 0.25em;
 margin: 0.25em 0em;
}

Well, that's certainly an improvement. But we can do even better by progressively enhancing the page with some additional CSS3 rules. Let's give the page some depth and texture.

/*  many of these rules generated with 
    the awesome http://css3generator.com/ or
    http://www.colorzilla.com/gradient-editor/ tools */

body { 
 /* round the corners of the body element's border */
 -moz-border-radius: 4px;
 border-radius: 4px; 
 
 /* apply inset box shadow to body element to 
    create the illusion of depth */
 -webkit-box-shadow: inset 2px 0px 25px #333333;
 -moz-box-shadow: inset 2px 0px 25px #333333;
 box-shadow: inset 2px 0px 25px #333333; 
}

h1, h2 {
 /* apply drop shadow to header text */
 text-shadow: 1px 1px 2px #333333;
 filter: dropshadow(color=#333333, offx=1, offy=1); 
}

body > header * { 
 /* by default, round the border corners of 
    all elements in the header */
 -moz-border-radius: 4px;
 border-radius: 4px; 
}

body > header > hgroup,
body > header > nav { 
 /* apply box shadows to top-level header containers 
    to create the illusion of depth */
 -webkit-box-shadow: 2px 4px 6px #333333;
 -moz-box-shadow: 2px 4px 6px #333333;
 box-shadow: 2px 4px 6px #333333; 
}

body > header > hgroup { 
 /* add a subtle gradient to header's background */
 background: -moz-linear-gradient(top, #BC9C73 0%, #D3BFA5 100%); /* firefox */
 background: -webkit-gradient(linear, left top, left bottom, color-stop(0%,#BC9C73), color-stop(100%,#D3BFA5)); /* webkit */
 filter: progid:DXImageTransform.Microsoft.gradient( startColorstr='#BC9C73', endColorstr='#D3BFA5',GradientType=0 ); /* ie */
}

body > header > nav ul {
 /* "square" the dividing line between navigation 
    tabs and their background */
 -moz-border-radius-bottomright: 0px;
 -moz-border-radius-bottomleft: 0px;
 border-bottom-right-radius: 0px;
 border-bottom-left-radius: 0px; 
}

body > header > nav ul li {
 /* round the top corners of the navigation links */
 -moz-border-radius-topleft: 10px;
 -moz-border-radius-topright: 10px;
 -moz-border-radius-bottomright: 0px;
 -moz-border-radius-bottomleft: 0px;
 border-top-left-radius: 10px;
 border-top-right-radius: 10px;
 border-bottom-right-radius: 0px;
 border-bottom-left-radius: 0px; 

 /* add a gradient to the background of our 
    navigation links */
 background: -moz-linear-gradient(top, #c3d9ff 0%, #486997 37%, #486997 100%); /* firefox */
 background: -webkit-gradient(linear, left top, left bottom, color-stop(0%,#c3d9ff), color-stop(37%,#486997), color-stop(100%,#486997)); /* webkit */
 filter: progid:DXImageTransform.Microsoft.gradient( startColorstr='#c3d9ff', endColorstr='#486997',GradientType=0 ); /* ie */
 
 /* apply a more drop shadow to navigation link text */
 text-shadow: 1px 2px 8px #ffffff;
 filter: dropshadow(color=#ffffff, offx=1, offy=2); 
}

body section,
body aside {
 /* round the corners of top-level semantic elements */
 -moz-border-radius: 4px;
 border-radius: 4px; 

 /* apply a gradient background "smear" 
    to top-level semantic elements */
 background: #FFFFFF; /* use white background if gradient not available*/
 background: -moz-linear-gradient(top, #D3BFA5 0%, #FFFFFF 33%); /* firefox */
 background: -webkit-gradient(linear, left top, left bottom, color-stop(0%,#D3BFA5), color-stop(33%,#FFFFFF)); /* webkit */
 filter: progid:DXImageTransform.Microsoft.gradient( startColorstr='#D3BFA5', endColorstr='#FFFFFF',GradientType=0 ); /* ie */
}

Many of the CSS3 rules used above were generated with the awesome CSS3 Generator and ColorZilla Gradient Generator tools. I highly recommend checking them out!

Hello, HTML5

Hello, HTML5

This week I've been skimming Mark Pilgrim's HTML5: Up and Running book with a keen interest in the new semantic elements that are being formalized as part of the HTML 5 spec. There are some great new semantic elements being proposed to replace pervasive, unformalized web developer memes like ...

 <!-- This is _so_ 2008. -->
 <div id="header">
  <ul id="navigation"></ul>
 </div>
 <div id="content"></div>
  <div id="main"></div>
  <div id="sidebar"></div>
 </div>

Notice in the above markup that the semantic info about each <div>'s purpose is captured in its id attribute. This is totally valid markup and will produce a well organized document, but it does have some serious drawbacks. While such a id-as-semantic-messenger anti-pattern is easily understood by a human web developer, it's not easily parsed by machines.

So why, you may ask, should we care whether machines can understand the semantics of our web pages? Aren't web pages designed to be read and understood by humans? No. Not, all humans read web pages. People with visual impairments have the web read to them by screen readers.

Screen readers aren't that great. Yet. For example, whereas a human can easily and reliably find the main navigation content of a web page, screen readers don't yet support jumping straight to a page's main navigation content. Consider the myriad HTML4 variations that a screen reader would have to handle in order to support a jump-straight-to-navigation feature:

• <ul id="navigation"></ul>
• <ul id="nav"></ul>
• <div id="navigation"></div>
• <ul id="navigacion"></ul>
• <ul id="menu_bar"></ul>
• etc.

HTML5 to the rescue! Writing screen readers that can find the <nav> element in a HTML5 document will be much easier than coding to handle the tag soup detailed above. And <nav> is not the only new semantic element that will make it easier to write better screen readers. HTML5 also defines <header>, <footer>, <section> and <aside>.

That's great news. As a father of a visually impaired child, I'm hoping that future screen reader software can provide my daughter with web interactions that are as rich as those that I enjoy. With that vision of the future in mind, I'm embracing HTML5 and have authored a quick "Hello, World!" example of HTML5 semantic elements in action.

<!DOCTYPE html>
<html lang="en">

<head>
  <meta charset="utf-8" />
  <title>Hello, HTML5 Semantic Elements!</title>
  <!--[if lt IE 9]>
    <script 
      src="http://html5shiv.googlecode.com/svn/trunk/html5.js">
    </script>
  <![endif]-->
</head>

<body>

  <header>
    <hgroup>
      <h1>Lorem ipsum ...</h1>
      <h2>dolor sit amet, consectetur adipiscing.</h2>
    </hgroup>
    <nav>
      <ul>
        <li><a>Donec hendrerit</a></li>
        <li><a>Pellentesque eget turpis</a></li>
        <li><a>Quisque facilisis porttitor</a></li>
      </ul>
    </nav>
  </header>

  <div id="main">

    <section>
      <header>
        <h2>Morbi a posuere magna</h2>
      </header>
      <p>Etiam feugiat pellentesque dapibus...</p>
      <p>Phasellus ullamcorper condimentum urna...</p>
    </section>

    <section>
      <header>
        <h2>Donec posuere lobortis ipsum</h2>
      </header>
      <p>Cras tellus odio, facilisis nec porttitor...</p>
      <p>Donec blandit suscipit urna ac luctus...</p>
    </section>

  </div>

  <div id="sidebar">

    <aside>
      <header>
        <h2>Ut tincidunt tincidunt</h2>
      </header>
      <p>Suspendisse varius, lacus a egestas...</p>
    </aside>

    <aside>
      <header>
        <h2>Praesent massa sapien</h2>
      </header>
      <p>Phasellus porttitor auctor sapien at...</p>
    </aside>

  </div>

  <footer>
    <p>&copy; 2010 Dave Wingate</p>
  </footer>  

</body>

</html>