2007年9月10日随笔档案 - 背着手扇扇子的人

2007年9月10日

有人问这样的sql该怎么实现：
表数据和结构
   CODE NAME    B01    S01    B02    S02
    1          张三       数学    80
    1          张三                            语文    75
    2          王五       数学    70
    2          王五
    3          李四       数学    50
    3          李四                           语文    88

希望查询出如下结果：
   CODE SUM_STR(NAME)    B01    SUM_STR(S01)    B02    SUM_STR(S02)
    1                张三                    数学                80           语文            75
    2                王五                    数学                70
    3                李四                    数学                50           语文             88
这个问题可以采用自定义的聚集函数来实现：

create or replace type strcat_type as object (

cat_string varchar2 ( 4000 ),

static function ODCIAggregateInitialize(cs_ctx In Out strcat_type) return number ,

member function ODCIAggregateIterate(self In Out strcat_type,value in varchar2 ) return

number ,

member function ODCIAggregateMerge(self In Out strcat_type,ctx2 In Out strcat_type)

return number ,

member function ODCIAggregateTerminate(self In Out strcat_type,returnValue Out

varchar2 ,flags in number ) return number

)

------------------------------------

create or replace type body strcat_type is

static function ODCIAggregateInitialize(cs_ctx IN OUT strcat_type) return number

begin

cs_ctx : = strcat_type( null );

return ODCIConst.Success;

end ;

member function ODCIAggregateIterate(self IN OUT strcat_type,

value IN varchar2 )

return number

begin

if self.cat_string is null then

self.cat_string : = value;

end if ;

return ODCIConst.Success;

end ;

member function ODCIAggregateTerminate(self IN Out strcat_type,

returnValue OUT varchar2 ,

flags IN number )

return number

begin

returnValue : = self.cat_string;

return ODCIConst.Success;

end ;

member function ODCIAggregateMerge(self IN OUT strcat_type,

ctx2 IN Out strcat_type)

return number

begin

       if self.cat_string is null then
                   self.cat_string := ctx2.cat_string;
          end if;

return ODCIConst.Success;

end ;

-------------------

CREATE OR REPLACE FUNCTION sum_str(input varchar2 )

RETURN varchar2

PARALLEL_ENABLE AGGREGATE USING strcat_type;

-------最后查询语句：

select code,sum_str(name), sum_str(b01) b01,sum_str(s01) ,sum_str(b02) b02,sum_str(s02)

from javaeye group by code order by code

posted @ 2009-01-05 21:55 kebo 阅读(966) | 评论 (4) | 编辑收藏

openlayers如何利用vector和style作出marker

定义标注的样式，这个决定标注显示的方式，必须定义好
1 $package("com.bct.map");
2 com.bct.map.EncoderMarkerStyle = {
3     'bigEncoder':{
4         graphicWidth:24,
5         graphicHeight : 24,
6         graphicXOffset : -12,
7         graphicYOffset : -24,
8         externalGraphic : "scripts/map/img/channel2.png"
9     },
10     'smallEncoder':{
11         graphicWidth:16,
12         graphicHeight : 16,
13         graphicXOffset : -8,
14         graphicYOffset : -16,
15         externalGraphic : "scripts/map/img/channel.gif"
16     },
17     'selectStyle':{
18         pointerEvents: "visiblePainted",
19         border:"border:25 outset #ff88ff",
20         cursor: "pointer",
21         graphicWidth:24,
22         graphicHeight : 24,
23         graphicXOffset : -12,
24         graphicYOffset : -24,
25         externalGraphic : "scripts/map/img/channel2.png"
26     },
27     styleMap: new OpenLayers.StyleMap({
28                     "select": new OpenLayers.Style({pointRadius: 24})
29     })
30 }

marker层，扩展vector层，通过point和style达到marker的效果

