ASH explodes in Oracle 11gR2

ASH is now at 93 fields in 11gR2, starting from an original 30 in 10gR1

Here is a spread sheet across 10.1.0, 10.2.0.1, 10.2.0.3,11.1, 11.2

http://spreadsheets.google.com/pub?key=t7c5YZ1hD_I25jLa2D6DdLg&output=html

For more information on ASH see http://ashmasters.com

Outlines from DBMS_XPLAN

dbms_xplan.display_cursor will output the hints for a plan.

These hints can then be taken and used in an outline for plan stability:

http://oracle-randolf.blogspot.com/2009/03/plan-stability-in-10g-using-existing.html

dbms_xplan.display_cursor - what are all those arguments?

Nice table on

http://www.psoug.org/reference/dbms_xplan.html

and some more discussion on

http://jonathanlewis.wordpress.com/2008/03/06/dbms_xplan3/

Diff'ing Explain Plans

Diff'ing explain plans is difficult and tedious.>Here is a new package function from Oracle for diff'ing explain plans
http://optimizermagic.blogspot.com/2009/09/whats-changed-between-my-new-query-plan.html
Of course I have my own ideas, some expressed here http://sites.google.com/site/youvisualize/sql-topology

but I will be outlining more visual ideas for sql plan analysis over this coming year.I have some exciting designs we are implenting in DB Optimizer (http://oraclemonitor.com)

plan stability and predicate evaluation order and more

Here's some nice code for looking at multiple child cursors with the same plan hash and evaluating through hashing other parameters to see if the plan is actually the same plan such as changes in the order of predicate filter evaluations

http://oracle-randolf.blogspot.com/2009/07/planhashvalue-how-equal-and-stable-are_26.html

Statistics on function in predicates for CBO

ASSOCIATE STATISTICS WITH FUNCTIONS my_function DEFAULT SELECTIVITY 0.1;
ASSOCIATE STATISTICS WITH FUNCTIONS my_function DEFAULT COST (10000, 1000, 0) /* cpu,io,net */;
http://www.oracle-developer.net/display.php?id=426#

Explain Plans

Explain Plans can be had via

• Explain Plan (version ? ancient)
• Trace Files (starting 7.3)
• Autotrace (starting 8.1.7)
• V$SQL_PLAN (starting 9iR2)

I was wondering which methods are dependable and which are not. Classically an explain plan can easily differ between the analyzer's session (such as a DBA's session) and the session being analyzed (like an end user session) . One of my first bug I logged back at Oracle in versin 6 on tkprof explain giving a different plan than actually happened in the trace. Now that there is v$sql_plan which contains the "live" plans for executed SQL, I was wondering if the other method had caught up. It doesn't seem so. It seemes that Autotrace uses EXPLAIN PLAN command even through 11gR1 despite the fact it does sampling.

Here is some info on Jonathan Lewis' scratchpad

http://jonathanlewis.wordpress.com/testing-autotrace/#comment-34301

Example of wrong plan because of bind variables:

http://www.jlcomp.demon.co.uk/wrong.html

Another blog post on Explain Plan giving wrong plan
http://kerryosborne.oracle-guy.com/2008/10/explain-plan-lies/

Example from Tom Kyte where Explain Plans assuming bind vars are characters even if they are defined as numbers

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:503512000346581816

An example of autotrace which uses Explain Plan giving the wrong execution plan:

Full presentation available on:

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

Latch: Cache Buffers Chains

Check out Tanel's analysis and scripts for cache buffer chains latch contention analysis. Solid stuff:
http://blog.tanelpoder.com/2009/08/27/latch-cache-buffers-chains-latch-contention-a-better-way-for-finding-the-hot-block/comment-page-1/#comment-2437

Alberto Dell'Era's XPLAN

Check out Alberto Dell'Era's SQL query analyzer - lots of good info like this compact table of indexes availble on the query:
http://www.adellera.it/blog/2009/06/07/optimizing-sql-statements-with-xplan/

--------------------------------------
02.|ColName     |1|2|3|4|5|P|U1|U2|R1|
03.--------------------U-U------------
04.|X           |1|1|2|1| |1|  |2 |  |
05.|PADDING     | | |1|2|1|2|R1|1 |  |
06.|RR          | | | | | | |  |  |1 |
07.|SYS_NC00004$|2| | | | | |  |  |  |
08.|SYS_NC00005$| |2| | | | |  |  |  |