This is the main procedure (entry point) into the package. Ordinarily this should be the only procedure that a customer is interested in using. Its purpose is to gather CBO statistics on all the relevant objects in the CA DataMinder database schema.
It has a long list of arguments, each of which has a default value. The intention is that for a new installation it should be sufficient to invoke this procedure on a scheduled basis, allowing all the parameters to default.
PROCEDURE Gather_all_stats(p_pn IN NUMBER DEFAULT 1, p_indexes IN NUMBER DEFAULT 1, p_num_partitions IN NUMBER DEFAULT -1, p_process IN NUMBER DEFAULT 1, p_Degree IN NUMBER DEFAULT 4, p_lock IN BOOLEAN DEFAULT TRUE, p_delete_gtt_stats IN BOOLEAN DEFAULT TRUE, p_set_rls_stats IN BOOLEAN DEFAULT TRUE, p_custom_call IN BOOLEAN DEFAULT TRUE, p_def_cap_samp_pct IN NUMBER DEFAULT 15, p_def_non_cap_samp_pc IN NUMBER DEFAULT 100, p_def_audit_samp_pct IN NUMBER DEFAULT 100, p_debug IN NUMBER DEFAULT 0);
The procedure operates by gathering statistics on all tables in the current schema that start with the prefix 'Wgn'. Then, depending on the supplied parameters and/or customizations, it will gather statistics on zero or more partitions for each table. It will optionally gather statistics on all indexes, and again for zero or more index partitions.
The number of partitions that have statistics gathered (for partitioned tables and indexes) depends on the p_num_partitions parameter, which directs it to gather statistics starting from the current partition and working backwards to the specified number of partitions. By default, all partitions have statistics collected.
By default, the procedure removes any statistics that have been collected on global temporary tables that start with the prefix 'TMP'. Additionally it will also set some user statistics on selected columns in the TMP_Wgn3AddrRLS and Wgn3EventParticipant tables that have been known to give inaccurate cardinality estimates without these statistics being set.
The following table describes each of the parameters in detail.
|
Parameter name |
Values |
Default |
Description |
|
|
p_pn |
0 Global |
1 |
Defines whether statistics are to be collected at global or global and partition level. |
|
|
P_indexes |
0 No indexes |
1 |
Defines if statistics are to be collected on indexes as well as tables. |
|
|
P_num_partitions |
Positive number |
-1 |
Specifies the number of partitions to collect statistics for. Defaults to -1, meaning all partitions. |
|
|
P_process |
Positive number |
1 |
A user defined process number. For example, 1 might be used for a daily process and 2 for a weekly process. |
|
|
P_degree |
Positive number |
4 |
Sets the degree of parallelism to use. |
|
|
P_lock |
Boolean |
True |
Locks statistics on completion. |
|
|
P_delete_gtt_stats |
Boolean |
True |
Deletes statistics on global temporary tables. |
|
|
P_set_stats_rls |
Boolean |
True |
Sets user-defined column cardinality statistics on some key columns |
|
|
P_custom_call |
Boolean |
True |
Makes a call to a user defined method in wgn_stats_cust. Only applicable if using customizations. |
|
|
P_def_cap_samp_pct |
Positive number |
15 |
Default sampling percentage used for main captured data tables (see Note 1 below). |
|
|
P_def_non_cap_samp_pct |
Positive number |
100 |
Default sampling percentage used for non captured data tables (see Note 3 below). |
|
|
P_def_audit_samp_pct |
Positive number |
100 |
Default sampling percentage used for audit data tables (see Note 2 below). |
|
|
P_debug |
0 No debug |
0 |
Defines the level of debug information written by DBMS_OUTPUT. |
|
Notes:
The captured data tables are:
The audit data tables are:
The non-captured data tables are all the others not listed above.
|
Copyright © 2014 CA.
All rights reserved.
|
|