oracle优化案例2-两种优化方式的比较-sidy

最新推荐文章于 2021-04-05 06:38:42 发布

原创最新推荐文章于 2021-04-05 06:38:42 发布 · 933 阅读

0 ·

CC 4.0 BY-SA版权

oracle优化案例专栏收录该内容

4 篇文章

订阅专栏

本文深入探讨了SQL性能优化策略，特别关注于如何通过优化查询结构、利用存储过程以及建立函数索引来减少逻辑读和物理读，进而提升数据库性能。详细解释了Oracle逻辑读的概念及其对性能的影响，并提供了优化案例分析。

sql：

SELECT B.SCODE F_SCODE, --股票代码
B.SNAME F_SNAME, --股票简称
B.STYPE F_STYPE, --证券代码
B.MKTCODE F_MKTCODE, --市场代码
TO_CHAR(SYSDATE, 'YYYYMMDD') CREATETIME, --当前表创建时间
TO_CHAR(SYSDATE, 'YYYYMMDD') UPDATETIME, --原记录更新时间
'' F_UNIT, --单位
SEQ_SI_STK_20023.NEXTVAL SYNTIMESTAMP, --内部使用
0 F_TYPE,
A.F_TRADEDAY, --交易日
A.F20140_20015 F20182_20023, --年最高价日
D.STARTDAY,
D.ENDDAY,
''
FROM (SELECT A.SCODE, A.F_TRADEDAY, B.TRADEDATE F20140_20015
FROM (SELECT A.SCODE,
C.YEAR F_TRADEDAY, --
MAX(A.HIGHPRICE) NDATA, --
''
FROM SDC_STOCKQUOTE A, SDC_DAYPRO C
WHERE A.TRADEDATE = C.TDATE
AND A.MKTCODE IN (1, 2)
AND C.YEAR = TO_CHAR(SYSDATE,'YYYY')
GROUP BY A.SCODE, C.YEAR) A,
(SELECT A.SCODE,
A.TRADEDATE,
C.YEAR F_TRADEDAY, --
A.HIGHPRICE NDATA
FROM SDC_STOCKQUOTE A, SDC_DAYPRO C
WHERE A.TRADEDATE = C.TDATE
AND A.MKTCODE IN (1, 2)
AND C.YEAR = TO_CHAR(SYSDATE,'YYYY')
) B
WHERE A.SCODE = B.SCODE
AND A.F_TRADEDAY = B.F_TRADEDAY
AND A.NDATA = B.NDATA) A,
SDC_SECURITY B,
(SELECT C.YEAR, MIN(C.TDATE) STARTDAY, MAX(C.TDATE) ENDDAY, ''
FROM SDC_DAYPRO C
GROUP BY C.YEAR) D
WHERE A.SCODE = B.SCODE
AND A.F_TRADEDAY = D.YEAR
AND B.STYPE IN (2, 3);

执行计划：

由执行计划可以看出改sql最大的问题时11、12都是全表扫描，导致速度很慢，最主要的是12表，因为11（sdc_daypro）记录很小，没必要走索引，12表（sdc_stockquote）有几百万记录，要是全表扫描的话很慢。但是从搜索的数据来看，要搜索的数据占全部数据的10%左右，所以sql中：

SELECT A.SCODE,
C.YEAR F_TRADEDAY, --
MAX(A.HIGHPRICE) NDATA, --
''
FROM SDC_STOCKQUOTE A, SDC_DAYPRO C
WHERE A.TRADEDATE = C.TDATE
AND A.MKTCODE IN (1, 2)
AND C.YEAR = TO_CHAR(SYSDATE,'YYYY')
GROUP BY A.SCODE, C.YEAR；

不会走索引。

而且这两个表SDC_STOCKQUOTE A, SDC_DAYPRO C在sql中还用到两次，所以从这里着手优化。

优化方案一：从业务上分析只用SDC_STOCKQUOTE表，建立函数索引

（1）、修改后的sql：

WITH TEMP AS
(SELECT A.SCODE,
SUBSTR(A.TRADEDATE, 1, 4) F_YEAR,
MAX(A.HIGHPRICE) NDATA
FROM SDC_STOCKQUOTE A
WHERE SUBSTR(A.TRADEDATE, 1, 4) = TO_CHAR(SYSDATE, 'YYYY') and A.MKTCODE IN (1, 2)
GROUP BY A.SCODE, SUBSTR(A.TRADEDATE, 1, 4))
SELECT B.SCODE F_SCODE, --股票代码
B.SNAME F_SNAME, --股票简称
B.STYPE F_STYPE, --证券代码
B.MKTCODE F_MKTCODE, --市场代码
TO_CHAR(SYSDATE, 'YYYYMMDD') CREATETIME, --当前表创建时间
TO_CHAR(SYSDATE, 'YYYYMMDD') UPDATETIME, --原记录更新时间
'' F_UNIT, --单位
SEQ_SI_STK_20023.NEXTVAL SYNTIMESTAMP, --内部使用
0 F_TYPE,
A.F_YEAR F_TRADEDAY, --交易日
A.F20140_20015 F20182_20023, --年最高价日
D.STARTDAY,
D.ENDDAY,
''
FROM (SELECT A.SCODE, A.F_YEAR, A.TRADEDATE F20140_20015
FROM (SELECT A.SCODE,
A.TRADEDATE,
d.F_YEAR F_YEAR, --
A.HIGHPRICE NDATA
FROM SDC_STOCKQUOTE A,temp d
WHERE A.MKTCODE IN (1, 2)
AND substr(a.tradedate,1,4) = D.F_YEAR
AND A.SCODE = D.SCODE
AND A.HIGHPRICE = D.NDATA)A) A,
SDC_SECURITY B,
(SELECT C.YEAR, MIN(C.TDATE) STARTDAY, MAX(C.TDATE) ENDDAY, ''
FROM SDC_DAYPRO C
GROUP BY C.YEAR) D
WHERE A.SCODE = B.SCODE
AND A.F_YEAR = D.YEAR
AND B.STYPE IN (2, 3)；

