使用ttXactAdmin、ttSQLCmdCacheInfo、ttSQLCmdQueryPlan獲取SQL相關詳細資訊

無論是在Oracle運維、TimesTen的運維還是做其他資料庫的運維，在實際工作的過程中都少不了對SQL的分析和最佳化；在TimesTen中，沒有像Oracle那樣提供10046/10053等事件對SQL執行進行診斷；也沒有HIST歷史表用於SQL語句的查詢，只能使用ttXactAdmin、ttSQLCmdCacheInfo、ttSQLCmdQueryPlan獲取SQL的實時相關詳細資訊；該方式只適合於tt11以上版本，tt11.2.2.x版本還提供了ttSQLCmdCacheInfo2使得SQL語句的執行資訊更加完整。
－先檢視實驗的TT版本：
$ 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'), , , , , , , , >
< 135710768, , 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:            
  LEVEL:           
  OPERATION:       
  TABLENAME:       
  TABLEOWNERNAME:  
  INDEXNAME:       
  INDEXEDPRED:     
  NONINDEXEDPRED:  


  SQLCMDID:         135710768
  QUERYTEXT:       
  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=============================================