SELECT P935.SEQUENCEID,
null FA_SEQUENCEID,
P935.ORDERID,
P935.ORGORDERID,
P935.PRODUCTNAME,
P935.PRODUCTNUM,
P935.ORDERTIME,
P935.LASTUPDATETIME,
P935.ORDERSTATUS,
P935.MEMO,
935 orderCode,
P935.PAYERACCTCODE,
P935.PAYERACCTTYPE,
P935.PAYEEACCTCODE PLATACCTCODE,
P935.PAYEEACCTTYPE PLATACCTTYPE,
P936.PAYEEACCTCODE,
P936.PAYEEACCTTYPE,
EXT935.PAYER_DISPLAYNAME,
EXT935.PAYER_NAME,
EXT935.PAYER_IDC,
EXT935.PAYER_MEMBERTYPE,
EXT936.PAYER_DISPLAYNAME PLAT_DISPLAYNAME,
EXT936.SUBMITNAME PLAT_NAME,
EXT936.PAYER_IDC PLAT_IDC,
EXT936.PAYER_MEMBERTYPE PLAT_MEMBERTYPE,
EXT936.PAYEE_DISPLAYNAME,
EXT936.PAYEE_NAME,
EXT936.PAYEE_IDC,
EXT936.PAYEE_MEMBERTYPE,
P935.PAYEEDISPLAYNAME WEBSITENAME,
CASE
WHEN (SELECT count(*)
FROM PAYMENTORDER P936
WHERE P936.Ordercode = 936
and P936.Orderstatus = 0
AND P936.Relatedsequenceid = P935.SEQUENCEID) > 0 THEN
0
ELSE
1
END AS SHARINGRESULT,
CASE D935.Dealcode
WHEN 210 then
14
else
D935.DEALTYPE
end PAYMETHOD,
D935.DEALAMOUNT,
G935.EXT1,
G935.Ext2,
G935.PAYERCONTACTTYPE,
G935.PAYERCONTACT,
NVL(D935.PAYEEFEE, 0) PAYEEFEE,
NVL(D935.PAYERFEE, 0) PAYERFEE,
nvl(MS936.PAYEEFEE, 0) PLATFORMFEE,
P935.VERSION
FROM PAYMENTORDER P935,
PAYMENTORDER P936,
DEAL D935,
GATEWAYORDER
G935,
MSGATEWAYSHARINGORDER MS936,
PAYMENTORDEREXT EXT935,
PAYMENTORDEREXT EXT936
WHERE P936.ORDERCODE = 936
AND P935.ORDERCODE = 935
AND P936.RELATEDSEQUENCEID = to_char(P935.SEQUENCEID)
AND P935.SEQUENCEID = G935.SEQUENCEID(+)
AND P935.SEQUENCEID = D935.ORDERSEQID(+)
AND P935.SEQUENCEID = EXT935.ORDERSEQID(+)
AND P936.SEQUENCEID = EXT936.ORDERSEQID(+)
AND P936.SEQUENCEID = MS936.SEQUENCEID(+)
AND MS936.SHARINGTYPE = 1
AND P935.SEQUENCEID = :1
UNION
SELECT P938.SEQUENCEID,
P935.SEQUENCEID FA_SEQUENCEID,
P938.ORDERID,
P938.ORGORDERID,
P935.PRODUCTNAME,
P935.PRODUCTNUM,
P938.ORDERTIME,
P938.LASTUPDATETIME,
P938.ORDERSTATUS,
P938.MEMO,
938 orderCode,
P938.PAYERACCTCODE,
P938.PAYERACCTTYPE,
P938.PAYEEACCTCODE PLATACCTCODE,
P938.PAYEEACCTTYPE PLATACCTTYPE,
P938.PAYEEACCTCODE,
P938.PAYEEACCTTYPE,
EXT938.PAYER_DISPLAYNAME,
EXT938.PAYER_NAME,
EXT938.PAYER_IDC,
EXT938.PAYER_MEMBERTYPE,
EXT938.PAYEE_DISPLAYNAME PLAT_DISPLAYNAME,
EXT938.SUBMITNAME PLAT_NAME,
EXT938.PAYEE_IDC PLAT_IDC,
EXT938.PAYEE_MEMBERTYPE PLAT_MEMBERTYPE,
EXT938.PAYEE_DISPLAYNAME,
EXT938.PAYEE_NAME,
EXT938.PAYEE_IDC,
EXT938.PAYEE_MEMBERTYPE,
P935.PAYEEDISPLAYNAME WEBSITENAME,
null SHARINGRESULT,
D938.DEALTYPE PAYMETHOD,
D938.DEALAMOUNT,
G935.EXT1,
G935.Ext2,
G935.PAYERCONTACTTYPE,
G935.PAYERCONTACT,
NVL(D938.PAYEEFEE, 0) PAYEEFEE,
NVL(D938.PAYERFEE, 0) PAYERFEE,
0 PLATFORMFEE,
P935.VERSION
FROM PAYMENTORDER P935,
PAYMENTORDER P938,
DEAL D938,
GATEWAYORDER G935,
PAYMENTORDEREXT EXT938
WHERE P935.ORDERCODE = 935
AND P938.ORDERCODE = 938
AND P938.RELATEDSEQUENCEID = to_char(P935.SEQUENCEID)
AND P935.SEQUENCEID = G935.SEQUENCEID(+)
AND P938.SEQUENCEID = D938.ORDERSEQID(+)
AND P938.SEQUENCEID = EXT938.ORDERSEQID(+)
AND P935.SEQUE
生产上数据库大量的latchfree导致的CPU资源耗尽的问题的解决(二)
善性能。注意v$sql中BUFFER_GETS/EXECUTIONS大的语句。 2.Hot block 当多个会话重复访问一个或多个由同一个子cache buffers chains锁存器保护的块时，热块就会产生。当多个会话争用cache buffers chains子锁存器时，就会出现这个等待事件。有时就算调优了SQL，但多个会话同时执行此SQL，那怕只是扫描特定少数块，也是也会出现HOT BLOCK的。