How to estimate pga_aggregate_target

-- Displays the overall pga usage
--

select sum(value) max_pga_mem
from v$sesstat s, v$statname n
where s.statistic# = n.statistic#
and n.name = 'session pga memory max';

-- Displays the overall current pga usage

select sum(value) all_current_sess_mem
from v$sesstat s, v$statname n
where s.statistic# = n.statistic#
and n.name = 'session pga memory';

--this should be accurate
select name, sum(value/1024) "Value - KB"
from v$statname n,
v$session s,
v$sesstat t
where s.sid=t.sid
and n.statistic# = t.statistic#
and s.type = 'USER'
and s.username is not NULL
and n.name in ('session pga memory', 'session pga memory max',
'session uga memory', 'session uga memory max')
group by name;

SORT_AREA_SIZE in 10g shared server

--In current 9i database, we don't use PGA_AGGREGATE_TARGET and use SORT_AREA_SIZE =1M
--In 10g database, we want to use PGA_AGGEGATE_TARGET, however below excepted from Oracle Document "Database Reference" confuses me, as we have shared server running with this database. Do I still need to set it explicitly?

"Note: Oracle does not recommend using the SORT_AREA_SIZE parameter unless the instance is configured with the shared server option. Oracle recommends that you enable automatic sizing of SQL working areas by setting PGA_AGGREGATE_TARGET instead. SORT_AREA_SIZE is retained for backward compatibility."

Conclusion from AskTom:

can safely leave sort_area_retained_size & sort_area_size as default shown below for 10g database using shared server, if set PGA_AGGREGATE_TARGET and workarea_size_policy='auto' sort_area_retained_size integer 0
sort_area_size integer 65536

ANSWERS from AskTom:

if ( version = 9i )
then
 if ( pga_aggregate_target is set and workarea_size_policy is auto )
 then
      if ( connection is dedicated server )
      then
            these parameters (sort area [retained] size) need not be set
      else
            they do need to be set, shared server uses sort area size
      end if
 else
      they do need to be set
 end if
elsif (version > 9i )
then
 if ( pga_aggregate_target is set and workarea_size_policy is auto )
 then
      these parameters (sort area [retained] size) need not be set
 else
      they do need to be set
 end if

version 9i:
shared server -> sort_area_size is used regardless.
dedicated server AND workarea = auto -> pga_aggregate_target is used
dedicated server AND workarea = manual -> sort_area_size is used

version 10g
workarea=auto -> pga_aggregate_target
workarea= manual -> sort_area_size


you need not remove the sort_area_size, and since sessions could set workarea to manual - you might


just want to leave it in with YOUR default.
end if

Automatic PGA Memory Management
Starting with Oracle9i, Release 1, a new way to manage PGA memory was introduced that avoids using
the SORT_AREA_SIZE, BITMAP_MERGE_AREA_SIZE, and HASH_AREA_SIZE Parameters. It was introduced to
attempt to address a few issues:
 *    Ease of use: Much confusion surrounded how to set the proper *_AREA_SIZE parameters. There
was also much confusion over how those parameters actually worked and how memory was allocated.
 *    Manual allocation was a “one-size-fits-all” method: Typically as the number of users
running similar applications against a database went up, the amount of memory used for
sorting/hashing went up linearly as well. If 10 concurrent users with a sort area size of 1MB used
10MB of memory, 100 concurrent users would probably use 100MB, 1,000 would probably use 1000MB, and
so on. Unless the DBA was sitting at the console continually adjusting the sort/hash area size
settings, everyone would pretty much use the same values all day long. Consider the previous
example, where you saw for yourself how the physical I/O to temp decreased as the amount of RAM we
allowed ourselves to use went up. If you run that example for yourself, you will almost certainly
see a decrease in response time as the amount of RAM available for sorting increases. Manual
allocation fixes the amount of memory to be used for sorting at a more or less constant number,
regardless of how much memory is actually available. Automatic memory management allows us to use
the memory when it is available; it dynamically adjusts the amount of memory we use based on the
workload.
 *    Memory control: As a result of the previous point, it was hard, if not impossible, to keep
