Deterministic function vs scalar subquery caching. Part 1

Posted on by Posted in deterministic functions, oracle, scalar subquery caching 8,569 Page views 8 Comments

I recently did a comparison caching mechanisms of scalar subquery caching(SSC) and deterministic functions in 11.2. Unfortunately, I do not have enough time to do a full analysis, so I will post it in parts.

Today’s topics:
1. Both mechanisms are based on hash functions.(You can read great articles about hash tables and hash collisions for scalar subquery caching by Tom Kyte and Jonathan Lewis(“Cost-Based Oracle fundamentals” chapter 9))
2. Deterministic caching depends on fetch size(arraysize) – results cached only within one fetch call, ssc has no this limitation.
3. Hash collisions depends on the single parameter “_query_execution_cache_max_size” for both mechanizms, but they are more frequent in SSC.

UPD: Part 2
Continue reading→

Materialization in subquery factoring without hint “materialize” can be considered only when exists at least one predicate

Posted on by Posted in CBO, oracle, query optimizing, undocumented 3,469 Page views 6 Comments

I found just now that materialization can not be considered by optimizer if there are no predicates in subquery factoring clause. Of course, i mean cases without forcing materialization through hint “materialize”.
Simple example:

Spoiler

[sourcecode language=”sql”]

SQL> create table tt1 as select 1 id from dual;

Table created.

SQL> exec dbms_stats.gather_table_stats(”,’TT1′);

PL/SQL procedure successfully completed.

SQL> explain plan for
2 with gen as (select * from tt1)
3 select * from gen,gen g2;

Explained.

SQL> @xplan

PLAN_TABLE_OUTPUT
———————————————————————————————————-
Plan hash value: 486748850

—————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 6 | 6 (0)| 00:00:01 |
| 1 | MERGE JOIN CARTESIAN| | 1 | 6 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | TT1 | 1 | 3 | 3 (0)| 00:00:01 |
| 3 | BUFFER SORT | | 1 | 3 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | TT1 | 1 | 3 | 3 (0)| 00:00:01 |
—————————————————————————–

SQL> explain plan for
2 with gen as (select * from tt1 where 1=1)
3 select * from gen,gen g2;

Explained.

SQL> @xplan

PLAN_TABLE_OUTPUT
———————————————————————————————————-
Plan hash value: 2673059801

———————————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————————————
| 0 | SELECT STATEMENT | | 1 | 26 | 7 (0)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6610_6641830 | | | | |
| 3 | TABLE ACCESS FULL | TT1 | 1 | 3 | 3 (0)| 00:00:01 |
| 4 | MERGE JOIN CARTESIAN | | 1 | 26 | 4 (0)| 00:00:01 |
| 5 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6610_6641830 | 1 | 3 | 2 (0)| 00:00:01 |
| 7 | BUFFER SORT | | 1 | 13 | 4 (0)| 00:00:01 |
| 8 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 9 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6610_6641830 | 1 | 3 | 2 (0)| 00:00:01 |
———————————————————————————————————
[/sourcecode]

[collapse]

Update: I did some additional tests and found:

  1. with “table()” but without “xmltable” materialization occurs always regardless of existence of predicates or another tables in subquery factoring clause
  2. with “xmltable” behavior is very strange – decision about materialization depends on ‘columns …’ clause: when it exists – materialization occurs, if not – doesn’t.
  3. with “selects a subset of table columns” as David Aldridge said – decision still depends from predicates existence

Tests with table() were like this:

[sourcecode language=”sql”]
with t as (select * from table(cast(:a as sys.ku$_vcnt)) /*where 1=0*/ )
select count(*) from t,t t2;
with t as (select * from t10,table(cast(:a as sys.ku$_vcnt)) /*where 1=0*/ )
select count(*) from t,t t2;
[/sourcecode]

[collapse]

Tests with xmltable() were like this:

[sourcecode language=”sql”]
with t as (select * from t10,xmltable(:a ) ttt where 1=1)
select count(*)
from t, t t1;
with t as (select * from t10,xmltable(:a columns n int) ttt where 1=0)
select count(*)
from t, t t1;
with t as (select/*+ no_merge */ * from table(cast(:a as sys.ku$_vcnt)),xmltable(:a) where 1=0 )
select count(*) from t,t t2;
[/sourcecode]

[collapse]

Test with 2 from 1000 columns

