Skip to content

Commit aeaca4d

Browse files
nigelbaylissnigelbayliss
committed
synop
1 parent e8f8c04 commit aeaca4d

16 files changed

+2170
-0
lines changed

‎optimizer/new_synopses/01_mixed.lst

Lines changed: 168 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,168 @@
1+
SQL> @01_mixed
2+
SQL> set timing on
3+
SQL> set linesize 1000
4+
SQL> set pagesize 100
5+
SQL> set trims on
6+
SQL> column partition_name format a40
7+
SQL> column ndv_alg format a30
8+
SQL> column inc_stale format a30
9+
SQL>
10+
SQL> drop table t1 purge;
11+
12+
Table dropped.
13+
14+
Elapsed: 00:00:00.15
15+
SQL>
16+
SQL> create table t1 (id number(10),num1 number(10), num2 number(10),txt varchar2(20))
17+
2 partition by range (num1)
18+
3 interval (1) (
19+
4 partition p1 values less than (1)
20+
5 ,partition p2 values less than (2));
21+
22+
Table created.
23+
24+
Elapsed: 00:00:00.05
25+
SQL>
26+
SQL> insert /*+ APPEND */ into t1
27+
2 select rownum, mod(rownum,5), mod(rownum,1000),'X'||mod(rownum,10000)
28+
3 from (select 1 from dual connect by level <=1000);
29+
30+
1000 rows created.
31+
32+
Elapsed: 00:00:00.23
33+
SQL>
34+
SQL> commit;
35+
36+
Commit complete.
37+
38+
Elapsed: 00:00:00.03
39+
SQL>
40+
SQL> --
41+
SQL> -- Enable incremental statistics
42+
SQL> --
43+
SQL> exec dbms_stats.set_table_prefs(null,'t1','incremental','true')
44+
45+
PL/SQL procedure successfully completed.
46+
47+
Elapsed: 00:00:00.02
48+
SQL> --
49+
SQL> --No need to set because this is the default
50+
SQL> --
51+
SQL> --exec dbms_stats.set_table_prefs(null,'t1', 'incremental_staleness', 'allow_mixed_format')
52+
SQL>
53+
SQL> --
54+
SQL> -- Create old-style synopses
55+
SQL> --
56+
SQL> exec dbms_stats.set_table_prefs(null,'t1', 'approximate_ndv_algorithm', 'adaptive sampling')
57+
58+
PL/SQL procedure successfully completed.
59+
60+
Elapsed: 00:00:00.00
61+
SQL> exec dbms_stats.gather_table_stats(null,'t1')
62+
63+
PL/SQL procedure successfully completed.
64+
65+
Elapsed: 00:00:00.54
66+
SQL>
67+
SQL> select dbms_stats.get_prefs('approximate_ndv_algorithm',user,'t1') ndv_alg from dual;
68+
69+
NDV_ALG
70+
------------------------------
71+
ADAPTIVE SAMPLING
72+
73+
Elapsed: 00:00:00.01
74+
SQL> select dbms_stats.get_prefs('incremental_staleness',user,'t1') inc_stale from dual;
75+
76+
INC_STALE
77+
------------------------------
78+
ALLOW_MIXED_FORMAT
79+
80+
Elapsed: 00:00:00.00
81+
SQL>
82+
SQL> @t1check
83+
SQL> select subobject_name partition_name,
84+
2 decode(h.spare1,NULL,'ADAPTIVE SAMPLING','HYPERLOGLOG') synopsis_type
85+
3 from dba_objects o,
86+
4 sys.wri$_optstat_synopsis_head$ h
87+
5 where o.object_type = 'TABLE PARTITION'
88+
6 and o.object_name = 'T1'
89+
7 and o.owner = USER
90+
8 and h.group# = o.object_id*2
91+
9 and intcol# = 1
92+
10 order by partition_name;
93+
94+
PARTITION_NAME SYNOPSIS_TYPE
95+
---------------------------------------- -----------------
96+
P1 ADAPTIVE SAMPLING
97+
P2 ADAPTIVE SAMPLING
98+
SYS_P2875 ADAPTIVE SAMPLING
99+
SYS_P2876 ADAPTIVE SAMPLING
100+
SYS_P2877 ADAPTIVE SAMPLING
101+
102+
Elapsed: 00:00:00.03
103+
SQL>
104+
SQL>
105+
SQL>
106+
SQL> --
107+
SQL> -- Make one existing partition stale and add a new partition
108+
SQL> --
109+
SQL> insert /*+ APPEND */ into t1
110+
2 select rownum, mod(rownum,2)+4, mod(rownum,1000),'X'||mod(rownum,10000)
111+
3 from (select 1 from dual connect by level <=1000);
112+
113+
1000 rows created.
114+
115+
Elapsed: 00:00:00.01
116+
SQL>
117+
SQL> exec dbms_stats.set_table_prefs(null,'t1', 'approximate_ndv_algorithm', 'hyperloglog')
118+
119+
PL/SQL procedure successfully completed.
120+
121+
Elapsed: 00:00:00.00
122+
SQL> select dbms_stats.get_prefs('approximate_ndv_algorithm',user,'t1') ndv_alg from dual;
123+
124+
NDV_ALG
125+
------------------------------
126+
HYPERLOGLOG
127+
128+
Elapsed: 00:00:00.00
129+
SQL> select dbms_stats.get_prefs('incremental_staleness',user,'t1') inc_stale from dual;
130+
131+
INC_STALE
132+
------------------------------
133+
ALLOW_MIXED_FORMAT
134+
135+
Elapsed: 00:00:00.01
136+
SQL> exec dbms_stats.gather_table_stats(null,'t1')
137+
138+
PL/SQL procedure successfully completed.
139+
140+
Elapsed: 00:00:00.25
141+
SQL>
142+
SQL> @t1check
143+
SQL> select subobject_name partition_name,
144+
2 decode(h.spare1,NULL,'ADAPTIVE SAMPLING','HYPERLOGLOG') synopsis_type
145+
3 from dba_objects o,
146+
4 sys.wri$_optstat_synopsis_head$ h
147+
5 where o.object_type = 'TABLE PARTITION'
148+
6 and o.object_name = 'T1'
149+
7 and o.owner = USER
150+
8 and h.group# = o.object_id*2
151+
9 and intcol# = 1
152+
10 order by partition_name;
153+
154+
PARTITION_NAME SYNOPSIS_TYPE
155+
---------------------------------------- -----------------
156+
P1 ADAPTIVE SAMPLING
157+
P2 ADAPTIVE SAMPLING
158+
SYS_P2875 ADAPTIVE SAMPLING
159+
SYS_P2876 ADAPTIVE SAMPLING
160+
SYS_P2877 HYPERLOGLOG
161+
SYS_P2913 HYPERLOGLOG
162+
163+
6 rows selected.
164+
165+
Elapsed: 00:00:00.00
166+
SQL>
167+
SQL>
168+
SQL> exit

