-
Notifications
You must be signed in to change notification settings - Fork 849
/
Copy patht4.lst
173 lines (152 loc) · 6.43 KB
/
t4.lst
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
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
SQL>
SQL> exec dbms_stats.set_table_prefs(user,'tab_corr1','publish','false')
SQL> exec dbms_stats.set_table_prefs(user,'tab_corr2','publish','false')
SQL> exec dbms_stats.gather_table_stats(user,'tab_corr1',no_invalidate=>false);
SQL> exec dbms_stats.gather_table_stats(user,'tab_corr2',no_invalidate=>false);
SQL>
SQL> --
SQL> -- This query won't see the new statistics because they are not published
SQL> -- so the cardinality estimate will still be wrong (100)
SQL> --
SQL> select /* MY_TEST_QUERY */ sum(b.n0),count(*)
2 from tab_corr1 a, tab_corr2 b
3 where a.n1 = 1
4 and a.n2 = 1
5 and a.n1 = b.n1
6 and a.n2 = b.n2;
SUM(B.N0) COUNT(*)
---------- ----------
1000 1000
SQL>
SQL> @@plan
SQL> set linesize 200
SQL> set trims on
SQL> set tab off
SQL> set pagesize 1000
SQL> column plan_table_output format a100
SQL>
SQL> SELECT *
2 FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'TYPICAL'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 7kjpawwbyh1bz, child number 2
-------------------------------------
select /* MY_TEST_QUERY */ sum(b.n0),count(*) from tab_corr1 a,
tab_corr2 b where a.n1 = 1 and a.n2 = 1 and a.n1 = b.n1 and a.n2
= b.n2
Plan hash value: 3486445757
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 18 (100)| |
| 1 | SORT AGGREGATE | | 1 | 18 | | |
|* 2 | HASH JOIN | | 1 | 18 | 18 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| TAB_CORR2 | 1 | 12 | 9 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| TAB_CORR1 | 100 | 600 | 9 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."N1"="B"."N1" AND "A"."N2"="B"."N2")
3 - filter(("B"."N1"=1 AND "B"."N2"=1))
4 - filter(("A"."N1"=1 AND "A"."N2"=1))
SQL>
SQL> --
SQL> -- Expose the next query to the column group statistics
SQL> -- and the cardinality estimate will be correct (1000)
SQL> --
SQL> alter session set OPTIMIZER_USE_PENDING_STATISTICS = TRUE;
SQL>
SQL> select /* MY_TEST_QUERY */ sum(b.n0),count(*)
2 from tab_corr1 a, tab_corr2 b
3 where a.n1 = 1
4 and a.n2 = 1
5 and a.n1 = b.n1
6 and a.n2 = b.n2;
SUM(B.N0) COUNT(*)
---------- ----------
1000 1000
SQL>
SQL> @@plan
SQL> set linesize 200
SQL> set trims on
SQL> set tab off
SQL> set pagesize 1000
SQL> column plan_table_output format a100
SQL>
SQL> SELECT *
2 FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'TYPICAL'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 7kjpawwbyh1bz, child number 3
-------------------------------------
select /* MY_TEST_QUERY */ sum(b.n0),count(*) from tab_corr1 a,
tab_corr2 b where a.n1 = 1 and a.n2 = 1 and a.n1 = b.n1 and a.n2
= b.n2
Plan hash value: 3486445757
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 18 (100)| |
| 1 | SORT AGGREGATE | | 1 | 18 | | |
|* 2 | HASH JOIN | | 1000 | 18000 | 18 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| TAB_CORR2 | 1 | 12 | 9 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| TAB_CORR1 | 1000 | 6000 | 9 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."N1"="B"."N1" AND "A"."N2"="B"."N2")
3 - filter(("B"."N1"=1 AND "B"."N2"=1))
4 - filter(("A"."N1"=1 AND "A"."N2"=1))
SQL>
SQL> --
SQL> -- Let's go ahead an publish
SQL> --
SQL> exec dbms_stats.publish_pending_stats(user,'tab_corr1')
SQL> exec dbms_stats.publish_pending_stats(user,'tab_corr2')
SQL> alter session set OPTIMIZER_USE_PENDING_STATISTICS = FALSE;
SQL> exec dbms_stats.set_table_prefs(user,'tab_corr1','publish','true')
SQL> exec dbms_stats.set_table_prefs(user,'tab_corr2','publish','true')
SQL>
SQL> select /* PEND_FALSE MY_TEST_QUERY */ sum(b.n0),count(*)
2 from tab_corr1 a, tab_corr2 b
3 where a.n1 = 1
4 and a.n2 = 1
5 and a.n1 = b.n1
6 and a.n2 = b.n2;
SUM(B.N0) COUNT(*)
---------- ----------
1000 1000
SQL>
SQL> @@plan
SQL> set linesize 200
SQL> set trims on
SQL> set tab off
SQL> set pagesize 1000
SQL> column plan_table_output format a100
SQL>
SQL> SELECT *
2 FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'TYPICAL'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 6f7fkus5pbcj6, child number 0
-------------------------------------
select /* PEND_FALSE MY_TEST_QUERY */ sum(b.n0),count(*) from
tab_corr1 a, tab_corr2 b where a.n1 = 1 and a.n2 = 1 and a.n1 =
b.n1 and a.n2 = b.n2
Plan hash value: 3486445757
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 18 (100)| |
| 1 | SORT AGGREGATE | | 1 | 18 | | |
|* 2 | HASH JOIN | | 1000 | 18000 | 18 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| TAB_CORR2 | 1 | 12 | 9 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| TAB_CORR1 | 1000 | 6000 | 9 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."N1"="B"."N1" AND "A"."N2"="B"."N2")
3 - filter(("B"."N1"=1 AND "B"."N2"=1))
4 - filter(("A"."N1"=1 AND "A"."N2"=1))
SQL>
SQL> spool off