Oracle RAC memory
May282018
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 |
srvctl stop instance -d orcl -i orcl2 srvctl start instance -d orcl -i orcl2 srvctl status database -d orcl SQL> select count(*),inst_id from gv$session group by inst_id; COUNT(*) INST_ID ---------- ---------- 1148 1 85 2 [oracle@hisdb02 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Mon May 28 19:48:40 2018 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> show parameter memory NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ hi_shared_memory_address integer 0 memory_max_target big integer 103168M memory_target big integer 103168M shared_memory_address integer 0 SQL> show parameter sga NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ lock_sga boolean FALSE pre_page_sga boolean FALSE sga_max_size big integer 103168M sga_target big integer 0 SQL> show parameter _pool_size NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ global_context_pool_size string java_pool_size big integer 0 large_pool_size big integer 0 olap_page_pool_size big integer 0 shared_pool_size big integer 0 streams_pool_size big integer 0 SQL> show parameter pga_ NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ pga_aggregate_target big integer 0 SQL> |
ORACLE内存分配和使用建议
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
1、OLTP系统 PGA_AGGREGATE_TARGET=(<Total Physical Memory> * 80%) * 20% 2、DSS系统 PGA_AGGREGATE_TARGET=(<Total Physical Memory> * 80%) * 50% 归纳如下: SGA + PGA <= <Total Physical Memory> * 80% 即: SGA_MAX_SIZE + PGA_AGGREGATE_TARGET <= <Total Physical Memory> * 80% 进一步简化: MEMORY_TARGET <= MEMORY_MAX_TARGET <= <Total Physical Memory> * 80% |
一般内存占用过大可以从两方面着手:
1:查查是不是数据库执行了很多低性能的sql,没有做绑定变量导致大量硬解析导致的;
2.可以根据v$session和v$process两个视图,根据paddr和addr找到spid,查查是占用内存较大的进程,具体进程具体分析。
1:查查是不是数据库执行了很多低性能的sql,没有做绑定变量导致大量硬解析导致的;
2.可以根据v$session和v$process两个视图,根据paddr和addr找到spid,查查是占用内存较大的进程,具体进程具体分析。
ORACLE RAC运行在300G内存以上需要考虑的东西
Best Practices and Recommendations for RAC databases using SGA larger than 300GB (文档 ID 1619155.1)
变更oracle 11.2.0.3 rac sga手工管理为sga及pga全自动管理
oracle11g rac asm 实例内存修改
Oracle体系结构之rac内存管理
Oracle 11.2.0.4 x64 RAC扩展存储空间
oracle数据库内存调整之增加内存
大型数据中心Oracle RAC调整内存参数
oracle rac服务器增加内存后无法重启数据库的问题及解决
Oracle 11g 修改SGA大小
Oracle RAC数据库运维在大内存服务器上的注意事项
LINUX 11G RAC ASM磁盘组在线增加磁盘扩容
oracle内存分配与调整
dataguard for RAC(主) to RAC(备)(1)
dataguard for RAC(主) to RAC(备)(2)