Yearly Archives: 2013

Little example of index creation on extended varchars

Posted on by Posted in 12c, oracle, undocumented 2,167 Page views Leave a comment 
-- it's just for fun:
SQL> alter system set "_scalar_type_lob_storage_threshold"=32000;

System altered.
SQL> create table t_varchar32000(v varchar2(32000 byte));

Table created.

SQL> insert into t_varchar32000
  2  select rpad(rownum,31999) || `x' str from dual connect by level<=1000;

1000 rows created.

SQL> commit;

Commit complete.

SQL> create index ix_t_varchar32000 on t_varchar32000(v) tablespace users;
create index ix_t_varchar32000 on t_varchar32000(v) tablespace users
                                  *
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded


SQL> create index ix_t_varchar32000 on t_varchar32000(v) tablespace ts_32k;
create index ix_t_varchar32000 on t_varchar32000(v) tablespace ts_32k
                                  *
ERROR at line 1:
ORA-01450: maximum key length (26510) exceeded

-- tablespace for big varchars:
SQL> alter system set DB_32K_CACHE_SIZE=100M;

System altered.

SQL> CREATE TABLESPACE TS_32K DATAFILE '/u01/app/oracle/oradata/xtsql/pdb1/ts_32k_1.dbf' SIZE 150M
  2   EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
  3   BLOCKSIZE 32K;

Tablespace created.

SQL> create table t_varchar16000(v varchar2(16000 byte)) tablespace ts_32k;

Table created.

SQL> insert into t_varchar16000
  2  select rpad(rownum,15999,'x' ) || 'y' from dual connect by level<=1000;

1000 rows created.

SQL> create index ix_t_varchar16000 on t_varchar16000(v) tablespace ts_32k;

Index created.
Statistics

[sourcecode language=”sql”]
SQL> begin
2 dbms_stats.gather_table_stats(
3 ownname => user
4 ,tabname => ‘T_VARCHAR16000’
5 ,method_opt => ‘for all columns size auto’
6 ,cascade => true
7 );
8 end;
9 /

PL/SQL procedure successfully completed.

SQL> @stats/tab t_varchar16000

OWNER TABLE_NAME PARTITION_NAME # ST_LOCK STALE_STA GLOBAL_ST USER_STAT NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_ROW_LEN AVG_SPACE LAST_ANALYZED
————— —————————— ——————– —- ——- ——— ——— ——— ———- ———- ———— ———– ———- —————
XTENDER T_VARCHAR16000 NO YES NO 1000 3016 0 16001 0 14-NOV-13

OWNER INDEX_NAME NUM_ROWS DISTINCT_KEYS BLEVEL LEAF_BLOCKS CL_FACTOR LAST_ANALYZED GLOBAL_ST USER_STAT
————— —————————— ———- ————- ———- ———– ———- ————— ——— ———
XTENDER IX_T_VARCHAR16000 1000 1000 1 1000 1000 14-NOV-13 YES NO

———————————————————————————————————————————————————————————————
| column_name | num_distinct| low_value | high_value | num_nulls | num_bucket| last_analyzed | sample_size| globa| user_| avg_c| histogram |
———————————————————————————————————————————————————————————————
| V | 1000 | 1000xxxxxxxxxxxxxx| 9xxxxxxxxxxxxxxxxx| 0 | 1 | 2013-11-14 21:11 | 1000 | YES | NO | 16001| NONE |
———————————————————————————————————————————————————————————————

[/sourcecode]

[collapse]

Just another SQL beautifier

Posted on by Posted in oracle, SQL*Plus, SQL*PLus tips 3,263 Page views 6 Comments

Previously i wrote beautifier in perl, but it was not so good, so i decided to write it in java using popular BlancoSQLFormatter library.
So you can download it now: https://orasql.org/scripts/SQLBeautifier.jar
Usage:

java -jar SQLBeautifier.jar your_file.sql

or

echo select * from dual | java -jar SQLBeautifier.jar

You certainly can conveniently use it within sql*plus with script like that:

set timing off head off termout off
col qtext format a150
prompt ################################  Original query text:  ################################################;
#spool &_SPOOLS/to_format.sql
spool to_format.sql
select
    coalesce(
        (select sql_fulltext from v$sqlarea a where a.sql_id='&1')
    ,   (select sql_text from dba_hist_sqltext a where a.sql_id='&1' and dbid=(select dbid from v$database))
    ) qtext
from dual
;
spool off

prompt ################################  Formatted query text #################################################;
#host perl inc/sql_format_standalone.pl &_SPOOLS/to_format.sql
host java -jar SQLBeautifier.jar to_format.sql
prompt ################################  Formatted query text End #############################################;
set termout on head on

Example:
beautifier2

Links:

Patch for “Bug 16516751 : Suboptimal execution plan for query with join and in-list using composite index” is available now

Posted on by Posted in 12c, bug, CBO, oracle, query optimizing 2,591 Page views 2 Comments

Bug about which i wrote previously is fixed now in 12.2, and patch 16516751 is available now for 11.2.0.3 Solaris64.
Changes:
1. CBO can consider filters in such cases now
2. Hint NUM_INDEX_KEYS fixed and works fine

UPD: Very interesting solution by Igor Usoltsev(in russian):
Ignored hint USE_CONCAT(OR_PREDICATES(N)) allows to avoid inlist iterator.
Example:

select--+ USE_CONCAT(OR_PREDICATES(32767))
 * from xt1,xt2
where
     xt1.b=10
 and xt1.a=xt2.a
 and xt2.b in (1,2)
/

Plan hash value: 2884586137          -- good plan:
 
----------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |       |       |   401 (100)|          |
|   1 |  NESTED LOOPS                 |        |       |       |            |          |
|   2 |   NESTED LOOPS                |        |   100 | 36900 |   401   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| XT1    |   100 | 31000 |   101   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | IX_XT1 |   100 |       |     1   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN           | IX_XT2 |     1 |       |     2   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID | XT2    |     1 |    59 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("XT1"."B"=10)
   5 - access("XT1"."A"="XT2"."A")
       filter(("XT2"."B"=1 OR "XT2"."B"=2))

From 10053 trace on nonpatched 11.2.0.3:
inlist_concat_diff_10053

Oracle 12c: behavior tests of the Inline functions, “Identities” and “defaults”

Posted on by Posted in 12c, curious, undocumented 2,933 Page views 4 Comments

I have done several minitests:
1. SQL and PL/SQL engines: which functions will be executed if there are two functions with same name as in SQL, as in PL/SQL (like “USER”, LPAD/RPAD, etc..)
– PL/SQL.

PL/SQL

[sourcecode language=”sql”]
SQL> @trace_on
Enter value for trace_identifier: inline
Enter value for level: 12
Tracing was enabled:

TRACEFILE_NAME
—————————————————————————-
/u01/app/oracle/diag/rdbms/xtsql/xtsql/trace/xtsql_ora_21599_inline.trc

SQL> with
2 function inline_user return varchar2 is
3 begin
4 return user;
5 end;
6 select
7 inline_user
8 from dual
9 /

INLINE_USER
——————————
XTENDER

1 row selected.

SQL> @trace_off
— unlike SQL’s "USER", PL/SQL function SYS.STANDARD.USER recursively executes "select user from sys.dual":
SQL> !grep USER /u01/app/oracle/diag/rdbms/xtsql/xtsql/trace/xtsql_ora_21599_inline.trc
SELECT USER FROM SYS.DUAL

SQL>
[/sourcecode]

[collapse]

2. Will there be any context switches if we call the inline functions which contain another pl/sql functions/procedures?
– Yes

Test 1

[sourcecode language=”sql”]
SQL> sho parameter max_string

NAME TYPE VALUE
———————————— ———— ——————————
max_string_size string STANDARD

SQL> @trace_pl_on

Session altered.

SQL> with
2 function blabla(p_str varchar2) return varchar2 is
3 begin
4 return lpad(p_str, 5000, ‘*’);
5 end;
6 select
7 length(blabla(dummy)) lpad_plsql
8 from dual;
9 /
from dual
*
ERROR at line 8:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 5

SQL> @trace_pl_last.sql

RUNID EVENT_SEQ EVENT_COMMENT EVENT_UNIT_OWNER EVENT_UNIT
———- ———- ——————————– —————— ———–
1 1 PL/SQL Trace Tool started
1 2 Trace flags changed
1 3 PL/SQL Virtual Machine started <anonymous>
1 4 PL/SQL Internal Call <anonymous>
1 5 PL/SQL Virtual Machine stopped

[/sourcecode]

[collapse]

Test 2

[sourcecode language=”sql”]
SQL> @trace_pl_on

Session altered.

SQL> create or replace function f_standalone(p varchar2) return varchar2 is
2 begin
3 return lpad(‘x’,3)||p;
4 end;
5 /

Function created.

SQL> with
2 function blabla(p_str varchar2) return varchar2 is
3 s varchar2(32767);
4 begin
5 s:= lpad(p_str, 100, ‘1’);
6 s:= s||s;
7 s:= s||lpad(p_str, 100, ‘3’);
8 s:= s||s;
9 s:= s||(1+10);
10 s:= f_standalone(s);
11 s:= f_standalone(s);
12 s:= f_standalone(s);
13 return s;
14 end;
15 select
16 length(blabla(dummy)) lpad_plsql
17 from dual
18 /

LPAD_PLSQL
———-
611

SQL> @trace_pl_last.sql

RUNID EVENT_SEQ EVENT_COMMENT EVENT_UNIT_OWNER EVENT_UNIT
———- ———- ——————————– —————– ————
2 1 PL/SQL Trace Tool started
2 2 Trace flags changed
2 3 PL/SQL Virtual Machine started <anonymous>
2 4 PL/SQL Internal Call <anonymous>
2 5 PL/SQL Virtual Machine stopped
2 6 PL/SQL Virtual Machine started <anonymous>
2 7 PL/SQL Virtual Machine started <anonymous>
2 8 PL/SQL Internal Call <anonymous>
2 9 PL/SQL Virtual Machine stopped
2 10 PL/SQL Virtual Machine stopped
2 11 PL/SQL Virtual Machine started <anonymous>
2 12 PL/SQL Virtual Machine started <anonymous>
2 13 PL/SQL Internal Call <anonymous>
2 14 PL/SQL Virtual Machine stopped
2 15 PL/SQL Virtual Machine stopped
2 16 PL/SQL Virtual Machine started <anonymous>
2 17 PL/SQL Internal Call <anonymous>
2 18 PL/SQL Internal Call <anonymous>
2 19 Procedure Call <anonymous>
2 20 PL/SQL Internal Call XTENDER F_STANDALONE
2 21 Return from procedure call XTENDER F_STANDALONE
2 22 Procedure Call <anonymous>
2 23 PL/SQL Internal Call XTENDER F_STANDALONE
2 24 Return from procedure call XTENDER F_STANDALONE
2 25 Procedure Call <anonymous>
2 26 PL/SQL Internal Call XTENDER F_STANDALONE
2 27 Return from procedure call XTENDER F_STANDALONE
2 28 PL/SQL Virtual Machine stopped

28 rows selected.
[/sourcecode]

[collapse]

Test 3

[sourcecode language=”sql”]
SQL> @trace_pl_on

Session altered.

SQL> with
2 function blabla(p_str varchar2) return varchar2 is
3 s varchar2(32767);
4 begin
5 s:= lpad(p_str, 100, ‘1’);
6 s:= s||s;
7 s:= s||lpad(p_str, 100, ‘3’);
8 s:= s||s;
9 s:= s||(1+10);
10 return s;
11 end;
12 select
13 length(blabla(dummy)) lpad_plsql
14 from dual
15 /

LPAD_PLSQL
———-
602

1 row selected.

SQL> @trace_pl_last.sql

RUNID EVENT_SEQ EVENT_COMMENT EVENT_UNIT_OWNER EVENT_UNIT
———- ———- ——————————– —————— ————
3 1 PL/SQL Trace Tool started
3 2 Trace flags changed
3 3 PL/SQL Virtual Machine started <anonymous>
3 4 PL/SQL Internal Call <anonymous>
3 5 PL/SQL Internal Call <anonymous>
3 6 PL/SQL Virtual Machine stopped

6 rows selected.

[/sourcecode]

[collapse]

3. How IDENTITY works?
For all identity columns Oracle creates a sequence with name like “ISEQ$$_XXX”, where “XXX” is the object_id of the table. All identities we can get through DBA_TAB_IDENTITY_COLS.
All Identity sequences:

select i.*
      ,tab.owner       tab_owner
      ,tab.object_name tab_name
      ,sq.object_name  sequence_name
from sys.idnseq$ i
    ,dba_objects tab
    ,dba_objects sq
where tab.object_id=i.obj#
  and sq.object_id = i.seqobj#

And we can see usage of this sequence in plans:

[sourcecode language=”sql”]
SQL_ID fn5tjw6hu0dtn, child number 0
————————————-
insert into xt_identity (description) values(‘1’)

Plan hash value: 3838626111

————————————————————————————————–
| Id | Operation | Name | Starts | Cost | A-Rows | A-Time | Buffers |
————————————————————————————————–
| 0 | INSERT STATEMENT | | 1 | 1 | 0 |00:00:00.01 | 35 |
| 1 | LOAD TABLE CONVENTIONAL | | 1 | | 0 |00:00:00.01 | 35 |
| 2 | SEQUENCE | ISEQ$$_91720 | 1 | | 1 |00:00:00.01 | 4 |
————————————————————————————————–
[/sourcecode]

[collapse]

4. When executes “default seq.nextval”?
Test

[sourcecode language=”sql”]
SQL> create sequence xt_sq1;
SQL> create sequence xt_sq2;
SQL> create table xt_default(
2 id1 int default xt_sq1.nextval
3 , pad varchar2(30)
4 , id2 int default xt_sq2.nextval
5 );

Table created.

SQL> insert into xt_default(pad) values(‘1’);

1 row created.

SQL> select xt_sq1.currval, xt_sq2.currval from dual;

CURRVAL CURRVAL
———- ———-
1 1

SQL> insert into xt_default(pad) values(1/0);
insert into xt_default(pad) values(1/0)
*
ERROR at line 1:
ORA-01476: divisor is equal to zero

SQL> select xt_sq1.currval, xt_sq2.currval from dual;

CURRVAL CURRVAL
———- ———-
2 2
[/sourcecode]

[collapse]

Oracle 12c: Extended varchars

Posted on by Posted in 12c 3,124 Page views 9 Comments

Tim Hall perfectly describes in his excellent post how new extended datatypes are stored on Oracle 12c.
I just found interesting parameter “_scalar_type_lob_storage_threshold“ – “threshold for VARCHAR2, NVARCHAR2, and RAW storage as BLOB” – This parameter is the max size in bytes, at which these data types will be stored “inline” as simple datatypes, without creation of the lob segments.
See little example:

Controlling store extended varchars as lob

[sourcecode language=”sql” highlight=”5,15,20,28,54″]
SQL> @param_ _scalar_type_lob_storage_threshold;

NAME VALUE DEFLT TYPE DESCRIPTION
—————————————- ———— ———— ———— ————————————————————
_scalar_type_lob_storage_threshold 4000 TRUE number threshold for VARCHAR2, NVARCHAR2, and RAW storage as BLOB

SQL> select * from user_lobs;

no rows selected

SQL> create table T_4000(
2 i int generated always as identity
3 ,v1000 varchar2(1000)
4 ,v4000 varchar2(4000)
5 ,v4500 varchar2(4500)
6 );

Table created.

SQL> alter system set "_scalar_type_lob_storage_threshold"=5000;

System altered.

SQL> create table T_5000(
2 i int generated always as identity
3 ,v1000 varchar2(1000)
4 ,v4000 varchar2(4000)
5 ,v4500 varchar2(4500)
6 );

Table created.

SQL> select table_name,column_name ,data_type,data_type_mod,data_length,char_col_decl_length,char_length,char_used
2 from user_tab_columns;

TABLE_NAME COLUMN_NAM DATA_TYPE DAT DATA_LENGTH CHAR_COL_DECL_LENGTH CHAR_LENGTH C
———- ———- ———- — ———– ——————– ———– –
T_4000 V4500 VARCHAR2 4500 4500 4500 B
T_4000 V4000 VARCHAR2 4000 4000 4000 B
T_4000 V1000 VARCHAR2 1000 1000 1000 B
T_4000 I NUMBER 22 0
T_5000 V4500 VARCHAR2 4500 4500 4500 B
T_5000 V4000 VARCHAR2 4000 4000 4000 B
T_5000 V1000 VARCHAR2 1000 1000 1000 B
T_5000 I NUMBER 22 0

8 rows selected.

SQL> select table_name,column_name,chunk,retention,cache,logging,encrypt,compression,deduplication,in_row,securefile
2 from user_lobs;

TABLE_NAME COLUMN_NAM CHUNK RETENTION CACHE LOGGING ENCR COMPRE DEDUPLICATION IN_ SEC
———- ———- ———- ———- ———- ——- —- —— ————— — —
T_4000 V4500 8192 YES YES NO NO NO YES YES

[/sourcecode]

[collapse]

Note that there are no lobs for table t_5000!

Oracle 12c: Lateral, row_limiting_clause

Posted on by Posted in 12c, CBO, query optimizing 3,392 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]

Oracle 12c: Inconsistency of Inline “with” functions

Posted on by Posted in 12c, oracle, PL/SQL 2,647 Page views 2 Comments

I was hoping that if inline “with” functions are in the query, so their results will be consistent with it (as operators), but unfortunately such functions returns also inconsistent results as standalone pl/sql functions. 

SQL> create table t as select 1 a from dual;
Table created.

SQL> declare
  2    j binary_integer;
  3  begin
  4    dbms_job.submit( j
  5                    ,'begin
  6                        for i in 1..10 loop
  7                          dbms_lock.sleep(1);
  8                          update t set a=a+1;
  9                          commit;
 10                        end loop;
 11                      end;'
 12                   );
 13    commit;
 14  end;
 15  /

PL/SQL procedure successfully completed.

SQL> with
  2     function f return int is
  3       res int;
  4     begin
  5       dbms_lock.sleep(1);
  6       select a into res from t;
  7       return res;
  8     end;
  9  select
 10     f
 11  from dual
 12  connect by level<=10;
 13  /

         F
----------
         1
         1
         1
         2
         3
         4
         5
         6
         7
         8

10 rows selected.

Interesting: Jonathan Lewis wrote that inline “deterministic” functions doesn’t use caching mechanism as standalone deterministic functions.

Too many function executions in simple query

Posted on by Posted in CBO, oracle, PL/SQL optimization, undocumented 2,923 Page views Leave a comment

Suppose we have a table with 10 rows:

SQL> select id from t10;

        ID
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.

And we have the query:

select *
from (
     select xf(t10.id) a
     from t10 
     )
where a*a >= 25

At first it may seem that the function should be executed as many times as rows in a table T10, i.e. 10 times.
Lets test it:

SQL> create or replace function xf(p int) return int as
  2  begin
  3    dbms_output.put_line('F fired!');
  4    return p;
  5  end;
  6  /

Function created.

SQL> set serverout on;
SQL> select *
  2  from (
  3       select xf(t10.id) a
  4       from t10
  5       )
  6  where a*a >= 25
  7  /

         A
----------
         5
         6
         7
         8
         9
        10

6 rows selected.

F fired!
F fired!
F fired!
F fired!
F fired! -- 5
F fired!
F fired!
F fired!
F fired!
F fired! -- 10
F fired!
F fired!
F fired!
F fired!
F fired! -- 15
F fired!
F fired!
F fired!
F fired!
F fired! -- 20
F fired!
F fired!
F fired!
F fired!
F fired! -- 25
F fired!

As you see, there are more than 10 executions, so lets see the execution plan:

SQL> @xplan +projection

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Plan hash value: 2919944937

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

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

   1 - filter("XF"("T10"."ID")*"XF"("T10"."ID")>=25)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "T10"."ID"[NUMBER,22]

Now you see that inner view was merged, and the function was executed 20 times in the filter and 6 times on the fetch after filtering(6 rows – 6 calls).
I see that often in such cases “no_merge” hint is suggested, but let’s test it:

SQL> select *
  2  from (
  3       select/*+ no_merge */ xf(t10.id) a
  4       from t10
  5       )
  6  where a*a >= 25
  7  /

         A
----------
         5
         6
         7
         8
         9
        10

6 rows selected.

F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!

As you can see, the number of function calls wasn’t changed.
And if we look into the plan, we understood why:

SQL> @xplan +projection

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 2027387203

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    13 |     3   (0)| 00:00:01 |
|   1 |  VIEW              |      |     1 |    13 |     3   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T10  |     1 |     3 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("XF"("T10"."ID")*"XF"("T10"."ID")>=25)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "A"[NUMBER,22]
   2 - "T10"."ID"[NUMBER,22]

20 rows selected.

Now you see, that main problem is the “filter pushdown” transformation. Previously, if we were to disable the “filter pushdown” operation, we had to use a variety of tricks, such as “materialize” hint or adding the “rownum” in expession, etc. But all these solutions require rewriting the query.
But from 11.2.0.3 we can use “_optimizer_filter_pushdown” parameter, for example:

SQL> begin
  2    dbms_sqltune.import_sql_profile(
  3      sql_text    => 'select * from (select xf(t10.id) a from t10) where a*a >= 25'
  4     ,profile     => sys.sqlprof_attr(
  5                             q'[NO_MERGE(@SEL$2)]'
  6                            ,q'[OPT_PARAM('_OPTIMIZER_FILTER_PUSHDOWN' 'FALSE')]'
  7                          )
  8     ,category    => 'DEFAULT'
  9     ,name        => 'TEST_PROFILE'
 10     ,force_match => true
 11     ,replace     => true
 12    );
 13  end;
 14  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.16
SQL> set serverout on
SQL> select * from (select xf(t10.id) a from t10) where a*a >= 25;

         A
----------
         5
         6
         7
         8
         9
        10

6 rows selected.

F fired!
F fired!
F fired!
F fired!
F fired! -- 5
F fired!
F fired!
F fired!
F fired!
F fired! -- 10
F fired! -- extra execution because of sql*plus
Elapsed: 00:00:00.17
-- there are no extra calls when we fetches by 100 rows in pl/sql:
SQL> exec for r in (select * from (select xf(t10.id) a from t10) where a*a >= 25) loop null; end loop;
F fired!
F fired!
F fired!
F fired!
F fired! -- 5
F fired!
F fired!
F fired!
F fired!
F fired! -- 10

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.22

With changing arraysize

[sourcecode language=”sql”]
— with fetching by 1 row:
SQL> declare
2 cursor c is select * from (select xf(t10.id) a from t10) where a*a >= 25;
3 n number;
4 begin
5 open c;
6 loop
7 fetch c into n;
8 exit when c%notfound;
9 end loop;
10 end;
11 /
F fired!
F fired!
F fired!
F fired!
F fired! — 5
F fired!
F fired!
F fired!
F fired!
F fired! — 10
F fired!
F fired!
F fired!
F fired!
F fired! — 15
F fired!
F fired!
F fired!
F fired!
F fired! — 20
F fired!
F fired! — 22

PL/SQL procedure successfully completed.

— with arraysize = 3
SQL> set arraysi 3
SQL> select * from (select xf(t10.id) a from t10) where a*a >= 25;

A
———-
5
6
7
8
9
10

6 rows selected.

F fired!
F fired!
F fired!
F fired!
F fired! — 5
F fired!
F fired!
F fired!
F fired!
F fired! — 10
F fired!
F fired!
F fired!
F fired! — 14
Elapsed: 00:00:00.45
SQL> set arraysi 2
SQL> select * from (select xf(t10.id) a from t10) where a*a >= 25;

A
———-
5
6
7
8
9
10

6 rows selected.

F fired!
F fired!
F fired!
F fired!
F fired! — 5
F fired!
F fired!
F fired!
F fired!
F fired! — 10
F fired!
F fired!
F fired!
F fired!
F fired!
F fired! — 16
Elapsed: 00:00:00.72
[/sourcecode]

[collapse]

PS. I found that about “_optimizer_filter_pushdown” parameter already wrote Randolf Geist

A couple of well-known but often forgotten things for PL/SQL developers

Posted on by Posted in oracle, PL/SQL, troubleshooting 3,606 Page views 2 Comments

1. Don’t forget always add NO_DATA_FOUND exception handling, when you doing “select into” in code which can be called as from PL/SQL, as from SQL.

A little example:
Suppose we need to create a function, which would call some procedure:

create or replace procedure p_nested as
  a int;
begin
  select 1 into a from dual where 1=0;
end;
/
create or replace function f_no_data_found return varchar2 as
begin
  p_nested;
  return 'ok';
end;
/

When we call this function in PL/SQL, it will raise NO_DATA_FOUND and we will see it:

SQL> exec dbms_output.put_line(f_no_data_found);
BEGIN dbms_output.put_line(f_no_data_found); END;

*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "XTENDER.P_NESTED", line 4
ORA-06512: at "XTENDER.F_NO_DATA_FOUND", line 3
ORA-06512: at line 1

But it doesn’t when we call it in SQL, because it’s normal for SQL: it’s just like a result of scalar subquery that returns nothing – NULL:

SQL> set null "NUL"
SQL> col ndf format a10
SQL> select f_no_data_found ndf from dual;

NDF
----------
NUL

1 row selected.

So if you want the function to behave the same way in PL/SQL and SQL, just add exception handling with reraising another exception or just return null.
It must be at the level of reflexes – “select into” → “exception when no_data_found”
Otherwise, later, when code become a big and difficult, you can get unstable hidden error.

2. Exceptions raised in a declaration section or in default parameters assigning will never be handled in exception section of the same level

Let’s take a look at a very simple example:

An example of exception in default parameter assigning

[sourcecode language=”sql”]
SQL> create or replace function f_value_error return int is
2 begin
3 raise value_error;
4 return 1;
5 end;
6 /

Function created.

SQL> create or replace function f(i int:=f_value_error) return varchar2 is
2 begin
3 return ‘ok’;
4 exception when others then
5 return dbms_utility.format_error_backtrace;
6 end;
7 /

Function created.

SQL> set serverout on;
SQL> begin
2 dbms_output.put_line(‘From f: ‘||chr(10)||f);
3 dbms_output.put_line(‘****************************’);
4 exception when others then
5 dbms_output.put_line(‘****************************’);
6 dbms_output.put_line(‘From higher level:’||chr(10)||dbms_utility.format_error_backtrace);
7 dbms_output.put_line(‘****************************’);
8 end;
9 /
****************************
From higher level:
ORA-06512: at "XTENDER.F_VALUE_ERROR", line 3
ORA-06512: at line 2

****************************

PL/SQL procedure successfully completed.
[/sourcecode]

[collapse]

As you can see, there are two problems:
1. an exception was handled at higher level
2. the error backtrace does not show the call of the function “F”.

If the exception was caused in the declaration, we would see the “correct” backtrace, but exception would be still handled at higher level only:

In the declaration

[sourcecode language=”sql” highlight=”25″]
SQL> create or replace function f(i int:=null) return varchar2 is
2 l_i int:=nvl(i,f_value_error);
3 begin
4 return ‘ok’;
5 exception when others then
6 return dbms_utility.format_error_backtrace;
7 end;
8 /

Function created.

SQL> set serverout on;
SQL> begin
2 dbms_output.put_line(‘From f: ‘||chr(10)||f);
3 dbms_output.put_line(‘****************************’);
4 exception when others then
5 dbms_output.put_line(‘****************************’);
6 dbms_output.put_line(‘From higher level:’||chr(10)||dbms_utility.format_error_backtrace);
7 dbms_output.put_line(‘****************************’);
8 end;
9 /
****************************
From higher level:
ORA-06512: at "XTENDER.F_VALUE_ERROR", line 3
ORA-06512: at "XTENDER.F", line 2
ORA-06512: at line 2

****************************

PL/SQL procedure successfully completed.
[/sourcecode]

[collapse]

Sometimes it’s not so dangerous, but last week I was investigating a complex case for this reason: one function when called in SQL throws strange exception, but in PL/SQL it works fine.
The exception was:

SQL> select PKG1.F(1,0,0,1275) from dual; 
select PKG1.F(1,0,0,1275) from dual 
       * 
ERROR at line 1: 
ORA-06553: PLS-801: internal error [1401]

And the function has many functions calls in default parameters initialization, so I couldn’t even find out which one contains a root problem.