Home » RDBMS Server » Performance Tuning » tune the query
tune the query [message #134205] Tue, 23 August 2005 18:27 Go to next message
ponnilavan
Messages: 12
Registered: January 2005
Junior Member
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

Re: tune the query [message #134365 is a reply to message #134205] Wed, 24 August 2005 08:50 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10752/toc.htm
Re: tune the query [message #134610 is a reply to message #134205] Thu, 25 August 2005 14:27 Go to previous message
vjeedigunta
Messages: 201
Registered: March 2005
Location: Hyderabad
Senior Member

It would be easy to determine the problem if you post the explain plan of the statement ..instead of pasting the query ..
look at explain plan..look at which statements are taking high cost ...and then you will be able to proceed from there ...

Also you need to mention oracle version / optimizer problems in the question ..

-Sai Jeedigunta
Previous Topic: Query Performance
Next Topic: How test performance of table? and how to calucalate row size min and max of the Table
Goto Forum:
  


Current Time: Fri Mar 29 02:17:51 CDT 2024