Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Ian.

Asked: June 06, 2000 - 10:09 am UTC

Last updated: August 30, 2011 - 3:44 pm UTC

Version: SQL Plus 3.2.3 on DB 7.3.4

Viewed 1000+ times

You Asked

I have written various stored functions to help inexperienced developers after a recent changes in our underlying database structure. These functions work well in a Select or Group By Having clause but can not be used in the WHERE clause as they cause the select to run very slowly. Any tips?

and Tom said...

Well, when used in "SELECT" clause, the function is only executed when you actually fetch the data -- in the aggregate clauses -- only after most of the data has been processed. So, they run rather rapidly since they process relatively few rows (or in the case of a select, a few rows at a time while the client fetches them so any overhead is not noticed).

In the case of a where clause -- they can tend to get executed once per row in the query -- even if they return a constant. That is because they might return a different value for each call (so they call it for every row).

I'd need some examples of what you are trying to do to tell you exactly how to optimize this but.... I can give a few pointers for making these run faster.

Lets say you were attempting to parameterize a view or a query. You had a stored procedure that filled in some package variables and functions to return these values (or in fact ANY function you knew only needed to be called ONCE per query -- not once per ROW per query) you could use the trick below to speed things up. Here is an example of what I mean:

scott@ORA734.WORLD> create or replace package my_pkg
2 as
3 procedure set_data( p_data in varchar2 );
4
4 function get_data1 return varchar2;
5 pragma restrict_references
6 ( get_data1, wnds, rnds, wnps );
7
7 function get_data2 return varchar2;
8 pragma restrict_references
9 ( get_data2, wnds, rnds, wnps );
10
10 pragma restrict_references
11 ( my_pkg, wnds, rnds, wnps, rnps );
12 end;
13 /

Package created.

scott@ORA734.WORLD> create or replace package body my_pkg
2 as
3 g_data varchar2(255);
4
4 procedure set_data( p_data in varchar2 )
5 is
6 begin
7 g_data := p_data;
8 end;
9
9
9 function get_data1 return varchar2
10 is
11 begin
12 return g_data;
13 end;
14
14 function get_data2 return varchar2
15 is
16 begin
17 return g_data;
18 end;
19 end;
20 /

Package body created.

So, we have a simple package to return some data from a function. the value returned by get_data never changes over the course of the SELECT call -- it is deterministic in nature.

scott@ORA734.WORLD> exec my_pkg.set_data( 'KING' );
PL/SQL procedure successfully completed.

scott@ORA734.WORLD> alter session set sql_trace=true;
Session altered.
We'll use sql_trace in 7.x to see how many times the plsql routine is actually called from sql...

scott@ORA734.WORLD> select ename, empno
2 from emp
3 where ename = my_pkg.get_data1;

ENAME EMPNO
---------- ----------
KING 7839

scott@ORA734.WORLD> select ename, empno
2 from emp
3 where ename = ( select my_pkg.get_data2 from dual );

ENAME EMPNO
---------- ----------
KING 7839

When we look at the SQL trace for this, we'll find the the predicate:
3 where ename = my_pkg.get_data1;

causes the function to be called once per row in the query whereas the predicate:
3 where ename = ( select my_pkg.get_data2 from dual );

caused the function to be called once PER QUERY...

begin :r:="MY_PKG"."GET_DATA1";end;


call count cpu elapsed disk ...
------- ------ -------- ---------- ---------- -----...
Parse 1 0.01 0.01 0 ...
Execute 14 0.02 0.02 0 ...
Fetch 0 0.00 0.00 0 ...
------- ------ -------- ---------- ---------- -----...
total 15 0.03 0.03 0 ...

begin :r:="MY_PKG"."GET_DATA2";end;


call count cpu elapsed disk ...
------- ------ -------- ---------- ---------- -----...
Parse 1 0.01 0.01 0 ...
Execute 1 0.01 0.01 0 ...
Fetch 0 0.00 0.00 0 ...
------- ------ -------- ---------- ---------- -----...
total 2 0.02 0.02 0 ...


(it should be noted that if we had an index on ENAME, similar execution profiles would have been seen for both predicates - the optizer would have evaluated the function ONCE and then gone to the index for the rows -- this trick applies to UNINDEXed columns or when the optimizer decides to not use the index on the column)



