NVL Function Internal Behaviour in Oracle for Scalar Calls.

During a performance analysis, identified a NVL Scalar Function calls was eating up lot of SQL time.
Scalar function was like below,

NVL
(
A_Table_Column,
(SELECT COUNT(*) FROM TABLE_B WHERE <>)
)

If we check Scalar Columns, we are ideally performing below task using NVL oracle Functions.

IF A_Table_Column is NULL Then
Check COUNT in TABLE_B
Else
Show A_Table_Column

So ideally, we should invoke ELSE part only when A_Table_Column is NULL, as per basic understanding.
But let see, how internally Oracle is behaving for NVL Functions and whether inner NVL expression is invoke for not null value?

Let start with field works, will create a dummy table.

CREATE TABLE TEST_PERF_NVL
AS
SELECT * FROM DBA_OBJECTS WHERE OWNER = 'SYS'
AND DATA_OBJECT_ID IS NOT NULL;

INSERT INTO TEST_PERF_NVL
(SELECT * FROM DBA_OBJECTS WHERE OWNER = 'SYS'
AND DATA_OBJECT_ID IS NULL AND ROWNUM < 10);

let check NULL Count for DATA_OBJECT_ID column in newly created table TEST_PERF_NVL.

Select Count(Case When Data_Object_Id Is Null Then 1 Else Null End) Data_Object_Id_Null,
count(Data_Object_Id) data_object_id_NOT_NULL
from Test_Perf_Nvl;

Output :
DATA_OBJECT_ID_NULL DATA_OBJECT_ID_NOT_NULL
------------------- -----------------------
9 3267

Only 9 Values are NULL for DATA_OBJECT_ID column.
Hence assuming only 9 times, inner expression of NVL should be invoke.

To conduct proper POC had implemented a function as below.

Function return normal count and also count how many times it has being invoked using Dbms_Application_Info.Read_Client_Info and Dbms_Application_Info.Set_Client_Info.

CREATE OR REPLACE
FUNCTION FUNC_PERF_1
RETURN NUMBER
IS
COUNT1 NUMBER;
COUNT2 NUMBER;
BEGIN
SELECT COUNT(*) INTO COUNT1 FROM TEST_PERF_NVL
WHERE DATA_OBJECT_ID IS NULL;
DBMS_APPLICATION_INFO.READ_CLIENT_INFO(COUNT2); -- Read context to increment counter
COUNT2 := COUNT2 + 1;
DBMS_APPLICATION_INFO.SET_CLIENT_INFO(COUNT2); -- Set context after increment of counter.
RETURN COUNT1;
END;

Now let’s try to run below query
Before running same let set application context to 0, to count number of time function as inner expression for NVL is getting invoke.

Exec Dbms_Application_Info.Set_Client_Info(0);

SELECT COUNT(NVL(DATA_OBJECT_ID,FUNC_PERF_1))
FROM TEST_PERF_NVL; --Calling function having application context set within NVL

Output :
3276

Now let see how many times function actually got invoked.

SET serveroutput ON;
DECLARE
COUNT1 NUMBER;
BEGIN
Dbms_Application_Info.Read_Client_Info(Count1);
Dbms_Output.Put_Line('FUNCTION INVOKED : ' || Count1);
End;

Output :
FUNCTION INVOKED : 3276.

That’s unusual and not at all required and out of 3276 Rows only 9 has DATA_OBJECT_ID as NULL.
So in Ideal case it should have been invoked only 9 times.

Key rule of performance, Avoid unneeded runs!
For NVL function oracle internally also invoked underlying inner expression and in our case functions even if it is not needed(As first expression is not null).
Thats the way it behave.

Let twist SQL with CASE expression as below.

SELECT COUNT
(
CASE WHEN DATA_OBJECT_ID IS NULL THEN FUNC_PERF_1 ELSE DATA_OBJECT_ID
END
)
FROM TEST_PERF_NVL;

Before running above SQL, resetting application context

Exec Dbms_Application_Info.Set_Client_Info(0);

Post running sql, rechecking function calls for sql.

SET serveroutput ON;
DECLARE
COUNT1 NUMBER;
BEGIN
Dbms_Application_Info.Read_Client_Info(Count1);
Dbms_Output.Put_Line('FUNCTION INVOKED : ' || Count1);
End;

Output:
FUNCTION INVOKED: 9

For above case, function was called only Nine times, that what is ideal and optimal calls.
As recommendations, suggested code changes as elaborated.

Execution Stats for SQL with NVL and CASE

Executions Phy IO Log IO Rows PLAN_HASH_VALUE SQL_ID
---------- ------ ---------- ----- --------------- -------------
1 0 584 1 479829861 7a5637cbanghr --CASE
1 0 173735 1 479829861 8dq78cbgfnw9w --NVL

Logical read for CASE expression is minimal as compared to NVL.
FOR NVL function, we need to be wise when using some other scalar calls as it would get invoked irrespective whether values is NULL or Not.

We can also use coalesce, instead of NVL for such cases.

Pls note:
All example were tested on Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production.

About Deepak Mahto

Having 8+ Years of relevant/exciting/fruitful/challenging/learning in Oracle Technology. Currently working as Oracle Performance Consultant in an MNC. Hold Expertise in Performance Engineering, SQL Tuning, Database Tuning, SQL, PLSQL Development/Design Considerations and Oracle Internals. I enjoy reading on Oracle internals, posting some of my finding or observations and playing football.
This entry was posted in Oracle Internal and tagged , , , . Bookmark the permalink.

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