程序优化小结

最新推荐文章于 2022-12-22 14:47:43 发布

吴不凡

最新推荐文章于 2022-12-22 14:47:43 发布

阅读量546

点赞数

分类专栏： oracle性能优化文章标签：优化 sql

本文链接：https://blog.csdn.net/buzhidao_2/article/details/55811691

版权

oracle性能优化专栏收录该内容

4 篇文章 0 订阅

订阅专栏

目录
文档控制 2
1. 程序优化小结 4
1.1. 查找效率低的主要瓶颈代码 4
1.2. 分析及优化瓶颈代码 7
1.3. sql优化相关 12

程序优化小结
客户化的程序包很可能随着数据量的增大会变得越来越慢，除此之外，在使用的过程
中需要添加新的逻辑，也可能导致程序运行效率降低。因此，在项目上对一些老从客
户化程序包进行优化应该还是很常见的。
这两周项目上优化了两个老的计算程序，发现程序程序优化是一个很大的课题，还有
很多不懂和需要学习的地方。暂且把这段时间学到的东西稍微作一个小结，希望对大
家有所帮助；本次文档中部分内容参考网上资料以及其他程序优化文档；

1.1. 查找导致效率低的瓶颈代码
俗话说，磨刀不误砍柴工，优化程序前我们先得花一些时间搞懂程序效率低的原因。这一点很重要，因为如果没弄清楚程序为什么运行慢，就兴冲冲的想去优化，很可能会走很多弯路，时间花费了很多不说，还没有很好的效果；
如何查找导致效率低的瓶颈代码呢，我觉得很方便的是创建自治事务的存储过程，在该过程中给日志表写入消息，在程序的重要的节点上写上相应的日志和时间，运行一次程序，再对日志表进行输出分析，可以很方便的看出来哪一段代码导致程序效率低；如果觉得创建日记表的过程很麻烦，直接使用dbms_output.put_line输出日志和当前系统时间也是可以的。
1.1.1. 创建日志表

– Create table
create table AGM_BG_RUN_LOG
(
id NUMBER,
request_id NUMBER,
log_content VARCHAR2(2000),
creation_date DATE default sysdate not null,
created_by NUMBER default -1 not null,
last_updated_by NUMBER default -1 not null,
last_update_date DATE default sysdate not null,
attribute1 VARCHAR2(500),
attribute2 VARCHAR2(500),
attribute3 VARCHAR2(500),
attribute4 VARCHAR2(500),
attribute5 VARCHAR2(500),
attribute6 VARCHAR2(500),
attribute7 VARCHAR2(500),
attribute8 VARCHAR2(500),
attribute9 VARCHAR2(500),
attribute10 VARCHAR2(500),
attribute11 VARCHAR2(500),
attribute12 VARCHAR2(500),
attribute13 VARCHAR2(500),
attribute14 VARCHAR2(500),
attribute15 VARCHAR2(500),
attribute16 VARCHAR2(500),
attribute17 VARCHAR2(500),
attribute18 VARCHAR2(500),
attribute19 VARCHAR2(500),
attribute20 VARCHAR2(500),
attribute21 VARCHAR2(500),
attribute22 VARCHAR2(500),
attribute23 VARCHAR2(500),
attribute24 VARCHAR2(500),
attribute25 VARCHAR2(500),
attribute26 VARCHAR2(500),
attribute27 VARCHAR2(500),
attribute28 VARCHAR2(500),
attribute29 VARCHAR2(500),
attribute30 VARCHAR2(500),
version NUMBER default 0 not null
)
tablespace JEP_TS_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
– Add comments to the table
comment on table AGM_BG_RUN_LOG
is ‘请求运行日志’;
– Add comments to the columns
comment on column AGM_BG_RUN_LOG.id
is ‘主键’;
comment on column AGM_BG_RUN_LOG.request_id
is ‘执行请求’;
comment on column AGM_BG_RUN_LOG.log_content
is ‘日志内容’;
– Create/Recreate indexes
create unique index AGM_BG_RUN_LOG_U1 on AGM_BG_RUN_LOG (ID)
tablespace JEP_TS_INDEX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);

