MOATS: The Mother of All Tuning Scripts!

Update: Based on the original MOATS built by Adrian Billington and myself, other folks have built cool variations of MOATS (either based on our code or written completely new ones):

MOATS (original) by Adrian Billington & Tanel:

MOATS 2.0 (RAC-aware) by Sidney Chen:

SQL Dashboard by Jagjeet Singh:

Both are RAC-aware, use terminal coloring techniques like my (fish.sql :) and some Exadata metrics.

A couple of screenshots from their tools:

moats_rac Screen Shot 2015-12-08 at 12.56.41

I’ve taken down the original video of MOATS (I actually messed something up in Youtube and the video disappeared :-) If you’d like to see the original MOATS, you can download it from Adrian’s website site (current version 1.05) and make sure you read the README.txt file in the zip!

Also thanks to Randolf Geist for finding and fixing some bugs in our alpha code (and anyone else that have contributed!)

P.S. Have you already figured out how the top-style screen refreshing in SQLplus works?! ;-)

This entry was posted in Cool stuff, Oracle, Productivity and tagged , , , , . Bookmark the permalink.

52 Responses to MOATS: The Mother of All Tuning Scripts!

  1. max says:

    Hi Tanel,

    I have been using MOATS for very long time but today was the first time i noticed something i never did….in the SQL_ID section i saw (o) , () …..would you happen to know why that would be the case, would that be related to commits or something that might causing that ?

    + TOP SQL_ID (child#) —–+ TOP SESSIONS ———+
    | 8% | 8043r26wd1bkv (0) | 1205,1281,845 |
    | 8% | (0) | |
    | 5% | 3s31uz4j0amyx (0) | 1752,1521 |
    | 5% | 3v581my0fv517 (0) | 621,606 |
    | 4% | () | |

    • Tanel Poder says:

      It’s probably how Oracle keeps track of (and doesn’t always properly restore) SQL_IDs in v$session when for example running a recursive query (or SQL in trigger) which then finishes and returns to the parent query. If you want to drill down into this – I’d use either sql*trace (with 10051 trace possibly for OPI calls) or ASH (top_level_call_name/sql_opname)

Leave a Reply

Your email address will not be published. Required fields are marked *