1 $package("com.bct.map");
 2 $import("com.bct.map.EncoderMarkerStyle");
 3 com.bct.map.MarkerVectorLayer = OpenLayers.Class(OpenLayers.Layer.Vector,{
 4 /**
 5 * parameters
 6 * attribute filer对象
 7 */
 8 getFeatureByAttribute :function(attributes){
 9 var feature = null;
10 for(var i=0;i<this.features.length; ++i){
11 var attri = this.features[i].attributes;
12 var find = false;
13 for(var j in attributes){
14 if(attributes[j] == attri[j]){
15 find = true;
16 }
17 }
18 if(find){
19 return this.features[i];
20 }
21 }
22
23 },
24 addEncorderFeature:function(encNode,location){
25 if(encNode&&this.repetitiveCheck(encNode.id)){
26 return;
27 }
28 var attributes = OpenLayers.Util.extend({}, encNode.attributes);
29 var enc_point = new OpenLayers.Geometry.Point(location.lon,location.lat);
30 var enc_Feature = new OpenLayers.Feature.Vector(enc_point,attributes,com.bct.map.EncoderMarkerStyle['smallEncoder']);
31 this.addFeatures([enc_Feature]);
32 if(encNode.attributes['lon']&&encNode.attributes['lat']&&encNode.attributes['lon'].length>0){
33 return;
34 }
35 this.updateChannel(encNode.id,location.lon,location.lat);
36 },
37 addDeptFeature:function(deptNode,location){
38 if(deptNode&&this.repetitiveCheck(deptNode.id)){
39 return;
40 }
41 var attributes = OpenLayers.Util.extend({}, deptNode.attributes);
42 var enc_point = new OpenLayers.Geometry.Point(location.lon,location.lat);
43 var enc_Feature = new OpenLayers.Feature.Vector(enc_point,attributes,com.bct.map.EncoderMarkerStyle['smallEncoder']);
44
45 this.addFeatures([enc_Feature]);
46
47 },
48 repetitiveCheck:function(entity_id){
49 if(this.getFeatureByAttribute({id:entity_id})){
50 return true;
51 }
52 return false;
53 },
54 updateChannel:function(channel_id,lon,lat){
55 Ext.Ajax.request({
56 url: 'deviceVideoEncoder.do?method=updateLonlat&id='+channel_id+"&lon="+lon+"&lat="+lat
57 });
58 },
59 channelMarkerClick:function() {
60 var features = this.selectedFeatures;
61 if(features.length >=0&&features[0]) {
62 feature = features[0];
63 var treeNodeAttribute = feature.attributes;
64 var vedioPopForm = new Ext.FormPanel({
65 frame:true,
66 labelAlign: 'top',
67 bodyStyle:'padding:5px',
68 width: 400,
69 height:200,
70 layout: 'fit',
71 items:[{
72 xtype:'fieldset',
73 title: '摄像头信息',
74 autoHeight:true,
75 autoWidth:true,
76 html:"名称:"+treeNodeAttribute['text']
77 +"通道号:"+treeNodeAttribute['channelNumber']
78 +"设备名称:"+treeNodeAttribute['deviceunitName']
79 +"所属部门:"+treeNodeAttribute['deptName']
80 +"经纬度:"+treeNodeAttribute['lon']+","+treeNodeAttribute['lat']
81 }]
82 });
83 var win = new Ext.Window({
84 width : 420,
85 height: 220,
86 items : vedioPopForm
87 });
88 win.show();
89 }
90 },
91 cartoonFeature :function(feature){
92 this.drawFeature(feature,com.bct.map.EncoderMarkerStyle['bigEncoder']);
93 var runner = new Ext.util.TaskRunner(1000);
94 var task = {
95 run:this.drawFeature,
96 scope:this,
97 args:[feature,com.bct.map.EncoderMarkerStyle['smallEncoder']],
98 interval: 1000
99 }
100 runner.start(task);
101 },
102 removeSelectFeature:function(){
103 var features = this.selectedFeatures;
104 for(var i=features.length-1; i>=0; i--) {
105 feature = features[i];
106 this.updateChannel(feature.attributes['id'],"","");
107 }
108 this.destroyFeatures(this.selectedFeatures);
109 },
110 monitorSelectFeature:function(){
111 var features = this.selectedFeatures;
112 if(features.length >=0&&features[0]) {
113 feature = features[0];
114 var treeNodeAttribute = feature.attributes;
115 var objId="mapAVShow"+treeNodeAttribute['id'];
116 var win = new Ext.Window({
117 width : 420,
118 height: 420,
119 html:"<div id='mapEncoder' width='100%' height='100%'><object width='100%' height='100%' id='"+objId+"' classid='clsid:574B47E8-A366-4AB9-B2EA-57F145CA3780'></object></div>"
120 });
121 win.show();
122 Ext.lib.Ajax.request('GET','channel.do?method=getSiteId&accept=json&id='+treeNodeAttribute['id'],
123 {success: function(o){
124 var encoderObj;
125 encoderObj=Ext.util.JSON.decode(o.responseText);
126 $import("com.bct.monitor.mapAVShow");
127 var avshowObj=document.getElementById(objId);
128 var avshow=new com.bct.monitor.mapAVShow(avshowObj,
129 encoderObj[0].siteId,encoderObj[0].enCoderId,encoderObj[0].diveceUnitTypeId,'');
130 avshow.startVideo();
131 win.on("destroy",function del(){
132 avshow.stopVideo();
133 });
134 }
135 });
136 }
137 }
138 });

