Re: Follow Up: DETERMINISTIC in user functions

In article <cs0qqq$eho$1_at_titan.btinternet.com>, Jonathan Lewis says...
>
>
>
>Cute trick.
>
>This looks as if it may be similar to the
>mechanism Oracle uses for caching the
>results of subqueries that have not been
>unnested and therefore run as a FILTER
>operation. (If so, the cache is probably
>a hash table of 256 rows in 8i and 9i,
>and 1024 rows in 10g).
>
>I would ascribe the drop from 48,000
>to 33,000 as being due to a side effect
>of the large number of times that Oracle
>creates an object and a synonym with the
>same name as consecutive steps (combined
>with the way you created the table, of course).
>
>The 66 / 27 is a bit of a puzzle, though - but
>it could be explained if
> a) there were a couple of owner names
> that collided in the hash table but
> b) the table was almost sorted by owner
> name anyway.
>

yes it is...

>I can't emulate your test, as I obviously have
>a different 10g install - but it would be interesting
>to get the owner name output each time the
>function was called, just to check the theory.
>After about the first 25 calls, I would expect
>to see just a couple of names alternating.
>

run this -- flipping 76 to 75. 75 seems "magic", but that doesn't explain the 27/66 above.

But basically -- it is a cache that works best if the bind variables to the scalar subquery comes to it sorted.

set echo on
set linesize 121

drop table t;
create table t as
select rownum x, mod(rownum,76) y
  from all_objects
 where rownum <= 5000;

create or replace function f( x in number ) return number as
begin

    dbms_application_info.set_client_info( userenv('client_info')+1 );     return 0;
end;
/

set autotrace traceonly
exec dbms_application_info.set_client_info(0); select (select f(y) from dual) from (select * from t order by x); exec dbms_output.put_line( userenv('client_info') );

exec dbms_application_info.set_client_info(0); select (select f(y) from dual) from (select * from t order by y); exec dbms_output.put_line( userenv('client_info') );

set autotrace off

