Solved

Size limitation and performance using xml db?

Posted on 2006-07-10
23
1,239 Views
Last Modified: 2008-03-06
Does anyone know what the size limitation for files is using xml db functionality.
 What would be the advanatge performance wise using xml db as opposed to building your own parser.

I have posted a few questions recently using xml db.  I rewrote a stored procedure using xml db the old stored procedure used instr, substr etc to manually parse and xml file.
I was wondering why he did this? Hence the question size limitation or performance limitation.

I will be using my stored procedure to load 64 million records each file will be no greater then 1.5 mb
Any other suggestions to improve performance in stored procedure would be appreciated.
Thanks.

/* Formatted on 2006/07/10 16:10 (Formatter Plus v4.8.5) */
CREATE OR REPLACE PACKAGE BODY parse_xml_pkg_7
AS
   xmldata                  XMLTYPE;
   DATA                     VARCHAR2 (2000);
   evh_data                 VARCHAR2 (2000);
   evd_data                 VARCHAR2 (2000);
   concat_data_parent_str   VARCHAR2 (2000);
   pin_detail_delim         VARCHAR2 (10)   := '||';
   pin_delim                VARCHAR2 (10)   := '~';
   counter                  NUMBER          := 0;
   idx_counter              NUMBER          := 0;
   count_occurence          NUMBER          := 0;

   FUNCTION parse_str_fnc (pin_str IN VARCHAR2, pin_delim IN VARCHAR2)
      RETURN str_tab
   AS
      l_str                VARCHAR2 (2000) DEFAULT pin_str || pin_delim;
      l_i                  NUMBER;
      l2_i                 NUMBER;
      l_tab                str_tab         := str_tab ();
      l_len_of_pin_delim   NUMBER;
   BEGIN
      --get the last val back add extra delim, without last val is not returend
         --l_str := l_str||pin_delim;
      l_len_of_pin_delim := LENGTH (pin_delim) - 1;

      LOOP
         l_i := INSTR (l_str, pin_delim);
         l2_i := INSTR (l_str, '||');
         EXIT WHEN (NVL (l_i, 0) = 0);
         l_tab.EXTEND;
         l_tab (l_tab.COUNT) := SUBSTR (l_str, 1, l_i - 1);
         --Add length of pin_delimiter to account correct value retrieved in substr function.
         l_str := SUBSTR (l_str, l_i + 1 + l_len_of_pin_delim);
      END LOOP;

      RETURN l_tab;
   END parse_str_fnc;

   PROCEDURE parse_xml_import_prc (
      pin_price_event_msg   IN       CLOB,
      pout_error            OUT      NUMBER,
      pout_error_msg        OUT      VARCHAR,
      pout_time_stamp       OUT      VARCHAR,
      pout_source_id        OUT      VARCHAR
   )
   AS
   BEGIN
      -- convert clob into xmltype instance
      xmldata := XMLTYPE.createxml (pin_price_event_msg);

      BEGIN
         SELECT EXTRACTVALUE (xmldata, '/PDATA/HDR') "HDR"
           INTO DATA
           FROM DUAL
          WHERE EXISTSNODE (xmldata, '/PDATA/HDR') = 1;

         DBMS_OUTPUT.put_line ('HDR Data: ' || DATA);
         hdr_val_tab := parse_str_fnc (DATA, pin_delim);
         pout_time_stamp := hdr_val_tab (1);
         pout_source_id := hdr_val_tab (2);

         FOR j IN 1 .. hdr_val_tab.COUNT
         LOOP
            DBMS_OUTPUT.put_line ('--HDR Value: ' || hdr_val_tab (j));
         END LOOP;

         SELECT COUNT (1)
           INTO idx_counter
           FROM TABLE (XMLSEQUENCE (EXTRACT (xmldata, '/PDATA/EVH')));

         <<outer_0>>
         FOR v IN 1 .. idx_counter
         LOOP
            SELECT EXTRACTVALUE (xmldata, '/PDATA/EVH[' || v || ']') "EVH"
              INTO evh_data
              FROM DUAL
             WHERE EXISTSNODE (xmldata, '/PDATA/EVH') = 1;

            SELECT EXTRACTVALUE (xmldata, '/PDATA/EVD[' || v || ']') "EVD"
              INTO evd_data
              FROM DUAL
             WHERE EXISTSNODE (xmldata, '/PDATA/EVD') = 1;

            evd_parent_val_tab := parse_str_fnc (evd_data, pin_detail_delim);

            <<pin_detail_delim_1>>
            FOR j IN 1 .. evd_parent_val_tab.COUNT
            LOOP
               concat_data_parent_str := evh_data || pin_delim || evd_data;
               --DBMS_OUTPUT.put_line ('PRE-parsed concat_data_parent_str : ' || concat_data_parent_str);
               concat_data_parent_str := evh_data || pin_delim || evd_parent_val_tab (j);
               --DBMS_OUTPUT.put_line ('parsed concat_data_parent_str : ##' || j || '## ' || concat_data_parent_str);
               concat_data_multi_parent_tab := parse_str_fnc (concat_data_parent_str, pin_delim);
               upsert_rec_prc (concat_data_multi_parent_tab, pout_error, concat_data_parent_str);
            END LOOP pin_detail_delim_1;
         END LOOP outer_0;
      EXCEPTION
         WHEN OTHERS
         THEN
            pout_error := 1;
            pout_error_msg := display_error_fnc (concat_data_multi_parent_tab, 'XML PARSE', SQLCODE, SQLERRM);
      END;
   END parse_xml_import_prc;

   PROCEDURE upsert_rec_prc (raw_rec_tab IN str_tab, pout_error OUT NUMBER, pout_error_msg IN OUT VARCHAR2)
   IS
   BEGIN
      g_concat_evh_evd_row := pout_error_msg;

      BEGIN
         BEGIN
            g_evh_transaction_type := raw_rec_tab (1);
            g_evh_store_id := raw_rec_tab (2);
            g_evh_event_id := raw_rec_tab (3);
            g_evh_event_type := raw_rec_tab (4);
            g_evh_event_sign_flag := raw_rec_tab (5);
            g_evh_event_override_flag := raw_rec_tab (6);
            g_evh_event_begin_time_stamp := TO_DATE (raw_rec_tab (7), g_date_fmt);
            g_evh_event_end_time_stamp := TO_DATE (raw_rec_tab (8), g_date_fmt);
            g_evh_event_name := raw_rec_tab (9);
            g_evd_item_id := raw_rec_tab (11);
            g_evd_item_price := raw_rec_tab (12);
            g_evd_item_status_code := raw_rec_tab (13);
            g_evd_sale_type := raw_rec_tab (14);
         EXCEPTION
            WHEN OTHERS
            THEN
               pout_error := 1;
               pout_error_msg := display_error_fnc (raw_rec_tab, 'OTHERS', SQLCODE, SQLERRM);
               NULL;
         END;

         IF g_evh_transaction_type NOT IN ('18', '32', '33')
         THEN
            INSERT INTO ps_itm_loc_prc
                        (id_str_rt,
                         id_evt_prc_chn,
                         ty_prc_chn,
                         fl_sgn_evt_prc,
                         fl_ovrd,
                         dc_eff_cur_sls,
                         dc_exp_cur_sls,
                         nm_evt_prc_chn,
                         id_itm,
                         rp_cur_sls,
                         sc_itm_loc_prc,
                         ty_sls,
                         de_rsn_evt_prc_chn,
                         sc_eff,
                         sc_exp,
                         sc_upd,
                         ts_crt_rcrd,
                         ts_mdf_rcrd
                        )
            VALUES      (g_evh_store_id,
                         g_evh_event_id,
                         g_evh_event_type,
                         g_evh_event_sign_flag,
                         g_evh_event_override_flag,
                         g_evh_event_begin_time_stamp,
                         g_evh_event_end_time_stamp,
                         g_evh_event_name,
                         g_evd_item_id,
                         g_evd_item_price,
                         g_evd_item_status_code,
                         g_evd_sale_type,
                         NULL,
                         0,
                         0,
                         g_ins_flag,
                         c_sysdate,
                         c_sysdate
                        );
         END IF;
      EXCEPTION
         WHEN DUP_VAL_ON_INDEX
         THEN
            pout_error := 1;
            pout_error_msg := display_error_fnc (raw_rec_tab, 'DUP_VAL_ON_INDEX', SQLCODE, SQLERRM);

            UPDATE ps_itm_loc_prc
               SET ty_prc_chn = g_evh_event_type,
                   fl_sgn_evt_prc = g_evh_event_sign_flag,
                   fl_ovrd = g_evh_event_override_flag,
                   dc_eff_cur_sls = g_evh_event_begin_time_stamp,
                   dc_exp_cur_sls = g_evh_event_end_time_stamp,
                   nm_evt_prc_chn = g_evh_event_name,
                   id_itm = g_evd_item_id,
                   rp_cur_sls = g_evd_item_price,
                   sc_itm_loc_prc = g_evd_item_status_code,
                   ty_sls = g_evd_sale_type,
                   ts_mdf_rcrd = c_sysdate,
                   sc_upd = g_upd_flag
             WHERE ps_itm_loc_prc.id_itm = g_evd_item_id
               AND ps_itm_loc_prc.id_str_rt = g_evh_store_id
               AND ps_itm_loc_prc.id_evt_prc_chn = g_evh_event_id;
      END;

      BEGIN
         IF g_evh_transaction_type IN ('18', '32')
         THEN
            UPDATE ps_itm_loc_prc
               SET ts_mdf_rcrd = c_sysdate,
                   sc_upd = g_upd_flag,
                   dc_exp_cur_sls = g_evh_event_end_time_stamp
             WHERE ps_itm_loc_prc.id_itm = g_evd_item_id
               AND ps_itm_loc_prc.id_str_rt = g_evh_store_id
               AND ps_itm_loc_prc.id_evt_prc_chn = g_evh_event_id;

            IF SQL%ROWCOUNT = 0
            THEN
               RAISE NO_DATA_FOUND;
            END IF;
         END IF;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            pout_error := 1;
            pout_error_msg := display_error_fnc (raw_rec_tab, 'NO_DATA_FOUND', SQLCODE, SQLERRM);
      END;

      BEGIN
         IF g_evh_transaction_type IN ('33')
         THEN
            UPDATE ps_itm_loc_prc
               SET ts_mdf_rcrd = c_sysdate,
                   sc_upd = g_upd_flag,
                   dc_exp_cur_sls = g_evh_event_end_time_stamp
             WHERE ps_itm_loc_prc.id_str_rt = g_evh_store_id AND ps_itm_loc_prc.id_evt_prc_chn = g_evh_event_id;

            IF SQL%ROWCOUNT = 0
            THEN
               RAISE NO_DATA_FOUND;
            END IF;
         END IF;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            pout_error := 1;
            pout_error_msg := display_error_fnc (raw_rec_tab, 'NO_DATA_FOUND', SQLCODE, SQLERRM);
      END;
   END upsert_rec_prc;

   FUNCTION display_error_fnc (
      raw_rec_tab         IN   str_tab,
      pin_msg_type        IN   VARCHAR2,
      pin_error_sqlcode   IN   VARCHAR2,
      pin_error_sqlerm    IN   VARCHAR2
   )
      RETURN VARCHAR2
   IS
      l_error_msg   VARCHAR2 (2000);
   BEGIN
      /*
         DBMS_OUTPUT.put_line (' Transaction Type,' || raw_rec_tab (1));
         DBMS_OUTPUT.put_line (' Store ID  ID_STR_RT,' || raw_rec_tab (2));
         DBMS_OUTPUT.put_line (' Event ID  ID_EVT_PRC_CHN,' || raw_rec_tab (3));
         DBMS_OUTPUT.put_line (' Event Type TY_PRC_CHN,' || raw_rec_tab (4));
         DBMS_OUTPUT.put_line (' Event Sign Flag FL_SGN_EVT_PRC,' || raw_rec_tab (5));
         DBMS_OUTPUT.put_line (' Override Indicator FL_OVRD,' || raw_rec_tab (6));
         DBMS_OUTPUT.put_line (' Event Begin Timestamp DC_EFF_CUR_SLS,' || raw_rec_tab (7));
         DBMS_OUTPUT.put_line (' Event End Timestamp DC_EXP_CUR_SLS,' || raw_rec_tab (8));
         DBMS_OUTPUT.put_line (' Event Name NM_EVT_PRC_CHN,' || raw_rec_tab (9));
         --DBMS_OUTPUT.put_line ('Event ID  ID_EVT_PRC_CHN (10),' || raw_rec_tab (10));
         DBMS_OUTPUT.put_line (' Item ID ID_ITM,' || raw_rec_tab (11));
         DBMS_OUTPUT.put_line (' Item Price RP_CUR_SLS,' || raw_rec_tab (12));
         DBMS_OUTPUT.put_line (' Item Status Code SC_ITM_LOC_PRC,' || raw_rec_tab (13));
         DBMS_OUTPUT.put_line (' Sale type TY_SLS,' || raw_rec_tab (14)); */
      l_error_msg :=
            pin_msg_type
         || '  '
         || ' slqcode: '
         || pin_error_sqlcode
         || 'sqlerm: '
         || pin_error_sqlerm
         || ' Transaction Type=>'
         || raw_rec_tab (1)
         || CHR (10)
         || ' ATTRIBUTE  COLUMN_NAME   VALUE'
         || CHR (10)
         || ' Store ID  ID_STR_RT=>'
         || raw_rec_tab (2)
         || CHR (10)
         || ' Event ID  ID_EVT_PRC_CHN=>'
         || raw_rec_tab (3)
         || CHR (10)
         || ' Event Type TY_PRC_CHN=>'
         || raw_rec_tab (4)
         || CHR (10)
         || ' Event Sign Flag FL_SGN_EVT_PRC=>'
         || raw_rec_tab (5)
         || CHR (10)
         || ' Override Indicator FL_OVRD=>'
         || raw_rec_tab (6)
         || CHR (10)
         || ' Event Begin Timestamp DC_EFF_CUR_SLS=>'
         || raw_rec_tab (7)
         || CHR (10)
         || ' Event End Timestamp DC_EXP_CUR_SLS=>'
         || raw_rec_tab (8)
         || CHR (10)
         || ' Event Name NM_EVT_PRC_CHN=>'
         || raw_rec_tab (9)
         || CHR (10)
         || ' Item Id ID_ITM,||'
         || raw_rec_tab (11)
         || CHR (10)
         || ' Item Price RP_CUR_SLS,||'
         || raw_rec_tab (12)
         || CHR (10)
         || ' Item Status Code SC_ITM_LOC_PRC=>'
         || raw_rec_tab (13)
         || CHR (10)
         || ' Sale type TY_SLS=>'
         || raw_rec_tab (14)
         || CHR (10);
      DBMS_OUTPUT.put_line (l_error_msg);
      RETURN l_error_msg;
   END display_error_fnc;
