8 Replies Latest reply: Oct 10, 2012 10:03 AM by Chrisjenkins-Oracle RSS

    lower performance for select in our setup

    856103
      We are currently doing performance testing for timesten for 2000 users. The application is a java project and is deployed on weblogic server. We are facing very poor performance with timesten. The response time for the same code with Oracle db is 0.116 second , however with timesten it is coming about 9 seconds.

      We have tried both the client-server connection as well as direct connection .
      The sql query is just a select statement which gets the count of records from the database . Our requirement is read only and we are not writing anything in timesten . We are caching data from oracle db in timesten tables and running our query on it .

      The details of the environment and the timesten database are as follows.

      1.)Timesten is intalled on RHEL 5 64 bit machine . The output of ttversion of the same is

      TimesTen Release 11.2.1.9.0 (64 bit Linux/x86_64) (TTEAG:23388) 2012-03-19T21:35:54Z
      Instance admin: tteag
      Instance home directory: /timestendb/TimesTen/TTEAG
      Group owner: ttadmin
      Daemon home directory: /timestendb/TimesTen/TTEAG/info
      PL/SQL enabled.

      2.)This machine has currently 10 cpu . The cpu details is

      processor     : 0
      vendor_id     : GenuineIntel
      cpu family     : 6
      model          : 44
      model name     : Intel(R) Xeon(R) CPU X5675 @ 3.07GHz
      stepping     : 2
      cpu MHz          : 3066.886
      cache size     : 12288 KB
      physical id     : 1
      siblings     : 5
      core id          : 0
      cpu cores     : 5
      apicid          : 32
      fpu          : yes
      fpu_exception     : yes
      cpuid level     : 11
      wp          : yes
      flags          : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx pdpe1gb rdtscp lm constant_tsc ida nonstop_tsc arat pni monitor ds_cpl vmx smx est tm2 ssse3 cx16 xtpr sse4_1 sse4_2 popcnt lahf_lm
      bogomips     : 6133.77
      clflush size     : 64
      cache_alignment     : 64
      address sizes     : 40 bits physical, 48 bits virtual
      power management: [8]





      3.)The memory details for the machine are :-

      MemTotal: 148449320 kB
      MemFree: 45912888 kB
      Buffers: 941548 kB
      Cached: 94945804 kB
      SwapCached: 48 kB
      Active: 93980700 kB
      Inactive: 5289636 kB
      HighTotal: 0 kB
      HighFree: 0 kB
      LowTotal: 148449320 kB
      LowFree: 45912888 kB
      SwapTotal: 147455984 kB
      SwapFree: 147455732 kB
      Dirty: 616 kB
      Writeback: 412 kB
      AnonPages: 3383108 kB
      Mapped: 298540 kB
      Slab: 2848180 kB
      PageTables: 19772 kB
      NFS_Unstable: 0 kB
      Bounce: 0 kB
      CommitLimit: 221549572 kB
      Committed_AS: 102509964 kB
      VmallocTotal: 34359738367 kB
      VmallocUsed: 340988 kB
      VmallocChunk: 34359395635 kB
      HugePages_Total: 128
      HugePages_Free: 96
      HugePages_Rsvd: 0
      Hugepagesize: 2048 kB

      4.)We have permanent and temporary both databases.

      4 a )The dsn entry for permanent timesten db is :-

      [TTEAG]
      Driver=/timestendb/TimesTen/TTEAG/lib/libtten.so
      DataStore=/timesten03/TimesTen/database/TTEAG/TT_1121
      LogDir=/timesten04/TimesTen/logs/TTEAG
      PermSize=50000
      TempSize=2000
      DatabaseCharacterSet=AL32UTF8
      OracleNetServiceName=EAG
      Connections=2047
      #MemoryLock=4
      PLSQL=1



      Output of monitor command for it
      Command> monitor;

      TIME_OF_1ST_CONNECT: Wed Sep 12 14:39:40 2012
      DS_CONNECTS: 1574
      DS_DISCONNECTS: 799
      DS_CHECKPOINTS: 746
      DS_CHECKPOINTS_FUZZY: 746
      DS_COMPACTS: 0
      PERM_ALLOCATED_SIZE: 51200000
      PERM_IN_USE_SIZE: 70188
      PERM_IN_USE_HIGH_WATER: 70188
      TEMP_ALLOCATED_SIZE: 2048000
      TEMP_IN_USE_SIZE: 26561
      TEMP_IN_USE_HIGH_WATER: 869386
      SYS18: 0
      TPL_FETCHES: 0
      TPL_EXECS: 0
      CACHE_HITS: 0
      PASSTHROUGH_COUNT: 0
      XACT_BEGINS: 738998
      XACT_COMMITS: 739114
      XACT_D_COMMITS: 0
      XACT_ROLLBACKS: 0
      LOG_FORCES: 746
      DEADLOCKS: 0
      LOCK_TIMEOUTS: 0
      LOCK_GRANTS_IMMED: 765835
      LOCK_GRANTS_WAIT: 0
      SYS19: 0
      CMD_PREPARES: 13
      CMD_REPREPARES: 0
      CMD_TEMP_INDEXES: 0
      LAST_LOG_FILE: 1
      REPHOLD_LOG_FILE: -1
      REPHOLD_LOG_OFF: -1
      REP_XACT_COUNT: 0
      REP_CONFLICT_COUNT: 0
      REP_PEER_CONNECTIONS: 0
      REP_PEER_RETRIES: 0
      FIRST_LOG_FILE: 1
      LOG_BYTES_TO_LOG_BUFFER: 305952
      LOG_FS_READS: 0
      LOG_FS_WRITES: 747
      LOG_BUFFER_WAITS: 0
      CHECKPOINT_BYTES_WRITTEN: 955832
      CURSOR_OPENS: 727934
      CURSOR_CLOSES: 728366
      SYS3: 0
      SYS4: 0
      SYS5: 0
      SYS6: 0
      CHECKPOINT_BLOCKS_WRITTEN: 6739
      CHECKPOINT_WRITES: 9711
      REQUIRED_RECOVERY: 0
      SYS11: 0
      SYS12: 1
      TYPE_MODE: 0
      SYS13: 0
      SYS14: 0
      SYS15: 0
      SYS16: 0
      SYS17: 0
      SYS9:

      4 b.) The dsn of temporary db is :-

      [TTEAGTMP]
      Driver=/timestendb/TimesTen/TTEAG/lib/libtten.so
      DataStore=/timesten03/TimesTen/database/TTEAGTMP/TT_1121
      LogDir=/timesten04/TimesTen/logs/TTEAGTMP
      Temporary=1
      AutoCreate=1
      PermSize=20000
      TempSize=20000
      DatabaseCharacterSet=AL32UTF8
      OracleNetServiceName=EAG
      Connections=2047
      #MemoryLock=4
      PLSQL=2
      PLSQL_MEMORY_ADDRESS=20000000

      The output of monitor command is :-

      Command> monitor;

      TIME_OF_1ST_CONNECT: Tue Sep 11 14:00:34 2012
      DS_CONNECTS: 4609
      DS_DISCONNECTS: 4249
      DS_CHECKPOINTS: 894
      DS_CHECKPOINTS_FUZZY: 893
      DS_COMPACTS: 0
      PERM_ALLOCATED_SIZE: 20480000
      PERM_IN_USE_SIZE: 70198
      PERM_IN_USE_HIGH_WATER: 70560
      TEMP_ALLOCATED_SIZE: 20480000
      TEMP_IN_USE_SIZE: 15856
      TEMP_IN_USE_HIGH_WATER: 326869
      SYS18: 0
      TPL_FETCHES: 0
      TPL_EXECS: 0
      CACHE_HITS: 0
      PASSTHROUGH_COUNT: 0
      XACT_BEGINS: 1005281
      XACT_COMMITS: 1005661
      XACT_D_COMMITS: 0
      XACT_ROLLBACKS: 6
      LOG_FORCES: 8
      DEADLOCKS: 0
      LOCK_TIMEOUTS: 8
      LOCK_GRANTS_IMMED: 2031645
      LOCK_GRANTS_WAIT: 2
      SYS19: 0
      CMD_PREPARES: 149
      CMD_REPREPARES: 0
      CMD_TEMP_INDEXES: 0
      LAST_LOG_FILE: 0
      REPHOLD_LOG_FILE: -1
      REPHOLD_LOG_OFF: -1
      REP_XACT_COUNT: 0
      REP_CONFLICT_COUNT: 0
      REP_PEER_CONNECTIONS: 0
      REP_PEER_RETRIES: 0
      FIRST_LOG_FILE: 0
      LOG_BYTES_TO_LOG_BUFFER: 12515480
      LOG_FS_READS: 0
      LOG_FS_WRITES: 36
      LOG_BUFFER_WAITS: 0
      CHECKPOINT_BYTES_WRITTEN: 0
      CURSOR_OPENS: 928766
      CURSOR_CLOSES: 929343
      SYS3: 0
      SYS4: 0
      SYS5: 0
      SYS6: 0
      CHECKPOINT_BLOCKS_WRITTEN: 0
      CHECKPOINT_WRITES: 0
      REQUIRED_RECOVERY: 0
      SYS11: 0
      SYS12: 1
      TYPE_MODE: 0
      SYS13: 0
      SYS14: 0
      SYS15: 0
      SYS16: 0
      SYS17: 0
      SYS9:

      5.)The weblogic installed is version 10.3.4 . There are three managed servers in it and they are in a cluster . The timesten data source is created in the weblogic . The application uses the jndi name to connect to the data source using jdbc driver .

      6 .) Cache groups

      We have 7 cachegroups created for the 7 tables. Indexes have also been created on the tables which are same as the source oracle db from where data is cached .

      The cachegroups details is :-

      Cache Group CACHEADM.PROCESS_INSTANCE_B_T_UG:

      Cache Group Type: User Managed
      Autorefresh: Yes
      Autorefresh Mode: Incremental
      Autorefresh State: On
      Autorefresh Interval: 1 Minute
      Autorefresh Status: ok
      Aging: No aging defined

      Root Table: EAGBE00.PROCESS_INSTANCE_B_T
      Table Type: Propagate


      7.)For client server connection. On the client machine weblogic 10.3.4 is installed with two managed servers .

      The ttversion for timesten client is :-

      TimesTen Release 11.2.1.9.0 (64 bit HPUX/IPF) (TTEAG) 2012-03-20T00:45:11Z
      Instance home directory: /globalapp/app/TimesTen/TTEAG
      Group owner: oinstall


      8.) The oracle database is 10.2.0.4.0



      Please let us know if you see anything wrong with this setup or if we are doing something wrong.
        • 1. Re: lower performance for select in our setup
          Chrisjenkins-Oracle
          Can you please provide some additional information for one of the problem queries:

          1. Full cache group definition including any additional indexes you have created (e.g. ones from Oracle DB)

          2. Number of rows in the tables referecned by the query

          3. The full text of the SQL statement.

          4. Whether or not you have computed statistics in TimesTen since loading the data into the cache groups.

          5. The query plan from TimesTen (in ttIsql do '*explain sql_statement*').

          6. Query plan for the same statement from Oracle database

          7. The timing for the statement in Oracle and in TimesTen, preferably from a command line tool such as SQL*Plus or ttIsql rather than the application.

          8. Information on how you do the timing in the application code. For example, does the timing include connect/disconnect or just query execution.

          Thanks,

          Chris
          • 2. Re: lower performance for select in our setup
            856103
            Please find the details as follows split in two posts:-

            1. Full cache group definition including any additional indexes you have created (e.g.
            ones from Oracle DB)
            a.) Cache group details – total 7

            create USERMANAGED cache group TASK_INSTANCE_T_UG
            AUTOREFRESH MODE INCREMENTAL INTERVAL 1 MINUTES STATE ON
            from eagbe00.TASK_INSTANCE_T
            (
            TKIID varbinary(16) not null primary key,
            NAME VARCHAR2(220) not null,
            NAMESPACE VARCHAR2(254) not null,
            TKTID varbinary(16),
            TOP_TKIID varbinary(16) not null,
            FOLLOW_ON_TKIID varbinary(16),
            APPLICATION_NAME VARCHAR2(220),
            APPLICATION_DEFAULTS_ID varbinary(16),
            CONTAINMENT_CONTEXT_ID varbinary(16),
            PARENT_CONTEXT_ID varbinary(16),
            STATE NUMBER(10) not null,
            KIND NUMBER(10) not null,
            AUTO_DELETE_MODE NUMBER(10) not null,
            HIERARCHY_POSITION NUMBER(10) not null,
            TYPE VARCHAR2(254),
            SVTID varbinary(16),
            SUPPORTS_CLAIM_SUSPENDED NUMBER(5) not null,
            SUPPORTS_AUTO_CLAIM NUMBER(5) not null,
            SUPPORTS_FOLLOW_ON_TASK NUMBER(5) not null,
            IS_AD_HOC NUMBER(5) not null,
            IS_ESCALATED NUMBER(5) not null,
            IS_INLINE NUMBER(5) not null,
            IS_SUSPENDED NUMBER(5) not null,
            IS_WAITING_FOR_SUBTASK NUMBER(5) not null,
            SUPPORTS_DELEGATION NUMBER(5) not null,
            SUPPORTS_SUB_TASK NUMBER(5) not null,
            IS_CHILD NUMBER(5) not null,
            HAS_ESCALATIONS NUMBER(5),
            START_TIME TIMESTAMP(6),
            ACTIVATION_TIME TIMESTAMP(6),
            LAST_MODIFICATION_TIME TIMESTAMP(6),
            LAST_STATE_CHANGE_TIME TIMESTAMP(6),
            COMPLETION_TIME TIMESTAMP(6),
            DUE_TIME TIMESTAMP(6),
            EXPIRATION_TIME TIMESTAMP(6),
            FIRST_ACTIVATION_TIME TIMESTAMP(6),
            DEFAULT_LOCALE VARCHAR2(32),
            DURATION_UNTIL_DELETED VARCHAR2(254),
            DURATION_UNTIL_DUE VARCHAR2(254),
            DURATION_UNTIL_EXPIRES VARCHAR2(254),
            CALENDAR_NAME VARCHAR2(254),
            JNDI_NAME_CALENDAR VARCHAR2(254),
            JNDI_NAME_STAFF_PROVIDER VARCHAR2(254),
            CONTEXT_AUTHORIZATION NUMBER(10) not null,
            ORIGINATOR VARCHAR2(128),
            STARTER VARCHAR2(128),
            OWNER VARCHAR2(128),
            ADMIN_QTID varbinary(16),
            EDITOR_QTID varbinary(16),
            POTENTIAL_OWNER_QTID varbinary(16),
            POTENTIAL_STARTER_QTID varbinary(16),
            READER_QTID varbinary(16),
            PRIORITY NUMBER(10),
            SCHEDULER_ID VARCHAR2(254),
            SERVICE_TICKET VARCHAR2(254),
            EVENT_HANDLER_NAME VARCHAR2(64),
            BUSINESS_RELEVANCE NUMBER(5) not null,
            RESUMES TIMESTAMP(6),
            SUBSTITUTION_POLICY NUMBER(10) not null,
            DELETION_TIME TIMESTAMP(6),
            VERSION_ID NUMBER(5) not null,
            PROPAGATE
            )
            ;

            create USERMANAGED cache group WORK_ITEM_T_UG
            AUTOREFRESH MODE INCREMENTAL INTERVAL 1 MINUTES STATE ON
            from eagbe00.WORK_ITEM_T
            (
            WIID varbinary(16) not null primary key,
            PARENT_WIID varbinary(16),
            OWNER_ID VARCHAR2(128),
            GROUP_NAME VARCHAR2(128),
            EVERYBODY NUMBER(5) not null,
            EXCLUDE NUMBER(5) not null,
            QIID varbinary(16),
            OBJECT_TYPE NUMBER(10) not null,
            OBJECT_ID varbinary(16) not null,
            ASSOCIATED_OBJECT_TYPE NUMBER(10) not null,
            ASSOCIATED_OID varbinary(16),
            REASON NUMBER(10) not null,
            CREATION_TIME TIMESTAMP(6) not null,
            KIND NUMBER(10) not null,
            AUTH_INFO NUMBER(10) not null,
            VERSION_ID NUMBER(5) not null,
            PROPAGATE
            )
            ;

            create USERMANAGED cache group RETRIEVED_USER_T_UG
            AUTOREFRESH MODE INCREMENTAL INTERVAL 1 MINUTES STATE ON
            from eagbe00.RETRIEVED_USER_T
            (
            QIID varbinary(16) not null,
            OWNER_ID VARCHAR2(128) not null,
            REASON NUMBER(10) not null,
            ASSOCIATED_OID varbinary(16),
            VERSION_ID NUMBER(5) not null,
            primary key (QIID, OWNER_ID),
            PROPAGATE
            );

            create USERMANAGED cache group PROCESS_INSTANCE_B_T_UG
            AUTOREFRESH MODE INCREMENTAL INTERVAL 1 MINUTES STATE ON
            from eagbe00.PROCESS_INSTANCE_B_T
            (
            PIID varbinary(16) not null primary key,
            PTID varbinary(16) not null,
            STATE NUMBER(10) not null,
            PENDING_REQUEST NUMBER(10) not null,
            CREATED TIMESTAMP(6),
            STARTED TIMESTAMP(6),
            COMPLETED TIMESTAMP(6),
            LAST_STATE_CHANGE TIMESTAMP(6),
            LAST_MODIFIED TIMESTAMP(6),
            NAME VARCHAR2(220) not null,
            PARENT_NAME VARCHAR2(220),
            TOP_LEVEL_NAME VARCHAR2(220) not null,
            COMPENSATION_SPHERE_NAME VARCHAR2(100),
            STARTER VARCHAR2(128),
            DESCRIPTION VARCHAR2(254),
            INPUT_SNID varbinary(16),
            INPUT_ATID varbinary(16),
            INPUT_VTID varbinary(16),
            OUTPUT_SNID varbinary(16),
            OUTPUT_ATID varbinary(16),
            OUTPUT_VTID varbinary(16),
            FAULT_NAME VARCHAR2(254),
            TOP_LEVEL_PIID varbinary(16) not null,
            PARENT_PIID varbinary(16),
            PARENT_AIID varbinary(16),
            TKIID varbinary(16),
            TERMIN_ON_REC NUMBER(5) not null,
            AWAITED_SUB_PROC NUMBER(5) not null,
            IS_CREATING NUMBER(5) not null,
            PREVIOUS_STATE NUMBER(10),
            EXECUTING_ISOLATED_SCOPE NUMBER(5) not null,
            SCHEDULER_TASK_ID VARCHAR2(254),
            RESUMES TIMESTAMP(6),
            PENDING_SKIP_REQUEST NUMBER(5) not null,
            UNHANDLED_EXCEPTION VARBINARY(16),
            VERSION_ID NUMBER(5) not null,
            PROPAGATE
            );

            create USERMANAGED cache group PROCESS_TEMPLATE_B_T_UG
            AUTOREFRESH MODE INCREMENTAL INTERVAL 1 MINUTES STATE ON
            from eagbe00.PROCESS_TEMPLATE_B_T
            (
            PTID varbinary(16) not null primary key,
            NAME VARCHAR2(220) not null,
            DEFINITION_NAME VARCHAR2(220),
            DISPLAY_NAME VARCHAR2(64),
            APPLICATION_NAME VARCHAR2(220),
            DISPLAY_ID NUMBER(10) not null,
            DESCRIPTION VARCHAR2(254),
            DOCUMENTATION varchar2(4),
            EXECUTION_MODE NUMBER(10) not null,
            IS_SHARED NUMBER(5) not null,
            IS_AD_HOC NUMBER(5) not null,
            STATE NUMBER(10) not null,
            VALID_FROM TIMESTAMP(6) not null,
            TARGET_NAMESPACE VARCHAR2(250),
            CREATED TIMESTAMP(6) not null,
            AUTO_DELETE NUMBER(5) not null,
            EXTENDED_AUTO_DELETE NUMBER(10) not null,
            VERSION VARCHAR2(32),
            SCHEMA_VERSION NUMBER(10) not null,
            ABSTRACT_BASE_NAME VARCHAR2(254),
            S_BEAN_LOOKUP_NAME VARCHAR2(254),
            S_BEAN60_LOOKUP_NAME VARCHAR2(254),
            E_BEAN_LOOKUP_NAME VARCHAR2(254),
            PROCESS_BASE_NAME VARCHAR2(254),
            S_BEAN_HOME_NAME VARCHAR2(254),
            E_BEAN_HOME_NAME VARCHAR2(254),
            BPEWS_UTID varbinary(16),
            WPC_UTID varbinary(16),
            BUSINESS_RELEVANCE NUMBER(5) not null,
            ADMINISTRATOR_QTID varbinary(16),
            READER_QTID varbinary(16),
            A_TKTID varbinary(16),
            A_TKTIDFOR_ACTS varbinary(16),
            COMPENSATION_SPHERE NUMBER(10) not null,
            AUTONOMY NUMBER(10) not null,
            CAN_CALL NUMBER(5) not null,
            CAN_INITIATE NUMBER(5) not null,
            CONTINUE_ON_ERROR NUMBER(5) not null,
            IGNORE_MISSING_DATA NUMBER(10) not null,
            PROPAGATE
            );

            create USERMANAGED cache group TASK_TEMPL_LDESC_T_UG
            AUTOREFRESH MODE INCREMENTAL INTERVAL 1 MINUTES STATE ON
            from eagbe00.TASK_TEMPL_LDESC_T
            (
            TKTID varbinary(16) not null,
            LOCALE VARCHAR2(32) not null,
            CONTAINMENT_CONTEXT_ID varbinary(16) not null,
            DISPLAY_NAME VARCHAR2(64),
            DESCRIPTION VARCHAR2(254),
            DOCUMENTATION varchar2(4) ,
            primary key (TKTID, LOCALE),
            PROPAGATE
            );


            create USERMANAGED cache group QUERY_VAR_INSTANCE_T_UG
            AUTOREFRESH MODE INCREMENTAL INTERVAL 1 MINUTES STATE ON
            from eagbe00.QUERYABLE_VARIABLE_INSTANCE_T
            (
            PKID varbinary(16) not null primary key,
            CTID varbinary(16) not null,
            PIID varbinary(16) not null,
            PAID varbinary(16) not null,
            VARIABLE_NAME VARCHAR2(254) not null,
            PROPERTY_NAME VARCHAR2(255) not null,
            PROPERTY_NAMESPACE VARCHAR2(254) not null,
            TYPE NUMBER(10) not null,
            GENERIC_VALUE VARCHAR2(512),
            STRING_VALUE VARCHAR2(512),
            NUMBER_VALUE NUMBER(20),
            DECIMAL_VALUE NUMBER,
            TIMESTAMP_VALUE TIMESTAMP(6),
            VERSION_ID NUMBER(5) not null,
            PROPAGATE
            );


            select count(1) from eagbe00.task, eagbe00.work_item, eagbe00.process_instance pi,eagbe00.TASK_TEMPL_DESC
            where WORK_ITEM.OBJECT_ID = TASK.TKIID AND TASK.TKTID = TASK_TEMPL_DESC.TKTID
            AND pi.piid = TASK.containment_ctx_id AND TASK.KIND = 105 AND TASK.STATE = 2
            and WORK_ITEM.REASON IN (1,4) and TASK.IS_ESCALATED = 0 AND TASK.SUSPENDED = 0
            and TASK.IS_INLINE = 1
            AND WORK_ITEM.OWNER_ID = '169403'
            ;


            SELECT count(1 )
            FROM (SELECT DISTINCT TA.TKIID , TA.ACTIVATED , TA.COMPLETED , TTD.DISPLAY_NAME , TA.ORIGINATOR ,
            TA.STATE ,QP1.NAME ,QP1.STRING_VALUE ,QP2.NAME AS NAME1,QP2.STRING_VALUE AS STRING_VALUE1,
            QP3.NAME AS NAME2,QP3.STRING_VALUE AS STRING_VALUE2,QP4.NAME AS NAME3,QP4.STRING_VALUE AS STRING_VALUE3,
            QP5.NAME AS NAME4,QP5.STRING_VALUE AS STRING_VALUE4
            FROM EAGBE00.TASK TA
            LEFT JOIN EAGBE00.QUERY_PROPERTY QP3 ON (TA.CONTAINMENT_CTX_ID = QP3.PIID)
            LEFT JOIN EAGBE00.QUERY_PROPERTY QP5 ON (TA.CONTAINMENT_CTX_ID = QP5.PIID)
            LEFT JOIN EAGBE00.QUERY_PROPERTY QP4 ON (TA.CONTAINMENT_CTX_ID = QP4.PIID)
            LEFT JOIN EAGBE00.QUERY_PROPERTY QP2 ON (TA.CONTAINMENT_CTX_ID = QP2.PIID)
            LEFT JOIN EAGBE00.QUERY_PROPERTY QP1 ON (TA.CONTAINMENT_CTX_ID = QP1.PIID),
            EAGBE00.WORK_ITEM WI,
            EAGBE00.TASK_TEMPL_DESC TTD
            WHERE (WI.OBJECT_ID = TA.TKIID AND TA.TKTID = TTD.TKTID) AND (TA.KIND IN (105 ) and TA.STATE IN (2 ,8 )
            and TA.IS_ESCALATED =0 and TA.SUSPENDED =0 and TA.IS_INLINE =1 and WI.REASON IN (1 ,4 )
            AND WI.EVERYBODY =0 and QP1.NAME ='starter'
            and QP2.NAME ='applicationName' and upper(QP2.STRING_VALUE) like '%GESS%'
            and QP3.NAME ='subType' and QP4.NAME ='description' and QP5.NAME ='additionalInfo' and WI.OWNER_ID ='169403' )
            ORDER BY TA.ACTIVATED DESC)
            ;


            CREATE USERMANAGED CACHE GROUP WORK_ITEM_TIMESTEN_UG
            AUTOREFRESH MODE INCREMENTAL INTERVAL 1 MINUTES STATE ON FROM
            EAGBE00.WORK_ITEM_TIMESTEN
            (WIID VARBINARY(16) ,
            PARENT_WIID VARBINARY(16),
            OWNER_ID VARCHAR2(128),
            GROUP_NAME VARCHAR2(128),
            EVERYBODY NUMBER(5) ,
            EXCLUDE NUMBER(5) ,
            QIID VARBINARY(16),
            OBJECT_TYPE NUMBER(10) ,
            OBJECT_ID VARBINARY(16) ,
            ASSOCIATED_OBJECT_TYPE NUMBER(10) ,
            ASSOCIATED_OID VARBINARY(16),
            REASON NUMBER(10) ,
            CREATION_TIME TIMESTAMP(6) ,
            KIND NUMBER(10) ,
            AUTH_INFO NUMBER(10) ,
            VERSION_ID NUMBER(5),
            SEQ_NO_PK NUMBER(6),
            primary key (SEQ_NO_PK),
            PROPAGATE
            );

            b .) Indexes

            create index EAGBE00.TI_ACOID on EAGBE00.TASK_INSTANCE_T (APPLICATION_DEFAULTS_ID);

            create index EAGBE00.TI_CCID on EAGBE00.TASK_INSTANCE_T (CONTAINMENT_CONTEXT_ID);

            create index EAGBE00.TI_NAME on EAGBE00.TASK_INSTANCE_T (NAME);

            create index EAGBE00.TI_PARENT on EAGBE00.TASK_INSTANCE_T (PARENT_CONTEXT_ID);

            create index EAGBE00.TI_SERVICET on EAGBE00.TASK_INSTANCE_T (SERVICE_TICKET);

            create index EAGBE00.TI_STATE on EAGBE00.TASK_INSTANCE_T (STATE);

            create index EAGBE00.TI_ST_KND_TI_NAME on EAGBE00.TASK_INSTANCE_T (STATE, KIND, TKIID, NAME);

            create index EAGBE00.TI_TI_KND_ST on EAGBE00.TASK_INSTANCE_T (TKIID, KIND, STATE);

            create index EAGBE00.TI_TK_TOPTK on EAGBE00.TASK_INSTANCE_T (TKTID, TKIID, TOP_TKIID);

            create index EAGBE00.TI_TOPTKIID on EAGBE00.TASK_INSTANCE_T (TOP_TKIID);

            create index EAGBE00.TI_TT_KND on EAGBE00.TASK_INSTANCE_T (TKTID, KIND);

            create index EAGBE00.WI_ASSOBJ_REASON on EAGBE00.WORK_ITEM_T (ASSOCIATED_OID, ASSOCIATED_OBJECT_TYPE, REASON, PARENT_WIID);

            create index EAGBE00.WI_AUTH_E on EAGBE00.WORK_ITEM_T (AUTH_INFO, EVERYBODY);

            create index EAGBE00.WI_AUTH_G on EAGBE00.WORK_ITEM_T (AUTH_INFO, GROUP_NAME);

            create index EAGBE00.WI_AUTH_GR_O_E on EAGBE00.WORK_ITEM_T (AUTH_INFO, GROUP_NAME, OWNER_ID, EVERYBODY);

            create index EAGBE00.WI_AUTH_L on EAGBE00.WORK_ITEM_T (EVERYBODY, GROUP_NAME, OWNER_ID, QIID);

            create index EAGBE00.WI_AUTH_O on EAGBE00.WORK_ITEM_T (AUTH_INFO, OWNER_ID DESC);

            create index EAGBE00.WI_AUTH_R on EAGBE00.WORK_ITEM_T (AUTH_INFO, REASON DESC);

            create index EAGBE00.WI_AUTH_U on EAGBE00.WORK_ITEM_T (AUTH_INFO, QIID);

            create index EAGBE00.WI_GROUP_NAME on EAGBE00.WORK_ITEM_T (GROUP_NAME);

            create index EAGBE00.WI_OBJID_TYPE_QIID on EAGBE00.WORK_ITEM_T (OBJECT_ID, OBJECT_TYPE, QIID);

            create index EAGBE00.WI_OBJID_TYPE_REAS on EAGBE00.WORK_ITEM_T (OBJECT_ID, OBJECT_TYPE, REASON);

            create index EAGBE00.WI_OT_OID_RS on EAGBE00.WORK_ITEM_T (OBJECT_TYPE, OBJECT_ID, REASON);

            create index EAGBE00.WI_OWNER on EAGBE00.WORK_ITEM_T (OWNER_ID, OBJECT_ID, REASON, OBJECT_TYPE);

            create index EAGBE00.WI_PARENT_WIID on EAGBE00.WORK_ITEM_T (PARENT_WIID);

            create index EAGBE00.WI_QIID on EAGBE00.WORK_ITEM_T (QIID);

            create index EAGBE00.WI_QI_OID_OWN on EAGBE00.WORK_ITEM_T (QIID, OBJECT_ID, OWNER_ID);

            create index EAGBE00.WI_QI_OID_RS_OWN on EAGBE00.WORK_ITEM_T (QIID, OBJECT_ID, REASON, OWNER_ID);

            create index EAGBE00.WI_QRY on EAGBE00.WORK_ITEM_T (OBJECT_ID, REASON, EVERYBODY, OWNER_ID);

            create index EAGBE00.WI_REASON on EAGBE00.WORK_ITEM_T (REASON);

            create index EAGBE00.WI_WI_QI on EAGBE00.WORK_ITEM_T (WIID, QIID);

            create index EAGBE00.RUT_ASSOC on EAGBE00.RETRIEVED_USER_T (ASSOCIATED_OID);

            create index EAGBE00.RUT_OWN_QIDESC on EAGBE00.RETRIEVED_USER_T (OWNER_ID, QIID DESC);

            create index EAGBE00.RUT_OWN_QIID on EAGBE00.RETRIEVED_USER_T (OWNER_ID, QIID);

            create index EAGBE00.RUT_QIID on EAGBE00.RETRIEVED_USER_T (QIID);

            create unique index EAGBE00.PIB_NAME on EAGBE00.PROCESS_INSTANCE_B_T (NAME);

            create index EAGBE00.PIB_PAP on EAGBE00.PROCESS_INSTANCE_B_T (PARENT_PIID);

            create index EAGBE00.PIB_PAR on EAGBE00.PROCESS_INSTANCE_B_T (PARENT_AIID);

            create index EAGBE00.PIB_PIID_PTID_STAT on EAGBE00.PROCESS_INSTANCE_B_T (PIID, PTID, STATE, STARTER, STARTED);

            create index EAGBE00.PIB_PIID_STATE on EAGBE00.PROCESS_INSTANCE_B_T (PIID, STATE);

            create index EAGBE00.PIB_PTID on EAGBE00.PROCESS_INSTANCE_B_T (PTID);

            create index EAGBE00.PIB_STATE on EAGBE00.PROCESS_INSTANCE_B_T (STATE);

            create index EAGBE00.PIB_TOP on EAGBE00.PROCESS_INSTANCE_B_T (TOP_LEVEL_PIID);

            create index EAGBE00.PTB_NAME on EAGBE00.PROCESS_TEMPLATE_B_T (PTID, NAME);

            create unique index EAGBE00.PTB_NAME_VALID on EAGBE00.PROCESS_TEMPLATE_B_T (NAME, VALID_FROM);

            create index EAGBE00.PTB_NAME_VF_STATE on EAGBE00.PROCESS_TEMPLATE_B_T (NAME, VALID_FROM, STATE, PTID);

            create index EAGBE00.PTB_STATE_PTID on EAGBE00.PROCESS_TEMPLATE_B_T (STATE, PTID);

            create index EAGBE00.PTB_TOP_APP on EAGBE00.PROCESS_TEMPLATE_B_T (APPLICATION_NAME);

            create index EAGBE00.TTLD_CCID on EAGBE00.TASK_TEMPL_LDESC_T (CONTAINMENT_CONTEXT_ID);

            create index EAGBE00.TTLD_TKTID on EAGBE00.TASK_TEMPL_LDESC_T (TKTID);

            create index EAGBE00.TTLD_TT_LOC on EAGBE00.TASK_TEMPL_LDESC_T (TKTID, LOCALE DESC);

            create index EAGBE00.QVI_PI_CT_PA on EAGBE00.QUERYABLE_VARIABLE_INSTANCE_T (PIID, CTID, PAID);

            create index EAGBE00.QVI_PI_DEC on EAGBE00.QUERYABLE_VARIABLE_INSTANCE_T (PIID, DECIMAL_VALUE);

            create index EAGBE00.QVI_PI_GEN_VALUE on EAGBE00.QUERYABLE_VARIABLE_INSTANCE_T (PIID, GENERIC_VALUE);

            create index EAGBE00.QVI_PI_NAMESPACE on EAGBE00.QUERYABLE_VARIABLE_INSTANCE_T (PIID, PROPERTY_NAMESPACE);

            create index EAGBE00.QVI_PI_NUM on EAGBE00.QUERYABLE_VARIABLE_INSTANCE_T (PIID, NUMBER_VALUE);

            create index EAGBE00.QVI_PI_PROPNAME on EAGBE00.QUERYABLE_VARIABLE_INSTANCE_T (PIID, PROPERTY_NAME);

            create index EAGBE00.QVI_PI_STR_VALUE on EAGBE00.QUERYABLE_VARIABLE_INSTANCE_T (PIID, STRING_VALUE);

            create index EAGBE00.QVI_PI_TIME on EAGBE00.QUERYABLE_VARIABLE_INSTANCE_T (PIID, TIMESTAMP_VALUE);

            create index EAGBE00.QVI_PI_VARNAME on EAGBE00.QUERYABLE_VARIABLE_INSTANCE_T (PIID, VARIABLE_NAME);

            c.)Views - 5 views

            CREATE VIEW EAGBE00.TASK
            (tkiid, activated, applic_defaults_id, applic_name, business_relevance, completed, containment_ctx_id,
            ctx_authorization, due, expires, first_activated, follow_on_tkiid, is_ad_hoc, is_escalated, is_inline,
            is_wait_for_sub_tk, kind, last_modified, last_state_change, name, name_space, originator, owner, parent_context_id,
            priority, started, starter, state, support_autoclaim, support_claim_susp, support_delegation, support_sub_task,
            support_follow_on, hierarchy_position, is_child, suspended, tktid, top_tkiid, type, resumes)
            AS
            SELECT TKIID, ACTIVATION_TIME,
            APPLICATION_DEFAULTS_ID, APPLICATION_NAME,
            BUSINESS_RELEVANCE, COMPLETION_TIME,
            CONTAINMENT_CONTEXT_ID, CONTEXT_AUTHORIZATION,
            DUE_TIME, EXPIRATION_TIME,
            FIRST_ACTIVATION_TIME, FOLLOW_ON_TKIID,
            IS_AD_HOC, IS_ESCALATED,
            IS_INLINE, IS_WAITING_FOR_SUBTASK,
            KIND, LAST_MODIFICATION_TIME,
            LAST_STATE_CHANGE_TIME, NAME,
            NAMESPACE, ORIGINATOR, OWNER,
            PARENT_CONTEXT_ID, PRIORITY, START_TIME,
            STARTER, STATE, SUPPORTS_AUTO_CLAIM,
            SUPPORTS_CLAIM_SUSPENDED, SUPPORTS_DELEGATION,
            SUPPORTS_SUB_TASK, SUPPORTS_FOLLOW_ON_TASK,
            HIERARCHY_POSITION, IS_CHILD, IS_SUSPENDED,
            TKTID, TOP_TKIID, TYPE,
            RESUMES
            FROM EAGBE00.TASK_INSTANCE_T
            ;

            CREATE VIEW EAGBE00.TASK
            (tkiid, activated, applic_defaults_id, applic_name, business_relevance, completed, containment_ctx_id,
            ctx_authorization, due, expires, first_activated, follow_on_tkiid, is_ad_hoc, is_escalated, is_inline,
            is_wait_for_sub_tk, kind, last_modified, last_state_change, name, name_space, originator, owner, parent_context_id,
            priority, started, starter, state, support_autoclaim, support_claim_susp, support_delegation, support_sub_task,
            support_follow_on, hierarchy_position, is_child, suspended, tktid, top_tkiid, type, resumes)
            AS
            SELECT TKIID, ACTIVATION_TIME,
            APPLICATION_DEFAULTS_ID, APPLICATION_NAME,
            BUSINESS_RELEVANCE, COMPLETION_TIME,
            CONTAINMENT_CONTEXT_ID, CONTEXT_AUTHORIZATION,
            DUE_TIME, EXPIRATION_TIME,
            FIRST_ACTIVATION_TIME, FOLLOW_ON_TKIID,
            IS_AD_HOC, IS_ESCALATED,
            IS_INLINE, IS_WAITING_FOR_SUBTASK,
            KIND, LAST_MODIFICATION_TIME,
            LAST_STATE_CHANGE_TIME, NAME,
            NAMESPACE, ORIGINATOR, OWNER,
            PARENT_CONTEXT_ID, PRIORITY, START_TIME,
            STARTER, STATE, SUPPORTS_AUTO_CLAIM,
            SUPPORTS_CLAIM_SUSPENDED, SUPPORTS_DELEGATION,
            SUPPORTS_SUB_TASK, SUPPORTS_FOLLOW_ON_TASK,
            HIERARCHY_POSITION, IS_CHILD, IS_SUSPENDED,
            TKTID, TOP_TKIID, TYPE,
            RESUMES
            FROM EAGBE00.TASK_INSTANCE_T
            ;

            CREATE VIEW EAGBE00.WORK_ITEM
            (wiid, owner_id, group_name, everybody, object_type, object_id, assoc_object_type, assoc_oid, reason, creation_time,
            qiid, kind)
            AS
            SELECT WORK_ITEM_T.WIID, WORK_ITEM_T.OWNER_ID, WORK_ITEM_T.GROUP_NAME,
            WORK_ITEM_T.EVERYBODY, WORK_ITEM_T.OBJECT_TYPE, WORK_ITEM_T.OBJECT_ID,
            WORK_ITEM_T.ASSOCIATED_OBJECT_TYPE, WORK_ITEM_T.ASSOCIATED_OID, WORK_ITEM_T.REASON,
            WORK_ITEM_T.CREATION_TIME, WORK_ITEM_T.QIID, WORK_ITEM_T.KIND
            FROM EAGBE00.WORK_ITEM_T
            WHERE WORK_ITEM_T.AUTH_INFO = 1
            UNION ALL SELECT WORK_ITEM_T.WIID, WORK_ITEM_T.OWNER_ID, WORK_ITEM_T.GROUP_NAME,
            WORK_ITEM_T.EVERYBODY, WORK_ITEM_T.OBJECT_TYPE, WORK_ITEM_T.OBJECT_ID,
            WORK_ITEM_T.ASSOCIATED_OBJECT_TYPE, WORK_ITEM_T.ASSOCIATED_OID, WORK_ITEM_T.REASON,
            WORK_ITEM_T.CREATION_TIME, WORK_ITEM_T.QIID, WORK_ITEM_T.KIND
            FROM EAGBE00.WORK_ITEM_T
            WHERE WORK_ITEM_T.AUTH_INFO = 2
            UNION ALL SELECT WORK_ITEM_T.WIID, WORK_ITEM_T.OWNER_ID, WORK_ITEM_T.GROUP_NAME,
            WORK_ITEM_T.EVERYBODY, WORK_ITEM_T.OBJECT_TYPE, WORK_ITEM_T.OBJECT_ID,
            WORK_ITEM_T.ASSOCIATED_OBJECT_TYPE, WORK_ITEM_T.ASSOCIATED_OID, WORK_ITEM_T.REASON,
            WORK_ITEM_T.CREATION_TIME, WORK_ITEM_T.QIID, WORK_ITEM_T.KIND
            FROM EAGBE00.WORK_ITEM_T
            WHERE WORK_ITEM_T.AUTH_INFO = 3
            UNION ALL SELECT WORK_ITEM_T.WIID, RETRIEVED_USER_T.OWNER_ID, WORK_ITEM_T.GROUP_NAME,
            WORK_ITEM_T.EVERYBODY, WORK_ITEM_T.OBJECT_TYPE, WORK_ITEM_T.OBJECT_ID,
            WORK_ITEM_T.ASSOCIATED_OBJECT_TYPE, WORK_ITEM_T.ASSOCIATED_OID, WORK_ITEM_T.REASON,
            WORK_ITEM_T.CREATION_TIME, WORK_ITEM_T.QIID, WORK_ITEM_T.KIND
            FROM EAGBE00.WORK_ITEM_T, EAGBE00.RETRIEVED_USER_T
            WHERE WORK_ITEM_T.AUTH_INFO = 0 AND WORK_ITEM_T.QIID = RETRIEVED_USER_T.QIID
            ;

            CREATE VIEW "EAGBE00"."PROCESS_INSTANCE" ("PTID",
            "PIID","NAME","STATE","CREATED","STARTED","COMPLETED",
            "PARENT_NAME","TOP_LEVEL_NAME","PARENT_PIID","TOP_LEVEL_PIID",
            "STARTER","DESCRIPTION","TEMPLATE_NAME","TEMPLATE_DESCR",
            "RESUMES","CONTINUE_ON_ERROR") AS
            SELECT EAGBE00.PROCESS_INSTANCE_B_T.PTID,
            EAGBE00.PROCESS_INSTANCE_B_T.PIID,
            EAGBE00.PROCESS_INSTANCE_B_T.NAME,
            EAGBE00.PROCESS_INSTANCE_B_T.STATE,
            EAGBE00.PROCESS_INSTANCE_B_T.CREATED,
            EAGBE00.PROCESS_INSTANCE_B_T.STARTED,
            EAGBE00.PROCESS_INSTANCE_B_T.COMPLETED,
            EAGBE00.PROCESS_INSTANCE_B_T.PARENT_NAME,
            EAGBE00.PROCESS_INSTANCE_B_T.TOP_LEVEL_NAME,
            EAGBE00.PROCESS_INSTANCE_B_T.PARENT_PIID,
            EAGBE00.PROCESS_INSTANCE_B_T.TOP_LEVEL_PIID,
            EAGBE00.PROCESS_INSTANCE_B_T.STARTER,
            EAGBE00.PROCESS_INSTANCE_B_T.DESCRIPTION,
            EAGBE00.PROCESS_TEMPLATE_B_T.NAME,
            EAGBE00.PROCESS_TEMPLATE_B_T.DESCRIPTION,
            EAGBE00.PROCESS_INSTANCE_B_T.RESUMES,
            EAGBE00.PROCESS_TEMPLATE_B_T.CONTINUE_ON_ERROR
            FROM EAGBE00.PROCESS_INSTANCE_B_T,
            EAGBE00.PROCESS_TEMPLATE_B_T
            WHERE EAGBE00.PROCESS_INSTANCE_B_T.PTID =
            EAGBE00.PROCESS_TEMPLATE_B_T.PTID
            ;

            CREATE VIEW EAGBE00.TASK_TEMPL_DESC AS
            SELECT TASK_TEMPL_LDESC_T.TKTID, TASK_TEMPL_LDESC_T.LOCALE, TASK_TEMPL_LDESC_T.DESCRIPTION,
            TASK_TEMPL_LDESC_T.DISPLAY_NAME
            FROM eagbe00.TASK_TEMPL_LDESC_T
            ;

            CREATE VIEW eagbe00.QUERY_PROPERTY
            (piid, variable_name, name, namespace, generic_value, string_value, number_value, decimal_value, timestamp_value)
            AS
            SELECT QUERYABLE_VARIABLE_INSTANCE_T.PIID, QUERYABLE_VARIABLE_INSTANCE_T.VARIABLE_NAME,
            QUERYABLE_VARIABLE_INSTANCE_T.PROPERTY_NAME, QUERYABLE_VARIABLE_INSTANCE_T.PROPERTY_NAMESPACE,
            QUERYABLE_VARIABLE_INSTANCE_T.GENERIC_VALUE, QUERYABLE_VARIABLE_INSTANCE_T.STRING_VALUE,
            QUERYABLE_VARIABLE_INSTANCE_T.NUMBER_VALUE, QUERYABLE_VARIABLE_INSTANCE_T.DECIMAL_VALUE,
            QUERYABLE_VARIABLE_INSTANCE_T.TIMESTAMP_VALUE
            FROM eagbe00.QUERYABLE_VARIABLE_INSTANCE_T
            ;
            • 3. Re: lower performance for select in our setup
              856103
              2.Number of rows in the tables referecned by the query

              The query just performs count operation. The maximum number of rows in the tables used is 10000 since its just a sample databse .


              3. The full text of the SQL statement.

              SELECT COUNT (DISTINCT TA.TKIID ) FROM EAGBE00.WORK_ITEM WI, EAGBE00.TASK TA WHERE
              (WI.OBJECT_ID = TA.TKIID) AND (TA.KIND IN (105 )and TA.STATE IN (2 ,8 )and
              WI.REASON IN (1 ,4 )and TA.IS_ESCALATED =0 and TA.SUSPENDED =0 and TA.IS_INLINE =1
              and WI.OWNER_ID =? AND WI.EVERYBODY =0 )

              4. Whether or not you have computed statistics in TimesTen since loading the data into the cache groups.

              No we have not computed statistics on the table.

              5. The query plan from TimesTen (in ttIsql do '*explain sql_statement*').

              Query Optimizer Plan:

              STEP: 1
              LEVEL: 2
              OPERATION: RowLkTtreeScan
              TBLNAME: EAGBE00.TASK_INSTANCE_T
              IXNAME: TI_ST_KND_TI_NAME
              INDEXED CONDITION: (TASK_INSTANCE_T.STATE = 2 OR TASK_INSTANCE_T.STATE = 8) AND TASK_INSTANCE_T.KIND = 105
              NOT INDEXED: TASK_INSTANCE_T.IS_INLINE = 1 AND TASK_INSTANCE_T.IS_SUSPENDED = 0 AND TASK_INSTANCE_T.IS_ESCALATED = 0


              STEP: 2
              LEVEL: 2
              OPERATION: RowLkTtreeScan
              TBLNAME: EAGBE00.WORK_ITEM_T
              IXNAME: WI_AUTH_E
              INDEXED CONDITION: WORK_ITEM_T.AUTH_INFO = 1
              NOT INDEXED: <NULL>


              STEP: 3
              LEVEL: 2
              OPERATION: RowLkTtreeScan
              TBLNAME: EAGBE00.WORK_ITEM_T
              IXNAME: WI_AUTH_E
              INDEXED CONDITION: WORK_ITEM_T.AUTH_INFO = 2
              NOT INDEXED: <NULL>


              STEP: 4
              LEVEL: 2
              OPERATION: RowLkTtreeScan
              TBLNAME: EAGBE00.WORK_ITEM_T
              IXNAME: WI_AUTH_E
              INDEXED CONDITION: WORK_ITEM_T.AUTH_INFO = 3
              NOT INDEXED: <NULL>


              STEP: 5
              LEVEL: 3
              OPERATION: RowLkTtreeScan
              TBLNAME: EAGBE00.WORK_ITEM_T
              IXNAME: WI_AUTH_U
              INDEXED CONDITION: WORK_ITEM_T.AUTH_INFO = 0
              NOT INDEXED: <NULL>


              STEP: 6
              LEVEL: 3
              OPERATION: TblLkTtreeScan
              TBLNAME: EAGBE00.RETRIEVED_USER_T
              IXNAME: RETRIEVED_USER_T
              INDEXED CONDITION: RETRIEVED_USER_T.QIID >= EAGBE00.WORK_ITEM_T.QIID
              NOT INDEXED: <NULL>


              STEP: 7
              LEVEL: 2
              OPERATION: MergeJoin
              TBLNAME: <NULL>
              IXNAME: <NULL>
              INDEXED CONDITION: EAGBE00.WORK_ITEM_T.QIID = EAGBE00.RETRIEVED_USER_T.QIID
              NOT INDEXED: <NULL>


              STEP: 8
              LEVEL: 2
              OPERATION: TmpTable
              TBLNAME: <NULL>
              IXNAME: <NULL>
              INDEXED CONDITION: <NULL>
              NOT INDEXED: WI.OBJECT_ID = TASK_INSTANCE_T.TKIID AND WI.EVERYBODY = 0 AND WI.OWNER_ID = QMARK1 AND (WI.REASON = 1 OR WI.REASON = 4)


              STEP: 9
              LEVEL: 2
              OPERATION: NestedLoop
              TBLNAME: <NULL>
              IXNAME: <NULL>
              INDEXED CONDITION: <NULL>
              NOT INDEXED: <NULL>


              STEP: 10
              LEVEL: 1
              OPERATION: Distinct
              TBLNAME: <NULL>
              IXNAME: <NULL>
              INDEXED CONDITION: <NULL>
              NOT INDEXED: <NULL>


              6.Query plan for the same statement from Oracle database



              Execution Plan
              ----------------------------------------------------------
              Plan hash value: 4153604462

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

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

              | Id | Operation | Name | Rows | Bytes | Cost

              (%CPU)| Time |

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

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

              | 0 | SELECT STATEMENT | | 1 | 42 | 8

              8 (2)| 00:00:02 |

              | 1 | SORT GROUP BY | | 1 | 42 |
              | |

              |* 2 | HASH JOIN | | 52 | 2184 | 8

              8 (2)| 00:00:02 |

              | 3 | VIEW | WORK_ITEM | 191 | 1910 | 7

              5 (2)| 00:00:01 |

              | 4 | UNION-ALL | | | |
              | |

              |* 5 | TABLE ACCESS FULL | WORK_ITEM_T | 59 | 1652 | 2

              2 (0)| 00:00:01 |

              |* 6 | TABLE ACCESS BY INDEX ROWID| WORK_ITEM_T | 23 | 644 | 1

              5 (0)| 00:00:01 |

              |* 7 | INDEX RANGE SCAN | WI_AUTH_R | 108 | |
              4 (0)| 00:00:01 |

              |* 8 | TABLE ACCESS BY INDEX ROWID| WORK_ITEM_T | 23 | 644 | 1

              5 (0)| 00:00:01 |

              |* 9 | INDEX RANGE SCAN | WI_AUTH_R | 108 | |
              4 (0)| 00:00:01 |

              | 10 | NESTED LOOPS | | 86 | 5676 | 2

              2 (0)| 00:00:01 |

              |* 11 | TABLE ACCESS FULL | WORK_ITEM_T | 216 | 9072 | 2

              2 (0)| 00:00:01 |

              |* 12 | INDEX UNIQUE SCAN | SYS_C0037783 | 1 | 24 |
              0 (0)| 00:00:01 |

              |* 13 | TABLE ACCESS FULL | TASK_INSTANCE_T | 236 | 7552 | 1

              3 (0)| 00:00:01 |

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

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


              Predicate Information (identified by operation id):
              ---------------------------------------------------

              2 - access("WI"."OBJECT_ID"="TASK_INSTANCE_T"."TKIID")
              5 - filter("WORK_ITEM_T"."AUTH_INFO"=1 AND ("WORK_ITEM_T"."REASON"=1 OR
              "WORK_ITEM_T"."REASON"=4) AND "WORK_ITEM_T"."OWNER_ID"='169403' AN

              D

              "WORK_ITEM_T"."EVERYBODY"=0)
              6 - filter("WORK_ITEM_T"."OWNER_ID"='169403' AND "WORK_ITEM_T"."EVERYBODY"=0)

              7 - access("WORK_ITEM_T"."AUTH_INFO"=2)
              filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("REASON"))=1 OR
              SYS_OP_UNDESCEND(SYS_OP_DESCEND("REASON"))=4)
              8 - filter("WORK_ITEM_T"."OWNER_ID"='169403' AND "WORK_ITEM_T"."EVERYBODY"=0)

              9 - access("WORK_ITEM_T"."AUTH_INFO"=3)
              filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("REASON"))=1 OR
              SYS_OP_UNDESCEND(SYS_OP_DESCEND("REASON"))=4)
              11 - filter("WORK_ITEM_T"."AUTH_INFO"=0 AND ("WORK_ITEM_T"."REASON"=1 OR
              "WORK_ITEM_T"."REASON"=4) AND "WORK_ITEM_T"."EVERYBODY"=0)
              12 - access("WORK_ITEM_T"."QIID"="RETRIEVED_USER_T"."QIID" AND
              "RETRIEVED_USER_T"."OWNER_ID"='169403')
              13 - filter("TASK_INSTANCE_T"."KIND"=105 AND ("TASK_INSTANCE_T"."STATE"=2 OR
              "TASK_INSTANCE_T"."STATE"=8) AND "TASK_INSTANCE_T"."IS_INLINE"=1 A

              ND

              "TASK_INSTANCE_T"."IS_SUSPENDED"=0 AND "TASK_INSTANCE_T"."IS_ESCAL

              ATED"=0)


              7.The timing for the statement in Oracle and in TimesTen, preferably from a command line tool such as SQL*Plus or ttIsql rather than the application.

              TimesTen timing ->
              Execution time (SQLExecute + Fetch Loop) = 0.232060 seconds.

              Oracle timing -> 00:00:00:03


              8.Information on how you do the timing in the application code. For example, does the timing include connect/disconnect or just query execution.

              We are using RPT tool for performance testing . The response time is of the page which displays the count . It includes database connection + query exceution + db disconnect time .
              • 4. Re: lower performance for select in our setup
                Chrisjenkins-Oracle
                Okay, several comments here including some things you have to fix...

                1. You must* compute optimiser statistics in TimesTen. Without these the optimizer is flying blind and these are actually quiet complex queries. From a ttIsql session do:

                statsupdate owner.tablename;

                for each table (not cache group). For example:

                statsupdate EAGBE00.WORK_ITEM_TIMESTEN;
                statsupdate EAGBE00.QUERYABLE_VARIABLE_INSTANCE_T;

                and so on.

                2. You really need MemoryLock=4 (better still huge pages configured and used) for a database of this size. Please check documentation and try to at least enable MemoryLock=4.

                3. Your timing methodology is invalid. It is invalid for Oracle DB (since it includes database connect/disconnect time) but it is totally bogus for TimesTen. Unless you have changed
                the default ramPolicy then the default behaviour for TimesTen is to load the database into memory when something connects and unload when the last connection closes.
                So, it is quite possible that your 'query time' includes a lot of time for loading/unloading the database. To avoid this you can manually load the database into memory before you
                start your tests as follows:

                ttAdmin -ramPolicy manual TTEAG

                ttAdmin -ramLoad TTEAG

                If you do this then you need to be sure to properly shutdown the database when you have finished with it...

                ttAdmin -ramUnload TTEAG

                If you want to set the behaviour back to the default:

                ttAdmin -ramPolicy inUse TTEAG

                Note that even with the database pre-loaded, connect/disconnect is very expensive. If you are running short queries like this then you will lose most of the benefit of TimesTen if
                you open and close a connection for each query. You should change the application to use long lived persistent connections and to to prepare then just once and then execute
                many times using parameterised inputs. Of course this will bring big benefits in Oracle as well as in TimesTen.

                Please apply these changes and then report back on how the timings look. It is likely there is still some query plan optimisation to be done but until you have optimsier stats in place and we can look at the 'real' plans it is hard to say much.

                Chris
                • 5. Re: lower performance for select in our setup
                  856103
                  Hi,

                  Please find the update on the timings after applying the changes .

                  1.You must compute optimiser statistics in TimesTen.

                  We have got huge performance improvement after updating statistics on the timesten tables . The response time is now down to 0.04 secs .

                  The explain plan for the query after updating the statistics is :-

                  Command> explain SELECT COUNT (DISTINCT TA.TKIID ) FROM EAGBE00.WORK_ITEM WI, EA GBE00.TASK TA WHERE (WI.OBJECT_ID = TA.TKIID) AND (TA.KIND IN (105 )and TA.STATE IN (2 ,8 )and WI.REASON IN (1 ,4 )and TA.IS_ESCALATED =0 and TA.SUSPENDED =0 an d TA.IS_INLINE =1 and WI.OWNER_ID ='169403' AND WI.EVERYBODY =0);

                  Query Optimizer Plan:

                  STEP: 1
                  LEVEL: 2
                  OPERATION: RowLkTtreeScan
                  TBLNAME: EAGBE00.WORK_ITEM_T
                  IXNAME: WI_AUTH_E
                  INDEXED CONDITION: WORK_ITEM_T.AUTH_INFO = 1
                  NOT INDEXED: <NULL>


                  STEP: 2
                  LEVEL: 2
                  OPERATION: RowLkTtreeScan
                  TBLNAME: EAGBE00.WORK_ITEM_T
                  IXNAME: WI_AUTH_E
                  INDEXED CONDITION: WORK_ITEM_T.AUTH_INFO = 2
                  NOT INDEXED: <NULL>


                  STEP: 3
                  LEVEL: 2
                  OPERATION: RowLkTtreeScan
                  TBLNAME: EAGBE00.WORK_ITEM_T
                  IXNAME: WI_AUTH_E
                  INDEXED CONDITION: WORK_ITEM_T.AUTH_INFO = 3
                  NOT INDEXED: <NULL>


                  STEP: 4
                  LEVEL: 3
                  OPERATION: RowLkTtreeScan
                  TBLNAME: EAGBE00.WORK_ITEM_T
                  IXNAME: WI_AUTH_U
                  INDEXED CONDITION: WORK_ITEM_T.AUTH_INFO = 0
                  NOT INDEXED: <NULL>


                  STEP: 5
                  LEVEL: 3
                  OPERATION: TblLkTtreeScan
                  TBLNAME: EAGBE00.RETRIEVED_USER_T
                  IXNAME: RETRIEVED_USER_T
                  INDEXED CONDITION: RETRIEVED_USER_T.QIID >= WORK_ITEM_T.QIID
                  NOT INDEXED: <NULL>


                  STEP: 6
                  LEVEL: 2
                  OPERATION: MergeJoin
                  TBLNAME: <NULL>
                  IXNAME: <NULL>
                  INDEXED CONDITION: WORK_ITEM_T.QIID = RETRIEVED_USER_T.QIID
                  NOT INDEXED: <NULL>


                  STEP: 7
                  LEVEL: 2
                  OPERATION: TmpTable
                  TBLNAME: <NULL>
                  IXNAME: <NULL>
                  INDEXED CONDITION: <NULL>
                  NOT INDEXED: WI.EVERYBODY = 0 AND WI.OWNER_ID = '169403' AND (WI.REASO N = 1 OR WI.REASON = 4)


                  STEP: 8
                  LEVEL: 2
                  OPERATION: RowLkTtreeScan
                  TBLNAME: EAGBE00.TASK_INSTANCE_T
                  IXNAME: TI_TI_KND_ST
                  INDEXED CONDITION: TASK_INSTANCE_T.TKIID = WI.OBJECT_ID AND TASK_INSTANCE_T. KIND = 105
                  NOT INDEXED: TASK_INSTANCE_T.IS_INLINE = 1 AND TASK_INSTANCE_T.IS_SUSP ENDED = 0 AND (TASK_INSTANCE_T.STATE = 2 OR TASK_INSTANCE_T.STATE = 8) AND TASK_ INSTANCE_T.IS_ESCALATED = 0


                  STEP: 9
                  LEVEL: 2
                  OPERATION: NestedLoop
                  TBLNAME: <NULL>
                  IXNAME: <NULL>
                  INDEXED CONDITION: <NULL>
                  NOT INDEXED: <NULL>


                  STEP: 10
                  LEVEL: 1
                  OPERATION: Distinct
                  TBLNAME: <NULL>
                  IXNAME: <NULL>
                  INDEXED CONDITION: <NULL>
                  NOT INDEXED: <NULL>


                  2.)Memlock = 4 setting .

                  After this setting we have not recieved any further impovement in the performance .

                  3.)Rampolicy for timesten database .

                  Currently we have the rampolicy is set to inUse . However we have a data source configured in weblogic server which maintains connection pool . Hence the connections to the timesten databse is always there .In our app code we do getConnection() from this data source. What we think is that the database is not unloaded from memory as long as the weblogic server is up.

                  However the performance of the Oracle database is still better and is about 0.01 secs. Please suggest if further changes are required to improve the performance . We are looking at results at par or less than oracle's response time i.e. 0.01 sec.
                  • 6. Re: lower performance for select in our setup
                    Chrisjenkins-Oracle
                    Some more things to look at here are:

                    STEP: 8
                    LEVEL: 2
                    OPERATION: RowLkTtreeScan
                    TBLNAME: EAGBE00.TASK_INSTANCE_T
                    IXNAME: TI_TI_KND_ST
                    INDEXED CONDITION: TASK_INSTANCE_T.TKIID = WI.OBJECT_ID AND TASK_INSTANCE_T. KIND = 105
                    NOT INDEXED: TASK_INSTANCE_T.IS_INLINE = 1 AND TASK_INSTANCE_T.IS_SUSP ENDED = 0 AND (TASK_INSTANCE_T.STATE = 2 OR TASK_INSTANCE_T.STATE = 8) AND TASK_ INSTANCE_T.IS_ESCALATED = 0

                    You want to get as many of these predicates into the INDEXED CONDITION. You should be able to do this by adding the columns IS_INLINE, IS_ESCALATED and IS_SUSP_ENDED into the index TI_TI_KND_ST.

                    STEP: 6
                    LEVEL: 2
                    OPERATION: MergeJoin
                    TBLNAME: <NULL>
                    IXNAME: <NULL>
                    INDEXED CONDITION: WORK_ITEM_T.QIID = RETRIEVED_USER_T.QIID
                    NOT INDEXED: <NULL>

                    MergeJoins are often not so good for response time. In ttIsql you can use an optimiser hint (trymergejoin 0) to ask the optimiser to use a nested loop join instead. Note that optimiser hints have transaction scope (commit or rollback resets all hints) so you have to disable autocommit (autocommit 0) in order to use hints. If this helps you can also issue the same hints from application code.

                    STEP: 7
                    LEVEL: 2
                    OPERATION: TmpTable
                    TBLNAME: <NULL>
                    IXNAME: <NULL>
                    INDEXED CONDITION: <NULL>
                    NOT INDEXED: WI.EVERYBODY = 0 AND WI.OWNER_ID = '169403' AND (WI.REASO N = 1 OR WI.REASON = 4)

                    This step is materialising a temporary table. If one can eliminate this (might not be possible) it will for sure help.


                    Chris
                    • 7. Re: lower performance for select in our setup
                      856103
                      Hi,

                      We have noticed that CACHE HITS is always 0 after several runs of the query as seen in the output of monitor command.
                      Hence we suspect that the query is being propagated to the Oracle database instead of being
                      run from the TimesTen cache.
                      Is there any way to determine for sure that this is the case?
                      Is there anyway we can force the query to run in timesten cache if previous is the case?
                      And what would be the performance improvements in this case ?

                      Currently we are able to achieve very good performance results when the count returned by the query is small .
                      But when the number of records go high the performance degrades very much even after updating the statistics.
                      • 8. Re: lower performance for select in our setup
                        Chrisjenkins-Oracle
                        Your cache groups are not dynamic (assuming the schema you posted previously has not changed) so there should not be any dynamic load occurring; the CACHE HITS columns is only relevant if you have one or more dynamic cache groups defined. If you do have dynamic cache groups then the SYS.MONITOR columns TPL_FETCHES and TPL_EXECS being non zero will indicate dynamic load activity against Oracle.

                        The only other way that queries can get passed to Oracle is via PassThrough but this will only happen if you have set PassThrough explicitly in your sys.odbc.ini settings, in the application connection string or programatically from the application. SYS.MONITOR.PASSTHROUGH_COUNT will show if any passthrough is occurring.

                        Did you optimize the query as I recommended earlier (changing indexing, eliminating MergeJoin)? If not then you need to do that as otherwise the performance could indeed degrade as the number of rows increases.

                        Chris