Scalar subqueries to reduce PL/SQL to SQL switching

Tom Kyte has written an excellent article on the current issue (September/October) of Oracle Magazine which shows how to reduce switching between pl/sql and sql using scalar subquery.

Tom describes a scalar subquery as "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 (or NULL if the subquery returns zero records). A scalar subquery can be used anywhere a literal could have been used."

On the article there are many comparisons of using scalar subquery vs deterministic pl/sql, result cache and etc.
Below is the simplest case of performance comparison with and without scalar subquery.

1.Create a big table to query

SQL> create table bigtable as select * from all_objects nologging;

2. Create the function that will be used in this test case

create or replace function mytest(p_owner varchar2) return number is
obj_count number :=0;
begin 
 select count(*) into obj_count from bigtable bt where bt.owner = p_owner; 
 return obj_count; 
end; 
/

3. Run the first test case without scalar subquery

set timing on
variable cpu number;
begin
:cpu := dbms_utility.get_cpu_time;
end;
/

SQL> select owner,mytest(owner) from bigtable;
....
SYS                                    31328
SYS                                    31328

72232 rows selected. -- this is all the rows in the table
Elapsed: 00:05:11.01

SQL> select dbms_utility.get_cpu_time-:cpu cpu_hsecs from dual;

CPU_HSECS
---------
30931

4. Second test case with scalar subquery caching

set timing on
variable cpu number;
begin
:cpu := dbms_utility.get_cpu_time;
end;
/

SQL> select owner,(select mytest(owner) from dual) mo from bigtable;

SYS                                                    31328
SYS                                                    31328
SYS                                                    31328

72232 rows selected.  -- all the rows in the table

Elapsed: 00:00:17.05

SQL> select dbms_utility.get_cpu_time-:cpu cpu_hsecs from dual;
CPU_HSECS
---------
56

As seen from the outputs both cpu usage and elapsed time has reduced. How this happens has been explained in the above mentioned article.