What SQL statements are captured by SQL PI?
The main idea of PI is to capture the SQL statements that are meaningful and contribute to the instance workload.
Statements are collected at one second intervals into memory. They are aggregated into 1 minute resolutions and stored in the PI repository and stored for 6 hours. The 1 minute resolutions are then aggregated into 15 minute resolutions and stored for 3 days, and so on according to the SQL PI Schema document. This is attached on the KB.
ROLLBACK query is one types of queries in addition to COMMIT, SELECT, CREATE TABLE that SQL PI is collecting frequently; SQL PI's collection frequency is every 1000ms (1 sec).
Foglight uses additional collections to gather data. Everything is in seconds in all the Foglight dashboards including the SQL PI workload.
To access the collections settings, from the Databases dashboard, select on the instance(s) check box and go to: Settings | Administration | Collections | search for the "SQL PI Instances Statistic" collection used by SQL PI.
The Collection Frequencies administration screen allows using the table below, which lists all the Foglight for SQL Server / Oracle databases collections, for enabling or disabling specific collections, as well as configuring the sampling frequency for each collection.
The available sampling frequencies are as follows:
Additional information can be found on the following documentation: