OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » Performance issue

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Performance issue [message #295090]

Mon, 21 January 2008 05:29

ruchika.wadhwa
Messages: 2
Registered: January 2008
Location: Noida

Junior Member

I have to retrieve 100 rows with multiple columns on my screen which are fetched by stored procedure. The columns are dynamically formed. I want to tune the procedure as it is taking lot of time. few columns of a row are inserted first. Remaining columns of that row are updated on by one through "execute immediate". But as it is done for every column X no. of rows, it is taking a lot of time. Any suggestions??

Report message to a moderator

Re: Performance issue [message #295092 is a reply to message #295090]

Mon, 21 January 2008 05:39

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

Let's look at what you have effectively asked.
"My procedure is slow, how can I make it go faster"
Have a read of the posting guidelines the come back and reformulate your question so that we can actually help.

Report message to a moderator

Re: Performance issue [message #295093 is a reply to message #295090]

Mon, 21 January 2008 05:39

vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH

Senior Member

Are you fetching / inserting / updating row by row?
Can you use BULK COLLECT / INSERT /UPDATE?

By
Vamsi

Report message to a moderator

Re: Performance issue [message #295097 is a reply to message #295093]

Mon, 21 January 2008 05:51

ruchika.wadhwa
Messages: 2
Registered: January 2008
Location: Noida

Junior Member

I was thinking to use it but its not feasible. Problem is we have to update or insert entire row in bulk commands. In my case, few columns are inserted first and the rmaining columns of the row are inserted by updation which is done for every remaining column. I am not able to use a pl/sql table coz i dont know how many columns will be inserted first and how many updated for that row later..

Report message to a moderator

Re: Performance issue [message #295206 is a reply to message #295090]

Mon, 21 January 2008 13:46

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

maybe it is time to go back to the drawing board and re-write your insert/update code. I have seen this many times and it is usually created by someone who does not understand the costs involved with this kind of process. They are usually fixated on creating "generic code" which as you have just learned is a bad idea. In particular I have never understood the need to update each column seperately. Surely on occassion this is necessary but most of the time not. Twenty Seven years in IM and I still haven't figured out how the idea of updating one column at a time got started as an acceptable way to do things.

Generic code can be a life saver in the right circumstances. Problem is, eveyone thinks their particular situation is "the perfect opportunity for some generic code" when in fact it is mearly a way to obscure what is happening in the system, and a way to make things go slow.

I suggest you think a little more about why this piece of code "needs" a generic solution, then forget about it and write the piece of code most other people would have created. What have you got to loose except a little time, and given how slow this thing is working at the moment, you have plenty of that.

Good luck, Kevin

Report message to a moderator

Re: Performance issue [message #295310 is a reply to message #295090]

Tue, 22 January 2008 01:56

santoshorcl
Messages: 24
Registered: August 2007
Location: pune

Junior Member

It difficult to say the performance issue is because of x and y. but you can solve this kind of issues by using few optimizer hints. like you can paralyze your update operation by using /*+parallel()*/ hint and insert using /*+APPEND*/ hint.

Report message to a moderator

Re: Performance issue [message #295316 is a reply to message #295310]

Tue, 22 January 2008 02:05

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Good and you don't consider that activating parallel may slow down the process?

Regards
Michel

Report message to a moderator

Re: Performance issue [message #295506 is a reply to message #295090]

Tue, 22 January 2008 08:41

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

sure he does, that why he said you can paralyze it.

google search

Definitions of paralyze on the Web:

make powerless and unable to function; "The bureaucracy paralyzes the entire operation"
cause to be paralyzed and immobile; "The poison paralyzed him"; "Fear paralyzed her"
wordnet.princeton.edu/perl/webwn

Paralysis is the complete loss of muscle function for one or more muscle groups. Major causes are stroke, trauma, poliomyelitis, amyotrophic lateral sclerosis (ALS), botulism, spina bifida, multiple sclerosis, and Guillain-Barr� syndrome. ...
en.wikipedia.org/wiki/Paralyze

Report message to a moderator

Re: Performance issue [message #295509 is a reply to message #295506]

Tue, 22 January 2008 08:43

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

sure he does, that why he said you can paralyze it.

(Didn't even notice it, I'm pretty good in reading what I want to read)

Regards
Michel

Report message to a moderator

Previous Topic:	Concurrency wait. How I can resolve.
Next Topic:	how to specify priority of active sql sessions running in the database

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jun 28 10:35:58 CDT 2024