oracle 之 內存—鞭辟近里（三）
今天是2013-07-08，今天晚上突然接到一個電話，我的外甥問我的qq是多少，我感覺很吃驚，他長大了。在他現在這個年齡就開始接觸網絡，我難免有少許擔心，希望他合理利用網絡。
? 一）pga introduction：
pga是為服務器進程分配的一塊私有的內存，其中包含了相關數據信息和控制信息。每個進程只能對自己的pga區域進行讀和寫相應的執行代碼信息。在運行區域中一個簡單的內容就是游標，每個時間游標都會被執行，每個新的run-time area也是為游標相應的分配pga中的內存，以此來完成服務進程所要完成的執行操作。也就是說，run-time area 的創建和釋放相應的內存是根據cursor的開啟和關閉的。當在共享服務器模式下，一部分運行區域在sga中進行分配，如果large pool設置了那么就在large pool中分配，如果沒有那么就存在于share pool中。
對于復雜的查詢（如決策執行查詢），那么大部分的運行區是從事于工作區進行密集型操作的，像如下：
》基于排序分類，像order by ,group by ,rollup以及窗口函數等
》hash-join
》bitmap merge
》bitmap create
》使用批量的寫入buffer
sort area使用工作區的部分內存進行數據行的排序操作，同樣的，這個hash-join操作使用hash area工作區進行處理一個散列表。
這個工作區域的大小能夠被控制和進行相應的改變，通常，對一個消耗很多內存的特別的操作，一個大的工作區域能夠顯著提高這個處理性能。理想上，這個工作區域有一個合理的值，可以很好的處理sql語句所執行的數據操作。當工作區的值小于這個合理的值的時候，哪進行sql操作的時候勢必會增減相應的執行時間，因為需要額外的把數據暫時緩存到磁盤上，然后在進行緩慢的處理。例如我要進行一個1g的數據排序操作，那么就需要有1g的內存需求，才能一次處理迅速處理完成，但是我的sort area為10M，那么它就會經過多次的數據input進行處理。
自動pga內存管理能夠簡化和改善pga內存的分配難題，默認情況下自動pga內存管理啟動了，在這種情況下，oracle會自己動態的根據sga的20%調整一部分pga內存給work area，最小值為10M。
二）配置自動pga內存管理：
workarea_size_policy有兩個值一個是auto代表開啟pga自動管理，一個是manual代表進行手動管理pga。但是我在聯機手冊上看到這么一句話：
Note:
PGA_AGGREGATE_TARGET
initialization parameter to 0. When automatic PGA memory management is disabled, the maximum size of a work area can be sized with the associated
_AREA_SIZE
parameter, such as the
SORT_AREA_SIZE
initialization parameter.
當在自動pga管理方式下，設置一個pga_aggregate_target不為0的參數，然后oracle會根據這個參數的限制進行pga 內部的component的size turn，在這種情況下*_area_size參數就會被忽略失效（包括：
bitmap_merge_area_size???????
create_bitmap_area_size??????
hash_area_size???????????????
sort_area_size???????????????
workarea_size_policy
）。
當配置一個剛剛創建的實例的時候，很難確定pga_aggregate_target該參數在參數文件中應該設置多么大的一個值，可以采用如下方法：
對于 pga_aggregate_target 參數值大小確認 oracle 給出了以下步驟：
?
1） 在初始狀態下并不知道 oracle 該值應該設置為多大合理，那么可以首先設置一個 sga 的 20% ，對于該值可能太小也可能大。后續在處理。
?
2） 運行一些具有工作負荷的語句然后觀察性能變化，然后使用 pga 統計這些數據，查看最大的使用 pga 是否在設置的值之下或是之上，然后進行相應的調整。
?
3）??????? 參考 使用 oracle pga 診斷統計器的值，進行修改 pga_aggregate_target 參數大小。
必須根據具體的實際oracle說占有的內存，進行調整pga和sga的大小：
對于OLTP系統的話，劃分一個很小的總占用內存的值，如20%，然后剩余80%給sga
對于dss系統，需要劃分一個大點的值，以滿足復雜請求需要，可以劃分0%（僅此參考，具體情況還需診斷在確認）。
oracle這么也給出了一個很好的參考計算方法：
For OLTP: PGA_AGGREGATE_TARGET = (total_mem * 80%) * 20%
For DSS: PGA_AGGREGATE_TARGET = (total_mem * 80%) * 50%
這些視圖能夠動態的查看pga內存管理的一些詳細信息，便于研究性能瓶頸和調整pga_aggregate_target參數：
1、v$pgastat： 該視圖是在數據庫的實例層，統計的自動內存分配情況：
eg:
SYS@orcl#select * from v$pgastat
? 2? ;
------------------------------------------------------------ ---------- ------------------------
aggregate PGA target parameter??????????????????????????????? 268435456 bytes
aggregate PGA auto target???????????????????????????????????? 167362560 bytes
global memory bound??????????????????????????????????????????? 53686272 bytes
total PGA inuse??????????????????????????????????????????????? 82583552 bytes
total PGA allocated?????????????????????????????????????????? 161752064 bytes
maximum PGA allocated???????????????????????????????????????? 238797824 bytes
total freeable PGA memory?????????????????????????????????????? 7536640 bytes
process count??????????????????????????????????????????????????????? 36
max processes count????????????????????????????????????????????????? 44
PGA memory freed back to OS????????????????????????????????? 2492006400 bytes
total PGA used for auto workareas???????????????????????????????????? 0 bytes
------------------------------------------------------------ ---------- ------------------------
maximum PGA used for auto workareas???????????????????????????? 5968896 bytes
total PGA used for manual workareas?????????????????????????????????? 0 bytes
maximum PGA used for manual workareas??????????????????????????? 268288 bytes
over allocation count???????????????????????????????????????????????? 0
bytes processed????????????????????????????????????????????? 2484548608 bytes
extra bytes read/written????????????????????????????????????????????? 0 bytes
cache hit percentage??????????????????????????????????????????????? 100 percent
recompute count (total)?????????????????????????????????????????? 11557
Aggregate pgatarget parameter : 該值為在初始化參數中 pga_aggregate_target 參數的大小，默認為 sga 的 20% ，如果參數值（ pga_aggregate_target ）為 0 ，那么自動管理 pga 就被關閉了。
SYS@orcl#r
1* select name,value from v$spparameter wherename like 'pga%'
?
NAME VALUE
------------------------------------------------------------
pga_aggregate_target 268435456
可以看到該值就是 spfile 中設置的值，為 256M
Aggregate pga auto target: 在自動模塊下能夠給工作去分配的總的 pga 大小，這個值動態的來源于 pga_aggregate_target 參數的值和工作區域的工作量占用的大小。然而，這個值被 oracle 不斷的進行調整，如果這個值比 pga_aggregate_target 值小，那么就有可能存在很多的 pga 內存被其他組件占用如 pl/sql 或是 java 內存，給工作區域留下了很小的一部分內存空間，在自動模塊一應該保證有足夠的 work area 進行運行相應內容。
Global memory? bound ：
在自動模塊下可用的工作區的最大大小。這個值經常被
oracle
進行調整，以此來反應當前工作區域執行任務的情況。當活動的工作區在系統中增加的時候，這個全局內存區域通常就會減小。一般來說這個全局值不應該小于
1M
，如果小于
1M
，那么就應該增加
pga_aggregate_target
參數的值了。
Total pga? iuse
：
該參數表示當前分配的pga中有多少內存給了work area ，一般可用參考該值進行反映出其他消耗內存的組件消耗了多少內存。
Total pga allocated ：
這個值反應了當前被實例所分配的所有pga的大小
，
oracle
會試圖保持該值在
pga_aggregate_target
參數值之下，當工作區域任務增加的非常快的時候或是這個初始化參數
pga_aggregate_target
設置的比較小，那么該值有可能在短暫超過
pga
——
aggregate_target
參數指定的值的大小。
maximum PGA allocated：
從實例開始在一個時間分配的pga的最大大小是多少。
Total freeablepga memory
：表示所有
process
占用的多少
pga
內存可以釋放給
os
。
process count：
表示在最后三秒內所有激活的進程數目是多少。
max process count：
從實例啟動開始算起，最大的進程數目是多少
PGA memory freed back to OS：
表示從實例啟動開始一共返回給系統多少的內大小。?
Total pga usedfor auto workareas ：
表示在自動內存管理模式下，當前有多少 pga 內存被工作區域所占用，這個值可以決定有多少 pga 內存被其他的組件進行消耗。
Pga other=total pga allocated-total pgaused for auto workareas
Over allocationcount:
該值就是一個計數，從實例啟動開始算起， 當 PGA_AGGREGATE_TARGET 設置非常小或工作區負載增長很快時，會超額分配 PGA 內存（分配的值大于 PGA_AGGREGATE_TARGET ）。這種情況發生時， Oracle 不能限制 PGA 內存小于 PGA_AGGREGATE_TARGET ，只能分配實際需要的 PGA 內存。此時，建議通過建議器視圖 V$PGA_TARGET_ADVICE 來增加 PGA_AGGREGATE_TARGET 的大小。
Total bytes processed ： 從實例啟動開始被進程處理的字節數。
Extra bytes read/written: 當一個工作區域達不到一個最優值的時候，自從實例啟動后，需要額外輸入數據所處理的字節數。當工作區無法在最佳狀態下運行時，就需要進行這個額外處理。
cache hit percentage ：
Oracle 計算出來的一個與 PGA 內存組件性能相關的數據，是自從實例啟動后累加的。如果這個值是 100% ，則表示實例啟動后，所有系統使用到的工作區都分配了最佳的 PGA 內存。
當工作區無法在最佳狀態下運行，就需要進行額外的數據輸入處理，這將會降低
cache hit percentage
。
2、v$process:
該視圖中可以反映出進程使用的pga的大小信息：
PGA_USED_MEM：該進程所使用的pga內存大小
PGA_ALLOC_MEM：分配給該進程的pga大小：
PGA_FREEABLE_MEM：該進程能夠釋放的內存大小
PGA_MAX_MEM：該進程所能夠分配的最大內存大小：
eg：
SQL> select program,pga_used_mem,pga_alloc_mem,pga_freeable_mem,pga_max_mem from v$process
? 2? ;
PROGRAM????????????????????????????????????????? PGA_USED_MEM PGA_ALLOC_MEM PGA_FREEABLE_MEM PGA_MAX_MEM
------------------------------------------------ ------------ ------------- ---------------- -----------
PSEUDO????????????????????????????????????????????????????? 0???????????? 0??????????????? 0?????????? 0
oracle@oracle-R2 (PMON)??????????????????????????????? 580052??????? 669072??????????????? 0????? 669072
oracle@oracle-R2 (PSP0)??????????????????????????????? 562660??????? 652672??????????????? 0????? 652672
oracle@oracle-R2 (VKTM)??????????????????????????????? 559584??????? 652672??????????????? 0????? 652672
oracle@oracle-R2 (GEN0)??????????????????????????????? 566528??????? 652672??????????????? 0????? 652672
oracle@oracle-R2 (DIAG)??????????????????????????????? 559612??????? 652672??????????????? 0????? 652672
oracle@oracle-R2 (DBRM)??????????????????????????????? 638676?????? 1045888?????????? 131072???? 1045888
oracle@oracle-R2 (DIA0)?????????????????????????????? 1542316?????? 1749756??????????????? 0???? 1749756
oracle@oracle-R2 (MMAN)??????????????????????????????? 562660??????? 652672??????????????? 0????? 652672
oracle@oracle-R2 (DBW0)?????????????????????????????? 5416072?????? 5804960??????????????? 0??? 10916768
oracle@oracle-R2 (LGWR)????????????????????????????? 11167108????? 11990400?????????? 131072??? 11990400
PROGRAM????????????????????????????????????????? PGA_USED_MEM PGA_ALLOC_MEM PGA_FREEABLE_MEM PGA_MAX_MEM
------------------------------------------------ ------------ ------------- ---------------- -----------
oracle@oracle-R2 (CKPT)??????????????????????????????? 609912??????? 875188??????????????? 0????? 875188
oracle@oracle-R2 (SMON)??????????????????????????????? 823148?????? 4715904????????? 3801088???? 4715904
oracle@oracle-R2 (RECO)??????????????????????????????? 703308?????? 1308032?????????? 393216???? 1308032
oracle@oracle-R2 (MMON)?????????????????????????????? 1613944?????? 3060476????????? 1179648???? 3977980
oracle@oracle-R2 (MMNL)??????????????????????????????? 648448??????? 849280??????????????? 0????? 849280
oracle@oracle-R2 (D000)??????????????????????????????? 807768?????? 1111424??????????????? 0???? 1111424
oracle@oracle-R2 (S000)??????????????????????????????? 357392??????? 587136??????????????? 0????? 587136
oracle@oracle-R2 (SMCO)??????????????????????????????? 559588??????? 652672??????????????? 0????? 652672
oracle@oracle-R2 (ARC0)????????????????????????????? 24261816????? 26801536????????? 1114112??? 26801536
oracle@oracle-R2 (ARC1)????????????????????????????? 24261816????? 26801536????????? 1114112??? 26801536
oracle@oracle-R2 (ARC2)????????????????????????????? 17452884????? 18675072??????????????? 0??? 18675072
PROGRAM????????????????????????????????????????? PGA_USED_MEM PGA_ALLOC_MEM PGA_FREEABLE_MEM PGA_MAX_MEM
------------------------------------------------ ------------ ------------- ---------------- -----------
oracle@oracle-R2 (ARC3)????????????????????????????? 24261816????? 26801536????????? 1114112??? 26801536
oracle@oracle-R2 (QMNC)??????????????????????????????? 563796??????? 718208??????????????? 0????? 718208
oracle@oracle-R2 (CJQ0)?????????????????????????????? 1051512?????? 9237888????????? 7864320???? 9827712
oracle@oracle-R2 (Q000)?????????????????????????????? 3678780?????? 4895484?????????? 589824???? 4895484
oracle@oracle-R2 (Q001)??????????????????????????????? 849964??????? 914816??????????????? 0???? 1635712
oracle@oracle-R2 (J000)??????????????????????????????? 585720?????? 1504640??????????????? 0???? 1504640
oracle@oracle-R2 (J001)??????????????????????????????? 559652??????? 652672??????????????? 0????? 652672
oracle@oracle-R2 (W000)??????????????????????????????? 662472??????? 783744??????????????? 0????? 783744
oracle@oracle-R2 (TNS V1-V3)?????????????????????????? 852756?????? 2168292?????????? 983040???? 2758116
31 rows selected.
SQL>
3、v$process_memory:
該視圖顯示了，對于oracle進程中哪些類型的組件占用了多少的pga內存情況信息。
包含六個字段：
pid：oracle內部進程號：
serial#：進行序列號
category：在使用進程占用內存的組件是什么，如sql，pl/sql,other，freeable（代表能夠釋放給系統多少size）：
allocated：分配的pga內存大小
userd：使用了多大
max_allocated:最大能夠分配給該components多么大的pga內存。
eg：
SQL> select * from v$process_memory where rownum<10;
?????? PID??? SERIAL# CATEGORY???????? ALLOCATED?????? USED MAX_ALLOCATED
---------- ---------- --------------- ---------- ---------- -------------
???????? 2????????? 1 PL/SQL??????????????? 2024??????? 136????????? 2024
???????? 2????????? 1 Other?????????????? 667048?????????????????? 667048
???????? 3????????? 1 PL/SQL??????????????? 2024??????? 136????????? 2024
???????? 3????????? 1 Other?????????????? 650648?????????????????? 650648
???????? 4????????? 1 PL/SQL??????????????? 2024??????? 136????????? 2024
???????? 4????????? 1 Other?????????????? 650648?????????????????? 650648
???????? 5????????? 1 PL/SQL??????????????? 2024??????? 136????????? 2024
???????? 5????????? 1 Other?????????????? 650648?????????????????? 650648
???????? 6????????? 1 PL/SQL??????????????? 2024??????? 136????????? 2024
9 rows selected.
SQL>
另外可以使用該視圖與v$process查看那個進程中存在哪些category占用內存情況：
eg：
SQL> select m.pid,p.pname,m.serial#,m.category,m.allocated,m.used,m.max_allocated
? 2? from v$process p,v$process_memory m where p.pid=m.pid and rownum<16;
?????? PID PNAME??? SERIAL# CATEGORY???????? ALLOCATED?????? USED MAX_ALLOCATED
---------- ----- ---------- --------------- ---------- ---------- -------------
???????? 2 PMON?????????? 1 PL/SQL??????????????? 2024??????? 136????????? 2024
???????? 2 PMON?????????? 1 Other?????????????? 667048?????????????????? 667048
???????? 3 PSP0?????????? 1 PL/SQL??????????????? 2024??????? 136????????? 2024
???????? 3 PSP0?????????? 1 Other?????????????? 650648?????????????????? 650648
???????? 4 VKTM?????????? 1 PL/SQL??????????????? 2024??????? 136????????? 2024
???????? 4 VKTM?????????? 1 Other?????????????? 650648?????????????????? 650648
???????? 5 GEN0?????????? 1 PL/SQL??????????????? 2024??????? 136????????? 2024
???????? 5 GEN0?????????? 1 Other?????????????? 650648?????????????????? 650648
???????? 6 DIAG?????????? 1 PL/SQL??????????????? 2024??????? 136????????? 2024
???????? 6 DIAG?????????? 1 Other?????????????? 650648?????????????????? 650648
???????? 7 DBRM?????????? 1 SQL????????????????????? 0????????? 0??????? 250144
?????? PID PNAME??? SERIAL# CATEGORY???????? ALLOCATED?????? USED MAX_ALLOCATED
---------- ----- ---------- --------------- ---------- ---------- -------------
???????? 7 DBRM?????????? 1 PL/SQL??????????????? 2024??????? 136????????? 2024
???????? 7 DBRM?????????? 1 Freeable??????????? 131072????????? 0
???????? 7 DBRM?????????? 1 Other?????????????? 912792?????????????????? 912792
???????? 8 DIA0?????????? 1 PL/SQL??????????????? 2024??????? 136????????? 2024
15 rows selected.
SQL>
4、4、v$sql_workarea_histogram;
該視圖展示了對不同的工作區域大小然后進行工作區域執行累計的統計信息。這個工作區域更具最優內存需求和增長的需要被換分了33個組，
對于每個工作區域，該視圖展示了有多少工作區域在哪個范圍能夠運行在最優化模式，有多少運行在一個one-pass模式，和有多少能夠運行在
multi-pass模式。
eg：
SQL> SELECT LOW_OPTIMAL_SIZE/1024 low_kb,
? 2???????? (HIGH_OPTIMAL_SIZE+1)/1024 high_kb,
? 3???????? OPTIMAL_EXECUTIONS, ONEPASS_EXECUTIONS, MULTIPASSES_EXECUTIONS
? 4??? FROM V$SQL_WORKAREA_HISTOGRAM
? 5?? WHERE TOTAL_EXECUTIONS != 0;
\
??? LOW_KB??? HIGH_KB OPTIMAL_EXECUTIONS ONEPASS_EXECUTIONS
---------- ---------- ------------------ ------------------
MULTIPASSES_EXECUTIONS
----------------------
???????? 2????????? 4?????????? 30492758????????????????? 0
???????????????????? 0
??????? 64??????? 128???????????? 128341????????????????? 0
???????????????????? 0
?????? 128??????? 256?????????????? 7253????????????????? 0
???????????????????? 0
??? LOW_KB??? HIGH_KB OPTIMAL_EXECUTIONS ONEPASS_EXECUTIONS
---------- ---------- ------------------ ------------------
MULTIPASSES_EXECUTIONS
----------------------
?????? 256??????? 512???????????? 165998????????????????? 0
???????????????????? 0
?????? 512?????? 1024???????????? 115171????????????????? 0
???????????????????? 0
????? 1024?????? 2048?????????????? 4962????????????????? 0
???????????????????? 0
??? LOW_KB??? HIGH_KB OPTIMAL_EXECUTIONS ONEPASS_EXECUTIONS
---------- ---------- ------------------ ------------------
MULTIPASSES_EXECUTIONS
----------------------
????? 2048?????? 4096????????????? 15169????????????????? 0
???????????????????? 0
????? 4096?????? 8192?????????????? 3189????????????????? 6
???????????????????? 0
????? 8192????? 16384??????????????? 261????????????????? 6
???????????????????? 0
??? LOW_KB??? HIGH_KB OPTIMAL_EXECUTIONS ONEPASS_EXECUTIONS
---------- ---------- ------------------ ------------------
MULTIPASSES_EXECUTIONS
----------------------
???? 16384????? 32768??????????????? 247????????????????? 0
???????????????????? 0
???? 32768????? 65536????????????????? 6????????????????? 0
???????????????????? 0
???? 65536???? 131072????????????????? 3????????????????? 2
???????????????????? 0
??? LOW_KB??? HIGH_KB OPTIMAL_EXECUTIONS ONEPASS_EXECUTIONS
---------- ---------- ------------------ ------------------
MULTIPASSES_EXECUTIONS
----------------------
??? 131072???? 262144????????????????? 4????????????????? 8
???????????????????? 0
13 rows selected.
可以從這次查詢的內容中看出如下信息：
當在4M的時候，有3189個area size可以運行在最優模式，有6個需要進行一次 one-pass，沒有mutil-pass。同樣反映出了，如果設置在4M之下，那么就能保證都運行在
最優模式下。（因此可以判斷area size的大小可以設置為3M，還算可以的）。
v$sql_workarea_active試圖包含了被系統分配的當前數案件的工作區域的相關信息，可以通過workarea_address與v$sql_workarea視圖進行連接，來查看哪個工作區域的信息。如果在一個work area被分到了磁盤上，然后這個視圖包含一些臨時段為協助完成work area解決空間不足處理sql的需求。通過這個視圖我們可以獲得如下問題的詳細信息：
1）當前被系統分配的前10最大的工作區域是哪些
2）What percentage of memory is over-allocated (
EXPECTED_SIZE
<
ACTUAL_MEM_USED
) and under-allocated (
EXPECTED_SIZE
>
ACTUAL_MEM_USED
)?
3）哪些活動的區域使用更多的內存。
4）哪些工作區域已經被分裂到磁盤上了。
eg：
session1：
SYS@orcl#set autotrace trace statistics
SYS@orcl#select * from dba_objects;
統計信息
----------------------------------------------------------
0? recursive calls
????????? 0? db block gets
?????? 7862? consistent gets
??????? 884? physical reads
????????? 0? redo size
??? 3803931? bytes sent via SQL*Net to client
????? 50915? bytes received via SQL*Net from client
?????? 4583? SQL*Net roundtrips to/from client
????????? 0? sorts (memory)
????????? 0? sorts (disk)
????? 68722? rows processed
session2:
select * from v$sql_workarea_active;
SQL> r
SQL_HASH_VALUE SQL_ID??????? SQL_EXEC_START SQL_EXEC_ID WORKAREA_ADDRESS OPERATION_TYPE?????????????????????????????????????????????????????????????????? OPERATION_ID POLICY????????????????????????? SID? QCINST_ID????? QCSID ACTIVE_TIME WORK_AREA_SIZE EXPECTED_SIZE ACTUAL_MEM_USED MAX_MEM_USED NUMBER_PASSES TEMPSEG_SIZE TABLESPACE?????????????????????? SEGRFNO#??? SEGBLK#
-------------- ------------- -------------- ----------- ---------------- -------------------------------------------------------------------------------- ------------ ------------------------ ---------- ---------- ---------- ----------- -------------- ------------- --------------- ------------ ------------- ------------ ------------------------------ ---------- ----------
1578617346 6avfua5g1gkh2 2013/7/11 22:3??? 16777219 0000000071DD1F48 HASH-JOIN????????????????????????????????????????????????????????????????????????????????? 10 AUTO??????????????????????????? 155?????????????????????????? 2952316??????? 3085312?????? 3084288???????? 1249280????? 1249280???????????? 0???????????????????????????????????????????????????????
??? 1578617346 6avfua5g1gkh2 2013/7/11 22:3??? 16777219 0000000071DD1FB0 HASH-JOIN?????????????????????????????????????????????????????????????????????????????????? 8 AUTO??????????????????????????? 155?????????????????????????? 2954647??????? 2848768?????? 2847744???????? 1271808????? 1271808???????????? 0???????????????????????????????????????????????????????
SQL>
哈哈，分析一下：
1）sql_hash_value:當前執行的sql語句的hash值：該值為：1578617346
| Id? | Operation????????????????????? | Name??????? | Rows? | Bytes | Cost (%CPU)| Time???? |
----------------------------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT?????????????? |???????????? | 65381 |??? 12M|?? 247?? (3)| 00:00:03 |
|?? 1 |? VIEW????????????????????????? | DBA_OBJECTS | 65381 |??? 12M|?? 247?? (3)| 00:00:03 |
|?? 2 |?? UNION-ALL??????????????????? |???????????? |?????? |?????? |??????????? |????????? |
|*? 3 |??? TABLE ACCESS BY INDEX ROWID | SUM$??????? |???? 1 |??? 11 |???? 1?? (0)| 00:00:01 |
|*? 4 |???? INDEX UNIQUE SCAN????????? | I_SUM$_1??? |???? 1 |?????? |???? 0?? (0)| 00:00:01 |
|?? 5 |??? TABLE ACCESS BY INDEX ROWID | OBJ$??????? |???? 1 |??? 30 |???? 3?? (0)| 00:00:01 |
|*? 6 |???? INDEX RANGE SCAN?????????? | I_OBJ1????? |???? 1 |?????? |???? 2?? (0)| 00:00:01 |
|*? 7 |??? FILTER????????????????????? |???????????? |?????? |?????? |??????????? |????????? |
|*? 8 |?? ? HASH JOIN?? ?????????????? |???????????? | 69509 |? 8281K|?? 244?? (3)| 00:00:03 |
|?? 9 |????? TABLE ACCESS FULL???????? | USER$ ?????? |??? 87 |? 1479 |???? 3?? (0)| 00:00:01 |
|* 10 |????? HASH JOIN? ?????????????? |???????????? | 69509 |? 7127K|?? 240?? (2)| 00:00:03 |
|? 11 |?????? INDEX FULL SCAN????????? | I_USER2???? |??? 87 |? 2001 |???? 1?? (0)| 00:00:01 |
|* 12 |?????? TABLE ACCESS FULL??????? | OBJ$??????? | 69509 |? 5566K|?? 239?? (2)| 00:00:03 |
|* 13 |???? TABLE ACCESS BY INDEX ROWID| IND$??????? |???? 1 |???? 8 |???? 2?? (0)| 00:00:01 |
|* 14 |????? INDEX UNIQUE SCAN???????? | I_IND1????? |???? 1 |?????? |???? 1?? (0)| 00:00:01 |
|? 15 |???? NESTED LOOPS?????????????? |???????????? |???? 1 |??? 28 |???? 2?? (0)| 00:00:01 |
|* 16 |????? INDEX FULL SCAN?????????? | I_USER2???? |???? 1 |??? 20 |???? 1?? (0)| 00:00:01 |
|* 17 |????? INDEX RANGE SCAN????????? | I_OBJ4????? |???? 1 |???? 8 |???? 1?? (0)| 00:00:01 |
|? 18 |??? NESTED LOOPS??????????????? |???????????? |???? 1 |?? 105 |???? 3?? (0)| 00:00:01 |
|? 19 |???? TABLE ACCESS FULL????????? | LINK$?????? |???? 1 |??? 88 |???? 2?? (0)| 00:00:01 |
|? 20 |???? TABLE ACCESS CLUSTER?????? | USER$?????? |???? 1 |??? 17 |???? 1?? (0)| 00:00:01 |
|* 21 |????? INDEX UNIQUE SCAN???????? | I_USER#???? |???? 1 |?????? |???? 0?? (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
DBMS_METADATA.GET_DDL('VIEW','
--------------------------------------------------------------------------------
CREATE OR REPLACE FORCE VIEW "SYS"."DBA_OBJECTS" ("OWNER", "OBJECT_NAME", "SUB
select u.name, o.name, o.subname, o.obj#, o.dataobj#,
?????? decode(o.type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
????????????????????? 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
????????????????????? 7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
????????????????????? 11, 'PACKAGE BODY', 12, 'TRIGGER',
????????????????????? 13, 'TYPE', 14, 'TYPE BODY',
????????????????????? 19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB',
????????????????????? 22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
????????????????????? 28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',
????????????????????? 32, 'INDEXTYPE', 33, 'OPERATOR',
????????????????????? 34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
????????????????????? 40, 'LOB PARTITION', 41, 'LOB SUBPARTITION',
????????????????????? 42, NVL(( SELECT 'REWRITE EQUIVALENCE'
FROM sum$ s
? WHERE s.obj#=o.obj#
???????????????????????????????????? and bitand(s.xpflags, 8388608) = 8388608),
????????????????????????????? 'MATERIALIZED VIEW'),
????????????????????? 43, 'DIMENSION',
????????????????????? 44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN',
????????????????????? 48, 'CONSUMER GROUP',
????????????????????? 51, 'SUBSCRIPTION', 52, 'LOCATION',
????????????????????? 55, 'XML SCHEMA', 56, 'JAVA DATA',
????????????????????? 57, 'EDITION', 59, 'RULE',
????????????????????? 60, 'CAPTURE', 61, 'APPLY',
????????????????????? 62, 'EVALUATION CONTEXT',
????????????????????? 66, 'JOB', 67, 'PROGRAM', 68, 'JOB CLASS', 69, 'WINDOW',
????????????????????? 72, 'WINDOW GROUP', 74, 'SCHEDULE', 79, 'CHAIN',
????????????????????? 81, 'FILE GROUP', 82, 'MINING MODEL', 87, 'ASSEMBLY',
????????????????????? 90, 'CREDENTIAL', 92, 'CUBE DIMENSION', 93, 'CUBE',
????????????????????? 94, 'MEASURE FOLDER', 95, 'CUBE BUILD PROCESS',
???????????????????? 'UNDEFINED'),
?????? o.ctime, o.mtime,
?????? to_char(o.stime, 'YYYY-MM-DD:HH24:MI:SS'),
?????? decode(o.status, 0, 'N/A', 1, 'VALID', 'INVALID'),
?????? decode(bitand(o.flags, 2), 0, 'N', 2, 'Y', 'N'),
?????? decode(bitand(o.flags, 4), 0, 'N', 4, 'Y', 'N'),
?????? decode(bitand(o.flags, 16), 0, 'N', 16, 'Y', 'N'),
?????? o.namespace,
?????? o.defining_edition
from sys."_CURRENT_EDITION_OBJ" o, sys.user$ u
where o.owner# = u.user#
? and o.linkname is null
? and (o.type# not in (1? /* INDEX - handled below */,
????????????????????? 10 /* NON-EXISTENT */)
?????? or
?????? (o.type# = 1 and 1 = (select 1
????????????????????????????? from sys.ind$ i
???????????????????????????? where i.obj# = o.obj#
?????????????????????????????? and i.type# in (1, 2, 3, 4, 6, 7, 9))))
? and o.name != '_NEXT_OBJECT'
? and o.name != '_default_auditing_options_'
? and bitand(o.flags, 128) = 0
union all
select u .name, l.name, NULL, to_number(null), to_number(null),
?????? 'DATABASE LINK',
?????? l.ctime, to_date(null), NULL, 'VALID','N','N', 'N', NULL, NULL
from sys.link$ l, sys.user$ u
w here l.owner# = u.user#
7） OPERATION_ID：該參數是在執行計劃中定義操作的一個唯一的數字號，可以通過與v$sql_plan視圖進行連接查看使用工作區內該語句的plan的信息;
8）POLICY： 該工作區的是auto還是manual
9）sid：執行該會話的id：該會話為155
10）QCINST_ID 查詢協調員實例標識符。隨著QCSID,使您能夠唯一地標識查詢協調員。
11) QCSID: 查詢協調員的會話標示，如果工作區通過連續的游標進行分配的，那么這個號是一個。
12)ACTIVE_TIME 這個工作區被激活的平均時間
13）WORK_AREA_SIZE：該操作占用的最大工作區的大小（kb)
14)EXPECTED_SIZE；一個估計的工作區大小
15）ACTUAL_MEM_USED:當前pga給area 分配的大小。
16) tempseg_size:寫道磁盤上的段的數據
17）tablespace：為work area分配段在哪個表空間
18）segrfno#，文件號
19）segblk#，塊號
eg：
SQL>
SQL> SELECT to_number(decode(SID, 65535, NULL, SID)) sid,
? 2???????? operation_type OPERATION,
? 3???????? trunc(EXPECTED_SIZE/1024) ESIZE,
? 4???????? trunc(ACTUAL_MEM_USED/1024) MEM,
? 5???????? trunc(MAX_MEM_USED/1024) "MAX MEM",
? 6???????? NUMBER_PASSES PASS,
? 7???????? trunc(TEMPSEG_SIZE/1024) TSIZE
? 8??? FROM V$SQL_WORKAREA_ACTIVE
? 9?? ORDER BY 1,2;
---------- ---------------------------------------- ---------- ----------
MAX MEM?????? PASS????? TSIZE
---------- ---------- ----------
2125 HASH-JOIN????????????????????????????????????? 1733?????? 1040
????? 1040????????? 0
當前執行的sql語句使用過的會話id為2125，操作類型為hash-join，這個工作區域估計占用大小為1733bytes，實際為1040，過去占用的pga內存為1040，無one-pass記錄，沒有產生額外的磁盤分配。
6、v$sql_workarea
另外我們可以通過workarea_address與v$sql_workarea查看哪些分配了最大內存消耗的操作：
eg：
SQL> SELECT *
? 2? FROM?? (SELECT workarea_address, operation_type, policy, estimated_optimal_size
? 3????????? FROM V$SQL_WORKAREA
? 4????????? ORDER BY estimated_optimal_size DESC)
? 5?? WHERE ROWNUM <= 10;
---------------- ---------------------------------------- --------------------
ESTIMATED_OPTIMAL_SIZE
----------------------
C0000004E2AA36F8 GROUP BY (HASH)????????????????????????? AUTO
????????????? 37981184
????????????? 31110144
????????????? 31110144
WORKAREA_ADDRESS OPERATION_TYPE?????????????????????????? POLICY
---------------- ---------------------------------------- --------------------
ESTIMATED_OPTIMAL_SIZE
----------------------
C0000004E2AA3690 HASH-JOIN??????????????????????????????? AUTO
????????????? 28812288
????????????? 20407296
????????????? 20373504
WORKAREA_ADDRESS OPERATION_TYPE?????????????????????????? POLICY
---------------- ---------------------------------------- --------------------
ESTIMATED_OPTIMAL_SIZE
----------------------
C0000004DE610500 HASH-JOIN??????????????????????????????? AUTO
????????????? 20373504
????????????? 12705792
?????????????? 9923584
WORKAREA_ADDRESS OPERATION_TYPE?????????????????????????? POLICY
---------------- ---------------------------------------- --------------------
ESTIMATED_OPTIMAL_SIZE
----------------------
C0000004E2CE30D8 HASH-JOIN??????????????????????????????? AUTO
?????????????? 9205760
10 rows selected.
可以看到，第一個是work area的地址為C0000004E2AA36F8，進行了group by的操作，使用的是自動work area 分配方式，該操作，估計需要消耗工作區域大小為37981184byte。
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++↖(^ω^)↗+++++++
?
?
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