[sourcecode language=”sql”]
declare
c varchar2(32767):=’create table t_1000_cols as select ‘;
c2 varchar2(32767);
begin
for i in 1..1000 loop
c2:=c2||’,lpad(1,4000,1) c’||i;
end loop;
c:=c||ltrim(c2,’,’)||’ from dual connect by level<=100′;
execute immediate c;
end;
/
exec dbms_stats.gather_table_stats(”,’T_1000_COLS’);
alter session set tracefile_identifier = mat1000;
alter session set events=’10053 trace name context forever, level 1′;

with t as (select c1,c2 from t_1000_cols)
select count(*)
from t, t t2;

with t as (select c1,c2 from t_1000_cols where 1=1)
select count(*)
from t, t t2;
[/sourcecode]

[collapse]

Bug in documentation about dbms_stats.gather_schema_stats or in the dbms_stats itself

Posted on by Posted in bug, CBO, documentation, oracle, statistics, undocumented 2,232 Page views 2 Comments

Recently I had to gather pending stats with Object Filter List by several objects, so I copied example from documentation and was surprised: instead of gather stats by specified filter list, oracle started to gather stats for all tables in current_schema! And ‘filter list’ applies only with dbms_stats.GATHER_DATABASE_STATS

UPD: Jared Still gave link to registered bug id, which i couldn’t find in MOS before:

Bug 12754926 – DBMS_STATS.gather_schema_stats does not work with an obj_filter_list parameter specified [ID 12754926.8]
Modified:04-Jan-2012 Type:PATCH Status:PUBLISHED
This issue is fixed in 11.2.0.4 (Future Patch Set)

Little example

[sourcecode language=”sql”]
SQL> exec dbms_stats.delete_schema_stats(‘HR’);

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.31
SQL> begin
2 for r in (select table_name
3 from dba_tables t
4 where t.owner=’XTENDER’
5 and table_name like ‘TMP%’
6 )loop
7 dbms_stats.delete_table_stats(‘XTENDER’,r.TABLE_NAME);
8 end loop;
9 end;
10 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.31
SQL> select
2 owner
3 ,table_name
4 ,num_rows
5 ,last_analyzed
6 from dba_tab_statistics t
7 where owner=’XTENDER’ and table_name like ‘TMP%’
8 or owner=’HR’;

OWNER TABLE_NAME NUM_ROWS LAST_ANALYZED
—————————— —————————— ———- ——————-
HR COUNTRIES
HR DEPARTMENTS
HR EMPLOYEES
HR JOBS
HR JOB_HISTORY
HR LOCATIONS
HR REGIONS
XTENDER TMP
XTENDER TMP1
XTENDER TMP_CONTENT
XTENDER TMP_DATA
XTENDER TMP_DOCUMENT
XTENDER TMP_EXCH

13 rows selected.

Elapsed: 00:00:00.11
SQL> col dt new_value dt
SQL> select sysdate dt from dual;

DT
——————-
2013-01-27 00:30:21

1 row selected.

Elapsed: 00:00:00.00
SQL> DECLARE
2 filter_lst DBMS_STATS.OBJECTTAB := DBMS_STATS.OBJECTTAB();
3 BEGIN
4 filter_lst.extend(2);
5 filter_lst(1).ownname := ‘XTENDER’;
6 filter_lst(1).objname := ‘TMP%’;
7 filter_lst(2).ownname := ‘HR’;
8 DBMS_STATS.GATHER_SCHEMA_STATS(NULL, obj_filter_list => filter_lst,
9 options => ‘gather’);
10 END;
11 /

PL/SQL procedure successfully completed.

Elapsed: 00:03:16.89
SQL> select owner,table_name
2 from dba_tables t
3 where t.LAST_ANALYZED>timestamp’&dt’+0;

OWNER TABLE_NAME
—————————— ——————————
XTENDER TT
XTENDER TR_LOG
XTENDER IOT1
…[skipped 171 rows]…
XTENDER DEPARTMENTS

175 rows selected.

Elapsed: 00:00:01.04
[/sourcecode]

[collapse]

PS. Also there is a typo in the example from oracle documentation: ‘gather_stale’ instead of ‘gather stale’ – underline instead of blank space.

Just another version of Tom Kyte’s runstats (runstats_pkg)

Posted on by Posted in oracle, runstats, statistics 2,437 Page views Leave a comment

I want to share my modifications of Tom Kyte’s runstats package, which include:

  • Any number of runs sets for analyzing
  • Standalone: No need to create other objects
  • Ability to specify session SID for statistics gathering
  • Ability to specify what to gather: latches, stats or both
  • Separate mask filters for output by statname and latchname
  • Ability to specify difference percentage for output separately for latches and statistics
  • More accurate with some statistics because of avoiding global temporary table usage, but less in several others because of collections usage

Link to package: https://github.com/xtender/xt_runstats

Output example:

SQL> begin
  2    -- little example which shows difference between "fast dual" and "full table scan dual":
  3    -- http://docs.oracle.com/cd/E11882_01/server.112/e17118/queries009.htm#SQLRF20036
  4    xt_runstats.init(p_latches => false);
  5    -- 1:
  6    for r in (select * from dual connect by level<=1e3) loop
  7      null;
  8    end loop;
  9    xt_runstats.snap;
 10
 11    -- 2:
 12    for r in (select 'X' dummy from dual connect by level<=1e3) loop
 13      null;
 14    end loop;
 15    xt_runstats.snap;
 16    xt_runstats.print(p_stats_mask => '%gets%');
 17  end;
 18  /
################     Results:      ##################
Run #  01 ran in 0 hsecs
Run #  02 ran in 0 hsecs
###########################################################################
Statistics                               | Run # 1        | Run # 2
###########################################################################
consistent gets......................... |              3 |              0
consistent gets from cache.............. |              3 |              0
consistent gets from cache (fastpath)... |              3 |              0
no work - consistent read gets.......... |              1 |              0
###########################################################################
-

Some usage examples:
1. for own session:

      begin
        xt_runstats.init();
        [some_code_1] 
        xt_runstats.snap();
        [some_code_2]
        xt_runstats.snap();
        ... 
        [some_code_N]
        xt_runstats.snap();
        -- result output:
        xt_runstats.print();
      end;

2. for session with sid = N

      begin xt_runstats.init(N); end;
      ...[after a while]
      begin xt_runstats.snap; end;
      ...[one more if needed...]
      begin xt_runstats.snap; end;
        -- result output:
      begin xt_runstats.print(); end;

3. Latches only:

xt_runstats.init(p_stats=>false);

4. Print stats with name like ‘%gets%’:

xt_runstats.print(p_stats_mask=>'%gets%');

5. Print latches which differ by 30% or more and stats differ by 15% or more:

xt_runstats.print( p_lat_diff_pct=>30, p_sta_diff_pct => 15);

Differences between integer(int) in SQL and PL/SQL

Posted on by Posted in oracle, undocumented 8,118 Page views Leave a comment

Very simple example:

create table t_integer(i integer);
insert into t_integer values(1e125);
select * from t_integer;

declare
  i1 integer;
begin 
  select i into i1 from t_integer;
end;
/
declare
  i2 t_integer.i%type;
begin 
  select i into i2 from t_integer;
end;
/

Although variable and the column specified as “integer”, but in both blocks you will get error: ORA-06502: PL/SQL: numeric or value error: number precision too large.
Same error will be with “int“.
Describe shows wrong datatype:

> desc t_integer;
 Name                        Null?    Type
 --------------------------- -------- ----------
 I                                    NUMBER(38)

Really it would be number without precision and scale=0. You can see it in dba_tab_columns.
sys.standard:

  subtype INTEGER is NUMBER(38,0);
  subtype INT is INTEGER;

Also a couple simple good-known but often forgotten things:
1. integer as parameter type or return type:

SQL> create function f_integer(i integer)
  2    return integer is
  3  begin
  4    return i;
  5  end;
  6  /

Function created.

SQL> select f_integer(1/3) from dual;

F_INTEGER(1/3)
--------------
    .333333333

1 row selected.

2. Old finding from sql.ru – no casting in assignments (from 10.2 till 11.2, was found by Elic):

declare
  numberVar  number        := 1/3;
  numberVar2 number(20,10) := 1/3;
  procedure Test(Label varchar2, Value int)
  is
    LocalVar int := Value;
  begin
    dbms_output.put_line(Label || '         : ' || Value);
    dbms_output.put_line(Label || ' assigned: ' || LocalVar);
  end Test;
begin
  Test('|                 const', 1/3);
  Test('|            number var', numberVar);
  Test('|constrained number var', numberVar2);
end;
/
|                 const         : .3333333333333333333333333333333333333333
|                 const assigned: .3333333333333333333333333333333333333333
|            number var         : .3333333333333333333333333333333333333333
|            number var assigned: .3333333333333333333333333333333333333333
|constrained number var         : .3333333333
|constrained number var assigned: .3333333333

PL/SQL procedure successfully completed.

