| Query Taking too much time [message #488811] |
Tue, 11 January 2011 04:59  |
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
|
Senior Member |
|
|
Hi
Please find extract of the tkprof file generated
(converted to lower case for readability)
select cast(multiset(select rt_cos_ctn_details_type(cun_id, cdd_id, ctn_dr_cr,
ctn_font_style_indicator, ctn_transaction_date,
ctn_transaction_description, ctn_transaction_level, ctn_transaction_type,
ctn_amount, ctn_transaction_id, ctn_cot_id, ctn_lat_id )
from
table(:b1 ) typ where typ.cun_id = :b4 and typ.ctn_cot_id = :b3 and
typ.ctn_lat_id = :b2 ) as t_cos_ctn_details_type) from dual
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 214 2411.87 2373.40 0 0 0 0
Fetch 214 164.34 161.78 0 642 0 214
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 428 2576.21 2535.18 0 642 0 214
The above statement is consuming 90% of the process time
It is the only session on that database executing at the moment
The Unix server has 2 CPUs and has 3 databases
There isn't a single active session on the other databases for long time
executing top command shows 94% cpu used by the above session
In this situation and where we can't see consistent reads, disk reads can we say all the issue is caused by 'CAST' function?
The same statement when executed on other servers worked fine in the past
The trace file is pretty huge
following is the extract of one of its iteration of the statement from trace file
BINDS #42:
bind 0: dty=122 mxl=4000(4000) mal=00 scl=00 pre=00 oacflg=00 oacfl2=1 size=4000 offset=0
bfp=9fffffffbf490cc0 bln=4000 avl=32842 flg=15
value=
Dump of memory from 0x9FFFFFFFBF490CC0 to 0x9FFFFFFFBF498D0A
9FFFFFFFBF490CC0 60000000 0024E310 9FFFFFFF BF490CD0 [`....$.......I..]
9FFFFFFFBF490CD0 9FFFFFFF BF04EFC0 60000000 00043758 [........`.....7X]
........................................................................
......................................................................
9FFFFFFFBF498D00 07786D08 11010101 0C43424C [.xm......CBL]
bind 1: dty=2 mxl=22(21) mal=00 scl=00 pre=00 oacflg=03 oacfl2=1 size=72 offset=0
bfp=9fffffffbf6dfee0 bln=22 avl=05 flg=05
value=10264166
bind 2: dty=2 mxl=22(21) mal=00 scl=00 pre=00 oacflg=03 oacfl2=1 size=0 offset=24
bfp=9fffffffbf6dfef8 bln=22 avl=04 flg=01
value=684289
bind 3: dty=2 mxl=22(21) mal=00 scl=00 pre=00 oacflg=03 oacfl2=1 size=0 offset=48
bfp=9fffffffbf6dff10 bln=22 avl=04 flg=01
value=384135
EXEC #42:c=11480000,e=11300386,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=11107754299304
FETCH #42:c=770000,e=751714,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=11107755051053
and surprisingly the wait event is showing 'db file sequential read' all the time which is not appearing in the trace generated for almost 45 minutes
Please suggest
Regards,
OraKaran
|
|
|
|
|
|
| Re: Query Taking too much time [message #488843 is a reply to message #488839] |
Tue, 11 January 2011 09:32  |
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
|
Senior Member |
|
|
Hello BlackSwan
I agree it need details to suggest a solution
In this case the following query is part of a big PL/SQL, also table in the query itself is a variable and also the query uses record type object. It is difficult to provide all these details in this case
My question is :
If the "query", "disk" and "current"- all counts are at such a low volume and CPU time consumed is high can we say fetching the data itself hasn't impacted but it is the conversion of the data itself which is making it slow
I checked with 'glance' and found there wasn't much I/O but CPU were too busy
Regards,
OraKaran
|
|
|
|
|
|
| Re: Query Taking too much time [message #488845 is a reply to message #488843] |
Tue, 11 January 2011 09:41 |
cookiemonster
Messages: 13921
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
|
As far as I can tell the only actual database table involved in your query is dual - everything else is object types and variables. You aren't fetching any data with this query (in the sense of reading data from tables), you're taking data from the PL/SQL engine and presenting it to the SQL engine. Hence the total mismatch between cpu and the other stats.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
