Dependency injection and custom annotation

Java EE provides a couple of extremely attractive options – DI (dependency injection feature) and custom annotations what allow qualify classes you develop. They bring remarkable flexibility in the job – but if you keep in mind the technique they use.

Couple of days ago I had to resolve some issue in the third-party code. I will simulate the case and keep the main idea of design to demonstrate what was the issue. Let’s create the basic interface and a custom annotation:

public interface SimpleInterface {
     String msg();
}
@Target({TYPE})
@Retention(RUNTIME)
public @interface QualifyerA {
    
} 

Also we create simple class and allow DI using @SessionScoped annotation.

@SessionScoped
@QualifyerA
public class SimpleClass 
implements SimpleInterface, Serializable {
    public String msg() { return "Got it"; }
}

Finally let’s create managed bean and the simple JSF to check results:

@ManagedBean
public class SimpleBean {
    SimpleInterface si = new SimpleClass();
    public String result() {
        if (si.getClass().isAnnotationPresent(QualifyerA.class))
                return si.msg();
        else
                return "Not annotated";
    }
    public String showClass() {
        return si.getClass().getName();
    }
}

A piece of JSF code:

    
        Hello, #{simpleBean.result()}
    

If we start the application on Web application server we get:

Hello, Got it

OK, everything is well so far. Let’s now use the injection instead of explicit instantiation:

public class SimpleBean {
    
    @Inject SimpleInterface si;
    
