【kingsql分享】關於PGA的研究

1.查詢PGA快取命中率（本例為測試庫，100%）

SQL> select * from v$pgastat;

NAME                                                                  VALUE UNIT

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

aggregate PGA target parameter                                    314572800 bytes

aggregate PGA auto target                                         220723200 bytes

global memory bound                                                62914560 bytes

total PGA inuse                                                    69308416 bytes

total PGA allocated                                                96744448 bytes

maximum PGA allocated                                             118622208 bytes

total freeable PGA memory                                          16711680 bytes

process count                                                            40

max processes count                                                      51

PGA memory freed back to OS                                       454557696 bytes

total PGA used for auto workareas                                         0 bytes

NAME                                                                  VALUE UNIT

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

maximum PGA used for auto workareas                                 2512896 bytes

total PGA used for manual workareas                                       0 bytes

maximum PGA used for manual workareas                                     0 bytes

over allocation count                                                     0

bytes processed                                                   393541632 bytes

extra bytes read/written                                                  0 bytes

cache hit percentage                                                    100 percent

recompute count (total)                                                1549

19 rows selected.

2.使用V$SQL_WORKAREA_HISTOGRAM檢視來計算資料庫有多少工作是在最優形式下完成的。

如果一個工作區域最優地完成工作，也就是說完全在PGA記憶體中進行，那麼就會為OPTIMAL_COUNT列中的值加1。

如果經過了一次或多次傳遞，那麼就會在ONEPASS_COUNT或MULTIPASS_COUNT列的值上加1。

SQL> select optimal_count,round(optimal_count*100/total,2) optimal_perc,onepass_count,round(onepass_count*100/total,2) onepass_perc,multipass_count,round(multipass_count*100/total,2) multipass_perc from (select decode(sum(total_executions),0,1,sum(total_executions)) total,sum(OPTIMAL_EXECUTIONS) optimal_count,sum(ONEPASS_EXECUTIONS) onepass_count,sum(MULTIPASSES_EXECUTIONS) multipass_count from v$sql_workarea_histogram where low_optimal_size>(64*1024))

;

OPTIMAL_COUNT OPTIMAL_PERC ONEPASS_COUNT ONEPASS_PERC MULTIPASS_COUNT MULTIPASS_PERC

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

          496          100             0            0               0              0

//使用V$SQL_WORKAREA_HISTOGRAM檢視來計算資料庫有多少工作是在最優形式下完成的。

如果一個工作區域最優地完成工作，也就是說完全在PGA記憶體中進行，那麼就會為OPTIMAL_COUNT列中的值加1。

如果經過了一次或多次傳遞，那麼就會在ONEPASS_COUNT或MULTIPASS_COUNT列的值上加1。

//一次傳遞比沒有傳遞要慢，但需要很多次傳遞的運算，就一定是資料庫存在問題的訊號，尤其是如果其中包含大的工作區。

如果很有可能會發現，如果資料庫不得不進行（即使是很少量包含大工作區--例如大小介於256M到2G之間的）多次傳遞時，它也會變慢，並且很難高效執行。

3.要想確定資料庫中沒有任何大工作區執行在多次傳遞模式下，可以執行下面這個查詢

SQL> select low_optimal_size/1024 low,(high_optimal_size+1)/1024 high,optimal_executions,onepass_executions,multipasses_executions from v$sql_workarea_histogram where total_executions!=0;

       LOW       HIGH OPTIMAL_EXECUTIONS ONEPASS_EXECUTIONS MULTIPASSES_EXECUTIONS

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

         2          4              17649                  0                      0

        64        128                 56                  0                      0

       128        256                 18                  0                      0

       256        512                 12                  0                      0

       512       1024                466                  0                      0

      1024       2048                  4                  0                      0

      2048       4096                  6                  0                      0

7 rows selected.

4.還可以執行簡單查詢來檢視V$SYSSTAT和V$SESSTAT等檢視，找出到底有多少工作區由資料庫在最優記憶體大小下（在PGA中）、一次傳遞以及多次傳遞模式下執行：

select name profile,cnt,decode(total,0,0,round(cnt*100/total)) percentage from (select name,value cnt,(sum(value) over ()) total from v$sysstat where name like 'workarea exec%');

SQL> select name profile,cnt,decode(total,0,0,round(cnt*100/total)) percentage from (select name,value cnt,(sum(value) over ()) total from v$sysstat where name like 'workarea exec%');

PROFILE                                                                 CNT PERCENTAGE

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

workarea executions - optimal                                         18716        100

workarea executions - onepass                                             0          0

workarea executions - multipass                                           0          0

//這個查詢顯示出了自資料庫自動以來，在這三種不同的執行模式（最優、一次傳遞、多次傳遞）下所執行的總工作區數。

要想獲得一定時間段內的這些資訊，可以使用包含ASH（Automatic Session History)的查詢。

還可以檢視AWR中的內容，從而檢視所選定的時間段中資料庫的PGA記憶體使用情況。

如果定期建立這些報告並儲存下來，就可以獲得在一定時間內PGA記憶體的分配情況的歷史記錄。

還可以檢視ADDM報告，來衡量在一定的時間段內資料庫分別有多少比例的工作執行在前面所述三種模式下。