FW: Performance problem: Loading data via insert
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: "'Oracle Freelist'" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 14 Mar 2014 05:32:23 -0400
missed list on reply
From: Mark W. Farnham [mailto:mwf@xxxxxxxx]
Sent: Friday, March 14, 2014 5:28 AM
To: 'Abhishek Gurung'
Subject: RE: Performance problem: Loading data via insert
I didn’t pay a lot of attention to your RDBMS release. You mentioned using java
stored in the database wrapped in a procedure.
The sizing of various memory pools may be relevant as well as whether you’re
using automatic memory management and/or setting your own sizes.
Doing this query on the different databases may show something useful:
SQL> r
1* select substr(name,1,40) name, substr(value,1,40) value from v$parameter
where name like '%pool%'
NAME VALUE
----------------------------------------
----------------------------------------
shared_pool_size 0
large_pool_size 0
java_pool_size 0
streams_pool_size 0
shared_pool_reserved_size 21810380
buffer_pool_keep ~
buffer_pool_recycle ~
global_context_pool_size ~
olap_page_pool_size 0
9 rows selected.
This was from my little toy database on my PC, so nothing is really sized. But
you might have things configured on dev/test that are friendly to java in the
database and something different on the problem database.
Just a shot in the dark.
But still, I think doing the insert bit of your program as a file formatted to
be received as select from an external file or via loader will do far better
(even though that is not an answer to your question about why is it different
between the two systems – probably you just really want it to always be fast.)
Good luck,
mwf
From: Abhishek Gurung [mailto:abhishek.gurung@xxxxxxxxxxx]
Sent: Friday, March 14, 2014 5:06 AM
To: Mark W. Farnham
Subject: RE: Performance problem: Loading data via insert
<snip>
<snipp>
Abhishek: Everything resides on database server. File, application is basically
a java program imported in to Oracle database. We have created procedure on
those Java programs.
4) Are you generating a list of one row inserts?
a. if so, you would probably be better off generating something in a format
that could either be read as an external table or by loader
b. if so, then you probably don’t want an instrumentation statement before
and after each insert. I’d start with something like before and after each of
your DDL statements and then before and after the first insert to each
different table and then after perhaps 100 rows and after then end of a given
table. Then at least you’ll know if the delay is with respect to some
individual DDL or some particular table.
Abhishek: Yes. Let me see what we can do on this line. Thanks for the
suggestion.
5) If you’re using sqlplus, did you set arraysize to some reasonable value
in your job stream, or are you relying on a glogin file or default that might
be different on the two servers?
Abhishek: I need to check that.
<snip>
Other related posts:
- » FW: Performance problem: Loading data via insert - Mark W. Farnham
