Advances in SQL Tuning Techniques at HOTSOS

Late breaking news - I'll be speaking at the 2010 HOTSOS conference!

http://www.hotsos.com/sym10/sym_speakers_hailey.html

Advances in SQL Tuning Techniques

In the past few years some novel SQL tuning techniques have appeared such Oracle 10g’s SQL profiles, 11g's SQL baselines , extended execution statistics, Tuning by Cardinality Feedback, Visual SQL Tuning, Oracle 11g’s real time SQL monitoring and plan stability. Find out what about these methods and how to best harness them easily and effectively on your system.

Learning objectives:

1) How to identify queries that are potentially suboptimal

2) How to analyze the query

3) What steps to take to come up with an optimal and stable plan

HOTOS is my favorite Oracle conference both for the high caliber of the speakers as well as the participants. Here is the impressive speaker list for 2010:

http://www.hotsos.com/sym10/sym_speakers.html

I'll also be teaching a 2 day Oracle performance and tuning seminar on the following dates and locations:

Melbourne Feb 22-23

Sydney Feb 25-26

Singapore Mar 1-2

Hong Kong Mar 4-5

Graphics vs Text : Picture superiority effect

According to the picture superiority effect, concepts are much more likely to be remembered experientially if they are presented as pictures rather than as words.According to dual-coding theory by Allan Paivio (1971, 1986), memory exists either (or both) verbally or "imaginally". Concrete concepts presented as pictures are encoded into both systems; however, abstract concepts are recorded only verbally.

http://en.wikipedia.org/wiki/Picture_superiority_effect

Oracle's Cardinality Hint, Profiles, Outlines and SQL Baselines

This is more of a bookmark for a future blog post on using the Cardinality hint. This post by Greg Rahn give some example and I posted in the comments some more detailed examples:

http://structureddata.org/2007/11/21/troubleshooting-bad-execution-plans/

Here is another excellent post by Kerry Osborne on SQL Baselines:

http://kerryosborne.oracle-guy.com/2009/04/oracle-11g-sql-plan-management-sql-plan-baselines/

which made me ask in the comments why one would want to use Outlines vs Profiles vs Baselines, in particular what advantages if any Profiles have.

Interesting question. I had an online dialog with a guy name Randolf Geist a year ago or so where he convinced me to look closer at SQL Profiles. Up until that point I had only seen SQL Profiles that were created by the SQL Tuning Advisor and I was not very impressed. They tended to go sour after a little time because of the OPT_ESTIMATE hint, but I digress. The answer is yes, I think SQL Profiles have a couple of advantages over Outlines at this point.

1. They have the ability to be used on multiple statements (ones that are the same except for literals) using the force matching signature bit.

2. They are newer. I’m starting to worry about Outlines getting buggy due to lack of attention. Documentation says they are deprecated and although they continue to work in 11g, I am a bit leery of continuing to use them at this point. (the whole hint based mechanism is tricky enough as it is).

11gR2 includes a procedure to migrate Outlines to Baselines by the way (DBMS_SPM.MIGRATE_STORED_OUTLINE). At any rate, I am not implementing Outlines at this point.

Explain Plan vs Real Plan on Oracle

A good part of my presentation Friday at NoCOUG was on explain plan command being an estimate of the actual execution plan. Now on the Oracle-L forum list, I see the same question being asked "Under what conditions, could the plan I get out of ‘explain plan’ differ from what I get from running the sql statement?" and Tanel Poder giving a succinct clear list of reasons:

main reasons:
1) The optimizer statistics the EXPLAIN PLAN ends up using are different from the statistics the other session ended up using
2) Explain plan does not use bind variable peeking thus will not optimize for current bind variable values
3) Explain plan treats all bind variables as VARCHAR2, thus you ma have implicit datatype conversion happening during the plan execution, (meaning to_char,to_number functions are added around variables/columns) and this for example may make optimizer to ignore some indexes if you get unlucky.

with the caveat:

Little correction to point number 3 - of course explain plan doesn't really execute the plan so the implicit datatype conversion you see is in the explained plan only, but if you actually execute the statement (with correct bind datatypes) then there's no implicit datatype conversion. And that's where the difference comes from...

From the presentation Friday at NoCOUG:

more examples on previous blog entry

http://db-optimizer.blogspot.com/2009/09/explain-plans.html

Future of Cloud Computing and Relational Databases

I was talking to someone from Amazon who indicated that Amazon is moving more and more processing away from Oracle into the middle tier and object oriented databases with the idea being to reduce usage of Oracle as well as to remove data processing delay times. The time it takes me as a consumer to put an order in can be reduced if Amazon just at takes all my information and says "purchase complete" and then process all the data and mails me later if anything goes wrong.

Taking this idea farther, there are a lot of cases where processing doesn't have to immediately be transactional. The transactional can be delayed in a lot of cases.

One accutely relevant recent example has been the recent naked shares scandal which helped sink the US banks much faster and deeper. These naked shares are when brokerage houses sell shares of a company that they don't really have. Normally a brokerage house would be transactional about buying and selling, but in this case the brokerage house can sell without actually owning the stock and buy the stock later. If the risk of this kind of inconsistency is smaller than the overhead a slower more expensive system insures consistency then why not go with the inconsistency?

Of course in the case of the brokerage scandal the inconsistency might have been intentional and if so and if regulated would bring back a transactional system. There will always be the requirement for transactional systems guaranteeing the usage of relational databases.

