Remote DBA_Helper is a tool used to manage Oracle
databases. It consists of the login form, menus and scripts that do
the work. The login form is a bit more visually polished than the
one used throughout this book, but the principle is exactly the
same. A picture of the login form is noted in Figure 7.1. All the
elements are self explanatory.
The first item on the main menu, “Connect” takes
the user back to the login form. The next item, “Show Connection”
returns the user to the main menu (this menu). The third item,
“Sessions” invokes a submenu resembling the following figure:
This menu is in many ways central to the tool and
deserves a closer look. A schema filter serves to restrict the
displayed sessions to the particular user. It is a little form created
by using the PEAR HTML_Form module, which provides a choice of
schemas, by selecting v$session.
Once the schema is selected, all other functions from the sessions
menu restrict themselves only to the sessions owned by the selected
schema. This is very useful when there are many users logged on the
system and the Remote DBA is only interested in the sessions being used by
one particular user or application.
Here also lies a possibility for improving the
utility; filtering is at the moment possible only by schema, not by
module or client. When developers start utilizing the
under-appreciated PL/SQL module DBMS_APPLICATION_INFO,
this will become a necessity. Chances are that this will happen in
Oracle 10g because DBMS_MONITOR
package has the ability to concentrate on a module or a client, not
just a specific session. Figure 7.4 shows what this filtering form
looks like:
The default selection is, of course, all sessions.
The next three selections in the sessions menu provide data from
v$session and v$sesstat,
sorted on different statistics. All three screens look the same; only
sorted by different statistics. They are implemented by using three
files instead of one, because of the “kill” link which should return
to the page it was invoked from.
Had it not been there, all three files could have
been merged into one, as is the case with the “Expensive SQL” menu.
The “return to the caller” mechanism functions by setting a session
variable named “invoker” ($_SESSION[‘invoker’]) which is then passed
to the “header” function in the script that actually kills the
session. Here is list of sessions, sorted by CPU:
All user sessions have links called “Kill” and
“Info”. It is quite clear that the kill link kills the session using
“ALTER SYSTEM DISCONNECT SESSION’ instead of the deprecated ‘ALTER
SYSTEM KILL SESSION’ used by so many other tools. The “Info” link
opens a new browser window, which looks like this:
The “Session Wait” item queries the V$SESSION_WAIT
table to show the event that the session is currently waiting for;
“Session Events” queries V$SESSION_EVENT, while Session SQL goes to
V$SQLTEXT. If the session is
inactive, it does not have current SQL and this item will not show
anything. If the session is active and is executing SQL, the SQL will
be shown as in Figure 7.6:
There is a link called “Explain Plan” at the
bottom of the page. The name is somewhat misleading, because the
EXPLAIN PLANstatement is not
issued. Instead of the explain plan for the SQL statement, the tool
queries the V$SQL_PLAN table available in Oracle version 9i and later. This
table contains the actual execution plan for the statement.
Most of the other tools can run the EXPLAIN PLANstatement in some form, but they do not show the actual
execution plan. The advantage of querying v$sql_planis observing the real situation. The advantage of the
EXPLAIN PLAN approach is the ability to experiment with different
hints, indexes and other things that affect execution plans.
Remote DBA_Helper is not a development tool and its
primary mission is to help diagnose database problems. That is why
querying the actual execution plan is preferred over using the EXPLAIN
PLANstatement. The look of the
plan is consistent with the style adopted throughout the tool as noted
in Figure 7.7:
It is exactly the same query that was shown in
earlier in this book with war paint added. The most obvious thing is
that the execution plan is not shown with the usual indentation, but
in a tabular format, as a HTML table.
This was done for several reasons, most compelling
of which was the consistency with the rest of the tool. Also, I do not
find the tabular presentation of an execution plan any less
understandable or clear. For a web page, the HTML table is the most
natural and clearest way of presenting a rather large quantity of
information in a clear and easy to follow manner.
The last item in the “Sessions” menu is “Sessions
by event waited”. This is a query from v$session_wait,
but for all sessions ordered by SECONDS_IN_WAIT column. The form at
the bottom of the sessions menu is something unique to this tool and
was added to the tool at the time of need.
The form looks for an object which has allocated a
given block in the given file. When the session waits for a block and
file (events like “db block sequential/scattered read”), all that can
be seen from the event arguments are block and file. I usually had to
type very fast in order to get the object. However, now I can copy and
paste the event arguments to this form and, voila, the desired object
is found.
Be aware that this is a very expensive query which
will consume significant resources and read several tables comprising
the view Remote DBA_extents. Such
query can be quite a hog in a large database with many objects. If
the form is filled in like Figure 7.8, the outcome in my database will
be the EMP_PK index, belonging to the user SCOTT:
