What SQL statements are captured by SQL PI and mechanism behind the scene? (4268776)

What SQL statements are captured by SQL PI?

• PI captures SQL statements from system table (like v$sqlarea, v$session in Oracle and sys.dm_exec_requests and sys.dm_exec_sessions in SQL Server)
• PI works on a 1-second based collection frequency, this is configurable to run less often as described in Knowledgebase article 4269068
• If the SQL Statement execution duration is higher than 1 second then PI will definitely capture it.
• If the SQL Statement execution duration is lower than 1 second then PI will capture it in the following cases:
  • The SQL statement is executed at the same time when the PI process captures the active and running SQL statements in the instance.
  • The SQL statement with frequent executions will have more chances to be captured by PI process during one of its executions.
  • If the database has more than 500 SQL statements executed, 20% is more than 1s and 80% less than 1s, the statements will be captured.
• PI does not capture the SQL statements of database background processes by default, but this possibility can be enabled by changing definitions

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.

For example:

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).

1. The SQL PI collection frequency can be adjusted:
2. Within the Databases main dashboard, select the instance(s).
3. Go to: Settings | Administration | SQL Performance Investigator
4. From the instances list, click on Settings and edit the value of SQL PI sampling frequency (milliseconds)

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:

• Real-Time - When a user is currently focusing on a screen, the sampling frequency for all of the collections associated with this screen switches to Real-Time.
• Online - When at least one user is connected, the sampling frequency for all of the collections that are not currently running at Real-Time frequency switches to Online.
• Offline - when no user is currently connected to the application

Additional information can be found in the Foglight for Databases Deployment Guide.