the Oracle instance inside a “box” memory-wise. You could not control the amount of memory the
instance was going to use, as you had no real control over the number of simultaneous sorts/hashes
taking place. It was far too easy to use more real memory (actual physical free memory) than was
available on the machine.
Enter automatic PGA memory management. Here, you first simply set up and size the SGA. The SGA is a
fixed-size piece of memory, so you can very accurately see how big it is, and that will be its
total size (until and if you change that). You then tell Oracle, “This is how much memory you
should try to limit yourself across all workareas—a new umbrella term for the sorting and hashing
areas you use.” Now, you could in theory take a machine with 2GB of physical memory and allocate
768MB of memory to the SGA and 768MB of memory to the PGA, leaving 512MB of memory for the OS and
other processes. I say “in theory” because it doesn’t work exactly that cleanly, but it’s close.
Before I discuss why that is true, we’ll take a look at how to set up automatic PGA memory
management and turn it on.
The process to set this up involves deciding on the proper values for two instance initialization
parameters, namely
 *    WORKAREA_SIZE_POLICY: This parameter may be set to either MANUAL, which will use the sort
area and hash area size parameters to control the amount of memory allocated, or AUTO, in which
case the amount of memory allocated will vary based on the current workload present in the
database. The default and recommended value is AUTO.
 *    PGA_AGGREGATE_TARGET: This parameter controls how much memory the instance should
allocate, in total, for all workareas used to sort/hash data. Its default value varies by version
and may be set by various tools such as the DBCA. In general, if you are using automatic PGA memory
management, you should explicitly set this parameter.
So, assuming that WORKAREA_SIZE_POLICY is set to AUTO, and PGA_AGGREGATE_TARGET has a nonzero
value, you will be using the new automatic PGA memory management. You can “turn it on" in your
session via the ALTER SESSION command or at the system level via the ALTER SESSION command.
Note    Bear in mind the previously discussed caveat that in Oracle9i, shared server connections
will not use automatic memory management; rather, they will use the SORT_AREA_SIZE and
HASH_AREA_SIZE parameters to decide how much RAM to allocate for various operations. In Oracle 10g
and up, automatic PGA memory management is available to both connection types. It is important to
properly set the SORT_AREA_SIZE and HASH_AREA_SIZE parameters when using shared server connections
with Oracle9i.
So, the entire goal of automatic PGA memory management is to maximize the use of RAM while at the
same time not using more RAM than you want. Under manual memory management, this was virtually
impossible goal to achieve. If you set SORT_AREA_SIZE to 10MB, when one user was performing a sort
operation that user would use up to 10MB for the sort workarea. If 100 users were doing the same,
they would use up to 1,000MB of memory. If you had 500MB of free memory, the single user performing
a sort by himself could have used much more memory, and the 100 users should have used much less.
That is what automatic PGA memory management was designed to do. Under a light workload, memory
usage could be maximized as the load increases on the system, and as more users perform sort or
hash operations, the amount of memory allocated to them would decrease—to obtain the goal of using
all available RAM, but not attempting to use more than physically exists.

35岁前应做好的十件事

35岁是青春的后期，35岁以后是收获的季节，如果你没有资格说这句话，你将会憎恨自己。所以在35岁以前，你最好把下面十件事做好。
　　第一，学会本行业所需要的一切知识并有所发展。已故零件大王布鲁丹在他35岁时，已经成为零件行业的领袖，并且组建了年收入达千万美元的海湾与西部工业公司。每个人在年轻时都可能有过彻夜不眠、刻苦攻读，这在20岁甚或30岁都没有问题，但到了35岁，就不应该再为学习基本技能而大伤脑筋了。35岁之前是一个人从事原始积累的阶段，35岁之后就应该薄发了。

　　第二，养成个人风格。在35岁以前，找出你所喜欢的，不论是衣着或是爱好，哪怕是与众不同的小习惯也好。20岁、30岁时你可以不断尝试、不断改变，但是到了35岁，你便要明确地建立个人风格。

　　第三，在感情生活方面平和安定。在攀登事业的高峰时，如果私人生活不愉快，陷入感情危机，对你会产生很大的干扰，甚至会逐渐令你对别的事物失去兴趣。那些在35岁之前私人生活已经平和安定的人，一般都比生活动荡不安的人有更大的机会获得成功。在35岁以后，你应该专注地看着你对事业的投资开始获利。

　　第四，明白自己的短处。承认有些事情你的确做不好，或者不愿做。在35岁之前，一定要投入你所喜爱、所擅长的那种工作。否则，35岁之后必然会有一段郁郁不乐的日子。而且，真正的成功可能因为活力的消退而丧失。

