Re: Follow Up: DETERMINISTIC in user functions

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

1. there were a couple of owner names that collided in the hash table but
2. the table was almost sorted by owner name anyway.

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.

-- 
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.

>