Home » RDBMS Server » Performance Tuning » Procedure takes too long to extract using By Ref Cursor
Procedure takes too long to extract using By Ref Cursor [message #65249] Mon, 05 July 2004 01:23 Go to next message
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 Go to previous messageGo to next message
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 #65251 is a reply to message #65249] Mon, 05 July 2004 11:16 Go to previous messageGo to next message
croK
Messages: 170
Registered: April 2002
Senior Member
Does your query take the same time when executing from within SQLPlus?.
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 Go to previous message
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
Previous Topic: What is Faster? Bulk Inserts/or Direct Path Insert?
Next Topic: NOLOGGING, APPEND, PARALLEL
Goto Forum:
  


Current Time: Thu Mar 28 19:43:58 CDT 2024