1.1.2. 创建自治事务的存储过程

–程序运行日志
procedure log(p_job_id in number, p_message in varchar2) is
pragma autonomous_transaction;
begin
DBMS_OUTPUT.PUT_LINE(P_MESSAGE);
insert into agm_bg_run_log
(id, request_id, log_content)
values
(agm_bg_run_log_s.nextval, p_job_id, p_message);
commit;
end log;

1.1.3. 在关键节点上写上日志
写日志的地方除了程序开始和结束的位置，还有程序里面你觉得可能导致程序效率低的节点。
1.1.4. 分析日志
运行完程序后，查询出日志表进行分析；
SELECT *
FROM agm_bg_run_log t
WHERE t.request_id = 2048658
ORDER BY t.creation_date, t.id;
一般情况下查找跨度最大的时间，定位两条日志之间的代码，就能找到导致效率低下的代码；

但是有时候则不一定时间跨度最大的就是效率瓶颈段代码，比如一个程序运行了一个小时，程序主要结构是一个cursor循环里面有很多逻辑的验证和处理，结构如下：
…
tnnt_common_utl.log(p_job_id, ‘日志1’);
for rec in (..) loop
tnnt_common_utl.log(p_job_id, ‘日志2 ‘);
…;–各种处理逻辑
end loop;
tnnt_common_utl.log(p_job_id, ‘日志3’);
…
日志1输出到日志到第一次日志2输出的时间为五六分钟，日志2之间的时间大概几秒钟甚至更短，最后日志3输出时程序的总共时间就达到一个小时了；这时候我们可能会把主要精力放到cursor优化上面，实际这样的话方向就有点搞错了，因为本次运行虽然日志1输出到日志到第一次日志2输出的时间最长，日志2之间的输出时间跨度并不大，但是因为循环的数据量很大，很小的时间累积之后就变得很可怕了，我们会发现日志2第一次输出到日志3的输出时间占了五十几分钟，因此优化cursor循环里面的代码才应该是我们最先关注的。
值得注意的是，cursor里面的sql应该是一次性查询出来的，而不是查询一条循环一次；举个例子我们常会用一下的代码来锁表记录
FOR rec IN (SELECT *
FROM table_name
where……
FOR UPDATE NOWAIT
)
LOOP
EXIT;
END LOOP;
第一次进入循环我们就exit了，但是可以锁到所有符合where条件的记录，说明cursor一开始就已查询出所有的记录；
以上，我们就可以确定日志1到日志2的时间就是cursor里面sql运行的时间，后面处理的时间与cursor里的查询sql就没多大关系了。

1.2. 分析及优化瓶颈代码
1.2.1. 将瓶颈代码段单独取出来分析
1、如果瓶颈代码不仅仅是个查询sql，我一般会编写一个匿名块，将瓶颈代码放入匿名块中进行分析；
2、测试环境中在分析测试代码时需要注意，每次运行过后应该清除一次本次session产生的缓存，否则会影响测试的时间，清除缓存的代码如下：
ALTER SESSION SET EVENTS ‘immediate trace name flush_cache’;
如果在正式环境测试查询语句，则需要注意不可随意清除缓存。
3、如果查询sql涉及的表很大，且表数量较多，很可能测试环境的执行计划和正式环境的执行计划是不同的；除了可能是两个环境表的数据量不同这个原因，还可能与两个环境的数据库服务器性能不同有关系；执行计划不同，就会导致同一个sql在两个环境的运行时间相差很大，使得测试环境的测试结果没有很大的参考意义，就不得不到正式环境去测试sql的性能了。
4、分析代码时，如果觉得执行计划不是想要的结果也可以尝试对sql里面的表进行数据收集，还可以使用hint强制oracle执行某个索引，指定某些表为驱动表等；数据收集和hint的使用将在1.3节中进行介绍。
1.2.2. 优化瓶颈代码

