Re: DBA technoid questions

On 11-Jan-2012 11:20 , rob@xxxxxxxxx wrote:

<<SNIP>>

One example was that VE showed a table scan. Most of the attendees
did not know that a table scan was bad (for other than a small test
table) and why.

Even for a large TABLE a table scan may not be "bad". Everything "depends" on innumerable other variables. But basically, if most of the data from the TABLE will be in the result set and most rows will be selected, then a table scan is often better than using an INDEX because the former is sequential I\O whereby data can be paged nicely and the latter is random I\O for which page faulting is more likely.

But he showed how he used that to generate an index and chopped a
query that took several seconds down to milliseconds. Then he ran VE
again and was able to see that it no longer did a table scan but hit
the index, the table and then got the result.

As with a table scan, there are costs for an INDEX and the maintenance incurred. Determining whether the ability to access by INDEX is better, must evaluate the costs involved in the trade-off from having more access paths.

He chopped the remaining time into a third by adding some of the
columns in the table to the index that were not even used for sorting
or selection. This allowed the query to never touch the table.

Index-only is a nice feature of the database query, unfortunately not something available to the non-query database I\O, but again there are costs which may or may not be justified to achieve that capability when "adding columns" is required.

But remaining questions I have include stuff like:
- if I run this query
SELECT
SYSTEM_TABLE_SCHEMA, SYSTEM_TABLE_NAME
, LOGICAL_READS, PHYSICAL_READS
FROM systablestat
WHERE system_table_schema='ERPLXF'
ORDER BY PHYSICAL_READS DESC
Why should I care?

Are there particular statistics that people pay attention to and
what actions do you perform based on them?

The value of the statistics depend greatly on what is the effect by the queries against the data. I tend not to look at such statistics except when analyzing a specific query, but then only reviewing the change since before until after the query\fetch completes. Actions taken may be revising a query or the design of applications for how the data is queried including the possibly if\how\where\normalization of the data in the TABLEs of the database.... but probably only after having gotten here due to some performance issue which needs to be resolved. Mostly the other features such as explain, advised indexes, and db monitor would have already assisted without ever having to review those other statistics.

Regards, Chuck