Extremely poor execute performance

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

Re: Extremely poor execute performance

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

Re: Extremely poor execute performance

Martin,

Thank you so much for the spot on reply. The work you saved with that
reply is greatly appreciated.

I checked the resource groups with this statement:

COL initial_rsrc_consumer_group FORMAT A24 HEADING 'Resource|Consumer|Group'
COL username FORMAT A12 HEADING 'User Name'
SELECT
initial_rsrc_consumer_group,
username
FROM dba_users
ORDER BY 1,2
;

The user was in the DEFAULT_CONSUMER_GROUP. I issued the following
commands as the sysdba to move the user to the SYS_GROUP, and the
problem went away (I'm the only user of this database, and it exists for
conversion of data.

EXEC
DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP('SOM EORACLEUSER',
'SYS_GROUP', TRUE);
EXEC DBMS_RESOURCE_MANAGER.SET_INITIAL_CONSUMER_GROUP('SOMEORACLE USER',
'SYS_GROUP');

I hope this helps someone else in the future.

Andy Baumhauer

Re: Extremely poor execute performance

Andy Baumhauer wrote:
> Martin,
>
> Thank you so much for the spot on reply. The work you saved with that
> reply is greatly appreciated.
>
Glad to hear it. resmg:wait quantum caught me some time ago when a
complicated query was taking over 8s every now and again and yet running
it outside of the application in sqlplus took less than .1s. It must now
be one of the most optimised queries we have as we spent some time on it
before we realised the remgr was adding an 8s wait every now and again
because of the total work the application had done.

Martin
> I checked the resource groups with this statement:
>
> COL initial_rsrc_consumer_group FORMAT A24 HEADING
> 'Resource|Consumer|Group'
> COL username FORMAT A12 HEADING 'User Name'
> SELECT
> initial_rsrc_consumer_group,
> username
> FROM dba_users
> ORDER BY 1,2
> ;
>
> The user was in the DEFAULT_CONSUMER_GROUP. I issued the following
> commands as the sysdba to move the user to the SYS_GROUP, and the
> problem went away (I'm the only user of this database, and it exists
> for conversion of data.
>
> EXEC
> DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP('SOM EORACLEUSER',
> 'SYS_GROUP', TRUE);
> EXEC
> DBMS_RESOURCE_MANAGER.SET_INITIAL_CONSUMER_GROUP('SOMEORACLE USER',
> 'SYS_GROUP');
>
> I hope this helps someone else in the future.
>
> Andy Baumhauer
>
>