1、情景一：对于以下这种结构代码
…
tnnt_common_utl.log(p_job_id, ‘日志1’);
for rec in (..) loop
tnnt_common_utl.log(p_job_id, ‘日志2 ‘);
1.验证1
2.验证2
3.处理1
4.处理2
…;–各种处理逻辑
end loop;
tnnt_common_utl.log(p_job_id, ‘日志3’);
…
如果是因为循环里面的处理逻辑导致效率的降低，可以尝试批量处理方式，同时可以尝试先将cursor里面的数据插入到临时表中，之后的操作都基于临时表，插入临时表的操作可以使用FETCH … BULK COLLECT INTO处理结构,可以提高插入的效率;插入事例如下：
PROCEDURE populate_sales IS

CURSOR cur_line IS
  SELECT ... FROM ...;

TYPE l_line_tbl_type IS TABLE OF cur_line%ROWTYPE INDEX BY BINARY_INTEGER;
l_line_tbl l_line_tbl_type;

l_loop_bool BOOLEAN := FALSE;

BEGIN

-- start
DELETE FROM jep_tnnt_sales_temp t WHERE t.batch_id = p_batch_id;
l_line_tbl.delete;

OPEN cur_line;
LOOP
  FETCH cur_line BULK COLLECT
    INTO l_line_tbl LIMIT 1000;
  l_loop_bool := cur_line%NOTFOUND;

  IF l_line_tbl.count > 0 THEN
    FORALL i IN l_line_tbl.first .. l_line_tbl.last
      INSERT INTO jep_tnnt_sales_temp
        (batch_id,
         project_num,
         sales_header_id,
         payment_method,
         deal_amount,
         tenant_num,
         premise_num,
         deal_date,
         key_cooperation_mode,
         attribute1, --刷卡店铺
         attribute2, --sales_payment_line_id
         attribute3, --source_header_id
         attribute4 --card_type
         )
      VALUES
        (p_batch_id,
         l_line_tbl(i).project_num,
         l_line_tbl(i).sales_header_id,
         l_line_tbl(i).payment_method,
         l_line_tbl(i).deal_amt,
         l_line_tbl(i).tenant_num,
         l_line_tbl(i).premise_num,
         l_line_tbl(i).deal_date,
         l_line_tbl(i).key_cooperation_mode,
         l_line_tbl(i).deal_premise_num,
         l_line_tbl(i).sales_payment_line_id,
         l_line_tbl(i).source_header_id,
         l_line_tbl(i).card_type);

    l_line_tbl.delete;
  END IF;
  EXIT WHEN l_loop_bool;
END LOOP;
CLOSE cur_line;
l_line_tbl.delete;

