Discussion :

[adetag]

Bonjour,

Si je fais la requête suivante:
ANALYZE TABLE hr.employees ESTIMATE STATISTICS SAMPLE 15 PERCENT;
J'ai la réponse:
Table analysed

Donc ça fonctionne, je comprend que cela sert au CBO mais que fait exactement ANALYSE TABLE que récupère t'il et comment s'en sert l'optimizer ???

Merci

[fatsora]

doc oficielle

http://download.oracle.com/docs/cd/B...s.htm#PFGRF003

14.1 Understanding Statistics

Optimizer statistics are a collection of data that describe more details about the database and the objects in the database. These statistics are used by the query optimizer to choose the best execution plan for each SQL statement. Optimizer statistics include the following:

Table statistics

Number of rows

Number of blocks

Average row length

Column statistics

Number of distinct values (NDV) in column

Number of nulls in column

Data distribution (histogram)

Index statistics

Number of leaf blocks

Levels

Clustering factor

System statistics

I/O performance and utilization

CPU performance and utilization


A partir de 9I perferer DBMS_STATS

Note:
Do not use the COMPUTE and ESTIMATE clauses of ANALYZE to collect optimizer statistics. These clauses are supported for backward compatibility. Instead, use the DBMS_STATS package, which lets you collect statistics in parallel, collect global statistics for partitioned objects, and fine tune your statistics collection in other ways. The optimizer, which depends upon statistics, will eventually use only statistics that have been collected by DBMS_STATS. See PL/SQL Packages and Types Reference for more information on the DBMS_STATS package.

You must use the ANALYZE statement (rather than DBMS_STATS) for statistics collection not related to the cost-based optimizer, such as:

To use the VALIDATE or LIST CHAINED ROWS clauses

To collect information on freelist blocks

[adetag]

Merci