Tag Archives: oracle undocumented behaviour

ORA exceptions that can’t be caught by exception handler

Posted on by Posted in curious, Funny, oracle, PL/SQL, SQL, troubleshooting 2,338 Page views Leave a comment

I know 2 “special” exceptions that can’t be processed in exception handler:

  • “ORA-01013: user requested cancel of current operation”
  • “ORA-03113: end-of-file on communication channel”
  • and + “ORA-00028: your session has been killed” from Matthias Rogel

Tanel Poder described the first one (ORA-01013) in details here: https://tanelpoder.com/2010/02/17/how-to-cancel-a-query-running-in-another-session/ where Tanel shows that this error is based on SIGURG signal (kill -URG):

-- 1013 will not be caught:
declare
 e exception;
 pragma exception_init(e,-1013);
begin
  raise e;
exception when others then dbms_output.put_line('caught');
end;
/

declare
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
ORA-06512: at line 5
Continue reading→

Laterals: is (+) documented for laterals?

Posted on by Posted in bug, CBO, documentation, oracle 1,538 Page views Leave a comment

I know this syntax for a long time, since when lateral() was not documented yet, but recently I found a bug: the following query successfully returns 1 row:

with a as (select level a from dual connect by level<10)
    ,b as (select 0 b from dual)
    ,c as (select 0 c from dual)
select
  *
from a,
     lateral(select * from b where a.a=b.b)(+) bb
     --left outer join c on c.c=bb.b
where a=1;

         A          B
---------- ----------
         1

But doesn’t if we uncomment “left join”:

with a as (select level a from dual connect by level<10)
    ,b as (select 0 b from dual)
    ,c as (select 0 c from dual)
select
  *
from a,
     lateral(select * from b where a.a=b.b)(+) bb
     left outer join c on c.c=bb.b
where a=1;

no rows selected

And outer apply works fine:

with a as (select level a from dual connect by level<10)
    ,b as (select 0 b from dual)
    ,c as (select 0 c from dual)
select
  *
from a
     outer apply (select * from b where a.a=b.b) bb
     left outer join c on c.c=bb.b
where a=1;

         A          B          C
---------- ---------- ----------
         1

Workarounds for JPPD with view and table(kokbf$), xmltable or json_table functions

Posted on by Posted in CBO, oracle, query optimizing, SQL, troubleshooting 2,150 Page views Leave a comment

You may know that table() (kokbf$ collection functions), xmltable and json_table functions block Join-Predicate PushDown(JPPD).

Simple example:

DDL

[sourcecode language=”sql”]
create table xtest(a, b, c) as
select mod(level,1000),level,rpad(‘x’,100,’x’)
from dual
connect by level<=1e4
/
create index itest on xtest(a)
/
create or replace view vtest as
select a,count(b) cnt
from xtest
group by a
/
call dbms_stats.gather_table_stats(user,’xtest’);
/
[/sourcecode]

[collapse]

select distinct v.* 
from table(sys.odcinumberlist(1,2,3)) c, vtest v
where v.a = c.column_value;

Plan hash value: 699667151

-------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |       |     1 |    19 |    80   (4)| 00:00:01 |
|   1 |  HASH UNIQUE                            |       |     1 |    19 |    80   (4)| 00:00:01 |
|*  2 |   HASH JOIN                             |       |     1 |    19 |    79   (3)| 00:00:01 |
|   3 |    COLLECTION ITERATOR CONSTRUCTOR FETCH|       |     1 |     2 |    29   (0)| 00:00:01 |
|   4 |    VIEW                                 | VTEST |  1000 | 17000 |    49   (3)| 00:00:01 |
|   5 |     HASH GROUP BY                       |       |  1000 |  8000 |    49   (3)| 00:00:01 |
|   6 |      TABLE ACCESS FULL                  | XTEST | 10000 | 80000 |    48   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

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

   2 - access("V"."A"=VALUE(KOKBF$))

same for json_table

[sourcecode language=”sql”]
select/*+ cardinality(c 1) use_nl(v) push_pred(v) */ *
from json_table(‘{"a":[1,2,3]}’, ‘$.a[*]’ COLUMNS (a int PATH ‘$’)) c
,vtest v
where c.a = v.a;

Plan hash value: 664523328

——————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 28 | 78 (2)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 28 | 78 (2)| 00:00:01 |
| 2 | JSONTABLE EVALUATION | | | | | |
|* 3 | VIEW | VTEST | 1 | 26 | 49 (3)| 00:00:01 |
| 4 | SORT GROUP BY | | 1000 | 8000 | 49 (3)| 00:00:01 |
| 5 | TABLE ACCESS FULL | XTEST | 10000 | 80000 | 48 (0)| 00:00:01 |
——————————————————————————–

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

3 – filter("V"."A"="P"."A")

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U – Unused (1))
—————————————————————————

1 – SEL$F534CA49 / V@SEL$1
U – push_pred(v)
[/sourcecode]

[collapse]
same for xmltable

[sourcecode language=”sql”]
select/*+ leading(c v) cardinality(c 1) use_nl(v) push_pred(v) */ v.*
from xmltable(‘(1,3)’ columns a int path ‘.’) c,vtest v
where c.a = v.a(+);

