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

RESULT_CACHE – can not be baselined

RESULT_CACHE Oracle hint alows to use the essential Oracle feature – the caching of low-volatile data gives the win in the performance. And it would be very usefull to baseline correspondent execution plan for existing queries.
Let’s check if we can do it.

From the beginning – let’s create the baseline and ensure we can use it.
Our first query should use INDEX FULL SCAN access path:

SQL> select empno from emp order by empno;
EMPNO
----------
7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
7900
7902
7934

14 rows selected.

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 27kbchb2hj15r, child number 0
-------------------------------------
select empno from emp order by empno

Plan hash value: 179099197
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | INDEX FULL SCAN | PK_EMP | 14 | 56 | 1 (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------------------------------

13 rows selected.

OK, now let’s force FULL SCAN usage:

SQL> select /*+ FULL(emp) */ empno from emp order by empno;
EMPNO
----------
7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
7900
7902
7934

14 rows selected.

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID gfm9uc4k95qct, child number 0
-------------------------------------
select /*+ FULL(emp) */ empno from emp order by empno

Plan hash value: 150391907

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | SORT ORDER BY | | 14 | 56 | 4 (25)| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 2 | TABLE ACCESS FULL| EMP | 14 | 56 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------

14 rows selected.

Let’s make plan of the last query baselined and assigned to the first query.

SQL> set serveroutput on
SQL> declare
  2      cnt number;
  3  begin
  4      cnt :=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
  5          sql_id=>'27kbchb2hj15r'
  6      );
  7      dbms_output.put_line(cnt);
  8  end;
  9  /
1
PL/SQL procedure successfully completed.

Elapsed: 00:00:01.71
SQL> SELECT sql_handle, sql_text, plan_name, enabled FROM
2 dba_sql_plan_baselines;

SQL_HANDLE
------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
PLAN_NAME ENA
------------------------------ ---
SYS_SQL_c9d708f33f36449b
select empno from emp order by empno
SQL_PLAN_cmps8yczmcj4v57c0790a YES

Now let’s disable existing baseline

SQL> declare
  2      cnt number;
  3  begin
  4      cnt :=DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
  5      SQL_HANDLE => 'SYS_SQL_c9d708f33f36449b',
  6      PLAN_NAME=> 'SQL_PLAN_cmps8yczmcj4v57c0790a',
  7      ATTRIBUTE_NAME => 'enabled',
  8      ATTRIBUTE_VALUE => 'NO');
  9  end;
10  /
PL/SQL procedure successfully completed.

And now let’s tailor new ineffective plan to our sql:

SQL> declare
  2      cnt number;
  3  begin
  4      cnt :=DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
  5      SQL_HANDLE => 'SYS_SQL_c9d708f33f36449b',
  6      PLAN_NAME=> 'SQL_PLAN_cmps8yczmcj4v57c0790a',
  7      ATTRIBUTE_NAME => 'enabled',
  8      ATTRIBUTE_VALUE => 'NO');
  9  end;
10  /
PL/SQL procedure successfully completed.

Elapsed: 00:00:00.14
SQL> SELECT sql_handle, sql_text, plan_name, enabled FROM
2 dba_sql_plan_baselines;

SQL_HANDLE
------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
PLAN_NAME ENA
------------------------------ ---
SYS_SQL_c9d708f33f36449b
select empno from emp order by empno
SQL_PLAN_cmps8yczmcj4v57c0790a NO

Elapsed: 00:00:00.00
SQL> declare
2 cnt number;
3 begin
4 cnt:=dbms_spm.load_plans_from_cursor_cache(
5 sql_id => 'gfm9uc4k95qct',
6 plan_hash_value => 150391907,
7 sql_handle => 'SYS_SQL_c9d708f33f36449b',
8 fixed=>'YES');
9 end;
10 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.17
SQL> SELECT sql_handle, sql_text, plan_name, enabled FROM
2 dba_sql_plan_baselines;

SQL_HANDLE
------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
PLAN_NAME ENA
------------------------------ ---
SYS_SQL_c9d708f33f36449b
select empno from emp order by empno
SQL_PLAN_cmps8yczmcj4v57c0790a NO

SYS_SQL_c9d708f33f36449b
select empno from emp order by empno
SQL_PLAN_cmps8yczmcj4vc6f9334c YES

SQL_HANDLE
------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
PLAN_NAME ENA
------------------------------ ---

Elapsed: 00:00:00.01
SQL> set autotrace on
SQL> select empno from emp order by empno;

EMPNO
----------
7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
7900
7902
7934

14 rows selected.

Elapsed: 00:00:01.39

Execution Plan
----------------------------------------------------------
Plan hash value: 150391907

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 56 | 4 (25)| 00:00:01 |
| 1 | SORT ORDER BY | | 14 | 56 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 56 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------

Note
-----
- SQL plan baseline "SQL_PLAN_cmps8yczmcj4vc6f9334c" used for this statement

