Oracle--Sharing Direct Links/Urls for Oracle Support/MOS documents

When we want to share an Oracle Support document with your collegue, we usually shared the document id by an email.. 

So, here is another way of sharing Oracle Support content.. As you below, we can share a direct link pointing to Document itself.. When you click these kind of Oracle Support Link, browser will reach the Oracle Support and after user/pass verification, Oracle Support will open the document directly.


You can use the following template to share the document links...

https://support.oracle.com/epmos/faces/DocumentDisplay?id=DOCUMENT ID <-- place the doc id here

For example;

https://support.oracle.com/epmos/faces/DocumentDisplay?id=1592445.1

On the other hand; sharing the document links directly by copying and pasting the url from the browser, is not a good method. 

Using this method, the document that will be displayed, may be different than you want to share :)

For example, following is a link that was gathered from the browser. It can be seen that it has 1080037.1, so one would think that this link will open the document with id 1080037.1, but in my computer it points to document with id 1592445.1 :)

 https://support.oracle.com/epmos/faces/ui/km/SearchDocDisplay.jspx?returnToSrId=&srnum=&_afrLoop=285590540171310&type=DOCUMENT&id=1080037.1&displayIndex=5&_afrWindowMode=0&_adf.ctrl-state=yhhl3fika_65

But, when I click on the following link, which is proper, I reach the document with id 1080037.1, as expected.

https://support.oracle.com/epmos/faces/DocumentDisplay?id=1080037.1

Toad for Oracle Presentation --in Turkish

Here is a presentation of TOAD and its features.

The presentation is powered with Live Demo of the Tool, in order to present the features of Database Admin and Sql Optimizer modules in detail. Presentation is in Turkish.

Click here for the slides of the presentation

EBS--11.2.0.4 Certified with EBS on 11 Platforms

Reference: Steve Chan's Oracle Blog

The latest news directly from E-Business Suite Development 6 Dec 2013

As a follow up to our prior announcements, Oracle Database 11g Release 2 (11.2.0.4) is now certified with Oracle E-Business Suite Release 11i and Release 12 on the following additional platforms:

EBS Release 12.1 (12.1.1 or higher):

• Oracle Solaris on SPARC (64-bit) (10, 11)
• Oracle Solaris on x86-64 (64-bit) (10)¹
• HP-UX Itanium (11.31)
• IBM AIX on Power Systems (64-bit) (5.3, 6.1, 7.1)

EBS Release 12.0 (12.0.4 or higher):

• Oracle Solaris on SPARC (64-bit) (10)
• Oracle Solaris on x86-64 (64-bit) (10)¹
• HP-UX Itanium (11.31)
• IBM AIX on Power Systems (64-bit) (5.3, 6.1, 7.1²)

EBS Release 11i (11.5.10.2 + ATG PF.H RUP 6 and higher):

• Oracle Solaris on SPARC (64-bit) (10, 11¹)
• Oracle Solaris on x86-64 (64-bit) (10)¹
• IBM AIX on Power Systems (64-bit) (5.3, 6.1)

¹ : This is a 'database tier only' or 'split tier configuration' certification where the application tier must be on a fully certified E-Business Suite platform.
² : Certified for 12.0.6 

This announcement for Oracle E-Business Suite R12 and 11i includes:

• Oracle Database 11gR2 version 11.2.0.4
• Oracle Database 11gR2 version 11.2.0.4 Real Application Clusters (RAC)
• Oracle Database Vault 11gR2 version 11.2.0.4
• Transparent Data Encryption (Column Encryption) using Oracle Database 11gR2 version 11.2.0.4
• TDE Tablespace Encryption using Oracle Database 11gR2 version 11.2.0.4
• Advanced Security Option (ASO)/Advanced Networking Option (ANO) with Oracle Database 11gR2 version 11.2.0.4
• Export/Import Process for Oracle E-Business Suite Release 11i and Release 12 Database Instances
• Transportable Database and Transportable Tablespaces Data Migration Processes for Oracle E-Business Suite Release 11i and Release 12

Exadata -- ZBR and Interleaving

Exadata introduces an opportunity of having Interleaved Disks, actually Interleaved Grid Disks..  Actually, interleaving is defined in the Cell Disk Layer, but Grid Disks become interleaved..

As known, Griddisks are the fourth layer of the disk abstraction in Exadata.. They are created on top of Cell Disks(third layer of abstraction) and used for building the ASM Groups.

Cell Disks can be considered as physical disks or luns , that we see on fdisk -l output on Linux/Unix systems( actually it s not, but lets suppose). Grid Disks can be thought as partitions on physical disk or luns. 

So by default, when we create our first grid disk on a cell disk, Exadata starts the creation from the fartest sectors.. Thus the first grid disk created becomes faster than the remaining disks..

This is an expected behaviour, as by default Exadata uses non-interleaving Grid Disks..

The interleaving Grid disks concepts is based on dividing the grid disks in multiple parts, thus protecting the perfomance across them..

In other words; by using the interleaving option in Exadata the cell disks are intelligently divided in to grid disks which are equal in performance.

In this interleaving disk concept, Exadata actually takes benefit of Zone Bit Recording (ZBR) to have higher transfer IO bandwith on outer part of the hard disks.. In addittion to that , Exadata manages the allocation of disk parts using its interleaving option in order to have equal disk portion in terms of performance..

To understand better, lets see the concept of disk structures..

Following shape describes the structure of a normal disk(none ZBR).
As you see below; altough distance from the center increases, the number of sectors in a given angle does not change...

A: Disk structure showing a track ,
B: a sector
C: a sector of track
D: a cluster of sectors

On the other hand;

