Friday 25 February 2005

See what's in the DB_CACHE_BUFFER

See what's in you db_cache buffer at that time, log on as SYSDBA and run the following query.
You will see what occupies the db_cache buffer at that time.


OLUMN object_name FORMAT a40
COLUMN number_of_blocks FORMAT 999,999,999,999

SELECT o.object_name, COUNT(1) number_of_blocks
FROM DBA_OBJECTS o, V$BH bh
WHERE o.object_id = bh.objd
AND o.owner != 'SYS'
GROUP BY o.object_name
ORDER BY count(1);

Wednesday 16 February 2005

Solaris ls command to list large files

Use this command on Solaris with bash, to get your files sorted from the smallest to the largest and quickly list large files.

$ ls -lhs | sort -nSk5

Note: k5 is the column number from left to right (in this example column 5). if your filesizes are displayed on the 3rd column you put ..k3. All depends on which column the command ls -l displays the filesizes.

Tuesday 1 February 2005

Unix/Linux kernel parameters for Oracle installation

This is Unix/Linux Kernel parameters configuration based on how many oracle instances with how many processes you wish to run on the Unix box.

SHMMAX = The maximum size(in bytes) of a single shared memory segment.
SHMMIN = The minimum size(in bytes) of a single shared memory segment.

The above settings let the max be bigger then we wil ever use and the min much smaller. They are sufficient for pretty much all systems.

SHMMNI = The number of shared memory identifiers.
SHMSEG = The maximum number of shared memory segments that can be attached by a process.

The above settings should also be sufficient.




SEMMNS = The number of semaphores in the system.
SEMMNI = The number of semaphore set identifiers in the system; determines the number of semaphore sets that can be created at any one time. 
SEMMSL = The maximum number of sempahores that can be in one semaphore set. It should be same size as maximum number of Oracle processes

(The PROCESSES parameter in the init.ora file).




SEMMSL

Set to 10 plus the largest initsid.ora PROCESSES parameter of an Oracle database on the system. The PROCESSES parameter can be found in each

initsid.ora file, loc ORACLE_HOME/dbs directory. The default value of PROCESSES

for the 8.1.5 prebuilt database is 50.




SEMMNS

Set to the sum of the PROCESSES parameter for each Oracle database the

largest one, plus 2 times the largest PROCESSES value, plus 10 number of Oracle

databases. For example, consider a system that has three Oracle instances with

the PROCESSES parameter in their initsid.ora files set to the following values:




ORACLE_SID=A, PROCESSES=100

ORACLE_SID=B, PROCESSES=100

ORACLE_SID=C, PROCESSES=200







The value of SEMMNS is calculated as follows:




SEMMNS = ((A=100) + (B=100)) + ((C=200) * 2) +

((# of instances=3) * 10) = 630







Definition: Semaphore




Semaphores are serialization devices (only one person at a time will get a

semaphore). In general, they are "heavy weight" (we have light weight latches

as well) and implemented by the OS itself. We use them to protect some shared

data structures from being updated by more then one process at a time.







From : http://asktom.oracle.com , Tom Kyte