Get SQL-related details using ttxactadmin, Ttsqlcmdcacheinfo, Ttsqlcmdqueryplan, suitable for tt11 or later.
$ ttversion
TimesTen Release 11.2.2.4.3 (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.
---Use ttxactadmin to view uncommitted transaction-related information
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
135710768TimesTen. TY
Table 1732208 Ixn
135710768TimesTen. TY
1 Outstanding transaction found
---Insert a row above to see the IX lock on database and the XN lock information for table ixn and row.
---The IX lock on TimesTen, you can refer to:
http://blog.itpub.net/24930246/viewspace-1183227/
http://blog.csdn.net/tangyun_/article/details/30902729
This time, if the table is DDL, it will time out
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) had it in the IXN (request was ixn). Holder SQL (insert into Ty values (1, ' Tangyun '))
The command failed.
Get SQL information----below
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>
After the----commits, the lock information will not exist.
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>
----can also use explain plan for Sqlcmdid to view the corresponding execution plan
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>
You can also use the SHOWPLAN command, and then execute SQL to display the corresponding execution plan.
=====================end==============================================