Interesting article on the subject (including quotes from me)

http://www.sdtimes.com/content/article.aspx?ArticleID=33912

Clean Installs (Embarcadero's All Access)

Ever have problems de-installing software on windows? Well Embarcadero has a new technology where there is no install for it's windows install. It's called "Instant On" and is part of Embarcadero's All Access suite of products. Just execute the program and it runs immediately without any install (the way it should work, like on UNIX).

This new "Instant On " functionality is the technology on 30 USB drives I gave away at my NoCOUG talk. The USB contained DB Optimizer, the product I work with the new Visual SQL Tuning (VST) as well as Embarcadero's other database products. The USB is good for 30 days and has another 30 day renewable trial license make 2 free months of trial time.

People who didn't get the USBs were asking me if I could sent some. I don't have any more but the same files are available online at

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

Just download the "All Access" client and run it. All Access will display all of Embarcadero's tools both database and codegear and allow you to download and trial any of these products with this new Instant On functionality. Super cool.

The above is a screen shot of the All Access client. Just click on any tool to automatically download the Instant On version of the product. DB Optimizer for example is in the bottom right, second from the bottom

Oracle Users Group - NoCOUG Fri 13th

Had an enjoyable day at the NoCOUG held at Oracle Corp, my old stomping grounds.

It was fun to see and catch up with Greg Rahn, John Beresniewicz, Graham Wood, Juan Loaiza, Maria Colgan and Tanel Poder. Tanel's presentation that I saw was the highlight for me. With in the first couple of minutes he showed some super timesaving devices for SQLPLUS in windows that I'd wanted for ages. For example, there seems to be no way to change the title of sqlplusw.exe but but by using sqlplus.exe in a cmd window you can with the simple dos "title" command. My first reaction was "I can't use a cmd window because cut and paste is wanky" then Tanel pointed out that classic cut and paste can be enabled via the cmd widow properties. He also showed the sqlplus command "set markup html on" which outputs sql query results in HTML - so what? well by spooling to a file with ".html" extention and using the dos command "start output.html" via sqlplus' host, we then get a browser with the data in an HTML table which is really nice when the data is wide. Same thing works with output to ".xls" - we get HTML data open directly in Excel. Very cool and convenient.

Tanel went over his famous snapper and finished with is latchprofX script for analyzing latch issues. The latchprofX script is a beautiful script and I'vce blogged at it before. You read more ot it at
Tanel Poder's Blog

I present my paper on SQL Tuning which is available in my previous post. One part of my paper that I was excited to present was Visual SQL Tuning (VST). Visualization and graphics are a passion of my (as anyone who knows me knows) . Graphics, when used wisely, can convey information more powerfully and easily than the equivalent text. The text of SQL plans and SQL traces have always been a chore for me to read and that's why I'm so excited about VST. VST turns SQL tuning into a fun activity not to mention speeding up my analysis time and improving the depth of my analysis. Since analyzing SQL for performance issues is one of the foundations of DBA and databse development, these new developments in VST is an important new development in database performance optimization.

Tanel, Kamran, me and Greg Rahn at NoCoug

Oracle Books online

Did you know Google books has some amazing Oracle resources on line?

I'm giving a presentation tomorrow at the Northern California Users Group at Oracle headquarters and am using alot of material from the following books which I recommend, and here are the links to their contents available online at Google books:

Troubleshooting Oracle Performance by Christian Antognini
Cost-Based Oracle Fundamentals by Jonathan Lewis
SQL Tuning by Dan Tow

I'm also indebted to the following web sites which I recommend for Oracle SQL tuning:

http://kerryosborne.oracle-guy.com/

http://oracle-randolf.blogspot.com/

http://structureddata.org/

http://www.centrexcc.com/

Here is my presentation from NoCOUG

http://www.perfvision.com/ftp/Nocoug_Nov_2009_sqltuning_nointro.ppt

Oracle 11g Baselines

this site

http://kerryosborne.oracle-guy.com/2009/04/oracle-11g-sql-plan-management-sql-plan-baselines

is the best source of information I've found on Oracle baselines an exciting new feature in Oracle 11g that seems to provide a solid solution for using hints on queries but still being able to track possible better execution plans with the evolve option.

Real Time SQL Monitoring

here is a feature that I'm excited about:

Real Time SQL Monitoring

http://askdba.org/weblog/2009/09/11gr2-monitoring-real-time-sql-plan-execution-from-oracle-enterprise-manager/

Active Session History has added fields that can track the execution id of a statement as well as the row source (ie line in the explain plan) that the query is executing. For long running queries this can be a way to see where the query is at as well as seeing which row sources are more expensive and for short running queries one can build up a statistical picture of what row sources are expensive.

11g ASH has added the fields

SQL_EXEC_ID

SQL_EXEC_START

that can track and distinguish different executions of the same sql.

11g ASH has added the fields

SQL_PLAN_LINE_ID

SQL_PLAN_OPERATION

SQL_PLAN_OPTIONS

That can track the particular line in the execution plan that is executing

Oracle EM Desktop Widgets

Oracle add desktop widgets to OEM. Wonder if this is targeted for mobile devices or not?

http://ilmarkerm.blogspot.com/2009/11/oracle-enterprise-manager-desktop.html

Nice article on DB Optimizer:

Taking the black art out of SQL tuning

by Martin Heller

Embarcadero DB Optimizer 2 takes a methodical approach to SQL query analysis and optimization

November 9, 2009