OraFAQ Forum: Performance Tuning » Performance tuning of PL/SQL Procedure

Home » RDBMS Server » Performance Tuning » Performance tuning of PL/SQL Procedure (Oracle 10.2.0.5)

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

Performance tuning of PL/SQL Procedure [message #494302]

Sun, 13 February 2011 01:13

Santrupta
Messages: 6
Registered: February 2011
Location: Johannesburg

Junior Member

Hi,
I want to update two fields, owner_accnt_loc and owner_accnt_name of the table eim_asset from the output of this query. (Please note, 2.7 Million records are present in this table and ALL need to be updated)

select b.par_accnt_name,b.par_accnt_loc from eim_account b,eim_asset a
where a.ast_bill_accntname = b.name and
b.accnt_type_cd = 'Billing'
AND b.par_accnt_name IS NOT NULL
AND b.par_accnt_loc IS NOT NULL

Updating the table using normal DML commands was getting cumbersome, so I tried this PL/SQL procedure. (Attached to the message).

However, this code is also taking a huge time. (3 minutes per record!!)

I am quite lost now, please could you show me a more performance friendly way to do this?

Regards,
Santrupta

Attachment: ASSET_OWNER_ASSIGN.SQL
(Size: 0.79KB, Downloaded 1318 times)

Report message to a moderator

Re: Performance tuning of PL/SQL Procedure [message #494303 is a reply to message #494302]

Sun, 13 February 2011 01:28

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

Senior Member
Account Moderator

Quote:

Updating the table using normal DML commands was getting cumbersome

How? PL/SQL is BAD in this case, just use a SQL statement.

Quote:

However, this code is also taking a huge time

Expected behaviour, row by row is slow by slow.
In addition, your procedure is wrong as it updates ALL rows at each loop.

Regards
Michel

Report message to a moderator

Re: Performance tuning of PL/SQL Procedure [message #494304 is a reply to message #494303]

Sun, 13 February 2011 01:42

Santrupta
Messages: 6
Registered: February 2011
Location: Johannesburg

Junior Member

Hi Michel,
Thanks for your reply.

Quote:

In addition, your procedure is wrong as it updates ALL rows at each loop.

Thanks for this, I was dumb enough not to look at it that way.

Quote:

How? PL/SQL is BAD in this case, just use a SQL statement.

Even though I used IS NOT NULL in the where clause of the select query I provided in my first post, NULL values are still getting into the fields. Any idea how or why this would happen?

I found a couple of lines on google that suggested me to load the table in REPLACE mode. How do I do that?

Regards,
Santrupta

Report message to a moderator

Re: Performance tuning of PL/SQL Procedure [message #494305 is a reply to message #494304]

Sun, 13 February 2011 02:00

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

Best way would be to rebuild the table with CTAS.

If you don't want to do that, here is an article that compares a bunch of different update methods.

Ross Leishman

Report message to a moderator

Re: Performance tuning of PL/SQL Procedure [message #494310 is a reply to message #494304]

Sun, 13 February 2011 02:30

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

Senior Member
Account Moderator

Quote:

Even though I used IS NOT NULL in the where clause of the select query I provided in my first post, NULL values are still getting into the fields. Any idea how or why this would happen?

As you didn't post your original statement, we can't know what is wrong.

Regards
Michel

Report message to a moderator

Re: Performance tuning of PL/SQL Procedure [message #494313 is a reply to message #494310]

Sun, 13 February 2011 02:54

Santrupta
Messages: 6
Registered: February 2011
Location: Johannesburg

Junior Member

Hi Michel,
The update statement is :

update eim_asset a
set owner_accnt_name = (select b.par_accnt_name from eim_account b
where a.ast_bill_accntname = b.name and
b.accnt_type_cd = 'Billing' AND b.par_accnt_name IS NOT NULL)

Report message to a moderator

Re: Performance tuning of PL/SQL Procedure [message #494316 is a reply to message #494313]

Sun, 13 February 2011 03:25

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

Senior Member
Account Moderator

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

WHERE clause is missing this is why you have NULL.

update eim_asset a
set owner_accnt_name = ( select b.par_accnt_name 
                         from eim_account b
                         where a.ast_bill_accntname = b.name 
                           and b.accnt_type_cd = 'Billing' 
                           AND b.par_accnt_name IS NOT NULL ) 
where exists ( select null
               from eim_account b
               where a.ast_bill_accntname = b.name 
                 and b.accnt_type_cd = 'Billing' 
                 AND b.par_accnt_name IS NOT NULL )

MERGE might be a better choice in this case.

Regards
Michel

[Updated on: Sun, 13 February 2011 03:26]

Report message to a moderator

Re: Performance tuning of PL/SQL Procedure [message #494903 is a reply to message #494316]

Thu, 17 February 2011 08:17

Santrupta
Messages: 6
Registered: February 2011
Location: Johannesburg

Junior Member

Hi Michel,
Thanks a lot.

Can you please help me with one last query on this?

Can we use a WHERE or AND clause for the UPDATE query after the WHERE EXISTS?

I am trying this but does not work.

update eim_asset a
set owner_accnt_name = ( select b.par_accnt_name 
                         from eim_account b
                         where a.ast_bill_accntname = b.name 
                           and b.accnt_type_cd = 'Billing' 
                           AND b.par_accnt_name IS NOT NULL ) 
where exists ( select null
               from eim_account b
               where a.ast_bill_accntname = b.name 
                 and b.accnt_type_cd = 'Billing' 
                 AND b.par_accnt_name IS NOT NULL ) and if_row_batch_num between 1 and 909

Regards,
Santrupta

Report message to a moderator

Re: Performance tuning of PL/SQL Procedure [message #494911 is a reply to message #494903]

Thu, 17 February 2011 08:35

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

Senior Member
Account Moderator

Yes you can if what you want to add is valid, I don't know what is your "if_row_batch_num".

Regards
Michel

Report message to a moderator

Re: Performance tuning of PL/SQL Procedure [message #494949 is a reply to message #494911]

Thu, 17 February 2011 09:15

Santrupta
Messages: 6
Registered: February 2011
Location: Johannesburg

Junior Member

Hi Michel,
We have batch loading. So, the if_row_batch_num field is the batch number.

I hope I answered your question.

Regards,
Santrupta

Report message to a moderator

Re: Performance tuning of PL/SQL Procedure [message #494950 is a reply to message #494949]

Thu, 17 February 2011 09:26

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

Is it a column? In which table?
When you say it doesn't work, what exactly do you mean? Are you getting an error? The wrong results?

Remember - we know nothing about your tables and data except what you tell us. We can not help you further without more information.

Report message to a moderator

Previous Topic:	Performance issue in jsp application
Next Topic:	full table scan

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 11:50:10 CDT 2024