BEGIN
   DBMS_OUTPUT.ENABLE (1000000);
END parse_xml_pkg_7;
/
0
Comment
Question by:dplinnane
  • 8
  • 7
  • 5
  • +2
23 Comments
 
LVL 14

Assisted Solution

by:sathyagiri
sathyagiri earned 150 total points
Comment Utility
I think one of the key performance adv would be that you would be using oracle relational system which would provide good throughputon your queries.

I am not aware of any size limitations except for that of varchar2's which would be 4000 instead you might want to use CLOB's for storing your strings
0
 
LVL 19

Expert Comment

by:actonwang
Comment Utility
xml db is a native xml support by oracle to use relational database engine to parse and store xml data. What do you think? using your own parser or xml db? it should be obvious.

one suggestion is that you can do a benchmark on small set of data to compare your new procs and old procs.
0
 
LVL 19

Accepted Solution

by:
actonwang earned 200 total points
Comment Utility
Organizations today typically manage their structured data and unstructured data differently:

    * Unstructured data, in tables, makes document access transparent and table access complex
    * Structured data, often in binary large objects (such as in BLOBs) makes access more complex and table access transparent.

With Oracle XML DB you can store and manage both structured, unstructured, and pseudo or semi-structured data, using a standard data model, and standard SQL and XML.

Oracle XML DB provides complete transparency and interchangeability between XML and SQL. You can perform both the following:

    * XML operations on object-relational (such as table) data
    * SQL operations on XML documents

This makes the database much more accessible to XML-shaped data content.

