Donnerstag, 23. Dezember 2010

GUI criticism

As a DBA, I'm not a big fan of Graphical User Interfaces.
From my point of view they too often hiding more information than they visualize.
As I am no GUI developer, please excuse me if I name some things not correct.

I'll show an example. Please take this screenshot. I do not own the tool myself but got this via email from a developer.

What can I see?
In the top left corner I see 3 tabs, the active one is Sessions. This shows me a table with one line highlighted (sid 160). Below that table, there is something I would assume as a sub-tab. Especially the borders of that tab makes me think so, as it's 'above' the 'Sessions' tab.
The sub-tab Waits is the active on. In it I see 2 Tables, Current Waits and Total Waits.
At the bottom I can find a line Last refresh. I'm glad I have any timestamp anywhere.

This is all really fine. But I'm still missing something:

  • In general, I'd like to know the statement which generates the data I can see here. In the topmost table, I assume it will query v$session, v$process and some others to get the informations. But what's the query exactly? Even small changes can mean something different. Do you see the column CPU in the first table? I can not even imagine it's origin.

  • Many tools provide the ability to spool all SQLs they generate into a file. But as they do not associate the SQL to the visual representation, it's still guesswork.

  • I can not see which columns I do not see! Is the AUDSID important? Most of the time I'd say no. Until I have to deal with auditing

  • I can not adapt the query according to my needs. In the Current Waits table - I assume based on v$session_wait I am often interested in the P1, P2 and P3 values (sometimes even raw - it depends). Sometimes also the change of SEQ# is of some interest. This rapidly forces me to go to the command line, or SQL-window if you want.

But there are also clever examples of some GUIs. The one I like most (as it helps both blind people and others) is Providing Textual Descriptions of Charts. It's still not the SQL, but at least the content of a graph in re-useable form.

I have only considered of any kind of query, yet. no DML or DDL operations at the moment. Maybe it's worth another post in the future ;-)


hillbillyToad hat gesagt…

'Current Statement' will show the SQL affected by 'Current Waits'

You can spool the SQL on that screen to see what is generated. Refresh the screen, see the SQL pop up, pretty easy.

The columns are configurable. We do not show EVERY column from V$SESSION by default for example, but you're a click away from toggling them on and off.

True, you cannot change the query, but if your user has scrolled RIGHT, you would have seen the P1, P2, & P3 columns, plus a lot more :)

GUIs aren't for everyone, but neither are command-line interfaces.

We have another GUI called Performance Analysis that tracks wait statements for every query executed on your system and store it in a repository - think of it as turning on tracing for the entire system with ZERO overhead on the system, and having nice graphs to read the output instead of TkProf.

Martin Berger hat gesagt…

hillibillyToad, I did not want to offend any product. That's the reason why I cut of the header of the window. I just used it as an example. The best I had at hand as it was real life and quite recent for me.
You are quite correct, this particular tool is quite powerful, and with proper training a good DBA can utilize it similar to a good set of scipts.
So My criticism here is not this particular GUI. I just quite often have to switch from the 'prepared' tables and statements to the 'plain sql' window, where I can insert my own SQLs. Let's say the first 10 SQLs are prepared by the GUI, but afterwards I am in such areas which are not presented by the GUI or where I (as a not trained user) can not navigate easily.
There is another criticism I did not mentioned earlier: even the best GUI does mot make anybody automatically into a good DBA :-)
As you mentioned Performance Analysis and claimed tracing with ZERO overhead I'd like to see this. I just don't believe) But that's another story.