Another optimization trick is to keep the complex code in the PLSQL routine but use DECODE on some of the "easier" conditions to limit the number of times a routine is called. For example, once I implemented some "row level security" in Oracle7 using this PLSQL called in a where clause feature. We had three columns we need to perform some complex logic on and this function would return 1 if you could see it, 0 if you could not. In my case, almost all of the rows could be seen, it was only when the 3 columns had some value in it we really needed to call the function. Since most of the data did not need to go through this routine, we set up our views as such:

select *
from t
where decode( c1||c2||c3, NULL, 1, f(c1,c2,c3) ) = 1;

That checked first in decode (very fast, native sql) to see if we needed to even call the function and if not, return 1 else call the function and return what it returned... In this fashion, we noticed almost no perform hit since most of the rows were done by decode and never called f().






Rating

  (13 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Parameterised Views and Application contexts...

Matt, January 24, 2003 - 10:36 pm UTC

We used 'parameterised views' quiet intentively in our 7.3.4.0 application and noticed simliar performance degradation over time. This (your explainiation) is something like what I thought was happening (that and the optimiser not being able to identify the PL/SQL function call as a possible bind value.).

We are using PL/SQL functions in the where to return the value of a packaged global variable with in a view. An example view definition might be something like

select * from table t_p1
where t_p1.col1 = my_package.my_function()
UNION ALL
select * from table t_p2
where t_p1.col1 = my_package.my_function()
...
UNION ALL
select * from table t_pn
where t_p1.col1 = my_package.my_function()

Now that we have upgraded (9.0.1.4.0) I was going to update this to take advantage of an application context. A function would set the context once before any query on the view and the view definition would be changed to access this context instead of the PL/SQL function. I think that this was I get the benefit of improved performance (no longer multiple calls to the PL/SQL function) when querying these views as well as the use of bind variables (the optimiser is aware of the sys_context() call and treats it as a bind variable).

Is my understanding correct?

Is this approach valid for both the RBO (using currently) and CBO (implementing this currently)?

Thanks and regards.

Tom Kyte
January 25, 2003 - 11:09 am UTC

Yes, AND you can do this in 734 with:

dbms_application_info.set_client_info( .... );


and then use

where t_p1.col1 = userenv( 'client_info' )


as well - it is "bindable" in 73 with client_info

OK

R.Saravanan, August 25, 2003 - 1:09 pm UTC

Dear Sir,
Well and wish the same from you.
I tried to create a function which takes a number and tests
for prime.I don't know to proceed with the code since I am
new to oracle.Could you please help?
I want the function like
create or replace function test_prime(param in number)
return varchar2 is
begin
if..code which tests the number for prime then
return 'Number is prime'
else
return 'Not prime'
end if;
end;
I want the code like this.
Thanks
R.Saravanan




Tom Kyte
August 25, 2003 - 2:53 pm UTC 

lets see -- maybe teach how to fish today....

I googled:

"determine if a number is prime" 

found an algorithm and converted from C to plsql and tadah:

ops$tkyte@ORA920> create or replace function is_prime( p_n in number ) return varchar2
  2  as
  3          l_stop number := ceil(sqrt(p_n));
  4  begin
  5          for i in 2 .. l_stop
  6          loop
  7                  if ( mod(p_n,i) = 0 )
  8                  then
  9                          return 'not prime';
 10                  end if;
 11          end loop;
 12          return 'prime';
 13  end;
 14  /
 
Function created.
 
ops$tkyte@ORA920> column is_prime format a10
ops$tkyte@ORA920>
ops$tkyte@ORA920>
ops$tkyte@ORA920> select user_id, is_prime(user_id) is_prime from all_users;
 
   USER_ID IS_PRIME
---------- ----------
         0 prime
         5 prime
        11 prime
        19 prime
        21 not prime
        30 not prime
        31 prime
        32 not prime
        33 not prime
        35 not prime
        36 not prime
        39 not prime
        40 not prime
        42 not prime
        43 prime
        44 not prime
        46 not prime
        47 prime
        48 not prime


<b>seems to work, but not tested 100%.  if you really need to have a prime checker, you'll need to do the necessary legwork to validate this mathematically!</b>

but I was told that 2 is the smallest prime ;o)

A reader, August 25, 2003 - 4:06 pm UTC


Tom Kyte
August 26, 2003 - 7:53 am UTC

there you go, that's why I added the caveat about "validate this ok"

