Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL Performance
PL/SQL Performance [message #285652] Wed, 05 December 2007 04:10 Go to next message
abuthalip
Messages: 9
Registered: May 2005
Location: India
Junior Member
Hi,
I am extracting the data from oracle. Till now we used utl_file to extract the data. now we had changed the architecture.like instead of doing the I/O using utl_file, we are doing with pro*c. but i cant see the performance difference. can anyone please explain why?

Old Architecture:
Build dynamic queries
execute the dynamic queries and write the data using utl_file.

New Architecture:
Store the dynamically built queries into table.
From Pro*c take the queries from the table and execute them and write the data into file.

Note:In both we are doing bulk fetch.

Regards,
Abu
Re: PL/SQL Performance [message #285659 is a reply to message #285652] Wed, 05 December 2007 04:19 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
Quote:

but i cant see the performance difference.


How did you measure performance?
Re: PL/SQL Performance [message #285661 is a reply to message #285652] Wed, 05 December 2007 04:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And why is this a problem?

Regards
Michel
Re: PL/SQL Performance [message #285676 is a reply to message #285659] Wed, 05 December 2007 05:00 Go to previous messageGo to next message
abuthalip
Messages: 9
Registered: May 2005
Location: India
Junior Member
We generate a log while extracting, with the start and end time of extraction.

PL/SQL Package
Start End Record Count Total Time
10:45:48 11:33:18 4598547 00:47:30

Pro*C
Start End Record Count Total Time
10:32:43 11:19:11 4598547 00:46:28

The total time includes the query execution time and writing time

Regards,
Abu
Re: PL/SQL Performance [message #285677 is a reply to message #285661] Wed, 05 December 2007 05:01 Go to previous messageGo to next message
abuthalip
Messages: 9
Registered: May 2005
Location: India
Junior Member
To improve the extraction performance, we switched to pro*c(like handling all file i/o thru pro*c)
Re: PL/SQL Performance [message #285678 is a reply to message #285676] Wed, 05 December 2007 05:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And so?

Regards
Michel
Re: PL/SQL Performance [message #285679 is a reply to message #285677] Wed, 05 December 2007 05:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you don't post both codes how can we know what is good or bad?

One tip: do you use array fetch?

Regards
Michel

[Updated on: Wed, 05 December 2007 05:03]

Report message to a moderator

Re: PL/SQL Performance [message #285680 is a reply to message #285679] Wed, 05 December 2007 05:07 Go to previous messageGo to next message
abuthalip
Messages: 9
Registered: May 2005
Location: India
Junior Member
sorry, i have attached the code now.
  • Attachment: forum.pc
    (Size: 14.44KB, Downloaded 211 times)
Re: PL/SQL Performance [message #285692 is a reply to message #285680] Wed, 05 December 2007 05:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You're lucky to get the same performances with C.
Actually you have to move data from server to the client whereas in full PL/SQL code all is done in database server without any data move.
But maybe the PL/SQL was not the most efficient one.

At first sight I don't see anything in your code.
Maybe you should instrumentalize it to know where you spend time.

Regards
Michel
Re: PL/SQL Performance [message #285713 is a reply to message #285692] Wed, 05 December 2007 06:49 Go to previous messageGo to next message
abuthalip
Messages: 9
Registered: May 2005
Location: India
Junior Member
Dear Michel Cadot,
i had tried the writting in 2 approch like writting record by record(looping thru the array and write each and every array element) and bulk write(fwrite). while doing bulk write i'm able to write only in the fixed lenght(4000 charachters in each line) format, as each element of array is of size 4000 eventhough i have the data of lenght 10(as we are giving the record lenght and no. of records as parameters for fwrite).whereas when i write record by record, it writes the exact data. Is there any option to eliminate the blanks while writting thru fwrite.

Thanks,
Abu
Re: PL/SQL Performance [message #285731 is a reply to message #285713] Wed, 05 December 2007 07:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No way I am aware of.

Regards
Michel
Re: PL/SQL Performance [message #285733 is a reply to message #285731] Wed, 05 December 2007 07:18 Go to previous messageGo to next message
abuthalip
Messages: 9
Registered: May 2005
Location: India
Junior Member
Thanks a lot for your effort Michel Cadot.
Re: PL/SQL Performance [message #285743 is a reply to message #285733] Wed, 05 December 2007 07:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think, if trimming is what you're thinking of, it is easy to write a function that goes from last character to the first non-blank and put a '\0' there, isn't it?

Regards
Michel
Re: PL/SQL Performance [message #286172 is a reply to message #285743] Thu, 06 December 2007 12:22 Go to previous messageGo to next message
abuthalip
Messages: 9
Registered: May 2005
Location: India
Junior Member
Ya i tried for trimming too, but the trimming process is more time consuming.
Re: PL/SQL Performance [message #287586 is a reply to message #285652] Wed, 12 December 2007 16:44 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
If you want to trim spaces, the easiest way I know of it to use sqlplus and let it happen for you.

if you could send your sqlcode to a file, you could execute the sqlcode from sqlplus using the following settings:

set timing off
set time off
set verify off
set echo off
set feedback off
set pagesize 0
set linesize 32767

SET TRIMSPOOL ON
SET TRIMOUT ON
SET TERMOUT OFF

spool ???
@???.sql
spool off

with luck you will have clean files with no trailing spaces and you will not suffer a performance hit to get it.

I am not wholly clear on the details of network cost given the sqlplus setting "SET TERMOUT OFF" so you might want to try the job both from a windows client, and also the host of the database.

Additionally, you may wish to consider running a script and putting output to a LOG file created by your OS. Many OS will generate variable length files and automagically trim white space off the right end of each line. This is very fast, and has the added benefit that many times people are already creating log files anyway and thus can skip the creation of the spool file. This is in fact a very old trick that goes back to Oracle 3 days (mid 80's). Maybe not for everyone though.

This is just a thought, good luck, Kevin.

Good luck, Kevin
Re: PL/SQL Performance [message #287587 is a reply to message #285652] Wed, 12 December 2007 17:10 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
If the majority of the elapsed time is gathering the data and only a VERY SMALL % is spent outputting the data; then optimizing the actual file generation will gain you little.

Just as a test, how long does it take if you 100% eliminate the outputting of the data & leave remaining the collecting & preparing the data to be written?

This test simulates ZERO time spent doing data output (100% optimization!)

If the elapsed time is still not acceptable, you can now focus on the rest of the SQL where I suspect the actual slowdown originates.

HTH & YMMV!
Re: PL/SQL Performance [message #287590 is a reply to message #285652] Wed, 12 December 2007 17:43 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
this is a good point anecedent. I should have mentioned it.

Thanks, Kevin.
Re: PL/SQL Performance [message #288581 is a reply to message #285659] Tue, 18 December 2007 01:42 Go to previous messageGo to next message
kommerashashi
Messages: 23
Registered: November 2007
Junior Member
we can measure the performance through................OEM tool thats called as Oracle Enterprise Managemnt tool........with that we can measure the performance ....
r else we can know throgh the TOAD fully licenced version ..we can get the measurment ............bye
Re: PL/SQL Performance [message #288584 is a reply to message #288581] Tue, 18 December 2007 01:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why.......... they are........ so many............... in your............. post?

Regards
Michel
Re: PL/SQL Performance [message #288598 is a reply to message #285652] Tue, 18 December 2007 02:50 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
1. Following query is NOT using bind variables:

char all_query[1024]="select DEQ_QUERY from TTPO_DEX_X_EXTRACTION_QUERY where DEQ_REQUEST_ID=";


2. Post TKPROF report
Re: PL/SQL Performance [message #288755 is a reply to message #288598] Tue, 18 December 2007 10:24 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
From my own personal experience i find Pro*C is very fast compared to utl_file. I don't know whether it has only happened to me but i have benchmarked it. For 100,000 records utl_file version took almost 90 seconds whereas the pro*c version finished less than 25 seconds.

Now coming back to your pro*c code why you have set your arraysize to 50000. Because i have read it in asktom and also it makes sense to me, always bulk collect in small chunks ( I don't mean 1 i mean is a manageable size) which is 100 and write it. But i don't know whether it will make a difference in your code but i just thought of sharing this.

Also Ana, has made a valid point if the time spent is in fetching data from oracle rather than on fwrite or utl_file.put_line then it is tune the sql.

HTH

Regards

Raj

Previous Topic: costs of joins
Next Topic: Spool time in query
Goto Forum:
  


Current Time: Sat Dec 03 16:20:37 CST 2016

Total time taken to generate the page: 0.08395 seconds