    public String result() {
        if (si.getClass().isAnnotationPresent(QualifyerA.class))
                return si.msg();
...

And we have failed:

Hello, Not annotated

Oops, we lost something, but what ? We can understand what happens if we look as the variable real class (
let’s change JSF code):

Hello, #{simpleBean.showClass()}

We get:

Hello, com.tray.SimpleClass$Proxy$_$$_WeldClientProxy

As you can see, Injection uses proxy class and, hence, we lost annotation information.
What we can do is to apply @Inherited to our custom annotation:

@Target({TYPE})
@Retention(RUNTIME)
@Inherited
public @interface QualifyerA {
   
}

After forcing the inheritance we gat what we need (don’t forget to restore original JSF code)

Hello, Got it 
Posted in Uncategorized | Leave a comment

12c identity column in PL/SQL record type

Identity columns are involved in 12c and this is amazing feature as it simplifies many aspects of program design.
But everything has a drawback. The first one is related to GENERAL ALWAYS default option and is similar to the problem what virtual columns cause when we try to use %ROWTYPE – based approach to insert records into table.

SQL> create table t (x int,
2 y generated always as (x+1))
3 /

Table created.

SQL> declare
2 tr t%rowtype;
3 begin
4 tr.x := 1;
5 insert into t values tr;
6 end;
7 /
declare

ORA-54013: INSERT operation disallowed on virtual columns

if we specify GENERATED ALWAYS identity column we get the similar problem (at least of the similar nature):

SQL> create table t (x int generated always as identity,
2 y varchar2(10))
3 /

Table created.

SQL> declare
2 tr t%rowtype;
3 begin
4 tr.y := ‘m';
5 insert into t values tr;
6 end;
7 /
declare
*

ORA-32795: cannot insert into a generated always identity column

Identity columns have few more options – BY DEFAULT and BY DEFAULT ON NULL.
Let’s look on BY DEFAULT:

SQL> create table t (x int generated by default as identity,
2 y varchar2(10))
3 /

Table created.

SQL> declare
2 tr t%rowtype;
3 begin
4 tr.y := ‘m';
5 insert into t values tr;
6 end;
7 /
declare
*

ORA-01400: cannot insert NULL into (“SCOTT”.”T”.”X”)

OK, now we can insert into column, but can’t insert NULL. The last step:

SQL> create table t (x int generated by default on null as identity,
2 y varchar2(10))
3 /

Table created.

SQL> declare
2 tr t%rowtype;
3 begin
4 tr.y := ‘m';
5 insert into t values tr;
6 end;
7 /

PL/SQL procedure successfully completed.

SQL> select * from t
2 /

X Y
———- ———-
1 m

OK, now we’ve got the desirable result. So if we use BY DEFAULT ON NULL option we can continue to use ROW-based insert syntax in PL/SQL.

Posted in Uncategorized | Leave a comment

12c partial indexes and list partitioning

Oracle 12c release introduces the brand new and impressive feature – partial indexes on partitioned tables. Richard Foote spent extremely useful articles to this aspect of 12c:

http://richardfoote.wordpress.com/2013/07/08/12c-partial-indexes-for-partitioned-tables-part-i-ignoreland/#comment-104322

http://richardfoote.wordpress.com/2013/07/12/12c-partial-indexes-for-partitioned-tables-part-ii-vanishing-act/

And one of the most attractive features is CBO ability to use index access where possible and partition full scan there index can not be used in one statement. But does this always work ?
Let’s make few experiments for LIST partitioned tables.

SQL> create table t (x int, y int)
  2  partition by list (y) (
  3  partition p_1 values(1)
  4  )
  5  /

SQL> begin
  2  for i in 2..8 loop
  3    execute immediate
  4   'alter table t add partition p_'||i||' values('||i||')';
  5  end loop;
  6  end;
  7  /

SQL> alter table t modify partition p_2 indexing off
  2  /

SQL> create sequence t_seq
  2  /

SQL> insert into t select t_seq.nextval, mod(rownum, 8)+1
  2  from dual connect by level  commit
  2  /

SQL> create index t_i on t(x) indexing partial
  2  /

SQL> begin
  2    dbms_stats.gather_table_stats(ownname => user,tabname => 'T',cascade => true);
  3  end;
  4  /

SQL> select * from t where x = 10
  2  /

         X          Y                                                           
---------- ----------                                                           
        10          3                                                           

SQL> select * from table(dbms_xplan.display_cursor)
  2  /

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
SQL_ID  61c22yvcwkaut, child number 0                                           
-------------------------------------                                           
select * from t where x = 10                                                    
                                                                                
Plan hash value: 1177543047                                                     
                                                                                
--------------------------------------------------------------------------------
----------------------------------------                                        
                                                                                
| Id  | Operation                                    | Name    | Rows  | Bytes |
 Cost (%CPU)| Time     | Pstart| Pstop |                                        

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
                                                                                
--------------------------------------------------------------------------------
----------------------------------------                                        
                                                                                
|   0 | SELECT STATEMENT                             |         |       |       |
    71 (100)|          |       |       |                                        
                                                                                
|   1 |  VIEW                                        | VW_TE_2 |     2 |    52 |
    71   (2)| 00:00:01 |       |       |                                        
                                                                                
|   2 |   UNION-ALL                                  |         |       |       |

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
            |          |       |       |                                        
                                                                                
|*  3 |    TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| T       |     1 |     8 |
     4   (0)| 00:00:01 | ROWID | ROWID |                                        
                                                                                
|*  4 |     INDEX RANGE SCAN                         | T_I     |     1 |       |
     3   (0)| 00:00:01 |       |       |                                        
                                                                                
|   5 |    PARTITION LIST SINGLE                     |         |     1 |     8 |
    67   (2)| 00:00:01 |     2 |     2 |                                        
                                                                                

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
|*  6 |     TABLE ACCESS FULL                        | T       |     1 |     8 |
    67   (2)| 00:00:01 |     2 |     2 |                                        
                                                                                
--------------------------------------------------------------------------------
----------------------------------------                                        
                                                                                
                                                                                
Predicate Information (identified by operation id):                             
---------------------------------------------------                             
                                                                                
   3 - filter(("T"."Y"=1 OR "T"."Y"=3 OR "T"."Y"=4 OR "T"."Y"=5 OR "T"."Y"=6 OR 

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
"T"."Y"=7 OR "T"."Y"=8))                                                        
                                                                                
   4 - access("X"=10)                                                           
   6 - filter("X"=10)                                                           
                                                                                

As expected CBO uses the combination of the index access and partition full scan. But we should take into special account the FILTER clause in EXPLAIN PLAN – it consists of 7 comparison predicates with OR between them.
Now let’s increase the number of partitions but still leave just one INDEXING OFF. Get the result for 1001 partitions (I omit most of statements – they are similar with above scenario):

SQL> create table t (x int, y int)
  2  partition by list (y) (
  3  partition p_1 values(1)
  4  )
  5  /

SQL> begin
  2  for i in 2..1001 loop
  3    execute immediate
  4   'alter table t add partition p_'||i||' values('||i||')';
  5  end loop;
  6  end;
  7  /

SQL> alter table t modify partition p_2 indexing off
  2  /
...

SQL> select * from t where x = 10
  2  /

         X          Y                                                           
---------- ----------                                                           
        10         11                                                           

SQL> 
SQL> select * from table(dbms_xplan.display_cursor)
  2  /

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
SQL_ID  61c22yvcwkaut, child number 0                                           
-------------------------------------                                           
select * from t where x = 10                                                    
                                                                                
Plan hash value: 1177543047                                                     
                                                                                
--------------------------------------------------------------------------------
----------------------------------------                                        
                                                                                
| Id  | Operation                                    | Name    | Rows  | Bytes |
 Cost (%CPU)| Time     | Pstart| Pstop |                                        

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
                                                                                
--------------------------------------------------------------------------------
----------------------------------------                                        
                                                                                
|   0 | SELECT STATEMENT                             |         |       |       |
    18 (100)|          |       |       |                                        
                                                                                
|   1 |  VIEW                                        | VW_TE_2 |     2 |    52 |
    18   (0)| 00:00:01 |       |       |                                        
                                                                                
|   2 |   UNION-ALL                                  |         |       |       |

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
            |          |       |       |                                        
                                                                                
|*  3 |    TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| T       |     1 |     9 |
     4   (0)| 00:00:01 | ROWID | ROWID |                                        
                                                                                
|*  4 |     INDEX RANGE SCAN                         | T_I     |     1 |       |
     3   (0)| 00:00:01 |       |       |                                        
                                                                                
|   5 |    PARTITION LIST SINGLE                     |         |     1 |     8 |
    14   (0)| 00:00:01 |     2 |     2 |                                        
                                                                                

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
|*  6 |     TABLE ACCESS FULL                        | T       |     1 |     8 |
    14   (0)| 00:00:01 |     2 |     2 |                                        
                                                                                
--------------------------------------------------------------------------------
----------------------------------------                                        
                                                                                
                                                                                
Predicate Information (identified by operation id):                             
---------------------------------------------------                             
                                                                                
   3 - filter(("T"."Y"=1 OR "T"."Y"=3 OR "T"."Y"=4 OR "T"."Y"=5 OR "T"."Y"=6 OR 

Easy to see the optimization is still actual. But let’s add one more partition.

SQL> create table t (x int, y int)
  2  partition by list (y) (
  3  partition p_1 values(1)
  4  )
  5  /

SQL> begin
  2  for i in 2..1002 loop
  3    execute immediate
  4   'alter table t add partition p_'||i||' values('||i||')';
  5  end loop;
  6  end;
  7  /
...
SQL> select * from t where x = 10
  2  /
select * from t where x = 10
                           *
error in row 1:
ORA-01795: maximum expression number in list - 1000 

Oooops… We’ve got the error !
This has the natural explanation – we have 1002 partitions total and just one is INDEXING OFF.
Oracle tries to execute the in-list filter with 1001 element what causes the error.
Another example shows what we can get full scan just adding DEFAULT partition to the list:

SQL> create table t (x int, y int)
  2  partition by list (y) (
  3  partition p_1 values(1)
  4  )
  5  /

SQL> begin
  2  for i in 2..8 loop
  3    execute immediate
  4   'alter table t add partition p_'||i||' values('||i||')';
  5  end loop;
  6  end;
  7  /

SQL> alter table t add partition p_default values(default)
  2  /

SQL> alter table t modify partition p_2 indexing off
  2  /

SQL> create sequence t_seq
  2  /

SQL> insert into t select t_seq.nextval, mod(rownum, 8)+1
  2  from dual connect by level  commit
  2  /

SQL> create index t_i on t(x) indexing partial
  2  /
 
SQL> begin
  2    dbms_stats.gather_table_stats(ownname => user,tabname => 'T',cascade => true);
  3  end;
  4  /

SQL> select * from t where x = 10
  2  /

         X          Y                                                           
---------- ----------                                                           
        10          3                                                           

SQL> 
SQL> select * from table(dbms_xplan.display_cursor)
  2  /

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
SQL_ID  61c22yvcwkaut, child number 0                                           
-------------------------------------                                           
select * from t where x = 10                                                    
                                                                                
Plan hash value: 3845649146                                                     
                                                                                
--------------------------------------------------------------------------------
-----------                                                                     
                                                                                
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     | Psta
rt| Pstop |                                                                     

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
                                                                                
--------------------------------------------------------------------------------
-----------                                                                     
                                                                                
|   0 | SELECT STATEMENT   |      |       |       |   522 (100)|          |     
  |       |                                                                     
                                                                                
|   1 |  PARTITION LIST ALL|      |     1 |     8 |   522   (1)| 00:00:01 |     
1 |     9 |                                                                     
                                                                                
|*  2 |   TABLE ACCESS FULL| T    |     1 |     8 |   522   (1)| 00:00:01 |     

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
1 |     9 |                                                                     
                                                                                
--------------------------------------------------------------------------------
-----------                                                                     
                                                                                
                                                                                
Predicate Information (identified by operation id):                             
---------------------------------------------------                             
                                                                                
   2 - filter("X"=10)                                                           

Seems Oracle can not compose clear in-list filter due to undefined DEFAULT value. But we can
get the situation back if we mark DEFAULT partition as non-indexed:

SQL> create table t (x int, y int)
  2  partition by list (y) (
  3  partition p_1 values(1)
  4  )
  5  /

SQL> begin
  2  for i in 2..8 loop
  3    execute immediate
  4   'alter table t add partition p_'||i||' values('||i||')';
  5  end loop;
  6  end;
  7  /

SQL> alter table t add partition p_default values(default)
  2  /

SQL> alter table t modify partition p_2 indexing off
  2  /

SQL> alter table t modify partition p_default indexing off
  2  /

SQL> create sequence t_seq
  2  /

SQL> insert into t select t_seq.nextval, mod(rownum, 8)+1
  2  from dual connect by level  commit
  2  /

SQL> create index t_i on t(x) indexing partial
  2  /

SQL> begin
  2    dbms_stats.gather_table_stats(ownname => user,tabname => 'T',cascade => true);
  3  end;
  4  /

SQL> select * from t where x = 10
  2  /

         X          Y                                                           
---------- ----------                                                           
        10          3                                                           

SQL> 
SQL> select * from table(dbms_xplan.display_cursor)
  2  /

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
SQL_ID  61c22yvcwkaut, child number 0                                           
-------------------------------------                                           
select * from t where x = 10                                                    
                                                                                
Plan hash value: 1776253938                                                     
                                                                                
--------------------------------------------------------------------------------
----------------------------------------                                        
                                                                                
| Id  | Operation                                    | Name    | Rows  | Bytes |
 Cost (%CPU)| Time     | Pstart| Pstop |                                        

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
                                                                                
--------------------------------------------------------------------------------
----------------------------------------                                        
                                                                                
|   0 | SELECT STATEMENT                             |         |       |       |
    71 (100)|          |       |       |                                        
                                                                                
|   1 |  VIEW                                        | VW_TE_2 |     2 |    52 |
    71   (2)| 00:00:01 |       |       |                                        
                                                                                
|   2 |   UNION-ALL                                  |         |       |       |

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
            |          |       |       |                                        
                                                                                
|*  3 |    TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| T       |     1 |     8 |
     4   (0)| 00:00:01 | ROWID | ROWID |                                        
                                                                                
|*  4 |     INDEX RANGE SCAN                         | T_I     |     1 |       |
     3   (0)| 00:00:01 |       |       |                                        
                                                                                
|   5 |    PARTITION LIST OR                         |         |     1 |     8 |
    67   (2)| 00:00:01 |KEY(OR)|KEY(OR)|                                        
                                                                                

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
|*  6 |     TABLE ACCESS FULL                        | T       |     1 |     8 |
    67   (2)| 00:00:01 |KEY(OR)|KEY(OR)|                                        
                                                                                
--------------------------------------------------------------------------------
----------------------------------------                                        
                                                                                
                                                                                
Predicate Information (identified by operation id):                             
---------------------------------------------------                             
                                                                                
   3 - filter(("T"."Y"=1 OR "T"."Y"=3 OR "T"."Y"=4 OR "T"."Y"=5 OR "T"."Y"=6 OR 

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
"T"."Y"=7 OR "T"."Y"=8))                                                        
                                                                                
   4 - access("X"=10)                                                           
   6 - filter(("X"=10 AND (("T"."Y"1 AND "T"."Y"3 AND "T"."Y"4 AND "T"."Y"
5 AND "T"."Y"6 AND                                                          
                                                                                
              "T"."Y"7 AND "T"."Y"8) OR "T"."Y" IS NULL) AND ((TBL$OR$IDX$PA
RT$NUM(,0,0,65535,ROWID)>=2 AND                                              
                                                                                
              TBL$OR$IDX$PART$NUM(,0,0,65535,ROWID)<=2) OR (TBL$OR$IDX$PART$N
UM(,0,0,65535,ROWID)>=9 AND                                                  

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
                                                                                
              TBL$OR$IDX$PART$NUM(,0,0,65535,ROWID)<=9))))                   
Posted in Uncategorized | 3 Comments

Partition pruning in INDEX access to partitioned table

Few days ago I have been asked to look at the specific problem, related to combinations of several factors.
There is the big table “T” (about 100 mln rows and 400 Gb size) what is partitioned by list on column “Y”. There is the global index on column “T”(“X”) with acceptable selectivity factor. There is a staging table “T_S” what is used as a “golden source” of “X” column values. We need to get data from table “T” what have “X” values from “T_S” list and “Y” column equal to ‘C’. The case is that such data are just a tiny part of all rows corresponding to (“T”.”X” = “T_S”.”X”) condition. Let me illustrate the scenario:

SQL> CREATE TABLE t (x int, big_column char(200), y char(1))
  2  PARTITION BY LIST (y) (
  3   PARTITION P_A VALUES('A'),
  4   PARTITION P_B VALUES('B'),
  5   PARTITION P_C VALUES('C'),
  6   PARTITION P_D VALUES('D'),
  7   PARTITION P_E VALUES('E')
  8  )
  9  /

Table created.

SQL> 
SQL> INSERT INTO t
  2  SELECT id,stub,pkey FROM
  3  (SELECT rownum id,
  4  'x' stub
  5  FROM dual
  6  CONNECT BY LEVEL <=100000
  7  ),
  8  (
  9  SELECT decode(rownum,1,'A',2,'B',3,'C',4,'D',5,'E') pkey
 10  FROM dual
 11  CONNECT BY LEVEL  
SQL> COMMIT
  2  /

Commit complete.

SQL> 
SQL> CREATE TABLE t_lead(x int)
  2  /

Table created.

SQL> 
SQL> INSERT INTO t_lead
  2  SELECT rownum*1000 FROM dual CONNECT BY LEVEL  
SQL> COMMIT
  2  /

Commit complete.

SQL> 
SQL> CREATE INDEX t_i ON t(x)
  2  /

Index created.

SQL> 
SQL> BEGIN
  2   dbms_stats.gather_table_stats(
  3  	 ownname=>user,
  4  	 tabname=>'T',
  5  	 cascade=>true
  6  	 );
  7   dbms_stats.gather_table_stats(
  8  	 ownname=>user,
  9  	 tabname=>'T_LEAD'
 10  	 );
 11  END;
 12  /

PL/SQL procedure successfully completed.

Now let’s look at the query and it’s plan:

SQL> SET AUTOTRACE TRACEONLY
SQL> SET PAGESIZE 2000
SQL> SET LINESIZE 120
SQL> 
SQL> 
SQL> SELECT t.x FROM t_lead, t
  2  WHERE t_lead.x = t.x
  3  AND t.big_column = 'x'
  4  AND t.y = 'C'
  5  /

50 rows selected.

Execution Plan
----------------------------------------------------------                                                              
Plan hash value: 3015557880                                                                                             

--------------------------------------------------------------------------------------------------------------          
| Id  | Operation                           | Name   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |          
--------------------------------------------------------------------------------------------------------------          
|   0 | SELECT STATEMENT                    |        |    50 | 10600 |   353   (0)| 00:00:05 |       |       |          
|   1 |  NESTED LOOPS                       |        |       |       |            |          |       |       |          
|   2 |   NESTED LOOPS                      |        |    50 | 10600 |   353   (0)| 00:00:05 |       |       |          
|   3 |    TABLE ACCESS FULL                | T_LEAD |    50 |   200 |     3   (0)| 00:00:01 |       |       |          
|*  4 |    INDEX RANGE SCAN                 | T_I    |     5 |       |     2   (0)| 00:00:01 |       |       |          
|*  5 |   TABLE ACCESS BY GLOBAL INDEX ROWID| T      |     1 |   208 |     7   (0)| 00:00:01 |     3 |     3 |          
--------------------------------------------------------------------------------------------------------------          

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

   4 - access("T_LEAD"."X"="T"."X")                                                                                     
   5 - filter("T"."BIG_COLUMN"='x' AND "T"."Y"='C')                                                                     

Statistics
----------------------------------------------------------                                                              
          0  recursive calls                                                                                            
          0  db block gets                                                                                              
        170  consistent gets                                                                                            
          0  physical reads                                                                                             
          0  redo size                                                                                                  
       1415  bytes sent via SQL*Net to client                                                                           
        557  bytes received via SQL*Net from client                                                                     
          5  SQL*Net roundtrips to/from client                                                                          
          0  sorts (memory)                                                                                             
          0  sorts (disk)                                                                                               
         50  rows processed

OK, we should notice Pstart and Pstop columns in TABLE ACCESS BY GLOBAL INDEX ROWID (5th row in plan) have value 3 what (in accordance to Oracle documentation) means partition pruning. E.g. Oracle accesses just one – required – partition and does not try to reach other ones when reaches table blocks.
We may reach the same using PARTITION() clause in FROM part of the query:

SQL> SELECT t.* FROM t_lead, t partition(p_c) t
  2  WHERE t_lead.x = t.x
  3  AND t.big_column = 'x'
  4  /

50 rows selected.

Execution Plan
----------------------------------------------------------                                                              
Plan hash value: 3015557880                                                                                             

--------------------------------------------------------------------------------------------------------------          
| Id  | Operation                           | Name   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |          
--------------------------------------------------------------------------------------------------------------          
|   0 | SELECT STATEMENT                    |        |    50 | 10600 |   353   (0)| 00:00:05 |       |       |          
|   1 |  NESTED LOOPS                       |        |       |       |            |          |       |       |          
|   2 |   NESTED LOOPS                      |        |    50 | 10600 |   353   (0)| 00:00:05 |       |       |          
|   3 |    TABLE ACCESS FULL                | T_LEAD |    50 |   200 |     3   (0)| 00:00:01 |       |       |          
|*  4 |    INDEX RANGE SCAN                 | T_I    |     5 |       |     2   (0)| 00:00:01 |       |       |          
|*  5 |   TABLE ACCESS BY GLOBAL INDEX ROWID| T      |     1 |   208 |     7   (0)| 00:00:01 |     3 |     3 |          
--------------------------------------------------------------------------------------------------------------          

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

   4 - access("T_LEAD"."X"="T"."X")                                                                                     
       filter(TBL$OR$IDX$PART$NUM("T",0,1,0,ROWID)=3)                                                                   
   5 - filter("T"."BIG_COLUMN"='x')                                                                                     

Statistics
----------------------------------------------------------                                                              
          0  recursive calls                                                                                            
          0  db block gets                                                                                              
        170  consistent gets                                                                                            
          0  physical reads                                                                                             
          0  redo size                                                                                                  
       2083  bytes sent via SQL*Net to client                                                                           
        557  bytes received via SQL*Net from client                                                                     
          5  SQL*Net roundtrips to/from client                                                                          
          0  sorts (memory)                                                                                             
          0  sorts (disk)                                                                                               
         50  rows processed

We can notice the difference between two plans despite they look almost alike and costs/rows estimations are the same. The second plan involves additional filtering: TBL$OR$IDX$PART$NUM(“T”,0,1,0,ROWID)=3 and this step seems being executed at the level of the index scan. E.g. Oracle gets rid of ROWIDs what don’t fit to particular partition.
We may easily notice this in the tracefile (I executed 10046 event level 12 for both ones):

PARSING IN CURSOR #2 len=83 dep=0 uid=84 oct=3 lid=84 tim=199354375853 hv=1590834553 ad='7ffb3dfa4a8' sqlid='984xy4jgd4dbt'
SELECT t.x FROM t_lead, t
WHERE t_lead.x = t.x
AND t.big_column = 'x'
AND t.y = 'C'
END OF STMT
PARSE #2:c=0,e=125,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3015557880,tim=199354375852
EXEC #2:c=0,e=56,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3015557880,tim=199354376052
WAIT #2: nam='SQL*Net message to client' ela= 2 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=199354376132
WAIT #2: nam='Disk file operations I/O' ela= 863 FileOperation=2 fileno=4 filetype=2 obj#=-1 tim=199354377098
FETCH #2:c=0,e=1140,p=0,cr=7,cu=0,mis=0,r=1,dep=0,og=1,plh=3015557880,tim=199354377321
WAIT #2: nam='SQL*Net message from client' ela= 163 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=199354377555
WAIT #2: nam='SQL*Net message to client' ela= 2 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=199354377720
FETCH #2:c=0,e=513,p=0,cr=48,cu=0,mis=0,r=15,dep=0,og=1,plh=3015557880,tim=199354378141
WAIT #2: nam='SQL*Net message from client' ela= 5458 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=199354383664
WAIT #2: nam='SQL*Net message to client' ela= 2 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=199354383822
FETCH #2:c=0,e=412,p=0,cr=48,cu=0,mis=0,r=15,dep=0,og=1,plh=3015557880,tim=199354384178
WAIT #2: nam='SQL*Net message from client' ela= 4449 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=199354388682
WAIT #2: nam='SQL*Net message to client' ela= 1 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=199354388782
FETCH #2:c=0,e=435,p=0,cr=48,cu=0,mis=0,r=15,dep=0,og=1,plh=3015557880,tim=199354389164
WAIT #2: nam='SQL*Net message from client' ela= 4593 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=199354393810
WAIT #2: nam='SQL*Net message to client' ela= 1 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=199354393958
FETCH #2:c=0,e=208,p=0,cr=19,cu=0,mis=0,r=4,dep=0,og=1,plh=3015557880,tim=199354394109
STAT #2 id=1 cnt=50 pid=0 pos=1 obj=0 op='NESTED LOOPS  (cr=170 pr=0 pw=0 time=0 us)'
STAT #2 id=2 cnt=250 pid=1 pos=1 obj=0 op='NESTED LOOPS  (cr=120 pr=0 pw=0 time=498 us cost=353 size=10600 card=50)'
STAT #2 id=3 cnt=50 pid=2 pos=1 obj=94055 op='TABLE ACCESS FULL T_LEAD (cr=11 pr=0 pw=0 time=441 us cost=3 size=200 card=50)'
STAT #2 id=4 cnt=250 pid=2 pos=2 obj=94056 op='INDEX RANGE SCAN T_I (cr=109 pr=0 pw=0 time=90 us cost=2 size=0 card=5)'
STAT #2 id=5 cnt=50 pid=1 pos=2 obj=94049 op='TABLE ACCESS BY GLOBAL INDEX ROWID T PARTITION: 3 3 (cr=50 pr=0 pw=0 time=0 us cost=7 size=208 card=1)'
WAIT #2: nam='SQL*Net message from client' ela= 6502 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=199354400849
CLOSE #2:c=0,e=17,dep=0,type=0,tim=199354400917
=====================
PARSING IN CURSOR #3 len=86 dep=0 uid=84 oct=3 lid=84 tim=199354401047 hv=1072898565 ad='7ffb3dd24f0' sqlid='bd34s5szz68h5'
SELECT t.* FROM t_lead, t partition(p_c) t
WHERE t_lead.x = t.x
AND t.big_column = 'x'
END OF STMT
PARSE #3:c=0,e=83,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3015557880,tim=199354401047
EXEC #3:c=0,e=34,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3015557880,tim=199354401292
WAIT #3: nam='SQL*Net message to client' ela= 2 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=199354401372
FETCH #3:c=0,e=95,p=0,cr=7,cu=0,mis=0,r=1,dep=0,og=1,plh=3015557880,tim=199354401512
WAIT #3: nam='SQL*Net message from client' ela= 182 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=199354401755
WAIT #3: nam='SQL*Net message to client' ela= 2 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=199354401855
FETCH #3:c=0,e=377,p=0,cr=48,cu=0,mis=0,r=15,dep=0,og=1,plh=3015557880,tim=199354402176
WAIT #3: nam='SQL*Net message from client' ela= 72296 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=199354474524
WAIT #3: nam='SQL*Net message to client' ela= 2 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=199354474808
FETCH #3:c=0,e=481,p=0,cr=48,cu=0,mis=0,r=15,dep=0,og=1,plh=3015557880,tim=199354475173
WAIT #3: nam='SQL*Net message from client' ela= 22029 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=199354497275
WAIT #3: nam='SQL*Net message to client' ela= 2 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=199354497514
FETCH #3:c=0,e=482,p=0,cr=48,cu=0,mis=0,r=15,dep=0,og=1,plh=3015557880,tim=199354497892
WAIT #3: nam='SQL*Net message from client' ela= 21923 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=199354519880
WAIT #3: nam='SQL*Net message to client' ela= 1 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=199354520063
FETCH #3:c=0,e=230,p=0,cr=19,cu=0,mis=0,r=4,dep=0,og=1,plh=3015557880,tim=199354520220
STAT #3 id=1 cnt=50 pid=0 pos=1 obj=0 op='NESTED LOOPS  (cr=170 pr=0 pw=0 time=0 us)'
STAT #3 id=2 cnt=50 pid=1 pos=1 obj=0 op='NESTED LOOPS  (cr=120 pr=0 pw=0 time=1911 us cost=353 size=10600 card=50)'
STAT #3 id=3 cnt=50 pid=2 pos=1 obj=94055 op='TABLE ACCESS FULL T_LEAD (cr=11 pr=0 pw=0 time=245 us cost=3 size=200 card=50)'
STAT #3 id=4 cnt=50 pid=2 pos=2 obj=94056 op='INDEX RANGE SCAN T_I (cr=109 pr=0 pw=0 time=0 us cost=2 size=0 card=5)'
STAT #3 id=5 cnt=50 pid=1 pos=2 obj=94049 op='TABLE ACCESS BY GLOBAL INDEX ROWID T PARTITION: 3 3 (cr=50 pr=0 pw=0 time=0 us cost=7 size=208 card=1)'
WAIT #3: nam='SQL*Net message from client' ela= 10514 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=199354530980
CLOSE #3:c=0,e=20,dep=0,type=0,tim=199354531107

Bold font stresses what in the first operator index scan operation reports 250 rows while in the second one – just 50.

Posted in Uncategorized | Leave a comment

Tom Kyte in Moscow

11th of Dec Tom Kyte presented his session in Moscow. Brilliant as always! Materials are published there: .

P.S. I won 2th edition of “Oracle for Experts” book, This is the second time I win and I have a chance t compare 2 different editions :)

Posted in Uncategorized | Leave a comment

DBMS_SQL.TO_CURSOR_NUMBER and “cursor leaking”

Some time ago it was a hot discussion in http://www.sql.ru forum over “does PL/SQL machine close cursors when I leave PL/SQL block or not”. I have found that many people are still being missdriven by Oracle mess of terminology for “open cursor”. In many cases people miss “open SQL area”, “parsing”, “PL/SQL cashed cursor”,”session cached cursor” ets. No wonder – Oracle documentation treats “open cursor” in too many ways and many Oracle gurus wrote of this many times.
But this misunderstanding and a wrong use of V$OPEN_CURSOR may lead to confusions in Oracle development and production support. Let’s look at the case when we use the brand new and useful DBMS_SQL functionality – the function TO_CURSOR_NUMBER.

First of all I have to remember that V$OPEN_CURSOR dictionary view reflects the information about specific library cache locks (but not about just open SQL area handlers). Because of this it reflects the information about parsed SQL statements. But this is not equivalent to open cursor ! To get it clear let’s execute the following code:

SQL> select value
  2  from v$mystat a, v$statname b
  3  where a.statistic# = b.statistic#
  4  and b.name = 'opened cursors current'
  5  /

     VALUE                                                                      
----------                                                                      
         2                                                                      

SQL> 
SQL> select cursor_type, count(*)
  2  from v$open_cursor
  3  where sid = sys_context('USERENV','SID')
  4  group by rollup(cursor_type)
  5  order by 1
  6  /

CURSOR_TYPE                                                        COUNT(*)     
---------------------------------------------------------------- ----------     
BUNDLE DICTIONARY LOOKUP CACHED                                           7     
DICTIONARY LOOKUP CURSOR CACHED                                          20     
OPEN                                                                      1     
OPEN-RECURSIVE                                                            2     
PL/SQL CURSOR CACHED                                                      5     
SESSION CURSOR CACHED                                                     3     
                                                                         38     

7 rows selected.

SQL> 
SQL> 
SQL> DECLARE
  2      rc_number INTEGER;
  3  BEGIN
  4      rc_number := DBMS_SQL.OPEN_CURSOR;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL> 
SQL> select value
  2  from v$mystat a, v$statname b
  3  where a.statistic# = b.statistic#
  4  and b.name = 'opened cursors current'
  5  /

     VALUE                                                                      
----------                                                                      
         3                                                                      

SQL> 
SQL> select cursor_type, count(*)
  2  from v$open_cursor
  3  where sid = sys_context('USERENV','SID')
  4  group by rollup(cursor_type)
  5  order by 1
  6  /

CURSOR_TYPE                                                        COUNT(*)     
---------------------------------------------------------------- ----------     
BUNDLE DICTIONARY LOOKUP CACHED                                           7     
DICTIONARY LOOKUP CURSOR CACHED                                          19     
OPEN                                                                      1     
OPEN-RECURSIVE                                                            2     
PL/SQL CURSOR CACHED                                                      5     
SESSION CURSOR CACHED                                                     4     
                                                                         38     

7 rows selected.

SQL> 
SQL> DECLARE
  2      rc_number INTEGER;
  3  BEGIN
  4      for i in 1..100 loop
  5          rc_number := DBMS_SQL.OPEN_CURSOR;
  6      end loop;
  7  END;
  8  /

PL/SQL procedure successfully completed.

SQL> 
SQL> select value
  2  from v$mystat a, v$statname b
  3  where a.statistic# = b.statistic#
  4  and b.name = 'opened cursors current'
  5  /

     VALUE                                                                      
----------                                                                      
       103                                                                      

SQL> 
SQL> select cursor_type, count(*)
  2  from v$open_cursor
  3  where sid = sys_context('USERENV','SID')
  4  group by rollup(cursor_type)
  5  order by 1
  6  /

CURSOR_TYPE                                                        COUNT(*)     
---------------------------------------------------------------- ----------     
BUNDLE DICTIONARY LOOKUP CACHED                                           7     
DICTIONARY LOOKUP CURSOR CACHED                                          19     
OPEN                                                                      1     
OPEN-RECURSIVE                                                            2     
PL/SQL CURSOR CACHED                                                      5     
SESSION CURSOR CACHED                                                     4     
                                                                         38     

7 rows selected.

I did not close cursor handlers explicitly in purpose to demostrate clearly what dispite I have 103 open cursor handlers (what is the subject of ORA-01000 exception), but V$OPEN_CURSOR says to me about 38 “open cursors” – i.g. about 38 handled library cache locks.
Next round of cursor opening leads to ORA-01000:

SQL> DECLARE
  2      rc_number INTEGER;
  3  BEGIN
  4      for i in 1..200 loop
  5          rc_number := DBMS_SQL.OPEN_CURSOR;
  6      end loop;
  7  END;
  8  /
DECLARE
*
ERROR at line 1:
ORA-01000: maximum open cursors exceeded 
ORA-06512: at "SYS.DBMS_SQL", line 1163 
ORA-06512: at line 5 


SQL> show parameter open_cursors

NAME                                 TYPE        VALUE                          
------------------------------------ ----------- ------------------------------ 
open_cursors                         integer     300                            

So if you want to see the real statistics of “cursor leaking” – please use v$mystat or v$sesstat, not v$open_cursor.
OK, let’s come back to DBMS_SQL functionality. If we use cursor variable opened in PL/SQL you may want to use DBMS_SQL.TO_CURSOR_NUMBER functon to get the acess to other DBMS_SQL functions (like DESCRIBE). But in this case you should handle you cursors explicitly. Let’s look at this.

SQL> select value
  2  from v$mystat a, v$statname b
  3  where a.statistic# = b.statistic#
  4  and b.name = 'opened cursors current'
  5  /

     VALUE                                                                      
----------                                                                      
         2                                                                      

SQL> DECLARE
  2  --    rc_number INTEGER;
  3      rc SYS_REFCURSOR;
  4  BEGIN
  5      OPEN rc FOR SELECT * FROM DUAL;
  6  --    rc_number := DBMS_SQL.TO_CURSOR_NUMBER(rc);
  7  END;
  8  /

PL/SQL procedure successfully completed.

SQL> select value
  2  from v$mystat a, v$statname b
  3  where a.statistic# = b.statistic#
  4  and b.name = 'opened cursors current'
  5  /

     VALUE                                                                      
----------                                                                      
         2                                                                      

SQL> DECLARE
  2      rc_number INTEGER;
  3      rc SYS_REFCURSOR;
  4  BEGIN
  5      OPEN rc FOR SELECT * FROM DUAL;
  6      rc_number := DBMS_SQL.TO_CURSOR_NUMBER(rc);
  7  END;
  8  /

PL/SQL procedure successfully completed.

SQL> select value
  2  from v$mystat a, v$statname b
  3  where a.statistic# = b.statistic#
  4  and b.name = 'opened cursors current'
  5  /

     VALUE                                                                      
----------                                                                      
         3                                                                      

SQL> DECLARE
  2      rc_number INTEGER;
  3      rc SYS_REFCURSOR;
  4  BEGIN
  5      OPEN rc FOR SELECT * FROM DUAL;
  6      rc_number := DBMS_SQL.TO_CURSOR_NUMBER(rc);
  7      DBMS_SQL.CLOSE_CURSOR(rc_number);
  8  END;
  9  /

PL/SQL procedure successfully completed.

SQL> 
SQL> select value
  2  from v$mystat a, v$statname b
  3  where a.statistic# = b.statistic#
  4  and b.name = 'opened cursors current'
  5  /

     VALUE                                                                      
----------                                                                      
         3    

When I just declare the cursor variable and open the cursor using it Oracle closes cursor handler implicitly when I leave PL/SQL block. In contrast when I transform REF CURSOR into DBMS_SQL cursor handler Oracle does not care of cursor closure during PL/SQL block leave.
I did not find the explicit mention of this in
Oracle documentation (except with the point what original REF CURSOR variable becomes unusable).
Interesting to note what the reverse operation TO_REFCURSOR seems moves control back to PL/SQL machine:

SQL> select value
  2  from v$mystat a, v$statname b
  3  where a.statistic# = b.statistic#
  4  and b.name = 'opened cursors current'
  5  /

     VALUE                                                                      
----------                                                                      
         2                                                                      

SQL> DECLARE
  2      rc_number INTEGER;
  3      rc1 SYS_REFCURSOR;
  4      rc2 SYS_REFCURSOR;
  5  BEGIN
  6      OPEN rc1 FOR SELECT * FROM DUAL;
  7      rc_number := DBMS_SQL.TO_CURSOR_NUMBER(rc1);
  8      rc2 := DBMS_SQL.TO_REFCURSOR(rc_number);
  9  END;
 10  /

PL/SQL procedure successfully completed.

SQL> select value
  2  from v$mystat a, v$statname b
  3  where a.statistic# = b.statistic#
  4  and b.name = 'opened cursors current'
  5  /

     VALUE                                                                      
----------                                                                      
         2     

P.S. ID 235291.1

Posted in Uncategorized | Leave a comment

FLASHBACK TABLE TO BEFORE DROP and referential partitioning

Referential partitioning gives the advantage of automated cross-table partitioning support. It’s based on referential constraint to map the parent partition structure to the child one.
Also Oracle provides the useful option FLASHBACK TABLE … TO BEFORE DROP. But this feature has the essential restriction what makes it unusable for child tables of referential partitioning schema.

SQL> CREATE TABLE PARENT_TAB (
  2  PARENT_ID NUMBER NOT NULL,
  3  PARENT_NAME VARCHAR2 (30) NOT NULL,
  4  LOCAL_TRANSACTION_TIME TIMESTAMP NOT NULL,
  5  CONSTRAINT PK_PARENT PRIMARY KEY (PARENT_ID))
  6  PARTITION BY RANGE (LOCAL_TRANSACTION_TIME) (
  7  PARTITION P_20090206 VALUES LESS THAN (TIMESTAMP' 2009-02-07 00:00:00'),
  8  PARTITION P_20090207 VALUES LESS THAN (TIMESTAMP' 2009-02-08 00:00:00'),
  9  PARTITION P_20090209 VALUES LESS THAN (TIMESTAMP' 2009-02-09 00:00:00'))
 10  ENABLE ROW MOVEMENT;

Table created.


SQL> CREATE TABLE CHILD_TAB
  2  (
  3  CHILD_ID NUMBER,
  4  PID NUMBER NOT NULL,
  5  CLASSIFICATION NUMBER,
  6  CONSTRAINT PK_CHILD PRIMARY KEY (CHILD_ID),
  7  CONSTRAINT FK_CHILD_TO_PARENT FOREIGN KEY (PID) REFERENCES PARENT_TAB (PARENT_ID)
  8  )
  9  PARTITION BY REFERENCE (FK_CHILD_TO_PARENT)
 10  ENABLE ROW MOVEMENT;

Table created.

SQL> INSERT INTO PARENT_TAB
  2  VALUES(1,'PARENT X', TIMESTAMP' 2009-02-08 00:00:00')
  3  /

1 row created.

SQL> INSERT INTO CHILD_TAB
  2  VALUES(1,1,0)
  3  /

1 row created.

SQL> commit;

Commit complete.

SQL> SELECT partition_name FROM user_tab_partitions
  2  WHERE table_name = 'CHILD_TAB'
  3  /

PARTITION_NAME                                                                  
------------------------------                                                  
P_20090206                                                                      
P_20090207                                                                      
P_20090209                                                                      

SQL> drop table child_tab;

Table dropped.

SQL> flashback table child_tab to before drop;
flashback table child_tab to before drop
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN 


SQL> create table t (x int);

Table created.

SQL> drop table t;

Table dropped.

SQL> flashback table t to before drop;

Flashback complete.

As we can see the child table even was not placed into recyclebin.
Be care :)

Posted in Uncategorized | Leave a comment