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?! ;-)

NB! I am running one more Advanced Oracle Troubleshooting training in 2018! You can attend the live online training and can download personal video recordings too. The Part 1 starts on 29th January 2018 - sign up here!

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. Kyle Hailey says:

    Very nice. Simple, clean and powerful.
    I like the grouping of top sessions for each top SQL.

    – Kyle Hailey

  2. Steve Bamber says:

    hi tanel

    discovered your blog this week whilst diagnosing a particularly nasty library latch contention issue around a high volume apex application.

    have to say i would never have got to the bottom of it without your latchprof script so many many thanks

    MOATS looks fantastic – i’ll be downloading and giving it a try


  3. Prem says:

    awesome , handy tool … Tanel . Simple and neat .

  4. Tanel Poder says:

    @Steve Bamber

    Steve, thanks! Do you have some output stored from your latchprof troubleshooting? I would love to blog about some real cases where my tools have helped real people with real problems. Or if you do have a blog, then just write about it yourself and I can link to your blog entry!

  5. Tanel Poder says:


    Prem yeah I love it as well. Adrian did most of the coding btw, that’s why it works so well, the idea + initial design came mostly from me.

  6. GregG says:

    I just cant wait ‘ORA-4031 and shared pool hacking video’ get published.
    I was there but totally forgot the difference between freeable and recreatable chunks :) and transient lru.

  7. Alex F says:

    Very nice! Does it require Enterprise Edition?

  8. Tanel Poder says:

    @Alex F

    Nope, it should work ok even on Oracle XE, as long as it’s Oracle 10.2+

  9. Tanel Poder says:

    Before anyone asks whether we’ll support Oracle 9i (or 8i or 7.3 or MSSQL Server) then the answer is no. No point in investing time in ancient db versions ;-)

  10. Steve Bamber says:

    @Tanel Poder
    hi tanel – i’ve posted my findings on the oracle forums, i gave your script a mention !

    intend to blog about it too when i get the chance

  11. Tanel Poder says:

    Yeah that’s one of the main benefits of latchprof/latchprofx – it actually allows you to narrow down exactly to the SQL_ID/libcache object and session’s holding the latches the most – you don’t have to guess!

  12. Steve Bamber says:

    @Tanel Poder
    that’s right – before i found your script we were focusing on some SQL that had a very high version count

    one run of latchprof and it was clear that the were irrelevant and the problems lay elsewhere

  13. Tanel Poder says:

    @Steve Bamber

    …even cooler! Looking forward your blog entry ;-)

  14. Jared says:

    I *seriously* wish I had thought of this. :)

  15. Lucian Lazar says:

    Hello Tanel, I installed the script using the steps in readme but I get no data in the last section (TOP_SQL_ID, PLAN_HASH_VALUE, SQL_TEXT) although I’m executing some queries for test. Could you please tell me if I do something wrong here? Thanks!

    + INSTANCE SUMMARY ——————————————————————————————+
    | Instance: is11g01 | Execs/s: 6.4 | sParse/s: 4.7 | LIOs/s: 10.5 | Read MB/s: 0.0 |

    | Cur Time: 31-Mar 15:31:13 | Calls/s: 6.5 | hParse/s: 0.0 | PhyRD/s: 0.7 | Write MB/s: 0.2 |
    | History: 0h 4m 45s | Commits/s: 0.1 | ccHits/s: 4.1 | PhyWR/s: 11.1 | Redo MB/s: 0.0 |

    + TOP SQL_ID (child#) —–+ TOP SESSIONS ———+ + TOP WAITS ————————-+ WAIT CLASS -+
    +————————————————–+ +————————————————–+

    + TOP SQL_ID —-+ PLAN_HASH_VALUE + SQL TEXT —————————————————————+

  16. Tanel Poder says:

    @Lucian Lazar
    Run a longer query. We don’t full scan through the whole V$SQLSTATS to show top sql, but only show these statements which show up in our ASH-like polling of V$SESSION … so if the SQL is significant (in other words, consumes lots of resource), you’ll see it

  17. Islam says:

    Thank you, very cool

  18. Steve Bamber says:

    @Tanel Poder
    hi tanel – blog post is up

    i’ve been trying moats and can see its going to be really useful :)

    been getting one or two display issues (looks like extra blank lines) when used via a putty session – are there any particular terminal settings it prefers ?

  19. Tanel Poder says:

    @Steve Bamber
    look into the settings in top.sql … I think “set pagesize 0” should be added so otherwise there’s a blank line after each page

  20. Lucian Lazar says:

    @Tanel Poder
    Thank you, it worked.

  21. Adam says:

    Hi Tanel,
    Just installed it and started playing. Thanks so much, this looks like another very cool tool!
    Just one tiny suggestion for the README; I think the moats_settings.sql script (4.1.2) needs to be run before executing the moats.format_window procedure (4.1.1) to get the right output.

  22. Adam says:


    I’m not sure if the stats in Instance Summary are correct. This is from a fairly small test DB with no users connected (first column removed to fit!):

    + INSTANCE SUMMARY ————————————————+
    Execs/s: 3.0 | sParse/s: 0.1 | LIOs/s: 1.1 | Read MB/s: 0.0 |
    Calls/s: 0.0 | hParse/s: 0.0 | PhyRD/s: 6514.5 | Write MB/s: 0.0 |
    Commits/s: 0.0 | ccHits/s: 0.1 | PhyWR/s: 6514.5 | Redo MB/s: 0.0 |

    And this from another test DB, this one has users connect doing “stuff”, but I’m still not sure there is this much going on:

    + INSTANCE SUMMARY —————————————————–+
    Execs/s: 1667.0 | Parse/s: 570.1 | LIOs/s: 27394.2 | Read MB/s: 0.0 |
    Calls/s: 308.6 | hParse/s: 1.6 | PhyRD/s: 148213.2 | Write MB/s: 0.0 |
    Commits/s: 54.0 | ccHits/s:1458.6 | PhyWR/s: 627892.2 | Redo MB/s: 0.1 |

    Above all, it’s the values for PhyRD/s and PhyWR/s that concern me, even in the “dead” database it’s constantly quoting 6514/s. And over 600,000PWR /s in the other DB. Can it be true?

  23. Tanel Poder says:

    Which DB version are you running on? MOATS was written for Oracle 10.2+

    And how do you run MOATS ..just using the function?

    The PhyRD/s and PhyWR/s indeed look weird as the MB/s columns show zero… so if you’re running the moats TOP function, it’s either our bug or a bug with oracle metrics in your db

  24. Adam says:

    Hey Tanel,

    1st db is
    2nd DB is

    To run it I follow the readme and just run:


    Let me know if you want further info from my DBs to find out where the problem lies, as you say it may well be to do with the metrics at my end.

  25. Tanel Poder says:


    Unfortunately I don’t have time to troubleshoot this case right now myself. Generally this has worked ok for all the databases I’ve ran it. So, if you want to figure this out yourself, I’d read some source code and compare the MOATS output with your own queries agains V$ views or Statspack/AWR/Snapper from the same time….

  26. Robert says:

    Hi Tanel,

    first: you do really great work!

    Anyway … during a test of MOATS on a test-RAC I got after several refreshes:

    ORA-20000: Error: ORA-01403: no data found at:
    ORA-06512: at “MOATS.MOATS”, line 178
    ORA-06512: at “MOATS.MOATS”, line 430
    ORA-06512: at “MOATS.MOATS”, line 740
    ORA-06512: at “MOATS.MOATS”, line 761
    ORA-06512: at line 1

    I restarted it several times with the same result, but few minutes later it worked well again. Hope it helps improving it.


  27. Steeve says:

    Can now troubleshoot on the bus ride to home on my iphone thanks to you :)

  28. Tanel Poder says:

    Thanks Robert, Yep either Adrian or I will take a look into it, as the errorstack line numbers are there, we can find what was the code location.

    Yeah, beats watching TV-shows, doesn’t it ;-)

  29. Omar Sawalhah says:

    Hi Tanel,
    Actually I didn’t try it myself I already download it, hope have a chance to play with it. But I have one question does it support RAC or not.


  30. Tanel Poder says:

    @Omar Sawalhah
    No RAC (GV$ view) support – yet. But if you have a few RAC nodes, you can just have a separate sqlplus window for each instance for now….

  31. Korkut says:

    I am using moats and select * from top command…
    In Top Waits column, %100 On CPU event is shown.
    But on DB’s OS machine, CPU is %80 idle…?
    Some settings are forgotten by me?
    Thanks, Korkut.

  32. Tanel Poder says:

    We plan some adjustments to MOATS about how it shows the %. This % currently means the percentage of total activity detected in database, not percentage of total CPU capacity in the system…

  33. Paresh says:


    You are “crazily” the best and one of the most generous Oracle expert. MOTS, Latchprof (the cool trick to sample the fixed view at high frequency), snapper, PerfSheet the list goes on. Recently I saw your work while at a client site when you helped in finding a performance bottleneck, you zeroed in on the performance problem with laser sharp focus in no time.


  34. Pradeep says:

    I installed MOATS on a non-RAC system and no problem. However when installing MOATS on a RAC ( system, while doing the type creation, the load went from 2.0 to 30 after 10 minutes. It had quickly printed 3 ‘type created’ prompts. After that the 4th ‘type created’ did not appear for 5-6 minutes and the 5th ‘type created’ never appeared. The system went into a high load. Are there any special steps for a RAC system?

  35. Tanel Poder says:

    It should be the same on RAC. I’ve installed and used MOATS on Exadata, so it has worked ok on these clusters…

    You probably did hit some shared pool or library cache issue and the MOATS compilation (regular PL/SQL after all) was just the victim of underlying shared pool problem.

  36. Pradeep says:

    Just realized that the first was on a system, not and that too RAC. Looks like that could be the problem as revoking grants also hangs when trying to undo things.

  37. Pradeep says:

    Tanel -The load on the system started going from 6 to 170 (was going up and down) – and I was able to use snapper once to see that all the time was spent on mutex events and box became unusable. The DBA shut down the database and now seems like they are seeing issue bringing it back up. This is a T-5220 system running Oracle-

    The first time problem was seen was at this ‘create type’ – since next prompt came after 5-6 minutes. The 5th ‘create type’ never appeared.

    create type moats_ash_ot as object
    ( snaptime timestamp

    I had to cancel it..

    Later when I did revote, it was stuck at

    undefine moats_priv_target;

    since I saw revoke on v$sql

    I did drop the special user also but alas something has gone wrong…

    any suggestions

  38. Tanel Poder says:

    So, what does the “issue bringing it back up” actually mean? Any error code when starting up?

    Moats is just a PL/SQL package like any other application PL/SQL in the database, so it’s not a MOATS problem. You are hitting some bug (11g is full of library cache cursor / mutex issues) or were hitting some shared pool problem, I’d say.

  39. Pradeep says:

    They had to bounce the cluster (twice), restart the DB – to get things back under control else uptime was bouncing between 6 and 180. Never seen at 180 till now…

    Will try again when the DBAs forget today :-)

  40. Jagjeet Singh says:

    Hi Tanel-

    Thanks for providing wonderful script. In-fact I have been thinking to write something
    similar but was stuck on continuous refresh functionality which you achieved using pipelined function based on arraysize+sleep. This is wonderful.

    Could you please explain the logic you are using to find TOP Sqls, it seems it is
    based on total no. occurrence of sql as per code I understood.

    Correct me if I am wrong, I expect it is/would be based on consumption of resources.
    Similar to TOP Waits.

  41. Tanel Poder says:

    @Jagjeet Singh
    You can just read the source code :-) Basically we do something like ASH does, but just by sampling V$SESSION view (as opposed to reading directly from memory like ASH). We just sample the current active sessions regularly and whichever SQL_ID, wait or session attribute is seen the most frequently, is what has taken the most time. Statistical sampling.

  42. Jagjeet Singh says:

    Thanks for answer, I found grid control “TOP Activity” graphs most useful to provide DB health at a glance. Is there any way we can get same functionality using sql*plus/sqls.

    I tried to trace the sql :( but could not get anything tangible.

  43. Jagjeet Singh says:

    @Jagjeet Singh
    I meant – using the same logic which Grid Control does apply.

  44. Regis Biassala says:

    Hi Tanel,

    Thanks, good scripts and helpful.


  45. Joachim says:

    Hi Tanel,
    thanks for the really useful script, I’d love to give it a productional try,
    but I’ve been running into the same issues as Adam did.
    The plsql-record PhyRD (v_metrics(5) in moats.pkb) contains strange values,
    compared with the values in v$sysstat. Must be something wrong with the snaps…
    Can you help me out?
    I’m on SE, single instance.

  46. Hi Tanel –
    I am back on same thread after sep. last year, As I said, I was looking for same kind of functionality but could never figure out how to implement auto-refresh functionality until you provided moats. Thank you very much. I got the idea from moats and I have written a similar *sqlplus dashboard* utility for my own usage. have a look.
    Thanks again.

Leave a Reply

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