Procedure/Function: Unterschied zwischen den Versionen
Aus MeinWiki
(→Statistic aktualisieren) |
(→Statistic aktualisieren) |
||
(4 dazwischenliegende Versionen desselben Benutzers werden nicht angezeigt) | |||
Zeile 2: | Zeile 2: | ||
=== Statistic aktualisieren === | === Statistic aktualisieren === | ||
*Procedur zum Erstellen der Statistik für Tabellen. | *Procedur zum Erstellen der Statistik für Tabellen. | ||
− | + | ||
− | + | CREATE OR REPLACE PROCEDURE Call_Statistic_Table | |
− | + | (p_schema dba_users.username%type) | |
− | + | is | |
− | + | -- Funktion | |
− | + | -- 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. | *Procedur zum Erstellen der Statistik für Indexe. | ||
create or replace PROCEDURE Call_Statistic_Index | create or replace PROCEDURE Call_Statistic_Index | ||
Zeile 37: | Zeile 41: | ||
begin | begin | ||
DBMS_OUTPUT.put_line ('PARAMETER = ' || p_schema); | 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 => 'KUE' | ||
+ | ); | ||
end; | end; | ||
/ | / |
Aktuelle Version vom 13. Juli 2016, 06:11 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 -- Funktion -- 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 => 'KUE' ); end; /