Databases Reference
In-Depth Information
Errors in hints
If there are errors in our hints, the optimizer will simply treat them as remarks. After adding
a hint, always compare the execution plans to check for the expected change, because there
will be no errors or exceptions to inform us of this situation.
Common errors, in addition to typos, are missing the plus sign or using the table name instead
of the table alias, when the latter was used in the query.
See also
F
The
Managing
stored
outlines
recipe in this chapter
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 = 'CUSTOMERS' AND OWNER = 'SH';
