1. alter system reset sga_target scope=spfile sid='instance1';
scope =spfile memory both， spfile时不会立即改当前SGA的大小，但是PDB没有起来时可能会hang住
2. show spparameters SGA
查看Spfile下定义的私有参数
Sga target not modified after setting sga_target to new value in RAC (Doc ID 1426733.1)
Applies to:
Oracle Database - Standard Edition - Version 10.1.0.2 to 11.2.0.2.0 [Release 10.1 to 11.2]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Oracle Database Cloud Service - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.
Symptoms
Running a RAC configuration.
After modifying sga_target in spfile and restarting the instance, the sga_target on first node didn't changed as expected
The issue can be reproduced at will with the following steps from the second node:
SQL>alter system set sga_target='XXXXM' scope=spfile;
SQL>shutdown immediate
SQL>startup
SQL>show SGA
Cause
On a RAC configuration you will have instance-specific parameters and database generic parameters. The instance-specific parameters in the spfile take priority over the generic parameters.
In this case, the spfile contains both an instance-specific parameter for instance1 and generic setting for sga_target as shown:
instance1.sga_target=1610612736
*.sga_target=5368709120
Because of this setting any change to generic value of sga_target is still overridden by the instance1.sga_target.
Solution
There are two different methods to resolve.
Solution 1. Delete the instance-specific setting
1. Login sqlplus as sysdba
2. Create a pfile from spfile
SQL>create pfile='/tmp/pfile.ora' from spfile;
3. Delete or comment out the instance-specific setting in pfile:
From:
instance1.sga_target=1610612736
*.sga_target=5368709120
To:
#instance1.sga_target=1610612736
*.sga_target=5368709120
4. Recreate the spfile from the pfile :
SQL>shutdown immediate
SQL>startup nomount pfile='/tmp/pfile.ora';
SQL>create spfile=<spfile_location> from pfile='/tmp/pfile.ora';
SQL>shutdown immediate
SQL>startup
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~OR~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Solution 2. Set the desired value for instance-specific settings.
1. Login sqlplus as sysdba
2.Modify the values of sga_target for specified instances by using sid=:
SQL>alter system set sga_target=5368709120 scope=spfile sid='instance1';
3.Restart the instance. The spfile will now contain the desired value for that instance 1:
instance1.sga_target=5368709120
*.sga_target=5368709120
alter system reset sga_target scope=spfile sid='instance1';
PDB SGA 使用情况
解决方案
查询语句应该在包含可插拔数据库的 CDB 中执行，因为许多查询使用 CDB 中特定的视图或列。关于非 CDB 环境中的查询，请参考： NOTE:430473.1。
CDB 和所有的可插拔数据库（PDBs）共享由 SGA 和后台进程组成的数据库实例。由于这种内存资源的共享，我们期望区分共享实例的数据库之间的资源使用情况。如果可能的话，将提供查询每个 PDB 使用 SGA 内存情况的语句。
使用 ROUND 函数是为了使查询结果以 MegaBytes 为单位，使其更易读。由于是 ROUND 之后的值，当与不同的 SGA 视图进行比较的时候，结果可能并不完全匹配。
set linesize 150
set pagesize 3000
set NUMWIDTH 15
col Parameter format a30
col component format a28
COLUMN DEFAULT_ATTR FORMAT A7
COLUMN OWNER FORMAT A15
COLUMN OBJECT_NAME FORMAT A15
COLUMN ALL_CONTAINERS FORMAT A3
COLUMN CONTAINER_NAME FORMAT A10
COLUMN CON_ID FORMAT 999
COLUMN pdb_name FORMAT A20
COLUMN memory Format A25
COLUMN spid HEADING 'OSpid' FORMAT a8
COLUMN pid HEADING 'Orapid' FORMAT 999999
COLUMN sid HEADING 'Sess id' FORMAT 99999
COLUMN serial# HEADING 'Serial#' FORMAT 999999
COLUMN status HEADING 'Status' FORMAT a8
COLUMN pga_alloc_mem HEADING 'PGA alloc' FORMAT 999,999,999
COLUMN pga_used_mem HEADING 'PGA used' FORMAT 999,999,999
COLUMN pga_max_mem HEADING 'PGA Max' FORMAT 999,999,999
COLUMN username HEADING 'oracleuser' FORMAT a12
COLUMN osuser HEADING 'OS user' FORMAT a12
COLUMN program HEADING 'Program' FORMAT a24
COLUMN Mbytes Heading 'Mbytes' FORMAT 999,999,999
COLUMN name FORMAT A22
--设置 session 级别的日期格式
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
--记录脚本运行的日期/时间
select sysdate from dual;
下面的查询显示脚本是否在 CDB 中运行。
结果应该显示 con_name（container name）为 CDB$ROOT，con_id（container id）为1。
show pdbs 命令会显示 CDB 中所有 PDB 的信息，包括它们的开启状态和是否处于受限模式。可以通过 container id（con_id） 来辨别 PDB 的名字，用在许多查询中来分析特定 container 的内存使用情况。
如果该命令在非 CDB 数据库中执行，将返回 NULL。
下面的例子中显示了 CDB 中的种子 PDB 和其他四个 PDB。PDB id 是5的PDB_COPY 为非 open 状态。其他 PDB 都是 open 状态。
show con_name
show con_id
show pdbs
CON_NAME
------------------------------
CDB$ROOT
CON_ID
------------------------------
1
CON_ID CON_NAME OPEN MODE RESTRICTED
--------------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB_SS READ WRITE NO
4 PDB1 READ WRITE NO
5 PDB_COPY MOUNTED
6 PDB2 READ WRITE NO
下面的查询显示 CDB 的名字。在这个例子中，名字为 CDB1。
select name, cdb, con_id from v$database;
NAME CDB CON_ID
---------------------- --- ------
CDB1 YES 0
下面的查询显示每个 container 的 ID 信息以及数据库状态。
--每个 container 的信息
SELECT NAME, CON_ID, OPEN_MODE, RESTRICTED, DBID, CON_UID, GUID FROM V$CONTAINERS ORDER BY CON_ID;
NAME CON_ID OPEN_MODE RES DBID CON_UID GUID
---------------------- ------ ---------- --- --------------- --------------- --------------------------------
CDB$ROOT 1 READ WRITE NO 762218087 1 C40F9B49FC9D19E0E0430BAAE80AFF01
PDB$SEED 2 READ ONLY NO 4031134518 4031134518 C40F9B49FC9C19E0E0430BAAE80AFF01
PDB_SS 3 READ WRITE NO 1556201860 1556201860 C4109F71E0095A2FE0430BAAE80A6619
PDB1 4 READ WRITE NO 3296179875 3296179875 C4AFBF825964352DE04362F519904F91
PDB_COPY 5 MOUNTED 1667449117 1667449117 D14DA20BBD781142E0430100007FBAFE
PDB2 6 READ WRITE NO 3868752707 3868752707 D14DA20BBD7C1142E0430100007FBAFE
下面的查询显示与 SGA 和 PGA 相关的参数设置。如果 sga_target 和 pga_aggregate_target 的值为零，并且 memory_target > 0，那么这些值会根据 AMM 动态调整。
--用户定义的参数
select con_id, name as Parameter, value/1024/1024 as Mbytes from v$parameter
where name in ('pga_aggregate_target','memory_target','memory_max_target','sga_max_size','sga_target','pga_aggregate_limit')
order by name;
CON_ID PARAMETER Mbytes
------ ------------------------------ ------------
1 memory_max_target 600
1 memory_target 600
1 pga_aggregate_limit 4,096
1 pga_aggregate_target 0
1 sga_max_size 600
1 sga_target 0
下面的查询显示动态 SGA 组件的当前值，和与每个组件大小相关的其他基本信息。
下面的查询结果显示，所有 sga 组件都与 root container id 0 关联。sga_target 的当前值是 392M。
--显示动态组件的当前值
select con_id, component, ROUND(USER_SPECIFIED_SIZE/1024/1024) as Mbytes, OPER_COUNT, LAST_OPER_TIME as "Last op time", ROUND(current_size/1024/1024) as Mbytes from v$memory_dynamic_components
order by component;
CON_ID COMPONENT Mbytes OPER_COUNT Last op t Mbytes
------ ---------------------------- ------------ --------------- --------- ------------
0 ASM Buffer Cache 0 0 0
0 DEFAULT 16K buffer cache 0 0 0
0 DEFAULT 2K buffer cache 0 0 0
0 DEFAULT 32K buffer cache 0 0 0
0 DEFAULT 4K buffer cache 0 0 0
0 DEFAULT 8K buffer cache 0 0 0
0 DEFAULT buffer cache 0 4 31-DEC-12 68
0 Data Transfer Cache 0 0 0
0 KEEP buffer cache 0 0 0
0 PGA Target 0 0 208
0 RECYCLE buffer cache 0 0 0
0 SGA Target 0 0 392
0 Shared IO Pool 20 0 20
0 java pool 0 0 4
0 large pool 0 3 31-DEC-12 12
0 shared pool 0 1 31-DEC-12 276
0 streams pool 0 0 0
下面对 V$SGAINFO 的查询显示 sga 组件大小、granule size 和 free sga memory。
下面的例子中显示的空闲 SGA 内存为 208M。这部分内存不包含在上面查询显示的当前 sga_target 392M 中，可以用来扩展 sga_target。
--通过数据库视图显示 SGA 内存使用情况
select name, ROUND(bytes/1024/1024) as Mbytes from v$sgainfo;
NAME Mbytes
------------------------- ------------
Fixed SGA Size 2
Redo Buffers 7
Buffer Cache Size 88
Shared Pool Size 276
Large Pool Size 12
Java Pool Size 4
Streams Pool Size 0
Shared IO Pool Size 20
Data Transfer Cache Size 0
Granule Size 4
Maximum SGA Size 597
Startup overhead in Share 107
d Pool
Free SGA Memory Available 208
以下针对 V$SGA 的查询显示了 SGA 大小的基本信息。所显示的变量大小包括各种 SGA 组件和空闲 SGA 内存。
select name, ROUND(value/1024/1024) as Mbytes from v$sga;
NAME Mbytes
------------------------- ------------
Fixed Size 2
Variable Size 500
Database Buffers 88
Redo Buffers 7
以下针对 V$SGA_DYNAMIC_FREE_MEMORY 的查询显示了可以分配给 SGA 的可用内存大小，可以用来扩展 sga_target。这应该与 V$SGAINFO 中显示的值相对应。
-显示可用来扩展 sga_target 的可用空闲 sga 内存
select ROUND(current_size/1024/1024) Mbytes from V$SGA_DYNAMIC_FREE_MEMORY;
Mbytes
------------
208
下面的两个查询显示了 sga 组件和空闲 sga 内存的总和。
这些查询基本上显示了 sga 内存使用的最差情况，因为这个总和包含了用来扩展 sga_target 的空闲 sga 内存。可以从前面的查询中确定空闲内存的大小。
这些结果将显示 SGA 可以达到的最大值，并且可以用于规划目的，以确定需要多少内存来满足 sga 需求。然后需要考虑 pga 内存大小，来确定总内存使用情况。
根据操作系统分配内存的原理，并非所有内存都驻留在物理内存中。部分内存可能在磁盘上处于虚拟状态。您需要使用适当的操作系统工具来确定 sga 的实际物理内存使用情况。然而，由于数据库和块访问的特性，将大部分 sga 内存放到物理内存中是好的做法。
如果 lock_sga 设置为 ture，那么所有的 sga 内存都在物理内存中。当设置了memory_target，不能使用 lock_sga。
--这两个查询显示了包含 free sga memory 的共享内存的最大值。
--取决于配置和操作系统，只能使用 OS 命令确定实际的固定/驻留共享内存。
--如果设置了 lock_sga，则 sga 驻留在内存中。
select ROUND(sum(bytes)/1024/1024) as Mbytes from v$sgainfo
where name not in ('Maximum SGA Size','Startup overhead in Shared Pool','Granule Size');
Mbytes
------------
617
select ROUND(sum(value)/1024/1024) as Mbytes from v$sga;
Mbytes
------------
597
上面两个查询结果的差异是由于 v$sgainfo 中包含了 Shared IO Pool Size，而 v$sga 中不包含。
下面两个查询不包括 free sga memory。这些查询可以用于确定当前分配的实际 sga 内存。
--当设置了 memory_target（不能使用 lock_sga），取决于数据库配置和操作系统，可以缩小共享内存以满足 pga 的需求。
--这些查询显示实际 sga 分配的总和，不包括 sga_target 之上的 free sga memory。
--可能不是所有的 sga 内存都驻留在内存中，一些是在虚拟内存中的。
select ROUND(sum(bytes)/1024/1024) as Mbytes from v$sgastat;
Mbytes
------------
389
select ROUND(sum(bytes)/1024/1024,1) as Mbytes from v$sgainfo
where name not in ('Maximum SGA Size','Startup overhead in Shared Pool','Granule Size','Free SGA Memory Available');
Mbytes
------------
409
同样的，上面两个查询结果的差异是由于 v$sgainfo 中包含了 Shared IO Pool Size，而 v$sga 中不包含。
下面的查询在容器级别分别显示 sga 内存使用情况。
下面的查询结果显示 container id 4 使用了 19M，container id 6 使用了 11M。大多数 sga 内存被 root container 使用，如 container id 0 和 1 所示。
select con_id, ROUND(sum(bytes)/1024/1024) as Mbytes from v$sgastat
group by con_id
order by con_id;
CON_ID Mbytes
------ ------------
0 136
1 213
2 3
3 7
4 19
5 0
6 11
以下查询分别显示容器级别的 sga 内存使用情况，以及各个 sga 组件的情况。
break on con_id skip 4
compute sum of Mbytes on con_id
select con_id, pool, name, ROUND(bytes/1024/1024,2) as Mbytes from v$sgastat
order by con_id,Mbytes;
break on off
CON_ID POOL NAME Mbytes
------ ------------ ------------------------- ------------
0 large pool session heap 0
fixed_sga 2
java pool free memory 4
large pool free memory 6
log_buffer 7
shared_io_pool 20
shared pool free memory 29
buffer_cache 68
****** ------------
sum 136
1 shared pool KCFIS SGA 0
shared pool KTC txn rsrc cnt 0
shared pool kdlxdup swapp 0
....
large pool session heap 5
shared pool event statistics per sess 7
shared pool private strands 7
shared pool row cache 7
shared pool PLMCD 10
shared pool XDBSC 14
shared pool KGLH0 17
shared pool SQLA 23
****** ------------
sum 212
2 shared pool KQR M PO 0
shared pool monitoring column usage e 0
shared pool keomg: entry list 0
...
shared pool work area table entry 0
shared pool KGLS 0
shared pool KGLH0 0
shared pool PDB Heap 1
****** ------------
sum 3
3 shared pool KQR S SO 0
shared pool kpscad: kpscscon 0
shared pool object queue header free 0
...
shared pool KQR L PO 0
shared pool PDB Heap 1
shared pool SQLA 2
shared pool KGLH0 2
****** ------------
sum 7
4 shared pool ktli log buffer pools 0
shared pool ktli log open descs 0
shared pool KGLNA 0
...
shared pool KGLS 1
shared pool PDB Heap 1
shared pool SQLA 2
shared pool KGLH0 3
shared pool XDBSC 10
****** ------------
sum 19
5 shared pool PDB Heap 0
****** ------------
sum 0
6 shared pool ktli log bkts 0
shared pool kwqmncgr: allocate buffer 0
shared pool kkcnRstatIni 0
...
shared pool KQR L PO 1
shared pool KGLS 1
shared pool KQR X SO 1
shared pool PDB Heap 1
shared pool ktli log buf s 2
shared pool SQLA 2
shared pool KGLH0 3
****** ------------
sum 11
下面的查询与上述相同，但包含了 where 子句，只显示超过 10M 的内存分配。
下面的查询可以修改，用于查询更大分配大小，也可以用于仅显示容器中非常大的内存分配。
如果遇到 sga 内存错误，如 ORA-4031，这个查询可以用来查看哪个容器使用了大量资源，可以作为一个候选者从根容器移动到其自己的非容器数据库。
--只显示超过 10M 的内存分配
break on con_id skip 4
select con_id, pool, name, ROUND(bytes/1024/1024,1) as Mbytes from v$sgastat
where bytes/1024/1024 > 10 --alter to see smaller/larger allocations
order by con_id;
break on off
CON_ID POOL NAME Mbytes
------ ------------ ------------------------- ------------
0 shared pool free memory 29
shared_io_pool 20
buffer_cache 68
****** ------------
sum 117
1 shared pool SQLA 23
shared pool XDBSC 14
shared pool KGLH0 17
****** ------------
sum 54
下面的查询显示 sga 和 pga 的总和，也就是 oracle 实例所使用的内存总量。
当 sga 被锁定在内存时，应始终使用此查询。该查询还可以用于确定 sga 分配的最大内存。
v$sga 的总和显示包含 free sga memory 的共享内存的最大值。
SELECT ROUND(SUM(bytes)/1024/1024) AS Mbytes
FROM (SELECT value AS bytes
FROM v$sga
UNION ALL
SELECT value AS bytes
FROM v$sesstat s, v$statname n
WHERE n.STATISTIC# = s.STATISTIC#
AND n.name = 'session pga memory');
Mbytes
------------
663
当 memory_target>0 时，应使用下面的查询
v$sgastat 的总和不包括空闲的 sga 内存。取决于配置和操作系统，只能使用 OS 命令确定实际的固定/驻留共享内存。如果想计算完整 sga 大小，包括空闲 sga 内存，可以使用上面的查询。
SELECT round(sum(Mbytes)) as Mbytes from (select sum(bytes)/1024/1024 as Mbytes from v$sgastat
UNION ALL
SELECT sum(value)/1024/1024 AS Mbytes
FROM v$sesstat s, v$statname n
WHERE n.STATISTIC# = s.STATISTIC#
AND n.name = 'session pga memory');
Mbytes
------------
465
扫一扫
2509
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