it isn't really a database question -- just trying to show how I might have gone about getting the answer all by myself.

it is a quick fix, no?

3 l_stop number := least( ceil(sqrt(p_n)), p_n-1 );


Define prime number = number which is divisible by 1 and itself

pasko, August 26, 2003 - 3:21 am UTC

i think Tom is right, if i correctly remember a prime number definition from my College Days::=
as a :

number which is divisible ONLY by 1 and itself.
divisible, meaning without remainder.

such as:
2,3,5,7,11,13

I Went Fishing

Mark A. Williams, September 18, 2003 - 5:32 pm UTC 

I find things like prime numbers, pythagorean triples, and perfect triangles to be interesting, so I did a little fishing based on Tom's code as a starting point.  I wrote some code that takes a slightly different approach to determining if a number is prime.  While the code itself is slightly more verbose, it does the necessary legwork to perform validation and eliminate numbers which are clearly non-prime early in the logic.  In addition, the run-time performance seems to be improved.

Sample code from above slightly modified to return number instead of varchar2:

SQL> create or replace function is_prime(p_in in number) return number as
  2    l_stop number := least(ceil(sqrt(p_in)),p_in-1);
  3  begin
  4    for i in 2..l_stop
  5    loop
  6      if (mod(p_in,i) = 0) then
  7        return 0;
  8      end if;
  9    end loop;
 10    return 1;
 11  end;
 12  /

Function created.

SQL> set timing on
SQL> begin
  2    for i in 1..1000000
  3    loop
  4      :v_prime := is_prime(i);
  5    end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

Elapsed: 00:04:24.06

SQL> create or replace package utl_math as
  2    function is_prime(p_in number) return number;
  3  end utl_math;
  4  /

Package created.

SQL> create or replace package body utl_math as
  2    function is_prime(p_in number) return number is
  3      l_sqrt number;
  4      l_sqrt_ceil number;
  5      l_divisor number;
  6      l_divisor_squared number;
  7    begin
  8      -- prime numbers must be >= 2
  9      if p_in < 2 then
 10        return 0;
 11      end if;
 12
 13      -- only integers can be prime
 14      if p_in != ceil(p_in) then
 15        return 0;
 16      end if;
 17
 18      -- 2 is the only even prime, so it is a special case
 19      if p_in = 2 then
 20        return 1;
 21      end if;
 22
 23      -- eliminate all other even numbers
 24      if mod(p_in,2) = 0 then
 25        return 0;
 26      end if;
 27
 28      -- if the sqrt of the number is an integer, the number is not prime
 29      l_sqrt := sqrt(p_in);
 30      l_sqrt_ceil := ceil(l_sqrt);
 31
 32      if l_sqrt = l_sqrt_ceil then
 33        return 0;
 34      end if;
 35
 36      -- the number has passed the basic elimination tests and may be prime
 37      -- loop through set of odd divisors to determine if number is prime
 38      l_divisor := 3;
 39
 40      for i in 1..l_sqrt_ceil
 41      loop
 42        l_divisor_squared := l_divisor * l_divisor;
 43
 44        -- l_divisor is a factor of p_in, therefore not a prime
 45        if mod(p_in,l_divisor) = 0 and l_divisor_squared < p_in then
 46          return 0;
 47        end if;
 48
 49        -- no factor found, therefore number is a prime
 50        if l_divisor_squared > p_in then
 51          return 1;
 52        end if;
 53
 54        l_divisor := l_divisor + 2;
 55      end loop;
 56    end is_prime;
 57  end utl_math;
 58  /

Package body created.

SQL> begin
  2    for i in 1..1000000
  3    loop
  4      :v_prime := utl_math.is_prime(i);
  5    end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

Elapsed: 00:02:52.01

This seems like a decent approach, but anyone see anything obvious that I missed out?

Thanks,

Mark

Execute Function only once per query - Why ?

Martin van Donselaar, June 09, 2004 - 6:24 am UTC

Tom,

First of all thanks for pointing out this approach to execute a function only once per query. This really helped me to solve my perfomance problem

I don't quite understand why the first query below is fast whereas the second one is very time-consuming

SELECT *
from xxcbr_as_sales_facts_v srt
where 1 = (select xxcbr_as_functions.XXCBR_AS_SECURITY(srt.SALESREP_ID,fnd_global.USER_ID) from dual)

