Optimizing the data access layer

Question

Hi, all.

I have a web service (JAX-RS/Spring) that generates SQL queries which run against a temp table in Oracle. The data is then archived to another table (through 3 MERGE statements). The execution of each "job" (querying and merging) is done in the background through a JMS broker (ActiveMQ). The sequence of operations of each job is something like:

   insert/update into table Q (select from table F) -- done between 4 and 20 times
   merge into table P (select from table Q)  -- twice
   merge into table P (select from table F)
   merge into table P (select from table F)
   create a view V as select from table P

(table Q is a temp table).

When I dispatch two or three jobs like that, it takes around 6-7 minutes for each job to execute. But when I dispatch up to 15 running at the same time, the duration stretches out way longer.

is this happening because all these processes are trying to insert/update into the temp table Q? thus fighting for the resource? What techniques should I be looking at to optimize this? For example, I thought of making 5 or 6 duplicates of table Q and "load balance" the data access object queries against them.

Thanks

Answer 1

When I dispatch two or three jobs like that, it takes around 6-7 minutes for each job to execute. But when I dispatch up to 15 running at the same time, the duration stretches out way longer.

There's any number of resources your processes could be contending for, not just the temporary table.

For starters, how many processors (CPUs/cores) does your database have? There's a pretty good rule of thumb that we shouldn't run more than 1.8 background jobs per processor. So there's no point in worrying about cloning your temporary table if you don't have enough processors to support a high degree of parallelism.

The key thing with tuning is: don't guess. Unlike some other DBMS products, Oracle has lots of instrumentation we can use to find out exactly where the time goes. It's called the Wait Interface. It's not perfect but it's a lot better than blindly re-designing your database schemas. Find out more.

Answer 2

If Q is really a temp table (as in a GLOBAL TEMPORARY TABLE) then each session will have a separate physical object, so they won't contend for locks or at the data level.

You are more likely to get contention on the permanent table P, or on server resources (memory and disk).