SQL command history, custom keyword lists and bug fixes (Mumbai version 2.2.1.804)

Version 2.2.1.804 of Mumbai is available for download.

Here’s the list of changes:

• You can now create your own keyword lists in the console window

  

• You can now search for the SQL text by OLD_HASH_VALUE (Ctrl+Q shortcut)

  

• Added SQL statement history in console window (CTRL+H)

  

• Fixed the vanishing bind variable grid after running scripts in the console window
• Fixed some of the default filters in the parameter views and now including parameters with ISMODIFIED=TRUE
• Fixed row count showing up as zero in the status bar of the console window for queries with bind variables
• The external tables for trace file and the alert log defined in the optional Mumbai42 schema now have statistics locked to prevent “ORA-20011: Approximate NDV failed” errors when gathering statistics when the underlying trace file no longer exists

 

 

Mumbai version 2.2.1.801 available

Version 2.2.1.801 of Mumbai is available for download.

Lots of small changes and fixes and a few nice new features and enhancements. I hope you like it and if you do, please spread the word.

Here’s the list of changes:

• The context menu for data grids in the console window now has menu actions depending on the column names returned by the query (e.g. column named “SID” give you a menu item “Jump to session table…”)
  E.g. when you execute the query “select * from dba_objects”, Mumbai looks at the column names returned and offers appropriate actions in the context menu of the data table:

• SQL*Plus “execute” command now is supported with “Run script” in console window

 

• Additional login credentials in the console window are now sorted by username:

• When changing the connection in the main window, the additional login credential buttons are now correctly updated

• Labels on the x-axis of graphs can now be turned on/off in the “Data shown” tab of the customization window (the default is on for not more than 30 values, otherwise off)

• Fixed a display bug after incremental search in the Connections drop-down list

• When wrong credentials for a connection are given in the connection manager, the popup window asking for the correct credentials now shows the “Connect as” selection box

• Object Dependencies and References are now shown recursively in table/view details

• Added Object Dependencies and References as context menu item in data grids (see first image above)

• When getting an execution plan including fetching rows, you can now limit the total number of rows fetched and the number of rows fetched per DB fetch call. This can help generate realistic executions plans without having to fetch all result rows:

• Fixed the AWR/SP reports delta calculations for overflowing values and SQL address changes

• AWR/SP SQL reports: There is now an option to sum up values/counts from different SQL_IDs with the same FORCE_MATCHING_SIGNATURE hash under one SQL_ID:

• AWR/SP SQL reports: You can now set the minimum number of Top SQL statements shown in the report (see image above)

• AWR/SP SQL reports: Added context menu for SQL series with actions for the SQL_ID under the cursor:
  

• AWR/SP SQL reports: Added context menu action to show the unaggregated data from STATS$SQL_SUMMARY / DBA_HIST_SQLSTAT

• AWR/SP SQL reports: Added “Locate SQL_ID” dialog that makes an SQL_ID visible in all charts, optionally including all SQL_ID which are above a custom correlation value:

• AWR/SP reports: Added SGA and PGA Statistics report
  
  

• Added SQL keyword proposals in console window (press CTRL+Space to trigger – you can add your own keywords to the …\config\keywords_user.txt file):
  Keywords are currently from Oracle 11.2, but I plan to add a feature that lets you fetch the keywords from your database and link the generated keyword file to a connection set up in Mumbai.
• You can now define snippets in the custom scripts dialog and use them from the SQL keyword proposals selector in the console window:

• Show execution plan context menu action on SQL_ID and CHILD_NUMBER columns now correctly observes the content of the CHILD_NUMBER column

• Added context menu actions for “Show compilation environment” and “Show reasons for non shared cursor” (when SQL_ID and CHILD_NUMBER columns are available)

• Reworked and extended the available parameter views (thanks Tanel Poder for the excellent hacking session on parameters)

• Added mouse wheel scrolling for card views (as seen in the database info view)

• Added views for V$SESSION_LONG_OPS, V$SESSION_CONNECT_INFO, V$PROCESS_MEMORY, DBA_DIRECTORIES

• Added database info view (consolidating V$DATABASE, V$INSTANCE, V$VERSION and V$DATABASE_INCARNATION)

• Added view for V$SQL_BIND_CAPTURE (including a handy context menu action which opens the selected SQL including the bind variable definitions in a console window)

• Added an editor macro recorder in the console window

• Fixed the “Missing bind variables” view which also showed child cursors for the same sql_id

• “Find object” now lets you search by OBJECT_ID

• You can now select a color/bold font style for the tabs in main window through the context menu for the tabs:

• You can now select the sources for SP/AWR correlations:

Poll: Would you attend a live web session about Mumbai?

Mumbai version 2.1.1.689

Version 2.1.1.689 of Mumbai is available for download.

This fixes an access violation on startup introduced with the previous version.

Mumbai version 2.1.1.688

Version 2.1.1.688 of Mumbai is available for download. Not too many changes and additions this time, but the fix for the positioning of sub-item on the custom button bar made me release it already. Here’s the list of changes:

• Added execute SQL/script on multiple connections functions in console window
  This is a nice little timesaver when you need to execute a query or run a script on multiple databases. Instead of manually connecting to each database, you select “Execute query/script on multiple connections” and select the target databases in the next dialog.
  Mumbai will connect to, query and disconnect from each selected database and show the combined result with a SOURCE column in a grid:
• Added “Cell multi select” option to grid popup menus (through the context menu of the column header)
  Until now it was only possible to select whole rows in grids, which made it awkward to copy just one or a few columns to the clipboard. You can now switch to “Cell multi select” mode and select continuous cells and copy these to the clipboard.