excerpt from:

http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96620/xdb01int.htm#1047784
0
 
LVL 19

Expert Comment

by:actonwang
Comment Utility
>> Does anyone know what the size limitation for files is using xml db functionality.
     if you use XMLTYPE, since it accepts CLOB as its XML content input, the limitation will be 4G for each xml file.
0
 
LVL 14

Expert Comment

by:sathyagiri
Comment Utility
Here 's a good overview document on XML DB and its advantages
http://www.oracle.com/technology/tech/xml/xmldb/Current/twp_xmldb_10gr2_tech_overview.pdf
0
 
LVL 47

Assisted Solution

by:schwertner
schwertner earned 150 total points
Comment Utility
XML is used for data exchange between different applications.
The usage of XML to store data is bad idea because the overhead (in tags)
of XML is very high.

I will suggest to use tools to manipulate XML. They will reflect the new changes
and new versions of Oracle and will fit the internal structure of Oracle.

CLOBs are the ideal candidate for storing and exchanging XMLs.
0
 

Author Comment

by:dplinnane
Comment Utility
For some reason my package is incredibly slow.  It has taken about 20 mins so far to load 400 recs.
I'm using toad to monitor it and I'm seeing that the following sql statement is
the current statement.
Things to note.
I am using oracle 10g express edition on windows.
The stored procedure is being called through java.
Any ideas what could be going wrong. I have posted the package body below. I am also using a nested table to store values.

CREATE OR REPLACE type str_tab is table of varchar2(4000);
/

SELECT EXTRACTVALUE (:b1, '/PDATA/EVD[' || :b2 || ']') "EVD"
  FROM DUAL
 WHERE EXISTSNODE (:b1, '/PDATA/EVD') = 1


/* Formatted on 2006/07/11 16:02 (Formatter Plus v4.8.5) */
CREATE OR REPLACE PACKAGE BODY priceevent
AS
/*********************** parse_str_fnc ********************************************************
This function takes a string and a delimiter and parses the values and places
them in an array l_tab.  It also has the capability to parse values if the delimiter is greater
then 1 character as in the example using double pipe.
This is handeled by adding 1 to the lenght of the delimiter
l_str := SUBSTR (l_str, l_i + 1 + l_len_of_pin_delim);
***********************************************************************************************/
   FUNCTION parse_str_fnc (pin_str IN VARCHAR2, pin_delim IN VARCHAR2)
      RETURN str_tab
   AS
      l_str                VARCHAR2 (2000) DEFAULT pin_str || pin_delim;
      l_i                  NUMBER;
      l2_i                 NUMBER;
      l_tab                str_tab         := str_tab ();
      l_len_of_pin_delim   NUMBER;
   BEGIN
      --get the last val back add extra delim, without last val is not returend
         --l_str := l_str||pin_delim;
      l_len_of_pin_delim := LENGTH (pin_delim) - 1;

      LOOP
         l_i := INSTR (l_str, pin_delim);
         l2_i := INSTR (l_str, '||');
         EXIT WHEN (NVL (l_i, 0) = 0);
         l_tab.EXTEND;
         l_tab (l_tab.COUNT) := SUBSTR (l_str, 1, l_i - 1);
         --Add length of pin_delimiter to account correct value retrieved in substr function.
         l_str := SUBSTR (l_str, l_i + 1 + l_len_of_pin_delim);
             
      END LOOP;

      RETURN l_tab;
   END parse_str_fnc;

/*********************** pin_price_event_msg ********************************************************
The procedure takes an xml string pin_price_event_msg and utilizes EXTRACTVALUE to extract a value for each node.
The following statement counts the number of nodes
SELECT COUNT (1)
           INTO idx_counter
           FROM TABLE (XMLSEQUENCE (EXTRACT (xml_data, '/PDATA/EVH')));
The following statement extractvalue is used to return a value for each node. The outer loop loops
n times where n is the number of nodes counted for a particular tag.
         FOR v IN 1 .. idx_counter
         LOOP
            SELECT EXTRACTVALUE (xml_data, '/PDATA/EVH[' || v || ']') "EVH"
              INTO evh_data
              FROM DUAL
             WHERE EXISTSNODE (xml_data, '/PDATA/EVH') = 1;
The outer loop parses the || delimiter
The inner loop concatenates the evh and evd data together and then parses each string delimited by ~
The inner loop also calls the insert update procedure.
***********************************************************************************************/
   PROCEDURE parse_xml_import_prc (
      pin_price_event_msg   IN       CLOB,
      pout_error            OUT      NUMBER,
      pout_error_msg        OUT      VARCHAR2,
      pout_time_stamp       OUT      VARCHAR2,
      pout_source_id        OUT      VARCHAR2
   )
   AS
      parsed_data_child_val_tab      str_tab;
      evd_val_tab                    str_tab;
      hdr_val_tab                    str_tab;
      evh_val_tab                    str_tab;
      concat_data_multi_parent_tab   str_tab;
      evd_parent_val_tab             str_tab;
      xml_data                       XMLTYPE;
      DATA                           VARCHAR2 (2000);
      evh_data                       VARCHAR2 (2000);
      evd_data                       VARCHAR2 (2000);
      concat_data_parent_str         VARCHAR2 (2000);
      pin_detail_delim               VARCHAR2 (10)   := '||';
      pin_delim                      VARCHAR2 (10)   := '~';
      idx_counter                    NUMBER          := 0;
   BEGIN
      -- convert clob into xmltype instance
      xml_data := XMLTYPE.createxml (pin_price_event_msg);
      c_sysdate := TO_DATE (SYSDATE, g_date_fmt);

      --DBMS_OUTPUT.put_line ('Constant sysdate and system sysdate' || c_sysdate || ' sysdate ' || SYSDATE);
      BEGIN
         SELECT EXTRACTVALUE (xml_data, '/PDATA/HDR') "HDR"
           INTO DATA
           FROM DUAL
          WHERE EXISTSNODE (xml_data, '/PDATA/HDR') = 1;

         --DBMS_OUTPUT.put_line ('HDR Data: ' || DATA);
             --hdr_val_tab.EXTEND;
         hdr_val_tab := parse_str_fnc (DATA, pin_delim);
         pout_time_stamp := hdr_val_tab (1);
         pout_source_id := hdr_val_tab (2);

         SELECT COUNT (1)
           INTO idx_counter
           FROM TABLE (XMLSEQUENCE (EXTRACT (xml_data, '/PDATA/EVH')));

         <<outer_0>>
         FOR v IN 1 .. idx_counter
         LOOP
            SELECT EXTRACTVALUE (xml_data, '/PDATA/EVH[' || v || ']') "EVH"
              INTO evh_data
              FROM DUAL
             WHERE EXISTSNODE (xml_data, '/PDATA/EVH') = 1;

            SELECT EXTRACTVALUE (xml_data, '/PDATA/EVD[' || v || ']') "EVD"
              INTO evd_data
              FROM DUAL
             WHERE EXISTSNODE (xml_data, '/PDATA/EVD') = 1;

            --evd_parent_val_tab.EXTEND;
            evd_parent_val_tab := parse_str_fnc (evd_data, pin_detail_delim);

            <<pin_detail_delim_1>>
            FOR j IN 1 .. evd_parent_val_tab.COUNT
            LOOP
               --concat_data_parent_str := evh_data || pin_delim || evd_data;
               --DBMS_OUTPUT.put_line ('PRE-parsed concat_data_parent_str : ' || concat_data_parent_str);
               concat_data_parent_str := evh_data || pin_delim || evd_parent_val_tab (j);
               DBMS_OUTPUT.put_line ('parsed concat_data_parent_str : ##' || j || '## ' || concat_data_parent_str);
                     --concat_data_multi_parent_tab.EXTEND;
               concat_data_multi_parent_tab := parse_str_fnc (concat_data_parent_str, pin_delim);
               upsert_rec_prc (concat_data_multi_parent_tab, pout_error, pout_error_msg);
            END LOOP pin_detail_delim_1;
         END LOOP outer_0;
      EXCEPTION
         WHEN OTHERS
         THEN
            pout_error := 1;
            pout_error_msg := display_error_fnc (concat_data_multi_parent_tab, 'XML PARSE', SQLCODE, SQLERRM);
      END;
   END parse_xml_import_prc;

