Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Abhiraj.

Asked: October 20, 2017 - 9:10 am UTC

Last updated: October 24, 2017 - 1:50 am UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

Can tables which only experience inserts and updates and not delete be fragmented.


and Connor said...

Conceivably updates that grow a row could yield a table larger than it needs to be

SQL> create table t pctfree 0 as
  2  select * from dba_objects;

Table created.

SQL>
SQL> exec dbms_stats.gather_table_stats('','T');

PL/SQL procedure successfully completed.

SQL> select blocks, avg_row_len from user_tables where table_name = 'T';

    BLOCKS AVG_ROW_LEN
---------- -----------
      1398         132

1 row selected.

SQL>
SQL> update t
  2  set owner = owner || 'XXXXXX';

78404 rows updated.

SQL> commit;

Commit complete.

SQL>
SQL> exec dbms_stats.gather_table_stats('','T');

PL/SQL procedure successfully completed.

SQL> select blocks, avg_row_len from user_tables where table_name = 'T';

    BLOCKS AVG_ROW_LEN
---------- -----------
      1534         132

1 row selected.


So the rows grew by 4% but table grew by 10%.

But fragmentation in itself is not a problem. It is only an issue if it has knock-on effects on your database (ie, slower queries etc).

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Pctfree

Maure, October 21, 2017 - 6:18 am UTC

Why you choose 0 pctfree for your test? What if pctfree is 20 ? I do not have machine to test but thanks for elaborating.
Connor McDonald
October 22, 2017 - 9:08 am UTC

Doens't really matter. I chose pctfree 0 so that I *knew* that an update to a row (to make it larger) which be guaranteed for the row to move.

pctfree 20 reduces the chances of that happening (as would "minimize rows_per_block") but the concept remains - rows can "shift" and thus leave space in blocks.

But my other point also remains - unless it is causing problems, you needn't worry about it.

Kind of problem

Rajeshwaran, Jeyabal, October 23, 2017 - 7:54 am UTC

But my other point also remains - unless it is causing problems, you needn't worry about it.

Could you show us what kind of problem it is ? you mean during Full scan lots of empty blocks getting scanned ?
Connor McDonald
October 24, 2017 - 1:50 am UTC

If my table is n% larger than it needs to be, then full scan performance would be affected, but that is always going to be a *percent* rather than orders of magnitude.

And if people are going to be that concerned about (say) 10% then I'd argue that perhaps they should spend more time on pctfree settings. Because 99% of customers I've *ever* seen still have pctfree=10 (the default) on an insert-only transaction table :-(

The use cases for defragmenting to improve full scans are pretty rare in my opinion, ie, you'd freed up some block (eg large delete) *and* you dont intend to re-use those blocks with subsequent transactions.

In terms of row by row access, where rows have been chained/migrated, even then you really only need to be concerned if you are accessing the columns that have been relocated. And 'table fetch continued row' stats can be used to gauge the impact of that

Where I *do* feel we might see an increase in the approach of moving data is in 12.2 where we can do everything online, so re-clustering data to make subsequent range-style access more efficient looks cool.


Who cares

A reader, October 23, 2017 - 11:44 pm UTC

This brings back a memory of couple of cases going back a few years

One was Oracle, where they decided writing directly to disk MUST be faster than allowing all the internal OS/disk & other buffers to be in command.

The other Was "Novell" where they were convinced by their user base that they MUST release the disk API for companies to allow optimizing and defragmenting of the disk storage data, because it was "full of holes" and must be wasting cPU resources.

Both cases were proved to be mostly false after massive amounts of cash and resources were wasted.

As long as you have the space (which any professional database owner should have) leave it alone and don't worry.
Performance issues are usually related to poorly defined indexed or SQL queries.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database