BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 
 

Tour of Remote DBA_Helper

Oracle Tips by Burleson Consulting

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:

SEE CODE DEPOT FOR FULL SCRIPTS


The above book excerpt is from  "Easy Oracle PHP: Creating Dynamic Web Pages with Oracle Data". 

You can buy it direct from the publisher for 50%-off and get instant access to the code depot of Oracle tuning scripts:

http://www.rampant-books.com/book_2005_2_php_oracle.htm

 


Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter