| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Performance!
Hi Daniel,
let's do some first aid!
> Here are the parameters you asked me:
> shared_pool_size -> 3500000
> db_block_buffers -> 550
> db_block_size -> 2048
> sort_area_size -> 65536
This means the following:
your Shared Pool is 3.5 MB,
your Buffersize is (block size*buffers) = 1.12 MB
your Sort_Area_size is 65 KB
That means, that your total SGA is about 5 MB.
Check it with the following statement as SYSTEM:
select round(sum(value)/1024/1024,3)||' MB' mySGA from v$sga;
If the the sga is about 5 MB and you have a server with 256 MB you have 251 MB hanging around, nothing to do, bored to live... Just kidding...
It means the database just uses 2% of the possible memory which is not much enough! Seems like you are using the database which installs automatically, don't you?
What we'll do now is inrease the uses Memory of the DB: If there are many applications on the Netware-server, we should use just 30% of the total Memory. If the database is the onliest thing running, we can use about 50%. Otherwise the server starts swapping and this is what you should avoid.
So do the following in your initora.log :
Restart the database. Queries should be little faster now. To do some fine tuning, You can increase some of the parameters.
Just check with the sql_statement above the 30 to 50 percent frontier...
So first step done, next step:
>
> My orclalrt.log file has 35MB size. Is this common? I've opened it
>
I depends of how long is the database running after Installation?
Seems like the db writes a lot of things in it. Check it, if there
are millions of Checkpoint Not Completes than we can do something.
If there are Millions of 'Errors in TraceFile XYZ'
you have a problem which one can answer by email. But I bet you
have a lot of checkpoints...
If it is so, I have to know the results of the following queries:
select * from v$log;
select * from v$logfile.
How mich disk does the server have? Can you use all of the disks? I bet we can tune the redo_logs as well...
>
> about the select statments: In my application I do not write any SQL
> STATEMENT, because I use Genexus. It creates the statements for me and
it's
> a powerfull tool, so I don't think this is the problem. Sometimes I have
to
> develope in VB6. In theses cases I shoult improve the SQL statments. But
> first I want Oracle Database itself best configured.
I'm not a friend of these tools because they can't think! But I agree, first of all the DB has to be 'start-configured'.
> My server has 256MB RAM. Is these sufficient?
Depend of how many users.
My database has something
> about 200MB size (summing all tables).
Is not too much, should be OK.
My netware server is configured to
> use 64k blocks.
So maybe you should increase the db_block_size up to 8k
BUT THIS IS ONLY POSSIBLE DURING FULL EXPORT,
CREATE A NEW DATABASE AND PERFORM A FULL
IMPORT, so first we put this thought away
>
> Another proble I find: for example I want to delete ALL rows from a table.
> This table has 30000 row, for example.
> I use SQL command: delete from <table_name>
> The server seems VERY VERY budy when I do this.
Redo Logs and Rollback-Segments Too small.
And it take a long time to
> tell me: rollback segment wasn't enought. Am I doing something wrong?
> I've looked in orclalrt.log file and there A LOT AND A LOT of lines like
> this:
> "IN_APLICA
> ORA-1653: unable to extend table PUKET.COEMCA by 5 in tablespace"
>
> IN_APLICA is my TABLESPACE AND PUKET.COEMCA is the table I wanted to
delete
> all rows
> and this:
> "IN_APLICA
> ORA-1654: unable to extend index PUKET.COENE3 by 210 in tablespace"
>
> there are a lot of lines like this too:
> Thread 1 advanced to log sequence 25058
> Current log# 2 seq# 25058 mem# 0: PROD:ORACLE\DATABASE\log1orcl.ora
>
>
> I didnt undertand what do you mean with 'checkpoint not complete'
It must be there like this
Thread 1 advanced to log sequence 169
Current log# 1 seq# 169 mem# 0: D:\ORABASE\RSEW\DATABASE\REDO01A.ORA
Current log# 1 seq# 169 mem# 1: E:\ORABASE\RSEW\DATABASE\REDO01B.ORA
Thread 1 advanced to log sequence 170
Current log# 2 seq# 170 mem# 0: D:\ORABASE\RSEW\DATABASE\REDO02A.ORA
Current log# 2 seq# 170 mem# 1: E:\ORABASE\RSEW\DATABASE\REDO02B.ORA
Thread 1 cannot allocate new log, sequence 171
Checkpoint not complete ################## Here it is!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!Current log# 2 seq# 170 mem# 0: D:\ORABASE\RSEW\DATABASE\REDO02A.ORA Current log# 2 seq# 170 mem# 1: E:\ORABASE\RSEW\DATABASE\REDO02B.ORA Thread 1 advanced to log sequence 171
The database performs log-switching during every transaction. Without the
Checkpoint
Not Complete, this lines are fine.
If you are sure during the delete-statement and you want to delete all rows,
you can use 'trunc Mytable' as well.
But you can't rollback a trunc-statement and the table will be empty after
(No Where
Clause-Possible!)
Just search for checkpoint. If you don't find it with 'not complete' it's
fine!
But we have to check the rollback-segments.
How many users are there?
Do you have some diskspace (about 50 to 100 MB?).
>
Ok, I'm waiting for your answers...
Bye
Uli
>
> Thanks for helping. I'm waiting new message. Thanks so much!
>
>
Daniel Cukier <danicuki_at_linux.ime.usp.br> schrieb in im Newsbeitrag:
7jp7en$if7_at_enews3.newsguy.com...
> Here are the parameters you asked me:
> shared_pool_size -> 3500000
> db_block_buffers -> 550
> db_block_size -> 2048
> sort_area_size -> 65536
>
> My orclalrt.log file has 35MB size. Is this common? I've opened it
>
>
> about the select statments: In my application I do not write any SQL
> STATEMENT, because I use Genexus. It creates the statements for me and
it's
> a powerfull tool, so I don't think this is the problem. Sometimes I have
to
> develope in VB6. In theses cases I shoult improve the SQL statments. But
> first I want Oracle Database itself best configured.
>
> My server has 256MB RAM. Is these sufficient? My database has something
> about 200MB size (summing all tables). My netware server is configured to
> use 64k blocks.
>
> Another proble I find: for example I want to delete ALL rows from a table.
> This table has 30000 row, for example.
> I use SQL command: delete from <table_name>
> The server seems VERY VERY budy when I do this. And it take a long time to
> tell me: rollback segment wasn't enought. Am I doing something wrong?
> I've looked in orclalrt.log file and there A LOT AND A LOT of lines like
> this:
> "IN_APLICA
> ORA-1653: unable to extend table PUKET.COEMCA by 5 in tablespace"
>
> IN_APLICA is my TABLESPACE AND PUKET.COEMCA is the table I wanted to
delete
> all rows
> and this:
> "IN_APLICA
> ORA-1654: unable to extend index PUKET.COENE3 by 210 in tablespace"
>
> there are a lot of lines like this too:
> Thread 1 advanced to log sequence 25058
> Current log# 2 seq# 25058 mem# 0: PROD:ORACLE\DATABASE\log1orcl.ora
>
>
> I didnt undertand what do you mean with 'checkpoint not complete'
>
>
> Thanks for helping. I'm waiting new message. Thanks so much!
>
>
> Daniel Cukier
>
>
>
> Ulrik Hoffmann <ulrik_at_hoffmann-kiel.de> escreveu nas notícias de
> mensagem:7jmkdf$lge$1_at_freeside.cls.net...
> >
> > > I've been making tests with my Oracle and I think it's very slow! I'd
> like
> > > to know how do I proced to make Oracle Work faster. What are the steps
I
> > > should take to get better perfomance on SELECTING, DELETING, or
UPDATING
> > > tables? I think my server is not correctly configured. I use Oracle 8
in
> a
> > > Netware Server.
> > >
> >
> > This is a thing which can have millions of causes:
> > Oracle normally recommends a lot of steps to tune, like
> > - Tune the Memory (we need the parameters shared_pool_size,
> > db_block_buffers, db_block_size,
> > sort_area_size)
> > in your initora.log - file (is used during startup)
> > you can get the parameters as well with select * from v$parameter as
> > system
> > - Tune the redo-logs (look at your orclalrt.log file (alert file), are
> there
> > any lines like
> > checkpoint not complete ?)
> > - Tune the Select statements (avoid unnecessary joining, use 'exists'
> > instead, and million
> > things more)
> > - Tune the database-model
> >
> > And then there are many more possible tuning-parameters...
> >
> > If you have more questions, I need the above mentionened parameters and
> > how much RAM the server has.
> >
> > Bye
> > Uli
> >
> >
>
>
Received on Fri Jun 11 1999 - 03:26:30 CDT
 |
 |