亚洲中字慕日产2020,大陆极品少妇内射AAAAAA,无码av大香线蕉伊人久久,久久精品国产亚洲av麻豆网站

資訊專欄INFORMATION COLUMN

有關(guān)11g關(guān)閉自適應(yīng)游標(biāo)需要注意的問(wèn)題整理

IT那活兒 / 1518人閱讀
有關(guān)11g關(guān)閉自適應(yīng)游標(biāo)需要注意的問(wèn)題整理
一. 為什么要關(guān)閉自適應(yīng)游標(biāo)


  • 可能會(huì)導(dǎo)致一定數(shù)量的硬解析

  • 可能會(huì)導(dǎo)致一定數(shù)量的額外的child cursor掛在同一個(gè)父游標(biāo)下，這會(huì)增加軟解析、軟軟解析時(shí)查找匹配子游標(biāo)的工作量

  • 為了存儲(chǔ)這些額外的子游標(biāo)，shared pool在空間方面也會(huì)承受額外的壓力，需要額外的調(diào)整shared pool的大小


二. 如何關(guān)閉

如果因?yàn)殚_(kāi)啟了自適應(yīng)游標(biāo)共享而導(dǎo)致系統(tǒng)產(chǎn)生了過(guò)多的子游標(biāo)，進(jìn)而導(dǎo)致shared pool空間緊張或者過(guò)多的mutex等待，則可以通過(guò)如下任意的一種方式來(lái)關(guān)閉自適應(yīng)游標(biāo)共享

  • 將隱含參數(shù)_optimizer_extended_cursor_sharing和_optimizer_extended_cursor_sharing_rel的值設(shè)為none，這樣就關(guān)閉了可擴(kuò)展性游標(biāo)共享。一旦可擴(kuò)展性游標(biāo)共享被禁，所有的子游標(biāo)不能再被標(biāo)記為綁定敏感，而綁定敏感是綁定感知的前提條件，一旦不能被標(biāo)記為綁定敏感，則后續(xù)的綁定感知就無(wú)法進(jìn)行

  • 將隱含參數(shù)_optimizer_adaptive_cursor_sharing的值設(shè)置為false，一旦這個(gè)值被設(shè)置的false，子游標(biāo)就不能被標(biāo)記為綁定感知，也就是說(shuō)自適應(yīng)游標(biāo)被關(guān)閉了

當(dāng)啟用綁定變量窺探之后，每次Oracle以硬解析的方式解析了使用綁定變量的目標(biāo)SQL時(shí)，Oracle都會(huì)實(shí)際窺探（Peeking）一下對(duì)應(yīng)綁定變量變量的具體輸入值，并以這些值為標(biāo)準(zhǔn)，來(lái)決定這些使用了綁定變量的目標(biāo)SQL的where條件中的selectivity和Cardinality的值，并根據(jù)此信息來(lái)選擇該SQL的執(zhí)行計(jì)劃，當(dāng)綁定變量的目標(biāo)SQL再次執(zhí)行時(shí)（軟解析或者軟軟解析），即便此時(shí)對(duì)應(yīng)綁定變量的具體輸入值和之前硬解析時(shí)對(duì)應(yīng)的值不同，Oracle也會(huì)沿用之前硬解析所產(chǎn)生的解析數(shù)和執(zhí)行計(jì)劃，而不會(huì)再次進(jìn)行窺探的動(dòng)作。


三. 案例演示

1.關(guān)閉參數(shù)

alter system set "_optimizer_adaptive_cursor_sharing"=none sid=*;
alter system set "_optimizer_extended_cursor_sharing"=none sid=*;
或者
alter system set "_optimizer_extended_cursor_sharing_rel"=false sid=*;

2.測(cè)試驗(yàn)證

1) 創(chuàng)建測(cè)試表

create table t as select * from dba_objects;

2) 創(chuàng)建索引

create index t_id on t(status);

3) 執(zhí)行以下SQL，收集統(tǒng)計(jì)信息

select count(*) from t where status=INVALID;
exec dbms_stats.gather_table_stats(dbmon,t,cascade=>true,no_invalidate=>false);

4）查看表status列是否已經(jīng)收集統(tǒng)計(jì)信息，從下面查詢可以看到status已經(jīng)有統(tǒng)計(jì)信息