Following shape, on the other hand; describes the structure of a disk, that uses ZBR(Zone Bit Recording).

Red: The closest to the center
Green : In the middle, have more sectors than Red.
Grey: Farthest to the center, have more sectors than Red.

As you above, as the distance from the center increases, the number of sectors in a given angle also increases.  As standard hard disks have a constant angular velocity, which means regardless of where the heads are, the same speed is used to turn the media.. So by knowing this, we can say that; the path that a disk would travers in  a 360 degree turn will be more in outer part of the disk, so more data will be read.. This will increase linear velocity.

Lets go back to the exadata.. 

When grid disks are created without interleaving option, Exadata allocates the outer part first... Like most of the operating systems -> Since both hard disks and floppy disks typically number their tracks beginning at the outer edge and continuing inward, and since operating systems typically fill the lowest-numbered tracks first, this is where the operating system typically stores its own files during its initial installation onto an empty drive.  So, considering the ZBR , the first disk created will be faster, and because of the ZBR's nature, the second disk will be slower..

Exadata brings a solution for that.. Actually it s an option, optional thing called Interleaving.

Interleaving is defined in Cell disk layer.

Note that: Whether you choose to use no interleaving or interleavig, Oracle will allocate the first set of extents on the outer part of the physical disk..

Oracle has two options for Interleaving . Normal redundancy and High redundancy.. These redundancies are not like ASM redundancy.. They have different meaning..
In Normal Redundancy, Oracle divides your grid disk into two ranges of disk tracks. In High Redundancy: Oracle divides your grid disk into three ranges of disk tracks..

So to create a cell disk with interleaving; we use the following command;
create celldisk interleaving_erman lun=0_11 INTERLEAVING='normal_redundancy'

After creating the cell disk , we can place the grid disks on this Cell disk with the following commands..

create griddisk DATA1 celldisk=interleaving_erman, size=.........
create griddisk RECO1 celldisk=interleaving_erman.

So the diagrammatic explanation of the output is the following;

As represented above, the cell disk named interleaving_erman is divided into 2 equal portion.  Grid disk DATA1 is placed  on the ranges of disk track, painted red..  Grid Disk RECO is placed on the ranges of disk tracks, painted green. In this scenario; DATA1 and RECO are considered almost equal in overall performance. Note that, the ranges in above shape can be changed accoring to the Grid Disk size specified.
What happens here is, the disk is divided in two parts..(%50 - %50 ).. And in each part, the disk is divided again..
So if we have 600G cell disk with interleaving normal redundancy and if we create two Grid Disks in sequence.. (Grid Disk A with 250G, Grid Disk B with 350G)  it happens as follows;
Oracle will divide the disk in to 300 gb pieces.
In the first part : Oracle will place 125 G of Grid Disk A(most outer) -> RED , and 175 G of Grid Disk B -> GREEN
In the second part: Oracle will place 125 GB of Grid Disk A -> RED  , and 175 G of Grid Disk B -> GREEN.

So the size of the rings will differ.. I didnt do the math, but this should be a good approach to catch the same IO rates for the interleaving disks..

In brief,
Oracle emphasize that, different ASM disk groups can share the Cell Disks without a performance bias, if you prefer to use Interleaving .. In other words, by using interleaving, Oracle can divide the Cell Disk in to the parts which are equal in performance(really equal?, it seems not exactly but almost equal to me :))
The idea behind that is ZBR. In ZBR, the tracks in the same zone are recorded with the same read/write rate..  So, Oracle's disk dividing operation provides using this zones intelligently..

EBS R12-- Cloning Oracle Applications Database -- compatible parameter ApplyRmanDatabase.class

While cloning Oracle EBS/Applications Database using ad utilities, you can encounter a problem with compatible settings.. I mean, if the compatible parameter of your Source Database is something like 11.2.0.2 or 11.2.0.3 , you can encounter this problem..In detail, Oracle Apps Dba's usually use ad utilities to clone EBS databases. While the adclone utility (adclone.pl) is cloning the database, it creates a temporary init.ora.. It uses this initSID.ora.tmp file to open the cloned database in the first place. The problem is that adclone.pl uses a class named ApplyRmanDatabase.class, and this class sets the compatible parameter.. 

Actually this class generates the initSID.ora.tmp.. For a 11gR2 database, this class writes compatible=11.2.0 in to temporary init.ora.. 

It is hardcoded.. It designed so, because EBS of the compatability issues, I guess.

Anyways, I decoded the class, and here is the line that writes compatible=11.2.0 into the initSID.ora.tmp file..

if(dbVersion != null && dbVersion.startsWith("db112")) m_InitTmpFile.println("compatible=11.2.0.")


So , to fix this and to be able to open your database, this line in ApplyRmanDatabase.class should be changed to something like ;

--suppose you have 11.2.0.2 compatible database.if(dbVersion != null && dbVersion.startsWith("db112")) m_InitTmpFile.println("compatible=11.2.0.2.0");


You can do this actually .. You can change and recompile the code with the same Java version that your EBS uses, but it is not supported..( it sound like right? :)


So, if you face this problem, please contact Oracle Support, and request the patched version of ApplyRmanDatabase.class , according to your needs.


I have faced this problem in an EBS 12.1.3 Production instance...
The version of the java class was "120.10.12010000.10" .

Database -- Methods for preventing Bind Peeking and ACS (Adaptive Cursor Sharing)

To reason that makes me write this post is a question that asked by one of our customers. They have both Microsoft and Oracle Databases, but they work with Microsoft Sql Server more intensively.
Sql Server has such an capability that it s not caching the ad-hoc queries (mostly executed by users) in its Library Cache. This seems like an opportunity to control the library cache usage, by preventing ad-hoc sql queries overloading the shared memory. Its another benefit is that, it can prevent bind variable peeking, as mentioned by the customer.  So they have wanted to configure Oracle to treat in the same way...

Bind variable peeking is not a new thing. It is basically about, peeking a bind variable and then constructing the Execution plan for a specific query according to that particular bind variable value. This brings the opportunity for preventing the hard parses and memory consumption.. On the other hand; this also brings out an issue if the underlying table has not a uniform data distribution.

Lets suppose a table X has a column Col1 and it has values like A,B,C ....   We have also an index on Col1.. (table and index analyzed)

The row counts are like below;
C1      Count
------------
A           1
B           1000000000
C          245

Now suppose we just started our database , and one of our users executes an sql statement like ;
select col1, col2 from X where Col1=:b1;    --> and the user supplies the bind variable 's value as 'A'

Normally, Oracle makes an hard parse in this situation. It does symantic checks, syntax checks and etc.. In that phase, Oracle builds an Execution Plan, too.. Execution plan is a very important aspect for CBO. Oracle uses Explain Plan to retrieve the data..

By looking to the row count(histograms and etc.),  Oracle will decide to use the index on Col1,that is, it generates the execution plan stating the usage of that index and retrieves the data very quicky..

Note that Oracle saves this information in its shared memory for upcoming sql statements.. One of the thing that makes Oracle to cache the cursor is, to eliminate the unnecessary I/O's and Cpu consumption for regenerating the Execution plan. (in case-- the same sql will be executed again)
This is a good thing but lets suppose the following scenario;

Just after the sql statement (select col1, col2 from X where Col1=:b1; ) is executed, another session executes the same sql statement with a different bind value  (select col1, col2 from X where Col1=:b1;  <--B).. This time the sql is executed with the conditon Col1='B') ..
Just because the structure of the sql seems  the same from the Oracle perspective, Oracle will use the same execution plan for that sql, too. This seems normal as it is really the same sql.. But, this time it is executed with Col1='B'... Bind peeking arise from this weakness.. Because of the bind peeking, Oracle will use that cached execution plan and use that index to retrieve 1000000000 B record...  On the other hand, it should use a Full table Scan, as it s so expensive to read such a big number of rows (big percentage of rows, as well)  from an index.. So this is wrong..

There are some ways to prevent bind variable peeking though;

we can avoid using Cursor_sharing parameter set as FORCE.. Cursor sharing parameter, when set to FORCE, will convert the conditions using literals to conditions using bind variables..
As, this will increase the chance for bind variable peeking, it s good  to set this parameter to SIMILAR or EXACT..(according to the situation)
On the other hand, this is not enough and it s not the solution for the problem, as we will always have queries with binds..

Another approach is to use different sql statements according to the data;
if (COLX > 20 )
then
  open l_cursor for
  select * from t alias1 where ...
else
   open l_cursor for
   select * from t alias2 where ...
end if;

Thus, different sql statement will be executed for differents values . There will be no bind peeking.. On the other hand, it requires an intervention to the code. That s why, may be considered as an expensive way.

Using stored outlines, not gathering statistics and disabling the bind peeking with the _optim_peek_user_binds underscore parameter can be considered as solutions for bind peeking.
Reference cursor can be another way, as they will trigger a parse on each and every open.

Analyzing the objects can also be used to change the cached Execution plans.. Automatic dependency tracking makes Oracle flush the Execution plans from the shared pool.

All of the methods explained above bring us indirect ways to handle the bind variable peeking problem..

So lets look at what Oracle developed in 11g to handle with it;

In 11g, there is new feature called Adaptive Cursor Sharing(ACS). It is enabled by default and can not be disabled. It enables a single statement that contains bind variables to use multiple execution plans. ACS supplies two terms with its methodology. Bind sensitive and Bind aware..
A bind-sensitive cursor is a cursor whose optimal plan may depend on the value of a bind variable. The database monitors the behavior of a bind-sensitive cursor that uses different bind values to determine whether a different plan is beneficial.

A bind-aware cursor is a bind-sensitive cursor eligible to use different plans for different bind values. After a cursor has been made bind-aware, the optimizer chooses plans for future executions based on the bind value and its selectivity estimate.

So what happens with ACS in 11g is as follows;

When a query is first executed, Oracle generates its plan and put it into cache.. If Oracle decides that the execution plan may need to change according to the bind variable values, it marks the cursor as bind sensitive. When the query executed again, Oracle executes the related statement with the same execution plan, but as it knows that query is a bind sensitive query, it compares the runtime statistics of the query with the statistics gathered in its previous run. If Oracle finds a remarkable difference between these two statistics, it will mark the query as Bind Aware.. By marking the query Bind aware, Oracle guarantees a hard parse, thus generating a new execution plan on the next run of that query..

This is how Oracle deals with bind variable peeking in the new releases..

In addition, the "BIND_AWARE" hint can also be used to make a cursor skip the bind sensitive phase and directly come to the Bind aware phase.. But this approach is not considered as a good recommendation..

In conclusion, ACS brings us a good and transperent way to deal with bind peeking in Oracle databases. By the help of ACS, we dont have to think for ways to deal with the performance problems caused by bind peeking.. In addition to that, in my opinion; as long as Oracle can manage it with its LRU implementation and features like ACS; caching ad-hoc queries are not a problem, but a choice..

EBS - Oracle Application Concepts Presentation

This presentation was prepared by my collegue, Hülya Yılmaz,  to create a general understanding on Oracle EBS technologies, as well as to give information about the tools that a developer may need to use within the EBS system.

