Importance of DETERMINISTIC Function…in continuation to my blog on CBC Latches

This is in continuation to my previous blog on CBC Latch Contention. While writing about the customer issue, I missed out one metrics that is worth mentioning. The Top Queries posted in this blog were mainly from Business Object Reports, which generate the Business Intelligence Data for a branch. When I checked the metrics for one report, with Original Implementation, with 1 Execution of the report, the number of Execution of the query in the function, on S_BRANCH, were around 0.3 Million and the I/O’s for the Report Query was around 14 Million per execution and for the query on S_BRANCH it was just 4 per execution. After Implementing the change, for a single execution of the Original Report Query, the executions of the query in the function were just 87 and the I/O’s of the Original Query dropped from 14 Million to 0.2 Million.

I received several mail responses on this issue and many of them were surprised to know about the behaviour of this Function, using a Scalar Subquery, without using a DETERMINISTIC function. The assumption, as of now, was that the combination of a function created with DETERMINISTIC clause and scalar subquery will give a better performance due to caching effect. In the solution that I provided, the function was not created with DETERMINISTIC clause and it worked absolutely fine and the benefit achieved was due to the Caching effect. This seems to be Undocumented.

For the Performance comparison with and without DETERMINISTIC clause, I recreated the functions as under :

create or replace function get_dname(i_deptno in number)
return varchar2 deterministic as
i_name varchar2(20);
begin
select /*+ ORIGINAL */ dname into i_name from dept where deptno=i_deptno;
return i_name;
end;
/
create or replace function get_dname_o(i_deptno in number)
return varchar2 deterministic as
i_name varchar2(20);
begin
select /*+ OPTIMIZED */ dname into i_name from dept where deptno=i_deptno;
return i_name;
end;
/

With this change, I executed the queries (Original Implementation and Modified) and measured the performance benefits. The performance statistics are as under :

With Original Implementation and without DETERMINISTIC FUNCTION
SQL_ID        SQL_TEXT                                           BUFFER_GETS EXECUTIONS VERSION_COUNT
------------- -------------------------------------------------- ----------- ---------- -------------
dpbdkb6u525g3 SELECT /*+ ORIGINAL */ DNAME FROM DEPT WHERE DEPTN         400        200             1
              O=:B1

With Original Implementation and with DETERMINISTIC FUNCTION
SQL_ID        SQL_TEXT                                           BUFFER_GETS EXECUTIONS VERSION_COUNT
------------- -------------------------------------------------- ----------- ---------- -------------
dpbdkb6u525g3 SELECT /*+ ORIGINAL */ DNAME FROM DEPT WHERE DEPTN          58         29             1
              O=:B1

Clearly, the performance of the DETERMINISTIC function outweighs the performance of the Original Implementation without DETERMINISTIC. The concurrency has reduced from 200 executions to 29 executions. With Scalar subquery Implementation, the performance with or without DERTERMINISTIC is same.

Advertisements

About Vivek Sharma
I am an Oracle Professional from Mumbai (India). I blog on the issues that I feel is Interesting for my readers. Some of these are my real life examples, which I hope, you would find interesting. Comments are always a welcome. The Technical Observations & Views here are my own and not necessarily those of Oracle or its affiliates. These are purely based on my understandings, learnings and resolutions of various customer issues.

2 Responses to Importance of DETERMINISTIC Function…in continuation to my blog on CBC Latches

  1. Guruswamy says:

    Hi Vivek,

    Greetings and appreciate your good work on the above topic.

    I checked the example at my end but i was not able to notice any significant change in logical IO’s by using Deterministic clause in my function.
    However by using scalary subquery i was able to see the change but that too only marginal improvement.
    I tried after flushing the shared_pool and my cursor_sharing parameter is set to force. Can you please shed some light on this further. Thanks in advance

    Like

    • Vivek says:

      Hi Guru,

      Even with cursor_sharing set to FORCE, I could see a drop in the number of executions of the inner function query. Can you check it on some other system ?

      Regards
      Vivek

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s