-
Notifications
You must be signed in to change notification settings - Fork 849
/
Copy pathtest2.sql
127 lines (101 loc) · 3.69 KB
/
test2.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
set echo on
set timing on
set linesize 1000
set pagesize 100
set trims on
--
-- HyperLogLog synopses
--
exec dbms_stats.set_table_prefs(null,'t1', 'approximate_ndv_algorithm', 'hyperloglog')
exec dbms_stats.delete_table_stats(null,'t1')
exec dbms_stats.gather_table_stats(null,'t1')
pause
--
-- Confirm we have HLL synopses
--
@t1check
pause
--
-- Take a look at synopses for HyperLogLog algorithm
--
--
-- No rows in this table for T1
--
select count(*) from sys.WRI$_OPTSTAT_SYNOPSIS$
where bo# = (select object_id from user_objects where object_name = 'T1' and object_type = 'TABLE');
--
-- Number of rows = #Partitions * #Table Columns
--
select count(*) from sys.WRI$_OPTSTAT_SYNOPSIS_HEAD$
where bo# = (select object_id from user_objects where object_name = 'T1' and object_type = 'TABLE');
--
-- Binary NDV data for each column per partition
-- Just showing the first few rows...
--
select spare1,spare2 from sys.WRI$_OPTSTAT_SYNOPSIS_HEAD$
where bo# = (select object_id from user_objects where object_name = 'T1' and object_type = 'TABLE')
and rownum<11;
pause
--
-- Adaptive sampling (pre-Oracle Database 12c Release 2)
--
exec dbms_stats.set_table_prefs(null,'t1', 'approximate_ndv_algorithm', 'adaptive sampling')
exec dbms_stats.delete_table_stats(null,'t1')
exec dbms_stats.gather_table_stats(null,'t1')
--
-- Confirm we have Adaptive Sampling synopses
--
@t1check
pause
--
-- NDV data for Adaptive Sampling algorythm
-- The number of rows is related to #Partitions, #Columns and NDV per column
--
select count(*) from sys.WRI$_OPTSTAT_SYNOPSIS$
where bo# = (select object_id from user_objects where object_name = 'T1' and object_type = 'TABLE');
--
-- Same #rows as HyperLogLog
--
select count(*) from sys.WRI$_OPTSTAT_SYNOPSIS_HEAD$
where bo# = (select object_id from user_objects where object_name = 'T1' and object_type = 'TABLE');
--
-- No binary NDV data
--
select spare1 ,spare2 from sys.WRI$_OPTSTAT_SYNOPSIS_HEAD$
where bo# = (select object_id from user_objects where object_name = 'T1' and object_type = 'TABLE')
and rownum<11;
pause
-- Ignore this timing
exec dbms_stats.delete_table_stats(null,'t1')
--
-- Look at the timings for deleting and gathering statistics
-- Adaptive Sampling
--
exec dbms_stats.set_table_prefs(null,'t1', 'approximate_ndv_algorithm', 'adaptive sampling')
exec dbms_stats.gather_table_stats(null,'t1')
exec dbms_stats.set_table_prefs(null,'t1', 'approximate_ndv_algorithm', 'adaptive sampling')
exec dbms_stats.delete_table_stats(null,'t1')
exec dbms_stats.gather_table_stats(null,'t1')
exec dbms_stats.set_table_prefs(null,'t1', 'approximate_ndv_algorithm', 'adaptive sampling')
exec dbms_stats.delete_table_stats(null,'t1')
exec dbms_stats.gather_table_stats(null,'t1')
exec dbms_stats.set_table_prefs(null,'t1', 'approximate_ndv_algorithm', 'adaptive sampling')
exec dbms_stats.delete_table_stats(null,'t1')
exec dbms_stats.gather_table_stats(null,'t1')
-- Ignore this timing
exec dbms_stats.delete_table_stats(null,'t1')
--
-- Compare these timing with the previous timings for deleting and gathering statistics
-- HyperLogLog
--
exec dbms_stats.set_table_prefs(null,'t1', 'approximate_ndv_algorithm', 'hyperloglog')
exec dbms_stats.gather_table_stats(null,'t1')
exec dbms_stats.set_table_prefs(null,'t1', 'approximate_ndv_algorithm', 'hyperloglog')
exec dbms_stats.delete_table_stats(null,'t1')
exec dbms_stats.gather_table_stats(null,'t1')
exec dbms_stats.set_table_prefs(null,'t1', 'approximate_ndv_algorithm', 'hyperloglog')
exec dbms_stats.delete_table_stats(null,'t1')
exec dbms_stats.gather_table_stats(null,'t1')
exec dbms_stats.set_table_prefs(null,'t1', 'approximate_ndv_algorithm', 'hyperloglog')
exec dbms_stats.delete_table_stats(null,'t1')
exec dbms_stats.gather_table_stats(null,'t1')