0

I'm loading about 1 million records into Oracle using a custom Java utility. The Java utility is multi-threaded and has worked numerous times in the past with no problem. My issue is that when I start the load for the very first time, it is lightning fast, around 150K object per hour. After about an hour or 2 the performance greatly decreases to around 6000 objects per hour. I'm almost certain that my performance hit has something to do with Oracle, but I can't figure out what it is. The Oracle machine has 16GB of RAM and 8 CPUs. I set the following system parameters, that have worked for me in the past:

optimizer_mode=ALL_ROWS
optimizer_index_cost_adj=10
query_rewrite_integrity=ENFORCED
pga_aggregate_target=300M
sga_target=5000M
sga_max_size=5000M

Does anyone have any Oracle knowledge to maybe know why my performance is great initially but drops off drastically? One additional note, if I stop the load, restart the machine, then start the load again, I continue to see the 6000 object per hour performance. So it's always the very first load after cloning our Production database that has the best performance. Hopefully someone has an idea, thanks in advance!!

Brandon Lehman
  • 69
  • 1
  • 1
  • 7
  • You should trace your session and look for expensive operations. You can find many examples around, for instance [this question](http://stackoverflow.com/questions/148648/oracle-is-there-a-tool-to-trace-queries-like-profiler-for-sql-server). If the problem comes from the db, you should see it in the tkprof report. My guess would be a select statement that doesn't use an index (which would be fast at the beginning, then more and more expensive as the db fills). Most likely the problem comes from your code, not from the setup. – Vincent Malgrat Nov 28 '13 at 14:33
  • Why do you limit SGA to 5000M if you have 16 GB of memory on the server? – mustaccio Nov 28 '13 at 14:55
  • Well I have to allow about 5GB for the Java utility, and I assume 2 GB for OS and other apps. I could increase it some I guess. I feel like something is "filling up" in Oracle and making it lose "juice". – Brandon Lehman Nov 28 '13 at 15:16
  • How can I tell if I should add a new data file? – Brandon Lehman Nov 28 '13 at 16:38
  • Seems improbable (because you say it takes an hour for perf to degrade), but maybe the (redo log) archiver is too slow and makes the log writer wait as soon ad redo logs are all in use. See http://docs.oracle.com/cd/B19306_01/server.102/b14211/instance_tune.htm#sthref814 and [Managing Archived Redo Logs](http://docs.oracle.com/cd/B19306_01/server.102)./b14231/archredo.htm#i1006971 – halfbit Nov 28 '13 at 16:39
  • What exactly does the code do and how? Are indexes involved? Is it just loading or also checking with lookups on growing tables? –  Nov 28 '13 at 17:12
  • Basically, the code reads the record from the staging table, creates a Java object with that info, and stores that object into the target database. I have indexes on the tables in both databases, without them the performance is even worse. – Brandon Lehman Nov 29 '13 at 19:20
  • One thing I noticed is that even though I'm allocated 5GB of memory to Oracle, it is only using 1.5GB. Is there a way to force Oracle to use more memory? – Brandon Lehman Nov 29 '13 at 19:20

1 Answers1

0

I assume that the load is only inserts and that the distribution of the data changes over time.

Or are it continuous inserts into the same table, like loading continuously Call Detail Records of a phone system?

In principe Oracle does not easily get slower with increasing and lasting use. But there are some ways to make it run slower:

Locks / latches

I would recommend checking that concurrent use by other Oracle sessions is not causing the problems due to short locks or latches. Given that it are inserts, it could maybe be the other threads trying to insert in the same data blocks given the distribution of the data which might become different after some time.

Restricted inserts per block

Please check that max_trans on the tables is not restricted to 1 or 2. I've seen that once and it was really funny to see how Oracle got down to a crawl when only one session can do something in a block.

SGA and kernel problems

With older Oracle releases (Oracle 7 and 8) I've seen numerous occassions on large systems where Oracle started to kill itself. This especially holds for multiprocessor systems, because locking/latching on a MP-system is implemented differently: the other processor might get it's work done, so an Oracle threads first just spins a little and then tries again. Also, problems with SGA fragmentation or even bad locking of the SGA can cause problems.

Please check that the insert statements use bind variables, batches or bypass SQL completely. You might also want to try running it in one thread. Is one thread processing stable over time (although slower)? If so, you have a locking issue somewhere. Google for locks/latches/spins and follow scenarios listed.

Guido Leenders
  • 4,232
  • 1
  • 23
  • 43
  • Thanks for the reply. It is continuous inserts to one table, as I'm only loading one object type. For this load I'm using a test environment so I don't have to worry about other sessions. I know mine is the only one. Checking the max_transactions on the tables is intriguing, I just googled that and found nothing useful. Is there a query or something to find that? – Brandon Lehman Nov 28 '13 at 17:04
  • Ok clear. One table, stand alone, just multiple thread loading one table. Max_transactions can be found as max_trans column in user_tables. Alter table xyz storage (maxtrans 255) I believe. I think to remember that each active transaction in a block takes 23 bytes or so. – Guido Leenders Nov 28 '13 at 19:53
  • The table I'm loading into already had a maxtrans value of 255. Darn – Brandon Lehman Nov 28 '13 at 20:20
  • Too bad. Challenging issue. Something DBA-ish: can you check that the redo log files can be switched fast enough? When they are being archived (unlikely on a test server but nonetheless) and the recipient folder does not process it fast enough, oracle will stall till the log switch has finished. Otherwise I must admit i'm out of options. – Guido Leenders Nov 29 '13 at 10:20