Re: Who (has) to prepare more ? Oracle vs. MSSQL DBAs

>One other thing I would like to hear is, where I can
>find a paper that describes shortly how to change the
>default settings to a reasonable starting configuration
> without having to read hundreds of pages on performance
>tuning.

I wish there was such a paper. There isn't yet I'm afraid!

It's never worth trying to cover all tuning issues in 5 mins, and any advice that tries to is usually too generalised. Hence I will mention an area of tuning which may help things.

In the initORCL.ora file ( *\orant\Database or similar) you will see a line for

db_block_buffers = xxxx.

Oversimplifying things a lot, the amount of RAM that Oracle uses for temporarily storing data (called the buffer pool) has a size of :-  

db_block_size * db_block_buffers

If you use SQL*PLUS and logon (at the server) with e.g. username SYSTEM , password MANAGER (unless you have changed it) and connection string of BEQ-LOCAL.WORLD Then if you type the following (hit enter at the end of each line) :-

SQL> COLUMN NAME FORMAT A30
SQL> COLUMN VALUE FORMAT A30   Then :-

SQL>Select name, value     
  2>FROM v$parameter               
  3>Where   name LIKE '%block%';    

You should get something like :-

NAME                                     VALUE
---------------------------------------- ------------------------------
db_block_buffers                         8000              
db_block_checksum                        FALSE
db_block_size                            2048        
db_block_checkpoint_batch                8
db_block_lru_statistics                  FALSE
db_block_lru_extended_statistics         0
db_block_lru_latches                     1
db_block_max_dirty_target                4294967294
log_block_checksum                       FALSE
db_file_multiblock_read_count            8
delayed_logging_block_cleanouts          TRUE
hash_multiblock_io_count                 1

12 rows selected.

SQL> This was done on an 8.0.5. E.E. installation so your resultset will probably look slighly different.

As you can see, db_block_buffers and db_block_size are both listed. The first is a straight count and the second is in bytes.

Increasing the number of db block buffers after a default installation sometimes this has an amazing performance improvement because instead of having to go to disk (as it has to) for lots of different reasons, (redo / rollback / datafile writes and many other reasons) Oracle can do a lot of its work by referring in nanoseconds to whatever is in memory (depending on the speed of memory in ns and what kind of cache there is on the cpu (L2 etc.).

After all, you only need 1.3 MBytes of block buffer storage, before Oracle can hold your 1000 rows table completely in memory. (1000 tuples * 15 columns of 100 bytes max)

Now you need to give Oracle a buffer cache much more than 1.3 Mb, because it needs buffers for storing data that is being updated (stores copies of the original data), it needs to give some buffers over to the shared pool which is its own working 'cache' if you will, so why not try giving Oracle say 10 or 15 MB of just buffer cache. You will then be able to test larger table sizes later on without needing to restart the Oracle Server (NT Services). So

15,000,000 / db_block_size = number of db_block_buffers you should set in the initORCL.ora file.

So using my example above , db_block_size is 2048 bytes so I maybe try something like

db_block_buffers = 7500

in initORCL.ora.

Inserts and Updates are certainly worth tuning, but this is harder to do on a typical NT box a) because there's maybe only 2 disks at most and b) for some reason Oracle weren't so hot on allowing DBAs to tune the redo_buffer_size setting (which if made larger can make the writes to the redo logs more efficient during a data insert or commit).

So backup your current initORCL.ora file, then change db_block_buffers = 7500 , (re)save the initORCL.ora file, stop the Oracle Services (NT), restart them and then run your performance tests again.

Interested to see if that makes the tests go any faster.

David P.

Oracle Certified DBA.