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.
The following steps will show how to collect statistics on database objects:
EXEC DBMS_STATS.GATHER_TABLE_STATS (OWNNAME => 'SH', - TABNAME => 'CUSTOMERS', - ESTIMATE_PERCENT => 20, BLOCK_SAMPLE => TRUE, - CASCADE => TRUE, DEGREE => 4);
SET PAGESIZE 100 SET LINESIZE ...