| Procedure takes too long to extract using By Ref Cursor [message #65249] |
Mon, 05 July 2004 01:23  |
Krishnan
Messages: 18
Registered: October 2000
|
Junior Member |
|
|
Hi,
I have a procedure that extracts data using the UTL_FILE Package.In this I have two cursors.
A ordinary cursor and a cursor By Ref.I have a Cursor For Loop and the By Ref Cursor is initialized from within the loop and an explicit fect is done based on a column that is retrieved by the For Loop Cursor.This procedure takes around 15 MInutes to extract. THe record size is also not huge.Could any body Pls help on this.
Bye,
Krish
|
|
|
|
| Re: Procedure takes too long to extract using By Ref Cursor [message #65250 is a reply to message #65249] |
Mon, 05 July 2004 10:15  |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
Just run a trace on the session to see what's happening.
alter session set timed_statistics=true;
alter session set sql_trace=true;
-- run your code
alter session set sql_trace=false;
Get the trace file from your udump directory (select value from v$parameter where name = 'background_dump_dest';)
run it through tkprof:
tkprof dev_ora_123.trc dev_ora_123.log explain=maceya/pass@dev sys=no sort=fchela
Don't run a second trace from the same session - it'll append to the existing file.
|
|
|
|
|
|
| Re: Procedure takes too long to extract using By Ref Cursor [message #65256 is a reply to message #65249] |
Tue, 06 July 2004 00:04 |
Adrian Billington
Messages: 139
Registered: December 2002
|
Senior Member |
|
|
Without seeing the code it is difficult to advise, but from what you have said, I'm assuming the following:-
1) you have an outer ROW-BY-ROW loop. This is slowing you way down - you should be using bulk fetching ( BULK COLLECT ), available for years.
2) Ref cursors generally perform slower than static cursors. Also, by the fact that you mention ref cursor instead of normal cursor, I'm betting that you are using dynamic SQL. Which means more parsing. And I'm also guessing that you are hard-parsing every statement, but without seeing whether you are binding your variables or whether you are passing object names to create the SQL statement, it is difficult to tell.
3) UTL_FILE itself is a pretty slow interface for writing to the filesystem, so this will be taking some time also.
Maybe you could post the code ?
Regards
Adrian
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
