Home » RDBMS Server » Performance Tuning » INTERNAL_FUNCTION in execution plan
INTERNAL_FUNCTION in execution plan [message #427425] Thu, 22 October 2009 09:49 Go to next message
McLaud
Messages: 15
Registered: August 2008
Junior Member
Hello All,
I have one sql statement, joining two tables: ENQUIRY and COUNTRY by two columns.
Also, exist one additional condition for ENQUIRY.MLE_CREATIONDATE between :p1 and :p2

When I browse the execution plan, discover that strange function INTERNAL_FUNCTION was applied to many column, includes and (MLE_CREATIONDATE), and it is one of the reason why certain index on MLE_CREATIONDATE not used.
I run SQL Tunning Advisor for this statement. One of his output was:
  The predicate "THIS_"."MLE_CREATIONDATE">=:B1 used at line ID 7 of the
  execution plan contains an implicit data type conversion on indexed column
  "MLE_CREATIONDATE". This implicit data type conversion prevents the
  optimizer from selecting indices on table "VMAS"."ENQUIRY".


So, the question is: what is the INTERNAL_FUNCTION and why it is applied on my column?
May be it is because of Oracle did not understand that :p1 and :p2 variables are date values, provided from the client side, and tries convert it to date?


SQL:
select * from ( SELECT this_.*
FROM ENQUIRY this_, 
COUNTRY country1_ 
WHERE
             this_.MLE_CNY_ADDRESSCOUNTRY=country1_.CNY_ALPHA2CODE AND
             this_.MLE_STATUS = :p0 and this_.MLE_CREATIONDATE between :p1
             and :p2 ORDER BY trim(this_.MLE_TRADINGNAME) asc nulls first )
             where rownum <= :p3


Execution Plan:
----------------------------------------------------------------------------------
| Id  | Operation              | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |         |    30 | 52140 |  2238   (2)| 00:00:27 |
|*  1 |  COUNT STOPKEY         |         |       |       |            |          |
|   2 |   VIEW                 |         |   101 |   171K|  2238   (2)| 00:00:27 |
|*  3 |    FILTER              |         |       |       |            |          |
|   4 |     SORT ORDER BY      |         |   101 | 24139 |  2238   (2)| 00:00:27 |
|*  5 |      HASH JOIN         |         |   101 | 24139 |  2237   (2)| 00:00:27 |
|   6 |       TABLE ACCESS FULL| COUNTRY |    43 |   774 |     3   (0)| 00:00:01 |
|*  7 |       TABLE ACCESS FULL| ENQUIRY |   235 | 51935 |  2233   (2)| 00:00:27 |
----------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(ROWNUM<=:P3)
   3 - filter(:P1<=:P2)
   5 - access(NLSSORT(INTERNAL_FUNCTION("THIS_"."MLE_CNY_ADDRESSCOUNTRY"),
              'nls_sort=''BINARY_CI''')=NLSSORT(INTERNAL_FUNCTION("COUNTRY1_"."CNY_ALPHA
              2CODE"),'nls_sort=''BINARY_CI'''))
   7 - filter("THIS_"."MLE_STATUS"=:P0 AND 
              INTERNAL_FUNCTION("THIS_"."MLE_CREATIONDATE")>=:P1 AND 
              INTERNAL_FUNCTION("THIS_"."MLE_CREATIONDATE")<=:P2)

Re: INTERNAL_FUNCTION in execution plan [message #427427 is a reply to message #427425] Thu, 22 October 2009 10:05 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Post Operating System (OS) name & version for DB server system.
Post results of SELECT * from v$version.

Please post top 5 - 6 lines from function/procedure
Re: INTERNAL_FUNCTION in execution plan [message #427429 is a reply to message #427427] Thu, 22 October 2009 10:26 Go to previous messageGo to next message
McLaud
Messages: 15
Registered: August 2008
Junior Member
OS: CentOs
>uname -a
Linux xxxx-xx-xxx 2.6.18-92.el5PAE #1 SMP Tue Jun 10 19:22:41 EDT 2008 i686 athlon i386 GNU/Linux



SELECT * from v$version

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

I do not use any PL/SQL procedure/function. The sql above is generated by NHibernate framework from client side.

[Updated on: Thu, 22 October 2009 10:27]

Report message to a moderator

Re: INTERNAL_FUNCTION in execution plan [message #427432 is a reply to message #427429] Thu, 22 October 2009 10:46 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
WHERE A = B
When A & B are different datatypes, then Oracle applies a conversion function such as TO_CHAR() or TO_NUMBER() to 1 variable or the other to obtain same datatype.
When any function is involved, it precludes use of index.
Re: INTERNAL_FUNCTION in execution plan [message #427433 is a reply to message #427425] Thu, 22 October 2009 10:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
INTERNAL_FUNCTION indicates a datatype conversion is made.

Regards
Michel
Re: INTERNAL_FUNCTION in execution plan [message #427435 is a reply to message #427425] Thu, 22 October 2009 10:48 Go to previous messageGo to next message
cookiemonster
Messages: 13959
Registered: September 2008
Location: Rainy Manchester
Senior Member
Never seen that one before. However a quick seach of the documentation reveals this:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14223/parpart.htm#sthref218

Seems it means it's converting the datatype.
Re: INTERNAL_FUNCTION in execution plan [message #427567 is a reply to message #427435] Fri, 23 October 2009 05:03 Go to previous messageGo to next message
McLaud
Messages: 15
Registered: August 2008
Junior Member
Hello All
I have attached two file: the trace output for one select, executed under the same user.
The only one difference in execution environment: in first case I have execute sql from PL/SQL developer, in second case execute the same sql through NHibernate framework. As you can see, the execution plans are different.
In first case the Oracle find and uses index INDX_ENQUIRY_CRDATE and CBO use it in "SINGLE TABLE ACCESS PATH" section, in second case Oracle found this index, but not analyses it in "SINGLE TABLE ACCESS PATH", also the INTERNAL_FUNCTION appears.
Any help is appreciated.
Re: INTERNAL_FUNCTION in execution plan [message #427568 is a reply to message #427567] Fri, 23 October 2009 05:03 Go to previous messageGo to next message
McLaud
Messages: 15
Registered: August 2008
Junior Member
No Message Body
Re: INTERNAL_FUNCTION in execution plan [message #427574 is a reply to message #427425] Fri, 23 October 2009 05:19 Go to previous messageGo to next message
cookiemonster
Messages: 13959
Registered: September 2008
Location: Rainy Manchester
Senior Member
They're not actually the same sql

from the hibernate trace:
"E"."MLE_CREATIONDATE">=:B1 
AND "E"."MLE_CREATIONDATE"<=:B2 
AND :B3>=:B4 
AND ("E"."MLE_INTERNATIONAL"=1 OR "E"."MLE_INTERNATIONAL"=0) 
AND ("E"."MLE_ECOMMERCE"=1 OR "E"."MLE_ECOMMERCE"=0) 
AND ("E"."MLE_STATUS"=1 OR "E"."MLE_STATUS"=0) 
AND "E"."MLE_TYPE"=0


From the pl/sql dev trace:
"E"."MLE_CREATIONDATE">=SYSDATE@! 
AND "E"."MLE_CREATIONDATE"<=SYSDATE@!+1 
AND SYSDATE@!+1>=SYSDATE@! 
AND ("E"."MLE_INTERNATIONAL"=1 OR "E"."MLE_INTERNATIONAL"=0) 
AND ("E"."MLE_ECOMMERCE"=1 OR "E"."MLE_ECOMMERCE"=0) 
AND ("E"."MLE_STATUS"=1 OR "E"."MLE_STATUS"=0) 
AND "E"."MLE_TYPE"=0


In PL/SQL developer you're using sysdate - which obviously is of date datatype.

In hibnernate you're using bind variables which'll be of char type.

Hence the difference.

To_date your bind variables.
Re: INTERNAL_FUNCTION in execution plan [message #427977 is a reply to message #427574] Mon, 26 October 2009 10:50 Go to previous message
McLaud
Messages: 15
Registered: August 2008
Junior Member
Hello All,
I have successfully solved problem with INTERNAL_FUNCTION.
In true, it was a problem with implicit data type conversion.
My table has DATE column type, but then you call .SetDateTime("bind variable name", DateTime.Now) in nhibernate client application, it actually thinks that your works with TIMESTAMP(4) data type.
Is was two solution with problem: define your own NHibernate Dialect, or change your data column.
I have change DATE->TIMESTAMP(4) and now filters in execution plan display right access, without INTERNAL_FUNCTION.
But I still have some problem with CHAR (CNY_ALPHA2CODE) and Varchar2 (MLE_LEGALNAME) datatype.
Seems, that oracle put NLSSORT function over my column and index cannot be used.
NLSSORT("THIS_"."MLE_LEGALNAME",'nls_sort=''BINARY_CI''')=NLSSORT(:P4,'nls_sort=''BINARY_CI''')

And
filter(NLSSORT("COUNTRY2_"."CNY_ALPHA2CODE",'nls_sort=''BINARY_CI''')=NLSSORT(:P3,'nls_sort=''BINARY_CI'''))


As you can see, my session and database NLS settings are different (NLS_SOR = BYNARY and BINARY_CI)
Is it possible, it is why oracle apply NLSSORT, instead of just make a join?
The attachment is trace file for this sql.
Any help and suggestion are appreciated.

select * from nls_database_parameters order by 1;
1	NLS_CALENDAR	GREGORIAN
2	NLS_CHARACTERSET	AL32UTF8
3	NLS_COMP	BINARY
4	NLS_CURRENCY	#
5	NLS_DATE_FORMAT	DD-MON-RR
6	NLS_DATE_LANGUAGE	ENGLISH
7	NLS_DUAL_CURRENCY	?
8	NLS_ISO_CURRENCY	UNITED KINGDOM
9	NLS_LANGUAGE	ENGLISH
10	NLS_LENGTH_SEMANTICS	BYTE
11	NLS_NCHAR_CHARACTERSET	AL16UTF16
12	NLS_NCHAR_CONV_EXCP	FALSE
13	NLS_NUMERIC_CHARACTERS	.,
14	NLS_RDBMS_VERSION	10.2.0.4.0
15	NLS_SORT	BINARY
16	NLS_TERRITORY	UNITED KINGDOM
17	NLS_TIME_FORMAT	HH24.MI.SSXFF
18	NLS_TIME_TZ_FORMAT	HH24.MI.SSXFF TZR
19	NLS_TIMESTAMP_FORMAT	DD-MON-RR HH24.MI.SSXFF
20	NLS_TIMESTAMP_TZ_FORMAT	DD-MON-RR HH24.MI.SSXFF TZR

select * from nls_session_parameters order by 1;
1	NLS_CALENDAR	GREGORIAN
2	NLS_COMP	ANSI
3	NLS_CURRENCY	$
4	NLS_DATE_FORMAT	DD-MON-RR
5	NLS_DATE_LANGUAGE	AMERICAN
6	NLS_DUAL_CURRENCY	$
7	NLS_ISO_CURRENCY	AMERICA
8	NLS_LANGUAGE	AMERICAN
9	NLS_LENGTH_SEMANTICS	BYTE
10	NLS_NCHAR_CONV_EXCP	FALSE
11	NLS_NUMERIC_CHARACTERS	.,
12	NLS_SORT	BINARY_CI
13	NLS_TERRITORY	AMERICA
14	NLS_TIME_FORMAT	HH.MI.SSXFF AM
15	NLS_TIME_TZ_FORMAT	HH.MI.SSXFF AM TZR
16	NLS_TIMESTAMP_FORMAT	DD-MON-RR HH.MI.SSXFF AM
17	NLS_TIMESTAMP_TZ_FORMAT	DD-MON-RR HH.MI.SSXFF AM TZR

Previous Topic: Simple select is not executed
Next Topic: INDEX_STATS.HEIGHT > 3
Goto Forum:
  


Current Time: Mon Dec 02 07:28:49 CST 2024