END populate_sales;
优化的处理结构：
…
–1.将cursor的数据fetch到临时表
–2.基于临时表批量验证1
–3.基于临时表批量验证3
–4.基于临时表批量处理1
–5.基于临时表批量处理2
…
2、情景二：如果大数据表A和大数据表B关联，且SQL的where过滤条件主要和A表有关、需要筛选出的数据量相对较小，那么有两种办法可提高效率：
1）可以考虑建立一个临时表，先根据where条件筛选A表的数据后，再用B表和A的临时表做关联取数。对于大数据量建议采用临时表，游标是要占用系统资源的，而且记录数目越多占用的资源越多，临时表不全都是存放在硬盘的。当你读取的时候，它是一块（页）的读出来的。这些都是占留内存的。
2）可以考虑建立一个游标，先根据where条件筛选A表的数据到一个游标，在循环游标里和B表作关联。游标查询出的数据少于3000条时建议用游标。
3、情景三：如果sql里面表数量很多也可以借鉴2中的方法进行拆分，将小数据量的几张表和大数据量的几张表分开嵌套循环；外面是小表的关联cursor，里面是大表的关联sql；同样也可以尝试将小表关联的数据插入临时表，在和几张大表进行关联；
4、情景四：查询视图异常缓慢（视图内包含大数据表）
情景：现有的以下查询SQL速度非常慢，例：
select *
from my_view tb
where tb.tb_big_id = 123;
视图my_view的创建语句为：
select *
from tb_big_table big,
tb_small_table1 small1,
tb_small_table2 small2
where big.tb_big_id = small1.tb_big_id
and small1.tb_small_id1 = small2.tb_small_id1;
思路：将查询视图的SQL的where子句中和大数据表相关的条件放到视图自身的SQL里，如此一来可以缩小大数据表的数据量再和其他表关联。
策略：（1）利用面向对象的方法，且同一个会话Session中可以对同一个变量进行存取值，首先定义一个程序包，包含大数据表的条件的局部变量（需初始化值为不存在的一个值）以及对该变量进行赋值的过程、取值的函数。例：
create or replace package my_view_pkg is
– 赋值过程
procedure tb_big_id(pi_id number);
– 取值函数
function tb_big_id return number;
end my_view_pkg;
/
create or replace package body my_view_pkg is
– 定义局部变量并设置初始值
ln_tb_big_id number := -1;
– 赋值过程
procedure tb_big_id(pi_id number) is
begin
ln_tb_big_id := tb_big_id;
end tb_big_id;
– 取值函数
function tb_big_id return number is
begin
return tb_big_id;
end tb_big_id;
end my_view_pkg;
/
（2）修改原有的视图的SQL，用刚建立的包里的取值函数作为条件的一端。列：
create or replace view my_view as
select *
from tb_big_table big,
tb_small_table1 small1,
tb_small_table2 small2
where big.tb_big_id = my_view_pkg.tb_big_id
and big.tb_big_id = small1.tb_big_id
and small1.tb_small_id1 = small2.tb_small_id1;
（3）在查询该视图前，为该视图内引用的变量进行事先赋值。例：
begin
– 先赋值
my_view_pkg.tb_big_id = 123;
– 查询结果和原视图效果一致，效果却大大提升
select * from my_view;
end;
1.2.3. 将优化后的代码替换源程序的瓶颈代码

优化达到代码结束后，将优化后的代码替换原有代码进行测试；

1.3. sql优化相关知识
1.3.1. 统计信息收集（数据收集）
1.在给sql做执行计划前，如果担心当前表的统计信息不是最新的，可以对这些表进行数据收集, 就是收集表和索引的信息，CBO根据这些信息决定SQL最佳的执行路径。通过对表的分析，可以产生一些统计信息，通过这些信息oracle的优化程序可以优化。
表级别的数据收集的函数如下：
DBMS_STATS.GATHER_TABLE_STATS ( ownname VARCHAR2, tabname VARCHAR2, partname VARCHAR2, estimate_percent NUMBER, block_sample BOOLEAN, method_opt VARCHAR2, degree NUMBER, granularity VARCHAR2, cascade BOOLEAN, stattab VARCHAR2, statid VARCHAR2, statown VARCHAR2, no_invalidate BOOLEAN, force BOOLEAN);
参数说明:
ownname:要分析表的拥有者
tabname:要分析的表名.
partname:分区的名字,只对分区表或分区索引有用.
estimate_percent:采样行的百分比,取值范围[0.000001,100],null为全部分析,不采样. 常量:DBMS_STATS.AUTO_SAMPLE_SIZE是默认值,由oracle绝定最佳取采样值.
block_sapmple:是否用块采样代替行采样.
method_opt:决定histograms信息是怎样被统计的.method_opt的取值如下:
for all columns:统计所有列的histograms.
for all indexed columns:统计所有indexed列的histograms.
for all hidden columns:统计你看不到列的histograms
for columns SIZE | REPEAT | AUTO | SKEWONLY:统计指定列的histograms.N的取值范围[1,254]; REPEAT上次统计过的histograms;AUTO由oracle决定N的大小;SKEWONLY multiple end-points with the same value which is what we define by “there is skew in the data
degree:决定并行度.默认值为null.
granularity:Granularity of statistics to collect ,only pertinent if the table is partitioned.
cascace:是收集索引的信息.默认为falase.
stattab指定要存储统计信息的表,statid如果多个表的统计信息存储在同一个stattab中用于进行区分.statown存储统计信息表的拥有者.以上三个参数若不指定,统计信息会直接更新到数据字典.
no_invalidate: Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE.
force:即使表锁住了也收集统计信息.
例子：

