O'Reilly logo

Oracle Database 11gR2 Performance Tuning Cookbook by Ciro Fiorillo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required