Home » Server Options » Streams & AQ » How to tune this code
How to tune this code [message #354018] Thu, 16 October 2008 02:13
ramya29p
Messages: 146
Registered: November 2007
Location: Chennai
Senior Member
Hi,
I am Executing the Following Code. It is taking Long time to Execute .. if i comment the line
dequeue_options.msgid         := '5947ED0636410BD0E044001A4B068B
it is executing faster,but i need 2 mention that line also. can anyone tell me how to tune this code?

declare
dequeue_options dbms_aq.dequeue_options_t;
    message_prop    dbms_aq.message_properties_t;
    message_hdl     RAW(16);
    --my_msg          SYS.AQ$_JMS_TEXT_MESSAGE;
    l_notif_rec            gwty_notif_data := gwty_notif_data(NULL, NULL,
                                                              NULL, NULL);
    l_notification_message VARCHAR2(32767);
    l_notif_message_tmp VARCHAR2(32767); --thilagam 30-08-2008
    l_notif_xml_msg_clob   CLOB;
    l_is_clob              VARCHAR2(1);
    l_err_code             VARCHAR2(20);
    l_err_param            VARCHAR2(3000);
    l_ts_header_tags       VARCHAR2(2000) := 'MSGID~NOTIF_REF_NO~BRANCH~NOTIF_CODE~';
    l_ts_header_values     VARCHAR2(2000);
    l_parent_nodes         VARCHAR2(2000) := 'FCUBS_NOTIF_IDENTIFIER>^';
    l_parent_format        VARCHAR2(2000) := '1>^';
    l_msg_obj              sys.aq$_jms_text_message;
    l_queue_options        dbms_aq.enqueue_options_t;
    l_msg_properties       dbms_aq.message_properties_t;
    l_msg_id               RAW(16);
    l_notif_status         VARCHAR2(1);
    l_notif_status_reason  VARCHAR2(4000);
    l_notification_ref_no  VARCHAR2(16);
    l_sys_date_char        VARCHAR2(20);
    l_seq_val              NUMBER;
    
    descr sys.aq$_descriptor;

 TYPE ty_notif_msg IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
    l_tb_notif_msg               ty_notif_msg;
    l_notif_msg                  VARCHAR2(4000);
    l_res_msg_ref_no             VARCHAR2(100);
    l_excep_msg                  VARCHAR2(255);

 l_pkey_fields                VARCHAR2(4000);
    l_pkey_values                VARCHAR2(4000);
    l_process_notif              VARCHAR2(1);
    l_log_flag                   VARCHAR2(1);
   
   BEGIN
dequeue_options.msgid         := '5947ED0636410BD0E044001A4B068BF8';
  dequeue_options.consumer_name := descr.consumer_name;
    
BEGIN
      --DESCR.QUEUE_NAME := 'GWAQ_FCUBS_NOTIFY_DATA_Q';
      dbms_aq.dequeue(queue_name => 'GWAQ_FCUBS_NOTIF_DATA_Q' /*descr.queue_name*/,
                      dequeue_options => dequeue_options,
                      message_properties => message_prop,
                      payload => l_notif_rec, msgid => message_hdl);

    
    END;
    
    l_pkey_fields := l_notif_rec.pkey_fields;
    l_pkey_values := l_notif_rec.pkey_values;
dbms_output.put_line('table='||l_notif_rec.base_table_name);
dbms_output.put_line('operation='||l_notif_rec.operation);

end;
Previous Topic: tnsnames
Next Topic: Stream replication solution
Goto Forum:
  


Current Time: Fri Mar 29 05:12:22 CDT 2024