Optimizing SQL Code - Oracle Database 11gR2 Performance Tuning - page 159

7. Re-compute statistics on the MY_SALES_2 table:

EXEC DBMS_STATS.GATHER_TABLE_STATS('SH', 'MY_SALES_2',
estimate_percent => 100,
method_opt => 'for all columns size 1');

8. Count the database blocks used by MY_SALES_ALL and MY_SALES_2 tables:

SELECT BLOCKS FROM DBA_TABLES
WHERE TABLE_NAME IN ('MY_SALES_ALL', 'MY_SALES_2');

9. Select some rows from the MY_SALES_ALL table, showing the execution plan

with statistics:
SET LINESIZE 120
SET AUTOT TRACE EXP STAT
SELECT * FROM sh.MY_SALES_ALL
WHERE TIME_ID > TO_DATE('20011220', 'YYYYMMDD');

10. Flush the buffer cache:
CONNECT / AS SYSDBA
ALTER SYSTEM FLUSH BUFFER_CACHE;

11. Select some rows using the same query from MY_SALES_2 table, showing the
execution plan with statistics:
CONNECT sh@TESTDB/sh
SET LINESIZE 120
SET AUTOT TRACE EXP STAT
SELECT * FROM sh.MY_SALES_2
WHERE TIME_ID > TO_DATE('20011220', 'YYYYMMDD');
SET AUTOT OFF

12. Shrink space on the MY_SALES_2 table:
ALTER TABLE sh.MY_SALES_2 ENABLE ROW MOVEMENT;
ALTER TABLE sh.MY_SALES_2 SHRINK SPACE;
EXEC DBMS_STATS.GATHER_TABLE_STATS('SH', 'MY_SALES_2',
estimate_percent => 100,
method_opt => 'for all columns size 1');