Oracle 11g manually collects user statistics

Daily manual collection of statistical information, commonly used parameters are as follows. User name is ROIDBA

exec dbms_stats.gather_schema_stats(‘ROIDBA‘,estimate_percent=>20,no_invalidate=>DBMS_STATS.AUTO_INVALIDATE,method_opt=>‘for all columns size auto‘,force=>TRUE,cascade=>TRUE,degree=>4);

Gather_schema_stats Proceduresthis procedure gathers statistics for all objects in a SCHEMA. Syntaxdbms_stats.                                                 Gather_schema_stats (Ownname VARCHAR2, estimate_percent number DEFAULT To_estimate_percent_type (Get_param (' estimate_percent ')), block_sample BOOLEAN DEFAULT FALSE, ME Thod_opt VARCHAR2 default Get_param (' method_opt '), degree number default To_degree_type (Get_param (' DEG REE '), granularity VARCHAR2 default Get_param (' granularity '), Cascade BOOLEAN default To_cascade_typ          E (Get_param (' CASCADE ')), stattab VARCHAR2 default NULL, Statid VARCHAR2 default NULL, Options VARCHAR2 default ' GATHER ', objlist out Objecttab, statown VARCHAR2 default NULL, n  O_invalidate BOOLEAN DEFAULT to_no_invalidate_type (Get_param (' no_invalidate ')), Force BOOLEAN DEFAULT FALSE, obj_filter_list objecttab DEFAULT NULL);D bms_stats.                                                 Gather_schema_stats (Ownname VARCHAR2, estimate_percent number DEFAULT To_estimate_percent_type (Get_param (' estimate_percent ')), block_sample BOOLEAN DEFAULT FALSE, ME Thod_opt VARCHAR2 default Get_param (' method_opt '), degree number default To_degree_type (Get_param (' DE GREE '), granularity VARCHAR2 default Get_param (' granularity '), Cascade BOOLEAN default To_cascade_ty           PE (get_param (' CASCADE ')), stattab VARCHAR2 default NULL, Statid VARCHAR2 default NULL, Options VARCHAR2 default ' GATHER ', statown VARCHAR2 default NULL, no_invalidate BOOLEAN default To_no _invalidate_type (Get_param (' no_invalidate '), Force BOOLEAN DEFAULT FAL SE, obj_filter_list objecttab DEFAULT NULL); Parameterstable 142-43 Gather_schema_statS Procedure parametersparameter Descriptionownnameschema to analyze (NULL means current schema) Estimate_percentpercenta GE of rows to estimate (NULL means compute): The valid range is [0.000001,100]. Use the constant dbms_stats. Auto_sample_size to has Oracle determine the appropriate SAMPLE SIZE for good statistics. This is the default. The default value can be changed using the Set_database_prefs Procedure, Set_global_prefs Procedure, Set_schema_prefs Proc Edure and Set_table_prefs Procedure.block_samplewhether or not to use random block sampling instead of random row sampling . The Random block sampling is more efficient, but if the data was not randomly distributed on disk and then the sample values may b e somewhat correlated. Only pertinent when doing a estimate Statistics.method_optAccepts:FOR all [INDEXED | HIDDEN] COLUMNS [size_clause]for COLUMNS [size clause] column [Size_clause] [, column [Size_clause] ...] Size_clause is defined as Size_clause: = size {integer | REPEAT | AUTO | Skewonly}column is defined as column: = column_name | Extension name | Extension-integer:number of histogram buckets. Must is in the range [1,254].-repeat:collects histograms is on the columns that already has HISTOGRAMS-AUTO:ORACL e determines the columns on which to collect histograms based on data distribution and the workload of the columns.-Skewo Nly:oracle determines the columns on which to collect histograms based on the data distribution of the columns. The default is for all COLUMNS SIZE AUTO. The default value can be changed using the Set_database_prefs Procedure, Set_global_prefs Procedure, Set_schema_prefs Proc Edure and Set_table_prefs procedure.degreedegree of parallelism. The default for degree is NULL. The default value can be changed using the Set_database_prefs Procedure, Set_global_prefs Procedure, Set_schema_prefs Proc Edure and Set_table_prefs Procedure. NULL means use the table default value specified by the degree clause in the CREATE table or ALTER table StatemEnt. Use the constant dbms_stats. Default_degree to specify the default value based on the initialization parameters. The Auto_degree value determines the degree of parallelism automatically. This is between 1 (serial execution) and Default_degree (the system DEFAULT value based on number of CPUs and Initializati On parameters) according to the size of the object. When using Degree=>null, Degree=>n, or degree=>dbms_stats. Default_degree, the current implementation of Dbms_stats could use serial execution if the size of the object does not Warra NT parallel execution.granularitygranularity of statistics to collect (only pertinent if the table is partitioned). ' All '-gathers all (subpartition, partition, and global) statistics ' AUTO '-determines the granularity based on the Partiti Oning type. This is the default value. ' DEFAULT '-gathers global and partition-level statistics. This option was obsolete, and while currently supported, it was included in the documentation for legacy reasonsOnly. You should use the ' GLOBAL and PARTITION ' for this functionality. Note The default value is now ' AUTO '. Global '-Gathers global statistics ' global and PARTITION '-gathers the global and PARTITION level statistics. No Subpartition level statistics be gathered even if it is a composite partitioned object. ' PARTITION '-gathers partition-level statistics ' subpartition '-gathers subpartition-level Statistics.cascadegather Statistics on the indexes as well. Using This option was equivalent to running the Gather_index_stats Procedure on each of the indexes in the schema in Additi On to gathering table and column statistics. Use the constant dbms_stats. Auto_cascade to has Oracle determine whether index statistics to is collected or not. This is the default. The default value can be changed using the Set_database_prefs Procedure, Set_global_prefs Procedure, Set_schema_prefs Proc Edure and Set_table_prefs procedure.stattabuser statistics TABLE identifier describing where to save the CUrrent statisticsstatididentifier (optional) to associate with these statistics within stattaboptionsfurther Specification of which objects to gather statistics for:GATHER:Gathers statistics on all objects in the schema. GATHER auto:gathers all necessary statistics automatically. Oracle implicitly determines which objects need new statistics, and determines how to gather those statistics. When GATHER AUTO was specified, the only additional valid parameters was Ownname, Stattab, Statid, objlist and Statown; All other parameter settings is ignored. Returns a list of processed objects. GATHER stale:gathers statistics on STALE objects as determined by looking at the *_tab_modifications. Also, return a list of objects found to be stale. GATHER empty:gathers statistics on objects which currently has no statistics. Also, return a list of objects found to has no statistics. List Auto:returns A list of objects to is processed with GATHER AUTO. List Stale:returns List of STALE objects As determined by looking at the *_tab_modifications. List Empty:returns list of objects which currently has no statistics.objlistlist of objects found to be stale or emptyst  Atownschema containing Stattab (if different than ownname) No_invalidatedoes not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE. Use Dbms_stats. Auto_invalidate. To has Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the Set_database_prefs Procedure, Set_global_prefs Procedure, Set_schema_prefs Procedure and Set_table_prefs procedure.forcegather statistics on objects even if they is Lockedobj_filter_lista List of object fil Ters. When provided, Gather_schema_stats would GATHER statistics only on objects which satisfy at least one object filter in the List as needed. In a single object filter, we can specify the constraints on the object attributes. The attribute values specified In the object filter is case-insensitive unless double-quoted. Wildcard is allowed in the attribute values. Suppose non-null values S1, s2, ... is specified for attributes A1, A2, ... in one object filter. An object o was said to satisfy this object filter if (o.a1 like S1) and (o.a2 like S2) and ... is true. See Applying an Object Filter List.

Oracle 11g manually collect user statistics