/*********************** upsert_rec_prc ********************************************************
The procedure takes the array values from raw_rec_tab and places them in global variables before
they are inserted into the database. Some records are formatted.  Here the logi is contained to
decide whether to insert the recs or update the recs.
There are exception handlers built in for not data found in this case we do an update.
The error msg and error number is returned to the main procedure parse_xml_import_prc.
There are a number of generic exception handlers. The when others used after assigning values
to the variables is handled using NULL which means do nothing and continue onto next
step which will be an update. It does not mnatter if we get a bad value g_evh_event_begin_time_stamp
as we will not be using this to update the table.
***********************************************************************************************/
   PROCEDURE upsert_rec_prc (raw_rec_tab IN str_tab, pout_error OUT NUMBER, pout_error_msg OUT VARCHAR2)
   IS
   BEGIN
      BEGIN
         BEGIN
            g_evh_transaction_type := raw_rec_tab (1);
            g_evh_store_id := raw_rec_tab (2);
            g_evh_event_id := raw_rec_tab (3);
            g_evh_event_type := raw_rec_tab (4);
            g_evh_event_sign_flag := raw_rec_tab (5);
            g_evh_event_override_flag := raw_rec_tab (6);
            g_evh_event_begin_time_stamp := TO_DATE (raw_rec_tab (7), g_date_fmt);
            g_evh_event_end_time_stamp := TO_DATE (raw_rec_tab (8), g_date_fmt);
            g_evh_event_name := raw_rec_tab (9);
            g_evd_event_id := raw_rec_tab (10);
            g_evd_item_id := raw_rec_tab (11);
            g_evd_item_price := raw_rec_tab (12);
            g_evd_item_status_code := raw_rec_tab (13);
            g_evd_sale_type := raw_rec_tab (14);

            /*IF g_evd_event_id <> g_evh_event_id
            THEN
               RAISE event_id_not_match;
               pout_error := 1;
               pout_error_msg := display_error_fnc (raw_rec_tab, 'RAISE event_id_not_match', SQLCODE, SQLERRM);
            END IF;
                  */
         EXCEPTION
            WHEN OTHERS
            THEN
               pout_error := 0;
               pout_error_msg := display_error_fnc (raw_rec_tab, 'OTHERS', SQLCODE, SQLERRM);
               NULL;
         END;

         IF g_evh_transaction_type NOT IN ('18', '32', '33')
         THEN
            INSERT INTO ps_itm_loc_prc
                        (id_str_rt,
                         id_evt_prc_chn,
                         ty_prc_chn,
                         fl_sgn_evt_prc,
                         fl_ovrd,
                         dc_eff_cur_sls,
                         dc_exp_cur_sls,
                         nm_evt_prc_chn,
                         id_itm,
                         rp_cur_sls,
                         sc_itm_loc_prc,
                         ty_sls,
                         de_rsn_evt_prc_chn,
                         sc_eff,
                         sc_exp,
                         sc_upd,
                         ts_crt_rcrd,
                         ts_mdf_rcrd
                        )
            VALUES      (g_evh_store_id,
                         g_evh_event_id,
                         g_evh_event_type,
                         g_evh_event_sign_flag,
                         g_evh_event_override_flag,
                         g_evh_event_begin_time_stamp,
                         g_evh_event_end_time_stamp,
                         g_evh_event_name,
                         g_evd_item_id,
                         g_evd_item_price,
                         g_evd_item_status_code,
                         g_evd_sale_type,
                         NULL,
                         0,
                         0,
                         g_ins_flag,
                         c_sysdate,
                         c_sysdate
                        );
                                    commit;
         END IF;
      EXCEPTION
         WHEN DUP_VAL_ON_INDEX
         THEN
            pout_error := 0;

            --pout_error_msg := display_error_fnc (raw_rec_tab, 'DUP_VAL_ON_INDEX', SQLCODE, SQLERRM);
            UPDATE ps_itm_loc_prc
               SET ty_prc_chn = g_evh_event_type,
                   fl_sgn_evt_prc = g_evh_event_sign_flag,
                   fl_ovrd = g_evh_event_override_flag,
                   dc_eff_cur_sls = g_evh_event_begin_time_stamp,
                   dc_exp_cur_sls = g_evh_event_end_time_stamp,
                   nm_evt_prc_chn = g_evh_event_name,
                   id_itm = g_evd_item_id,
                   rp_cur_sls = g_evd_item_price,
                   sc_itm_loc_prc = g_evd_item_status_code,
                   ty_sls = g_evd_sale_type,
                   ts_mdf_rcrd = c_sysdate,
                   sc_upd = g_upd_flag
             WHERE ps_itm_loc_prc.id_itm = g_evd_item_id
               AND ps_itm_loc_prc.id_str_rt = g_evh_store_id
               AND ps_itm_loc_prc.id_evt_prc_chn = g_evh_event_id;
                     commit;
      END;

      BEGIN
         IF g_evh_transaction_type IN ('18', '32')
         THEN
            UPDATE ps_itm_loc_prc
               SET ts_mdf_rcrd = c_sysdate,
                   sc_upd = g_upd_flag,
                   dc_exp_cur_sls = g_evh_event_end_time_stamp
             WHERE ps_itm_loc_prc.id_itm = g_evd_item_id
               AND ps_itm_loc_prc.id_str_rt = g_evh_store_id
               AND ps_itm_loc_prc.id_evt_prc_chn = g_evh_event_id;
                     commit;

            IF SQL%ROWCOUNT = 0
            THEN
               RAISE NO_DATA_FOUND;
            END IF;
         END IF;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            pout_error := 1;
            pout_error_msg := display_error_fnc (raw_rec_tab, 'NO_DATA_FOUND', SQLCODE, SQLERRM);
      END;

      BEGIN
         IF g_evh_transaction_type IN ('33')
         THEN
            UPDATE ps_itm_loc_prc
               SET ts_mdf_rcrd = c_sysdate,
                   sc_upd = g_upd_flag,
                   dc_exp_cur_sls = g_evh_event_end_time_stamp
             WHERE ps_itm_loc_prc.id_str_rt = g_evh_store_id AND ps_itm_loc_prc.id_evt_prc_chn = g_evh_event_id;
commit;
            IF SQL%ROWCOUNT = 0
            THEN
               RAISE NO_DATA_FOUND;
            END IF;
         END IF;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            pout_error := 1;
            pout_error_msg := display_error_fnc (raw_rec_tab, 'NO_DATA_FOUND', SQLCODE, SQLERRM);
      END;
   --c_sysdate := null;
   END upsert_rec_prc;

/*********************** display_error_fnc ********************************************************
The procedure takes the record been inserted and concatenates it together and prints the col
attribute col name and valu on each row as well as the error message.
***********************************************************************************************/
   FUNCTION display_error_fnc (
      raw_rec_tab         IN   str_tab,
      pin_msg_type        IN   VARCHAR2,
      pin_error_sqlcode   IN   VARCHAR2,
      pin_error_sqlerm    IN   VARCHAR2
   )
      RETURN VARCHAR2
   IS
      l_error_msg   VARCHAR2 (2000);
   BEGIN
      l_error_msg :=
            pin_msg_type
         || '  '
         || ' slqcode: '
         || pin_error_sqlcode
         || ' sqlerm: '
         || pin_error_sqlerm
         || ' Transaction Type=>'
         || raw_rec_tab (1)
         || CHR (10)
         || ' ATTRIBUTE  COLUMN_NAME   VALUE'
         || CHR (10)
         || ' Store ID  ID_STR_RT=>'
         || raw_rec_tab (2)
         || CHR (10)
         || ' EVH Event ID  ID_EVT_PRC_CHN=>'
         || raw_rec_tab (3)
         || CHR (10)
         || ' Event Type TY_PRC_CHN=>'
         || raw_rec_tab (4)
         || CHR (10)
         || ' Event Sign Flag FL_SGN_EVT_PRC=>'
         || raw_rec_tab (5)
         || CHR (10)
         || ' Override Indicator FL_OVRD=>'
         || raw_rec_tab (6)
         || CHR (10)
         || ' Event Begin Timestamp DC_EFF_CUR_SLS=>'
         || raw_rec_tab (7)
         || CHR (10)
         || ' Event End Timestamp DC_EXP_CUR_SLS=>'
         || raw_rec_tab (8)
         || CHR (10)
         || ' Event Name NM_EVT_PRC_CHN=>'
         || raw_rec_tab (9)
         || CHR (10)
         || 'EVD Event ID  ID_EVT_PRC_CHN=>'
         || raw_rec_tab (10)
         || CHR (10)
         || ' Item Id ID_ITM=>'
         || raw_rec_tab (11)
         || CHR (10)
         || ' Item Price RP_CUR_SLS=>'
         || raw_rec_tab (12)
         || CHR (10)
         || ' Item Status Code SC_ITM_LOC_PRC=>'
         || raw_rec_tab (13)
         || CHR (10)
         || ' Sale type TY_SLS=>'
         || raw_rec_tab (14)
         || CHR (10);
      DBMS_OUTPUT.put_line (l_error_msg);
      RETURN l_error_msg;
   END display_error_fnc;
