Scalar Subquery

-- Scalar Subquery ---


CREATE OR REPLACE
  FUNCTION f(
      x IN VARCHAR2)
    RETURN NUMBER
  AS
  BEGIN
    dbms_application_info.set_client_info(userenv('client_info')+1);
    RETURN LENGTH(x);
END;
/

VARIABLE cpu NUMBER

--------------------------------------------------------------------------
BEGIN
  :cpu := dbms_utility.get_cpu_time;
  dbms_application_info.set_client_info(0);
END;
/

SELECT owner, f(owner) FROM all_objects;

SELECT dbms_utility.get_cpu_time - :cpu AS cpu_hsecs,
  userenv('client_info')                AS calls
FROM dual;

/*

CPU_HSECS CALLS                                                          
--------- ----------------------------------------------------------------
       20 5050  
       
*/

--------------------------------------------------------------------------
BEGIN
  :cpu := dbms_utility.get_cpu_time;
  dbms_application_info.set_client_info(0);
END;
/

SELECT owner, (select f(owner) from dual) FROM all_objects;

SELECT dbms_utility.get_cpu_time - :cpu AS cpu_hsecs,
  userenv('client_info')                AS calls
FROM dual;


CPU_HSECS CALLS                                                          
--------- ----------------------------------------------------------------
        5 4           

--------------------------------------------------------------------------        
-- Application

-- Instead of 

SELECT * FROM t
where c = plsql_function();

-- User
SELECT * FROM t
WHERE c = (select plsql_function());

REFERENCES: Oracle Magazine: On Caching and Evangelizing SQL by Tom Kyte (September/October 2011)