Demantra Table reorg

Demantra Performance Clustering Factor Out of Order Ratio TABLE_REORG CHECK_REORG (Doc ID 1594372.1)

How to check Demantra Performance DataBase and Worksheet Parameters (Doc ID 1356886.1)

*******TOP 10 TABLES in A SCHEMA*******

set lines 300

col OWNER for a35;

col SEGMENT_NAME for a45;

select

   *

from (

   select

      owner,

      segment_name,

      bytes/1024/1024 meg

   from

      dba_segments

   where

      segment_type = 'TABLE' and owner='XXDEM'

   order by

      bytes/1024/1024 desc)

where

   rownum <= 10;

  

********SIZE OF THE SCHEMA*********

SQL> SELECT sum(bytes) / 1024 / 1024 / 1024 as "Size in GB"

  FROM dba_segments

 WHERE owner = UPPER('&schema_name');  2    3

************GATHER STATS :**************

execute DBMS_STATS.GATHER_TABLE_STATS(ownname => 'XXDEM', tabname => 'SALES_DATA', estimate_percent=> 100, method_opt=>'for all columns size 1, for all indexed columns size auto', degree=> 16);

exec dbms_stats.GATHER_SCHEMA_STATS(OWNNAME=>'XXDEM', estimate_percent=>100 ,DEGREE=> 16);

***********STATS ANALYZED**************

set lines 300

col TABLE_NAME for a35;

select table_name, stale_stats, last_analyzed from dba_tab_statistics where table_name='&TABLE_NAME' and stale_stats='YES';

***********OBJECT SIZE**************

set lines 300;

col SEGMENT_NAME for a25;

select segment_name,segment_type,bytes/1024/1024 MB from dba_segments where segment_type='TABLE' and segment_name='&TABLE_NAME';

***********DEMANTRA TABLE REORG**************

àCapture Invalid objects list

àDrop old RDF tables

set pages 25

SELECT 'DROP TABLE "' || TABLE_NAME || '";' FROM user_tables where TABLE_NAME lIKE 'RDF%';

è  Choose RDF tables from the above list

BEGIN

 DBMS_REDEFINITION.ABORT_REDEF_TABLE (

   uname       => '&demantra_schema_name',

   orig_table  => '&original_table_name',

   int_table   => '&interim_table_name');

END;

/

@grant_table_reorg.sql

EXECUTE table_reorg.reorg(USER,UPPER('SALES_DATA'),UPPER('C'),20,32);

EXECUTE table_reorg.reorg(USER,UPPER('SALES_DATA'),UPPER('R'),20,32);

@revoke_table_reorg.sql

àRebuild indexes

select 'alter index '||owner||'.'||index_name ||' rebuild online nologging;'

 from dba_indexes where owner=upper('XXDEM');

set lines 300

 col INDEX_NAME for a45;

 select INDEX_NAME,DEGREE from dba_indexes where owner=upper('XXDEM') and TABLE_NAME='&TABLE_NAME';

ALTER INDEX DEGREE TO NOPARALLEL

select 'alter index '||owner||'.'||index_name ||' NOPARALLEL;' from dba_indexes where owner=upper('XXDEM') and TABLE_NAME='&TABLE_NAME';

Gather Stats on all the reorg tables