Skip to Main Content
  • Questions

  • Using Parallel on create index causes fragmentation

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Dennis.

Asked: October 30, 2001 - 11:13 am UTC

Last updated: September 28, 2009 - 8:21 am UTC

Version: 8.1.6.3

Viewed 1000+ times

You Asked

Hi Tom.

We are using a 'good' storage management policy, in that all objects in one tablespace are the same size (we use tablespace defaults as opposed to specifying them). The sizes we use are 160 KB, 5120 KB (5 MB), and 160 MB.

We have been switching indexes to be nologging and parallel to improve speed of creation. Recently we had an index tablespace causing update jobs to abort because it could not create temp segments in the index tablespace. The only way we found to 'fix' the problem (after dropping tablespace and data files several times) was to create the indexes as noparallel, after which a quick scan of dba_free_space showed only one big contiguous memory chunk. When we had recreated with parallel (no dml operations ongoing), dba_free_space revealed all kinds of fragmentation.

A quick check of the other tablespaces reveals some 160 KB tablespaces fragmented, all but one 5 MB tablespace fragmented (one has 5 MB holes, but that's what we want to see), and the 160 MB tablespaces don't appear to be fragmented (hard to tell because we didn't use a tablespace size divisible by 160, so the holes could be at the end of the file...). The tablespace that blew up however, was a 5 MB tablespace (this tablespace has indexes on smaller tables than the 'perfect' 5 MB does).

When using parallel, it does not appear that the 'temp' segment sizes not held to the 'boundaries' determined by our extent sizes. Instead of finding a bunch of 'usable holes' that were the same size as our extents, we found many little holes that were unusable. The TAR we had opened suggested doing a tablespace coalesce after each create index. Their reasoning was that the extents were being allocated faster than smon could coalesce (since we use PCTINCREASE 0). This failed to work however, and still left the tablespace full of holes.

Will using Locally Managed Tablespaces force these temp segments when using parallel to be the size we need? I don't know if we are getting fragmentation because the parallel thread is writing to a whole bunch of 'non-sequential' areas, or if it is writing sequentially, and not following the sizing we would like.

I guess if I didn't confuse you (since I'm confused now after typing this) the real questions I have are the following.

1. Using Parallel seems to cause fragmentation. In a no parallel index, the 'temp' segments are the same size as an actual extent, and so we get no fragmentation. Does parallel not follow the same standard, and instead write a bunch of little segments that it converges back into one 'normally' sized extent when the sorting is done?

2. Does locally managed have the ability to keep the tablespace from being fragmented by controlling how big the temp segments are and making sure they write on the extent boundaries? (I haven't been able to test this yet, and actually got in to submit questions so here you go).

3. Is the fragmentation causing behaviour of parallel normal? The TAR we had seemed to expect that it would happen, and didn't seem surprised. I've never seen anything saying parallel would cause fragmentation, so just kinda curious here.

Thanks for your help. Hope you're booking some time off this holiday season already. If you need it, I have some pictures of the dba_free_space tucked away in a few places (not sure I saved them all, but I know I have at least one - the one that I did an alter tablespace coalesce after each and every create index, as the TAR suggested).

Dennis

and Tom said...

This is a side effect of parallel -- it does extent trimming. See
</code> http://docs.oracle.com/cd/A81042_01/DOC/server.816/a76965/c22paral.htm#8399 <code>
it talks of this extent trimming. SQLLDR direct path loads do the same. It can mess up your NEXT extent allocation as well.

LMTs with UNIFORM will solve this 100%.



Rating

  (6 ratings)

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

Comments

That was exactly what I wanted to hear

Dennis, October 30, 2001 - 1:18 pm UTC

Thanks Tom! I didn't know anything about the trimming, but that explains my 'tiny' bit of data tablespace fragmentation that I was seeing (but not worrying about yet). You killed two birds and I only wanted one, so I am very appreciative and happy. Thank you very much. The LMT and 9i upgrade is coming up soon for us, and now that you've answered my question, I can not worry about this. If needed, we can switch some tablespaces a little early, or 'temporarily' use no parallel.

Thanks so much.
Dennis

Scofield

A reader, September 05, 2009 - 10:57 pm UTC

Respected Sir,
In your expert oracle database architecture book,page 638:
You gave an example of space wastage on uniform extent tablespace by using insert /*+append*/
and
You said that:"and remember that this space is associated with the segment and will be included
in a full scan of the table".

Since direct path load inserts data above HWM, why does it have an impact on full scan of table?
Tom Kyte
September 07, 2009 - 2:32 am UTC

I know you mean it as a term of respect, but the "respected sir" isn't necessary (and it is a bit "over the top").


It loads above the HWM, but in the example I used (manual segment space management), it uses the entire new extent, the load RAISES the HWM 

ops$tkyte%ORA10GR2> create tablespace uniform datafile size 15m autoextend on next 5m extent management local uniform size 5m segment space management manual;

Tablespace created.

ops$tkyte%ORA10GR2> create tablespace autoallocate datafile size 10m autoextend on next 1m extent management local autoallocate segment space management manual;

Tablespace created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create table stage as select * from all_objects;

Table created.

ops$tkyte%ORA10GR2> alter table stage parallel 8;

Table altered.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create table t1 parallel 8 tablespace uniform as select * from stage where 1=0;

Table created.

ops$tkyte%ORA10GR2> create table t2 parallel 8 tablespace autoallocate as select * from stage where 1=0;

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> alter session enable parallel dml;

Session altered.

ops$tkyte%ORA10GR2> insert /*+ append */ into t1 select * from stage;

50396 rows created.

ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2> insert /*+ append */ into t1 select * from stage;

50396 rows created.

ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2> insert /*+ append */ into t2 select * from stage;

50396 rows created.

ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2> insert /*+ append */ into t2 select * from stage;

50396 rows created.

ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec show_space( 'T1' );
Free Blocks.............................           4,374
Total Blocks............................           5,760
Total Bytes.............................      47,185,920
Total MBytes............................              45
Unused Blocks...........................               0
Unused Bytes............................               0
Last Used Ext FileId....................              13
Last Used Ext BlockId...................           3,209
Last Used Block.........................             640

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec show_space( 'T2' );
Free Blocks.............................              38
Total Blocks............................           1,424
Total Bytes.............................      11,665,408
Total MBytes............................              11
Unused Blocks...........................               0
Unused Bytes............................               0
Last Used Ext FileId....................              14
Last Used Ext BlockId...................             529
Last Used Block.........................               8

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> set termout off
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> set autotrace traceonly
ops$tkyte%ORA10GR2> select /*+ noparallel(t1) */ count(*) from t1;


Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |  1270   (1)| 00:00:16 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T1   |   128K|  1270   (1)| 00:00:16 |
-------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       5762  consistent gets
       4218  physical reads
          0  redo size
        413  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

ops$tkyte%ORA10GR2> select /*+ noparallel(t2) */ count(*) from t2;


Execution Plan
----------------------------------------------------------
Plan hash value: 3321871023

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   316   (1)| 00:00:04 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T2   | 94031 |   316   (1)| 00:00:04 |
-------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1439  consistent gets
       1324  physical reads
          0  redo size
        413  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

ops$tkyte%ORA10GR2> set autotrace off

Scofield

A reader, September 12, 2009 - 10:02 pm UTC

Thanks Sir,

1-)So, in direct path load, Oracle first tries to insert the data above hwm.
If there is not enough space in above hwm, it will allocate new extent and therefore hwm rises.
Is that right?

2-)Is the result of the example that you show same for automatic segment space management?
Tom Kyte
September 14, 2009 - 1:13 pm UTC