‎optimizer/new_synopses/01_mixed.sql

Lines changed: 61 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,61 @@
1+
--
2+
-- Create some test tables
3+
-- NOTE!
4+
-- Tables called T1 and EXCH will be dropped
5+
--
6+
set echo on
7+
set timing on
8+
set linesize 1000
9+
set pagesize 100
10+
set trims on
11+
column partition_name format a40
12+
column ndv_alg format a30
13+
column inc_stale format a30
14+
15+
drop table t1 purge;
16+
17+
create table t1 (id number(10),num1 number(10), num2 number(10),txt varchar2(20))
18+
partition by range (num1)
19+
interval (1) (
20+
partition p1 values less than (1)
21+
,partition p2 values less than (2));
22+
23+
insert /*+ APPEND */ into t1
24+
select rownum, mod(rownum,5), mod(rownum,1000),'X'||mod(rownum,10000)
25+
from (select 1 from dual connect by level <=1000);
26+
27+
commit;
28+
29+
--
30+
-- Enable incremental statistics
31+
--
32+
exec dbms_stats.set_table_prefs(null,'t1','incremental','true')
33+
--
34+
--No need to set because this is the default
35+
--
36+
--exec dbms_stats.set_table_prefs(null,'t1', 'incremental_staleness', 'allow_mixed_format')
37+
38+
--
39+
-- Create old-style synopses
40+
--
41+
exec dbms_stats.set_table_prefs(null,'t1', 'approximate_ndv_algorithm', 'adaptive sampling')
42+
exec dbms_stats.gather_table_stats(null,'t1')
43+
44+
select dbms_stats.get_prefs('approximate_ndv_algorithm',user,'t1') ndv_alg from dual;
45+
select dbms_stats.get_prefs('incremental_staleness',user,'t1') inc_stale from dual;
46+
47+
@t1check
48+
49+
--
50+
-- Make one existing partition stale and add a new partition
51+
--
52+
insert /*+ APPEND */ into t1
53+
select rownum, mod(rownum,2)+4, mod(rownum,1000),'X'||mod(rownum,10000)
54+
from (select 1 from dual connect by level <=1000);
55+
56+
exec dbms_stats.set_table_prefs(null,'t1', 'approximate_ndv_algorithm', 'hyperloglog')
57+
select dbms_stats.get_prefs('approximate_ndv_algorithm',user,'t1') ndv_alg from dual;
58+
select dbms_stats.get_prefs('incremental_staleness',user,'t1') inc_stale from dual;
59+
exec dbms_stats.gather_table_stats(null,'t1')
60+
61+
@t1check

0 commit comments

Comments
 (0)