weixin_42712131 2019-09-04 19:23 采纳率: 0%
浏览 540

oracle语句CPU过高问题

SELECT count(1)
FROM (SELECT A.TRANDT,
A.ID,
A.BUSI_CHANNEL,
A.NET_WORK,
A.TELLER,
A.LOGIN_NAME,
A.USER_NAME,
A.SWIFTNUMBER,
A.TRANSACTIONCODE,
CAST(CASE
WHEN REGEXP_LIKE(A.TRANAM, '^-?[0-9]+(.[0-9]+)?$') THEN
A.TRANAM
ELSE
''
END AS NUMBER(20, 2)) TRANAM,
NVL(C.ZPZS, 0) MASTERNO,
DECODE(A.SIGNFILEPATH, NULL, 0, 1) MASTERNO2,
NVL(C.FPZS, 0) DEPUTYNO,
NVL(C.FJZS, 0) ATTACHNO,
NVL(C.DZFJZS, 0) ELECATTACHNO,
NVL(C.NOFLZS, 0) NOFLZS,
A.ACCTNO,
A.ACCTNA,
A.TRANTIME,
A.SIGNFILEPATH,
A.ACCOUNT_MARK,
A.PAPERLESS_MARK,
NVL(EPS.PACKAGE_STATE, '-1') RKSTATE,
A.TELLER || '-' || A.USER_NAME TELLERNAME,
CASE
WHEN NVL2(D.BUSI_ID, '1', '2') = '1' THEN
'2'
WHEN NVL2(D.BUSI_ID, '1', '2') = '2' AND
ENC.NEEDMASTERNO -
(NVL(C.ZPZS, 0) + DECODE(A.SIGNFILEPATH, NULL, 0, 1) -
NVL(C.ZFZPZS, 0)) <= 0 AND
ENC.NEEDDEPUTYNO - NVL(C.FPZS, 0) - NVL(C.ZFFPZS, 0) <= 0 AND
ENC.NEEDENCLOSURENO - NVL(C.FJZS, 0) - NVL(C.ZFFJS, 0) <= 0 THEN
'0'
ELSE
'1'
END ERRORFLAG
FROM (select t.TRANDT,
t.ID,
t.BUSI_CHANNEL,
t.NET_WORK,
t.TELLER,
t.LOGIN_NAME,
t.USER_NAME,
t.SWIFTNUMBER,
t.TRANSACTIONCODE,
t.TRANAM,
t.SIGNFILEPATH,
t.ACCTNO,
t.ACCTNA,
t.TRANTIME,
t.ACCOUNT_MARK,
t.PAPERLESS_MARK,
t.LEGALPERSONNUMBER
from table(fun_get_edmp_busi_info_0822('20190728',
'20190815',
'023',
'320223032',
'320223032104')) t) A
INNER JOIN EDMP_SETTLE_ACCOUNTS ESA
ON A.TELLER = ESA.TELLER
AND A.NET_WORK = ESA.Organ
and A.LEGALPERSONNUMBER = '023'
and A.TRANDT = ESA.TRANDT
LEFT JOIN FUN_GET_edmp_trans_cert_num_0822(A.TRANDT, A.SWIFTNUMBER, '023') C
ON A.SWIFTNUMBER = C.LSH
and A.LEGALPERSONNUMBER = frorg_cdoe
and A.TRANDT = C.WORK_DATE
LEFT JOIN (select ID,
TELLER,
NET_WORK,
crt_no,
TRANDT,
NEEDMASTERNO,
NEEDDEPUTYNO,
NEEDENCLOSURENO
from table(FUN_GET_edmp_neccessary_certno_0822('20190728',
'20190815',
'023',
'320223032',
'320223032104'))) ENC
on A.ID = ENC.ID
and A.TELLER = ENC.TELLER
and A.NET_WORK = ENC.NET_WORK
and A.LEGALPERSONNUMBER = enc.crt_no
and A.TRANDT = ENC.TRANDT
LEFT JOIN EDMP_PACKAGE_STATE EPS
on EPS.GROUP_ID = ESA.GROUP_NUM
and EPS.ORGAN = ESA.Organ
AND EPS.TRANDT = ESA.TRANDT
LEFT JOIN EDMP_SETTLE_ACCOUNTS_INFO D
ON A.SWIFTNUMBER = D.SWIFTNUMBER
and A.TELLER = d.teller
and A.NET_WORK = d.org_code
AND A.TRANDT = D.TRANDT
inner join EDMP_ONLINEPZ EO
on A.NET_WORK = EO.ORG_CODE
and A.TRANDT >= TO_CHAR(EO.ONLINE_TIME, 'yyyymmdd')
WHERE 1 = 1
AND A.NET_WORK = '320223032'
AND A.TELLER = '320223032104'
AND NVL(EPS.PACKAGE_STATE, 0) < 2
AND A.LEGALPERSONNUMBER = '023')
WHERE (TRANDT >= '20190728' AND TRANDT <= '20190815')
响应时间较快,但CPU占用非常大,16C,并发30就执行100%
执行计划如下:
图片说明

  • 写回答

2条回答 默认 最新

  • qq_22847605 2019-09-05 09:43
    关注

    建议用程序实现,少用如此复杂的sql语句

    评论

报告相同问题?

悬赏问题

  • ¥100 关于使用MATLAB中copularnd函数的问题
  • ¥20 在虚拟机的pycharm上
  • ¥15 jupyterthemes 设置完毕后没有效果
  • ¥15 matlab图像高斯低通滤波
  • ¥15 针对曲面部件的制孔路径规划,大家有什么思路吗
  • ¥15 钢筋实图交点识别,机器视觉代码
  • ¥15 如何在Linux系统中,但是在window系统上idea里面可以正常运行?(相关搜索:jar包)
  • ¥50 400g qsfp 光模块iphy方案
  • ¥15 两块ADC0804用proteus仿真时,出现异常
  • ¥15 关于风控系统,如何去选择