 |
|
EnterpriseDB PL Debugger
Oracle Tips by Burleson Consulting
|
The PL
Debugger is a source level debugger for EnterpriseDB SPL and PL/pgSQL
(Figure 2.28). You can view and change variables as the program runs.
It has all of the functionality expected in a source level debugger:
Step Into, Step Over, Continue, and Breakpoints.
Figure 2.28: PL
Debugger
The debugger is launched from inside Developer Studio by highlighting a stored
procedure, function or package and hitting the Debug button or right clicking on
the stored object in the navigator and selecting Debug.
A current
limitation to the debugger is that you must be an administrative user to use it.
We will use the
debugger extensively in later chapters.
EnterpriseDB Remote DBA
Management Server
The EnterpriseDB
Remote DBA Management Server (Figure 2.29) is the tool that allows you to monitor your
database usage. With the Management Server, you can view the health of your
server, user activity, database configuration and security. You can schedule
database jobs, configure messaging and run some prepared reports.
Figure 2.29: Remote DBA
Management Server Dashboard
In Chapter 7, I
will walk you through all of the screens but for now, I want to show you a
couple of the more important ones. The screen in Figure 2.29 is the dashboard
that shows you the health of your database.
You can see the
transaction rates, memory and disk usage. If you see the message, “Please
enable block level statistics….”, select the Monitoring menu option and choose
Configuration.
On the
Configuration screen (Figure 2.30), make sure you have block-level statistics
turned on. For the purposes of our development and testing functionality, I
recommend turning on stats_block_level, stats_command_string and stats_row_level.
Figure 2.30:
Statistics Configuration
These
configuration variables determine how much logging the database server
produces. There is some overhead associated with logging but for development
and test, I think it is well worth it.
In a production
environment, you may want to play with specific values to get the right level of
logging with the minimal amount of overhead.
Once you’ve turned
on the options, press the apply button. Select Home, Home Page from the menu
and you should now be seeing statistics for your memory and disk usage.
I want to talk
about three more screens in the Remote DBA Management Server where you will likely
spend a lot of time.
The Monitoring -->
User Activity screen will show you who is connected to your database and what
they are currently running (Figure 2.31).
Figure 2.31: User
Activity
Under the SQL menu
option is iQuery (Figure 2.32). iQuery works like the SQL Interactive tool.
You enter a query in the query window and you get your results in the data
grid. The messages tab will display any error messages, the number of rows
returned and how long the query took to run.
Figure 2.32:
iQuery
Sometimes it’s
simpler to run a query here than it is to launch Developer Studio or the SQL
Terminal.
The final screen I
want to talk about now is the Query Profiler (Figure 2.33). This is somewhat of
a misnomer. This is not the explain plan feature. This is more of a SQL Trace.
Figure 2.33: Query
Profiler
Before your
queries are logged, you must go to SQL, Query Profiler. Check the Query Logging
checkbox and hit the apply button.
After turning on
query logging, run your queries or your application again and then come back to
this screen. Choose a log file, press Parse Log & Run Report and then view the
report output.
When you run the
report, you populate the bottom portion of the screen. You will see each
command that was run and statistical information about that command.
Not only is this
screen a great help in tuning your applications, it can also be a great help at
debugging your applications.
In Chapter 6, I
will cover all of the Remote DBA Management Server screens and show you how to add all
of your servers to a central management server.
This is an excerpt
from the book "EnterpriseDB: The Definitive Reference" by Rampant TechPress.
