Database Reference
In-Depth Information
lead to a table with a lot of sparsely populated blocks is when tables are subject to more deletes than inserts. The
following example, which is an excerpt of the output generated by the
full_scan_hwm.sql
script, illustrates this:
At the beginning, a query leads to 468 logical reads in order to return 40 rows:
•
SQL> SELECT * FROM t WHERE n2 = 19;
SQL> SELECT last_output_rows, last_cr_buffer_gets, last_cu_buffer_gets
2 FROM v$session s, v$sql_plan_statistics p
3 WHERE s.prev_sql_id = p.sql_id
4 AND s.prev_child_number = p.child_number
5 AND s.sid = sys_context('userenv','sid')
6 AND p.operation_id = 1;
LAST_OUTPUT_ROWS LAST_CR_BUFFER_GETS LAST_CU_BUFFER_GETS
---------------- ------------------- -------------------
40 468 1
•
Then, almost all rows (9,960 out of 10,000) are deleted. However, the number of logical reads
needed to execute the query doesn't change. In other words, a lot of completely empty blocks
were uselessly accessed:
SQL> DELETE t WHERE n2 <> 19;
9960 rows deleted.
SQL> SELECT * FROM t WHERE n2 = 19;
SQL> SELECT last_output_rows, last_cr_buffer_gets, last_cu_buffer_gets
2 FROM v$session s, v$sql_plan_statistics p
3 WHERE s.prev_sql_id = p.sql_id
4 AND s.prev_child_number = p.child_number
5 AND s.sid = sys_context('userenv','sid')
6 AND p.operation_id = 1;
LAST_OUTPUT_ROWS LAST_CR_BUFFER_GETS LAST_CU_BUFFER_GETS
---------------- ------------------- -------------------
40 468 1
•
To lower the high watermark, a physical reorganization of the table is necessary. If the table is
stored in a tablespace with automatic segment space management, you can do this with the
following SQL statements. Note that row movement must be activated because rows might get
a new rowid during the reorganization:
SQL> ALTER TABLE t ENABLE ROW MOVEMENT;
SQL> ALTER TABLE t SHRINK SPACE;
•
After the reorganization, the query performs only 24 logical reads in order to return 40 rows:
