This discussion is archived
8 Replies Latest reply: Oct 10, 2012 8:03 AM by ChrisJenkins RSS

lower performance for select in our setup

856103 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points