一生产库在早上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
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 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 1549402607 =====è说明在2013-09-01 00:00:12 时候就已经保持着错误的执行计划。
2013-09-02 10:50:04 2969810046 =====》收集完统计信息,重建索引后,生成的执行计划。
2013-09-02 10:50:04 2969810046
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
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
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 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>
SQL> show parameter db_file_multiblock_read_count
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count integer 32 ======》正常
SQL>
SQL>
SQL>
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
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 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
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 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
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 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
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29446986/viewspace-1173559/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29446986/viewspace-1173559/