The change in “dump sort statistics” trace (event 10032)

Posted on by Posted in CBO, oracle, trace 1,898 Page views Leave a comment

Earlier “Event 10032 – Dump Sort Statistics” showed information about sorts only(11.2.0.1 incl.), but in 11.2.0.3 it also showing “Abridged” call stack. I have not tested it on 11.2.0.2, so I can’t tell from which version it works.

alter session set events '10032 trace name context forever, level 10';

From trace file:

*** 2012-10-30 23:14:34.627
*** SESSION ID:(41.15067) 2012-10-30 23:14:34.627
*** CLIENT ID:() 2012-10-30 23:14:34.627
*** SERVICE NAME:(orasql.org) 2012-10-30 23:14:34.627
*** MODULE NAME:(SQL*Plus) 2012-10-30 23:14:34.627
*** ACTION NAME:() 2012-10-30 23:14:34.627

soropn: opened (new) sort, sordef 0x7fc4679e2550, flags 0x802
        maxkey 25, nflds 12, nkflds 1

*** 2012-10-30 23:14:34.678
----- Current SQL Statement for this session (sql_id=3ktacv9r56b51) -----
select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#, nvl(property,0),subname,type#,d_attrs from dependency$ d, obj$ o where d_obj#=:1 and p_obj#=obj#(+) order by order#
        Abridged call stack trace:
ksedsts<-soropn<-qersoProcessULS<-qersoFetch<-opifch2<-opifch<-opiodr<-rpidrus<-skgmstack<-rpiswu2<-rpidrv<-rpifch<-kqllod<-kglobld<-kglobpn<-kglpim<-kglpin<-kglgob<-kgldpo0<-qcdlgpo<-qcsRslvPLSQLInvoc1<-qcsRslvPLSQLInvoc<-qcsRslvName<-qcsridn<-qcsraic<-qcspqbDescendents
<-qcspqb<-kkmdrv<-opiSem<-opiDeferredSem<-opitca<-kksFullTypeCheck<-rpiswu2<-kksLoadChild<-kxsGetRuntimeLock<-kksfbc<-kkspsc0<-kksParseCursor<-opiosq0<-kpooprx<-kpoal8<-opiodr<-ttcpip<-opitsk<-opiino<-opiodr<-opidrv<-sou2o<-opimai_real<-ssthrdmain<-main<-__libc_start_main
<-_start        End of abridged call stack trace.

*** 2012-10-30 23:14:35.328
soreod: sorp 0x7fc4679e2550
---- Sort Parameters ------------------------------
sort_area_size                    65536
sort_area_retained_size           65536
sort_multiblock_read_count        1
max intermediate merge width      3

Deceptive commit after select from dblink.

Posted on by Posted in commit, documentation, oracle, remote statements, trace, transactions 3,384 Page views Leave a comment

Recently on our russian forum we discussed about distributed transaction: is “insert /*+ append */ into x select * from t@dblink” a distributed transaction or not?
According to the documentation – no:

A distributed transaction includes one or more statements that, individually or as a group, update data on two or more distinct nodes of a distributed database

And Tom Kyte said the same:

In the following, we do not have a distributed transaction when we just insert /*+ append */ into local_table select * from remote@table; – we just have a single site transaction.

But v$global_transaction(but v$transaction), another part of documentation and v$lock(type=’DX’) assure that it is.

Distributed statement: A statement that accesses data on two or more distinct nodes/instances of a distributed database.
A remote statement accesses data on one remote node of a distributed database.

Who is right?

But it not so interesting as my another finding: commit after simple “select * from dblink” would be “read-only” commit on local db, but on remote it became “read-only” rollback! Except cases when there was any dml in that transaction(with no difference local or not). But how Oracle determining: read-only or not, execute commit or rollback?

Yet another interesting thing: If we do a few times “commit” after “select from dblink”, then, as expected, rollback will be executed only once on remote. But when we closing our session, there is another commit on remote(real read-only commit).

Tests below: Continue reading→

Easy way to tracing

Posted on by Posted in diagnostic event 10046, oracle, trace 2,147 Page views Leave a comment

Recently I needed to trace connections incoming by dblink, and I decided to make it more convenient, without the trigger.
It’s just three simple steps:

  1. Create new service
  2. Turn on tracing on this service
  3. Edit tnsnames and add dblink for this service if needed

So Oracle will trace all connections to this service.

Below code for sql*plus:

begin
  DBMS_SERVICE.CREATE_SERVICE('db11203trace','db11203trace');
