FW: Performance problem: Loading data via insert

From: Mark W. Farnham [mailto:mwf_at_rsiz.com] 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_at_hotmail.com] 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?

1. 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
2. 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>

--
http://www.freelists.org/webpage/oracle-l