BEGIN
   EXECUTE IMMEDIATE ('alter session set nls_date_format= ''YYYY-MM-DD HH24:MI:SS''');

   DBMS_OUTPUT.ENABLE (1000000);
END priceevent;
/
0
 
LVL 14

Expert Comment

by:sathyagiri
Comment Utility
Do you have the right indexes on your tables?
0
 

Author Comment

by:dplinnane
Comment Utility
CREATE TABLE PS_ITM_LOC_PRC
(
  ID_ITM              VARCHAR2(14 BYTE)         NOT NULL,
  ID_STR_RT           CHAR(5 BYTE)              NOT NULL,
  ID_EVT_PRC_CHN      INTEGER                   NOT NULL,
  TY_PRC_CHN          VARCHAR2(20 BYTE)         NOT NULL,
  NM_EVT_PRC_CHN      VARCHAR2(40 BYTE),
  DE_RSN_EVT_PRC_CHN  VARCHAR2(255 BYTE),
  RP_CUR_SLS          NUMBER(13,2),
  DC_EFF_CUR_SLS      TIMESTAMP(6)              NOT NULL,
  DC_EXP_CUR_SLS      TIMESTAMP(6),
  TY_SLS              VARCHAR2(20 BYTE),
  SC_ITM_LOC_PRC      VARCHAR2(10 BYTE),
  FL_OVRD             CHAR(1 BYTE)              DEFAULT '0',
  FL_SGN_EVT_PRC      CHAR(1 BYTE)              DEFAULT '0'                   NOT NULL,
  SC_EFF              VARCHAR2(10 BYTE)         DEFAULT '0',
  SC_EXP              VARCHAR2(10 BYTE)         DEFAULT '0',
  SC_UPD              VARCHAR2(10 BYTE)         DEFAULT '0',
  TS_CRT_RCRD         TIMESTAMP(6),
  TS_MDF_RCRD         TIMESTAMP(6)
)
TABLESPACE SYSTEM
PCTUSED    40
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;


ALTER TABLE PS_ITM_LOC_PRC ADD (
  PRIMARY KEY (ID_ITM, ID_STR_RT, ID_EVT_PRC_CHN)
    USING INDEX
    TABLESPACE SYSTEM
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       2147483645
                PCTINCREASE      0
                FREELISTS        1
                FREELIST GROUPS  1
               ));


0
 
LVL 14

Expert Comment

by:sathyagiri
Comment Utility
Try including the append hint in your index

INSERT /*+ APPEND */ INTO TABLE VALUES()
0
 
LVL 19

Expert Comment

by:actonwang
Comment Utility
Try not easy to just add some indexes.

I suggest that you execute your procedure in small steps and try to spot which portion causes problem.

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 19

Expert Comment

by:actonwang
Comment Utility
>>INSERT /*+ APPEND */ INTO TABLE VALUES()

      /*+ APPEND */ not working on your INSERT statement as "insert into ... values ... ". It will have no effect on your procedure.

     
     
     


   
0
 

Author Comment

by:dplinnane
Comment Utility
I tried the insert append, makes no difference. Probably loading 10 recs a min at the moment.  The fact that is being called on the command thru java should that make any difference. When I run it directly passing in an xml string instead of a file its a lot quicker but I am limited copying an pasting a huge string in my pl/sql procedure.

Below is my init.ora params, I'm wondering is there anything obvious there.  

