Skip to content

Commit b1c902e

Browse files
nigelbaylissnigelbayliss
committed
nopart update
1 parent e00a09b commit b1c902e

File tree

2 files changed

+116
-21
lines changed

2 files changed

+116
-21
lines changed

‎optimizer/gather_stale/nopart.lst

Lines changed: 85 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -14,26 +14,46 @@ SQL> drop table stale_test1 purge;
1414

1515
Table dropped.
1616

17+
SQL> drop table stale_test2 purge;
18+
19+
Table dropped.
20+
1721
SQL>
1822
SQL> create table stale_test1 (col1 number(10));
1923

2024
Table created.
2125

26+
SQL> create table stale_test2 (col1 number(10));
27+
28+
Table created.
29+
2230
SQL>
2331
SQL> create index stale_test1_i on stale_test1(col1);
2432

2533
Index created.
2634

35+
SQL> create index stale_test2_i on stale_test2(col1);
36+
37+
Index created.
38+
2739
SQL>
2840
SQL> exec dbms_stats.set_table_prefs(user,'stale_test1','stale_percent','5')
2941

3042
PL/SQL procedure successfully completed.
3143

44+
SQL> exec dbms_stats.set_table_prefs(user,'stale_test2','stale_percent','5')
45+
46+
PL/SQL procedure successfully completed.
47+
3248
SQL>
3349
SQL> insert into stale_test1 values (1);
3450

3551
1 row created.
3652

53+
SQL> insert into stale_test2 values (1);
54+
55+
1 row created.
56+
3757
SQL> commit;
3858

3959
Commit complete.
@@ -43,19 +63,24 @@ SQL> exec dbms_stats.gather_table_stats(user,'stale_test1')
4363

4464
PL/SQL procedure successfully completed.
4565

66+
SQL> exec dbms_stats.gather_table_stats(user,'stale_test2')
67+
68+
PL/SQL procedure successfully completed.
69+
4670
SQL>
4771
SQL> PROMPT Table stats are not STALE
4872
Table stats are not STALE
4973
SQL> PROMPT
5074

5175
SQL> select table_name, last_analyzed,stale_stats
5276
2 from user_tab_statistics
53-
3 where table_name in ('STALE_TEST1')
77+
3 where table_name in ('STALE_TEST1','STALE_TEST2')
5478
4 order by 1,2;
5579

5680
TABLE_NAME LAST_ANALYZED STA
5781
------------------------- ------------------- ---
58-
STALE_TEST1 07:22:39 2018-03-16 NO
82+
STALE_TEST1 09:49:24 2018-03-20 NO
83+
STALE_TEST2 09:49:25 2018-03-20 NO
5984

6085
SQL>
6186
SQL> pause p...
@@ -70,6 +95,10 @@ SQL> exec dbms_stats.gather_table_stats(user,'stale_test1')
7095

7196
PL/SQL procedure successfully completed.
7297

98+
SQL> exec dbms_stats.gather_table_stats(user,'stale_test2')
99+
100+
PL/SQL procedure successfully completed.
101+
73102
SQL>
74103
SQL> PROMPT But even though the stats were not stale, statistics have been re-gathered
75104
But even though the stats were not stale, statistics have been re-gathered
@@ -79,12 +108,51 @@ SQL> PROMPT
79108

80109
SQL> select table_name,last_analyzed,stale_stats
81110
2 from user_tab_statistics
82-
3 where table_name in ('STALE_TEST1')
111+
3 where table_name in ('STALE_TEST1','STALE_TEST2')
112+
4 order by 1,2;
113+
114+
TABLE_NAME LAST_ANALYZED STA
115+
------------------------- ------------------- ---
116+
STALE_TEST1 09:49:29 2018-03-20 NO
117+
STALE_TEST2 09:49:29 2018-03-20 NO
118+
119+
SQL>
120+
SQL> pause p...
121+
p...
122+
123+
SQL>
124+
SQL> PROMPT Make STALE_TEST2 stale
125+
Make STALE_TEST2 stale
126+
SQL> PROMPT
127+
128+
SQL> insert into stale_test2 values (10);
129+
130+
1 row created.
131+
132+
SQL> insert into stale_test2 values (10);
133+
134+
1 row created.
135+
136+
SQL> commit;
137+
138+
Commit complete.
139+
140+
SQL>
141+
SQL> exec dbms_stats.flush_database_monitoring_info
142+
143+
PL/SQL procedure successfully completed.
144+
145+
SQL>
146+
SQL>
147+
SQL> select table_name,last_analyzed,stale_stats
148+
2 from user_tab_statistics
149+
3 where table_name in ('STALE_TEST1','STALE_TEST2')
83150
4 order by 1,2;
84151

85152
TABLE_NAME LAST_ANALYZED STA
86153
------------------------- ------------------- ---
87-
STALE_TEST1 07:22:43 2018-03-16 NO
154+
STALE_TEST1 09:49:29 2018-03-20 NO
155+
STALE_TEST2 09:49:29 2018-03-20 YES
88156

89157
SQL>
90158
SQL> pause p...
@@ -94,12 +162,14 @@ SQL>
94162
SQL> DECLARE
95163
2 filter_lst DBMS_STATS.OBJECTTAB := DBMS_STATS.OBJECTTAB();
96164
3 BEGIN
97-
4 filter_lst.extend(1);
165+
4 filter_lst.extend(2);
98166
5 filter_lst(1).ownname := user;
99167
6 filter_lst(1).objname := 'stale_test1';
100-
7 DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>user,obj_filter_list=>filter_lst,options=>'gather auto');
101-
8 END;
102-
9 /
168+
7 filter_lst(1).ownname := user;
169+
8 filter_lst(1).objname := 'stale_test2';
170+
9 DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>user,obj_filter_list=>filter_lst,options=>'gather auto');
171+
10 END;
172+
11 /
103173

