一生產庫在早上9：30的時候，出現大量的read by other session 等待事件，經查原因是一條sql統計資訊不準走全表掃描導致，對sql進行收集統計資訊後，執行計劃沒有立即改變，最後透過重建索引使執行計劃發生改變，後續分析是由於當時指令碼里面忘記加no_invalidate=>false。在不加這引數時CBO預設是按照_optimizer_invalidation_periond指定的時間去生成新的執行計劃。

-------取樣時間

-------top event

------top sql

------分析當時資料庫佔用資源較多的sql語是sql_id: b0qf7znnrz64c

------執行計劃走的是動態取樣

PLAN_TABLE_OUTPUT

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

SQL_ID b0qf7znnrz64c, child number 0

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

SELECT NVL(SUM(NVL("A1"."CHARGE",0)),0) FROM "ACCT_ITEM_AGGR_11309" "A1" WHERE

"A1"."ACCT_ID"=:LACCTID

Plan hash value: 1549402607

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

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

| 0 | SELECT STATEMENT | | | | 2 (100)| |

| 1 | SORT AGGREGATE | | 1 | 26 | | |

|* 2 | TABLE ACCESS FULL| ACCT_ITEM_AGGR_11309 | 1 | 26 | 2 (0)| 00:00:01 |

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

Query Block Name / Object Alias (identified by operation id):

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

1 - SEL$1

2 - SEL$1 / A1@SEL$1

Predicate Information (identified by operation id):

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

2 - filter("A1"."ACCT_ID"=:LACCTID)

Column Projection Information (identified by operation id):

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

