get number of table scans

[Former Member]

Hello,

in DBM-Gui there's the activity overview of some statistical data, amongst others the number of table scans / index scans. I have two questions regarding this numbers

a) what's the sql statement to fetch the data, and (much more important)

b) we need the data on table level, e.g. number of table scans for table A, table B, ... Is this possible ?

many thanks in advance...GERD...

PS: MaxDB 7.5.0.38 (on linux)

[lbreddemann]

> in DBM-Gui there's the activity overview of some statistical data, amongst others the number of table scans / index scans. I have two questions regarding this numbers

You should better use the DBAnalyzer output for more detailled information.

> a) what's the sql statement to fetch the data, and (much more important)

There's a feature called "command monitor" to figure this out (check the WIKI pages on that topic!)

> b) we need the data on table level, e.g. number of table scans for table A, table B, ... Is this possible ?

Why the heck do you need this?

Don't you rather need to know the statements that access the tables in order to tune them?

Or even another step back: you need to know what application steps these statements belong to, so that you can estimate whether tuning these statements would actually pay off.

> many thanks in advance...GERD...

>

> PS: MaxDB 7.5.0.38 (on linux)

Damn old version!

AT THE VERY LEAST install the current patch for 7.5.

Way better would be to upgrade to 7.6 or 7.7 - in these versions there are many new and enhanced features for monitoring and analysing performance issues.

regards,

Lars

[Former Member]

Hello Lars,

a) what's the sql statement to fetch the data, and (much more important)
 There's a feature called "command monitor" to figure this out (check the WIKI pages on that topic!)

O.K., I found it, but we don't want to start collecting all the stuff. Where does the activity overview gets its data from, since we're not running any monitors for collecting data ?

BTW:

DBAnalyzer isn't starting (via DBM-Gui), after clicking on it, a dialog appears, the text "0" and button O.K. inside...?!?!

b) we need the data on table level, e.g. number of table scans for table A, table B, ... Is this possible ?
 Why the heck do you need this?
 Don't you rather need to know the statements that access the tables in order to tune them?
 Or even another step back: you need to know what application steps these statements belong to, so that you can estimate whether tuning these statements would actually pay off.

You're right, it's a request from development. They wanted to know the tables which are involved in table scans most. I totally agree that it will be much more efficient to get the statements itself. Therefore we need the db-analyzer, right ?

 Damn old version!
 AT THE VERY LEAST install the current patch for 7.5.
 Way better would be to upgrade to 7.6 or 7.7 - in these versions there are many new and enhanced features for monitoring and analysing performance issues.

Full ack., again.....we have plans for upgrading to 7.6, but no schedule yet....

[lbreddemann]

>

> a) what's the sql statement to fetch the data, and (much more important)
>  There's a feature called "command monitor" to figure this out (check the WIKI pages on that topic!)
>  

> O.K., I found it, but we don't want to start collecting all the stuff. Where does the activity overview gets its data from, since we're not running any monitors for collecting data ?

There are certain system tables that countain views to global counters.

Without activating the monitors the information you want is just not available anywhere in the database.

> BTW:

> DBAnalyzer isn't starting (via DBM-Gui), after clicking on it, a dialog appears, the text "0" and button O.K. inside...?!?!

Hmm... no idea - maybe a current version of DBMGUI and MaxDB might help here...

>

> You're right, it's a request from development. They wanted to know the tables which are involved in table scans most. I totally agree that it will be much more efficient to get the statements itself. Therefore we need the db-analyzer, right ? 
Nope - the DBAnalyzer would just be a useful tool to figure out where general performance issues my be rooted.
I propose that 'development' refines it's request.
And by the way: it's really not that tablescans are bad per se.
So the right question is not "What tables are involved in full table scans most often?" 
The right question would be "What statement does impact our business processes the most?".
> Full ack., again.....we have plans for upgrading to 7.6, but no schedule yet....

Then : please, please, please - install the current 7.5 patch if somehow possible.

regards,

Lars