DB Optimizer

Migrating to wordpress

2011-01-01T09:21:00.000-08:00

I'm migrating to wordpress. In 5 seconds you should be redirected to

Delphix deals before the end of year

2010-12-28T17:53:00.000-08:00

It's let's make a deal time at Delphix. If you have ever had the fun of closing an Oracle deal at their fiscal year end, you know what happens. Not only is it fiscal year end but Delphix is also millimeters away from adding an extra figure to their end of quarter total and I'm betting that now is the last time these low of prices will ever be seen - just my view point from being inside the castle.

I'm not sure if you can imagine the ease and savings of virtualizing databases

1) Super fast provision – three clicks and a few minutes to stand up a fully functional, point-in-time 10g/11g database copy/clone

2) Storage savings - a new database copy basically only consists of some pointers and the space it takes for private redo and temp

Delphix doesn't require filesystem snashots or EMC or NetApps. It only requires x86 box with about the same amount of the disk space of the database you want to virtualize. The source database is copied onto the Delphix machine with RMAN calls, the data is compressed and Delphix handles the snapshots and provisioning of virtual databases. Virtual database can be provisioned from the original source copy, or any incremental snapshot or if log shipping is used, then any SCN or date in between. Then you can make as many copies as you want, with in reason, for almost free in terms of storage.

If you are a departmental head and some extra budget that will be lost at year end then Delphix is a good way to use that budget.

For these cut rate deals, everything has to be in by Friday at the absolute latest. For best info contact

kaycee.lai@delphix.com and/or garrett.stanton@delphix.com

This idea is being harnessed by more and more companies across different industries - we've be implemented this at Shopping.com (part of ebay), Tivo (consumer tech), Corporate Express (retail), Tagged (social networking), Clearwire (telcom), KLA Tencor (hardware), Holland America (travel) .

When I talk to customers who don't have development or QA copies of production I cringe. Usually developers work on subsets of production and often query performance on these subsets don't reflect the performance in production. Even if production is a full copy of development there is often just one copy shared by all the developers and QA. Any change to this copy has to go through a number of hoops and signoffs which slows down the development process and de-motivates developers. I don't know if you can imagine having a copy per developer of production where each developer can make and test as many changes as fast as they want on an exact copy of production - it's awesome and inspires the developers. After the developers are confident of there modifications then they can submit the final change request for review.

Delphix makes all of this easy with no changes on production and creation of virtual databases just a few clicks of a mouse in GUI .

New Oracle Monitor - what language would you use?

2010-12-19T13:54:00.000-08:00

I don't consider myself a programmer and my exposure to some of these languages is quite superficial, so please feel encouraged to jump into this discussion with your experiences, corrections and comments.

What language and UI would you use to create an Oracle monitor? Why?

What are the pros and cons ? I want a tool to be

easy to install
quick to program
have interactive graphics
run fast
web enabled

These goals are hard, if not impossible, to achieve in the same product. A product's choice of programming language will be limited if interactive graphics are involved. I think graphics are the only way to make a tool easy to use and information easy to grasp but graphics definitely limits the coding options. A really nice option, and practically a commercial requirement is to run in a web browser and running in a browser even further limits the the coding options and and the possibilities of achieving all the goals

What programming languages could be used? Some choices are

C
SQL*Plus
PL/SQL
perl
python
tcl/tk
ruby(on rails)
Java fat client
Java web app (with Flex)
Delphi
Visual Basic
Apex

And tangential languages

ksh(bash/sh/csh) with AWK
HTML5

Here is a quick (5 minute off the cuff ) impression of the languages

Do they require an install? or can the be run immediately with simple executable?

Are interactive graphics possible (within reason)

Are they easy or difficult to program in?

Do they have cross platform support? (*nix, mac, win)

Are they web enabled?

Are the fast or are they memory (and CPU) hogs?

Do you have to pay for the development platform?

Installation:

To have an absolutely brain dead install would require the tool to be written in C, Delphi or VB as these are compiled into single executables. All the other languages, AFAIK, require the language interpreter or engine to be installed and generally require Oracle libraries to be there. Actually, C, Delphi and VB might require Oracle libraries as well, but at least for C, it seems like there would be a way to link a static executable. Oracle is not required for most Java applications as they are generally shipped with a JDBC driver for Oracle, though it's just another file to scatter about on your file system as part of a complicated install. On the other hand Embarcadero has a neat option where everything, even in a java app, is packaged into one executable called an "instant on" executable, much more akin to running a self contained executable on UNIX via some windows magic in the sauce.

The installs required by other products vary. I'd say most are pretty easy, though how easy depends largely on how the language environment is packaged along with the tool. If the tool does no prepackaging and lets the user set up the required packages then installation can be a nightmare. A tool like ASHMON written in TCL/TK for examples requires graphing package, thread package, math extension and an Oracle connection package. I've packaged all these up into a single download which still requires the Oracle client libraries to be pre-installed. All and all it takes a good bit of upkeep to maintain the binaries of all packages on the platforms people might use and even upon installation it doesn't have single executable, but an execution shell that has to launch and find the scripts which depend upon directory locations and it has to find the Oracle install and tnsname.ora. It would be so awesome if I could just make one executable with everything. So installation for Perl, Python and ruby would be similar, AFAIK. A java app is a bit better - one executable though it depends on java already being installed, and for APEX - I believe all sorts of stuff has to be installed and configured on an Oracle database and either installing a full Oracle database for the tool or requiring the target database to have APEX installed is a lot to ask for.

The software that is most likely to be installed is SQL*Plus but even SQL*Plus may not always be available on a customers laptop or desktop. My laptop did not have SQL*Plus until yesterday, almost 2 months after I started using the laptop. I was just using SSH and other tools. Yesterday I finally installed SQL*Plus to start working on a new performance monitoring tool. Why a new tool? I want a new tool that will give me performance data from customer's Oracle databases that I can review at a later point in time. I want a tool that the customer can run as quickly and easily as possible and that requires the least amount of user knowledge and interaction. Beyond the goal of collection performance data, I would also want the tool to give some immediate feedback on performance. Of course SQL*Plus should be the easiest thing to use. Maybe the laptop or desktop does not have SQL*Plus installed, but in the realm of Oracle performance, nothing should be more reasonable than expecting the customer to have SQL*Plus installed on the desktop/laptop.

Graphics

AFAIK, the only reasonable interactive graphics options are Java Applications, Java in the web using FLEX (SGV was an option but is been shelved), TCL/TK, Ruby with RMagick, Visual Basic and Delphi. Not such luck for SQL, PLSQL, Perl or Python.

Apex may have some, but not sure how robust they are.

Java has gobs, but how good they are, not so sure and how do you find the right one? Many are pay for and I'd like to first commit to Java before paying and I want to be sure the package I'm buying is the best. Ideally I'd rather not have to pay

TCL's BLT package has a quite good graphing library.

For graphs, there are lots of small but important options - auto resize, filled area stacking, log scales, mixing bars and lines, easily adding and deleting items from the graph, actions on graph contents triggered by mouse passing over or clicking, etc. Sure, graphing can be done in almost anything, but having a package to take care of all the minutia is the only kind really worth my time. TCL's BTL graphs also use "vectors" or very efficient lists of numbers. I can act on these lists - divided two lists, add to lists, multiply a list by a value or modify points, add points, delete points and have the graph updated immediately.

Delphi and Visual Basic have graphic packages though, like JAVA, there are lots of options and many are pay for.

Ruby seems to have Rmagick. Not sure if there are other options.

Java web applications have Flex which requires Adobe's IDE.

Data Manipulation

SQL and PL/SQL of course are pretty simple - at least they provide the ability to collect and manipulate data in a far more powerful and easy way than any other language. The con with SQL of course is it's not procedure. PLSQL is of course procedural but often leads to creating things in the database like global temporary tables, packages and procedures and creating things in the target database is a "no no" for me. I want to affect as little as possible the database I'm monitoring. The best approach is to collect the data via SQL or even PLSQL with as little manipulation and then manipulate the data on a local, not target database. I've done this in the past with AWK but it's tedious and brittle. Similarly I've manipulated the data in TCL and again, though clearer than AWK it's still quite tedious. I've tried it in JAVA and JAVA just gives me the hebee jebees - it might not be as tedious, but I just get the feeling that the data structures are inefficient, non-scalable, slow and memory intensive. Another maybe better alternative is using a locals database but this requires installing a databases which is a bit overkill at least if the database is Oracle. The solution to this is to use an embeddable database like HSQL, H2, SQLite or firebird (is Derby embeddable?) For other languages we could also manipulate the data in local structures or objects but tacking aggregations, groupings, sums, averages, counts etc is just so tedious in code and so easy and effortless in a database. I assume it's pretty easy to embed a databases in almost all the languages except maybe C because then I'd assume we'd be loosing the advantage of having C be an easy to install executable, though maybe it's easy.

I started to play with SQLite and TCL a several years ago and though it may be simple it was certainly taking me more time than just interfacing TCL with a running Oracle database.

Beyond whether to use a database or not and how, there is the question of how productive is a coder in a language. The lower level the language the more tedious. For example C, though elegant and fast, is quite tedious. At the other end is something like Delphi which is a piece of cake. Another aspect is just how heavy the language is. I find Java heavy. The overhead to set up simple functionality feels simply crippling to me.

UI layout

Delphi is easy to layout UI.

TCL/TK is difficult.

I think Java is somewhere in between

For SQL the only UI is just hacking with ASCII - generally not pretty.

Ruby - I don't know.

Flex - not sure either though I'd guess its good though no where near as easy as Delphi.

VB - I assume it's like Delphi.

Perl and Python - not much options unless they are married to something like TK.

Apex - don't know, I've head good things but it is Oracle and from what I've seen of APEX it's not a cakewalk, at least to get up and running and ready. Maybe when all the pieces are in place its straight forward.

Coding

Java IMO seems like crap - it's like "new money" , you know those LA mansions that are gaudy fake, expensive and needlessly huge.

Perl is not pretty but at leasts it's efficient. You don't get any LA mansions in Perl. It's more like a souped up jalopy. It's sort of junky but it runs fast.

Python seems good to me.

Ruby seems like a cross between TCL and Python - seems nice - sort of zen, though I've only written a few lines.

TCL - little strange, but ok - might have been nice but it's been long forgotten.

Delphi uses a modified Pascal and what's easier than Pascal?

Performance

Java is a pig.

Flex is a pig.

OK, OK don't tell me that it's not the language its the programmer. I've never seen a C program take up 600Ms to collect a few K of data and I've never seen a Java program run under 50Ms. (more like 100M+)

Delphi is like 10M, runs fast. What's not to like? (pay for IDE and it's washed up, the UI looks like so 20 years ago)

C of course is lightning fast.

TCL seems fine, maybe at bit slow, but for the structures I care about like vectors and in graphs have been well optimized

Python seems fast.

Perl - I assume it's pretty fast. I tried to write a direct memory access program in Perl and it was slower than executing SQL but that's because the API attached and detached from the Oracle SGA for every bit of data I collected. I theoretically could have gone in and modified the Perl API for shared memory.

Web

Who works on the web for a GUI? Apex, Java with Flex, Ruby on Rails with Rmagick. Rmagick may or may not provide GUI interaction - I have yet to try it. I spent a couple hours last weekend trying to get it to work under Cygwin and eventually gave up and just installed it straight under windows, but have yet to play with it.

Java with Flex is probably the premier solution but it's fat and the IDE is payfor.

What about HTML5?

Is FLEX an option? maybe for an enterprise application but not for a desktop web enabled application as far as I can tell.

My Story

I've been thinking about this for years. I started programming TCL/TK graphics back in 1994. The only other option before 2000 or so was Visual Basic and Delphi . I didn't hear of Delphi till about 2007 (!) and Visual Basic never seemed like an option since it was limited to Windows. The windows limitation seems much less important now days, but back then I only worked on UNIX and didn't have a laptop, so TCL/TK was the only option until around 2000 when Java became an option. In 2002 when I started working on OEM 10gR1. When I arrived at Oracle in 2002 to work on OEM, I had no idea it was web based and pure HTML. HTML ?! I had thought that I would be working on the 9i java fat client. The 9i java client was relatively good at least compared to OEM 7 and 8 and I thought with a little facelift the 9i client could be good. I could hardly believe the 10g OEM was HTML. Pure HTML ? No interactive graphics?! and this was the monitor that was suppose to performance shoot, monitor and tune the some of the most important production databases in the world? Are you kidding me? The "solution" back pre-10gR1 was to have use gifs for any graphics. Any change in the gifs causes not a partial page refresh but a full page refresh. and these gifs would only be updated occasionally. The refresh rate on the Oracle home page could be as much as an hour out of date. Wow.

Well we change things - got SVG in, got the refresh rate to be much faster, but OEM is still slow and the data collection has hardly improved in the last 6 years. So much more could be done with some small changes, for example adding the average wait times for different kind of IOs, harnessing the wait histograms better, high lighting some key statistics , trending, forecasting, showing the differences between selected periods clearly, adding more drill down and aggregation options on the ASH data and externalizing in a clear graphical way the new extended row course data in ASH. I had want to get Adobe Flash/Flex into OEM but Oracle was reluctant. The compromise was SVG. OEM does use Flex but I've heard that Flex is going to be ripped out in lieu of ADF, and what's even more twisted is ADF builds it's graphic components upon Flex. Strange.

In all cases OEM is slow and huge and slow to change. Java, whether it's in the middle tier or a Java application is memory pig and generally slow.

Summary

I want the tool to run fast which means using as little CPU and memory as possible.

I want a tool that's fast to install.

I want a tool that has interactive graphics.

I want a tool that is easy to create and program.

I want the tool to be web enabled.

For collection, the best sounds like using SQL*Plus with anonymous PLSQL - this could possibly be wrapped in C for a single collection executable.

For aggregation, anything works as long as it has a database.

