Oracle 参数 PGA_AGGREGATE_LIMIT 官方解释,作用,如何配置最优化建议
PGA_AGGREGATE_LIMIT参数用来设置数据库中的总的PGA内存配置,它的范围是大小在几KB到几GB之间的整数,这个参数在11g版本以后出现。
这个参数的默认值根据操作系统不同而不同,Windows操作系统默认值为2GB,Linux等操作系统默认值为4GB。
设置PGA_AGGREGATE_LIMIT参数的步骤如下:
1.确保数据库已经关闭,打开控制文件,在控制文件中添加参数PGA_AGGREGATE_LIMIT,如pga_aggregate_limit=4G;
2.打开parameter文件,添加该参数PGA_AGGREGATE_LIMIT,确保要决定设置一个合理的值,值必须大于100KB。
3.设置参数文件,重新启动数据库;
4.使用以下SQL查看当前设置的值,查看是否设置正确
select name,value from v$parameter where name= pga_aggregate_limit
5.如果设置正确,提交参数文件保存,关闭数据库。
官方英文解释PGA_AGGREGATE_LIMIT specifies a limit on the aggregate PGA memory consumed by the instance.
If MEMORY_TARGET is set, then PGA_AGGREGATE_LIMIT defaults to the MEMORY_MAX_TARGET value.
If MEMORY_TARGET is not set, then PGA_AGGREGATE_LIMIT defaults to 200% of PGA_AGGREGATE_TARGET.
If MEMORY_TARGET is not set, and PGA_AGGREGATE_TARGET is explicitly set to 0, then the value of PGA_AGGREGATE_LIMIT is set to 90% of the physical memory size minus the total SGA size.
In all cases, the default PGA_AGGREGATE_LIMIT is at least 2GB and at least 3MB times the PROCESSES parameter (and at least 5MB times the PROCESSES parameter for an Oracle RAC instance).
For a PDB, the default value is the same as the CDB s default value.
Do not attempt to set PGA_AGGREGATE_LIMIT below its default value, even in a parameter file (pfile), or instance startup will fail. However, PGA_AGGREGATE_LIMIT can be set to 0 either in a parameter file or dynamically after startup. If a value of 0 is specified, it means there is no limit to the aggregate PGA memory consumed by the instance.
Parallel queries will be treated as a unit. First, the sessions that are using the most untunable memory will have their calls aborted. Then, if the total PGA memory usage is still over the limit, the sessions that are using the most untunable memory will be terminated.
SYS processes and background processes other than job queue processes will not be subjected to any of the actions described in this section. Instead, if they are using the most untunable memory, they will periodically write a brief summary of their PGA usage to a trace file.
Note:
This parameter is optional for pluggable databases (PDBs). When this parameter is set for a PDB, it specifies the maximum PGA size for the PDB.
To be able to use Resource Manager in a CDB to control the amount of memory each PDB can use:
The NONCDB_COMPATIBLE initialization parameter must be set to FALSE at the CDB level (in the root of the CDB).
You must set the PGA_AGGREGATE_LIMIT initialization parameter in a PDB to a value that meets these requirements:
Greater than or equal to twice the value of PGA_AGGREGATE_TARGET set in the PDB
When you set PGA_AGGREGATE_LIMIT in a PDB to a value that does not meet these requirements, you receive an error. If these requirements are violated after the PDB’s parameter is set (for example, if the PGA_AGGREGATE_LIMIT value is changed at the CDB level, Oracle will adjust the PDB’s value to meet these requirements.
Oracle Multitenant
Administrator s Guide for more information about the initialization parameters that control the memory usage of PDBs
Oracle Database
Performance Tuning Guide for more information about this parameter
我想要获取技术服务或软件
服务范围:MySQL、ORACLE、SQLSERVER、MongoDB、PostgreSQL 、程序问题
服务方式:远程服务、电话支持、现场服务,沟通指定方式服务
技术标签:数据恢复、安装配置、数据迁移、集群容灾、异常处理、其它问题
本站部分文章参考或来源于网络,如有侵权请联系站长。
数据库远程运维 Oracle 参数 PGA_AGGREGATE_LIMIT 官方解释,作用,如何配置最优化建议
相关文章
- Oracle 视图 DBA_INMEMORY_AIMTASKS 官方解释,作用,如何使用详细说明
- Oracle 参数 CONTAINERS_PARALLEL_DEGREE 官方解释,作用,如何配置最优化建议
- Oracle 参数 CURSOR_SPACE_FOR_TIME 官方解释,作用,如何配置最优化建议
- Oracle 参数 HS_AUTOREGISTER 官方解释,作用,如何配置最优化建议
- Oracle 参数 LARGE_POOL_SIZE 官方解释,作用,如何配置最优化建议
- Oracle 参数 OPTIMIZER_ADAPTIVE_PLANS 官方解释,作用,如何配置最优化建议
- 配置Oracle最大并发数调整实践(oracle最大并发数)
- 掌握Oracle条件查询的基本使用方法(oracle条件查询语句)
- 分析Oracle数据库触发器的类型及其应用(oracle触发器类型)
- 利用Oracle触发器实现自动化操作(oracle触发器类型)
- 精通 Oracle 触发器:细节与类型一目了然(oracle触发器类型)
- Oracle数据库:只有数据文件吗?(oracle只有数据文件)
- 配置Oracle监听地址配置实战指南(oracle监听地址)
- Oracle教程:快速安装与配置步骤(oracle教程安装)
- Oracle 监听实例的设置与配置方法详解(oracle监听实例)
- Oracle 数据库内存配置的规范性研究(oracle内存配置规范)
- Oracle如何降低内存使用(oracle 内存调小)
- Oracle关闭回显一步捷径(oracle关闭回显)
- 吧醉心于Oracle-一场酒吧之行(l oracle 酒)
- 使用CX Oracle实现高效的编码管理(cx oracle 编码)
- Java配置Oracle实现稳定的跨平台数据库连接(java配置oracle)
- 里探索Oracle中的编辑器命令行(oracle中编辑器在哪)
- Oracle时分秒的精确保存(oracle 保存时分秒)
- Oracle EMCA安装配置指南(oracle中emca)
- Oracle无需TNS配置的优势(oracle不配置tns)
- Oracle OCM考场预备,成就梦想成真(oracle ocm考场)