SGA_MAX_SIZE,SGA_TARGET以及PRE_PAGE_SGA参数

Comments

1. admin says

   2010/08/26 at 20:09

   dr: 9205576 10.2.0.4.0 RDBMS 10.2.0.4.0 UNKNOWN PRODID-5 PORTID-197
   Abstract: CONNECTION TAKES MORE TIME WITH PRE_PAGE_SGA=TRUE IN 4 CELL COMPARED TO 3 CELL

   pre_page_sga should just cause the shadow to loop across
   all pages in the SGA and read a value from the start of
   each page so that it has been touched within that process.
   As per the notes this is done within ksmprepage() which is
   where the time is shown.
   To help check the time difference please get for EACH
   configuration (3 and 4 cell):
   a. Details of the init.ora parameter values
   b. The tracefile output from:
   oradebug setmypid
   oradebug ipc
   c. Details of opatch lsinventory -detail
   From the uploaded init.ora and trace files there is no
   difference in granule size, pagesize, subarea quantity,
   subarea sizes etc.. between the two runs.

   Hence in both configurations it looks like ksmprepage()
   is executing on exactly the same values so it should be
   performing the exact same sequence of assembler instructions
   in each case.
   This function just reads the first byte from every single
   page in the SGA. For such a large SGA this represents a
   loop of about 15 million iterations (which is why ksmprepage
   is a hot function in itself on connect).

   check.sh looks like it just runs a number of concurrent
   sessions to get various bits of information from the DB.
   The HP profile data shows ksmprepage() as the hot function
   so it seems like the problem may be related to:

   – A number of concurrent Unix processes running
   – With a common large chunk of shared memory attached to them all
   – Each process looping across the shared memory chunk and reading
   the first byte of each page (where the pagesize is 0x1000).

   In our source we are doing something like this:

   dvoid *first;
   ub8 remsize, cnt;
   ub4 sum = (ub4)0;
   volatile ub1 *p;

   // first gets set to the start address
   // remsize = num bytes in the shared memory chunk
   // pagesize is 0x1000, zero is (ub8)0 , ub8 is 8 byte unsigned int

   for (p = (ub1 *)first, cnt = (remsize/(ub8)(pagesize));
   !(cnt==zero); –cnt)
   {
   /* Make some half-hearted attempt to avoid optimizing the read
   away */
   sum += *p;
   p += pagesize;
   }

   Which looks like it has compiled to this sort of assembler loop:

   :1 : mov.i ar.lc=r16
   :2 : nop.i 0x0;;

   :0 : ld1 r23=[r14],r45;;
   :1 : lfetch [r14],r44
   :2 : adds r24=32,r12

   :0 : nop.m 0x0
   :1 : nop.m 0x0
   :2 : br.cloop.dptk.many ksmprepage+0x690;;

   It may be sensible to work with HP to create a small C program that
   creates a large chunk (60Gb) of shared memory (akin to the SGA),
   then have a number of concurrent processes attach to that memory and
   execute code similar to the above to see if it exhibits similar
   issues between 3 and 4 cell configurations. You would want to try
   to get similar assembler for the C code to be sure to be comparing
   similar instruction sequences.

   回复
2. admin says

   2010/08/26 at 20:11

   Hdr: 575023 7.3.3 RDBMS 7.3.3 A58506-01 PRODID-5 PORTID-319
   Abstract: PRE_PAGE_SGA IS NOT RELEVANT IN AIX – PLEASE DOCUMENT THAT

   Problem:
   ~~~~~~~~
   Customer tried to lock the SGA into main memory, and set in his init.ora the
   parameter
   PRE_PAGE_SGA = TRUE

   However, customer is still able to startup the database with a SGA bigger than
   the machine physical memory. Obvioulsy, PRE_PAGE_SGA is not doing his job.
   After some research, I found that PRE_PAGE_SGA is not used
   —
   For Oracle on AIX we can not gurantee locking the SGA in memory .
   Yes we can bring up the instance even though complete SGA is not in the memory.

   回复