For UI Delphi sounds like the easiest to program *and* run (possibly VB - anyone know the pros and cons of VB verses Delphi?)

For web - it's either JAVA and FLEX and a memory pig and a pay for IDE or is RUBY on rails a possibility? Ruby has an Oracle , SQLite and UI packages.

What about HTML5 ? Too new? Does it have Oracle access? does it have an embeddable database? What graphic options does it have?

Solaris AWK still broken after all these years

2010-12-07T21:39:00.000-08:00

It's amazing how time flies. I've got the above book sitting on my desk at work. It's the only book on my desk at work. Its probably my favorite computer book for it's terse clear style and simple power. I've keep it for about 20 years, and 20 years later, I'd expect any bugs in basic code examples to be worked out. For the most part on most platforms this is true, but not Solaris.

AWK is an awesome utility always available on UNIX and makes a perfect tool to for scripts that will run anywhere with no need for dependencies nor if's, and's or but's. For that reason it's all the more dissapointing that the default awk on Solaris is broken for syntaxes that were valid over 20 years ago. On the default awk on Solaris the following are broken

awk -vVAR=value
if ( $0 ~ "string" )
gsub , sub

Probably the most useful thing to know is for point 1, passing in a shell variable, you can do it by setting a variable in the shell such as "VAR=10" and accessing it in the awk script with:

 VAR="'"$VAR"'"

Point 2, the if construct can be gotten around a bit less elegantly  with

 /string/   { ...

Point 3, gsub and sub are broken is just plain annoying and it takes writing your own routines with index and substr.

Which was too bad for me. I wanted to write a script to eliminate more than 2 redo logs from database creation for "scratch" databases. I don't know if you can imagine a create script like:

STARTUP NOMOUNT pfile='/mnt/provision/redo2a/datafile/initredo2a.ora.provision'

CREATE CONTROLFILE REUSE SET DATABASE "redo2a" RESETLOGS ARCHIVELOG

MAXLOGFILES $MAXLOGFILES

MAXLOGMEMBERS $MAXLOGMEMBERS

MAXDATAFILES $MAXDATAFILES

MAXINSTANCES $MAXINSTANCES

MAXLOGHISTORY $MAXLOGHISTORY

LOGFILE

GROUP 1(

'/mnt/provision/redo2a/datafile/opt/oracle/oradata/homer/redo01.log',

'/mnt/provision/redo2a/datafile/opt/oracle/oradata/homer/redo01a.log'

) SIZE 52428800,

GROUP 2(

'/mnt/provision/redo2a/datafile/opt/oracle/oradata/homer/redo02.log',

'/mnt/provision/redo2a/datafile/opt/oracle/oradata/homer/redo02a.log'

) SIZE 52428800,

GROUP 3(

'/mnt/provision/redo2a/datafile/opt/oracle/oradata/homer/redo03.log'

) SIZE 52428800

DATAFILE

'/mnt/provision/redo2a/datafile/opt/oracle/oradata/homer/system01.dbf',

'/mnt/provision/redo2a/datafile/opt/oracle/oradata/homer/undotbs01.dbf',

'/mnt/provision/redo2a/datafile/opt/oracle/oradata/homer/sysaux01.dbf',

'/mnt/provision/redo2a/datafile/opt/oracle/oradata/homer/users01.dbf',

'/mnt/provision/redo2a/datafile/opt/oracle/oradata/homer/t1.dbf'

$CHARACTER_SET

;

I just wanted 2 redo logs and one member for group, so I wanted to pull out all that's highlighted in orange. Simple with AWK:

cat ${MNTDIR}/doCreateControlFile.sh | \
awk -vNREDO="$NREDO" '
BEGIN {
 output="on"
 members="off"
 nlogs=0
 last="no"
}
{
# members
if ( $0 ~ "GROUP"     ) { members="on" ; nlogs=0}
# output
if ( $0 ~ NREDO       ) { last="yes" }
if ( $0 ~ "DATAFILE"  ) { output="on"  }
if ( $0 ~ "SIZE"      ) { members="off" ; nlogs=0}
if ( output == "on"   ) {
   nlogs++
       gsub(/,/,"")
 }
 if ( nlogs < last ="=" last = "no" output="off"> /tmp/redo2.$$

The script works on AIX, HPUX, Redhat but not on Solaris. For Solaris I had a few choices. I could try and use nawk or gawk which would require me using some sort of "ifdef" and even then using nawk would require some changes. AFAIK gawk would have to be installed on the machines, thus ruling it out. For me, I wanted the safest option. The safest option for me was just to re-write the code in a way that worked around the bugs on Solaris default awk.

cat ${MNTDIR}/doCreateControlFile.sh | \
awk  '
BEGIN {
output="on"
members="off"
nlogs=0
last="no"
size="no"
NREDO="'"$NREDO"'"
}
/DATAFILE/               { output="on"  } # turn output back on
/SIZE/                   { members="off" ; nlogs=0; size="yes"}  # end of group members
/GROUP/                  { members="on"  ; nlogs=-1 }  # start counting group members
{ if ( index($0,NREDO) )   last="yes"    } # reach the maximum  of redo logs to recreate
{
if ( output == "on"   ) {
str=$0
if ( members == "on"  ) { # we are in the log member section
   nlogs++             # count members
   i=index(str,",");   # get rid of comma at end of line
   if ( i > 0 ) {
       str=substr(str,0,i-1)
   }
}
if ( nlogs < size ="=" last ="=" str="$0" i="index(str,"> 0 ) {
                str=substr(str,0,i-1)
            }
            last = "no"
            output="off"   # we reach max # of redo logs, turn output off till we see DATAFILE
         }
      }
      print str
   }
}
}
{ size = "no" }
'  > /tmp/redo2.$$

Job Openings for Oracle experts

2010-12-02T14:38:00.000-08:00

You know it's a good company when the support desk looks like this:

Delphix, the company I've been at for the past month, is hiring! We are interested primarily in Oracle expertise centered around backup and recovery, rman, dataguard and ASM.

Big plus for solid knowledge of NFS, storage systems, zfs and networking with an eye for performance tuning.

We are looking for sales consultants, support, engineers and QA.

I'm super happy with this new position - small company where everyone makes an impact and the people are super smart, motivated and experienced.

We've got an amazing team from the the creators of Dtrace and zfs to principle players in the development of Oracle RAC, cache fusion and dataguard to the founder of Avamar and the marketing VP who took VMware public. Come join us !

If interested, contact me at kyle.hailey @ delphix dot com.

Delphix turns physical database infrastructure into software that can run in a fraction of the space, automating some of the most challenging and time consuming operations in the datacenter: refreshing and provisioning of databases. With our database virtualization software, enterprises can reduce complex provisioning tasks from 10 days to 10 seconds and shrink 10 TB into 1 TB.

Enterprises typically create multiple copies of production databases for development, testing, QA, staging, UAT, pilots, training, operational reporting, etc. By linking with production databases via standard APIs, Delphix enables the benefits of consolidation and virtualization for all supporting database infrastructure—with no need to make changes to production systems. As a result, an investment in Delphix can generate a high-impact ROI with very low risk.

The team at Delphix has been responsible for developing products that have generated billions in annual revenue, including Oracle RAC, used in more than 20% of Oracle deployments, and EMC Avamar, the solution that pioneered data de-duplication, one of the fastest growing technologies in datacenters today. Our board of directors includes founding board members for Oracle, Business Objects, Informatica, Riverbed, Brocade, and Avamar. One of our lead investors was the co-founding chairman of Oracle, founding chairman of Business Object and founding chairman of Informatica. Our Advisory board has a number of recognized industry icons including the original WW VP’S of Field ops from Oracle and VMware. I We are head quartered in Palo Alto. Delphix will be hard to beat in terms of working on very interesting technology, a great business, and the ability to grow both your career and technical ability.

NFS and IO testing : Bonnie++

2010-11-23T12:58:00.001-08:00

Been spending time lately testing out and trying to tune NFS mounts.

For testing IO, I've been using

dd
iozone
bonnie++
orion

This first option, dd, is fine for file creation tests and/or sequential reads but less flexible for random reads or random writes

To test random reads I thought I'd use iozone but don't see a solely random read test.

Then I thought I'd use the Oracle supplied tool, orion, to do random read test on an NFS mounted file system, but this doesn't work, at least on AIX 6.1, with my mount settings.

First orion test gave this error

$ orion -run simple -testname orion
ORION: ORacle IO Numbers -- Version 11.1.0.7.0
orion_20101123_1503
rwbase_read_luncfg: SlfFopen error on orion.lun
orion_parse_args: rwbase_read_luncfg failed

OK, have to create "orion.lun" with either my lun locations or my file locations. I put in file locations:

$ cat orion.lun
/tmp/system01.dbf

Now get another error

$ orion -run simple -testname orion
ORION: ORacle IO Numbers -- Version 11.1.0.7.0
orion_20101123_1508
Test will take approximately 9 minutes
Larger caches may take longer
orion_spawn: skgpspawn failed: Error category: 27155, Detail: 2
orion_main: orion_spawn failed
Non test error occurred
Orion exiting
Illegal instruction(coredump)

Looks like the "orion" executable wasn't being found, at least not by execve

$ truss -f orion -run simple -testname orion
...
700502: execve("orion", 0x0FFFFFFFFFFBF2D0, 0x0FFFFFFFFFFFFB30) Err#2 ENOENT

so I ran it from my bin directory where the orion executable could be found. Now get another error

$ orion -run simple -testname orion
ORION: ORacle IO Numbers -- Version 11.1.0.7.0
orion_20101123_1510
Test will take approximately 9 minutes
Larger caches may take longer
storax_skgfr_openfiles: File identification failed on /kyle/system01.dbf
OER 27054: please look up error in Oracle documentation
Additional information: 6
rwbase_lio_init_luns: lun_openvols failed
rwbase_rwluns: rwbase_lio_init_luns failed
orion_thread_main: rw_luns failed
Non test error occurred
Orion exiting

If the datafile was on "/tmp" it word fine but if it was on my NFS mount it failed with the above error. Hmm - doesn't work over NFS?

Finally I decided to try bonnie++. I download, unziped, tar extracted bonnie++ , ran "./configure" and then make, but got error

$ make
make: 1254-002 Cannot find a rule to create target bon_io.o from dependencies.
Stop.

For some reason the rule for the ".o" doesn't seem to work:

%.o: %.cpp %.h bonnie.h port.h
$(CXX) -c $<

Not feeling like trying to figure make out I just did it by hand

$ for i in *cpp; do
name=`echo $i | sed -e 's/.cpp//'`
echo "g++ -c $name.cpp $name.h bonnie.h port.h"
done > makeo
$ sh makeo
$ make

That worked. Test results coming

Virtual Conference on Oracle Performance!

2010-11-11T12:05:00.000-08:00

Check this out ( from http://tech.e2sn.com/virtual-conferences )

The E2SN Virtual Conferences are webinar-style online training sessions, but just like at conferences, you can listen to multiple top speakers within a single event.

The standard conference consists of 4 x 1.5 hour presentations with 30-minute Q & A sessions after each presentation. The four sessions are spread across two days, so the conference takes only four hours per day. So, in addition to not even having to leave your desk for learning from world's top experts, you can still get your important work done in the office, too!

The first scheduled virtual conference is focusing on a walk-through on Systematic Oracle SQL Optimization, with emphasis on practical applications and real-life scenarios.

Systematic Oracle SQL Optimization virtual conference:

The speakers are: Cary Millsap, Jonathan Lewis, Kerry Osborne and Tanel Poder
The conference takes place on 18. and 19. November ( 2 x 0.5 days )
The time of the conference sessions is: 08:00am - 12:00pm Pacific Standard Time (PST) on both days
For this inaugural event we have a special launch price of 475 USD per attendee!

Click here for agenda, abstract and registration....

SVG and OEM on windows 64 bit

2010-10-27T12:01:00.000-07:00

Anyone try to use OEM from a windows 64bit machine? Looks like there is no SVG install for windows 64bit so the charts on the performance pages are all just gifs.

Seems surprising but alas with Oracle and their dealings with UI, its not that surprising.

I wanted Oracle to use flash in OEM 10g but that got nixed. I was glad to have SVG in OEM 10g rather than nothing, but SVG has been EOLed and so unless Oracle does something then there will be more and more cases where OEMs SVG components don't work in browsers as OS's advance.

On a good note though, in 11g I was happy to see some FLEX components but word has it that these will be pulled - hmpf!

I'm happy to see that my current company, Delphix, is using FLEX (not that FLEX is the only way to go but it's a nice way, at least until HTML5) . Here's an example of Delphix's interface

http://www.delphix.com/resources.php?tab=product-demo

After working at Oracle for 13 years , UI seemed to be some of the worst in the valley often harking back to web pages from 1992, it's nice to be at Delphix, a company that seems to appreciate UI and aesthetics. I never understood why Oracle's UI wasn't better since Larry was such good friends with Steve Jobs. Steve Jobs and Apple are so good at look and feel. I figured Steve Jobs would have influenced Larry and Oracle to have great UI, but alas there has never been any indication of that other than the fun demos Larry and Steve use to do around Oracle and the NEXT machine back in the day.

ASHMON on 64bit Oracle 11gR2

2010-10-26T14:47:00.001-07:00

