A Question

I attended the Jonathan Lewis seminar three weeks ago and wrote just after that about information overload. The things learned there have helped me understanding issues I have to deal with the Oracle optimizer. The amount of information gained need some time to melt into mind. One issue talked there was a question. The original written question was "How many choises does Oracle have in this example?" With addition there was mentioned "for all versions".

drop table t1;

create table t1( 
  n1 number
, n2 number
, n3 number
, constraint t1p primary key(n1)
, constraint t1u unique (n2));

select count(*) from t1;

I answered wrongly mentioning also that it depends on the version. As so often my mind started to think howcome I answered such an answer. Maybe I was thinking a question: How many different execution plan possibilities does Oracle have in this example? Unfortunately I am not attending UKOUG to throw this to the OakTable Challenge. Feel free to use "Give me at least one that was not in the list of the Jonathan's answer and how to get there." I will be coming back to the issue after 7th of December.

Filtering outer join to hash join plan

I gave a SQL presentation a couple of years ago. There I mentioned the possibility to restrict an outer join when using ANSI joins. Every now and then seen such an join condition. Maybe even written myself. This week such a query has been bothering me with a long running nested loops access path. This was a simpler query than the one with outer join exists predicate I have mentioned earlier. While dealing with it I saw a quite funny looking predicate.

drop table aa;

drop table bb;

create table aa as 
select rownum id
     , nullif(mod(rownum,3),1) val 
  from dual connect  by level < 12;

create table bb as 
select rownum id
     , nullif(mod(rownum,3),0)+10 val 
  from dual connect by level < 9;

alter table aa modify id not null;

alter table bb modify id not null;

select aa.* from aa;

        ID        VAL
---------- ----------
         1
         2          2
         3          0
         4
         5          2
         6          0
         7
         8          2
         9          0
        10
        11          2

select bb.* from bb;

        ID        VAL
---------- ----------
         1         11
         2         12
         3
         4         11
         5         12
         6
         7         11
         8         12

Desired results:

   
        ID        VAL         ID        VAL
---------- ---------- ---------- ----------
         1
         2          2          2         12
         3          0
         4
         5          2          5         12
         6          0
         7
         8          2          8         12
         9          0
        10
        11          2

A normal outer join uses hash join plan. Adding the predicate aa.val = 2 to the join condition I get the result I want.

select * 
  from aa 
  left outer join bb 
    on aa.id=bb.id
;

-----------------------------------
| Id  | Operation          | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|*  1 |  HASH JOIN OUTER   |      |
|   2 |   TABLE ACCESS FULL| AA   |
|   3 |   TABLE ACCESS FULL| BB   |
-----------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("AA"."ID"="BB"."ID")
   
   
select * 
  from aa 
  left outer join bb 
    on aa.id=bb.id and aa.val = 2
;

-------------------------------------
| Id  | Operation            | Name |
-------------------------------------
|   0 | SELECT STATEMENT     |      |
|   1 |  NESTED LOOPS OUTER  |      |
|   2 |   TABLE ACCESS FULL  | AA   |
|   3 |   VIEW               |      |
|*  4 |    FILTER            |      |
|*  5 |     TABLE ACCESS FULL| BB   |
-------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("AA"."VAL"=2)
   5 - filter("AA"."ID"="BB"."ID")

But the 11.1.0.7 optimizer goes to a nested loops path. My problem is that the table BB is kind of big. Together with plan row 4 filter is not filtering out so many rows the execution is full table scanning the table bb too many times. Throwing the same query to 11.2.0.3 optimizer I get a hash join access path. Nice. That was something I was hoping for. But look at the access predicate.

-----------------------------------
| Id  | Operation          | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|*  1 |  HASH JOIN OUTER   |      |
|   2 |   TABLE ACCESS FULL| AA   |
|   3 |   TABLE ACCESS FULL| BB   |
-----------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("AA"."ID"="BB"."ID" AND "AA"."VAL"=CASE  WHEN ("BB"."ID"
              IS NOT NULL) THEN 2 ELSE 2 END )

Obviously the outer join (+) are missing next to BB.ID. You need to read it from the word OUTER next to the HASH JOIN. Sqldeveloper is able to visualize the predicate differently.

AA.ID=BB.ID(+) AND AA.VAL=CASE  WHEN (BB.ID(+) IS NOT NULL) THEN 2 ELSE 2 END

But that case statement seems kind of weird. "If something then constant otherwise the same constant". Should that case statement not be possible to be optimized to be a simple constant 2? I rewrote my query to use the old style join. And with 11.2.0.3 the plan and results stay the same. But going back to 11.1.0.7 things change.

select aa.*,bb.* 
 from aa, bb 
where aa.id=bb.id(+) 
  and aa.val = case when (bb.id(+) is not null) then 2 else 2 end
