Database optimization typically involves the following aspects：
Database architecture tuning(Oracle DataGuard,Oracle RAC,Extend RAC,GDS,MySQL Master-slave,MySQL MGR etc)
Database parameter tuning(include Operating system kernel parameters)
Database SQL tuning(SQL Plan \SQL query rewrite etc)
The following is an example of Oracle SQL optimization. Through optimization, the performance is improved by tens of times.
SELECT NVL(SUM(NVL(GJL.ENTERED_DR, 0) - NVL(GJL.ENTERED_CR, 0)), 0)
FROM GL_JE_HEADERS xx1, GL_JE_LINES xx2, GL_CODE_COMBINATIONS xx3
WHERE xx1.JE_HEADER_ID = xx2.JE_HEADER_ID
AND xx1.CODE_COMBINATION_ID = xx3.CODE_COMBINATION_ID
AND xx1.CODE_COMBINATION_ID = :B6
AND ((:B5 = 'GL' AND xx1.JE_SOURCE NOT IN
('Assets', 'Inventory', 'Payables', 'Receivables')) OR :B5 = 'ALL')
AND xx3.LEDGER_ID = :B4
AND xx2.SEGMENT1 = :B3
AND xx1.CURRENCY_CODE = :B2
AND xx1.PERIOD_NAME = :B1;
after change :
SELECT /*+ leading(xx2) use_nl(xx3) use_nl(xx1) */NVL(SUM(NVL(xx3.ENTERED_DR,
0) - NVL(xx3.ENTERED_CR, 0)), 0)
FROM GL_JE_HEADERS xx1, GL_JE_LINES xx2, GL_CODE_COMBINATIONS xx3
WHERE xx1.JE_HEADER_ID = xx2.JE_HEADER_ID
AND xx1.CODE_COMBINATION_ID = xx3.CODE_COMBINATION_ID
AND x11.CODE_COMBINATION_ID = 598217
AND (('ALL' = 'GL' AND GJH.JE_SOURCE NOT IN
('Assets', 'Inventory', 'Payables', 'Receivables')) OR 'ALL' = 'ALL')
AND xx3.LEDGER_ID = 2021
AND xx2.SEGMENT1 = '1651'
AND xx1.CURRENCY_CODE = 'CNY'
AND xx1.PERIOD_NAME = '2020-04';
By adjusting the execution plan order, Buffer gets is reduced by 50 times！
The execution time of a customer’s stored procedure is shortened from 350s to 30s after rewrite 。
with mmt as
(
SELECT /*+ parallel(mmt,8)*/
202208271800 as k,MMT.*
FROM XXMA_MTL_MATERIAL_TRANSACTIONS MMT
WHERE 1 = 1
AND (xxx.TRANSACTION_SOURCE_TYPE_ID IN (3, 6, 100, 101, 102, 103) OR
xxx.TRANSACTION_TYPE_ID IN (244) OR
xxx.TRANSACTION_TYPE_ID IN
(SELECT MTT.TRANSACTION_TYPE_ID
FROM FND_LOOKUP_VALUES_VL FLV, MTL_TRANSACTION_TYPES MTT
WHERE FLV.LOOKUP_TYPE = 'XXMA_EXP_INV_TRANSACTION_TYPE'
AND FLV.ENABLED_FLAG = 'Y'
AND FLV.MEANING = MTT.TRANSACTION_TYPE_NAME))
AND xxx.DISTRIBUTION_ACCOUNT_ID IS NOT NULL
AND xxx.TRANSACTION_DATE BETWEEN
TRUNC( /*:B1*/to_date('07/01/2022 00:00:00', 'mm/dd/yyyy hh24:mi:ss')) AND
(TRUNC( /*:B6*/to_date('07/31/2022 23:59:59',
'mm/dd/yyyy hh24:mi:ss')) + 0.999988)
AND (xxx.OWNING_TP_TYPE = 2 OR xxx.OWNING_TP_TYPE IS NULL)
AND (xxx.LOGICAL_TRANSACTION = 2 OR xxx.LOGICAL_TRANSACTION IS NULL)
AND EXISTS
(SELECT NULL
FROM XXMA_MTL_SECONDARY_INVENTORIES MSI
WHERE bbb.ORGANIZATION_ID = xxx.ORGANIZATION_ID
AND bbb.SECONDARY_INVENTORY_NAME = xxx.SUBINVENTORY_CODE
AND bbb.ASSET_INVENTORY = 1
AND bbb.SECONDARY_INVENTORY_NAME NOT LIKE '%INTRAN')
AND NOT EXISTS
(SELECT NULL
FROM FND_LOOKUP_VALUES_VL FL, ORG_ORGANIZATION_DEFINITIONS OOD
WHERE FL.LOOKUP_TYPE = 'XXMA_SUBINVENTORY_EXCEPTION'
AND FL.TAG = OOD.ORGANIZATION_CODE
AND FL.LOOKUP_CODE = xxx.SUBINVENTORY_CODE
AND FL.ENABLED_FLAG = 'Y')
AND ROWNUM >= 1
),
xxx2 as
(
select ATTRIBUTE1,XXMA_CST_WIP_EXP_ALLOCATE_PKG.GET_SHOW_FLAG(PN_CCID => NULL,
PV_DEPT_CODE => xxx.ATTRIBUTE1,
PV_BU => NULL,
PV_BU_TYPE => /*:B5*/ 'RESTORE',
PV_REST_TYPE => /*:B4*/ 3,
PV_BU_CODE => /*:B3*/ 'HKBU12400',
PV_BG_KBU => /*:B2*/ 'KBU12400',
PD_DATE => /*:B1*/ to_date('07/01/2022 00:00:00',
'mm/dd/yyyy hh24:mi:ss')) as GET_SHOW_FLAG
from
(
select ATTRIBUTE1 from xxx group by ATTRIBUTE1
)xxx
)
select xxx.* from xxx
left join xxx2 on xxx2.ATTRIBUTE1 = xxx.ATTRIBUTE1
WHERE (EXISTS
(SELECT 'exists'
FROM XXMA_CST_PROCESSING_ORGS
WHERE PROCESSING_ID = /*:B7*/
654121
AND ORGANIZATION_ID = xxx.ORGANIZATION_ID) OR
xxx2.GET_SHOW_FLAG = 'Y')