有趣的测试
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
本文链接地址: 有趣的测试
|
1 |
本人测试源于itpub的一个帖子，测试版本是10.2.0.5，欢迎大家一起讨论。 |
|
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 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 |
SQL> drop table killdb; Table dropped. SQL> create table killdb (id number,name varchar2(10)); Table created. SQL> create index killdb_idx on killdb(id); Index created. SQL> insert into killdb values(1,'killdb.com'); 1 row created. SQL> / 1 row created. SQL> / 1 row created. SQL> / 1 row created. SQL> / 1 row created. SQL> / 1 row created. SQL> commit; Commit complete. SQL> begin 2 for i in 1..5 loop 3 insert into killdb select * from killdb; 4 commit; 5 end loop; 6 end; 7 / PL/SQL procedure successfully completed. SQL> select count(*) from killdb; COUNT(*) ---------- 192 SQL> begin 2 for i in 1..100 loop 3 insert into killdb select * from killdb; 4 commit; 5 end loop; 6 end; 7 / begin * ERROR at line 1: ORA-01013: user requested cancel of current operation ORA-06512: at line 3 SQL> select count(*) from killdb; COUNT(*) ---------- 196608 SQL> insert into killdb values (2,'baidu.com'); 1 row created. SQL> / 1 row created. SQL> / 1 row created. SQL> / 1 row created. SQL> / 1 row created. SQL> / 1 row created. SQL> / 1 row created. SQL> / 1 row created. SQL> begin 2 for i in 1..5 loop 3 insert into killdb values(2,'baidu.com'); 4 commit; 5 end loop; 6 end; 7 / PL/SQL procedure successfully completed. SQL> commit; Commit complete. SQL> select id, count(*) from killdb group by id; ID COUNT(*) ---------- ---------- 1 196608 2 13 SQL> begin 2 dbms_stats.gather_table_stats( ownname => 'ROGER', 3 tabname => 'KILLDB', 4 estimate_percent => dbms_stats.auto_sample_size, 5 method_opt => 'for all columns size 254', 6 cascade => true, 7 degree => 2 ); 8 end; 9 / PL/SQL procedure successfully completed. SQL> set lines 140 SQL> set autot trace exp SQL> select * from killdb where id=2; Execution Plan ---------------------------------------------------------- Plan hash value: 2601906965 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 34 | 442 | 4 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| KILLDB | 34 | 442 | 4 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | KILLDB_IDX | 34 | | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ID"=2) SQL> set autot off SQL> insert into killdb select * from killdb where id=2; 13 rows created. SQL> commit; Commit complete. SQL> set autot trace exp SQL> select * from killdb where id=2; Execution Plan ---------------------------------------------------------- Plan hash value: 2601906965 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 34 | 442 | 4 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| KILLDB | 34 | 442 | 4 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | KILLDB_IDX | 34 | | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ID"=2) SQL> set autot off SQL> begin 2 dbms_stats.gather_table_stats( ownname => 'ROGER', 3 tabname => 'KILLDB', 4 cascade => false, 5 degree => 2 ); 6 end; 7 / PL/SQL procedure successfully completed. SQL> set autot trace exp SQL> select * from killdb where id=2; Execution Plan ---------------------------------------------------------- Plan hash value: 2601906965 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 14 | 4 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| KILLDB | 1 | 14 | 4 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | KILLDB_IDX | 1 | | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ID"=2) SQL> begin 2 dbms_stats.gather_table_stats( ownname => 'ROGER', 3 tabname => 'KILLDB', 4 estimate_percent => dbms_stats.auto_sample_size, 5 method_opt => 'for all columns size 254', 6 cascade => false, 7 degree => 2 ); 8 end; 9 / PL/SQL procedure successfully completed. SQL> select * from killdb where id=2; Execution Plan ---------------------------------------------------------- Plan hash value: 2601906965 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 14 | 4 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| KILLDB | 1 | 14 | 4 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | KILLDB_IDX | 1 | | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ID"=2) SQL> begin 2 dbms_stats.gather_table_stats( ownname => 'ROGER', 3 tabname => 'KILLDB', 4 estimate_percent => dbms_stats.auto_sample_size, 5 method_opt => 'for all columns size 254', 6 cascade => true, 7 degree => 2 ); 8 end; 9 / PL/SQL procedure successfully completed. SQL> set autot trace exp SQL> select * from killdb where id=2; Execution Plan ---------------------------------------------------------- Plan hash value: 2601906965 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 52 | 676 | 4 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| KILLDB | 52 | 676 | 4 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | KILLDB_IDX | 53 | | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ID"=2) SQL> set autot off SQL> alter session set events '10053 trace name context forever, level 1'; Session altered. SQL> select * from killdb where id=2; ID NAME ---------- ---------- 2 baidu.com 2 baidu.com 2 baidu.com 2 baidu.com 2 baidu.com 2 baidu.com 2 baidu.com 2 baidu.com 2 baidu.com 2 baidu.com 2 baidu.com 2 baidu.com 2 baidu.com 2 baidu.com 2 baidu.com 2 baidu.com 2 baidu.com 2 baidu.com 2 baidu.com 2 baidu.com 2 baidu.com 2 baidu.com 2 baidu.com 2 baidu.com 2 baidu.com 2 baidu.com 26 rows selected. SQL> alter session set events '10053 trace name context off'; Session altered. SQL> select sql_id, sql_text 2 from v$sqlarea 3 where sql_text like '%select * from killdb%'; SQL_ID SQL_TEXT ------------- ----------------------------------------------------------------------------------- 1tjf3zusd7h0k select sql_id,sql_text from v$sqlarea where sql_text like '%select * from killdb%' 4yt3uzwprgscs select * from killdb where id=2 <== 格式化10053 trace |
|
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 |
SQL> start sqltrcasplit.sql /home/ora10g/admin/roger/udump/roger_ora_9740.trc PL/SQL procedure successfully completed. Parameter 1: Trace Filename (required) Value passed to sqltrcasplit.sql: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ TRACE_FILENAME: /home/ora10g/admin/roger/udump/roger_ora_9740.trc Splitting /home/ora10g/admin/roger/udump/roger_ora_9740.trc NOTE: If you get one of these errors it means SQLTXPLAIN is not installed: PLS-00201: identifier 'SQLTXPLAIN.SQLT$A' must be declared ORA-00904: "SQLTXPLAIN"."SQLT$A"."VALIDATE_USER": invalid identifier Review NN_*.log files created during installation and fix errors reported. PL/SQL procedure successfully completed. ... please wait ... To monitor progress, login as SQLTXPLAIN into another session and execute: SQL> SELECT * FROM trca$_log_v; ... splitting trace(s) ... Execution ID: 57658 started at 2012-02-18 06:36:03 In case of premature termination, read trcanlzr_error.log located in SQL*Plus default directory /*************************************************************************************/ 06:36:03 => trcanlzr 06:36:03 directory path was ignored from "/home/ora10g/admin/roger/udump/roger_ora_9740.trc" 06:36:03 file_name:"roger_ora_9740.trc" 06:36:03 analyze:"NO" 06:36:03 split:"YES" 06:36:03 tool_execution_id:"57658" 06:36:03 directory_alias_in:"SQLT$STAGE" 06:36:03 file_name_log:"" 06:36:03 file_name_html:"" 06:36:03 file_name_txt:"" 06:36:03 file_name_10046:"" 06:36:03 file_name_10053:"" 06:36:03 out_file_identifier:"" 06:36:03 calling trca$p.parse_main 06:36:03 => parse_main 06:36:03 analyzing input file roger_ora_9740.trc in /home/ora10g/admin/roger/udump 06:36:03 -> parse_file 06:36:03 parsing file roger_ora_9740.trc in /home/ora10g/admin/roger/udump 06:36:03 parsed roger_ora_9740.trc (input 46354 bytes, parsed as 46354 bytes) 06:36:03 <- parse_file 06:36:03 parsed 1 file(s) (input 46354 bytes) 06:36:03 first trace: /home/ora10g/admin/roger/udump/roger_ora_9740.trc 06:36:03 <= parse_main 06:36:03 |
|
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 |
++++++ 如下是10053 trace格式化以后的关键部分 ++++++ *************************************** BASE STATISTICAL INFORMATION *********************** Table Stats:: Table: KILLDB Alias: KILLDB #Rows: 195119 #Blks: 622 AvgRowLen: 13.00 Index Stats:: Index: KILLDB_IDX Col#: 1 LVLS: 2 #LB: 604 #DK: 2 LB/K: 302.00 DB/K: 258.00 CLUF: 516.00 *************************************** SINGLE TABLE ACCESS PATH ----------------------------------------- BEGIN Single Table Cardinality Estimation ----------------------------------------- Column (#1): ID(NUMBER) AvgLen: 3.00 NDV: 2 Nulls: 0 Density: 1.7934e-04 Min: 1 Max: 2 Histogram: Freq #Bkts: 2 UncompBkts: 5576 EndPtVals: 2 Table: KILLDB Alias: KILLDB Card: Original: 195119 Rounded: 52 Computed: 52.49 Non Adjusted: 52.49 ----------------------------------------- END Single Table Cardinality Estimation ----------------------------------------- Access Path: TableScan Cost: 171.51 Resp: 171.51 Degree: 0 Cost_io: 170.00 Cost_cpu: 43454376 Resp_io: 170.00 Resp_cpu: 43454376 Access Path: index (AllEqRange) Index: KILLDB_IDX resc_io: 4.00 resc_cpu: 48096 ix_sel: 2.6901e-04 ix_sel_with_filters: 2.6901e-04 Cost: 4.00 Resp: 4.00 Degree: 1 Best:: AccessPath: IndexRange Index: KILLDB_IDX Cost: 4.00 Degree: 1 Resp: 4.00 Card: 52.49 Bytes: 0 <== IndexRange计算出来的card为52.49，也就是plan中显示的53 *************************************** OPTIMIZER STATISTICS AND COMPUTATIONS *************************************** GENERAL PLANS *************************************** Considering cardinality-based initial join order. Permutations for Starting Table :0 *********************** Join order[1]: KILLDB[KILLDB]#0 *********************** Best so far: Table#: 0 cost: 4.0017 card: 52.4890 bytes: 676 (newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:80000 ********************************* Number of join permutations tried: 1 ********************************* Final - All Rows Plan: Best join order: 1 Cost: 4.0017 Degree: 1 Card: 52.0000 Bytes: 676 <== 这里计算出来为52 Resc: 4.0017 Resc_io: 4.0000 Resc_cpu: 48096 Resp: 4.0017 Resp_io: 4.0000 Resc_cpu: 48096 kkoipt: Query block SEL$1 (#0) ******* UNPARSED QUERY IS ******* SELECT "KILLDB"."ID" "ID","KILLDB"."NAME" "NAME" FROM "ROGER"."KILLDB" "KILLDB" WHERE "KILLDB"."ID"=2 kkoqbc-subheap (delete addr=0xb72ebdd8, in-use=9964, alloc=11176) kkoqbc-end : call(in-use=11616, alloc=32736), compile(in-use=32992, alloc=33876) apadrv-end: call(in-use=11616, alloc=32736), compile(in-use=33528, alloc=33876) sql_id=53zmp1j4dsgy4. Current SQL statement for this session: select * from killdb where id=2 ============ Plan Table ============ -------------------------------------------------+-----------------------------------+ | Id | Operation | Name | Rows | Bytes | Cost | Time | -------------------------------------------------+-----------------------------------+ | 0 | SELECT STATEMENT | | | | 4 | | | 1 | TABLE ACCESS BY INDEX ROWID | KILLDB | 52 | 676 | 4 | 00:00:01 | ==> 52 | 2 | INDEX RANGE SCAN | KILLDB_IDX| 53 | | 3 | 00:00:01 | ==> 53 -------------------------------------------------+-----------------------------------+ Predicate Information: ---------------------- 2 - access("ID"=2) |
|
1 |
虽然前面收集统计信息是size 254，实际上buckets只有2个，如下： |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
SQL> set lines 150 SQL> col owner for a15 SQL> col table_name for a15 SQL> select owner, 2 table_name, 3 COLUMN_NAME, 4 NUM_DISTINCT, 5 DENSITY, 6 NUM_BUCKETS, 7 HISTOGRAM 8 from dba_tab_col_statistics 9 where table_name = 'KILLDB' 10 and owner = 'ROGER'; OWNER TABLE_NAME COLUMN_NAME NUM_DISTINCT DENSITY NUM_BUCKETS HISTOGRAM -------- --------------- -------------- ------------ ---------- ----------- --------------- ROGER KILLDB ID 2 2.5625E-06 2 FREQUENCY ROGER KILLDB NAME 2 2.5625E-06 2 FREQUENCY |
|
1 2 3 4 5 6 |
上面的过滤因子2.6901e-04 是什么计算出来的，还不太清楚，特别是直方图存在的情况下， 对于无直方图的情况比较简单，过滤因子即为1/NDV，所以card计算公式就为如下： Cardinality = 行数*过滤因子 计算结果如下： |
|
1 2 3 4 5 |
SQL> select 195119 * (2.6901e-04) from dual; 195119*(2.6901E-04) ------------------- 52.4889622 |
|
1 |
总的来说，还有些地方没有搞明白，还需要继续研究！ |
Leave a Reply
You must be logged in to post a comment.