Home » RDBMS Server » Performance Tuning » Tune data coming from legacy system into Oracle DB (Oracle 11gR1)
Tune data coming from legacy system into Oracle DB [message #555223] Tue, 22 May 2012 09:25 Go to next message
vivekgupta15
Messages: 3
Registered: May 2012
Junior Member
Customer is sending data from legacy system (Source) with the help of web service which in turn calls a package lying on Oracle server (Target). Now this package is simply inserting data passed by legacy system into master staging table in Oracle database. When they started this process in Sept 2011 then 4 lack records were inserted into staging table. In Oct 11 it was 0 records (not sure why), Nov 11 it was 2 lack records, Dec 11 it was 1 lack records, in Jan 12 it was 1 lac records, Feb 12 73k records, Mar 12 0 records, Apr 12 52k records.

As we see that number of records inserted in the table got reduced with time so any ideas what could be the reason? Just wondering what should be the starting point here since web service is calling that package on the fly, how can i enable trace for that package? I cannot replicate this is Dev as this process is only working in PROD.

Any help/pointers will be appreciated!!
Re: Tune data coming from legacy system into Oracle DB [message #555225 is a reply to message #555223] Tue, 22 May 2012 09:34 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/


you could write custom LOGON trigger that enables SQL_TRACE when Web Service connects to the DB
Re: Tune data coming from legacy system into Oracle DB [message #555240 is a reply to message #555223] Tue, 22 May 2012 10:30 Go to previous messageGo to next message
Michel Cadot
Messages: 67946
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe you can also check the source of the package to see what it does.

Regards
Michel
Re: Tune data coming from legacy system into Oracle DB [message #555256 is a reply to message #555223] Tue, 22 May 2012 13:19 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
Senior Member
vivekgupta15 wrote on Tue, 22 May 2012 10:25
Customer is sending data ... Etc ...

Have you tried auditing with the customer the number of "records" sent against the number of "records" and/or rows inserted into the staging table?

[Updated on: Tue, 22 May 2012 13:26] by Moderator

Report message to a moderator

Re: Tune data coming from legacy system into Oracle DB [message #555259 is a reply to message #555256] Tue, 22 May 2012 13:51 Go to previous messageGo to next message
vivekgupta15
Messages: 3
Registered: May 2012
Junior Member
They are sending record one by one i.e. send 1 record then let that record get validated and processed using couple of sub programs on Target. Once record got processed then send message back to legacy system (web service) of success or error and now send 2nd record and so on.

As of now we have planned to divide problem into two pieces:

1) Legacy system calling web service to send data to Oracle DB.
2) Once data gets inserted in staging table (Target) then data processing and validation done by plsql code.

For 2nd part i have suggested running dbms_profiler against the plsql code to see how much time each line of code is taking. Hope that makes sense!!

For 1st part i am still thinking what to do!
Re: Tune data coming from legacy system into Oracle DB [message #555260 is a reply to message #555259] Tue, 22 May 2012 13:55 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>i have suggested running dbms_profiler against the plsql code to see how much time each line of code is taking.

Why was this NOT done prior to realizing code into Production?
Re: Tune data coming from legacy system into Oracle DB [message #555262 is a reply to message #555260] Tue, 22 May 2012 14:03 Go to previous message
vivekgupta15
Messages: 3
Registered: May 2012
Junior Member
This code was implemented some time back but then project got a full stop because of financial constraints of client. Now client gave go ahead to proceed again (seems made good money!!) and go-live is next month. While testing client faced this perf issue.
Previous Topic: Application Slow in EE but fast in XE (2 Merged)
Next Topic: Index skip scan
Goto Forum:
  


Current Time: Fri Sep 17 11:28:14 CDT 2021