execute dbms_stats.gather_table_stats(ownname => ‘owner’,tabname => ‘table_name’ ,estimate_percent => null ,method_opt => ‘for all indexed columns’ ,cascade => true);

execute dbms_stats.gather_table_stats(ownname => ‘boss0817’,tabname => ‘t_subscriberelations’,estimate_percent => null ,method_opt => ‘for all indexed columns’ ,cascade => true);
2、EBS中也可以提交并发请求进行统计信息的收集
Oracle ERP中有几个与Gather有关的标准Request：
所有列信息数据统计：
Gather All Column Statistics –FND_STATS.GATHER_ALL_COLUMN_STATS()
某张表的列信息数据统计：
Gather Column Statistics –FND_STATS.GATHER_COLUMN_STATS()
schema级别的信息数据统计：
Gather Schema Statistics –FND_STATS.GATHER_SCHEMA_STATS()
表级别信息数据统计：
Gather Table Statistics –FND_STATS.GATHER_TABLE_STATS()
查看FND_STATS 这个Package的写法，其实它就是在调用Oracle DB中Standard的Package dbms_stats 中的某些Function。
参考网上资料：1.http://blog.csdn.net/cnham/article/details/5724934
2. http://blog.itpub.net/26892340/viewspace-721935/
3. http://www.cnblogs.com/yangzw478/archive/2012/12/11/2812508.html
1.3.2. hint的使用
hints是oracle提供的一种机制，用来告诉优化器按照我们的告诉它的方式生成执行计划。
1.索引控制
/+ INDEX(TABLE INDEX1) /
/+ NO_INDEX(TABLE INDEX1, index2) /
/+ INDEX(mmt,index1) INDEX(mtln,index2) /
表明对表选择索引的扫描方法。
第1种是指定SQL要走的索引，不过注意使用别名，且条件中应该存在索引字段的条件，事例如下:

第2种表示禁用某个索引，特别适合于准备删除某个索引前的评估操作。

第3种表示指定多个表的多个索引。

2.驱动表控制：/+ ORDERED /
FROM子句中默认最后一个表是驱动表，ORDERED将from子句中第一个表作为驱动表. 特别适合于多表连接非常慢时尝试。例如：
SELECT /+ ORDERED / A.COL1,B.COL2,C.COL3 FROM TABLE1 A,TABLE2 B,TABLE3 C WHERE A.COL1=B.COL1 AND B.COL1=C.COL1;
1.3.3. 连接方式
使用CBO 时，要注意看采用了哪种类型的表连接方式。ORACLE的共有Sort Merge Join（SMJ）、Hash Join（HJ）和Nested Loop Join（NL）；
1、Nested Loop Join（NL）
对于被连接的数据子集较小的情况，nested loop连接是个较好的选择。nested loop就是扫描一个表，每读到一条记录，就根据索引去另一个表里面查找，没有索引一般就不会是 nested loops。一般在nested loop中，驱动表满足条件结果集不大，被驱动表的连接字段要有索引，这样就走nstedloop。如果驱动表返回记录太多，就不适合nested loops了。如果连接字段没有索引，则适合走hash join，因为不需要索引。
可用ordered提示来改变CBO默认的驱动表，可用USE_NL(table_name1 table_name2)提示来强制使用nested loop。

