SEO

Hi everyone,

I want to share a performance experience and I would like to know if this
performance is quite okay or if it is worth digging into tuning or other
aspects because there is boombastic room for improvements.

This is about loading LOB data into an Oracle instance.

What I have is this:

Oracle Server: 9i, Win2000, 1GHz, 1MByte, one test instance running. Not
swapping. Data file that the LOB is going to is on an IDE hard disk. Nothing
else doing.
Oracle Client: 9i, Win2000, 1GHz, 1Mbyte, nothing else doing. LOB data not
read from file (so hard disk is no issue here) but generated randomly in
main memory while running (chunks of 32kb).

Both are connected via a TCP/IP ethernet 100MBit/s.

I have a table "LOBTest" which has a string key column and a BLOB column.
I insert a row with an empty_lob and then get a lob locator and stuff
10.000.000 bytes (so roughly 10MB) into the lob locator.

What I get is it takes around 25 seconds with my appl built in RELEASE
mode (VC++) and 35 seconds when build in DEBUG mode. During that time period
no significant load is incurred on any of the computers (i observe with task
manager) nor is the network a bottleneck (my network admin monitors with a
tool that shows no collisions).

At first glance I find that 25+ secs a lot for just 10MB. Of course I know
there is some overhead on both the client and especially the server.

But maybe other people can come up with some figures from their
environment or maybe you ask me to provide more details even though I have
put here everything that seems top important for this issue. If some guys
come up and say that is a terribly bad performance I would also appreciate
any hints as to what I could do to improve.

Hi from Paris

in order to know what exactly happens on the Oracle instance, have a look at
the STATSPACK package...


anyway, have a look at the online redo logs size, as many redo entries will
be generated during huge loading...

Cdlt


"André Hartmann" <andrehartmann@hotmail.com> a écrit dans le message de
news:411ce04a@olaf.komtel.net...
> Hi everyone,
>
> I want to share a performance experience and I would like to know if
this
> performance is quite okay or if it is worth digging into tuning or other
> aspects because there is boombastic room for improvements.
>
> This is about loading LOB data into an Oracle instance.
>
> What I have is this:
>
> Oracle Server: 9i, Win2000, 1GHz, 1MByte, one test instance running. Not
> swapping. Data file that the LOB is going to is on an IDE hard disk.
Nothing
> else doing.
> Oracle Client: 9i, Win2000, 1GHz, 1Mbyte, nothing else doing. LOB data
not
> read from file (so hard disk is no issue here) but generated randomly in
> main memory while running (chunks of 32kb).
>
> Both are connected via a TCP/IP ethernet 100MBit/s.
>
> I have a table "LOBTest" which has a string key column and a BLOB
column.
> I insert a row with an empty_lob and then get a lob locator and stuff
> 10.000.000 bytes (so roughly 10MB) into the lob locator.
>
> What I get is it takes around 25 seconds with my appl built in RELEASE
> mode (VC++) and 35 seconds when build in DEBUG mode. During that time
period
> no significant load is incurred on any of the computers (i observe with
task
> manager) nor is the network a bottleneck (my network admin monitors with a
> tool that shows no collisions).
>
> At first glance I find that 25+ secs a lot for just 10MB. Of course I
know
> there is some overhead on both the client and especially the server.
>
> But maybe other people can come up with some figures from their
> environment or maybe you ask me to provide more details even though I have
> put here everything that seems top important for this issue. If some guys
> come up and say that is a terribly bad performance I would also appreciate
> any hints as to what I could do to improve.
>
>

André Hartmann wrote:

[snip]
>
> What I have is this:
>
> Oracle Server: 9i, Win2000, 1GHz, 1MByte, one test instance running. Not
> swapping. Data file that the LOB is going to is on an IDE hard disk. Nothing
> else doing.
> Oracle Client: 9i, Win2000, 1GHz, 1Mbyte, nothing else doing. LOB data not
> read from file (so hard disk is no issue here) but generated randomly in
> main memory while running (chunks of 32kb).
>
> Both are connected via a TCP/IP ethernet 100MBit/s.
>
> I have a table "LOBTest" which has a string key column and a BLOB column.
> I insert a row with an empty_lob and then get a lob locator and stuff
> 10.000.000 bytes (so roughly 10MB) into the lob locator.
>
> What I get is it takes around 25 seconds with my appl built in RELEASE
> mode (VC++) and 35 seconds when build in DEBUG mode. During that time period
> no significant load is incurred on any of the computers (i observe with task
> manager) nor is the network a bottleneck (my network admin monitors with a
> tool that shows no collisions).
[snip]

