When Oracle replaced the sort_area_size and hash_area_size functionality with thepga_aggregate_targetparameter in Oracle9i, there was a great deal of speculation about how to control sorting and hash joins within the
large PGA region. Here we discuss two issues. How do we increase the RAM for hash joins and RAM sorting within thepga_aggregate_target?
For example, we might have a single evening batch task that would benefit from using all of thepga_aggregate_target,not just the 5% limit.
Increasing Hash Joins
To force hash joins you must perform two steps. It may not be enough to increase the hash_area_size if the CBO is stubborn, and usually you must force the hash join with a hint.
Step 1 - Increase thehash_area_sizemaximum
alter session set workarea_size_policy=manual;
alter session set hash_area_size=1048576000;
Step 2 - Add ause_hashhint to the SQL
select /*+ use_hash(a, b)*/
from . . .
Increasing RAM sorting
In Oracle9i, the sorting default is that no single task may consume more than 5% of thepga_aggregate_targetregion before the sort pages-out to the TEMP tablespace for a disk sort. For parallel
sorts, the limit is 30% of the PGA aggregate, regardless of the number of parallel processes.
You can override the default sorting behavior in two ways:
Option 1- Manual override:
alter session set workarea_size_policy=manual;
alter session set sort_area_size=1048576000;
Option 2- Bounce with special hidden parameter: In this example, we increase the default amount of RAM available to sort operations from 5% to about 50%:
pga_aggregate_target=10g
_smm_max_size=4000000;
Not that the number for_smm_max_sizeis expressed in k-bytes, so this value is about 4.5 gigabytes.
Remember, hidden parameters are totally unsupported, so use this technique at your own risk.
转载自:http://www.dba-oracle.com/oracle_tips_9i_sort_size.htm
分享到:
相关推荐
PGA_PGA自聚焦_PGA_ISAR_自聚焦.zip
PGA_PGA自聚焦_PGA_ISAR_自聚焦_源码.zip
FS-MD-PGA_PGA_SAR_FS成像_自聚焦.zip
FS-MD-PGA_PGA_SAR_FS成像_自聚焦_源码.zip
FS成像算法仿真,加入载机运动误差采用MD和PGA算法进行自聚焦
4.23 PGA_AGGREGATE_TARGET 8 4.24 WORKAREA_SIZE_POLICY 9 4.25 SQL_TRACE 9 4.26 TIMED_STATISTICS 9 4.27 DB_CACHE_SIZE 9 4.28 DB_KEEP_CACHE_SIZE 9 4.29 DB_RECYCLE_CACHE_SIZE 9 4.30 DB_FILE_MULTIBLOCK_...
在使用放大器的场合中,往往希望增益能够调整,以使...这里只介绍BURR-BROWN公司的PGA202/203程控仪表放大器,该芯片无需外围芯片,而且PGA202与PGA203级联使用可组成从1~8000倍的16种程控增益,使应用更灵活方便。
PGA导致oracle内存泄露,经历1个月的时间,终于搞定该问题,对于学习ORACLE 内存泄露是一次比较好的总结。
此sql可以帮助你查看oracle对当前pga大小的推荐值,达到oracle性能优化的目的
获得optimal、onepass、multipass执行次数的百分比,很明显,optimal所占的百分比越高越好,假如onepass和multipass占的百分比很高,就不需要增加pga_aggregate_target的值了,或者调整SQL语句以使用更少的PGA区
v$process查看表的三个新数据列包括pga_used_memory、 pga_allocated_memory和 pga_max_memory。从这些刻度表里,可以看到Oracle环境里各个后台进程的实际内存利用率,还可以查看每个到数据库的连接所需要的内存。
基于相位梯度法PGA的 Matlab 估计相位误差 估计多普勒中心
stm32f303 内部PGA的使用,包括源码,亲自测试可用,增益带宽大概10M左右吧
KEIL软件C语言开发,为C8051F系列单片机的PGA编程
文档里包含了对Oracle PGA详细讲解!
简单的主从式并行遗传算法,使得实现速度更快
可以处理来自日本的大批量地震动,可以解决大量求解地震动数据得到PGA,并可以用于求解其他参数。
简单并行遗传算法代码,由串行遗传算法改编而来。
基于msp430f247的256点fft程序,外部adcPGA113 采样,12864显示。