Statistics
----------------------------------------------------------
36 recursive calls
14 db block gets
26 consistent gets
2 physical reads
2976 redo size
566 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
14 rows processed

Well, OK, we did it. :)
Now let’s do the same for RESULT_CACHE

SQL> select * from dept;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

Elapsed: 00:00:00.01
SQL> SELECT * FROM table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 3154rqzb8xudy, child number 0
-------------------------------------
select * from dept

Plan hash value: 3383998547

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------

13 rows selected.

Elapsed: 00:00:00.34
SQL> select /*+ RESULT_CACHE */ * from dept;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

Elapsed: 00:00:00.06
SQL> select /*+ RESULT_CACHE */ * from dept;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

Elapsed: 00:00:00.00
SQL> SELECT * FROM table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 2km3uu3ddcqan, child number 0
-------------------------------------
select /*+ RESULT_CACHE */ * from dept

Plan hash value: 3383998547

--------------------------------------------------------------------------------
-----------------

| Id | Operation | Name | Rows | Bytes | Cost (
%CPU)| Time |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
-----------------

| 0 | SELECT STATEMENT | | | | 3
(100)| |

| 1 | RESULT CACHE | 8r96gypwgu6ng1gwzmznkkp626 | | |
| |

| 2 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
(0)| 00:00:01 |

--------------------------------------------------------------------------------
-----------------

Result Cache Information (identified by operation id):
------------------------------------------------------

1 -

19 rows selected.

Please notice interesting thing – dispite we have quite different SQL_IDs for these two queries above we have similar plan hash values.
OK, let’s try now to make it baselined:

SQL> declare
  2      cnt number;
  3  begin
  4   FOR c IN (SELECT * FROM dba_sql_plan_baselines) LOOP
  5      cnt := DBMS_SPM.DROP_SQL_PLAN_BASELINE
  6          (c.sql_handle, c.plan_name);
  7   END LOOP;
  8  END;
9  /
PL/SQL procedure successfully completed.

Elapsed: 00:00:00.42
SQL> SELECT sql_handle, sql_text, plan_name, enabled FROM
2 dba_sql_plan_baselines;

no rows selected

Elapsed: 00:00:00.00
SQL> set serveroutput on
SQL> declare
2 cnt number;
3 begin
4 cnt :=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
5 sql_id=>'3154rqzb8xudy'
6 );
7 dbms_output.put_line(cnt);
8 end;
9 /
1

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.07
SQL> SELECT sql_handle, sql_text, plan_name, enabled FROM
2 dba_sql_plan_baselines;

SQL_HANDLE
------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
PLAN_NAME ENA
------------------------------ ---
SYS_SQL_8e73ec4c599bebb6
select * from dept
SQL_PLAN_8wwzc9jctruxq0e23be79 YES

Elapsed: 00:00:00.00
SQL> declare
2 cnt number;
3 begin
4 cnt :=DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
5 SQL_HANDLE => 'SYS_SQL_8e73ec4c599bebb6',
6 PLAN_NAME=> 'SQL_PLAN_8wwzc9jctruxq0e23be79',
7 ATTRIBUTE_NAME => 'enabled',
8 ATTRIBUTE_VALUE => 'NO');
9 end;
10 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.20
SQL> SELECT sql_handle, sql_text, plan_name, enabled FROM
2 dba_sql_plan_baselines;

SQL_HANDLE
------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
PLAN_NAME ENA
------------------------------ ---
SYS_SQL_8e73ec4c599bebb6
select * from dept
SQL_PLAN_8wwzc9jctruxq0e23be79 NO

Elapsed: 00:00:00.00
SQL> declare
2 cnt number;
3 begin
4 cnt:=dbms_spm.load_plans_from_cursor_cache(
5 sql_id => '2km3uu3ddcqan',
6 plan_hash_value => 3383998547,
7 sql_handle => 'SYS_SQL_8e73ec4c599bebb6',
8 fixed=>'YES');
9 end;
10 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.31
SQL> SELECT sql_handle, sql_text, plan_name, enabled FROM
2 dba_sql_plan_baselines;

SQL_HANDLE
------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
PLAN_NAME ENA
------------------------------ ---
SYS_SQL_8e73ec4c599bebb6
select * from dept
SQL_PLAN_8wwzc9jctruxq0e23be79 YES

Elapsed: 00:00:00.00
SQL> set autotrace on
SQL> select * from dept;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 3383998547

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 80 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------

Note
-----
- SQL plan baseline "SQL_PLAN_8wwzc9jctruxq0e23be79" used for this statement

Statistics
----------------------------------------------------------
15 recursive calls
14 db block gets
15 consistent gets
0 physical reads
2924 redo size
666 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed

Seems this does not work. As we can see Oracle does not create new baseline in this particular case but
re-enables the existing one.
I have no idea now is it a bug or a feature. Because the procedure LOAD_PLAN_FROM_CURSOR_CACHE uses both sql_id and plan_hash_value all this looks to me as a bug.

Posted in Uncategorized | 3 Comments