5 Replies Latest reply on Dec 4, 2018 8:54 AM by ChrisJenkins-Oracle

    Spark Application tested with Timesten takes more time than with oracle

    3146278

      I have tested My Apache spark application code written in Java with Timesten (as a cache) it takes more time for writing to 5 tables (45 minutes).

       

      Same application code tested with oracle (without Timesten), it takes less time for writing to same 5 tables (24 minutes).

       

      Why It takes more time with timesten? Tables are same and Code is also same.

       

      Thanks.

        • 1. Re: Spark Application tested with Timesten takes more time than with oracle
          ChrisJenkins-Oracle

          Can you maybe provide some details so we can give an opinion? At a minimum:

           

          1.    The table and index definitions. Is TimesTen acting as a database or a cache for Oracle?

          2.    How you are connecting to TimesTen (local or remote, DSN definition, connection string, ...)

          3.    The code you are using to write the data.

          4.    How are you timing the process?

          5.    What OS and hardware is TimesTen running on?

           

          Thanks,

           

          Chris

          • 2. Re: Spark Application tested with Timesten takes more time than with oracle
            3146278

            Thanks for the reply.

             

            Below are the details :

             

            1.    The table and index definitions. Is TimesTen acting as a database or a cache for Oracle?

             

                              - Timesten is acting as a Cache for oracle

                              - By table and index definitions - do you mean you need DDL for the table?

             

            2.    How you are connecting to TimesTen (local or remote, DSN definition, connection string, ...)

             

                                - I am connecting TimesTen using connection string. : String ttConnection = "jdbc:timesten:client:dsn=ttsparc_N;UID=xxxx;PWD=yyyy";

             

            3.    The code you are using to write the data.

             

                                - txndetailDS.write().mode("append").format("jdbc").option("url", ttConnection).option("dbtable", "CI_TXN_DETAIL").save();

             

            4.    How are you timing the process?

             

                                 - sorry i am not able to understand this question, can you please elaborate?

             

            5.    What OS and hardware is TimesTen running on?

             

                                - Timesten is running on UNIX.

                                    Below is disk and memory info.

             

            -bash-4.1$ df

            Filesystem            1K-blocks       Used  Available Use% Mounted on

            /dev/xvda2             51475068   23934992   24902252  50% /

            tmpfs                  62286848          0   62286848   0% /dev/shm

            /dev/xvda1               999320     508328     438564  54% /boot

            /dev/xvda5              5029504     902548    3848428  19% /oem

            /dev/mapper/DomUVol-scratch

                                 1666004512   88229184 1493124116   6% /scratch

            mumnas402:/export/home1/vvpawar

                                 4284300400  272013808 4012286592   7% /home/vvpawar

            mumnas402.in.oracle.com:/export/local/x86_64/redhat/60_prod/packages

                                 5368431360 4410014464  958416896  83% /usr/local/remote/packages

             

             

            -bash-4.1$ free

                         total       used       free     shared    buffers     cached

            Mem:      61962612   61214416     748196    8614472     766856   55370472

            • 3. Re: Spark Application tested with Timesten takes more time than with oracle
              ChrisJenkins-Oracle

              Hi,

               

              Yes, I meant I would like to see the DDL used to define the cache table (and any indexes on it) in TimesTen.

               

              Also, what are your DSN parameters for the TimesTen cache database.

               

              With regard to

               

              txndetailDS.write().mode("append").format("jdbc").option("url", ttConnection).option("dbtable", "CI_TXN_DETAIL").save();

               

              this doesn't tell me anything. What does this code do under the covers? Are these methods something you have implemented or are they part of some framework or...?

               

              Thanks,

               

              Chris

               

              • 4. Re: Spark Application tested with Timesten takes more time than with oracle
                3146278

                Hi Chris,

                 

                Sorry for delay , below are the DDLs for 5 tables which were created in Timesten cache, indexes were not created.

                 

                1) CREATE ASYNCHRONOUS WRITETHROUGH CACHE GROUP CACHEUSER.CI_PRICEITEM_PARM_GRP FROM CI_PRICEITEM_PARM_GRP ("PRICEITEM_PARM_GRP_ID" NUMBER(22,0) NOT NULL ENABLE,"PRICEITEM_PARM_CD" VARCHAR2(30) NOT NULL ENABLE,

                "PRICEITEM_PARM_VAL" VARCHAR2(254),PRIMARY KEY ("PRICEITEM_PARM_GRP_ID", "PRICEITEM_PARM_CD"));

                 

                2) CREATE ASYNCHRONOUS WRITETHROUGH CACHE GROUP CACHEUSER.CI_PRICEITEM_PARM_GRP_K FROM CI_PRICEITEM_PARM_GRP_K ( "PRICEITEM_PARM_GRP_ID" NUMBER(22,0) NOT NULL ENABLE,"PARM_COUNT" NUMBER(20,0) NOT NULL ENABLE,

                "PARM_STR" VARCHAR2(4000),PRIMARY KEY ("PRICEITEM_PARM_GRP_ID"));

                 

                3) CREATE ASYNCHRONOUS WRITETHROUGH CACHE GROUP CACHEUSER.CI_TXN_DETAIL FROM CI_TXN_DETAIL

                ( "TXN_DETAIL_ID" NUMBER(15,0) NOT NULL ENABLE,

                  "TXN_HEADER_ID" CHAR(14) NOT NULL ENABLE,

                  "TXN_SOURCE_CD" VARCHAR2(30) NOT NULL ENABLE,

                  "TXN_REC_TYPE_CD" VARCHAR2(30) NOT NULL ENABLE,

                  "TXN_DTTM" DATE NOT NULL ENABLE,

                  "EXT_TXN_NBR" VARCHAR2(256) DEFAULT ' ' NOT NULL ENABLE,

                  "CUST_REF_NBR" VARCHAR2(256) DEFAULT ' ' NOT NULL ENABLE,

                  "CIS_DIVISION" CHAR(5) NOT NULL ENABLE,

                  "ACCT_ID" CHAR(10) NOT NULL ENABLE,

                  "TXN_VOL" NUMBER(36,18) DEFAULT 0 NOT NULL ENABLE,

                  "TXN_AMT" NUMBER(36,18) DEFAULT 0 NOT NULL ENABLE,

                  "CURRENCY_CD" CHAR(3) DEFAULT ' ' NOT NULL ENABLE,

                  "MANUAL_SW" CHAR(1) DEFAULT 'N' NOT NULL ENABLE,

                  "USER_ID" CHAR(8) NOT NULL ENABLE,

                  "HOW_TO_USE_TXN_FLG" CHAR(1) DEFAULT ' ' NOT NULL ENABLE,

                  "MESSAGE_CAT_NBR" NUMBER(5,0) DEFAULT 0 NOT NULL ENABLE,

                  "MESSAGE_NBR" NUMBER(5,0) DEFAULT 0 NOT NULL ENABLE,

                  "UDF_CHAR_1" VARCHAR2(60) DEFAULT ' ' NOT NULL ENABLE,

                  "UDF_CHAR_2" VARCHAR2(60) DEFAULT ' ' NOT NULL ENABLE,

                  "UDF_CHAR_3" VARCHAR2(60) DEFAULT ' ' NOT NULL ENABLE,

                  "UDF_CHAR_4" VARCHAR2(60) DEFAULT ' ' NOT NULL ENABLE,

                  "UDF_CHAR_5" VARCHAR2(60) DEFAULT ' ' NOT NULL ENABLE,

                  "UDF_CHAR_6" VARCHAR2(60) DEFAULT ' ' NOT NULL ENABLE,

                  "UDF_CHAR_7" VARCHAR2(60) DEFAULT ' ' NOT NULL ENABLE,

                  "UDF_CHAR_8" VARCHAR2(60) DEFAULT ' ' NOT NULL ENABLE,

                  "UDF_CHAR_9" VARCHAR2(60) DEFAULT ' ' NOT NULL ENABLE,

                  "UDF_CHAR_10" VARCHAR2(60) DEFAULT ' ' NOT NULL ENABLE,

                  "UDF_CHAR_11" VARCHAR2(60) DEFAULT ' ' NOT NULL ENABLE,

                  "UDF_CHAR_12" VARCHAR2(60) DEFAULT ' ' NOT NULL ENABLE,

                  "UDF_CHAR_13" VARCHAR2(60) DEFAULT ' ' NOT NULL ENABLE,

                  "UDF_CHAR_14" VARCHAR2(60) DEFAULT ' ' NOT NULL ENABLE,

                  "UDF_CHAR_15" VARCHAR2(60) DEFAULT ' ' NOT NULL ENABLE,

                  "UDF_NBR_1" NUMBER(36,18) DEFAULT 0 NOT NULL ENABLE,

                  "UDF_NBR_2" NUMBER(36,18) DEFAULT 0 NOT NULL ENABLE,

                  "UDF_NBR_3" NUMBER(36,18) DEFAULT 0 NOT NULL ENABLE,

                  "UDF_NBR_4" NUMBER(36,18) DEFAULT 0 NOT NULL ENABLE,

                  "UDF_NBR_5" NUMBER(36,18) DEFAULT 0 NOT NULL ENABLE,

                  "UDF_NBR_6" NUMBER(36,18) DEFAULT 0 NOT NULL ENABLE,

                  "UDF_NBR_7" NUMBER(36,18) DEFAULT 0 NOT NULL ENABLE,

                  "UDF_NBR_8" NUMBER(36,18) DEFAULT 0 NOT NULL ENABLE,

                  "UDF_NBR_9" NUMBER(36,18) DEFAULT 0 NOT NULL ENABLE,

                  "UDF_NBR_10" NUMBER(36,18) DEFAULT 0 NOT NULL ENABLE,

                  "UDF_AMT_1" NUMBER(36,18) DEFAULT 0 NOT NULL ENABLE,

                  "UDF_CURRENCY_CD_1" CHAR(3) DEFAULT ' ' NOT NULL ENABLE,

                  "UDF_AMT_2" NUMBER(36,18) DEFAULT 0 NOT NULL ENABLE,

                  "UDF_CURRENCY_CD_2" CHAR(3) DEFAULT ' ' NOT NULL ENABLE,

                  "UDF_AMT_3" NUMBER(36,18) DEFAULT 0 NOT NULL ENABLE,

                  "UDF_CURRENCY_CD_3" CHAR(3) DEFAULT ' ' NOT NULL ENABLE,

                  "UDF_AMT_4" NUMBER(36,18) DEFAULT 0 NOT NULL ENABLE,

                  "UDF_CURRENCY_CD_4" CHAR(3) DEFAULT ' ' NOT NULL ENABLE,

                  "UDF_AMT_5" NUMBER(36,18) DEFAULT 0 NOT NULL ENABLE,

                  "UDF_CURRENCY_CD_5" CHAR(3) DEFAULT ' ' NOT NULL ENABLE,

                  "UDF_DTTM_1" DATE,

                  "UDF_DTTM_2" DATE,

                  "UDF_DTTM_3" DATE,

                  "UDF_DTTM_4" DATE,

                  "UDF_DTTM_5" DATE,

                  "BUS_OBJ_CD" CHAR(30) DEFAULT ' ' NOT NULL ENABLE,

                  "BO_STATUS_CD" CHAR(12) DEFAULT ' ' NOT NULL ENABLE,

                  "STATUS_UPD_DTTM" DATE,

                  "VERSION" NUMBER(5,0) DEFAULT 1 NOT NULL ENABLE,

                  "DO_NOT_AGG_SW" CHAR(1) DEFAULT 'Y' NOT NULL ENABLE,

                  "TXN_UPLOAD_DTTM" DATE,

                  "ACCT_NBR_TYPE_CD" CHAR(8) DEFAULT ' ' NOT NULL ENABLE,

                  "ACCT_NBR" VARCHAR2(30) DEFAULT ' ' NOT NULL ENABLE,

                  "UDF_CHAR_16" VARCHAR2(60) DEFAULT ' ' NOT NULL ENABLE,

                  "UDF_CHAR_17" VARCHAR2(60) DEFAULT ' ' NOT NULL ENABLE,

                  "UDF_CHAR_18" VARCHAR2(60) DEFAULT ' ' NOT NULL ENABLE,

                  "UDF_CHAR_19" VARCHAR2(60) DEFAULT ' ' NOT NULL ENABLE,

                  "UDF_CHAR_20" VARCHAR2(60) DEFAULT ' ' NOT NULL ENABLE,

                  "UDF_CHAR_21" VARCHAR2(60) DEFAULT ' ' NOT NULL ENABLE,

                  "UDF_CHAR_22" VARCHAR2(60) DEFAULT ' ' NOT NULL ENABLE,

                  "UDF_CHAR_23" VARCHAR2(60) DEFAULT ' ' NOT NULL ENABLE,

                  "UDF_CHAR_24" VARCHAR2(60) DEFAULT ' ' NOT NULL ENABLE,

                  "UDF_CHAR_25" VARCHAR2(60) DEFAULT ' ' NOT NULL ENABLE,

                  "RULE_CD" CHAR(12) DEFAULT ' ' NOT NULL ENABLE,

                  "MESSAGE_DESC" VARCHAR2(300),

                  "DISAGG_SW" CHAR(1) DEFAULT 'N' NOT NULL ENABLE,

                  "DISAGG_CNT" NUMBER(3,0) DEFAULT 0,

                  "PROCESSING_DT" DATE,

                  "LAST_SYS_PRCS_DT" DATE,

                  "CURR_SYS_PRCS_DT" DATE NOT NULL ENABLE,

                  "UDF_CHAR_26" VARCHAR2(60) DEFAULT ' ' NOT NULL ENABLE,

                  "UDF_CHAR_27" VARCHAR2(60) DEFAULT ' ' NOT NULL ENABLE,

                  "UDF_CHAR_28" VARCHAR2(60) DEFAULT ' ' NOT NULL ENABLE,

                  "UDF_CHAR_29" VARCHAR2(60) DEFAULT ' ' NOT NULL ENABLE,

                  "UDF_CHAR_30" VARCHAR2(60) DEFAULT ' ' NOT NULL ENABLE,

                  "UDF_CHAR_31" VARCHAR2(60) DEFAULT ' ' NOT NULL ENABLE,

                  "UDF_CHAR_32" VARCHAR2(60) DEFAULT ' ' NOT NULL ENABLE,

                  "UDF_CHAR_33" VARCHAR2(60) DEFAULT ' ' NOT NULL ENABLE,

                  "UDF_CHAR_34" VARCHAR2(60) DEFAULT ' ' NOT NULL ENABLE,

                  "UDF_CHAR_35" VARCHAR2(60) DEFAULT ' ' NOT NULL ENABLE,

                  "UDF_CHAR_36" VARCHAR2(60) DEFAULT ' ' NOT NULL ENABLE,

                  "UDF_CHAR_37" VARCHAR2(60) DEFAULT ' ' NOT NULL ENABLE,

                  "UDF_CHAR_38" VARCHAR2(60) DEFAULT ' ' NOT NULL ENABLE,

                  "UDF_CHAR_39" VARCHAR2(60) DEFAULT ' ' NOT NULL ENABLE,

                  "UDF_CHAR_40" VARCHAR2(60) DEFAULT ' ' NOT NULL ENABLE,

                  "UDF_CHAR_41" VARCHAR2(60) DEFAULT ' ' NOT NULL ENABLE,

                  "UDF_CHAR_42" VARCHAR2(60) DEFAULT ' ' NOT NULL ENABLE,

                  "UDF_CHAR_43" VARCHAR2(60) DEFAULT ' ' NOT NULL ENABLE,

                  "UDF_CHAR_44" VARCHAR2(60) DEFAULT ' ' NOT NULL ENABLE,

                  "UDF_CHAR_45" VARCHAR2(60) DEFAULT ' ' NOT NULL ENABLE,

                  "UDF_CHAR_46" VARCHAR2(60) DEFAULT ' ' NOT NULL ENABLE,

                  "UDF_CHAR_47" VARCHAR2(60) DEFAULT ' ' NOT NULL ENABLE,

                  "UDF_CHAR_48" VARCHAR2(60) DEFAULT ' ' NOT NULL ENABLE,

                  "UDF_CHAR_49" VARCHAR2(60) DEFAULT ' ' NOT NULL ENABLE,

                  "UDF_CHAR_50" VARCHAR2(60) DEFAULT ' ' NOT NULL ENABLE,

                  "UDF_NBR_11" NUMBER(36,18) DEFAULT 0 NOT NULL ENABLE,

                  "UDF_NBR_12" NUMBER(36,18) DEFAULT 0 NOT NULL ENABLE,

                  "UDF_NBR_13" NUMBER(36,18) DEFAULT 0 NOT NULL ENABLE,

                  "UDF_NBR_14" NUMBER(36,18) DEFAULT 0 NOT NULL ENABLE,

                  "UDF_NBR_15" NUMBER(36,18) DEFAULT 0 NOT NULL ENABLE,

                  "UDF_NBR_16" NUMBER(36,18) DEFAULT 0 NOT NULL ENABLE,

                  "UDF_NBR_17" NUMBER(36,18) DEFAULT 0 NOT NULL ENABLE,

                  "UDF_NBR_18" NUMBER(36,18) DEFAULT 0 NOT NULL ENABLE,

                  "UDF_NBR_19" NUMBER(36,18) DEFAULT 0 NOT NULL ENABLE,

                  "UDF_NBR_20" NUMBER(36,18) DEFAULT 0 NOT NULL ENABLE,

                  "UDF_AMT_6" NUMBER(36,18) DEFAULT 0 NOT NULL ENABLE,

                  "UDF_AMT_7" NUMBER(36,18) DEFAULT 0 NOT NULL ENABLE,

                  "UDF_AMT_8" NUMBER(36,18) DEFAULT 0 NOT NULL ENABLE,

                  "UDF_AMT_9" NUMBER(36,18) DEFAULT 0 NOT NULL ENABLE,

                  "UDF_AMT_10" NUMBER(36,18) DEFAULT 0 NOT NULL ENABLE,

                  "UDF_CURRENCY_CD_6" CHAR(3) DEFAULT ' ' NOT NULL ENABLE,

                  "UDF_CURRENCY_CD_7" CHAR(3) DEFAULT ' ' NOT NULL ENABLE,

                  "UDF_CURRENCY_CD_8" CHAR(3) DEFAULT ' ' NOT NULL ENABLE,

                  "UDF_CURRENCY_CD_9" CHAR(3) DEFAULT ' ' NOT NULL ENABLE,

                  "UDF_CURRENCY_CD_10" CHAR(3) DEFAULT ' ' NOT NULL ENABLE,

                  "ILM_ARCH_SW" CHAR(1) DEFAULT 'N',

                  CONSTRAINT "XF238P0" PRIMARY KEY ("TXN_DETAIL_ID"));

                 

                 

                4) CREATE ASYNCHRONOUS WRITETHROUGH CACHE GROUP CACHEUSER.CI_TXN_DTL_PRITM FROM CI_TXN_DTL_PRITM

                ( "TXN_DETAIL_ID" NUMBER(15,0) NOT NULL ENABLE,

                  "INITIAL_PRICE_ITEM_CD" CHAR(30) NOT NULL ENABLE,

                  "INTIAL_TOU_CD" CHAR(8) DEFAULT ' ' NOT NULL ENABLE,

                  "CIS_DIVISION" CHAR(5) NOT NULL ENABLE,

                  "ACCT_ID" CHAR(10) NOT NULL ENABLE,

                  "VERSION" NUMBER(5,0) DEFAULT 1 NOT NULL ENABLE,

                  "TXN_DTTM" DATE NOT NULL ENABLE,

                  "PRICE_SRCH_DT" DATE,

                  "SEQ_NO" NUMBER(5,0) DEFAULT 1 NOT NULL ENABLE,

                  "TXN_HEADER_ID" CHAR(14) DEFAULT ' ' NOT NULL ENABLE,

                  "TXN_SOURCE_CD" VARCHAR2(30) DEFAULT ' ' NOT NULL ENABLE,

                  "ACCT_NBR_TYPE_CD" CHAR(8) DEFAULT ' ' NOT NULL ENABLE,

                  "ACCT_NBR" VARCHAR2(30) DEFAULT ' ' NOT NULL ENABLE,

                  "BILLABLE_CHG_ID" CHAR(12) DEFAULT ' ' NOT NULL ENABLE,

                  "PRICEITEM_PARM_GRP_ID" NUMBER(22,0),

                  "DO_NOT_AGG_SW" CHAR(1) DEFAULT ' ',

                  "TXN_PRITM_STATUS_CD" NUMBER(3,0),

                  "IS_DISAGG" CHAR(1),

                  "PROCESSING_DT" DATE,

                  "ORG_DIVISION" CHAR(5),

                  "LAST_SYS_PRCS_DT" DATE,

                  "CURR_SYS_PRCS_DT" DATE,

                  "TXN_VOL" NUMBER(36,18) DEFAULT 0 NOT NULL ENABLE,

                  "TXN_AMT" NUMBER(36,18) DEFAULT 0 NOT NULL ENABLE,

                  "CURRENCY_CD" CHAR(3) DEFAULT ' ' NOT NULL ENABLE,

                  "UDF_AMT_1" NUMBER(36,18) DEFAULT 0 NOT NULL ENABLE,

                  "UDF_CURRENCY_CD_1" CHAR(3) DEFAULT ' ' NOT NULL ENABLE,

                  "UDF_AMT_2" NUMBER(36,18) DEFAULT 0 NOT NULL ENABLE,

                  "UDF_CURRENCY_CD_2" CHAR(3) DEFAULT ' ' NOT NULL ENABLE,

                  "UDF_AMT_3" NUMBER(36,18) DEFAULT 0 NOT NULL ENABLE,

                  "UDF_CURRENCY_CD_3" CHAR(3) DEFAULT ' ' NOT NULL ENABLE,

                  "UDF_AMT_4" NUMBER(36,18) DEFAULT 0 NOT NULL ENABLE,

                  "UDF_CURRENCY_CD_4" CHAR(3) DEFAULT ' ' NOT NULL ENABLE,

                  "UDF_AMT_5" NUMBER(36,18) DEFAULT 0 NOT NULL ENABLE,

                  "UDF_CURRENCY_CD_5" CHAR(3) DEFAULT ' ' NOT NULL ENABLE,

                  "UDF_NBR_1" NUMBER(36,18) DEFAULT 0 NOT NULL ENABLE,

                  "UDF_NBR_2" NUMBER(36,18) DEFAULT 0 NOT NULL ENABLE,

                  "UDF_NBR_3" NUMBER(36,18) DEFAULT 0 NOT NULL ENABLE,

                  "UDF_NBR_4" NUMBER(36,18) DEFAULT 0 NOT NULL ENABLE,

                  "UDF_NBR_5" NUMBER(36,18) DEFAULT 0 NOT NULL ENABLE,

                  "UDF_NBR_6" NUMBER(36,18) DEFAULT 0 NOT NULL ENABLE,

                  "UDF_NBR_7" NUMBER(36,18) DEFAULT 0 NOT NULL ENABLE,

                  "UDF_NBR_8" NUMBER(36,18) DEFAULT 0 NOT NULL ENABLE,

                  "UDF_NBR_9" NUMBER(36,18) DEFAULT 0 NOT NULL ENABLE,

                  "UDF_NBR_10" NUMBER(36,18) DEFAULT 0 NOT NULL ENABLE,

                  "AGG_PARM_GRP_ID" NUMBER(22,0) DEFAULT 1,

                  "TXN_RATING_CRITERIA" CHAR(4) DEFAULT null,

                  "TXN_CALC_ID" NUMBER(22,0),

                  "UDF_NBR_11" NUMBER(36,18) DEFAULT 0 NOT NULL ENABLE,

                  "UDF_NBR_12" NUMBER(36,18) DEFAULT 0 NOT NULL ENABLE,

                  "UDF_NBR_13" NUMBER(36,18) DEFAULT 0 NOT NULL ENABLE,

                  "UDF_NBR_14" NUMBER(36,18) DEFAULT 0 NOT NULL ENABLE,

                  "UDF_NBR_15" NUMBER(36,18) DEFAULT 0 NOT NULL ENABLE,

                  "UDF_NBR_16" NUMBER(36,18) DEFAULT 0 NOT NULL ENABLE,

                  "UDF_NBR_17" NUMBER(36,18) DEFAULT 0 NOT NULL ENABLE,

                  "UDF_NBR_18" NUMBER(36,18) DEFAULT 0 NOT NULL ENABLE,

                  "UDF_NBR_19" NUMBER(36,18) DEFAULT 0 NOT NULL ENABLE,

                  "UDF_NBR_20" NUMBER(36,18) DEFAULT 0 NOT NULL ENABLE,

                  "UDF_AMT_6" NUMBER(36,18) DEFAULT 0 NOT NULL ENABLE,

                  "UDF_AMT_7" NUMBER(36,18) DEFAULT 0 NOT NULL ENABLE,

                  "UDF_AMT_8" NUMBER(36,18) DEFAULT 0 NOT NULL ENABLE,

                  "UDF_AMT_9" NUMBER(36,18) DEFAULT 0 NOT NULL ENABLE,

                  "UDF_AMT_10" NUMBER(36,18) DEFAULT 0 NOT NULL ENABLE,

                  "UDF_CURRENCY_CD_6" CHAR(3) DEFAULT ' ' NOT NULL ENABLE,

                  "UDF_CURRENCY_CD_7" CHAR(3) DEFAULT ' ' NOT NULL ENABLE,

                  "UDF_CURRENCY_CD_8" CHAR(3) DEFAULT ' ' NOT NULL ENABLE,

                  "UDF_CURRENCY_CD_9" CHAR(3) DEFAULT ' ' NOT NULL ENABLE,

                  "UDF_CURRENCY_CD_10" CHAR(3) DEFAULT ' ' NOT NULL ENABLE,

                  "ILM_ARCH_SW" CHAR(1) DEFAULT 'N',

                  "SUMM_HASHCODE" NUMBER(10,0),

                  "ID" NUMBER NOT NULL ENABLE,

                  CONSTRAINT "XF240P0" PRIMARY KEY ("ID"));

                 

                 

                5) CREATE ASYNCHRONOUS WRITETHROUGH CACHE GROUP CACHEUSER.CI_TXN_DTL_PRITM_SUMMARY FROM CI_TXN_DTL_PRITM_SUMMARY

                ( "ACCT_ID" CHAR(10) NOT NULL ENABLE,

                  "INIT_PRICEITEM_CD" CHAR(30) NOT NULL ENABLE,

                  "TOU_CD" CHAR(8) DEFAULT ' ' NOT NULL ENABLE,

                  "PRICEITEM_PARM_GRP_ID" NUMBER(22,0) DEFAULT 1 NOT NULL ENABLE,

                  "ORG_TXN_DATE" DATE NOT NULL ENABLE,

                  "DER_DIVISION" CHAR(5) NOT NULL ENABLE,

                  "PRICE_ASGN_ID" CHAR(10),

                  "FIN_PRICEITEM_CD" CHAR(30),

                  "IGNORE_SW" CHAR(1),

                  "DO_NOT_AGG_SW" CHAR(1),

                  "PA_PER_ID" CHAR(10),

                  "PA_PRICELIST_ID" CHAR(10),

                  "BCHG_START_DT" DATE NOT NULL ENABLE,

                  "BCHG_END_DT" DATE NOT NULL ENABLE,

                  "PARM_SCORE" NUMBER,

                  "LVL_SCORE" NUMBER,

                  "PA_ACCT_ID" CHAR(10),

                  "BILLABLE_CHG_ID" CHAR(12) DEFAULT ' ',

                  "STATUS" CHAR(1),

                  "ACCT_NBR_TYPE_CD" CHAR(8),

                  "ACCT_NBR" VARCHAR2(30),

                  "SCHEDULE_CD" CHAR(12),

                  "MESSAGE_CAT_NBR" NUMBER(5,0) DEFAULT 0 NOT NULL ENABLE,

                  "MESSAGE_NBR" NUMBER(5,0) DEFAULT 0 NOT NULL ENABLE,

                  "SA_ID" CHAR(10),

                  "CURR_CONV_ALG_CD" VARCHAR2(2000),

                  "PRICE_CURRENCY_CD" CHAR(3),

                  "SUMMARY_ID" VARCHAR2(80) DEFAULT '-1',

                  "PROCESSING_DT" DATE,

                  "SW_PRM_TXN_HEADER_ID" CHAR(14) DEFAULT ' ',

                  "SW_PRM_TXN_SOURCE_CD" VARCHAR2(30) DEFAULT ' ',

                  "SW_PRM_CIS_DIVISION" CHAR(5) DEFAULT ' ',

                  "SMRY_UID" NUMBER(30,0) NOT NULL ENABLE,

                  "MESSAGE_PARM1" VARCHAR2(60),

                  "MESSAGE_PARM2" VARCHAR2(60),

                  "AGG_PARM_GRP_ID" NUMBER(22,0) DEFAULT 1,

                  "TXN_RATING_CRITERIA" CHAR(4) DEFAULT 'DNRT',

                  "RS_CD" CHAR(8),

                  "TXN_CALC_ID" NUMBER(22,0),

                  "RUN_GRP_ID" NUMBER(22,0),

                  "ACCT_CURRENCY_CD" CHAR(3) DEFAULT ' ' NOT NULL ENABLE,

                  "MESSAGE_PARM3" VARCHAR2(60),

                  "MESSAGE_PARM4" VARCHAR2(60),

                  "MESSAGE_PARM5" VARCHAR2(60),

                  "ID" NUMBER NOT NULL ENABLE,

                  CONSTRAINT "XF240P1" PRIMARY KEY ("ID"));

                 

                ## DSN Parameters for TimesTen database cache are :

                 

                [ttsparc_N]

                Driver=/scratch/rmbbuild/TT_SOFT/TimesTen/ttsparc/lib/libtten.so

                DataStore=/var/TimesTen/ttsparc/DemoDataStore/ttsparc_N

                PermSize=10240

                TempSize=2048

                PLSQL=1

                DatabaseCharacterSet=AL32UTF8

                OracleNetServiceName=PDM2600N

                CacheGridEnable=0

                 

                ## txndetailDS.write().mode("append").format("jdbc").option("url", ttConnection).option("dbtable", "CI_TXN_DETAIL").save();

                 

                This line basically writes records to CI_TXN_DETAIL table. yes these are Spark framework methods.

                 

                Thanks,

                Anand

                • 5. Re: Spark Application tested with Timesten takes more time than with oracle
                  ChrisJenkins-Oracle

                  Sorry for the delay in replying. For some reason I didn't get a notification of your response.

                   

                  The first thing I would try here is to add the following lines to your DSN definition:

                   

                  MemoryLock=4

                  LogBufMB=1024

                   

                  Note that:

                   

                  a)   Depending on the OS you might need to make some configuration in order to use memory lock (see TimesTen installation guide for info)

                  b)   This will increase the size of the database shared memory segment by 1 Gb so you might need to increase shmmax/shmall kernel parameters.

                   

                  I would still be very interested to understand what code/logic gets executed by that Spark method...

                   

                  Chris