3. admin says

   2010/08/26 at 20:15

   Hdr: 5072402 9.2.0.6 RDBMS 9.2.0.6 VOS PRODID-5 PORTID-59
   Abstract: SLOW CONNECT TIME WITH SQLPLUS TO INSTNACE WITH 22GIG SGA AND PRE_PAGE_SGA
   PROBLEM:
   ——–
   With a 22gig sga and PRE_PAGE_SGA = true, the sqlplus “/ as sysdba”
   can take up between 3 and 10 seconds.

   DIAGNOSTIC ANALYSIS:
   ——————–
   It seems to be a pause after ioctl

   WORKAROUND:
   ———–
   Connect through sqlnet.

   RELATED BUGS:
   ————-

   REPRODUCIBILITY:
   —————-
   WE are working on reproducing in house but it could be difficult to find a
   machine capable of such a large SGA.

   TEST CASE:
   ———-

   STACK TRACE:
   ————
   1141261744.684820 shmget(171386209, NULL, 0) …………. ERR#2 ENOENT
   1141261744.685453 shmget(171386210, NULL, 0) …………. ERR#2 ENOENT
   1141261744.685874 shmget(171386211, NULL, 0) …………. ERR#2 ENOENT
   1141261744.686560 shmat(4009, NULL, 0) ………………. =
   0xc000000040000000
   ##MIKE##1141261744.687048 shmctl(4009, IPC_STAT, 0x800003ffbfff2c90) = 0
   ##MIKE##1141261744.724370 ioctl(8, 0x80186103, 0x800003ffbfff2c78) = 0
   ##MIKE##1141261746.728660 In user-mode ……………………… [running]
   ##MIKE##1141261752.477845 getuid() …………………………. = 100
   (100)
   10
   1141261754.280652 mmap(NULL, 532, PROT_READ,
   MAP_SHARED|MAP_VARIABLE|MAP_FILE|MAP_ADDR32, 10, NULL) = 0x80013000
   1141261754.377998 close(10) ………………………… = 0
   1141261754.647684 socket(AF_UNIX, SOCK_DGRAM, 0) ……… = 10
   1141261754.837198 getpid() …………………………. = 18682 (18655)

   回复