It's been years since I've changed any ASHMON code though I have used ASHMON whenever I didn't have access to DB Optimizer or when I wanted to specifically access v$active_session_history graphically. Yesterday, though, I just got a new laptop that is 64bit and I couldn't get ASHMON to run! So after only sleeping 4 hours last night I finally got it to run. (a lot of time was just getting Oracle to install which is a separate story - suffice it to say the 11gR2 full database download installed where as the 11gR2 client gave an error that main wasn't found).

The fix to make ASHMON run on 64 bit turns out to be pretty easy. To make ASHMON run on 64bit requires two 32bit dlls as well as forcing TNS_ADMIN to the directory with the tnsnames.ora (unfortunately oratcl doesn't seem to accept explicit database addresses , ie name:port:sid, that could bypass the tnsnames.ora) ASHMON for some reason wasn't picking up the tnsnames.ora in the Oracle 11gR2 directory. I could have forced ASHMON to use the Oracle 11 tnsnames.ora with TNS_ADMIN but figured it was easier to just point ASHMON to a tnsnames in the ASHMON install with TNS_ADMIN. You can edit ashmon/funcs/begin.tcl to change TNS_ADMIN to point to a different tnsnames.ora.

I have now made a new compressed file of ASHMON including the two dlls, oci.dll and oraocius10.dll, as well as changing the variables ORACLE_HOME and TNS_NAMES to point to ./ashmon and ./ashmon/network/admin respectively.

Here is the new "rar" file (I don't have gzip on my new laptop so just downloaded rar)

http://www.perfvision.com/ftp/ashmon_2010_10_22_64bit.rar

This version of ASHMON should run on any windows box within reason. The only "gotcha" is that you have to edit the tnsnames.ora file in the ./ashmon/network/admin to add your targets.

Oracle on VMware webinar tomorrow (Sept 30)

2010-09-29T18:43:00.000-07:00

Tomorrow I'm on an Embarcadero hosted webinar about the pros and cons of hosting Oracle on VMware. If interested the link is at

http://www.embarcadero.com/oracle-database-performance-licensing-dynamics-in-a-virtualized-environment

It's an interactive Q&A webinar with Dave Welch the main guy at VMware's booth at Oracle Open World last week. Dave is pro VMware as one might imagine.
If you have any burning questions about VMware and Oracle and/or positive/negative experiences with Oracle on VMware, drop me a line and I'll try and get your content, questions and feedback into the webinar. (during the webinar questions can be posted in the online chat)

The current list of questions center around performance overhead on VMware, Oracle's attitude toward supporting VMware, whether RAC is appropriate and/or supported on VMware and how VMware compares to Oracle's VM server.

Oracle Closed World Presentations Downloads

2010-09-23T09:37:00.001-07:00

Presentations from Oracle Closed World will be posted to

http://www.perfvision.com/ftp/OCW/

Links to scripts from Tanel Poder's presentations at

http://blog.tanelpoder.com/2010/09/20/oracle-closed-world-presentation-links/

Some photos from OCW 2010

http://www.flickr.com/photos/kylehailey/sets/72157625018583630/

Thanks all to an awesome Oracle Closed World and the Oaktable, Miracle Consulting and Embarcadero for sponsoring this event.

PS stay tuned for the 10 important database ideas black paper that we will be putting together from Oracle Closed World. (if you would like to contribute please email me kyle.hailey@embarcadero.com)

Kerry, Karl, Kyle

Link to a few more OCW 2010 photos from Karl : http://www.flickr.com/photos/kylehailey/sets/72157625025196338/

DB Optimizer vs OEM

2010-09-22T13:56:00.000-07:00

Oracle OEM is slower, more complicated and it if it breaks, which has plenty, it’s super hard to fix.

OEM has nothing like visual sql tuning diagrams. If OEM can’t tune a query they you are at a dead end street with no way out. With DBO you’ve got the VST diagrams which explain tons about what is going on with the query

http://www.youtube.com/watch?v=EoDnR02S2WA - VST demo from webinar with Jonathan Lewis
http://db-optimizer.blogspot.com/2010/09/db-optimizer-251-released.html
http://db-optimizer.blogspot.com/2010/09/sql-tuning-best-practice-visualizing.html
http://db-optimizer.blogspot.com/2010/07/product-design.html

There is no load testing or SQL IDE in OEM

http://db-optimizer.blogspot.com/2010/07/14-minute-video-overview-of-db.html

OEM’s SQL Tuining Advisor (STA) has a solid history of not being able to tune queries or worse still “tuning” the query and making it run slower

Oracle Closed World: last minute additions Exadata any MySQL talks

2010-09-22T10:10:00.001-07:00

We've just added talks on Exadata and MySQL starting at 1:15-2pm today.

In summary

1:15-2pm Exadata and MySQL

2pm Gwen Shapira on webscale, /dev/null http://www.pythian.com/news/author/shapira/)

3pm Kevin Closson on SMT and Flash (http://kevinclosson.wordpress.com/)

Beer served the whole time and lunch around 2:30

Oracle Closed World 1-4 Thirsty Bear, Sept 22, 2010, directions http://tinyurl.com/2dzjkh5

Oracle Closed World: last day with Kevin Closson as well as Gwen Shapira

2010-09-21T18:06:00.000-07:00

Rest assured, Kevin has left his bow and arrow at home this time. We are working on a hands on presentation complete with hunting tutorial for next year!

Wednesday Sept 21 1-4pm will be the last day of Oracle Closed World 2010

Highlights of the last day will be

3PM - 4PM Keynote Speaker Kevin Closson will present on SMT and Flash (http://kevinclosson.wordpress.com/)

as well as Gwen Shapira (http://www.pythian.com/news/author/shapira/) who will be talking convincingly about webscale, /dev/null

direction to Oracle Closed World at the Thirsty Bear:

http://tinyurl.com/2dzjkh5

Beer, Brains and Mayhem

Oracle Closed World 1:30-2:30 Sept 20, 2010 I'll be speaking

2010-09-20T21:03:00.000-07:00

I'll be speaking tomorrow at Oracle Closed World 1:30-2:30 at the Thirsty Bear, Sept 21, 2010

http://tinyurl.com/2dzjkh5

Database Performance Made Easy Through Graphics

Average Active Sessions (AAS) Formulas, Interpreting Load Charts and Visual SQL Tuning (VST)

Kyle begins his presentation with a discussion of the use of sample data to create Active Sessions History (ASH). But ASH is dense, multidimensional data, which makes it difficult to analyze. Fortunately, ASH can be simplified using a metric called Average Active Sessions (AAS). AAS shows the load on the database and allows you to drill down with precision and clarity into the cause of each bottleneck.

Most often, bottlenecks are caused by poor-performing SQL statements. For analyzing suboptimal SQL statements, Kyle will show you how to lay out the query graphically in a Visual SQL Tuning (VST) diagram. Through the VST, you’ll learn how to quickly visualize any coding errors in the query, discover flaws in the underlying database schema, and most importantly, find the best execution path through the query.

Oracle Closed World : Lots of hacking and undocumented stuff! , 30 minutes and counting !

2010-09-20T13:35:00.000-07:00

Tanel Poder, world expert on Oracle performance tuning and internals will be speaking live from Oracle Closed World on "Lots of hacking and undocumented stuff!"

Find out how to do things like trace a sql statement only across all sessions in the database. The presentation will be offered live by podcast if you are unable to join in person at the Thirsty Bear in San Francico.

Secret Oracle Closed World Presentation

Mogens (left) and Tanel (right) working on the
Oracle Closed World webinar broadcast
masterplan (it was very hard work)

Lots of hacking and undocumented stuff!

Join us for a Webinar on September 20 2-3pm PST

This session doesn't have any slides, but Tanel will show you lots of (undocumented) ways to hack Oracle instead. Some of this stuff may even be useful, but some is just fun ;-) Topics will range from how to read systemstate dumps, how to perform in-memory conditional tracing and how to trace a single SQL etc as time permits!

Title:		Lots of hacking and undocumented stuff!

Date:		Monday, September 20, 2010

Time:		2:00 PM - 3:00 PM PDT

Seminar delivered by Tanel Poder (the guy below):

About the speaker:
http://tech.e2sn.com/team/tanel-poder

Arup Nanda on RAC - live from Oracle Closed World

2010-09-20T13:30:00.000-07:00

Oracle Closed World has kicked off and Arup Nanda, http://arup.blogspot.com/, is presenting on RAC

Full house but we can sqeeze in some more. Come early while there is still free beer and Oracle Closed World T-shirts.

Arup Nanda is presenting on how cache fusion works, difference between row locks and RAC block locks, and answering questions like can I buffer my whole database in memory.

Arup Nanda has been an Oracle DBA since 1993, which has exposed him to all facets of database administration&emdash;from modeling to disaster recovery. He currently leads the global DBA team at Starwood Hotels, the parent of chains such as Sheraton and Westin, in White Plains, NY. He serves as a contributing editor of SELECT Journal, the publication of Independent Oracle Users Group (IOUG); speaks at many Oracle Technology&emdash;related events such as Oracle World and local user groups such as New York Oracle User Group; and has written many articles for both print publications such as Oracle Magazine and online publications such as Oracle Technology Network. Arup has coauthored two books: Oracle Privacy Security Auditing (Rampant, 2003) and Oracle PL/SQL for DBAs (O'Reilly, 2005). Recognizing his professional accomplishments and contributions to user community, Oracle chose him as the "DBA of the Year" in 2003. He lives in Danbury, Connecticut, with his wife Anindita and son Anish. He can be reached at arup@proligence.com.

Oracle Closed World live, now!

2010-09-20T12:21:00.000-07:00

Click here for the lastest info: Oracle Closed World 2010 !

Mon, Tue, Wed 1pm-4pm at the Thirsty Bear during Oracle Open World.

OCW is bigger and better this year. That means more beer, more technical goodness, chicken wings and 3+ keynotes.

The agenda will include a lot of spontaneity and improve. No telling who will show up to talk on what, but here is a brief idea, and plan for some of the talks:

Monday, September 20: 1PM to 4PM

Of special interest, check out Tanel's presentation, available on podcast, on Oracle Secret internals, called "Lots of hacking and undocumented stuff!", more info here http://tech.e2sn.com/secret

1-2 PM: Keynote Speaker Arup Nanda

Cache Fusion Demystified
Cache Fusion the fundamental to performance in RAC by reducing the need to go to disk for the data and getting it from the memory of another instance directly. But it is also a source of a lot of mystery, myths and misconceptions.
Full Abstract

2-3 PM: Keynote Speaker Tanel Poder
This session doesn't have any slides, but Tanel will show you lots of (undocumented) ways to hack Oracle instead. Some of this stuff is actually useful, some is more fun ;-) Topics will range from how to read systemstate dumps, how to perform in-memory conditional tracing and how to trace a single SQL etc as time permits! More on Tanel's thought leadership here: http://tech.e2sn.com and his blog http://blog.tanelpoder.com
Full Abstract

3-4 PM: Keynote Speaker Riyaj Shamsudeen will present on RAC Advanced Trouble Shooting.http://orainternals.wordpress.com

Also on the Agenda for Monday:

Karl Arao will spend 5 to 10 minutes on each of the following topics:

Graphing the AAS with Perfsheet a la Enterprise Manager
http://karlarao.wordpress.com/2010/07/25/graphing-the-aas-with-perfsheet-a-la-enterprise-manager/

Linear Regression with AAS
http://karlarao.tiddlyspot.com/#r2project

____________________________________________________________________________________

Tuesday, September 21: 1PM to 4PM

1:30 - 2:30 PM: Keynote Speaker Kyle Hailey
Database Performance Made Easy Through Graphics
Average Active Sessions (AAS) Formulas, Interpreting Load Charts and Visual SQL Tuning (VST)
Full Abstract

Also on the Agenda for Tuesday:

Martin Nash will present a 5-Minute presentation "Helping MOS to Help Me", which is about diagnosing the cause of an ORA-600 error and then working around the error until a patch is available/applied. Obviously it's not applicable to every ORA-600, but the approach to identifying the source of the problem is Martin feels is worth sharing.http://oraganism.wordpress.com/author/martinpaulnash/

Arjen Visser will present a 5-Minute presentation on how to use DBvisit to implement a standby database without Data Guard. When using Oracle Standard Edition and Standard Edition One, Data Guard is not available. Dbvisit can be used as an alternative to implement and managing standby databases to ensure complete protection for your primary database. This is a quick overview of the Dbvisit solution. http://blog.dbvisit.com

OPEN MIC - 5-MINUTES-OF-FAME AND 5-MINTUES-OF-HELP

_____________________________________________________________________________________

Wednesday, September 22: 1PM to 4PM

AWARDS!

3PM - 4PM Keynote Speaker Kevin Closson will present on SMT and Flash

Also on the Agenda for Wednesday:

Gwen Shapira will talk convincingly about webscale, /dev/null

Quest vs Embarcadero : SQL Tuning

2010-09-15T15:07:00.001-07:00

I get asked regularly what does Embarcadero's SQL tuner have that is any different than Quest's SQL tuner. The HUGE difference between Quest and Embarcadero’s approach is the Visual SQL Tuning (VST). Without VST most automated SQL tuning turns into dead ends. Why dead ends? Because in general the databases are going to correctly optimize queries. Queries are rarely incorrectly optimized. In those rare instances where a database get’s it wrong, then the approach of blindly generating and testing alternative execution plans can be helpful but only helpful in the case where queries are small enough that many alternative cases can be tested.

If on the other hand the query is long running say, a day, then testing alternative cases will not lead to results for easily several days. On the other hand with VST diagram we can quickly identified the best execution path using the diagram and guide the database to use this path.
Also for queries that can’t be optimized by the database because the are inherently slow, intractable or badly written - then what do you do? Generating alternative execution cases is pointless. What is going to help you quickly and easily understand the query and what the problems are? The solution is VST diagrams which only Embarcadero has in DB Optimizer.

Not only does DB Optimizer generate and test alternative execution plans, analyze indexes, suggest missing indexes, verify table statistics, analyze columns for histogram candidates and the have the powerful VST diagrams, DB Optimizer also has database load profiling, detail drilldown, load testing and sql editing with error flagging, automated error fixing, type ahead and code templates. All of this is wrapped in the Eclipse IDE and is a single executable supporting the same interface across Oracle, SQL Server, Sybase and DB2.

DB Optimizer - "the wow factor of the InSync conference"

2010-09-10T17:00:00.000-07:00

Just recovering from a whirlwind 2 weeks in APAC (Asia-Pacific)

Japan:

First off was 48 hours in Tokyo where I was warmly welcomed by Oaktable member, Ichiro Obata. Check out Ichro's blog at

http://ichiroobata.blogspot.com/2010/08/optimizerindexcostadj.html

Ichiro not only warmly welcomed me, treated me to Sushi and Sapporo, discussed Exadata, data warehousing, and performance tuning, but he also took me to the crazy neighborhood of Akihabara, sort of a mix of computer stores, nerds, manga, goth and geisha. Here's someones description:

http://www.brettb.com/TravelLog.Tokyo.Akihabara.asp

China

Traveled then to China where I gave another presentation to a packed house

and visited numerous clients as well has having Peking Duck for the first time (not to mention pigs feed and jelly fish - two new ones on me)

Australia

Finished up with an awesome time in Melbourne at the Insync Conference where I got to listen to great presentations by Richard Foote, Connor McDonald among others as well as giving my own presentation on simplifying database performance monitoring and tuning with graphics. After my presentation one of the participants called my section on Visual SQL Tuning (VST), "the wow factor of the conference!"

SQL Joins Graphically

2010-09-10T13:29:00.000-07:00

I've noticed this print out in a number of cubes at work and I think that attests to it's attractiveness and/or usefulness. I don't use most of these join methods but it is a nice complete feeling list with graphic representations and we might expand our join graphics in DB Optimizer to include similar graphics.

See:

http://www.codeproject.com/KB/database/Visual_SQL_Joins.aspx?msg=2919602

Simulated ASH 2.1 (SASH 2.1)

2010-09-10T11:16:00.000-07:00

Thanks to Marcin Przepiorowski for improving the SASH code!

see http://ashmasters.com/ash-simulation/simulation-v21/

V2.1 - Installs on Windows or Unix on Standard Edition
I would like to present a new version of S-ASH package. It is based on Kyle’s version v2 and it has been modified by me to meet a customer requirements related to minimize changes on production database and some bug fixing and new functionality has been added. In addition to that a new project called OraSASH has been created on SourceForge to keep tracking of all changes and bug fixing.

In version 1 and version 2 collection job has been implemented on production database and PL/SQL package has to been deployed on it too. In new version collection job is running on repository database and it is accessing target database via DB link and list of changes on production database has been limited to one user and one view.

————————————————————————————

Installation

Please find a list of installation steps:

create a SASH user - run repo_0_user.sql - as user SYS on repository database
add sys procedure to kill jobs - run repo_1_sys_procedure.sql - as user SYS on repository database
create repository - run - repo_1_tables.sql - as user SASH on repository database
create repository management package - run - repo_2_pkg.sql - as user SASH on repository database
create user and view on target - run - targ_1_userview_9i.sql for 9i ortarg_1_userview_10g.sql for 10g and above - as SYS user on database being monitored
create database link using - run - repo_3_dblink.sql - as user SASH on repository database
create a sash packages - run - repo_4_packages.sql - as user SASH on repository database
fill setup tables - login into SQL*Plus as SASH user and execute following statements
exec sash_pkg.set_dbid; exec sash_pkg.get_event_names; exec sash_pkg.get_users; exec sash_pkg.get_params; sash_pkg.get_data_files ; commit;
If target database is in version 9i fill up wait classes table - run repo_5_waitgroups.sql - as user SASH on repository database
If target database is in version 10g or above run following statement as SASH user on repository database
update sash_event_names sen set sen.wait_class = ( select wg.wait_class from gv$event_name@sashprod wg where wg.name=sen.name); commit;
setup jobs - login into SQL*Plus as SASH user and execute following statements
exec sash_repo.setup_jobs

—————————————–
Maintenace
All commands have to be executed as SASH user connected to repository database.

Starting collection job
exec sash_repo.start_collecting_jobs;

Stoping collection job
exec sash_repo.stop_collecting_jobs;

Data retention
Collection job is using “poor” man partitioning trick and number of tables have been increased from 7 in version 2 into 31 in version 2.1. It allow to keep performance data for one month.
Whole solution has been tested on database with 300 connected sessions and it gathered about 100 MB
of performance data daily.

Retention setup:
exec sash_repo.set_retention(’x');

where x is one of:
- d - last week
- w - last month
- h - last 24 h
- m - last 30 minutes

If you have any problems or questions feel free to add comments here or contact me directly
Marcin Przepiorowski

SQL Tuning Best Practice – Visualizing the Query

2010-09-10T08:00:00.000-07:00

SQL tuning can be a challenge to even the most experienced database professional. So, how can you give yourself a leg up in optimizing a difficult query? As you will discover in this best practice, visualizing the query can help you quickly understand the query, as well as define its optimal execution path.
A best practice for visualizing the query is to draw the query out as a Visual SQL Tuning (VST) diagram.
A VST diagram consists of the following elements:
tables as nodes
joins as connectors
filter percentages
relationships
join sizes
Historically, SQL tuning has been limited to the following two approaches:
Explain plan for the query; and/or
Trace the query
For example, a trace output might look something like this:e

PARSING IN CURSOR #2 len=53 dep=0 uid=61 oct=3 lid=61 tim=1151519905950403 hv=2296704914 ad='4e50010c'
SELECT 'Hello, world; today is ' || SYSDATE FROM dual
END OF STMT
PARSE #2:c=4000,e=1540,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1151519905950397
BINDS #2:
EXEC #2:c=0,e=58,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1151519906034782
WAIT #2: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1151519906034809
FETCH #2:c=0,e=29,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,tim=1151519906034864
WAIT #2: nam='SQL*Net message from client' ela= 215 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1151519906035133
FETCH #2:c=0,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1151519906035165
WAIT #2: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1151519906035188
WAIT #2: nam='SQL*Net message from client' ela= 192 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1151519906035400
STAT #2 id=1 cnt=1 pid=0 pos=1 obj=0 op='FAST DUAL (cr=0 pr=0 pw=0 time=3 us)

Not exactly user-friendly. I think I might need a couple of double espressos before I plunge into this one.
How about an explain plan? An explain plan is better, but still lacking insightful information

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
---------------------------------------------------------------------------------------------
| 1 | HASH GROUP BY | | 1 | 1 | 1 |
|* 2 | FILTER | | 1 | | 1909 |
|* 3 | TABLE ACCESS BY INDEX ROWID | PS_RETROPAYPGM_TBL | 1 | 1 | 3413 |
| 4 | NESTED LOOPS | | 1 | 165 | 6827 |
|* 5 | HASH JOIN | | 1 | 165 | 3413 |
|* 6 | HASH JOIN | | 1 | 165 | 3624 |
| 7 | TABLE ACCESS BY INDEX ROWID | WB_JOB | 1 | 242 | 2895 |
| 8 | NESTED LOOPS | | 1 | 233 | 2897 |
| 9 | TABLE ACCESS BY INDEX ROWID| PS_PAY_CALENDAR | 1 | 1 | 1 |
|* 10 | INDEX RANGE SCAN | PS0PAY_CALENDAR | 1 | 1 | 1 |
|* 11 | INDEX RANGE SCAN | WBBJOB_B | 1 | 286 | 2895 |
|* 12 | TABLE ACCESS FULL | WB_RETROPAY_EARNS | 1 | 27456 | 122K|
| 13 | TABLE ACCESS FULL | PS_RETROPAY_RQST | 1 | 13679 | 13679 |
|* 14 | INDEX RANGE SCAN | PS#RETROPAYPGM_TBL | 3413 | 1 | 3413 |
| 15 | SORT AGGREGATE | | 1791 | 1 | 1791 |
| 16 | FIRST ROW | | 1791 | 1 | 1579 |
|* 17 | INDEX RANGE SCAN (MIN/MAX) | WB_JOB_F | 1791 | 1 | 1579 |
| 18 | SORT AGGREGATE | | 1539 | 1 | 1539 |
| 19 | FIRST ROW | | 1539 | 1 | 1539 |
|* 20 | INDEX RANGE SCAN (MIN/MAX) | WB_JOB_G | 1539 | 1 | 1539 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("B"."EFFDT"= AND "B"."EFFSEQ"=))
3 - filter("E"."OFF_CYCLE"="A"."PAY_OFF_CYCLE_CAL")
5 - access("D"."RETROPAY_SEQ_NO"="C"."RETROPAY_SEQ_NO")
6 - access("C"."EMPLID"="B"."EMPLID" AND "C"."EMPL_RCD#"="B"."EMPL_RCD#")
10 - access("A"."RUN_ID"='PD2' AND "A"."PAY_CONFIRM_RUN"='N')
11 - access("B"."COMPANY"="A"."COMPANY" AND "B"."PAYGROUP"="A"."PAYGROUP")
12 - filter(("C"."RETROPAY_PRCS_FLAG"='C' AND "C"."RETROPAY_LOAD_SW"='Y'))
14 - access("E"."RETROPAY_PGM_ID"="D"."RETROPAY_PGM_ID")
17 - access("F"."EMPLID"=:B1 AND "F"."EMPL_RCD#"=:B2 AND "F"."EFFDT"<=:B3)
20 - access("G"."EMPLID"=:B1 AND "G"."EMPL_RCD#"=:B2 AND "G"."EFFDT"=:B3)

If I was greeted with this first thing in the morning, I'd definitely get a coffee before attempting to operate on it.
The explain plan is just a set of directions that the database takes to retrieve the data from the database in order to satisfy an SQL query. An analogy would be if we imagine the SQL query as a google maps request for directions to get from my home in the suburbs to my office downtown. The explain plan is like the turn-by-turn route list. But for any of us who have gotten lost well know, it’s also nice to be able to see the map as well as the turn-by-turn directions.
If google maps only gave me the directions without the map I'd be pretty upset, probably lost and totally anxious about how long it's going to take me to get to my destination.
Are these a good set of directions? (from where I live to where I work, in San Francisco):

It might help if I had a map:
On the map, the directions look pretty sensible, but what if there was a traffic jam on some streets and not others? How about a map that included traffic jams (i.e. bottlenecks)?
Whether the turn-by-turn directions are good or bad requires a map (i.e. visual information). So how do we get a map of for an SQL query? Let's start with the terrain. We can describe the terrain by laying out the tables in the query graphically and drawing connectors for every join, or routes through the diagram. A diagram could look like this:

Now we have a map of the terrain. Seeing the terrain allows us to pick out an optimal explain plan. To pick an optimal explain plan, find tables that have the most selective filter and start the execution there. In the above case there is only one table with a filter. This filter is denoted by the F icon on the table BROKER. Start at BROKER and then look for joins that keep the running row set size to a minimum. The running row set size is the number of rows we carry from the previous node to the next node in the VST. We have to visit all the nodes, but the fewer rows we have to carry with us from one node to the next, the faster we’re going to arrive at our destination.
Whether you draw out the VST by hand or use a tool, a best practice is to draw the child table above the parent table. This layout encourages us to navigate the VST join down to keep our running row set the same size vs. joining up which will generally increase the row set size:

Thus, the idea is to start at the most selective filter and join down before joining up. In our example there is only one filter on BROKER, so we start there, we join down to OFFICE_LOCATION, then finish off with CLIENT_TRANSACTION, and end at CLIENT.
But what if we have more than one filter and we have to choose? In that case we have to calculate the filter percentage. The filter percentage is simply the number of filter rows divided by total rows in the table. Filter rows are the number of rows returned after applying the filter criteria on that table from the query.
In the diagram below, BROKER is the most selective filter at .05% so we start at BROKER and follow the same path as we just discussed:

But what if we don’t have access to any information identifying the parent-child relationships (such as PK/FK constraints, unique indexes or unique constraints)? In those cases, we may be looking at many-to-many relationships. With many-to-many relationships, it's unclear how to navigate the VST. We no longer know whether to join up or down. To solve this impasse, we can add the two table join row sizes to help us navigate. Below, we’ve drawn the two table join sizes on the join lines:

To navigate the VST above, we again start at BROKER with the most selective filter, then look for the smallest two table join size which is OFFICE_LOCATION with a join size of 3. Then we join to CLIENT_TRANSACTION and finally CLIENT.
As you can see, VST diagrams act like the map for the terrain that an SQL query has to navigate. When a simple map is not enough, we can add statistics such as filter percentages and two table join sizes to complete the map. VST diagrams can be drawn on paper, with tools such as Visio or with database products that draw them automatically using the SQL query, schema definitions and table statistics.

For more perspective on the visual approach to tuning SQL and drawing VST diagrams see
SQL Tuning by Dan Tow
Refactoring SQL Applications by Stephan Faroult , chapter 5
"Designing Efficient SQL: A Visual Approach" by Jonathan Lewis
http://www.simple-talk.com/sql/performance/designing-efficient-sql-a-visual-approach/

Oracle Closed World 2010

2010-09-09T16:04:00.000-07:00

Get ready for Oracle Closed World 2010 !

Mon, Tue, Wed 1pm-4pm at the Thirsty Bear during Oracle Open World.

OCW is bigger and better this year. That means more beer, more technical goodness, chicken wings and 3+ keynotes.

Data visualization weak in the BI industry

2010-09-09T15:08:00.001-07:00

From Stephen Few's blog http://www.perceptualedge.com/blog/?p=820

The industry that has claimed responsibility for helping organizations get real value from information goes by the name “business intelligence.” This term was originally coined by Hans Peter Luhn, an IBM researcher, way back in 1958. Luhn defined business intelligence as “the ability to apprehend the interrelationships of presented facts in such a way as to guide action towards a desired goal.” The term didn’t catch on, however, until sometime after Howard Dresner, best known for his work at Gartner, used it again to breathe new life into to the data warehousing industry. Dresner defined the term as “concepts and methods to improve business decision making using fact-based support systems.”
Contained in these early definitions was the seed of an inspiring vision that caused people like me to imagine a better world, but the business intelligence industry has done little to help us achieve the vision of the people who coined the term. When Thornton May was interviewing people for his book “The New Know”, he asked a prominent venture capitalist known for his 360-degree view of the technology industry what he thought of when he heard the phrase business intelligence. His response was “big software, little analysis.” Sadly, his response rings true.

DB Optimizer 2.5.1 Released

2010-09-08T13:13:00.000-07:00

I’m excited to announce the release of DB Optimizer 2.5.1 which has some big new features for a point release along with the regular minor bug fixes. Many thanks to the DBO team for another awesome release.

Download available at:

https://downloads.embarcadero.com/free/db_optimizer

http://www.perfvision.com/ftp/DBO251/dbo_251_300810_ion.exe

And DBO 2.5.1 is included in Embarcadero's All Access 1.8.

The new features are

Visual SQL Tuning (VST) diagram can be saved to file – previously it had to be regenerated every time a saved TUN session was reopened.
VST has new notations

o Sub-queries are encapsulated as well as nested sub-queries

o Exists/In and Not Exist/Not In are indicated on the diagram

o Outer Joins are denoted on the diagram

I've made 3 “.tun” files available here

http://www.perfvision.com/ftp/DBO251/example_tun/

that showcase some of these new features. For example, just drag and drop “bigstmt2.tun” into DB Optimizer 2.5.1 from windows explorer and then click on the analysis tab. The analysis tab immediately shows the VST diagram where you can the double click on “BIG_STATEMENT2” table on the right and you can, without being connected to a database, drill into all the sub-queries and views. Impressive and great for “oohs and aahs” in demos:

Now expanded

The only “gotcha” is that the 14 day trial version is a limited functionality trial license. With the limited trial license you can only tune 5 SQL statements and when you profile, you see the load but not the SQL text for the queries it find. You can get around this by getting a temporary retail license from a sales person, or I can try and get you one for you.Let me know if you download DB Optimizer and how it goes and if I can help.

Other examples:

Query from Jonathan Lewis webinar on visual approach to SQL tuning

Example of outer joins from q2_251outer.tun

The illustrated guide to a Ph.D.

2010-08-27T21:12:00.000-07:00

A nice graphical story of the PhD and perspective from http://matt.might.net/articles/phd-school-in-pictures/

here is the article (click on above link to get original)

By the time you finish elementary school, you know a little:Imagine a circle that contains all of human knowledge:

By the time you finish high school, you know a bit more:

With a bachelor's degree, you gain a specialty:

A master's degree deepens that specialty:

Reading research papers takes you to the edge of human knowledge:

Once you're at the boundary, you focus:

You push at the boundary for a few years:

Until one day, the boundary gives way:

And, that dent you've made is called a Ph.D.:

Of course, the world looks different to you now:

So, don't forget the bigger picture:

Keep pushing.

Refactoring SQL Applications

2010-08-16T15:59:00.001-07:00

"The application design is the most important factor in performance"

I've heard the phrase so many times, it doesn't even register on me. Sure the application design is the most important factor in performance, but what do I do about it? Well, "Refactoring SQL Applications" (RSA) is the book I've been waiting for. RSA lays out examples of how application coders tend to think either procedurally or object oriented and how this thinking can completely miss the boat when it comes to writing SQL and interacting with the database.

In RSA, Stephane Faroult, lays out examples of Java code, user defined functions and views and how a coders notions of modular procedural programing and/or object instantiation can run undermine database performance from cookie cutter reuse of code to table joins written in Java. If you've ever seen Tom Kyte's "Developer Super Session - The Best Way", then you know the best way is not to do it and his example is of a coders doing "count(*)" to find out how many loops to do or how big an array to layout. Tom explains the disasters this can create and how the best way is just to not do the count(*) and how this works by letting the database do the work. Letting the database do the work is explained clearly and with many examples from different perspectives in "Refactoring SQL Applications".

Stock Market Patterns: High Frequency Trade 'Crop Circle'

2010-08-14T20:06:00.000-07:00

You'd think I 'd find this on "Information is Beautiful" but got the link from a friend on Facebook. The images below visually display the patterns of "High Frequency Trading" (HTF), which can consist of high frequency trades to manipulate the markets.

See original article (article part 2) (flash crash article)

"Aside from the fact that it is illegal to indicate a quote without a trade intent, this form of quote stuffing is in fact manipulative when conducted by HFT repeaters in specific "shapes" as it actually moves the NBBO actively higher or lower, in cases pushing the bid/offer range up to 10% higher without even one trade ever having occurred, simply by masking a big block order which other algos interpret as bid interest and pull all offers progressively or step function higher (or vice versa, although we have rarely if ever seen the walking down of a stock over the past 18 months)."

BATS "Flag Repeater". 15,000 quotes in 11 seconds, dropping the ASK price 1 penny each quote from $9.36 to $8.58 and back up again.

07-29-10
"The Crown". While not a large number of quotes, this NASDAQ/BATS Bidsize sequence was just too unusual to bypass.

07-27-10

NASDAQ "Blotter". One of the more unusual repeating Asksize cycles.

Looks simple but ...

2010-08-05T13:46:00.000-07:00

(open images in a new window to see full size)

I'm really enjoying DB Optimizers simple drill down into SQL statements that hide complexity, for example this query looks pretty simple:

but a simple double click into "BIG_STATEMENT2" shows me what's really going on:

Michigan OakTable Symposium

2010-08-04T16:52:00.000-07:00

I'm excited to be speaking about Visual SQL Tuning at Michigan OakTable Symposium. The symposium sounds awesome with many of my favorite Oracle experts. Looks to be the mid-year northern version of HOTSOS.

http://michiganoaktable.intuitwebsites.com/

"The Michigan OakTable Symposium will take place September 16-17th, 2010, in Ann Arbor, Michigan. The Michigan OakTable Symposium brings together a world-class list of speakers on a variety of Oracle database subjects, with a focus on DBA and development subjects.

Due to the size of the conference hall, this event is strictly limited to 300 attendees! This provides a unique opportunity for easy access to all speakers, with plenty of opportunities for one-on-one interaction, discussion, and networking, with the leading experts in Oracle database administration and development.

A few of the confirmed speakers are:

ACE Directors - Doug Burns, Alex Gorbachev, Jonathan Lewis, Cary Millsap, Mogens Norgaard, Tanel Poder

ACE's - Randolph Geist, Tim Gorman, Marco Gralike, Joze Senegacnik, Riyaj Shamsudeen, Chen Shapira"

Oracle acquisitions

2010-08-03T20:45:00.000-07:00

From: SoftwareAdvice.com

In this graphic of the size, type and timeline of Oracle acquisitions:

Follow-up:

I played with the data a little myself. Unfortunately not as long as I would like since I was using Tableau and my Tableau license runs out today. Tableau was a fun way to play with the data, though I didn't find all the knobs and dials I wanted, but here is what I had so far:

(full size at http://www.flickr.com/photos/kylehailey/4946414476/sizes/o/in/photostream/ )

(full size at http://www.flickr.com/photos/kylehailey/4946414510/sizes/o/in/photostream/ )

Product Design : VST

2010-08-03T12:04:00.000-07:00

I think the VST diagrams are powerful on their own but my original goal was to show the execution path on top of the diagram in order to look at to execution plans side by side and quickly see the differences which isn’t possible with textual explain plans. Here is an example of two explain plans in text and then graphically

This graphic was one of my attempts to show executions on top of the VST diagram, and I’ve been working with how best to show the order. Because the diagram layout stays the same, and the order is overlaid on top, it’s easy to compare them side by side. I can also see why original execution plan, on the left, was wrong. The original execution started at E which joins to C producing 198201422 rows (as seen on the join line), yet the query only returns 44,000 rows (not shown). Also table E has no filtering, really, since 99% of it's rows are returned after applying the filtering condition. The filtering is represented in blue to the bottom right of the table as a percentage of rows returned from the table. The query should start at A where there is 2% filter ratio. After starting at A we should join to C or B (which is a subquery) because the result set sizes are the smallest 85K and 642K respectively. Making this change (via hints) took this query from running over 24 hours to 5 minutes)

Designing VST diagrams has been fun exciting and challenging. I wonder if you can imagine this: Have you ever been rock climbing ? and I’m not talking about the gym, but out on real cliffs? If so you probably know that 99% of the time people use a route map that shows exactly where to go and that indeed there is a way to climb up and off the cliff and so you won’t get stuck halfway up with nowhere to go. Climbing these routes is super scary and exciting, but the amazing thing is someone climbed them without a map, not knowing if they would get up 1000 feet and find that there is no way to finish, and somehow have to get down, which might not even be possible given the gear . Seriously life threatening.

That’s what I think about writing a completely new way of tuning SQL. Of course it’s not mortally life threatening J, but years of my life and my job are on the line. I have no idea where the end is, or what all the road blocks will be. I certainly didn’t when I started but I’m far enough along to see that the diagrams provide powerful information rapidly to the user in an easy to understand graphic way. Graphics can be misused even abused resulting worse information instead of better, but when graphics are used well they are much more powerful than the textual quantitative data. The graphical diagrams are a great way to understand the relationships in the query faster than reading the query text. It’s sort of like looking at the Google map and seeing the route drawn verses having to read the directions. Both are important but I can understand the route on map much faster than the directions, though I might want to read the directions after reading the map. The map with the route though is often all I need.

I’m not able to store vast amounts of information and see all the permutations like some Oracle experts can. This can be a curse and a blessing. It’s a blessing because it makes me look for ways where I can understand the problem space with out having to buffer tons of information and such solutions are valuable to the general public. On the other hand its scary in that I don’t see all the possible problems, but that can be a blessing as well because I try to just concentrate on the solutions that have the biggest return, the biggest bang for the buck , the issues that can be most easily solved, instead of getting distracted the overwhelming possibilities and issues.

In some ways the SQL optimization space seems overwhelming and in others ways it seems really small. I think that VST diagrams will help make the problem space seem much smaller, manageable and understandable as the VST diagrams mature.

14 Minute video overview of DB Optimizer

2010-07-30T13:10:00.000-07:00

There are a lot of powerful features in DB Optimizer so I put together a quick 14 minute video to go over them:

Profiler - view database load, find top SQL and send SQL to SQL Tuner for tuning
Tuner - supply a SQL statement and have DB Optimizer's SQL Tuner automatically tune the query
Editor - write and debug SQL code with code assistance and quick fixes
Load Editor - test SQL code with multiple concurrent users

DB Optimizer XE product demo from Kyle Hailey on Vimeo.

An iPad for your thoughts?

2010-07-27T13:26:00.000-07:00

Embarcadero s conducting a brief survey to learn more about database professionals what challenges do they face this year and day to day for that matter?What technologies and tools are they using, and which do they wish they had? Where is the database world headed? If you're a database professional or deal with databases for your organization, please take 5 minutes to complete this survey.

Embarcadero knows your time is valuable. To thank you for your participation, Embarcadero are offering a chance to win an iPad. All results and comments will be kept anonymous.

Oracle 10053 SQL Trace Viewer

2010-07-26T16:42:00.000-07:00

The infamous 10053 trace is probably the most extensive trace for understanding why the Oracle optimizer chose a query's execution path (also see event 10132 at level 1). The 10053 trace can been large, cumbersome and tedious to read. Finally help is on the way. See Jonathan Lewis' blog at

http://jonathanlewis.wordpress.com/2010/04/30/10053-viewer/

download at http://www.oaktable.net/sites/default/files/new_treeview.zip.doc

(rename and take the ".doc" extension off)

New VST graphics coming in DB Optimizer 2.5.1

2010-07-25T21:29:00.000-07:00

I'm excited about the upcoming minor release of DB Optimizer in a few weeks, 2.5.1. Version 2.5.1 will have graphics for outer joins, in/exists, and not it/not exists as well as diagramming the embedded nature of the subqueries:

Subqueries are expandable and collapsible in the VST diagram by right clicking on them and choosing "expand" or "collapse".

The diagram will also have a new optional legend to explain the parts:

and as always the text and the graphics will be interactive and correlated as we can see by the text of the subquery being highlighted after the subquery had been clicked on in the diagram.
Can't wait for version 3.0 where we plan to visual show the flow of the execution path on top of the diagram as well as visually diagramming remote tables (for example at dblinks) and partitions (partitions are already show under table statistics).

see blog post

http://db-optimizer.blogspot.com/2010/06/more-vst-notation.html

for the sql text

Speaking at the InSync10 event in Melbourne

2010-07-23T12:05:00.000-07:00

I'll be speaking at the InSync10 event in Melbourne

about Database Performance Made Easy at the InSync10 event in Melbourne, Australia 16 Aug, 3:30pm, http://bit.ly/bi5xLj.

InSync10 provides the most complete conference program developed by Oracle customers for Oracle customers

See full two day program of high calibre speakers

This year’s conference theme is "Innovate, Interact, Educate and Maximise the Use of Your Oracle Software". InSync10 promises the most comprehensive Oracle applications and database agenda outside the US with the largest number of Oracle customers in one place.

Streams include Database, Middleware and Applications. This foremost event will showcase over 100 speakers from around Australia, New Zealand and further afield, with updates, presentations and reports from senior Oracle staff as well as an array of presentations from Oracle users.

You can’t afford to miss this opportunity to get "insync" with other Oracle users to share ideas and expand your knowledge. How do you get more value from your Oracle solutions? Come to InSync10 and find out. Join like-minded peers and share ideas, innovation and solutions.
Add yourself to our email list and hear the latest. Registration now open.

Kyle Hailey
Program Manager, Database Performance and Optimization Products, Embarcadero Technologies

Kyle Hailey joined Embarcadero with a goal of producing the easiest, most powerful tools for database performance tuning in the industry. Prior to Embarcadero, Kyle worked on a complete redesign of the Oracle Enterprise Manager 10g performance pages. His input shifted the screens away from confusing clutter to simple-yet-powerful graphics based on session load and wait bottlenecks, and this design continues to be the foundation of OEM 11g. Kyle has a long and distinguished career in the database world, having also worked for Oracle, Quest and other organizations on database performance tuning and optimization. He has designed tools to improve high-end performance monitoring, such as direct memory attach to bypass SQL and interactive graphic displays of performance data. Kyle presents regularly at industry conferences and teaches database performance tuning classes around the world.

posted in database tools by admin

"If I can't picture it, I can't understand it." - Albert Einstein

2010-07-22T06:59:00.001-07:00

New book recommendation:

Refactoring SQL Applications by Stephan Faroult

I found the book easy to read, well thought out and covers efficiently designing code to access the database, efficiently designing SQL and how to refactor existing applications and testing the results for accuracy and performance. Excellent book. What's that have to do with pictures and visualization? Stephan Faroult includes some new and innovative ways to visually represent SQL statements in such a way as to see patterns and problems. Look to see these methods in a future version of DB Optimizer! In discussion DB Optimizer with Stephan, he mention a quote that rings so true for me:

"If I can't picture it, I can't understand it." - Albert Einstein:

Along the same lines

"It is impossible to even think without a mental picture." - Aristotle:

"Man's mind cannot understand thoughts without images of them." - Thomas Aqunias:

"The evolution of images is a kind of intermediate between that of the perceptions and that of the intelligence." - Jean Piaget:

Mathematics is cognitive process-thinking-that requires the dual coding of imagery and language. Imagery is fundamental to the process of thinking with numbers. Albert Einstein, whose theories of relativity helped explain our universe, used imagery as the base for his mental processing and problem solving. Perhaps he summarized the importance of imagery best when he said, "If I can't picture it, I can't understand it." - NANCI BELL AND KIMBERLY TULEY

One of my favorite examples of the power of graphics to easily, quickly and powerful display quantitative information is Anscombe's Quartet. From http://en.wikipedia.org/wiki/Anscombe's_quartet

Anscombe's Quartet
I		II		III		IV
x	y	x	y	x	y	x	y
10.0	8.04	10.0	9.14	10.0	7.46	8.0	6.58
8.0	6.95	8.0	8.14	8.0	6.77	8.0	5.76
13.0	7.58	13.0	8.74	13.0	12.74	8.0	7.71
9.0	8.81	9.0	8.77	9.0	7.11	8.0	8.84
11.0	8.33	11.0	9.26	11.0	7.81	8.0	8.47
14.0	9.96	14.0	8.10	14.0	8.84	8.0	7.04
6.0	7.24	6.0	6.13	6.0	6.08	8.0	5.25
4.0	4.26	4.0	3.10	4.0	5.39	19.0	12.50
12.0	10.84	12.0	9.13	12.0	8.15	8.0	5.56
7.0	4.82	7.0	7.26	7.0	6.42	8.0	7.91
5.0	5.68	5.0	4.74	5.0	5.73	8.0	6.89

Edward Tufte uses this example from Anscombe to show 4 datasets of x

and y that have the same mean, standard deviation, and regression

line, but which are qualitatively different. - http://matplotlib.sourceforge.net/examples/pylab_examples/anscombe.html

Oracle's SQL Tuning Pack - part 3

2010-07-06T13:18:00.000-07:00

How does the STA work in 11gR2 with the query from "Oracle's SQL Tuning Pack - part 2" ?

Here is the view on the Top Activity Page:

The first spike is when I ran the query. I pass the gray window over the spike and selected the first query and submitted it to the tuner. The rest of the load, the next 30 minutes is the STA running trying to find a better plan. After 30 minutes the STA times out saying not better plan found.

On 10gR2 the STA finishes in less than 2 minutes and finds a profile though the profile puts a higher load on the database for this query. On 11gR2, the STA runs for 30 and times out without finding any suggestions.

Here is a video on Vimeo of the tuning with DB Optimizer which takes less than 60 seconds to find a faster plan:

Oracle's SQL Tuning Pack, part 2

2010-07-06T11:17:00.000-07:00

Above its the load produced by the same load. Its a query being run by 10 users 20 times with a sleep of random interval between 1-2 seconds.
The graph measures the load on the database. Green is CPU, blue is IO and the redish brown is concurrency.

First, on the left is the load of the original default execution plan.

Second, in the middle is the load after applying a profile from the SQL Tuning Advisor (STA).

Third, on the right is the load after having been tuned by DB Optimizer.

All this goes to show that the STA's profile can be sub-optimal even when a better plan exists.

Here is the view of the same load from the "Statistics" tab of the SQL Details in OEM 10g.

First, dark blue, is load from the default path.

Second, light blue is the load from the STA suggested profile.

Third is the load after tuning the query with DB Optimizer.

Here is the query, actually a Peoplesoft query,

SELECT
A.COMPANY,
A.PAYGROUP,
E.OFF_CYCLE,
E.SEPCHK_FLAG,
E.TAX_METHOD,
E.TAX_PERIODS,
C.RETROPAY_ERNCD,
SUM (C.AMOUNT_DIFF) SUM_AMOUNT
FROM
PS_PAY_CALENDAR A,
WB_JOB B,
WB_RETROPAY_EARNS C,
PS_RETROPAY_RQST D,
PS_RETROPAYPGM_TBL E
WHERE
A.RUN_ID = 'PD2' AND
A.PAY_CONFIRM_RUN = 'N' AND
B.COMPANY = A.COMPANY AND
B.PAYGROUP = A.PAYGROUP AND
E.OFF_CYCLE = A.PAY_OFF_CYCLE_CAL AND
B.EFFDT = (SELECT MAX (F.EFFDT)
FROM WB_JOB F
WHERE
  F.EMPLID = B.EMPLID AND
  F.EMPL_RCD# = B.EMPL_RCD# AND
  F.EFFDT < = A.PAY_END_DT) AND

       B.EFFSEQ = (SELECT MAX (G.EFFSEQ)
FROM WB_JOB G
WHERE
    G.EMPLID = B.EMPLID AND
    G.EMPL_RCD# = B.EMPL_RCD# AND
    G.EFFDT = B.EFFDT) AND
C.EMPLID = B.EMPLID AND
C.EMPL_RCD# = B.EMPL_RCD# AND
C.RETROPAY_PRCS_FLAG = 'C' AND
C.RETROPAY_LOAD_SW = 'Y' AND
D.RETROPAY_SEQ_NO = C.RETROPAY_SEQ_NO AND
E.RETROPAY_PGM_ID = D.RETROPAY_PGM_ID
GROUP BY
A.COMPANY,
A.PAYGROUP,
E.OFF_CYCLE,
E.SEPCHK_FLAG,
E.TAX_METHOD,
E.TAX_PERIODS,
C.RETROPAY_ERNCD;

For the courageous or bored with idle time, the dmp and SQL are available at

http://www.perfvision.com/ftp/tcf/TC1.zip

and for kicks, the query's VST diagram

Couple of thoughts questions in my mind.

First if the reason for picking sub-optimal path in the first place is due to a bug, then it would make some sense that the the STA would still have the same bug and make the same mistakes.

Second, I wonder if the STA is really trying to find better plans, or if it is trying to pull the statistics used by the optimizer more in line with the actual statistics that would happen when running the query along the lines of TCF or tuning by cardinality feedback. When I look at the hints in profile the are of the nature of skewing stats one way or another:

OPT_ESTIMATE(@"SEL$485D066A", TABLE, "C"@"SEL$1", SCALE_ROWS=4.412144743)
OPT_ESTIMATE(@"SEL$485D066A", INDEX_SKIP_SCAN, "C"@"SEL$1", WBBRETROPAY_EARNS, SCALE_ROWS=3.642257471e-005)
OPT_ESTIMATE(@"SEL$485D066A", JOIN, ("B"@"SEL$1", "A"@"SEL$1"), SCALE_ROWS=12.28434396)
OPT_ESTIMATE(@"SEL$485D066A", JOIN, ("B"@"SEL$1", "VW_SQ_2"@"SEL$09936D23"), SCALE_ROWS=3.049755426)
OPT_ESTIMATE(@"SEL$485D066A", INDEX_SKIP_SCAN, "C"@"SEL$1", WB_RETROPAY_EARNS_IDX1, SCALE_ROWS=3.642257471e-005)
OPT_ESTIMATE(@"SEL$485D066A", INDEX_FILTER, "C"@"SEL$1", WB_RETROPAY_EARNS_IDX1, SCALE_ROWS=3.642257471e-005)
OPT_ESTIMATE(@"SEL$485D066A", JOIN, ("C"@"SEL$1", "B"@"SEL$1", "A"@"SEL$1", "VW_SQ_2"@"SEL$09936D23"), SCALE_ROWS=15.63082791)
OPT_ESTIMATE(@"SEL$485D066A", JOIN, ("C"@"SEL$1", "B"@"SEL$1", "A"@"SEL$1"), SCALE_ROWS=5.133606627)
OPT_ESTIMATE(@"SEL$485D066A", JOIN, ("B"@"SEL$1", "A"@"SEL$1", "VW_SQ_2"@"SEL$09936D23"), SCALE_ROWS=37.50606343)
OPT_ESTIMATE(@"SEL$B186933D", TABLE, "C"@"SEL$1", SCALE_ROWS=4.412144743)
OPT_ESTIMATE(@"SEL$B186933D", INDEX_SKIP_SCAN, "C"@"SEL$1", WBBRETROPAY_EARNS, SCALE_ROWS=3.642257471e-005)
OPT_ESTIMATE(@"SEL$B186933D", JOIN, ("B"@"SEL$1", "A"@"SEL$1"), SCALE_ROWS=9.626410845)
OPT_ESTIMATE(@"SEL$B186933D", INDEX_SKIP_SCAN, "C"@"SEL$1", WB_RETROPAY_EARNS_IDX1, SCALE_ROWS=3.642257471e-005)
OPT_ESTIMATE(@"SEL$B186933D", JOIN, ("E"@"SEL$1", "D"@"SEL$1", "C"@"SEL$1", "B"@"SEL$1", "A"@"SEL$1"), SCALE_ROWS=3.896066538)
OPT_ESTIMATE(@"SEL$B186933D", INDEX_FILTER, "C"@"SEL$1", WB_RETROPAY_EARNS_IDX1, SCALE_ROWS=3.642257471e-005)
OPT_ESTIMATE(@"SEL$B186933D", JOIN, ("E"@"SEL$1", "B"@"SEL$1", "A"@"SEL$1"), SCALE_ROWS=9.626410845)
OPT_ESTIMATE(@"SEL$6E19F182", TABLE, "C"@"SEL$1", SCALE_ROWS=4.412144743)
OPT_ESTIMATE(@"SEL$6E19F182", INDEX_FILTER, "C"@"SEL$1", WB_RETROPAY_EARNS_IDX1, SCALE_ROWS=3.642257471e-005)
OPT_ESTIMATE(@"SEL$6E19F182", INDEX_SKIP_SCAN, "C"@"SEL$1", WBBRETROPAY_EARNS, SCALE_ROWS=3.642257471e-005)
OPT_ESTIMATE(@"SEL$6E19F182", INDEX_SKIP_SCAN, "C"@"SEL$1", WB_RETROPAY_EARNS_IDX1, SCALE_ROWS=3.642257471e-005)

The above profile "hints" are mostly concerning scaling the expected rows.

Speaking of TCF it's interesting to not that in DB Optimizer the TCF values of estimate verses actual come out closer in the optimize query verses the original.

Below are the explain plans for the default plan, plan with profile and the DB Optimizer tuned plan. Notice the "Orders of Magnitude" column. This column shows the orders of magnitude that the estimated rows differed from the actual rows per line in the execution plan. The larger the difference the bigger the discrepancy between what the optimizer expected and what actually happened

Default Plan and TCF

Profile Explain and TCF

DB Optimized Plan and TCF

Ironically the plan using the profile has the worst TCF discrepancies

Oracle's SQL Tuning Pack - how well does it work?

2010-07-01T16:52:00.000-07:00

Have you used Oracle's SQL Tuning Pack? What were your experiences like?
At my last consulting gig 2 out 3 statements I "tuned" with the SQL tuning pack actually ran significantly more slowly after applying the profiles recommended. I thought it was just my bad karma and luck of the draw until recently. Recently I've started asking other DBAs what their experiences were and generally finding less than enthusiastic responses. In the classes on Oracle 10g and OEM, I've always cautioned that profiles should not be blindly trusted and give an example using a Swingbench query where the SQL runs more slowly after the profile.If applying the profile, I recommend to jump immediately to the SQL performance page and to verify the effect of the profile on the load of the query.

Above is a page from OEM after the SQL Tuning Advisor has been run, a profile is suggested, and now I can implement the profile.

After the profile has been implemented, I'd recommend clicking on the SQL_ID to see go to the SQL details page and see what the new execution plan looks like on the load chart

In the above SQL details we can see the new execution plan hash 467377872 and that it's load is now higher. The number of executions by the application, swingbench remained the same, but the load by the SQL statement with the new profile is higher.
At this point I'd click on "Tuning Information" and back out the profile.

I thought the above example was a fluke along with the couple of bad experiences I had at a customer site but after asking a number of other DBAs who have used the SQL Tuning Advisor and who have responded less than enthusiastically, I'm now curious to poll a wider audience.

A couple of thoughts:

One, I think the reason the STA's profile created more load is because the application, swingbench, actually only uses one row from the query result set and ignores the rest of the result set. I'm guessing that the STA tuned the query for the entire result set.

Two, it seems to be a serious flaw that the STA doesn't actually run the query with the new profile to compare the before and after. At least this should be offered as an option.

It seems that Oracle 11 will address some of this issues in a round about way with SQL baselines and the evolve. See http://www.oracle.com/technology/oramag/oracle/09-mar/o29spm.html

From the above link, for example


                     Baseline Plan       Test Plan   Improv. Ratio
                     --------------      ---------   -------------
Execution Status:          COMPLETE       COMPLETE
Rows Processed:                   0              0
Elapsed Time(ms):              5036           1033         4.88
CPU Time(ms):                   254            700          .36
Buffer Gets:                   1728          43945          .04
Disk Reads:                     254             22        11.55
Direct Writes:                    0              0
Fetches:                         49             22         2.23
Executions:                       1              1

Though I haven't investigated how the SQL Baselines and the evolve option are incorporated in OEM 11g.

DB Optimizer Demo Webinar June 22 @ 9am PST

2010-06-22T17:16:00.000-07:00

We had almost 2000 people sign up for the webinar on SQL tuning with Jonathan Lewis and I. After the webinar many people had questions on how to harness DB Optimizer for performance tuning on their database whether DB2, SQL Server, Oracle or Sybase so
tomorrow, June 22 at 9am PST I'll be giving a live demo of the different parts of DB Optimizer and how to use them.
To view this webinar sign up at
http://forms.embarcadero.com/forms/AMUSCA1006DBOpti25Web-ZandyKrisher

Data Structures: Knuth Got It Wrong

2010-06-16T10:46:00.000-07:00

(Via Slashdot http://developers.slashdot.org/story/10/06/14/2225251/Knuth-Got-It-Wrong)

Full article at http://queue.acm.org/detail.cfm?id=1814327

Data structures and how data is grouped, stored and manipulated seems to be a lost art in computer programming yet it's the cornerstone to programming.

Poul-Henning Kamp's article is a refreshingly clear perspective on performance and datastructures, check it out.

Reminds me somewhat of the Oracle notion of how deep is your index

As you can see the traditional binary heap, on left, is deeper than the new structure, the "b-heap", on the right. The "b-heap" is slightly computationally more expensive but if less pages are visited avoiding visiting any paged out pages, the "b-heap" will perform much better.

The article goes into detail quite normal situations where the new structure performs an order of magnitude better than the traditional binary heap.

(thanks to Mark Bobak for sharing this link with me)

Jonathan Lewis / Kyle Hailey Webinar Replay

2010-06-14T14:10:00.001-07:00

Thanks to everyone who joined for the webinar with Jonathan Lewis last Thursday. I've been getting a number of inquiries asking if the webinar was recorded. The webinar was recorded and you can view it at
http://www.embarcadero.com/master-sql-tuners-oracle-lewis-hailey
Here is a snippet from my part using DB Optimizer

It's a bit fuzzy on youtube. The real presentation is high quality.
Here is a clear image from DB Optimizer:

Cursor_sharing : a picture is worth a 1000 words

2010-06-08T13:01:00.000-07:00

Anyone who has been around Oracle performance over the years knows the grief that hard parsing SQL queries can cause on highly concurrent applications. The number one reason for hard parsing has been applications that don't use bind variables. Without bind variables queries that would otherwise be shared get recompiled because their text is different and Oracle treats them as different queries. Oracle addressed this issue with a parameter called cursor_sharing. The parameter cursor_sharing has three values
exact - the default
similar - replace literals with bind variables, if a histogram keep literal in place
force - replace literals with bind variables and use existing plan if it exists
Here is what the load looks like going from the default, exact, to the value force on a load of the same query but a query that doesn't use bind variables:

looks like a significant load savings - impressive!

Now many people tell me that they think there are bugs with "force" and that you should use "similar". The value similar does a similar thing but if there are histograms on the column, then Oracle will attempt, in certain cases, to have different plans based on different values. Sounds cool huh? Well their are bugs. Here is the same load with similar:

If we look at the different child cursors for this statement we find that Oracle, instead of sharing the children creates a different one for each execution:

This bug still seems to exist on 11gR2 :

Here is the code for the examples I (run by 8 users on 10g and 12 users on 11g)

--alter session set cursor_sharing=exact;
--alter session set cursor_sharing=force;
--alter session set cursor_sharing=similar;
declare
 l_cursor integer default 0;
 stmt varchar2(400);
 ret number;
BEGIN
 select hparse.nextval into ret  from dual;
 dbms_random.seed(ret);
 FOR i IN 1..1000  LOOP
   l_cursor:=dbms_sql.open_cursor;
   stmt:='SELECT  count(*) FROM t1 where c1  < '|| 
     dbms_random.value()||' and c2  < '||dbms_random.value();
   execute immediate stmt into ret;
   dbms_sql.close_cursor(l_cursor);
 END LOOP;
END;
/

Table t1 has no histograms. In the case above it had one row, but results were similar with no rows:

create table t1 (c1 number, c2 number);

insert into t1 values (0,0);

commit;

The issue should be addressed in 11g with a combination of cursor_sharing and adaptive cursor sharing

http://optimizermagic.blogspot.com/2010/03/explain-adaptive-cursor-sharing.html

More VST Notation

2010-06-08T11:26:00.000-07:00

Just got DB Optimizer 2.5 out the door two weeks ago and now work is going fast on 2.5.1 slated for beginning of August. Already added EXISTS and NOT EXISTS notation into the diagrams:

SELECT
  cs.customerid,
  cs.firstname,
  cs.lastname,
  mr.rentalid,
  mr.duedate,
  mr.totalcharge,
  ri.itemnumber
FROM
  MOVIES.customer cs,
  MOVIES.movierental mr,
  MOVIES.rentalitem ri
WHERE
  LENGTH (cs.lastname) = 5 AND
  cs.zip > 75062 AND
  1 < cs.customerid + 2 AND
  cs.phone BETWEEN 9625569900 AND 9999569900 AND
  ROUND (ri.rentalid) > 10 AND
  TRUNC (ri.itemnumber) > 1 AND
  mr.totalcharge > (SELECT AVG (totalcharge)
      FROM MOVIES.movierental) AND
  ri.moviecopyid NOT IN (SELECT mc.moviecopyid
         FROM MOVIES.moviecopy mc
         WHERE
         mc.copyformat = 'vhs' AND
         mc.copycondition = 'old' AND
         mc.movieid IN (SELECT mt.movieid
            FROM MOVIES.movietitle mt
            WHERE
              mt.year < 1990 AND
              mt.rating IN ('pg', 'r') AND
              mt.categoryid IN (SELECT mc.categoryid
                  FROM MOVIES.moviecategory mc
                  WHERE mc.rentalprice = (SELECT MAX (rentalprice)
                        FROM MOVIES.moviecategory
                        WHERE categoryid = mc.categoryid)))) AND
  mr.CUSTOMERID = cs.CUSTOMERID AND
  ri.RENTALID = mr.RENTALID

Identifying Execution Plan Problems

2010-05-30T08:30:00.000-07:00

(original at http://sites.google.com/site/embtdbo/tuner/oracle-tcf-query-and-lios-per-row)
Based on the idea by Wolfgang Breitling that we can identify cost and cardinality errors of Oracle's optimizer by looking at the discrepancies between the ESTIMATED rows and the ACTUAL rows in each line of the execution plan.
I also added in a calculation to see how many IOs we were doing per row returned.
In order to get much out of this script you have to have run the query with the hint

/*+ gather_plan_statistics */
or
set statistics_level=all
or
sql_trace=true (only way on 9i other than an underscore parameter)


col cn format 99
col ratio format 99
col ratio1 format A6
--set pagesize 1000
set linesize 140
break on sql_id on cn
col lio_rw format 999
col "operation" format a60
col a_rows for 999,999,999
col e_rows for 999,999,999
col elapsed for 999,999,999

Def v_sql_id=&SQL_ID

select
       -- sql_id,
       --hv,
       childn                                         cn,
       --ptime, stime,
       case when stime - nvl(ptime ,0) > 0 then
          stime - nvl(ptime ,0) 
        else 0 end as  elapsed,
       nvl(trunc((lio-nvl(plio,0))/nullif(a_rows,0)),0) lio_rw,
       --id,
       --parent_id,
       --starts,
       --nvl(ratio,0)                                    ratio,
       ' '||case when ratio > 0 then
   rpad('-',ratio,'-') 
       else 
   rpad('+',ratio*-1 ,'+') 
       end as                                           ratio1,
       starts*cardinality                              e_rows,
                                                       a_rows,
       --nvl(lio,0) lio, nvl(plio,0)                      parent_lio,
                                                         "operation"
from (
  SELECT
      stats.LAST_ELAPSED_TIME                             stime,
      p.elapsed                                  ptime,
      stats.sql_id                                        sql_id
    , stats.HASH_VALUE                                    hv
    , stats.CHILD_NUMBER                                  childn
    , to_char(stats.id,'990')
      ||decode(stats.access_predicates,null,null,'A')
      ||decode(stats.filter_predicates,null,null,'F')     id
    , stats.parent_id
    , stats.CARDINALITY                                    cardinality
    , LPAD(' ',depth)||stats.OPERATION||' '||
      stats.OPTIONS||' '||
      stats.OBJECT_NAME||
      DECODE(stats.PARTITION_START,NULL,' ',':')||
      TRANSLATE(stats.PARTITION_START,'(NRUMBE','(NR')||
      DECODE(stats.PARTITION_STOP,NULL,' ','-')||
      TRANSLATE(stats.PARTITION_STOP,'(NRUMBE','(NR')      "operation",
      stats.last_starts                                     starts,
      stats.last_output_rows                                a_rows,
      (stats.last_cu_buffer_gets+stats.last_cr_buffer_gets) lio,
      p.lio                                                 plio,
      trunc(log(10,nullif
         (stats.last_starts*stats.cardinality/
          nullif(stats.last_output_rows,0),0)))             ratio
  FROM
       v$sql_plan_statistics_all stats
       , (select sum(last_cu_buffer_gets + last_cr_buffer_gets) lio,
                 sum(LAST_ELAPSED_TIME) elapsed,
          child_number, 
  parent_id,
                 sql_id   
         from v$sql_plan_statistics_all   
         group by child_number,sql_id, parent_id) p
  WHERE
    stats.sql_id='&v_sql_id'  and
    p.sql_id(+) = stats.sql_id and
    p.child_number(+) = stats.child_number and 
    p.parent_id(+)=stats.id
) 
order by sql_id, childn , id
/
clear breaks

output like


Enter value for sql_id: g2w9n4gksyys6
old  59:     stats.sql_id='&v_sql_id'  and
new  59:     stats.sql_id='g2w9n4gksyys6'  and

  ELAPSED LIO_RW RATIO1  E_ROWS A_ROWS operation
---------- ------ ------ ------- ------ ---------------------------------
         0      0                     1 SELECT STATEMENT
 5,720,456      0              1      1  HASH GROUP BY
    29,711      0                 1,909   NESTED LOOPS
         0      0  +++         1  1,909    NESTED LOOPS
 1,969,304      0  +++         1  1,909     NESTED LOOPS
         0      0  +++         1  2,027      NESTED LOOPS
 7,939,649      0  +++         1  1,656       NESTED LOOPS
   716,054      0  +++         1  1,657        NESTED LOOPS
   270,201      0  ++         39 23,171         HASH JOIN
        23      0              5      1          JOIN FILTER CREATE :BF00
        31      1              5      1           TABLE ACCESS BY INDEX R
        14      2              5      1            INDEX RANGE SCAN PS0PA
   141,467      0         18,503 23,171          VIEW  VW_SQ_1
 3,032,120      0         18,503 23,171           HASH GROUP BY
   152,564      0        163,420 33,020            JOIN FILTER USE :BF000
   407,746      0        163,420 33,020             MERGE JOIN
        55      0              5      1              SORT JOIN
        12      2              5      1               INDEX RANGE SCAN PS
    79,435      0         40,000 33,020              SORT JOIN
   119,852      0         40,000 40,000               INDEX FAST FULL SCA
 2,959,031     13  -      23,171  1,657         TABLE ACCESS BY INDEX ROW
   944,887      1         23,171 23,174          INDEX RANGE SCAN WB_JOB
   102,650      0          1,657  1,656        VIEW PUSHED PREDICATE  VW_
    73,769      0          1,657  1,657         SORT AGGREGATE
    25,617      0          1,657  1,657          FIRST ROW
   225,497      1          1,657  1,657           INDEX RANGE SCAN (MIN/M
   357,872      0          3,312  2,027       TABLE ACCESS BY INDEX ROWID
 3,655,774      1          3,312  2,027        INDEX RANGE SCAN WB_RETROP
   199,884      0          2,027  1,909      TABLE ACCESS BY INDEX ROWID 
   317,793      1          2,027  1,909       INDEX RANGE SCAN PS_RETROPA
    71,534      0          1,909  1,909     INDEX RANGE SCAN PS#RETROPAYP
    18,396      0          1,909  1,909    TABLE ACCESS BY INDEX ROWID PS

I can see that are 5 lines where the optimizer only expect 1 row and the actual results were over 1000, ie 3 orders of magnitude difference.
These are the three lines with
+++
There is one line with
-
where actual was an order of magnitude smaller. On that same line wee see it's one of the slower lines almost 3 seconds and that the were 13 lio's per row returned, which is sign of inefficiency.

Oracle Video Demos

2010-05-30T08:15:00.000-07:00

Just found this one

http://www.oracle.com/technology/obe/demos/admin/demos.html

video demos of Oracle features - pretty cool. For example one on managed baseline metrics in OEM:

http://www.oracle.com/technology/obe/demos/10gr2/metric_baselines.viewlet/metric_baselines_viewlet_swf.html

On the subject of OEM 10g and adaptive thresholds, Doug Burn's blog has by far the best explanation of setting up an using them, see here:

http://oracledoug.com/serendipity/index.php?/plugin/tag/adaptive+thresholds

I never worked on the specifics of adaptive baselines, that was JB's specialtiy, but i did work on some UI mockups:

Here's what it actually looks like in OEM 10g:

SQLPLUS -prelim

2010-05-30T08:07:00.000-07:00

There is a way to connect to Oracle even when sessions are maxed out or there is a hang etc:

I knew about this a while back but its just one of those things that's important to remember when it's needed! Thus, so I can always know where to find it , I'm reposting:

http://oraclue.com/2008/09/25/sqlplus-preliminary-connection/

1) sqlplus -prelim / as sysdba

2) sqlplus /nolog
set _prelim on
conn / as sysdba

see more from Tanel Poder

http://tech.e2sn.com/oracle/troubleshooting/hang/how-to-log-on-even-when-sysdba-can-t-do-so

Visual SQL Tuning Webinar with Jonathan Lewis June 10, 2010

2010-05-26T22:45:00.000-07:00

http://www.embarcadero.com/master-sql-tuners-oracle-lewis-hailey

Tune in for the Ultimate SQL Tune-off

According to Jonathan Lewis, there is a basic strategy to writing efficient SQL, but it requires knowledge of the data, the metadata, and the intent of the query.

Jonathan will start with the basic principles of efficient data access and then show different ways of analyzing the data. He'll then describe a graphical approach to finding the most efficient access path for a query and then apply the method to a problem he recently saw with one of his clients.

But wait... is there more than one way to skin a cat? Kyle Hailey thinks so, and he's not afraid to let Jonathan know it!

Kyle's fancy footwork will include his own approach to Visual SQL Tuning (VST) with some ring side support from DB Optimizer XE. Hitting below the belt? You be the judge.

Live! June 10, 2010 at 11AM Pacific

Why graphics get a bad rap

2010-05-26T00:33:00.001-07:00

Can you figure out what this chart is trying to say? I have a hard time of it:

Here is the data laid out in a more compelling manner:

I can easily see the data correlations.

Central region, the first line, earned the most revenue, increased the most over last year and earned better than forecast.

Eastern earned the second highest but lost the most over last year and earned under forecast.

My ideas for the new layout came from Lars Schubert

See for example http://cuboo.files.wordpress.com/2010/05/oracle_success.png

Lars' excel spread sheet with data and variations http://bit.ly/aeK6PA

More info on http://www.perceptualedge.com/blog/?p=784

Visual SQL Tuning Webinar with Jonathan Lewis June 10, 2010

2010-05-23T08:20:00.000-07:00

UPDATE: register for webinar at

http://www.embarcadero.com/master-sql-tuners-oracle-lewis-hailey

Stay tuned for a live free webinar on Visual SQL Tuning with Jonathan Lewis and Kyle Hailey on June 10th brought to you by Embarcadero Technologies.

DB Optimizer's Visual SQL Tuning diagram:

Visual SQL Tuning article by Jonathan Lewis

http://www.simple-talk.com/sql/performance/designing-efficient-sql-a-visual-approach/

Jonathan Lewis and Visual SQL Tuning

2010-05-05T15:40:00.000-07:00

Sometimes, it is a great idea to push away the keyboard when tackling the problems of an ill-performing, complex, query, and take up pencil and paper instead. By drawing a diagram to show off all the tables involved, the joins, the volume of data involved, and the indexes, you'll see more easily the relative efficiency of the possible paths that your query could take through the tables.

-- Jonathan Lewis

Check out Jonathan's insightful article on the power of graphically representing a SQL statement to help identify efficiencies and inefficiencies in a SQL query:

http://www.simple-talk.com/sql/performance/designing-efficient-sql-a-visual-approach/

Oaktable Website

2010-04-23T21:30:00.000-07:00

Thanks to the valient work of James Morle there is a awesome new website aggregating the work and postings of the members of the Oaktable network. Check it out:

http://www.oaktable.net/

Types of Data Visualization

2010-04-23T07:59:00.000-07:00

I'm fascinated by data visualization and here is a fun little widget - the periodic chart of visualization methods. Make sure and pass your mouse over each cell to see the example graphic popup.

http://www.visual-literacy.org/periodic_table/periodic_table.html

calculating the SQL_ID hash from the SQL_TEXT

2010-03-31T15:07:00.000-07:00

This really cool:

http://www.slaviks-blog.com/2010/03/30/oracle-sql_id-and-hash-value/

he explains a how to calculate the sql_id from the SQL_TEXT. The SQL_ID is just a hash of the text.

(saw on Tanels blog at http://blog.tanelpoder.com/2010/03/31/calculate-sql_id-and-sql_hash_value-from-sql-text/ ) calculating

VST - complete for DB Optimizer 2.5 !

2010-03-26T15:34:00.000-07:00

The last step is finished before we functioinal freeze on the VST diagrams in DB Optimizer 2.5. We have now added Filter Ratios to the diagram ! Functional freeze is next week and production date is targeted for May 3.

blue numbers are the percent of the table returned after the predicate filters have been applied
red numbers are the two table join sizes
green numbers are the table sizes

The idea is to start at the most selective filter and then join into keep the running row set to the smallest size.

Start at A, the most selective filter

Join to C, the smallest running row set size

Join to G, the smallest row set size

Join last to D

This path was almost 3x as fast as the default path chosen by the optimizer. I just looked at the diagram , order the tables in that fashion and used the /*+ ORDERED */ hint

Interesting items in the diagram

only fields used in the where clause are shown by default
clicking on a link shows the fields used in the join (above I've clicked on two links higlight two joins)
Fields with an "F" have a filter on them

There many other interactive features in the diagram.

The text for this query was

select distinct * from foo.a, foo.c, foo.d, foo.g
WHERE a.planted_date = to_date('02/10/2008','dd/mm/yyyy')
AND a.pears = 'D'
AND a.green_beans = '1'
AND a.planted_date = c.planted_date
AND a.pears = c.pears
AND a.zuchinis = c.zuchinis
AND a.brocoli = c.brocoli
AND a.planted_date = d.planted_date
AND a.pears = d.pears
AND a.harvest_size = d.harvest_size
AND c.oranges = d.oranges
AND c.apples = d.apples
AND (d.lemons = 0 OR d.lemons IS NULL)
AND a.planted_date = g.planted_date
AND a.pears = g.pears
AND a.harvest_size = g.harvest_size
AND c.oranges = g.oranges
AND c.apples = g.apples
AND (g.lemons = 0 OR g.lemons IS NULL)
and a.zuchinis='0236'
ORDER BY a.zuchinis, a.brocoli;

VST - Visual SQL Tuning , the next generation

2010-03-19T12:31:00.000-07:00

If you saw my presentation at HOTSOS 2010 or attended one of my recent Oracle 10g Performance Tuning course, you know I'm excited about Visual SQL Tuning or VST diagrams.

Now, I'm even more excited because we are adding statistics to the diagram! For example:

In the above diagram the #s in red are the two table join set sizes and the numbers in green are the table sizes. I can see immediately that the query should return 0 rows because the join (rentalitem,movierental) returns 0 rows. I can also see that the join (mr,movierental) is a horribly inefficient join it returns over 1 million rows for a join between 2018 rows and 2018 rows.

The VST diagram tells me immediately what's happening in the query and now with the statistics I can immediately see where there are efficient joins and inefficient joins.

The text for this query, which has none of this information is:

SELECT

cs.customerid,

cs.firstname,

cs.lastname,

mr.rentalid,

mr.duedate,

mr.totalcharge,

ri.itemnumber

FROM

MOVIES.customer cs,

MOVIES.movierental mr,

MOVIES.rentalitem ri

WHERE

LENGTH (cs.lastname) = 5 AND

cs.zip > 75062 AND

1 <>

cs.phone BETWEEN 9625569900 AND 9999569900 AND

ROUND (ri.rentalid) > 10 AND

TRUNC (ri.itemnumber) > 1 AND

mr.totalcharge > (SELECT AVG (totalcharge)

FROM MOVIES.movierental) AND

mr.CUSTOMERID = cs.CUSTOMERID AND

ri.RENTALID = mr.RENTALID

Stay tuned because next week we will be adding filter ratios to the diagram. A filter ratio is the ratio of rows returned after a predicate filter has been applied to a table. In the above diagram CUSTOMER and RENTALITEM both have predicate filters as denoted by the "F" in green.

In DB Optimizer the diagram is context sensitve so if I click on a table in the diagram the text will be highlighted

VST diagrams are available now in DB Optimizer 2.0 but in May we are scheduled to release VST diagrams with displayed statistics in with DB Optimizer 2.5 !

Big thanks to DB Optimizer's development team for this awesome work. Special kudo's to Stan who's recuperating and itching to even start working again even from his bed!

managing statistics on partitions

2010-03-17T07:21:00.000-07:00

The best methods for managing statistics in general on Oracle is an area that has yet to well ironed out but when it comes to partitions is all the more challenging. Doug Burns is putting together a set of blog post attempting to iron out some of the issue :

Part 1 – In which we see a simple example and do a default stats collection
Part 2 – In which we consider Global Stats
Part 3 – In which subpartitions and aggregation cause problems
Part 4 – In which our hero fights his way through stats aggregation woes
Part 5 - In which we encounter a partition exchange

Nice aggregated listing via Jonathan Lewis

Trending with Statspack

2010-03-16T22:15:00.000-07:00

Anjo use to do statspack analysis at oraperf.com which has been out of order every time I've looked over the past few years.

Tim Gorman published scripts years ago to output trending reports on statspack which AFAIK are still a valid resource today.

But now I see there is a web site to upload zips of statspack reports and output graphical trending reports. Looks interesting:

http://www.ondatafine.com/

Monitoring SQL run on MySQL

2010-03-16T15:01:00.001-07:00

Nice example of using Wireshark to monitor what's getting run on MySQL.

http://feedblog.org/2008/07/29/debugging-mysql-applications-with-wireshark/

I played around with something similar for Oracle didn't get to far. It's an interesting idea for monitoring,auditing and/or creating replay scripts.

Bug or Feature

2010-03-16T14:46:00.000-07:00

I love this little cartoon

comes from http://www.oraclealchemist.com/oracle/this-bug-brought-to-you-by-the-letter-u/

which is a funny story about why OEM DB Control wouldn't start up because the hostname started with a "U" believe it or not (read the blog post for more info!). It's yet another reason why I have issues with OEM. When OEM fails to work it can be exasperating. Just last week I was watching a presentation one OEM where OEM wouldn't start for 20 minutes - provide for quick thinking on the presenters part to fill in that 20 minute hole. I was tempted to run up to the presenter and give them a DVD with instant on DB Optimizer which require no agents and no install. Just put the DVD in (also available via free download) and click on the executable and point it at any database (SQL Server, Sybase, DB2 or Oracle ) and voila you get a load chart of average active sessions and a breakdown on where the load is coming from immediately.

What Bind Variable values were used in my Query

2010-03-16T14:06:00.000-07:00

Check out this query by Kerry Osborne via Oracle-L (archives at Oracle-l Archives) for getting the peeked bind variable values from v$sql_plan. These values are in a raw format so the following query requires a function to decode the values. See the function code listing at the end

col bind_name for a20
col bind_type for a20
col value for a40
select
bind_name,
decode(bind_type,1,'VARCHAR2',2,'NUMBER',bind_type) bind_type,
decode(bind_type,1,display_raw(bind_data,'VARCHAR2'),2,display_raw(bind_data,'NUMBER'),bind_data) value
from (
select
extractvalue(value(d), '/bind/@nam') as bind_name,
extractvalue(value(d), '/bind/@dty') as bind_type,
extractvalue(value(d), '/bind') as bind_data
from
xmltable('/*/*/bind'
passing (
select
xmltype(other_xml) as xmlval
from
v$sql_plan
where
sql_id like nvl('&sql_id',sql_id)
and child_number = '&child_no'
and other_xml is not null
)
) d
)
;

create or replace function display_raw (rawval raw, type varchar2)
return varchar2
is
cn number;
cv varchar2(32);
cd date;
cnv nvarchar2(32);
cr rowid;
cc char(32);
begin
if (type = 'NUMBER') then
dbms_stats.convert_raw_value(rawval, cn);
return to_char(cn);
elsif (type = 'VARCHAR2') then
dbms_stats.convert_raw_value(rawval, cv);
return to_char(cv);
elsif (type = 'DATE') then
dbms_stats.convert_raw_value(rawval, cd);
return to_char(cd,'dd-mon-yyyy');
elsif (type = 'NVARCHAR2') then
dbms_stats.convert_raw_value(rawval, cnv);
return to_char(cnv);
elsif (type = 'ROWID') then
dbms_stats.convert_raw_value(rawval, cr);
return to_char(cnv);
elsif (type = 'CHAR') then
dbms_stats.convert_raw_value(rawval, cc);
return to_char(cc);
else
return 'UNKNOWN DATATYPE';
end if;
end;
/

Interesting to note that the v$sql_bind_capture has captured bind variables but not necessarily the ones that were peeked at optimization and these captured values can be recaptured without being used in the optimization of the query. From http://jonathanlewis.wordpress.com/2008/07/24/bind-capture/

by using an ‘alter system’ call to change the “_cursor_bind_capture_interval” to a (performance-threatening) 10 seconds on a small test system, I found that the values in v$sql_bind_capture would change fairly regularly as I re-executed a given query with constantly changing input bind values.

further reading about bind vars from the errorstack by Tanel:

http://tech.e2sn.com/oracle/troubleshooting/how-to-read-errorstack-output

who opened that DB Link

2010-03-05T16:02:00.000-08:00

very nice analysis of tracking down who has a dblink open on another database

http://jkstill.blogspot.com/2010/03/whos-using-database-link.html

ORASRP: 10046 trace file formating

2010-03-05T13:55:00.000-08:00

In case you have seen this free tool:

http://www.oracledba.ru/orasrp/

It's pretty cool. Takes 10046 trace files and analyzes and formats them.

Here is an example output: http://www.oracledba.ru/orasrp/sample2.html