Hi Tom,
Here is what i did trying to understand the enhancements of 12c. Here i was trying to understand the enhancements of WITH clause.
I have created the table and compiled the below function.
CREATE TABLE lnd_numbers AS
SELECT LEVEL * power(LEVEL,2) val FROM dual CONNECT BY LEVEL < 101;
CREATE OR REPLACE FUNCTION lnd_print_size(n IN INTEGER) RETURN VARCHAR2 IS
k CONSTANT NUMBER NOT NULL := 1024;
m CONSTANT NUMBER NOT NULL := k * k;
g CONSTANT NUMBER NOT NULL := m * k;
t CONSTANT NUMBER NOT NULL := g * k;
BEGIN
RETURN CASE
WHEN n <= k - 1 THEN
to_char(n, '999999') || 'byte'
WHEN n / k <= k - 1 THEN
to_char(n / k, '999999') || 'K'
WHEN n / m <= k - 1 THEN
to_char(n / m, '999999') || 'M'
WHEN n / g <= k - 1 THEN
to_char(n / g, '999999') || 'G'
ELSE
to_char(n / t, '999999') || 'T'
END;
END lnd_print_size;
Then i have run this in a session.
SET ECHO ON
SET FEED ON
SET TIMING ON
SET TERM OFF
SET WRAP OFF
SET LINESIZE 200
SET AUTOTRACE ON
PROMPT "Basic Case Statement"
SELECT val
,(CASE
WHEN val <= 1023 THEN
to_char(val, '999999') || ' byte'
WHEN val / 1024 <= 1023 THEN
to_char(val / 1024, '999999') || ' K'
WHEN val / 1048576 <= 1023 THEN
to_char(val / 1048576, '999999') || ' M'
WHEN val / 1073741824 <= 1023 THEN
to_char(val / 1073741824, '999999') || ' G'
ELSE
to_char(val / 1099511627776, '999999') || ' T'
END) case_val
FROM lnd_numbers
/
PROMPT "Stored Function - Basic"
SELECT val, lnd_print_size(val) func_val FROM lnd_numbers
/
SET AUTOTRACE OFF
Output:
SQL> PROMPT "Basic Case Statement"
"Basic Case Statement"
SQL>
SQL> SELECT val
2 ,(CASE
3 WHEN val <= 1023 THEN
4 to_char(val, '999999') || ' byte'
5 WHEN val / 1024 <= 1023 THEN
6 to_char(val / 1024, '999999') || ' K'
7 WHEN val / 1048576 <= 1023 THEN
8 to_char(val / 1048576, '999999') || ' M'
9 WHEN val / 1073741824 <= 1023 THEN
10 to_char(val / 1073741824, '999999') || ' G'
11 ELSE
12 to_char(val / 1099511627776, '999999') || ' T'
13 END) case_val
14 FROM lnd_numbers
15 /
<Removed the records to shorten the output here>
100 rows selected.
Elapsed: 00:00:00.53
Execution Plan
----------------------------------------------------------
Plan hash value: 1699660660
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 500 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| LND_NUMBERS | 100 | 500 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
15 recursive calls
2 db block gets
50 consistent gets
3 physical reads
0 redo size
2623 bytes sent via SQL*Net to client
439 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
100 rows processed
SQL>
SQL> PROMPT "Stored Function - Basic"
"Stored Function - Basic"
SQL>
SQL> SELECT val, lnd_print_size(val) func_val FROM lnd_numbers
2 /
<Removed the records to shorten the output here>
100 rows selected.
Elapsed: 00:00:00.50
Execution Plan
----------------------------------------------------------
Plan hash value: 1699660660
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 500 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| LND_NUMBERS | 100 | 500 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
133 recursive calls
2 db block gets
101 consistent gets
0 physical reads
0 redo size
2525 bytes sent via SQL*Net to client
439 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
100 rows processed
SQL>
Short Question:
1. In general, does the context switch account for the recursive calls?
2. In this example, is that what happened?
Sorry - I dont see any "WITH" clause in your example.
I presume you are referring to the 12c syntax of:
WITH
[my own plsql function]
select ...
We are not eliminating context switches, we are making them more efficient (in these cases where plsql functions are called from sql).
For example
SQL> create or replace
2 function F return number is
3 begin
4 return 1;
5 end;
6 /
Function created.
SQL> select sum(f)
2 from
3 ( select level from dual
4 connect by level <= 1000 ),
5 ( select level from dual
6 connect by level <= 1000 )
7 ;
SUM(F)
----------
1000000
Elapsed: 00:00:02.04
Now I'll inline it, or use the pragma UDF parameter, to see the benefits
SQL> with
2 function f1 return number is
3 begin
4 return 1;
5 end;
6 select sum(f1)
7 from
8 ( select level from dual
9 connect by level <= 1000 ),
10 ( select level from dual
11 connect by level <= 1000 )
12 /
SUM(F1)
----------
1000000
Elapsed: 00:00:00.52
SQL> create or replace
2 function F return number is
3 pragma udf;
4 begin
5 return 1;
6 end;
7 /
SQL> select sum(f)
2 from
3 ( select level from dual
4 connect by level <= 1000 ),
5 ( select level from dual
6 connect by level <= 1000 )
7 ;
SUM(F)
----------
1000000
Elapsed: 00:00:00.36