使用ttXactAdmin、ttSQLCmdCacheInfo、ttSQLCmdQueryPlan获取SQL相关具体信息[TimesTen运维]

使用ttXactAdmin、ttSQLCmdCacheInfo、ttSQLCmdQueryPlan获取SQL相关具体信息，适合于tt11以上版本号。

$ 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.****.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以显示对应的运行计划。



=====================End==============================================