Collects SQL statements statistics (for pluggable database) from V$SQLAREA and V$SQL_PLAN dynamic performance views.
Long description:
Collects SQL statements statistics from V$SQLAREA and V$SQL_PLAN dynamic performance views. To calculate similarities between SQL statements, the Oracle internal procedure UTL_MATCH.EDIT_DISTANCE_similarity is used.
The maximum number of days to keep statistics for, in the repository tables. Remember that it can quickly take several GB of space for one week of statistics. It is important to monitor space consumption in the Management part of dbWatch Monitor GUI.
dbwatch tablespace max size
5000
Maximum size (in MB) of dbwatch tablespace.
collect execution plan
YES
Collects execution plan statistics into the history table if set to “YES”.
collect internal statistics
30
How often (in minutes) size of internal objects should be checked. “SQL statistics” job collects a large amount of data in the dbWatch schema tables, so it is important to keep track of space usage in the dbWatch tablespace.
SQL repository max size
80
Maximum space consumption (in percentage) of maximum size of the dbWatch tablespace for internal/repository objects that contain statistics for SQL statements.
min buffer gets
100
Minimum buffer gets value (per execution) for a SQL statement to be registered into history tables (repository tables).
check similarities
YES
Check similarities between SQL statements while collecting statistics. This option can be switched off if the procedure consumes too much time and resources. To calculate similarities between SQL statements, the Oracle internal procedure UTL_MATCH.EDIT_DISTANCE_similarity is used
return status
1
Return status value (ALARM – 2, WARNING – 1, or OK – 0) when reached maximum space consumption (in percentage) of maximum size of the dbWatch tablespace (parameter “SQL repository max size”)
Post your comment on this topic.