（2）执行计划：

由执行假话可看出cpu开销和逻辑读（34446）和物理读（3960）降了很多，执行速度提高了很多。

优化方案二：用存储过程来写

（1）、sql：

SELECT B.SCODE F_SCODE, --股票代码
B.SNAME F_SNAME, --股票简称
B.STYPE F_STYPE, --证券代码
B.MKTCODE F_MKTCODE, --市场代码
TO_CHAR(SYSDATE, 'YYYYMMDD') CREATETIME, --当前表创建时间
TO_CHAR(SYSDATE, 'YYYYMMDD') UPDATETIME, --原记录更新时间
'' F_UNIT, --单位
SEQ_SI_STK_20023.NEXTVAL SYNTIMESTAMP, --内部使用
0 F_TYPE,
A.F_YEAR F_TRADEDAY, --交易日
si_pkg.getTradedate(A.Scode,a.f_year,a.ndata,1) F20182_20023, --年最高价日 --A.F20140_20015 F20182_20023, --年最高价日
D.STARTDAY,
D.ENDDAY,
''
FROM (SELECT A.SCODE,
SUBSTR(A.TRADEDATE, 1, 4) F_YEAR,
MAX(A.HIGHPRICE) NDATA
FROM SDC_STOCKQUOTE A
WHERE SUBSTR(A.TRADEDATE, 1, 4) = TO_CHAR(SYSDATE, 'YYYY')
and A.MKTCODE IN (1, 2)
GROUP BY A.SCODE, SUBSTR(A.TRADEDATE, 1, 4)) A,
SDC_SECURITY B,
(SELECT C.YEAR, MIN(C.TDATE) STARTDAY, MAX(C.TDATE) ENDDAY, ''
FROM SDC_DAYPRO C
GROUP BY C.YEAR) D
WHERE A.SCODE = B.SCODE
AND A.F_YEAR = D.YEAR and b.status=1
AND B.STYPE IN (2, 3);

执行计划：

总结：

比较优化一和优化二：

优化一：优化二：

cup：2528 1705

逻辑读：34446 350035180

物理读3960 519581

发现：虽然优化二的cpu使用率很低，但是物理读和逻辑读都很大，说明一种的with as临时表起到了作用，临时表是把数据放到内存中，减少了物理读和逻辑读，这样大大的减少了开销。优化的时候需要多关注下物理读和逻辑读。存储过程有的时候并不是一个很好的方式。

总结下逻辑读如下：

Oracle性能调优中，逻辑读是个很重要的度量值，它不仅容易收集，而且能够告诉我们许多关于数据库引擎工作量的信息。逻辑读是在执行SQL语句的时候从高速缓存中读取的块数。

逻辑读在Oracle调优中有四个好处：

（1）逻辑读是受制于CPU能力的操作，因而，很好的反映了CPU的使用情况。

（2）逻辑读可能导致物理读，因而，通过减少逻辑读的数量，很可能会降低I/O操作次数。

（3）逻辑读是受制于串行的操作，既然经常要考虑多用户负载的优化，最小化逻辑读将有利于避免扩展性问题。

（4）逻辑读的数量可以通过SQL跟踪文件和动态性能视图在SQL语句以及执行计划级别获得。

我们都知道,数据块是oracle最基本的读写单位,但用户所需要的数据,并不是整个块,而是块中的行,或列.当用户发出SQL语句时,此语句被解析执行完毕，就开始了数据的抓取阶段,在此阶段,服务器进程会先将行所在的数据块从数据文件中读入buffer cache,这个过程叫做物理读.物理读,每读取一个块,就算一次物理读.当块被送进buffer cache后,并不能立即将块传给用户,因为用户所需要的并不是整个块,而是块中的行.从buffer cache的块中读取行的过程,就是逻辑读.为了完成一次逻辑读,服务器进程先要在hash表中查找块所在的buffer cache 链.找到之后,需要在这个链上加一个cache buffer chains 闩,加闩成功之后,就在这个链中寻找指定的块,并在块上加一个pin锁.并释放cache buffer chains闩.然后就可以访问块中的行了.服务器进程不会将块中所有满足条件的行一次取出,而是根据你的抓取命令,每次取一定数量的行.这些行取出之后,会经由PGA传给客户端用户.行一旦从buffer cache中取出,会话要释放掉在块上所加的PIN.本次逻辑读就算结束.如果还要再抓取块中剩余的行,服务器进程要再次申请获得cache bufffer链闩.再次在块上加PIN.这就算是另外一次逻辑读咯.也就是说,服务器进程每申请一次cache buffer链闩,就是一次逻辑读.而每次逻辑读所读取的行的数量,可以在抓取命令中进行设置.
逻辑读和Cache buffer chains闩关系密切，TOM曾有文章提到，进程每申请一次Cache buffer chains闩，就是一次逻辑读。但是，逻辑读并不等同于Cache buffer chains闩，每次逻辑读，在9i中至少需要获得两Cache buffer chains闩。逻辑读是指在Hash表中定位块的这个过程。