How to get information about running command?

[Former Member]

Hi MaxDB-fans,

just a simple question?

How do I get as much information about a long running SQL command? For example, how much data was already processed by the statement? I want to calculate the remaining time for the command in relation to the existing size of the table to be processed.

As I know I can use the parameter SHAREDSQL to set the DB in an "mode" that I can get the running commands, but is there a possibility to get information what happens within the command?

Thanks for your help,

Christian

[Former Member · Accepted solution]

Hello Lars,

thank you for your fast response.

The statement will process the complete table (compression of a BI request in an info cube).

So is there a possibillity to get similar information from the task?

Something like:

1. note the records / pages read from the task (or session?) before starting the statement with x_cons

2. show the records / pages read from the task (or session?) while statement is in progress with x_cons?

3. calculate difference and compare with size of table to estimate the progress of the statement

Thank you.

Christian

[lbreddemann · Accepted solution]

Hi Christian,

nice idea - but as you may assume there would be some kind of nice view to such data if it would be available.

Actually you can only see the number of I/Os done for a specific task (which maps to a specific session).

That is unfortunately not enough information to come up with a good estimation, since you cannot know:

- whether the task is wating for something else during the runtime

- whether all I/Os related to your command are done by this task (think prefetching, think parallel index access)

- whether other I/O is influencing the performance of your session

- how much data is read with one I/O, as you have a fact table in BI it's likely to be stored clustered which would make up I/O with up to 64 pages per I/O instead of the 1 page with non-clustered storage.

So - still no good option to produce a prediction.

BTW: afaik BI fact tables are compressed per request or request-wise - that would mean the data is not accessed via a full table scan but via an index over the request data.

regards,

Lars

[Former Member]

Hi Lars,

thank you for your time and knowledge.

So it will remain a great mystery what happens inside a running SQL command, I hope for upcomming MaxDB versions

Best regards

Christan

[lbreddemann]

Hi Christian,

well - knowing what a specific task does right now is not that difficult.

You can use the x_cons tool to display the current task state and the suspends.

Also you can create a stack back trace of any task (using DEBUG TASK) so that you can see in what code part the task was working.

Of course this is not what you wanted - but we're not completely blind nowadays - just half-sighted

regards,

Lars

[lbreddemann]

> Hi MaxDB-fans

Hi Christian!

> just a simple question?

rather not...

> How do I get as much information about a long running SQL command? For example, how much data was already processed by the statement? I want to calculate the remaining time for the command in relation to the existing size of the table to be processed.

>

> As I know I can use the parameter SHAREDSQL to set the DB in an "mode" that I can get the running commands, but is there a possibility to get information what happens within the command?

There is an option to see the running commands but you need to activate the Command Monitor before you can use it. Even then you will just see the command text but not what MaxDB is actually doing to execute the command.

SharedSQL does not yet provide the interfaces for accessing the command texts without the Command Monitor - but the development is working on it! BTW: the main goal of SharedSQL is to save parsing time and memory - it keeps a cache of already parsed statements which may be reused.

So currently there is no way to estimate the remaining runtime of any command.

Anyhow, calculating a commands runtime in relation to a table size only makes sense if you really want to process all data in that table. As all data is stored in B*Trees in MaxDB most often data can be accesses much more effective than simply scanning a complete table and by that data access effort won't be linear related to the amount of data in the table.

Sorry to disappoint you - but such features are still in development right now.

best regards,

Lars