Andy Baumhauer wrote:
Running DBI 1.58 and DBD::Oracle 1.19 with Oracle 10g (10.2) on Fedora
7 64 bit OS:
Copying a table from one tablespace to another tablespace (two $dbh
handles). The table has 56 fields. I've simplified the process to:
fetch row from first dbh
execute row to second dbh
commit every 100 rows
The table contains 1.9 million rows -- I copied 20,000 here and used
DBI::Profile to get these results:
DBI::Profile: 130.809944s 93.63% (40001 calls) test_tie.pl @
2007-09-14 15:18:40
'DESTROY' =>
'DBD::Oracle::st::DESTROY' =>
0.000075s
'err' =>
'DBD::Oracle::st::err' =>
0.026046s / 19999 = 0.000001s avg (first 0.000001s, min
0.000000s, max 0.000021s)
'DBD::_::common::err' =>
0.000010s
'execute' =>
'DBD::Oracle::st::execute' =>
130.783813s / 20000 = 0.006539s avg (first 0.004013s, min
0.000390s, max 29.996666s)
Basically, most executes finish in milliseconds, but randomly
(different every run), the execute holds for 30 seconds, almost dead
on. I can't figure out why, but the the delay when it halts (doesn't
map up with the commits either) looks like some kind of timer.
I haven't noticed this problem on tables with fewer columns.
Any ideas?
Andy
What resource manager group is your user in? Long waits for apparently
no reason (in the middle of a lot of work) are often the resource
manager doing quantum wait. If you run apex (the web app) you can often
see the resmgr:wait quantum events with a time under the monitor->wait
page (otherwise you'll need to look up the query do select the waits
yourself). There are literally hundreds of other waits too but I'd guess
you are hitting a quantum wait.
Martin