Plan hash value: 564839666

————————————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————————————————
| 0 | SELECT STATEMENT | | 1 | 28 | 78 (2)| 00:00:01 |
| 1 | NESTED LOOPS OUTER | | 1 | 28 | 78 (2)| 00:00:01 |
| 2 | COLLECTION ITERATOR PICKLER FETCH| XQSEQUENCEFROMXMLTYPE | 1 | 2 | 29 (0)| 00:00:01 |
|* 3 | VIEW | VTEST | 1 | 26 | 49 (3)| 00:00:01 |
| 4 | SORT GROUP BY | | 1000 | 8000 | 49 (3)| 00:00:01 |
| 5 | TABLE ACCESS FULL | XTEST | 10000 | 80000 | 48 (0)| 00:00:01 |
————————————————————————————————————

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

3 – filter("V"."A"(+)=CAST(TO_NUMBER(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(VALUE(KOKBF$),0,0,54525952,0),
50,1,2)) AS int ))

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U – Unused (1))
—————————————————————————

1 – SEL$6722A2F6 / V@SEL$1
U – push_pred(v)
[/sourcecode]

[collapse]

And compare with this:

create global temporary table temp_collection(a number);

insert into temp_collection select * from table(sys.odcinumberlist(1,2,3));

select/*+ cardinality(c 1) no_merge(v) */
   distinct v.* 
from temp_collection c, vtest v
where v.a = c.a;

Plan hash value: 3561835411

------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                 |     1 |    26 |    41   (3)| 00:00:01 |
|   1 |  HASH UNIQUE                             |                 |     1 |    26 |    41   (3)| 00:00:01 |
|   2 |   NESTED LOOPS                           |                 |     1 |    26 |    40   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL                     | TEMP_COLLECTION |     1 |    13 |    29   (0)| 00:00:01 |
|   4 |    VIEW PUSHED PREDICATE                 | VTEST           |     1 |    13 |    11   (0)| 00:00:01 |
|*  5 |     FILTER                               |                 |       |       |            |          |
|   6 |      SORT AGGREGATE                      |                 |     1 |     8 |            |          |
|   7 |       TABLE ACCESS BY INDEX ROWID BATCHED| XTEST           |    10 |    80 |    11   (0)| 00:00:01 |
|*  8 |        INDEX RANGE SCAN                  | ITEST           |    10 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------

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

   5 - filter(COUNT(*)>0)
   8 - access("A"="C"."A")

You can see that JPPD works fine in case of global temporary tables and, obviously, the first workaround is to avoid such functions with complex views.
But in such simple queries you have 2 other simple options:
1. you can avoid JPPD and get optimal plans using CVM(complex view merge) by just simply rewriting the query using IN or EXISTS:

select * 
from vtest v
where v.a in (select/*+ cardinality(c 1) */ c.* from table(sys.odcinumberlist(1,2,3)) c);

Plan hash value: 1474391442

---------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |       |    10 |   100 |    42   (5)| 00:00:01 |
|   1 |  SORT GROUP BY NOSORT                     |       |    10 |   100 |    42   (5)| 00:00:01 |
|   2 |   NESTED LOOPS                            |       |    10 |   100 |    41   (3)| 00:00:01 |
|   3 |    NESTED LOOPS                           |       |    10 |   100 |    41   (3)| 00:00:01 |
|   4 |     SORT UNIQUE                           |       |     1 |     2 |    29   (0)| 00:00:01 |
|   5 |      COLLECTION ITERATOR CONSTRUCTOR FETCH|       |     1 |     2 |    29   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN                      | ITEST |    10 |       |     1   (0)| 00:00:01 |
|   7 |    TABLE ACCESS BY INDEX ROWID            | XTEST |    10 |    80 |    11   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

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

   6 - access("A"=VALUE(KOKBF$))

the same with json_table and xmltable

[sourcecode language=”sql”]
select *
from vtest t
where t.a in (select/*+ cardinality(v 1) */ v.a from json_table(‘{"a":[1,2,3]}’, ‘$.a[*]’ COLUMNS (a int PATH ‘$’)) v);

Plan hash value: 2910004067

—————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————————
| 0 | SELECT STATEMENT | | 10 | 100 | 42 (5)| 00:00:01 |
| 1 | SORT GROUP BY NOSORT | | 10 | 100 | 42 (5)| 00:00:01 |
| 2 | NESTED LOOPS | | 10 | 100 | 41 (3)| 00:00:01 |
| 3 | NESTED LOOPS | | 10 | 100 | 41 (3)| 00:00:01 |
| 4 | SORT UNIQUE | | | | | |
| 5 | JSONTABLE EVALUATION | | | | | |
|* 6 | INDEX RANGE SCAN | ITEST | 10 | | 1 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID| XTEST | 10 | 80 | 11 (0)| 00:00:01 |
—————————————————————————————

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

6 – access("A"="P"."A")

select v.*
from vtest v
where exists(select/*+ cardinality(c 1) */ 1 from xmltable(‘(1,3)’ columns a int path ‘.’) c where c.a = v.a);

