The optimizer chooses plans from statistics: the number of rows and blocks of each table, the number of distinct values in each column, and histograms for uneven data. DBMS_STATS gathers them. Oracle does it automatically each night, but after large changes, gathering at once keeps plans right.
Code for This Guide
The main examples are in the examples/pkg-performance folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
dbms_stats.gather_table_stats(ownname, tabname,
method_opt => 'for all columns size auto');
dbms_stats.gather_schema_stats(ownname, options => 'GATHER STALE');Statistics appear in USER_TAB_STATISTICS and USER_TAB_COL_STATISTICS. SIZE AUTO lets Oracle decide which columns need histograms.
Gather after a Large Change
Example:
create table flight_copy as select * from flights; -- gathers statistics too
delete from flight_copy where status = 'CANCELLED';
commit;
select num_rows, blocks from user_tab_statistics where table_name = 'FLIGHT_COPY';
begin
dbms_stats.gather_table_stats(user, 'FLIGHT_COPY',
method_opt => 'for all columns size auto');
end;
/
select num_rows, blocks from user_tab_statistics where table_name = 'FLIGHT_COPY';
select column_name, num_distinct, histogram
from user_tab_col_statistics
where table_name = 'FLIGHT_COPY' and column_name in ('STATUS', 'ROUTE_ID', 'FLIGHT_ID');Output:
Table FLIGHT_COPY created.
50 rows deleted.
Commit complete.
NUM_ROWS BLOCKS
___________ _________
2856 39
PL/SQL procedure successfully completed.
NUM_ROWS BLOCKS
___________ _________
2806 39
COLUMN_NAME NUM_DISTINCT HISTOGRAM
______________ _______________ ____________
FLIGHT_ID 2806 NONE
ROUTE_ID 50 NONE
STATUS 2 FREQUENCY- CREATE TABLE AS SELECT gathered statistics as it loaded: 2,856 rows.
- After 50 rows are deleted, the statistics still say 2,856 until they are gathered again.
- After gathering, NUM_ROWS is 2,806, and STATUS, with only two values left and uneven counts, gets a frequency histogram.
Things to Know
- Statistics count as stale when more than 10 percent of a table's rows changed, by default.
- Gathering invalidates dependent cursors over time, so new plans are made with the new statistics.
- Gather statistics on a table right after loading it in a batch job, before queries run against it.
Related Guides
Conclusion
DBMS_STATS gathers the table, column, and histogram statistics the optimizer plans with. Gather after large data changes and loads, let SIZE AUTO choose histograms, and check the results in the statistics views.
