To work well, the optimizer relies on information about both—the data structures involved in the query and the data contained in them; the latter information is provided by statistics.
In this recipe, we will see how to collect statistics on database objects and see its effects on the optimizer's performance.
How to do it...
The following steps will show how to collect statistics on database objects:
- Connect to
- Collect statistics on the
EXEC DBMS_STATS.GATHER_TABLE_STATS (OWNNAME => 'SH', - TABNAME => 'CUSTOMERS', - ESTIMATE_PERCENT => 20, BLOCK_SAMPLE => TRUE, - CASCADE => TRUE, DEGREE => 4);
- Query for some statistic data collected in the previous step:
SET PAGESIZE 100 SET LINESIZE ...