Home » RDBMS Server » Performance Tuning » Query to be restructured for performance gain (Oracle 10G)
Query to be restructured for performance gain [message #434902] Fri, 11 December 2009 10:44 Go to next message
bholeuday
Messages: 28
Registered: April 2009
Location: Talavali
Junior Member

Hi,

I want to tune below query..

SELECT BATCH_REC_TYPE1(batch_guid,xml_filename,subscriber_id) batch_rec BULK COLLECT INTO BATCH_TABLE_TYPE_TAB
FROM acknowledgement_queue a
WHERE batch_guid IN (SELECT v.batch_guid FROM
(SELECT batch_guid,
min(next_dttm) next_dttm
FROM acknowledgement_queue aq
WHERE next_dttm <= sys_extract_utc(current_timestamp)
AND((status IN ('P', 'R') and process_name IS NULL and host_name is null)
OR
(status='I' and host_name=i_host_name and process_name=i_process_name)
)
AND retry_count <=
(SELECT max_retry_count
FROM acknowledgement_parameters)
AND EXISTS (SELECT '1'
FROM acknowledgement_subscribers
WHERE subscriber_id = aq.subscriber_id
AND ack_send_flag = 'Y'
AND business_unit_code = i_business_unit_code)
GROUP BY batch_guid
ORDER BY next_dttm
) v
WHERE ROWNUM <= NVL(i_set_size,0)
)
AND EXISTS (SELECT '1' FROM acknowledgement_subscribers
WHERE subscriber_id = a.subscriber_id
AND ack_send_flag = 'Y'
AND business_unit_code = i_business_unit_code)
AND a.status IN ('P','R','I')
ORDER BY next_dttm,batch_guid
FOR UPDATE NOWAIT;

Sorry for formatting issues very small window to accomodate.
Any tuning tips in this statement are welcome....i have tried most of basics query restructuring but not of any help.
Re: Query to be restructured for performance gain [message #434905 is a reply to message #434902] Fri, 11 December 2009 10:49 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
unless & until you post these details, your request will be ignored
Re: Query to be restructured for performance gain [message #434906 is a reply to message #434902] Fri, 11 December 2009 10:50 Go to previous message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
SELECT   Batch_rec_type1(batch_guid,xml_filename,subscriber_id) batch_rec
BULK COLLECT INTO batch_table_type_tab
FROM     acknowledgement_queue a
WHERE    batch_guid IN (SELECT v.batch_guid
                        FROM   (SELECT   batch_guid,
                                         Min(next_dttm) next_dttm
                                FROM     acknowledgement_queue aq
                                WHERE    next_dttm <= Sys_extract_utc(current_timestamp)
                                         AND ((status IN ('P','R')
                                               AND process_name IS NULL
                                               AND host_name IS NULL)
                                               OR (status = 'I'
                                                   AND host_name = i_host_name
                                                   AND process_name = i_process_name))
                                         AND retry_count <= (SELECT max_retry_count
                                                             FROM   acknowledgement_parameters)
                                         AND EXISTS (SELECT '1'
                                                     FROM   acknowledgement_subscribers
                                                     WHERE  subscriber_id = aq.subscriber_id
                                                            AND ack_send_flag = 'Y'
                                                            AND business_unit_code = i_business_unit_code)
                                GROUP BY batch_guid
                                ORDER BY next_dttm) v
                        WHERE  ROWNUM <= Nvl(i_set_size,0))
         AND EXISTS (SELECT '1'
                     FROM   acknowledgement_subscribers
                     WHERE  subscriber_id = a.subscriber_id
                            AND ack_send_flag = 'Y'
                            AND business_unit_code = i_business_unit_code)
         AND a.status IN ('P','R','I')
ORDER BY next_dttm,
         batch_guid
FOR UPDATE NOWAIT; 
Previous Topic: APPEND hint in Rule and Cost based optimizer
Next Topic: Cost of a Rollback
Goto Forum:
  


Current Time: Wed Dec 07 03:24:48 CST 2016

Total time taken to generate the page: 0.11102 seconds