Database 12c: Outlines are Deprecated in JDBC or SQLJ?


SONY DSC

My colleague has tested on his blog whether Stored Outlines under 12c still work. The answer? YES. But the Oracle documentation shows the Stored Outlines in conjunction with JDBC and SQLJ will no longer be supported. Because many applications use Weblogic or Tomcat to connect to the database this may a significant limitation.

I have written a small JDBC Program and created also a Stored Outline on my database to test if Stored Outlines still working.

First take a look on the created Outline.

SQL> select sql_text from dba_outlines where name = 'SYS_OUTLINE_13072120423745704';

SQL_TEXT
--------------------------------------------------------------------------------
select username from SYS.V_$session

So in this case it is a very simple SQL similar the SQL tested from my colleague. Now let us see what happen when i execute this query from sqlplus.

SQL> select username from SYS.V_$session;

USERNAME
------------------------------

SYSTEM
SYS

55 rows selected.

I get 55 rows back (the output was truncated here in the post). But the more intressting thing is, has my execution plan used the Stored Outline.

SQL_ID    fw57buauwnf43, child number 3
-------------------------------------
select username from SYS.V_$session

Plan hash value: 3425234845

----------------------------------------------------------------------------------
| Id  | Operation                 | Name            | Rows   | Bytes  |  Cost (%CPU)|
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                 |        |        |      1 (100)|
|   1 |  NESTED LOOPS             |                 |     56 |   1512 |      0   (0)|
|   2 |   NESTED LOOPS            |                 |     56 |   1288 |      0   (0)|
|   3 |    FIXED TABLE FULL       | X$KSLWT         |     61 |    488 |      0   (0)|
|*  4 |    FIXED TABLE FIXED INDEX| X$KSUSE (ind:1) |      1 |     15 |      0   (0)|
|*  5 |   FIXED TABLE FIXED INDEX | X$KSLED (ind:2) |      1 |      4 |      0   (0)|
----------------------------------------------------------------------------------

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

   4 - filter(("S"."INDX"="W"."KSLWTSID" AND BITAND("S"."KSUSEFLG",1)<>0
          AND BITAND("S"."KSSPAFLG",1)<>0 AND "S"."INST_ID"=USERENV('INSTANCE')))
   5 - filter("W"."KSLWTEVT"="E"."INDX")

Note
-----
   - outline "SYS_OUTLINE_13072120423745704" used for this statement
   - statistics feedback used for this statement

So this looks good in Oracle 12.1.0.1, but what will happen when i try the same with a JDBC program. Here is the code (ok i know that can be better written but should work for this test).

import java.sql.*;

public class TestDBOracle {

  public static void main(String[] args)
      throws ClassNotFoundException, SQLException
  {
    Class.forName("oracle.jdbc.driver.OracleDriver");

     DriverManager.registerDriver
            (new oracle.jdbc.driver.OracleDriver());

        String url = "jdbc:oracle:thin:@//server1:1521/db12se.example.com";
    Connection conn =
         DriverManager.getConnection(url,"system","welcome1");

    conn.setAutoCommit(false);

    Statement stmt0 = conn.createStatement();
    ResultSet rset0 =
         stmt0.executeQuery("alter session set use_stored_outlines=DEFAULT");
    stmt0.close();

//

    Statement stmt = conn.createStatement();
    ResultSet rset =
         stmt.executeQuery("select username from SYS.V_$session");
    while (rset.next()) {
         System.out.println (rset.getString(1));
    }
    stmt.close();
//
    Statement stmt2 = conn.createStatement();
    ResultSet rset2 =
         stmt2.executeQuery("select PLAN_TABLE_OUTPUT from table(dbms_xplan.display_cursor())");
    while (rset2.next()) {
         System.out.println (rset2.getString(1));
    }
    stmt2.close();
    System.out.println ("Ok.");
  }
}

If you compile and start the program you see finally the program show the same execution Plan.

/u01/app/12.1.0/grid/jdk/bin/javac TestDBOracle.java
java -cp .:/u01/app/oracle/product/12.1.0/db_se_1/jdbc/lib/ojdbc6.jar TestDBOracle
(...)
SQL_ID  fw57buauwnf43, child number 3
-------------------------------------
select username from SYS.V_$session

Plan hash value: 3425234845

----------------------------------------------------------------------------------
| Id  | Operation                 | Name            | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                 |       |       |     1 (100)|
|   1 |  NESTED LOOPS             |                 |    56 |  1512 |     0   (0)|
|   2 |   NESTED LOOPS            |                 |    56 |  1288 |     0   (0)|
|   3 |    FIXED TABLE FULL       | X$KSLWT         |    61 |   488 |     0   (0)|
|*  4 |    FIXED TABLE FIXED INDEX| X$KSUSE (ind:1) |     1 |    15 |     0   (0)|
|*  5 |   FIXED TABLE FIXED INDEX | X$KSLED (ind:2) |     1 |     4 |     0   (0)|
----------------------------------------------------------------------------------

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

   4 - filter(("S"."INDX"="W"."KSLWTSID" AND BITAND("S"."KSUSEFLG",1)<>0 
              AND BITAND("S"."KSSPAFLG",1)<>0 AND "S"."INST_ID"=USERENV('INSTANCE')))
   5 - filter("W"."KSLWTEVT"="E"."INDX")

Note
-----
   - outline "SYS_OUTLINE_13072120423745704" used for this statement
   - statistics feedback used for this statement

So we see the same execution Plan. In both sqlplus and the JDBC example and also the outline is used.

Conclusion:

I proved that in my test stored outlines continue to function, even if the documentation describes this differently. This does not mean that in a future patch set, the function will be removed permanently.

Advertisements

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s