深入解析参数 OPTIMIZER_DYNAMIC_SAMPLING
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
|
1 2 3 4 5 6 7 8 9 |
下午看同事的博客提到了参数 OPTIMIZER_DYNAMIC_SAMPLING, 原帖地址: <a href="http://www.muzijiang.cn/index.php/2012/04/gather_columns_group_stats/" style="font-size: 12px; text-decoration: underline; color: #0000ff; font-family: monospace;">http://www.muzijiang.cn/index.php/2012/04/gather_columns_group_stats/</a> 没有提到关于该参数的详细说明，下面我这里就在研究一下，跟大家分享。 10g 的官方文档有如下的简单描述： |
|
1 2 3 |
If OPTIMIZER_FEATURES_ENABLE is set to 10.0.0 or higher, then 2 If OPTIMIZER_FEATURES_ENABLE is set to 9.2.0, then 1 If OPTIMIZER_FEATURES_ENABLE is set to 9.0.1 or lower, then 0 |
|
1 2 3 |
该参数的取值范围是0~10. 我们这里通过自己的实验来探究该参数的奥秘。 |
|
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 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 |
-- Create test table SQL> create table t_stats as select * from dba_objects; Table created. SQL> select count(*) from t_stats; COUNT(*) ---------- 50936 SQL> Exec DBMS_STATS.GATHER_TABLE_STATS(ownname=> 'ROGER', tabname=> 'T_STATS', estimate_percent=>100, degree=>2); PL/SQL procedure successfully completed. SQL> set lines 150 SQL> select count(*) from t_stats where object_id+data_object_id >1000; COUNT(*) ---------- 3841 SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last')); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------- SQL_ID 3n8ukg0rs29p8, child number 0 ------------------------------------- select count(*) from t_stats where object_id+data_object_id >1000 Plan hash value: 3162492167 ----------------------------------------------- | Id | Operation | Name | E-Rows | ----------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 | SORT AGGREGATE | | 1 | |* 2 | TABLE ACCESS FULL| T_STATS | 2547 | ----------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("OBJECT_ID"+"DATA_OBJECT_ID">1000) Note ----- - Warning: basic plan statistics not available. These are only collected when: PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------- * hint 'gather_plan_statistics' is used for the statement or * parameter 'statistics_level' is set to 'ALL', at session or system level 25 rows selected. SQL> alter session set statistics_level=all; Session altered. SQL> select count(*) from t_stats where object_id+data_object_id >1000; COUNT(*) ---------- 3841 SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------- SQL_ID cb4f1rba8yn42, child number 0 ------------------------------------- select count(*) from t_stats where object_id+data_object_id >1000 Plan hash value: 3162492167 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.03 | 705 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.03 | 705 | |* 2 | TABLE ACCESS FULL| T_STATS | 1 | 2547 | 3841 |00:00:00.02 | 705 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("OBJECT_ID"+"DATA_OBJECT_ID">1000) 19 rows selected. SQL> alter system flush shared_pool; System altered. SQL> alter session set statistics_level=typical; Session altered. SQL> select /*+ gather_plan_statistics */ count(*) from t_stats where object_id+data_object_id >1000; COUNT(*) ---------- 3841 SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------- SQL_ID f0t9aqq1jrgsv, child number 0 ------------------------------------- select /*+ gather_plan_statistics */ count(*) from t_stats where object_id+data_object_id >1000 Plan hash value: 3162492167 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 705 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 705 | |* 2 | TABLE ACCESS FULL| T_STATS | 1 | 2547 | 3841 |00:00:00.02 | 705 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("OBJECT_ID"+"DATA_OBJECT_ID">1000) 20 rows selected. ---- Test optimizer_dynamic_sampling SQL> alter system flush shared_pool; System altered. SQL> alter session set optimizer_dynamic_sampling=2; Session altered. SQL> select /*+ gather_plan_statistics */ count(*) from t_stats where object_id+data_object_id >1000; COUNT(*) ---------- 3841 SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------- SQL_ID f0t9aqq1jrgsv, child number 0 ------------------------------------- select /*+ gather_plan_statistics */ count(*) from t_stats where object_id+data_object_id >1000 Plan hash value: 3162492167 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 705 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 705 | |* 2 | TABLE ACCESS FULL| T_STATS | 1 | 2547 | 3841 |00:00:00.02 | 705 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("OBJECT_ID"+"DATA_OBJECT_ID">1000) 20 rows selected. SQL> alter system flush shared_pool; System altered. SQL> alter session set optimizer_dynamic_sampling=3; Session altered. SQL> select /*+ gather_plan_statistics */ count(*) from t_stats where object_id+data_object_id >1000; COUNT(*) ---------- 3841 SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------- SQL_ID f0t9aqq1jrgsv, child number 0 ------------------------------------- select /*+ gather_plan_statistics */ count(*) from t_stats where object_id+data_object_id >1000 Plan hash value: 3162492167 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 705 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 705 | |* 2 | TABLE ACCESS FULL| T_STATS | 1 | 1490 | 3841 |00:00:00.02 | 705 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("OBJECT_ID"+"DATA_OBJECT_ID">1000) Note ----- - dynamic sampling used for this statement 24 rows selected. SQL> alter system flush shared_pool; System altered. SQL> alter session set optimizer_dynamic_sampling=4; Session altered. SQL> select /*+ gather_plan_statistics */ count(*) from t_stats where object_id+data_object_id >1000; COUNT(*) ---------- 3841 SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last')); PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------- SQL_ID 885p45bwprw2y, child number 0 ------------------------------------- select /*+ gather_plan_statistics */ count(*) from t_stats where object_id+data_object_id >1000 Plan hash value: 3162492167 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 705 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 705 | |* 2 | TABLE ACCESS FULL| T_STATS | 1 | 1490 | 3841 |00:00:00.02 | 705 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("OBJECT_ID"+"DATA_OBJECT_ID">1000) Note ----- - dynamic sampling used for this statement 24 rows selected. SQL> alter system flush shared_pool; System altered. SQL> alter session set optimizer_dynamic_sampling=5; Session altered. SQL> select /*+ gather_plan_statistics */ count(*) from t_stats where object_id+data_object_id >1000; COUNT(*) ---------- 3841 SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------- SQL_ID f0t9aqq1jrgsv, child number 0 ------------------------------------- select /*+ gather_plan_statistics */ count(*) from t_stats where object_id+data_object_id >1000 Plan hash value: 3162492167 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 705 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 705 | |* 2 | TABLE ACCESS FULL| T_STATS | 1 | 3281 | 3841 |00:00:00.05 | 705 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("OBJECT_ID"+"DATA_OBJECT_ID">1000) Note ----- - dynamic sampling used for this statement 24 rows selected. SQL> alter system flush shared_pool; System altered. SQL> alter session set optimizer_dynamic_sampling=6; Session altered. SQL> select /*+ gather_plan_statistics */ count(*) from t_stats where object_id+data_object_id >1000; COUNT(*) ---------- 3841 SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------- SQL_ID 885p45bwprw2y, child number 0 ------------------------------------- select /*+ gather_plan_statistics */ count(*) from t_stats where object_id+data_object_id >1000 Plan hash value: 3162492167 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 705 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 705 | |* 2 | TABLE ACCESS FULL| T_STATS | 1 | 3328 | 3841 |00:00:00.04 | 705 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("OBJECT_ID"+"DATA_OBJECT_ID">1000) Note ----- - dynamic sampling used for this statement 24 rows selected. SQL> alter session set optimizer_dynamic_sampling=8; Session altered. SQL> alter system flush shared_pool; System altered. SQL> alter session set optimizer_dynamic_sampling=8; Session altered. SQL> select /*+ gather_plan_statistics */ count(*) from t_stats where object_id+data_object_id >1000; COUNT(*) ---------- 3841 SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last')); PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------- SQL_ID 885p45bwprw2y, child number 0 ------------------------------------- select /*+ gather_plan_statistics */ count(*) from t_stats where object_id+data_object_id >1000 Plan hash value: 3162492167 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 705 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 705 | |* 2 | TABLE ACCESS FULL| T_STATS | 1 | 3841 | 3841 |00:00:00.02 | 705 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("OBJECT_ID"+"DATA_OBJECT_ID">1000) Note ----- - dynamic sampling used for this statement 24 rows selected. SQL> alter system flush shared_pool; System altered. SQL> alter session set optimizer_dynamic_sampling=9; Session altered. SQL> select /*+ gather_plan_statistics */ count(*) from t_stats where object_id+data_object_id >1000; COUNT(*) ---------- 3841 SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------- SQL_ID f0t9aqq1jrgsv, child number 0 ------------------------------------- select /*+ gather_plan_statistics */ count(*) from t_stats where object_id+data_object_id >1000 Plan hash value: 3162492167 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 705 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 705 | |* 2 | TABLE ACCESS FULL| T_STATS | 1 | 3841 | 3841 |00:00:00.02 | 705 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("OBJECT_ID"+"DATA_OBJECT_ID">1000) Note ----- - dynamic sampling used for this statement 24 rows selected. ########## 10053 trace event ########## SQL> alter system flush shared_pool; System altered. SQL> alter session set optimizer_dynamic_sampling=6; Session altered. SQL> alter session set events '10053 trace name context forever, level 1'; Session altered. SQL> select /*+ gather_plan_statistics */ count(*) from t_stats where object_id+data_object_id >1000; COUNT(*) ---------- 3841 SQL> alter session set events '10053 trace name context off'; Session altered. |
|
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 |
########## 10053 trace log ########## **************** QUERY BLOCK TEXT **************** select /*+ gather_plan_statistics */ count(*) from t_stats where object_id+data_object_id >1000 ********************* QUERY BLOCK SIGNATURE ********************* qb name was generated signature (optimizer): qb_name=SEL$1 nbfros=1 flg=0 fro(0): flg=0 objn=54933 hint_alias="T_STATS"@"SEL$1" ***************************** SYSTEM STATISTICS INFORMATION ***************************** Using NOWORKLOAD Stats CPUSPEED: 2398 millions instruction/sec IOTFRSPEED: 4096 bytes per millisecond (default is 4096) IOSEEKTIM: 10 milliseconds (default is 10) *************************************** BASE STATISTICAL INFORMATION *********************** Table Stats:: Table: T_STATS Alias: T_STATS #Rows: 50936 #Blks: 720 AvgRowLen: 93.00 *************************************** SINGLE TABLE ACCESS PATH ----------------------------------------- BEGIN Single Table Cardinality Estimation ----------------------------------------- *** 2012-04-11 00:46:03.969 ** Performing dynamic sampling initial checks. ** ** Dynamic sampling initial checks returning TRUE (level = 6). *** 2012-04-11 00:46:03.970 ** Generated dynamic sampling query: query text : SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0) FROM (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("T_STATS") FULL("T_STATS") NO_PARALLEL_INDEX("T_STATS") */ 1 AS C1, CASE WHEN "T_STATS"."OBJECT_ID"+"T_STATS"."DATA_OBJECT_ID">1000 THEN 1 ELSE 0 END AS C2 FROM "T_STATS" SAMPLE BLOCK (17.638889 , 1) SEED (1) "T_STATS") SAMPLESUB *** 2012-04-11 00:46:03.975 ** Executed dynamic sampling query: level : 6 sample pct. : 17.638889 <== (128-1)/720=0.176388889 actual sample size : 8938 filtered sample card. : 584 orig. card. : 50936 block cnt. table stat. : 720 block cnt. for sampling: 720 max. sample block cnt. : 128 <== 这里是最大是sample block数量，128个block。 sample block cnt. : 127 <== 这里我猜测是除去段头得到的，也就是说128个block里面肯定要包含段头。 min. sel. est. : 0.05000000 ** Using single table dynamic sel. est. : 0.06533900 <== 这里是什么来的呢？ 584/8938=0.065339002 Table: T_STATS Alias: T_STATS Card: Original: 50936 Rounded: 3328 Computed: 3328.11 Non Adjusted: 3328.11 ----------------------------------------- END Single Table Cardinality Estimation ----------------------------------------- Access Path: TableScan Cost: 196.76 Resp: 196.76 Degree: 0 Cost_io: 196.00 Cost_cpu: 21936317 Resp_io: 196.00 Resp_cpu: 21936317 Best:: AccessPath: TableScan Cost: 196.76 Degree: 1 Resp: 196.76 Card: 3328.11 Bytes: 0 <== 这里card当然就是sel*table rownums了。 *************************************** OPTIMIZER STATISTICS AND COMPUTATIONS *************************************** GENERAL PLANS *************************************** Considering cardinality-based initial join order. Permutations for Starting Table :0 *********************** Join order[1]: T_STATS[T_STATS]#0 *********************** Best so far: Table#: 0 cost: 196.7622 card: 3328.1074 bytes: 23296 (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: 196.7622 Degree: 1 Card: 3328.0000 Bytes: 23296 Resc: 196.7622 Resc_io: 196.0000 Resc_cpu: 21936317 Resp: 196.7622 Resp_io: 196.0000 Resc_cpu: 21936317 kkoipt: Query block SEL$1 (#0) ******* UNPARSED QUERY IS ******* SELECT COUNT(*) "COUNT(*)" FROM "ROGER"."T_STATS" "T_STATS" WHERE "T_STATS"."OBJECT_ID"+"T_STATS"."DATA_OBJECT_ID">1000 kkoqbc-subheap (delete addr=0xb72ebe3c, in-use=10232, alloc=11076) kkoqbc-end : call(in-use=15100, alloc=32736), compile(in-use=32720, alloc=33876) apadrv-end: call(in-use=15100, alloc=32736), compile(in-use=33316, alloc=33876) sql_id=f0t9aqq1jrgsv. Current SQL statement for this session: select /*+ gather_plan_statistics */ count(*) from t_stats where object_id+data_object_id >1000 ============ Plan Table ============ --------------------------------------+-----------------------------------+ | Id | Operation | Name | Rows | Bytes | Cost | Time | --------------------------------------+-----------------------------------+ | 0 | SELECT STATEMENT | | | | 197 | | | 1 | SORT AGGREGATE | | 1 | 7 | | | | 2 | TABLE ACCESS FULL | T_STATS | 3328 | 23K | 197 | 00:00:03 | --------------------------------------+-----------------------------------+ Predicate Information: ---------------------- 2 - filter("OBJECT_ID"+"DATA_OBJECT_ID">1000) Content of other_xml column =========================== db_version : 10.2.0.5 parse_schema : ROGER dynamic_sampling: yes plan_hash : 3162492167 Outline Data: /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('10.2.0.5') OPT_PARAM('optimizer_dynamic_sampling' 6) ALL_ROWS OUTLINE_LEAF(@"SEL$1") FULL(@"SEL$1" "T_STATS"@"SEL$1") END_OUTLINE_DATA */ |
|
1 |
下面来自官方文档关于在10g中，该参数 level的详细描述： |
|
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 |
Level 0: Do not use dynamic Level 1: Sample all tables twing criteria are met: (1) there is at least (2) this unanalyzed tars in a subquery or non-mergeable view; (3) this unanalyzed ta (4) this unanalyzed tablocks that would be used for dynamic sampling of this table. The number of blocksic sampling blocks (32). Level 2: Apply dynamic sampl The number of blocks saf dynamic sampling blocks. Level 3: Apply dynamic samplteria, plus all tables for which standard selectivity ete that is a potential dynamic sampling predicate. The number of blocks s sampling blocks. For unanalyzed tables, the number of blocks sof dynamic sampling blocks. Level 4: Apply dynamic samplteria, plus all tables that have single-table predicates thatr of blocks sampled is the default number of dynamic sampling blocks. For unanalyzed tables, the number of blocks sampled is two times the default number of dynamic sampling blocks. Levels 5, 6, 7, 8, and 9: Apply dynamic sampling to all tables that meet the previous level criteria using 2, 4, 8, 32, or 128 times the default number of dynamic sampling blocks respectively. Level 10: Apply dynamic sampling to all tables that meet the Level 9 criteria using all blocks in the table. |
|
1 |
下面是一个换算结果图： |
|
1 2 3 4 5 6 7 8 9 10 11 12 |
level Blocks -------- -------- 1 32 2 32 3 32 4 64 5 64 6 128 7 256 8 1024 9 4096 10 all blocks |
|
1 |
下面我们来研究下默认的情况下sample 比例是多少呢？其实就是5%，也就是32个block。 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
########## 10053 trace event on ########## SQL> alter system flush shared_pool; System altered. SQL> alter session set optimizer_dynamic_sampling=2; Session altered. SQL> alter session set events '10053 trace name context forever, level 1'; Session altered. SQL> select /*+ gather_plan_statistics */ count(*) from t_stats where object_id+data_object_id >1000; COUNT(*) ---------- 3841 SQL> alter session set events '10053 trace name context off'; Session altered. |
|
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 |
########## 10053 trace file format ########## **************** QUERY BLOCK TEXT **************** select /*+ gather_plan_statistics */ count(*) from t_stats where object_id+data_object_id >1000 ********************* QUERY BLOCK SIGNATURE ********************* qb name was generated signature (optimizer): qb_name=SEL$1 nbfros=1 flg=0 fro(0): flg=0 objn=54933 hint_alias="T_STATS"@"SEL$1" ***************************** SYSTEM STATISTICS INFORMATION ***************************** Using NOWORKLOAD Stats CPUSPEED: 2398 millions instruction/sec IOTFRSPEED: 4096 bytes per millisecond (default is 4096) IOSEEKTIM: 10 milliseconds (default is 10) *************************************** BASE STATISTICAL INFORMATION *********************** Table Stats:: Table: T_STATS Alias: T_STATS #Rows: 50936 #Blks: 720 AvgRowLen: 93.00 *************************************** SINGLE TABLE ACCESS PATH ----------------------------------------- BEGIN Single Table Cardinality Estimation ----------------------------------------- Table: T_STATS Alias: T_STATS Card: Original: 50936 Rounded: 2547 Computed: 2546.80 Non Adjusted: 2546.80 ----------------------------------------- END Single Table Cardinality Estimation ----------------------------------------- Access Path: TableScan Cost: 196.76 Resp: 196.76 Degree: 0 Cost_io: 196.00 Cost_cpu: 21936317 Resp_io: 196.00 Resp_cpu: 21936317 Best:: AccessPath: TableScan Cost: 196.76 Degree: 1 Resp: 196.76 Card: 2546.80 Bytes: 0 *************************************** OPTIMIZER STATISTICS AND COMPUTATIONS *************************************** GENERAL PLANS *************************************** Considering cardinality-based initial join order. Permutations for Starting Table :0 *********************** Join order[1]: T_STATS[T_STATS]#0 *********************** Best so far: Table#: 0 cost: 196.7622 card: 2546.8000 bytes: 17829 (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: 196.7622 Degree: 1 Card: 2547.0000 Bytes: 17829 Resc: 196.7622 Resc_io: 196.0000 Resc_cpu: 21936317 Resp: 196.7622 Resp_io: 196.0000 Resc_cpu: 21936317 kkoipt: Query block SEL$1 (#0) ******* UNPARSED QUERY IS ******* SELECT COUNT(*) "COUNT(*)" FROM "ROGER"."T_STATS" "T_STATS" WHERE "T_STATS"."OBJECT_ID"+"T_STATS"."DATA_OBJECT_ID">1000 kkoqbc-subheap (delete addr=0xb72ebe3c, in-use=9720, alloc=11076) kkoqbc-end : call(in-use=12348, alloc=32736), compile(in-use=32484, alloc=33876) apadrv-end: call(in-use=12348, alloc=32736), compile(in-use=33020, alloc=33876) sql_id=f0t9aqq1jrgsv. Current SQL statement for this session: select /*+ gather_plan_statistics */ count(*) from t_stats where object_id+data_object_id >1000 ============ Plan Table ============ --------------------------------------+-----------------------------------+ | Id | Operation | Name | Rows | Bytes | Cost | Time | --------------------------------------+-----------------------------------+ | 0 | SELECT STATEMENT | | | | 197 | | | 1 | SORT AGGREGATE | | 1 | 7 | | | | 2 | TABLE ACCESS FULL | T_STATS | 2547 | 17K | 197 | 00:00:03 | --------------------------------------+-----------------------------------+ Predicate Information: ---------------------- 2 - filter("OBJECT_ID"+"DATA_OBJECT_ID">1000) Content of other_xml column =========================== db_version : 10.2.0.5 parse_schema : ROGER plan_hash : 3162492167 Outline Data: /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('10.2.0.5') ALL_ROWS OUTLINE_LEAF(@"SEL$1") FULL(@"SEL$1" "T_STATS"@"SEL$1") END_OUTLINE_DATA */ |
|
1 2 3 4 5 |
SQL> select 50936*0.05 from dual; 50936*0.05 ---------- 2546.8 <== 进行四舍五入以后即是我们看到的card值。 |
|
1 2 3 4 5 6 7 |
所以准确的讲，只有参数optimizer_dynamic_sampling 越大，得到的数据才是最准确的， 当然这里也存在弊端，对于比较大的表来说，level越高的话，对于系统资源的消耗越大。 我们也可以发现oracle的CBO非常强悍了，在缺少统计信息的情况下，我们使用hint即可 得到正常的执行计划，只是说对于默认情况下该参数level较低，数据不太准确而已。 其实对于非常小的表，默认的level应该就是准确的了。 下面我复制一下这个测试表，数据量稍微小点，用来验证我的观点： |
|
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 |
SQL> create table t_stats_2 as select * from t_stats where rownum < 2000; Table created. SQL> exec dbms_stats.gather_table_stats(ownname=> 'ROGER', tabname=> 'T_STATS_2', estimate_percent=>100, degree=>2); PL/SQL procedure successfully completed. SQL> alter system flush shared_pool; System altered. SQL> select /*+ gather_plan_statistics */ count(*) from t_stats_2 where object_id+data_object_id >1000; COUNT(*) ---------- 290 SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last')); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------- SQL_ID 8zv1brsz19pvj, child number 0 ------------------------------------- select /*+ gather_plan_statistics */ count(*) from t_stats_2 where object_id+data_object_id >1000 Plan hash value: 3465718877 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 28 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 28 | |* 2 | TABLE ACCESS FULL| T_STATS_2 | 1 | 290 | 290 |00:00:00.01 | 28 | ------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("OBJECT_ID"+"DATA_OBJECT_ID">1000) Note ----- - dynamic sampling used for this statement 24 rows selected. |
|
1 |
得到验证了，所以这或许也就是为什么oracle将这个参数默认设置为2的原因吧(10g中). |
4 Responses to “深入解析参数 OPTIMIZER_DYNAMIC_SAMPLING”
good
深入学习
11gR2 对应关系已经有所改变了，如下：
level Blocks——– ——–1 322 643 644 645 1286 2567 5128 10249 409610 ALL blocks
good
Leave a Reply
You must be logged in to post a comment.