| INTERNAL_FUNCTION in execution plan [message #427425] |
Thu, 22 October 2009 09:49  |
 |
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 #427429 is a reply to message #427427] |
Thu, 22 October 2009 10:26  |
|
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
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 #427574 is a reply to message #427425] |
Fri, 23 October 2009 05:19 |
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 |
|
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