It was presented to EBS developers and functional users ... It covers general information about topics such as general architecture, file locations, cloning, gathering diagnostics, tracing , monitoring and etc..

Click here to reach the slides of the presentation..

RDBMS -- Oracle Database Presentation

Acutally I have prepared and presented this presentation to developer who have been using Oracle Database for development and data analysis. I tried to create a dba perspective on them.
In this presentation, you can find the following topics;

Click here to reach the slides of the presentation..

Presentation Name :
Oracle Database for Developers

File Name: ORACLE_DATABASE_snip_by_Earslan.ppsx

Contents:

Primary Components
Oracle Instance
Oracle Database
Memory Architecture(pga, sga, Shared pool, buffer cache, ...)
Processes (user,server,background)
Diagnostic Files
Trace Levels (Tracefiles, tkprof)
Redo & Undo
Commit
Async Commit
Rollback
Row Migration & Chaining
HWM
Indexes( stats, constraint , clustering factor, index and cbo, notes)
Locks (blocking locks, identifying, v$lock)
Cbo
Auto_trace (sqlplus)
Execution Plan / Explain Plan

Hints
Dictionary

References:
Tom Kyte //asktom.oracle.com
Richard Foote //Oracle Index Internals
Donald Burleson //dba-oracle.com
Pavan Kumar > pavandba.com
Natalka Roshak //What is blocking my lock
Michael Messina // Indiana University
Oracle // docs.oracle.com

Database-- Oracle, allocating/deallocating/resizing shared memory, Sga_target, Investigating Oracle shm allocation.

We know that, Oracle introduced Sga_target parameter to manage Buffer cache (DB_CACHE_SIZE), Shared pool (SHARED_POOL_SIZE),Large pool (LARGE_POOL_SIZE) and Java pool (JAVA_POOL_SIZE). It s considered as an important concept in Automatic Memory Management (AMM)

As it is well known by the most of the community, I will not go in details about this parameter, except one thing = Reducing the memory usage using sga_target parameter.  

Actually, the reason that made me write this post is, the shared memory.. I was testing some memory boundries and behaviours of the operating system in case of a shared memory deallocation.. I was working on it because the information was needed to analyze an EBS production system performance..   So, I was working on a Linux 64 bit, writing a program to allocate some shared memory memory ... 

I used SYSV compatible shmget and allocated some shared memory.. I could use shmctl to deallocate this shared memory without any problems..

I could use ipcs on Linux to display and saw memory allocation and deallocation activities.

On the other hand, I realized that I could not resize the allocated shared memory on-the-fly. It seems using shmget , you cant do this in Linux. There is no such option to resize a shared memory segment using shmget in Linux.. In Aix,there is a shm_size argument for shmget . It s for resizing. But it seems it is not available for Linux.

So, I used the following C code to allocate and deallocate 1.5G shared memory.

#include <sys/types.h>

#include <sys/ipc.h>

#include <sys/shm.h>

#include <stdio.h>

#include <errno.h>

#include <string.h>

#define SHMSZ 1589934592

int main()

{

int shmid;

int shamerman;

key_t key = 5690;

shmid = shmget(key,SHMSZ,0666 | IPC_CREAT);

fprintf(stdout,"%d",shmid);

shmctl(shmid,SHM_SIZE,589934592);

sleep(3);

if ((shamerman=shmctl(shmid,IPC_RMID,0)) !=0)

{

fprintf(stderr,"ERROR with shmctl (%d: %s)\n", (int)(errno), strerror(errno));

return 1;

}

return 0;

}

I could see the shared memory segment that I allocated in ipcs output..
This server was an Oracle Database server, and I saw the shared memory segments allocated by the running Oracle Database , too. Oracle Database allocated approx 7*2 gb(14gb in total) shared memory segments.. I checked its SGA to be sure.. Both, the sga_target and sga_max_size parameters were set to 14gb. On the other hand, the kernel parameter shmmax was set to 2gb.. That was why Oracle allocated the 14 gb memory segment in 7 pieces..  (Note that : Shmmax describes maximum size of a shared memory segment)

Lets come back to the our actual topic; I was allocating and deallocating the shared memory segments.  I could do it with shmget. But one thing that I cant do with it , is that I could not resize a shared segment on-the-fly. Ofcourse, I could deallocate and reallocate a segment with a new size, but this is not satisfying..

To able to resize a shared memory segment, I decided to use shm_open(create or gain access to a shared memory segment) and ftruncate(truncate a file to a specified length), posix memory operation functions..
So shm_open uses a file based approach and technology for shared memory operations. It basically uses tmpfs,/dev/shm to function. Because it s using a file based architecture, there is an opportunity to use the ftruncate for resizing the file, which is actually tied to a shared memory segment.. By doing this, a shared memory segment can be resized on-the-fly.

I used following C program , which uses shm_open and ftruncate functions to allocate and resize the shared memory segment ;
Note that : this program should be compiled using gcc -lrt ,  by using -lrt argument posix support is added to the gnu c compiler.

#include <sys/types.h>

#include <sys/ipc.h>

#include <sys/shm.h>

#include <stdio.h>

#include <errno.h>

#include <string.h>

#include <sys/mman.h>

#include <sys/stat.h>        /* For mode constants */

#include <fcntl.h>

int main()

{

int *result = NULL;

int integerSize = sizeof(300000);

int descriptor = shm_open("/shmerman",O_CREAT | O_RDWR, S_IRUSR | S_IWUSR);

fprintf(stderr,"ERROR with shmctl (%d: %s)\n", (int)(errno), strerror(errno));

fprintf(stderr,"%d",descriptor);

ftruncate(descriptor, integerSize);

fprintf(stderr,"ERROR with shmctl (%d: %s)\n", (int)(errno), strerror(errno));

result = mmap(NULL, integerSize, PROT_WRITE | PROT_READ, MAP_SHARED, descriptor, 0 );

sleep(10);

fprintf(stderr,"SIMDI DEALLOCATE EDIORUM");

sleep(10);

ftruncate(descriptor, 600000);

return 0;

}

