Home » SQL & PL/SQL » SQL & PL/SQL » Statement execution speed depends on single statement or stored procedure? (Oracle10g, Solaris 10)
Statement execution speed depends on single statement or stored procedure? [message #403334] Thu, 14 May 2009 21:26 Go to next message
bbmonster
Messages: 47
Registered: June 2007
Location: UB
Member

Dear all,

I have a problem with executing statements in my stored procedure.
When I executing the stored procedure it takes long time to complete. But executing the update statements in TOAD sql editor one by one it executes instantly.

Can somebody help me how to solve this problem? I need these update statements in stored procedure.


Thanks in advance,

Have a nice day

bbmonster


[Updated on: Thu, 14 May 2009 21:31]

Report message to a moderator

Re: Statement execution speed depends on single statement or stored procedure? [message #403335 is a reply to message #403334] Thu, 14 May 2009 22:07 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

You have table & DDL. We don't
You have data & DML. We don't.
You have code. We don't.

You have ALL the information & can't debug the problem.
Why do you expect others, who have NO details, to solve your mystery?


SQL> ALTER SESSION SET SQL_TRACE=TRUE
SQL> EXEC SLOW_PROCEDURE;
SQL> EXIT

now run trace file through tkprof to see where time is being spent
Re: Statement execution speed depends on single statement or stored procedure? [message #403486 is a reply to message #403335] Fri, 15 May 2009 16:21 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I expect you will provide the details BlackSwan has asked for.

In the mean time, lets start with three reasons why this can happen.

1a) when you execute a sql statement in toad that returns data, it does not normally return all data, but instead returns a subset of the data (say 20 rows) then stops and waits for you to ask for the next set of data.

1b) when you execute the same sql statement in a procedure it usually fetches all rows not just the first set. So... you may be comparing the retrieval of 20 rows against 200,000 rows (you get the idea).

2a) when you execute a sql statment from toad the optimizer uses the default settings of our connection meaning that if your database is set for first_rows, that is how the query plan is created.

2b) when you execute the same sql from a procedure, the optimizer ingores that database setting and nnormally optimizes the sql using all_rows hint (at least it does for the releases I use). Why? read#1b above.

3a) when you execute a sql statement from toad it is one statement so fetching is done by array size aka. set processing is done (this is related to 1a above)

3b) when you execute the same sql statement from a procedure, programmers get stupid for some reason and the put the statement in a cursor or for loop and loop through the data one row at a time. As Tom Kyte says, row by row = slow by slow.

OK so that is some food for thought.

First get BlackSwan what was asked for.
Second, think about the above and tell us if one of these three applies to you, or if it is something else.

Good luck, Kevin
Previous Topic: String and Numeric Manipulation
Next Topic: query problem
Goto Forum:
  


Current Time: Sun Dec 04 19:09:54 CST 2016

Total time taken to generate the page: 0.06064 seconds