OracleHere is an example of the kernel parameter settings I typically use for Oracle Database on Solaris. This is provided only as an example. You should not implement these settings without understanding what they do!

For a more in-depth explanation of these parameters see my other article Semaphore Settings and Shared Memory for Oracle Database.

You should always consult the Oracle documentation for your platform and release of Oracle for recommended kernel settings.

These settings reside in the /etc/system file on Solaris and must be setup by the root user. Any line beginning with an asterisk (*) is treated as a comment and not processed by the operating system. After these settings are implemented in teh /etc/system file the system will need to be rebooted for them to take affect.

************************************************************
* Example Semaphore and Shared Memory Settings *
* for Oracle on Solaris *
* Written by Jon Emmons *
* www.lifeaftercoffee.com *
************************************************************
* Shared memory settings
set shmsys:shminfo_shmmax=4294967295
* shmmax sets the largest memory segment in bytes
* which can be allocated
set shmsys:shminfo_shmmin=1
* shmmin sets the smalles memory segment in bytes
* which can beallocated
set shmsys:shminfo_shmmni=500
* shmmni defines the maximum number of shared memory
* segments in the entire system
set shmsys:shminfo_shmseg=50
* shmseg defines the maximum number of shared memory
* segments which can be used by one process* Semaphore settings
set semsys:seminfo_semmni=300
* semmni sets the number of semaphore sets available
set semsys:seminfo_semmsl=500
* semmsl sets the number of semaphores per set
set semsys:seminfo_semmns=30000
* semmns sets the total number of semaphores available
* the actual semaphores available will be the lesser of
* (semmni * semmns) or semmns
set semsys:seminfo_semopm=250
* semopm determines the maximum number of operations
* per semop call

database, database administration, database tuning, dba, solaris, system administration, oracle

In UNIX semaphore and shared memory settings control several aspects of how applications can use memory. Many people just drop these settings into place without truly understanding what they do but this can bite you in the long run.

In this article I hope to shed some light on what these settings really do. Though settings vary by platform and database version their purpose remains constant. As always, mileage may vary. Consult the documentation for your system/db version before changing any settings.

My examples come from a Solaris system running several Oracle 9i databases. Semaphore and shared memory settings need to be set by root and will likely require a restart of the system to take affect.

Typically in UNIX each process obtains memory for its purposes. That memory is protected from access by other processes. Shared memory allows an application to allocate a chunk of memory which can be viewed by other processes. Oracle uses shared memory to create the System Global Area (SGA) which all Oracle processes must be able to access.

Semaphores act as flags for shared memory. Semaphores are either set on or off. When an Oracle process accesses the SGA (in shared memory) it will check for a semaphore for that portion of memory. If it finds a semaphore set on for that portion of memory that process will sleep and check again later. If there is no semaphore set on for that portion of memory it will set one on and proceed with its operation. When it is done it will switch that semaphore back to off.

If that all makes sense let’s look at some of these settings and some example values. These appear in the format you would see in a Solaris /etc/system file. An asterisk (*) is treated as a comment in this file.

Shared Memory Parameters

Ideally Oracle would like to find one contiguous chunk of shared memory to put the entire SGA into. When this is not possible it will attempt to find several contiguous segments of shared memory. Failing that it will resort to several non-contiguous segments of shared memory. If none of these are available your Oracle database will fail to start.

set shmsys:shminfo_shmmax=4294967295
*shmmax sets the largest memory segment which can be
*allocated

As the comment implies, this is the largest single segment of shared memory which can be allocated in the system. This setting is set in bytes. Setting this to a large value (half of the physical memory of the system or more) makes it possible that Oracle will be able to allocate the SGA in one contiguous memory segment. This example is from a machine with 8GB of physical memory so the largest shared segment is set to 4GB. Note that this is a maximum, so Oracle will still only occupy as much memory as the SGA requires.

set shmsys:shminfo_shmmin=1
*shmmin sets the smallest memory segment which can be
*allocated

This sets the minimum size a shared memory segment is allowed to be. Of course it is unlikely (read impossible) to have a 1 byte SGA, but having this set to 1 guarantees maximum flexibility.

set shmsys:shminfo_shmmni=500
*shmmni defines the maximum number of shared memory
*segments in the entire system

The number of shared memory segments available is controlled by SHMMNI. Ideally we may only use one per Oracle instance, but realistically it is nice to have plenty around.

