Information overload

More than two days of Jonathan Lewis an then some other Ougf autumn seminar presentations. Feels like I am a beginner. I actually do not know enough. There is so much to learn about the basics. The change vector in Oracle. To learn the basics today it is much harder to figure out today than ten years ago. There is so many performance improvements disturbing the basic calculation based on numbers in simple tests.

Mark to your calendars two last days at the end of may 2012. There will be a Ougf seminar in Hämeenlinna. Just another time to feel closer as a beginner. And better yet learn things to unlearn...

Well just a tought. Lending an example from Jonathans teaching. Should the bind name be a part to be considered a part of sql text by optimizer?

drop table t1;

drop table t2;

create table t1(n1 number, n2 number);

create table t2(n1 number, n2 number);

create index t1_i1 on t1(n1);

create index t2_i1 on t2(n1);

First without binds.

 
select t1.n2,t2.n2
 from t1, t2
 where t1.n1=1
   and t2.n1=1
   and t1.n1=t2.n1
;
select * from table(dbms_xplan.display_cursor(format=>'basic +predicate'));

EXPLAINED SQL STATEMENT:
------------------------
select t1.n2,t2.n2  from t1, t2  where t1.n1=1    and t2.n1=1    and 
t1.n1=t2.n1
 
Plan hash value: 1051316565
 
-----------------------------------------------
| Id  | Operation                     | Name  |
-----------------------------------------------
|   0 | SELECT STATEMENT              |       |
|   1 |  NESTED LOOPS                 |       |
|   2 |   NESTED LOOPS                |       |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1    |
|*  4 |     INDEX RANGE SCAN          | T1_I1 |
|*  5 |    INDEX RANGE SCAN           | T2_I1 |
|   6 |   TABLE ACCESS BY INDEX ROWID | T2    |
-----------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("T1"."N1"=1)
   5 - access("T1"."N1"="T2"."N1")
       filter("T2"."N1"=1)
 

Nice looking plan.and now with binds.

select t1.n2,t2.n2
 from t1, t2
 where t1.n1=:b1
   and t2.n1=:b1
   and t1.n1=t2.n1
;

select * from table(dbms_xplan.display_cursor(format=>'basic +predicate'));

EXPLAINED SQL STATEMENT:
------------------------
select t1.n2,t2.n2  from t1, t2  where t1.n1=:b1    and t2.n1=:b1    
and t1.n1=t2.n1
 
Plan hash value: 810726339
 
------------------------------------------------
| Id  | Operation                      | Name  |
------------------------------------------------
|   0 | SELECT STATEMENT               |       |
|*  1 |  FILTER                        |       |
|   2 |   MERGE JOIN CARTESIAN         |       |
|   3 |    TABLE ACCESS BY INDEX ROWID | T1    |
|*  4 |     INDEX RANGE SCAN           | T1_I1 |
|   5 |    BUFFER SORT                 |       |
|   6 |     TABLE ACCESS BY INDEX ROWID| T2    |
|*  7 |      INDEX RANGE SCAN          | T2_I1 |
------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(TO_NUMBER(:B1)=TO_NUMBER(:B1))
   4 - access("T1"."N1"=TO_NUMBER(:B1))
       filter("T1"."N1"=TO_NUMBER(:B1))
   7 - access("T2"."N1"=TO_NUMBER(:B1))
       filter("T2"."N1"=TO_NUMBER(:B1))
 

 
select t1.n2,t2.n2
 from t1, t2
 where t1.n1=1
   and t2.n1=1
   and t1.n1=t2.n1
;

Plan changed. Oracle knows :B1 should be :B1 1 - filter(TO_NUMBER(:B1)=TO_NUMBER(:B1)) But why the plan changed? The bind might be null. And NUL is not equalt to NULL. How about sql monitor report.

 
 
SQL Monitoring Report

SQL Text
------------------------------
select /*+monitor*/t1.n2,t2.n2 from t1, t2 where t1.n1=:b1 and t2.n1=:b1 and t1.n1=t2.n1

Global Information
------------------------------
 Status              :  DONE (ALL ROWS)     
 Instance ID         :  1                   
 Session             :  RAFU (28:21)        
 SQL ID              :  dm8z90v1mk8tb       
 SQL Execution ID    :  16777217            
 Execution Started   :  11/03/2011 13:54:27 
 First Refresh Time  :  11/03/2011 13:54:27 
 Last Refresh Time   :  11/03/2011 13:54:27 
 Duration            :  .002418s            
 Module/Action       :  SQL Developer/-     
 Service             :  SYS$USERS           
 Program             :  SQL Developer       
 Fetch Calls         :  1                   

Binds
========================================================================================================================
| Name | Position |     Type     |                                        Value                                        |
========================================================================================================================
| :B1  |        1 | VARCHAR2(32) | 1                                                                                   |
| :B1  |        2 | VARCHAR2(32) | 1                                                                                   |
========================================================================================================================

Global Stats
==============================
| Elapsed |  Other   | Fetch |
| Time(s) | Waits(s) | Calls |
==============================
|    0.00 |     0.00 |     1 |
==============================

SQL Plan Monitoring Details (Plan Hash Value=810726339)
=======================================================================================================================================
| Id |            Operation             | Name  |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Activity | Activity Detail |
|    |                                  |       | (Estim) |      | Active(s) | Active |       | (Actual) |   (%)    |   (# samples)   |
=======================================================================================================================================
|  0 | SELECT STATEMENT                 |       |         |      |           |        |     1 |          |          |                 |
|  1 |   FILTER                         |       |         |      |           |        |     1 |          |          |                 |
|  2 |    MERGE JOIN CARTESIAN          |       |       1 |    1 |           |        |     1 |          |          |                 |
|  3 |     TABLE ACCESS BY INDEX ROWID  | T1    |       1 |    1 |           |        |     1 |          |          |                 |
|  4 |      INDEX RANGE SCAN            | T1_I1 |       1 |    1 |           |        |       |          |          |                 |
|  5 |     BUFFER SORT                  |       |       1 |      |           |        |       |          |          |                 |
|  6 |      TABLE ACCESS BY INDEX ROWID | T2    |       1 |      |           |        |       |          |          |                 |
|  7 |       INDEX RANGE SCAN           | T2_I1 |       1 |      |           |        |       |          |          |                 |
=======================================================================================================================================

B1 is named nicely. But through jdbc seems like the bind variable naming is lost at some point. A named bind becomes named like :B1 and :B2 all thou there was only one name while binding.