使用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锁信息。
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==============================================
$ 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
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==============================================