OraFAQ Forum: Performance Tuning » Oracle Buffer Cache vs AIX Filesystem Cache

Home » RDBMS Server » Performance Tuning » Oracle Buffer Cache vs AIX Filesystem Cache (Oracle 10gR2 on AIX 6.1)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Oracle Buffer Cache vs AIX Filesystem Cache [message #506283]

Sat, 07 May 2011 19:21

eclipsis
Messages: 14
Registered: May 2011

Junior Member

Hello,

I am currently in the favorable situation in which I have excess amounts of memory available on the database server - a single node setup. The server only serves the single instance and no other processing. Database size is around 2.3tb and memory is 50gb. For the majority of processing, AIX is allocating a significant amount (anywhere from 30-40%) of the memory to the AIX file system cache (persistent pages).

I've been trying to find documentation about this, but have not had any luck yet. My guess is that it would be better to allow Oracle to cache this data - meaning increase the SGA target and max size to allow for a larger buffer cache. However, the nice thing about the AIX cache is if process memory is needed, the file system cache gives up pages. If the memory was allocated to the SGA, its pretty much locked in.

I have read several articles stating that a larger buffer cache is not always better, as a larger cache takes more management. But having both of the caches active seem to be a waste of memory, effectively storing the data twice - once in AIX persistent pages and a second time in Oracle database buffer cache.

More than likely, I'm just going to have to do some testing. But any suggestions or theories to help point testing in the right direction would be appreciated.

Thanks!

Report message to a moderator

Re: Oracle Buffer Cache vs AIX Filesystem Cache [message #506285 is a reply to message #506283]

Sat, 07 May 2011 19:40

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

post FORMATTED results as below

SQL> select * from V_$SGA_TARGET_ADVICE ;

  SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS
---------- --------------- ------------ ------------------- -------------------
       488		.5	   1137 	     1.1735		 129462
       732	       .75	    969 		  1		 117107
       976		 1	    969 		  1		 117107
      1220	      1.25	    969 		  1		 117107
      1464	       1.5	    969 		  1		 117107
      1708	      1.75	    969 		  1		 117107
      1952		 2	    969 		  1		 117107

7 rows selected.

bigger SGA is NOT always better.

Report message to a moderator

Re: Oracle Buffer Cache vs AIX Filesystem Cache [message #506290 is a reply to message #506285]

Sat, 07 May 2011 21:05

eclipsis
Messages: 14
Registered: May 2011

Junior Member

Looks like a larger SGA would benefit - although maybe not that much. So back to the original question, is it better to let AIX handle this extra memory as it can dynamically allocate it to process or persistent pages, or bump up the SGA some.

  SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS
---------- --------------- ------------ ------------------- -------------------
      3000             .25    116339764              1.1171           440452214
      6000              .5    105592057              1.0139           217783729
      9000             .75    104529784              1.0037           203049054
     12000               1    104144449                   1           201018764
     15000            1.25    103936161                .998           196616453
     18000             1.5    103811188               .9968           194083617
     21000            1.75    103665386               .9954           190827113
     24000               2    103571656               .9945           188837027

Report message to a moderator

Re: Oracle Buffer Cache vs AIX Filesystem Cache [message #506291 is a reply to message #506290]

Sat, 07 May 2011 21:14

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>AIX is allocating a significant amount (anywhere from 30-40%) of the memory to the AIX file system cache (persistent pages).
Just to split hairs or nitpick. I disagree with statement above, but it is more semantics than technical.
AIX (or most every *NIX) will utilize for file system cache any RAM that Oracle does not acquire.
From my perspective it is not that AIX is allocating the RAM; it is the fact that Oracle is NOT allocating the RAM.

>is it better to let AIX handle this extra memory as it can dynamically allocate it to process or persistent pages, or bump up the SGA some.
I seriously doubt you'll be able measure any difference regardless of which is done (or not done).
If SGA of existing DB is increased, it will NOT be available should another DB needs to co-exist on this system.
If it were my call, I'd leave SGA at current size.

Report message to a moderator

Re: Oracle Buffer Cache vs AIX Filesystem Cache [message #506310 is a reply to message #506290]

Sun, 08 May 2011 01:22

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

I doubt that an improvement of 0.5% by doubling the SGA size can be noticed by anyone.
And as BlackSwan said, allocating this to the current database may negatively impact the other components in the same server (and maybe in return the current database).

Regards
Michel

Report message to a moderator

Previous Topic:	After trigger causing performance bottleneck
Next Topic:	Table occupying max size

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 09:59:43 CDT 2024