Skip to content

Commit e00a09b

Browse files
nigelbaylissnigelbayliss
committed
gather stale
1 parent ed1186d commit e00a09b

File tree

5 files changed

+651
-0
lines changed

5 files changed

+651
-0
lines changed

‎optimizer/gather_stale/README.md

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,20 @@
1+
How to gather statistics on individual tables, but only if statistics need to be gathered.
2+
3+
The scripts are designed to work in SQL Plus or SQLCL.
4+
5+
Spool files (.lst) are included so you can see the expected results.
6+
7+
nopart.sql is an example for non-partitioned environments
8+
9+
part.sql creates a partitioned table
10+
11+
### DISCLAIMER
12+
13+
* These scripts are provided for educational purposes only.
14+
* They are NOT supported by Oracle World Wide Technical Support.
15+
* The scripts have been tested and they appear to work as intended.
16+
* You should always run scripts on a test instance.
17+
18+
### WARNING
19+
20+
* These scripts drop and create tables. For use on test databases.

‎optimizer/gather_stale/nopart.lst

Lines changed: 134 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,134 @@
1+
SQL> set echo on
2+
SQL> @nopart
3+
SQL> set trims on pagesize 100 linesize 250
4+
SQL> column table_name format a25
5+
SQL> column partition_or_global format a25
6+
SQL> column index_name format a25
7+
SQL>
8+
SQL> alter session set NLS_DATE_FORMAT = 'HH24:MI:SS YYYY-MM-DD';
9+
10+
Session altered.
11+
12+
SQL>
13+
SQL> drop table stale_test1 purge;
14+
15+
Table dropped.
16+
17+
SQL>
18+
SQL> create table stale_test1 (col1 number(10));
19+
20+
Table created.
21+
22+
SQL>
23+
SQL> create index stale_test1_i on stale_test1(col1);
24+
25+
Index created.
26+
27+
SQL>
28+
SQL> exec dbms_stats.set_table_prefs(user,'stale_test1','stale_percent','5')
29+
30+
PL/SQL procedure successfully completed.
31+
32+
SQL>
33+
SQL> insert into stale_test1 values (1);
34+
35+
1 row created.
36+
37+
SQL> commit;
38+
39+
Commit complete.
40+
41+
SQL>
42+
SQL> exec dbms_stats.gather_table_stats(user,'stale_test1')
43+
44+
PL/SQL procedure successfully completed.
45+
46+
SQL>
47+
SQL> PROMPT Table stats are not STALE
48+
Table stats are not STALE
49+
SQL> PROMPT
50+
51+
SQL> select table_name, last_analyzed,stale_stats
52+
2 from user_tab_statistics
53+
3 where table_name in ('STALE_TEST1')
54+
4 order by 1,2;
55+
56+
TABLE_NAME LAST_ANALYZED STA
57+
------------------------- ------------------- ---
58+
STALE_TEST1 07:22:39 2018-03-16 NO
59+
60+
SQL>
61+
SQL> pause p...
62+
p...
63+
64+
SQL>
65+
SQL> exec dbms_lock.sleep(2);
66+
67+
PL/SQL procedure successfully completed.
68+
69+
SQL> exec dbms_stats.gather_table_stats(user,'stale_test1')
70+
71+
PL/SQL procedure successfully completed.
72+
73+
SQL>
74+
SQL> PROMPT But even though the stats were not stale, statistics have been re-gathered
75+
But even though the stats were not stale, statistics have been re-gathered
76+
SQL> PROMPT You can see this because the LAST_ANALYZED time has changed since we last looked (above)
77+
You can see this because the LAST_ANALYZED time has changed since we last looked (above)
78+
SQL> PROMPT
79+
80+
SQL> select table_name,last_analyzed,stale_stats
81+
2 from user_tab_statistics
82+
3 where table_name in ('STALE_TEST1')
83+
4 order by 1,2;
84+
85+
TABLE_NAME LAST_ANALYZED STA
86+
------------------------- ------------------- ---
87+
STALE_TEST1 07:22:43 2018-03-16 NO
88+
89+
SQL>
90+
SQL> pause p...
91+
p...
92+
93+
SQL>
94+
SQL> DECLARE
95+
2 filter_lst DBMS_STATS.OBJECTTAB := DBMS_STATS.OBJECTTAB();
96+
3 BEGIN
97+
4 filter_lst.extend(1);
98+
5 filter_lst(1).ownname := user;
99+
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 /
103+
104+
PL/SQL procedure successfully completed.
105+
106+
SQL>
107+
SQL> exec dbms_lock.sleep(2);
108+
109+
PL/SQL procedure successfully completed.
110+
111+
SQL> PROMPT
112+
113+
SQL> PROMPT We used dbms_stats.gather_schema_stats(obj_filter_list=> ... , options=>'gather auto')
114+
We used dbms_stats.gather_schema_stats(obj_filter_list=> ... , options=>'gather auto')
115+
SQL> PROMPT
116+
117+
SQL> PROMPT This time the non-stale table have been skipped
118+
This time the non-stale table have been skipped
119+
SQL> PROMPT and statistics have only been gathered where there are stale stats.
120+
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.
123+
SQL> select table_name,last_analyzed,stale_stats
124+
2 from user_tab_statistics
125+
3 where table_name in ('STALE_TEST1')
126+
4 order by 1,2;
127+
128+
TABLE_NAME LAST_ANALYZED STA
129+
------------------------- ------------------- ---
130+
STALE_TEST1 07:22:43 2018-03-16 NO
131+
132+
SQL>
133+
SQL>
134+
SQL> spool off

