Home » RDBMS Server » Performance Tuning » Need Help In Performance Tuning (10g)
Need Help In Performance Tuning [message #591378] Sat, 27 July 2013 10:39 Go to next message
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 1986 times)
Re: Need Help In Performance Tuning [message #591379 is a reply to message #591378] Sat, 27 July 2013 11:04 Go to previous messageGo to next message
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.
Re: Need Help In Performance Tuning [message #591380 is a reply to message #591378] Sat, 27 July 2013 12:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Need Help In Performance Tuning [message #591381 is a reply to message #591378] Sat, 27 July 2013 12:15 Go to previous message
John Watson
Messages: 8922
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.
Previous Topic: Cannot collect Top Frequency Histogram on column
Next Topic: Please tune this query.
Goto Forum:
  


Current Time: Thu Mar 28 20:46:18 CDT 2024