;

-----------------------------------
| Id  | Operation          | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|*  1 |  HASH JOIN OUTER   |      |
|*  2 |   TABLE ACCESS FULL| AA   |
|   3 |   TABLE ACCESS FULL| BB   |
-----------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("AA"."ID"="BB"."ID")
   2 - filter("AA"."VAL"=2)
   

The case statement is optimized as a simple constant and used as a filter staright to the table AA. Query results change compared to 11.2.0.3 results. Actually the thing I wanted out is without the else part in the predicate.

select aa.*,bb.* 
 from aa, bb 
where aa.id=bb.id(+) 
  and aa.val = case when (bb.id(+) is not null) then 2 end
;
-----------------------------------
| Id  | Operation          | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|*  1 |  HASH JOIN OUTER   |      |
|   2 |   TABLE ACCESS FULL| AA   |
|   3 |   TABLE ACCESS FULL| BB   |
-----------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("AA"."ID"="BB"."ID" AND "AA"."VAL"=CASE  WHEN ("BB"."ID"
              IS NOT NULL) THEN 2 END )

The results are what I was requesting. This may be rewoten back to ANSI join that is understood also by 11.1.0.7 optimizer and the execution goes to a hash join path.

select aa.*,bb.* 
  from aa 
  left outer join bb 
    on aa.id=bb.id 
   and aa.val = case when (bb.id is not null) then 2 end
;

I am satisfied with the results. Well throw another problem to the optimizers. How about nulls. Change the "and aa.val =" to "and aa.val is null" and even 11.2.0.3 is not able to go to a hash join plan. Here is an problematic nested loops access path query and a dirty trick on a way to a hash path.

select aa.*,bb.*
  from aa 
  left outer join bb 
    on aa.id=bb.id 
   and aa.val is null
;

select * 
  from aa 
  left outer join bb 
    on aa.id=bb.id 
   and coalesce(nullif(aa.val,bb.id),aa.val) is null
;

Skip locked

SQL SELECT SKIP LOCKED not mentioned in new features part of the SQL document. Seems to be there already in 11gr1.

SQL> create table skiplocked as select  level n from dual connect by level < 3;

Table created.

SQL> select * from skiplocked where n=1 for update;

         N
----------
         1

At the same time another session

SQL> select * from skiplocked;

         N
----------
         1
         2

SQL> select * from skiplocked for update skip locked;

         N
----------
         2

As the 11gr1 have been around for a while. Rob Van Wijk has written something about the feature.
http://rwijk.blogspot.com/2007/12/parallellism-in-skip-locked-scenario.html
http://rwijk.blogspot.com/2009/02/for-update-skip-locked.html
"when using FOR UPDATE SKIP LOCKED, records are locked when they are fetched, not when the cursor is opened" Need to read those again when the need for this feature comes...

Installing an agent and not a free lunch

Installing an Cloud Control 12 agent using rpm method. So not letting the Cloud Control do the agent installation, but do it manually at the agent node. While generating the rpm

$OMS_HOME/bin/emcli get_agentimage_rpm -destination=/tmp -platform="Linux x86-64"

I got an error

Agent image to rpm conversion failed

and the logfile stating

Directory /usr/lib/oracle doesnt exist. Please create the directory with write permissions and then retry the emcli command.

Well that is mentioned in the document Prerequisites, but This requirement is for the server OMS side. So as a root before issuing the command:

mkdir /usr/lib/oracle
chown oracle:oinstall /usr/lib/oracle

Also note that Editing agent.properties File (Only for Virtual Hosts) means here (Mandatory). Put the agent node host name there.

ORACLE_HOSTNAME (Only for Virtual Hosts) Enter the virtual host name where you want to install the Management Agent. 

And before issuing the  make sure the machines see each other. After installed the agent I had a small issue with machine naming at /etc/hosts. The installed agent and host appeared yellow "Pending" at the summary pie chart. After fixing the visibity of the agent machine emcli resyncAgent fixed the issue.

$OMS_HOME/bin/emcli resyncAgent -agent="agentname:3872"

Would it be cool to have following situation possible:

But there is no such thing as a free lunch. XE Options and Major Features Not Included ... Tuning Pack Nice to see that XE 11g installation changes the default control_management_pack_access DIAGNOSTIC+TUNING to NONE by default. It is changed actually with a comment that the default is changed because not an EE installation.

I would not have guessed before starting to install an agent to end up browsing licensing documentation. Seems like Even in PE: The Management Packs are not included in Personal Edition.


Next to the offline agent loading and generating oracle-agt-12.1.0.1.0-1.0.i386.rpm as the Cloud Control has only the oracle-agt-12.1.0.1.0-1.0.x86_64.rpm available. As i would like to try out agent installation also to a -platform="Linux x86" Lets see how that goes in the future...

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.