-
Can someone help me with SGA tuning. What is the standard for SGA values.
I am running 8i (8.1.5) on Solaris 2.6, total allocation to SGA is 100MB of 2GB (total memory on the system). Out of this total:
54 MB buffer cache with hit ratio of 96.8 %
45 MB shared pool with hit ratio of 74.5 %
171 KB redo buffer
Drilling down furhter:
data buffer cache: 15% on average and 60% max.
In regards to the above information, is the data buffer cache considered to be part of the buffer cache? If they are one in the same, what is an acceptable value for it? What is the standard for shared pool hit ratio?
on another note, we are only using 100 MB of SGA which left us almost 1.8GB of memory. However, when I ran top, only 45 MB of memory is free. when I sorted by RS memory, oracle processes comes right on top. What should I do next? Looking into turning off certain function in oracle which we are not using such as JAVA? How do I find out what is causing the memory consumption? I am tempted just to run the system without starting oracle, just to check out how much is the system using memory. This way I know for sure, that's it's oracle if the system is not consuming memory.
The fool i used to get the above information is Tora. I am not sure of it's accuracy. But people who uses it, gives it good praises.
Thanks in advance for your help.
-
Top lies. It doesn't handle shared memory very well. The better way to tell your shared memory is by ipcs or simply doing a /usr/ucb/ps -aux for the smon process and looking at the %MEM value. With all that memory, I would at least DOUBLE your shared_pool_size and db_block_buffers.
Jeff Hunter
-
It is a bit strange that with 2G RAM you have only 54 MB buffer cache and a 45 MB shared pool. Do you have other instances running on that machine? Increase the values as Jeff suggested.
-
Juilian, there is only one instance running and Yes, I agreed totally that the size of the SGA should be larger considering that we have 2GB of memory available. But I am not the senior DBA at this place, every time I open my mouth about tunning it, the Senior guy get very offended. I believe the database is not fully optimized, but he seems to have a very diffrent opinion than mine. I am hoping to find some justification that the SGA needs tunning and that is why I am looking into the it. I hope you guys can clarify that the SGA is not size correctly considering the physical memory we have.
what's more, the database is running in dedicated server mode, for christ sake shouldn't it be in MTS; I think solaris 2.6 running on an E450 with 2 cpu can handle it. On top of this, he has connection pooling and multthreading on. There's two things wrong here. One, connection pooling and multiplexing is mutually exclusive. Two, I don't think dedicated server can support pooling and multiplexing.
Sorry for renting at this forum like this. Thanks for your input, I will look into the memory issue with ipcs and ps command. I will let you know of the results.
-
Originally posted by tekion
what's more, the database is running in dedicated server mode, for christ sake shouldn't it be in MTS; I think solaris 2.6 running on an E450 with 2 cpu can handle it. On top of this, he has connection pooling and multthreading on. There's two things wrong here. One, connection pooling and multiplexing is mutually exclusive. Two, I don't think dedicated server can support pooling and multiplexing.
No, multiplexing and pooling won't support dedicated servers. You have to be in MTS mode.
Multiplexing and Pooling are mutually exclusive. In fact, Oracle recommends you don't run the two together on the same dispatchers.
MTS carries a lot of headaches with it. Unless you are running with a large number of users (> 200+), I wouldn't even consider MTS. Personally, I wouldn't consider using multiplexing or pooling unless my users were approaching 1000.
Jeff Hunter
-
Well, usually the Sen. has the final word. I have been in your situation and I understand how it feels. Try to get a job where you will be the Sen.
Jeff gave you a very good follow up. I am also against Shared Server if not absolutely needed.
When you ask the Sen. "How do you expect to have connection pooling and multiplexing (which are BTW mutually exclusive) in a dedicated server?" what is his answer :-)
-
morist89,
here's a pre-formatted output from /usr/ucb/ps -aux:
USER %MEM COMMAND
oracle8i 5.6 ora_i201_PROD
oracle8i 5.6 ora_pmon_PROD
oracle8i 5.6 ora_dbw0_PROD
oracle8i 5.6 ora_lgwr_PROD
oracle8i 5.7 ora_ckpt_PROD
oracle8i 5.8 ora_smon_PROD
oracle8i 5.7 ora_reco_PROD
oracle8i 5.7 ora_snp0_PROD
oracle8i 5.7 ora_i101_PROD
oracle8i 5.7 ora_i102_PROD
Total percentage 56.7 which is 1.61216 GB out of 2GB
Is this normatl memory consumption? Note, I only include background processes. With a total of 15 connection ( bec. we're running in dedicated server mode) taking up a total of 87.4% of memory(1.790GB of 2GB), see below:
oracle8i 5.9 (LOCAL=
oracle8i 6.0 (LOCAL=
oracle8i 5.9 (LOCAL=
oracle8i 6.0 (LOCAL=
oracle8i 5.8 (LOCAL=
oracle8i 5.9 (LOCAL=
oracle8i 5.9 (LOCAL=
oracle8i 6.1 (LOCAL=
oracle8i 5.7 (LOCAL=
oracle8i 5.7 (LOCAL=
oracle8i 5.7 (LOCAL=
oracle8i 5.7 (LOCAL=
oracle8i 5.7 (LOCAL=
oracle8i 5.7 (LOCAL=
oracle8i 5.7 (LOCAL=
With the above calculation, this gives me about a total of 3.4 Gb. So, the number does not add to 2GB. I take it this is because of share mem, each one of those processes is digging into share mem. How do I find out how much is each proccess using the share memory. In other words how do I take into account my calcualtion of share mem for each processes.
I think this may be the cause of my memory heist. Any input is appreciated.
Thanks.
On another note, Does anyone know what "ora_i" is? Thanks.
-
Originally posted by tekion
Total percentage 56.7 which is 1.61216 GB out of 2GB
Nope. Shared memory at work. All your background processes are using shared memory, probably about 5.6% of your physical memory.
Is this normatl memory consumption? Note, I only include background processes. With a total of 15 connection ( bec. we're running in dedicated server mode) taking up a total of 87.4% of memory(1.790GB of 2GB)
Nope again. Each connection is using about .3 -.4 % distinct memory. The rest is shared memory.
How do I find out how much is each proccess using the share memory.
ipcs and pmap are the keys to figuring out your shared memory segments.
Jeff Hunter
-
Morist89,
Why would you say that the background processes is only consuming 5.6% of memory? and the Local connection between .3 and .4 of memory? It doesn't make since when I check how much each processes is tapping into share mem. via pmap. Here's a sample output of it via "
./pmap -x 29709 | egrep "Address|total":
Address Kbytes Resident Shared Private Permissions Mapped File
total Kb 172768 140896 12024 128872
according to this, each procesesses consume about 12 Mb of share mem.
Could you explain why? Thanks.
-
Lets try it this way:
On one of my systems, I have various user sessions:
Code:
oracle8@oradev1 ibdev1$ ps -ef | grep oracleibdev1
oracle8 4618 4610 0 15:00:01 ? 0:01 oracleibdev1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle8 3649 1 0 13:48:21 ? 0:01 oracleibdev1 (DESCRIPTION=(LOCAL=no)(ADDRESS=(PROTOCOL=BEQ)))
If I use pmap on process 4618 and look for the shared memory:
Code:
oracle8@oradev1 ibdev1$ pmap -x 4618 | grep shared
80000000 264312 264312 - 264312 read/write/exec/shared [ ism shmid=0x79c00 ]
FF1D0000 8 8 8 - read/write/exec/shared [ anon ]
This tells me that my process is attached to a shared memory segment at location x79c00 ( or 498688). The size of this shared memory segment is 264312K or about 258MB.
If I then look at my shared memory segments using ipcs:
Code:
oracle8@oradev1 ibdev1$ ipcs -a
IPC status from as of Fri Jan 18 15:11:09 EST 2002
T ID KEY MODE OWNER GROUP CREATOR CGROUP CBYTES QNUM QBYTES LSPID LRPID STIME RTIME CTIME
Message Queues:
q 1400 0x5a005301 -Rrw-rw-rw- alexbkup alexbkup root root 0 0 65536 15567 15571 15:11:00 15:11:00 14:15:36
q 3301 0x5a005bc5 -Rrw-rw-rw- root root root root 0 0 65536 15597 15632 14:16:13 14:16:13 14:16:03
T ID KEY MODE OWNER GROUP CREATOR CGROUP NATTCH SEGSZ CPID LPID ATIME DTIME CTIME
Shared Memory:
m 498688 0x466822b8 --rw-r----- oracle8 dba oracle8 dba 34 270655488 4984 4618 15:00:01 15:00:01 9:28:10
m 179201 0xb2da34f4 --rw-r----- oracle8 dba oracle8 dba 13 97787904 16914 4620 15:00:01 15:00:01 10:25:43
m 31746 0x615110ec --rw-r----- oracle dba oracle dba 11 252207104 23409 4509 14:57:24 14:57:24 13:23:00
m 1918980 0x76ab35c0 --rw-r----- oracle dba oracle dba 10 252207104 3383 25198 14:35:33 14:42:24 15:31:09
T ID KEY MODE OWNER GROUP CREATOR CGROUP NSEMS OTIME CTIME
Semaphores:
s 38404096 0x9e423e14 --ra-r----- oracle8 dba oracle8 dba 204 15:10:52 9:28:10
s 20316161 0x9e417c74 --ra-r----- oracle8 dba oracle8 dba 54 15:11:07 10:25:43
s 2555906 0x54c69ac --ra-r----- oracle dba oracle dba 154 15:07:22 13:23:01
s 124911620 0xb0ce325c --ra-r----- oracle dba oracle dba 154 15:06:56 15:31:12
I can see that I am attached to shared memory segment where ID=498688.
Jeff Hunter
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
|