Plan hash value: 1646016183

—————————————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————————————————
| 0 | SELECT STATEMENT | | 10 | 100 | 42 (5)| 00:00:01 |
| 1 | SORT GROUP BY NOSORT | | 10 | 100 | 42 (5)| 00:00:01 |
| 2 | NESTED LOOPS | | 10 | 100 | 41 (3)| 00:00:01 |
| 3 | NESTED LOOPS | | 10 | 100 | 41 (3)| 00:00:01 |
| 4 | SORT UNIQUE | | 1 | 2 | 29 (0)| 00:00:01 |
| 5 | COLLECTION ITERATOR PICKLER FETCH| XQSEQUENCEFROMXMLTYPE | 1 | 2 | 29 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | ITEST | 10 | | 1 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | XTEST | 10 | 80 | 11 (0)| 00:00:01 |
—————————————————————————————————————

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

6 – access("A"=CAST(TO_NUMBER(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(VALUE(KOKBF$),0,0,54525952,0),50,1,2)) AS int ))
[/sourcecode]

[collapse]

2. Avoid JPPD using lateral():

select/*+ cardinality(c 1) no_merge(lat) */
   distinct lat.* 
from table(sys.odcinumberlist(1,2,3)) c, 
     lateral(select * from vtest v where v.a = c.column_value) lat;

Plan hash value: 18036714

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                 |    10 |   190 |    41   (3)| 00:00:01 |
|   1 |  HASH UNIQUE                            |                 |    10 |   190 |    41   (3)| 00:00:01 |
|   2 |   NESTED LOOPS                          |                 |    10 |   190 |    40   (0)| 00:00:01 |
|   3 |    COLLECTION ITERATOR CONSTRUCTOR FETCH|                 |     1 |     2 |    29   (0)| 00:00:01 |
|   4 |    VIEW                                 | VW_LAT_4DB60E85 |    10 |   170 |    11   (0)| 00:00:01 |
|   5 |     SORT GROUP BY                       |                 |    10 |    80 |    11   (0)| 00:00:01 |
|   6 |      TABLE ACCESS BY INDEX ROWID BATCHED| XTEST           |    10 |    80 |    11   (0)| 00:00:01 |
|*  7 |       INDEX RANGE SCAN                  | ITEST           |    10 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

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

   7 - access("A"=VALUE(KOKBF$))

Let’s see a bit more complex query:

Test tables 2

[sourcecode language=”sql”]
create table xtest1(id primary key, a) as
select level,level from dual connect by level<=1000;

create table xtest2(a, b, c) as
select mod(level,1000),level,rpad(‘x’,100,’x’)
from dual
connect by level<=1e4
/
create index itest2 on xtest2(a)
/
create or replace view vtest2 as
select a,count(b) cnt
from xtest2
group by a
/
[/sourcecode]

[collapse]

select v.* 
from xtest1 t1,
     vtest2 v
where t1.id in (select/*+ cardinality(c 1) */ * from table(sys.odcinumberlist(1,2,3)) c)
  and v.a = t1.a;

Plan hash value: 4293766070

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |              |     1 |    36 |    80   (3)| 00:00:01 |
|*  1 |  HASH JOIN                                 |              |     1 |    36 |    80   (3)| 00:00:01 |
|   2 |   JOIN FILTER CREATE                       | :BF0000      |     1 |    10 |    31   (4)| 00:00:01 |
|   3 |    NESTED LOOPS                            |              |     1 |    10 |    31   (4)| 00:00:01 |
|   4 |     NESTED LOOPS                           |              |     1 |    10 |    31   (4)| 00:00:01 |
|   5 |      SORT UNIQUE                           |              |     1 |     2 |    29   (0)| 00:00:01 |
|   6 |       COLLECTION ITERATOR CONSTRUCTOR FETCH|              |     1 |     2 |    29   (0)| 00:00:01 |
|*  7 |      INDEX UNIQUE SCAN                     | SYS_C0026365 |     1 |       |     0   (0)| 00:00:01 |
|   8 |     TABLE ACCESS BY INDEX ROWID            | XTEST1       |     1 |     8 |     1   (0)| 00:00:01 |
|   9 |   VIEW                                     | VTEST2       |  1000 | 26000 |    49   (3)| 00:00:01 |
|  10 |    HASH GROUP BY                           |              |  1000 |  8000 |    49   (3)| 00:00:01 |
|  11 |     JOIN FILTER USE                        | :BF0000      | 10000 | 80000 |    48   (0)| 00:00:01 |
|* 12 |      TABLE ACCESS FULL                     | XTEST2       | 10000 | 80000 |    48   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

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

   1 - access("V"."A"="T1"."A")
   7 - access("T1"."ID"=VALUE(KOKBF$))
  12 - filter(SYS_OP_BLOOM_FILTER(:BF0000,"A"))

As you see, CVM can’t help in this case, but we can use lateral():

select/*+ no_merge(lat) */ lat.* 
from xtest1 t1,
     lateral(select * from vtest2 v where v.a = t1.a) lat
where t1.id in (select/*+ cardinality(c 1) */ * from table(sys.odcinumberlist(1,2,3)) c);