end;
/
column new_value new_value new_value;
select p.value||',db11203trace' new_value from v$parameter p where p.name like 'service_names';
alter system set service_names='&new_value';
exec dbms_monitor.serv_mod_act_trace_enable(service_name => 'db11203trace',waits => true,binds => true);

Amazing optimization of getting distinct values from the index, and TopN for each of them

Posted on by Posted in CBO, Index bouncy scan, oracle, query optimizing 11,949 Page views 5 Comments

A couple of days ago someone posted a question on the forum which at the first glance seemed old, boring, beaten up and down:

There is a news feed. All news are divided into 10 categories (Politics, sport, auto, real estate, etc).
I need to get top 4 news sorted by time descending for each category with 1 query.
If you sort the results – you get 4 politics news, then 4 sport news etc.

But the task was to make it optimal, and the standard solution with usual TopN using row_number can not be called optimal in any way, especially in case of big tables, relatively small number of categories and uneven distribution or just overall low selectivity.

So my idea was to start from min() and get next values using “Index range scan(min/max)” recursively.  I couldn’t find a good name for this technique, so let’s call it as Jonathan Lewis – “Index bouncy scan”:

1. Getting distinct values from the index 

Suppose we have a table with index on the “а” column:

create table xt_test(a not null,b not null,c)
as
select
    length(object_name)
   ,nvl(object_id,0)
   ,o.OBJECT_NAME
from dba_objects o;
create index ix_test_a on xt_test(a);
SQL> select i.index_name
  2        ,i.distinct_keys,i.num_rows
  3        ,i.blevel,i.leaf_blocks
  4        ,i.avg_leaf_blocks_per_key,i.avg_data_blocks_per_key
  5  from user_indexes i where i.table_name='XT_TEST';

INDEX_NAME  DISTINCT_KEYS  NUM_ROWS   BLEVEL LEAF_BLOCKS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY
----------- ------------- --------- -------- ----------- ----------------------- -----------------------
IX_TEST_A              30     69230        1         135                       4                     191

1 row selected.

DDL for this test case:

Spoiler

[sourcecode language=”sql”]
drop table xt_test purge;
create table xt_test(a not null,b not null,c)
as
select
length(object_name)
,nvl(object_id,0)
,o.OBJECT_NAME
from dba_objects o
;
create index ix_test_a on xt_test(a);
begin
dbms_stats.gather_table_stats(
”
,’XT_TEST’
,estimate_percent=>100
,cascade=>true
,method_opt => ‘for all indexed columns size auto’
);
end;
/

select i.index_name
,i.distinct_keys,i.num_rows
,i.blevel,i.leaf_blocks
,i.avg_leaf_blocks_per_key,i.avg_data_blocks_per_key
from user_indexes i
where i.table_name=’XT_TEST’;
[/sourcecode]

[collapse]

This field have very skewed distribution of values:

distribution

A COUNT(*)
1 11
2 20
3 59
4 92
5 178
6 251
7 521
9 570
10 636
8 640
11 962
12 970
13 1151
15 1363
14 1544
16 1692
18 2021
17 2023
19 2550
20 2606
21 3050
22 3171
23 3395
24 3472
29 3527
27 3596
26 3698
28 4130
25 4268
30 17063
ALL 69230

[collapse]

A standard query using distinct is very unsuccessful – there are only 30 distinct keys in the index, while there are 135 blocks to read!
With IFS:

DB11G/XTENDER> select/*+ INDEX(xt_test) */ distinct a from xt_test;

30 rows selected.

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 3405466263

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |    30 |    90 |   140   (3)| 00:00:02 |
|   1 |  SORT UNIQUE NOSORT|           |    30 |    90 |   140   (3)| 00:00:02 |
|   2 |   INDEX FULL SCAN  | IX_TEST_A | 69230 |   202K|   137   (1)| 00:00:02 |
--------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        138  consistent gets
          0  physical reads
          0  redo size
        751  bytes sent via SQL*Net to client
        431  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         30  rows processed
With IFFS:

[sourcecode language=”sql”]
DB11G/XTENDER&gt; select distinct a from xt_test;

30 rows selected.

Elapsed: 00:00:00.05

Execution Plan
———————————————————-
Plan hash value: 4206828362

———————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————–
| 0 | SELECT STATEMENT | | 30 | 90 | 42 (10)| 00:00:01 |
| 1 | HASH UNIQUE | | 30 | 90 | 42 (10)| 00:00:01 |
| 2 | INDEX FAST FULL SCAN| IX_TEST_A | 69230 | 202K| 38 (0)| 00:00:01 |
———————————————————————————–

