Name: SQL statistics
Platform: Oracle
Category: Performance
Extra cost package: SQL Performance package
Description: 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.
Version: 2
Default schedule: 0 * * *
Requires engine install: Yes
Compatibility tag: [CDATA[.[type=‘instance’ & databasetype=‘oracle’]/.[hasengine=‘YES’ & is_rds=0 & eleven_and_above=‘1’ & is_pluggdb=‘1’
Parameters
Name Default value Description
history threshold 7 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”)

Feedback

Was this helpful?

Yes No
You indicated this topic was not helpful to you ...
Could you please leave a comment telling us why? Thank you!
Thanks for your feedback.

Post your comment on this topic.

Post Comment