ELAPSED_TIME的单位

运行查询
SELECT *
FROM   (SELECT a.sql_text,
        a.executions,
        a.elapsed_time
        FROM   v$sqlarea a where parsing_schema_name='IRMADMIN'
        ORDER BY 3 DESC)
WHERE  rownum <= 10;
能够查到library cache中的一些SQL信息：

那么需要记录的是Elapsed_time,单位是微妙(Micro Second 1/1.000.000)

SQL_TEXT        EXECUTIONS                       ELAPSED_TIME
----------     ------------                     ---------------
select * from  ( SELECT TaskID, ElgCmpntID, ANCESTORID, CMPNTID, LFCCLCDID, LFCCLPHSID, NEXTELGLFCCL
DT, CONFLICT, ELIGIBLE, fullpathdepth, Compdefid FROM TS_LIFECYCLEQUERYRESULTS start with CmpntID =
:1 and  TaskID = :2 and FULLPATHDEPTH = 0 connect by PRIOR ElgCmpntID = AncestorID  and  TaskID = :3
 ) anc order by anc.FullPathDepth desc
     25000   2516950186

可以看到这条SQL执行了25000次，每错环境总共有25K条记录需要处理。那么每处理一条记录，都需要执行一个start with connect by这样的树查询.
25000次SQL语句总共消耗了2516.95秒=41.95分钟，占用了整个transaction的50%的时间，那么后期会通过优化进行改进了。

delete from TS_LOCKS where COALESCE(FLPLNCMPNTID, 0)=:1 and COALESCE(FLPLNCMPNTPARTID, 0)=:2
     25000    130841706

SELECT TP_ANCSTRLISTR(:B3 , 0, :B2 , CAST(:B1 AS NUMBER(19))) RO FROM DUAL UNION ALL SELECT TP_ANCST
RLISTR(V1.FLPLNVIEWID, AR1.RVRSPATHLNGTH, AR1.FLPLNCMPNTSRCID, AR1.SRCCMPNTPARTID) FROM (SELECT LEVE
L AS RVRSPATHLNGTH, R.FLPLNCMPNTSRCID, R.FLPLNCMPNTTGTID, R.FLPLNRELDEFID, R.SRCCMPNTPARTID FROM TS_
FLPLNREL R START WITH R.FLPLNCMPNTTGTID = :B2 AND R.FLPLNRELDEFID IN (SELECT RD.FLPLNRELDEFID FROM T
S_FLPLNRELDEF RD JOIN TS_FLPLNVIEW V ON RD.FLPLNVIEWID = V.FLPLNVIEWID WHERE V.FLPLNVIEWID = :B3 OR
:B3 = 0) CONNECT BY R.FLPLNCMPNTTGTID = PRIOR R.FLPLNCMPNTSRCID AND OP_FLPLNTREE.ISINSAMEVIEW(R.FLPL
NRELDEFID, PRIOR R.FLPLNRELDEFID) > 0) AR1 JOIN TS_FLPLNRELDEF RD1 ON AR1.FLPLNRELDEFID = RD1.FLPLNR
ELDEFID JOIN TS_FLPLNVIEW V1 ON RD1.FLPLNVIEWID = V1.FLPLNVIEWID
     50000    100797623

SELECT 1 FROM DUAL WHERE EXISTS (SELECT RD1.FLPLNRELDEFID FROM TS_FLPLNRELDEF RD1 JOIN TS_FLPLNVIEW
V ON RD1.FLPLNVIEWID = V.FLPLNVIEWID JOIN TS_FLPLNRELDEF RD2 ON V.FLPLNVIEWID = RD2.FLPLNVIEWID WHER
E RD1.FLPLNRELDEFID = :B2 AND RD2.FLPLNRELDEFID = :B1 )
    500000     23554870

begin :1 := JAVA_XA.xa_start_new(:2,:3,:4,:5,:6); end;
     25334     14308124

SELECT R.FLPLNCMPNTSRCID, CD.FLPLNCMPNTDEFID FROM TS_FLPLNREL R JOIN TS_FLPLNCMPNT C ON R.FLPLNCMPNT
SRCID = C.FLPLNCMPNTID JOIN TS_FLPLNCMPNTDEF CD ON C.FLPLNCMPNTDEFID = CD.FLPLNCMPNTDEFID WHERE R.FL
PLNCMPNTTGTID = :B2 AND R.FLPLNVIEWID = :B1
    300000     13317488

begin :1 := JAVA_XA.xa_end_new(:2,:3,:4,:5); end;
     25334     12389831

begin :1 := JAVA_XA.xa_commit_new (:2,:3,:4,:5); end;
     25334     11978856

update TS_FLPLNCMPNT set LfCclPhsID=:1, FlPlnCmpntDspDt=:2 where FLPLNCMPNTID=:3
     25000     11159355

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12361284/viewspace-151830/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12361284/viewspace-151830/