- 最后登录
- 2015-7-31
- 在线时间
- 45 小时
- 威望
- 5
- 金钱
- 205
- 注册时间
- 2013-9-27
- 阅读权限
- 10
- 帖子
- 22
- 精华
- 1
- 积分
- 5
- UID
- 1252
|
1#
发表于 2013-11-2 16:31:54
|
查看: 13690 |
回复: 15
本帖最后由 bamuta 于 2013-11-2 18:55 编辑
基本环境
操作系统:windows server 2008 r2 enterprise
SQL> select * from V$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for 64-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
开发中的库,在做测试时,发现有1SQL比较消耗资源,我想把他优化一下。
SQL如下:
SELECT r.OUT_VER_BEGIN_ID dataID
FROM DMS_DATA_RELA r, DMS_OBJ o
WHERE r.DELETE_FLAG = '0'
AND r.RELA_TYPE_CODE = 'parent'
AND r.OUT_OBJ_CODE = o.OBJ_CODE
AND o.DELETE_FLAG = '0'
AND o.OPEN_STATE = '1'
AND r.IN_OBJ_CODE != 'o_in'
START WITH r.IN_DATA_ID in
(SELECT d.OUT_DATA_ID
FROM DMS_DATA_RELA d
where d.OUT_VER_BEGIN_ID = :1
and d.last_curent_flag = '1')
CONNECT BY r.IN_VER_BEGIN_ID = PRIOR r.OUT_VER_BEGIN_ID
执行计划: (来自awrsqrpt)
0 SELECT STATEMENT 8045 (100)
1 FILTER
2 CONNECT BY WITH FILTERING
3 FILTER
4 COUNT
5 HASH JOIN 717K 111M 8045 (1) 00:01:37
6 TABLE ACCESS FULL DMS_OBJ 41 656 3 (0) 00:00:01
7 TABLE ACCESS FULL DMS_DATA_RELA 717K 100M 8037 (1) 00:01:37
8 TABLE ACCESS BY INDEX ROWID DMS_DATA_RELA 1 91 4 (0) 00:00:01
9 INDEX RANGE SCAN OUT_VER_BEGIN_ID_INDEX 1 3 (0) 00:00:01
10 HASH JOIN
11 CONNECT BY PUMP
12 COUNT
13 HASH JOIN 717K 111M 8045 (1) 00:01:37
14 TABLE ACCESS FULL DMS_OBJ 41 656 3 (0) 00:00:01
15 TABLE ACCESS FULL DMS_DATA_RELA 717K 100M 8037 (1) 00:01:37
随便找个变量单独执行这条SQL试了下,大概能执行6秒。
表上记录数
SQL> select count(*) from dms_data_rela;
COUNT(*)
----------
858470
SQL> select count(*) from dms_obj;
COUNT(*)
----------
41
表上索引:(索引较多,如果不影响此条SQL的查询性能,先不考虑这些索引合不合理,因为有些非技术因素)
SQL> select index_name,column_name,table_name from dba_ind_columns where table_name ='DMS_DATA_RELA' order by index_name;
INDEX_NAME COLUMN_NAME TABLE_NAME
------------------------------ -------------------- ------------------------------
CREATE_TIME_INDEX SYS_NC00031$ DMS_DATA_RELA
DELETE_FLAG_INDEX DELETE_FLAG DMS_DATA_RELA
IN_DATA_ID_INDEX IN_DATA_ID DMS_DATA_RELA
IN_DATA_NAME_INDEX IN_DATA_NAME DMS_DATA_RELA
IN_DATA_SOURCE_CODE_INDEX IN_DATA_SOURCE_CODE DMS_DATA_RELA
IN_DATA_SOURCE_ID_INDEX IN_DATA_SOURCE_ID DMS_DATA_RELA
IN_OBJ_CODE_INDEX IN_OBJ_CODE DMS_DATA_RELA
IN_VER_BEGIN_ID_INDEX IN_VER_BEGIN_ID DMS_DATA_RELA
LAST_CURENT_FLAG_INDEX LAST_CURENT_FLAG DMS_DATA_RELA
OUT_DATA_ID_INDEX OUT_DATA_ID DMS_DATA_RELA
OUT_DATA_NAME_INDEX OUT_DATA_NAME DMS_DATA_RELA
OUT_DATA_SOURCE_ID_INDEX OUT_DATA_SOURCE_ID DMS_DATA_RELA
OUT_OBJ_CODE_INDEX OUT_OBJ_CODE DMS_DATA_RELA
OUT_VER_BEGIN_ID_INDEX OUT_VER_BEGIN_ID DMS_DATA_RELA
PK_DMS_DATA_RELA RELA_ID DMS_DATA_RELA
RELA_TYPE_CODE_INDEX RELA_TYPE_CODE DMS_DATA_RELA
SQL> select index_name,column_name,table_name from dba_ind_columns where table_name ='DMS_OBJ' order by index_name;
INDEX_NAME COLUMN_NAME TABLE_NAME
------------------------------ -------------------- ------------------------------
PK_DMS_OBJ OBJ_ID DMS_OBJ
上面SQL走全表,我找了一些资料说是在IN_VER_BEGIN_ID 列上有索引的话,会走索引,但是实际有索引,还是全表,加hint也还是全表。
我试过物化视图
CREATE MATERIALIZED VIEW mv_dms_ddr
REFRESH force
ON demand
WITH ROWID enable query rewrite AS
SELECT r.OUT_VER_BEGIN_ID, r.IN_DATA_ID, r.IN_VER_BEGIN_ID
FROM DMS_DATA_RELA r, DMS_OBJ o
WHERE r.DELETE_FLAG = '0'
AND r.RELA_TYPE_CODE = 'parent'
AND r.IN_OBJ_CODE != 'o_in'
AND r.OUT_OBJ_CODE = o.OBJ_CODE
AND o.DELETE_FLAG = '0'
AND o.OPEN_STATE = '1'
改写SQL
SELECT OUT_VER_BEGIN_ID dataID
FROM mv_dms_ddr
START WITH IN_DATA_ID in
(SELECT d.OUT_DATA_ID
FROM DMS_DATA_RELA d
where d.OUT_VER_BEGIN_ID = :1
and d.last_curent_flag = '1')
CONNECT BY PRIOR OUT_VER_BEGIN_ID = IN_VER_BEGIN_ID
(没用fast更新),带入变量试了,第1次慢,后面快,执行时间大概是3秒多了。有提升,但不是很理想,而且表更新也频繁,刷新方式没做on commit。 (我猜测在数据库变动频繁的情况下,是不是这个查询还是会慢,这个想法还没验证)
另外表也收集过统计信息了。
附件中有相关的awr ,awrsql ,sqlhc。 大神们,这种SQL该怎么优化呢? |
|