13 Replies Latest reply: Dec 15, 2008 8:39 AM by 675343 RSS

    ALTER INDEX REBUILD and huge waste space

    675343
      Hi everybody.

      Concerns RDBMS EE 10.2.0.2 on a box with 16CPUs. Non-standard initialization parameters:

      db_16k_cache_size=3G
      pga_aggregate_target=3G
      sga_max_size=12G
      sga_target=5G
      workarea_size_policy=AUTO

      I have a big table partitioned monthly with a couple of local bitmap indexes on it. Table and indexes are stored in different tablespaces. The index tablespace is

      EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
      BLOCKSIZE 16K
      SEGMENT SPACE MANAGEMENT AUTO

      Every night a batch job makes some index partitions unusable then inserts/appends a portion of data and rebuilds indexes with

      ALTER INDEX ... REBUILD PARTITION ... PARALLEL NOLOGGING

      When finished, query on DBA_IND_PARTITIONS shows that for any index partition EXTENTS value is much greater than value of used BYTES, for example one of the partitions has 106 EXTENTS (1MB each so it makes 106MB of space) while only 15MB for BYTES.

      I understand that during REBUILDing parallel slave processes create temporary segments in the destination index tablespace so that are allocating much more space than finally that segment takes. But it also means that space is not freed. (DEALLOCATing/SHRINKing won't help). Same thing can be shown by queries on DBA_SEGMENTS and DBA_FREE_SPACE. Because of this behaviour I have huge wastage of space in the index tablespace.

      Can anybody help, please?
      Przemek

      Allocating space by parallel slave processes is documented in the book "Oracle Database Data Warehousing 10gR2", chapter 25 "Using Parallel Execution".
        • 1. Re: ALTER INDEX REBUILD and huge waste space
          damorgan
          I'm not sure, from what you've written, I can define the issue. The amount of space you are talking about seems insignificant if your database is so large you have partitioning.

          Is this an OLTP or DW system? If OLTP I would question whether bitmap indexes are a good idea.
          • 2. Re: ALTER INDEX REBUILD and huge waste space
            675343
            Hi.

            This is quite a big DW system (approx. 2TB). What I wrote is just an example of what's going on with my indexes. Bitmaps are the only option. There are about 50 of them, each consisting of about 50 partitions. So you can see that my problem is really bad: huge wastes of index space. I don't know what to do about that. Dropping and rebuilding is not the option. Already opened a Metalink SR but an Oracle guy seems to ask irrelevant questions and the whole thing lasts forever...

            Any help will be appreciated.
            • 3. Re: ALTER INDEX REBUILD and huge waste space
              108476
              Hi Przemek.
              db_16k_cache_size=3G
              Be careful!

              Please note that there is a nasty, unfixed bug with 16k blocksizes, and MAKE SURE you are not hitting this nasty bug:

              [http://www.oraclealchemist.com/tag/blocksize/]

              Also, be aware that not all indexes will benefit (perdormance wise) from a rebuild. Oracle ACE Andy Kerber developed a cool script to detect this:

              http://www.dba-oracle.com/t_script_to_detect_indexes_rebuilding.htm

              ***************************************************
              I understand that during REBUILDing parallel slave processes create temporary segments in the destination index tablespace so that are allocating much more space than finally that segment takes.
              Kinda sorta!

              - Oracle sweeps the index nodes, in-order, gathering keys and column values

              - The value-key pairs are stored in TEMP segments. There MAY BE intermediate baggage during this process

              - Then, Oracle re-names the TEMP segments into the tree structures and drops the old index tree

              When I rebuild indexes, I always use two tablespaces for each index (flip and flop), and I always make sure to have plenty of room in each!

              **************************************************
              But it also means that space is not freed.
              Did you see this yourself? Can we see the script you used?

              I've not seen that, but it could be that Oracle has not yet "gotten around" to nuking the TEMP segments.

              You can check by running a tablespace mapper script:


              set linesize 132;
              set pages 999;

              rem set feedback off;
              rem set verify off;
              rem set heading off;
              rem set termout off;

              break on file_id skip page;
              break on free skip 1;
              compute sum of KB on free;

              spool tsfrag;

              column owner format a10;
              column segment_name format a10;
              column tablespace_name format a14;
              column file_id format 99 heading ID;
              column end format 999999;
              column KB format 9999999;
              column begin format 999999;
              column blocks format 999999;

              select
              tablespace_name,
              file_id,
              owner,
              segment_name,
              block_id begin,
              blocks,
              block_id+blocks-1 end,
              bytes/1024 KB,
              '' free
              from sys.dba_extents
              where tablespace_name not in ('RBS','SYSTEM','TEMP','TOOLS','USER')
              union
              select
              tablespace_name,
              file_id,
              '' owner,
              '' segment_name,
              block_id begin,
              blocks,
              block_id+blocks+1 end,
              bytes/1023 KB,
              'F' free
              from sys.dba_free_space
              where tablespace_name not in ('RBS','SYSTEM','TEMP','TOOLS','USER')
              order by 1, 2, 5
              ;


              Hope this helps . . .

              Donald K. Burleson
              Oracle Press author
              Author of "Oracle Tuning: The Definitive Reference"
              http://www.rampant-books.com/book_2005_1_awr_proactive_tuning.htm
              • 4. Re: ALTER INDEX REBUILD and huge waste space
                675343
                Hi Don, thanks for your response.
                there is a nasty, unfixed bug with 16k blocksizes
                As for my 16KB segments' speed I shouldn't complain. But I'll have in mind that bug.
                not all indexes will benefit (perdormance wise) from a rebuild
                Because of a nightly multi-million batch inserts/appends I don't have another option than marking some of the index partitions unusable and rebuilding them after the job has finished. It is a usual procedure in DW systems, I think.
                Can we see the script you used?
                I don't have any special scripts for monitoring indexes but simple quering DBA_SEGMENTS/DBA_FREE_SPACE and comparing columns BYTES and EXTENTS.

                I know that Oracle rebuilds an index partition in parallel if only it's able to. In my case (16CPU's) Oracle uses 65 slave processes, surely 1 master and 32 for retrieving data and other 32 for storing it. If my understanding is correct, Oracle during the index rebuilt creates at least 32 temporary extents (1MB each) in the destination tablespace then renames them into permanent ones, right? They're seen as EXTENTS column in DBA_SEGMENTS view. What's really used by an index tree is shown in BYTES column. The difference between SUM(BYTES) and SUM(EXTENTS) is getting bigger and bigger every night in ratio about 1:10. It means to me that every night new extents are used and old ones never reused/freed up to the index tablespace. This is exactly what I can see from DBA_FREE_SPACE. In consequence, I'm quickly running out of space...

                Now, what can I do about the growing waste space? I tried to rebuild some index partitions into another tablespace with NOPARALLEL option. But (1) that takes much more time and (2) BYTES equals EXTENTS but strangely Oracle uses approximately the same number of extents or more as with PARALLEL option... It's really annoying while I know that this or that partition index can be many times smaller...

                PS. Any strict results I may deliver on Monday.
                • 5. Re: ALTER INDEX REBUILD and huge waste space
                  Jonathan Lewis
                  >


                  >
                  I know that Oracle rebuilds an index partition in parallel if only it's able to. In my case (16CPU's) Oracle uses 65 slave processes, surely 1 master and 32 for retrieving data and other 32 for storing it. If my understanding is correct, Oracle during the index rebuilt creates at least 32 temporary extents (1MB each) in the destination tablespace then renames them into permanent ones, right? They're seen as EXTENTS column in DBA_SEGMENTS view.
                  Your understanding is correct - although most people would probably say 64 slaves, since the 65th process is your normal background process acting as the query co-ordinator.
                  What's really used by an index tree is shown in BYTES column. The difference between SUM(BYTES) and SUM(EXTENTS) is getting bigger and bigger every night in ratio about 1:10. It means to me that every night new extents are used and old ones never reused/freed up to the index tablespace. This is exactly what I can see from DBA_FREE_SPACE. In consequence, I'm quickly running out of space...
                  This is where things get a bit hazy: dba_extents has a column called BYTES, and it's the number of bytes allocated to the extent (whether or not they've been used in the index). dba_segments has a column called extents and a column called bytes, which are the number of extents and bytes allocated to the segment.

                  I can't work out what you are doing when you say you are comparing sum(bytes) and sum(extents).

                  Earlier on, you say that you had an example showing 15MB vs. 106MB - can you supply the two queries that got these results.

                  >
                  Now, what can I do about the growing waste space? I tried to rebuild some index partitions into another tablespace with NOPARALLEL option. But (1) that takes much more time and (2) BYTES equals EXTENTS but strangely Oracle uses approximately the same number of extents or more as with PARALLEL option... It's really annoying while I know that this or that partition index can be many times smaller...
                  Just a shot in the dark - but there used to be a bug in 10.1 where a parallel build did the wrong arithmetic for sizing when it collected the different sections of the parallel index, and ended up with conflicting data in dba_extents and dba_segments. This should have been fixed by 10.2 - but perhaps you've found an example where the bug has lingered on.

                  Don't worry about the '16K block size bug' that Burleson mentions, it has nothing to do with your case. It's actually a bug relating to row migration in ASSM tablespaces when the block size is greater than 8KB. On the other hand - since there is rarely any benefit from putting objects into non-standard block sizes, you might want to review why your your bitmap indexes are in a non-standard block size, and consider moving them back into the standard block size the next time you rebuild them.

                  Regards
                  Jonathan Lewis
                  http://jonathanlewis.wordpress.com
                  http://www.jlcomp.demon.co.uk

                  "The temptation to form premature theories upon insufficient data is the bane of our profession."
                  Sherlock Holmes (Sir Arthur Conan Doyle) in "The Valley of Fear".

                  Edited by: Jonathan Lewis on Dec 14, 2008 4:07 PM
                  Previously stated dba_indexes where it should have been dba_segments in the "shot in the dark".
                  • 6. Re: ALTER INDEX REBUILD and huge waste space
                    Randolf Geist
                    user2038804 wrote:
                    Concerns RDBMS EE 10.2.0.2 on a box with 16CPUs. Non-standard initialization parameters:

                    When finished, query on DBA_IND_PARTITIONS shows that for any index partition EXTENTS value is much greater than value of used BYTES, for example one of the partitions has 106 EXTENTS (1MB each so it makes 106MB of space) while only 15MB for BYTES.
                    Przemek,

                    I can confirm that there is a bug in 10.2.0.2 which leads to inconsistent size-related information in DBA_SEGMENTS / DBA_EXTENTS after an index rebuild in parallel of a large index, possibly bug 4771672 fixed in 10.2.0.3. If I remember correctly the EXTENT information is the correct one and the information reported in DBA_SEGMENTS is misleading.

                    The Metalink note suggests to use the DBMS_SPACE_ADMIN.TABLESPACE_FIX_SEGMENT_EXTBLKS procedure to fix the wrong information in the dictionary, but I'm not sure if that was the one we used when encountering the issue.

                    Regards,
                    Randolf

                    Oracle related stuff blog:
                    http://oracle-randolf.blogspot.com/

                    SQLTools++ for Oracle (Open source Oracle GUI for Windows):
                    http://www.sqltools-plusplus.org:7676/
                    http://sourceforge.net/projects/sqlt-pp/
                    • 7. Re: ALTER INDEX REBUILD and huge waste space
                      675343
                      Randolf wrote:
                      I can confirm that there is a bug in 10.2.0.2 which leads to inconsistent size-related information in DBA_SEGMENTS / DBA_EXTENTS after an index rebuild in parallel of a large index, possibly bug 4771672 fixed in 10.2.0.3.
                      Hi Randolf. It looks like my case, thanks for pointing to this bug! Yesterday I'll try to verify it and let you know.
                      Jonathan wrote:
                      On the other hand - since there is rarely any benefit from putting objects into non-standard block sizes, you might want to review why your your bitmap indexes are in a non-standard block size, and consider moving them back into the standard block size the next time you rebuild them.
                      Hi Jonathan, thank you too for your remarks. I'll think about recreating indexes into the standard block size. I just thought that having bigger index block size would benefit from index scans (multiblock read).

                      Best regards,
                      Przemek
                      • 8. Re: ALTER INDEX REBUILD and huge waste space
                        675343
                        Yesterday I'll try to verify it and let you know.
                        Oops! Tomorrow, of course!

                        :o)
                        • 9. Re: ALTER INDEX REBUILD and huge waste space
                          Jonathan Lewis
                          user2038804 wrote:

                          Hi Jonathan, thank you too for your remarks. I'll think about recreating indexes into the standard block size. I just thought that having bigger index block size would benefit from index scans (multiblock read).
                          There are arguments for and against - and there are arguments for (and against) using smaller block sizes for bitmap indexes. In general, if you haven't got a good argument why your particular circumstances make a non-standard block size appropriate for some particular objects then you might as well stick to a single block size in your database.

                          In most cases where someone has used a non-standard block size for some of their system, there's probably no compelling reason to change things though unless (as you are doing) the objects are being dropped and recreated regularly anyway.

                          You should read Richard Foote's blog about indexing if you want a better idea about the pros and cons. The "generic" idea that bigger blocks are better for index ranges scans is very misleading and should be thought through very carefully.

                          Regards
                          Jonathan Lewis
                          http://jonathanlewis.wordpress.com
                          http://www.jlcomp.demon.co.uk

                          "Science is more than a body of knowledge; it is a way of thinking" Carl Sagan
                          • 10. Re: ALTER INDEX REBUILD and huge waste space
                            675343
                            You should read Richard Foote's blog about indexing if you want a better idea about the pros and cons.
                            Thanks, Jonathan. I'll certainly do.
                            • 11. Re: ALTER INDEX REBUILD and huge waste space
                              137669
                              Hi Przemek,

                              When you recreate your indexes I would recommend you to set parallel to 4 or maybe to 8.
                              There are 2 reasons for this:
                              - On most systems the performance doesn't get improved by setting parallel to a value larger then 8 .
                              - Each parallel processes needs it's own extent and the consequence of this is that the index may become much larger as realy needed.

                              Also because it is a DW I would recommend you to increase the value of pga_aggregate_target (to as much as possible).
                              Related to this you may have a look at:
                              http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/memory.htm#i49320

                              Regards
                              Maurice
                              • 12. Re: ALTER INDEX REBUILD and huge waste space
                                675343
                                Maurice wrote:

                                - On most systems the performance doesn't get improved by setting parallel to a value larger then 8 .
                                - Each parallel processes needs it's own extent and the consequence of this is that the index may become much larger as realy needed.
                                Yes, Maurice, I should find the reasonable trade-off between degree of parallelism and rebuilt time. Thank you for your hints.

                                Regards,
                                Przemek
                                • 13. Re: ALTER INDEX REBUILD and huge waste space
                                  675343
                                  Randolf wrote;

                                  I can confirm that there is a bug in 10.2.0.2 which leads to inconsistent size-related information in DBA_SEGMENTS / DBA_EXTENTS after an index rebuild in parallel of a large index, possibly bug 4771672 fixed in 10.2.0.3.If I remember correctly the EXTENT information is the correct one and the information reported in DBA_SEGMENTS is misleading.
                                  Hi Randolf.

                                  I've just made a couple of tests described in Metalink articles

                                  bq. 352330.1 Differences for Bytes and Blocks Columns Between DBA_SEGMENTS and DBA_EXTENTS After Creating Index in Parallel \\ 463101.1 HOW TO DISCOVER AND FIX THE MISTMATCH BETWEEN DBA_SEGMENTS AND DBA_EXTENTS DICTIONARY VIEWS

                                  and results assured me that my database encountered the bug you'd mentioned. After executing DBMS_SPACE_ADMIN.TABLESPACE_FIX_SEGMENT_EXTBLKS the mismatch between DBA_SEGMENTS, DBA_EXTENTS, and DBA_FREE_SPACE disappeared.

                                  The sad side of this story is that my indexes are much bigger that I thought (hoping that DBA_SEGMENTS values are correct...)

                                  I'd like to thank all who contributed to resolving my problem.

                                  Best regards,
                                  Przemek