>
>--
>Regards
>
>Jonathan Lewis
>
>http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>The Co-operative Oracle Users' FAQ
>
>http://www.jlcomp.demon.co.uk/seminar.html
>Public Appearances - schedule updated Dec 23rd 2004
>
>
>
>
>
>
>"Thomas Kyte" <tkyte_at_oracle.com> wrote in message
>news:115451089.00011d1d.011_at_drn.newsguy.com...
>> In article <b4cefdce.0501101228.b0e90a8_at_posting.google.com>, David says...
>>>
>>>Hi, two years and one month ago I posted a question in this NG that
>>>was very satisfactorily answered. I am contemplating upgrading the
>>>firms version of Oracle to 10g and find myself asking myself the same
>>>question again, not that it is relevant to the upgrade but more out of
>>>interest. After Googl'ing through the Oracle 10g docs on-line, I've
>>>decided to ask the same question again and importantly seek expert
>>>opinion whether the situation has changed (especially the scenario
>>>outlined by Connor). Highlights from the original message thread
>>>pasted in-line for those of you whose newsservers don't go back this
>>>far.
>>>Thank you
>>>David
>>>
>>
>> No, it hasn't changed, however -- there is and has been "scalar subquery
>> caching" which I frequently use to cut down the calls. Consider:
>>
>> ops$tkyte_at_ORA10G> /*
>> ops$tkyte_at_ORA10G> drop table t;
>> ops$tkyte_at_ORA10G> create table t as select * from all_objects;
>> ops$tkyte_at_ORA10G> exec dbms_stats.gather_table_stats( user, 'T' );
>> ops$tkyte_at_ORA10G> */
>> ops$tkyte_at_ORA10G>
>>
>>
>> 1* select count(*), count(distinct object_name), count(distinct owner)
>> from t
>> ops$tkyte_at_ORA10G> /
>>
>> COUNT(*) COUNT(DISTINCTOBJECT_NAME) COUNT(DISTINCTOWNER)
>> ---------- -------------------------- --------------------
>> 48039 28399 27
>>
>>
>>
>> so, there are 48,039 rows in there -- object_name repeats, only has 28,399
>> unique ones and owner really repeats. we create a function:
>>
>>
>> ops$tkyte_at_ORA10G> create or replace function f( x in varchar2 ) return
>> number
>> 2 DETERMINISTIC
>> 3 as
>> 4 begin
>> 5 dbms_application_info.set_client_info(
>> userenv('client_info')+1 );
>> 6 return 0;
>> 7 end;
>> 8 /
>>
>> Function created.
>>
>>
>> it'll just count "how often I've been called". Now we are ready to test.
>> I ran
>> a set of 4 queries (well, 8 really -- once for object_name, again for
>> owner)
>> using
>>
>> a) reset client_info to zero
>> b) run query
>> c) remember client_info value in a table for later
>>
>>
>> Note the queries -- I run basically the same query twice, once with and
>> once
>> without an inline view:
>>
>> ops$tkyte_at_ORA10G> set autotrace traceonly
>> ops$tkyte_at_ORA10G> exec dbms_application_info.set_client_info(0);
>> PL/SQL procedure successfully completed.
>>
>> ops$tkyte_at_ORA10G> select object_name, f(object_name) from t;
>> 48039 rows selected.
>>
>> Execution Plan
>> ----------------------------------------------------------
>> 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=154 Card=48039
>> Bytes=1152936)
>> 1 0 TABLE ACCESS (FULL) OF 'T' (TABLE) (Cost=154 Card=48039
>> Bytes=1152936)
>>
>> ops$tkyte_at_ORA10G> exec insert into msg values ( 'First => ' ||
>> userenv('client_info') );
>> PL/SQL procedure successfully completed.
>>
>> ops$tkyte_at_ORA10G> exec dbms_application_info.set_client_info(0);
>> PL/SQL procedure successfully completed.
>>
>> ops$tkyte_at_ORA10G> select object_name, f(object_name) from (select
>> object_name
>> from t order by object_name);
>> 48039 rows selected.
>>
>> Execution Plan
>> ----------------------------------------------------------
>> 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=502 Card=48039
>> Bytes=816663)
>> 1 0 VIEW (Cost=502 Card=48039 Bytes=816663)
>> 2 1 SORT (ORDER BY) (Cost=502 Card=48039 Bytes=1152936)
>> 3 2 TABLE ACCESS (FULL) OF 'T' (TABLE) (Cost=154 Card=48039
>> Bytes=1152936)
>>
>> ops$tkyte_at_ORA10G> exec insert into msg values ( 'Second => ' ||
>> userenv('client_info') );
>> PL/SQL procedure successfully completed.
>>
>> ops$tkyte_at_ORA10G> exec dbms_application_info.set_client_info(0);
>> PL/SQL procedure successfully completed.
>>
>> ops$tkyte_at_ORA10G> select object_name, (select f(object_name) from dual)
>> from t;
>> 48039 rows selected.
>>
>> Execution Plan
>> ----------------------------------------------------------
>> 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=154 Card=48039
>> Bytes=1152936)
>> 1 0 FAST DUAL (Cost=2 Card=1)
>> 2 0 TABLE ACCESS (FULL) OF 'T' (TABLE) (Cost=154 Card=48039
>> Bytes=1152936)
>>
>> ops$tkyte_at_ORA10G> exec insert into msg values ( 'Third => ' ||
>> userenv('client_info') );
>> PL/SQL procedure successfully completed.
>>
>> ops$tkyte_at_ORA10G> exec dbms_application_info.set_client_info(0);
>> PL/SQL procedure successfully completed.
>>
>> ops$tkyte_at_ORA10G> select object_name, (select f(object_name) from dual)
>> from
>> (select object_name from t order by object_name);
>> 48039 rows selected.
>>
>> Execution Plan
>> ----------------------------------------------------------
>> 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=502 Card=48039
>> Bytes=816663)
>> 1 0 FAST DUAL (Cost=2 Card=1)
>> 2 0 VIEW (Cost=502 Card=48039 Bytes=816663)
>> 3 2 SORT (ORDER BY) (Cost=502 Card=48039 Bytes=1152936)
>> 4 3 TABLE ACCESS (FULL) OF 'T' (TABLE) (Cost=154 Card=48039
>> Bytes=1152936)
>>
>>
>> Now, the counts:
>>
>> ops$tkyte_at_ORA10G> select * from msg;
>>
>> TXT
>>--------------------------------------------------------------------------------
>> First => 48039
>> Second => 48039
>> Third => 33206
>> Fourth => 28399
>> First with owner => 48039
>> Second with owner => 48039
>> Third with owner => 66
>> Fourth with owner => 27
>>
>> 8 rows selected.
>>
>
>

-- 
Thomas Kyte
Oracle Public Sector
http://asktom.oracle.com/
opinions are my own and may not reflect those of Oracle Corporation