When the explanation doesn't sound quite right...

Recommend

Contact

Menu
∙ Home ∙ Index (All Topics) ∙ About Me ∙ About This Blog ∙ Favourite Links ∙ RSS Feed

Categories
∙ ASP & PHP (1) ∙ HTML, XML and CSS (2) ∙ C / C++ (5) ∙ Java, JSP and Servlet (0) ∙ SQL-Oracle-PL/SQL (28) ∙ Operating Systems (1) ∙ OFF-Topic (8)

Popular Topics (Top 10)
∙ Decode Function in Oracle SQL (10430) ∙ Derin anlamlı sözler - Bunlar da Türkçe olanlar :) (9613) ∙ Turkcell Staj Günlüğü - 1: Introduction to Oracle (8682) ∙ Oracle performance analysis - Tracing and performance evaluation (7822) ∙ Implicit vs. Explicit cursors - Performance analysis (5222) ∙ Turkcell Staj Günlüğü - 4: Transaction Management (4268) ∙ Differences between C and C++ (3982) ∙ Turkcell Staj Günlüğü - 3: Tablespaces, Datafiles and Control Files (3779) ∙ Turkcell Staj Günlüğü - 9: "SQL, PL/SQL and Java" ve "Redo Internals" (3607) ∙ Turkcell Staj Günlüğü - 2: Data Blocks, Extends and Segments (3506)

Most Recent (Last 10)
∙ Matematik Asla Yalan Söylemez! ∙ Finding and Removing Loop on a Singly-Linked List ∙ Obfuscated C ∙ Is C a Vitamin? Yes, of course... ∙ Differences between C and C++ ∙ Whence C? Why C? Whither C? ∙ Türkçe Karakterli Domain'lerin İç Yüzü ∙ Windows Source Codes ∙ Decode Function in Oracle SQL ∙ Hello World!

Recent Comments (Last 10)
∙ "Adulation?" By fizikci on Matematik Asla Yalan Söylemez! ∙ "Rehberlik için çook teşekkürler" By Pınar Tanrıverdi on Kahin'e yolculuk nasıl başlamalı? ∙ "bilgi paylaşıldıkça deger kazanır." By zülfü yıldırım on Turkcell Staj Günlüğü - 12: Partitioning ∙ "Teşekkürler" By serdar on Turkcell Staj Günlüğü - 12: Partitioning ∙ By Gokhan on Türkçe Karakterli Domain'lerin İç Yüzü ∙ "The Power of Attitude" By Tonguç Yılmaz on Matematik Asla Yalan Söylemez! ∙ ":)" By Ozay Akdora on Obfuscated C ∙ By Ömer YURDUSEVEN on Windows Source Codes ∙ By Özgür Macit on Türkçe Karakterli Domain'lerin İç Yüzü ∙ "Thanks" By Ganesh on Difference between db block gets and consistent gets

Archive (Last 12 Months)
∙ Feb, 2008 (4) ∙ Jan, 2008 (2) ∙ Dec, 2007 (1) ∙ Sep, 2007 (4) ∙ Aug, 2007 (9) ∙ Jul, 2007 (22) ∙ Jun, 2007 (3) ∙ Index (All Records)

Other Related Blogs
∙ Tom Kyte’s Blog ∙ Steven Feuerstein’s Blog ∙ Jonathan Lewis’s Blog ∙ H.Tonguç Yılmaz Oracle Blog ∙ Mennan Tekbir's Blog ∙ Hakkı Oktay’s Blog ∙ Osman Çam’s Blog

Stats

Total Topics
Total Topic Views
Total Comments
Unique Visitors
Total Visitors

: 45
: 120069
: 36

When the explanation doesn't sound quite right...

Category: SQL-Oracle-PL/SQL
Date: 03.08.2007 08:03:49

Quoted from Mr. KYTE's blog:

Q: Under what conditions, autotrace & explain plan can not give the correct execution plan of a sql?

A: To start with the answer to this - we need to understand that autotrace is just a feature of SQL Plus that automates an explain plan for us - so, autotrace and explain plan are sort of synonymous in this regard. I'll be using Oracle 10g Release 2 in these examples and will be using autotrace or sql_trace=true and TKPROF - you can get the same results in 9i and later using EXPLAIN PLAN and DBMS_XPLAN.DISPLAY to see the results.

Explain Plan is in the here and now...

This is the first problem with explain plan - it is in the "here and now". It uses the current optimizer environment, the current set of statistics and so on. That means the explain plan you see in a tkprof could differ from the REAL PLAN used 5 minutes ago (when performance was 'bad').

...

Explain plan is blind to the bind

Explain plan does not "bind peek".

...

Explain plan doesn't see your datatype...

The last bit about explain plan I'll look at is the fact that explain plan doesn't see your bind datatype. It presumes all binds are varchar2's regardless of how the developer is binding.

...

http://tkyte.blogspot.com/2007/04/when-explanation-doesn-sound-quite.html