0 积分	0 好友	14 主题

发消息

[RAC Clusterware] DR$WAITING与全文索引优化

1^#

发表于 2013-1-30 13:51:11 | 查看: 3033| 回复: 0

本帖最后由 fluttersnow 于 2013-1-30 13:57 编辑

OS: redhat 5.5
db: 10.2.0.4

最近awr报告中出现

SQL ordered by Reads
Total Disk Reads: 1,513,903
Captured SQL account for 34.3% of Total
Physical Reads Executions Reads per Exec %Total CPU Time (s) Elapsed Time (s) SQL Id SQL Module SQL Text
231,823 0 15.31 65.23 3528.16 ayjg3k6ubm9v6 DECLARE job BINARY_INTEGER := ...
148,090 1 148,090.00 9.78 164.78 999.65 8vhn6gyp4gmw7 PinyinJpCreate.exe update prd_poi a set (a.chlnam...
96,110 1 96,110.00 6.35 17.52 641.82 1xjwug6sa5g2d DELETE FROM DR$WAITING WHERE W...
77,257 1 77,257.00 5.10 8.20 491.15 97nyvwdg8kx5b select /*+ ORDERED USE_NL(base...
75,126 0 4.96 39.57 3532.17 7nq61nqm3ha1n DECLARE job BINARY_INTEGER := ...
56,125 10,630 5.28 3.71 18.39 2234.81 1j4ufb71sdr61 insert into "HB_POI"."DR$IDX_H...
36,968 262 141.10 2.44 72.07 835.49 fxtrx79ka73zq select /*+NO_EXPAND INDEX(i)*/...
34,231 394,733 0.09 2.26 231.90 1374.75 5huk51g6a7mt2 PinyinJpCreate.exe update prd_poi set chlname=:ch...
20,937 23,697 0.88 1.38 32.78 274.01 77tk8xc8qcz2j insert into "HB_POI"."DR$IDX_H...
16,920 1 16,920.00 1.12 1.45 56.44 9azgp5p7074gk update "HB_POI"."DR$IDX_HB_PAR...
SQL Id SQL Text
04xtrk7uyhknh select obj#, type#, ctime, mtime, stime, status, dataobj#, flags, oid$, spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null
06zgqp7sxwnt8 select /*+ DYNAMIC_SAMPLING(0) INDEX(a) */ count(TOKEN_TEXT), nvl(sum(TOKEN_COUNT), 0) from "HB_POI"."DR$IDX_ROADCROSS_ADDRESS$I" a where TOKEN_TEXT = :token and TOKEN_TYPE = :tktype and TOKEN_FIRST >= 4400566
1dtqqzfujhkmf begin ctxsys.drvxmd.OptSetState(:idx_id, :ixp_id, :ntab, :token, :type); :etime := ctxsys.drvxmd.OptGetTimer; end;
1gu8t96d0bdmu select t.ts#, t.file#, t.block#, nvl(t.bobj#, 0), nvl(t.tab#, 0), t.intcols, nvl(t.clucols, 0), t.audit$, t.flags, t.pctfree$, t.pctused$, t.initrans, t.maxtrans, t.rowcnt, t.blkcnt, t.empcnt, t.avgspc, t.chncnt, t.avgrln, t.analyzetime, t.samplesize, t.cols, t.property, nvl(t.degree, 1), nvl(t.instances, 1), t.avgspc_flb, t.flbcnt, t.kernelcols, nvl(t.trigflag, 0), nvl(t.spare1, 0), nvl(t.spare2, 0), t.spare4, t.spare6, ts.cachedblk, ts.cachehit, ts.logicalread from tab$ t, tab_stats$ ts where t.obj#= :1 and t.obj# = ts.obj# (+)
1j4ufb71sdr61 insert into "HB_POI"."DR$IDX_HB_PARENT_POI_ADDRESS$I" values (:token, :ttype, :first, :last, :count, :data)
1txyx94yjpzpd update tmp_phone_modify set phone_back=:phone_back, normflag='1' where permanentid =:permanentid
1xjwug6sa5g2d DELETE FROM DR$WAITING WHERE WTG_CID = :B2 AND WTG_PID = :B1 AND EXISTS (SELECT 1 FROM DR$PENDING WHERE DR$PENDING.PND_CID = DR$WAITING.WTG_CID AND DR$PENDING.PND_PID = DR$WAITING.WTG_PID AND DR$PENDING.PND_ROWID = DR$WAITING.WTG_ROWID)
Segments by Physical Reads
Total Physical Reads: 1,513,903
Captured Segments account for 38.0% of Total
Owner Tablespace Name Object Name Subobject Name Obj. Type Physical Reads %Total
CTXSYS SYSAUX SYS_IOT_TOP_41153 INDEX 164,806 10.89
CXL_POI CXL_POI PRD_POI TABLE 123,960 8.19
HB_POI HB_POI DR$IDX_HB_PARENT_POI_ADDRESS$I TABLE 75,800 5.01
HB_POI HB_POI DR$IDX_HB_PARENT_POI_ADDRESS$X INDEX 63,508 4.19
CXL_POI CXL_POI XPK_PRD_POI_POIID INDEX 34,379 2.27