Skip to content

Commit f932b89

Browse files
nigelbaylissnigelbayliss
committed
nomix
1 parent 7b95bf8 commit f932b89

File tree

2 files changed

+347
-0
lines changed

2 files changed

+347
-0
lines changed

‎optimizer/new_synopses/08_nomix.lst

Lines changed: 256 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,256 @@
1+
SQL> set timing on
2+
SQL> set linesize 1000
3+
SQL> set pagesize 100
4+
SQL> set trims on
5+
SQL> column partition_name format a40
6+
SQL> column ndv_alg format a30
7+
SQL> column inc_stale format a30
8+
SQL>
9+
SQL> drop table t1 purge;
10+
11+
Table dropped.
12+
13+
Elapsed: 00:00:00.08
14+
SQL> drop table exch purge;
15+
16+
Table dropped.
17+
18+
Elapsed: 00:00:00.03
19+
SQL>
20+
SQL> create table t1 (id number(10),num1 number(10), num2 number(10),txt varchar2(20))
21+
2 partition by range (num1)
22+
3 interval (1) (
23+
4 partition p1 values less than (1)
24+
5 ,partition p2 values less than (2));
25+
26+
Table created.
27+
28+
Elapsed: 00:00:00.01
29+
SQL>
30+
SQL> insert /*+ APPEND */ into t1
31+
2 select rownum, mod(rownum,2), mod(rownum,1000),'X'||mod(rownum,10000)
32+
3 from (select 1 from dual connect by level <=1000);
33+
34+
1000 rows created.
35+
36+
Elapsed: 00:00:00.04
37+
SQL>
38+
SQL> commit;
39+
40+
Commit complete.
41+
42+
Elapsed: 00:00:00.01
43+
SQL>
44+
SQL> create table exch as select * from t1 where 1=-1;
45+
46+
Table created.
47+
48+
Elapsed: 00:00:00.02
49+
SQL>
50+
SQL> insert /*+ APPEND */ into exch
51+
2 select rownum,0,mod(rownum,10000),'X'||mod(rownum,100000)
52+
3 from (select 1 from dual connect by level <=100);
53+
54+
100 rows created.
55+
56+
Elapsed: 00:00:00.01
57+
SQL>
58+
SQL> commit;
59+
60+
Commit complete.
61+
62+
Elapsed: 00:00:00.01
63+
SQL>
64+
SQL> --
65+
SQL> -- Enable incremental statistics
66+
SQL> --
67+
SQL> exec dbms_stats.set_table_prefs(null,'t1','incremental','true')
68+
69+
PL/SQL procedure successfully completed.
70+
71+
Elapsed: 00:00:00.00
72+
SQL> exec dbms_stats.set_table_prefs(null,'exch','incremental','true')
73+
74+
PL/SQL procedure successfully completed.
75+
76+
Elapsed: 00:00:00.00
77+
SQL>
78+
SQL> --
79+
SQL> -- Prepare to create a synopsis on the EXCH table
80+
SQL> --
81+
SQL> exec DBMS_STATS.SET_TABLE_PREFS (null,'exch','INCREMENTAL_LEVEL','table');
82+
83+
PL/SQL procedure successfully completed.
84+
85+
Elapsed: 00:00:00.00
86+
SQL> --
87+
SQL> -- The exchange table has an old-style synopsis
88+
SQL> --
89+
SQL> exec dbms_stats.set_table_prefs(null,'exch', 'approximate_ndv_algorithm', 'adaptive sampling')
90+
91+
PL/SQL procedure successfully completed.
92+
93+
Elapsed: 00:00:00.00
94+
SQL> exec dbms_stats.gather_table_stats(null,'exch');
95+
96+
PL/SQL procedure successfully completed.
97+
98+
Elapsed: 00:00:00.13
99+
SQL>
100+
SQL> --
101+
SQL> -- The partitioned table has new-style synopses
102+
SQL> -- Also, disalow mixed format.
103+
SQL> -- Compare the behavior here to the previous example (07_defaultmx)
104+
SQL> --
105+
SQL> exec dbms_stats.set_table_prefs(null,'t1', 'approximate_ndv_algorithm', 'hyperloglog')
106+
107+
PL/SQL procedure successfully completed.
108+
109+
Elapsed: 00:00:00.00
110+
SQL> exec dbms_stats.set_table_prefs(null,'t1', 'incremental_staleness', 'null')
111+
112+
PL/SQL procedure successfully completed.
113+
114+
Elapsed: 00:00:00.00
115+
SQL> exec dbms_stats.gather_table_stats(null,'t1')
116+
117+
PL/SQL procedure successfully completed.
118+
119+
Elapsed: 00:00:00.09
120+
SQL>
121+
SQL> @t1check
122+
SQL> select subobject_name partition_name,
123+
2 decode(h.spare1,NULL,'ADAPTIVE SAMPLING','HYPERLOGLOG') synopsis_type
124+
3 from dba_objects o,
125+
4 sys.wri$_optstat_synopsis_head$ h
126+
5 where o.object_type = 'TABLE PARTITION'
127+
6 and o.object_name = 'T1'
128+
7 and o.owner = USER
129+
8 and h.group# = o.object_id*2
130+
9 and intcol# = 1
131+
10 order by partition_name;
132+
133+
PARTITION_NAME SYNOPSIS_TYPE
134+
---------------------------------------- -----------------
135+
P1 HYPERLOGLOG
136+
P2 HYPERLOGLOG
137+
138+
Elapsed: 00:00:00.00
139+
SQL>
140+
SQL>
141+
SQL>
142+
SQL> pause
143+
144+
SQL>
145+
SQL> alter table t1 exchange partition p1 with table exch;
146+
147+
Table altered.
148+
149+
Elapsed: 00:00:00.02
150+
SQL>
151+
SQL> @t1check
152+
SQL> select subobject_name partition_name,
153+
2 decode(h.spare1,NULL,'ADAPTIVE SAMPLING','HYPERLOGLOG') synopsis_type
154+
3 from dba_objects o,
155+
4 sys.wri$_optstat_synopsis_head$ h
156+
5 where o.object_type = 'TABLE PARTITION'
157+
6 and o.object_name = 'T1'
158+
7 and o.owner = USER
159+
8 and h.group# = o.object_id*2
160+
9 and intcol# = 1
161+
10 order by partition_name;
162+
163+
PARTITION_NAME SYNOPSIS_TYPE
164+
---------------------------------------- -----------------
165+
P1 ADAPTIVE SAMPLING
166+
P2 HYPERLOGLOG
167+
168+
Elapsed: 00:00:00.00
169+
SQL>
170+
SQL>
171+
SQL>
172+
SQL> pause
173+
174+
SQL>
175+
SQL> --
176+
SQL> -- Add a partition
177+
SQL> --
178+
SQL> insert /*+ APPEND */ into t1
179+
2 select rownum, 2, mod(rownum,1000),'X'||mod(rownum,10000)
180+
3 from (select 1 from dual connect by level <=1000);
181+
182+
1000 rows created.
183+
184+
Elapsed: 00:00:00.01
185+
SQL>
186+
SQL> exec dbms_stats.gather_table_stats(null,'t1')
187+
188+
PL/SQL procedure successfully completed.
189+
190+
Elapsed: 00:00:00.09
191+
SQL>
192+
SQL> @t1check
193+
SQL> select subobject_name partition_name,
194+
2 decode(h.spare1,NULL,'ADAPTIVE SAMPLING','HYPERLOGLOG') synopsis_type
195+
3 from dba_objects o,
196+
4 sys.wri$_optstat_synopsis_head$ h
197+
5 where o.object_type = 'TABLE PARTITION'
198+
6 and o.object_name = 'T1'
199+
7 and o.owner = USER
200+
8 and h.group# = o.object_id*2
201+
9 and intcol# = 1
202+
10 order by partition_name;
203+
204+
PARTITION_NAME SYNOPSIS_TYPE
205+
---------------------------------------- -----------------
206+
P1 HYPERLOGLOG
207+
P2 HYPERLOGLOG
208+
SYS_P3604 HYPERLOGLOG
209+
210+
Elapsed: 00:00:00.01
211+
SQL>
212+
SQL>
213+
SQL>
214+
SQL> pause
215+
216+
SQL>
217+
SQL> --
218+
SQL> -- Make P1 stale
219+
SQL> --
220+
SQL> insert /*+ APPEND */ into t1
221+
2 select rownum, 0, mod(rownum,1000),'X'||mod(rownum,10000)
222+
3 from (select 1 from dual connect by level <=1000);
223+
224+
1000 rows created.
225+
226+
Elapsed: 00:00:00.01
227+
SQL>
228+
SQL> exec dbms_stats.gather_table_stats(null,'t1')
229+
230+
PL/SQL procedure successfully completed.
231+
232+
Elapsed: 00:00:00.06
233+
SQL>
234+
SQL> @t1check
235+
SQL> select subobject_name partition_name,
236+
2 decode(h.spare1,NULL,'ADAPTIVE SAMPLING','HYPERLOGLOG') synopsis_type
237+
3 from dba_objects o,
238+
4 sys.wri$_optstat_synopsis_head$ h
239+
5 where o.object_type = 'TABLE PARTITION'
240+
6 and o.object_name = 'T1'
241+
7 and o.owner = USER
242+
8 and h.group# = o.object_id*2
243+
9 and intcol# = 1
244+
10 order by partition_name;
245+
246+
PARTITION_NAME SYNOPSIS_TYPE
247+
---------------------------------------- -----------------
248+
P1 HYPERLOGLOG
249+
P2 HYPERLOGLOG
250+
SYS_P3604 HYPERLOGLOG
251+
252+
Elapsed: 00:00:00.01
253+
SQL>
254+
SQL>
255+
SQL> exit
256+

