Procedure/Function: Unterschied zwischen den Versionen
Aus MeinWiki
(→Statistic aktualisieren) |
(→Statistic aktualisieren) |
||
Zeile 51: | Zeile 51: | ||
end; | end; | ||
/ | / | ||
+ | *Führt die beiden oberen Proceduren aus. | ||
+ | create or replace PROCEDURE Call_Statistic_USER | ||
+ | is | ||
+ | -- Ruft statistic für user aus dem Tablespace paridb* und users auf | ||
+ | -- KUE | ||
+ | v_username dba_users.username%Type; | ||
+ | cursor c_username is | ||
+ | select username from dba_users where default_tablespace != 'SYSTEM' and default_tablespace != 'SYSAUX' and default_tablespace != 'UNDOTBS1' and default_tablespace != 'TEMP'; | ||
+ | begin | ||
+ | open c_username; | ||
+ | loop | ||
+ | fetch c_username into v_username; | ||
+ | exit when c_username%NOTFOUND; | ||
+ | DBMS_OUTPUT.put_line ('PARAMETER = ' || v_username); | ||
+ | Call_Statistic_Table(v_username); | ||
+ | Call_Statistic_Index(v_username); | ||
+ | end loop; | ||
+ | close c_username; | ||
+ | end; | ||
+ | / | ||
+ | *Scheduler installieren. | ||
+ | begin | ||
+ | sys.dbms_scheduler.create_job(job_name => 'SYS."STATISTIC_USER_TABLE_INDEX"', | ||
+ | job_type => 'STORED_PROCEDURE', | ||
+ | job_action => 'Call_Statistic_USER', | ||
+ | start_date => to_date('28-02-2014 00:00:00', 'dd-mm-yyyy hh24:mi:ss'), | ||
+ | repeat_interval => 'Freq=Daily;Interval=1;ByHour=05;ByMinute=30', | ||
+ | end_date => to_date('31-12-2099 00:00:00', 'dd-mm-yyyy hh24:mi:ss'), | ||
+ | job_class => 'DEFAULT_JOB_CLASS', | ||
+ | enabled => true, | ||
+ | auto_drop => false, | ||
+ | comments => ''); | ||
+ | end; | ||
+ | / |
Version vom 23. Januar 2015, 09:00 Uhr
Procedure
Statistic aktualisieren
- Procedur zum Erstellen der Statistik für Tabellen.
create or replace PROCEDURE Call_Statistic_Table (p_schema dba_users.username%type) is -- Erstellt eine neue Statisitic von Tabellen -- KUE v_tablename dba_tables.table_name%Type; cursor c_tablename is select table_name from dba_tables where owner = p_schema; begin DBMS_OUTPUT.put_line ('PARAMETER = ' || p_schema) open c_tablename; loop fetch c_tablename into v_tablename; exit when c_tablename%NOTFOUND; begin dbms_stats.gather_table_stats(p_schema, v_tablename); exception when others then dbms_output.put_line('Error!' || p_schema ); end; end loop; close c_tablename; end; /
- Procedur zum Erstellen der Statistik für Indexe.
create or replace PROCEDURE Call_Statistic_Index (p_schema dba_users.username%type) is -- Erstellt eine neue Statisitic von Index -- KUE v_indexname dba_indexes.index_name%Type; cursor c_indexname is select index_name from dba_indexes where owner = p_schema; begin DBMS_OUTPUT.put_line ('PARAMETER = ' || p_schema); open c_indexname; loop fetch c_indexname into v_indexname; exit when c_indexname%NOTFOUND; begin dbms_stats.gather_index_stats(p_schema, v_indexname); exception when others then dbms_output.put_line('Error!' || p_schema ); end; end loop; close c_indexname; end; /
- Führt die beiden oberen Proceduren aus.
create or replace PROCEDURE Call_Statistic_USER is -- Ruft statistic für user aus dem Tablespace paridb* und users auf -- KUE v_username dba_users.username%Type; cursor c_username is select username from dba_users where default_tablespace != 'SYSTEM' and default_tablespace != 'SYSAUX' and default_tablespace != 'UNDOTBS1' and default_tablespace != 'TEMP'; begin open c_username; loop fetch c_username into v_username; exit when c_username%NOTFOUND; DBMS_OUTPUT.put_line ('PARAMETER = ' || v_username); Call_Statistic_Table(v_username); Call_Statistic_Index(v_username); end loop; close c_username; end; /
- Scheduler installieren.
begin sys.dbms_scheduler.create_job(job_name => 'SYS."STATISTIC_USER_TABLE_INDEX"', job_type => 'STORED_PROCEDURE', job_action => 'Call_Statistic_USER', start_date => to_date('28-02-2014 00:00:00', 'dd-mm-yyyy hh24:mi:ss'), repeat_interval => 'Freq=Daily;Interval=1;ByHour=05;ByMinute=30', end_date => to_date('31-12-2099 00:00:00', 'dd-mm-yyyy hh24:mi:ss'), job_class => 'DEFAULT_JOB_CLASS', enabled => true, auto_drop => false, comments => ); end; /