Statistics
———————————————————-
1 recursive calls
0 db block gets
143 consistent gets
0 physical reads
0 redo size
751 bytes sent via SQL*Net to client
431 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
30 rows processed
[/sourcecode]

[collapse]

We also could go along the tree visiting only the required blocks, but not all leaf blocks! However, Oracle can’t manage this on its own so we have to make a certain twist: aside from IFS(min/max) Oracle also has IRS(min/max) which works well with ranges and boundaries. We can use recursive query to make it read only what we need!

DB11G/XTENDER> with t_unique( a ) as (
  2                select min(t1.a)
  3                from xt_test t1
  4                union all
  5                select (select min(t1.a) from xt_test t1 where t1.a&amp;amp;gt;t.a)
  6                from t_unique t
  7                where a is not null
  8  )
  9  select * from t_unique where a is not null;

30 rows selected.

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 2791305641

-------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |           |     2 |    26 |     4   (0)| 00:00:01 |
|*  1 |  VIEW                                     |           |     2 |    26 |     4   (0)| 00:00:01 |
|   2 |   UNION ALL (RECURSIVE WITH) BREADTH FIRST|           |       |       |            |          |
|   3 |    SORT AGGREGATE                         |           |     1 |     3 |            |          |
|   4 |     INDEX FULL SCAN (MIN/MAX)             | IX_TEST_A |     1 |     3 |     2   (0)| 00:00:01 |
|   5 |    SORT AGGREGATE                         |           |     1 |     3 |            |          |
|   6 |     FIRST ROW                             |           |     1 |     3 |     2   (0)| 00:00:01 |
|*  7 |      INDEX RANGE SCAN (MIN/MAX)           | IX_TEST_A |     1 |     3 |     2   (0)| 00:00:01 |
|*  8 |    RECURSIVE WITH PUMP                    |           |       |       |            |          |
-------------------------------------------------------------------------------------------------------

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

   1 - filter("A" IS NOT NULL)
   7 - access("T1"."A"&amp;amp;gt;:B1)
   8 - filter("A" IS NOT NULL)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         36  consistent gets
          0  physical reads
          0  redo size
        751  bytes sent via SQL*Net to client
        431  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
         32  sorts (memory)
          0  sorts (disk)
         30  rows processed

The difference is obvious: 36 consistent gets for 30 values, instead of 135. Note that this is a very small table, and we will have а much notable difference for millions and billions of entries!
Here is the explanation of the algorithm:

  • In the first part of union all (3-4 strings of plan) we specify where to start the recursion, and more specifically we choose a minimal (first) the value from the index.
  • After that we choose the first value that is bigger than the one chosen in the previous step, using IRS(min/max) (7-6-5 stings of the plan).
  • Repeat the recursion while we find anything

Proceed to the next:

2. TopN entries for every key value
Now as we are armed with an easy tool to get every initial value, we can easily get Top N for each of them. The only problem that remains is that, we can not use inline view with row_number/rownum, as the predicate from higher level won’t be pushed there, and we will have to use simple restriction by count stop key (by rownum) with required access by IRS descending (order by is generally unnecessary there, but it further reduces reading costs of IRS descending, which is necessary for implicit sorting) with the index_desc hint, to nail it dead, otherwise sorting may break. So to make this happen we either have to use an undocumented Lateral() with a corresponding event turned on, or use a simpler and standard table(multiset(…)) or a little harder with xmltable() – but it is not so dangerous. Yet another variant is the use cursor() with pushed predicates:

With cursor() 

[sourcecode language="sql"]
with t_unique( a ) as (
              select min(t1.a)
              from xt_test t1
              union all
              select (select min(t1.a) from xt_test t1 where t1.a&amp;amp;amp;amp;gt;t.a)
              from t_unique t
              where a is not null
)
select cursor(
              select rid from(
                 select/*+ index_desc(tt ix_xt_test_ab) */
                    tt.a
                   ,tt.rowid rid
                   ,row_number()over(partition by a order by b desc) rn
                 from xt_test tt
                 order by tt.b desc
              )
              where a=v.a and rn&amp;amp;amp;amp;lt;=5
       )
from t_unique v
[/sourcecode]

[collapse]
With table() and multiset() 

