Operating System - HP-UX
1747984 Members
4729 Online
108756 Solutions
New Discussion юеВ

Performance on ORACLE 8.0.6 !

 
SOLVED
Go to solution
Ivan Azuara
Regular Advisor

Performance on ORACLE 8.0.6 !

Happy Valentines Day !

In resume if i have the next:

-HP9000/K580
-HPUX/11.00/64 bits
- 4 CPU PA8200/240 Mhz
- Physical Memory:3715.3MB Real Memory:
Active:86402.7 KB
Total:128209.5 KB

Virtual Memory: Active:230183.1 KB
Total:366240.0 KB
Free Memory Pages:7739 at 4 KB/page SwapSpace: Avail:5124MB
Used:2289 MB
-ORACLE 8.0.6 64 bits:
* content of init.ora file:
db_files = 80
db_file_multiblock_read_count = 8
db_block_buffers = 100
db_block_size = 64
shared_pool_size = 3500000
log_checkpoint_interval = 10000
processes = 50
parallel_max_servers = 5
log_buffer =8192
sequence_cache_entries = 10
sequence_cache_hash_buckets = 10
max_dump_file_size = 10240 # limit trace file size to 5 Meg each
control_files = (ora_control1, ora_control2)

Parameters Kernel :

- dbc_max_pct 50
- dbc_min_pct 5
- bufpages 0
- maxdsiz 0X11E1A300
- maxdsiz64_bit 0x0000000040000000
- maxssiz 0x00800000
- maxssiz_64 bit 0x00800000
- maxswapchuncks 4096
- maxuprc 500

In base to their recommendations, just need to change the value for "dbc_max_pct to 10", make some changes in the init.ora file about "db_block_buffers,db_block_size,shared_pool_size" ?

With this changes my use of memory change 99% to 50% aprox.?

The time of respose of my application will incrementate ?.

Best Regards !

"Enjoy the life .."
8 REPLIES 8
Christopher McCray_1
Honored Contributor

Re: Performance on ORACLE 8.0.6 !

One change that definately sticks out is that dbc_max_pct be changed to 10.

I will not comment on the init.ora changes as that is not my forte (Apologies).

Best of luck

Chris
It wasn't me!!!!
Christopher McCray_1
Honored Contributor
Solution

Re: Performance on ORACLE 8.0.6 !

Add on...

Yes, your memory usage will drop dramatically; about 40%.

Chris
It wasn't me!!!!
Ian Dennison_1
Honored Contributor

Re: Performance on ORACLE 8.0.6 !

Changing the 'dbc_max_pct' to a low value will increase the amount of space available for other processes to run in.

However, if you want to use this space for Oracle, you must increase the SGA size.

I would take 2 steps for this,...

Step 1 - Reduce dbc_max_pct and watch what memory is now free. Watch for 1 or 2 days in the week.

Step 2 - Increase the Oracle SGA to use some of the memory now free.

The 'show sga' command in Oracle's 'svrmgrl' will show the configuration of the SGA.

Bon chance! Ian Dennison
Building a dumber user
Wodisch
Honored Contributor

Re: Performance on ORACLE 8.0.6 !

Hello Ivan,

hopefully you have lots of typos in there, as some values are very, very bad ;-)

db_block_buffers = 100

that's a joke, isn't it?
You omitted two zeros, right?

db-block_size = 64

that's impossible, you mean KB, right? Which would be a little bit large...

The product of the two should be several hudred megabytes, if not even reaching into gigabytes to make Oracle really fast...

shared_pool_size = 3500000

that's too low, make it at least bigger than 50MB

log_buffer = 8192

way too small, how about half a megabyte or one megabyte?

and finally the silly (hp's sillyness)

dbc_max_pct 50

which I would not even *use*, but set

bufpages 100000

that would give you a fixed amount of 400MB UNIX buffer cache, even if you install more RAM later (and its kind of maximum for HP-UX11.00).

You do not tell us wether you use raw-devices or data-files, but in case you do the latter, I hope you have the "Online JOurnaled Filesystem" installed and use the mount-options "-o convosync=direct,mincache=direct" for the filesystem containing the Oracle datafiles (and ONLY those).

You'll probably have to tune a lot more of the "standard" UNIX tunable parameters, too.

Just my $0.02,
Wodisch
Bill Thorsteinson
Honored Contributor

Re: Performance on ORACLE 8.0.6 !

Reducing memory allocated to Oracle does not necessarily significantly reduce performance. SGA size needs to be sufficient to store the queries and user objects.

Most of the memory should be for block buffers. 64k blocks are generally far too large. Depending on record sizes 8K or 16k are typical. For best performance you need sufficient block buffers to store the blocks required to run your queries in memory. Once you achieve this level more buffers will have minimal impact. If you go below this level the impact can be significant. I am aware of one case where the impact was 2 order of magnitude.

Memory allocated to block buffers should generally be a percentage of the allocated space in the tablespaces. My development databases run in the order of 100MB with lots of free space in the tablespaces so I would expect 50MB of block buffers to be more than sufficient
Wodisch
Honored Contributor

Re: Performance on ORACLE 8.0.6 !

Ivan,

if you never assign points (or "N/A") to answers, we cannot help you - as we then do not know what was helpful and was not!

Besides it would be just the polite thing to do ;-)

Regards,
Wodisch
Volker Borowski
Honored Contributor

Re: Performance on ORACLE 8.0.6 !

The very problem with db_block_size is:

You can NOT change it ! It is set fixed when you create the database. It will require re-creation of the database to change it!

Since you have some memory, go for more db_block_buffers first and stay with the db_block_size. This one can be delayed to a more fitting point in time.

Volker
Dennis J Robinson
Frequent Advisor

Re: Performance on ORACLE 8.0.6 !

Ivan,

You will need to rebuild this database, I don't think that db_block_size of 64 is even valid, Oracle probably has used the 2048 block size.

My questions:

What type of database is this?

What is it used for?

How many users connected?

My suggestions:

do a full export of your database.

I have made changes to your init.ora settings which WILL provide gains:


* content of init.ora file:
db_files = 256
db_file_multiblock_read_count = 32 # larger read buffer == lowered CPU cost and greater throughput
db_block_buffers = 32768 # 100 is not good
for anything.
db_writer_processes=4 # same as # of CPU's
db_block_lru_latches=12 # multiples of db_writer_processes up to 6*#CPU's
db_block_size = 8192 # 8192 is the value most use today, use 16k for pure batch will show higher throughput, lowered CPU cost
shared_pool_size = 100000000 # heck you had shared pool larger than db_block_buffers
log_checkpoint_interval = 10000
processes = 50
parallel_max_servers = 5
log_buffer = 1048576
sequence_cache_entries = 10
sequence_cache_hash_buckets = 10
max_dump_file_size = 10240 # limit trace file size to 5 Meg each
control_files = (ora_control1, ora_control2)

Parameters Kernel :

- dbc_max_pct 20
- dbc_min_pct 5
- bufpages 0
- maxdsiz 0X11E1A300
- maxdsiz64_bit 0x0000000040000000
- maxssiz 0x00800000
- maxssiz_64 bit 0x00800000
- maxswapchuncks 4096
- maxuprc 500
- shmmax 0x40000000
- shmmni 1024
- shmseg 256
- semmni 2048
- semmns 1024
- semmap 2050

You know the drill