-
It is a basic question , how to configure the sga size on machines with very large memory.
My production database runs on Sun E4500 with 12CPU/12G memory. And the top wait event is the db file sequential read and log file sync. It is because of slow disk system.
To tune the db file sequential read, i have tuned most of the sql using the best index, and compressed some important indexes. Maybe i should enlarge the data buffer size , maybe this helps.
So, the question of how to configure the proper sga size appear. We are using veritas QuickIO datafile, so filesystem buffer does not help much(though Cached Quick IO maybe helps, but it should not be good as a larger data buffer size)
I gathered some data about my system:
vmstat 2:
procs memory page disk faults cpu
r b w swap free re mf pi po fr de sr s6 s9 s1 sd in sy cs us sy id
0 0 0 8966720 380928 4 0 0 0 196 0 28 0 0 0 33 4048 14112 10178 12 4 84
0 0 0 8966720 380912 3 0 0 0 156 0 24 0 0 0 109 4325 15674 10952 16 5 79
0 0 0 8966720 380792 13 0 0 0 1080 0 205 0 0 0 116 3951 12751 9566 14 4 82
0 2 0 8966720 380928 1 0 0 0 72 0 8 0 0 0 88 3762 12588 9174 12 4 85
0 0 0 8966720 380824 2 0 0 0 260 0 38 0 0 0 56 3766 12634 9013 11 6 84
0 0 0 8966720 380904 1 0 0 0 204 0 30 0 0 0 52 3972 13474 9611 22 4 74
0 0 0 8966720 380896 6 0 0 0 328 0 47 0 0 0 81 3845 13669 9349 17 5 78
It seems that there is always that much free memory pages:
average: 380K Page * 8KB/Page = 3040 MB
top -s 1:
last pid: 20319; load averages: 2.96, 2.71, 2.59 20:55:38
453 processes: 448 sleeping, 1 running, 4 on cpu
CPU states: 78.3% idle, 15.3% user, 5.7% kernel, 0.6% iowait, 0.0% swap
Memory: 12G real, 372M free, 5308M swap in use, 8756M swap free
oracle@main-db1$prtmem
Total memory: 11904 Megabytes
Kernel Memory: 418 Megabytes
Application: 5072 Megabytes --sga 4.7GB+
Executable & libs: 83 Megabytes
File Cache: 5963 Megabytes
Free, file cache: 366 Megabytes
Free, free: 5 Megabytes
18:21:15 SQL> show sga
Total System Global Area 4711026364 bytes
Fixed Size 102076 bytes
Variable Size 415678464 bytes
Database Buffers 4294967296 bytes
Redo Buffers 278528 bytes
21:13:39 SQL> show parameter sort_area
NAME TYPE VALUE
------------------------------------ ------- ------------------------------
sort_area_retained_size integer 0
sort_area_size integer 1048576
oracle@main-db1$swap -l
swapfile dev swaplo blocks free
/dev/dsk/c0t10d0s1 32,73 16 8392048 8294992
I think i can enlarge the data buffer to a larger value, for example 6G, but i am not sure the optimal value, For this is a 7*24 System, we reboot only when maintenance after several monthes. I cannot try.
Please share your valuable experience.
Thanks.
-
add some more information:
21:39:27 SQL> select a.name, sum(b.value) from v$sesstat b,v$statname a
21:39:35 2 where a.statistic#=b.statistic#
21:39:42 3 and a.statistic# in (16,15,20,21,188)
21:39:45 4 group by a.name;
NAME SUM(B.VALUE)
---------------------------------------------------------------- ------------
session pga memory 238548664
session pga memory max 243857160
session uga memory 73514488
session uga memory max 144877192
sorts (memory) 26083641
5 rows selected.
21:42:43 SQL> select status,count(*) from v$session group by status;
STATUS COUNT(*)
-------- ----------
ACTIVE 22
INACTIVE 372
2 rows selected.
www.cnoug.org
-
change following parameters (see oracle docs).
log_buffer
log_checkpoint_interval
DB_WRITER_PROCESSES
DB_BLOCK_LRU_LATCHES
sort_area_size
db_file_multiblock_read_count
-
I think you misunderstand my question.
The question is what will be the proper size of the data buffer that give the oracle the best performance, without making the OS paging/swapping, not how to enlarge my sga
If a machine has 32GB of physical memory, then the proper sga size will be 1/2 of physical memory, or 25GB, leaving 7 gb for os and client connection?
Of course we assume this is a dedicated database server.
-
xyz2000 might just want to suggest you some solutions to decrease the disk I/O waittimes. Adequate I/O distribution will also give you a high total waittimes on disk I/O waitevents.
Oracle Certified Master - September, 2003, the Second OCM in China
*** LOOKING for PART TIME JOB***
Data Warehouse & Business Intelligence Expert
MCSE, CCNA, SCJP, SCSA from 1998
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|