MOATS: The Mother of All Tuning Scripts!

People talk about the Oracle SQL Developer 3 being out, which is cool, but I have something even cooler for you today ;-)

I finally figured out how to convert my screen-recordings to uploadable videos, so that the text wouldn’t get unreadable and blurry.

So, here’s the first video, about a tool called MOATS, which we have built together with fellow OakTable Network member and a PL/SQL wizard Adrian Billington (of oracle-developer.net).

Here’s the video, it’s under 3 minutes long. Play the video in full screen for best results (and if it’s too slow loading, change it to lower resolution from HD mode):

Check it out and if you like 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… Note that MOATS is still kind of beta right now…

P.S. I will post my ORA-4031 and shared pool hacking video real soon now, too! :-)

P.P.S. Have you already figured out how it works?! ;-)

Update: Now you can suggest new features and improvement requests here:

Note that this year’s only Advanced Oracle Troubleshooting class takes place in the end of April/May 2014, so sign up now if you plan to attend this year!

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

58 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

    cheers
    steve

  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

    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. Tanel Poder says:

    Update: You can suggest new features and improvement requests here:

    http://goo.gl/mod/0ANl

  7. GregG says:

    Tanel,
    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.
    Regards
    Greg

  8. Alex F says:

    Very nice! Does it require Enterprise Edition?

  9. Tanel Poder says:

    @Alex F

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

  10. 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 ;-)

  11. Steve Bamber says:

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

    http://forums.oracle.com/forums/thread.jspa?threadID=2199844&tstart=0

    intend to blog about it too when i get the chance

  12. 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!

  13. 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

  14. Tanel Poder says:

    @Steve Bamber

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

  15. Jared says:

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

  16. 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 —————————————————————+

  17. 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

  18. Islam says:

    Thank you, very cool

  19. Steve Bamber says:

    @Tanel Poder
    hi tanel – blog post is up http://is.gd/deE1Lg

    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 ?

  20. 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

  21. Lucian Lazar says:

    @Tanel Poder
    Thank you, it worked.

  22. 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.

  23. Adam says:

    Hi,

    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?

  24. Tanel Poder says:

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

    And how do you run MOATS ..just using the moats.top 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

  25. Adam says:

    Hey Tanel,

    1st db is 10.2.4.0
    2nd DB is 11.1.0.7.0

    To run it I follow the readme and just run:

    SELECT * FROM TABLE(moats.top);

    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.

  26. Tanel Poder says:

    @Adam

    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….

  27. Robert says:

    Hi Tanel,

    first: you do really great work!

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

    ERROR:
    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.

    Robert

  28. Steeve says:

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

  29. Tanel Poder says:

    @Robert
    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.

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

  30. Pingback: » Best Oracle Peformance Tools?

  31. 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.

    Omar

  32. 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….

  33. Korkut says:

    Hi;
    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.

  34. Tanel Poder says:

    @Korkut
    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…

  35. Paresh says:

    Tanel,

    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.

    Thanks!
    Paresh

  36. Pradeep says:

    I installed MOATS on a non-RAC 11.2.0.2 system and no problem. However when installing MOATS on a RAC (11.2.0.2) 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?

  37. Tanel Poder says:

    @Pradeep
    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.

  38. Pradeep says:

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

  39. 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-11.2.0.2.

    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

  40. Tanel Poder says:

    @Pradeep
    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.

  41. 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 :-)

  42. 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.

  43. 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.

  44. 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.

  45. Jagjeet Singh says:

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

  46. Regis Biassala says:

    Hi Tanel,

    Thanks, good scripts and helpful.

    Regis

  47. 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 11.1.0.7 SE, single instance.
    Thanks!
    joachim

  48. Pingback: Sqlplus Dashboard For RAC « Jagjeet's Oracle Blog!

  49. 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.
    http://jagjeet.wordpress.com/2012/05/13/sqlplus-dashboard-for-rac/
    Thanks again.

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>