I have never been keen on the option to “shrink space” for a table because of the negative impact it can have on performance.
I don’t seem to have written about it in the blog but I think there’s something in one of my books pointing out that the command moves data from the “end” of the table (high extent ids) to the “start” of the table (low extent ids) by scanning the table backwards to find data that can be moved and scanning forwards to find space to put it. This strategy can have the effect of increasing the scattering of the data that you’re interested in querying if most of your queries are about “recent” data, and you have a pattern of slowing deleting aging data. (You may end up doing a range scan through a couple of hundred table blocks for data at the start of the table that was once packed into a few blocks near the end of the table.)
In a discussion with a member of the audience at the recent DOAG conference (we were talking about execution plans for queries that included filter subqueries) I suddenly thought of another reason why (for an unlucky person) the shrink space command could be a disaster – here’s a little fragment of code and output to demonstrate the point.
rem rem Script: shrink_scalar_subq.sql rem Author: Jonathan Lewis rem Dated: Nov 2018 rem Purpose: rem rem Versions tested rem 12.2.0.1 rem select /*+ gather_plan_statistics pre-shrink */ count(*) from ( select /*+ no_merge */ outer.* from emp outer where outer.sal > ( select /*+ no_unnest */ avg(inner.sal) from emp inner where inner.dept_no = outer.dept_no ) ) ; alter table emp enable row movement; alter table emp shrink space compact; select /*+ gather_plan_statistics post-shrink */ count(*) from ( select /*+ no_merge */ outer.* from emp outer where outer.sal > ( select /*+ no_unnest */ avg(inner.sal) from emp inner where inner.dept_no = outer.dept_no ) ) ;
The two queries are the same and the execution plans are the same (the shrink command doesn’t change the object statistics, after all), but the execution time jumped from 0.05 seconds to 9.43 seconds – and the difference in timing wasn’t about delayed block cleanout or other exotic side effects.
COUNT(*) ---------- 9498 Elapsed: 00:00:00.05 COUNT(*) ---------- 9498 Elapsed: 00:00:09.43
The query is engineered to have a problem, of course, and enabling rowsource execution statistics exaggerates the anomaly – but the threat is genuine. You may have seen my posting (now 12 years old) about the effects of scalar subquery caching – this is another example of the wrong item of data appearing in the wrong place making us lose the caching benefit. The emp table I’ve used here is (nearly) the same emp table I used in the 2006 posting, but the difference between this case and the previous case is that I updated a carefully selected row to an unlucky value in 2006, but here in 2018 the side effects of a call to shrink space moved a row from the end of the table (where it was doing no harm) to the start of the table (where it had a disastrous impact).
Here are the two execution plans – before and after the shrink space – showing the rowsource execution stats. Note particularly the number of times the filter subquery ran – jumping from 7 to 3172 – the impact this has on the buffer gets, and the change in time recorded:
---------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.03 | 1880 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.03 | 1880 | | 2 | VIEW | | 1 | 136 | 9498 |00:00:00.03 | 1880 | |* 3 | FILTER | | 1 | | 9498 |00:00:00.03 | 1880 | | 4 | TABLE ACCESS FULL | EMP | 1 | 19001 | 19001 |00:00:00.01 | 235 | | 5 | SORT AGGREGATE | | 7 | 1 | 7 |00:00:00.02 | 1645 | |* 6 | TABLE ACCESS FULL| EMP | 7 | 2714 | 19001 |00:00:00.02 | 1645 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("OUTER"."SAL">) 6 - filter("INNER"."DEPT_NO"=:B1) ---------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:09.42 | 745K| | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:09.42 | 745K| | 2 | VIEW | | 1 | 136 | 9498 |00:00:11.71 | 745K| |* 3 | FILTER | | 1 | | 9498 |00:00:11.70 | 745K| | 4 | TABLE ACCESS FULL | EMP | 1 | 19001 | 19001 |00:00:00.01 | 235 | | 5 | SORT AGGREGATE | | 3172 | 1 | 3172 |00:00:09.40 | 745K| |* 6 | TABLE ACCESS FULL| EMP | 3172 | 2714 | 10M|00:00:04.33 | 745K| ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("OUTER"."SAL">) 6 - filter("INNER"."DEPT_NO"=:B1)
For completeness, here’s the code to generate the emp table. It’s sitting in a tablespace using system managed extents and automatic segment space management.
create table emp( dept_no not null, sal, emp_no not null, padding, constraint e_pk primary key(emp_no) ) as with generator as ( select null from dual connect by level <= 1e4 -- > comment to avoid wordpress format issue ) select mod(rownum,6), rownum, rownum, rpad('x',60) from generator v1, generator v2 where rownum <= 2e4 -- > comment to avoid wordpress format issue ; insert into emp values(432, 20001, 20001, rpad('x',60)); delete /*+ full(emp) */ from emp where emp_no <= 1000; -- > comment to avoid wordpress format issue commit; begin dbms_stats.gather_table_stats( ownname => user, tabname => 'EMP', method_opt => 'for all columns size 1' ); end; /
Recent comments
1 year 3 weeks ago
1 year 15 weeks ago
1 year 19 weeks ago
1 year 20 weeks ago
1 year 25 weeks ago
1 year 46 weeks ago
2 years 14 weeks ago
2 years 44 weeks ago
3 years 28 weeks ago
3 years 29 weeks ago