SQL> select COLUMN_NAME,HISTOGRAM from dba_tab_col_statistics where owner=DBMON and table_name=T;

COLUMN_NAME                    HISTOGRAM
-------------------------------------   -------------------------
OWNER                                NONE
OBJECT_NAME                     NONE
SUBOBJECT_NAME              NONE
OBJECT_ID                            NONE
DATA_OBJECT_ID                 NONE
OBJECT_TYPE                      NONE
CREATED                               NONE
LAST_DDL_TIME                    NONE
TIMESTAMP                            NONE
STATUS                                   FREQUENCY
TEMPORARY                          NONE


COLUMN_NAME                    HISTOGRAM
--------------------------------------  --------------------------
GENERATED                           NONE
SECONDARY                           NONE
NAMESPACE                           NONE
EDITION_NAME                       NONE

5）使用綁定變量進(jìn)行驗(yàn)證測(cè)試

var b varchar2(20);

--當(dāng)我們使用值INVALID，我們肯定是認(rèn)為走索引

exec :b:=INVALID;

select count(distinct object_name) from t where status=:b;

--通過(guò)SQL執(zhí)行計(jì)劃，確是和我們的想法是一樣的

-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name     | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |          |      1 |        |       |    14 (100)|          |      1 |00:00:00.01 |      96 |
|   1 |  SORT AGGREGATE                |          |      1 |      1 |    66 |            |          |      1 |00:00:00.01 |      96 |
|   2 |   VIEW                         | VW_DAG_0 |      1 |    693 | 45738 |    14   (8)| 00:00:01 |    440 |00:00:00.01 |      96 |
|   3 |    HASH GROUP BY               |          |      1 |    693 | 22176 |    14   (8)| 00:00:01 |    440 |00:00:00.01 |      96 |
|   4 |     TABLE ACCESS BY INDEX ROWID| T        |      1 |    695 | 22240 |    13   (0)| 00:00:01 |    659 |00:00:00.01 |      96 |
|*  5 |      INDEX RANGE SCAN          | T_ID     |      1 |    695 |       |     2   (0)| 00:00:01 |    659 |00:00:00.01 |       3 |
-------------------------------------------------------------------------------------------------------------------------------------

--多次執(zhí)行SQL，使該SQL為軟軟解析狀態(tài)

select count(distinct object_name) from t where status=:b;
select count(distinct object_name) from t where status=:b;

--替換綁定變量值VALID，根據(jù)數(shù)據(jù)的分布，我們肯定認(rèn)為該SQL是走全表掃描的

exec :b:=VALID;
select count(distinct object_name) from t where status=:b;

--通過(guò)SQL執(zhí)行計(jì)劃，我們發(fā)現(xiàn)和我們的判斷是相反的，SQL還是走的索引。從buffers可以看到數(shù)據(jù)很大，應(yīng)該走全表掃描

-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name     | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |          |      1 |        |       |    14 (100)|          |      1 |00:00:00.11 |    1443 |
|   1 |  SORT AGGREGATE                |          |      1 |      1 |    66 |            |          |      1 |00:00:00.11 |    1443 |
|   2 |   VIEW                         | VW_DAG_0 |      1 |    693 | 45738 |    14   (8)| 00:00:01 |  51967 |00:00:00.10 |    1443 |
|   3 |    HASH GROUP BY               |          |      1 |    693 | 22176 |    14   (8)| 00:00:01 |  51967 |00:00:00.09 |    1443 |
|   4 |     TABLE ACCESS BY INDEX ROWID| T        |      1 |    695 | 22240 |    13   (0)| 00:00:01 |  85956 |00:00:00.05 |    1443 |
|*  5 |      INDEX RANGE SCAN          | T_ID     |      1 |    695 |       |     2   (0)| 00:00:01 |  85956 |00:00:00.02 |     206 |
-------------------------------------------------------------------------------------------------------------------------------------

--多次執(zhí)行該SQL，執(zhí)行計(jì)劃并沒(méi)有發(fā)生改變。

select count(distinct object_name) from t where status=:b;