SELECT *
from xxcbr_as_sales_facts_v srt
where xxcbr_as_functions.XXCBR_AS_SECURITY(srt.SALESREP_ID,fnd_global.USER_ID) = 1

Could you please explain this? Why would the first approach cause the function to run only once whereas the second one will cause it to run for every row in the view? The salesrep_id could be different for each row so I don't see how the first approach can run the function only once (or maybe it runs only once for each distinct salesrep?)

Regards,

Martin



Tom Kyte
June 09, 2004 - 9:19 am UTC 

it is to do with how scalar subqueries are executed and cached.

Consider this example with comments inline:

ops$tkyte@ORA9IR2> create table t ( x int, y int );
Table created.
 
ops$tkyte@ORA9IR2> insert into t select mod(rownum,100), dbms_random.random from all_objects;
32091 rows created.
 
ops$tkyte@ORA9IR2> create or replace context my_ctx using f;
Context created.
 
ops$tkyte@ORA9IR2> create or replace function f( p_x in number, p_name in varchar2 ) return number
  2  as
  3  begin
  4          dbms_session.set_context( 'my_ctx', p_name, nvl(sys_context('my_ctx',p_name),0)+1 );
  5          return 1;
  6  end;
  7  /
Function created.
 
<b>that function always returns 1 but each time it is called, it will retrieve an application context value and increment it and put it back -- we can use that to see how many times the function is called...

We'll reconnect to "reset" our context values -- in case you run this more than once:</b>

ops$tkyte@ORA9IR2> @connect /
ops$tkyte@ORA9IR2> set termout off
ops$tkyte@ORA9IR2> set termout on
ops$tkyte@ORA9IR2> set linesize 121
ops$tkyte@ORA9IR2> set timing on
ops$tkyte@ORA9IR2> set autotrace traceonly

ops$tkyte@ORA9IR2> select * from (select * from t order by x) where 1 = f(x,'ORDER_BY_X_NO_SUBQ');
 
32091 rows selected.
 
Elapsed: 00:00:05.53

<b>That called the function F(x,...) once per row in this case.  It took about 5 seconds.  If we had indexed (f(x,'...')) it could have called it once per query but here it processed row by row by row...</b>
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   VIEW
   2    1     SORT (ORDER BY)
   3    2       TABLE ACCESS (FULL) OF 'T'
 
 
 
 
Statistics
----------------------------------------------------------
      32121  recursive calls
          0  db block gets
      96362  consistent gets
          0  physical reads
          0  redo size
     658774  bytes sent via SQL*Net to client
      24028  bytes received via SQL*Net from client
       2141  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
      32091  rows processed
 
ops$tkyte@ORA9IR2> select * from (select * from t order by y) where 1 = f(x,'ORDER_BY_Y_NO_SUBQ');
 
32091 rows selected.
 
Elapsed: 00:00:05.65

<b>we'll discover below that this too was called once per row -- row by row by row...</b>
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   VIEW
   2    1     SORT (ORDER BY)
   3    2       TABLE ACCESS (FULL) OF 'T'
 
 
 
 
Statistics
----------------------------------------------------------
      32091  recursive calls
          0  db block gets
      96349  consistent gets
          0  physical reads
          0  redo size
     632545  bytes sent via SQL*Net to client
      24028  bytes received via SQL*Net from client
       2141  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      32091  rows processed
 
ops$tkyte@ORA9IR2> select * from (select * from t order by x) where 1 = (select f(x,'ORDER_BY_X_SUBQ') from dual);
 
32091 rows selected.
 
Elapsed: 00:00:00.48

<b>that was really fast -- but does the same thing.  What we'll discover is that the scalar subquery was executed 100 times.  There is caching going on for that query so that as the first row is fetched, the database looks at the inputs to the scalar subquery and asks "did we just do this query with these inputs, if so, lets reuse that answer".  On the first row -- we got a miss (did the function) but the next 99 rows (because of the order by x) are all the same input value -- so it just reused those values...</b>

 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   FILTER
   2    1     VIEW
   3    2       SORT (ORDER BY)
   4    3         TABLE ACCESS (FULL) OF 'T'
   5    1     TABLE ACCESS (FULL) OF 'DUAL'
 
 
 
 