posted @ 2008-09-04 14:12 kebo 阅读(3938) | 评论 (1) | 编辑收藏

一次sql调优历程

在项目进入性能测试阶段，终于爆发了sql运行缓慢，系统吞吐量下降，甚至一度出现oracle服务器cpu100%的情况。具体开发和测试人员报告情况，开始介入处理。

具体查找性能缓慢的过程略除。
发现一条sql运行缓慢。通过跟踪发现一下信息
select alias_p2.pendingid, alias_p2.workitemid, alias_p2.operationid, alias_p2.operationkey,

2 alias_p2.title, alias_p2.sendercn, alias_p2.operatedes, alias_p2.pendingstate,

3 alias_p2.parameter, alias_p2.createdate, alias_p2.deptname, alias_p2.completeddate ,

4 alias_p2.openstate , alias_p2.name, alias_p2.processinstanceid, alias_p2.asset

5 from ( select alias_p1.pendingid, alias_p1.workitemid, alias_p1.operationid,

6 alias_p1.operationkey, alias_p1.title, alias_p1.sendercn, alias_p1.operatedes,

7 alias_p1.pendingstate, alias_p1.parameter, alias_p1.createdate, alias_p1.deptname,

8 alias_p1.completeddate , alias_p1.openstate , alias_p1.name, alias_p1.processinstanceid ,

9 alias_p1.asset , rownum rn from(select alias_p.pendingid, alias_p.workitemid, alias_p.operationid,

10 alias_p.operationkey, alias_p.title, alias_p.sendercn, alias_p.operatedes, alias_p.pendingstate,

11 alias_p.parameter, alias_p.createdate, alias_p.deptname, alias_p.completeddate , alias_p.openstate ,

12 pd.name, w.processinstanceid , eam_db.concatassetname( alias_p.operationkey, alias_p.operationid )

13 asset from WF_Pending alias_p, WF_WorkItem w, WF_ProcessDefinition pd, WF_ProcessInstance pi

14 where alias_p.ownerid='qinxue' and alias_p.pendingstate in(0,3,5,7,9,10,11,12)

15 and (alias_p.deptname=' 审控部信息处 ' or alias_p.deptname='' or alias_p.deptname is null)

16 and w.workitemid = alias_p.workitemid and pi.processinstanceid = w.processinstanceid

17 and pi.completeddate is null and pd.processdefinitionid = w.processdefinitionid order by alias_p.createdate desc) alias_p1 where rownum <=10)

alias_p2 where rn>=1;

已选择 10 行。

执行计划

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=10 Card=1 Bytes=2507

)

1 0 VIEW (Cost=10 Card=1 Bytes=2507)

2 1 COUNT (STOPKEY)

3 2 VIEW (Cost=10 Card=1 Bytes=2494)

4 3 SORT (ORDER BY STOPKEY) (Cost=10 Card=1 Bytes=167)

5 4 NESTED LOOPS (Cost=8 Card=1 Bytes=167)

6 5 NESTED LOOPS (Cost=7 Card=1 Bytes=162)

7 6 NESTED LOOPS (Cost=6 Card=1 Bytes=134)

8 7 TABLE ACCESS (FULL) OF 'WF_PENDING' (Cost=5

Card=1 Bytes=111)

9 7 TABLE ACCESS (BY INDEX ROWID) OF 'WF_WORKITE

M' (Cost=1 Card=3 Bytes=69)

10 9 INDEX (UNIQUE SCAN) OF 'SYS_C003694' (UNIQ

UE)

11 6 TABLE ACCESS (BY INDEX ROWID) OF 'WF_PROCESSDE

FINITION' (Cost=1 Card=1 Bytes=28)

12 11 INDEX (UNIQUE SCAN) OF 'SYS_C003684' (UNIQUE

)

13 5 TABLE ACCESS (BY INDEX ROWID) OF 'WF_PROCESSINST

ANCE' (Cost=1 Card=1 Bytes=5)

14 13 INDEX (UNIQUE SCAN) OF 'SYS_C003662' (UNIQUE)

统计信息

----------------------------------------------------------

314 recursive calls