O7_DICTIONARY_ACCESSIBILITY      FALSE      YES      Version 7 Dictionary Accessibility Support      NO      NO      FALSE
active_instance_count            YES      number of active instances in the cluster database      NO      NO      FALSE
aq_tm_processes      0      YES      number of AQ Time Managers to start      NO      YES      FALSE
archive_lag_target      0      YES      Maximum number of seconds of redos the standby could lose      NO      YES      FALSE
asm_diskgroups            YES      disk groups to mount automatically      NO      YES      FALSE
asm_diskstring            YES      disk set locations for discovery      NO      YES      FALSE
asm_power_limit      1      YES      number of processes for disk rebalancing      YES      YES      FALSE
audit_file_dest      D:\ORACLEXE\APP\ORACLE\ADMIN\XE\ADUMP      NO      Directory in which auditing files are to reside      NO      YES      TRUE
audit_sys_operations      FALSE      YES      enable sys auditing      NO      NO      FALSE
audit_trail      NONE      YES      enable system auditing      NO      NO      FALSE
background_core_dump      partial      YES      Core Size for Background Processes      NO      NO      FALSE
background_dump_dest      D:\ORACLEXE\APP\ORACLE\ADMIN\XE\BDUMP      NO      Detached process dump directory      NO      YES      FALSE
backup_tape_io_slaves      FALSE      YES      BACKUP Tape I/O slaves      NO      YES      TRUE
bitmap_merge_area_size      1048576      YES      maximum memory allow for BITMAP MERGE      NO      NO      FALSE
blank_trimming      FALSE      YES      blank trimming semantics parameter      NO      NO      FALSE
buffer_pool_keep            YES      Number of database blocks/latches in keep buffer pool      NO      NO      FALSE
buffer_pool_recycle            YES      Number of database blocks/latches in recycle buffer pool      NO      NO      FALSE
circuits            YES      max number of circuits      NO      YES      FALSE
cluster_database      FALSE      YES      if TRUE startup in cluster database mode      NO      NO      FALSE
cluster_database_instances      1      YES      number of instances to use for sizing cluster db SGA structures      NO      NO      FALSE
cluster_interconnects            YES      interconnects for RAC use      NO      NO      FALSE
commit_point_strength      1      YES      Bias this node has toward not preparing in a two-phase commit      NO      NO      FALSE
commit_write            YES      transaction commit log write behaviour      YES      YES      FALSE
compatible      10.2.0.1.0      NO      Database will be completely compatible with this software version      NO      NO      FALSE
control_file_record_keep_time      7      YES      control file record keep time in days      NO      YES      FALSE
control_files      D:\ORACLEXE\ORADATA\XE\CONTROL.DBF      NO      control file names list      NO      NO      FALSE
core_dump_dest      D:\ORACLEXE\APP\ORACLE\ADMIN\XE\CDUMP      NO      Core dump directory      NO      YES      FALSE
cpu_count      1      YES      number of CPUs for this instance      NO      YES      FALSE
create_bitmap_area_size      8388608      YES      size of create bitmap buffer for bitmap index      NO      NO      FALSE
create_stored_outlines            YES      create stored outlines for DML statements      YES      YES      FALSE
cursor_sharing      EXACT      YES      cursor sharing mode      YES      YES      FALSE
cursor_space_for_time      FALSE      YES      use more memory in order to get faster execution      NO      NO      FALSE
db_16k_cache_size      0      YES      Size of cache for 16K buffers      NO      YES      FALSE
db_2k_cache_size      0      YES      Size of cache for 2K buffers      NO      YES      FALSE
db_32k_cache_size      0      YES      Size of cache for 32K buffers      NO      YES      FALSE
db_4k_cache_size      0      YES      Size of cache for 4K buffers      NO      YES      FALSE
db_8k_cache_size      0      YES      Size of cache for 8K buffers      NO      YES      FALSE
db_block_buffers      0      YES      Number of database blocks cached in memory      NO      NO      FALSE
db_block_checking      FALSE      YES      header checking and data and index block checking      YES      YES      FALSE
db_block_checksum      TRUE      YES      store checksum in db blocks and check during reads      NO      YES      FALSE
db_block_size      8192      YES      Size of database block in bytes      NO      NO      FALSE
db_cache_advice      ON      YES      Buffer cache sizing advisory      NO      YES      FALSE
db_cache_size      0      YES      Size of DEFAULT buffer pool for standard block size buffers      NO      YES      FALSE
db_create_file_dest            YES      default database location      YES      YES      FALSE
db_create_online_log_dest_1            YES      online log/controlfile destination #1      YES      YES      FALSE
db_create_online_log_dest_2            YES      online log/controlfile destination #2      YES      YES      FALSE
db_create_online_log_dest_3            YES      online log/controlfile destination #3      YES      YES      FALSE
db_create_online_log_dest_4            YES      online log/controlfile destination #4      YES      YES      FALSE
db_create_online_log_dest_5            YES      online log/controlfile  destination #5      YES      YES      FALSE
db_domain            YES      directory part of global database name stored with CREATE DATABASE      NO      NO      FALSE
db_file_multiblock_read_count      128      YES      db block to be read each IO      YES      YES      FALSE
db_file_name_convert            YES      datafile name convert patterns and strings for standby/clone db      YES      NO      FALSE
db_files      200      YES      max allowable # db files      NO      NO      FALSE
db_flashback_retention_target      1440      YES      Maximum Flashback Database log retention time in minutes.      NO      YES      FALSE
db_keep_cache_size      0      YES      Size of KEEP buffer pool for standard block size buffers      NO      YES      FALSE
db_name      XE      NO      database name specified in CREATE DATABASE      NO      NO      FALSE
db_recovery_file_dest      D:\oraclexe\app\oracle\flash_recovery_area      NO      default database recovery file location      NO      YES      FALSE
db_recovery_file_dest_size      10737418240      NO      database recovery files size limit      NO      YES      FALSE
db_recycle_cache_size      0      YES      Size of RECYCLE buffer pool for standard block size buffers      NO      YES      FALSE
db_unique_name      XE      YES      Database Unique Name      NO      NO      FALSE
db_writer_processes      1      YES      number of background database writer  processes to start      NO      NO      FALSE
dbwr_io_slaves      0      YES      DBWR I/O slaves      NO      NO      FALSE
ddl_wait_for_locks      FALSE      YES      Disable NOWAIT DML lock acquisitions      YES      YES      FALSE
dg_broker_config_file1      D:\ORACLEXE\APP\ORACLE\PRODUCT\10.2.0\SERVER\DATABASE\DR1XE.DAT      YES      data guard broker configuration file #1      NO      YES      FALSE
dg_broker_config_file2      D:\ORACLEXE\APP\ORACLE\PRODUCT\10.2.0\SERVER\DATABASE\DR2XE.DAT      YES      data guard broker configuration file #2      NO      YES      FALSE
dg_broker_start      FALSE      YES      start Data Guard broker framework (DMON process)      NO      YES      FALSE
disk_asynch_io      TRUE      YES      Use asynch I/O for random access devices      NO      NO      FALSE
dispatchers      (PROTOCOL=TCP) (SERVICE=XEXDB)      NO      specifications of dispatchers      NO      YES      FALSE
distributed_lock_timeout      60      YES      number of seconds a distributed transaction waits for a lock      NO      NO      FALSE
dml_locks      212      YES      dml locks - one for each table modified in a transaction      NO      NO      FALSE
drs_start      FALSE      YES      start DG Broker monitor (DMON process)      NO      YES      FALSE
event            YES      debug event control - default null string      NO      NO      FALSE
fal_client            YES      FAL client      NO      YES      FALSE
fal_server            YES      FAL server list      NO      YES      FALSE
fast_start_io_target      0      YES      Upper bound on recovery reads      NO      YES      FALSE
fast_start_mttr_target      0      YES      MTTR target of forward crash recovery in seconds      NO      YES      FALSE
fast_start_parallel_rollback      LOW      YES      max number of parallel recovery slaves that may be used      NO      YES      FALSE
file_mapping      FALSE      YES      enable file mapping      NO      YES      FALSE
fileio_network_adapters            YES      Network Adapters for File I/O      NO      NO      FALSE
filesystemio_options            YES      IO operations on filesystem files      NO      NO      FALSE
fixed_date            YES      fixed SYSDATE value      NO      YES      FALSE
gc_files_to_locks            YES      mapping between file numbers and global cache locks      NO      NO      FALSE
gcs_server_processes      0      YES      number of background gcs server processes to start      NO      NO      FALSE
global_context_pool_size            YES      Global Application Context Pool Size in Bytes      NO      NO      FALSE
global_names      FALSE      YES      enforce that database links have same name as remote database      YES      YES      FALSE
hash_area_size      131072      YES      size of in-memory hash work area      YES      NO      FALSE
hi_shared_memory_address      0      YES      SGA starting address (high order 32-bits on 64-bit platforms)      NO      NO      FALSE
hs_autoregister      TRUE      YES      enable automatic server DD updates in HS agent self-registration      NO      YES      FALSE
ifile            YES      include file in init.ora      NO      NO      FALSE
instance_groups            YES      list of instance group names      NO      NO      FALSE
instance_name      xe      YES      instance name supported by the instance      NO      NO      FALSE
instance_number      0      YES      instance number      NO      NO      FALSE
instance_type      RDBMS      YES      type of instance to be executed      NO      NO      FALSE
java_max_sessionspace_size      0      YES      max allowed size in bytes of a Java sessionspace      NO      NO      FALSE
java_pool_size      0      YES      size in bytes of java pool      NO      YES      FALSE
java_soft_sessionspace_limit      0      YES      warning limit on size in bytes of a Java sessionspace      NO      NO      FALSE
job_queue_processes      4      NO      number of job queue slave processes      NO      YES      FALSE
large_pool_size      0      YES      size in bytes of large pool      NO      YES      FALSE
ldap_directory_access      NONE      YES      RDBMS's LDAP access option      NO      YES      FALSE
license_max_sessions      0      YES      maximum number of non-system user sessions allowed      NO      YES      FALSE
license_max_users      0      YES      maximum number of named users that can be created in the database      NO      YES      FALSE
license_sessions_warning      0      YES      warning level for number of non-system user sessions      NO      YES      FALSE
local_listener            YES      local listener      NO      YES      FALSE
lock_name_space            YES      lock name space used for generating lock names for standby/clone database      NO      NO      FALSE
lock_sga      FALSE      YES      Lock entire SGA in physical memory      NO      NO      FALSE
log_archive_config            YES      log archive config parameter      NO      YES      FALSE
log_archive_dest            YES      archival destination text string      NO      YES      FALSE
log_archive_dest_1            YES      archival destination #1 text string      YES      YES      FALSE
log_archive_dest_10            YES      archival destination #10 text string      YES      YES      FALSE
log_archive_dest_2            YES      archival destination #2 text string      YES      YES      FALSE
log_archive_dest_3            YES      archival destination #3 text string      YES      YES      FALSE
log_archive_dest_4            YES      archival destination #4 text string      YES      YES      FALSE
log_archive_dest_5            YES      archival destination #5 text string      YES      YES      FALSE
log_archive_dest_6            YES      archival destination #6 text string      YES      YES      FALSE
log_archive_dest_7            YES      archival destination #7 text string      YES      YES      FALSE
log_archive_dest_8            YES      archival destination #8 text string      YES      YES      FALSE
log_archive_dest_9            YES      archival destination #9 text string      YES      YES      FALSE
log_archive_dest_state_1      enable      YES      archival destination #1 state text string      YES      YES      FALSE
log_archive_dest_state_10      enable      YES      archival destination #10 state text string      YES      YES      FALSE
log_archive_dest_state_2      enable      YES      archival destination #2 state text string      YES      YES      FALSE
log_archive_dest_state_3      enable      YES      archival destination #3 state text string      YES      YES      FALSE
log_archive_dest_state_4      enable      YES      archival destination #4 state text string      YES      YES      FALSE
log_archive_dest_state_5      enable      YES      archival destination #5 state text string      YES      YES      FALSE
log_archive_dest_state_6      enable      YES      archival destination #6 state text string      YES      YES      FALSE
log_archive_dest_state_7      enable      YES      archival destination #7 state text string      YES      YES      FALSE
log_archive_dest_state_8      enable      YES      archival destination #8 state text string      YES      YES      FALSE
log_archive_dest_state_9      enable      YES      archival destination #9 state text string      YES      YES      FALSE
log_archive_duplex_dest            YES      duplex archival destination text string      NO      YES      FALSE
log_archive_format      ARC%S_%R.%T      YES      archival destination format      NO      NO      FALSE
log_archive_local_first      TRUE      YES      Establish EXPEDITE attribute default value      NO      YES      FALSE
log_archive_max_processes      2      YES      maximum number of active ARCH processes      NO      YES      FALSE
log_archive_min_succeed_dest      1      YES      minimum number of archive destinations that must succeed      YES      YES      FALSE
log_archive_start      FALSE      YES      start archival process on SGA initialization      NO      NO      FALSE
log_archive_trace      0      YES      Establish archivelog operation tracing level      NO      YES      FALSE
log_buffer      2859008      YES      redo circular buffer size      NO      NO      FALSE
log_checkpoint_interval      0      YES      # redo blocks checkpoint threshold      NO      YES      FALSE
log_checkpoint_timeout      1800      YES      Maximum time interval between checkpoints in seconds      NO      YES      FALSE
log_checkpoints_to_alert      FALSE      YES      log checkpoint begin/end to alert file      NO      YES      FALSE
log_file_name_convert            YES      logfile name convert patterns and strings for standby/clone db      NO      NO      FALSE
logmnr_max_persistent_sessions      1      YES      maximum number of threads to mine      NO      NO      FALSE
max_commit_propagation_delay      0      YES      Max age of new snapshot in .01 seconds      NO      NO      FALSE
max_dispatchers            YES      max number of dispatchers      NO      YES      FALSE
max_dump_file_size      UNLIMITED      YES      Maximum size (blocks) of dump file      YES      YES      FALSE
max_enabled_roles      150      YES      max number of roles a user can have enabled      NO      NO      FALSE
max_shared_servers            YES      max number of shared servers      NO      YES      FALSE
object_cache_max_size_percent      10      YES      percentage of maximum size over optimal of the user session's object cache      YES      YES      TRUE
object_cache_optimal_size      102400      YES      optimal size of the user session's object cache in bytes      YES      YES      TRUE
olap_page_pool_size      0      YES      size of the olap page pool in bytes      YES      YES      TRUE
open_cursors      300      NO      max # cursors per session      NO      YES      FALSE
open_links      4      YES      max # open links per session      NO      NO      FALSE
open_links_per_instance      4      YES      max # open links per instance      NO      NO      FALSE
optimizer_dynamic_sampling      2      YES      optimizer dynamic sampling      YES      YES      FALSE
optimizer_features_enable      10.2.0.1      YES      optimizer plan compatibility parameter      YES      YES      FALSE
optimizer_index_caching      0      YES      optimizer percent index caching      YES      YES      FALSE
optimizer_index_cost_adj      100      YES      optimizer index cost adjustment      YES      YES      FALSE
optimizer_mode      ALL_ROWS      YES      optimizer mode      YES      YES      FALSE
optimizer_secure_view_merging      TRUE      YES      optimizer secure view merging and predicate pushdown/movearound      NO      YES      FALSE
os_authent_prefix            NO      prefix for auto-logon accounts      NO      NO      FALSE
os_roles      FALSE      YES      retrieve roles from the operating system      NO      NO      FALSE
parallel_adaptive_multi_user      TRUE      YES      enable adaptive setting of degree for multiple user streams      NO      YES      FALSE
parallel_automatic_tuning      FALSE      YES      enable intelligent defaults for parallel execution parameters      NO      NO      FALSE
parallel_execution_message_size      2148      YES      message buffer size for parallel execution      NO      NO      FALSE
parallel_instance_group            YES      instance group to use for all parallel operations      YES      YES      FALSE
parallel_max_servers      0      YES      maximum parallel query servers per instance      NO      YES      FALSE
parallel_min_percent      0      YES      minimum percent of threads required for parallel query      YES      NO      FALSE
parallel_min_servers      0      YES      minimum parallel query servers per instance      NO      YES      FALSE
parallel_server      FALSE      YES      if TRUE startup in parallel server mode      NO      NO      FALSE
parallel_server_instances      1      YES      number of instances to use for sizing OPS SGA structures      NO      NO      FALSE
parallel_threads_per_cpu      2      YES      number of parallel execution threads per CPU      NO      YES      FALSE
pga_aggregate_target      199229440      NO      Target size for the aggregate PGA memory consumed by the instance      NO      YES      FALSE
plsql_ccflags            YES      PL/SQL ccflags      YES      YES      FALSE
plsql_code_type      INTERPRETED      YES      PL/SQL code-type      YES      YES      FALSE
plsql_compiler_flags      INTERPRETED, NON_DEBUG      YES      PL/SQL compiler flags      YES      YES      FALSE
plsql_debug      FALSE      YES      PL/SQL debug      YES      YES      FALSE
plsql_native_library_dir            YES      plsql native library dir      NO      YES      FALSE
plsql_native_library_subdir_count      0      YES      plsql native library number of subdirectories      NO      YES      FALSE
plsql_optimize_level      2      YES      PL/SQL optimize level      YES      YES      FALSE
plsql_v2_compatibility      FALSE      YES      PL/SQL version 2.x compatibility flag      YES      YES      FALSE
plsql_warnings      DISABLE:ALL      YES      PL/SQL compiler warnings settings      YES      YES      FALSE
pre_page_sga      FALSE      YES      pre-page sga for process      NO      NO      FALSE
processes      40      YES      user processes      NO      NO      FALSE
query_rewrite_enabled      TRUE      YES      allow rewrite of queries using materialized views if enabled      YES      YES      FALSE
query_rewrite_integrity      enforced      YES      perform rewrite using materialized views with desired integrity      YES      YES      FALSE
rdbms_server_dn            YES      RDBMS's Distinguished Name      NO      NO      FALSE
read_only_open_delayed      FALSE      YES      if TRUE delay opening of read only files until first access      NO      NO      FALSE
recovery_parallelism      0      YES      number of server processes to use for parallel recovery      NO      NO      FALSE
recyclebin      on      YES      recyclebin processing      YES      YES      FALSE
remote_archive_enable      TRUE      YES      remote archival enable setting      NO      NO      FALSE
remote_dependencies_mode      TIMESTAMP      YES      remote-procedure-call dependencies mode parameter      YES      YES      FALSE
remote_listener            YES      remote listener      NO      YES      FALSE
remote_login_passwordfile      EXCLUSIVE      NO      password file usage parameter      NO      NO      FALSE
remote_os_authent      FALSE      YES      allow non-secure remote clients to use auto-logon accounts      NO      NO      FALSE
remote_os_roles      FALSE      YES      allow non-secure remote clients to use os roles      NO      NO      FALSE
replication_dependency_tracking      TRUE      YES      tracking dependency for Replication parallel propagation      NO      NO      FALSE
resource_limit      FALSE      YES      master switch for resource limit      NO      YES      FALSE
resource_manager_plan            YES      resource mgr top plan      NO      YES      FALSE
resumable_timeout      0      YES      set resumable_timeout      YES      YES      FALSE
rollback_segments            YES      undo segment list      NO      NO      FALSE
serial_reuse      disable      YES      reuse the frame segments      NO      NO      FALSE
service_names      XE      YES      service names supported by the instance      NO      YES      FALSE
session_cached_cursors      20      YES      Number of cursors to cache in a session.      YES      NO      FALSE
session_max_open_files      10      YES      maximum number of open files allowed per session      NO      NO      FALSE
sessions      49      NO      user and system sessions      NO      NO      FALSE
sga_max_size      599785472      YES      max total SGA size      NO      NO      FALSE
sga_target      599785472      NO      Target size of SGA      NO      YES      FALSE
shadow_core_dump      partial      YES      Core Size for Shadow Processes      NO      NO      FALSE
shared_memory_address      0      YES      SGA starting address (low order 32-bits on 64-bit platforms)      NO      NO      FALSE
shared_pool_reserved_size      7759462      YES      size in bytes of reserved area of shared pool      NO      NO      FALSE
shared_pool_size      0      YES      size in bytes of shared pool      NO      YES      FALSE
shared_server_sessions            YES      max number of shared server sessions      NO      YES      FALSE
shared_servers      4      NO      number of shared servers to start up      NO      YES      FALSE
skip_unusable_indexes      TRUE      YES      skip unusable indexes if set to TRUE      YES      YES      FALSE
smtp_out_server            YES      utl_smtp server and port configuration parameter      YES      YES      FALSE
sort_area_retained_size      0      YES      size of in-memory sort work area retained between fetch calls      YES      YES      TRUE
sort_area_size      65536      YES      size of in-memory sort work area      YES      YES      TRUE
spfile      D:\ORACLEXE\APP\ORACLE\PRODUCT\10.2.0\SERVER\DBS\SPFILEXE.ORA      NO      server parameter file      NO      NO      FALSE
sql92_security      FALSE      YES      require select privilege for searched update/delete      NO      NO      FALSE
sql_trace      FALSE      YES      enable SQL trace      YES      YES      FALSE
sql_version      NATIVE      YES      sql language version parameter for compatibility issues      YES      NO      FALSE
sqltune_category      DEFAULT      YES      Category qualifier for applying hintsets      YES      YES      FALSE
standby_archive_dest      %ORACLE_HOME%\RDBMS      YES      standby database archivelog destination text string      NO      YES      FALSE
standby_file_management      MANUAL      YES      if auto then files are created/dropped automatically on standby      NO      YES      FALSE
star_transformation_enabled      FALSE      YES      enable the use of star transformation      YES      YES      FALSE
statistics_level      TYPICAL      YES      statistics level      YES      YES      FALSE
streams_pool_size      0      YES      size in bytes of the streams pool      NO      YES      FALSE
tape_asynch_io      TRUE      YES      Use asynch I/O requests for tape devices      NO      NO      FALSE
thread      0      YES      Redo thread to mount      NO      YES      FALSE
timed_os_statistics      0      YES      internal os statistic gathering interval in seconds      YES      YES      FALSE
timed_statistics      TRUE      YES      maintain internal timing statistics      YES      YES      FALSE
trace_enabled      TRUE      YES      enable KST tracing      NO      YES      FALSE
tracefile_identifier            YES      trace file custom identifier      YES      NO      FALSE
transactions      53      YES      max. number of concurrent active transactions      NO      NO      FALSE
transactions_per_rollback_segment      5      YES      number of active transactions per rollback segment      NO      NO      FALSE
undo_management      AUTO      NO      instance runs in SMU mode if TRUE, else in RBU mode      NO      NO      FALSE
undo_retention      900      YES      undo retention in seconds      NO      YES      FALSE
undo_tablespace      UNDO      NO      use/switch undo tablespace      NO      YES      FALSE
use_indirect_data_buffers      FALSE      YES      Enable indirect data buffers (very large SGA on 32-bit platforms)      NO      NO      FALSE
user_dump_dest      D:\ORACLEXE\APP\ORACLE\ADMIN\XE\UDUMP      NO      User process dump directory      NO      YES      FALSE
utl_file_dir            YES      utl_file accessible directories list      NO      NO      FALSE
workarea_size_policy      AUTO      YES      policy used to size SQL working areas (MANUAL/AUTO)      YES      YES      FALSE
0
 

