The table has around 5 million records, and used for OLTP.
Locking SGA « Intermediate SQLIn the previous post we discussed how ORACLE allocates shared memory for SGA in AIX and one of the conclusions was that AIX does not give all the requested memory to ORACLE instance right away but merely promises it. While this technique allows to use memory more efficiently and you (at least temporarily), can request more memory for processes and shared segments than what AIX physically has, it also has a rather unpleasant consequence – when we get to the limit of physical memory, AIX will have no choice but to start paging memory. Paging is not necessarily a bad thing – moving older and not- so- often used data out of memory is something that will be done rather routinely – this is how AIX keeps a healthy system. However, when SGA memory starts to page out (and, more importantly, page back in) things can go bad quickly as, well, ORACLE does not really expect SGA to be a disk based area .
The question becomes: how can we accomplish that on AIX? Pinning ORACLE SGA into AIX Memory. It turns out that there are several ways to pin ORACLE SGA into AIX memory, some of them ORACLE- driven, some AIX- driven and a combination of both .
Obviously, when this happens, the entire SGA memory is used and thus allocated. Let’s see for ourselves. Before we even begin, let’s remind ourselves how memory is allocated when this parameter is NOT set. As you can see, in the beginning, most of the memory is under allocated (AIX promised it but did not yet deliver) as not all of the memory has been used. After setting pre.
This obviously has a direct effect on the time it takes to start up the database – in my environment it took ~ 4. Gb SGA. Presumably, however, this additional time has not been wasted – all further requests to SGA memory are supposed to hit real physical memory and AIX will not need to do any additional allocations. Still, there are two problems with this approach: Notice that the memory, although fully allocated, is NOT really pinned. That means that if AIX starts experiencing memory shortages, you can bet that it will start paging SGA memory out with all the unpleasant consequences. A somewhat unexpected consequence is that it now takes more time for any ORACLE process to start as the “touching” it is not done just during instance startup – it is happening for any new ORACLE process (i. In my environment, average database connection time went from ~ 0.
System Privileges to Create Chains: dbms The source code to this post is here (requires VS2010): DataGridDisplayNameTest.zip. One of the great things about the new DataGrid in.NET 4.0 is that it allows you. Tom I observed that my predecessor developers,have coded a lot of stuff lik e create or replace procedure.
Improving performance with pipelined table functions. In January 2009, I wrote a section on performance tuning with pipelined functions for Steven Feuerstein's Oracle. Nancy, FYI: you don’t need Oracle Instant Client if you are installing Oracle Server software because full Oracle Client and sqlplus are part of the Oracle Server.
Given these downsides, it is really hard to find a good justification for using pre. I’m guessing this parameter is probably a relict of the past, or, perhaps, a way to pre- load memory for systems that do not support real memory pinning (remember that ORACLE can run on many operating systems). But in modern AIX, I just do not see how it can be effectively used. So, let’s move on to the next parameter – lock. The database startup still takes more time than without this parameter (on my system, ~ 3.
Gb SGA), but normal database connections do not suffer any longer as, beyond startup, ORACLE processes do not need to do any (major) extra stuff. One note here: Many ORACLE documentation sources recommend to also set v. With versions up to 9i, ORACLE used a different call for memory pinning: shmget(IPC. As I mentioned, in 1. ORACLE uses: shmctl(shm. In my tests, ORACLE 1. You can of course, still set it if you need it for ORACLE 9i or for other applications.
In any case, looks like setting lock? Traditionally, these chunks have always had a mandatory size of 4.
K, but recently, with the advent of machines that can handle large amounts of RAM, this started to change. AIX on a modern hardware now supports 4 different page sizes: 4.
K, 6. 4K, 1. 6M and 1. G and I outlined the difference between them in the table below: Page size. Svmon symbol. Configuration. Pageable. How to configure.
How to use. 4Ks. Traditional, Automatic. YESN/ABy default. Km. Automatic. YESN/ABy default. MLManual. NOvmo - p - o lgpg. Default in this context means that you do not need to do anything special to either enable these pages or use them – AIX will decide when to use 6. K pages instead of 4.
K and this will be done completely transparently to programs (including ORACLE, of course). In fact, in modern hardware, you would most likely see 6. K pages used by ORACLE SGA as (a large) SGA size will definitely warrant them. There is also an interesting development with AIX 6.
While AIX 5. 3 can allocate 6. K pages from the start, AIX 6. K memory regions and see if they can be “collapsed” from 4. K to 6. 4K pages. Beyond medium (6. K) pages, AIX also allows to use even larger pages – 1. M or 1. 6G. However, there are two important differences here: Large pages are NOT available by default.
They require extra steps to enable them and (separately) to use them. Large pages are NOT pageable. Once allocated, they always stay in memory and cannot be paged in or out (which is probably a good thing, but you do need to pay special attention to how you size them). In addition to that, not all AIX hardware will support larger pages.
To see if your particular hardware supports them run: AIX> pagesize - a. The one problem with large pages is that they are somewhat cumbersome to use. First of all, you have to pre calculate the “large page memory” size and explicitly set it with the VMM (this will take memory away from regular VMM operations and designate it to “large page” region). AIX> vmo - p- olgpg.
Think about this: for a 3. Gb SGA (which is not excessively big these days . Well, see for yourself: This would normally conclude AIX memory story, if not for one thing – ORACLE 1. SGA, in addition to PGA much more dynamic.