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  |
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  |
Frank
Messages: 7901
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 |
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 #205610 is a reply to message #205608] |
Sun, 26 November 2006 15:18 |
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 #205666 is a reply to message #205657] |
Mon, 27 November 2006 00:47 |
rleishman
Messages: 3728
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 #205755 is a reply to message #205743] |
Mon, 27 November 2006 06:48 |
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 #205921 is a reply to message #205888] |
Tue, 28 November 2006 02:44 |
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 |
rleishman
Messages: 3728
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 |
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
|
|
|
|
|
|
Goto Forum:
Current Time: Wed May 01 21:31:28 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