This time , I checked the file named shmerman, which was located under /dev/shm and saw that its size was 300000b after created and it jumped to 600000b after 10 seconds..(sleep 10)
Note that: You cant see the shared memory segment allocated by shm_open in ipcs output. You can check the segments from the /dev/shm filesystem.

In 11g , we have memory_target and I believe Oracle uses a similar method, that I just mentioned above,  to manage the shared memory . As you know; to be able to use above method, you need to have a /dev/shm..  So /dev/shm is required for using memory_target in Oracle.
Beyond that,  I think and read maybe, that Oracle uses /dev/shm to pass segments from SGA(shared memory) to PGA (process memory).. I think it just gives back the shared memory back to OS ,and OS gives it to the processes in case they needed. Giving back the unused memory should be enough for managing the PGA,as is not a limit and it s is not a fixed memory area resides in memory..

So far so good.. On the other hand, 10g Oracle does not use memory_target , naturally it does not use a /dev/shm based memory access..
ipcs output shows that it uses shmget to get the needed shared segments... Also , I have sent a mail to Tanel Poder regarding this topic, and he said by using strace in Oracle startup; shmget call can be seen..

Following was taken in startup process of an Oracle 10gR2 database.

open("/proc/sys/kernel/shmmax", O_RDONLY) = 2   --> It reads the shmmax to obtain the max segment size.
[pid 12584] shmget(2505607756, 6444548096, IPC_CREAT|IPC_EXCL|SHM_HUGETLB|0600) = -1 ENOMEM (Cannot allocate memory)  --> It tries the Hugetlbs and can not get them as it s not configured.
[pid 12584] shmget(2505607756, 6444548096, IPC_CREAT|IPC_EXCL|0660) = 557059, then it uses small pages to allocate the 6gb sga , which shmid= 557059

shmat(557059, 0x7fbfffdf01, 0) = ?  --> it attaches to the allocated shared memory

It gets the shared memory using shmget call;

Here is the ipcs command output; shmid in the ouput is the same as it s returned from shmget..(557059)

------ Shared Memory Segments --------

key        shmid      owner      perms      bytes      nattch     status      

0xb92f91b4 524290     proddb    660        526385152  27                      

0x95588a4c 557059     proddb    660        6444548096 12                      

0x00000000 360452     proddb    644        151552     2          dest         

0x00000000 393221     proddb    644        122880     2          dest         

0x00000000 425990     proddb    644        790528     2          dest 

So it s clear Oracle 10g uses shmget to allocate the shared memory segments..
The question is that , how it can resize them?
As we know we can decrease the size of sga on-the-fly using sga_target parameter.

I tested and analyzed it to satisfy my curiosity;

1) sga_target = sga_max_size

First I tested this functionality in a Oracle Database, which has 6 gb sga capacity and configured to use all the 6gb of it. (sga_max_size = 6gb, sga_target=6gb)
There parameter lock_sga=FALSE ..  Note that  LOCK_SGA locks the entire SGA into physical memory. It is usually advisable to lock the SGA into real (physical) memory, it prevents paging.. I checked this parameter because. It can mislead us..

After the startup of the instance; I see that shared memory segment size is 6gb. So, it s clear that Oracle allocated 6 gb of shared memory (note that sga_target is 6gb too)

Here is the ipcs command output , just after startup;

------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status          
0x95588a4c 557059     proddb    660        6444548096 16                    
0x00000000 360452     proddb    644        151552     2          dest      
0x00000000 393221     proddb    644        122880     2          dest      
0x00000000 425990     proddb    644        790528     2          dest

Look at the following Oracle process.. It has 6325 of virtual memory.. 6 gb + some mbytes.. So Oracle process is prepared to be is able to access 6 gb memory at the present moment.

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                                                                                        
12606 proddb    16   0 6325m 1.3g 1.2g S  0.0  7.1   0:15.36 oracle

Output of free command show approx 18007-(17064+227)
free -m
             total       used       free     shared    buffers     cached
Mem:         18049      18007         41          0        227      17064
-/+ buffers/cache:        716      17333
Swap:         8001          0       8000

Lets look at the Shared memory components of Oracle.It has a sga configured to 6 gb.
It has 5301600256 bytes 's of buffer cache.  1056964608 bytes of Shared pool and etc..

SQL> show sga

Total System Global Area 6442450944 bytes
Fixed Size                  2093544 bytes
Variable Size            1124077080 bytes
Database Buffers         5301600256 bytes
Redo Buffers               14680064 bytes

SQL> select * from v$sgainfo;

NAME                                  BYTES RES

-------------------------------- ---------- ---

Fixed SGA Size                      2093544 No

Redo Buffers                       14680064 No

Buffer Cache Size                5301600256 Yes

Shared Pool Size                 1056964608 Yes

Large Pool Size                    16777216 Yes

Java Pool Size                     16777216 Yes

Streams Pool Size                  33554432 Yes

Granule Size                       16777216 No

Maximum SGA Size                 6442450944 No

Startup overhead in Shared Pool   167772160 No

Free SGA Memory Available                 0

So everything is just like expected, here.. we use all the sga , and Free SGA Memory Available =0

2) sga_target < sga_max_size

Next I tested this functionality in a Oracle Database, which has 6 gb sga capacity but configured to use 4gb of  it. (sga_max_size = 6gb, sga_target=4gb)

