Technical Blog Post
Abstract
'STMTID' the 2nd episode - Understanding by examples
Body
Some years ago, I wrote a blog about using 'STMTID' to track long term performance changes for SQLs.
( Blog link : 'STMTID' - The key for long term performance tracking of an SQL )
Has anyone of you ever applied to your system ?
Intermittently, I have gotten personal contacts asking to give some examples showing how it works.
So here let me introduce very simple examples just to share what I meant.
To remind, 'mon_get_pkg_cache_stmt' function has kinds of ID columns.
- EXECUTABLE_ID : It always changes whenever the same SQL is compiled into package cache.
- STMT_PKG_CACHE_ID : internal package cache identifier (ID) for a dynamic SQL statement
Usually does not change even after multiple compilation and restarting database.
But from my experience on a heavy workload system, it appears not to be consistent when restarting occurs.
( On this blog, we are not interested about why. )
By the way, it's still be able to be used for same SQL tracking purpose, but no guarantee to give the same value always.
- STMTID : The hash key value that identifies normalized statement text that is associated with a section.
This is supported since DB2 V10.5 FP3.
1. Firstly, let's see how it looks like. I compared IDs before and after DB reactivation on a marked SQL.
- You can see EXECUTABLE_ID changes for the same SQL whereas 'STMT_PKG_CACHE_ID' is same.
2. So let's say that we are tracking an interested SQL.
Here I created sample tables with 1 million rows per each and made a change creating index.
This is just an example to give you the idea what to do in real database furthermore.
2-1. creating tables and load data for the test.
$ db2 "create table A ( key int not null , f1int int , f2char20 char(20) , f3char30 char(30) , f4int int , f5char30 char(30) ) "
$ db2 -v "load from data4.unl of del modified by coldel| replace into AAA nonrecoverable"
$ db2 "create table BBB ( key int not null , f1int int , f2char20 char(20) , f3char30 char(30) , f4int int , f5char30 char(30) ) "
$ db2 -v "load from data5.unl of del modified by coldel| replace into BBB nonrecoverable"
2-2. Run a SQL to track for sample.
Run the SQL to track.
$ db2 "select * from AAA , BBB where AAA.key < 50000 and AAA.key = BBB.key"
2-3. Get STMTID of the SQL.
STMTID is -3791034270387023455
$ db2 "select executable_id, STMT_PKG_CACHE_ID, semantic_env_id, STMTID, varchar(STMT_TEXT,50) as STMT_TEXT FROM TABLE(MON_GET_PKG_CACHE_STMT(NULL,NULL,NULL,-1)) where STMT_TEXT like '%AAA%'"
EXECUTABLE_ID STMT_PKG_CACHE_ID SEMANTIC_ENV_ID STMTID STMT_TEXT
------------------------------------------------------------------- -------------------- -------------------- -------------------- --------------------------------------------------
x'000000010000000000000000000001D700000000000220170804120245954359' 12894732288 -4484546821111269827 2615904627939437315 select executable_id, STMT_PKG_CACHE_ID, semantic_
SQL0445W Value "select executable_id, STMT_PKG_CACHE_ID, semantic_env_id, ST"
has been truncated. SQLSTATE=01004
x'000000010000000000000000000001D600000000000220170804120236465285' 12915376128 4310297683605654029 -3791034270387023455 select * from AAA , BBB where AAA.key < 50000 and
SQL0445W Value "select * from AAA , BBB where AAA.key < 50000 and AAA.key = "
has been truncated. SQLSTATE=01004
2-4. Get information about the SQL.
Here, I'm just checking Total time and Rows_read for example.
( But there are lots of metrics columns you may be interested. )
$ db2 terminate;db2 deactivate db sample ; db2 activate db sample;db2 connect to sample
$ db2 "select * from AAA , BBB where AAA.key < 50000 and AAA.key = BBB.key"
$ db2 "select TOTAL_ACT_TIME,TOTAL_ACT_WAIT_TIME,ROWS_READ, varchar(STMT_TEXT,50) as STMT_TEXT FROM TABLE(MON_GET_PKG_CACHE_STMT(NULL,NULL,NULL,-1)) where STMTID = -3791034270387023455"
TOTAL_ACT_TIME TOTAL_ACT_WAIT_TIME ROWS_READ STMT_TEXT
-------------------- -------------------- -------------------- --------------------------------------------------
2878 2183 2000000 select * from AAA , BBB where AAA.key < 50000 and
2-5. Made a sample change (index creation).
$ db2 "create index i1 on AAA ( key )"
$ db2 -v runstats on table $USER.AAA with distribution and detailed indexes all
$ db2 "create index i2 on BBB ( key )"
$ db2 -v runstats on table $USER.BBB with distribution and detailed indexes all
$ db2 "select * from AAA , BBB where AAA.key < 50000 and AAA.key = BBB.key”
$ db2 terminate;db2 deactivate db sample ; db2 activate db sample;db2 connect to sample
$ db2 "select TOTAL_ACT_TIME,TOTAL_ACT_WAIT_TIME,ROWS_READ, varchar(STMT_TEXT,50) as STMT_TEXT FROM TABLE(MON_GET_PKG_CACHE_STMT(NULL,NULL,NULL,-1)) where STMTID = -3791034270387023455"
TOTAL_ACT_TIME TOTAL_ACT_WAIT_TIME ROWS_READ STMT_TEXT
-------------------- -------------------- -------------------- --------------------------------------------------
346 346 76 select * from AAA , BBB where AAA.key < 50000 and
Furthermore, you may have the followings in your mind now.
- Collecting top SQLs with MON_GET_PKG_CACHE_STMT periodically.
Then maintain the information into a database so that you can query when necessary.
- Then see the performance trend of SQLs and check when it changes.
- Etc.
Thank you for reading this blog and have a nice weekend.
Jun.
UID
ibm11139980