Re: Oracle Performance Questions

On Thu, 23 Feb 2006 08:07:35 -0800, DMina wrote:

>
> 1. I have read that JFS and Large File Enabled filesystem is not
> advisable! Do you have any comments on this? My filesystems (all
> datafiles) are Large File Enabled.

Are you sure that this is JFS? It may be JFS2, which is world of a difference. What exatly are the differences can be seen at: http://tinyurl.com/joxvm
Also, if it is JFS2 (and, with 5.2, it most likely is) you should enable concurrent I/O, which makes it possible for 2 or more processes to concurrently write at the same inode. Detailed explanation of the advanced I/O features of the JFS file system is available here: http://www-03.ibm.com/servers/aix/whitepapers/db_perf_aix.pdf

>
> 2. If you are using bufferred I/O does it make any sense to have
> multiple buffer caches? (db_16K_cache_size etc) My db_block_size is 8K

Buffered vs. direct I/O doesn't have anything to do with the multiple buffer caches. You must decide whether to split your memory resources into two or more disjoint parts, so that underutilized parts cannot be reused by other components. You also have to decide why would you want to do that. I don't.

>
> 3. If i make use of Direct I/O what changes do i have to change on OS?
> (except of course mounting the filesystem using dio)

You must make it mauve in color, as it takes the least RAM.

>
> 4. Do you use LOCK_SGA=TRUE and if yes what do i have to have in mind
> in terms of OS configuration parameters?

I don't use it and I wouldn't use it without a good reason. Why would you want to use it and what do you expect to gain? Also, I find your line of questioning somewhat disturbing, as it points to the mentality of tuning instance and not the applications. You tune instance and change parameters as a reaction to the particular problem, you don't set "optimal parameters". LOCK_SGA and PRE_PAGE_SGA are far beyond the normal tuning of "standard parameters".

If you want to know more about Oracle, I must advise the following three books:

1. Expert Oracle Database Architecture, by T. Kyte.
2. Optimizing Oracle Performance, by C. Millsap, Jeff Holt.
3. Effective Oracle by Design, by T. Kyte.

These 3 books are mandatory for being a DBA. In addition to them, I'd recommend the following ones:

1. Cost Based Oracle Fundamentals, by Jonathan Lewis (This one is hard reading, but worth every invested minute. Not for the faint hearted people)
2. Tuning SQL, by Dan Tow
3. Oracle Wait Interface, by Richmond Shee, Kirti Deshpande and Gopal.
4. Not really needed, but any book from O'Reilly Publishers with a camel or llama on the cover. DBA must know a scripting language. I don't care which one is it, as long as it is Perl.
5. Effective Oracle Database 10g Security by Design, by D.Knox. (This book elicited very few comments so far, but I am unclear why is that, as the book is extremely well written and covers topics of extreme importance. This book is one of my favorites. May be, Oracle should have put a beautiful blonde on the cover, instead of David Knox?)

-- 
http://www.mgogala.com