1 - (#keys=0) SUM(NVL("A1"."CHARGE",0))[22]

2 - "A1"."CHARGE"[NUMBER,22]

Note

-----

- dynamic sampling used for this statement

--------檢視執行計劃生成的日期

SQL> select to_char(timestamp,'yyyy-mm-dd hh24:mi:ss'),PLAN_HASH_VALUE FROM DBA_HIST_SQL_PLAN WHERE sql_id='b0qf7znnrz64c';

TO_CHAR(TIMESTAMP,' PLAN_HASH_VALUE

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

2013-09-01 00:00:12 1549402607

2013-09-01 00:00:12 1549402607 =====è說明在2013-09-01 00:00:12 時候就已經保持著錯誤的執行計劃。

2013-09-02 10:50:04 2969810046 =====》收集完統計資訊，重建索引後，生成的執行計劃。

2013-09-02 10:50:04 2969810046

7 rows selected.

SQL> select to_char(timestamp,'yyyy-mm-dd hh24:mi:ss'),plan_hash_value from v$sql_plan where sql_id='b0qf7znnrz64c';

TO_CHAR(TIMESTAMP,' PLAN_HASH_VALUE

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

2013-09-02 10:50:04 2969810046 =====》目前正在使用的執行計劃。

2013-09-02 10:50:04 2969810046

------檢視sql語句的最後一次執行時間

SQL> select child_number,parse_calls,executions,first_load_time,last_load_time,last_active_time from v$sql where sql_id='b0qf7znnrz64c';

CHILD_NUMBER PARSE_CALLS EXECUTIONS FIRST_LOAD_TIME LAST_LOAD_TIME LAST_ACTIVE_TIME

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

0 21831 21831 2013-09-01/00:00:12 2013-09-02/10:50:04 2013-09-02 13:35:46

--------檢視錶的統計信收集情況（發現表沒有收集統計資訊）

SQL> select owner,table_name,LAST_ANALYZED,NUM_ROWS,SAMPLE_SIZE from dba_tables where table_name='ACCT_ITEM_AGGR_11309';

OWNER TABLE_NAME LAST_ANALYZED NUM_ROWS SAMPLE_SIZE

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

BILL ACCT_ITEM_AGGR_11309

-------檢視錶的索引情況（表列ACCT_ID）上有索引但沒有走索引）

select INDEX_OWNER,INDEX_NAME,TABLE_OWNER,TABLE_NAME,COLUMN_NAME,COLUMN_POSITION from dba_ind_columns where table_name='ACCT_ITEM_AGGR_11309'；

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

BILL PK_ACCT_ITEM_AGGR_11309 BILL ACCT_ITEM_AGGR_11309 ACCT_ITEM_ID 1

BILL IDX_ACCT_ITEMAGGR_SERVID_11309 BILL ACCT_ITEM_AGGR_11309 SERV_ID 1

BILL IDX_ACCT_ITEMAGGR_ACCTID_11309 BILL ACCT_ITEM_AGGR_11309 ACCT_ID

-----收集表的統計資訊（發現還是走以前的執行計劃）

BEGIN

dbms_stats.gather_table_stats(ownname => 'BILL',

tabname =>'ACCT_ITEM_AGGR_11309', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE,

method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

END;

/

Plan hash value: 1549402607

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

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

| 0 | SELECT STATEMENT | | | | 2 (100)| |

| 1 | SORT AGGREGATE | | 1 | 26 | | |

|* 2 | TABLE ACCESS FULL| ACCT_ITEM_AGGR_11309 | 1 | 26 | 2 (0)| 00:00:01 |

---------檢視引數

SQL>

SQL> show parameter db_file_multiblock_read_count

NAME TYPE VALUE

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

db_file_multiblock_read_count integer 32 ======》正常

SQL>

SQL> show parameter optimizer_index_cost_adj

NAME TYPE VALUE

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

optimizer_index_cost_adj integer 50 =====》預設值100，目前值為50說明本庫應該傾向走索引。

SQL>

--------檢視隱含引數（

SQL> SELECT a.ksppinm, b.ksppstvl, b.ksppstdf

2 FROM x$ksppi a, x$ksppcv b

3 WHERE a.indx = b.indx

4 AND a.ksppinm like '%_optimizer_invalidation_period%'

5 ORDER BY a.ksppinm;

KSPPINM

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

KSPPSTVL

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

KSPPSTDF

---------

_optimizer_invalidation_period ==========》收集統計資訊後，重新生成新的執行計劃的時間是 18000/60/60=5小時

18000

TRUE

Note :

因此判斷可以說明為什麼重新對錶收集統計資訊後，執行計劃沒有改變。

重新收集統計時候時，不會重新生成執行計劃，（收集就（預設）重新生成執行計劃是在10g之前的版本，mos的文章做為依據）

要想重新生成執行計劃，必須在收集統計資訊時加一個引數 no_invalidate=>false ；

--------檢視繫結變數值佔用整個表的百分比

Sql_text= SELECT NVL(SUM(NVL("A1"."CHARGE",0)),0) FROM "ACCT_ITEM_AGGR_11309" "A1" WHERE "A1"."ACCT_ID"=:LACCTID;

SQL> SELECT SQL_ID,NAME,DATATYPE,DATATYPE_STRING,LAST_CAPTURED,VALUE_STRING,VALUE_ANYDATA FROM DBA_HIST_SQLBIND WHERE SQL_ID='b0qf7znnrz64c' AND LAST_CAPTURED > to_date('2013-09-01 00:00:12','yyyy-mm-dd hh24:mi:ss');

SQL_ID NAME DATATYPE DATATYPE_STRING LAST_CAPTURED VALUE_STRING VALUE_ANYDATA()

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

b0qf7znnrz64c :LACCTID 2 NUMBER 2013-09-02 09:48:54 240000234632 ANYDATA()

b0qf7znnrz64c :LACCTID 2 NUMBER 2013-09-02 08:01:18 240003213596 ANYDATA()

b0qf7znnrz64c :LACCTID 2 NUMBER 2013-09-02 06:57:07 240000213902 ANYDATA()

b0qf7znnrz64c :LACCTID 2 NUMBER 2013-09-02 08:47:41 240003460468 ANYDATA()

b0qf7znnrz64c :LACCTID 2 NUMBER 2013-09-02 05:56:46 240000210185 ANYDATA()

b0qf7znnrz64c :LACCTID 2 NUMBER 2013-09-02 10:38:46 240002501392 ANYDATA()

6 rows selected.

從中無法確定在第一次生成執行計劃（'2013-09-01 00:00:12）時的變數值。

SQL> col acct_id for 999999999999999

SQL> select * from (select acct_id,count(acct_id) from bill.ACCT_ITEM_AGGR_11309 group by acct_id order by 2 desc) where rownum<20 ;

ACCT_ID COUNT(ACCT_ID)

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

240000191649 5760

240002326438 5473

240002253886 5349

240002135954 5343

240003435040 4733

240002207037 4503

240002212124 3750

240002139158 3682

240002144620 3619

240002326542 3194

240002189931 3152

240002188438 3084

240001085504 2773

240002392050 2610

240002211354 2150

240000203644 2030

240001904525 1952

240002212358 1701

240001245355 1532

19 rows selected.

SQL> select (5760/count(1))*100||'%' from bill.ACCT_ITEM_AGGR_11309;

(5760/COUNT(1))*100||'%'

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

.073427410503332151186018401215019678801% ==========è欄位的最大值佔整個欄位的0.073%

---------最大值生成執行計劃

SQL> explain plan for SELECT NVL(SUM(NVL(A1.CHARGE,0)),0) FROM BILL.ACCT_ITEM_AGGR_11309 A1 WHERE A1.ACCT_ID='240000191649';

Explained.

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 2969810046

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

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

| 0 | SELECT STATEMENT | | 1 | 11 | 4 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 11 | | |

| 2 | TABLE ACCESS BY INDEX ROWID| ACCT_ITEM_AGGR_11309 | 7 | 77 | 4 (0)| 00:00:01 |

|* 3 | INDEX RANGE SCAN | IDX_ACCT_ITEMAGGR_ACCTID_11309 | 7 | | 2 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

3 - access("A1"."ACCT_ID"=240000191649)

15 rows selected.

SQL> explain plan for SELECT /*+ dynamic_sampling(A1) */ NVL(SUM(NVL(A1.CHARGE,0)),0) FROM BILL.ACCT_ITEM_AGGR_11309 A1 WHERE A1.ACCT_ID='240000191649';

Explained.

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 2969810046

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

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

| 0 | SELECT STATEMENT | | 1 | 11 | 4 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 11 | | |

| 2 | TABLE ACCESS BY INDEX ROWID| ACCT_ITEM_AGGR_11309 | 7 | 77 | 4 (0)| 00:00:01 |

|* 3 | INDEX RANGE SCAN | IDX_ACCT_ITEMAGGR_ACCTID_11309 | 7 | | 2 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

3 - access("A1"."ACCT_ID"=240000191649)

15 rows selected

Note:

取樣分析和收集統資訊分析的結果都是使用索引。說明當時應該跟統計資訊沒有關係。

---------重建表索引

執行計劃發生改變

SQL_ID b0qf7znnrz64c, child number 0

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

SELECT NVL(SUM(NVL("A1"."CHARGE",0)),0) FROM "ACCT_ITEM_AGGR_11309" "A1" WHERE "A1"."ACCT_ID"=:LACCTID

Plan hash value: 2969810046

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

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

| 0 | SELECT STATEMENT | | | | 4 (100)| |

| 1 | SORT AGGREGATE | | 1 | 11 | | |

| 2 | TABLE ACCESS BY INDEX ROWID| ACCT_ITEM_AGGR_11309 | 7 | 77 | 4 (0)| 00:00:01 |

|* 3 | INDEX RANGE SCAN | IDX_ACCT_ITEMAGGR_ACCTID_11309 | 7 | | 2 (0)| 00:00:01 |

SELECT NVL(SUM(NVL("A1"."CHARGE",0)),0) FROM "ACCT_ITEM_AGGR_11309" "A1" WHERE "A1"."ACCT_ID"=:LACCTID

_optimizer_invalidation_periond導致收集統計資訊後執行計劃沒有改變

相關文章