　　第五，知道自己的长处。你应该知道自己擅长什么，并且清楚你所喜欢做而又做得比别人好的事情。不管你目前担任什么样的角色，知道自己的长处对成功都很重要。

　　第六，储备辞职另谋生路的钱。在这个多变的职业世界里，你也许不会永远在一个地方工作，或者永远在一个位置上淋漓尽致地发挥自己，当你感到无法施展时，你很可能会想到辞职，如果你事先储蓄了足够的钱，你便有了一个安全的后盾。

　　第七，建立人际关系网。如果到了35岁你仍未建立起牢固的人际关系网，那你就有麻烦了。人际关系网不是一朝一夕就能建立起来的，它需要几年甚至十几年的培养。一个人在事业上、生活上的成功其实如同一个政党的成功，你要有许多人散布在适当的地方，你可以依赖他们，他们也可以依赖你。

　　第八，学会授权他人。许多人不肯或不能这样做，因此始终被钉在从属的职位上。授权他人是成功的一半，一个事无巨细，不能将工作授权别人的人，注定会遇到极大的障碍。到了35岁，你最好已成为这方面的专家。换言之，你懂得挑选合适的人并信任他们。

　　第九，学会在什么时候三缄其口。因说话不小心而自毁前程的人，比因为任何其他原因丧失成功的人都多。要学会保持沉默而且看起来机智———别人自然以为你知道的比实际还多。别讲别人的闲话，别谈论你自己的大计，守口如瓶所赢得的声誉，远比讲人闲话所带来的东西更加珍贵。

　　第十，对人要忠诚。如果你到了35岁仍未能建立起坚如磐石的忠诚信誉，这一缺点将会困扰你一生。35岁以前，忠诚只是投资；35岁以后，你会作为一个可以信赖的人收到忠诚的回报。”。

Shared server health check

prompt ***************************************
prompt Dispatcher detail usage
prompt ***************************************
prompt ( if time busy >50, then change MTS_MAX_DISPATCHERS in init.ora)
select name, status, idle, busy, busy/(busy+idle)*100 "Time Busy Rate" from v$dispatcher;

--select network "Protocol",
-- to_char((sum(busy)/(sum(busy)+sum(idle))*100), '99.9999') "%Busy"
--from v$dispatcher
--group by network
--order by to_char((sum(busy)/(sum(busy)+sum(idle))*100), '99.9999') desc;

--Use the following statement to check for responses to user processes that are waiting in a queue to be sent to the user:
Prompt *************************************
Prompt Average wait time for response queue
Prompt *************************************
select Network Protocol,
Decode (Sum(Totalq), 0, 'No Responses',
Sum(Wait) / Sum(TotalQ) || ' hundredths of a second')
"Average Wait Time Per Response"
from V$Queue Q, V$Dispatcher D
where Q.Type = 'DISPATCHER'
and Q.Paddr = D.Paddr
group by Network;

--Use the following statement to check the requests from user processes that are waiting in a queue to be sent to the user:
Prompt *******************************************
Prompt All average wait time for common requests
Prompt *******************************************

select Decode (Sum(Totalq), 0, 'Number of Requests',
Sum(Wait) / Sum(TotalQ) || 'hundredths of a second')
"Average Wait Time Per Request"
from V$Queue
where Type = 'COMMON';

v$sqlarea,v$sql,v$sqltext这三个视图提供的sql语句有什么区别？

V$SQLAREA

V$SQLAREA lists statistics on shared SQL area and contains one row per SQL string. It provides statistics on SQL statements that are in memory, parsed, and ready for execution.

V$SQL

V$SQL lists statistics on shared SQL area without the GROUP BY clause and contains one row for each child of the original SQL text entered. Statistics displayed in V$SQL are normally updated at the end of query execution. However, for long running queries, they are updated every 5 seconds. This makes it easy to see the impact of long running SQL statements while they are still in progress.

V$SQLTEXT

This view contains the text of SQL statements belonging to shared SQL cursors in the SGA.

v$sqltext
存储的是完整的SQL,SQL被分割

SQL> desc v$sqltext
Name Null? Type
----------------------------------------- -------- ----------------------------
ADDRESS RAW(4) ---------
HASH_VALUE NUMBER --------- 和 address 一起唯一标志一条sql
COMMAND_TYPE NUMBER
PIECE NUMBER ---------- 分片之后的顺序编号
SQL_TEXT VARCHAR2(64) -------------- 注意长度