Author Comment

by:dplinnane
Comment Utility
I got rid of insert update statement to see what performance was like. Still terrible performance.
--upsert_rec_prc (concat_data_multi_parent_tab, pout_error, pout_error_msg);
0
 
LVL 19

Expert Comment

by:actonwang
Comment Utility
you need to test to boil down to:

(1) which proc takes mose time?
(2) in this proc, which statement takes most time?
0
 

Author Comment

by:dplinnane
Comment Utility
SELECT EXTRACTVALUE (:b1, '/PDATA/EVH[' || :b2 || ']') "EVH"
  FROM DUAL
 WHERE EXISTSNODE (:b1, '/PDATA/EVH') = 1

The following statement
has a total
2009536 disk reads
7119041 buffer gets
9 parse calls

This is while the procedure is stil running without inserts.
0
 
LVL 19

Expert Comment

by:actonwang
Comment Utility
>>2009536 disk reads
 
     wo. that is a hit.
0
 
LVL 19

Expert Comment

by:actonwang
Comment Utility
>>            SELECT EXTRACTVALUE (xmldata, '/PDATA/EVH[' || v || ']') "EVH"
              INTO evh_data
              FROM DUAL
             WHERE EXISTSNODE (xmldata, '/PDATA/EVH') = 1;


are you talking about this one?