Plan hash value: 1798023704

------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                 |    10 |   360 |    42   (3)| 00:00:01 |
|   1 |  NESTED LOOPS                            |                 |    10 |   360 |    42   (3)| 00:00:01 |
|   2 |   NESTED LOOPS                           |                 |     1 |    10 |    31   (4)| 00:00:01 |
|   3 |    SORT UNIQUE                           |                 |     1 |     2 |    29   (0)| 00:00:01 |
|   4 |     COLLECTION ITERATOR CONSTRUCTOR FETCH|                 |     1 |     2 |    29   (0)| 00:00:01 |
|   5 |    TABLE ACCESS BY INDEX ROWID           | XTEST1          |     1 |     8 |     1   (0)| 00:00:01 |
|*  6 |     INDEX UNIQUE SCAN                    | SYS_C0026365    |     1 |       |     0   (0)| 00:00:01 |
|   7 |   VIEW                                   | VW_LAT_A18161FF |    10 |   260 |    11   (0)| 00:00:01 |
|   8 |    SORT GROUP BY                         |                 |    10 |    80 |    11   (0)| 00:00:01 |
|   9 |     TABLE ACCESS BY INDEX ROWID BATCHED  | XTEST2          |    10 |    80 |    11   (0)| 00:00:01 |
|* 10 |      INDEX RANGE SCAN                    | ITEST2          |    10 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------

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

   6 - access("T1"."ID"=VALUE(KOKBF$))
  10 - access("A"="T1"."A")

There is also another workaround with non-documented “precompute_subquery” hint:

select v.* 
from xtest1 t1,
     vtest2 v 
where t1.id in (select/*+ precompute_subquery */ * from table(sys.odcinumberlist(1,2,3)) c)
and v.a = t1.a;

Plan hash value: 1964829099

------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |              |    30 |   480 |    37   (3)| 00:00:01 |
|   1 |  HASH GROUP BY                  |              |    30 |   480 |    37   (3)| 00:00:01 |
|   2 |   NESTED LOOPS                  |              |    30 |   480 |    36   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                 |              |    30 |   480 |    36   (0)| 00:00:01 |
|   4 |     INLIST ITERATOR             |              |       |       |            |          |
|   5 |      TABLE ACCESS BY INDEX ROWID| XTEST1       |     3 |    24 |     3   (0)| 00:00:01 |
|*  6 |       INDEX UNIQUE SCAN         | SYS_C0026365 |     3 |       |     2   (0)| 00:00:01 |
|*  7 |     INDEX RANGE SCAN            | ITEST2       |    10 |       |     1   (0)| 00:00:01 |
|   8 |    TABLE ACCESS BY INDEX ROWID  | XTEST2       |    10 |    80 |    11   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

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

   6 - access("T1"."ID"=1 OR "T1"."ID"=2 OR "T1"."ID"=3)
   7 - access("A"="T1"."A")

It can help even in most difficult cases, for example if you can’t rewrite query (in this case you can create sql patch or sql profile with “precompute_subquery”), but I wouldn’t suggest it since “precompute_subquery” is non-documented, it can be used only with simple collections and has limitation in 1000 values.
I’d suggest to use the workaround with lateral, since it’s most reliable and very simple.

Create External Table as Select

Posted on by Posted in curious, oracle, SmartScan 2,014 Page views Leave a comment

I was looking through a test script and saw something I didn’t know you could do in Oracle. I mentioned it to an Oracle ACE and he didn’t know it either. I then said to the External Table engineers “Oh I see you’ve added this cool new feature” and he replied dryly – “Yes, we added it in Oracle 10.1”. Ouch! So just in case you also didn’t know, you can create an External Table using a CTAS and the ORACLE_DATAPUMP driver.

This feature only work with the ORACLE_DATAPUMP access driver (it does NOT work with with the LOADER, HIVE, or HDFS drivers) and we can use it like this:

SQL> create table cet_test organization external
  2  (
  3    type ORACLE_DATAPUMP
  4    default directory T_WORK
  5    location ('xt_test01.dmp','xt_test02.dmp')
  6  ) parallel 2
  7  as select * from lineitem
 
Table created.

Checking the results shows us

-rw-rw---- ... 786554880 Mar 9 10:48 xt_test01.dmp 
-rw-rw---- ... 760041472 Mar 9 10:48 xt_test02.dmp

This can be a great way of creating a (redacted) sample of data to give to a developer to test or for a bug repro to give to support or to move between systems. 

Bug with integer literals in PL/SQL

Posted on by Posted in curious, oracle, PL/SQL, PL/SQL optimization, undocumented 1,784 Page views Leave a comment

This interesting question was posted on our russian forum yesterday:

We have a huge PL/SQL package and this simple function returns wrong result when it’s located at the end of package body:

create or replace package body PKGXXX as
  ...
  function ffff return number is
  nRes number;
  begin        
    nRes :=  268435456;
    return nRes;
  end;
end;
/

But it works fine in any of the following cases:
* replace 268435456 with power(2, 28), or
* replace 268435456 with small literal like 268, or
* move this function to the beginning of package body