Statistics
----------------------------------------------------------
        100  recursive calls
          0  db block gets
        676  consistent gets
          0  physical reads
          0  redo size
     658774  bytes sent via SQL*Net to client
      24028  bytes received via SQL*Net from client
       2141  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      32091  rows processed
 
ops$tkyte@ORA9IR2> select * from (select * from t order by y) where 1 = (select f(x,'ORDER_BY_Y_SUBQ') from dual);
 
32091 rows selected.
 
Elapsed: 00:00:02.46

<b>that was "pretty fast" but not "truly fast".  What happened here was we defeated the caching a little bit - we ordered the data not by X but by a random number.  So row 1 comes out with say X=55, cache it.  row 2 comes out with X=77, cache it.  Row N comes out with X=55 -- X=55 might still be sitting in our little cache -- might not.  We might get a hit or a miss and have to reexecute the scalar subquery..</b>
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   FILTER
   2    1     VIEW
   3    2       SORT (ORDER BY)
   4    3         TABLE ACCESS (FULL) OF 'T'
   5    1     TABLE ACCESS (FULL) OF 'DUAL'
 
 
 
 
Statistics
----------------------------------------------------------
       7690  recursive calls
          0  db block gets
      46216  consistent gets
          0  physical reads
          0  redo size
     632545  bytes sent via SQL*Net to client
      24028  bytes received via SQL*Net from client
       2141  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      32091  rows processed
 
ops$tkyte@ORA9IR2> set autotrace off
ops$tkyte@ORA9IR2> select * from session_context;
 
NAMESP ATTRIBUTE            VALUE
------ -------------------- ------
MY_CTX ORDER_BY_X_SUBQ      100
MY_CTX ORDER_BY_Y_SUBQ      7690
MY_CTX ORDER_BY_Y_NO_SUBQ   32091
MY_CTX ORDER_BY_X_NO_SUBQ   32091
 
Elapsed: 00:00:00.05
ops$tkyte@ORA9IR2>

<b>there is the results.  ordering by the inputs to the function -- and then calling the function via a scalar subquery caused the function to be called once per unique set of values -- 100 times.  Ordering by something unrelated to the inputs (so the inputs arrive randomly) shows a dramatic increase (or decrease depending on how you look at this :) in calls.  The caching was "good", it benefited the query, but because of the random nature of the data -- it was "not as fast as it could be".
</b>
The last two show that when processed row by row -- this caching does not take place.

Great Explanation

Martin, June 09, 2004 - 10:56 am UTC

Thanks Tom,

This is great stuff. If I understand well then ordering on salesrep_id in the example I provided might (or might not) speed up the query, depending on the number of distinct values of salesrep_id and how scattered they are

