Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: tune for INSERT performance?
On Tue, 23 Dec 1997 12:52:04 -0600, Gregg Sporar <gregg_sporar_at_i2.com> wrote:
>Thomas Kyte wrote:
>
>> You should look at your log files to see how large (or small) they are. You
>> might have the default 512k log files which are way too small. Look in the file
>> \orant\rdbms73\trace\{$ORACLE_SID}alrt.ora and see if you have any "checkpoint
>> not complete..." type of messages. These indicate that log files are not sized
>> appropriately for the amount of work you want to do. Try increasing the log
>> files.
>>
>
>It looks like this may be where the problem is: we do have the default size log
>files and lots of those "checkpoint not complete..." messages. My thanks to you and
>all the others who responded; I'll recreate the database after the holidays and
>start the testing over again.
>
>Thanks,
>
>Gregg Sporar
>i2 Technologies
>
You don't need to recreate the db to resize logfiles. it only takes a minute to rebuild them bigger. the following shows how you might go from 3x512k logfiles to 3x10meg logfiles for example. do this in sqlplus as sys or system:
REM find out where the logfiles are now and how big they are
select v$logfile.member, v$log.status, v$log.bytes
from v$log, v$logfile
where v$log.group# = v$logfile.group#
/
REM add 3 new logfiles that are the right size... REM of course a production system would add 2 or more members REM per group on different devices... here we only have 1 member/group
alter database add logfile '/user2/oracle73/dbs/oradata/oracle73/newredo1.log'
size 10m;
alter database add logfile '/user2/oracle73/dbs/oradata/oracle73/newredo2.log'
size 10m;
alter database add logfile '/user2/oracle73/dbs/oradata/oracle73/newredo3.log'
size 10m;
REM issue the following enough times until the CURRENT logfile is one of the
REM NEW logfiles. The first query above shows you the current logfile....
alter system switch logfile;
alter system switch logfile;
REM Now, drop the little logfiles we don't need anymore. You need to erase or REM rm them from the filesystem yourself AFTER you drop them from the database.
alter database drop logfile
'/user2/oracle73/dbs/oradata/oracle73/redooracle7301.log';
alter database drop logfile
'/user2/oracle73/dbs/oradata/oracle73/redooracle7302.log';
alter database drop logfile
'/user2/oracle73/dbs/oradata/oracle73/redooracle7303.log';
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed Dec 24 1997 - 00:00:00 CST