set shmsys:shminfo_shmseg=50
*shmseg defines the maximum number of shared memory
*segments which can be used by one process

SHMSEG limits the number of simultaneous shared memory segments which can be accessed by an individual process. Again, this would ideally be low, but it doesn’t seem to hurt to have plenty of overhead on this setting.

Semaphore Parameters

Oracle requires one semaphore for each process of each database on a system. By this logic the number of semaphores available need only be equal to the PROCESSES parameter in the init.ora file (or spfile). For systems with multiple databases the number would be the sum of the PROCESSES parameter for all databases.

That’s nice in theory, but there are a couple additional considerations. For some reason durring database startup Oracle will request enough semaphores for twice the processes specified. I guess the theory is to make sure there are plenty of semaphores to go around.

It is also nice to consider expansion when planning semaphores. If you need to increase the PRCOESSES parameter for one of your databases, or want to add a database to an existing server, you would have to restart to update the semaphore parameters. For this reason it seems fortuitous to have relatively (but not astronomically) high values for the semaphore settings.

set semsys:seminfo_semmni=300
*semmni sets the number of semaphore sets available

This controls the number of semaphore sets available. In an ideal world we would only need one set per database, but more realistically it is nice to have a bunch extra around.

set semsys:seminfo_semmsl=500
*semmsl sets the number of semaphores per set

This parameter will determine how many semaphores exist in each semaphore set. Since semaphores are always allocated in sets it is most convenient if this is greater than or equal to the PROCESSES parameter for your largest database.

set semsys:seminfo_semmns=30000
*semmns sets the total number of semaphores available

SEMMNS limits the number of semaphores which can be generated in the system. I recommend setting this fairly high to avoid limiting the number of semaphores available. The actual number of semaphores available will actually be the lower of either SEMMNS or SEMMSL*SEMMNI.

set semsys:seminfo_semvmx=65534
*semvmx is the semaphore maximum value

I have to admit to not knowing exactly what SEMVMX controls (other than what is in the comment above). After some extensive googleing I cannot turn up a satisfactory answer for this. If you know, please leave a comment to this post.

NOTE: See comment from Christopher Gait regarding SEMVMX. He mentions that this parameter is obsolete in Solaris 10. I don’t believe this is even in the Oracle install guide for Database 9i and up, but has been left in this article for legacy purposes.

set semsys:seminfo_semopm=250
*semopm determines the maximum number of operations
*per semop call

This controls the number of operations which can occur during a single semaphore call. A value of 250 should allow a sufficient number of operations while keeping the number of calls low.

NOTE: These are examples given for educational purposes. As mentioned earlier in this article, always consult your documentation before changing system parameters.

oracle, database, dba, database administration, system administration, solaris, database tuning

While Oracle has been moving in the direction of locally managed extents in tablespaces several of us are still working with dictionary managed tablespaces. Here’s a good trick for adjusting table storage clauses in dictionary managed tablespaces.

Note: “compressing” extents is not a way to make data take up less space, but rather a way to take a fragmented (for lack of a better word) table and re-ogranize it into a more contiguous area of storage.

In the older dictionary managed method a storage clause in the table creation command controls how much space was initially allocated to that table (the initial extent) and how much would additionally be allocated if it filled the initial space (the next extent). If no storage clause was specified an often absurd default would be used.

This leaves us with two problems: First, we may have a very low value for the next extent. This can leave us creating extents of, for instance, 32KB even though each row may be 50KB.

Second, we may have tables which already contain a ridiculous number of extents and possibly chained rows. (Chained rows are a topic for another day.)

For my example I will use a table owned by system called session_audit.

To view the storage parameters on a table we can query the dba_tables view:

SQL > SELECT initial_extent, next_extent
FROM dba_tables
WHERE table_name='SESSION_AUDIT' AND owner='SYSTEM';

INITIAL_EXTENT NEXT_EXTENT
-------------- -----------
131072 65536

These numbers represent size in bytes, so dividing by 1024 we see that this table is configured to have an initial extent of 128KB and a next extent of 64KB.

We can also count the current number of extents whch make up this table with the following SQL command:

SQL> SELECT count(*) FROM dba_extents
WHERE segment_name='SESSION_AUDIT' AND
OWNER='SYSTEM';

COUNT(*)
--------
77

It may seem the right method is to change the initial and next values with an ALTER TABLE command like this:

SQL> ALTER TABLE system.session_audit
STORAGE (INITIAL 10M NEXT 5M);
ALTER TABLE SYSTEM.SESSION_AUDIT
*
ERROR at line 1:
ORA-02203: INITIAL storage options not allowed

If we dropped the change to the initial extent we could use this command to change the next extent. If we want to update the initial extent we will need to get sneaky. We could create a new table with the desired storage settings, move the data, then rename the new table to the old name, but this would cause the table to be temporarily unavailable and have the unfortunate side-effect of invalidating any of this tables dependencies. Alternately we could export and import the table but this would lead to the same problem.

Instead, using the ‘ALTER TABLE MOVE’ command, we can tell Oracle to move this table into a different tablespace while at the same time sneaking a new storage clause in.

SQL> ALTER TABLE system.session_audit
MOVE TABLESPACE system
STORAGE (INITIAL 10M NEXT 5M);

Table altered.

We now have our table in the new tablespace with the desired storage clause. This also creates an initial extent of the specified size instead of the smaller extents we had previously. If we want to move the table back to it’s original tablespace we can just issue the same command with the original tablespace.

NOTE: I was also able to tell Oracle to “move” the table to the same tablespace it was originally in. This had the same affect of changing the storage clause and compressing the extents without needing a foster tablespace.

We can now re-check the number of extents and storage clause for the table:

SQL> SELECT count(*) FROM dba_extents
WHERE segment_name='SESSION_AUDIT' AND
OWNER='SYSTEM';

COUNT(*)
--------
1

SQL > SELECT initial_extent, next_extent
FROM dba_tables
WHERE table_name='SESSION_AUDIT' AND owner='SYSTEM';

INITIAL_EXTENT NEXT_EXTENT
-------------- -----------
10485760 5242880

We see that the number of extents currently being used by the table has been reduced to 1 and our next extent will be 5MB in size.

oracle, database, database administrator, oracle database, dba, sql, database tuning

Wondering if you’re doing everything possible to secure your Oracle database? You probably aren’t, but the Center for Internet Security has compiled a practical checklist which will get you damned close!

The Center for Internet Security (CIS) is a non-profit organization which is committed to providing information on best-practices for security. The CIS does not report every vulnerability in a piece of software, but rather provides a set of best-practices for setup and configuration of systems and applications to minimize security risks.

The CIS Oracle Benchmarks provide a wealth of information on installing and configuring Oracle. Far from a step-by-step on how to install Oracle, it does fill in many of the gaps that are easily overlooked.

The CIS approach is very practical. As an example, item 2.01 from the Oracle benchmark reads as follows:

2.01
Installation
Try to ensure that no other users are connected while installing Oracle 10g

The Oracle 10g installer application could potentially
create files in a temporary directory with public
privileges. It would be possible for any local user to
delete, overwrite or corrupt these files during the
installation process. Try to ensure that no other users
are connected while installing Oracle 10g. Also set the
$TMP and $TMPDIR environment variables to a
protected directory with access given only to the Oracle
software owner and the ORA_INSTALL group.

In this compressed format the Oracle 10g Benchmarks still span 55 pages; however, these 55 pages represent the equivalent of several years of experience.

While the complete list of benchmarks offered by the CIS is relatively small it hits the high points on enterprise level software. I expect in the long run the list will grow to include more of the open-source solutions we are now finding commonplace.

So grab your favorite sys-admin and a big cup of coffee and run through this checklist. You’ll be surprised what you find. If you happen to be the DBA _and_ the sys-admin you’d better make it an extra large coffee.

oracle, oracle security, oracle 10g, oracle 9i, database administration, dba, database, database security

Here are some quick and dirty examples for adding and subtracting days and months and finding the difference between dates in Oracle.

These examples select the result from the ‘dual’ table. Dual is a dummy table that exists in all Oracle databases. I am also using sysdate heavily. Sysdate simply returns the current date and time.

Adjusting Days, Weeks, Hours and Minutes

To add and subtract days from a date we simply use + or -. Here are some examples:

SQL> SELECT sysdate + 7 FROM dual;

SYSDATE+7
---------
06-DEC-05

SQL> SELECT sysdate - 30 FROM dual;

SYSDATE-3
---------
30-OCT-05

SQL> SELECT to_char(sysdate - 14, 'MM/DD/YYYY HH:MI AM') FROM dual;

