꿈꾸는 개발자, DBA 커뮤니티 구루비

MOATS

Download :http://www.oracle-developer.net/content/utilities/moats_v1.04.zip
MOATS(Mother Of All Tunig scripts)
- 성능 튜닝 작업을 하기 전에 어떤 부분에 문제가 있는지를 직관적으로 파악하기 위한 모니터링 기능을 제공
- 고가의 모니터링 툴을 구입할 여유가 없는 경우에 비용 대비 최고의 효과



SQL> SELECT * FROM TABLE (moats . top(5));


MOATS: The Mother Of All Tuning Scripts v1.0 by Adrian Billington & Tanel Poder
       http://www.oracle-developer.net & http://www.e2sn.com

+ INSTANCE SUMMARY ------------------------------------------------------------------------------------------+

OUTPUT
--------------------------------------------------------------------------------------------------------------
| Instance: PRD              | Execs/s:   925.2 | sParse/s:    24.9 | LIOs/s:  303783.8 | Read MB/s:    42.8 |
| Cur Time: 12-May 01:26:20  | Calls/s:   941.6 | hParse/s:     0.0 | PhyRD/s:   5472.7 | Write MB/s:    0.1 |
| History:  0h 0m 7s         | Commits/s:   5.8 | ccHits/s:    65.6 | PhyWR/s:     16.2 | Redo MB/s:     0.0 |
+------------------------------------------------------------------------------------------------------------+

+ TOP SQL_ID (child#) -----+ TOP SESSIONS ---------+      + TOP WAITS -------------------------+ WAIT CLASS -+
|  89% | 0szdu5xyt628h (1) | 93,103,104,111,112    |      |  81% | read by other session       | User I/O    |
|   3% | any10yyapacp5 (3) | 197,502,375,219       |      |  12% | db file sequential read     | User I/O    |
|   1% | 33sr04320nk79 (0) | 322                   |      |   5% | latch: cache buffers chains | Concurrency |
|   1% | 4jgvbz1z484wk (0) | 398                   |      |   1% | db file parallel read       | User I/O    |
|   1% | 6y9n7a20n7mrd (0) | 99                    |      |   1% | ON CPU                      | ON CPU      |

OUTPUT
--------------------------------------------------------------------------------------------------------------
+--------------------------------------------------+      +--------------------------------------------------+

+ TOP SQL_ID ----+ PLAN_HASH_VALUE + SQL TEXT ---------------------------------------------------------------+
| 0szdu5xyt628h  | 2111011212      | SELECT "VBELN" , "TUTOR" , "ERDAT" , "AUDAT" , "KVGR2" , "WKDAY" , "AUG |
|                |                 | RU" , "VGBEL" FROM "VBAK" WHERE "MANDT" = :A0 AND "VBELN" BETWEEN :A1 A |
+ ---------------------------------------------------------------------------------------------------------- +
| any10yyapacp5  | 570089534       | SELECT T_00 . "VBELN" , COUNT(*) "ZCNT" FROM "ZBW_TEMP11" T_00 INNER JO |
|                |                 | IN "VBUP" T_01 ON T_01 . "MANDT" = :A0 AND T_00 . "VBELN" = T_01 . "VBE |
+ ---------------------------------------------------------------------------------------------------------- +
| 33sr04320nk79  | 2315334320      | SELECT T_01 . "TUTOR" , T_03 . "MATNR" , T_04 . "ZMVGR" FROM "ZSDT0078" |
|                |                 |  T_00 INNER JOIN "ZSDT0085" T_01 ON T_01 . "MANDT" = :A0 AND T_00 . "VB |

OUTPUT
--------------------------------------------------------------------------------------------------------------
+ ---------------------------------------------------------------------------------------------------------- +
| 4jgvbz1z484wk  | 2605360516      | SELECT "VBELN" , "POSNR" , "VBELV" , "ERDAT" FROM "VBAP" WHERE "MANDT"  |
|                |                 | = :A0 AND "EDUFL" = :A1 AND "MVGR1" IN ( :A2 , :A3 )                    |
+ ---------------------------------------------------------------------------------------------------------- +
| 6y9n7a20n7mrd  | 1492336331      | insert /*+ append */ into sys.ora_temp_1_ds_40535 select /*+ no_paralle |
|                |                 | l(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name |
+ ---------------------------------------------------------------------------------------------------------- +

시스템 전반의 스냅샷 리포트와 Top SQL, Top Wait, 액티브 세션 히스토리 등이 일목요연하게 출력되어 매우 간편하게 시스템 전반의 성능문제를 파악 할 수있다.
액티브 세션 히스토리를 V$ACTIVE_SESSION_HISTORY 뷰에서 읽기 때문에 최소한의 부하로 데이터를 가져온다.