数据库版本:10.2.0.4
同事反应下面的查询执行非常慢:
select GETNEXTPRODUCTPICURL(p.id, p.brandid) as nextproductpicurl,
GetProductURLNew(p.id, 8) as productpriceurl,
GetProductURLNew(p.id, 9) as productparamurl,
GetProductURLNew(p.id, 10) as productpicurl,
GetProductURLNew(p.id, 12) as productlaburl,
GetProductURLNew(p.id, 13) as productcommenturl,
GetProductURLNew(p.id, 14) as productdownloadurl,
p.productname,
p.brandname,
p.brandid,
GetProductURLNew(p.id, 0) as producturl,
p.refprice as refprice,
v.catalogurlcode as catalogurlcode,
v.brandurlcode as brandurlcode,
p.catalogname,
p.catalogid,
getProductPicUrlNew(pp.id, 1) as picpageurl,
pp.id picid,
pp.smallimage picsmallimage,
pp.largeimage piclargeimage,
pt.name name,
p.image4 image4,
pp.description picdescription,
pp.type pictype,
pp.clicknum picclicknum,
pp.height picheight,
pp.width picwidth
from product p,
productpic pp,
catalogrelateproduct cp,
productpic_type pt,
view_aux_cms1 v
where pp.productid = p.id
and p.id = 310501
and p.id = cp.productid
and cp.catalogid = v.brandid
and pp.type = pt.id
order by pp.priority desc;
我执行了一次,这里返回441行数据,cpu时间:61.76S,逻辑IO:752000
按这样的逻辑IO,却消耗了这么大的cpu时间,明显是偏大的,应该还有其它操作大量的消耗了CPU,而正是这样的CPU消耗导致了cpu时间偏长,导致了执行时间偏长
这里有3个函数,其中一个函数引起了我的注意:
CREATE OR REPLACE FUNCTION GETNEXTPRODUCTPICURL (vProductID IN NUMBER,vBrandID IN NUMBER) RETURN varchar2
IS
vUrl varchar(255);
BEGIN
select 'http://xxx.com/picture/'||trunc(nvl(next_pid,first_pid)/1000)||'/'||nvl(next_pid,first_pid)||'/'||nvl(next_ppid,first_ppid)||'.shtml'
into vUrl
from
(
select pid,
ppid,
lead(pid)over(order by product_priority desc,pid desc) next_pid,
lead(ppid)over(order by product_priority desc,pid desc) next_ppid,
first_value(pid)over(order by product_priority desc,pid desc) first_pid,
first_value(ppid)over(order by product_priority desc,pid desc) first_ppid
from
(
select pid,product_priority,ppid
from
(
select p.id pid,p.priority product_priority,
row_number()over(partition by p.id order by pp.priority desc,pp.id desc) r,
pp.id ppid
from product p,productpic pp
where p.brandid=vBrandID and p.id=pp.productid
)
where r=1
)
)
where pid=vProductID;
RETURN vUrl;
exception
when others then
return null;
END GETNEXTPRODUCTPICURL;
其实这个函数的业务逻辑是,求一个产品同品牌的下一个有图片的产品的图片url.
这里使用了几个分析函数,这里分析函数导致的排序操作肯定是消耗CPU的。
其实这个函数已经返回了某个品牌下的所有产品的下一个有图片的产品的图片url,只是最后又限制了一个产品id,才得到了特定的产品的信息.
这显然是浪费了一些资源,但因为这里要求返回的产品必须有图片,而且产品排序后最后一个产品的下一个实际上应该是第一个产品,所以改起来还是稍微麻烦些.
其实这个函数最大的问题是:明显这个函数是确定性函数,但这里却没有告诉oracle是确定性函数.
而且上面执行缓慢的sql语句,其实是查询了某个产品的所有图片信息,实际上只是显示了一个产品信息,每一行又显示了这个产品的下一个产品的图片信息,所以虽然返回了441行数据,但GETNEXTPRODUCTPICURL这个函数应该只调用一次就可以了,所以直接改写函数如下:
CREATE OR REPLACE FUNCTION GETNEXTPRODUCTPICURL (vProductID IN NUMBER,vBrandID IN NUMBER) RETURN varchar2 deterministic
加上deterministic,改为确定性函数
改为确定性函数之后,再次执行上面的sql语句,cpu时间:0.23S,逻辑IO:4862
性能上已经有了明显的改善,因为确定性函数使得可以进行函数调用次数上的优化,所以原来非确定性函数的时候,这个函数被调用了返回行数(441)次,而函数里分析函数是消耗cpu的,所以导致了cpu时间偏长,而改为确定性函数之后,实际上这个函数只是调用了一次(对于这个例子而言,是调用的GETNEXTPRODUCTPICURL(310501,294),它的逻辑IO:1709,cpu时间:0.24s),所以执行时间上有了明显的改善.
实际上从10g开始,确定性函数在调用次数上的优化才得以实现,在10g之前,这个特性其实并没有实现,这时候可以使用标量子查询来实现调用次数上的优化
select (select GETNEXTPRODUCTPICURL_zsj(p.id, p.brandid) from dual) as nextproductpicurl,
GetProductURLNew(p.id, 8) as productpriceurl,
GetProductURLNew(p.id, 9) as productparamurl,
GetProductURLNew(p.id, 10) as productpicurl,
GetProductURLNew(p.id, 12) as productlaburl,
GetProductURLNew(p.id, 13) as productcommenturl,
GetProductURLNew(p.id, 14) as productdownloadurl,
p.productname,
p.brandname,
p.brandid,
GetProductURLNew(p.id, 0) as producturl,
p.refprice as refprice,
v.catalogurlcode as catalogurlcode,
v.brandurlcode as brandurlcode,
p.catalogname,
p.catalogid,
getProductPicUrlNew(pp.id, 1) as picpageurl,
pp.id picid,
pp.smallimage picsmallimage,
pp.largeimage piclargeimage,
pt.name name,
p.image4 image4,
pp.description picdescription,
pp.type pictype,
pp.clicknum picclicknum,
pp.height picheight,
pp.width picwidth
from product p,
productpic pp,
catalogrelateproduct cp,
productpic_type pt,
view_aux_cms1 v
where pp.productid = p.id
and p.id = 310501
and p.id = cp.productid
and cp.catalogid = v.brandid
and pp.type = pt.id
order by pp.priority desc;
这里GETNEXTPRODUCTPICURL_zsj又变回了非确定性函数的实现,但这里使用了标量子查询,实现了同样的效果:cpu时间:0.23S,逻辑IO:4862
***************************************
其实说到这里,有必要谈谈oracle在确定性函数,标量子查询上的cache机制.
在改为确定性函数之后,在这441行数据里,如果这个函数的输入值实际上只有一个值对(比如这里的情形),那么这个函数肯定只调用了一次,但如果是交替出现的2个值对,返回440行数据的话,这个函数却不一定只执行了2次,虽然说极大的概率下可能是执行了2次的,但也可能执行440次的,这个和oracle在保存值对的返回值时使用的hash算法有关,oracle使用pga中的hash table来每个保存值对的结果,如果这两个值对刚好发生了hash冲突,那么每次都会覆盖上一个值对的结果,所以下次调用这个值对的时候还是需要再次执行的.这里使用标量子查询实现的话,存在同样的问题。当然因为并不清楚oracle使用的是怎样的一种hash算法,所以要模拟这种hash冲突是件很困难的事情.不过确实可以看看这种hash冲突以及值的改变带来的冲突的缓解:
数据库版本:10.2.0.4
CREATE TABLE zsj_objs AS SELECT * FROM all_objects;
SELECT Count(DISTINCT OWNER) owner_cnt,Count(1) total_cnt FROM zsj_objs;
OWNER_CNT TOTAL_CNT
---------- ----------
21 51926
这里共21个不同的owner,数据行总数是51926行数据
CREATE OR REPLACE PACKAGE pack_zsj_test
IS
cnt1 NUMBER;
cnt2 NUMBER;
cnt3 NUMBER;
cnt4 NUMBER;
END;
/
CREATE OR REPLACE FUNCTION func_zsj_test1(v_owner VARCHAR2) RETURN NUMBER deterministic
IS
v_cnt NUMBER;
BEGIN
pack_zsj_test.cnt1:=pack_zsj_test.cnt1+1;
SELECT Count(1) INTO v_cnt
FROM zsj_objs
WHERE owner=v_owner;
RETURN v_cnt;
END;
/
CREATE OR REPLACE FUNCTION func_zsj_test2(v_owner VARCHAR2) RETURN NUMBER deterministic
IS
v_cnt NUMBER;
BEGIN
pack_zsj_test.cnt2:=pack_zsj_test.cnt2+1;
SELECT Count(1) INTO v_cnt
FROM zsj_objs
WHERE owner=v_owner;
RETURN v_cnt;
END;
/
CREATE OR REPLACE FUNCTION func_zsj_test3(v_owner VARCHAR2) RETURN NUMBER
IS
v_cnt NUMBER;
BEGIN
pack_zsj_test.cnt3:=pack_zsj_test.cnt3+1;
SELECT Count(1) INTO v_cnt
FROM zsj_objs
WHERE owner=v_owner;
RETURN v_cnt;
END;
/
CREATE OR REPLACE FUNCTION func_zsj_test4(v_owner VARCHAR2) RETURN NUMBER
IS
v_cnt NUMBER;
BEGIN
pack_zsj_test.cnt4:=pack_zsj_test.cnt4+1;
SELECT Count(1) INTO v_cnt
FROM zsj_objs
WHERE owner=v_owner;
RETURN v_cnt;
END;
/
SQL> BEGIN
2 pack_zsj_test.cnt1:=0;
3 pack_zsj_test.cnt2:=0;
4 pack_zsj_test.cnt3:=0;
5 pack_zsj_test.cnt4:=0;
6 END;
7 /
PL/SQL procedure successfully completed
SQL> SELECT Sum(owner_obj_cnt1) CNT1,Sum(owner_obj_cnt2) CNT2,Sum(owner_obj_cnt3) CNT3,Sum(owner_obj_cnt4) CNT4
2 FROM
3 (
4 SELECT func_zsj_test1(owner) owner_obj_cnt1,(SELECT func_zsj_test2(owner) FROM dual) owner_obj_cnt2,
5 func_zsj_test3(owner) owner_obj_cnt3,(SELECT func_zsj_test4(owner) FROM dual) owner_obj_cnt4
6 FROM zsj_objs
7 );
CNT1 CNT2 CNT3 CNT4
---------- ---------- ---------- ----------
944256530 944256530 944256530 944256530
SQL> BEGIN
2 Dbms_Output.put_line('确定性函数一般调用的总次数: '||pack_zsj_test.cnt1);
3 Dbms_Output.put_line('确定性函数标量子查询调用的总次数: '||pack_zsj_test.cnt2);
4 Dbms_Output.put_line('非确定性函数一般调用的总次数: '||pack_zsj_test.cnt3);
5 Dbms_Output.put_line('非确定性函数标量子查询调用的总次数: '||pack_zsj_test.cnt4);
6 END;
7 /
确定性函数一般调用的总次数: 738
确定性函数标量子查询调用的总次数: 56
非确定性函数一般调用的总次数: 51926
非确定性函数标量子查询调用的总次数: 56
PL/SQL procedure successfully completed
多次执行都是这样的结果.
可以看到非确定性函数一般调用的时候,调用次数就是数据行数,也就是说每一行都调用了一次。同时可以看到这里标量子查询调用次数的优化效果是最好的,确定性函数一般调用的效果都不如非确定性函数标量子查询的调用效果好.但调用次数都是多于不同owner的数量21的，应该是和确定性函数,标量子查询CACHE时的HASH冲突有关吧!
SQL> BEGIN
2 pack_zsj_test.cnt1:=0;
3 pack_zsj_test.cnt2:=0;
4 pack_zsj_test.cnt3:=0;
5 pack_zsj_test.cnt4:=0;
6 END;
7 /
PL/SQL procedure successfully completed
SQL> SELECT Sum(owner_obj_cnt1) CNT1,Sum(owner_obj_cnt2) CNT2,Sum(owner_obj_cnt3) CNT3,Sum(owner_obj_cnt4) CNT4,Sum(rn)
2 FROM
3 (
4 SELECT func_zsj_test1(owner) owner_obj_cnt1,(SELECT func_zsj_test2(owner) FROM dual) owner_obj_cnt2,
5 func_zsj_test3(owner) owner_obj_cnt3,(SELECT func_zsj_test4(owner) FROM dual) owner_obj_cnt4,
6 ROWNUM rn
7 FROM(SELECT owner FROM zsj_objs ORDER BY Dbms_Random.Value)
8 );
CNT1 CNT2 CNT3 CNT4 SUM(RN)
---------- ---------- ---------- ---------- ----------
944256530 944256530 944256530 944256530 1348180701
--这里通过随机排序使得取出的owner顺序是不一样的
SQL> BEGIN
2 Dbms_Output.put_line('确定性函数一般调用的总次数: '||pack_zsj_test.cnt1);
3 Dbms_Output.put_line('确定性函数标量子查询调用的总次数: '||pack_zsj_test.cnt2);
4 Dbms_Output.put_line('非确定性函数一般调用的总次数: '||pack_zsj_test.cnt3);
5 Dbms_Output.put_line('非确定性函数标量子查询调用的总次数: '||pack_zsj_test.cnt4);
6 END;
7 /
确定性函数一般调用的总次数: 28
确定性函数标量子查询调用的总次数: 28
非确定性函数一般调用的总次数: 51926
非确定性函数标量子查询调用的总次数: 28
PL/SQL procedure successfully completed
多次执行都是这样的结果,除了非确定性函数的一般调用没变仍然是数据行次数的调用之外,明显其它调用次数比前一次实验少了
其实我是想通过随机的排序模拟出hash冲突更严重的情形来,可没想到这里冲突却得到了缓解,而且多次执行都是这样的结果,不知道为什么.
可以看到这里的hash冲突比上次有所缓解,调用次数28只比不同owner的数量21多了7个而已,那么很可能是行数很少的owner和行数很多的owner发生了冲突,那么修改一下行数很少的owner是否有可能导致hash冲突减少呢?
SQL> select owner,count(1) cnt from zsj_objs group by owner order by cnt desc;
OWNER CNT
------------------------------ ----------
SYS 23014
PUBLIC 20075
PRODUCTUSER 2235
ORDSYS 1721
SYSMAN 1308
MDSYS 906
OLAPSYS 718
SYSTEM 425
XDB 343
CTXSYS 312
WMSYS 302
EXFSYS 278
DMSYS 189
DBSNMP 46
VIEWUSER 12
ORDPLUGINS 10
SCOTT 8
OUTLN 8
SI_INFORMTN_SCHEMA 8
ORACLE_OCM 6
TSMSYS 2
21 rows selected
SQL> update zsj_objs set owner=owner||'2' where owner in('ORACLE_OCM','TSMSYS');
8 rows updated
这里将行数最少的两个owner修改了一下,后面并上一个2,提交后,还是进行上面的代码测试(ORDER BY Dbms_Random.Value的这个).
确定性函数一般调用的总次数: 28
确定性函数标量子查询调用的总次数: 28
非确定性函数一般调用的总次数: 51926
非确定性函数标量子查询调用的总次数: 28
多次测试后都是这样的结果,和修改前没有任何的区别.仔细想想,一个字符串在进行hash运算的时候,可能前几个字符要比后几个字符的影响要大吧,所以把原来加上的2给去掉,同时把行数量少于100的8个owner都加上一个前缀2,最终是这样的数据:
SQL> select owner,count(1) cnt from zsj_objs group by owner order by cnt desc;
OWNER CNT
------------------------------ ----------
SYS 23014
PUBLIC 20075
PRODUCTUSER 2235
ORDSYS 1721
SYSMAN 1308
MDSYS 906
OLAPSYS 718
SYSTEM 425
XDB 343
CTXSYS 312
WMSYS 302
EXFSYS 278
DMSYS 189
2DBSNMP 46
2VIEWUSER 12
2ORDPLUGINS 10
2SCOTT 8
2OUTLN 8
2SI_INFORMTN_SCHEMA 8
2ORACLE_OCM 6
2TSMSYS 2
21 rows selected
之后还是进行ORDER BY Dbms_Random.Value的这个代码测试,多次测试的结果都是下面这样的:
确定性函数一般调用的总次数: 22
确定性函数标量子查询调用的总次数: 22
非确定性函数一般调用的总次数: 51926
非确定性函数标量子查询调用的总次数: 22
PL/SQL procedure successfully completed
可以看到hash冲突的次数进一步减少了,所以3种形式的调用都只是调用了22次而已了.
当然,那一次的冲突到底是谁造成的,要寻找起来估计是件很困难的事情,但我还是决定碰碰运气.我觉得应该是行数很少的某个owner引入的冲突,那就从'2TSMSYS'这个值改起吧,居然让我给蒙对了:
SQL> update zsj_objs set owner='6TSMSYS' where owner='2TSMSYS';
2 rows updated
后提交,之后还是进行ORDER BY Dbms_Random.Value的这个代码测试,多次测试的结果都是下面这样的:
确定性函数一般调用的总次数: 21
确定性函数标量子查询调用的总次数: 21
非确定性函数一般调用的总次数: 51926
非确定性函数标量子查询调用的总次数: 21
PL/SQL procedure successfully completed
可以看到这里真的没有任何的hash冲突了,3种形式的调用次数都变成了不同owner值的数量21了.
SELECT Sum(owner_obj_cnt1) CNT1,Sum(owner_obj_cnt2) CNT2,Sum(owner_obj_cnt3) CNT3,Sum(owner_obj_cnt4) CNT4
FROM
(
SELECT func_zsj_test1(owner) owner_obj_cnt1,(SELECT func_zsj_test2(owner) FROM dual) owner_obj_cnt2,
func_zsj_test3(owner) owner_obj_cnt3,(SELECT func_zsj_test4(owner) FROM dual) owner_obj_cnt4
FROM zsj_objs
);
这个sql执行后,结果也和上面的完全一样了.
你可以看到owner值的改变,也就是hash函数输入值的改变,改变了hash函数的结果,从而改变了hash冲突的次数,改变了3种形式的调用的次数.
当然,影响hash函数输出,也就是hash冲突大小的除了hash函数的输入值之外,还有就是hash表的大小(一般来说,hash表越大,冲突的几率越小,但对于有些值来说,原来不冲突的,加大hash table之后它们冲突了也是有可能的,所以对于一些特定的输入值集合来说,极少数的情况下,加大hash table大小,会激化它们的冲突,这也是有可能的)。至于这时使用的hash table的大小:8i,9i中确定是256个条目。10g中可以简单的说是1024个条目,但实际上取决于输入,输出和_query_execution_cache_max_size这个参数的设置,实际上是_query_execution_cache_max_size这样大小的内存中可以放置的条目数(hash table size始终是2的n次方个),最多可以达到16384个条目.
可以参见Jonathan Lewis 在他的《Cost-Based Oracle Fundamentals》第九章《Query Transformation》中的相关论述.
http://www.itpub.net/viewthread.php?tid=1051245&extra=page%3D2%26amp%3Bfilter%3Ddigest&page=3 这里对不同的函数调用形式的影响也有讨论,可以看看.
***********************************************************
最后想说一下在sql语句中不必要的调用pl/sql的问题
还是说一开始的这个sql语句,这里多次调用了GetProductURLNew这个函数,这个函数的定义如下:
CREATE OR REPLACE FUNCTION GETPRODUCTURLNEW(vProductID IN NUMBER,vType IN VARCHAR2 DEFAULT 7) RETURN VARCHAR2 deterministic
IS
sURL VARCHAR2(512);
BEGIN
sURL:='http://xxx.com/'||TRUNC(vProductID/1000,0)||'/'||vProductID||'/'
||CASE vType WHEN 8 THEN 'price.shtml'
WHEN 9 THEN 'param.shtml'
WHEN 10 THEN 'pic.shtml'
WHEN 11 THEN 'quote.shtml'
WHEN 12 THEN 'lab.shtml'
WHEN 13 THEN 'comment.shtml'
WHEN 14 THEN 'download.shtml'
END;
RETURN sURL;
END GetProductURLNew;
实际上这个函数没有访问任何的表,只是拼装了一下url而已.
在sql里调用这个函数,相对于把这个拼装的形式直接挪动到sql语句中,确实可重用性更强了,代码也更简洁,更易于维护了,当可能带来性能上的问题:每次调用这个函数都会带来sql引擎和pl/sql引擎的上下文切换的问题,从操作系统的角度来讲,其实也就是上下文切换的问题,而这是存在开销的.当然了,对于这里的代码而言,我也会采用调用函数的形式,因为开销确实不大,而且不使用函数,直接在sql语句中拼装的话,代码看起来确实太冗余了,也太不易于维护了.
但下面的sql语句的性能上的开销还可以忽略吗?
select p.id pid,GETNEXTPRODUCTPICURL(p.id,p.brandid) next_pp_url
from product p
where p.brandid=294 and exists(select 1 from productpic pp where pp.productid=p.id)
order by 1;
我这里实际上是查看某个品牌下所有有图片的产品信息,包括产品ID,下一个有图片的产品的图片url,所以调用了函数GETNEXTPRODUCTPICURL(这里已经改为确定性函数了)
这里返回了487行数据,cpu时间:63s,逻辑IO:83W
但其实呢?我在函数的实现中其实已经把某个品牌下的产品信息都提取出来了,只不过最后过滤了一下,只显示了这个品牌下某个产品的信息而已.
所以这里可以不用调用函数,而直接用函数的实现sql来实现的:
select pid,'http://xxx.com/picture/'||trunc(nvl(next_pid,first_pid)/1000)||'/'||nvl(next_pid,first_pid)||'/'||nvl(next_ppid,first_ppid)||'.shtml' next_pp_url
from
(
select pid,
ppid,
lead(pid)over(order by product_priority desc,pid desc) next_pid,
lead(ppid)over(order by product_priority desc,pid desc) next_ppid,
first_value(pid)over(order by product_priority desc,pid desc) first_pid,
first_value(ppid)over(order by product_priority desc,pid desc) first_ppid
from
(
select pid,product_priority,ppid
from
(
select p.id pid,p.priority product_priority,
row_number()over(partition by p.id order by pp.priority desc,pp.id desc) r,
pp.id ppid
from product p,productpic pp
where p.brandid=294 and p.id=pp.productid
)
where r=1
)
)
order by 1;
返回结果完全相同,但cpu时间:0.2s,逻辑IO:1709
明显,性能上的差异是不能忽视的.这里给出的例子虽然有些极端,但确实说明了一些问题:oracle确实可以对sql进行查询转换,转换为等效的高效实现形式,但对于有些调用,特别是函数调用,很多时候是转换不了的,比如说这里给出的第一个sql,oracle它还没有智能到转换为第二个sql语句去实现的地步.sql里调用pl/sql函数很多时候也不如直接用sql来完成的高效.比如说用一条sql实现,连接的时候可以使用更高效的hash join,而那样调用函数的时候,也许只能使用比较低效的nl连接了.(sql里调用函数的时候,oracle会进行查询转换吗?有时间的话研究一下),所以使用函数还是不使用函数,这是一个问题,至少是一个应该考虑的问题.
扫一扫
456
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