[sourcecode language="sql"]
DB11G/XTENDER&amp;amp;amp;amp;gt; with t_unique( a ) as (
  2                select min(t1.a)
  3                from xt_test t1
  4                union all
  5                select (select min(t1.a) from xt_test t1 where t1.a&amp;amp;amp;amp;gt;t.a)
  6                from t_unique t
  7                where a is not null
  8  )
  9  select/*+ use_nl(rids tt) */ *
 10  from t_unique v
 11      ,table(
 12            cast(
 13                 multiset(
 14                          select/*+ index_desc(tt ix_xt_test_ab) */ tt.rowid rid
 15                          from xt_test tt
 16                          where tt.a=v.a
 17                            and rownum&amp;amp;amp;amp;lt;=5
 18                          order by tt.b desc
 19                         )
 20                 as sys.odcivarchar2list
 21                )
 22            ) rids
 23      ,xt_test tt
 24  where tt.rowid=rids.column_value
 25  order by tt.a,tt.b desc;

150 rows selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 4085270117

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                    | Name          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                             |               |    11M|   506M|       |   149K  (1)| 00:29:54 |
|   1 |  SORT ORDER BY                               |               |    11M|   506M|   649M|   149K  (1)| 00:29:54 |
|   2 |   NESTED LOOPS                               |               |    11M|   506M|       | 16402   (1)| 00:03:17 |
|   3 |    NESTED LOOPS                              |               | 16336 |   239K|       |    60   (0)| 00:00:01 |
|   4 |     VIEW                                     |               |     2 |    26 |       |     4   (0)| 00:00:01 |
|   5 |      UNION ALL (RECURSIVE WITH) BREADTH FIRST|               |       |       |       |         |             |
|   6 |       SORT AGGREGATE                         |               |     1 |     3 |       |         |             |
|   7 |        INDEX FULL SCAN (MIN/MAX)             | IX_TEST_A     |     1 |     3 |       |     2   (0)| 00:00:01 |
|   8 |       SORT AGGREGATE                         |               |     1 |     3 |       |         |             |
|   9 |        FIRST ROW                             |               |     1 |     3 |       |     2   (0)| 00:00:01 |
|* 10 |         INDEX RANGE SCAN (MIN/MAX)           | IX_TEST_A     |     1 |     3 |       |     2   (0)| 00:00:01 |
|* 11 |       RECURSIVE WITH PUMP                    |               |       |       |       |         |             |
|  12 |     COLLECTION ITERATOR SUBQUERY FETCH       |               |  8168 | 16336 |       |    28   (0)| 00:00:01 |
|* 13 |      COUNT STOPKEY                           |               |       |       |       |         |             |
|* 14 |       INDEX RANGE SCAN DESCENDING            | IX_XT_TEST_AB |  2308 | 64624 |       |     8   (0)| 00:00:01 |
|* 15 |    TABLE ACCESS BY USER ROWID                | XT_TEST       |   692 | 22144 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------------

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

  10 - access("T1"."A"&amp;amp;amp;amp;gt;:B1)
  11 - filter("A" IS NOT NULL)
  13 - filter(ROWNUM&amp;amp;amp;amp;lt;=5)
  14 - access("TT"."A"=:B1)
  15 - access(CHARTOROWID(VALUE(KOKBF$)))

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        166  consistent gets
          0  physical reads
          0  redo size
       7523  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
         11  SQL*Net roundtrips to/from client
         33  sorts (memory)
          0  sorts (disk)
        150  rows processed
[/sourcecode]

[collapse]

It is similarly possible through “lateral”:

code 

[sourcecode language="sql"]
alter session set events '22829 trace name context forever';
with t_unique( a ) as (
              select min(t1.a)
              from xt_test t1
              union all
              select (select min(t1.a) from xt_test t1 where t1.a&amp;amp;amp;amp;gt;t.a)
              from t_unique t
              where a is not null
)
select/*+ use_nl(rids tt) */ *
from t_unique v
    ,lateral(
              select/*+ index_desc(tt ix_xt_test_ab) */ tt.*
              from xt_test tt
              where tt.a=v.a
                and rownum&amp;amp;amp;amp;lt;=5
              order by tt.a, b desc
     ) r
order by r.a,r.b desc
[/sourcecode]

[collapse]

In general, we could do without the dangerous sorting, using “xmltable” and dbms_xmlgen instead of “table” sending a parameter directly to the internal subquery, but this is a bit harder than the regular ”table”

With xmltable() 

