oracle语句CPU过高问题

Question

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%
执行计划如下：

Answer 1

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