1 2 Previous Next 17 Replies Latest reply: Oct 18, 2012 12:45 AM by CBessios RSS

    11.2.0.2 AW size grows steadily with every cube build

    CBessios
      I'm facing a very strange situation here. I was having an unnecessary and rapid grow of the tablespace I use for the various analytic workspaces I have. Therefore, I contacted a simple test as follows:

      I created some sample relational data tables consisting of 7 dimension tables and 1 fact table. I inserted data to these tables once and never touch them again.

      Then, I created a NEW analytic workspace and in there created 7 dimensions and 1 cube all mapped to the above tables. The cube is a compressed cube without partitioning and with all dimensions marked as sparse.

      I measured the size of the AW using AWM/Reports/Analytic Workspace Size and it was 159MB (structure only).

      Then, I loaded the dimension data only, resulting in the AW size of 448MB.

      I then did 10 loads of the cube using this simple script (I run the script 10 subsequent times and in between I measured the size of the AW):

      begin
      DBMS_CUBE.BUILD(
      'OLAPCOL3.EXPACT USING (CLEAR VALUES, LOAD, SOLVE)',
      'C', -- refresh method
      false, -- refresh after errors
      1, -- parallelism
      true, -- atomic refresh
      true, -- automatic order
      false); -- add dimensions

      exception
      when others then
      rollback;
      raise;


      end;

      The resulting size of the AW after each build was as follows:

      Schema Only     159 MB
      Load Dimension Data Only     448 MB
      Cube Build 1     617 MB
      Cube Build 2     753 MB
      Cube Build 3     881 MB
      Cube Build 4     1018 MB
      Cube Build 5     1147 MB
      Cube Build 6     1275 MB
      Cube Build 7     1419 MB
      Cube Build 8     1532 MB
      Cube Build 9     1668 MB
      Cube Build 10     1804 MB

      Therefore, without adding any new data, with every build of the cube (clear values, load, solve) the aw grows by approximately 130-140MB.

      The results of this simple test are consistent with my initial observation that my production tablespace grows unjustified and rapidly. I'm pretty sure I do something wrong here or I'm hitting a bug.

      The database is 11.2.0.2 RAC (2 nodes) with OEL x86.

      Thank you for any helpful information.
        • 1. Re: 11.2.0.2 AW size grows steadily with every cube build
          Joan Puig
          I have the same exact "problem" in 11.2.0.3 and a 11g AW.

          I have not documented the size after each build, because in my case I have been adding and removing data from the fact table. And since I don't know if I can trust Fast Refresh, I only have one crazy example of the issue.

          This two builds were made one right after the other, using AWM to change the aggregation level and dbms_cube.build to perform a Complete Refresh:

          ------------------

          - 7 days of data (~21M rows on fact table) / *80% aggregation* on both top and bottom partitions:

          Cube Size: 3.69 GB
          Time dimension: 3.24 GB (2 years created, only 7 days with data)

          - 7 days of data / *30% aggregation* on both top and bottom partitions:

          Cube Size: 6.62 GB
          Time dimension: 3.24 GB

          ------------------

          As you can see, the size of the cube almost doubled while the two dimensions kept the same size.

          And yes, the title of each build is correct: with 80% aggregation the cube had half the size of the cube built with 30%

          I don't currently have any solution to this problem, but I hope this extra info helps pin down the issue.

          Joan
          • 2. Re: 11.2.0.2 AW size grows steadily with every cube build
            Joan Puig
            UPDATE: I found this on [http://www.oracle.com/technetwork/database/enterprise-edition/oracleolapnewsletter-sept07-087021.html]



            -----
            Does AW tablespace size or growth seem to be a problem? What can you do?

            1. check the dba_recylebin is purged and old excess AWs are deleted:

            As SYSDBA

            SQL> purge dba_recyclebin;

            2. Do you have large extent sizes for your tablespace? Each object in the AW uses an extent, and there's lots of AW objects so maybe the extents are too large taking up the space. Use the defaults for Extent Management when creating the tablespaces:

            SQL>create tablespace... EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1024K
            SEGMENT SPACE MANAGEMENT AUTO; for temp tablespaces, and

            SQL>create tablespace... EXTENT MANAGEMENT LOCAL AUTOALLOCATE
            SEGMENT SPACE MANAGEMENT AUTO; for permanent tablespaces.

            3. Are you continually updating the same AW? Maybe the generations aren't releasing space. You can release some of this space manually by running OLAP DML in 10.2 as follows:

            In OLAP Worksheet:

            AW attach rwx
            define my_var variable int
            make a trivial change e.g. my_var=2
            Update
            Commit
            Repeat steps 3,4 and 5 twice more

            -----


            I've tried nº 3, which seems to describe the problem, but the solution didn't work.

            Joan
            • 3. Re: 11.2.0.2 AW size grows steadily with every cube build
              CBessios
              1. I don't use recycle_bin in this instance
              2. I don't see any problem in the extend sizes of the tablespace
              3. This indeed works for 10.2 but I don't think it has any effect in 11.2

              Actually I have opened an SR with the support team to investigate. Let's see.

              Chris
              • 4. Re: 11.2.0.2 AW size grows steadily with every cube build
                Nasar-Oracle
                Chris,
                To some extent this is expected behavior. See an old posting here explaining this behavior in 9.2 OLAP
                Re: AW size grows, grows, grows and stops

                Maybe someone from OLAP Development can shed more light on this behavior in 11.2 version.

                If there are hierarchy changes happening, then instead of just re-aggregating data, maybe you can use that "window" to delete/re-create AW at that time also.
                This will help with any fragmentation as well as keep the size of AW to minimum.


                Joan,
                See an old post by David Greenfield about Cost-based aggreation and how much to precompute.
                Question on cube build / query performance (11.2.0.3)



                .

                Edited by: Nasar Ali-Khan on Oct 9, 2012 5:47 PM
                • 5. Re: 11.2.0.2 AW size grows steadily with every cube build
                  CBessios
                  Nasar,

                  Thank you for your answer. I have read this article and I understand the way olap create generations and then reuse free pages.

                  In my testcase thought, I have no real readers. I initially build the cube. Then for each subsequent build I clear values first (I beleive the dbms_cube.build(... (clear values)) just set variables to NA) and then load again the same data. In the test case I did this 10 times and I had a constant increase in the size.

                  In my real cube, which consist of the same dataset but with daily small additions to the data, the workspace has already reached a size of 55GB while it should have been less than 1GB. The cube is cleared and loaded 5 times a day. It seems to me, that after clearing values that pages are never reused. That's why I consider this as a problem.

                  I have other cubes that I do incremental builds and there the size grows logically along with the increase of the original data (I don't see any sizing problem with incremental builds). The problem I see is with the full build with clear values.

                  Chris
                  • 6. Re: 11.2.0.2 AW size grows steadily with every cube build
                    Jim Carey-Oracle
                    A couple questions about the tablespace this AW is stored in--is it ASSM or MSSM? Note I'm not asking about whether you are using file-system files or ASM; that's different. The difference is in the type of LOBs we use for storage.

                    How big is the tablespace you have the cube in? How are you determining the size of the AW?

                    What I'm thinking is that for "basic file" lobs (the kind you get with MSSM tblspaces), the lob code prefers to allocate new space rather than re-use old space. I'm not sure about "secure file" lobs (ASSM tblspaces). If the tblspace is MSSM, try doing
                    ALTER TABLE <schema>.AW$<awname> REBUILD FREEPOOLS
                    That will reclaim the obsolete space from the lobs. The documentation claims that it is not necessary for "secure file" lobs.

                    Jim
                    • 7. Re: 11.2.0.2 AW size grows steadily with every cube build
                      CBessios
                      Jim thank you for your reply.

                      I'm not aware how to get the information concerning ASSM or MSSM. What I can tell you is that I created initially an olap tablespace using:

                      create tablespace olap_tbs
                      datafile '+DATA/bidw/datafile/olap_tbs.01'
                      size 5G reuse
                      autoextend on next 200M
                      maxsize unlimited
                      extent management local
                      segment space management auto;

                      In this tablespace I have various analytic workspaces. I determine the size of each aw using (I got this from AWM reports):

                      SELECT dbal.owner
                      ||'.'
                      ||SUBSTR(dbal.table_name,4) awname,
                      SUM(dbas.bytes)/1024/1024 AS mb,
                      dbas.tablespace_name
                      FROM dba_lobs dbal,
                      dba_segments dbas
                      WHERE dbal.column_name = 'AWLOB'
                      AND dbal.segment_name = dbas.segment_name
                      GROUP BY dbal.owner,
                      dbal.table_name,
                      dbas.tablespace_name
                      ORDER BY dbal.owner,
                      dbal.table_name ;

                      I tried your suggestion but it says:

                      ALTER TABLE OLAPCOL3.AW$COL_V3 REBUILD FREEPOOLS;

                      Error starting at line 3 in command:
                      ALTER TABLE OLAPCOL3.AW$COL_V3 REBUILD FREEPOOLS
                      Error report:
                      SQL Error: ORA-01735: invalid ALTER TABLE option
                      01735. 00000 - "invalid ALTER TABLE option"
                      *Cause:   
                      *Action:

                      Chris
                      • 8. Re: 11.2.0.2 AW size grows steadily with every cube build
                        Joan Puig
                        Hello Chris, Jim

                        As you can see in the response from David Greenfield in the following threat and in the SecureFile LOBs documentation, the syntax to rebuild freepools is a little bit different:

                        alter table aw$<AW name> modify lob (awlob) (rebuild freepools);

                        Sources:
                        Reclaiming space in OLAP 11.1.0.7
                        http://docs.oracle.com/cd/E11882_01/appdev.112/e18294/adlob_smart.htm#autoId27



                        However, when I tried to execute it I got the following error:

                        Error starting at line 15 in command:
                        alter table aw$XXX_aw modify lob (awlob) (rebuild freepools)
                        Error report:
                        SQL Error: ORA-60016: Operation not supported on SECUREFILE segment


                        Which contrasts with what the documentation says.

                        I hope it helps.

                        Joan
                        • 9. Re: 11.2.0.2 AW size grows steadily with every cube build
                          CBessios
                          Hi Joan,

                          I have exactly the same error trying to alter my table as well. I don't know if we are in the right direction, I mean trying to free pools from the lob, or we should look at the OLAP side (build with clear values).

                          Chris
                          • 10. Re: 11.2.0.2 AW size grows steadily with every cube build
                            CBessios
                            Jim I researched a little what you said. Indeed I use a ASSM tablespace. I examined the lob column (securefiles) of the table using dbms_space.space_usage (I found an interesting article here http://www.oracle.com/technetwork/articles/sql/11g-securefiles-084075.html).

                            The results are that on the LOB SUBPARTITIONS I have:

                            Used bytes: 1.193MB
                            Expired bytes: 338MB

                            Problem is I'm not very familiar with LOB storage details (what exactly are expired bytes and how you reclaim the space).

                            Thank you.

                            Chris

                            Edited by: CBessios on Oct 12, 2012 12:26 AM
                            • 11. Re: 11.2.0.2 AW size grows steadily with every cube build
                              Joan Puig
                              Hi Chris, Experts,

                              Yesterday I did some more research about the topic. I stumbled upon the RETENTION clause, which seemed to be the perfect candidate for the root of our problems. After digging a little more, I tried it with the "classic" ALTER TABLE ... MODIFY LOB, but I got an ORA-60016 again.

                              -----------

                              alter table aw$xxx_aw
                              modify lob (awlob)
                              (retention none);

                              ...
                              SQL Error: ORA-60016: Operation not supported on SECUREFILE segment

                              -----------

                              Then someone recommended me the SHRINK SPACE clause, which I tried using the same statement again, but it yielded another error:

                              -----------

                              Error starting at line 1 in command:
                              alter table aw$xxx_aw
                              modify lob (awlob)
                              (shrink space)

                              Error report:
                              SQL Error: ORA-10635: Invalid segment or tablespace type
                              10635. 00000 - "Invalid segment or tablespace type"
                              *Cause:    Cannot shrink the segment because it is not in auto segment space
                              managed tablespace or it is not a data, index or lob segment.
                              *Action:   Check the tablespace and segment type and reissue the statement

                              ------------

                              Looking at USER_SEGMENTS, I can see that the table subpartitions for my AW$XXX_AW table have ASSM as segment subtype, while the LOB subpartitions have SECUREFILE as segment subtype.

                              The "good" news are that there are two commands that did work: DEDUPLICATE and COMPRESS. The results would be very promising if those "Expired Bytes" were reclaimed. I'll post them, as they might give another example of the tablespace growth. These results correspond to the biggest of the lob subpartitions I had at the start.

                              ------------

                              *"Initial state"*
                              segment_size_blocks => 533248
                              segment_size_bytes => 4368367616
                              used_blocks => 501824
                              used_bytes => 4110942208
                              expired_blocks => 30736
                              expired_ MegaBytes => 240,125 -----> l_expired_bytes/1024/1024 = expired_ MegaBytes
                              unexpired_blocks => 0
                              unexpired_bytes => 0

                              *"After DEDUPLICATE"*
                              segment_size_blocks => 1003904 -----> Note how the disk space almost doubles
                              segment_size_bytes => 8223981568
                              used_blocks => 500816 -----> While used space is only reduced by a little
                              used_bytes => 4102684672
                              expired_blocks => 501857 -----> And expired space  explodes in size
                              expired_ MegaBytes => 3920,7578125
                              unexpired_blocks => 0
                              unexpired_bytes => 0

                              *"After COMPRESS MEDIUM"*
                              segment_size_blocks => 1003904 -----> In this case disk space remains the same
                              segment_size_bytes => 8223981568
                              used_blocks => 70006 -----> While used space drops dramatically
                              used_bytes => 573489152
                              expired_blocks => 932667 -----> So expired space grows again as a result
                              expired_ MegaBytes => 7286,4609375
                              unexpired_blocks => 0
                              unexpired_bytes => 0

                              -----------------------

                              I'm sorry for this LONG post, but in my opinion every bit of information is useful. I really think that this last example really shows what the problem is and, most importantly, why is it a problem.

                              Joan

                              P.S.: I have checked that the Analytic Workspace has no readers before every test.
                              • 12. Re: 11.2.0.2 AW size grows steadily with every cube build
                                DavidGreenfield
                                Please try setting the "atomic refresh" parameter to FALSE. e.g.
                                begin
                                 DBMS_CUBE.BUILD(
                                 'OLAPCOL3.EXPACT USING (CLEAR VALUES, LOAD, SOLVE)',
                                 'C', -- refresh method
                                 false, -- refresh after errors
                                 1, -- parallelism
                                 false, -- atomic refresh
                                 true, -- automatic order
                                 false); -- add dimensions
                                
                                exception
                                 when others then
                                 rollback;
                                raise;
                                You will still find a growth of around 3X from the original AW size, but after that is should stop growing. I believe this is a bug and will be following up with OLAP development on the issue.
                                • 13. Re: 11.2.0.2 AW size grows steadily with every cube build
                                  CBessios
                                  Dear David,

                                  I have started SR 3-6295848241 with OLAP dev team for this issue.

                                  In their recent update (today) they tell me that the issue can be reproduced and the development team is working on that (SR state is waiting for internal answer).

                                  Anyway, I will delete the AW, recreate it and try to build it without "atomic refresh". I will let you know very soon the results.

                                  Thank you very much.

                                  Chris
                                  • 14. Re: 11.2.0.2 AW size grows steadily with every cube build
                                    DavidGreenfield
                                    Hi Chris,

                                    I had reviewed your SR and was the person who reproduced it from your (excellent!) test case. I then reproduced the same problem on an internal schema and was able to determine that the atomic flag made a difference. Oddly this problem (with the atomic flag) seems to happen only in 11.2.0.2, not in 11.2.0.3. Specifically I don't see the growth in AW size on 11.2.0.3. So Joan (JPuig) must be facing another issue. Let us know if turning off the atomic build resolves the issue for you. We are also looking for a real fix for the problem.

                                    David
                                    1 2 Previous Next