timesten ttXactAdmin

无论是在Oracle运维、TimesTen的运维还是做其他数据库的运维，在实际工作的过程中都少不了对SQL的分析和优化；在TimesTen中，没有像Oracle那样提供10046/10053等事件对SQL执行进行诊断；也没有HIST历史表用于SQL语句的查找，只能使用ttXactAdmin、ttSQLCmdCacheInfo、ttSQLCmdQueryPlan获取SQL的实时相关详细信息；该方式只适合于tt11以上版本，tt11.2.2.x版本还提供了 ttSQLCmdCacheInfo2使得SQL语句的执行信息更加完整 。
－先查看实验的TT版本：
$ ttversion
TimesTen Release 11.2.2.4.3 (64 bit Linux/x86_64) (tt1122:53396) 2013-02-09T17:19:52Z
  Instance admin: timesten
  Instance home directory: /TimesTen/tt1122
  Group owner: timesten
  Daemon home directory: /TimesTen/tt1122/info
  PL/SQL enabled.
－创建实验使用的表：
Command> create table ty(tid tt_smallint,tname varchar2(20));
－插入实验数据：
Command> set autocommit 0;
Command> insert into ty values(1,'tangyun');
1 row inserted.
－使用ttxactadmin查看未提交的事务相关信息
Command> host ttxactadmin tytt
2014-06-14 15:58:30.301
/ttchk/DataStore/11g/TYTT/TYTTdata
TimesTen Release 11.2.2.4.3

Outstanding locks

PID     Context            TransID     TransStatus Resource  ResourceID           Mode  SqlCmdID             Name

Program File Name: ttIsqlCmd

3473    0x14a82610            1.5      Active      Database  0x01312d0001312d00   IX    0                    
                                                   Row       BMUFVUAAABoAAAAAAO   Xn     135710768             TIMESTEN.TY
                                                   Table     1732208              IXn    135710768             TIMESTEN.TY

1 outstanding transaction found
－上面插入一行记录可以看到Database的IX锁和table的IXn和Row的Xn锁信息。

－关于TimesTen的IX锁，可以参考：

http://blog.itpub.net/24930246/viewspace-1183227/

http://blog.csdn.net/tangyun_/article/details/30902729

－这个时候如果对表进行DDL，将会超时退出
Command> alter table ty add tdept varchar2(20);
 6003: Lock request denied because of time-out
Details: Tran 2.8 (pid 3604) wants X lock on table TIMESTEN.TY. But tran 1.5 (pid 3473) has it in IXn (request was IXn). Holder SQL (insert into ty values(1,'tangyun'))
The command failed.

－下面获取SQL信息
Command> call ttsqlcmdcacheinfo(135710768);
< 135710768, 2048, 1, 1, 0, 1, 1640, TIMESTEN                       , insert into ty values(1,'tangyun') >
1 row found.
Command> call ttsqlcmdqueryplan(135710768);
< 135710768, insert into ty values(1,'tangyun'), <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL> >
< 135710768, <NULL>, 1, 1, Insert                         , TY                             , TIMESTEN                       ,                                , ,  >
2 rows found.

－使用列模式显示：
Command> vertical 1;
Command> call ttsqlcmdcacheinfo(135710768);

  SQLCMDID:                        135710768
  PRIVATE_COMMAND_CONNECTION_ID:   2048
  EXECUTIONS:                      1
  PREPARES:                        1
  REPREPARES:                      0
  FREEABLE:                        1
  SIZE:                            1640
  OWNER:                           TIMESTEN
  QUERYTEXT:                       insert into ty values(1,'tangyun')

1 row found.
Command> call ttsqlcmdqueryplan(135710768);

  SQLCMDID:         135710768
  QUERYTEXT:        insert into ty values(1,'tangyun')
  STEP:             <NULL>
  LEVEL:            <NULL>
  OPERATION:        <NULL>
  TABLENAME:        <NULL>
  TABLEOWNERNAME:   <NULL>
  INDEXNAME:        <NULL>
  INDEXEDPRED:      <NULL>
  NONINDEXEDPRED:   <NULL>


  SQLCMDID:         135710768
  QUERYTEXT:        <NULL>
  STEP:             1
  LEVEL:            1
  OPERATION:        Insert
  TABLENAME:        TY
  TABLEOWNERNAME:   TIMESTEN
  INDEXNAME:        
  INDEXEDPRED:      
  NONINDEXEDPRED:   

2 rows found.
Command> 
－提交后，锁的信息将不存在。
Command> commit;
Command> host ttxactadmin tytt
2014-06-14 16:00:13.051
/ttchk/DataStore/11g/TYTT/TYTTdata
TimesTen Release 11.2.2.4.3

0 outstanding transactions found
Command> 
－还可以使用 explain plan for sqlcmdid 查看相应的执行计划
Command> explain plan for sqlcmdid 135710768;

Query Optimizer Plan:
 Query Text: insert into ty values(1,'tangyun')

  STEP:             1
  LEVEL:            1
  OPERATION:        Insert
  TABLENAME:        TY
  TABLEOWNERNAME:   TIMESTEN
  INDEXNAME:        
  INDEXEDPRED:      
  NONINDEXEDPRED:   

Command> 
也可以使用showplan命令，然后执行SQL以显示相应的执行计划