昨晚通宵不说,刚睡下床,电话响了,一个项目运维反馈说有个功能历史记录查询十分缓慢,程序接口返回1000s,我去分析数据库时感觉很郁闷上去查看,查看执行计划看了,创建了组合索引,因为这两列没走索引,但SQL依然慢,断续分析,目前最大的表是T_SESSIONS有一千6百多万数据,记得以前优化过SQL执行计划返回错误的行数,恍然大悟。
1:通过分析得知T_SESSIONS此表没收集过统计信息。
2:收集T_SESSIONS统计信息:
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'ROBINSON',
tabname => 'P_TEST',
estimate_percent => 30,
method_opt => 'for all columns size repeat',
no_invalidate => FALSE,
degree => 8,
granularity => 'ALL', --所有分区都收集
cascade => TRUE);
END;
/
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'ROBINSON',
tabname => 'P_TEST',
estimate_percent => 30,
method_opt => 'for all columns size repeat',
no_invalidate => FALSE,
degree => 8,
granularity => 'ALL', --所有分区都收集
cascade => TRUE);
END;
/
3:查看收集统计后的信息
4:查看执行计划
未收集统计信息前:
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1567673 consistent gets
281309 physical reads
0 redo size
2184 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
11 rows processed
收集统计信息后:
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1954 consistent gets
0 physical reads
0 redo size
2041 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
通过执行SQL: 0.8秒出结果 ^_^|||