Re: 8.1.7 hangs on inserts

From: Paul Drake <paledHOWMUCHSPAMCANYOUEAT_at_home.com>
Date: Fri, 31 Aug 2001 02:18:14 GMT
Message-ID: <3B8EF3DE.5090906@home.com>

Kris wrote:

>
>
> Sybrand Bakker wrote:
>

>> "Kris" <free_heeler226_at_yahoo.com> wrote in message
>> news:3B8E7FA5.8050507_at_yahoo.com...
>>
>>> I've got a Java process that inserts records into an table, it runs
>>> great.  However, just before our DBA left for Greece for two weeks he
>>> exported the database and then imported it to a new instance.  When I
>>> run the exact same process on the new instance it takes between three
>>> seconds and twenty minutes to insert every 1000 records.  When I run the
>>> process against the original instance it chugs along at a consistent
>>> 1000 records every ten seconds.
>>>
>>> Both instances are on the same machine, I'm the only user on the
>>> machine, the only thing that's changed in the Java code is the
>>> sid/username/password.
>>>
>>> Since the DBA did an export/import I'm wondering what could be causing
>>> this problem and where is the best place to start looking for
>>> differences in the two instances.
>>>
>>> Thanks,
>>> Kris
>>>
>>>
>>
>> I would start checking the size of the online redolog files
>> (select * from v$log)
>> and the frequency of checkpoints occurring
>> select * from v$log_history.
>>
>> I guess he didn't change them from default, and the defaults are in most
>> situations too small.
>> You can also check in the alert log
>> (alert<sid>.log located in the background_dump_dest directory (show
>> parameter background_dump_dest will tell you where that is)
>> whether you have
>> 'checkpoint not complete messages'
>> If you have those you need to chance the redolog configuration
>> by alter database drop logfile group <n>
>> followed by alter database add logfile group <n>
>> ('filename1...','filename2...') size (minimum 5M, the default is 500k)
>>
>> Hth,
>>
>> Sybrand Bakker, Senior Oracle DBA

>
>
>
> Thanks for the answer. I did a diff on the init files for each instance
> and the working instance had this line:
>
> mts_dispatchers = "(protocol=TCP)"
>
> commented out. I commented it out in the new instance and everything
> seems to be working fine. I have only a vague idea what the
> mts_dispatchers do, but I know we don't them for right now.
>
>

great find.

Most likely your batch process was acquiring a shared server.

if you look at the view v$session - check the column "server" - e.g.

select osuser,terminal,server,program
from v$session
where username='MYUSERNAME'
/

(where your username = MYUSERNAME)

then again, that might not have been it - it can largely depend upon the client-side tnsnames.ora or Oracle Names server settings.

hth,

Paul Received on Thu Aug 30 2001 - 21:18:14 CDT