• Added category field in connection management
• Added “Free MB of max” column in “Tablespace and datafiles” view
• Fixed refresh action in “Tablespace and datafiles” view
• Fixed positioning of sub-item on custom button bar
• Including CREATED and LAST_DDL_TIME columns in DBA_OBJECTS queries now
• Added “Convert to uppercase/lowercase” functions to context menu in console window
• Added “Users,Roles/privileges” view
  This view shows all users, system and object privileges granted to users and roles granted to users, including other roles and privileges granted to this role.The USER_PRIVS column shows a tree-like indented structure, while the USER_PRIVS_PLAIN column shows the same info unindented, so that you can filter on privileges/roles (“Who has a privilege/role?”).
  Though technically a role, PUBLIC is shown as a user and while all users have the privileges and roles assigned to PUBLIC, these are not shown below the users. So, if you would like to see all privileges a user has, you would need to filter for the username and ‘PUBLIC’ on the USERNAME column.

Mumbai version 2.1.1.630

Version 2.1.1.630 of Mumbai is available for download. Here’s the list of changes:

• Added graphical execution plan visualization
• Execution plans are now shown with additional columns as seen in Randolf Geist’s brilliant xplan_extended_display_cursor script (http://oracle-randolf.blogspot.com/2011/12/extended-displaycursor-with-rowsource.html) – Kudos and big thanks to Randolf!
• Added correlation report for SP/AWR data
• Made the SP/AWR Top SQL report queries significantly faster
• Added Elapsed time, CPU time, Buffer gets and Disk reads per execution graphs in SP/AWR SQL reports
• Added “Disk reads” graph to Statspack/AWR SQL reports
• Fixed a problem in the “Elapsed time” graph for SP/AWR data showing unrealistic high values
• Fixed error message in the log file switch map for more than 99 log file switches per hour
• Joined the info from DBA_JOBS_RUNNING and DBA_JOBS into one view
• Added “Base table” column in Segments View
• Three new grouping options for Snapper ASH mode are available: “sid+sql_hash_value+event+program”, “sid+sql_hash_value+event+module”, “sid+sql_hash_value+event+terminal”
• In the snapper ASH diagram you can now actually group by all columns that you asked snapper to gather
• Added “Export to png” for chart views (in the “Data shown” tab of the customization window)
• Updated Orasrp fixing a problem with empty 10046 trace file reports
• Added “Show user ddl” context menu items in the user view
• “Search SQL text by sql_id” now searches in V$SQLTEXT_WITH_NEWLINES
• Fixed fetched record count in the status bar of the console window showing “?”
• A couple of other fixes and small improvements…

Correlation reports for Statspack and AWR data

The upcoming now available version of Mumbai will adds correlation reports for Statspack and AWR data.

Here is how it works: Once you have one or more SP/AWR reports for “Instance activity”, “Wait events” or “Top SQL” open in Mumbai, you can click on “Correlate by time”.

Here’s an example: In the following screenshot you see an SP Wait events diagram which shows an interval of high wait times for wait event “SQL*Net more data from DB link”. Also notice that there is already a SP Top SQL report open.

With the data for “Wait events” and “Top SQL” already loaded in the background, we can now click on “Correlate by time”.

What we get then is a new window which shows two columns with identifiers for data series for wait events, SQL_IDs or system statistics (depending on what kind of reports you had open) and a third column showing a correlation value within -100% and +100%.

A high correlation value means that the two data series have a strong correlation over time. A high negative value means that when the first series has high values, the second series has low values and vice versa.

You can browse the correlation data in this table, but the significant correlation values for a data series are also shown as a hint when you hover the mouse over the graph for a series.

Going back to our example above, it looks like this:

This shows that there is a correlation (over time) between the “SQL*Net more data from DB link” wait event and the SQL statement with the SQL_ID “f410crm1ck46k”.

(EDIT 1:) If we select this statement on the “Elapsed time” tab of the “Top SQL” page, we can visually confirm that there is a correlation over time between the wait event and the elapsed time for this SQL_ID.

Here’s another example of one SQL statement with a correlating value to another SQL statement:

This seems to indicate that the SQL with SQL_ID “cj5ywur2kvhj2″ is called from within the parent SQL call for that the graph is shown above. You can see this kind of correlation frequently with PL/SQL blocks and scheduler jobs.

I guess these example show what the correlation report can do for you. I hope you find it useful.

Some additional notes:

• The correlation report is time based, what means that there is not necessarily a logical correlation between two series. It could be just coincidence.
• The correlations are calculated not by the absolute values for the data series, but on the relative series values compared to the maximum value of the data series. This is the reason why graphs for series with very high absolute values can have a high correlation value with graphs that have low absolute values. (EDIT 3: As Sergei pointed out in the comments below, it actually doesn’t matter if the correlation is calculated based on absolute or normalized values.)
• You might not see all correlations that actually exist. For example, if one execution of an SQL statement spans multiple snapshots, the wait events produced by this SQL will show up in all of these snapshots. However, the SQL execution times in the Top SQL report will only show up in the snapshot when the execution finished. Though there is a logical correlation between the SQL execution time and the wait events triggered by the execution of the SQL, the correlation report can’t find it, because the correlation calculation is snapshot based.
• (EDIT 2:) Special thanks and greetings to Yuri van Buren! His “Mining for gold in the AWR” session at the DOAG conference and our conversations inspired me to build the correlation report.