This discussion is archived
4 Replies Latest reply: Jun 22, 2011 2:07 AM by 869980 RSS

Reclaiming space in OLAP 11.1.0.7

869980 Newbie
Currently Being Moderated
Hi,
I have been trying to understand how OLAP cube loads consume space and how can we free up space used by OLAP AW objects in DB version 11.1.0.7. I have the following questions.

1.     During the loading process a lot of data tablespace is apparently is being used as TEMP, since I observe that the data space is consumed while a cube build is going on but the free space in the tablespace increases once the load is over.
2.     It is very difficult to reclaim space used by an AW. I tried deleting cubes but it does not work. The only possible way that I have figured out is to drop and recreate the complete AW using the xmls. Is there a better way?
3.     At times incremental data loads take up more space than the full loads. Does this mean the old data is still not deleted?

Thanks in Advance.
  • 1. Re: Reclaiming space in OLAP 11.1.0.7
    DavidGreenfield Expert
    Currently Being Moderated
    One of the quirks of Analytic Workspaces is that many versions, or generations, can exist at once. This is very powerful, especially for "what if?" analysis, but these generations do take up space. A particular AW generation will last as long as there is a client attached to it or if it is one of the last three generations. The following code can be used to flush out some memory by forcing the creation of three new (small) generations. The AW in this case is GLOBAL, so you would need to adjust accordingly.
    set serverout on size unlimited
    GLOBAL > select round(sum(DBMS_LOB.GETLENGTH(AWLOB))/1024,0) kb from aw$global;
    
         KB
    ----------
        167699
    
    
    GLOBAL > declare 
    2  aw_name varchar2(30);
    3  begin 
    4   aw_name := 'GLOBAL';
    5   dbms_aw.execute('aw attach ' || aw_name || ' rwx;define junkvar int;update;commit');
    6   for i in 1..3 loop 
    7     dbms_aw.execute('aw reattach ' || aw_name || ' rwx;junkvar=junkvar+1;update;commit');
    8   end loop; 
    9   dbms_aw.execute('delete junkvar;update;commit;aw detach ' || aw_name || '');
    10 end;
    11 /
    
    PL/SQL procedure successfully completed.
    
    GLOBAL > select round(sum(DBMS_LOB.GETLENGTH(AWLOB))/1024,0) kb from aw$global;
    
         KB
    ----------
        147019
    Another useful trick is to call rebuild freepools. This is done automatically in 11.2, but you would need to do it yourself in 11.1.0.7.
    alter table aw$global modify lob (awlob) (rebuild freepools);
    Here is a quote from the Oracle documentation. Note that you should not run this if you are using SECUREFILES.
    The REBUILD FREEPOOLS clause removes all the old data from the LOB column. This clause is useful only if you reverting to PCTVERSION for management of LOBs. You might want to do this to manage older data blocks.
    Even after all this there is still an unadvoidable growth of the AW$ table. As a rule of thumb you can expect the table to grow three times its size after its original load. This is mostly a side effect of using LOBS to store the AW since the LOB subsystem doesn't like giving up memory once it has obtained it.
    2. It is very difficult to reclaim space used by an AW. I tried deleting cubes but it does not work. The only possible way that I have figured out is to drop and recreate the complete AW using the xmls. Is there a better way?
    This is a bug and was discussed (and indeed discovered) in a recent thread: Releasing Storage space occupied by Cube
    3. At times incremental data loads take up more space than the full loads. Does this mean the old data is still not deleted?
    Incremental data loads, or more specifically inremental aggregations, can take existing lists of child nodes and extend them. This can lead to fragmentation similar to a fragmented disk drive. After a complete load the child lists will be stored together again. This is, incidentally, why incremental performance can degrade over time and why a periodic complete aggregation can improve performance.
  • 2. Re: Reclaiming space in OLAP 11.1.0.7
    869980 Newbie
    Currently Being Moderated
    Thanks David..
    This was really helpful!
    Also wanted to ask you, if is there a way to truncate and reload a cube to ensure that it goes for a full load and not a differential one?
    I need this as in my case the cube is very small (<500 MB) and loads within seconds if I drop and reimport the XML, but it goes on for hours if I try to load with some data already present in it.

    Thanks,
    Abhishek
  • 3. Re: Reclaiming space in OLAP 11.1.0.7
    DavidGreenfield Expert
    Currently Being Moderated
    To truncate a cube, SALES_CUBE say, you can execute the following.
    exec dbms_cube.build('SALES_CUBE USING(CLEAR)', add_dimensions=>false, parallelism=>X)
    To truncate a table and then reload in one operation you can say
    exec dbms_cube.build('SALES_CUBE USING(CLEAR, LOAD, SOLVE)', add_dimensions=>false, parallelism=>X)
    This is related to the notion of 'build script' in AWM. The AWM default is "LOAD, SOLVE", which means the cube does not get truncated before the cube is rebuilt. The result of this choice is that all AWM cubes are aggregated incrmentally.

    To change the default build using PL/SQL you could create the following procedure
    CREATE OR REPLACE PROCEDURE SET_CUBE_BUILD 
    (
      CUBE_NAME VARCHAR2,
      SCRIPT VARCHAR2,
      CUBE_OWNER VARCHAR2 DEFAULT USER
    ) AUTHID CURRENT_USER
    AS
      xmlvc VARCHAR2(4000);
    BEGIN
      -- Construct the XML
      xmlvc := '
    <Metadata Version="1.0">
      <Cube Name="' || CUBE_NAME || '" Owner="' || CUBE_OWNER || '">
        <Organization>
          <AWCubeOrganization>
            <DefaultBuild>
              <![CDATA[BUILD SPEC SYS_DEFAULT( ' || SCRIPT || ')]]>
            </DefaultBuild>
          </AWCubeOrganization>
        </Organization>
      </Cube>
    </Metadata>';
      -- Import the XML
      DBMS_CUBE.IMPORT_XML(xmlvc);
    END;
    /
    Once this is defined you can change the script as follows.
    exec set_cube_build('UNITS_CUBE', 'CLEAR, LOAD, SOLVE')
  • 4. Re: Reclaiming space in OLAP 11.1.0.7
    869980 Newbie
    Currently Being Moderated
    Thanks, Very Helpful!

    Regards,
    Abhishek

Legend

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