search
HomeDatabaseMysql TutorialOracle PGA内存的配置和使用
Oracle PGA内存的配置和使用Jun 07, 2016 pm 03:01 PM
oracleuseMemoryEnterConfiguration

欢迎进入Oracle社区论坛,与200万技术人员互动交流 >>进入 PGA(Process Global Area),是server process一段私有内存区,它包含有全局变量,数据结构和一些控制信息。在Oracle8i 中,PGA调整非常复杂,要调整SORT_AREA_SIZE、HASH_AREA_SIZE、BITMAP_MERGE_A

欢迎进入Oracle社区论坛,与200万技术人员互动交流 >>进入

  PGA(Process Global Area),是server process一段私有内存区,它包含有全局变量,数据结构和一些控制信息。在Oracle8i 中,PGA调整非常复杂,要调整SORT_AREA_SIZE、HASH_AREA_SIZE、BITMAP_MERGE_AREA_SIZE、

  PGA(Process Global Area),是server process一段私有内存区,它包含有全局变量,数据结构和一些控制信息。在Oracle8i 中,PGA调整非常复杂,要调整SORT_AREA_SIZE、HASH_AREA_SIZE、BITMAP_MERGE_AREA_SIZE、 CREATE_BITMAP_AREA_SIZE等参数。在ORACLE9I中,只需要调整 PGA_AGGREGATE_TARGET。

  --PGA_AGGREGATE_TARGET初始化设置

  PGA_AGGREGATE_TARGET的值应该基于Oracle实例可利用内存的总量来设置,这个参数可以被动态的修改。假设Oracle实例可分配4GB的物理内存,剩下的内存分配给操作系统和其它应用程序。你也许会分配80%的可用内存给Oracle实例,即3.2G。现在必须在内存中划分SGA和PGA区域。

  在OLTP系统中,典型PGA内存设置应该是总内存的较小部分(例如20%),剩下80%分配给SGA。

  OLTP:PGA_AGGREGATE_TARGET = (total_mem * 80%) * 20%

  copyright Sqlclub

  在DSS系统中,由于会运行一些很大的查询,典型的PGA内存最多分配70%的内存。

  DSS:PGA_AGGREGATE_TARGET = (total_mem * 80%) * 50%

  在这个例子中,总内存4GB,DSS系统,你可以设置PGA_AGGREGATE_TARGET为1600MB,OLTP则为655MB。

  --配置PGA自动管理

  不用重启DB,直接在线修改。

  SQL> alter system set workarea_size_policy=auto scope=both;

  System altered.

  SQL> alter system set pga_aggregate_target=512m scope=both;

  System altered.

  SQL> show parameter workarea

  NAME                                 TYPE        VALUE Sqlclub

  ------------------------------------ ----------- ------------------------------

  workarea_size_policy                 string      AUTO --这个设置成AUTO

  SQL> show parameter pga

  NAME                                 TYPE        VALUE

  ------------------------------------ ----------- ------------------------------

  pga_aggregate_target                 big integer 536870912

  SQL>

  --监控自动PGA内存管理的性能

  V$PGASTAT:这个视图给出了一个实例级别的PGA内存使用和自动分配的统计。 Sqlclub学习社区

  SQL> set lines 256

  SQL> set pages 42

  SQL> SELECT * FROM V$PGASTAT;

  NAME                                                                  VALUE UNIT

  ---------------------------------------------------------------- ---------- ------------

  aggregate PGA target parameter                                    536870912 bytes    --当前PGA_AGGREGATE_TARGET的值

  aggregate PGA auto target                                         477379584 bytes    --当前可用于自动分配了的PGA大小,不应该比PGA_AGGREGATE_TARGET 小

  内容来自Sqlclub.cn

  global memory bound                                                26843136 bytes    --自动模式下工作区域的最大大小,Oracle根据工作负载自动调整。

  total PGA inuse                                                     6448128 bytes

  total PGA allocated                                                11598848 bytes    --PGA的最大分配

  Sqlclub

  maximum PGA allocated                                             166175744 bytes

  total freeable PGA memory                                            393216 bytes    --PGA的最大空闲大小

  PGA memory freed back to OS                                        69074944 bytes

  total PGA used for auto workareas                                         0 bytes    --PGA分配给auto workareas的大小 Sqlclub学习社区

  maximum PGA used for auto workareas                                 1049600 bytes

  total PGA used for manual workareas                                       0 bytes

  maximum PGA used for manual workareas                                530432 bytes

  over allocation count                                                  1118    --实例启动后,发生的分配次数,如果这个值大于0,就要考虑增加pga的值 copyright Sqlclub

  bytes processed                                                   114895872 bytes

  extra bytes read/written                                            4608000 bytes

  cache hit percentage                                                  96.14 percent    --命中率

  16 rows selected. copyright Sqlclub

  --V$PGA_TARGET_ADVICE

  SQL> SELECT round(PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb,

  ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc,

  ESTD_OVERALLOC_COUNT

  FROM v$pga_target_advice;

  The output of this query might look like the following:

  TARGET_MB   CACHE_HIT_PERC ESTD_OVERALLOC_COUNT

  ---------- -------------- --------------------

  63       23          367

  125        24          30

  250        30          3

  375        39          0

  500        58          0

  600        59          0

  700        59          0 Sqlclub.cn

  800        60          0

  900        60          0

  1000    61          0

  1500    67          0

  2000    76          0

  3000    83          0

  4000    85          0

  可以看出当TARGET_MB 为375M是ESTD_OVERALLOC_COUNT=0,所以我们可以将PGA_AGGREGATE_TARGET设置成375M。

Oracle PGA内存的配置和使用

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
什么是oracle asm什么是oracle asmApr 18, 2022 pm 04:16 PM

oracle asm指的是“自动存储管理”,是一种卷管理器,可自动管理磁盘组并提供有效的数据冗余功能;它是做为单独的Oracle实例实施和部署。asm的优势:1、配置简单、可最大化推动数据库合并的存储资源利用;2、支持BIGFILE文件等。

oracle怎么查询所有索引oracle怎么查询所有索引May 13, 2022 pm 05:23 PM

方法:1、利用“select*from user_indexes where table_name=表名”语句查询表中索引;2、利用“select*from all_indexes where table_name=表名”语句查询所有索引。

oracle全角怎么转半角oracle全角怎么转半角May 13, 2022 pm 03:21 PM

在oracle中,可以利用“TO_SINGLE_BYTE(String)”将全角转换为半角;“TO_SINGLE_BYTE”函数可以将参数中所有多字节字符都替换为等价的单字节字符,只有当数据库字符集同时包含多字节和单字节字符的时候有效。

Oracle怎么查询端口号Oracle怎么查询端口号May 13, 2022 am 10:10 AM

在Oracle中,可利用lsnrctl命令查询端口号,该命令是Oracle的监听命令;在启动、关闭或重启oracle监听器之前可使用该命令检查oracle监听器的状态,语法为“lsnrctl status”,结果PORT后的内容就是端口号。

oracle怎么删除sequenceoracle怎么删除sequenceMay 13, 2022 pm 03:35 PM

在oracle中,可以利用“drop sequence sequence名”来删除sequence;sequence是自动增加数字序列的意思,也就是序列号,序列号自动增加不能重置,因此需要利用drop sequence语句来删除序列。

oracle怎么查询数据类型oracle怎么查询数据类型May 13, 2022 pm 04:19 PM

在oracle中,可以利用“select ... From all_tab_columns where table_name=upper('表名') AND owner=upper('数据库登录用户名');”语句查询数据库表的数据类型。

oracle查询怎么不区分大小写oracle查询怎么不区分大小写May 10, 2022 pm 05:45 PM

方法:1、利用“LOWER(字段值)”将字段转为小写,或者利用“UPPER(字段值)”将字段转为大写;2、利用“REGEXP_LIKE(字符串,正则表达式,'i')”,当参数设置为“i”时,说明进行匹配不区分大小写。

Oracle怎么修改sessionOracle怎么修改sessionMay 13, 2022 pm 05:06 PM

方法:1、利用“alter system set sessions=修改后的数值 scope=spfile”语句修改session参数;2、修改参数之后利用“shutdown immediate – startup”语句重启服务器即可生效。

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

Repo: How To Revive Teammates
1 months agoBy尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
1 months agoBy尊渡假赌尊渡假赌尊渡假赌

Hot Tools

SublimeText3 English version

SublimeText3 English version

Recommended: Win version, supports code prompts!

Safe Exam Browser

Safe Exam Browser

Safe Exam Browser is a secure browser environment for taking online exams securely. This software turns any computer into a secure workstation. It controls access to any utility and prevents students from using unauthorized resources.

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

DVWA

DVWA

Damn Vulnerable Web App (DVWA) is a PHP/MySQL web application that is very vulnerable. Its main goals are to be an aid for security professionals to test their skills and tools in a legal environment, to help web developers better understand the process of securing web applications, and to help teachers/students teach/learn in a classroom environment Web application security. The goal of DVWA is to practice some of the most common web vulnerabilities through a simple and straightforward interface, with varying degrees of difficulty. Please note that this software

mPDF

mPDF

mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),