Re: Performance!

These values look like they're from the default orcl database the is created during install (if you let the installer do that). They are totally inadequate for a real database.

Increase the shared_pool to 9000000
Increase db_block_buffers to 5000
I'd increase the db_block_size to 4096, but you can't change the block size after the database is created. You'd need to create a new database with the larger blocksize.
I bet the log files are too small too. Is it the default 200K? I'd make them at least 2M. You can create new logs with the larger size and then drop the old ones. Increase the log_checkpoint_interval parameter as well when you do that so that you only get checkpoints when the log switches.

That's just a few ideas to start with.

Daniel Cukier wrote in message <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 Thu Jun 10 1999 - 15:45:58 CDT