(BTW there's a term for no of distinct values in a field, granularity or something?)

Regards,

Martin

Tom Kyte
June 09, 2004 - 11:28 am UTC

cardinality

sorting the values of a field in given order

KRISHNAKANTH, November 10, 2006 - 6:13 am UTC

for eg. I am having a table like this

relyear dotnum budgetcategory projecttype
2003 0 ecp appl
2003 0 ecp appl
2004 0 ecp appl
2004 1 base clnp
2004 0 ecp appl
2003 1 ecp clnp
2003 1 base appl
2005 1 ecp appl
2005 0 cap clnp
2003 0 ecp appl
2004 0 base rebl
2003 1 ecp rebl
2003 1 base appl
2005 0 base randr
2005 0 cap randr
2005 0 cap appl

create table project(relyear char(4),dotnum char(1),budgetcategory char(10),projecttype char(8));
insert into project values('2003','0','ecp','appl');
insert into project values('2003','0','ecp','appl');
insert into project values('2004','0','ecp','appl');
insert into project values('2004','1','base','clnp');
insert into project values('2004','0','ecp','appl');
insert into project values('2003','1','ecp','clnp');
insert into project values('2003','1','base','appl');
insert into project values('2005','1','ecp','appl');
insert into project values('2005','0','cap','clnp');
insert into project values('2003','0','ecp','appl');
insert into project values('2004','0','base','rebl');
insert into project values('2003','1','ecp','rebl');
insert into project values('2003','1','base','appl');
insert into project values('2005','0','base','randr');
insert into project values('2005','0','cap','randr');
insert into project values('2005','0','cap','appl');

The field 'budgetcategory' consists of values 'base','cap','ecp'
The field 'projecttype' consists of values 'appl','clnp','rebl','randr'

I want to sort the field 'projecttype' in the order 'clnp','rebl','randr','appl' and not in ascending or descending order.

or else the sorting should be in the following order.
(1) base
a. clnp
b. rebl
c. randr
d. appl
(2) ecp
a. clnp
b. rebl
c. randr
d. appl
(3) cap
a. clnp
b. rebl
c. randr
d. appl

how this can be done with stored procedure

Tom Kyte
November 10, 2006 - 9:02 am UTC 

ops$tkyte%ORA10GR2> select *
  2    from project
  3   order by budgetcategory,
  4            case
  5            when projecttype = 'clnp' then 1
  6            when projecttype = 'rebl' then 2
  7            when projecttype = 'randr' then 3
  8            when projecttype = 'appl' then 4
  9            end
 10  /

RELY D BUDGETCATE PROJECTT
---- - ---------- --------
2004 1 base       clnp
2004 0 base       rebl
2005 0 base       randr
2003 1 base       appl
2003 1 base       appl
2005 0 cap        clnp
2005 0 cap        randr
2005 0 cap        appl
2003 1 ecp        clnp
2003 1 ecp        rebl

Is this a valid method to 'cache' a function?

Martin van Donselaar, November 21, 2006 - 1:40 pm UTC

Hi Tom,

Some time ago you already gave me great advise in this thread on how to make oracle cache functions.

Recently I came across of the following method of 'caching' a function. It seems usefull when there are a limited number of possible values to run the function with.
The Idea is to wrap the original security function in a function that stores the returned value of the security function in a pl/sql table and returns it if it does not yet exist, otherwise the wrapper function just returns the previously fetched value for the same input parameter (which is already stored in the pl/sql table).

I would like to know your opinion and if you see any (big) caveats to consider.

CREATE OR REPLACE PACKAGE BODY xxdis_eos_functions
IS

TYPE org_id_lookup_tabtype IS TABLE OF boolean INDEX BY binary_integer;
org_id_lookup_tab org_id_lookup_tabtype;

FUNCTION show_bis_record (org_id in number default null)return varchar2
IS
BEGIN
IF org_id_lookup_tab(org_id)
THEN
RETURN('TRUE');
ELSE
RETURN('FALSE');
END IF;

EXCEPTION
WHEN no_data_found THEN
IF (hr_security.show_bis_record(org_id ) = 'TRUE')
THEN org_id_lookup_tab(org_id) := true;
RETURN 'TRUE';
ELSE org_id_lookup_tab(org_id) := false;
RETURN 'FALSE';
END IF;
END show_bis_record;

END xxdis_eos_functions;

A reader, November 27, 2006 - 5:33 pm UTC

Tom

From your original example:

select ename, empno
2 from emp
3 where ename = my_pkg.get_data1;

We have Oracle 9i and it seem to cache the function for an unindexed "ename" column, and does not invoke it for each row. We have Rule Based optimizer.
Has something been added to Oracle version to do this optimization over the 7.3 version that you were testing against?

Tom Kyte
November 27, 2006 - 8:14 pm UTC 

ops$tkyte%ORA10GR2> create or replace package my_pkg
  2  as
  3          function get_data1 return varchar2;
  4  end;
  5  /

Package created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace package body my_pkg
  2  as
  3
  4  function get_data1 return varchar2
  5  as
  6  begin
  7          dbms_application_info.set_client_info( userenv('client_info')+1 );
  8          return 'x';
  9  end;
 10
 11  end;
 12  /

Package body created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select ename, empno from emp where ename = my_pkg.get_data1;

no rows selected

ops$tkyte%ORA10GR2> select userenv('client_info') from dual;

USERENV('CLIENT_INFO')
----------------------------------------------------------------
14



and how did you measure that - we did not even define it as deterministic, it cannot "cache" it.

karthick, December 04, 2007 - 1:31 am UTC 

create table t(x integer);


Table created.

insert into t select 1 from dual connect by level <= 10000;

10000 rows created.

commit;
Commit complete.

create function f(x in integer) return integer as begin return 1; end;
/


Function created.

analyze table t compute statistics;

Table analyzed.

set autotrace traceonly
set timing on

select * from t where (select f(x) from dual) = 1;

10000 rows selected.

Elapsed: 00:00:15.00

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=10 Card=10000 Byte
s=20000)