how big is your xml data?
0
 
LVL 14

Expert Comment

by:sathyagiri
Comment Utility
Can you store your XML in a temp table and try to parse it out from the table instead of passing it as a param and see if it makes any diff?
0
 

Author Comment

by:dplinnane
Comment Utility
yes
SELECT EXTRACTVALUE (xmldata, '/PDATA/EVH[' || v || ']') "EVH"
              INTO evh_data
              FROM DUAL
             WHERE EXISTSNODE (xmldata, '/PDATA/EVH') = 1;
pretty all the extractvalue clauses are taking time.      
the variable v goes from 1 to n depending on the number of nodes. In a file of about .5 mb there are 2000 nodes. I'm thinking its some ora param.  
Maybe hiiting dual causes a lot of recursive sql.

Xml file is .5 MB   the largest the files will be is 1.5mb

We had an old parsing package which did not use xml db and apparently was quicker that's why I posted about performance of xml db.
I could create a table but I don't think that is solving the problem files of 1.5MB should not be a problem? Should they?

A java application has been written by some other group to run this package so if I start adding table I'm sure it will have to change on there end as well.


C:\java_app>java -cp .;ojdbc14.jar ImportPriceChange XE admin password FlatPriceChangeFeed3.dat
Number of bytes in text file:484312
Exception in thread "main" java.sql.SQLException: ORA-00028: your session has been killed
ORA-00028: your session has been killed

        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
        at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
        at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:215)
        at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:954)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1169)
        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3285)
        at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3390)
        at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4223)
        at ImportPriceChange.main(ImportPriceChange.java:71)

C:\java_app>
0
 

Author Comment

by:dplinnane
Comment Utility
I ran my package on
laptop oracle express windows     1000 recs processed in 3 hours
desktop oracle enterprise windows     5000 recs processed in 20 mins
server racle enterprise linux          5000 recs processed in 6 mins

The following is the offending query.
Is XML db just slow.  We have to laod 64 million recs in 8 hours. Each file will be no larger then 1.5MB
Based on what I have seen so far I doubt if we can load 64000 never mind 64 million. Any ideas or suggestions.

I have placed my code here if anyone want to have a look
http://www.ihos-t.com/code/

The .dat files have to be opened and the xml placed in between singles quotes to pass in the xml data.  Locally I use a java app to call stored procedure.

Should I just build my own parser as we will always only have 4 tags?
0
 
LVL 16

Expert Comment

by:MohanKNair
Comment Utility
>>         FOR v IN 1 .. idx_counter
>>         LOOP
>>            SELECT EXTRACTVALUE (xml_data, '/PDATA/EVH[' || v || ']') "EVH"
>>              INTO evh_data
>>              FROM DUAL
>>             WHERE EXISTSNODE (xml_data, '/PDATA/EVH') = 1;
>>
>>            SELECT EXTRACTVALUE (xml_data, '/PDATA/EVD[' || v || ']') "EVD"
>>              INTO evd_data
>>              FROM DUAL
>>             WHERE EXISTSNODE (xml_data, '/PDATA/EVD') = 1;
>>
>>            --evd_parent_val_tab.EXTEND;
>>            evd_parent_val_tab := parse_str_fnc (evd_data, pin_detail_delim);


The statement is repeated
0
 
LVL 16

Expert Comment

by:MohanKNair
Comment Utility
>> laptop oracle express windows     1000 recs processed in 3 hours
>> desktop oracle enterprise windows     5000 recs processed in 20 mins
>> server racle enterprise linux          5000 recs processed in 6 mins

There is a big difference for laptop windows and Enterprise Linux. There must be some resource bottleneck. When the SP is running in laptop check the increase in CPU usage and DISK IO. If the IO is the bottleneck then use BULK operations to insert/update. Replace seperate insert/update statements by single insert. Make use of local variables to store data for different conditions.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now