This discussion is archived
12 Replies Latest reply: Jul 24, 2012 2:46 AM by ChrisJenkins RSS

Analyze index

880143 Newbie
Currently Being Moderated
Hi all,
I have a table in TimesTen DB, which accessing frequency (insert, update, delete) is very high.
I want to analyze indexes of this table to increase inserting speed, like alter index <index_name> analyze in Oracle.

Thanks for your help!

Edited by: LeoBon on Jul 17, 2012 9:45 AM
  • 1. Re: Analyze index
    rajeshp Explorer
    Currently Being Moderated
    Timesten does provide some built procedures to achieve this. You can use

    call ttOptEstimateStats('tblName', invalidate, 'sampleStr')
    or
    call ttOptUpdateStats( 'tblName', 1 )

    More details available here

    http://docs.oracle.com/cd/E21901_01/doc/timesten.1122/e21643/proced.htm#CHDJEJFG


    Regards
    Rajesh
  • 2. Re: Analyze index
    880143 Newbie
    Currently Being Moderated
    Thank rajeshp,
    when I run: CALL ttOptUpdateStats ('<table_name>', 1);, one table met error:
    TT0907: Unique constraint (COL_STATS.TBLID.COLN on SYS.COL_STATS) violated at Rowid

    What should I do to solve this prob?

    Thank all!
  • 3. Re: Analyze index
    jspalmer Journeyer
    Currently Being Moderated
    This apparently means that two sessions or threads were trying to update the same statistics at the same time. Did you have multiple sessions performing ttOptUpdateStats() simultaneously? It's a trivial error anyway. I would have thought that if you ran ttOptUpdateStats() on each user table consecutively, then you should avoid this error.
  • 4. Re: Analyze index
    880143 Newbie
    Currently Being Moderated
    Hi jspalmer,
    I have just one thread, update my tables one by one. My business is:
    - Open TimesTen connection
    - call function updateStatistic(connection, table_1)
    - call function updateStatistic(connection, table_2)
    - call function updateStatistic(connection, table_3)
    - call function updateStatistic(connection, table_4)
    - Close TimesTen connection

    My function does:
    - stmt = connection.createStatement();
    - stmt.execute("call ttOptUpdateStats(table_name, 1)");
    - connection.commit();

    I met error while updating statistic of table_4.
    Any suggestions for me?
  • 5. Re: Analyze index
    jspalmer Journeyer
    Currently Being Moderated
    I think we'll need a testcase to understand what's going on here. Are you able to open an SR with Support which includes a testcase, so we can work through trying to reproduce it here? If we need to do tracing of locks or latches, that isn't something we can really do via the forum. I don't know why you'd get the error otherwise if you're updating stats sequentially on user tables using a single connection.
  • 6. Re: Analyze index
    880143 Newbie
    Currently Being Moderated
    Hi all,
    I describe my case in detail:

    - I have a table on TimesTen DB:
    + 80 cols, ~ 800.000 rows
    + one primary key (2 cols), 5 indexes on separate column
    + Time-based data aging to automatically delete old data (just keep data of 3 recent days, checking cycle is 5 minutes)

    - One java process to synchronize changed data of this table from Oracle to TimesTen DB.
    - Insert, update, delete is done on Oracle, select from TimesTen DB.

    My problem is: After 2-3 days, accessing speed to this table is quite slow (I count time).

    Any suggestions for me ??

    Thanks in advance!
  • 7. Re: Analyze index
    880143 Newbie
    Currently Being Moderated
    Hi all,
    I describe my case in detail:

    - I have a table on TimesTen DB:
    + 80 cols, ~ 800.000 rows
    + one primary key (2 cols), 5 indexes on separate column
    + Time-based data aging to automatically delete old data (just keep data of 3 recent days, checking cycle is 5 minutes)

    - One java process to synchronize changed data of this table from Oracle to TimesTen DB.
    - Insert, update, delete is done on Oracle, select from TimesTen DB.

    My problem is: After 2-3 days, accessing speed to this table is quite slow (I count time).

    Any suggestions for me ??

    Thanks in advance!
  • 8. Re: Analyze index
    880143 Newbie
    Currently Being Moderated
    Hi all,
    I describe my case in detail:

    - I have a table on TimesTen DB:
    + 80 cols, ~ 800.000 rows
    + one primary key (2 cols), 5 indexes on separate column
    + Time-based data aging to automatically delete old data (just keep data of 3 recent days, checking cycle is 5 minutes)

    - One java process to synchronize changed data of this table from Oracle to TimesTen DB.
    - Insert, update, delete is done on Oracle, select from TimesTen DB.

    My problem is: After 2-3 days, accessing speed to this table is quite slow (I count time).

    Any suggestions for me ??

    Thanks in advance!
  • 9. Re: Analyze index
    880143 Newbie
    Currently Being Moderated
    Hi all,
    I describe my case in detail:

    - I have a table on TimesTen DB:
    + 80 cols, ~ 800.000 rows
    + one primary key (2 cols), 5 indexes on separate column
    + Time-based data aging to automatically delete old data (just keep data of 3 recent days, checking cycle is 5 minutes)

    - One java process to synchronize changed data of this table from Oracle to TimesTen DB.
    - Insert, update, delete is done on Oracle, select from TimesTen DB.

    My problem is: After 2-3 days, accessing speed to this table is quite slow (I count time).

    Any suggestions for me ??

    Thanks in advance!
  • 10. Re: Analyze index
    ChrisJenkins Guru
    Currently Being Moderated
    There are many possible reasons why the queries get slower. Non-optimal execution plans are just one of them. Some questions:

    1. Does the rowcount of the table increase over time or are there always ~800K rows in it?
    2. How often do you update optimiser statistics for this table and what call do you use to do so?
    3. Have you checked the plans for the queries executed in TimesTen (use explain command in ttIsql)? Do the plans look good?

    I'm also curious as to why you use a java process to synchronise data from oracle to TimesTen rather than using e.g. a READONLY cache group in TimesTen, though this doesn't really have any bearing on your question.

    Chris
  • 11. Re: Analyze index
    880143 Newbie
    Currently Being Moderated
    Hi ChrisJenkins,

    1. Does the rowcount of the table increase over time or are there always ~800K rows in it?
    I set data aging for this table, so number of rows of this table always about 800k rows
    2. How often do you update optimiser statistics for this table and what call do you use to do so?
    I run 2 times/ a day
    3. Have you checked the plans for the queries executed in TimesTen (use explain command in ttIsql)? Do the plans look good?
    I've checked, my query uses indexes in query plan.
    (I wonder why the first time I create table, load full data from Oracle, accessing operation (insert, update,...) is quite fast; however, after 2-3 days, accessing speed slower. Is data aging the cause?
    I'm also curious as to why you use a java process to synchronise data from oracle to TimesTen rather than using e.g. a READONLY cache group in TimesTen.
    My Oracle DB use characterset that TimesTen doesn't support
    Any suggestions for me?

    Thanks!
  • 12. Re: Analyze index
    ChrisJenkins Guru
    Currently Being Moderated
    If the plans use indexes and most/all of the predicate evaluation is in the INDEXED: part as opposed to the NOT INDEXED: part then yoiu are probably okay from that side of things (if you are doing joins etc. you should look at those aspects of the plans too of course). Assuming the plans are good and remain good as the data changes hen the issue is likely elsewhere.

    On the TimesTen side do you see any increase in lock contention (SYS.MONITOR.LOCK_TIMEOUTS, LOCK_GRANTS_WAIT, DEADLOCKS per minute) when things are going slower compared to when they are faster? What about latch activity in TimesTen (ttXactAdmin -latchstats) for the good versus bad times?

    Have you monitored memory usage on the system as a whole? Are you running low on memory over time so that paging activity increases and affects performance? What about Java garbage collection?

    What does the application itself do; you mentioned the java process to sync the data but presumably there is also some application accessing the data in TimesTen? Does it have memory issues? Garbage collection issues?

    You need to look at everything; TimesTen does not just 'get slower' over time for no good reason and the reason is not always internal to the database.

    Chris

Legend

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