Hi,
I have been asked to tune an existing query in my application which takes 17 mins to get the data ...Please suggest me how to tune & where to tune in the query
Here is the query
--onnet_A4
SELECT DISTINCT
nvl (exception_count.active_counts, 0) exceptcnt,
nvl (exception_count.file_count, 0) except_file_cnt,
v_ff_fs_billing_file_stats.BILLING_FILE_NM,
v_ff_fs_billing_file_stats.BILLING_FILE_ID,
tape_header_switch_id as source_nm,
DECODE(cpc4000_file.file_nm, NULL,
DECODE(CRP_BILLABLE_MOU, 0,'No Billable MOU',
NULL, 'No Billable MOU',
DECODE(PRICING_PRICED_CDR_MOU, 0, 'No Pricing Info',
NULL, 'No Pricing Info', '4000 file not picked up')),
cpc4000_file.file_nm) file_nm,
START_DT,
START_TM,
END_DT,
END_TM,
to_char( to_date (trunc(START_TM),'sssss'),'hh24:mi:ss') as StartTime,
to_char( to_date (trunc(END_TM),'sssss'),'hh24:mi:ss') as EndTime,
( ( decode( floor(END_DT - START_DT), 0,0,
1,decode( greatest(end_tm, start_tm), end_tm,(floor(END_DT - START_DT)*24*60), 0),
(floor(END_DT - START_DT) -1) * 24 * 60)) +
(to_char ( ( to_date (trunc(END_TM),'sssss') - to_date (trunc(START_TM),'sssss')) + trunc(sysdate), 'hh24') * 60) +
(to_char ( ( to_date (trunc(END_TM),'sssss') - to_date (trunc(START_TM),'sssss')) + trunc(sysdate), 'mi') ) +
(to_char ( ( to_date (trunc(END_TM),'sssss') - to_date (trunc(START_TM),'sssss')) + trunc(sysdate), 'ss') / 60) ) as TimeElapsed,
navis_input_events as navis_input,
navis_billed_events as navis_billed,
navis_marked_malformed_events as navis_malformed,
navis_output_events as navis_output,
CRP_SUSPENSE_CNT,
CRP_SUSPENSE_MOU,
CRP_TEST_TRUNK_CNT,
CRP_NEVER_BILLABLE_CNT,
CRP_NEVER_BILLABLE_MOU,
CRP_BILLABLE_CNT,
CRP_BILLABLE_MOU,
CRP_NOT_PRCBL_CNT,
CRP_NOT_PRCBL_MOU,
CRP_CRPCDR_CNT,
CRP_CRPCDR_MOU,
CRP_TRUNK_SEIZURE_CNT,
CRP_TRUNK_SEIZURE_MOU,
NVL(CRP_SUSPENSE_CNT,0) + NVL(CRP_NEVER_BILLABLE_CNT,0) + NVL(CRP_NOT_PRCBL_CNT,0) + NVL(CRP_BILLABLE_CNT,0) + NVL(CRP_TRUNK_SEIZURE_CNT,0) as CRP_PROCESSED_CNT,
NVL(CRP_SUSPENSE_MOU,0) + NVL(CRP_NEVER_BILLABLE_MOU,0) + NVL(CRP_NOT_PRCBL_MOU,0) + NVL(CRP_BILLABLE_MOU,0) + NVL(CRP_TRUNK_SEIZURE_MOU,0) as CRP_PROCESSED_MOU,
CRP_CDR_READ_CNT,
CRP_CDR_READ_MOU,
CRP_HOLD_TIME_CNT,
CRP_HOLD_TIME_MOU,
PRICING_PRICED_CDR_CNT,
PRICING_PRICED_CDR_MOU,
PRICING_NOT_BILLABLE_CNT,
PRICING_NOT_BILLABLE_MOU,
PRICING_NOT_PRCBL_CNT,
PRICING_NOT_PRCBL_MOU,
PRICING_SUSPENSE_CNT,
PRICING_SUSPENSE_MOU,
nvl(PRICING_PRICED_CDR_CNT,0) + nvl(PRICING_NOT_BILLABLE_CNT,0) + nvl(PRICING_NOT_PRCBL_CNT,0) + nvl(PRICING_SUSPENSE_CNT,0) as PRICING_PROCESSED_CNT,
nvl(PRICING_PRICED_CDR_MOU,0) + nvl(PRICING_NOT_BILLABLE_MOU,0) + nvl(PRICING_NOT_PRCBL_MOU,0) + nvl(PRICING_SUSPENSE_MOU,0) as PRICING_PROCESSED_MOU,
PRICING_CRPCDR_READ_CNT,
PRICING_CRPCDR_READ_MOU,
nvl(CRP_CRPCDR_CNT,0) - nvl(PRICING_CRPCDR_READ_CNT,0) as DIFF_CDR_CRP_IP_CNT,
nvl(CRP_CRPCDR_MOU,0) - nvl(PRICING_CRPCDR_READ_MOU,0) as DIFF_CDR_CRP_IP_MOU,
CRP_BILLABLE_CNT - (PRICING_CRPCDR_READ_CNT - PRICING_NOT_BILLABLE_CNT - PRICING_NOT_PRCBL_CNT) as DIFF_BILL_NB_NP_CNT,
nvl(CRP_BILLABLE_MOU,0) - (nvl(PRICING_CRPCDR_READ_MOU,0) - nvl(PRICING_NOT_BILLABLE_MOU,0) - nvl(PRICING_NOT_PRCBL_MOU,0)) as DIFF_BILL_NB_NP_MOU,
nvl(CRP_BILLABLE_CNT,0) - (nvl(PRICING_SUSPENSE_CNT,0) + nvl(PRICING_PRICED_CDR_CNT,0)) as DIFF_BILL_ERR_PRCD_CNT,
nvl(CRP_BILLABLE_MOU,0) - (nvl(PRICING_SUSPENSE_MOU,0) + nvl(PRICING_PRICED_CDR_MOU,0)) as DIFF_BILL_ERR_PRCD_MOU,
0 as MIS_COCOT_B_CNT,
0 as MIS_COCOT_B_DUR,
0 AS ufeed_out_count,
DECODE(UFEED_PAYPHONE_SUMM_DTL.SOURCE_SYSTEM_CD,'B',UFEED_PAYPHONE_SUMM_DTL.SUMM_RECORD_CNT,NULL) AS UFEED_SENT_TO_PAYAPPL_CNT,
DECODE(UFEED_PAYPHONE_SUMM_DTL.SOURCE_SYSTEM_CD,'B',UPPER(UFEED_PAYPHONE_SUMM_DTL.SUMM_FILE_NM),NULL) AS UFEED_COCOT_FILE_NM,
DECODE(UFEED_PAYPHONE_SUMM_DTL.SOURCE_SYSTEM_CD,'B',UFEED_PAYPHONE_SUMM_DTL.RECORDS_READ_CNT,NULL) AS UFEED_PAYAPPL_READ_CNT
FROM v_ff_fs_billing_file_stats,
UFEED_PAYPHONE_SUMM_DTL,
file_relation,
(select file_id,
count(process_exception.file_id) active_counts,
1 file_count
from process_exception
group by file_id) exception_count,
file_fact cpc4000_file,
SWITCH_ID
WHERE v_ff_fs_billing_file_stats.billing_file_id = UFEED_PAYPHONE_SUMM_DTL.billing_file_id(+)
AND v_ff_fs_billing_file_stats.file_seq = file_relation.parent_file_seq(+)
AND v_ff_fs_billing_file_stats.TAPE_HEADER_SWITCH_ID = SWITCH_ID.TAPE_HDR_SWITCH_ID(+)
AND v_ff_fs_billing_file_stats.billing_file_id = exception_count.file_id(+)
AND file_relation.child_file_seq = cpc4000_file.file_seq(+)
AND file_relation.file_relation_type_seq(+) = 122
AND cpc4000_file.file_type_def_seq(+) = 140
AND ( to_date(billing_file_DT,'DD-MON-YY') =TO_DATE(:ad_filedt,'DD-MON-YY') )
-- AND ( billing_file_DT =:ad_filedt )
AND v_ff_fs_billing_file_stats.file_type_def_seq in (100,110,111)
AND (
(
:as_switchtype = 'O' AND
(
substr(UPPER(SWITCH_ID.SWITCH_OWNER),1,3) != 'LCI' OR
(
substr(UPPER(SWITCH_ID.SWITCH_OWNER),1,3) = 'LCI' AND switch_id.switch_id ='CITCHK'
)
)
AND (
:as_switch_nm = 'All' OR
UPPER(switch_id.tape_hdr_switch_id) = UPPER(:as_switch_nm)
)
)
OR
(
SUBSTR(UPPER(SWITCH_ID.SWITCH_OWNER),1,3) = 'LCI'
AND switch_id.switch_id !='CITCHK'
AND :as_switchtype = 'L'
AND ( :as_switch_nm = 'All' OR upper (switch_id.tape_hdr_switch_id) = upper(:as_switch_nm) )
)
)
ORDER BY CASE WHEN billing_file_NM not like 'U%' THEN billing_file_nm
WHEN billing_file_nm like 'U%' THEN tape_header_switch_id || billing_file_nm
END ASC
|
] 
Blog
Search
Help
Members
Register
Login
Home
