The Group Matching feature allows to group similar SQL queries that don’t use bind variables.
The purpose of this feature is to be able to check how much Oracle resources are consumed by several small queries.
Until now, we were only able to say that, for example, 1 million SQL queries were running the same way, and were consuming, let’s say, 12 ms each. That means nothing huge was visible in any summary report.
Now, with the Group Matching feature, d.side Replay can show that these similar queries are cumulating more than 3 hours (1 million x 12 ms).
These small SQL queries could be like:
select name from customers where customer_num = 12;
select name from customers where customer_num = 789;
select name from customers where customer_num = 1234;
…Using the Group Matching feature, the Replay capture job will compute a value to group these queries like if they all were based on a bind variable:
select name from customers where customer_num = :1;Now, we are not anymore talking about 1 million different « invisible » queries executed once each, but we can view a unique group representing 1 million executions. With cumulated statistics.
The GATHER procedure offers different levels of Group Matching calculation, through its gmatch parameter.
The possible values for this parameter are explained here.
DSIDE_REPLAY.gmatch_all #
Every SQL statement is parsed to be computed a Group Matching value according to its text.
Choosing this level will give more detailed results, but should consume much more Oracle resources, mainly CPU, than when Group Matching is not activated.
DSIDE_REPLAY.gmatch_all_except_plsql #
Any SQL statement is computed a Group Matching value, except PL/SQL blocks, that are all grouped in a unique ‘COMMAND:47’ group, without any distinction. That means two totally different PL/SQL blocks will be stored in the same group.
For example, these two PL/SQL blocks will be part of the same ‘COMMAND:47‘ group:
begin dbms_stats.gather_table_stats(…); end;
begin select count(*) from …; end;As less parsing is performed, this level should consume less Oracle resources than the previous one, during capture. And that remains very interesting because every SQL queries running from PL/SQL blocks have already been parsed and applied a Group Matching value.
DSIDE_REPLAY.gmatch_only_dml_ddl #
A Group Matching value is computed only for the following SQL statements:
SELECT, INSERT, UPDATE, DELETE, MERGE, CREATE TABLE and CREATE MATERIALIZED VIEW.
Any other SQL statement is not parsed and is associated to a group whose name is made of the Oracle command type number. Example:
'COMMAND:9' for CREATE INDEX
'COMMAND:26' for LOCK
'COMMAND:42' for ALTER SESSION
'COMMAND:47' for PL/SQL EXECUTE
'COMMAND:110' for CREATE PUBLIC SYNONYM
…Please, refer to Oracle « Reference » guide for more details about the command type.
As less parsing effort is required, this level should consume less Oracle resources than the previous one, during capture. And it remains interesting as these SQL statements cover the main part of Oracle activity.
