数据库迁移后缺失直方图导致index full scan导致的性能问题1

数据库迁移后缺失直方图导致index full scan导致的性能问题1

数据从11.2.0.3迁移到12.2.0.1,使用数据泵迁移，迁移后发现数据库DBTime很高，并且部分SQL执行时间过长，这个是一个核心系统需要尽快定位问题，我们直接从ASH找出大的等待SQL以及等待事件。发现都是涉及一条SQL，这条SQL并发，一个小时执行了近700次。而在之前库则没有这个问题，
SQL> select sql_id ,event,count(*) from v$active_session_history group by sql_id,event order by count(*);

1a3svb46kv8x9 direct path write temp 10449
1a3svb46kv8x9 latch: cache buffers chains 10605
1a3svb46kv8x9 db file sequential read 27013
1a3svb46kv8x9 db file parallel read 45292
1a3svb46kv8x9 read by other session 90551
1a3svb46kv8x9 166732
这些等待事件都说明该SQL的执行计划不是优，之前跑地好好的，为什么迁移后就变得这么慢，且如此的耗费资源拉高DBTime，下面是执行计划

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 1a3svb46kv8x9, child number 0
-------------------------------------
SELECT T.STUDS_ID, T.TCR_STUDSID, T.TEST_ID, T.TEST_STUDS_ID,
T.TEST_TCR_STUDSID, T.BANK_ID, T.CHANNEL_ID, T.STUDSTYPE_ID,
T.STUDS_TIME, T.STUDS_AMOUNT, DECODE(T.STUDS_STATUS, '2', '1',
T.STUDS_STATUS) AS STUDS_STATUS FROM MAY_STUDS_WEB T WHERE T.TEST_ID =
:B1 AND T.STUDSTYPE_ID NOT IN ( '06','09','10','11') AND EXISTS (SELECT
* FROM TABLE(CAST(:B2 AS TB_MAY_QRY_PLAT)) R WHERE R.STUDS_ID =
T.TCR_STUDSID)

Plan hash value: 2357415847

------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 892 (100)| | | |
| 1 | MERGE JOIN SEMI | | 4 | 384 | 892 (1)| 00:00:01 | | |
| 2 | SORT JOIN | | 1175M| 102G| 862 (0)| 00:00:01 | | |
| 3 | PARTITION RANGE ALL | | 1175M| 102G| 862 (0)| 00:00:01 | 1 | 37 |
| 4 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| MAY_STUDS_WEB | 1175M| 102G| 862 (0)| 00:00:01 | 1 | 37 |
| 5 | INDEX FULL SCAN | IDX_WEB_STUDS_TCRSTUDSID | 2177M| | 62 (0)| 00:00:01 | 1 | 37 |
| 6 | SORT UNIQUE | | 8168 | 16336 | 30 (4)| 00:00:01 | | |
| 7 | COLLECTION ITERATOR PICKLER FETCH | | 8168 | 16336 | 29 (0)| 00:00:01 | | |
----------------- -----------------------------------------------------------------------

发现问题出在步骤 INDEX FULL SCAN ，也就是会从个leaf block开始逐个扫描索引块 再通过回表过滤数据，这部分耗费资源，根源很可能是走了错误的索引方式。


select sql_id ,event,blocking_session,count(*) from v$active_session_history group by sql_id ,event,blocking_session order by count(*);

表字段的直方图信息
SQL>select COLUMN_NAME,NUM_DISTINCT,NUM_BUCKETS,AVG_COL_LEN,HISTOGRAM from dba_tab_columns where table_name='MAY_STUDS_WEB'

COLUMN_NAME NUM_DISTINCT NUM_BUCKETS AVG_COL_LEN HISTOGRAM
---------------------------------------- ------------ ----------- ----------- ---------------
STUDS_ID 2140930048 1 19 NONE
TEST_ID 1 1 5 NONE
TEST_STUDS_ID 2177772571 1 28 NONE
TCR_STUDSID 280625152 1 4 NONE <<<<<<没有直方图

下面是好的执行计划
为了比较执行计划的差异，我们到生产库查询之前的执行计划

new 1: select * from table(dbms_xplan.display_cursor('1a3svb46kv8x9',null,'ADVANCED PEEKED_BINDS IOSTATS LAST -PREDICATE -NOTE'))

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 1a3svb46kv8x9, child number 0
-------------------------------------
SELECT T.STUDS_ID, T.TCR_STUDSID, T.TEST_ID, T.TEST_STUDS_ID,
T.TEST_TCR_STUDSID, T.BANK_ID, T.CHANNEL_ID, T.STUDSTYPE_ID,
T.STUDS_TIME, T.STUDS_AMOUNT, DECODE(T.STUDS_STATUS, '2', '1',
T.STUDS_STATUS) AS STUDS_STATUS FROM MAY_STUDS_WEB T WHERE T.TEST_ID =
:B1 AND T.STUDSTYPE_ID NOT IN ( '06','09','10','11') AND EXISTS (SELECT
* FROM TABLE(CAST(:B2 AS TB_MAY_QRY_PLAT)) R WHERE R.STUDS_ID =
T.TCR_STUDSID)

Plan hash value: 3711966574

----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 143 (100)| | | |
| 1 | NESTED LOOPS | | | | | | | |
| 2 | NESTED LOOPS | | 9 | 882 | 143 (1)| 00:00:02 | | |
| 3 | SORT UNIQUE | | 1 | 2 | 29 (0)| 00:00:01 | | |
| 4 | COLLECTION ITERATOR PICKLER FETCH| | 1 | 2 | 29 (0)| 00:00:01 | | |
| 5 | PARTITION RANGE ALL | | 1 | | 111 (0)| 00:00:02 | 1 | 37 |
| 6 | INDEX RANGE SCAN | IDX_WEB_STUDS_TCRSTUDSID | 1 | | 111 (0)| 00:00:02 | 1 | 37 |
| 7 | TABLE ACCESS BY LOCAL INDEX ROWID | MAY_STUDS_WEB | 9 | 864 | 113 (0)| 00:00:02 | 1 | 1 |
----------------------------------------------------------------------------------------------------------------------------------

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

1 - SEL$09D7319C
4 - SEL$09D7319C / KOKBF$0@SEL$3
6 - SEL$09D7319C / T@SEL$1
7 - SEL$09D7319C / T@SEL$1

从这里看整个执行计划走了嵌套，索引是range scan ,显然这个索引方式要好多了，从评估看只有一行数据，从这里我们可以知道优化器总体上依然觉得走索引快，但是迁移后cbo判断
走了index full scan ，也就是优化器的判断除了问题，此时我们就需要将重点放到直方图上了，因为直方图可以更准确的评估数据分布，从更小范围访问数据，这样就可以影响索引的方式
为了印证我们的推断，在原库查询直方图。
原库有直方图
SYS@ dzzf21>select COLUMN_NAME,NUM_DISTINCT,NUM_BUCKETS,AVG_COL_LEN,HISTOGRAM from dba_tab_columns where table_name='MAY_STUDS_WEB';

TCR_STUDSID 539719680 254 4 HEIGHT BALANCED <<<<有直方图
TEST_STUDS_ID 5074223104 254 28 HEIGHT BALANCED
TEST_ID 1 1 5 FREQUENCY
STUDS_ID 5079108479 254 19 HEIGHT BALANCED

而迁移后新库的表统计信息没有直方图，解决方式：
在新库收集该列的直方图，buckets数量254

exec dbms_stats.gather_table_stats(tab_name=>'',parname=>'',method_opt=>'for columns a size 254',并行度auto)