2、HASH JOIN
hash join是CBO 做大数据集连接时的常用方式。优化器扫描小表（或数据源），利用连接键（也就是根据连接字段计算hash 值）在内存中建立hash表，然后扫描大表，每读到一条记录就来探测hash表一次，找出与hash表匹配的行。
当小表可以全部放入内存中，其成本接近全表扫描两个表的成本之和。如果表很大不能完全放入内存，这时优化器会将它分割成若干不同的分区，不能放入内存的部分就把该分区写入磁盘的临时段，此时要有较大的临时段从而尽量提高I/O 的性能。临时段中的分区都需要换进内存做hash join。这时候成本接近于全表扫描小表+分区数*全表扫描大表的代价和。至于两个表都进行分区，其好处是可以使用parallel query，就是多个进程同时对不同的分区进行join，然后再合并。但是复杂。
以下条件下hash join可能有优势：
两个巨大的表之间的连接。
在一个巨大的表和一个小表之间的连接。
可用ordered提示来改变CBO默认的驱动表，可用USE_HASH(table_name1 table_name2)提示来强制使用hash join。

3、SORT MERGE JOIN
sort merge join的操作通常分三步：对连接的每个表做table access full；对table access full的结果进行排序；进行merge join对排序结果进行合并。sort merge join性能开销几乎都在前两步。一般是在没有索引的情况下，9i开始已经很少出现了，因为其排序成本高，大多为hash join替代了。
通常情况下hash join的效果都比sort merge join要好，然而如果行源已经被排过序，在执行sort merge join时不需要再排序了，这时sort merge join的性能会优于hash join。
在全表扫描比索引范围扫描再通过rowid进行表访问更可取的情况下，sort merge join会比nested loops性能更佳。
可用USE_MERGE(table_name1 table_name2)提示强制使用sort merge join。

参考资料网址：1. http://www.cnblogs.com/zwl715/p/3788070.html
2. http://www.cnblogs.com/zwl715/p/3789042.html
1.3.4. 优化sql注意点
1、索引只能告诉你什么存在于表中，而不能告诉你什么不存在于表中。对于存在空值的列可以创建伪复合索引，具体如下：
某一张表mytable中含有三列 A\B\C，其中A列中含有NULL值
现在有索引 create index ind _a on mytable(A)
select * from mytable where a is null(is not null) 均不走索引
如果有伪复合索引 create index ind_a1 on mytable(A,0)
则：select * from mytable where a is null(is not null) 走索引
需要注意的是，当空值占比很大时，这种方法是走不了索引的；
也可以建立nvl的函数索引：
CREATE INDEX ind _a ON mytable (NVL(A,0));
查询时如下：
select * from mytable where a is nvl(A,0)=:P;
2、经常同时存取多列且每列都含有重复值可考虑建立组合索引。组合索引要尽量使关键查询形成索引覆盖，其前导列一定是使用最频繁的列。
3、LIKE用于模糊检索，LIKE检索的样式有三种：前匹配(XX%)、中间匹配(X%X)、后匹配(%XX)。对于前匹配可以使用索引，而使用中间匹配和后匹配，都不能使用索引。因此除非必要，否则应尽量避免使用中间匹配和后匹配。
4.使用hint的方式调整sql执行时的连接方式，索引使用，使之与另一个未加hint的sql一致，查询效果也有可能不同，因为索引的使用方式有可能不同。

共有五类不同的使用模式。

1。INDEX UNIQUE SCAN 效率最高，主键或唯一索引
2。INDEX FULL SCAN 有顺序的输出，不能并行读索引
问题：如果表中建立了多个索引，Oracle是把所有的索引都扫描一遍么？

3。INDEX FAST FULL SCAN 读的最块，可以并行访问索引，但输出不按顺序
4。INDEX RANGE SCAN 给定的区间查询
5。INDEX SKIP SCAN 联合索引，不同值越少的列，越要放在前面
参考链接：http://www.cnblogs.com/tracy/archive/2011/09/02/2163462.html
5. 如果你自己能访问DB服务器的话，直接在DB服务器上执行$ORACLE_HOME/rdbms/admin/awrrpt脚本就能生成
生成的时候会让你选择格式是html的还是txt得，会让选择要看哪个时间段的数据
html格式的可以很清晰地看到各种top10语句