Discussion :

[genio]

Bonjour les forumistes...
Le DBMS_STAT.gather .. fait-il en même temps les stats de colonnes ?

Merci pour vos réponses ...

[orafrance]

oui si tu changes les options. Je t'invite à aller lire la doc

[orafrance]

tu peux lire la note : 122371.1

Mais je te conseille de ne le faire que sur quelques colonnes et donc utiliser DBMS_STATS (noyau Oracle)

[genio]

Excuse-moi Fred mais je n'ai pas compris la phrase :

Mais je te conseille de ne le faire que sur quelques colonnes et donc utiliser DBMS_STATS (noyau Oracle)

Je fais bien des dbms_stats.gather.table où schéma.. et pas des Analyze... alors ?

[orafrance]

désolé, je croyais que tu utilisais fnd_stats

Alors voila la description (que tu trouves dans dba_source):

procedure gather_table_stats
(ownname varchar2, tabname varchar2, partname varchar2 default null,
estimate_percent number default null, block_sample boolean default FALSE,
method_opt varchar2 default 'FOR ALL COLUMNS SIZE 1',
degree number default null,
granularity varchar2 default 'DEFAULT', cascade boolean default FALSE,
stattab varchar2 default null, statid varchar2 default null,
statown varchar2 default null,
no_invalidate boolean default FALSE);
--
-- This procedure gathers table and column (and index) statistics.
-- It attempts to parallelize as much of the work as possible, but there
-- are some restrictions as described in the individual parameters.
-- This operation will not parallelize if the user does not have select
-- privilege on the table being analyzed.
--
-- Input arguments:
-- ownname - schema of table to analyze
-- tabname - name of table
-- partname - name of partition
-- estimate_percent - Percentage of rows to estimate (NULL means compute).
-- The valid range is [0.000001,100]. Use the constant
-- DBMS_STATS.AUTO_SAMPLE_SIZE to have Oracle determine the
-- appropriate sample size for good statistics.
-- block_sample - whether or not to use random block sampling instead of
-- random row sampling. Random block sampling is more efficient, but
-- if the data is not randomly distributed on disk then the sample values
-- may be somewhat correlated. Only pertinent when doing an estimate
-- statistics.
-- method_opt - method options of the following format
-- size_clause := SIZE [integer | auto | skewonly | repeat],
-- where integer is between 1 and 254
-- FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]
-- FOR COLUMNS [size_clause]
-- column|attribute [size_clause]
-- [,column|attribute [size_clause] ... ]
-- Optimizer related table statistics are always gathered.
-- degree - degree of parallelism (NULL means use of table default value
-- which was specified by DEGREE clause in CREATE/ALTER TABLE statement)
-- Use the constant DBMS_STATS.DEFAULT_DEGREE for the default value
-- based on the initialization parameters.
-- granularity - the granularity of statistics to collect (only pertinent
-- if the table is partitioned)
-- 'DEFAULT' - gather global- and partition-level statistics
-- 'SUBPARTITION' - gather subpartition-level statistics
-- 'PARTITION' - gather partition-level statistics
-- 'GLOBAL' - gather global statistics
-- 'ALL' - gather all (subpartition, partition, and global) statistics
-- cascade - gather statistics on the indexes for this table.
-- Index statistics gathering will not be parallelized. Using this
-- option is equivalent to running the gather_index_stats procedure
-- on each of the table's indexes.
-- stattab - The user stat table identifier describing where to save
-- the current statistics.
-- statid - The (optional) identifier to associate with these statistics
-- within stattab.
-- statown - The schema containing stattab (if different then ownname)
-- no_invalidate - Do not invalide the dependent cursors if set to TRUE.
-- When the 'cascade' argument is specified, not pertinent with certain
-- types of indexes described in the gather_index_stats section.
--
-- Exceptions:
-- ORA-20000: Table does not exist or insufficient privileges
-- ORA-20001: Bad input value
-- ORA-20002: Bad user statistics table, may need to upgrade it
--

[genio]

Ben en fait, j'ai aussi utilisé le programme de stat issu d'Oracle Appli 'fndgstst' sur 5 schémas...

1°) Peux-tu me dire quelle est la différence entre le programme 'fndgstst' et les stats qu'on récolte via DBMS-STATS.gather (schéma ou table) ?

2°) Laquelle des deux méthodes est-elle meilleure ?

3°) 'Fndgstst' est-il le programme envoyé par Oracle Appli quand on effectue la collecte des stats via OracleAppli (avec en paramètre, le schéma, le pourcentage etx...)



Merci pour tes réponses...