RDBMS ORACLE- Stale Statistics II

Di seguito alcune query utili per individuare tabelle che hanno statistiche di tipo STALE e verificare la percentuale di movimentazione dei record avvenuta nelle tabelle stesse. Di seguito un semplice script per rieseguire le statistiche.

• Tabelle non partizionate

WITH 
  DT AS (SELECT * FROM DBA_TABLES WHERE OWNER =v_owner AND NUM_ROWS > 0 AND PARTITIONED='NO'),
 DTM AS (SELECT * FROM DBA_TAB_MODIFICATIONS WHERE TABLE_OWNER=v_owner AND PARTITION_NAME IS NULL),
 DTS AS (SELECT * FROM DBA_TAB_STATISTICS WHERE OWNER=v_owner AND STALE_STATS='YES' AND PARTITION_NAME IS NULL )             
          SELECT 'TABLE_NAME' TYPE,DT.OWNER,
                 DT.TABLE_NAME,TO_CHAR(NULL) PARTITION_NAME, TO_CHAR(NULL) SUBPARTITION_NAME,
                 ROUND ((DTM.DELETES + DTM.UPDATES + DTM.INSERTS) / DT.NUM_ROWS * 100)  PERCENTAGE,
                 DTS.STALE_STATS, DTS.LAST_ANALYZED
          FROM   DT, DTM, DTS
          WHERE      DT.OWNER = DTM.TABLE_OWNER
                 AND DT.OWNER = DTS.OWNER
                 AND DT.TABLE_NAME = DTM.TABLE_NAME
                 AND DT.TABLE_NAME = DTS.TABLE_NAME

• Tabelle partizionate

 WITH 
  DT AS (SELECT * FROM DBA_TAB_PARTITIONS WHERE TABLE_OWNER =v_owner AND NUM_ROWS > 0),
 DTM AS (SELECT * FROM DBA_TAB_MODIFICATIONS WHERE TABLE_OWNER=v_owner AND SUBPARTITION_NAME IS NULL),
 DTS AS (SELECT * FROM DBA_TAB_STATISTICS WHERE OWNER=v_owner AND STALE_STATS='YES' AND SUBPARTITION_NAME IS NULL)
            SELECT 'PARTITION_NAME' TYPE,DT.TABLE_OWNER,
                   DT.TABLE_NAME,DT.PARTITION_NAME,TO_CHAR(NULL) SUBPARTITION_NAME,
                   ROUND ((DTM.DELETES + DTM.UPDATES + DTM.INSERTS) / DT.NUM_ROWS * 100)  PERCENTAGE,
                   DTS.STALE_STATS, DTS.LAST_ANALYZED
            FROM   DT, DTM, DTS
            WHERE      DT.TABLE_OWNER = DTM.TABLE_OWNER
                   AND DT.TABLE_OWNER = DTS.OWNER
                   AND DT.TABLE_NAME = DTM.TABLE_NAME
                   AND DT.TABLE_NAME = DTS.TABLE_NAME
                   AND DT.PARTITION_NAME=DTM.PARTITION_NAME
                   AND DT.PARTITION_NAME=DTS.PARTITION_NAME

•  Tabelle SottoPartizionate

 WITH 
  DT AS (SELECT * FROM DBA_TAB_SUBPARTITIONS WHERE TABLE_OWNER =v_owner AND NUM_ROWS > 0),
 DTM AS (SELECT * FROM DBA_TAB_MODIFICATIONS WHERE TABLE_OWNER=v_owner AND SUBPARTITION_NAME IS NOT NULL ),
 DTS AS (SELECT * FROM DBA_TAB_STATISTICS WHERE OWNER=v_owner AND STALE_STATS='YES' AND SUBPARTITION_NAME IS NOT NULL )
            SELECT 'SUBPARTITION_NAME' TYPE,DT.TABLE_OWNER,DT.TABLE_NAME,DT.PARTITION_NAME,DT.SUBPARTITION_NAME,
                   ROUND ((DTM.DELETES + DTM.UPDATES + DTM.INSERTS) / DT.NUM_ROWS * 100)  PERCENTAGE,
                   DTS.STALE_STATS, DTS.LAST_ANALYZED
            FROM   DT, DTM, DTS
            WHERE      DT.TABLE_OWNER = DTM.TABLE_OWNER
                   AND DT.TABLE_OWNER = DTS.OWNER
                   AND DT.TABLE_NAME = DTM.TABLE_NAME
                   AND DT.TABLE_NAME = DTS.TABLE_NAME
                   AND DT.PARTITION_NAME=DTM.PARTITION_NAME
                   AND DT.PARTITION_NAME=DTS.PARTITION_NAME
                   AND DT.SUBPARTITION_NAME=DTM.SUBPARTITION_NAME
                   AND DT.SUBPARTITION_NAME=DTS.SUBPARTITION_NAME

 Il risultato ottenuto può essere inserito in un cursore per calcolare le singole statistiche.

declare
--- Variabili ------------------
v_cmd VARCHAR2(32000);
v_err VARCHAR2(4000);
....

begin


---- TABLES----  -------------------------------------------------------------
 for x in (
          <query 1>
  )loop

     IF x.PERCENTAGE >= 50 THEN
                      v_cmd:='BEGIN DBMS_STATS.GATHER_TABLE_STATS(...); END;';
                      execute immediate v_cmd using in x.owner,x.table_name;               
     END IF;
   end loop;
  
---- PARTITIONS  -------------------------------------------------------------

for y in (
           <query 2>
  )loop
     IF y.PERCENTAGE >= 50 THEN
                      v_cmd:='BEGIN DBMS_STATS.GATHER_TABLE_STATS(...); END;';
                      execute immediate v_cmd using in y.table_owner,y.table_name,y.partition_name;               
     END IF;
   end loop;

---- SUB PARTITIONS ----------------------------------------------------------

for z in (
            <query 3>
  )loop
     IF z.PERCENTAGE >= 50 THEN
                      v_cmd:='BEGIN DBMS_STATS.GATHER_TABLE_STATS(...); END;';
                      execute immediate v_cmd using in z.table_owner,z.table_name,z.partition_name;               
     END IF;
   end loop;


exception

       when others then
          v_err:='ERRORE => '||SQLERRM;
          --dbms_output.put_line(v_err);
          raise_application_error (-20000,'ERRORE  :'||v_err);  
  
end;