Re: MASSIVE degradation of insert using sequence ID's via JDBC on 9i?

Dear all;

Thanks for all your help and pointers.
I have still not resolved why I'm cripping 9i, but I have learnt about trace events and analysing trace files.

I have now run my test (as listed elsewhere) against both 9i and 10g, with
10046 tracing at level 12.

As well as tkprof, I used the
AppsDBA resource profiler
http://home.comcast.net/~arivenes/scripts/resource.zip to analyse the results.

The biggest (and it's BIG) difference is clearly shown in the summary:

10 looks "ok"

INTERVAL RATES AND WAITS (service time + wait time)

                                         Total Event  % Event       Total  --------------- Cumulative ----------------
Event Name                               Time (sec)     Time      Events  Avg Time(sec)  Min Time(sec)  Max Time(sec)


--------------------------------  -----------------  -------  ----------  -------------  -------------  -------------

unaccounted-for                            5.871474    33.73
SQL*Net message from client                5.448164    31.30      20,007       0.000272       0.000164       0.197724
CPU Service                                4.706311    27.04      25,974       0.000181       0.000000       0.097985
log file sync                              1.231318     7.07       5,097       0.000242       0.000004       0.056876
SQL*Net message to client                  0.088047     0.51      20,007       0.000004       0.000002       0..000131
enq: RO - fast object reuse                0.050858     0.29           4       0.012715       0.000007       0.048422
log file switch completion                 0.009098     0.05           1       0.009098       0.009098       0.009098
rdbms ipc reply                            0.001343     0.01           4       0.000336       0.000126       0.000847


--------------------------------  -----------------  -------  ----------  -------------  -------------  -------------

Total                                     17.406613   100.00      71,094

While 9 appears "unwell"

INTERVAL RATES AND WAITS (service time + wait time)

                                         Total Event  % Event       Total  --------------- Cumulative ----------------
Event Name                               Time (sec)     Time      Events  Avg Time(sec)  Min Time(sec)  Max Time(sec)


--------------------------------  -----------------  -------  ----------  -------------  -------------  -------------

log file sync                            572.589406    97.21       5,058       0.113205       0.000043       1.000852
unaccounted-for                            6.100452     1.04
SQL*Net message from client                5.280490     0.90      20,007       0.000264       0.000146       0.133374
CPU Service                                4.710000     0.80      26,420       0.000178       0.000000       0.100000
db file sequential read                    0.292716     0.05          66       0.004435       0.000025       0.016963
SQL*Net message to client                  0.032749     0.01      20,007       0.000002       0.000000       0.000722
rdbms ipc reply                            0.012621     0.00           8       0.001578       0.000810       0.003936


--------------------------------  -----------------  -------  ----------  -------------  -------------  -------------

Total                                    589.018434   100.00      71,566

the log file sync event count is very similar, but where 10g is doing each flush in 0.000242, 9i takes a rather leisurely 0.113205.

Googling shows that "log file sync" simply mean the LGWR system saving redo information.

I'm off to learn more, but would like to say "thank you" to all the good folks here who helped me get this far.

   BugBear (still happy to receive advice) Received on Wed Jun 29 2005 - 08:55:40 CDT