Collecting statistics

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:

1. Connect to SH schema:

   CONNECT sh@TESTDB/sh
   

2. Collect statistics on the CUSTOMERS table:

   EXEC DBMS_STATS.GATHER_TABLE_STATS (OWNNAME => 'SH', -
     TABNAME => 'CUSTOMERS', -
     ESTIMATE_PERCENT => 20, BLOCK_SAMPLE => TRUE, -
     CASCADE => TRUE, DEGREE => 4);
   

3. Query for some statistic data collected in the previous step:

   SET PAGESIZE 100
   SET LINESIZE 90
   SELECT
     NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT,
     AVG_ROW_LEN, AVG_SPACE_FREELIST_BLOCKS, NUM_FREELIST_BLOCKS,
     SAMPLE_SIZE, GLOBAL_STATS, USER_STATS, LAST_ANALYZED
   FROM DBA_TABLES
   WHERE TABLE_NAME ...