‎optimizer/gather_stale/nopart.sql

Lines changed: 64 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,64 @@
1+
set trims on pagesize 100 linesize 250
2+
column table_name format a25
3+
column partition_or_global format a25
4+
column index_name format a25
5+
6+
alter session set NLS_DATE_FORMAT = 'HH24:MI:SS YYYY-MM-DD';
7+
8+
drop table stale_test1 purge;
9+
10+
create table stale_test1 (col1 number(10));
11+
12+
create index stale_test1_i on stale_test1(col1);
13+
14+
exec dbms_stats.set_table_prefs(user,'stale_test1','stale_percent','5')
15+
16+
insert into stale_test1 values (1);
17+
commit;
18+
19+
exec dbms_stats.gather_table_stats(user,'stale_test1')
20+
21+
PROMPT Table stats are not STALE
22+
PROMPT
23+
select table_name, last_analyzed,stale_stats
24+
from user_tab_statistics
25+
where table_name in ('STALE_TEST1')
26+
order by 1,2;
27+
28+
pause p...
29+
30+
exec dbms_lock.sleep(2);
31+
exec dbms_stats.gather_table_stats(user,'stale_test1')
32+
33+
PROMPT But even though the stats were not stale, statistics have been re-gathered
34+
PROMPT You can see this because the LAST_ANALYZED time has changed since we last looked (above)
35+
PROMPT
36+
select table_name,last_analyzed,stale_stats
37+
from user_tab_statistics
38+
where table_name in ('STALE_TEST1')
39+
order by 1,2;
40+
41+
pause p...
42+
43+
DECLARE
44+
filter_lst DBMS_STATS.OBJECTTAB := DBMS_STATS.OBJECTTAB();
45+
BEGIN
46+
filter_lst.extend(1);
47+
filter_lst(1).ownname := user;
48+
filter_lst(1).objname := 'stale_test1';
49+
DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>user,obj_filter_list=>filter_lst,options=>'gather auto');
50+
END;
51+
/
52+
53+
exec dbms_lock.sleep(2);
54+
PROMPT
55+
PROMPT We used dbms_stats.gather_schema_stats(obj_filter_list=> ... , options=>'gather auto')
56+
PROMPT
57+
PROMPT This time the non-stale table have been skipped
58+
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.
60+
select table_name,last_analyzed,stale_stats
61+
from user_tab_statistics
62+
where table_name in ('STALE_TEST1')
63+
order by 1,2;
64+

0 commit comments

Comments
 (0)