Dear all,
One of our jobs retrieves data from tables and writes it to files. This job was running for around 15 minutes for the past 8 months. Now, this job is runnig for 45-50 minutes. I checked with the DBA's and found no issues with database. The time taken by to job to write to the file is considerably long.
Is there any reason why this issue should occur suddenly. Or is there any thing which I can look at to find why the job is taking such a long time writing to the file.
Hi,
I cant give the snapshot, but the explanation is here. It is a proC code which declares a cursor for a join between two tables:
table1: 959424 rows, table2: 1892623 rows.
The join condition is a simple where clause on the common keys. The rows fetched by the join condition are retrieved one by one and written into a file. The database session was all the time waiting for response from client.
There were no changes made to our Application server recently. There was no isses with the CPU availability. Cant figure out why it is runnig long all of a sudden(for the past one week)
is the join query that you use make use of the indexed columns
if so, check if the index had been disabled or corrupted
even if indexing is fine, check for the indexing method (that could have been changed ...)
logical logs (informix) and redo logs (oracle) availability to backup data to the disk
Apart from that what are the other DML operations that you have on the table(s)?
I assume you have some tedious working on the ProC side once the rows are fetched ? because.. one could think you could do this without using ProC and spooling the file directly from SQL*Plus (assuming you're using Oracle), avoiding the ProC overhead.
Assuming this is Oracle: I would advice you to use statspack, and take snapshots at the beginning and end of the job, so you could *clearly* see where are your real waitings. 15 minutes for writing about 1M rows is just *huge*. If you are analyzing these tables periodically (and you should), the plans could have changed -- so check that as well. Try to tune it from inside-out.. that is, tune the query, then the db, application, etc. Also, if this is written constanly, I believe having these files written to RAID0+1 or 1+0 disks with a different controller than where your DB is would also improve the performance. I believe that when you say "The database session was all the time waiting for response from client" is that you traced the sessions were the job were running to see the wait times ? If they were SQL*Net waits, there could be network latency between your ProC application and the DB.
Problem: It is virtually impossible for anyone to snap a guess on this. You need to do some more investigative work. Take your Pro*C SQL and check the execution plan in sqlplus or some other utility or turn tracing on while the application is running and run a TKPROF report. This will give you the answer. Your DBAs should be able to assist you with explain plan, tracing, and tkprof. Look for how the two tables are joined (in the plan). Make sure Oracle is using an index, which it probably isn't. If this is the case, make sure the index is valid and have the DBAs generate fresh statistics. If your problem doesn't go away, post the explain plan (preferably the tkprof report) and the problem should present itself. Database structural problems can cause problems but it's not likely to be the case here.
Problem: ProC isn't optimized, I'll give you that *but it's C code, just like sqlplus was written in. I'm going out on a limb here but I would imagine that if the OP's process was running in 15 minutes with 1mm rows joining 1.8mm rows, without changing anything from the application side...then...Pro*C's poor C optimization is affecting the result set's performance. The same poorly performing SQL is going to run just as poor in sqlplus. :rolleyes: