5 Replies Latest reply on Dec 18, 2013 9:54 AM by Marco Gralike

    lack of performance in SELECT-ing XML records

    user12117163

      Hello Champs, I am new to XML world. But as a DBA now I'm into the situation to suggest better performance improvement in accessing XML records.

       

      Problem:

      There is a batch job from informatica, fetching records from XML tables(close to 400, one by one) stored in oracle database. All 400 tables just have two columns as described below:

       

      Name                                         Null?          Type

      ----------------------------------------- -     -------         ---------------------------

      RECID                                     NOT NULL VARCHAR2(255)

      XMLRECORD                                            XMLTYPE

       

      Each table has NORMAL index created for it only for VARCHAR2 column:

      CREATE UNIQUE INDEX "username"."Indexname_PK" ON "username"."table_name" ("RECID") PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "tbs_idx" ;

       

      All the table has the CLOB index created for it with below description.

           CHUNK PCTVERSION  RETENTION  FREEPOOLS CACHE      IN_ROW    FORMAT          PAR

            ----------             ---------          ----------             ---------- ----------          ---------    ---------------            ---

            8192                 14400            YES                           YES         ENDIAN     NEUTRAL          NO

       

      Informatica issues below query on all the table, where it could just fetch only 400rows/sec in average. This takes entire buisness day to complete the batch job for all 400 tables, which gives big problem in production environment.

      SELECT <table_name>.<column_name>.getclobval() FROM  <table_name>;

       

      SQL> select * from v$version;

      BANNER
      ----------------------------------------------------------------
      Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
      PL/SQL Release 10.2.0.5.0 - Production
      CORE    10.2.0.5.0      Production
      TNS for IBM/AIX RISC System/6000: Version 10.2.0.5.0 - Productio
      NLSRTL Version 10.2.0.5.0 - Production

       

      Clarification required:

      1. Where do you see in this scenario, there is a problem which blocks the performance?

      2. What type of index on this setup is normally advisable?

       

      Many Thanks for your assistance,