首页 > 代码库 > 使用ttXactAdmin、ttSQLCmdCacheInfo、ttSQLCmdQueryPlan获取SQL相关详细信息[TimesTen运维]

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

2024-07-08 08:45:07   227人阅读

使用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.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以显示相应的执行计划。

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


联系
我们