So here is the  ipcs ouput just after restart.. Oracle still allocates 6gb Sga..  But this does not mean, that this memory is  initialized and taken from physical memory.

------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status                    
0x95588a4c 589827     proddb    660        6444548096 14                    
0x00000000 360452     proddb    644        151552     2          dest      
0x00000000 393221     proddb    644        122880     2          dest      
0x00000000 425990     proddb    644        790528     2          dest      

Oracle processes still maps more than 6gb memory.. VIRT column supplies this information.. Virtual memory is a memory map that can be point to physical memory, or a swap disk space, or a memory "to be allocated".

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                                                                                        
14231 proddb    16   0 6284m  38m  34m S  0.0  0.2   0:00.06 oracleDEV (LOCAL=NO)                                                                                                            
$ free -m
             total       used       free     shared    buffers     cached
Mem:         18049      18016         32          0        229      17048
-/+ buffers/cache:        737      17311
Swap:         8001          0       8000

       
SQL> show sga

Total System Global Area 6442450944 bytes
Fixed Size                  2093584 bytes
Variable Size            2986348016 bytes
Database Buffers         3439329280 bytes
Redo Buffers               14680064 bytes

SQL> select * from v$sgainfo;

NAME                                  BYTES RES

-------------------------------- ---------- ---

Fixed SGA Size                      2093584 No

Redo Buffers                       14680064 No

Buffer Cache Size                3439329280 Yes

Shared Pool Size                  771751936 Yes

Large Pool Size                    16777216 Yes

Java Pool Size                     16777216 Yes

Streams Pool Size                  33554432 Yes

Granule Size                       16777216 No

Maximum SGA Size                 6442450944 No

Startup overhead in Shared Pool   134217728 No

Free SGA Memory Available        2147483648

As you see above, Oracle allocated 6gb Sga. Buffer cache size is 3.2Gb. , the variable size is 2.78Gb..
It looks like it is same whether we use sga_target = 4gb or sga_target = 6b.. But it is not the same.
Look at Free SGA Memory Available .. It is 2147483648, which is 2gb.

