Jeff Moss
article
about the commercial and free versions of Toad and the incredibly
tenacious, persistent breed of salesperson bred by Quest Software got
me thinking about the Oracle DBA tools I use.
- People
- SQL*Plus
- Statspack
- putty
People are important because people have developed the application,
people are using the application, people are managing the servers,
people are managing the database and intelligent people have
configured that very expensive storage array.
These people know a lot about the application, the history of the
project, the successes and failures, the lessons learned, the
architecture, the infrastructure whereas I may know, quite literally,
nothing about the same subjects.
I have seen people using Toad (and similar GUI based Oracle
development tools) very effectively, multi-tasking, flipping between
windows at breakneck speed. Sometimes it makes me quite tired just
watching them.
However, I prefer SQL*Plus to do most of the work because :-
- SQL*Plus provides a complete report of my session, with timings,
query plans, statistics. No need to frantically try to write it all
down.
- The SQL script can be incrementally developed and is repeatable. It
is quite easy in Toad to execute an arbitrary set of mouse-clicks so
it is not clear which of the six 'alter session' statements were
actually in effect when you finally got the optimal results.
- SQL*Plus is the 'vi' of the Oracle world. It is the one Oracle tool
you are guaranteed to have access to, everywhere. The sys admin may
not allow you to install commercial software without a valid license
or may use another tool you have never seen before.
- SQL*Plus can use bind variables, provide the query plan (with or
without retrieving the 6 million records) and provide the vital
statistics (consistent gets) just the same as the GUI tools. Almost
everything you need in fact.
Statspack is important because it is an Oracle package. Oracle will
maintain and develop statspack for the latest features available in
10g. Statspack produces reports in a standard format which can easily
be analysed by others (colleagues, DBA's, even Oracle). Statspack can
also be configured to run automatically at regular intervals. If there
is always a problem with the overnight ETL at 03.30, I would rather
statspack gets the overtime and gathers the performance metrics rather
than me sitting there in the middle of the night.
Statspack tells the truth, the whole truth and nothing but the truth.
It is incredibly tempting to use an Oracle monitoring tool like OEM or
Spotlight, obsessively watching the screen, drilling in on what
appears to be an expensive query and your salvation. The problem is
that you don't know the query in question is only spawned once a
quarter for the accountant's financial report and runs in batch. The
query could take 37 minutes and no-one would care. It is simply not
important. It is not what the users are complaining about.
Worse, because your refresh interval is 5 seconds, you are blissfully
unaware that you have missed the crucial SQL query with literal SQL
that takes 1.7 seconds but is executed hundreds of times per second.
Statspack does.
Setting the default statspack level to level 7 means that statspack
can (retrospectively) produce the query plan for any problematic SQL
statement identified in the summary report. This is handy where you
may have long winded SQL statements where the summary report has
tantalisingly truncated the SQL text just as the WHERE clause starts.
Putty is important so you can run O/S utilities (prstat, glance,
topas, top, iostat, vmstat) to monitor the actual database server
during the investigations. If the server is a development server
hosting multiple Oracle instances with 2 CPU's running at 100% and
saturated disks, then the performance of the application will be
impacted, no matter what wizardry you weave.