Visit Our UNIX and Linux User Community


reg files


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting reg files
# 1  
Old 02-09-2006
reg files

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.

Regards,
Ranjith
# 2  
Old 02-09-2006
Can we hav the sample of u r job u r using
# 3  
Old 02-09-2006
join between two tables

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)
# 4  
Old 02-09-2006
check for these,

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)?
# 5  
Old 02-09-2006
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.
# 6  
Old 02-09-2006
Quote:
Originally Posted by ranj@chn
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.
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.

Quote:
Originally Posted by fidodido
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.
Problem: Pro*C 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. Smilie

Previous Thread | Next Thread
Test Your Knowledge in Computers #99
Difficulty: Easy
Unix has a flat file structure which allows easier access and maintenance of data.
True or False?

10 More Discussions You Might Find Interesting

1. AIX

reg DS_LVZ

HI in a vg i want to display the lv name & whether the LV is enabled with DS_LVZ parameter? I used #lsvg -o | grep vgname | lsvg -il this gives the output of lv's in the vg. buti treid with lsvg -o | grep vgname| lsvg -il | egrep "LOGICAL VOLUME|DS_LVZ" but No... (5 Replies)
Discussion started by: balumurugesh
5 Replies

2. Solaris

doubt reg time stamp in files.

I copied a file from one host to another using sftp. But after copying the time stamp is not updating . Even though I checked the permission, it looks good. I copied the same file to some temporary location, there it updating the time stamp. Anyone have any idea on this (6 Replies)
Discussion started by: rogerben
6 Replies

3. Solaris

Reg: ZONES

HI Friends, What is the min. Requirement to install Solaris ZONEs, i am using INTEL PC at home and i allready installed Solaris 10 can i configure ZONES in it, and i want to know the basic information of ZONES. Thanks in Advance. (3 Replies)
Discussion started by: kurva
3 Replies

4. UNIX for Dummies Questions & Answers

reg chown

hi i wrote a script to run 'C' executable which will create a new file, after that util is completed, i have to change the file ownership to some other user. for that i used "chown" for changing the file permission in Korn script :confused:but it is throwing error is "operation... (2 Replies)
Discussion started by: ilayans
2 Replies

5. Shell Programming and Scripting

Reg expression For

HI system.sysUpTime.0 : Timeticks: (1519411311) 175 days, 20:35:13.11 From the above output i need only 175days in a perl script.. Please Help (2 Replies)
Discussion started by: Harikrishna
2 Replies

6. Shell Programming and Scripting

need a help reg -d in shell

hi, I am using this to get previous month `date -d"1 month ago" "+%m"` But will it work for january?..will it return 12? Please advice. (2 Replies)
Discussion started by: vanathi
2 Replies

7. Shell Programming and Scripting

Reg: Gzip

Hi , I want gzip a folder te55 which has got 3 files test1.test2,test3 the name of the gzipped folder should be te55.gz with the 3 files as test1,test2,test3 itself... Is it possible... thanks in advance sam (5 Replies)
Discussion started by: sam99
5 Replies

8. UNIX for Dummies Questions & Answers

Reg: delete older files from ftp

Hi, I want to delete older files from ftp server. (files which are more than 5 days old). Please advice Thanks , sam (3 Replies)
Discussion started by: sam99
3 Replies

9. Shell Programming and Scripting

Reg : binary files

HI Friends, My actual requirement is to find out the binary files except txt/gif from parent and subdirectories. so,i have to write unix shell script. Any one please suggest me on this how to write script for this or any other alternate way is there to find out. Thanks & Regards, Srujana (13 Replies)
Discussion started by: srujana
13 Replies

10. UNIX for Dummies Questions & Answers

Help with Reg. Expression

I need help with this: Can any one tell me what does these below mean: 1. "\(.\).*") != '/' 2. sed 's+^\./++;s+/.*++' 3. sed "s+${f}/+ + Thanks in advance (7 Replies)
Discussion started by: moe2266
7 Replies

Featured Tech Videos