A Restriction of the Cardinality Hint

Here is a restriction of the cardinality hint in conjunction with the materialize-hint ( note: both are undocumented but sometimes of great use ):
we cannot tell the optimizer in the outer query ( the one that uses the materialized subquery ) about the cardinality of the materialization, this can only – and then not always – be done within the materializing query.

Update 21/01/2014.
Randolf Geist shows in this comment that this is not true and gives techniques how to achive this.

The example to show that is stolen from Tom Kyte’s Presentation S13961_Best_Practices_for_Managing_Optimizer_Statistics_Short.pptx from ukoug 2013.zip:

sokrates@12.1 > create type str2tbltype is table of varchar2(100);
  2  /

Type created.

sokrates@12.1 > create function str2tbl( p_str in varchar2 ) return str2tblType
  as
  l_str   long default p_str || ',';
  l_n	     number;
  l_data    str2tblType := str2tblType();
  begin
  loop
  l_n := instr( l_str, ',' );
  exit when (nvl(l_n,0) = 0);
 l_data.extend;
 l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1)));
 l_str := substr( l_str, l_n+1 );
 end loop;
 return l_data;
 end;
  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16  /

Function created.

sokrates@12.1 > create table t as select object_id, object_name from dba_objects;

Table created.

sokrates@12.1 > create index t_idx on t( object_name );

Index created.

sokrates@12.1 > exec dbms_stats.gather_table_stats( user, 'T' );

PL/SQL procedure successfully completed.

sokrates@12.1 > variable in_list varchar2(100)
sokrates@12.1 > exec :in_list := 'DBMS_OUTPUT,UTL_FILE,DBMS_PIPE'

PL/SQL procedure successfully completed.

sokrates@12.1 > select count(*) from table(cast( str2tbl( :in_list) as str2tblType) ) t;

  COUNT(*)
----------
	 3

The optimizer does know nothing about the cardinality of this “table(cast( str2tbl( :in_list) as str2tblType) )”.
A clever human could prove that the cardinality of this “table” can never exceed 33.000, so humans sometimes are more clever than the optimizer in estimating cardinalities. In our example, we want to tell the optimizer that the cardinality of this table is approximately 10, which will influence the execution plan of a select which joins this table to a real table.

Without cardinality-hint, the optimizer uses a default cardinality and chooses the wrong hash join:

sokrates@12.1 > with data as
( select *
   from table(cast( str2tbl( :in_list) as str2tblType) ) t
)
select t.object_id, t.object_name
  from data, t
 where t.object_name = data.column_value
  2    3    4    5    6    7    8  /

Execution Plan
----------------------------------------------------------
Plan hash value: 386533642

----------------------------------------------------------------------------------------------
| Id  | Operation			   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		   |	     | 14005 |	 437K|	 154   (1)| 00:00:01 |
|*  1 |  HASH JOIN			   |	     | 14005 |	 437K|	 154   (1)| 00:00:01 |
|   2 |   COLLECTION ITERATOR PICKLER FETCH| STR2TBL |	8168 | 16336 |	  29   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL		   | T	     | 90964 |	2664K|	 124   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

   1 - access("T"."OBJECT_NAME"=VALUE(KOKBF$))

When we tell the optimizer via cardinality hint the correct magnitude of the real cardinality, the right access path “nested loops and index” is used

sokrates@12.1 > with data as
( select /*+cardinality(t, 10) */ *
   from table(cast( str2tbl( :in_list) as str2tblType) ) t
)
select t.object_id, t.object_name
  from data, t
 where t.object_name = data.column_value
  2    3    4    5    6    7    8  /

Execution Plan
----------------------------------------------------------
Plan hash value: 2392632293

-----------------------------------------------------------------------------------------------
| Id  | Operation			    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		    |	      |    17 |   544 |    59	(0)| 00:00:01 |
|   1 |  NESTED LOOPS			    |	      |       |       | 	   |	      |
|   2 |   NESTED LOOPS			    |	      |    17 |   544 |    59	(0)| 00:00:01 |
|   3 |    COLLECTION ITERATOR PICKLER FETCH| STR2TBL |    10 |    20 |    29	(0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN		    | T_IDX   |     2 |       |     2	(0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID	    | T       |     2 |    60 |     3	(0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

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

   4 - access("T"."OBJECT_NAME"=VALUE(KOKBF$))

Note, that we can “pull this hint into the outer query” and still get the exact same execution plan ( even the same plan hash value ):

sokrates@12.1 > with data as
( select *
   from table(cast( str2tbl( :in_list) as str2tblType) ) t
)
select /*+cardinality(data, 10) */ t.object_id, t.object_name
  from data, t
 where t.object_name = data.column_value
  2    3    4    5    6    7    8  /

Execution Plan
----------------------------------------------------------
Plan hash value: 2392632293

-----------------------------------------------------------------------------------------------
| Id  | Operation			    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		    |	      |    17 |   544 |    59	(0)| 00:00:01 |
|   1 |  NESTED LOOPS			    |	      |       |       | 	   |	      |
|   2 |   NESTED LOOPS			    |	      |    17 |   544 |    59	(0)| 00:00:01 |
|   3 |    COLLECTION ITERATOR PICKLER FETCH| STR2TBL |    10 |    20 |    29	(0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN		    | T_IDX   |     2 |       |     2	(0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID	    | T       |     2 |    60 |     3	(0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

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

   4 - access("T"."OBJECT_NAME"=VALUE(KOKBF$))

This behaviour changes when we materialize the inner query.
First we hint the cardinality in the materializing query:

sokrates@12.1 > with data as
( select /*+materialize cardinality(t, 10) */*
   from table(cast( str2tbl( :in_list) as str2tblType) ) t
)
select t.object_id, t.object_name
  from data, t
 where t.object_name = data.column_value
  2    3    4    5    6    7    8  /

Execution Plan
----------------------------------------------------------
Plan hash value: 2115576147

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation			    | Name			| Rows	| Bytes | Cost (%CPU)| Time	|
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		    |				|    17 |  1394 |    61   (0)| 00:00:01 |
|   1 |  TEMP TABLE TRANSFORMATION	    |				|	|	|	     |		|
|   2 |   LOAD AS SELECT		    | SYS_TEMP_0FD9D666D_268859 |	|	|	     |		|
|   3 |    COLLECTION ITERATOR PICKLER FETCH| STR2TBL			|    10 |    20 |    29   (0)| 00:00:01 |
|   4 |   NESTED LOOPS			    |				|	|	|	     |		|
|   5 |    NESTED LOOPS 		    |				|    17 |  1394 |    32   (0)| 00:00:01 |
|   6 |     VIEW			    |				|    10 |   520 |     2   (0)| 00:00:01 |
|   7 |      TABLE ACCESS FULL		    | SYS_TEMP_0FD9D666D_268859 |    10 |    20 |     2   (0)| 00:00:01 |
|*  8 |     INDEX RANGE SCAN		    | T_IDX			|     2 |	|     2   (0)| 00:00:01 |
|   9 |    TABLE ACCESS BY INDEX ROWID	    | T 			|     2 |    60 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------

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

   8 - access("T"."OBJECT_NAME"="DATA"."COLUMN_VALUE")

Still the right plan.

This changes when we pull the cardinality-hint into the outer query:

sokrates@12.1 > with data as
( select /*+materialize */ *
   from table(cast( str2tbl( :in_list) as str2tblType) ) t
)
select /*+cardinality(data, 10) */ t.object_id, t.object_name
  from data, t
 where t.object_name = data.column_value
  2    3    4    5    6    7    8  /

Execution Plan
----------------------------------------------------------
Plan hash value: 4042153407

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation			    | Name			| Rows	| Bytes | Cost (%CPU)| Time	|
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		    |				| 14005 |  1121K|   157   (1)| 00:00:01 |
|   1 |  TEMP TABLE TRANSFORMATION	    |				|	|	|	     |		|
|   2 |   LOAD AS SELECT		    | SYS_TEMP_0FD9D666F_268859 |	|	|	     |		|
|   3 |    COLLECTION ITERATOR PICKLER FETCH| STR2TBL			|  8168 | 16336 |    29   (0)| 00:00:01 |
|*  4 |   HASH JOIN			    |				| 14005 |  1121K|   128   (1)| 00:00:01 |
|   5 |    VIEW 			    |				|  8168 |   414K|     3   (0)| 00:00:01 |
|   6 |     TABLE ACCESS FULL		    | SYS_TEMP_0FD9D666F_268859 |  8168 | 16336 |     3   (0)| 00:00:01 |
|   7 |    TABLE ACCESS FULL		    | T 			| 90964 |  2664K|   124   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------

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

   4 - access("T"."OBJECT_NAME"="DATA"."COLUMN_VALUE")

The cardinality-hint was ignored and the wrong plan was chosen.

This is a pity because when the materialized subquery is more complicated than in this example ( think of a multi-join where the developer just “knows” the magnitude of the result set for some reason the optimizer cannot be aware of – there are always such cases -), we have no chance in helping the optimizer to find the “right” plan just via cardinality hint.

Issue with updatable views

It’s sometimes amazing, how many bugs there are still with elementary SQL.

Here is one concerning updatable views:

sokrates@12.1 > create table t ( v varchar2(30) );

Table created.

sokrates@12.1 > create view v as
  2  select v as dontdothatman, v as canbelostwheninserted
  3  from t; 

View created.

sokrates@12.1 > insert /* this is fine */ into v 
  2  values('fine', 'fine');

1 row created.

sokrates@12.1 > select * from v;

DONTDOTHATMAN		       CANBELOSTWHENINSERTED
------------------------------ ------------------------------
fine			       fine

sokrates@12.1 > insert /* exception expected because 1st value is lost */ into v
  2  values('this one is lost', 'why isnt that one lost ?');

1 row created.

sokrates@12.1 > select * from v;

DONTDOTHATMAN		       CANBELOSTWHENINSERTED
------------------------------ ------------------------------
fine			       fine
why isnt that one lost ?       why isnt that one lost ?

Best Practice in 12c

Since PL/SQL now is closely integrated into SQL, we hence can happily state

sokrates@12.1 > with function bestpractice return varchar2
  2  is
  3  begin
  4     return 'Do not use PL/SQL when it can be done with SQL alone !';
  5  end bestpractice;
  6  select bestpractice() from dual
  7  /

BESTPRACTICE()
--------------------------------------------------------------------------------
Do not use PL/SQL when it can be done with SQL alone !

Strange ORA-14196

It seems that sometimes you need a non-unique index to enforce a unique constraint even if this constraint is declared as not deferrable.

sokrates@11.2 > create table strange(i int not null, j int not null);

Table created.

sokrates@11.2 > alter table strange add constraint unique_i unique(i) not deferrable
  2  using index ( create unique index struix on strange ( i, j ) )
  3  /
alter table strange add constraint unique_i unique(i) not deferrable
*
ERROR at line 1:
ORA-14196: Specified index cannot be used to enforce the constraint.

WTF ?
We have to create a non-unique index here !

sokrates@11.2 > alter table strange add constraint unique_i unique(i) not deferrable
  2  using index ( create  index struix on strange ( i, j ) )
  3  /

Table altered.

Also reproduced on 12.1.
Who can explain this behaviour to me ( I suppose it is a bug ) ?

Partition Info in V$SESSION_LONGOPS

Oracle’s advanced partitioning has some deficiencies. For example, partition info is missing in V$SESSION_LONGOPS for scan-operations ( full table scans, full index scans ). V$SESSION_LONGOPS.TARGET only shows OWNER.TABLE_NAME in these cases, even when the underlying table/index is partitioned, though the longop doesn’t refer to the whole segment but only to one (sub-)partition of it.
I filed an enhancement request several years ago concerning this matter, but never received any feedback.
However, there is a workaround to that. In many cases, we can find out on which (sub-) partition the longop is working on: V$SESSION_WAIT’s P1- and P2-info can be used for that in case the session is waiting mainly on I/O ( which might be most likely for many systems. )
Here is an extension to V$SESSION_LONGOPS which tries to figure out this additional info.

Update 27/02/2014
Note that the original version has been improved by Jonathan Lewis. I have marked the relevant part with a corresponding comment.
I haven’t observed so far that I wasn’t able to get the partition information from v$session.row_wait_obj# ( as suggested by him ), but from the part marked as “superfluous most likely” ( my original version ). However, I have no proof that this is not possible.

select
   coalesce(
        (
            select 'does not apply'
            from dual
            where slo.TARGET not like '%.%'
            or slo.TARGET is null
        ),
        (
            select 'does not apply'
            from dba_tables dt
            where dt.OWNER=substr(slo.target, 1, instr(slo.target, '.') - 1)
            and dt.TABLE_NAME=substr(slo.target, instr(slo.target, '.') + 1)
            and dt.PARTITIONED='NO'       
        ),
        (
            -- Jonathan Lewis, see http://jonathanlewis.wordpress.com/2014/01/01/nvl-2/#comment-62048
            select
               ob.subobject_name || ' (' || ob.object_type || ')'
            from v$session s, dba_objects ob
            where
              ob.object_id = s.row_wait_obj#
            and s.sid = slo.sid
            and ob.OBJECT_TYPE like '%PARTITION%'            
        ),
        (
            -- superfluous most likely
            select
               de.partition_name || ' (' || de.segment_type || ') NOT SUPERFLUOUS IF YOU SEE THAT'
            from v$session_wait sw, dba_extents de
            where
              sw.sid=slo.sid
            and slo.opname like '%Scan%'
            and sw.P1TEXT like 'file%'
            and sw.P1 = de.FILE_ID and sw.P2 between de.BLOCK_ID and de.BLOCK_ID + de.BLOCKS - 1
            and de.owner = substr(slo.target, 1, instr(slo.target, '.') - 1)
            and de.segment_type in
            (
               'TABLE PARTITION', 'TABLE SUBPARTITION',
               'INDEX PARTITION', 'INDEX SUBPARTITION'            
            )
            and de.segment_name in
            (
                 -- table
                 select
                    substr(slo.target, instr(slo.target, '.') + 1)
                 from dual
                 union all
                 -- index
                 select di.index_name
                 from dba_indexes di
                 where di.owner=substr(slo.target, 1, instr(slo.target, '.') - 1)
                 and di.TABLE_NAME = substr(slo.target, instr(slo.target, '.') + 1)
            )
         ),
        'unknown'
      )
   as partition_info,     
   slo.*
from v$session_longops slo
where slo.TIME_REMAINING > 0

Note that this might take a bit longer than a simple

select slo.*
from v$session_longops slo
where slo.TIME_REMAINING > 0

, though due to coalesce’s short circuiting it is quite efficient.

(UTL_RAW.)CAST_TO_DATE

Tim wrote
… the UTL_RAW package has a bunch of casting functions for RAW values (CAST_TO_BINARY_DOUBLE, CAST_TO_BINARY_FLOAT, CAST_TO_BINARY_INTEGER, CAST_TO_NUMBER, CAST_TO_NVARCHAR2, CAST_TO_VARCHAR2). Note the absence of a CAST_TO_DATE function.

Bertrand Drouvot also misses it, see Bind variable peeking: Retrieve peeked and passed values per execution in oracle 11.2

Here is a try to write one, fixes and improvements are welcome !

create or replace function CAST_TO_DATE(bdr in raw) return date deterministic is
begin
  return
     date'1-1-1'
     + NUMTOYMINTERVAL(
         100 * (to_number(substr(bdr,1,2), 'xx') - 100) + 
         to_number(substr(bdr,3,2), 'xx') - 101, 
       'year')
     + NUMTOYMINTERVAL(to_number(substr(bdr,5,2), 'xx')-1, 'month')
     + NUMTODSINTERVAL(to_number(substr(bdr,7,2), 'xx')-1, 'day')
     + NUMTODSINTERVAL(to_number(substr(bdr,9,2), 'xx') - 1, 'hour')   
     + NUMTODSINTERVAL(to_number(substr(bdr,11,2), 'xx') - 1, 'minute')   
     + NUMTODSINTERVAL(to_number(substr(bdr,13,2), 'xx') - 1, 'second');
  exception when others then return to_date(1, 'J');
end CAST_TO_DATE;   
/

A simple pipelined version of print_table

Tom Kyte’s print_table procedure, available on
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1035431863958#14442395195806
seems to be very popular and there exist tricky variations on the theme, for example the following nice xml-trick by Sayan Malakshinov.

Please note that it is very easy to use the existing print_table-code to generate a pipelined version which can be used in SQL.
I use the following code since ages and it always does me a great job, so probably it is worth sharing.

create or replace function fprint_table
( p_query in varchar2,
  p_date_fmt in varchar2 default 'dd-mon-yyyy hh24:mi:ss' )
return sys.odcivarchar2list 
authid current_user
pipelined
   is
l varchar2(4000);
s integer default 1;
begin
  dbms_output.enable(buffer_size => null);
  
  print_table(
     p_query => p_query,
     p_date_fmt => p_date_fmt
  );

  loop
     dbms_output.get_line(line => l, status => s);
     exit when s != 0;
     begin
        pipe row(l);
     exception when no_data_needed then exit;
     end;
  end loop;
    
  return;

end fprint_table;
/

sokrates@11.2 > select * from table(fprint_table('select user,sysdate from dual'));

USER                          : SOKRATES
SYSDATE                       : 10-apr-2013 12:27:50
-----------------
1 row selected.

An undocumented restriction in Workspace Manager – exporting tables with valid time support

If you are using Workspace Manager, it could be probably useful to know, that there is an undocumented restriction concerning import/export.
Due to Import and Export Considerations,
…Workspace Manager supports the import and export of version-enabled tables in one of the following two ways: a full database import and export, and a workspace-level import and export through Workspace Manager procedures. No other export modes, such as schema, table, or partition level, are currently supported….

However, this does not hold for tables with valid time support:

sokrates@11.2 > CREATE TABLE d (id NUMBER PRIMARY KEY);

Table created.

sokrates@11.2 > EXECUTE DBMS_WM.EnableVersioning (table_name=>'D', validTime=>TRUE, hist => 'NONE');

PL/SQL procedure successfully completed.

sokrates@11.2 >  EXECUTE DBMS_WM.Export(table_name => 'D',staging_table => 'D_STG', workspace => 'LIVE');
BEGIN DBMS_WM.Export(table_name => 'D',staging_table => 'D_STG', workspace => 'LIVE'); END;

*
ERROR at line 1:
ORA-20171: WM error: Export not supported on a table with valid time
ORA-06512: at "WMSYS.LT", line 13185
ORA-06512: at line 1

Support confirmed, that in this case only full db import/export (!) is supported, documentation would be updated somewhen.

tuning

The biggest advantage of being developer and DBA at the same time in my eyes is: tuning lies in one hand.
Peter Scott twittered about bringing a query down from 25 hours to 83 seconds by rewriting a query using MINUS.
Funny, in February 1996 I started a new job and my first task was tuning a query running for several hours – basically

select ...
from t
where not exists
( select ...
  from r@remote r
  where <join t and r>
)

which could be tuned down to a few seconds using MINUS, so quite similar to Peter’s job ( Version was 7.0.something at that time as far as I remember ).
In my experience, most performance gains are achieved by rewriting SQL or even by restructuring your entire application logic.

what’s in my buffer cache ?

The following SQL shows me what is currently in my buffer cache and runs in a reasonable amount of time ( never longer than 30 seconds with some dozens of GB buffer cache and around 5 million entries in v$cache ), it also shows me the cached percentage of each segment which is currently part of the cache.
Version is 11.2.

with cache_raw 
as 
(
  select
     c.owner#, c.kind, c.name, c.partition_name, 
     c.status, c.file#, 
     count(*) co
  from v$cache c
  group by 
     c.owner#, c.kind, c.name, c.partition_name,
     c.partition_name, file#, c.status
), cache_raw2
as
(
  select 
     du.username, c.kind, 
     case when c.partition_name is null
            then c.name
            else c.name || ' partition ' || c.partition_name
     end as name,
     case c.status
        when 'free' then 'not currently in use'
        when 'xcur' then 'exclusive'
        when 'scur' then 'shared current'
        when 'cr' then 'consistent read'
        when 'read' then 'being read from disk'
        when 'mrec' then 'in media recovery mode'
        when 'irec' then 'in instance recovery mode'
     end as status,
     c.file#,  
     co * (select value from v$parameter where name='db_block_size') as anzb
  from cache_raw c, dba_users du
  where du.user_id(+)=c.owner#
), cache_segm as
(
  select 
     c.username as owner, c.kind, 
     case 
       when c.kind in ('INDEX', 'INDEX PARTITION')
       then c.name || ' index on '|| ind.table_name
       else c.name
     end as name, 
     round(100 * c.anzb / sum(c.anzb) over (), 2) percentage,
     round(c.anzb / (1024 * 1024 * 1024), 2) as gbytes_in_cache, 
     round(100 * c.anzb / seg.bytes, 2) perc_of_segment_in_cache, 
     round(seg.bytes / (1024 * 1024 * 1024), 2) as gbytes_in_segment,   
     c.status, 
     round(c.anzb / (1024 * 1024), 2) as mbytes_in_cache,
     round(seg.bytes / (1024 * 1024), 2) as mbytes_in_segment,   
     round(c.anzb / (1024), 2) as kbytes_in_cache,   
     round(seg.bytes / (1024), 2) as kbytes_in_segment,            
     c.anzb as bytes_in_cache, 
     seg.bytes as bytes_in_segment,   
     seg.segment_subtype, seg.tablespace_name
  from cache_raw2 c, dba_segments seg, dba_indexes ind
  where 
     seg.owner(+)=c.username
     and 
     case when seg.partition_name(+) is null
            then seg.segment_name(+)
            else seg.segment_name(+) || ' partition ' || seg.partition_name(+)
     end = c.name
     and seg.segment_type(+)=c.kind
  and
     ind.owner(+)=c.username and ind.index_name(+)=nvl(substr(c.name, 1, instr(c.name, ' ', 1) - 1), c.name)
)   
  select 
     case row_number() over(order by c.bytes_in_cache desc) 
       when 1 then round(sum(c.bytes_in_cache) over () / (1024 * 1024 * 1024), 2) 
     end as gb_total,
     c.owner, c.kind, c.name, 
     c.percentage,
     c.gbytes_in_cache, c.perc_of_segment_in_cache, 
     c.gbytes_in_segment, c.status, 
     c.mbytes_in_cache, c.mbytes_in_segment,   
     c.kbytes_in_cache, c.kbytes_in_segment,            
     c.bytes_in_cache, c.bytes_in_segment,   
     c.segment_subtype, c.tablespace_name
  from cache_segm c
  order by 
     c.bytes_in_cache desc