使用ttXactAdmin、ttSQLCmdCacheInfo、ttSQLCmdQueryPlan获取SQL相关详细信息[TimesTen运维]
来源:互联网 发布:mysql打不开闪退 编辑:程序博客网 时间:2024/04/29 22:06
使用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==============================================
0 0
- 使用ttXactAdmin、ttSQLCmdCacheInfo、ttSQLCmdQueryPlan获取SQL相关详细信息[TimesTen运维]
- timesten ttXactAdmin
- timesten获取sql相关执行计划
- 使用ShellClass获取文件属性详细信息
- sqlserver 利用sql获取表详细信息
- TimeSten相关收集
- oracle获取当前用户表、字段详细信息SQL
- DB2获取当前用户表、字段详细信息SQL
- 一键获取SQL的各种详细信息--脚本
- 使用TraceMon分析TimesTen查询最大连续内存块瞬间Hang问题[TimesTen运维]
- Timesten 使用笔记
- 使用WMI获取远程机器操作系统的详细信息
- 使用 WPF 获取你电脑上所有目录的详细信息:
- 使用CreateToolhelp32Snapshot获取指定进程详细信息的方法
- android actionbar中share使用、图片详细信息获取
- 获取系统版本详细信息
- 获取order的详细信息
- edid 获取显示器详细信息
- Web服务器管理系列:6、网络和共享中心的安全配置
- 我的编程生涯
- 简历的问题
- DBCP使用apache
- Java中的多线程的理解
- 使用ttXactAdmin、ttSQLCmdCacheInfo、ttSQLCmdQueryPlan获取SQL相关详细信息[TimesTen运维]
- GenerationType
- 如何自定义sequence
- Oracle学习笔记安装篇之在Redhat Enterprise Linux 6.3 x86_64下安装Oracle11g R2
- Xianfeng轻量级Java中间件平台:权限管理
- 系统分析与设计——活动图画法
- Android项目结构
- using namespace std;
- JAVA中代码段的执行顺序