1) the HWM rises regardless of whether we added a new extent or not.

you wrote:

"in direct path load, Oracle first tries to insert the data above hwm."

that should just be:

in direct path load, Oracle inserts the data above hwm.


2) it is similar. however there is a low high water mark and a high high water mark - it will tend to have less to scan due to the different strategies used by ASSM over manual

Scofield

A reader, September 19, 2009 - 3:08 am UTC

Respected Sir;

You said that
"the HWM rises regardless of whether we added a new extent or not."

I dont understand how hwm increases in direct path load ?

Suppose this is my hwm


|
v
+--------------------------------------------------------+
|x |x |x |x |x |x |x |x |x |x |x |x | | | | | | | |
|x |x |x |x |x |x |x |x |x |x |x |x | | | | | | | |
+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+




After direct part load (I insert the values "y"), It will be like:

|
v
+--------------------------------------------------------+
|x |x |x |x |x |x |x |x |x |x |x |x |y |y |y |y | | | |
|x |x |x |x |x |x |x |x |x |x |x |x |y |y |y |y | | | |
+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+


So the hwm doesnt change..Am I wrong Respected Sir?
Tom Kyte
September 28, 2009 - 8:21 am UTC

your high water mark would be above the Y data - if it were not, we wouldn't know about the existence of the new Y data and would never see it.

I don't know why you think it would not move, think about it - how could it be that it WOULD NOT move???

A reader, October 03, 2009 - 4:21 pm UTC

Now Im clear.
Sorry about this silly question sir

Need More Information

A reader, October 15, 2009 - 12:07 pm UTC

Hi Sir,

Can you please elaborate more on

"2) it is similar. however there is a low high water mark and a high high water mark - it will tend to have less to scan due to the different strategies used by ASSM over manual"

What is the difference between two and if possible some example as i know you only believe in number so example would be always there Right
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 channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.