The one of the interesting findings was that the returned value is equal to the one of literals in another function.
We can reproduce this bug even with an anonymous pl/sql block. The following test case uses 32768 integer literals from 1000001 to 1032768 and prints 5 other integers:

declare n number;
begin
  n:=1000001; -- this part
  n:=1000002; -- creates
  n:=1000003; -- 32768 
   ...        -- integer
  n:=1032768; -- literals
    dbms_output.put_line('100000='||100000); -- it should print: 100000=100000
    dbms_output.put_line('32766 ='||32766);
    dbms_output.put_line('32767 ='||32767);    
    dbms_output.put_line('32768 ='||32768);
    dbms_output.put_line('32769 ='||32769);
end;

Test code

[sourcecode language=”sql”]
declare
c clob:=’declare n number;begin’||chr(10);
f varchar2(100):=’n:=%s;’||chr(10);
v varchar2(32767);
n number:=32768;
begin
for i in 1..n loop
v:=v||utl_lms.format_message(f,to_char(1e7+i));
if length(v)>30000 then
c:=c||v;
v:=”;
end if;
end loop;
v:=v||q'[
dbms_output.put_line(‘100000=’||100000);
dbms_output.put_line(‘32766 =’||32766);
dbms_output.put_line(‘32767 =’||32767);
dbms_output.put_line(‘32768 =’||32768);
dbms_output.put_line(‘32769 =’||32769);
end;
]’;
c:=c||v;
execute immediate c;
end;
/
[/sourcecode]

[collapse]
It produces the following output:

100000=10000001
32766 =32766
32767 =32767
32768 =10000002
32769 =10000003

This test case well demonstrates wrong results:
* instead of 100000 we get 10000001, which is the value from first line after “begin”, ie 1st integer literal in the code,
* for 32766 and 32767 oracle returns right values
* instead of 32768 (==32767+1) it returns 10000002, which is the integer from 2nd line, ie 2nd integer literal in the code,
* instead of 32769 (==32767+2) it returns 10000003, which is the integer from 3rd line, ie 3rd integer literal in the code
After several tests I can make a conclusion:

  • It doesn’t matter what plsql_optimize_level or plsql_code_type you set, was debug enabled or not, the behaviour is the same.
  • It seems that this is a kind of PL/SQL optimization: during parsing, oracle leaves integer literal in place if its value is in range -32768..32767 (16bit signed int), but if its value is out of this range, oracle adds this value into array of integers’ constants and replaces the value with the index of this element in this array. But because of index value overflow in cases when a count of such integer literals becomes larger than 32768, instead of Nth element of this array, oracle returns Mth element, where M is mod(N,32767).

So we can describe this behaviour using first test case:

declare n number;
begin
  n:=1000001; -- this part
  n:=1000002; -- creates
  n:=1000003; -- 32768 
   ...        -- integer
  n:=1032768; -- literals
    dbms_output.put_line('100000='||100000); -- it should print 100000, ie 32768th element of array, but prints 10000001
                                             -- where 10000001 is the 1st element of array (1==mod(32768,32767))
    dbms_output.put_line('32766 ='||32766);  -- these 2 lines print right values,
    dbms_output.put_line('32767 ='||32767);  -- because their values are in the range of -32768..32767
    dbms_output.put_line('32768 ='||32768);  -- this line contains 32769th element and prints 2nd element of array (2==mod(32769,32767))
    dbms_output.put_line('32769 ='||32769);  -- this line contains 32770th element and prints 3nd element of array (3==mod(32770,32767))
end;

The following query can help you to find objects which can potentially have this problem:

select
  s.owner,s.name,s.type
 ,sum(regexp_count(text,'(\W|^)3\d{4,}([^.0-9]|$)')) nums_count -- this regexp counts integer literals >= 30000
from dba_source s 
where 
    owner='&owner'
and type in ('FUNCTION','PROCEDURE','PACKAGE','PACKAGE BODY')
group by s.owner,s.name,s.type
having sum(regexp_count(text,'(\W|^)3\d{4,}([^.0-9]|$)'))>32767 -- filter only objects which have >=32767 integer literal

Workaround:
You may noticed that I wrote about INTEGER literals only, so the easiest workaround is to make them FLOAT – just add “.” to the end of each literal:

declare n number;
begin
  n:=1000001.;
  n:=1000002.;
  n:=1000003.;
   ...       
  n:=1032768.;
    dbms_output.put_line('100000='||100000.);
    dbms_output.put_line('32766 ='||32766.);
    dbms_output.put_line('32767 ='||32767.);    
    dbms_output.put_line('32768 ='||32768.);
    dbms_output.put_line('32769 ='||32769.);
end;

Fixed test cases

[sourcecode language=”sql”]
declare
c clob:=’declare n number;begin’||chr(10);
f varchar2(100):=’n:=%s.;’||chr(10); — I’ve added here "."
v varchar2(32767);
n number:=32768;
begin
for i in 1..n loop
v:=v||utl_lms.format_message(f,to_char(1e7+i));
if length(v)>30000 then
c:=c||v;
v:=”;
end if;
end loop;
v:=v||q'[
dbms_output.put_line(‘100000=’||100000.); — .
dbms_output.put_line(‘32766 =’||32766.);
dbms_output.put_line(‘32767 =’||32767.);
dbms_output.put_line(‘32768 =’||32768.);
dbms_output.put_line(‘32769 =’||32769.);
end;
]’;
c:=c||v;
execute immediate c;
end;
/
[/sourcecode]

