This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Oct 17, 2012 10:45 PM by CBessios RSS

11.2.0.2 AW size grows steadily with every cube build

CBessios Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points