v$sqlarea --------- 存储的SQL 和一些相关的信息，比如累计的执行次数，逻辑读，物理读等统计信息
SQL> desc v$sqlarea
Name Null? Type
----------------------------------------- -------- ----------------------------
SQL_TEXT VARCHAR2(1000)
SHARABLE_MEM NUMBER
PERSISTENT_MEM NUMBER
RUNTIME_MEM NUMBER
SORTS NUMBER
VERSION_COUNT NUMBER
LOADED_VERSIONS NUMBER
OPEN_VERSIONS NUMBER
USERS_OPENING NUMBER
FETCHES NUMBER
EXECUTIONS NUMBER
USERS_EXECUTING NUMBER
LOADS NUMBER
FIRST_LOAD_TIME VARCHAR2(38)
INVALIDATIONS NUMBER
PARSE_CALLS NUMBER
DISK_READS NUMBER
BUFFER_GETS NUMBER
ROWS_PROCESSED NUMBER
COMMAND_TYPE NUMBER
OPTIMIZER_MODE VARCHAR2(25)
PARSING_USER_ID NUMBER
PARSING_SCHEMA_ID NUMBER
KEPT_VERSIONS NUMBER
ADDRESS RAW(4)
HASH_VALUE NUMBER
MODULE VARCHAR2(64)
MODULE_HASH NUMBER
ACTION VARCHAR2(64)
ACTION_HASH NUMBER
SERIALIZABLE_ABORTS NUMBER
CPU_TIME NUMBER
ELAPSED_TIME NUMBER
IS_OBSOLETE VARCHAR2(1)
CHILD_LATCH NUMBER




v$sql ---------- 存储的是具体的SQL 和执行计划相关信息，实际上，v$sqlarea 可以看做 v$sql 根据 sqltext 等 做了 group by 之后的信息


SQL> desc v$sql
Name Null? Type
----------------------------------------- -------- ----------------------------
SQL_TEXT VARCHAR2(1000)
SHARABLE_MEM NUMBER
PERSISTENT_MEM NUMBER
RUNTIME_MEM NUMBER
SORTS NUMBER
LOADED_VERSIONS NUMBER
OPEN_VERSIONS NUMBER
USERS_OPENING NUMBER
FETCHES NUMBER
EXECUTIONS NUMBER
USERS_EXECUTING NUMBER
LOADS NUMBER
FIRST_LOAD_TIME VARCHAR2(38)
INVALIDATIONS NUMBER
PARSE_CALLS NUMBER
DISK_READS NUMBER
BUFFER_GETS NUMBER
ROWS_PROCESSED NUMBER
COMMAND_TYPE NUMBER
OPTIMIZER_MODE VARCHAR2(10)
OPTIMIZER_COST NUMBER
PARSING_USER_ID NUMBER
PARSING_SCHEMA_ID NUMBER
KEPT_VERSIONS NUMBER
ADDRESS RAW(4)
TYPE_CHK_HEAP RAW(4)
HASH_VALUE NUMBER
PLAN_HASH_VALUE NUMBER
CHILD_NUMBER NUMBER ---------- 注意这个
MODULE VARCHAR2(64)
MODULE_HASH NUMBER
ACTION VARCHAR2(64)
ACTION_HASH NUMBER
SERIALIZABLE_ABORTS NUMBER
OUTLINE_CATEGORY VARCHAR2(64)
CPU_TIME NUMBER
ELAPSED_TIME NUMBER
OUTLINE_SID NUMBER -------------- 注意这里跟 outline 有关
CHILD_ADDRESS RAW(4)
SQLTYPE NUMBER
REMOTE VARCHAR2(1)
OBJECT_STATUS VARCHAR2(19)
LITERAL_HASH_VALUE NUMBER
LAST_LOAD_TIME VARCHAR2(38)
IS_OBSOLETE VARCHAR2(1)
CHILD_LATCH NUMBER