‎optimizer/new_synopses/08_nomix.sql

Lines changed: 91 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,91 @@
1+
set echo on
2+
set timing on
3+
set linesize 1000
4+
set pagesize 100
5+
set trims on
6+
column partition_name format a40
7+
column ndv_alg format a30
8+
column inc_stale format a30
9+
10+
drop table t1 purge;
11+
drop table exch purge;
12+
13+
create table t1 (id number(10),num1 number(10), num2 number(10),txt varchar2(20))
14+
partition by range (num1)
15+
interval (1) (
16+
partition p1 values less than (1)
17+
,partition p2 values less than (2));
18+
19+
insert /*+ APPEND */ into t1
20+
select rownum, mod(rownum,2), mod(rownum,1000),'X'||mod(rownum,10000)
21+
from (select 1 from dual connect by level <=1000);
22+
23+
commit;
24+
25+
create table exch as select * from t1 where 1=-1;
26+
27+
insert /*+ APPEND */ into exch
28+
select rownum,0,mod(rownum,10000),'X'||mod(rownum,100000)
29+
from (select 1 from dual connect by level <=100);
30+
31+
commit;
32+
33+
--
34+
-- Enable incremental statistics
35+
--
36+
exec dbms_stats.set_table_prefs(null,'t1','incremental','true')
37+
exec dbms_stats.set_table_prefs(null,'exch','incremental','true')
38+
39+
--
40+
-- Prepare to create a synopsis on the EXCH table
41+
--
42+
exec DBMS_STATS.SET_TABLE_PREFS (null,'exch','INCREMENTAL_LEVEL','table');
43+
--
44+
-- The exchange table has an old-style synopsis
45+
--
46+
exec dbms_stats.set_table_prefs(null,'exch', 'approximate_ndv_algorithm', 'adaptive sampling')
47+
exec dbms_stats.gather_table_stats(null,'exch');
48+
49+
--
50+
-- The partitioned table has new-style synopses
51+
-- Also, disalow mixed format.
52+
-- Compare the behavior here to the previous example (07_defaultmx)
53+
--
54+
exec dbms_stats.set_table_prefs(null,'t1', 'approximate_ndv_algorithm', 'hyperloglog')
55+
exec dbms_stats.set_table_prefs(null,'t1', 'incremental_staleness', 'null')
56+
exec dbms_stats.gather_table_stats(null,'t1')
57+
58+
@t1check
59+
60+
pause
61+
62+
alter table t1 exchange partition p1 with table exch;
63+
64+
@t1check
65+
66+
pause
67+
68+
--
69+
-- Add a partition
70+
--
71+
insert /*+ APPEND */ into t1
72+
select rownum, 2, mod(rownum,1000),'X'||mod(rownum,10000)
73+
from (select 1 from dual connect by level <=1000);
74+
75+
exec dbms_stats.gather_table_stats(null,'t1')
76+
77+
@t1check
78+
79+
pause
80+
81+
--
82+
-- Make P1 stale
83+
--
84+
insert /*+ APPEND */ into t1
85+
select rownum, 0, mod(rownum,1000),'X'||mod(rownum,10000)
86+
from (select 1 from dual connect by level <=1000);
87+
88+
exec dbms_stats.gather_table_stats(null,'t1')
89+
90+
@t1check
91+

0 commit comments

Comments
 (0)