亚洲AV日韩AⅤ综合手机在线观看,激情婷婷久久综合色,欧美色五月婷婷久久,久久国产精品99久久人人澡

  • <abbr id="uk6uq"><abbr id="uk6uq"></abbr></abbr>
  • <tbody id="uk6uq"></tbody>
  • Oracle認(rèn)證:ORACLE綁定變量BINDPEEKING

    時間:2024-08-25 15:57:01 Oracle認(rèn)證 我要投稿
    • 相關(guān)推薦

    Oracle認(rèn)證:ORACLE綁定變量BINDPEEKING

      ORACLE 在9i之后引入了bind peeking,通過bind peeking,oracle可以在硬解析的時候窺探綁定變量的值,并根據(jù)當(dāng)前綁定變量的值生成執(zhí)行計劃。在oracle 9i之前的版本中,oracle僅僅通過統(tǒng)計信息來生成執(zhí)行計劃。

      下面看一下不同版本oracle下綁定變量對執(zhí)行計劃的影響

      SQL> alter system flush shared_pool;

      系統(tǒng)已更改。

      SQL> alter system set optimizer_features_enable='8.1.7';

      系統(tǒng)已更改。

      SQL> var v number;

      SQL> exec :v := 1;

      PL/SQL 過程已成功完成。

      SQL> select count(*) from acs_test_tab where record_type = :v;

      COUNT(*)

      ----------

      1

      SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

      PLAN_TABLE_OUTPUT

      ----------------------------------------------------------------------------------------------------

      SQL_ID3rg5r8sghcvb3, child number 0

      -------------------------------------

      select count(*) from acs_test_tab where record_type = :v

      Plan hash value: 2956728990

      --------------------------------------------------------------------------------

      | Id | Operation | Name | Rows | Bytes | Cost |

      --------------------------------------------------------------------------------

      | 0 | SELECT STATEMENT | | | | 3 |

      | 1 | SORT AGGREGATE | | 1 | 4 | |

      |* 2 | INDEX RANGE SCAN| ACS_TEST_TAB_RECORD_TYPE_I | 2 | 8 | 3 |

      --------------------------------------------------------------------------------

      Predicate Information (identified by operation id):

      ---------------------------------------------------

      2 - access("RECORD_TYPE"=:V)

      已選擇47行。

      SQL> alter system flush shared_pool;

      系統(tǒng)已更改。

      SQL> alter system set optimizer_features_enable='11.2.0.3.1';

      系統(tǒng)已更改。

      SQL> var v number;

      SQL> exec :v := 1;

      PL/SQL 過程已成功完成。

      SQL> select count(*) from acs_test_tab where record_type = :v;

      COUNT(*)

      ----------

      1

      SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

      PLAN_TABLE_OUTPUT

      ----------------------------------------------------------------------------------------------------

      SQL_ID3rg5r8sghcvb3, child number 0

      -------------------------------------

      select count(*) from acs_test_tab where record_type = :v

      Plan hash value: 2956728990

      ------------------------------------------------------------------------------------------------

      | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

      ------------------------------------------------------------------------------------------------

      | 0 | SELECT STATEMENT | | | | 3 (100)| |

      | 1 | SORT AGGREGATE | | 1 | 4 | | |

      |* 2 | INDEX RANGE SCAN| ACS_TEST_TAB_RECORD_TYPE_I | 1 | 4 | 3 (0)| 00:00:01 |

      ------------------------------------------------------------------------------------------------

      Peeked Binds (identified by position):

      --------------------------------------

      1 - :V (NUMBER): 1 --綁定變量窺探

      Predicate Information (identified by operation id):

      ---------------------------------------------------

      2 - access("RECORD_TYPE"=:V)

      已選擇49行。

      SQL> alter system flush shared_pool;

      系統(tǒng)已更改。

      SQL> exec :v := 2;

      PL/SQL 過程已成功完成。

      SQL> select count(*) from acs_test_tab where record_type = :v;

      COUNT(*)

      ----------

      50000

      SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

      PLAN_TABLE_OUTPUT

      ----------------------------------------------------------------------------------------------------

      SQL_ID3rg5r8sghcvb3, child number 0

      -------------------------------------

      select count(*) from acs_test_tab where record_type = :v

      Plan hash value: 2957754476

      ----------------------------------------------------------------------------------------------------

      | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

      ----------------------------------------------------------------------------------------------------

      | 0 | SELECT STATEMENT | | | | 136 (100)| |

      | 1 | SORT AGGREGATE | | 1 | 4 || |

      |* 2 | INDEX FAST FULL SCAN| ACS_TEST_TAB_RECORD_TYPE_I | 48031 | 187K| 136 (1)| 00:00:02 |

      ----------------------------------------------------------------------------------------------------

      Peeked Binds (identified by position):

      --------------------------------------

      1 - :V (NUMBER): 2 --綁定變量窺探,綁定變量會影響最初硬解析的執(zhí)行計劃

      Predicate Information (identified by operation id):

      ---------------------------------------------------

      2 - filter("RECORD_TYPE"=:V)

      已選擇49行。

      使用綁定變量窺測的好處是:可以幫助優(yōu)化器在第一次硬解析時選擇最優(yōu)的執(zhí)行計劃。但是同時這也是其弊端:在第一次硬解析后,后面發(fā)生的所有解析都會使用第一次硬解析生成的執(zhí)行計劃,如果數(shù)據(jù)的分布是均勻的,問題不大,如果數(shù)據(jù)分布式傾斜的,那么第一次硬解析生成的執(zhí)行計劃未必是最優(yōu)的,甚至可能是非常糟糕的。例如:

      SQL> show parameter optimizer_feat

      NAME TYPE VALUE

      ------------------------------------ ----------- ------------------------------

      optimizer_features_enable string 11.2.0.3.1

      SQL> alter system flush shared_pool;

      系統(tǒng)已更改。

      SQL> var v number;

      SQL> exec :v := 2;

      PL/SQL 過程已成功完成。

      SQL> select count(*) from acs_test_tab where record_type = :v;

      COUNT(*)

      ----------

      50000

      SQL> select * from table(dbms_xplan.display_cursor);

      PLAN_TABLE_OUTPUT

      ----------------------------------------------------------------------------------------------------

      SQL_ID3rg5r8sghcvb3, child number 0

      -------------------------------------

      select count(*) from acs_test_tab where record_type = :v

      Plan hash value: 2957754476

      ----------------------------------------------------------------------------------------------------

      | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

      ----------------------------------------------------------------------------------------------------

      | 0 | SELECT STATEMENT | | | | 136 (100)| |

      | 1 | SORT AGGREGATE | | 1 | 4 || |

      |* 2 | INDEX FAST FULL SCAN| ACS_TEST_TAB_RECORD_TYPE_I | 48031 | 187K| 136 (1)| 00:00:02 |

      ----------------------------------------------------------------------------------------------------

      Predicate Information (identified by operation id):

      ---------------------------------------------------

      2 - filter("RECORD_TYPE"=:V)

      已選擇19行。

      SQL> exec :v := 1

      PL/SQL 過程已成功完成。

      SQL> select count(*) from acs_test_tab where record_type = :v;

      COUNT(*)

      ----------

      1

      SQL> select * from table(dbms_xplan.display_cursor);

      PLAN_TABLE_OUTPUT

      ----------------------------------------------------------------------------------------------------

      SQL_ID3rg5r8sghcvb3, child number 0

      -------------------------------------

      select count(*) from acs_test_tab where record_type = :v

      Plan hash value: 2957754476

      ----------------------------------------------------------------------------------------------------

      | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

      ----------------------------------------------------------------------------------------------------

      | 0 | SELECT STATEMENT | | | | 136 (100)| |

      | 1 | SORT AGGREGATE | | 1 | 4 || |

      |* 2 | INDEX FAST FULL SCAN| ACS_TEST_TAB_RECORD_TYPE_I | 48031 | 187K| 136 (1)| 00:00:02 |

      ----------------------------------------------------------------------------------------------------

      Predicate Information (identified by operation id):

      ---------------------------------------------------

      2 - filter("RECORD_TYPE"=:V)

      已選擇19行。

      SQL> select count(*) from acs_test_tab where record_type = 1;

      COUNT(*)

      ----------

      1

      SQL> select * from table(dbms_xplan.display_cursor);

      PLAN_TABLE_OUTPUT

      ----------------------------------------------------------------------------------------------------

      SQL_ID1pxm87f6yd0bp, child number 0

      -------------------------------------

      select count(*) from acs_test_tab where record_type = 1

      Plan hash value: 2956728990

      ------------------------------------------------------------------------------------------------

      | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

      ------------------------------------------------------------------------------------------------

      | 0 | SELECT STATEMENT | | | | 3 (100)| |

      | 1 | SORT AGGREGATE | | 1 | 4 | | |

      |* 2 | INDEX RANGE SCAN| ACS_TEST_TAB_RECORD_TYPE_I | 1 | 4 | 3 (0)| 00:00:01 |

      ------------------------------------------------------------------------------------------------

      Predicate Information (identified by operation id):

      ---------------------------------------------------

      2 - access("RECORD_TYPE"=1)

      已選擇19行。

      對于變量v的取值為1的執(zhí)行計劃和采用常量1的執(zhí)行計劃性能差距還是比較大的。

      總結(jié):oracle在9i后引入變量窺測技術(shù),該技術(shù)對于數(shù)據(jù)分布均勻的數(shù)據(jù)是非常合適的,但是對于分布傾斜的數(shù)據(jù)或者在OLAP系統(tǒng)中是不建議使用的。

    【Oracle認(rèn)證:ORACLE綁定變量BINDPEEKING】相關(guān)文章:

    Oracle認(rèn)證作用03-19

    Oracle認(rèn)證簡介11-30

    Oracle最新認(rèn)證03-09

    Oracle認(rèn)證途徑03-20

    Oracle認(rèn)證:Oracle控制件文件修復(fù)03-18

    Oracle認(rèn)證:Oracle內(nèi)存結(jié)構(gòu)研究-PGA篇03-08

    Oracle認(rèn)證:Oracle避免全表掃描方式03-08

    Oracle認(rèn)證職業(yè)前景03-19

    Oracle認(rèn)證考試技巧03-19