SQL Joins

Growing up on Ansi 89 and working at Oracle for 10 years, some of the the cleaner SQL join notation seems new to me. Here is a great write up on joins and join notation:

http://blog.mclaughlinsoftware.com/oracle-sql-programming/basic-sql-join-semantics/

SQL Videos

More videos from Stéphane Faroult

I have yet to go through these videos but based on the previous two I posted from Stephane, these should be well worth looking at

Finding the problem is the hard part

Interesting blog on how finding performance problems takes 80% of the mean time to resolution.

Sybase ASE Performance and Tuning

This is why I like DB Optimizer so much. DB Optimizer let's me find the root problem in seconds on a database.

Types of subqueries

Types of subqueries (today I couldn't think of the term "inline view")

• subquery ( subselect used in where clause)
• correlated subquery (subselect uses fields from outer query)
• scalar subquery (subselect in select list)
• inline views (subselect in from clause)

Tune SQL

One of the main things I do to tune SQL is to encourage the optimizer to take path.

I often take the most important steps to do first and put them in a sub select. I've been meaning to write something up about this for years. Unfortunately all the great examples have been lost at customer sites happily left behind after my work was done. Only later did I kick myself for not having saved more. I have not taken the time or energy to come up with some good examples. Well, all is not lost because here is another great video from Stephen Faroult on this exact subject:

http://www.youtube.com/watch?v=ZVisY-fEoMw

The ideas he lays out

     1. take the highest seletivity steps and do them first (this is where I often use "inline views")

     2. sort the least amount of data , ie sort the key column and not all the data columns

     3. Join in non-key columns as late as possible

To summarise, I look for the central data (what Stephen calls the "key") and put this in an inline view , then sort, then finally join in any missing columns

Correlating SQL to Procedures

Starting in Oracle 10.2.0.3 (ie not 10.2.0.2 and below) it's easy to track what package and/or procedure a SQL execution came from using v$session and or v$active_session_history (ASH) which have the new fields

COUNT(*) SQL_ID calling_code
-------- ---------- ----------------------------------
     2 1xxksrhwtz3zf OE.NEWORDER => DBMS_RANDOM.VALUE
     2 1xxksrhwtz3zf OE.NEWORDER => DBMS_LOCK.SLEEP
    13 1xxksrhwtz3zf OE.NEWORDER
    76 dw2zgaapax1sg OE.NEWORDER
   131 75621g9y3xmvd OE.BROWSEANDUPDATEORDERS
   163 0uuqgjq7k12nf ORDERENTRY.NEWORDER

using SQL like ( http://www.perfvision.com/ash/ashpl2.sql)

Stephan Faroult's Videos

I love this video on "getting dreadful database performance" by Stephan Faroult.

The video is hilarious and infomative:

http://www.roughsea.com/vids/SQL_Best_Practices.html

He tackles top 5 keys to dreadful peformace

1. EAV "schema" 2:10

2. Unusable indexes 6:00

3. Hard parsing 7:36

4. Unitary processing 10:40

4.5 user functions (instead of join, ex: currency conversion) 12:19

5. views of views 14:46

Conclusion: most developers are young and clueless about database (java programmers?) and code must be refactored by devlopers not by DBAs in production databases, and hopefully with guidance by an experienced (older) expert.

The first topic EAVs was a huge problem at 3 of the last 4 companies I was at, including Oracle's OEM 10g Grid control, despite solid analysis of pro's and con's by John Beresniwisz.

Check out more on the EAV at: http://en.wikipedia.org/wiki/Entity-Attribute-Value_model

And by Tom Kyte at: http://en.wikipedia.org/wiki/August_24

Just emailing Stephan, I found out that his video was inspired by this fun video (though less related to UI or databases):

Identity 2.0

http://identity20.com/media/OSCON2005/oscon_videos/oscon_qt_sm.html

I like this kind of presentation. Fun and informative as well as efficiently re-iterating the important points.