OraFAQ Forum: Performance Tuning » Need Help In Performance Tuning

Home » RDBMS Server » Performance Tuning » Need Help In Performance Tuning (10g)

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

Need Help In Performance Tuning [message #591378]

Sat, 27 July 2013 10:39

developer12
Messages: 88
Registered: July 2013

Member

Hi,
I have written the code as attached in the text file.
NOTE: I need to compare column-wise data of 8 historic tables in 2 DB's- B&C and have to do the development in DB-A.
What basically is required, that firstly i need to identify the securities which are less than equal to 90days old and then retrieve the data for those securities from 8 historic tables from database B. I have firstly identified the securities and then inserted them into a temporary table-SPOKE_SECURITIES in database A. After that I have read data from database B for those particular securities as in SPOKE_SECURITIES table and inserted them into another table temp table in database A. The same has been done 8 times for 8 historic security tables. After that I have written another procedure to read from a control table(in DB-A) of the columns to be compared in the 8 tables and then compared the column data across 8 temp tables created in DB-A and 8 historic tables in DB-C. Once compared all the data which are matching or NOT columnwise are inserted into another table in DB-A. However, as the historic tables across DB-B&C have huge amount of data around 10000rows each it the DB procedure is freezing when run. Can anyone help me in fine tuning the package with cursors(while inserting data maybe) as I am not at all comfortable with cursors and this work needs to be completed urgently. Also any other suggestions or help are welcome.
Thanks.

Attachment: code.txt
(Size: 7.98KB, Downloaded 2547 times)

Report message to a moderator

Re: Need Help In Performance Tuning [message #591379 is a reply to message #591378]

Sat, 27 July 2013 11:04

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

since we don't have your tables or data, we can not run, test, debug or improve posted code.

Report message to a moderator

Re: Need Help In Performance Tuning [message #591380 is a reply to message #591378]

Sat, 27 July 2013 12:01

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Remove all WHEN OTHERS.

Format your code.
If you don't know how to do it, learn it using SQL Formatter.

Regards
Michel

Report message to a moderator

Re: Need Help In Performance Tuning [message #591381 is a reply to message #591378]

Sat, 27 July 2013 12:15

John Watson
Messages: 8963
Registered: January 2010
Location: Global Village

Senior Member

Apart from following SB's request:
First, you must format your code properly. I do not understand how anyone can debug code that looks like yours! If you don't have a standard for this, you can always use this formatter,
http://www.dpriver.com/pp/sqlformat.htm
Second, not a tecnical issue, but you should correct the spelling of RETREIVE or you wll confuse everyone.
Third, if you believe that "temporary" tables are necessary (they are a very SQL Server concept) then you should be using Global Temporary Tables (which are an Oracle concept),
http://docs.oracle.com/cd/E16655_01/server.121/e15858/tgsql_statscon.htm#sthref523
your procedure as written will fall apart if more than one session runs the code.
Fourth, are you sure that you need those temporary tables? Can it not be done with, for instance, views?
Fifth, You do do need to find out why it is "freezing". You have only 10000 rows in each table, that is such a tiny amount of data that no matter how poor the code is, it should execute pretty quickly. Running each query wih timing on would be a good start.

Report message to a moderator

Previous Topic:	Cannot collect Top Frequency Histogram on column
Next Topic:	Please tune this query.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 23 15:09:23 CST 2025