0 db block gets

29433 consistent gets

0 physical reads

0 redo size

2153 bytes sent via SQL*Net to client

372 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

101 sorts (memory)

0 sorts (disk)

10 rows processed

其中一致读达到近3万次，关联调用出现314次。排序数值也非常多，显然第一目标是把这两个数据降下来。
通过进一步的分析。发现出现这些问题的主要原因是调用eam_db.concatassetname( alias_p.operationkey, alias_p.operationid )这个包。
开始考虑直接在sql外层做关联，不用function来实现。利用聚集函数来合并数据。
着手建立：

聚集函数： CREATE OR REPLACE FUNCTION F_ASSETLINK(P_STR VARCHAR2) RETURN VARCHAR2
AGGREGATE USING asset_link;

----------------------
创建type：CREATE OR REPLACE TYPE ASSET_LINK AS OBJECT (
STR VARCHAR2(30000),
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT ASSET_LINK) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT ASSET_LINK, VALUE IN VARCHAR2) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN ASSET_LINK, RETURNVALUE OUT VARCHAR2, FLAGS IN NUMBER) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT ASSET_LINK, CTX2 IN ASSET_LINK) RETURN NUMBER
)
------------------------------------------------------

创建type body：CREATE OR REPLACE TYPE BODY ASSET_LINK IS
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT ASSET_LINK) RETURN NUMBER IS
BEGIN
SCTX := ASSET_LINK(NULL);
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT ASSET_LINK, VALUE IN VARCHAR2) RETURN NUMBER IS
BEGIN
SELF.STR := SELF.STR ||','|| VALUE;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN ASSET_LINK, RETURNVALUE OUT VARCHAR2, FLAGS IN NUMBER) RETURN NUMBER IS
BEGIN
RETURNVALUE := SELF.STR;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT ASSET_LINK, CTX2 IN ASSET_LINK) RETURN NUMBER IS
BEGIN
NULL;
RETURN ODCICONST.SUCCESS;
END;
END;
调整sql如下：
select alias_p.pendingid, alias_p.workitemid, alias_p.operationid,
   alias_p.operationkey, alias_p.title, alias_p.sendercn, alias_p.operatedes, alias_p.pendingstate,
   alias_p.parameter, alias_p.createdate, alias_p.deptname, alias_p.completeddate , alias_p.openstate ,
   pd.name, w.processinstanceid
   --,T.ASSETCLASS3 ASSET
   ,f_assetlink(d3.typename) ASSET
   --,eam_db.concatassetname( alias_p.operationkey, alias_p.operationid ) asset
   from WF_Pending alias_p, WF_WorkItem w,
   WF_ProcessDefinition pd, WF_ProcessInstance pi
   , tb_asset_dizhiyihao T,dic_app_wfconfig wfc,dic_app_assettype3 d3
   where alias_p.ownerid='qinxue'
   and alias_p.pendingstate in(0,3,5,7,9,10,11,12)
   and (alias_p.deptname='审控部信息处' or alias_p.deptname='' or alias_p.deptname is null)
   and w.workitemid = alias_p.workitemid
   and pi.processinstanceid = w.processinstanceid
   and pi.completeddate is null
   and pd.processdefinitionid = w.processdefinitionid
   AND    t.pk_businessid = alias_p.operationid
          and alias_p.operationkey = wfc.memo_1
          and wfc.wfconfig_code = t.wfconfig_code
   and t.assetclass3 = d3.assettype3_id
   group by alias_p.pendingid, alias_p.workitemid, alias_p.operationid,
   alias_p.operationkey, alias_p.title, alias_p.sendercn, alias_p.operatedes, alias_p.pendingstate,
   alias_p.parameter, alias_p.createdate, alias_p.deptname, alias_p.completeddate , alias_p.openstate ,
   pd.name, w.processinstanceid
   order by alias_p.createdate desc
得到统计数据如下：
C:\Documents and Settings\ibm>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on 星期一 9月 10 19:27:33 2007

