Procedure/Function: Unterschied zwischen den Versionen
Aus MeinWiki
(→Statistic aktualisieren) |
(→Statistic aktualisieren) |
||
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; | ||
+ | |||
+ | |||
create or replace PROCEDURE Call_Statistic_Table | create or replace PROCEDURE Call_Statistic_Table | ||
(p_schema dba_users.username%type) | (p_schema dba_users.username%type) |
Version vom 13. Juli 2016, 06:07 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;
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; /