-
Notifications
You must be signed in to change notification settings - Fork 4
/
Copy pathcreate_all_statistic.sql
145 lines (114 loc) · 4.76 KB
/
create_all_statistic.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
--==============================================================================
-- GPI - Gunther Pippèrr
-- Desc: recreate the statistic of a database
-- Date: 01.2013
-- Doku: http://www.pipperr.de/dokuwiki/doku.php?id=dba:statistiken
--
-- Src:
-- http://psoug.org/reference/system_stats.html
-- http://docs.oracle.com/cd/E11882_01/server.112/e40402/initparams040.htm#REFRN10023
-- http://docs.oracle.com/cd/E18283_01/server.112/e16638/stats.htm
--==============================================================================
-----------------------------------------------
set timing on
set serveroutput on
set linesize 256
set pagesize 200
set echo on
set serveroutput on
define degree = "1"
-----------------------------------------------
column last format a14
column owner format a20
column table_name format a30
spool recreate_stat.log
select count (*), owner, to_char (LAST_ANALYZED, 'dd.mm.yyyy') as last
from dba_tables
group by owner, to_char (LAST_ANALYZED, 'dd.mm.yyyy'), to_char (LAST_ANALYZED, 'YYYYDDMM')
order by owner, to_char (LAST_ANALYZED, 'YYYYDDMM') desc
/
-----------------------------------------------
-- delete all old statistics if necessary
--
--exec DBMS_STATS.DELETE_DATABASE_STATS;
--exec DBMS_STATS.DELETE_DICTIONARY_STATS;
--exec DBMS_STATS.DELETE_FIXED_OBJECTS_STATS;
-----------------------------------------------
-----------------------------------------------
-- gather first system stats over the io of the creation of the system statistic
exec DBMS_STATS.gather_system_stats('Start');
-----------------------------------------------
--
exec DBMS_STATS.gather_fixed_objects_stats;
-----------------------------------------------
--
exec DBMS_STATS.GATHER_DICTIONARY_STATS (estimate_percent => 100, degree => &°ree ,options => 'GATHER')
-----------------------------------------------
--
declare
cursor c_owner
is
select owner
from dba_tables
where owner not in ('SYS', 'SYSTEM', 'XDB')
-- System User statitiken anlegen?
-- and owner not in ('MDSYS','SI_INFORMTN_SCHEMA','ORDPLUGINS','ORDDATA','ORDSYS','EXFSYS','XS$NULL','CTXSYS','WMSYS','APPQOSSYS','DBSNMP','ORACLE_OCM','DIP','OUTLN','FLOWS_FILES','OLAPSYS','OWBSYS','OWBSYS_AUDIT')
group by owner;
v_parallel number := &°ree;
begin
dbms_output.put_line (
'-- Info Start Anlegen der neuen Statisiken für die DB User um ::' || to_char (sysdate, 'dd.mm.yyyy hh24:mi'));
dbms_output.put_line ('-----------------------');
for rec in c_owner
loop
dbms_output.put_line (
'-- Info Starte das Anlegen der Statisik für den User ::'
|| rec.owner
|| ' um ::'
|| to_char (sysdate, 'dd.mm.yyyy hh24:mi'));
if rec.owner not in ('MAIN_USER')
then
-- keine histogramme
dbms_stats.gather_schema_stats (ownname => rec.owner
, options => 'GATHER'
, estimate_percent => dbms_stats.auto_sample_size
, cascade => true
, degree => v_parallel);
else
-- Mit Histogrammen
dbms_stats.gather_schema_stats (ownname => rec.owner
, cascade => true
, estimate_percent => dbms_stats.AUTO_SAMPLE_SIZE
, Block_Sample => false
, degree => v_parallel
, no_invalidate => true
, granularity => 'ALL'
, method_opt => 'FOR ALL COLUMNS SIZE AUTO');
end if;
dbms_output.put_line (
'-- Info Anlegen der Statisik für den User ::'
|| rec.owner
|| ' beendet um ::'
|| to_char (sysdate, 'dd.mm.yyyy hh24:mi'));
dbms_output.put_line ('-----------------------');
end loop;
dbms_output.put_line ('-----------------------');
dbms_output.put_line (
'-- Info Anlegen der neuen Statisiken für die DB User um ::' || to_char (sysdate, 'dd.mm.yyyy hh24:mi') || ' beendet');
end;
/
-----------------------------------------
-- Falls etwas fehlt
exec DBMS_STATS.GATHER_DATABASE_STATS (degree=>&°ree,options=>'GATHER AUTO' );
-----------------------------------------
-- end system statistic
execute DBMS_STATS.gather_system_stats('Stop');
------------------------------------------
select count (*), owner, to_char (LAST_ANALYZED, 'dd.mm.yyyy') as last
from dba_tables
group by owner, to_char (LAST_ANALYZED, 'dd.mm.yyyy'), to_char (LAST_ANALYZED, 'YYYYDDMM')
order by owner, to_char (LAST_ANALYZED, 'YYYYDDMM') desc
/
spool off;
set echo off
set timing off