[sourcecode language="sql"]
with t_unique( owner ) as (
              select min(owner)
              from ttt
              union all
              select (select min(t1.owner) from ttt t1 where t1.owner&amp;amp;amp;amp;gt;t.owner)
              from t_unique t
              where owner is not null
)
select r.*
from t_unique v
    ,xmltable('/ROWSET/ROW'
              passing(
                dbms_xmlgen.getxmltype(
                  q'[select *
                     from (
                       select/*+ index_asc(tt ix_ttt) */ owner, to_char(created,'yyyy-mm-dd hh24:mi:ss') created
                       from ttt tt
                       where tt.owner=']'||v.owner||q'['
                       order by tt.created asc
                     )
                     where rownum&amp;amp;amp;amp;lt;=5
                  ]'
                )
              )
              columns
                owner   varchar2(30) path 'OWNER'
               ,created varchar2(30) path 'CREATED'
               ,x xmltype path '.'
             ) r
where
  v.owner is not null
order by r.owner,r.created asc;

-----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name                   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |                        |      1 |        |    148 |00:00:00.28 |     365 |       |       |          |
|   1 |  SORT ORDER BY                              |                        |      1 |  16336 |    148 |00:00:00.28 |     365 | 20480 | 20480 |18432  (0)|
|   2 |   NESTED LOOPS                              |                        |      1 |  16336 |    148 |00:00:00.10 |     365 |       |       |          |
|*  3 |    VIEW                                     |                        |      1 |      2 |     30 |00:00:00.01 |      66 |       |       |          |
|   4 |     UNION ALL (RECURSIVE WITH) BREADTH FIRST|                        |      1 |        |     31 |00:00:00.01 |      66 |       |       |          |
|   5 |      SORT AGGREGATE                         |                        |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
|   6 |       INDEX FULL SCAN (MIN/MAX)             | IX_TTT                 |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
|   7 |      SORT AGGREGATE                         |                        |     30 |      1 |     30 |00:00:00.01 |      63 |       |       |          |
|   8 |       FIRST ROW                             |                        |     30 |      1 |     29 |00:00:00.01 |      63 |       |       |          |
|*  9 |        INDEX RANGE SCAN (MIN/MAX)           | IX_TTT                 |     30 |      1 |     29 |00:00:00.01 |      63 |       |       |          |
|  10 |      RECURSIVE WITH PUMP                    |                        |     31 |        |     30 |00:00:00.01 |       0 |       |       |          |
|  11 |    COLLECTION ITERATOR PICKLER FETCH        | XMLSEQUENCEFROMXMLTYPE |     30 |   8168 |    148 |00:00:00.10 |     299 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------------------------

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

   3 - filter("V"."OWNER" IS NOT NULL)
   9 - access("T1"."OWNER"&amp;amp;amp;amp;gt;:B1)
[/sourcecode]

[collapse]

Update: Since Oracle 12c it would be much better to use Laterals

Link to the online unwrapper in the official Oracle documentation

Posted on by Posted in curious, documentation, oracle 5,886 Page views 3 Comments

Of course, everyone knows about “unwrappers” and I guess many of you even tried to make an unwrapper of your own (I also tried to make one after Pete Finnigan’s presentation:). But nevertheless its funny that Oracle in its documentation for 11.2 “loudly” states that unwrap does not protect anything from viewing, and even gives a direct link to the online unwrapper 🙂

11.2 PL/SQL Source Text Wrapping:

Note:

Wrapping text does not prevent anyone from displaying it with a utility such as:
http://www.codecheck.info/UnwrapIt/

For high-assurance security, use Oracle Database Vault, described in Oracle Database Vault Administrator’s Guide.

Here is a comparison of unwrap capabilities in documentations for different versions:

  • In 9.2 everything is safely hidden, except for literals, names of variables, tables and columns:

    String literals, number literals, and names of variables, tables, and columns remain in plain text within the wrapped file. Wrapping a procedure helps to hide the algorithm and prevent reverse-engineering, but it is not a way to hide passwords or table names that you want to be secret.

  • In 10.2 the data is hidden only from “most users”, but at least it makes reverse-engineering difficult!

    Although wrapping a compilation unit helps to hide the algorithm and makes reverse-engineering difficult, Oracle Corporation does not recommend it as a secure method for hiding passwords or table names. Obfuscating a PL/SQL unit prevents most users from examining the source code, but might not stop all attempts.

  • In 11.1 everything looks humble and boring:

    Wrapping is not a secure method for hiding passwords or table names. Wrapping a PL/SQL unit prevents most users from examining the source code, but might not stop all of them.