另外注意这个
QL> desc v$sql_plan
Name Null? Type
----------------------------------------- -------- ----------------------------
ADDRESS RAW(4)
HASH_VALUE NUMBER
CHILD_NUMBER NUMBER ------------ 注意这个和 v$sql 里面的相同字段
OPERATION VARCHAR2(60)
OPTIONS VARCHAR2(60)
OBJECT_NODE VARCHAR2(20)
OBJECT# NUMBER
OBJECT_OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(64)
OPTIMIZER VARCHAR2(40)
ID NUMBER
PARENT_ID NUMBER
DEPTH NUMBER
POSITION NUMBER
SEARCH_COLUMNS NUMBER
COST NUMBER
CARDINALITY NUMBER
BYTES NUMBER
OTHER_TAG VARCHAR2(70)
PARTITION_START VARCHAR2(10)
PARTITION_STOP VARCHAR2(10)
PARTITION_ID NUMBER
OTHER VARCHAR2(4000)
DISTRIBUTION VARCHAR2(40)
CPU_COST NUMBER
IO_COST NUMBER
TEMP_SPACE NUMBER
ACCESS_PREDICATES VARCHAR2(4000)
FILTER_PREDICATES VARCHAR2(4000)


实际上，看起来同样的一句SQL ，往往具有不同的执行计划
如果是不同的数据库用户，那么相应的涉及的 对象 可能都不一样，注意v$sql 中
OBJECT# NUMBER
OBJECT_OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(64)
OPTIMIZER VARCHAR2(40)

即使是相同的数据库用户，若 session 的优化模式、session 级的参数 等不一样，执行计划也能不同。所以即使相同的sql，也可能具有不同的执行计划！

v$sql join to v$sql_plan 就代表了具体的sql的执行计划,通过下面3个字段做连接

ADDRESS RAW(4)
HASH_VALUE NUMBER
CHILD_NUMBER NUMBER


而v$SQLAREA 忽略了 执行计划 等差异，只是在形式上sql文本看起来一样！相当于做了个聚合，是多个不同执行计划的sql的聚合和累计信息

补充：

1、查一下这些视图的定义你就能理解，它们的源都是一个。
SELECT view_definition FROM v$fixed_view_definition WHERE view_name='GV$SQL';
SELECT view_definition FROM v$fixed_view_definition WHERE view_name='GV$SQL_AREA';

2、实际上最模糊的是v$sql与v$sqlarea,区别与联系除biti说的还有：

a、v$sql_area相当于是按INST_ID, KGLNAOBJ, KGLHDPAR, KGLNAHSH, KGLNATIM, GLOBTS0,GLOBT19, KGLOBTS1, KGLOBT20,DECODE(KGLOBT33, 1, 'Y', 'N'),KGLHDCLT这些列的自v$sql的group by，也就是说v$sql的每一行表示的是每一个sql语句的一个versiion，而v$sqlarea存放的是相同语句不同version一个汇总。

b、 v$sql与v$sqlarea的源都是一个：X$KGLCURSOR

c、实际调优中建议使用v$sql,相对来说比v$sqlarea快，而且还不会产生share pool latch的争用。

3、因v$sql及v$sqlarea存放着统计信息在调优时使用居多，但其sql是不全的，如果想获得完整的sql就要用v$sqltext了。

ASH size

Sizing ASH
ASH is actually a first-in, first-out (FIFO) buffer in memory that collects statistics on current session
activity. These statistics are gathered by extracting samples from V$SESSION every second.
Because this kind of frequent gathering could quickly overwhelm the system, ASH continually
ages out old statistics to make room for new ones.
ASH resides in the System Global Area (SGA) and its size is fixed for the lifetime of the
instance. Its size is calculated using the following calculation:
The lesser of:
 Total number of CPUs × 2MB of memory
 5 percent of the Shared Pool size
So, on a 16-processor system, with a Shared Pool size of 500MB, the size of the ASH buffer
could be calculated as follows:
 ASH desired size: 16 × 2MB = 32MB
 5 percent of Shared Pool size: 500MB × 5% = 25MB
 Final ASH size: 25MB
Using the Automatic Workload Repository (AWR) 265
Because the desired ASH size exceeded 5 percent of the Shared Pool size, Oracle will choose
the lesser size. There are, therefore, only two ways to increase the ASH buffer size:
 Increase the number of CPUs.
 Increase the Shared Pool size.

select * from v$sgastat where name like '%ASH%';

How To Use Automatic Shared Memory Management (ASMM) In Oracle10g

Pls refer to metalink doc. Note:295626.1

Keys things are:

1. To use the feature in 10g, we need to set sga_target value. The default value is 0 , which means disable ASMM.

2. Important dict tables

v$sga_dynamic_component

v$sga_dynamic_free_memory

v$sga_resize_ops