[collapse]

Little quiz: Ordering/Grouping – Guess the output

Posted on by Posted in curious, oracle, SQL 1,795 Page views Leave a comment

How many times have you guessed the right answer? 🙂

1

[sourcecode language=”SQL”]
select * from dual order by -1;
select * from dual order by 0;
[/sourcecode]

[collapse]
2

[sourcecode language=”SQL”]
select * from dual order by -(0.1+0/1) desc;
select 1 n,0 n,2 n,0 n,1 n from dual group by grouping sets(1,2,3,2,1,0) order by -(0.1+0/1) desc;
[/sourcecode]

[collapse]
3

[sourcecode language=”SQL”]
select 1 n,0 n,2 n,0 n,1 n from dual group by grouping sets(1,2,3,2,1,0) order by 0;
select 1 n,0 n,2 n,0 n,1 n from dual group by grouping sets(1,2,3,2,1,0) order by 0+0;
select 1 n,0 n,2 n,0 n,1 n from dual group by grouping sets(1,2,3,2,1,0) order by 3+7 desc;
select 1 n,0 n,2 n,0 n,1 n from dual group by grouping sets(1,2,3,2,1,0) order by -(3.1+0f) desc;
[/sourcecode]

[collapse]
4

[sourcecode language=”SQL”]
select column_value x,10-column_value y from table(ku$_objnumset(5,4,3,1,2,3,4)) order by 1.9;
select column_value x,10-column_value y from table(ku$_objnumset(5,4,3,1,2,3,4)) order by 2.5;
select column_value x,10-column_value y from table(ku$_objnumset(5,4,3,1,2,3,4)) order by 2.7 desc;
select column_value x,10-column_value y from table(ku$_objnumset(5,4,3,1,2,3,4)) order by -2.7 desc;
[/sourcecode]

[collapse]

PRECOMPUTE_SUBQUERY hint

Posted on by Posted in CBO, hints, undocumented 2,709 Page views 3 Comments

I’ve just found out that we can specify query block for PRECOMPUTE_SUBQUERY: /*+ precompute_subquery(@sel$2) */
So we can use it now with SQL profiles, SPM baselines and patches.

SQL> select/*+ precompute_subquery(@sel$2) */ * from dual where dummy in (select chr(level) from dual connect by level<=100);

D
-
X

SQL> @last

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  c437vsqj7c4jy, child number 0
-------------------------------------
select/*+ precompute_subquery(@sel$2) */ * from dual where dummy in
(select chr(level) from dual connect by level<=100)

Plan hash value: 272002086