-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name     | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |          |      1 |        |       |    14 (100)|          |      1 |00:00:00.09 |    1443 |
|   1 |  SORT AGGREGATE                |          |      1 |      1 |    66 |            |          |      1 |00:00:00.09 |    1443 |
|   2 |   VIEW                         | VW_DAG_0 |      1 |    693 | 45738 |    14   (8)| 00:00:01 |  51967 |00:00:00.09 |    1443 |
|   3 |    HASH GROUP BY               |          |      1 |    693 | 22176 |    14   (8)| 00:00:01 |  51967 |00:00:00.07 |    1443 |
|   4 |     TABLE ACCESS BY INDEX ROWID| T        |      1 |    695 | 22240 |    13   (0)| 00:00:01 |  85956 |00:00:00.04 |    1443 |
|*  5 |      INDEX RANGE SCAN          | T_ID     |      1 |    695 |       |     2   (0)| 00:00:01 |  85956 |00:00:00.01 |     206 |
-------------------------------------------------------------------------------------------------------------------------------------

select count(distinct object_name) from t where status=:b;
-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name     | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |          |      1 |        |       |    14 (100)|          |      1 |00:00:00.09 |    1443 |
|   1 |  SORT AGGREGATE                |          |      1 |      1 |    66 |            |          |      1 |00:00:00.09 |    1443 |
|   2 |   VIEW                         | VW_DAG_0 |      1 |    693 | 45738 |    14   (8)| 00:00:01 |  51967 |00:00:00.09 |    1443 |
|   3 |    HASH GROUP BY               |          |      1 |    693 | 22176 |    14   (8)| 00:00:01 |  51967 |00:00:00.07 |    1443 |
|   4 |     TABLE ACCESS BY INDEX ROWID| T        |      1 |    695 | 22240 |    13   (0)| 00:00:01 |  85956 |00:00:00.04 |    1443 |
|*  5 |      INDEX RANGE SCAN          | T_ID     |      1 |    695 |       |     2   (0)| 00:00:01 |  85956 |00:00:00.01 |     206 |
-------------------------------------------------------------------------------------------------------------------------------------

3. 總結(jié)

簡(jiǎn)單描述：如果關(guān)閉自適應(yīng)游標(biāo)，含有直方圖信息的列使用綁定變量，目標(biāo)SQL執(zhí)行多次后（該SQL為軟解析或者軟軟解析狀態(tài)），后續(xù)綁定變量無(wú)論傳入任何值，都會(huì)沿用第一次執(zhí)行SQL的執(zhí)行計(jì)劃，即使這個(gè)執(zhí)行計(jì)劃是錯(cuò)誤的。和丁偉大哥觀察，移動(dòng)業(yè)務(wù)都是關(guān)閉的，含有直方圖信息的列使用綁定變量需要注意是否存在執(zhí)行計(jì)劃不準(zhǔn)確的問(wèn)題。


END



更多精彩干貨分享

點(diǎn)擊下方名片關(guān)注

IT那活兒

文章版權(quán)歸作者所有，未經(jīng)允許請(qǐng)勿轉(zhuǎn)載,若此文章存在違規(guī)行為，您可以聯(lián)系管理員刪除。

轉(zhuǎn)載請(qǐng)注明本文地址：http://www.ezyhdfw.cn/yun/129783.html

相關(guān)文章

  • Oracle Linux And Oracle Database 11g R2 Intsallati

    摘要：最近工作中用到了作為測(cè)試的數(shù)據(jù)庫(kù)，在裝和折騰了很久，這篇文章就這兩點(diǎn)對(duì)大家分享一些我安裝過(guò)程中遇到的問(wèn)題和注意點(diǎn)，暫時(shí)還未配置，稍后找時(shí)間補(bǔ)上現(xiàn)在網(wǎng)上找一篇好的文章好難，往往要在一個(gè)話題或技術(shù)上找多方資源東拼西湊，互相借鑒才能達(dá)成目的，原因 最近工作中用到了Oracle作為Intel測(cè)試的數(shù)據(jù)庫(kù)，在裝Oracle Linux和Oracle Database 11g R2折騰了很久，這...

    Flands 評(píng)論0 收藏0

發(fā)表評(píng)論

0條評(píng)論

最新活動(dòng)
閱讀需要支付1元查看
<