oracle优化逻辑读过⾼,SQL逻辑读⾼的优化
经常在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,