104174
PL/SQL procedure successfully completed.
105175

@@ -114,21 +184,21 @@ SQL> PROMPT We used dbms_stats.gather_schema_stats(obj_filter_list=> ... , optio
114184
We used dbms_stats.gather_schema_stats(obj_filter_list=> ... , options=>'gather auto')
115185
SQL> PROMPT
116186

117-
SQL> PROMPT This time the non-stale table have been skipped
118-
This time the non-stale table have been skipped
187+
SQL> PROMPT This time the non-stale table has been skipped
188+
This time the non-stale table has been skipped
119189
SQL> PROMPT and statistics have only been gathered where there are stale stats.
120190
and statistics have only been gathered where there are stale stats.
121-
SQL> PROMPT You can see this because LAST_ANALYZED time has not changed.
122-
You can see this because LAST_ANALYZED time has not changed.
191+
SQL> PROMPT You can see if you consider the LAST_ANALYZED time.
192+
You can see if you consider the LAST_ANALYZED time.
123193
SQL> select table_name,last_analyzed,stale_stats
124194
2 from user_tab_statistics
125-
3 where table_name in ('STALE_TEST1')
195+
3 where table_name in ('STALE_TEST1','STALE_TEST2')
126196
4 order by 1,2;
127197

128198
TABLE_NAME LAST_ANALYZED STA
129199
------------------------- ------------------- ---
130-
STALE_TEST1 07:22:43 2018-03-16 NO
200+
STALE_TEST1 09:49:29 2018-03-20 NO
201+
STALE_TEST2 09:49:36 2018-03-20 NO
131202

132-
SQL>
133203
SQL>
134204
SQL> spool off

‎optimizer/gather_stale/nopart.sql

Lines changed: 31 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -6,46 +6,71 @@ column index_name format a25
66
alter session set NLS_DATE_FORMAT = 'HH24:MI:SS YYYY-MM-DD';
77

88
drop table stale_test1 purge;
9+
drop table stale_test2 purge;
910

1011
create table stale_test1 (col1 number(10));
12+
create table stale_test2 (col1 number(10));
1113

1214
create index stale_test1_i on stale_test1(col1);
15+
create index stale_test2_i on stale_test2(col1);
1316

1417
exec dbms_stats.set_table_prefs(user,'stale_test1','stale_percent','5')
18+
exec dbms_stats.set_table_prefs(user,'stale_test2','stale_percent','5')
1519

1620
insert into stale_test1 values (1);
21+
insert into stale_test2 values (1);
1722
commit;
1823

1924
exec dbms_stats.gather_table_stats(user,'stale_test1')
25+
exec dbms_stats.gather_table_stats(user,'stale_test2')
2026

2127
PROMPT Table stats are not STALE
2228
PROMPT
2329
select table_name, last_analyzed,stale_stats
2430
from user_tab_statistics
25-
where table_name in ('STALE_TEST1')
31+
where table_name in ('STALE_TEST1','STALE_TEST2')
2632
order by 1,2;
2733

2834
pause p...
2935

3036
exec dbms_lock.sleep(2);
3137
exec dbms_stats.gather_table_stats(user,'stale_test1')
38+
exec dbms_stats.gather_table_stats(user,'stale_test2')
3239

3340
PROMPT But even though the stats were not stale, statistics have been re-gathered
3441
PROMPT You can see this because the LAST_ANALYZED time has changed since we last looked (above)
3542
PROMPT
3643
select table_name,last_analyzed,stale_stats
3744
from user_tab_statistics
38-
where table_name in ('STALE_TEST1')
45+
where table_name in ('STALE_TEST1','STALE_TEST2')
46+
order by 1,2;
47+
48+
pause p...
49+
50+
PROMPT Make STALE_TEST2 stale
51+
PROMPT
52+
insert into stale_test2 values (10);
53+
insert into stale_test2 values (10);
54+
commit;
55+
56+
exec dbms_stats.flush_database_monitoring_info
57+
58+
59+
select table_name,last_analyzed,stale_stats
60+
from user_tab_statistics
61+
where table_name in ('STALE_TEST1','STALE_TEST2')
3962
order by 1,2;
4063

4164
pause p...
4265

4366
DECLARE
4467
filter_lst DBMS_STATS.OBJECTTAB := DBMS_STATS.OBJECTTAB();
4568
BEGIN
46-
filter_lst.extend(1);
69+
filter_lst.extend(2);
4770
filter_lst(1).ownname := user;
4871
filter_lst(1).objname := 'stale_test1';
72+
filter_lst(1).ownname := user;
73+
filter_lst(1).objname := 'stale_test2';
4974
DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>user,obj_filter_list=>filter_lst,options=>'gather auto');
5075
END;
5176
/
@@ -54,11 +79,11 @@ exec dbms_lock.sleep(2);
5479
PROMPT
5580
PROMPT We used dbms_stats.gather_schema_stats(obj_filter_list=> ... , options=>'gather auto')
5681
PROMPT
57-
PROMPT This time the non-stale table have been skipped
82+
PROMPT This time the non-stale table has been skipped
5883
PROMPT and statistics have only been gathered where there are stale stats.
59-
PROMPT You can see this because LAST_ANALYZED time has not changed.
84+
PROMPT You can see if you consider the LAST_ANALYZED time.
6085
select table_name,last_analyzed,stale_stats
6186
from user_tab_statistics
62-
where table_name in ('STALE_TEST1')
87+
where table_name in ('STALE_TEST1','STALE_TEST2')
6388
order by 1,2;
6489

0 commit comments

Comments
 (0)