Oracle Parameter confusion

[Tim Downs]

I'm looking for some input on both kernel and init.ora prameters. We are moving from a K570 with 2 200mhz processors and 2 gig ram to a rp5405 with 2 675mhz processors and 4 gig ram.
I have setup 8gig of swap on the new server. The server has only 1 database - 18 gig and also runs a web server (Netscape Enterprise). I confused about what the oracle parameters should be after reading many suggestions. I have attached a copy of the contents of the kernel parameters and init.ora file. The users are expecting a performance increase with the new server but I know if the parameters aren't correct they won't notice much. Thanks

[Helen French]

It would be difficult to give you an exact value for each paramegter. I would go through the Oracle installation guide and then tune the parameter depends up on your requirements, instances, users etc. You may also need to consider about other user processes running on the system and the web server requirements, while tuning the kernel. A little work and investigation needs to be done from your side in order to make the best kernel values to get the best performance. To start with, I would defenitely consider lowering the value of dbc_max_pct.

[Helen French]

Some useful discussions here:

http://forums.itrc.hp.com/cm/QuestionAnswer/1,,0x7acacf38d6bdd5118ff10090279cd0f9,00.html

http://otn.oracle.com/documentation/content.html

http://forums.itrc.hp.com/cm/QuestionAnswer/1,,0xbfcb7680e012d71190050090279cd0f9,00.html

[Julio Yamawaki]

Tim,

Can you send more information about your DB?
Like: number of concurrent users, tablespaces, number of concurrent user of the web server.
What version of Oracle are you going to use?
For now, I have only 3 suggestions: put, at least, 1 GB to shhmax, dbc_max_pct to 10 and swapmen_on to 0.
Performance can be increased from K570 to rp5405, we are talking about 10350 tpm to 23800 tpm, also, disks have a much better performance.
Talking about disks, how many drives do you have? Is's important to divide I/O.

[A. Clay Stephenson]

The very first thing to do is reduce max_dbc_pct to no more than 20% (800MB and that's very generous). Actually, if it were me, I'd set bufpages to somewhere around 100000 (400MB) as a good, fixed starting point.

I would never initially setup a box with 2x swapspace; if you think you need that much, you are doing it wrong. The minute you begin to pageout, don't even talk about performance.

Look at Oracle's suggestions for shared memory, kernel threads, messages, and semaphores and make sure that you meet or exceed them and also make sure that you have installed the latest HardWare Enablement (HWE) and Quality Packs GOLDQPK HP-UX patches.

I tend to be a bit more conservative than most; I suggest that you get the kernel tuned before you start changing init.ora parameters.
Otherwise, you are throwing too many variables at the problem at once.

Now, here's the really bad news. It's possible that your hardware upgrades - even with "perfect" tuning - may have minimal effect. If your application and/or SQL code is really bad then anything you do at the OS/Hardware level isn't going to make nearly the difference that you expect. It's very possible that a single additional index could exceed the benefits of all your upgrades.

[malay boy]

Hi,
Totally agreed with Clay,I spent about 6 month study our oracle database performance(trust me it's the worst 6 month of my sysadmin life).A lot of thing have been done on hardware,moving the datafile,study the index , read the utlb and utle stat but improvement very minimal.End up we check the SQL and it's poorly written,a lot of join on big table.
Trust master Clay he know what he's taking about.
But check the oracle installation manual .Make sure you comply with the requirement.It should be a head start.

p/s: some tip on joining 3 big table.Do not join them in one sql statement , big mistake.What you do is join 2 big table and stored the result in temp table.Next join that temp table to third table.

Hope this a bit help

regards
mB

[Tim Downs]

The version of oracle is 8.1.6 and we have about 50 concurrent users. The database is only about 18 gig in size (4 years old). The webserver can have up to 300 users but my guess is that it would have about 20 users on it at the same time. The web server is used for requistions so user place there order and log off.

[Indira Aramandla · Accepted solution]

Hi Tim,
From your information I gather that your Oracle database is on 8i RDBMS of 16K BLOCK_SIZE with about 50 concurrent users. The following are the init.ora parameters that you posted.

open_cursors = 500 -----The overhead is about 250 bytes per open cursor within the shared pool.
A common problem with open cursors is within the applications. For example, forms that call forms, etc. In this case, the cursors of the calling form remain open.

max_enabled_roles = 30 ----- Specifies the maximum number of database roles that users can enable, including roles contained within other roles. The actual number of roles users can enable is 2 plus the value of MAX_ENABLED_ROLES, because each user has two additional roles, PUBLIC and the user's own role. Default value 20 and range of values is 0 to 148

db_block_buffers = 2048 --- Refer previous posting for details
shared_pool_size = 512000000 --- Refer previous posting for details

large_pool_size = 52428800 ---- By default there is no large pool allocated, it must be explicitly configured. The main use of the large pool i)For the User Global Area (UGA) of sessions connected using MTS (multi-threaded server) ii).Buffering for sequential file IO. iii). The large pool is also used for allocation of parallel execution buffers if PARALLEL_AUTOMATIC_TUNING is TRUE.

java_pool_size = 0 ---The default size for the java pool is 20 megabytes. You can set it much lower if you are not using java. For example 100k seems about the best for a non-java environment.

log_checkpoint_interval = 10000 --- Refer to previous posting
log_checkpoint_timeout = 1800 ----Specifies the amount of time, in seconds, that has passed since the incremental checkpoint at the position where the last write to the redo log (sometimes called the tail of the log) occurred. Specifying a value of 0 for the timeout disables time-based checkpoints. Hence, setting the value to 0 is not recommended.

processes = 200---Specifies the maximum number of operating system user processes that can simultaneously connect to Oracle. Its value should allow for all background processes such as locks, job queue processes, and parallel execution processes. The default values of the SESSIONS and TRANSACTIONS parameters are derived from this parameter. Range of values 6 to operating system-dependent

log_buffer = 163840-----Refer previous posting for details
db_block_size = 16384-----Refer previous posting for details

The parameters which affect database tuning are:-

DB_BLOCK_BUFFERS
SHARED_POOL_SIZE
SORT_AREA_SIZE
DBWR_IO_SLAVES
ROLLBACK_SEGMENTS
SORT_AREA_RETAINED_SIZE
DB_BLOCK_LRU_EXTENDED_STATISTICS
SHARED_POOL_RESERVED_SIZE

Refer to the attached document "init_parameters_affecting_performance.doc" for more details on the above parameters.

As a system administrator and DBA, you will often be confronted with users who say that the response time on the system is unacceptable.
When performance-tuning Oracle databases there are three subcategories to be looked after.
1. Application tuning
2. RDBMS tuning (database structure and parameters)
3. UNIX system tuning
Refer to the attached document for some hints on "Performance Tuning Approaches

[Indira Aramandla]

Here is the other attachment t "init_parameters_affecting_performance.doc"