Home » RDBMS Server » Performance Tuning » Fine Tuning This query
Fine Tuning This query [message #197286] |
Tue, 10 October 2006 21:02 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
bagulia_pilla
Messages: 25 Registered: July 2006
|
Junior Member |
|
|
Please help to fine tune this query.
Thanks in advance
SELECT '1', 'U' AS update_status_ind, 'OLD', hist.transaction_date, alcm.limit_charge_map_id,
alcm.charge_id, alcm.charge_detail_id, sc.sci_las_sec_id,
hist.reference_id AS cms_collateral_id, sci_security_dtl_id, sc.charge_nature,
sc.charge_amount, sc.charge_currency_code, sc.le_charge, sc.le_charge_date,
sc.legal_charge_date, sc.le_juridict, sc.le_juridict_date, sc.le_govnlaw,
sc.le_govnlaw_date,
(CASE
WHEN sc.prior_charge_currency IS NULL
THEN NULL
ELSE sc.prior_charge_amount
END
) prior_charge_amount,
(CASE
WHEN sc.prior_charge_amount IS NULL
THEN NULL
ELSE sc.prior_charge_currency
END
) prior_charge_currency,
sc.security_rank, sc.prior_chargee, sc.prior_charge_type, sc.charge_type,
sc.confirm_charge_date, alsm.cms_lsp_appr_lmts_id, alsm.sci_las_sys_gen_id,
alsm.sci_las_llp_id, alsm.sci_las_lsp_id, alsm.sci_las_le_id, alsm.sci_las_lmt_id,
alsm.sci_las_sec_id, alsm.las_pledgor_id
FROM trans_history hist,
cms_stage_security col,
cms_stage_limit_charge_map slcm,
cms_stage_limit_security_map slsm,
cms_stage_charge_detail sc,
cms_limit_charge_map alcm,
cms_limit_security_map alsm,
cms_charge_detail ac,
(SELECT MAX (HISTORY.transaction_date) transaction_date, HISTORY.transaction_id
FROM trans_history HISTORY,
cms_stage_security col_stage,
(SELECT MAX (h.transaction_date) last_trx_date, h.transaction_id
FROM trans_history h, cms_security c
WHERE h.transaction_date >= TO_DATE (:ctrl1dt, 'dd/mm/yyyy HH24:MI:SS')
AND h.transaction_date < TO_DATE (:ctrl2dt, 'dd/mm/yyyy HH24:MI:SS')
AND c.cms_collateral_id = TO_NUMBER (h.reference_id)
AND c.is_security_perfected = 'N'
AND c.status NOT IN ('DELETED', 'PENDING_DELETE')
AND h.opsdesc NOT IN ('PART_DELETE', 'FULL_DELETE')
AND h.from_state = 'PENDING_UPDATE'
AND h.status = 'ACTIVE'
AND h.transaction_type = 'COL'
GROUP BY h.transaction_id)
LAST_TRX_N
WHERE HISTORY.transaction_id = LAST_TRX_N.transaction_id
AND HISTORY.from_state <> 'ACTIVE'
AND HISTORY.opsdesc NOT IN ('PART_DELETE', 'FULL_DELETE')
AND HISTORY.status = 'ACTIVE'
AND ( (TRUNC (HISTORY.transaction_date) < TRUNC (LAST_TRX_N.last_trx_date)
)
OR ( TRUNC (HISTORY.transaction_date) = TRUNC (LAST_TRX_N.last_trx_date)
AND NOT EXISTS (
SELECT 1
FROM trans_history HISTORY1
WHERE HISTORY1.transaction_id = LAST_TRX_N.transaction_id
AND HISTORY1.transaction_type = 'COL'
AND TRUNC (HISTORY1.transaction_date) < TRUNC (LAST_TRX_N.last_trx_date))
)
)
AND HISTORY.staging_reference_id = col_stage.cms_collateral_id
AND col_stage.is_security_perfected = 'Y'
GROUP BY HISTORY.transaction_id )
PREV_LAST_TRX
WHERE hist.transaction_id = PREV_LAST_TRX.transaction_id
AND hist.transaction_date = PREV_LAST_TRX.transaction_date
AND col.cms_collateral_id = TO_NUMBER (hist.staging_reference_id)
AND col.cms_collateral_id = slcm.cms_collateral_id
AND slsm.charge_id = slcm.charge_id
AND sc.charge_detail_id = slcm.charge_detail_id
AND alsm.cms_lsp_appr_lmts_id = slsm.cms_lsp_appr_lmts_id
AND alcm.cms_lsp_appr_lmts_id = slcm.cms_lsp_appr_lmts_id
AND alsm.cms_collateral_id = TO_NUMBER (hist.reference_id)
AND alsm.charge_id = alsm.charge_id
AND ac.charge_detail_id = alcm.charge_detail_id
AND ac.cms_ref_id = sc.cms_ref_id
AND col.status NOT IN ('DELETED', 'PENDING_DELETE')
AND (ac.status <> 'DELETED' OR ac.status IS NULL)
AND alcm.status <> 'DELETED'
AND slcm.status <> 'DELETED'
AND alsm.update_status_ind <> 'D'
AND slsm.update_status_ind <> 'D'
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
SELECT STATEMENT Optimizer Mode=CHOOSE 1 73
NESTED LOOPS 1 305 73
NESTED LOOPS 1 238 72
NESTED LOOPS 1 211 71
MERGE JOIN CARTESIAN 1 168 69
TABLE ACCESS BY INDEX ROWID CMS_R14_DEV.TRANS_HISTORY 1 35 3
NESTED LOOPS 1 68 53
HASH JOIN 1 33 50
VIEW 1 21 20
SORT GROUP BY 1 98 20
FILTER
NESTED LOOPS 1 98 18
TABLE ACCESS BY INDEX ROWID CMS_R14_DEV.TRANS_HISTORY 1 75 17
INDEX RANGE SCAN CMS_R14_DEV.IDX_TEST1 1K 7
TABLE ACCESS BY INDEX ROWID CMS_R14_DEV.CMS_SECURITY 1 23 1
INDEX UNIQUE SCAN CMS_R14_DEV.SYS_C001217 1 32 K
VIEW 1 12 29
SORT GROUP BY 1 90 29
FILTER
NESTED LOOPS 1 90 28
NESTED LOOPS 1 78 27
VIEW 1 23 20
SORT GROUP BY 1 98 20
FILTER
NESTED LOOPS 1 98 18
TABLE ACCESS BY INDEX ROWID CMS_R14_DEV.TRANS_HISTORY 1 75 17
INDEX RANGE SCAN CMS_R14_DEV.IDX_TEST1 1 K 7
TABLE ACCESS BY INDEX ROWID CMS_R14_DEV.CMS_SECURITY 1 23 1
INDEX UNIQUE SCAN CMS_R14_DEV.SYS_C0012171 32 K
TABLE ACCESS BY INDEX ROWID CMS_R14_DEV.TRANS_HISTORY 1 55 7
INDEX RANGE SCAN CMS_R14_DEV.IDX_TR_HIST_1 25 2
TABLE ACCESS BY INDEX ROWID CMS_R14_DEV.CMS_STAGE_SECURITY 1 12 1
INDEX UNIQUE SCAN CMS_R14_DEV.SYS_C0012388 100
TABLE ACCESS BY INDEX ROWID CMS_R14_DEV.TRANS_HISTORY 1 25 2.50964819384571
BITMAP CONVERSION TO ROWIDS
BITMAP AND
BITMAP INDEX SINGLE VALUE CMS_R14_DEV.IDX_TR_HIST_2
BITMAP CONVERSION FROM ROWIDS
INDEX RANGE SCAN CMS_R14_DEV.IDX_TR_HIST_1 25 3
INDEX RANGE SCAN CMS_R14_DEV.IDX_TEST1 2 2
BUFFER SORT 1 100 66
TABLE ACCESS FULL CMS_R14_DEV.CMS_CHARGE_DETAIL 1 100 16
TABLE ACCESS BY INDEX ROWID CMS_R14_DEV.CMS_LIMIT_CHARGE_MAP 1 43 2
INDEX RANGE SCAN CMS_R14_DEV.IDX_LMT_CHGE_MAP_01 5 1
TABLE ACCESS BY INDEX ROWID CMS_R14_DEV.CMS_SECURITY 1 27 1
INDEX UNIQUE SCAN CMS_R14_DEV.SYS_C0012171 5
TABLE ACCESS BY INDEX ROWID CMS_R14_DEV.CMS_LIMIT_SECURITY_MAP 1 67 1
INDEX UNIQUE SCAN CMS_R14_DEV.SYS_C0012031 20
|
|
|
Re: Fine Tuning This query [message #198489 is a reply to message #197286] |
Tue, 17 October 2006 06:38 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
kimant
Messages: 201 Registered: August 2006 Location: Denmark
|
Senior Member |
|
|
No problem!
Tuning with out any information about indexes etc ![Smile](images/smiley_icons/icon_smile.gif)
Your query can be just fine, we have no way of knowing.
A full table scan can be good. And they can be bad.
Nested loops can be good. And they can be bad.
Unique index can be good. And they can be bad.
Buffer sort.....etc.
I can see that You have
BITMAP CONVERSION TO ROWIDS
These *can* be bad (and they can be good).
But I would check them.
Also, try to get a grip about Your data. Where *should* the datbase start querying, and where is it not a good idea.
Br
Kim Anthonisen
|
|
|
Goto Forum:
Current Time: Thu Sep 12 11:49:27 CDT 2024
|