Prepočítanie štatistík - Oracle

Medzi základné prvky optimalizácie v rámci Oracle, ale aj medzi inými databázovými systémami, patrí pravidelné prepočítavanie štatistík nad databázovými objektami. Štatistiky tvoria neoddeliteľnú súčasť práce administrácie databáz, lebo ak sa by sa nad tabuľkovými objektami, v ktorých sa uskutočňujú DML operácie, nerobili, neboli by aktuálne primárne kľúče a indexi a tak by DB systém navrhoval nesprávny exekučný plán, čoho dôsledkom by mohlo byť radikálne spomalenie SQL dopytov a tým následné spomalenie výkonu databázy.

Vzhľadom na dôležitosť štatistík, niektoré databázové systémy majú defaultne nastavené určité prepočítavanie štatistík na dennej alebo týždennej báze kedy sa predpokladá najmenšie vyťaženie databázy čo sa však nedá povedať o Oracle, ktorému treba explicitne definovať prepočítavanie štatistík. Preto nesprávne nastavenie prepočítavania zo strany administrátorov databáz (ďalej DBA) môže z dlhodobého hľadiska znamenať kolaps databázových systémov a ich naviazaných aplikácií preto je dôležité sa tomu venovať. (Napríklad postgres to už vyriešilo dennými automatickými vacuum jobmi a aktuálnymi údajmi.)

Čo spôsobuje problémy

Neprepočítané štatistiky vznikajú väčšinou nedokončením prepočtov štatistík v už explicitne definovanom prevažne víkendovom jobe alebo neplánovaným reštartom databáz,kedy databázový systém nemá podľa čoho vytvoriť optimalizovaný exekučný plán. Väčšinou problém je v tom, že veľké tabuľky a ich partície vyžadujú častejšie prepočítavanie štatistík a explicitne nastavenie histogramu pre presné percento prepočítania štatistík v tabuľkových objektoch.

Základná kontrola

1. Zistíme či máme už job na prepočítavanie štatistík

--pod sys uctom
 
SELECT job_name, enabled
 
FROM dba_scheduler_jobs
 
WHERE job_name = 'GATHER_STATS_JOB'

2. ak ano, pozrieme či beží správne, ak nie alebo ho nemáme nastavený, nastavíme ho na dennej báze alebo počas maitenance window(toto sa odporúča)
• DENNE

BEGIN 
DBMS_SCHEDULER.create_job ( job_name => 'GATHER_STATS_JOB', 
 
job_type => 'PLSQL_BLOCK', 
 
job_action => 'BEGIN DBMS_STATS.GATHER_DATABASE_STATS; END;'
 
, start_date => SYSTIMESTAMP, 
 
repeat_interval => 'FREQ=DAILY; BYHOUR=2;', enabled => TRUE ); 
 
END;

• MAITENANCE

BEGIN 

 DBMS_SCHEDULER.create_job ( job_name => 'GATHER_STATS_JOB', 

 job_name        => 'GATHER_STATS_JOB',
  
   job_type        => 'PLSQL_BLOCK',
   
  job_action      => 'BEGIN DBMS_STATS.GATHER_DATABASE_STATS; END;',
   
  schedule_name   => 'MAINTENANCE_WINDOW',
   
  enabled         => TRUE)
END;

3. Ak sme ho nastavili, kontrolujeme procesovanie prvý týždeň a ak beží správne bez chýb, štatistiky máme na nejaký čas vybavené a zčasu na čas kontrolujeme čas procesu

SELECT job_name, status, actual_start_date, run_duration

 FROM dba_scheduler_job_run_details

 WHERE job_name = 'GATHER_STATS_JOB'

Môžeme sa rozhodnúť vytvoriť aj job len pre niektoré schémy, tabuľky alebo vytvoríme customizovanú procedúru. Pre schemy to vyzerá nasledovne:

BEGIN 
DBMS_SCHEDULER.create_job ( job_name => 'GATHER_STATS_JOB', 
 
job_type => 'PLSQL_BLOCK', 
 
job_action => 'BEGIN DBMS_STATS.GATHER_SCHEMA_STATS('SchemaName'); END;'
 
, start_date => SYSTIMESTAMP, 
 
repeat_interval => 'FREQ=DAILY; BYHOUR=2;', enabled => TRUE ); 
 
END;

BEGIN 
DBMS_SCHEDULER.create_job ( job_name => 'GATHER_STATS_JOB', 
 
job_type => 'PLSQL_BLOCK', 
 
job_action => 'BEGIN DBMS_STATS.GATHER_TABLE_STATS('SH',‘SALES’, cascade=>true);DBMS_STATS.GATHER_TABLE_STATS('SH',‘SALES’, cascade=>true);DBMS_STATS.GATHER_TABLE_STATS('SH',‘SALES’, cascade=>true); END;'
 
, start_date => SYSTIMESTAMP, 
 
repeat_interval => 'FREQ=DAILY; BYHOUR=2;', enabled => TRUE ); 
 
END;

BEGIN 
DBMS_SCHEDULER.create_job ( job_name => 'GATHER_STATS_JOB', 
job_type => 'PLSQL_BLOCK', 
job_action => '
declare
    p_est_percent INTEGER;
    p_count       integer;
    p_table_name  varchar(30);
    p_check_queue integer;
BEGIN
    for curr in (
        SELECT M.Table_name,
               T.Num_Rows,
               T.Last_Analyzed,
               M.Inserts,
               M.Deletes,
               M.Updates,
               CASE
                   WHEN NVL(T.Num_Rows, 0) = 0 THEN 100
                   ELSE 100 * (M.Inserts + M.Deletes) / T.Num_Rows END estimate_percent
        FROM dba_Tab_Modifications M
                 JOIN dba_Tables T ON T.Table_name = M.Table_name
                 LEFT OUTER JOIN dba_Queue_Tables Q ON Q.Queue_table = T.Table_Name
        WHERE Q.Queue_Table IS NULL          -- Never Queue tables
          AND TO_char(trunc(m.TIMESTAMP),'YYYY-MM-DD') = TO_char(sysdate, 'YYYY-MM-DD') --only current date
         AND (
            (
                (
                    T.Last_Analyzed IS NULL -- Always when all tables requested and statistics were not yet gathered
                        OR
                    (
                        M.Inserts + M.Deletes> 0
                            AND 
                        CASE
                            WHEN (M.Inserts + M.Deletes) > T.Num_Rows THEN
                        t.sample_size  / (M.Inserts + M.Deletes)
                    ELSE
                        (M.Inserts + M.Deletes) / T.Num_Rows
                 END >=
                        5
                        )
                    )
                )
            )

          and t.owner = 'SCHEMA_NAME'
        ORDER BY M.Table_Name)
        loop

          --set 1 if table is queued and skip step
            begin
                select 1 into p_check_queue from dba_Queue_Tables Q where queue_table = curr.Table_name;
            exception
                when others then null;
            end;


            continue when p_check_queue = 1;

            begin
                p_est_percent := case when curr.estimate_percent > 100 then 100 else curr.estimate_percent end;zh


                if curr.partition_name is null then
                    DBMS_STATS.GATHER_TABLE_STATS('DW_DATA', curr.table_name,
                                                  estimate_percent => p_est_percent, cascade => true);
                    p_table_name := curr.table_name;
                elsif curr.partition_name is not null and p_table_name <> curr.table_name then
                    DBMS_STATS.GATHER_TABLE_STATS('DW_DATA', curr.table_name, partname => curr.partition_name,
                                                  estimate_percent => p_est_percent,  cascade => false);
                end if;
            exception
                when others then
                    continue;
            end;
        end loop;
end', start_date => SYSTIMESTAMP, repeat_interval => 'FREQ=DAILY, BYHOUR=2', enabled => TRUE ); 
END;

Ak nechceme joby, odsledujeme štatistiky manuálne

select t.table_name,num_rows,inserts,deletes,updates,last_analyzed,timestamp from dba_tables t
join dba_tab_modifications td on td.table_name=t.table_name
where owner='SH'