Do the math:
10MB/25 secs == 4MB/sec == 40Mbps
That is _without_ taking in consideration any overhead
regarding setting up network connections (how's your
DNS performing?!?), opening threads, opening file(s),
buffering (how large is your LOB buffer?), TCP overhead,
etc, etc.
I guess when taking all that in account, you may well
hit your network max throughput of 100Mbps during the
actual transfer... Perhaps you can work out how long
the actual transfer takes? E.g. by sinking it to
/dev/null?

There seems a lot of overhead in the VC++ code... Is
there a PERFORMANCE mode ?
--

Regards,
Frank van Bortel

> Do the math:
> 10MB/25 secs == 4MB/sec == 40Mbps

uh ? what math's is that ? 10MB = 10*8 Mbit = 80Mbit. Rate: 80Mbit / 25sec
= 3.2Mbps, at least with my calculator...

> That is _without_ taking in consideration any overhead
> regarding setting up network connections (how's your
> DNS performing?!?), opening threads, opening file(s),
> buffering (how large is your LOB buffer?), TCP overhead,

client side lob buffering is not enabled. is there a server side lob
buffering ?

> I guess when taking all that in account, you may well
> hit your network max throughput of 100Mbps during the
> actual transfer... Perhaps you can work out how long
> the actual transfer takes? E.g. by sinking it to
> /dev/null?

i am on windows. no /dev/null there, right ? what do you mean ? should i
compare a simple filesystem-to-filesystem copy to get an idea how much of
the performance is taken by tcp/ip, ports, hard disks ? I tried that. A file
of 18MB copies from one computer to the other in 3 secs, so that's roughly
48Mbps.

>
> There seems a lot of overhead in the VC++ code... Is
> there a PERFORMANCE mode ?

I compile my application in DEBUG and RELEASE modes ... both results I
gave. Of course it is faster in RELEASE mode (which is what i would deliver
to the customer): 25sec. I got 35sec in DEBUG mode.

AH

André Hartmann wrote:

>>Do the math:
>>10MB/25 secs == 4MB/sec == 40Mbps
>
>
> uh ? what math's is that ? 10MB = 10*8 Mbit = 80Mbit. Rate: 80Mbit / 25sec
> = 3.2Mbps, at least with my calculator...
>
Oops - brain shut down without notification...

>
>>That is _without_ taking in consideration any overhead
>>regarding setting up network connections (how's your
>>DNS performing?!?), opening threads, opening file(s),
>>buffering (how large is your LOB buffer?), TCP overhead,
>
>
> client side lob buffering is not enabled. is there a server side lob
> buffering ?
>

AFAIK, you'll use a variable to hold a piece of the data, and
append it to the LOB - loop until all data processed. What's
the size of that variable? Something along the line of:
WHILE length_count <> 0 LOOP
dbms_lob.read (temp_blob, amount, position, data_buffer);
temp_buffer := utl_raw.cast_to_varchar2(data_buffer);

>Perhaps you can work out how long
>>the actual transfer takes? E.g. by sinking it to
>>/dev/null?
>
>
> i am on windows. no /dev/null there, right ? what do you mean ? should i
> compare a simple filesystem-to-filesystem copy to get an idea how much of
> the performance is taken by tcp/ip, ports, hard disks ? I tried that. A file
> of 18MB copies from one computer to the other in 3 secs, so that's roughly
> 48Mbps.

Well, that's useful info, but I thought more along the line of:
- prepare client side (open files)
- prepare network
- write time marker
- transfer
- write time marker

No need to actually _save_ the data... Anyway, your copy shows it
must be possible to do it faster...

Sorry for the brain outage...
--

Regards,
Frank van Bortel