| Spool from DB table to flat file slow [message #198306] |
Mon, 16 October 2006 09:09  |
yogen
Messages: 39
Registered: October 2006
Location: UK
|
Member |
|
|
Hi Gurus,
I am facing a problem while taking spool of a oracle table (Oracle 9i) on a unix (5.9 Generic_112233-12 sun4u sparc SUNW,Sun-Fire-880) server. table is having about 90,000 records and it takes nearly 7 mins to spool the data.Select statement in the query is very fast and it takes hardly 2-3 secs to select the data.Here is my query i am using to generate the file.
echo Processing table : ${TABLE}
# Creating dump control file : careful with EOF sign. The placement is critical
cat > _dumpcontrol.sql << !EOF
set heading off
set tab off
set linesize 32000
set pagesize 0
set feedback off
SET ARRAYSIZE 5000
SET trimspool on
SET TERMOUT OFF
SPOOL ${FILENAME}
select * from ${TABLE};
SPOOL OFF
SET TERMOUT ON
exit
!EOF
if [ -e ${FILENAME} ]; then rm ${FILENAME}; fi
sqlplus -s ${DBname} @_dumpcontrol.sql > ${FILENAME}
please help me to make it fast.Same spool in other enviroment (diffrent server)
takes 4 seconds only.Only diffrence i can see in both the server is of load.In my production server load is load average: 1.1, 0.95, 0.99 but on the server where query is fast it is .35,.3,.34 .But i am not sure if it is the only reason.
Please give me some suggestion to make it fast.
[Updated on: Mon, 16 October 2006 09:30] Report message to a moderator |
|
|
|
|
|
| Re: Spool from DB table to flat file slow [message #198313 is a reply to message #198309] |
Mon, 16 October 2006 09:37  |
yogen
Messages: 39
Registered: October 2006
Location: UK
|
Member |
|
|
yes it takes only 4 seconds as it's spooling only..and that is my only surprise why it's taking 6-7 minutes on other server.
And the use of flat file is that some other processes will be using those files so i don't know how this external table will look like.
Website suggested by you describes about pro*C code that is not available to me and no other professional tool also i can use as the client will have to pay extra for that.
so if you can tell me more info from where i can get about external table.Please let me know.And one more thing i have to unload data to OS and not from OS to DB.
Thanks a lot.
[Updated on: Mon, 16 October 2006 09:39] Report message to a moderator |
|
|
|
|
|
| Re: Spool from DB table to flat file slow [message #198379 is a reply to message #198309] |
Mon, 16 October 2006 21:54 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
| Mahesh Rajendran wrote on Tue, 17 October 2006 00:23 | You can make use of external tables.
|
Are you sure Mahesh? I though EOTs could only unload data (as opposed to upload) in the non-text Data Pump format. I could be wrong...
@yogen, you have not clearly established whether it is the data retrieval or the file write that is slow.
Try it without the SPOOL statement. The results won't go to disk. If its still slow, you have a data retrieval problem - probably HWM (do a search).
If its still slow, then you have a disk or network problem. You will have to get the System Admin and Network Admin involved to trace/monitor the job as it is running.
Ross Leishman
|
|
|
|
| Re: Spool from DB table to flat file slow [message #198440 is a reply to message #198379] |
Tue, 17 October 2006 03:35 |
yogen
Messages: 39
Registered: October 2006
Location: UK
|
Member |
|
|
Here is the results of my query.
this is the result when i am spooling the result in the file.I mean a file is created and saved in the Disk with required data.
">time fp_copypstfile_my
Processing table : sdb_planstarttimedata_frmfp_v
real 6m29.74s----time taken by the process.
user 6m21.30s
sys 0m2.09s"
same processing if i do in sqlplus but take the spool on the screen(I mean Data retrival only ) it takes time
"
90573 rows selected.
Elapsed: 00:02:17.96
"
so as per my understanding it's taking time both in data retrival as well as writing to the file.
@Ross if you don't mind could you pleas tell me what do you mean by 'HWM' as i run a search on google but didn't get anything relevant.
|
|
|
|
|
|
|
|
| Re: Spool from DB table to flat file slow [message #198512 is a reply to message #198463] |
Tue, 17 October 2006 07:54 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
HWM is not your problem. Research it if you are interested, but it won't help you. Spooling to the screen is abysmally slow, so if it is faster than spooling to disk, you have a serious problem on the disk or the network.
I'm not an expert in either of these fields, but in my experience, this would be almost impossible for us to solve remotely. At this stage you have sufficient proof with your above example to call in the Network and System administrators.
Before you do though - connect locally and try spooling to a local disk on the database server. It should be super-speedy. It will serve as extra proof when you raise this with the admins.
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