1 0 FILTER
2 1 TABLE ACCESS (FULL) OF 'T' (TABLE) (Cost=8 Card=10000 By
tes=20000)

3 1 FAST DUAL (Cost=2 Card=1)




Statistics
----------------------------------------------------------
17 recursive calls
0 db block gets
721 consistent gets
0 physical reads
0 redo size
403627 bytes sent via SQL*Net to client
87750 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10000 rows processed

select * from t where (select f(x) from dual) = 1 order by x;

10000 rows selected.

Elapsed: 00:00:04.08

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=12 Card=10000 Byte
s=20000)

1 0 SORT (ORDER BY) (Cost=12 Card=10000 Bytes=20000)
2 1 FILTER
3 2 TABLE ACCESS (FULL) OF 'T' (TABLE) (Cost=8 Card=10000
Bytes=20000)

4 2 FAST DUAL (Cost=2 Card=1)




Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
31 consistent gets
0 physical reads
0 redo size
403627 bytes sent via SQL*Net to client
87750 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10000 rows processed

select * from (select * from t order by x) where (select f(x) from dual) = 1;


10000 rows selected.

Elapsed: 00:00:11.06

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=12 Card=10000 Byte
s=20000)

1 0 SORT (ORDER BY) (Cost=12 Card=10000 Bytes=20000)
2 1 FILTER
3 2 TABLE ACCESS (FULL) OF 'T' (TABLE) (Cost=8 Card=10000
Bytes=20000)

4 2 FAST DUAL (Cost=2 Card=1)




Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
31 consistent gets
0 physical reads
0 redo size
403627 bytes sent via SQL*Net to client
87750 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10000 rows processed</code>

Here t.x has only one value that is 1. so i expected the same perfomance in all the query hoping that the sub query (select f(x) from dual) will be executed only 1 for all the queries. But i guess iam wrong.

Can you explain whats happening here

Size of the hash table cache in Oracle 10g

Daniel Ramos Vicente, August 24, 2011 - 5:15 am UTC

Hi tom


I read at oracle magazine 20110910

"Oracle Database will assign
the number 10 to a hash value between 1
and 255 (the size of the hash table cache in
Oracle Database 10g and Oracle Database
11g currently)"


This is my test case:

(your function):
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;
/
begin
:cpu := dbms_utility.get_cpu_time;
dbms_application_info.set_client_info(0);
end;
/


--
-- Test case 1 (your case)
--

select owner, (select f(owner) from dual) f from all_objects;
91048 rows selected.


select
dbms_utility.get_cpu_time-:cpu cpu_hsecs,
userenv('client_info')
from dual;

CPU_HSECS USERENV('CLIENT_INFO')
---------- ----------------------------------------------------------------
315 493


--
--Test case 2 over a set of ordered records:
--

select owner, (select f(owner) from dual) f from all_objects order by 1;
91048 rows selected.

select
dbms_utility.get_cpu_time-:cpu cpu_hsecs,
userenv('client_info')
from dual;S
CPU_HSECS USERENV('CLIENT_INFO')
---------- ----------------------------------------------------------------
86 130



--
-- If i have 79 distinct owners:
--

select count(distinct(owner)) from all_objects;

COUNT(DISTINCT(OWNER))
----------------------
79

Why 493 and 130?
The number of distinct values is < the size of the hash table => the number of calls to the funcion should be 79. Why does it not true?



select * from v$version;

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

Regards.
Tom Kyte
August 30, 2011 - 3:44 pm UTC

article link:
http://www.oracle.com/technetwork/issue-archive/2011/11-sep/o51asktom-453438.html


Why 493 and 130?


because of hash collisions - I covered that in the article.

As for why 130 after "sorting" - there is nothing saying that the order by was done BEFORE the scalar subquery - in fact - it was probably after (we have to get the row source object to sort before we can sort it)


try:

select owner, (select f(owner) from dual) f
from (select owner, rownum r from all_objects order by 1)
where r >= 1;

The rownum has to be assigned deterministically before the order by occurs in that case - you'll feed the owners into the scalar subquery in perfect sort order..


Size of the hash table cache in Oracle 10

Daniel Ramos Vicente, September 01, 2011 - 3:47 am UTC

Whenever I read your answers I think the same thing:
this is logical
this is simple
this is even easier...
...Why did not I think?

Thanks Tom
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Security

All of the vital components for a secure database are covered in the Security guide.