---------------------------------------------------------------------------
| Id  | Operation         | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |        |       |     2 (100)|          |
|*  1 |  TABLE ACCESS FULL| DUAL |      1 |     2 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / DUAL@SEL$1

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

   1 - filter(("DUMMY"='' OR "DUMMY"='' OR "DUMMY"='♥' OR "DUMMY"='♦'
              OR "DUMMY"='♣' OR "DUMMY"='♠' OR "DUMMY"='' OR "DUMMY"=' OR
              "DUMMY"=' ' OR "DUMMY"=' ' OR "DUMMY"='' OR "DUMMY"='' OR "DUMMY"=' '
              OR "DUMMY"='' OR "DUMMY"='' OR "DUMMY"='►' OR "DUMMY"='◄' OR
              "DUMMY"='' OR "DUMMY"='' OR "DUMMY"='' OR "DUMMY"='' OR "DUMMY"=''
              OR "DUMMY"='' OR "DUMMY"='↑' OR "DUMMY"='↓' OR "DUMMY"='' OR
              "DUMMY"=' OR "DUMMY"='' OR "DUMMY"='' OR "DUMMY"='' OR "DUMMY"=''
              OR "DUMMY"=' ' OR "DUMMY"='!' OR "DUMMY"='"' OR "DUMMY"='#' OR
              "DUMMY"='$' OR "DUMMY"='%' OR "DUMMY"='&' OR "DUMMY"='''' OR
              "DUMMY"='(' OR "DUMMY"=')' OR "DUMMY"='*' OR "DUMMY"='+' OR "DUMMY"=','
              OR "DUMMY"='-' OR "DUMMY"='.' OR "DUMMY"='/' OR "DUMMY"='0' OR
              "DUMMY"='1' OR "DUMMY"='2' OR "DUMMY"='3' OR "DUMMY"='4' OR "DUMMY"='5'
              OR "DUMMY"='6' OR "DUMMY"='7' OR "DUMMY"='8' OR "DUMMY"='9' OR
              "DUMMY"=':' OR "DUMMY"=';' OR "DUMMY"='<' OR "DUMMY"='=' OR "DUMMY"='>'
              OR "DUMMY"='?' OR "DUMMY"='@' OR "DUMMY"='A' OR "DUMMY"='B' OR
              "DUMMY"='C' OR "DUMMY"='D' OR "DUMMY"='E' OR "DUMMY"='F' OR "DUMMY"='G'
              OR "DUMMY"='H' OR "DUMMY"='I' OR "DUMMY"='J' OR "DUMMY"='K' OR
              "DUMMY"='L' OR "DUMMY"='M' OR "DUMMY"='N' OR "DUMMY"='O' OR "DUMMY"='P'
              OR "DUMMY"='Q' OR "DUMMY"='R' OR "DUMMY"='S' OR "DUMMY"='T' OR
              "DUMMY"='U' OR "DUMMY"='V' OR "DUMMY"='W' OR "DUMMY"='X' OR "DUMMY"='Y'
              OR "DUMMY"='Z' OR "DUMMY"='[' OR "DUMMY"='\' OR "DUMMY"=']' OR
              "DUMMY"='^' OR "DUMMY"='_' OR "DUMMY"='`' OR "DUMMY"='a' OR "DUMMY"='b'
              OR "DUMMY"='c' OR "DUMMY"='d'))

PS. I’m not sure, but as far as i remember, when I tested it on 10.2, it didn’t work with specifying a query block.
And I have never seen such usage.

Oracle 12c: Lateral, row_limiting_clause

Posted on by Posted in 12c, CBO, query optimizing 3,393 Page views 3 Comments

Previously i showed how we can optimize getting TopN rows sorted by field “B” for each distinct value “A” with undocumented “lateral” in previous versions of Oracle RDBMS.
But now it is documented!
Very simple example:

with t as (select level a from dual connect by level&amp;lt;=10)
select *
from t
    ,lateral(
             select *
             from dba_objects o
             where object_id=t.a
            )
;


Moreover, we can make now this optimization more stable and simple with row_limiting_clause:

With row_limiting_clause and multiset:

[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;gt;t.a)
from t_unique t
where a is not null
)
select/*+ use_nl(rids tt) */ *
from t_unique v
,table(
cast(
multiset(
select/*+ index_desc(tt ix_xt_test_ab) */ tt.rowid rid
from xt_test tt
where tt.a=v.a
order by tt.b desc
fetch first 5 rows only
)
as sys.odcivarchar2list
)
) rids
,xt_test tt
where tt.rowid=rids.column_value
order by tt.a,tt.b desc
[/sourcecode]

[collapse]
With row_limiting_clause and lateral:

[sourcecode language=”sql”]
with t_unique( a ) as (
select min(t1.a)
from xt_test t1
union all
select next_a
from t_unique t, lateral(select min(t1.a) next_a from xt_test t1 where t1.a&amp;gt;t.a) r
where t.a is not null
)
select/*+ use_nl(v r t) leading(v r t) */ t.*
from t_unique v
,lateral(
select/*+ index_desc(tt ix_xt_test_ab) */ rowid rid
from xt_test tt
where tt.a=v.a
order by b desc
fetch first 5 rows only
) r
,xt_test t
where r.rid=t.rowid
[/sourcecode]

[collapse]

Unfortunately, the recursive_subquery_clause with scalar subqueries sometimes doesn’t work:

Spoiler

[sourcecode language=”sql”]
SQL> 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;gt;t.a)
6 from t_unique t
7 where a is not null
8 )
9 select/*+ use_nl(v r) */ *
10 from t_unique v
11 ,lateral(
12 select/*+ index_desc(tt ix_xt_test_ab) */ tt.*
13 from xt_test tt
14 where tt.a=v.a
15 order by tt.a, b desc
16 fetch first 5 rows only
17 ) r
18 order by r.a,r.b desc;
from xt_test t1
*
ERROR at line 3:
ORA-00600: internal error code, arguments: [qctcte1], [0], [], [], [], [], [], [], [], [], [], []
[/sourcecode]

[collapse]

But I think oracle will fix it soon, because this ORA-600 can be solved easily with hint “materialize”, but it’s not so good:

Spoiler

[sourcecode language=”sql”]
SQL> 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;gt;t.a)
6 from t_unique t
7 where a is not null
8 ), v as (
9 select–+ materialize
10 *
11 from t_unique
12 )
13 select/*+ use_nl(v r) */ *
14 from v
15 ,lateral(
16 select/*+ index_desc(tt ix_xt_test_ab) */ tt.*
17 from xt_test tt
18 where tt.a=v.a
19 order by tt.a, b desc
20 fetch first 5 rows only
21 ) r
22 order by r.a,r.b desc;

150 rows selected.

Elapsed: 00:00:01.01

Statistics
———————————————————-
10 recursive calls
8 db block gets
11824 consistent gets
1 physical reads
624 redo size
4608 bytes sent via SQL*Net to client
462 bytes received via SQL*Net from client
11 SQL*Net roundtrips to/from client
64 sorts (memory)
0 sorts (disk)
150 rows processed
[/sourcecode]

[collapse]

UPDATE: There is a better solution:

Spoiler

[sourcecode language=”sql” highlight=”11″]
SQL> 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;gt;t.a)
6 from t_unique t
7 where a is not null
8 ), v as (
9 select * from t_unique
10 union all
11 select null from dual where 1=0 — &amp;lt;&amp;lt;– workaround
12 )
13 select/*+ use_nl(v r) */ *
14 from v
15 ,lateral(
16 select/*+ index_desc(tt ix_xt_test_ab) */ tt.*
17 from xt_test tt
18 where tt.a=v.a
19 order by tt.a, b desc
20 fetch first 5 rows only
21 ) r
22 order by r.a,r.b desc;
[/sourcecode]

[collapse]

And note that we can’t use now row_limiting_clause in cursor’s:

cursor(...row_limiting_clause)

[sourcecode language=”sql”]
SQL> with
2 t_unique( a ) as (
3 select min(t1.a)
4 from xt_test t1
5 union all
6 select next_a
7 from t_unique t, lateral(select min(t1.a) next_a from xt_test t1 where t1.a&amp;gt;t.a) r
8 where t.a is not null
9 )
10 select
11 cursor(
12 select *
13 from xt_test t
14 where t.a=v.a
15 order by a,b desc
16 fetch first 5 rows only
17 ) c
18 from t_unique v
19 ;
with
*
ERROR at line 1:
ORA-03001: unimplemented feature
ORA-00600: internal error code, arguments: [kokbcvb1], [], [], [], [], [], [], [], [], [], [], []
[/sourcecode]

[collapse]
And, just for fun, with inline pl/sql function(inconsistent):

[sourcecode language=”sql”]
SQL> with
2 function f(v_a int)
3 return sys.ku$_vcnt
4 as
5 res sys.ku$_vcnt;
6 begin
7 select tt.rowid as rid
8 bulk collect into res
9 from xt_test tt
10 where tt.a = v_a
11 order by a,b desc
12 fetch first 5 rows only;
13 return res;
14 end;
15
16 t_unique( a ) as (
17 select min(t1.a)
18 from xt_test t1
19 union all
20 select next_a
21 from t_unique t, lateral(select min(t1.a) next_a from xt_test t1 where t1.a&amp;gt;t.a) r
22 where t.a is not null
23 )
24 select/*+ use_nl(v r t) leading(v r t) */ t.*
25 from t_unique v
26 ,table(f(v.a)) r
27 ,xt_test t
28 where r.column_value=t.rowid;
29 /

150 rows selected.

Elapsed: 00:00:00.06

Statistics
———————————————————-
31 recursive calls
0 db block gets
173 consistent gets
0 physical reads
0 redo size
5657 bytes sent via SQL*Net to client
642 bytes received via SQL*Net from client
11 SQL*Net roundtrips to/from client
32 sorts (memory)
0 sorts (disk)
150 rows processed

[/sourcecode]

[collapse]

Deterministic function vs scalar subquery caching. Part 3

Posted on by Posted in deterministic functions, oracle, scalar subquery caching 2,795 Page views 1 Comment

In previous parts i already point out that:

  1. Both mechanisms are based on hash functions.
  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.
  4. Oracle doesn’t keep last result of deterministic functions as it does for scalar subquery caching
  5. Caching of deterministic functions results turns off after a certain number of attempts to get the value from the cache. But SSC always returns results from cache if values already cached.

Upd 2015-02-19:
A couple additions about deterministic functions:

Today’s post is just addition to previous topics:

I wrote about turning caching off after many unsuccessfull attempts to get value from cache, but i didn’t say what it is the number. In fact caching of deterministic functions also depends on two another hidden parameters:

SQL> @param_ plsql%cach

NAME                                 VALUE        DEFLT    TYPE       DESCRIPTION
------------------------------------ ------------ -------- ---------- ------------------------------------------------------------------
_plsql_cache_enable                  TRUE         TRUE     boolean    PL/SQL Function Cache Enabled
_plsql_minimum_cache_hit_percent     20           TRUE     number     plsql minimum cache hit percentage required to keep caching active

First parameter “_plsql_cache_enable” is just a parameter which enables/disables this caching mechanism.
But the second parameter – “_plsql_minimum_cache_hit_percent” – is responsible for the percentage of unsuccessful attempts which disables caching.

I will show their effects with the example from the previous post:

-- set this parameter to big value for maximizing caching:
alter session set "_query_execution_cache_max_size" = 131072;
-- log table clearing:
truncate table t_params;
-- test with percentage = 50
alter session set "_plsql_minimum_cache_hit_percent"=50;
select sum(f_deterministic(n)) fd
from
  xmltable('1 to 10000,1 to 10000'
           columns n int path '.'
          );
 
select 10000-count(count(*)) "Count of cached results"
from t_params
group by p
having count(*)>1;
/*
Count of cached results
-----------------------
                      0
*/
-- now i change cache hit percentage parameter to 0:
alter session set "_plsql_minimum_cache_hit_percent"=0;
truncate table t_params;
select sum(f_deterministic(n)) fd
from
  xmltable('1 to 10000,1 to 10000'
           columns n int path '.'
          );
 
select 10000-count(count(*)) "Count of cached results"
from t_params
group by p
having count(*)>1;
/*
Count of cached results
-----------------------
                   2039
*/

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.