SQL逻辑读高的优化
来源:互联网 发布:it was until 编辑:程序博客网 时间:2024/06/14 11:18
经常在awr报告上看到逻辑读非常高的SQL,不清楚这个对系统有什么影响,今天做了一个实验对比,结论是逻辑读高很消耗CPU。
测试方法:找到一条逻辑读高的SQL执行,用10046事件跟踪。然后去掉前列的很多查询项,再用10046事件跟踪。
修改前:
SELECT /*+CHOOSE*/*
FROM (SELECT INNER_TABLE.*,
ROW_NUMBER() OVER(ORDER BY NULL) OUTER_TABLE_ROWNUM
FROM (SELECT INTER_APPLY.INTER_APPLY_ID,
INTER_APPLY.APPLY_DATE,
INTER_APPLY.PROPOSER_ID,
INTER_APPLY.PROPOSER,
INTER_APPLY.WORK_PROPOSER_ID,
INTER_APPLY.WORK_PROPOSER,
INTER_APPLY.APPLY_DEPARTMENT_ID,
INTER_APPLY.APPLY_DEPARTMENT_CODE,
INTER_APPLY.APPLY_DEPARTMENT,
INTER_APPLY.WORK_DEPARTMENT_ID,
INTER_APPLY.WORK_DEPARTMENT_CODE,
INTER_APPLY.WORK_DEPARTMENT,
INTER_APPLY.DISPATCH_LEVEL,
INTER_APPLY.IS_PLAN,
INTER_APPLY.DEVICE_PROPERTY,
INTER_APPLY.INTER_DEVICE,
INTER_APPLY.OVERHAUL_TYPE,
INTER_APPLY.OVERHAUL_CONTENT,
INTER_APPLY.AREA_NEED,
INTER_APPLY.APPLY_START_TIME,
INTER_APPLY.APPLY_END_TIME,
INTER_APPLY.APPLY_DEFER_START_TIME,
INTER_APPLY.APPLY_DEFER_END_TIME,
INTER_APPLY.SUBMIT_DATE,
INTER_APPLY.CONFIRM_START_TIME,
INTER_APPLY.CONFIRM_END_TIME,
INTER_APPLY.CONFIRM_DEFER_START_TIME,
INTER_APPLY.CONFIRM_DEFER_END_TIME,
INTER_APPLY.IS_INFORM_CUSTOMER,
INTER_APPLY.FLOW_STATE,
INTER_APPLY.TASK_NAME,
INTER_APPLY.IMPORT_MONTH_PLAN_ID,
INTER_APPLY.BACK_FLAG,
INTER_APPLY.VERIFY_FLAG,
INTER_APPLY.IS_SUBMIT_MONTHPLAN,
INTER_APPLY.PLAN_NO,
INTER_APPLY.APPLY_NO,
INTER_APPLY.CONTACTER,
INTER_APPLY.CONTACTER_PHONE,
INTER_APPLY.APPLY_DEPARTMENT_PRINCIPAL,
INTER_APPLY.WORK_DEPARTMENT_PRINCIPAL,
INTER_APPLY.SIGN_DEPARTMENT,
INTER_APPLY.REMARK,
INTER_APPLY.IS_INFLUENCE_CABLE,
INTER_APPLY.DEPT_SPECIALITY_ID,
INTER_APPLY.DEPT_SPECIALITY,
INTER_APPLY.INTER_LEVEL,
INTER_APPLY.OBJECT_BUNDLE_NAME,
INTER_APPLY.EDITOR,
INTER_APPLY.ATTEMPER_AFFIRM,
INTER_APPLY.IS_ADVISE,
INTER_APPLY.ADVISOR,
INTER_APPLY.BY_ADVISOR,
INTER_APPLY.ADVISE_TIME,
INTER_APPLY.CHECK_DEPARTMENT_ID,
INTER_APPLY.CHECK_DEPARTMENT,
INTER_APPLY.INTERRUPT_START_TIME,
INTER_APPLY.INTERRUPT_END_TIME,
INTER_APPLY.ADVISE_CORRELATION_DEPARTMENT,
INTER_APPLY.DEFER_STATE,
INTER_APPLY.DEFER_CAUSE,
INTER_APPLY.DEFER_REQUESTOR,
INTER_APPLY.DEFER_DISPATCHER,
INTER_APPLY.DEFER_APPROVE,
INTER_APPLY.INTERRUPT_START_DISPATCHER,
INTER_APPLY.INTERRUPT_START_RECEIVE_ORDER,
INTER_APPLY.INTERRUPT_END_RECEIVE_ORDER,
INTER_APPLY.INTERRUPT_END_DISPATCHER,
INTER_APPLY.WORK_START_TIME,
INTER_APPLY.WORK_START_RECEIVE_ORDER,
INTER_APPLY.WORK_START_DISPATCHER,
INTER_APPLY.WORK_END_TIME,
INTER_APPLY.WORK_END_RECEIVE_ORDER,
INTER_APPLY.WORK_END_DISPATCHER,
INTER_APPLY.AUDITING_NOTES,
INTER_APPLY.CONFIRM_CODE,
INTER_APPLY.CONFIRM_WORK_PLAN_END_DATE,
INTER_APPLY.CONFIRM_WORK_PLAN_START_DATE,
INTER_APPLY.INTER_USER_NUM,
INTER_APPLY.WORK_PRINCIPAL_PHONE,
INTER_APPLY.GROUP_ID,
NVL(INTER_APPLY.IS_EFFECT_COMMUNICATE, -1) IS_EFFECT_COMMUNICATE,
NVL(INTER_APPLY.IS_NEED_STARTUP, -1) IS_NEED_STARTUP,
NVL(INTER_APPLY.IS_CHANGE_RELAYSETTING, -1) IS_CHANGE_RELAYSETTING,
NVL(INTER_APPLY.IS_CHANGE_CT, -1) IS_CHANGE_CT,
NVL(INTER_APPLY.IS_CHANGE_PARAMETER, -1) IS_CHANGE_PARAMETER,
NVL(INTER_APPLY.IS_NEED_HX, -1) IS_NEED_HX,
INTER_APPLY.CHANGE_START_TIME,
INTER_APPLY.CHANGE_END_TIME,
INTER_APPLY.CHANGE_DISPATCHER,
INTER_APPLY.CHANGE_CAUSE,
INTER_APPLY.IS_CHANGE_APPLY_TIME,
INTER_APPLY.MARK,
INTER_APPLY.SYSTEM_REPLY_TIME,
INTER_APPLY.DEVICE_MAN_DEPT_ID,
INTER_APPLY.DEVICE_MAN_DEPT_NAME,
INTER_APPLY.CREATOR,
INTER_APPLY.CACEL_DATE,
INTER_APPLY.CACEL_CAUSE,
INTER_APPLY.ARRANGE_EXPERIMENT_WORK,
INTER_APPLY.OVERHAUL_DEPARTMENT,
INTER_APPLY.CREATOR_ID,
INTER_APPLY.CHANGE_DATE_FLOW_STATE,
INTER_APPLY.RISKLEVEL,
INTER_APPLY.CHANGE_DATE_FLAG,
INTER_APPLY.IS_REPORTED,
INTER_APPLY.VOLTAGE_GRADE,
INTER_APPLY.INTER_TYPE,
INTER_APPLY.APPLY_RISK,
INTER_APPLY.TEMP_SECURITY_MEASURES,
INTER_APPLY.INTER_AREA,
INTER_APPLY.INTER_OPESTART_TIME,
INTER_APPLY.INTER_OPEEND_TIME,
INTER_APPLY.POWERON_OPESTART_TIME,
INTER_APPLY.POWERON_OPEEND_TIME,
INTER_APPLY.CHANGE_DATE_WORK_STATE,
INTER_APPLY.FLAG,
INTER_APPLY.IS_EMERGENT,
INTER_APPLY.IS_EXIT_RECLOSING,
INTER_APPLY.EXIT_RECLOSING_LINE,
INTER_APPLY.IS_CHANGE_PICTURE,
DECODE(INTER_APPLY.CHANGE_START_TIME,
NULL,
INTER_APPLY.CONFIRM_START_TIME,
INTER_APPLY.CHANGE_START_TIME) CHANGESTARTTIME,
DECODE(INTER_APPLY.CHANGE_END_TIME,
NULL,
INTER_APPLY.CONFIRM_END_TIME,
INTER_APPLY.CHANGE_END_TIME) CHANGEENDTIME,
DECODE(INTER_APPLY.CONFIRM_DEFER_START_TIME,
null,
DECODE(INTER_APPLY.CHANGE_START_TIME,
null,
DECODE(INTER_APPLY.CONFIRM_START_TIME,
null,
INTER_APPLY.APPLY_START_TIME,
INTER_APPLY.CONFIRM_START_TIME),
INTER_APPLY.CHANGE_START_TIME),
INTER_APPLY.CONFIRM_DEFER_START_TIME) INTER_TIME_FROM,
DECODE(INTER_APPLY.CONFIRM_DEFER_END_TIME,
null,
DECODE(INTER_APPLY.CHANGE_END_TIME,
null,
DECODE(INTER_APPLY.CONFIRM_END_TIME,
null,
INTER_APPLY.APPLY_END_TIME,
INTER_APPLY.CONFIRM_END_TIME),
INTER_APPLY.CHANGE_END_TIME),
INTER_APPLY.CONFIRM_DEFER_END_TIME) INTER_TIME_TO
FROM (SELECT view_INTER.*,
DECODE(view_INTER.CHANGE_START_TIME,
NULL,
view_INTER.CONFIRM_START_TIME,
view_INTER.CHANGE_START_TIME) CHANGESTARTTIME,
DECODE(view_INTER.CHANGE_END_TIME,
NULL,
view_INTER.CONFIRM_END_TIME,
view_INTER.CHANGE_END_TIME) CHANGEENDTIME,
DECODE(VIEW_INTER.CONFIRM_DEFER_START_TIME,
NULL,
DECODE(VIEW_INTER.CHANGE_START_TIME,
NULL,
DECODE(VIEW_INTER.CONFIRM_START_TIME,
NULL,
VIEW_INTER.APPLY_START_TIME,
VIEW_INTER.CONFIRM_START_TIME),
VIEW_INTER.CHANGE_START_TIME),
VIEW_INTER.CONFIRM_DEFER_START_TIME) INTER_TIME_FROM,
DECODE(VIEW_INTER.CONFIRM_DEFER_END_TIME,
NULL,
DECODE(VIEW_INTER.CHANGE_END_TIME,
NULL,
DECODE(VIEW_INTER.CONFIRM_END_TIME,
NULL,
VIEW_INTER.APPLY_END_TIME,
VIEW_INTER.CONFIRM_END_TIME),
VIEW_INTER.CHANGE_END_TIME),
VIEW_INTER.CONFIRM_DEFER_END_TIME) INTER_TIME_TO
FROM view_INTER) INTER_APPLY
WHERE 1 = 1
AND TEMP_STORE = 0
ORDER BY INTER_APPLY.CHANGESTARTTIME asc nulls last) INNER_TABLE
WHERE ROWNUM <= 50) OUTER_TABLE
WHERE OUTER_TABLE_ROWNUM > 0;
修改后:
SELECT /*+CHOOSE*/*FROM (SELECT INNER_TABLE.*,
ROW_NUMBER() OVER(ORDER BY NULL) OUTER_TABLE_ROWNUM
FROM (SELECT INTER_APPLY.INTER_APPLY_ID
FROM (SELECT view_INTER.INTER_APPLY_ID,
view_INTER.TEMP_STORE,
DECODE(view_INTER.CHANGE_START_TIME,
NULL,
view_INTER.CONFIRM_START_TIME,
view_INTER.CHANGE_START_TIME) CHANGESTARTTIME
FROM view_INTER) INTER_APPLY
WHERE 1 = 1
AND TEMP_STORE = 0
ORDER BY INTER_APPLY.CHANGESTARTTIME asc nulls last) INNER_TABLE
WHERE ROWNUM <= 50) OUTER_TABLE
WHERE OUTER_TABLE_ROWNUM > 0;
修改前:
统计信息
----------------------------------------------------------
17 recursive calls
0 db block gets
1566953 consistent gets
0 physical reads
260 redo size
22653 bytes sent via SQL*Net to client
11435 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
50 rows processed
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.05 0.05 0 105 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 5 11.87 11.62 0 1566843 0 50
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 11.93 11.67 0 1566948 0 50
修改后:
统计信息
----------------------------------------------------------
15 recursive calls
0 db block gets
6666 consistent gets
0 physical reads
0 redo size
1156 bytes sent via SQL*Net to client
383 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
50 rows processed
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.03 0.03 0 105 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 5 0.25 0.24 0 6556 0 50
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 0.28 0.27 0 6661 0 50
- SQL逻辑读高的优化
- 降低逻辑读优化SQL(转)
- mysql下的高并发SQL编写逻辑注意事项
- SQL优化--逻辑优化--非SPJ优化
- 大数据量高并发的数据库优化,sql查询优化
- 大数据量高并发的数据库优化与sql优化
- 大数据量高并发的数据库优化,sql查询优化
- 大数据量高并发的数据库优化与sql优化
- 大数据量高并发的数据库优化与sql优化
- 大数据量高并发的数据库优化与sql优化
- SQL优化--逻辑优化--条件化简
- SQL优化--逻辑优化--数据库的约束规则与语义优化
- 优化SQL查询:如何写出高性能的SQL语句
- 一个SQL逻辑读异常的解决方法
- SQL优化--逻辑优化--子查询优化(MySQL)
- SQL高性能查询优化
- 针对sql 2005优化的高性能分页存储过程
- SQL Server数据库的高性能优化经验总结
- asp.net打开office文件检索COM 类工厂中CLSID~组件加载失败(解决方案)
- 如何添加透明幕墙
- ios 关于推送发布的一些流程
- SQL SERVER 执行计划各字段注释
- J2EE系统的简单权限管理
- SQL逻辑读高的优化
- c++实现的一对锁的封装(挺有意思的)
- android 项目移植/分辨率适配与dip(dp)的使用
- 数据结构学习之链栈c++实现
- Socket(UDP)通信winform demo
- 单队优化DP
- NSLog使用详解
- 范特西的伤感空间日志推荐:我在风中颤抖,你却再难回头
- 如何在指定的内容中找出指定字符串的个数