|
|
以下是从数据库中抓取的部分语句：
RECOMMENDATION 3: SQL Tuning, 2.1% benefit (49 seconds)
ACTION: 对 SQL_ID 为 "6d9gp9xr2da5p" 的 SQL 语句运行 SQL Tuning Advisor。
RELEVANT OBJECT: SQL statement with SQL_ID 6d9gp9xr2da5p and
PLAN_HASH 3220314678
select FGUID from TORGSYSTEM where FGUID IN (SELECT b.FGUID FROM
TORGSYSTEM b, TORGSYSTEM a where a.FGUID IN (SELECT FGUID FROM
TORGSYSTEM WHERE (FGUID in (select FGUID from TORGINDEX where
UPPER(FID) = 'ORGUNIT.FUNCREF' and UPPER(FVALUE) =
'\YWKJ_GY\SW.FUNC'))) AND b.FGUID IN (SELECT FGUID FROM TORGSYSTEM
WHERE ( (UPPER(FFILENAME) LIKE '%.PSM') )) AND ((( (b.FPATH LIKE
a.FPATH || a.FID || a.FKIND || '\%') OR ((a.FKIND = '.PTN') AND
((b.FID = a.FID AND b.FKIND = '.PTM') OR (b.FPATH || b.FID || b.FKIND
LIKE '%\' || a.FID || '.PTM' || '\%'))) ) OR ( a.FPATH || a.FID ||
a.FKIND = b.FPATH || b.FID || b.FKIND )))) and TO_CHAR(FDELETELEVEL)
= 0
RATIONALE: SQL_ID 为 "6d9gp9xr2da5p" 的 SQL 语句执行了 16 次, 每次执行平均用时 3.1 秒。
以下为新闻公告部分的信息：不知道能修改不？
RECOMMENDATION 1: Application Analysis, 3.3% benefit (77 seconds)
ACTION: 研究应用程序逻辑, 探讨使用绑定变量来代替文字的可能性。
ACTION: 另外, 也可以将参数 "cursor_sharing" 设置为 "force"。
RATIONALE: 至少发现 96 条带有 PLAN_HASH_VALUE 145661717 的 SQL 语句正在使用字面值。请参阅
SQL_ID 为 "c68a7qw8kx39c" 的 SQL 语句示例。
RELEVANT OBJECT: SQL statement with SQL_ID c68a7qw8kx39c and
PLAN_HASH 145661717
SELECT OA_NEWSTABLE.FID, OA_NEWSTABLE.FINFOID, OA_NEWSTABLE.FTOPIC,
OA_NEWSTABLE.FKEYWORDS, OA_NEWSTABLE.FTYPE, OA_NEWSTABLE.FCLASSIFY,
OA_NEWSTABLE.FCHANNEL, OA_NEWSTABLE.FEARA, OA_NEWSTABLE.FIMPORTANCE,
OA_NEWSTABLE.FLEAVEL, OA_NEWSTABLE.FSTARTTIME, OA_NEWSTABLE.FENDTIME,
OA_NEWSTABLE.FSTATE, OA_NEWSTABLE.FPRESS, OA_NEWSTABLE.FMEMO,
OA_NEWSTABLE.FZDPERSON, OA_NEWSTABLE.FZDDATE, OA_NEWSTABLE.FDEPT,
OA_NEWSTABLE.FPOSITION, OA_NEWSTABLE.FDEPTURL, OA_NEWSTABLE.FAPPLYPS,
OA_NEWSTABLE.FAPPLYDEPT, OA_NEWSTABLE.FAPPLEDATE, OA_NEWSTABLE.FTOP,
OA_NEWSTABLE.FTOPTIME
FROM OA_NEWSTABLE
WHERE (OA_NEWSTABLE.FCLASSIFY = '0' AND OA_NEWSTABLE.FSTATE = '4' and
OA_NEWSTABLE.FSTARTTIME <= :1 and OA_NEWSTABLE.FENDTIME >= :2 ) AND
((FID IN (SELECT FINFOID FROM OA_NEWSRANGE WHERE FRANGEURL IN
('ORG:\ROOT\RZGJT.OGN\WYGS.OGN\WY010BGS.DPT\GW0030MS.PTM\WY_WY.PSM' ,
'ORG:\ROOT\RZGJT.OGN\WYGS.OGN\WY010BGS.DPT\GW0030MS.PTM' ,
'ORG:\ROOT\RZGJT.OGN\WYGS.OGN\WY010BGS.DPT' ,
'ORG:\ROOT\RZGJT.OGN\WYGS.OGN' , 'ORG:\ROOT\RZGJT.OGN'))))
ORDER BY OA_NEWSTABLE.FTOP DESC, OA_NEWSTABLE.FTOPTIME DESC,
OA_NEWSTABLE.FZDDATE DESC
RATIONALE: 至少发现 96 条带有 PLAN_HASH_VALUE 145661717 的 SQL 语句正在使用字面值。请参阅
SQL_ID 为 "7arv62jrys6cs" 的 SQL 语句示例。
RELEVANT OBJECT: SQL statement with SQL_ID 7arv62jrys6cs and
PLAN_HASH 145661717
SELECT OA_NEWSTABLE.FID, OA_NEWSTABLE.FINFOID, OA_NEWSTABLE.FTOPIC,
OA_NEWSTABLE.FKEYWORDS, OA_NEWSTABLE.FTYPE, OA_NEWSTABLE.FCLASSIFY,
OA_NEWSTABLE.FCHANNEL, OA_NEWSTABLE.FEARA, OA_NEWSTABLE.FIMPORTANCE,
OA_NEWSTABLE.FLEAVEL, OA_NEWSTABLE.FSTARTTIME, OA_NEWSTABLE.FENDTIME,
OA_NEWSTABLE.FSTATE, OA_NEWSTABLE.FPRESS, OA_NEWSTABLE.FMEMO,
OA_NEWSTABLE.FZDPERSON, OA_NEWSTABLE.FZDDATE, OA_NEWSTABLE.FDEPT,
OA_NEWSTABLE.FPOSITION, OA_NEWSTABLE.FDEPTURL, OA_NEWSTABLE.FAPPLYPS,
OA_NEWSTABLE.FAPPLYDEPT, OA_NEWSTABLE.FAPPLEDATE, OA_NEWSTABLE.FTOP,
OA_NEWSTABLE.FTOPTIME
FROM OA_NEWSTABLE
WHERE (OA_NEWSTABLE.FCLASSIFY = '0' AND OA_NEWSTABLE.FSTATE = '4' and
OA_NEWSTABLE.FSTARTTIME <= :1 and OA_NEWSTABLE.FENDTIME >= :2 ) AND
((FID IN (SELECT FINFOID FROM OA_NEWSRANGE WHERE FRANGEURL IN
('ORG:\ROOT\RZGJT.OGN\GFYGS.OGN\MTBM050JSB.DPT\BGS.PTM\MT_ZHY.PSM' ,
'ORG:\ROOT\RZGJT.OGN\GFYGS.OGN\MTBM050JSB.DPT\BGS.PTM' ,
'ORG:\ROOT\RZGJT.OGN\GFYGS.OGN\MTBM050JSB.DPT' ,
'ORG:\ROOT\RZGJT.OGN\GFYGS.OGN' , 'ORG:\ROOT\RZGJT.OGN'))))
ORDER BY OA_NEWSTABLE.FTOP DESC, OA_NEWSTABLE.FTOPTIME DESC,
OA_NEWSTABLE.FZDDATE DESC
RATIONALE: 至少发现 7 条带有 PLAN_HASH_VALUE 539813373 的 SQL 语句正在使用字面值。请参阅
SQL_ID 为 "d62qtvwvvc4yr" 的 SQL 语句示例。
RELEVANT OBJECT: SQL statement with SQL_ID d62qtvwvvc4yr and
PLAN_HASH 539813373
SELECT * FROM TTASK WHERE TTASK.FGUID IN(SELECT
TTASKMESSAGE.FTASKGUID FROM TTASKMESSAGE WHERE (TTASKMESSAGE.FSTATE
IN('tmsSend', 'tmsReceived', 'tmsProcessing')) AND
(TTASKMESSAGE.FRORGURL
IN('\ROOT\RZGJT.OGN\DSGWYXGS.OGN\SGS0020CWB.DPT\GW0110GS.PTM\SGS_ZY.P
SM') OR (TTASKMESSAGE.FRPERSON = 'SGS_ZY'))) AND (TTASK.FWARNINGTIME
is not null or TTASK.FLIMITTIME < :1) ORDER BY TTASK.FWARNINGTIME
RATIONALE: 至少发现 7 条带有 PLAN_HASH_VALUE 539813373 的 SQL 语句正在使用字面值。请参阅
SQL_ID 为 "gnjczxvy559ra" 的 SQL 语句示例。
RELEVANT OBJECT: SQL statement with SQL_ID gnjczxvy559ra and
PLAN_HASH 539813373
SELECT * FROM TTASK WHERE TTASK.FGUID IN(SELECT
TTASKMESSAGE.FTASKGUID FROM TTASKMESSAGE WHERE (TTASKMESSAGE.FSTATE
IN('tmsSend', 'tmsReceived', 'tmsProcessing')) AND
(TTASKMESSAGE.FRORGURL
IN('\ROOT\RZGJT.OGN\DSGWYXGS.OGN\SGS0080SCB.DPT\GW0000BZ.PTM\SGS_MSP.
PSM') OR (TTASKMESSAGE.FRPERSON = 'SGS_MSP'))) AND
(TTASK.FWARNINGTIME is not null or TTASK.FLIMITTIME < :1) ORDER BY
TTASK.FWARNINGTIME
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: 对 SQL 语句的硬语法分析消耗了大量数据库时间。 (3.5% impact [80 seconds]) |
|
发表于 2009-11-5 14:13:43
楼主