This content has been marked as final. Show 4 replies
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.
Another useful trick is to call rebuild freepools. This is done automatically in 11.2, but you would need to do it yourself in 18.104.22.168.
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
Here is a quote from the Oracle documentation. Note that you should not run this if you are using SECUREFILES.
alter table aw$global modify lob (awlob) (rebuild freepools);
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.
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.
To truncate a cube, SALES_CUBE say, you can execute the following.
To truncate a table and then reload in one operation you can say
exec dbms_cube.build('SALES_CUBE USING(CLEAR)', 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.
exec dbms_cube.build('SALES_CUBE USING(CLEAR, LOAD, SOLVE)', add_dimensions=>false, parallelism=>X)
To change the default build using PL/SQL you could create the following procedure
Once this is defined you can change the script as follows.
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; /
exec set_cube_build('UNITS_CUBE', 'CLEAR, LOAD, SOLVE')