3) Lets reduce the sga_target value from 4gb to 2b of this running instance ..
(sga_max_size=6gb, sga_target = 2gb (reduced from 4gb, without restarting the db)

SQL> alter system set sga_target=2g;            
System altered.

ipcs output shows the same; 6gb shared memory segment is stil allocated.

------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status          
0x95588a4c 589827     proddb    660        6444548096 15                    
0x00000000 360452     proddb    644        151552     2          dest      
0x00000000 393221     proddb    644        122880     2          dest      
0x00000000 425990     proddb    644        790528     2          dest

Top command shows the same, VIRTUAL Memory of a given Oracle process is approx. 6gb.

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                                                                                        
13611 proddb    16   0 6284m  20m  16m S  0.0  0.1   0:00.03 oracle      

Output of free command shows similar amount of free memory..

             total       used       free     shared    buffers     cached
Mem:         18049      18009         40          0        238      17032
-/+ buffers/cache:        738      17310
Swap:         8001          0       8000


SQL> show sga

Total System Global Area 6442450944 bytes
Fixed Size                  2093584 bytes
Variable Size            5133831664 bytes
Database Buffers         1291845632 bytes
Redo Buffers               14680064 bytes

SQL> select * from v$sgainfo;

NAME                                  BYTES RES
-------------------------------- ---------- ---
Fixed SGA Size                      2093584 No
Redo Buffers                       14680064 No
Buffer Cache Size                1291845632 Yes
Shared Pool Size                  771751936 Yes
Large Pool Size                    16777216 Yes
Java Pool Size                     16777216 Yes
Streams Pool Size                  33554432 Yes
Granule Size                       16777216 No
Maximum SGA Size                 6442450944 No
Startup overhead in Shared Pool   134217728 No
Free SGA Memory Available        4294967296

As we see;  ipcs ,top -p, free -m and oracle's sga outputs are similar(they point to 6gb shared memory), but Free SGA Memory Available showed in v$sqainfo display 4gb free, as expected..

Not seeing the unused portion of the shared memory in ipcs and top is not an issue. These are tools, so they can be coded that way. No need to investigate..

But, following are the questions;
How can Oracle make use of the advantages brought by sga_target? Actually, how does OS threat these allocated but not used shared memory blocks..

I will try to answer these questions in a few days.

Linux & EBS 11i -- uek kernel, PAE support(32->36 bits), HighMem-LowMem,address large memory

Oracle Enterprise Linux 5 32 bit comes with a built-in PAE support.
You can see it by looking to the kernel configuration file;
Configuration file is under /boot directory, and it s something like config-2.6.32-300.10.1.el5uek.
In this confiuration file, you can see the PAE support declaration -> CONFIG_X86_PAE=y

PAE stands for Physical Address Extension.. It is basically a feature for 32 bit systems.. It provides addressing memory larger than 4GB. Technically a 32 bit system can address 2^32 byte  (4GB)  memory.. PAE increases address size from 32 bits to 36 bits.. By increasing the address size, PAE actually increases the adressable memory from 4GB to 64 GB.

Lets look how does an Operating System like Linux locate the data? (without PAE)

The memory manager locates the Page Directory for the current process. The memory manager is informed of the location of the Page Directory for the process by a special control register.
The 10-bit Page Directory Index in the virtual address is used to locate a Page Directory Entry (PDE) that defines the location of the Page Table needed to translate the virtual memory address.
The 10-bit Page Table Index in the virtual address is used to locate the correct Page Table Entry (PTE).
The PTE is used to locate the correct 4 KB page of memory.
After accessing this page, the 12-bit Byte Index in the virtual address is used to locate the physical address of the desired data.

With PAE -> A page directory pointer table  is also added to the process above. Actually page directory pointer table provides accessing more than 4 gb memory.

Following figure explains reaching a page in the memory using a PAE kernel memory architecture..
(Reference: Paging Extentions for the Pentium Pro Processors, by Robert R. Collins)

As you see, there is a high level hierarchy. This hierarchy between page tables are used to decrease the size of the page tables.

Note that: Adding page tables to the hierarchy can reduce performance..

The virtual address is the same, it s 32 bit. The first table which is the Page Directory pointer uses 2 bits to points to the 4-page directory. The entries in the page directories are 64 bit , but only 36 bits of them are used to decribe the location.  So we have 36 bit addresses.. By using 36 bit, 64 GB memory becomes addressable.
In my opinion, the virtual address is still 32 bit, so a single process can not access more than 4gb even with PAE. Maybe using swapping techniques, a single process can address more than 4gb pyhsical memory , but at single point in time, it will have an address space of 4gb at most.


Okay, technically PAE supports 64 GB, but it is not the case in practice, especially for critical systems..

A Reference: Mel Gorman, University of Miami , Understanding The Linux Virtual Memory Manager

PAE allows a processor to address up to 64GiB in theory but, in practice, pro-
cesses in Linux still cannot access that much RAM as the virtual address space is
still only 4GiB. This has led to some disappointment from users who have tried to
malloc() all their RAM with one process.
Secondly, PAE does not allow the kernel itself to have this much RAM available.
The struct page used to describe each page frame still requires 44 bytes and this
uses kernel virtual address space in ZONE_NORMAL. That means that to describe 1GiB
of memory, approximately 11MiB of kernel memory is required. Thus, with 16GiB,
176MiB of memory is consumed, putting significant pressure on ZONE_NORMAL. This
does not sound too bad until other structures are taken into account which use
ZONE_NORMAL. Even very small structures such as Page Table Entries (PTEs) require
about 16MiB in the worst case. This makes 16GiB about the practical limit for
available physical memory Linux on an x86. If more memory needs to be accessed,
the advice given is simple and straightforward, buy a 64 bit machine.

To test this; we booted our 64 bit HP server (which has 64gb memory installed) with a PAE enabled Oracle Linux 32 bit.. It could see the 64 GB Ram.. So we used hugepages to address the large memory, and took necessary actions to make Oracle use a big sga. Everything seemed perfect in the beginning..  Then we started up our Oracle Database with 4gb Sga. Database started, so still no problem. Then we started to create some tablespaces and lastly, we ended up with the following;

Nov 12 10:25:22 productlinux kernel: lowmem_reserve[]: 0 0 0 0
Nov 12 10:25:22 productlinux kernel: DMA: 1*4kB 1*8kB 0*16kB 0*32kB
1*64kB 0*128kB 1*256kB 1*512kB 1*1024kB 0*2048kB 0*4096kB = 1868kB
Nov 12 10:25:22 productlinux kernel: DMA32: empty
Nov 12 10:25:22 productlinux kernel: Normal: 1*4kB 1*8kB 8*16kB 1*32kB
0*64kB 0*128kB 0*256kB 1*512kB 1*1024kB 1*2048kB 0*4096kB = 3756kB
Nov 12 10:25:22 productlinux kernel: HighMem: 0*4kB 0*8kB 1*16kB
0*32kB 1*64kB 1*128kB 1*256kB 1*512kB 1*1024kB 0*2048kB 6114*4096kB =
25044944kB
Nov 12 10:25:22 productlinux kernel: 5360827 pagecache pages
Nov 12 10:25:22 productlinux kernel: Swap cache: add 0, delete 0, find
0/0, race 0+0
Nov 12 10:25:22 productlinux kernel: Free swap = 34799608kB
Nov 12 10:25:22 productlinux kernel: Total swap = 34799608kB
Nov 12 10:25:22 productlinux kernel: Free swap: 34799608kB
Nov 12 10:25:22 productlinux kernel: 16777216 pages of RAM
Nov 12 10:25:22 productlinux kernel: 16547840 pages of HIGHMEM
Nov 12 10:25:22 productlinux kernel: 403562 reserved pages
Nov 12 10:25:22 productlinux kernel: 7467319 pages shared
Nov 12 10:25:22 productlinux kernel: 0 pages swap cached
Nov 12 10:25:22 productlinux kernel: 158886 pages dirty
Nov 12 10:25:22 productlinux kernel: 0 pages writeback
Nov 12 10:25:22 productlinux kernel: 146965 pages mapped
Nov 12 10:25:22 productlinux kernel: 90980 pages slab
Nov 12 10:25:22 productlinux kernel: 5958 pages pagetables
Nov 12 10:25:22 productlinux kernel: Out of memory: Killed process

So, from the kernel messages above, we can say that, OOM killer killed one of our background processes.. In this case the process got killed, was LGWR..

OOM can be disabled, but it s a built-in security mechanism.. It s actually there for helping us. OOM killed the process because it saw that the amount of free memory is decreasing to zero..

But we have 64 gb right? There should be a lot of memory left even when the database was running..

We see the HighMem there, 25044944kB, it s approx. 23 GB. It seems okay...

Higmem is the memory that an application or lets say user process can access. In this case, there were plenty of rooms in High Mem.

Lets look at the line starting with Normal.. It should be the line for the Low Memory.. In this case, Low Memory had only 3756KB (approx 3MB) available.. This points to a problem.. Low Memory is reserved memory for kernel, and it seems the source of the problem is the Low Memory... Linux 32bit systems implements by default a 3:1 split memory, as 32 bit Linux can address 4GB of memory; the first 3GB of the memory is called High Memory which is the user address space and the remaining 1GB is called Low Memory which is the kernel address space. So when the kernel needs to create its structures and load data into them, it uses the Low Memory. For a 32 bit Linux System with 64 Gb memory installed, it seems the kernel stores a lot of information just to manage this big memory. It stores this information in the low memory.. So even if you use PAE, it is not stable when you adress such a big memory like 64 gb.. The reason behind that is that PAE uses 3:1 split for high-low memory.. So in brief, PAE kernel can see 64 gb memory, but when the server load increases, it can fill out the low memory very easily and break our processes down.


Lets talk about HugeMem support . Hugemem is another option that allows Linux 32 bit kernel to address more than 4 GB memory. Hugemem memory boundries are not like PAE(3:1), so a kernel that support hugemem has a 4:4 split memory . That is, 4 Gb for high mem, 4 Gb for low mem... It makes a Linux kernel to be able to address more than 16gb memory on a 32 bit system. It is not like the PAE, as it has a 4:4 split. As you predict, It will not suffer from the 1gb lowmem limit like the other 32bit kernels. Ofcourse, if you need to address such a big memory on 32 bit system, you have to use your Oracle Database with some specific parameters (like indirect data buffer).

Bad news is that, Oracle Enterprise Linux 5 and upwards does not support HugeMem. It seems it is not supported because of the insufficiency for the new patches and widespread use of 64 bit systems in the Enterprise Level..

In addition, there is no equivalent kernel for Oracle Enterprise Linux 5 and above.. It seems, the same applies for Redhat, too..

In brief, if you want to use 64 Gb memory on a 32 bit Linux system, you need to use Oracle Enterprise Linux 4 with hugemem. If using an 64 Gb memory is a very important requirement and if you want to use Oracle Enterprise Linux 5 or 6, you need to go for 64 bit..


So lets suppose we have an Application (Oracle EBS) running on a Oracle Enterprise Linux 5 32 bit OS installed on a 32bit server with 16 GB memory installed;

As I mentioned above we can use PAE for this configuration..

Oracle Enterprise Linux 5 supports PAE option.. PAE kernel can address 16 gb memory stably.

To test this, lets use the following C program;
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#define PAGE_SZ (1<<12) /* it makes 4096*/
int main() {
int i;
int gb = 2;
for (i = 0; i < ((unsigned long)gb<<30)/PAGE_SZ ; ++i) { /*converting GB to Byte */
void *m = malloc(PAGE_SZ);
if (!m)
break; /*Break if you can not allocate*/
memset(m, 0, 1); /*Write*/
}
printf("allocated %lu MB\n", ((unsigned long)i*PAGE_SZ)>>20); /*Converting Byte to MB*/
getchar();
return 0;
}

We upload this c code into a file and compile it with gcc on our Oracle Enterprise Linux 5 system.

The name of the produced executable is "a.out"

So lets execute this a.out..

It will allocate 2GB of memory .. It will allocate them in pages(4096 by 4096). It will stop if it can not allocate any more memory and it will print out the total memory allocated.

The server has 16gb memory as below;

op - 16:48:13 up 3:45, 15 users, load average: 0.12, 0.12, 0.04

Tasks: 454 total, 1 running, 453 sleeping, 0 stopped, 0 zombie

Cpu(s): 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st

Mem: 16502020k total, 15973388k used, 528632k free, 8072k buffers
Swap: 33559776k total, 1195800k used, 32363976k free, 35676k cached

Lets start 8 a.out processes.. (as I know that a process can allocate 2GB memory at most in Linux 32 bit..)

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                 

 2196 root      20   0 2056m 2.0g  344 S  0.0 12.7   0:01.56 a.out                                                   

 2037 root      20   0 2056m 2.0g  344 S  0.0 12.7   0:01.41 a.out                                                   

 2001 root      20   0 2055m 2.0g  344 S  0.0 12.7   0:01.39 a.out                                                   

 2101 root      20   0 2056m 2.0g  344 S  0.0 12.7   0:01.41 a.out                                                   

 2004 root      20   0 2055m 2.0g  344 S  0.0 12.7   0:01.39 a.out                                                   

 2068 root      20   0 2056m 2.0g  344 S  0.0 12.7   0:01.36 a.out                                                   

 2218 root      20   0 2055m 2.0g  344 S  0.0 12.7   0:03.10 a.out                                                   

 1967 root      20   0 2056m 975m  344 S  0.0  6.1   0:01.40 a.out    

Not that : The RES column in the above ps output shows , the real allocated physical memory for each process.. As you see 7x a.out processes allocates 2 gb memory per process and 1x a.out process allocates 975m of memory, because only 975m memory has left..

This proves that a PAE enabled 32 bit Linux can adress 16Gb memory without problems..

Lastly, I will write something from the Oracle EBS / Apps Dba perspective..

By using this information in this post, a conclusion has arrived -> If you need to EBS 11i in to a Linux environment, I suggest you to choose a split configuration. 

First, Install the database into a 64 bit Linux and use a big sga and then install the Application Tier into a 32 bit Linux with PAE support. Note that, EBS 11i application tier should be installed into a 32bit Linux because EBS 11i application code is not supported on 64 bit Linux Operating Systems..

By using this split configuration method, you can use a big sized sga for database, as well as 16 gb memory for application services..

I don't want to say that EBS application code can not be compiled and run in a 64 bit Linux.. Maybe it can.. Maybe by modifying the makefiles, environments and so on, you can compile or relink executables.. On the other hand; It is not supported by Oracle.