General
cancel
Showing results for 
Search instead for 
Did you mean: 

How to familiarise my database's behaviour?

SOLVED
Go to solution
zhaogui
Super Advisor

How to familiarise my database's behaviour?

Can anyone tell me how I can know my Oracle8i database's behaviour on HPUX11, such as % of read/write, % of sequential/random read/write?
How to know if SGA is enough?
If Oracle doesn't use OS buffer cache, can I reduce it by reducing dbc_max_pct?
9 REPLIES
Steven Sim Kok Leong
Honored Contributor
Solution

Re: How to familiarise my database's behaviour?

Hi,

Have you already done a search on the ITRC forums?

> Can anyone tell me how I can know my Oracle8i database's behaviour on HPUX11, such as % of read/write, % of sequential/random read/write?

One way is to use OEM I believe. There are some third-party Oracle performance monitoring tools which can be found by performing a search on google.

> How to know if SGA is enough?

It is also limited by how big your shmmax is and how many shared memory segments you are having on the system.

> If Oracle doesn't use OS buffer cache, can I reduce it by reducing dbc_max_pct?

Yes, to prevent double-buffering. Oracle uses its own buffer cache. You can reduce dbc_max_pct to 5% and dbc_min_pct to 3% if you like.

Hope this helps. Regards.

Steven Sim Kok Leong
Email: steven@beepz.com. Homepage: https://www.beepz.com
T G Manikandan
Honored Contributor

Re: How to familiarise my database's behaviour?

You can download this software from quest software.

It is spotlight on oracle.If you are not familiar with sqlplus queries.you can install it on a windows machine and connect to your database.Try it.
http://www.quest.com/requests/?RequestDefID=182


THanks
Michael Tully
Honored Contributor

Re: How to familiarise my database's behaviour?

Hi,

For a starters I would have your shmmax is at least 1gb. How much RAM do you have ?
The buffer cache should be set to somewhere
around 300mb.

Michael

Anyone for a Mutiny ?
zhaogui
Super Advisor

Re: How to familiarise my database's behaviour?

Yes, my shmmax is 1GB and the physical memory is 4GB. Oracle8i is running on HPUX 11(64bit). Current OS buffer cache is 2GB. But I am not sure if Oracle SGA is enough or not, is there any formula or general guide-line?

#kmtune|grep shm
shmem 1
shmmax 0X0000000040000000
shmmni 512
shmseg 120
T G Manikandan
Honored Contributor

Re: How to familiarise my database's behaviour?

Yes,you can reduce that buffer cache from 50% to 15% as oracle has its own buffer cache.

Reduce the dbc_max_pct parameter there
MANOJ SRIVASTAVA
Honored Contributor

Re: How to familiarise my database's behaviour?

Hi Zhaoqui

There are lots of site which give the bencmark results in terms of tpc etc etc . You can try looking at :
http://www.tpc.org/tpcc/results/tpcc_results.asp?orderby=dbms


However for the specific question that u ahve posed well it depends on lots of factors like
memory of the machine , SGA etc etc , Yes oracle does vary in behaviour on OS cache , all you can do it to cahnge it and see the behaviour

also look at these
Parameter Required
==================================
SHMMAX 1gb
SHMMIN 1
SHMMNI 100
SHMSEG 10

SEMMNI 70
SEMMNS 200

MAX_THREAD_PROC >= 256


Manoj Srivastava

Michael Tully
Honored Contributor

Re: How to familiarise my database's behaviour?

Hi,

Suggest you use this as a guide. Your shmmax is
certainly not large enough. If you have 4gb RAM
you may need a larger shared memory.

# kmtune | grep -i shm

shmem 1 - 1
shmmax 2147483648 Y 2147483648
shmmni 1024 - 1024
shmseg 1024 Y 1024

For your 'dbc_max_pct' set to 8%, this will
reduce the size to 327Mb should be plenty.
As mentioned before anywhere around 300Mb is enough.

Michael
Anyone for a Mutiny ?
T G Manikandan
Honored Contributor

Re: How to familiarise my database's behaviour?

The SGA size is dependent on the initialization parameters like
DB_CACHE_SIZE(DB_BLOCK_BUFFERS),SHARED_POOL_SIZE,LARGE_POOL_SIZE,PROCESSES,etc.
It depends how you set these parameters.So when you are creating the database these parameters are set and the size of the SGA is dynamically altered.
There is no problem that you can change these parameters on the run which dynamically changes the SGA size.
Unless you specify the max_sga_size parameter which limits the SGA,you can alter these above parameters to dynamically change the SGA size.
It depends whether your database is a small,medium or large one.The init.ora file(initialization file for database) you can find the values for small,med and large database recommened size of the above parameters.Find the suitable one and create the database.Later if the value requires to be bumped up you can do it

Thanks
G Manikandan
zhaogui
Super Advisor

Re: How to familiarise my database's behaviour?

Thank you for your comments. I will give your points and I will also discuss these with my DBA colleagues.