Home » RDBMS Server » Performance Tuning » Performance of Lower() function in query
Performance of Lower() function in query [message #205589] Sun, 26 November 2006 10:11 Go to next message
rsilvestri
Messages: 7
Registered: November 2006
Junior Member
Hi all,

I am strugling with a performance issue which is a bit weird to me.

A query with where clause
LOWER(nx.mac_address) LIKE LOWER('00028A0F3868')
performs much slower the
LOWER(nx.mac_address) LIKE '00028a0f3868'.

Any clue on the reason?

Thanks
Ruedi
Re: Performance of Lower() function in query [message #205598 is a reply to message #205589] Sun, 26 November 2006 12:01 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
That doesn't really make much sense.
Removing the lower on the left-hand side could influence the performance, if mac_address had an index on it.
'x' and lower('x') should both be treated as constants.

by the way, the LIKE doesn't do anything here. You'd better replace it with "=" for better readability of the query.
Re: Performance of Lower() function in query [message #205607 is a reply to message #205598] Sun, 26 November 2006 13:08 Go to previous messageGo to next message
rsilvestri
Messages: 7
Registered: November 2006
Junior Member
That's exactly what my understanding was before comming accross this.
Absolutelly the like doesn't make much sense in this constellation. Just I am troubleshooting an issue in a application where the constant on right of the query is entered in the GUI.

I did analyse the issue back to the query and can reproduce the issue as stated above.

Ruedi
Re: Performance of Lower() function in query [message #205608 is a reply to message #205607] Sun, 26 November 2006 13:46 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Execute both statements, trace them and use tkprof to analyze the traces.
Please post the result of the (relevant part of) tkprof here.
Re: Performance of Lower() function in query [message #205610 is a reply to message #205608] Sun, 26 November 2006 15:18 Go to previous messageGo to next message
rsilvestri
Messages: 7
Registered: November 2006
Junior Member
Hi Frank,

here's the result. Hope this is what you are asking for:

Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
ORACLE_HOME = /opt/oracle/Ora926
System name: SunOS
Node name: hsrinv02
Release: 5.8
Version: Generic_108528-27
Machine: sun4u
Instance name: INSTANCE
Redo thread mounted by this instance: 1
Oracle process number: 29
Unix process pid: 11301, image: oracle@... (TNS V1-V3)

*** SESSION ID:(42.1282) 2006-11-11 14:23:24.917
JDK14 logger could not be instantiated, so instantiating SimpleLogger !!
Check your deployment !!!!
JDK14 logger could not be instantiated, so instantiating SimpleLogger !!
Check your deployment !!!!
JDK14 logger could not be instantiated, so instantiating SimpleLogger !!
Check your deployment !!!!
11:11:2006 14:23:25:588: locale is : English
timezone is : Central European Time
user is : null
database is : cn=INSTANCE...
/opt/oracle/Ora926/admin/INSTANCE/udump/INSTANCE_ora_11301.trc
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
ORACLE_HOME = /opt/oracle/Ora926
System name: SunOS
Node name: ...
Release: 5.8
Version: Generic_108528-27
Machine: sun4u
Instance name: INSTANCE
Redo thread mounted by this instance: 1
Oracle process number: 16
Unix process pid: 11301, image: oracle@... (TNS V1-V3)

*** SESSION ID:(38.12232) 2006-11-26 22:10:25.887
JDK14 logger could not be instantiated, so instantiating SimpleLogger !!
Check your deployment !!!!
JDK14 logger could not be instantiated, so instantiating SimpleLogger !!
Check your deployment !!!!
JDK14 logger could not be instantiated, so instantiating SimpleLogger !!
Check your deployment !!!!
26:11:2006 22:10:26:570: locale is : English
timezone is : Central European Time
user is : null
database is : cn=INSTANCE,...
APPNAME mod='SQL*Plus' mh=3669949024 act='' ah=4029777240
=====================
PARSING IN CURSOR #1 len=32 dep=0 uid=1292 oct=42 lid=1292 tim=1281325444838 hv=3943786303 ad='4a9f674c'
alter session set sql_trace=true
END OF STMT
EXEC #1:c=0,e=7290,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=1281325444697
*** 2006-11-26 22:11:04.109
=====================
PARSING IN CURSOR #1 len=39 dep=0 uid=1292 oct=42 lid=1292 tim=1281357266235 hv=485942061 ad='4fff0d4c'
alter session set timed_statistics=true
END OF STMT
PARSE #1:c=10000,e=21456,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=1281357266217
EXEC #1:c=0,e=1129,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1281357267562
*** 2006-11-26 22:11:20.529
=====================
PARSING IN CURSOR #1 len=480 dep=0 uid=1292 oct=3 lid=1292 tim=1281373301408 hv=3423654953 ad='4ba8d1fc'
SELECT DISTINCT
NVL(s.name, :"SYS_B_0") xname,
p.portid,
p.port2porttype,
P.port2provisionstatus
FROM SERVICE s,
SERVICEOBJECT so,
PORT p,
NODE n,
NODE_EXT_RCPE nx
WHERE s.serviceid(+) = so.serviceobject2service
AND so.serviceobject2dimobject(+) = :"SYS_B_1"
AND so.serviceobject2object(+) = p.portid
AND p.port2node = n.nodeid
AND n.nodeid = nx.nodeid
AND p.name = :"SYS_B_2"
AND LOWER(nx.mac_address) LIKE LOWER(:"SYS_B_3")
END OF STMT
PARSE #1:c=10000,e=3949,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=0,tim=1281373301393
EXEC #1:c=0,e=225,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1281373316639
*** 2006-11-26 22:11:33.299
FETCH #1:c=8620000,e=12455459,p=46861,cr=45542,cu=0,mis=0,r=1,dep=0,og=4,tim=1281385772257
FETCH #1:c=0,e=39,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1281385773817
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT UNIQUE '
STAT #1 id=2 cnt=1 pid=1 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #1 id=3 cnt=1 pid=2 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #1 id=4 cnt=1 pid=3 pos=1 obj=0 op='HASH JOIN '
STAT #1 id=5 cnt=664727 pid=4 pos=1 obj=0 op='HASH JOIN '
STAT #1 id=6 cnt=664727 pid=5 pos=1 obj=50161 op='TABLE ACCESS FULL PORT '
STAT #1 id=7 cnt=673394 pid=5 pos=2 obj=53272 op='INDEX FAST FULL SCAN NODE_PK '
STAT #1 id=8 cnt=1 pid=4 pos=2 obj=58945 op='TABLE ACCESS BY INDEX ROWID NODE_EXT_RCPE '
STAT #1 id=9 cnt=1 pid=8 pos=1 obj=65600 op='INDEX RANGE SCAN NODE_EXT_RCPE_LOWMAC '
STAT #1 id=10 cnt=0 pid=3 pos=2 obj=50330 op='TABLE ACCESS BY INDEX ROWID SERVICEOBJECT '
STAT #1 id=11 cnt=0 pid=10 pos=1 obj=50332 op='INDEX RANGE SCAN SEROBJ_OBJ_I '
STAT #1 id=12 cnt=0 pid=2 pos=2 obj=50324 op='TABLE ACCESS BY INDEX ROWID SERVICE '
STAT #1 id=13 cnt=0 pid=12 pos=1 obj=53339 op='INDEX UNIQUE SCAN SERV_PK '
=====================
PARSING IN CURSOR #1 len=473 dep=0 uid=1292 oct=3 lid=1292 tim=1281388322924 hv=140485942 ad='4acb0084'
SELECT DISTINCT
NVL(s.name, :"SYS_B_0") xname,
p.portid,
p.port2porttype,
P.port2provisionstatus
FROM SERVICE s,
SERVICEOBJECT so,
PORT p,
NODE n,
NODE_EXT_RCPE nx
WHERE s.serviceid(+) = so.serviceobject2service
AND so.serviceobject2dimobject(+) = :"SYS_B_1"
AND so.serviceobject2object(+) = p.portid
AND p.port2node = n.nodeid
AND n.nodeid = nx.nodeid
AND p.name = :"SYS_B_2"
AND LOWER(nx.mac_address) LIKE :"SYS_B_3"
END OF STMT
PARSE #1:c=10000,e=3262,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=0,tim=1281388322911
EXEC #1:c=10000,e=302,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1281388330335
FETCH #1:c=0,e=381,p=0,cr=14,cu=0,mis=0,r=1,dep=0,og=4,tim=1281388330870
FETCH #1:c=0,e=34,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1281388331724
XCTEND rlbk=0, rd_only=1
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT UNIQUE '
STAT #1 id=2 cnt=1 pid=1 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #1 id=3 cnt=1 pid=2 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #1 id=4 cnt=1 pid=3 pos=1 obj=0 op='NESTED LOOPS '
STAT #1 id=5 cnt=1 pid=4 pos=1 obj=0 op='NESTED LOOPS '
STAT #1 id=6 cnt=1 pid=5 pos=1 obj=58945 op='TABLE ACCESS BY INDEX ROWID OBJ#(58945) '
STAT #1 id=7 cnt=1 pid=6 pos=1 obj=65600 op='INDEX RANGE SCAN OBJ#(65600) '
STAT #1 id=8 cnt=1 pid=5 pos=2 obj=53272 op='INDEX UNIQUE SCAN OBJ#(53272) '
STAT #1 id=9 cnt=1 pid=4 pos=2 obj=50161 op='TABLE ACCESS BY INDEX ROWID OBJ#(50161) '
STAT #1 id=10 cnt=5 pid=9 pos=1 obj=50162 op='INDEX RANGE SCAN OBJ#(50162) '
STAT #1 id=11 cnt=0 pid=3 pos=2 obj=50330 op='TABLE ACCESS BY INDEX ROWID OBJ#(50330) '
STAT #1 id=12 cnt=0 pid=11 pos=1 obj=50332 op='INDEX RANGE SCAN OBJ#(50332) '
STAT #1 id=13 cnt=0 pid=2 pos=2 obj=50324 op='TABLE ACCESS BY INDEX ROWID OBJ#(50324) '
STAT #1 id=14 cnt=0 pid=13 pos=1 obj=53339 op='INDEX UNIQUE SCAN OBJ#(53339) '

Thanks for your support
Ruedi

Re: Performance of Lower() function in query [message #205657 is a reply to message #205610] Mon, 27 November 2006 00:20 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Very strange. Two different plans for what should be the same query.
Moving this thread to Performance Tuning forum.
Re: Performance of Lower() function in query [message #205666 is a reply to message #205657] Mon, 27 November 2006 00:47 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
That is mysterious.

Could you also show us the Explain Plan from SQL*Plus showing the Cardinality (Rows) on each step.

The problem is possibly the CBO making silly decisions about how many rows a range scan will return. With the LOWER function, it thinks it will return a lot (hence hash joins), but with the bare bind-variable it thinks it will return very few (hence nested-loops).

Ross Leishman
Re: Performance of Lower() function in query [message #205682 is a reply to message #205666] Mon, 27 November 2006 02:00 Go to previous messageGo to next message
rsilvestri
Messages: 7
Registered: November 2006
Junior Member
Hi Ross,

since I am a newbie in this topic, could you please give me some advice how to do so?

Regards
Ruedi
Re: Performance of Lower() function in query [message #205743 is a reply to message #205682] Mon, 27 November 2006 05:15 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Teach a man to fish...

Ross Leishman
Re: Performance of Lower() function in query [message #205755 is a reply to message #205743] Mon, 27 November 2006 06:48 Go to previous messageGo to next message
rsilvestri
Messages: 7
Registered: November 2006
Junior Member
Hi Ross,

see the Explain Plan of the two different queries:
1st:
SELECT DISTINCT
	   NVL(s.name, 'no service found') xname,
	   p.portid,
	   p.port2porttype,
	   P.port2provisionstatus
  FROM SERVICE s,
  	   SERVICEOBJECT so,
	   PORT p,
	   NODE n,
	   NODE_EXT_RCPE nx
 WHERE s.serviceid(+) = so.serviceobject2service
   AND so.serviceobject2dimobject(+) = 4
   AND so.serviceobject2object(+) = p.portid
   AND p.port2node = n.nodeid
   AND n.nodeid = nx.nodeid
   AND p.name = 'Cable.1'
   AND LOWER(nx.mac_address) LIKE LOWER('0000070e32cd')Operation	

Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop

SELECT STATEMENT Optimizer Mode=CHOOSE		1  	 	15  	 	      	             	 
  SORT UNIQUE		1  	92  	15  	 	      	             	 
    NESTED LOOPS OUTER		1  	92  	13  	 	      	             	 
      NESTED LOOPS OUTER		1  	63  	11  	 	      	             	 
        NESTED LOOPS		1  	51  	8  	 	      	             	 
          NESTED LOOPS		1  	22  	5  	 	      	             	 
            TABLE ACCESS BY INDEX ROWID	CRAMER.NODE_EXT_RCPE	1  	17  	4  	 	      	             	 
              INDEX RANGE SCAN	CRAMER.NODE_EXT_RCPE_LOWMAC	1  	 	3  	 	      	             	 
            INDEX UNIQUE SCAN	CRAMER.NODE_PK	1  	5  	1  	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	CRAMER.PORT	1  	29  	3  	 	      	             	 
            INDEX RANGE SCAN	CRAMER.PORT_PC_FK_I	5  	 	2  	 	      	             	 
        TABLE ACCESS BY INDEX ROWID	CRAMER.SERVICEOBJECT	1  	12  	3  	 	      	             	 
          INDEX RANGE SCAN	CRAMER.SEROBJ_OBJ_I	2  	 	2  	 	      	             	 
      TABLE ACCESS BY INDEX ROWID	CRAMER.SERVICE	1  	29  	2  	 	      	             	 
        INDEX UNIQUE SCAN	CRAMER.SERV_PK	1  	 	1  	 	      	             	 



2nd:
SELECT DISTINCT
	   NVL(s.name, 'no service found') xname,
	   p.portid,
	   p.port2porttype,
	   P.port2provisionstatus
  FROM SERVICE s,
  	   SERVICEOBJECT so,
	   PORT p,
	   NODE n,
	   NODE_EXT_RCPE nx
 WHERE s.serviceid(+) = so.serviceobject2service
   AND so.serviceobject2dimobject(+) = 4
   AND so.serviceobject2object(+) = p.portid
   AND p.port2node = n.nodeid
   AND n.nodeid = nx.nodeid
   AND p.name = 'Cable.1'
   AND LOWER(nx.mac_address) LIKE '0000070e32cd'

Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop

SELECT STATEMENT Optimizer Mode=CHOOSE		1  	 	15  	 	      	             	 
  SORT UNIQUE		1  	92  	15  	 	      	             	 
    NESTED LOOPS OUTER		1  	92  	13  	 	      	             	 
      NESTED LOOPS OUTER		1  	63  	11  	 	      	             	 
        NESTED LOOPS		1  	51  	8  	 	      	             	 
          NESTED LOOPS		1  	22  	5  	 	      	             	 
            TABLE ACCESS BY INDEX ROWID	CRAMER.NODE_EXT_RCPE	1  	17  	4  	 	      	             	 
              INDEX RANGE SCAN	CRAMER.NODE_EXT_RCPE_LOWMAC	1  	 	3  	 	      	             	 
            INDEX UNIQUE SCAN	CRAMER.NODE_PK	1  	5  	1  	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	CRAMER.PORT	1  	29  	3  	 	      	             	 
            INDEX RANGE SCAN	CRAMER.PORT_PC_FK_I	5  	 	2  	 	      	             	 
        TABLE ACCESS BY INDEX ROWID	CRAMER.SERVICEOBJECT	1  	12  	3  	 	      	             	 
          INDEX RANGE SCAN	CRAMER.SEROBJ_OBJ_I	2  	 	2  	 	      	             	 
      TABLE ACCESS BY INDEX ROWID	CRAMER.SERVICE	1  	29  	2  	 	      	             	 
        INDEX UNIQUE SCAN	CRAMER.SERV_PK	1  	 	1  


[Added formatting: Mod]

Regards
Ruedi

[Updated on: Tue, 28 November 2006 00:50] by Moderator

Report message to a moderator

Re: Performance of Lower() function in query [message #205888 is a reply to message #205755] Tue, 28 November 2006 00:52 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Um. Your plans are the same now. You'll find that they now run at the same speed.

Welcome to Performance Tuning Very Happy

Ross Leishman
Re: Performance of Lower() function in query [message #205921 is a reply to message #205888] Tue, 28 November 2006 02:44 Go to previous messageGo to next message
rsilvestri
Messages: 7
Registered: November 2006
Junior Member
I're correct in saying the plan are the same, just when executing the two queries using sqlplus there is quite some significant difference:

LOWER(nx.mac_address) LIKE LOWER('0000070e32cd') --> needs 20 sec

LOWER(nx.mac_address) LIKE '0000070e32cd' --> needs < 1 sec

I am still strugling at the same point.

Regards
Ruedi

Re: Performance of Lower() function in query [message #206124 is a reply to message #205921] Tue, 28 November 2006 20:05 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I know this sounds hard to believe, but if those two plans you posted above are correct, then they will run in EXACTLY the same amount of time.

When it takes a different amount of time, it is using a different plan - probably the one with HASH joins shown in your earlier trace.

Try this.

ALTER SESSION SET SQL_TRACE = TRUE;
Run Explain Plan for 1st SQL
Execute 1st SQL
Run Explain Plan for 2nd SQL
Execute 2nd SQL
ALTER SESSION SET SQL_TRACE = FALSE;
Run the trace file through TK*Prof - don't just post the trace file like you did above - its too hard to read.

Post the explain plans AND the TK*Prof output here. Don't forget to enclose it all in [code] and [/code] tags to preserve indentation (once again, so we can read it).

Ross Leishman

Re: Performance of Lower() function in query [message #206806 is a reply to message #206124] Fri, 01 December 2006 10:44 Go to previous messageGo to next message
rsilvestri
Messages: 7
Registered: November 2006
Junior Member
Hi Ross,

took a while due to priority issues. Here is the result:

TKPROF: Release 9.2.0.6.0 - Production on Fri Dec 1 17:39:08 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Trace file: pcr5rm1_ora_15631.trc
Sort options: default

********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing 
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************

The following statement encountered a error during parse:

insert into plan_table (statement_id, timestamp, operation, options,object_node, object_owner, object_name, object_instance, object_
type,search_columns, id, parent_id, position, other,optimizer, cost, cardinality, bytes, other_tag, partition_start, partition_stop,
 partition_id, distribution, cpu_cost, io_cost, temp_space, access_predicates, filter_predicates ) values(:1,SYSDATE,:2,:3,:4,:5,:6,
:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,:26,:27)

Error encountered: ORA-00904
********************************************************************************

ALTER SESSION SET SQL_TRACE = TRUE


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        1      0.00       0.00          0          0          0           0

Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer goal: CHOOSE
Parsing user id: 1292  
********************************************************************************

EXPLAIN PLAN FOR
  SELECT DISTINCT NVL(s.name, 'no service found') xname, p.portid, p.port2porttype, P.port2provisionstatus
  FROM SERVICE s, SERVICEOBJECT so, PORT p, NODE n, NODE_EXT_RCPE nx
 WHERE s.serviceid(+) = so.serviceobject2service
   AND so.serviceobject2dimobject(+) = 4
   AND so.serviceobject2object(+) = p.portid
   AND p.port2node = n.nodeid
   AND n.nodeid = nx.nodeid
   AND p.name = 'Cable.1'
   AND LOWER(nx.mac_address) LIKE '0000070e32cd'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01          0          0          0           0
Execute      1      0.02       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.03       0.02          0          0          0           0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 1292  
********************************************************************************

insert into plan_table (statement_id, timestamp, operation, options,
  object_node, object_owner, object_name, object_instance, object_type,
  search_columns, id, parent_id, position, other,optimizer, cost, cardinality,
   bytes, other_tag, partition_start, partition_stop, partition_id, 
  distribution, cpu_cost, io_cost, temp_space ) 
values
(:1,SYSDATE,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,
  :20,:21,:22,:23,:24,:25)


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute     30      0.00       0.00          0          2         38          30
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       32      0.00       0.00          0          2         38          30

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 1292     (recursive depth: 1)
********************************************************************************

select o.name, u.name 
from
 sys.obj$ o, sys.user$ u where obj# = :1 and owner# = user#


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      0      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          0          0           0

Misses in library cache during parse: 1
Parsing user id: SYS   (recursive depth: 1)
********************************************************************************

  SELECT DISTINCT NVL(s.name, :"SYS_B_0") xname, p.portid, p.port2porttype, P.port2provisionstatus
  FROM SERVICE s, SERVICEOBJECT so, PORT p, NODE n, NODE_EXT_RCPE nx
 WHERE s.serviceid(+) = so.serviceobject2service
   AND so.serviceobject2dimobject(+) = :"SYS_B_1"
   AND so.serviceobject2object(+) = p.portid
   AND p.port2node = n.nodeid
   AND n.nodeid = nx.nodeid
   AND p.name = :"SYS_B_2"
   AND LOWER(nx.mac_address) LIKE :"SYS_B_3"

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.02          5         14          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.03          5         14          0           1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 1292  

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT UNIQUE 
      1   NESTED LOOPS OUTER 
      1    NESTED LOOPS OUTER 
      1     NESTED LOOPS  
      1      NESTED LOOPS  
      1       TABLE ACCESS BY INDEX ROWID NODE_EXT_RCPE 
      1        INDEX RANGE SCAN NODE_EXT_RCPE_LOWMAC (object id 65600)
      1       INDEX UNIQUE SCAN NODE_PK (object id 53272)
      1      TABLE ACCESS BY INDEX ROWID PORT 
      5       INDEX RANGE SCAN PORT_PC_FK_I (object id 50162)
      0     TABLE ACCESS BY INDEX ROWID SERVICEOBJECT 
      0      INDEX RANGE SCAN SEROBJ_OBJ_I (object id 50332)
      0    TABLE ACCESS BY INDEX ROWID SERVICE 
      0     INDEX UNIQUE SCAN SERV_PK (object id 53339)

********************************************************************************

EXPLAIN PLAN FOR
  SELECT DISTINCT NVL(s.name, 'no service found') xname, p.portid, p.port2porttype, P.port2provisionstatus
  FROM SERVICE s, SERVICEOBJECT so, PORT p, NODE n, NODE_EXT_RCPE nx
 WHERE s.serviceid(+) = so.serviceobject2service
   AND so.serviceobject2dimobject(+) = 4
   AND so.serviceobject2object(+) = p.portid
   AND p.port2node = n.nodeid
   AND n.nodeid = nx.nodeid
   AND p.name = 'Cable.1'
   AND LOWER(nx.mac_address) LIKE LOWER('0000070e32cd')

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

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 1292  
********************************************************************************

  SELECT DISTINCT NVL(s.name, :"SYS_B_0") xname, p.portid, p.port2porttype, P.port2provisionstatus
  FROM SERVICE s, SERVICEOBJECT so, PORT p, NODE n, NODE_EXT_RCPE nx
 WHERE s.serviceid(+) = so.serviceobject2service
   AND so.serviceobject2dimobject(+) = :"SYS_B_1"
   AND so.serviceobject2object(+) = p.portid
   AND p.port2node = n.nodeid
   AND n.nodeid = nx.nodeid
   AND p.name = :"SYS_B_2"
   AND LOWER(nx.mac_address) LIKE LOWER(:"SYS_B_3")

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      8.75      10.20      47398      46372          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      8.75      10.20      47398      46372          0           1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 1292  

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT UNIQUE 
      1   NESTED LOOPS OUTER 
      1    NESTED LOOPS OUTER 
      1     HASH JOIN  
 674727      HASH JOIN  
 674727       TABLE ACCESS FULL PORT 
 683444       INDEX FAST FULL SCAN NODE_PK (object id 53272)
      1      TABLE ACCESS BY INDEX ROWID NODE_EXT_RCPE 
      1       INDEX RANGE SCAN NODE_EXT_RCPE_LOWMAC (object id 65600)
      0     TABLE ACCESS BY INDEX ROWID SERVICEOBJECT 
      0      INDEX RANGE SCAN SEROBJ_OBJ_I (object id 50332)
      0    TABLE ACCESS BY INDEX ROWID SERVICE 
      0     INDEX UNIQUE SCAN SERV_PK (object id 53339)

********************************************************************************

ALTER SESSION SET SQL_TRACE = FALSE


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          0          0           0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 1292  



********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        5      0.02       0.03          0          0          0           0
Execute      6      0.03       0.02          0          0          2           0
Fetch        4      8.75      10.22      47403      46386          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       15      8.80      10.28      47403      46386          2           2

Misses in library cache during parse: 5
Misses in library cache during execute: 1


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        4      0.00       0.00          0          0          0           0
Execute     30      0.00       0.00          0          2         38          30
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       34      0.00       0.00          0          2         38          30

Misses in library cache during parse: 2

    8  user  SQL statements in session.
    2  internal SQL statements in session.
   10  SQL statements in session.
********************************************************************************
Trace file: pcr5rm1_ora_15631.trc
Trace file compatibility: 9.02.00
Sort options: default

       1  session in tracefile.
       8  user  SQL statements in trace file.
       2  internal SQL statements in trace file.
      10  SQL statements in trace file.
       8  unique SQL statements in trace file.
     186  lines in trace file.


Regards
Ruedi
Re: Performance of Lower() function in query [message #206962 is a reply to message #205589] Sat, 02 December 2006 13:12 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
At least we know now why 2 queries performs so differently.

Can you try following:
1. Exchange LIKE to " = ".
2. Run TKPROF again.

The difference may be caused by "value peeking", so you may try flushing the shared pool as well.

HTH.
Previous Topic: Timing queries
Next Topic: Analyze tables using OEM
Goto Forum:
  


Current Time: Sun Dec 04 06:19:14 CST 2016

Total time taken to generate the page: 0.14314 seconds