Scalar subqueries are used to minimize the number of times a PL/SQL function is called from SQL. This is important because the overhead of going from SQL to PL/SQL over and over again can be quite expensive, and the scalar subquery caching database feature can dramatically decrease this overhead.
What exactly is a scalar subquery? It is a subquery in a SQL statement that returns exactly one column and zero rows or one row. That single column can be a complex object type, so it can consist of many attributes, but the subquery returns a single scalar value.
rajesh@ORA11GR2> create table t
2 nologging
3 as
4 select * from all_objects;
Table created.
Elapsed: 00:00:06.14
rajesh@ORA11GR2>
rajesh@ORA11GR2> exec dbms_stats.gather_table_stats(user,'T');
Elapsed: 00:00:00.76
rajesh@ORA11GR2> create or replace function f(x varchar2)
2 return number
3 as
4 begin
5 dbms_application_info.set_client_info( userenv('client_info') + 1 );
6 return (length(x));
7 end;
8 /
Function created.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.73
Statistics
----------------------------------------------------------
55 recursive calls
0 db block gets
1527 consistent gets
0 physical reads
0 redo size
808576 bytes sent via SQL*Net to client
5663 bytes received via SQL*Net from client
479 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
71598 rows processed
rajesh@ORA11GR2> set autotrace off;
rajesh@ORA11GR2>
rajesh@ORA11GR2> select userenv('client_info') ,
2 dbms_utility.get_cpu_time - :cpu
3 from dual;
USERENV('CLIENT_INFO') DBMS_UTILITY.GET_CPU_TIME-:CPU
---------------------------- ------------------------------
30 44
What exactly is a scalar subquery? It is a subquery in a SQL statement that returns exactly one column and zero rows or one row. That single column can be a complex object type, so it can consist of many attributes, but the subquery returns a single scalar value.
rajesh@ORA11GR2> create table t
2 nologging
3 as
4 select * from all_objects;
Table created.
Elapsed: 00:00:06.14
rajesh@ORA11GR2>
rajesh@ORA11GR2> exec dbms_stats.gather_table_stats(user,'T');
PL/SQL procedure successfully completed.
rajesh@ORA11GR2> create or replace function f(x varchar2)
2 return number
3 as
4 begin
5 dbms_application_info.set_client_info( userenv('client_info') + 1 );
6 return (length(x));
7 end;
8 /
Function created.
Elapsed: 00:00:00.67
rajesh@ORA11GR2>
rajesh@ORA11GR2> select count(distinct owner) from t;
rajesh@ORA11GR2>
rajesh@ORA11GR2> select count(distinct owner) from t;
COUNT(DISTINCTOWNER)
--------------------
30
--------------------
30
Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2> exec dbms_application_info.set_client_info(0);
rajesh@ORA11GR2> exec dbms_application_info.set_client_info(0);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2> variable cpu number;
rajesh@ORA11GR2> exec :cpu := dbms_utility.get_cpu_time;
rajesh@ORA11GR2>
rajesh@ORA11GR2> variable cpu number;
rajesh@ORA11GR2> exec :cpu := dbms_utility.get_cpu_time;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
rajesh@ORA11GR2>
rajesh@ORA11GR2> set autotrace traceonly statistics;
rajesh@ORA11GR2> select object_id,f(owner) from t;
rajesh@ORA11GR2>
rajesh@ORA11GR2> set autotrace traceonly statistics;
rajesh@ORA11GR2> select object_id,f(owner) from t;
71598 rows selected.
Elapsed: 00:00:00.73
Statistics
----------------------------------------------------------
55 recursive calls
0 db block gets
1527 consistent gets
0 physical reads
0 redo size
808576 bytes sent via SQL*Net to client
5663 bytes received via SQL*Net from client
479 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
71598 rows processed
rajesh@ORA11GR2> set autotrace off;
rajesh@ORA11GR2>
rajesh@ORA11GR2> select userenv('client_info') ,
2 dbms_utility.get_cpu_time - :cpu
3 from dual;
USERENV('CLIENT_INFO') DBMS_UTILITY.GET_CPU_TIME-:CPU
--------------------------- ------------------------------
71598 67
--------------------------- ------------------------------
71598 67
Elapsed: 00:00:00.09
rajesh@ORA11GR2>
you can see that the function was called once per row even though the inputs to the function were repeated over and over again. If you use a scalar subquery you will notice a massive reduction in calls to the function
rajesh@ORA11GR2> exec dbms_application_info.set_client_info(0);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
rajesh@ORA11GR2> exec :cpu := dbms_utility.get_cpu_time;
rajesh@ORA11GR2> exec :cpu := dbms_utility.get_cpu_time;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
rajesh@ORA11GR2> set autotrace traceonly statistics;
rajesh@ORA11GR2> select object_id,(select f(owner) from dual) from t;
rajesh@ORA11GR2> set autotrace traceonly statistics;
rajesh@ORA11GR2> select object_id,(select f(owner) from dual) from t;
71598 rows selected.
Elapsed: 00:00:00.18
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1495 consistent gets
0 physical reads
0 redo size
808592 bytes sent via SQL*Net to client
5663 bytes received via SQL*Net from client
479 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
71598 rows processed
----------------------------------------------------------
1 recursive calls
0 db block gets
1495 consistent gets
0 physical reads
0 redo size
808592 bytes sent via SQL*Net to client
5663 bytes received via SQL*Net from client
479 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
71598 rows processed
rajesh@ORA11GR2> set autotrace off;
rajesh@ORA11GR2> select userenv('client_info') ,
2 dbms_utility.get_cpu_time - :cpu
3 from dual;
rajesh@ORA11GR2> select userenv('client_info') ,
2 dbms_utility.get_cpu_time - :cpu
3 from dual;
USERENV('CLIENT_INFO') DBMS_UTILITY.GET_CPU_TIME-:CPU
-------------------------- ------------------------------
64 8
-------------------------- ------------------------------
64 8
Elapsed: 00:00:00.01
rajesh@ORA11GR2>
The function call went down from 71598 to 64 and CPU time got dropped. How about making the function DETERMINISTIC
rajesh@ORA11GR2> create or replace function f(x varchar2)
2 return number
3 deterministic
4 as
5 begin
6 dbms_application_info.set_client_info( userenv('client_info') + 1 );
7 return (length(x));
8 end;
9 /
2 return number
3 deterministic
4 as
5 begin
6 dbms_application_info.set_client_info( userenv('client_info') + 1 );
7 return (length(x));
8 end;
9 /
Function created.
Elapsed: 00:00:00.06
rajesh@ORA11GR2>
rajesh@ORA11GR2> exec dbms_application_info.set_client_info(0);
rajesh@ORA11GR2>
rajesh@ORA11GR2> exec dbms_application_info.set_client_info(0);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
rajesh@ORA11GR2> exec :cpu := dbms_utility.get_cpu_time;
rajesh@ORA11GR2> exec :cpu := dbms_utility.get_cpu_time;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
rajesh@ORA11GR2> set autotrace traceonly statistics;
rajesh@ORA11GR2> select object_id,f(owner) from t;
rajesh@ORA11GR2> set autotrace traceonly statistics;
rajesh@ORA11GR2> select object_id,f(owner) from t;
71598 rows selected.
Elapsed: 00:00:00.21
Statistics
----------------------------------------------------------
17 recursive calls
0 db block gets
1519 consistent gets
0 physical reads
0 redo size
808576 bytes sent via SQL*Net to client
5663 bytes received via SQL*Net from client
479 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
71598 rows processed
----------------------------------------------------------
17 recursive calls
0 db block gets
1519 consistent gets
0 physical reads
0 redo size
808576 bytes sent via SQL*Net to client
5663 bytes received via SQL*Net from client
479 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
71598 rows processed
rajesh@ORA11GR2> set autotrace off;
rajesh@ORA11GR2>
rajesh@ORA11GR2> select userenv('client_info') ,
2 dbms_utility.get_cpu_time - :cpu
3 from dual;
rajesh@ORA11GR2>
rajesh@ORA11GR2> select userenv('client_info') ,
2 dbms_utility.get_cpu_time - :cpu
3 from dual;
USERENV('CLIENT_INFO') DBMS_UTILITY.GET_CPU_TIME-:CPU
------------------------------ ------------------------------
954 16
------------------------------ ------------------------------
954 16
Elapsed: 00:00:00.01
rajesh@ORA11GR2>
DETERMINISTIC function reduced the function calls, but not as smart as scalar subquery caching. so going one step futher how about making the function as result_cache in Oracle 11g?
rajesh@ORA11GR2> create or replace function f(x varchar2)
2 return number
3 result_cache
4 as
5 begin
6 dbms_application_info.set_client_info( userenv('client_info') + 1 );
7 return (length(x));
8 end;
9 /
2 return number
3 result_cache
4 as
5 begin
6 dbms_application_info.set_client_info( userenv('client_info') + 1 );
7 return (length(x));
8 end;
9 /
Function created.
Elapsed: 00:00:00.06
rajesh@ORA11GR2>
rajesh@ORA11GR2> exec dbms_application_info.set_client_info(0);
rajesh@ORA11GR2>
rajesh@ORA11GR2> exec dbms_application_info.set_client_info(0);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
rajesh@ORA11GR2> exec :cpu := dbms_utility.get_cpu_time;
rajesh@ORA11GR2> exec :cpu := dbms_utility.get_cpu_time;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
rajesh@ORA11GR2> set autotrace traceonly statistics;
rajesh@ORA11GR2> select object_id,f(owner) from t;
rajesh@ORA11GR2> set autotrace traceonly statistics;
rajesh@ORA11GR2> select object_id,f(owner) from t;
71598 rows selected.
Elapsed: 00:00:00.54
Statistics
----------------------------------------------------------
17 recursive calls
0 db block gets
1519 consistent gets
0 physical reads
0 redo size
808576 bytes sent via SQL*Net to client
5663 bytes received via SQL*Net from client
479 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
71598 rows processed
----------------------------------------------------------
17 recursive calls
0 db block gets
1519 consistent gets
0 physical reads
0 redo size
808576 bytes sent via SQL*Net to client
5663 bytes received via SQL*Net from client
479 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
71598 rows processed
rajesh@ORA11GR2> set autotrace off;
rajesh@ORA11GR2> select userenv('client_info') ,
2 dbms_utility.get_cpu_time - :cpu
3 from dual;
rajesh@ORA11GR2> select userenv('client_info') ,
2 dbms_utility.get_cpu_time - :cpu
3 from dual;
USERENV('CLIENT_INFO') DBMS_UTILITY.GET_CPU_TIME-:CPU
---------------------------- ------------------------------
30 44
Elapsed: 00:00:00.03
rajesh@ORA11GR2>
The function called is equal to the number of distinct values present in the column OWNER. If you run the query again then function call becomes zero but still the cpu utilization time is longer than scalar subquery.
rajesh@ORA11GR2> exec dbms_application_info.set_client_info(0);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
rajesh@ORA11GR2> exec :cpu := dbms_utility.get_cpu_time;
rajesh@ORA11GR2> exec :cpu := dbms_utility.get_cpu_time;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
rajesh@ORA11GR2> set autotrace traceonly statistics;
rajesh@ORA11GR2> select object_id,f(owner) from t;
rajesh@ORA11GR2> set autotrace traceonly statistics;
rajesh@ORA11GR2> select object_id,f(owner) from t;
71598 rows selected.
Elapsed: 00:00:00.50
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1495 consistent gets
0 physical reads
0 redo size
808576 bytes sent via SQL*Net to client
5663 bytes received via SQL*Net from client
479 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
71598 rows processed
----------------------------------------------------------
0 recursive calls
0 db block gets
1495 consistent gets
0 physical reads
0 redo size
808576 bytes sent via SQL*Net to client
5663 bytes received via SQL*Net from client
479 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
71598 rows processed
rajesh@ORA11GR2> set autotrace off;
rajesh@ORA11GR2>
rajesh@ORA11GR2> select userenv('client_info') ,
2 dbms_utility.get_cpu_time - :cpu
3 from dual;
rajesh@ORA11GR2>
rajesh@ORA11GR2> select userenv('client_info') ,
2 dbms_utility.get_cpu_time - :cpu
3 from dual;
USERENV('CLIENT_INFO') DBMS_UTILITY.GET_CPU_TIME-:CPU
------------------------------ ------------------------------
0 42
------------------------------ ------------------------------
0 42
Elapsed: 00:00:00.01
rajesh@ORA11GR2>
This shows that even if the function is defined as DETERMINISTIC or RESULT_CACHE its better to have the function called as scalar subquery for efficient CPU utilization.
No comments:
Post a Comment