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.
[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]
2. Will there be any context switches if we call the inline functions which contain another pl/sql functions/procedures?
– Yes
[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]
[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]
[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]
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]
4. When executes “default seq.nextval”?
[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]