Dynamic SQL snapshot stats

[martynh]

From a snapshot of dynamic SQL I understand most of the numbers returned. However, for inserts, I get (for example)

Number of executions = 1
Number of compilations = 1
Worst preparation time (ms) = 4
Best preparation time (ms) = 4
Internal rows deleted = 0
Internal rows inserted = 0
Rows read = 74
Internal rows updated = 0
Rows written = 1466
Statement sorts = 0
Total execution time (sec.ms) = 5.219089
Total user cpu time (sec.ms) = 0.265625
Total system cpu time (sec.ms) = 0.015625
Statement text = INSERT INTO tb1 (col1, col2, col3) VALUES (?, ?, ?)

How can 'Number of executions' be 1, whilst 'Rows written' be 1466. The insert statement only inserts 1 row at a time. There are no triggers on the table which might cause other activity. I wondered if it was reporting moved rows due to page splits, but I guess these would report as 'Internal rows'.

Thanks

Martyn Hodgson
CDE Solutions

 

[shim21c]

I want to know how to know snashop information exactly.
When I execute the command "db2 get snapshot for dynamic sql ~" , I don't know all information about dynamic sql.
For example,
~
rows of insert : not collected
rows of delete : not collected
rows of update : not collected
total cpu access time : not collected
~ etc..

Please, Give me the answer. How to Update command...

 

[sathyarams]

You do not have your monitor switches on ..

You can do it at the instance level using

db2 update dbm cfg using MON_STMT ON

or

at the session level using

db2 update monitor switches using STATEMENT ON


Remember, dynamic SQL Snapshot is a very expensive one ... So, it is not desirable to switch the same ON on production unless essential

Cheers

Sathyaram

More DB2 questions answered at

http://www.dbforums.com/f8

 

[martynh]

Having done a bit of digging around at what the application is doing, I think I can answer my own question.

I think that within our application, the inserts are done in a single transaction. The "Number of executions" is registered as 1 because there is just one transaction COMMIT. The number of "Rows written" is the number of insert statements commited in that transaction.

 

[sathyarams]

Your assumption on "Rows written" is true for simple INSERTs ..

But in complex SQL Queries, this value will have the number of rows written inot a temp table ...

Cheers
Sathyaram

More DB2 questions answered at

http://www.dbforums.com/f8