When Replay schema installation has completed, data can be collected into this Replay schema, using the DSIDE_REPLAY PL/SQL package that has been created.
The procedure to be executed is DSIDE_REPLAY.GATHER.
Note: for much more comfort, this procedure is embedded in d.side Console, to allow the user to start, stop and monitor capture jobs in a few clicks.
For more details about d.side Console, please refer to “d.side Console User Guide”.
GATHER procedure #
This procedure starts collecting data from Oracle GV$ views and stores their content into DSIDE$ tables.
Syntax #
DSIDE_REPLAY.GATHER(
interval IN NUMBER,
maxrun IN NUMBER,
jobname IN VARCHAR2,
delay IN NUMBER,
starttime IN VARCHAR2,
tracelimit IN NUMBER,
debug IN BOOLEAN,
alerting IN BOOLEAN,
binds IN NUMBER,
bgprocess IN BOOLEAN,
cores IN BOOLEAN,
dynsamp IN BOOLEAN,
fra IN BOOLEAN,
gmatch IN NUMBER,
iostats IN BOOLEAN,
lockedobjs IN BOOLEAN,
longops IN BOOLEAN,
network IN BOOLEAN,
pga IN BOOLEAN,
pxstats IN BOOLEAN,
reasons IN BOOLEAN,
resumable IN BOOLEAN,
segstats IN NUMBER,
largesegstats IN BOOLEAN,
sqlfulltext IN BOOLEAN,
syssegments IN BOOLEAN,
syssessions IN BOOLEAN,
syssql IN BOOLEAN,
tempstats IN BOOLEAN,
dropstorage IN BOOLEAN,
noplanpreds IN BOOLEAN,
norac IN BOOLEAN,
plansunique IN BOOLEAN,
racgroup IN BOOLEAN,
sessionshead IN BOOLEAN,
unparse IN BOOLEAN,
customfield IN VARCHAR2,
shift IN NUMBER,
sqllimit IN NUMBER
);Parameters #
| Parameter | Description |
|---|---|
| alerting | If true, run a lighter collect dedicated to alerting, not troubleshooting. For example, execution plan steps are not gathered in alerting mode. Default: false |
| bgprocess | If true, collect data used by the Backgroud Processes Analyzer. Default: false. |
| binds | Collect bind variables values. Possible values are: – 0: no bind value is gathered. This is the default. – DSIDE_REPLAY.bindsfull: collect every bind variables values, anyway. – DSIDE_REPLAY.bindsplan: collect bind variables values only if the execution plan changed. This can lead to a performance drop when collecting, but uses less storage space. |
| cores | If true, collect CPU cores and sockets number. Default: false. |
| customfield | Any Oracle V$SESSION field that could be of interest, like client_info for example. The “Custom field” is visible in Session Info when running a dynamic Replay. Its size is truncated to 32 characters. Default: null. |
| debug | If true, display the time to process tables when longer than the “tracelimit” threshold. Default: false |
| delay | Before starting collecting data from Oracle views, number of minutes to wait. Default: 0, no need to wait |
| dropstorage | Everyday, the collected data is stored in a different table. This option is used to perform a TRUNCATE with “DROP STORAGE” clause instead of the default “REUSE STORAGE” one, when switching from a day to another one. Default: false. |
| dynsamp | If true, collect Oracle dynamic sampling queries. Default: false |
| fra | If true, collect data related to Oracle Fast Recovery Area usage. Default: false |
| gmatch | Allow captured SQL queries to be grouped according to their text. Check the “Group Matching feature” chapter for more details. Possible values are: – 0: no Group Matching is computed. This is the default. – DSIDE_REPLAY.gmatch_only_dml_ddl: Group Matching value is computed for SELECT, DML and CREATE TABLE statements. Any other statement will be given a unique ‘COMMAND:nn’ group value, with nn matching the Oracle command type available in Oracle Reference Guide. For example, any CREATE INDEX statement will be associated to the ‘COMMAND:9’ group. – DSIDE_REPLAY.gmatch_all_except_plsql: Group Matching value is computed for any statement, except for PL/SQL blocks that are grouped in a unique ‘COMMAND:47’ group, without distinction between the blocks. – DSIDE_REPLAY.gmatch_all: Group Matching value is computed for every SQL statements that have been collected. |
| interval | Time (in seconds) to be waited between two snapshots. Default: 60 seconds |
| iostats | If true, collect statistics used by the Wait IOs Analyzer. Default: false. |
| jobname | Name given to the gather job that will be run. Limit: 20 characters. If this option is not used, a default “JOB_YYYYMMDD_HH24MISS” name is given, based on the workstation date and time. |
| largesegstats | To be set to true if V$SEGMENT_STATISTICS is based on a huge X$ table. For example when many DROPs have been performed in the database. Only useful when option “segstats” is activated. Default: false. |
| lockedobjs | If true, collect locked objects, optionally shown in the Locks and Dependencies Analyzer. Default: false. |
| longops | If true, collect Oracle long operations statistics, used by the Undo and Longops Analyzer. Default: false. |
| maxrun | Number of total running hours before the collect job stops. Default: 24 hours. A value of 0 means the job only stops when an error occured (Errors management) or when it has been cancelled by the user (Stopping the Replay gathering job) |
| network | If true, collect sessions network activity, for the Network Analyzer. Default: false. |
| noplanpreds | To be used with d.side software support, when hitting an Oracle bug (ORA-7445) when gathering SQL_PLAN table content. Default: false. |
| norac | If true, only collect data related to the currently displayed RAC instance, not the whole GV$ content. Default: false. |
| pga | If true, collect sessions PGA usage, for the PGA Analyzer. Default: false. |
| plansunique | If true, ensure information about an execution plan are stored once, even if this execution plan is used several times, for example on queries not using bind variables. An important amount of storage space can be saved, but details on the execution plan (like filter or access) could become much less accurate. Default: false. |
| pxstats | If true, collect statistics used by the Parallel Execution Analyzer. Default: false. |
| racgroup | Set the parallel_instance_group Oracle parameter to the ‘DSIDE_NONE’ value under RAC, when hitting Oracle issues because of the RAC parallel processes behavior during the capture job. Default: false. |
| reasons | If true, collect the reasons why SQL children have not been shared. Default: false. |
| resumable | If true, collect information about Oracle resumable operations. Default: false. |
| segstats | Collect segments statistics, used in different Analyzers. Possible values are: – 0: no segment statistic is gathered. This is the default. – DSIDE_REPLAY.segstatsbasic: collect segments statistics used by the Buffer Cache and RAC Analyzers. – DSIDE_REPLAY.segstatsfull: also collect statistics used by the Segment Statistics Analyzer. Enabling this option could require more time, resources and disk space, as more statistics are stored. |
| sessionshead | If true, reduce the storage usage to collect sessions statistics. Can lead the gathering job to lower performances. Default: false. |
| shift | Shift time, from 1 (1 am) to 23 (11pm), to choose the new hour when tables are switched from a day to the next one. Default: 0 (midnight) |
| sqlfulltext | If true, collect the full SQL queries text, not only the first 1000 characters. Default: false. |
| sqllimit | Maximum number of SQL queries kept per snapshot. The most consuming queries are kept, sorted according to their elapsed time. Default: 400. A value of 0 means all SQL queries are collected, without limitation. |
| starttime | Job start time is deferred, until the given operating system date and time. Expected format : YYYYMMDD-HH24MI. Default: null. Note: if both “delay” and “starttime” options are provided, “delay” is prioritary, and is the only one taken into account. In that case, “starttime” has no effect. |
| syssegments | If true, collect ‘SYS’ user segment statistics. Default: false. |
| syssessions | If true, collect statistics about sessions held by user ‘SYS’. Default: false. |
| syssql | If true, collect SQL statistics (queries, plans, binds…) for user ‘SYS’. Default: false. |
| tempstats | If true, collect Oracle temporary spaces statistics used by the Temp Analyzer. Default: false. |
| tracelimit | If ‘debug’ option is set, threshold (in milliseconds) to display tables that took time. Default: 1000, so the job logs every time a table took more than one second to be processed. |
| unparse | To be set with d.side software support, when hitting an Oracle bug (ORA-7445) when gathering SQL_PLAN table content. Default: false. |
By default, the Replay capture job does not collect a lot a data, in order to limit the disk space usage. Each time a user wants more data to be collected, the DSIDE_REPLAY.GATHER procedure must be notified of what data must be added to the snapshots.
For example, by default the job does not collect Oracle long operations. If the user wants these longops to be captured, the “longops” option must be specified when launching the GATHER procedure.
Launching a capture: example #
begin
DSIDE_REPLAY.GATHER(
interval=>30,
jobname=>'TEST 200 USERS',
starttime=>'20260131-1430',
debug=>true,
tracelimit=>500,
iostats=>true,
longops=>true,
binds=>DSIDE_REPLAY.bindsfull);
end;
/This command will start collecting data at 2:30pm the 31-jan-2026. The run to be launched will be given the ‘TEST 200 USERS’ jobname, with “debug” traces for tables that need more than 500 milliseconds to proceed. After a snapshot has completed, a new one will be taken after a sleep time of 30 seconds, including statistics about IOs, used by the Wait IOs Analyzer, and long operations. Also, bind variables will have their value gathered for every SQL queries using bind variables.
Warning #
Some GATHER options require special attention as they can affect performances or consumption on the database.
Some (like “gmatch”) can be used to reduce storage usage of the Replay schema. But in such cases, performances of the GATHER procedure could be impacted, and the global database activity as well.
“reasons” option too can sometimes lead the capture job to long time query running and consuming.
That’s why d.side software recommends not to activate these options without testing their effect on performances and disk usage, in order to be able to evaluate and accept the compromise between performance and storage usage.
