Redo log and performance....

[LeDam]

Hello all...
I have a question :
I have a single database Oracle 8i running on an AIX server. When looking with "topas", i can see that the "user CPU time" is all the time very high (+80%)!!
It is strange because there are 4 CPUs in this server...

There are 3 redo logs that are switched every 20 sec! So, all the redo logs files are used in one minute!!there is a lot of "Checkpoint not complete" in the alert log. So i will increase the size of these logs.
But could it be my problem???
In the alert log, there is also :

ORA-1653: unable to extend table TEST.TRACE by 125 in tablespace ETT
ORA-1653: unable to extend table TEST.TRACE by 125 in tablespace ETT
ORA-1653: unable to extend table ETT.AUDIT_IEDATA by 63 in tablespace ETT

I don't know if i can extend this table.... but could it be my problem???

How can i tune my DB???
I'm not a database Admin....

 

[carp]

It sounds like you would be well advised to increase the size of your log files.

And while you're at it, you will probably want to increase the size of your ETT tablespace so the tables can extend.

 

[sem]

As for ORA-1653, you should also check that it was due to lack of free space, not fragmentation.

Regards, Dima

 

[SantaMufasa]

LeDam,

Carp will probably concur that when we were Oracle DBA instructors, we advised students to set the size of the on-line redo log files such that it took around 20 MINUTES for a log switch to occur...20 seconds is just beating the heck out of the poor checkpoint process, and killing your performance. If you are using the default size of on-line redo logs that Oracle creates with their default databases, their size (as I recall) is only about 500K each. At our sites, the on-line redo logs are at least 30MB each (and sometimes larger for more active dbs).

Your other problem (ORA-1653) is a completely different issue. Something is trying to extent the size of TEST.TRACE by 1MB, and there is simply not 1MB of CONTIGUOUS space left in the ETT tablespace. Your quickest solutions involve adding more space to the ETT tablespace. One solution is to add 1 or more datafiles to ETT:

ALTER TABLESPACE ett ADD DATAFILE '<file_name_here>'
SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE 2000M;

I added the &quot;...AUTOEXTEND...&quot; clause to the above syntax, which allocates space to the file on a just-in-time, as-needed basis. It does not cause any appreciable performance hit to use AUTOEXTEND.

Another solution is to put your existing files for ETT into AUTOEXTEND mode. Here is that syntax:

ALTER DATABASE DATAFILE <existing_ETT_file_name>
AUTOEXTEND ON NEXT 10M MAXSIZE 2000M;

BTW, the reason I use a 2000M maximum is to avoid problems on operating systems that have trouble with file sizes in excess of 2GB.

To determine the current sizes and &quot;AUTOEXTEND&quot; status of your database datafiles, you can use the following script:

set echo on
REM **************************************************************
REM David L. Hunt (file author) distributes this and other
REM files/scripts for educational purposes only, to illustrate the
REM use or application of various computing techniques. Neither the
REM author nor Dasages, LLC, makes any warranty regarding this
REM script's fitness for any industrial application or purpose nor is
REM there any claim that this or any similarly-distributed scripts
REM are error free or should be used for any purpose other than
REM illustration.
REM **************************************************************
set echo off
set linesize 180
set pagesize 40
set heading on
col fname heading &quot;Filename&quot; format a60
col fnum heading &quot;#&quot; format 99
col ts heading &quot;Tablespace|Name&quot; format a15
col tb heading &quot;Total|File Size&quot; format 999,999,999,999
col used heading &quot;Bytes Used&quot; like tb
col free heading &quot;Bytes Free&quot; like tb
col autoext heading &quot;Auto|Extend&quot; format a6
break on report
compute sum of tb used free on report
spool TablespaceUsage.txt
select substr(tablespace_name,1,15) ts
,d.file_id fnum
,d.bytes tb
,decode(d.bytes,0,0,d.bytes-nvl(freebytes,0)) used
,decode(d.bytes,0,0,nvl(freebytes,0)) free
,decode(e.file#,null,'No','Yes') autoext
,''''||substr(file_name,1,55)||'''' fname
from sys.dba_data_files d
,(select file_id,sum(bytes) freebytes
from sys.dba_free_space
group by file_id) f
,sys.filext$ e
,v$datafile v
where d.file_id=f.file_id(+)
and d.file_id=e.file#(+)
and v.file#=d.file_id
order by tablespace_name,creation_time
/
spool off
prompt
prompt Wrote spool file &quot;TablespaceUsage.txt&quot;.
prompt

********************************************
If you receive an error saying that &quot;sys.filext$&quot; does not
exist, it is because your database has never put any file
in AUTOEXTEND mode. You can resolve this problem by simply
putting at least one file in AUTOEXTEND mode with the code,
&quot;ALTER DATABASE DATAFILE <existing_ETT_file_name>
AUTOEXTEND ON NEXT 10M MAXSIZE 2000M;&quot;.

Cheers,

Dave

 

[jimbopalmer]

just a counter point of view on redo log file size.

I look at the size of my Hardware RAID NVRAM buffer and make my redo log file that size or smaller if possible. (often 4 meg to 64 meg)

then a write to redo is at RAM speed, not Disk speed.*

*if the NVRAM is on the far side of a SCSI chain, you may be at SCSI controller speed, still faster than disk speed

I tried to remain child-like, all I acheived was childish.

 

[LeDam]

Ok,
thanks everyone...
I will do this and tell you if this work...