1.SQL 平均运行事件24.17s
SQL_ID=9w7qc9h3b1vr5
2.5小时内运行12次;
2.SQL分析
--可以发现该SQL直接是硬编码，未使用绑定变量。
--OLTP系统中强烈建议使用绑定变量,以更好的重用执行计划。
SELECT "A1"."POLICYNO",
"A1"."ENDORSEQNO",
"A1"."BUSINESSTYPE",
"A1"."COMPANYCODE",
"A1"."UNDERWRITEENDDATE",
"A1"."STATIND",
"A1"."ACCEPTDATE",
"A1"."AGRICULTUREFLAG",
"A2"."POLICYNO",
"A2"."ENDORSEQNO",
"A2"."RISKCODE",
"A2"."STARTDATE",
"A2"."PRICETAXTOTAL"
FROM "GUPOLICYCOPYMAIN" "A1", "GUPOLICYCOPYITEMKIND" "A2"
WHERE "A1"."POLICYNO" = "A2"."POLICYNO"
AND "A2"."ENDORSEQNO" = '000'
AND ("A1"."ACCEPTDATE" >=
TO_DATE(' 2024-01-23 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"A1"."ACCEPTDATE" <=
TO_DATE(' 2024-01-23 23:59:59', 'syyyy-mm-dd hh24:mi:ss') OR
"A1"."UNDERWRITEENDDATE " >=
TO_DATE(' 2023-12-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"A1"."UNDERWRITEENDDATE" <=
TO_DATE(' 2024-01-31 23:59:59', 'syyyy-mm-dd hh24:mi:ss') OR
"A1"."UNDERWRITEENDDATE" >=
TO_DATE(' 2023-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"A1"."UNDERWRITEENDDATE" <=
TO_DATE(' 2023-01-23 23:59:59', 'syyyy-mm-dd hh24:mi:ss') OR
GREATEST("A1"."UNDERWRITEENDDATE", "A2"."STARTDATE") >=
TO_DATE(' 2023-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
GREATEST("A1"."UNDERWRITEENDDATE", "A2"."STARTDATE") <=
TO_DATE(' 2024-12-31 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))
AND "A1"."ENDORSEQNO" = '000'
AND "A1"."STATIND" = '1'
AND "A1"."BUSINESSTYPE" = '1';
3.执行计划查看
select * from table(dbms_xplan.display_cursor('9w7qc9h3b1vr5',0,'advanced'));
SYS@coredb1> select * from table(dbms_xplan.display_cursor('9w7qc9h3b1vr5',0,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 9w7qc9h3b1vr5, child number 0
-------------------------------------
SELECT "A1"."POLICYNO","A1"."ENDORSEQNO","A1"."BUSINESSTYPE","A1"."COMPA
NYCODE","A1"."UNDERWRITEENDDATE","A1"."STATIND","A1"."ACCEPTDATE","A1"."
AGRICULTUREFLAG","A2"."POLICYNO","A2"."ENDORSEQNO","A2"."RISKCODE","A2".
"STARTDATE","A2"."PRICETAXTOTAL" FROM "GUPOLICYCOPYMAIN"
"A1","GUPOLICYCOPYITEMKIND" "A2" WHERE "A1"."POLICYNO"="A2"."POLICYNO"
AND "A2"."ENDORSEQNO"='000' AND ("A1"."ACCEPTDATE">=TO_DATE('
2024-01-23 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"A1"."ACCEPTDATE"<=TO_DATE(' 2024-01-23 23:59:59', 'syyyy-mm-dd
hh24:mi:ss') OR "A1"."UNDERWRITEENDDATE">=TO_DATE(' 2023-12-01
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"A1"."UNDERWRITEENDDATE"<=TO_DATE(' 2024-01-31 23:59:59', 'syyyy-mm-dd
hh24:mi:ss') OR "A1"."UNDERWRITEENDDATE">=TO_DATE(' 2023-01-01
00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"A1"."UNDERWRITEENDDATE"<=TO_DATE(' 2023-01-23 23:59:59', 'syyyy-mm-dd
hh24:mi:ss') OR GREATEST("A1"."UNDERWRITEENDDATE","A2"."STARTDATE")>=TO_
DATE(' 2023-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') A
Plan hash value: 2054969912
---------------------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 478K(100)| | | |
|* 1 | HASH JOIN | | 343K| 35M| 484M| 478K (1)| 01:35:48 | | |
| 2 | PARTITION RANGE ALL | | 6774K| 407M| | 190K (1)| 00:38:05 | 1 | 12 |
|* 3 | TABLE ACCESS FULL | GUPOLICYCOPYMAIN | 6774K| 407M| | 190K (1)| 00:38:05 | 1 | 12 |
|* 4 | TABLE ACCESS FULL | GUPOLICYCOPYITEMKIND | 17M| 750M| | 217K (1)| 00:43:29 | | |
---------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 - SEL$1
3 - SEL$1 / A1@SEL$1
4 - SEL$1 / A2@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
OPT_PARAM('_b_tree_bitmap_plans' 'false')
OPT_PARAM('_optimizer_null_aware_antijoin' 'false')
OPT_PARAM('_bloom_filter_enabled' 'false')
OPT_PARAM('_optimizer_extended_cursor_sharing' 'none')
OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')
OPT_PARAM('_optimizer_adaptive_cursor_sharing' 'false')
OPT_PARAM('_optimizer_use_feedback' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")*/
4.使用SAA 创建优化任务
--查看SQL在哪个用户下执行。
select owner,table_name from dba_tables where table_name='GUPOLICYCOPYMAIN';
OWNER TABLE_NAME
------------------------------ ------------------------------
CTPROD GUPOLICYCOPYMAIN
--连接到CTPROD用户
conn CTPROD/CTPROD
DECLARE
task_name VARCHAR2(200);
BEGIN
task_name := 'SQL_ACCESS_9w7qc9h3b1vr5';
DBMS_ADVISOR.QUICK_TUNE(DBMS_ADVISOR.SQLACCESS_ADVISOR, task_name,
'SELECT "A1"."POLICYNO",
"A1"."ENDORSEQNO",
"A1"."BUSINESSTYPE",
"A1"."COMPANYCODE",
"A1"."UNDERWRITEENDDATE",
"A1"."STATIND",
"A1"."ACCEPTDATE",
"A1"."AGRICULTUREFLAG",
"A2"."POLICYNO",
"A2"."ENDORSEQNO",
"A2"."RISKCODE",
"A2"."STARTDATE",
"A2"."PRICETAXTOTAL"
FROM "GUPOLICYCOPYMAIN" "A1", "GUPOLICYCOPYITEMKIND" "A2"
WHERE "A1"."POLICYNO" = "A2"."POLICYNO"
AND "A2"."ENDORSEQNO" = ''000''
AND ("A1"."ACCEPTDATE" >=
TO_DATE('' 2024-01-23 00:00:00'', ''syyyy-mm-dd hh24:mi:ss'') AND
"A1"."ACCEPTDATE" <=
TO_DATE('' 2024-01-23 23:59:59'', ''syyyy-mm-dd hh24:mi:ss'') OR
"A1"."UNDERWRITEENDDATE" >=
TO_DATE('' 2023-12-01 00:00:00'', ''syyyy-mm-dd hh24:mi:ss'') AND
"A1"."UNDERWRITEENDDATE" <=
TO_DATE('' 2024-01-31 23:59:59'', ''syyyy-mm-dd hh24:mi:ss'') OR
"A1"."UNDERWRITEENDDATE" >=
TO_DATE('' 2023-01-01 00:00:00'', ''syyyy-mm-dd hh24:mi:ss'') AND
"A1"."UNDERWRITEENDDATE" <=
TO_DATE('' 2023-01-23 23:59:59'', ''syyyy-mm-dd hh24:mi:ss'') OR
GREATEST("A1"."UNDERWRITEENDDATE", "A2"."STARTDATE") >=
TO_DATE('' 2023-01-01 00:00:00'', ''syyyy-mm-dd hh24:mi:ss'') AND
GREATEST("A1"."UNDERWRITEENDDATE", "A2"."STARTDATE") <=
TO_DATE('' 2024-12-31 23:59:59'', ''syyyy-mm-dd hh24:mi:ss''))
AND "A1"."ENDORSEQNO" = ''000''
AND "A1"."STATIND" = ''1''
AND "A1"."BUSINESSTYPE" = ''1''');
END;
/
PL/SQL procedure successfully completed.
CTPROD@coredb1>
5.查看优化任务
--查看上面优化任务创建是否成功以及其基本情况 col STATUS_MESSAGE for a50 set lin 200 select a.owner,a.task_id,a.task_name,execution_start,a.status_message,b.command from dba_advisor_log a,dba_advisor_actions b where a.task_id=b.task_id and b.task_name='SQL_ACCESS_9w7qc9h3b1vr5'; CTPROD@coredb1> OWNER TASK_ID TASK_NAME EXECUTION_START STATUS_MESSAGE COMMAND ------- -------- ---------- ------------------------------ ------------------ -------------------------------------------------- CTPROD 125986 SQL_ACCESS_9w7qc9h3b1vr5 23-JAN-24 Access advisor execution completed CREATE MATERIALIZED VIEW LOG CTPROD 125986 SQL_ACCESS_9w7qc9h3b1vr5 23-JAN-24 Access advisor execution completed CREATE MATERIALIZED VIEW LOG CTPROD 125986 SQL_ACCESS_9w7qc9h3b1vr5 23-JAN-24 Access advisor execution completed CREATE MATERIALIZED VIEW CTPROD 125986 SQL_ACCESS_9w7qc9h3b1vr5 23-JAN-24 Access advisor execution completed GATHER TABLE STATISTICS
6.查看使用优化建议前后的资源成本对比信息
SELECT sql_id, precost 优化前cost, postcost 优化后cost,(precost/postcost) cost提升倍数,
decode(PRIORITY,1,'高',2,'中',3,'低') 重要性
FROM dba_advisor_sqla_wk_stmts WHERE task_name='SQL_ACCESS_9w7qc9h3b1vr5';
SQL_ID 优化前COST 优化后COST COST提升倍数 重
------------- ---------- ---------- ------------ ---
b64y3xkbtcxzu 478977 67125 7.13559777 中
col STATUS_MESSAGE for a50
set lin 300
select a.owner,
a.task_id,
a.task_name,
execution_start,
a.status_message,
b.command from dba_advisor_log a,
dba_advisor_actions b
where a.task_id = b.task_id and b.task_name = 'SQL_ACCESS_9w7qc9h3b1vr5';
OWNER TASK_ID TASK_NAME EXECUTION_START STATUS_MESSAGE COMMAND
------------------------------ ---------- ------------------------------ ------------------ -------------------------------------------------- ----------------------------------------------------------------
CTPROD 125986 SQL_ACCESS_9w7qc9h3b1vr5 23-JAN-24 Access advisor execution completed CREATE MATERIALIZED VIEW LOG
CTPROD 125986 SQL_ACCESS_9w7qc9h3b1vr5 23-JAN-24 Access advisor execution completed CREATE MATERIALIZED VIEW LOG
CTPROD 125986 SQL_ACCESS_9w7qc9h3b1vr5 23-JAN-24 Access advisor execution completed CREATE MATERIALIZED VIEW
CTPROD 125986 SQL_ACCESS_9w7qc9h3b1vr5 23-JAN-24 Access advisor execution completed GATHER TABLE STATISTICS
--查看oracle的建议。
CTPROD@coredb1>
select COMMAND||' '||ATTR1||' '||ATTR3||' '||ATTR4||' '||ATTR5||' '||ATTR6 from dba_advisor_actions where task_name = 'SQL_ACCESS_9w7qc9h3b1vr5';
COMMAND ATTR1 ATTR3 ATTR4 ATTR5 ATTR6
------------------------------------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
CREATE MATERIALIZED VIEW LOG "CTPROD"."GUPOLICYCOPYITEMKIND" ROWID
CREATE MATERIALIZED VIEW LOG "CTPROD"."GUPOLICYCOPYMAIN" ROWID
CREATE MATERIALIZED VIEW "CTPROD"."MV$$_1EC220000" REFRESH FAST WITH ROWID ENABLE
GATHER TABLE STATISTICS "CTPROD"."MV$$_1EC220000" -1
--oracle的建议是创建物化视图,可以提升7倍的性能。
7.检查表的情况
GUPOLICYCOPYMAIN 表是分区表,但是从2012年之后未做分区。
分区字段是 INPUTDATE,但是查询中并没有使用到该字段,而是使用:UNDERWRITEENDDATE,ACCEPTDATE 字段。
该SQL9:30~11:00,总共运行12次。说明不算特别频繁。
--由于该SQL是在日期字段上
GUPOLICYCOPYITEMKIND 表应该在:STARTDATE 字段上简历索引。
GUPOLICYCOPYMAIN 表应该在:UNDERWRITEENDDATE,ACCEPTDATE 字段上建立索引。
而不应该如上面所示的建议,建立物化视图。因为物化视图的维护较为麻烦。
P2001 TO_DATE(' 2001-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') tablespace tblspace_oth pctfree 10 initrans 1 maxtrans 255 storage ( initial 64k next 1m minextents 1 maxextents unlimited )
P2002 TO_DATE(' 2002-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') tablespace tblspace_oth pctfree 10 initrans 1 maxtrans 255 storage ( initial 64k next 1m minextents 1 maxextents unlimited )
P2003 TO_DATE(' 2003-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') tablespace tblspace_oth pctfree 10 initrans 1 maxtrans 255 storage ( initial 64k next 1m minextents 1 maxextents unlimited )
P2004 TO_DATE(' 2004-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') tablespace tblspace_oth pctfree 10 initrans 1 maxtrans 255 storage ( initial 64k next 1m minextents 1 maxextents unlimited )
P2005 TO_DATE(' 2005-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') tablespace tblspace_oth pctfree 10 initrans 1 maxtrans 255 storage ( initial 64k next 1m minextents 1 maxextents unlimited )
P2006 TO_DATE(' 2006-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') tablespace tblspace_oth pctfree 10 initrans 1 maxtrans 255 storage ( initial 64k next 1m minextents 1 maxextents unlimited )
P2007 TO_DATE(' 2007-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') tablespace tblspace_oth pctfree 10 initrans 1 maxtrans 255 storage ( initial 64k next 1m minextents 1 maxextents unlimited )
P2008 TO_DATE(' 2008-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') tablespace tblspace_oth pctfree 10 initrans 1 maxtrans 255 storage ( initial 64k next 1m minextents 1 maxextents unlimited )
P2009 TO_DATE(' 2009-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') tablespace tblspace_oth pctfree 10 initrans 1 maxtrans 255 storage ( initial 64k next 1m minextents 1 maxextents unlimited )
P2010 TO_DATE(' 2010-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') tablespace tblspace_oth pctfree 10 initrans 1 maxtrans 255 storage ( initial 64k next 1m minextents 1 maxextents unlimited )
P2011 TO_DATE(' 2011-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') tablespace tblspace_oth pctfree 10 initrans 1 maxtrans 255 storage ( initial 384k next 1m minextents 1 maxextents unlimited )
P2012 MAXVALUE tablespace tblspace_oth pctfree 10 initrans 1 maxtrans 255 storage ( initial 64k next 1m minextents 1 maxextents unlimited )
8.优化方案
--给where条件中的字段创建索引。 create index ctprod.I_STARTDATE on ctprod.GUPOLICYCOPYITEMKIND(STARTDATE); create index ctprod.I_UNDERWRITEENDDATE on ctprod.GUPOLICYCOPYMAIN(UNDERWRITEENDDATE) local; create index ctprod.I_ACCEPTDATE on ctprod.GUPOLICYCOPYMAIN(ACCEPTDATE)local; --由于是生产环境,暂时无法验证。只能无业务时间,创建索引再看执行计划。
9.总结
SQL优化顾问SAA,有时候可以给出正确的优化建议，但是有时候不能给出好的优化建议。
--删除优化任务。
exec dbms_sqltune.drop_tuning_task('SQL_ACCESS_9w7qc9h3b1vr5');
PL/SQL procedure successfully completed