SQL语句简单优化思路

简单优化思路:

1. 生成并查看awr报告
2. 检查top sql
3. 检查sql中消耗时间多的涉及表的记录数，统计信息准确性，以及SQL对应执行计划
4. 确定优化方法，选择合适的字段创建索引
5. 操作过程放在后台执行，避免网络中断问题
6. 检查优化结果是否达到预期效果

检查awr报告，发现涉及表TELE_PERFORMANCE_BILL的SQL有多出，并且消耗时间和逻辑读较多，SQL相对简单，可考虑快速优化问题：

涉及语句UPDATE TELE_PERFORMANCE_BILL B SET B.RESOURCE_ID=:B2 WHERE B.IP=:B1 AND FLDINSERTTIME>SYSDATE-1

SELECT DISTINCT IP FROM TELE_PERFORMANCE_BILL WHERE FLDINSERTTIME>SYSDATE-1

UPDATE TELE_PERFORMANCE_BILL B SET B.RESOURCE_ID=:B2 WHERE B.IP=:B1 AND FLDINSERTTIME>SYSDATE-1

报告截图如下：

通过PL/SQL工具快速查看相关执行计划，查找一天的数据，却都走了全表扫描。

检查表大小：5000多万条记录。

查看where条件中的字段，在表中没有相关索引。所以考虑在这些SQL语句的条件中权衡，只需在FLDINSERTTIME字段上创建索引即可。

考虑到在线的系统，同时sar 1 100查看主机cpu资源比较空闲。确定通过多个并行在线创建索引，同时放在后台执行，以避免网络中断引起的异常问题。

select * from user_tables where table_name='TELE_PERFORMANCE_BILL'

 

 

准备后台执行脚本：

[[email protected] oracle]# cat /home/oracle/createidx.sql

alter session set workarea_size_policy=manual;

alter session set sort_area_size=1073741820;

alter session set sort_area_retained_size=1073741820;

alter session set db_file_multiblock_read_count=128;

create index IDX_PERFORMANCE_FLDINSERTTIME on TELE_PERFORMANCE_BILL (fldinserttime) online parallel 8;

alter index IDX_PERFORMANCE_FLDINSERTTIME noparallel;

[[email protected] oracle]# cat idx.sh

#!/bin/sh

su - oracle -c "sqlplus /nolog <<EOF

set time on

set timing on

conn xxxx/xxxx

@/home/oracle/createidx.sql

exit;

EOF" >> /home/oracle/createidx.out

 

[[email protected] ~]# cd /home/oracle

后台执行

[[email protected] oracle]# nohup ./idx.sh &

 

 

[[email protected] oracle]# cat createidx.out

 

SQL*Plus: Release 12.2.0.1.0 Production on Fri Nov 29 11:04:17 2019

 

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

 

SQL> 11:04:17 SQL> 11:04:17 SQL> Connected.

11:04:17 SQL>

Session altered.

 

Elapsed: 00:00:00.00

 

Session altered.

 

Elapsed: 00:00:00.00

 

Session altered.

 

Elapsed: 00:00:00.00

 

Session altered.

 

Elapsed: 00:00:00.00

 

Index created.

 

Elapsed: 00:00:41.58

 

Index altered.

 

Elapsed: 00:00:00.81

11:04:59 SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

再次检查执行计划，顺利走索引了。

SELECT DISTINCT IP FROM TELE_PERFORMANCE_BILL WHERE FLDINSERTTIME>SYSDATE-1

其中某SQL原先107秒降到了0.391秒，性能明显提升。