關於_ksmg_granule_size與SGA大小的關係

SQL> show parameter large

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
large_pool_size                      big integer 0
SQL> alter system set large_pool_size=2m;

System altered.

SQL> show parameter large_pool

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
large_pool_size                      big integer 4M
SQL> alter system set large_pool_size=5m;

System altered.

SQL> show parameter large

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
large_pool_size                      big integer 8M
SQL> show sga

Total System Global Area  197132288 bytes
Fixed Size                  1218484 bytes
Variable Size              83888204 bytes
Database Buffers          109051904 bytes
Redo Buffers                2973696 bytes
SQL> show parameter sga_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 188M
sga_target                           big integer 188M

在9i SG中寫到,SGA<128M時候,granule大小是4M,但是我們看到在10G的時候,

這個增長是按4M增長的.按eygle查詢引數的語法看了一下

SQL> col value for a20
SQL> l
  1  select x.ksppinm name,y.ksppstvl value,x.ksppdesc descbtion
  2  from x$ksppi x,x$ksppcv y
  3  where x.inst_id=userenv('Instance')
  4   and y.inst_id=userenv('Instance')
  5   and x.indx=y.indx
  6*  and x.ksppinm like '%_ksmg_granule%'
SQL> /

NAME                 VALUE                DESCBTION
-------------------- -------------------- --------------------------------------
_ksmg_granule_size   4194304              granule size in bytes
_ksmg_granule_lockin 1                    granule locking status

這個大小是4M.

查了一下metalink:

主題:	'startup migrate' failed with ORA-64 while upgrading to 10.2.0.2 with DBUA
	文件 ID:	註釋:386855.1	型別:	PROBLEM
	上次修訂日期:	04-MAR-2008	狀態:	PUBLISHED

In this Document
  Symptoms
  Changes
  Cause
  Solution
  References

---

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.2
This problem can occur on any platform.

Symptoms

Customer is trying to upgrade to 10.2.0.2 from 9.2.0.7 using DBUA.

When attempting to start upgrade using DBUA,
the following error occurs.
This error could be seen through 10.2.0.2 SQLPLUS.

SQL> startup upgrade

ORA-00064: object is too large to allocate on this O/S (1,7614720)

Changes

Startup database with 9.2.0.7 parameter file to upgrade database to 10.2.0.2 using DBUA.

Cause

1. High value of oracle parameter PROCESSES

 The oracle parameter PROCESSES bigger than 1500 requests the allocation of big granule size.

2.  Low value of granule size.

The oracle hidden parameter "_ksmg_granule_size" is set based on the oracle sga size.

  -  sga_max_size <= 1024M then _ksmg_granule_size = 4M
  - sga_max_size > 1024M then _ksmg_granule_size = 16M

If you set a parameter that exceeds the value of the granule size that was
calculated based on the size of the sga.

You will manually have to set the parameter _ksmg_granule_size.

  - default _ksmg_granule_size is 4M

Low value of _ksmg_granule_size can prohibit the database startup.


3. The ORA-64 error could be occured even though the big "_ksmg_granule_size" was configured in
init.ora file.

   DBUA in 10.2.x strips out  the oracle hidden parameter while opening oracle database.

Solution

< Solution 1 >

reduce the oracle parameter "PROCESSES" as described in Note 310838.1

# open oracle parameter file with editor and put following line
  PROCESSES <= 1500

< Solution 2 >

Increase Oracle hidden parameter value of  "_ksmg_granule_size"

 1.  Increase directly the value of '_ksmg_granule_size' parameter to 16M (16777216) or 32M (33554432)

    1.1  open oracle parameter file with editor and put following line
       _ksmg_granule_size=16777216  or 33554432

    1.2 do the manual upgradation

   OR

 2. Increase SGA size bigger than 1024M to affect granule size

   2.1 open oracle parameter file with editor and put following line
        sga_max_size = 1025M

   2.2 Continue the upgradation using DBUA or manually.



看來10G已經改了

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/175005/viewspace-374472/，如需轉載，請註明出處，否則將追究法律責任。