Home » RDBMS Server » Performance Tuning » Spool from DB table to flat file slow
Spool from DB table to flat file slow [message #198306] Mon, 16 October 2006 09:09 Go to next message
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 #198309 is a reply to message #198306] Mon, 16 October 2006 09:23 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> Same spool in other enviroment (diffrent server) takes 4 seconds only
just 4 seconds to create the file in OS? Are you sure? Is the dataset same?


Did you also compare the alternatives?
You can make use of external tables.
create a stagingtable (as external table ) from the sourcetable.
This external will look like a 'flat file' in OS. No need to spool anything.

or
Try anymethods discussed here?
http://asktom.oracle.com/~tkyte/flat/index.html
Re: Spool from DB table to flat file slow [message #198313 is a reply to message #198309] Mon, 16 October 2006 09:37 Go to previous messageGo to next message
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 #198316 is a reply to message #198313] Mon, 16 October 2006 09:43 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> Website suggested by you describes about pro*C cod
Seems you missed a lot.
Other options specified in that URL is to make use UTL_FILE, which is an Oracle Built-in.
>>so if you can tell me more info from where i can get about external table.Please let me know.
Search the forum/google for "oracle external tables" / dig into documentation.
A good demonstration is here ( but for a different purpose. Treating a OS file as an Oracle table. Same concepts will apply to you also.)
http://www.dbazine.com/oracle/or-articles/still1
Re: Spool from DB table to flat file slow [message #198379 is a reply to message #198309] Mon, 16 October 2006 21:54 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous messageGo to next message
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 #198461 is a reply to message #198440] Tue, 17 October 2006 04:43 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Thanks Ross.
>> non-text Data Pump format. I could be wrong...
You are right. I was wrong and talking about something different.
Re: Spool from DB table to flat file slow [message #198463 is a reply to message #198440] Tue, 17 October 2006 04:45 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> @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.
Again,
search this forum for HWM. There are too many links.

[Updated on: Tue, 17 October 2006 04:46]

Report message to a moderator

Re: Spool from DB table to flat file slow [message #198512 is a reply to message #198463] Tue, 17 October 2006 07:54 Go to previous message
rleishman
Messages: 3724
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
Previous Topic: datapump parallel
Next Topic: Basic question in PT
Goto Forum:
  


Current Time: Mon Dec 05 11:14:06 CST 2016

Total time taken to generate the page: 0.09858 seconds