Contents of this section:

Index Maintenance
Gathering Table or Schema Statistics
Deleting Old Data From the Database
Workflow and Job Instance Snapshots

In order for the system to perform properly, the system database must be properly maintained and administered. This is most important on Oracle, where proper index maintenance can be critical to system performance.

Qorus Integration Engine includes some facilities for assisting the DBA with maintaining the database. These facilities are documented in this section.

Index Maintenance

On Oracle dataservers, indexes must be maintained; as data is changed or deleted from indexed tables, indexes become less efficient; the number of dead leaf nodes and the number of indirections grow, slowing down table accesses. When these parameters breach pre-defined maximum values; the indexes should be rebuilt.

Qorus has two different ways of analyzing and rebuilding Oracle indexes that need it: internally in the system, using the system.arch service, and from the command-line, using the schema-tool program.

Additionally, Qorus provides a facility for unconditionally rebuilding all indexes in a schema; for more information on this topic, see Unconditionally Rebuilding Indexes.

Analyzing and Rebuilding Oracle Indexes with schema-tool

To analyze and rebuild indexes on the entire system schema in Oracle using schema-tool, execute the following command:

unixprompt% schema-tool -R

The thresholds by default are given in the following table:

Criteria	Value
maximum index height	3
maximum percentage of deleted leaf nodes	20%

To override these values, use the –max-height and –max-leaf-pct options.

If either value is above the threshold, then the index will be rebuilt. The command used to rebuild the index is:

alter index [name] rebuild parallel nologging compute statistics

Note that indexes in any Oracle schema can be analyzed and rebuilt, as long as the Oracle user has permission to do so. To analyze and rebuild indexes in another schema, pass the name of the datasource as an argument to the -R option; for example:

unixprompt% schema-tool -R=billing

Analyzing and Rebuilding Oracle Indexes with the system.arch Service

The arch.analyze_rebuild_indexes() method will perform index analysis and rebuilding in a manner similar to schema-tool.

The system.arch service uses the following system parameters (set with the system.prop service):

Parameter Name	Default	Meaning
`ora-idx-max-height`	`3`	maximum index height threshold
`ora-idx-max-pct-del-leaf`	`20`	maximum percentage of deleted leaf nodes

To call this method manually from the command-line, type:

unixprompt% ocmd omq.system.service.arch.analyze_rebuild_indexes

Check the service’s log file for information about what actions were taken.

To just analyze indexes without rebuilding them, pass a boolean True or non-zero integer as the first argument to the method as follows:

unixprompt% ocmd omq.system.service.arch.analyze_rebuild_indexes true

The results of the index analysis can be found in the service’s log file.

Unconditionally Rebuilding Indexes

Indexes in the system schema can be unconditionally rebuilt using schema-tool as follows:

unixprompt% schema-tool -F

To unconditionally rebuild indexes in another schema, pass the name of the datasource to the -F option as follows:

unixprompt% schema-tool -F=datasourcename

On Oracle, the list of indexes in the schema is acquired executing the following SQL (note that partitioned indexes are ignored):

select index_name, tablespace_name from user_indexes 
where partitioned = 'NO' and index_type != 'LOB'"

Then indexes are rebuilt with the following command:

alter index [name] rebuild parallel nologging compute statistics

On PostgreSQL, the list of indexes is acquired by executing the following SQL:

SELECT c.relname as name
FROM pg_catalog.pg_class c
     JOIN pg_catalog.pg_roles r ON r.oid = c.relowner
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
     LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
     LEFT JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid
WHERE n.nspname <> 'pg_catalog'
  AND n.nspname !~ '^pg_toast'
  AND pg_catalog.pg_table_is_visible(c.oid)
  and r.rolname = 'schemaname' and c.relkind = 'i'
ORDER BY

Then indexes are rebuilt with the following command:

reindex index [name]

On MySQL, indexes are rebuilt by using the "repair table" command. A list of tables is acquired by executing "show tables". Then indexes on each table are rebuild by executing the following command on each table:

repair table [name] quick

Gathering Table or Schema Statistics

The schema-tool program can be used to gather statistics on Oracle, MySQL, and PostgreSQL databases.

Gathering Statistics on an Entire Schema

To gather statistics on the entire system schema, execute the following command:

unixprompt% schema-tool -S

Note that by giving a valid Qorus datasource name (defined in the system datasource (dbparams) File file), you can analyze statistics on any datasource known to Qorus (as long as the user has the appropriate permissions); for example, to gather statistics on the schema defined by the datasource named billing, execute the following:

unixprompt% schema-tool -S=billing

For Oracle, the following SQL is executed:

begin dbms_stats.gather_schema_stats(ownname => 'schemaname', 
	estimate_percent => 10, granularity => 'all', 
	method_opt => 'for all columns size auto', 
	degree => dbms_stats.default_degree, 
	options => 'gather auto', cascade => true ); 
end;

For PostgreSQL, the following command is executed:

analyze

For MySQL, the following command is executed for each table in the schema (as determined by executing "show tables"):

analyze table [tablename]

Gathering Statistics on a Table

To gather statistics on a particular table, in the system schema execute the following command:

unixprompt% schema-tool -T=tablename

To gather statistics on a particular table in another schema in a defined datasource, type the following:

unixprompt% schema-tool -T=datasourcename=tablename

For Oracle, the following SQL is executed:

begin dbms_stats.gather_table_stats (ownname => 'schemaname', 
	tabname => 'tablename', partname => null, 
	estimate_percent => dbms_stats.auto_sample_size, 
	degree => 3, cascade => true); 
end;

For PostgreSQL, the following command is executed:

analyze [tablename]

For MySQL, the following command is executed:

analyze table [tablename]

Deleting Old Data From the Database

See Arch system service.

Workflow and Job Instance Snapshots

A common task for a typical Qorus operations team is to check the status of workflow orders. When there is a very large amount of orders in the online schema (for example, more than 1 million), summarizing the status for all workflows can take a long time and use up valuable database processing resources. In order to alleviate this problem, Qorus can be configured to use the info-snapshot option to pre-calculate historical live data by status.

Affected tables:

Live Table	Snapshot
`JOB_INSTANCE`	`JOB_INSTANCE_STATS`
`WORKFLOW_INSTANCE`	`WORKFLOW_INSTANCE_STATS`

For example, a simple oview call without arguments generates a summary report for orders modified in previous 24 hours.

unixprompt% oview

But calling

unixprompt% oview –ca

Takes all orders from the WORFLOW_INSTANCE table which can contain millions of rows, and the database has to perform a so-called full table scan reading all of the table's data blocks into memory with slow I/O operations.

When the info-snapshot option is defined, a pre-calculated status snapshot is used. For example: info-snapshot is set to 7. It means that the status report will use:

live rows from the WORKFLOW_INSTANCE table the data requested is less than 7 days old
the rest of the data from the report will be taken from the snapshot table (or materialized view on Oracle) WORKFLOW_INSTANCE_STATS

The WORFKLOW_INSTANCE_STATS table (or materialized view on Oracle) contains order instance status counts grouped by day and workflow. But using this snapshow, the final SQL execution plan for summary queries with large amounts of historical data will use fewer I/O operations.

Snapshot Refresh

Snapshots refresh can take some time. The exact duration depends on data amount in source tables.

Note: Any other refresh method not listed below is not supported by Qore Technologies.

With schema-tool: the preferred and suggested refresh method is done with schema-tool:
```
schema-tool -s 
schema-tool --snapshot 
```
This method uses native DBMS commands to refresh the snapshot. To ensure periodical refresh the schema-tool can be included in UNIX crontab or in similar tools.
Activating System Job: the system-wide way how to refresh snapshots. Qorus comes with job named qorus-snapshot-refresh. The job is deactivated by default. An administrator can activate (and reschedule) the job.
Warning
API Snapshot refreshing can take long time. And because it runs inside the Qorus server process it can make Qorus unresponsive for operational procedures like system shutdown etc.
With System API: the secondary resfresh method can be done with system API omq.system.schema.refresh-snapshots The API call needs to be run periodically as well.
Warning
API Snapshot refreshing can take long time. And because it runs inside the Qorus server process it can make Qorus unresponsive for operational procedures like system shutdown etc.

Notes about Snapshotting

The snapshot has to be refreshed periodically. The refresh interval needs to be set independently for each Qorus environment.
Snapshot refreshing can take some time, from a few minutes to tens of minutes depending on amount of rows in the live table.
The information returned from oview, the GUI, or the info service older than "now - days(info-snapshot)" are always returned as it was generated in the last snapshot refresh. For example: oview returns one workflow/order instance in ERROR status. The order was modified 2 months ago. Operations retries the order, causing it to get a COMPLETE status. oview will show a summary containing this workflow order instance counted in both the ERROR (from the snapshot) and COMPLETE (from live data) columns until the new snapshot refresh (then it will be corrected).