A main feature in Cache Monitor is profiling of sql statements. Specifically for SQL analysis InterSystems added in Caché 2008.1 the %SYSTEM.PTools.SQLStats class and measures lines of code,global references, times run and duration of run e.g.
These information are used in Caché Monitor to implement the Profiling feature.
Here are some detail information about this new feature from the InterSystems Caché docs: This is an compile time option, so all dynamic queries are purged to re-generate them with statistics enabled. The stats gathered are:
Number of Global References
Number of Lines of Code Executed
Number of Times a Module is called
Total Time in a Module
Number of Rows returned by the query
The modules can be nested in one another. The outer module will display inclusive numbers, so the Module MAIN will be the overall results for the full Query Run. When you first enable SQLStats you should Purge Cached Query to force code regeneration.
When you turn off gather stats: do SetSQLStats^%apiSQL(0) you do not need to Purge Cached Queries, the couple extra lines of code should not change the performance. All of the data is stored in %SYS.PTools.SQLQuery and %SYS.PTools.SQLStats.
How does it work?
The Caché Monitor Profiler is easily accessible in the Server Navigators tree under the performance node. Before executing a Test sql, you have to enable the server side compile time option to re-generate all dynamic queries. You can enable this from the Tab PTools configuration. Press Enable PTools and the the server side compile time option is enabled.
After executing some sql statements you see the statistics like on the sreenshot below. You can reload the statistic table by pressing reload in the upper right corner. If you select a row in the statistic table Caché Monitor displays the associated sql statement.
On Tab Execution plan Caché Monitor shows the Execution plan from the selected sql statement.
Like the Execution plan feature in Query Analyzer, information about indexes and column selectivity are available.