TO_CHAR(SYSDATE-14,
-------------------
11/15/2005 09:41 AM

In the first example we see that the query returns 7 days from today. The second one shows 30 days before today. In the third query I have added the to_char function so we see hour and minute. This shows us that while the date has changed, the time has not.

The first and third examples above also show how adding days would be used to add weeks. If you wanted to simplify this you could add the logic into the query like this:

SQL> SELECT sysdate + (7 * 2) FROM dual;

SYSDATE+(
---------
13-DEC-05

Likewise to work with hours you would use fractional days. The simplest way to show this is by dividing the hours by 24 right in the query. The parentheses may not be necessary, but I feel they make the query a little clearer.

Here I show the current time, then the current time plus two hours:

SQL> SELECT to_char(sysdate, 'HH:MI AM') FROM dual;

TO_CHAR(
--------
09:48 AM

SQL> SELECT to_char(sysdate + (2/24), 'HH:MI AM') FROM dual;

TO_CHAR(
--------
11:48 AM

An alternative to this method is to use the numtodsinterval function. The example above can instead be written like this:

SQL> SELECT
to_char(sysdate + numtodsinterval(2, 'HOUR'), 'HH:MI AM')
FROM dual;

TO_CHAR(
--------
11:57 AM

Here the numtodsinterval function is doing the work of dividing 2/24 for hours. Valid options for the numtodsinterval are ‘DAY’, ‘HOUR’, ‘MINUTE’, or ‘SECOND’. Here is an example using ‘MINUTE’. When working with minutes the numtodsinterval function is much more readable.

SQL> SELECT
to_char(sysdate + numtodsinterval(45, 'MINUTE'), 'HH:MI AM')
FROM dual;

TO_CHAR(
--------
10:47 AM

Adjusting Months and Years

To work with months and years (either of which may have a varying number of days) Oracle has provided the function numtoyminterval. This works much like the numtodsinterval function mentioned above by taking a number and a string. Valid options for the string are ‘YEAR’ or ‘MONTH’.

SQL> SELECT
to_char(sysdate + numtoyminterval(5, 'MONTH'), 'MM/DD/YYYY')
FROM dual;

TO_CHAR(SY
----------
04/29/2006

SQL> SELECT
to_char(sysdate + numtoyminterval(2, 'YEAR'), 'MM/DD/YYYY')
FROM dual;

TO_CHAR(SY
----------
11/29/2007

Comparing dates

Let’s say we want to compare some dates and find out how many days or weeks between them. To simply see the result in days we can use the minus operator like this:

SQL> SELECT
TO_DATE('12/25/2005', 'MM/DD/YYYY') - sysdate
FROM dual;

TO_DATE('12/25/2005','MM/DD/YYYY')-SYSDATE
------------------------------------------
26.5124421

The result is expressed in days including fractional hours. Of course if we wanted weeks we could just divide by 7. Similarly if we’re looking for hours we could multiply by 24, but if we want months we need to use the months_between function.

SQL> SELECT
months_between(sysdate, to_date('01/01/2006', 'MM/DD/YYYY'))
FROM dual;

MONTHS_BETWEEN(SYSDATE,TO_DATE('01/01/2006','MM/DD/YYYY'))
----------------------------------------------------------
-1.0829409

Finding the Latest or Earliest Date in a Set

The Oracle functions greatest and least can be used on dates to return the latest or earliest date.

SQL> SELECT
greatest(sysdate, to_date('09/11/2005','MM/DD/YYYY'),
to_date('12/25/2005','MM/DD/YYYY'))
FROM dual;

GREATEST(
---------
25-DEC-05

SQL> SELECT
least(sysdate, to_date('09/11/2005','MM/DD/YYYY'),
to_date('12/25/2005','MM/DD/YYYY'))
FROM dual;

LEAST(SYS
---------
11-SEP-05

What’s the Last Day in a Given Month

The last_day function can be used to return the last day in a given month.

SQL> select last_day(sysdate) from dual;

LAST_DAY(
---------
30-NOV-05

Next Day of the Week After a Date

The next_day function gives the date of the next occurrence of a day of the week (‘Monday’, ‘Tuesday’, etc.) after a given date. Here we see the date of the next Sunday after today:

SQL> select next_day(sysdate,'Sunday') from dual;

NEXT_DAY(
---------
04-DEC-05

Still haven’t found what you’re looking for?

You may also want to look at these other articles:

Converting Time Zones in Oracle
Oracle, SQL, Dates and Timestamps
UNIX timestamp to Oracle Date Conversion

oracle, sql, dba, database administration, database development

« Previous PageNext Page »