4. admin says

   2010/09/11 at 07:57

   How SGA_MAX_SIZE Parameter Works
   PURPOSE
   ——-

   Give some concepts and explanation on SGA_MAX_SIZE
   in particular when this init parameter is used on Windows OS

   SCOPE & APPLICATION
   ——————-

   Database Administrators and System Administrators

   How SGA_MAX_SIZE Parameter Works
   ——————————–

   – The Main Definition of SGA_MAX_SIZE parameter

   SGA_MAX_SIZE specifies the maximum size of SGA for the lifetime of the instance.
   In Oracle8i Database Administrators (DBAs) do not have any control over the
   SGA size once the instance is started. Oracle9i allows a DBA to modify the SGA
   size dynamically. This provides an SGA that will grow and shrink in response to
   a DBA command.

   This feature implements an infrastructure to allow the Oracle server to change
   the SGA configuration without shutting down the instance. The Oracle Server
   can modify the physical address space use to respond to the operating system’s
   use of physical memory

   – How SGA_MAX_SIZE Parameter Works

   1. The components of sga are : fixed sga, variable sga, buffer cache, redo
   buffers. The sga is allocated in multiples of granules.
   2. The buffer cache and the variable sga are rounded up to the next granule
   size. Shared pool, large pool and java pool are major components of
   variable sga.
   3. The granule size increases from 4m->8m->16m as sga size increases. The
   sga size is set as sga_max_size which is by default calculated by adding
   up all sga components. If sga_max_size is set in init.ora then sga of
   sga_max_size or sum of sga components whichever higher is allocated at
   the instance startup.
   4. When sga_max_size is set to higher value resulting in change in granule
   size, we see increased allocation for buffer cache and variable sga as
   round off boundary has increased.
   5. If sga_max_size is set to a value greater than the sum of sga components
   then the excess amount is seen in variable sga. Because of this we see
   variable sga size quite larger than the sum of its components.

   – SGA_MAX_SIZE and some related concepts on Windows OS

   o sga_max_size is the max size the SGA can get to.
   As a result of this, Oracle must ‘reserve’ this size in the
   process address space. If it does not, then something else may come along,
   use the space and then the SGA cannot be expanded

   o this is therefore reserved within the virtual address space and since the
   physical memory has not yet been allocated, then this will not show up within
   physical memory. It will ONLY show up in virtual memory

   o the result of this, is that there may be further restrictions on certain resources
   e.g. the number of concurrent oracle connections to this particular database

   o this is due to the space being reserved, so the remaining memory
   structures cannot grow as high as they could have done previously

   o this is NOT a bug. Consider the virtual address space to be a memory ‘map’
   which defines the layout of addressable memory
   i.e. it is the template to which the process must conform

   o the fact that this does not resemble physical memory used (yet) is the way
   the memory management system works

   o Oracle has to reserve space in the process address space and therefore
   also in the virtual memory layout, but since it has not yet physically allocated the extra
   memory, then it is correct that physical memory does not show this memory as being used

   o sysadmins should consider the memory mapping as signifying how things can get
   fitted in and what space they have left for other things.
   Looking at physical memory (which is mapped to virtual address space via windows O/S)
   shows how much physical memory is used/free at any one time,
   but does not reflect the memory model.

   o The 2 memory types, physical and virtual, although related, are not to be
   compared directly.

   – Virtual memory is a ‘model’ or template that signifies how processes
   should structure and address the memory for their use and consists of the entire memory
   mapping that a given process can access.
   However, given that not every single page of a process’ memory will be
   required at any one time, the amount of physical memory used by each process is typically a
   very small subset of the virtual memory layout.
   – However, Physical memory is where the actual memory is taken from when
   needed. This physical memory is mapped to the relevant virtual memory ‘pages’ that are
   required at that specific time.

   o since windows uses a virtual memory system, then in reality, even though
   there may be loads of virtual memory ‘reserved’ the reality is that only a small amount
   of physical memory (a subset of the virtual if you like) is actually in use at any one time.
   The store for the virtual memory is typically disk-based.

   – thus with a large enough backing file, since not all the physical
   memory is required simultaneously, we can have a lot more virtual memory ‘committed’ than
   physical memory. So if there is sufficient virtual memory there could be 5GB of virtual
   memory used even though the system only has 4GB of physical memory, as an example. The virtual
   memory has not all been mapped to physical pages since it is not needed all the time.

   o however, for any given process we must still maintain the template of memory so
   that we know what kernel and application structures can be accomodated

   o the main philosophy behind the virtual memory system is that we can allow
   more applications to run than we physically have enough memory for,
   were we to allocate it all in physical memory
   concurrently; essentially we ‘share’ the physical memory between a
   much larger number of consumers by only mapping the amount of physical memory that
   is strictly needed at any one time.

   – Question
   Is there a way that Oracle can check the amount of available
   memory and map, move, or fit address space that it
   needs from virtual memory to available physical memory ?

   – Answer
   o this is essentially asking if we can use spare physical memory to house extra address
   space that doesn’t fit in our existing process space.
   Due to the sga_max_size virtual memory layout reducing the amount of space left
   to map database connections, the question here is could we ‘borrow’ the space from
   elsewhere where there is memory available.

   The answer to this is ‘no’ (although VLM sort of does this, but only for
   the buffer cache and within the O/S bounds)

   – the reason is 2-fold:

   1/ it is the operating system’s job to worry about how virtual/physical
   memory is managed/mapped.
   The application (in this case Oracle) just asks for memory and the O/S either
   gives it to us or says there is insufficient.
   An application does not want to have to worry about how to get memory aside
   from just requesting it from the O/S, else it would have to know in detail the architecture,
   underlying memory model structures and so on
   2/ if applications were to start ‘borrowing’ memory from other places, bypassing
   the O/S, then if something else wanted that memory the O/S may believe it to be free
   and then problems will arise when something else asks for the memory

   – there are minor exceptions to this, for example VLM, although this is not
   quote the same thing

   Summary
   ——-
   Therefore memory layout needs to be managed in order to allow the most
   flexible resource usage with the optimum SGA sizings required for the
   application. To accomplish this and reach an appropriate compromise, the
   following points should be considered when investigating the type of
   setup/architecture required for a given application:

   – don’t set sga_max_size
   this will allow a larger piece of address space to be used for database connections
   – use VLM this will allow for a larger buffer cache using certain areas of memory
   that are outside of normal process address space, so the SGA can be increased
   without sacrificing too many database connections, still do not set sga_max_size
   – go to 64-bit architecture
   this gives a much larger overall address space and solves many of these
   restrictions (essentially it raises the restrictions to a higher level,
   so they’re still there but the maximum figures are bigger)

   回复