SQL> conn jic/jic@name
已连接。
SQL> set autotrace traceonly
SQL> select alias_p.pendingid, alias_p.workitemid, alias_p.operationid,
2     alias_p.operationkey, alias_p.title, alias_p.sendercn, alias_p.operatedes, alias_p.pendingstate,
3     alias_p.parameter, alias_p.createdate, alias_p.deptname, alias_p.completeddate , alias_p.openstate ,
4     pd.name, w.processinstanceid
5     --,T.ASSETCLASS3 ASSET
6     ,f_assetlink(d3.typename) ASSET
7     --,eam_db.concatassetname( alias_p.operationkey, alias_p.operationid ) asset
8     from WF_Pending alias_p, WF_WorkItem w,
9     WF_ProcessDefinition pd, WF_ProcessInstance pi
10     , tb_asset_dizhiyihao T,dic_app_wfconfig wfc,dic_app_assettype3 d3
11     where alias_p.ownerid='qinxue'
12     and alias_p.pendingstate in(0,3,5,7,9,10,11,12)
13     and (alias_p.deptname='审控部信息处' or alias_p.deptname='' or alias_p.deptname is null)
14     and w.workitemid = alias_p.workitemid
15     and pi.processinstanceid = w.processinstanceid
16     and pi.completeddate is null
17     and pd.processdefinitionid = w.processdefinitionid
18     AND    t.pk_businessid = alias_p.operationid
19            and alias_p.operationkey = wfc.memo_1
20            and wfc.wfconfig_code = t.wfconfig_code
21     and t.assetclass3 = d3.assettype3_id
22     group by alias_p.pendingid, alias_p.workitemid, alias_p.operationid,
23     alias_p.operationkey, alias_p.title, alias_p.sendercn, alias_p.operatedes, alias_p.pendingstate,
24     alias_p.parameter, alias_p.createdate, alias_p.deptname, alias_p.completeddate , alias_p.openstate ,
25     pd.name, w.processinstanceid
26     order by alias_p.createdate desc;

已选择30行。

执行计划
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=19 Card=1 Bytes=205)
   1    0   SORT (GROUP BY) (Cost=19 Card=1 Bytes=205)
   2    1     NESTED LOOPS (Cost=17 Card=1 Bytes=205)
   3    2       HASH JOIN (Cost=16 Card=1 Bytes=191)
   4    3         HASH JOIN (Cost=11 Card=1 Bytes=183)
   5    4           NESTED LOOPS (Cost=8 Card=1 Bytes=167)
   6    5             NESTED LOOPS (Cost=7 Card=1 Bytes=139)
   7    6               NESTED LOOPS (Cost=6 Card=1 Bytes=134)
   8    7                 TABLE ACCESS (FULL) OF 'WF_PENDING' (Cost=5
          Card=1 Bytes=111)

9 7 TABLE ACCESS (BY INDEX ROWID) OF 'WF_WORKITE
M' (Cost=1 Card=1 Bytes=23)

10 9 INDEX (UNIQUE SCAN) OF 'SYS_C004347' (UNIQ
UE)

11 6 TABLE ACCESS (BY INDEX ROWID) OF 'WF_PROCESSIN
STANCE' (Cost=1 Card=1 Bytes=5)

12 11 INDEX (UNIQUE SCAN) OF 'SYS_C004334' (UNIQUE
)

13 5 TABLE ACCESS (BY INDEX ROWID) OF 'WF_PROCESSDEFI
NITION' (Cost=1 Card=1 Bytes=28)

14   13               INDEX (UNIQUE SCAN) OF 'SYS_C004329' (UNIQUE)
15    4           TABLE ACCESS (FULL) OF 'DIC_APP_WFCONFIG' (Cost=2
          Card=24 Bytes=384)

16 3 TABLE ACCESS (FULL) OF 'TB_ASSET_DIZHIYIHAO' (Cost=4
Card=310 Bytes=2480)

17 2 TABLE ACCESS (BY INDEX ROWID) OF 'DIC_APP_ASSETTYPE3'
(Cost=1 Card=1 Bytes=14)

18 17 INDEX (UNIQUE SCAN) OF 'PK_DIC_APP_ASSETTYPE3' (UNIQ
UE)

统计信息
----------------------------------------------------------
          6 recursive calls
          0 db block gets
        847 consistent gets
          0 physical reads
          0 redo size
       4102 bytes sent via SQL*Net to client
        383 bytes received via SQL*Net from client
          3 SQL*Net roundtrips to/from client
          1 sorts (memory)
          0 sorts (disk)

其中排序由101变为1次
一致读降为847。下降非常客观
关联调用仅有6次。
此sql性能优化非常可观。至此优化结束：)

posted @ 2007-09-10 19:35 kebo 阅读(461) | 评论 (0) | 编辑收藏

常用链接

留言簿(1)

随笔分类

随笔档案

文章档案

相册

收藏夹

朋友

搜索

积分与排名

最新评论

阅读排行榜

评论排行榜