近期整理(二)--pga,sga設定問題的兩個診斷案例

案例1：

關鍵詞：direct path write temp

這是開發人員反饋的問題：
有個程式查詢差不多需要返回20多萬個物件，在DEV和SIT都沒問題，但在pre-production就跑不出來
開始以為這個庫沒跑dbms_stats，結果執行了dbms_stats之後仍然老樣子。
並且在執行這個查詢的時候app server和db server負載都不高...怪了。
抓了一下當時的awr report
但是系統負載並不高：

Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:              4,535.44              2,351.49
              Logical reads:                125.43                 65.03
              Block changes:                 16.73                  8.67
             Physical reads:                 24.76                 12.84
            Physical writes:                  8.57                  4.44
                 User calls:                 28.02                 14.53
                     Parses:                  7.09                  3.68
                Hard parses:                  0.01                  0.01
                      Sorts:                  1.03                  0.53
                     Logons:                  0.05                  0.02

Top 等待事件倒是挺特殊：direct path write temp
Top 5 Timed Events                                         Avg %Total
~~~~~~~~~~~~~~~~~~                                        wait   Call
Event                                 Waits    Time (s)   (ms)   Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
direct path write temp                4,346          59     14   62.0   User I/O
CPU time                                             25          26.5
db file parallel write                  187          15     82   16.0 System I/O
log file sync                         1,887          15      8   16.0     Commit
log file parallel write               2,006          15      8   15.8 System I/O


官方說明：direct path write and direct path write temp
When a process is writing buffers directly from PGA (as opposed to the DBWR writing them from the buffer cache), the process waits on this event for the write call to complete. Operations that could perform. direct path writes include when a sort goes to disk, during parallel DML operations, direct-path INSERTs, parallel create table as select, and some LOB operations.
Like direct path reads, the number of waits is not the same as number of write calls issued if the I/O subsystem supports asynchronous writes. The session waits if it has processed all buffers in the PGA and is unable to continue work until an I/O request completes.

Causes
This happens in the following situations:
1) Sorts are too large to fit in memory and are written to disk
2) Parallel DML are issued to create/populate objects
3) Direct path loads
1不符合，因為awr report中：In-memory Sort %: 100.00
2不符合，沒有開parallel,執行的查詢也是程式來完成的
3不符合，執行的操作是個查詢

到這裡陷入迷茫，繼續往下看report:
Elapsed      CPU                  Elap per % Total
Time (s)   Time (s) Executions   Exec (s) DB Time    SQL Id
---------- ---------- ------------ ---------- ------- -------------
        68          6            1       67.6    70.7 f2zymmdhuzvxj
Module: JDBC Thin Client
SELECT 'wt.part.WTPart',A0.blob$entrySetadHocAcl,A0.checkoutInfoIsNull,A0.classn
amekeyA2checkoutInfo,A0.idA3A2checkoutInfo,A0.statecheckoutInfo,A0.classnamekeyc
ontainerReferen,A0.idA3containerReference,A0.contractNumber,A0.classnamekeydomai
nRef,A0.idA3domainRef,A0.entrySetadHocAcl,A0.eventSet,A0.classnamekeyA2foldering

這條語句引起了懷疑，問了開發人員，應用確實是這個查詢。
順手抓了它的執行計劃：

SQL> set linesize 200
SQL> set pagesize 9999
SQL> SELECT * FROM TABLE(dbms_xplan.display_awr('f2zymmdhuzvxj'));
。。。。省略了sql
Plan hash value: 1054932678
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation                     | Name                      | Rows | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                              |              |            |               | 27177 (100)|          |
|   1 | HASH JOIN                   |                              |   574K   |   329M|  115M     | 27177   (1)  | 00:06:21 |
|   2 |   TABLE ACCESS FULL  | WTPARTMASTER     |   686K | 107M    |              | 2935   (1)     | 00:00:42 |
|   3 |   TABLE ACCESS FULL  | WTPART                  |   574K |   239M |              | 8804   (1)      | 00:02:04 |
---------------------------------------------------------------------------------------------------------------------------

到這裡就知道direct path write temp的原因了：
查詢結果太大，使用了hash join,結果沒有在pga裡放下（hash join是在pga中完成的），於是使用了tempspace.從pga直接讀寫tempspace，自然就會有direct path write temp等待。

根本原因：
這個庫是開發人員安裝程式時應用自動建立的，pga和sga等值都太小了,調整之後就沒有任何問題了

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/10701850/viewspace-526732/，如需轉載，請註明出處，否則將追究法律責任。