RE: DBA technoid questions

That's going to show you which physical files in library ERPLXF are hit a lot. The busiest one is at the top of the list. You probably know the busiest already and have done what you can with indexes to reduce the number of physical hits. Look down the list for surprises.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
Sent: Wednesday, January 11, 2012 1:20 PM
To: midrange-l@xxxxxxxxxxxx
Subject: DBA technoid questions

Last night at our local user group we had a speaker who came from mainframe and other backgrounds with extensive DBA experience. He spoke about some of the stuff in iNav that blows away stuff he's seen on these other platforms (like Visual Explain), or if it was available on other platforms, cost extra. His presentation was good but my constructive criticism dealt with what in this stuff should we be looking for and what actions should we take on them?

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. 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. 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.

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?



Rob Berendt