Login or Register to Ask a Question and Join Our Community


AIX

Oracle performance optimum vs. SGA memory allocation

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Operating Systems AIX Oracle performance optimum vs. SGA memory allocation
# 1  
Old 09-29-2011
Question Oracle performance optimum vs. SGA memory allocation
Dear all experts,
I have a p750 Power 7 3.3GHz server with 4 processors and 48GB Memory. This is a DB server which is using Oracle 9i.
I have been told that Oracle 9i can only allocate 10GB as SGA Max to get the oracle optimum performance. Anything more will result in overflow of memory and will impact oracle slowness.
I would like to know is the above statement a correct statement that Oracle 9i cannot take more memory when I have so much to offer?
I do always think that the more memory we have, the faster the server running but it seems not the same case in this situation.
May I have experts view of this situation so that I can have the server to be run much more performance sound and utilised as many of memory as possible.

Below is the initABCHOST.ora
Code:
*._kgl_latch_count=34
*.aq_tm_processes=1
*.audit_trail='DB'
*.background_dump_dest='/app/oracle/admin/ABCHOST/bdump'
*.compatible='9.2.0.1.0'
*.control_file_record_keep_time=7
*.control_files='/data05/HLAPROD/control01.ctl','/data10/ABCHOST/control02.ctl'
*.core_dump_dest='/app/oracle/admin/ABCHOST/cdump'
*.cpu_count=4
*.cursor_sharing='FORCE'
*.db_block_size=8192
*.db_cache_advice='ON'
*.db_cache_size=3674210304
*.db_domain=''
*.db_file_multiblock_read_count=32
*.db_files=300
*.db_keep_cache_size=1572864000
*.db_name='HLAPROD'
*.db_writer_processes=4
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ABCHOSTXDB)'
*.dml_locks=4860
*.enqueue_resources=5124
*.fast_start_mttr_target=300
*.hash_join_enabled=TRUE
*.instance_name='ABCHOST'
*.java_pool_size=15286400
*.job_queue_processes=10
*.large_pool_size=576777216
*.log_buffer=2048000
*.log_checkpoint_interval=10000000
*.log_checkpoint_timeout=0
*.log_checkpoints_to_alert=TRUE
*.max_enabled_roles=120
*.open_cursors=2000
*.optimizer_index_caching=70
*.optimizer_index_cost_adj=100
*.pga_aggregate_target=3221225472
*.processes=1500
*.query_rewrite_enabled='TRUE'
*.remote_dependencies_mode='SIGNATURE'
*.remote_login_passwordfile='EXCLUSIVE'
*.resource_limit=TRUE
*.session_cached_cursors=500
*.sga_max_size=10737418240
*.shared_pool_reserved_size=247815065
*.shared_pool_size=1503013120
*.sort_area_size=4194304
*.star_transformation_enabled='FALSE'
*.timed_statistics=TRUE
*.transactions=2000
*.undo_management='AUTO'
*.undo_retention=10000
*.undo_suppress_errors=TRUE
*.undo_tablespace='UNDOTBS2'
*.user_dump_dest='/app/oracle/admin/ABCHOST/udump'
*.utl_file_dir='/data06/utlfiles'
*.workarea_size_policy='AUTO'

Thanks.
Last edited by zxmaus; 09-29-2011 at 02:13 AM.. Reason: added code tags
# 2  
Old 09-29-2011
You should not size the SGA according to what you have in the box but according to what your DB needs. If your SGA is too big this can dramatically slow down performance. If it is not big enough, the same is true. And apart from an SGA you do have a PGA too - sometimes it makes more sense to give more memory to the PGA instead of extending the SGA and so on.

Oracle is a thread based DB, so how much resources you need obviously depends on how many connections you have in parallel - a box with 20 connections per day needs a lot less than a box with 3000 connections in parallel - each of those connections is forking a process, each of those processes can use up to (size of one pp in the corresponding VG) memory - and if your box is doing anything else apart from the DB - and what kind of load your DB has - Data warehouses have a very different utilization pattern than Traders for example. And are you doing EOD batches, what kind of backups are you running, how big are your tables and so on.

Regards
zxmaus
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Advanced & Expert Users

Mapping Oracle SGA memory into physical files in Solaris.

Is there a way by which we could map Oracle SGA memory into physical files in solaris. We could find these physical files in /dev/shm/ folder in linux environment but they are missing in solaris. (1 Reply)
Discussion started by: arjun_chauhan
1 Replies

2. UNIX for Dummies Questions & Answers

Memory allocation problem

I am using ubuntu. I have written a program to calculate prime factors. it works perfectly fine till entered number is less than 9989 (or so ) but when one enters a number higher than that, for example 15000, it does not work. Can anyone guide me whats the problem ? although new codes are welcome,... (2 Replies)
Discussion started by: Abhishek_kumar
2 Replies

3. Programming

memory allocation for string in C

hi in the following code, how the memory is allocated for a1 which holds the values of a2 after cpy function call. #include <stdio.h> #include <string.h> void cpy(char* d, const char* s){ while(*d++=*s++); } main(){ char* a1; char* a2="done"; cpy(a1,a2); ... (3 Replies)
Discussion started by: mprakasheee
3 Replies

4. Shell Programming and Scripting

memory allocation to a variable

hello all.. i'm a beginner in shell scripting. I need to know what is really happening when we are creating a variable in shell scripting? how memory is allocated for that variable? (3 Replies)
Discussion started by: aarathy
3 Replies

5. Programming

Memory Allocation Query

When we dynamically allocate the memory say 100 integers say int *x = new int(1000); then does entire chunk of memory gets allocated at once after the completion of the statement? I mean will the the concept of page fault come into picture over here? (3 Replies)
Discussion started by: rupeshkp728
3 Replies

6. Programming

Memory allocation in C

Hi Experts I need some help in static memory allocation in C. I have a program in which I declared 2 variables, one char array and one integer. I was little surprised to see the addresses of the variables. First: int x; char a; printf("%u %u\n', &x, a); I got the addresses displayed... (2 Replies)
Discussion started by: unx_freak
2 Replies

7. Programming

memory allocation in subroutine

Hi everyone, I'm not new to C programming, but I'm having question regarding the memory allocation of a pointer variable which, for instance, will be declared in main(), but its memory will be allocated in subroutine. To clearify my question, I provide a small working example: #include... (1 Reply)
Discussion started by: MIB_Maik
1 Replies

8. HP-UX

HP-UX memory usage allocation

Hi all, I have a HP-UX Server with 4 gigabytes of physical RAM. When I use the 'Glance' utility to see what my memory utilization is, my memory usage shows up maxed out at 99%. I shut off all the known processes that I'm running on that box and the memory utilization is still at 78% (with Swap... (3 Replies)
Discussion started by: dehuang83
3 Replies

9. UNIX for Dummies Questions & Answers

HP-UX memory usage allocation

Hi all, I have a HP-UX Server with 4 gigabytes of physical RAM. When I use the 'Glance' utility to see what my memory utilization is, my memory usage shows up maxed out at 99%. I shut off all the known processes that I'm running on that box and the memory utilization is still at 78% (with Swap... (1 Reply)
Discussion started by: dehuang83
1 Replies

10. UNIX for Dummies Questions & Answers

memory allocation

I would like to know how I could allocate some more memory to a process. Please note that I am not the root user. (1 Reply)
Discussion started by: sagar
1 Replies
Login or Register to Ask a Question