Hi guys... I am back... this time as a Advanced DB2 9.7 DBA... Thanks to Sathy for helping me prepare for the exam and Beth Flood for giving me a free voucher (otherwise I never had guts to put 5k on stake for my capabilities)... But very soon an all new DB2 is expected I am hope it will have hell lot of new features and I will have to upgrade or take the certification exams from scratch...
Well recently I have been working with some scenarios where people came to me with an update statement which was working very slow... When I looked at it, I said "I need to tell people there is something like MERGE"... There was one thing common, all the statements were having correlated sub-queries for set value... I replaced their queries with a MERGE and it worked like wonder...
Let's see some example...
UPDATE Table1
SET T1Col1=(select T2Col1 from Tables2 where Table1.T1IDPK=T2IDPK);
So the problem with this query is the inner query will be executed for every row of table Table1...
So I wrote them a query MERGE, which shall first create a HASH JOIN and then do an RID based update:
MERGE INTO Table1
USING Table2 on (Table1.T1IDPK=Table2.T2IDPK)
WHEN MATCHED THEN UPDATE
SET Table1.T1Col1=Table2.T2Col1;
Another scenario was of a process where a scheduled process will pick up rows from a table (which has designated pagenumbers/rownumers) in batches and after processing certain number of rows in every batch the scheduled process will sleep... Remaining rows shall be renumbered and partitioned in batches...
UPDATE PagedTab t1
set SEQNUM=(select row_number() over(order by PagedTab_IDPKColumn) from PagedTab t2 where t1.PagedTab_IDPKColumn=t2.PagedTab_IDPKColumn);
So I just changed it to below:
MERGE INTO PagedTab t1
USING (select PagedTab_IDPKColumn,row_number() over(order by PagedTab_IDPKColumn) as rnum from PagedTab ) t2
on t1.PagedTab_IDPKColumn=t2.PagedTab_IDPKColumn
WHEN MATCHED THEN UPDATE
SET t1.SEQNUM=t2.rnum;
I have simulated the below scenrio to make it more easy to be understood as the actual was more complex...
Third place was where there was a Mater-Detail kind (Dependent Entity) kind of relationship and some details were stored in Master Table and for every IDPK in master table one and only one record existed in detail table... ACCOUNTID and LASTACCESSDATE and STATUS were some of the details... some one wanted to first find out the LATEST record (based on date) from detail table for every ACCOUNTID and then find out primary key for that record and then for that PK whatever status is found in detail had to bee copied to master... So here goes the query people will write in first attempt...
UPDATE MasterTable outTab
SET STATUS=
(select STATUS from DetailsTable t2 join
(select ACCOUNTID, MAX(LASTACCESSDATE) DT from DetailTable group by ACCOUNTID)t1
on t1.ACCOUNTID=t2.ACCOUNTID and t2.LASTACCESSDATE=t1.DT and t2.IDPK=outTab.IDPK
)temp;
Actually the query which was written had used one more IN clause which I have converted to JOIN to avoid more confusion...
I modified it...
MERGE into MasterTable t1
USING
(select IDPK, DENSE_RANK() over(PARTITION BY ACCOUNTID order by LASTACCESSDATE) rn, STATUS from DetailsTable) t2
ON (t1.IDPK=t2.IDPK and rn=1)
WHEN MATCHED THEN UPDATE
SET STATUS=t2.STATUS;
And this avoided a join and sort which was being done for row... Did a single sort a hash join and an update based on RID... Wow!!!
One final instance where I saw something like this:
UPDATE TableX X
SET
Col1=(select Col1 from TableY Y where X.IDPK=Y.IDPK),
Col2=(select Col2 from TableY Y where X.IDPK=Y.IDPK),
Col3=(select Col3 from TableY Y where X.IDPK=Y.IDPK),
Col4=(select Col4 from TableY Y where X.IDPK=Y.IDPK)
;
and then
INSERT INTO TableX (COl1, COl2, COl3, COl4)
Select COl1, COl2, COl3, Col4 from TableY Y
where not exists (select 1 from TableX X where X.IDPK=Y.IDPK);
and finally...
DELETE FROM TableX X where not exists (select 1 from TableY where X.IDPK=Y.IDPK);
I will leave it upto the readers to interpret the purpose of these statements... I will just give an alternate query...
MERGE INTO TableX X
USING TableY Y on (X.IDPK=Y.IDPK)
WHEN MATCHED then UPDATE
SET
X.COl1=Y.COl1,
X.COl2=Y.COl2,
X.COl3=Y.COl3,
X.COl4=Y.COl4
WHEN NOT MATCHED THEN INSERT(COl1, COl2, COl3, COl4)
VALUES (Y.COl1, Y.COl2, Y.COl3, Y.COl4)
;
This has combined the UPDATE and INSERT statements and is much more efficient cause the hash join is executed once as opposed to execution of 4 correlated sub-queries for every row in TableX for Update and then for INSERT an anti-join is performed...
The delete can not be optimized or combined in merge which is a little disappointing and I hope to see this feature in DB2 very soon... A clause of "WHEN NOT MATCHED BY SOURCE" exists in SQL Server using which I could have just said:
MERGE INTO TableX X
USING TableY Y on (X.IDPK=Y.IDPK)
WHEN MATCHED then UPDATE
SET
X.COl1=Y.COl1,
X.COl2=Y.COl2,
X.COl3=Y.COl3,
X.COl4=Y.COl4
WHEN NOT MATCHED THEN INSERT(COl1, COl2, COl3, COl4)
VALUES (Y.COl1, Y.COl2, Y.COl3, Y.COl4)
WHEN NOT MATCHED BY SOURCE THEN DELETE
;
Well this blog is bloated too much and I do not want to bore people, you can have multiple WHEN MATCHED and WHEN NOT MATCEHD clauses and combine with another condition (AND col1=col2) and have different different action for every branch, hence you will write one single statement instead of writing one update for every condition... I will try to put some examples sometime...
BTW thanks to all my readers... I feel lucky and honored to be listed in "My Favorite DB2 Blogs" by Troy Coleman @ db2tutor
If you are reading this Mr. Coleman, trust me db2tutor and db2geek are my frist reference points for any query... :) and thanks for putting me on the list...
About Me
- Sameer
- Singapore, Singapore, Singapore
- I am currently working as a DB Consultant with Ashnik. We are into Open Source and Cloud based enterprise class solutions. Prior to this I was working as a DBA @ Misys India (Banking Services). Working on IBM DB2, Oracle Microsoft SQL Server. Selected as IBM Champion for Data Management 2011. I like bit of photography, cycling, poetry and I just love gadgets!!
Connect to me
SQL Anti Logic: Usage of CASE Statement
Hello all... Reviewing queries while development and fixing performance issues in SQLs are part of my job... I get lot of queries where people have written a really bad query and thank god we have this process in place to tune such queries... You can take this as a sequel to my Post on "Anti SQL Logic"
So someone had a requirement of getting Account Statistics from a table storing statistics for every account (I have tweaked the requirement and actual query, a little for abstraction and a little to highlight the significance of improvements done in query) ... Stats are stored in periodical fashion and I can configure a period code to get statistics for a specific period say monthly or say yearly... Additionally I want stats for my current period as well as for my previous period...
Probably a naive approach that come first in mind i below:
1) I get a union of all the possible combinations for previous period and current period. Below is basic query for that...
SELECT AST.* FROM {?schemaName}.ACCSTATSTABLE AST, {?schemaName}.ACCOUNTTABLE ACC
WHERE ACC.ACCOUNTNUMBER = AST.ACCOUNTNUMBER
AND ACC.ACCOUNTNUMBER = '{?inputACCOUNTNUMBER}'
AND 'Q' = (SELECT VALUE FROM CONFIGTABLE WHERE CONFIGNAME='StatsPeriod' )
AND {?FORMONTH} > (AST.STATSPERIOD - 1) * 3 +1 //for current period
--AND {?FORMONTH} <= (AST.STATSPERIOD-1) * 3 //for previous period AND AST.FORYEAR = '{?FORYEAR}'
UNION
SELECT AST.* FROM {?schemaName}.ACCSTATSTABLE AST, {?schemaName}.ACCOUNTTABLE ACC
WHERE ACC.ACCOUNTNUMBER = AST.ACCOUNTNUMBER
AND ACC.ACCOUNTNUMBER = '{?inputACCOUNTNUMBER}'
AND 'H' = (SELECT VALUE FROM CONFIGTABLE WHERE CONFIGNAME='StatsPeriod' )
AND {?FORMONTH} > (AST.STATSPERIOD - 1) * 6 +1 //for current period
AND {?FORMONTH} <= AST.STATSPERIOD * 6 // for previous period AND AST.FORYEAR = {?FORYEAR}
UNION
SELECT AST.* FROM {?schemaName}.ACCSTATSTABLE AST, {?schemaName}.ACCOUNTTABLE ACC
WHERE ACC.ACCOUNTNUMBER = AST.ACCOUNTNUMBER
AND ACC.ACCOUNTNUMBER = '{?inputACCOUNTNUMBER}'
AND 'Y' = (SELECT VALUE FROM CONFIGTABLE WHERE CONFIGNAME='StatsPeriod' )
AND AST.FORYEAR ={?FORYEAR} //for curr period
-- AND AST.FORYEST={?FORYEAR}-1 //for prev period
2) I also get a result of last month/quarter and 2nd-half of previous year result sets (so that if I pass 1st month as my input then I shall get previous years last month ;-o )
3) I join these results sets
4) Use an inner query to get what is the configured period code and then I get stats only for that period code...
Well this is quite procedural and algorithmic... SQLs are not procedural (and anyways I am personally very much against usage of PLs codes with RDBMS especially the cursor based things, well that is debated topic)...
Well I wrote a Structured Query to get what was required making the best I can make of CASE:
1) Select ALL the columns for statitics twice
i) Once actual value if it your period value (e.g. 4th quarter) else select it as 0
ii) Select stats column as 0 if it matches your period value else select actual value
This will help you categorize stats in column wise fashion where current period columns will be holding actual value and 2nd set of columns will be holding data for other periods.
2) now you need to filter the current period and previous data
i) Filtering current period data is pretty easy as you know the period number
ii) For filtering the period number for previous period I used below case logic
(CASE 'M'
WHEN 'M' THEN 2
WHEN 'Q' THEN (2-1/3)+1
WHEN 'H' THEN (2-1/6)+1
WHEN 'Y' THEN AST.STATSPERIOD
END) in
( {PeriodNumber}, //Current Period number
(CASE {?FORMONTH} WHEN 1 THEN //Logic to arrive at previous period
case {PERIODCODE} when 'M' THEN 12 //if monthly then prev month is 12
WHEN 'Q' THEN 4 //if quarterly then prev quarter is 3
WHEN 'H' THEN 2 //if halfyearly then prev period is 2
ELSE {PeriodNumber} //if yearly, prev period number does not matter
END
ELSE {PeriodNumber}-1 end) //finally it should be input period number-1
)
AND AST.FORYEAR IN( {?FORYEAR},
(CASE {?PERIODCODE} WHEN 'Y' THEN {?FORYEAR}-1
else
(case when {PeriodNumber}=1 then {?FORYEAR}-1
//If my input number is 1 then I need to consider prev year stats as well
ELSE {?FORYEAR} END)
END)
3) Now I have got 0 as prev period stats in rows which belong to current period and 0 as current period stats for rows which belong to prev year. All I need to do is sum
SELECT
SUM(temp.CURR_MAXIMUM_BALANCE),
SUM(temp.CURR_MINIMUM_BALANCE),
SUM(temp.CURR_AVG_BALANCE),
SUM(temp.CURR_CREDIT_COUNTER ),
SUM(temp.CURR_INTERESTPAID),
SUM(temp.CURR_CHARGES),
SUM(temp.PREV_MAXIMUM_BALANCE),
SUM(temp.PREV_MINIMUM_BALANCE),
SUM(temp.PREV_AVG_BALANCE),
SUM(temp.PREV_CREDIT_COUNTER ),
SUM(temp.PREV_INTERESTPAID),
SUM(temp.PREV_CHARGES
from
(select
case when AST.STATSPERIOD=2 then MAXIMUM_BALANCE else 0 END as CURR_MAXIMUM_BALANCE,
case when AST.STATSPERIOD=2 then MINIMUM_BALANCE else 0 END as CURR_MAXIMUM_BALANCE,
case when AST.STATSPERIOD=2 then AVG_BALANCE else 0 END as CURR_AVG_BALANCE,
case when AST.STATSPERIOD=2 then CREDIT_COUNTER else 0 END as CURR_CREDIT_COUNTER ,
case when AST.STATSPERIOD=2 then INTERESTPAID else 0 END as CURR_INTERESTPAID,
case when AST.STATSPERIOD=2 then CURR_CHARGES else 0 END as CURR_CHARGES,
case when AST.STATSPERIOD=2 then 0 ELSE MAXIMUM_BALANCE END as PREV_MAXIMUM_BALANCE,
case when AST.STATSPERIOD=2 then 0 ELSE MINIMUM_BALANCE END as PREV_MAXIMUM_BALANCE,
case when AST.STATSPERIOD=2 then 0 ELSE AVG_BALANCE END as PREV_AVG_BALANCE,
case when AST.STATSPERIOD=2 then 0 ELSE CREDIT_COUNTER END as PREV_CREDIT_COUNTER ,
case when AST.STATSPERIOD=2 then 0 ELSE INTERESTPAID END as PREV_INTERESTPAID,
case when AST.STATSPERIOD=2 then 0 ELSE PREV_CHARGES END as PREV_CHARGES,
FROM {?schemaName}.ACCOUNTSTATISTICS AST
[where statement discussed above]
group by ACCOUNTID
I guess I have made it very boring and complex to understand... But I hope it will help people understand how flexible can be usage of CASE statements...
I am looking at putting a content on improving INSERT performance... (this again has come from processing of rows from temp tables, where data is populated in bulk and deleted in bulk)...
So someone had a requirement of getting Account Statistics from a table storing statistics for every account (I have tweaked the requirement and actual query, a little for abstraction and a little to highlight the significance of improvements done in query) ... Stats are stored in periodical fashion and I can configure a period code to get statistics for a specific period say monthly or say yearly... Additionally I want stats for my current period as well as for my previous period...
Probably a naive approach that come first in mind i below:
1) I get a union of all the possible combinations for previous period and current period. Below is basic query for that...
SELECT AST.* FROM {?schemaName}.ACCSTATSTABLE AST, {?schemaName}.ACCOUNTTABLE ACC
WHERE ACC.ACCOUNTNUMBER = AST.ACCOUNTNUMBER
AND ACC.ACCOUNTNUMBER = '{?inputACCOUNTNUMBER}'
AND 'Q' = (SELECT VALUE FROM CONFIGTABLE WHERE CONFIGNAME='StatsPeriod' )
AND {?FORMONTH} > (AST.STATSPERIOD - 1) * 3 +1 //for current period
--AND {?FORMONTH} <= (AST.STATSPERIOD-1) * 3 //for previous period AND AST.FORYEAR = '{?FORYEAR}'
UNION
SELECT AST.* FROM {?schemaName}.ACCSTATSTABLE AST, {?schemaName}.ACCOUNTTABLE ACC
WHERE ACC.ACCOUNTNUMBER = AST.ACCOUNTNUMBER
AND ACC.ACCOUNTNUMBER = '{?inputACCOUNTNUMBER}'
AND 'H' = (SELECT VALUE FROM CONFIGTABLE WHERE CONFIGNAME='StatsPeriod' )
AND {?FORMONTH} > (AST.STATSPERIOD - 1) * 6 +1 //for current period
AND {?FORMONTH} <= AST.STATSPERIOD * 6 // for previous period AND AST.FORYEAR = {?FORYEAR}
UNION
SELECT AST.* FROM {?schemaName}.ACCSTATSTABLE AST, {?schemaName}.ACCOUNTTABLE ACC
WHERE ACC.ACCOUNTNUMBER = AST.ACCOUNTNUMBER
AND ACC.ACCOUNTNUMBER = '{?inputACCOUNTNUMBER}'
AND 'Y' = (SELECT VALUE FROM CONFIGTABLE WHERE CONFIGNAME='StatsPeriod' )
AND AST.FORYEAR ={?FORYEAR} //for curr period
-- AND AST.FORYEST={?FORYEAR}-1 //for prev period
2) I also get a result of last month/quarter and 2nd-half of previous year result sets (so that if I pass 1st month as my input then I shall get previous years last month ;-o )
3) I join these results sets
4) Use an inner query to get what is the configured period code and then I get stats only for that period code...
Well this is quite procedural and algorithmic... SQLs are not procedural (and anyways I am personally very much against usage of PLs codes with RDBMS especially the cursor based things, well that is debated topic)...
Well I wrote a Structured Query to get what was required making the best I can make of CASE:
1) Select ALL the columns for statitics twice
i) Once actual value if it your period value (e.g. 4th quarter) else select it as 0
ii) Select stats column as 0 if it matches your period value else select actual value
This will help you categorize stats in column wise fashion where current period columns will be holding actual value and 2nd set of columns will be holding data for other periods.
2) now you need to filter the current period and previous data
i) Filtering current period data is pretty easy as you know the period number
ii) For filtering the period number for previous period I used below case logic
(CASE 'M'
WHEN 'M' THEN 2
WHEN 'Q' THEN (2-1/3)+1
WHEN 'H' THEN (2-1/6)+1
WHEN 'Y' THEN AST.STATSPERIOD
END) in
( {PeriodNumber}, //Current Period number
(CASE {?FORMONTH} WHEN 1 THEN //Logic to arrive at previous period
case {PERIODCODE} when 'M' THEN 12 //if monthly then prev month is 12
WHEN 'Q' THEN 4 //if quarterly then prev quarter is 3
WHEN 'H' THEN 2 //if halfyearly then prev period is 2
ELSE {PeriodNumber} //if yearly, prev period number does not matter
END
ELSE {PeriodNumber}-1 end) //finally it should be input period number-1
)
AND AST.FORYEAR IN( {?FORYEAR},
(CASE {?PERIODCODE} WHEN 'Y' THEN {?FORYEAR}-1
else
(case when {PeriodNumber}=1 then {?FORYEAR}-1
//If my input number is 1 then I need to consider prev year stats as well
ELSE {?FORYEAR} END)
END)
3) Now I have got 0 as prev period stats in rows which belong to current period and 0 as current period stats for rows which belong to prev year. All I need to do is sum
SELECT
SUM(temp.CURR_MAXIMUM_BALANCE),
SUM(temp.CURR_MINIMUM_BALANCE),
SUM(temp.CURR_AVG_BALANCE),
SUM(temp.CURR_CREDIT_COUNTER ),
SUM(temp.CURR_INTERESTPAID),
SUM(temp.CURR_CHARGES),
SUM(temp.PREV_MAXIMUM_BALANCE),
SUM(temp.PREV_MINIMUM_BALANCE),
SUM(temp.PREV_AVG_BALANCE),
SUM(temp.PREV_CREDIT_COUNTER ),
SUM(temp.PREV_INTERESTPAID),
SUM(temp.PREV_CHARGES
from
(select
case when AST.STATSPERIOD=2 then MAXIMUM_BALANCE else 0 END as CURR_MAXIMUM_BALANCE,
case when AST.STATSPERIOD=2 then MINIMUM_BALANCE else 0 END as CURR_MAXIMUM_BALANCE,
case when AST.STATSPERIOD=2 then AVG_BALANCE else 0 END as CURR_AVG_BALANCE,
case when AST.STATSPERIOD=2 then CREDIT_COUNTER else 0 END as CURR_CREDIT_COUNTER ,
case when AST.STATSPERIOD=2 then INTERESTPAID else 0 END as CURR_INTERESTPAID,
case when AST.STATSPERIOD=2 then CURR_CHARGES else 0 END as CURR_CHARGES,
case when AST.STATSPERIOD=2 then 0 ELSE MAXIMUM_BALANCE END as PREV_MAXIMUM_BALANCE,
case when AST.STATSPERIOD=2 then 0 ELSE MINIMUM_BALANCE END as PREV_MAXIMUM_BALANCE,
case when AST.STATSPERIOD=2 then 0 ELSE AVG_BALANCE END as PREV_AVG_BALANCE,
case when AST.STATSPERIOD=2 then 0 ELSE CREDIT_COUNTER END as PREV_CREDIT_COUNTER ,
case when AST.STATSPERIOD=2 then 0 ELSE INTERESTPAID END as PREV_INTERESTPAID,
case when AST.STATSPERIOD=2 then 0 ELSE PREV_CHARGES END as PREV_CHARGES,
FROM {?schemaName}.ACCOUNTSTATISTICS AST
[where statement discussed above]
group by ACCOUNTID
I guess I have made it very boring and complex to understand... But I hope it will help people understand how flexible can be usage of CASE statements...
I am looking at putting a content on improving INSERT performance... (this again has come from processing of rows from temp tables, where data is populated in bulk and deleted in bulk)...
An Alternative to truncate for pre v9.7
So many a times people have to truncate a table in run time, i.e. while processing a batch or a temp tabel or while flushing a table which stores some periodic stats etc... DB2 9.7 has an Amazingly fast command which will help you do that in flash of a blink called truncate... But ooops you are not using db2 v9.7... you had upgraded to db2 9 or db2 9.5 recently and can not take the effort required to port your application to new version and do all the testing again... No sweat... I will be sharing a few such tips which will help you imitate truncate table
1) Which is generic for all platforms...
ALTER TABLE tablename ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE;
commit;
but I am not very sure if space acquired by table is retained in this case
2) Obviously
ALTER TABLE tablename ACTIVATE NOT LOGGED INITIALLY;
delete from tablename;
commit;
3) This will be specific to a OS platform and I will not suggest using this if you have to code it in your application:
i) *NIX --> import from /dev/null of del replace into tablename
ii) Windows --> import from nul of del replace into tablename
This operation is though faster than normal delete but is logged and hence will not be as performant as first... Some people argue that second option is not recoverable, but we are looking at an alternative for truncate (which is not recoverable either) and hence recovery is not my priority... Moreover I have committed the transaction as soon as I fire "NOT LOGGED" command for "tablename" hence should not be an issue...
1) Which is generic for all platforms...
ALTER TABLE tablename ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE;
commit;
but I am not very sure if space acquired by table is retained in this case
2) Obviously
ALTER TABLE tablename ACTIVATE NOT LOGGED INITIALLY;
delete from tablename;
commit;
3) This will be specific to a OS platform and I will not suggest using this if you have to code it in your application:
i) *NIX --> import from /dev/null of del replace into tablename
ii) Windows --> import from nul of del replace into tablename
This operation is though faster than normal delete but is logged and hence will not be as performant as first... Some people argue that second option is not recoverable, but we are looking at an alternative for truncate (which is not recoverable either) and hence recovery is not my priority... Moreover I have committed the transaction as soon as I fire "NOT LOGGED" command for "tablename" hence should not be an issue...
Improvise your Backup-Restore
Somehow I am managing to keep-up with my busy office life and on call support... Which it seems is literally 24X7...
I find myself out of topic while talking to old mates... Office and being single are only topics I could blabber about... This blogs should give me a change... :)
So this is not going to be a HADR tips or how you should decide your backup schedules... It is more on how you can save your time while taking backups and how you can improvise the speed of restores (and hence reduce non-availability time)...
To start with, it is generally advised that you take backups when you system is not being accessed or it having low-traffic... And well while restoring I won't offer the same advice, you don't actually have much of a choice... Your system is not available and best you can do is to reduce the time it takes...
Your throttle the priority by
SET UTIL_IMPACT_PRIORITY FOR utility_id TO num
which will throttle the backup utility or you can set the same by specifying
"UTIL_IMPACT_PRIORITY" clause in the backup command as well...
We should also recollect that if your UTIL_IMPACT_LIM dbm cfg is set too low, no matter how high you set UTIL_IMPACT_PRIORITY itcan not go beyond the threshold set by UTIL_IMPACT_LIM...
To have the backup operation faster it is good to increase your BACKUP BUFFER SIZE/RESTORE BUFFER SIZE "BACKBUFSZ"/"RESTBUFSZ" dbm cfg or you can override it in backup/restore command with "BUFFER buffer_size"... Also you should choose a good (not your lucky number) number for your number of backup/restore buffers to be used... This has to be decided by your db size and RAM available... It can be set with "WITH n BUFFERS"... Multiple buffers specially boost up the performance of backup when using PRALLELISM... Prallelism decides the number of buffer handlers...
Then comes compression, my favorite... If you think compression slows down the speed of backup due to CPU processing lag which is involved in compression algorithm, then you correct about your analysis but not about final conclusion it leads you to...
Compression algorithm takes some extra CPU cycles but you save a great deal on I/O cycle which are more expensive (in terms of time and their availability)... hence compression will not just save space but time as well... and plus your buffers can be utilized easily... Same applies while you are restoring a backup, you spend less time on reads and more data fits in your buffer and hence your restore is faster...
Additionally going by basic h/w an OS concepts having a backup file on disk which is other than one where your db resides/going to be created (during restore) is often faster... Of course there are other reasons as well for which you should not store backups on same disk where DB resides...
Incremental/delta backups helps you take smaller backups which saves time backing up only
what has changed (or delta of change) and while restoring you have to restore a full backup and then changes are to be applied one by one hence makes the restore slower... But if you use backup/restore to shift data periodically (say once a day or week) to a different server incremental/delta backups are best... Cuase they take less time to be backed-up and then less time to be restored (as your restoring only the diff/incremental backup)...
Finally, snapshot backups using Netapp/Tivoli etc might boost up the back up process drastically... Though those are bit expensive to invest into, but if you have very (and I mean very) large volume of database your investments will be well paid off...
TO close this, backup is nothing but making an image of your database in a single file... Database is stored in datafiles which are spread over various blocks in your disk... Now if those blocks were scattered, backup would be quite slow, and hence it is gives your another reason to keep your db well re-orged... Also if your db is spread across various disks/file system it could boost the performance of backups and restores as well...
I find myself out of topic while talking to old mates... Office and being single are only topics I could blabber about... This blogs should give me a change... :)
So this is not going to be a HADR tips or how you should decide your backup schedules... It is more on how you can save your time while taking backups and how you can improvise the speed of restores (and hence reduce non-availability time)...
To start with, it is generally advised that you take backups when you system is not being accessed or it having low-traffic... And well while restoring I won't offer the same advice, you don't actually have much of a choice... Your system is not available and best you can do is to reduce the time it takes...
Your throttle the priority by
SET UTIL_IMPACT_PRIORITY FOR utility_id TO num
which will throttle the backup utility or you can set the same by specifying
"UTIL_IMPACT_PRIORITY" clause in the backup command as well...
We should also recollect that if your UTIL_IMPACT_LIM dbm cfg is set too low, no matter how high you set UTIL_IMPACT_PRIORITY itcan not go beyond the threshold set by UTIL_IMPACT_LIM...
To have the backup operation faster it is good to increase your BACKUP BUFFER SIZE/RESTORE BUFFER SIZE "BACKBUFSZ"/"RESTBUFSZ" dbm cfg or you can override it in backup/restore command with "BUFFER buffer_size"... Also you should choose a good (not your lucky number) number for your number of backup/restore buffers to be used... This has to be decided by your db size and RAM available... It can be set with "WITH n BUFFERS"... Multiple buffers specially boost up the performance of backup when using PRALLELISM... Prallelism decides the number of buffer handlers...
Then comes compression, my favorite... If you think compression slows down the speed of backup due to CPU processing lag which is involved in compression algorithm, then you correct about your analysis but not about final conclusion it leads you to...
Compression algorithm takes some extra CPU cycles but you save a great deal on I/O cycle which are more expensive (in terms of time and their availability)... hence compression will not just save space but time as well... and plus your buffers can be utilized easily... Same applies while you are restoring a backup, you spend less time on reads and more data fits in your buffer and hence your restore is faster...
Additionally going by basic h/w an OS concepts having a backup file on disk which is other than one where your db resides/going to be created (during restore) is often faster... Of course there are other reasons as well for which you should not store backups on same disk where DB resides...
Incremental/delta backups helps you take smaller backups which saves time backing up only
what has changed (or delta of change) and while restoring you have to restore a full backup and then changes are to be applied one by one hence makes the restore slower... But if you use backup/restore to shift data periodically (say once a day or week) to a different server incremental/delta backups are best... Cuase they take less time to be backed-up and then less time to be restored (as your restoring only the diff/incremental backup)...
Finally, snapshot backups using Netapp/Tivoli etc might boost up the back up process drastically... Though those are bit expensive to invest into, but if you have very (and I mean very) large volume of database your investments will be well paid off...
TO close this, backup is nothing but making an image of your database in a single file... Database is stored in datafiles which are spread over various blocks in your disk... Now if those blocks were scattered, backup would be quite slow, and hence it is gives your another reason to keep your db well re-orged... Also if your db is spread across various disks/file system it could boost the performance of backups and restores as well...
Optimize your Tablespaces and Bufferpools-3: Bufferpool Optimization
Hi all...
It has rather been quite a busy quarter for me... Office has kept me quite busy and was quite pre-occupied with my job... But here I am back with all the stuffs I wanted to share...
We have already seen how to wisely choose optimum value for various tablespace related parameters and how to decide on tablespace sizing... Now we will proceed to the last thread in this discussion and see how one can optimize bufferpools so that it can match the expectations set by tablespace optimiztions (that we have learned in pervious two posts)...
TO start with as a rule of thumb you should have a separate bufferpool for temporary tablespace and if you have different user temp tablespace(which I strongly recommend) you should have a diff bufferpool for that too... If you do not do this, you will see a lot of sort overflows or low bufferpool utilization in snapshots or high read/write operations @ disk level...
Also you should ideally have different bufferpools for index and data tablespaces... This is help you avoid situations where an index tree forced to flushed out of bufferpool to make space for a table read operation...
More importantly you should have different bufferpools for tablespaces storing transactional/operational (which is generally huge in volumes and cardinality) tables and a separate one for tablespaces which store config data tables... As config data is generally queried again and again e.g. every transaction you do in a bank will fetch transaction code (Cash Deposite or withdrwal etc are transaction codes) for your transaction... If you tablespace storing the table for transaction codes available in system is mapped to same bufferpool which is also mapped to tablepace storing table which logs all the transaction (e.g. your cash withdrawal activity and your friends credit card payment activity etc)... In such a scenario when a user does an enquiry on transactions it will flush the transaction code data it will have to read gain for a new transaction... If a transaction enquiry is frequent activity it will screw up cause of this faulty bufferpool design...
You can keep a watch on bufferpool utilization with sysibmadm.snapbp snapshot view... Ideally I will say table-bufferpool utilization should be above 90% (but 85% might be acceptable in certain cases)... For index bufferpools if it is below 95% then you need to worry, ideally it should be arround 97%...
Generally Even in an OLTP system, transactional data is read sequentially when working with a report... This helps making best of I/O cycles (obviously this decision should be based on queries you have in you system)... In such cases you have an option to specify NUMPAGEBLOCK in bufferpool which means the number of page-blocks that would be kept aside specially for block data... you can keep a watch on this utilization of specification with pages_from_block_ios, unread_prefetch_pages, pool_async_data_read_reqs and prefetch_wait_time monitor elements... Not to mention that it will also help in sequential prefetching...
Unlike other performance improvement techniques e.g. indexes and table-partitioning etc. a need for which could be easily guessed over a period of monitoring, a flaw in tablespace design or bufferpool allocation can not be so easily caught at times... The snapshots or anyother monitor data might not be indicative or directly pointing at it... I will suggest thinking about these well in advance... Many people think that this is more of a production call... But a production-DBA knows less than you (the development DBA, involved in table design) about the number of tables, volume of data and nature of queries...
I shall some time try to sum-up on how to use various monitoring techniques to keep a track of parameters set for your bufferpool/tablespaces...
It has rather been quite a busy quarter for me... Office has kept me quite busy and was quite pre-occupied with my job... But here I am back with all the stuffs I wanted to share...
We have already seen how to wisely choose optimum value for various tablespace related parameters and how to decide on tablespace sizing... Now we will proceed to the last thread in this discussion and see how one can optimize bufferpools so that it can match the expectations set by tablespace optimiztions (that we have learned in pervious two posts)...
TO start with as a rule of thumb you should have a separate bufferpool for temporary tablespace and if you have different user temp tablespace(which I strongly recommend) you should have a diff bufferpool for that too... If you do not do this, you will see a lot of sort overflows or low bufferpool utilization in snapshots or high read/write operations @ disk level...
Also you should ideally have different bufferpools for index and data tablespaces... This is help you avoid situations where an index tree forced to flushed out of bufferpool to make space for a table read operation...
More importantly you should have different bufferpools for tablespaces storing transactional/operational (which is generally huge in volumes and cardinality) tables and a separate one for tablespaces which store config data tables... As config data is generally queried again and again e.g. every transaction you do in a bank will fetch transaction code (Cash Deposite or withdrwal etc are transaction codes) for your transaction... If you tablespace storing the table for transaction codes available in system is mapped to same bufferpool which is also mapped to tablepace storing table which logs all the transaction (e.g. your cash withdrawal activity and your friends credit card payment activity etc)... In such a scenario when a user does an enquiry on transactions it will flush the transaction code data it will have to read gain for a new transaction... If a transaction enquiry is frequent activity it will screw up cause of this faulty bufferpool design...
You can keep a watch on bufferpool utilization with sysibmadm.snapbp snapshot view... Ideally I will say table-bufferpool utilization should be above 90% (but 85% might be acceptable in certain cases)... For index bufferpools if it is below 95% then you need to worry, ideally it should be arround 97%...
Unlike other performance improvement techniques e.g. indexes and table-partitioning etc. a need for which could be easily guessed over a period of monitoring, a flaw in tablespace design or bufferpool allocation can not be so easily caught at times... The snapshots or anyother monitor data might not be indicative or directly pointing at it... I will suggest thinking about these well in advance... Many people think that this is more of a production call... But a production-DBA knows less than you (the development DBA, involved in table design) about the number of tables, volume of data and nature of queries...
I shall some time try to sum-up on how to use various monitoring techniques to keep a track of parameters set for your bufferpool/tablespaces...
Optimize your Tablespaces and Bufferpools-2: Tablespace Sizing
Hey people...
So I hope the last post was informative... This post is going to be rather short... Not cause of the topic, but cause of my lack of knowledge on this matter... I could have very well knocked it off, but thought of giving some basics (to which my knowledge is restricted) from where people can carry on with google...
We will discuss sizing of tablespaces and their separation... We will wind up with little on temp tablespaces...
If as a DBA you tend to think, you can allocate any space, on your disk for your tablespaces and allow them to grow unlimited by any fraction as incremental size, and can sit back... You are damn wrong... Things that can cause problems...
1) If you allocate even initial size (which may be too huge) for all the tablespaces, the table spaces which has too frequent writes might face issues like allocation of new extents and which might be too scattered cause initial allocation was not thought properly...
2) Same applies if you think allocation for every tablespace should go at same pace... You might end up killing the space requirements for tablespaces which are too frequently accessed for writes... Moreover you will be facing issues with cost for storage (sooner or latter)...
3) Also you need to consider the fact that, some tables will have very sequential access and the tablespace having those tables, should have a more incremental size otherwise your data is going to scattered and prefetching suffers...
4) Static data and configuraiton data needs to have a medium allocation at starting and the increment window need not be too huge as very rarely you add new configurations to the system and very rarely you access configuration in sequence (well this might not be true always)...
Now wat is the way out??? This is something I have learnt from my seniors at work...
1) List the tables, and list the tablespaces they belog to. List the tables' row size and its cardinality as well
2) Sum up the above data per tablespace and see at what rate the data increases for every table
3) Combine all these inputs to decide, what should be the initial size (do not keep it same as current data's requirement at least keep 10%-50% extra space depending on rate at which data grows) and what should be the increment size...
This will really help you get rid of time lags for your write operations...
Next is to place all such tables together in a tablespace, where you think there will be sequential reads... Prefetching will benefit from this (as long as you remember to schedule a reorg at a proper frequency)... Tables which has static data which hardly gets updated/inserted should go into one tablespace (which should be reorganized at initial setup)... We will see more about bufferpool planing in upcoming posts...
Last... People tend to ignore the importance of system temporary tablespace and user temporary tablespace...
It is important to separate these two and explicitly create a new user temp tablespace, this will really help your sort operation which can have more freedom of "space" in system temp tablespaces... All other temp processing might as well benefit... Also, you should consider keeping a separate bufferpool for user temporary tablespace... Also, if you have not allocated enough space for you temp tablespace, it will try to reallocate/acquire space for sort operations, this will cause a delay in result for your sorting operations, which is quite frequent in application we see these days (patient enquiry sorted by admit date or if you remember the transaction order in your bank statement)...
So the mantra is to think wisely and group your tables in proper tablespaces, then size them and decide proper growth for them... Meanwhile do no forget the reorganization of tables and indexes (frequently) and of tablespaces at times... Meanwhile never ignore the importance of temp tablespaces...
I will be back with some tips on deciding proper strategy for your bufferpools and how to make them do the best according to your tablespace and tables contained by it...
Meanwhile I have got a some content on backup recovery... Planning to write it some time...
So I hope the last post was informative... This post is going to be rather short... Not cause of the topic, but cause of my lack of knowledge on this matter... I could have very well knocked it off, but thought of giving some basics (to which my knowledge is restricted) from where people can carry on with google...
We will discuss sizing of tablespaces and their separation... We will wind up with little on temp tablespaces...
If as a DBA you tend to think, you can allocate any space, on your disk for your tablespaces and allow them to grow unlimited by any fraction as incremental size, and can sit back... You are damn wrong... Things that can cause problems...
1) If you allocate even initial size (which may be too huge) for all the tablespaces, the table spaces which has too frequent writes might face issues like allocation of new extents and which might be too scattered cause initial allocation was not thought properly...
2) Same applies if you think allocation for every tablespace should go at same pace... You might end up killing the space requirements for tablespaces which are too frequently accessed for writes... Moreover you will be facing issues with cost for storage (sooner or latter)...
3) Also you need to consider the fact that, some tables will have very sequential access and the tablespace having those tables, should have a more incremental size otherwise your data is going to scattered and prefetching suffers...
4) Static data and configuraiton data needs to have a medium allocation at starting and the increment window need not be too huge as very rarely you add new configurations to the system and very rarely you access configuration in sequence (well this might not be true always)...
Now wat is the way out??? This is something I have learnt from my seniors at work...
1) List the tables, and list the tablespaces they belog to. List the tables' row size and its cardinality as well
2) Sum up the above data per tablespace and see at what rate the data increases for every table
3) Combine all these inputs to decide, what should be the initial size (do not keep it same as current data's requirement at least keep 10%-50% extra space depending on rate at which data grows) and what should be the increment size...
This will really help you get rid of time lags for your write operations...
Next is to place all such tables together in a tablespace, where you think there will be sequential reads... Prefetching will benefit from this (as long as you remember to schedule a reorg at a proper frequency)... Tables which has static data which hardly gets updated/inserted should go into one tablespace (which should be reorganized at initial setup)... We will see more about bufferpool planing in upcoming posts...
Last... People tend to ignore the importance of system temporary tablespace and user temporary tablespace...
It is important to separate these two and explicitly create a new user temp tablespace, this will really help your sort operation which can have more freedom of "space" in system temp tablespaces... All other temp processing might as well benefit... Also, you should consider keeping a separate bufferpool for user temporary tablespace... Also, if you have not allocated enough space for you temp tablespace, it will try to reallocate/acquire space for sort operations, this will cause a delay in result for your sorting operations, which is quite frequent in application we see these days (patient enquiry sorted by admit date or if you remember the transaction order in your bank statement)...
So the mantra is to think wisely and group your tables in proper tablespaces, then size them and decide proper growth for them... Meanwhile do no forget the reorganization of tables and indexes (frequently) and of tablespaces at times... Meanwhile never ignore the importance of temp tablespaces...
I will be back with some tips on deciding proper strategy for your bufferpools and how to make them do the best according to your tablespace and tables contained by it...
Meanwhile I have got a some content on backup recovery... Planning to write it some time...
Optimize your Tablespaces and Bufferpools-1: Tablespace Design design
hey people... I am back after a long time... Office had kept me busy, and it will continue to do so for next few quarters... After all we have promised ourselves to build a world class product, which got to be best!!!
Well so getting back to db2... In this post I will discussing little bit on the physical database design...
Lets discuss the tablesapces first...
So lets start with pagesize... If u are really concerned about growth of your data, you should better go for a 32K pagesize, offering maximum storage... For configuration data and static data though you can choose to go with a 4k/8k pagesize and you might see some improvement in bufferpool and disk utilization (especially if you have a table with random access for records)...
Next is file-system caching... DB2 always caches the data (and caches it in more usable format for it db-engine) in bufferpools... So needless to say, if your OS is also caching the data, it is wasting both time and memory... So you better keep filesystem caching off (thank god it is default in 9.7)...
Exten Size is something which should be wisely thought out (esp when using automatic storage)... It is the amount of data/index pages which will be written to a container before going to the next container... If it is too low, you have too much of scattered data whereas if it is too high you have too much of I/O load... In case you do not specify extent size while creating the tablespace it will take it from dft_extent_sz db cfg... A thumb of rule is to use following formula:
RAID disk drives * Disk strip size
Prefetch Size is the no of pages for pre-fetching... It should not be smaller then extent size in any case, other wise you end up wasting I/O cycles... Most optimum suggestion is to keep it same as extent size, but if you are using multiplexing of data pages, it might be a good idea to keep it in multiples of extent size...
Overhead and transfer rate are two mostly ignored aspects of tablespace design and people tend to go with defaults... See generally hardware performances changes much rapidly and moreover organizations tend to change the hardware more frequently compared to s/w (at times it is vice versa)... Hence you transfer rate (which conveys to optimizer the speed @ which data will be tranferred from underlying container) and overhead (which conveys the other disk seek and various latencies related to disk) should be set properly... If they are wrong it might impact as much as deciding for a table scan instead of an index scan just cause the optimizer thinks the seek time will be too much for index pages...
At last let's try to answer the question on "If DMS/SMS/ASM???"... Well I will not bore with what's pros n cons of each as you can get lot of content on internet on this, and I do not want to copy paste stuff... :)
Well I advise going for Automatic Storage, as it is more easy to manage and handles parallelism more efficiently... It can utilize RAW devices more efficiently...
Lastly you can enable I/O parallelism with-in a tablespace (if it is spread across multiple containers)... for example If you have RAID 7 array you can set the below registry parameter using db2set
DB2_PARALLEL_IO=*:7
I shall continue the topic in next post... Where I will discuss on sizing and increment for tablespaces and also the temporary tablespaces...
Well so getting back to db2... In this post I will discussing little bit on the physical database design...
Lets discuss the tablesapces first...
So lets start with pagesize... If u are really concerned about growth of your data, you should better go for a 32K pagesize, offering maximum storage... For configuration data and static data though you can choose to go with a 4k/8k pagesize and you might see some improvement in bufferpool and disk utilization (especially if you have a table with random access for records)...
Next is file-system caching... DB2 always caches the data (and caches it in more usable format for it db-engine) in bufferpools... So needless to say, if your OS is also caching the data, it is wasting both time and memory... So you better keep filesystem caching off (thank god it is default in 9.7)...
Exten Size is something which should be wisely thought out (esp when using automatic storage)... It is the amount of data/index pages which will be written to a container before going to the next container... If it is too low, you have too much of scattered data whereas if it is too high you have too much of I/O load... In case you do not specify extent size while creating the tablespace it will take it from dft_extent_sz db cfg... A thumb of rule is to use following formula:
RAID disk drives * Disk strip size
Prefetch Size is the no of pages for pre-fetching... It should not be smaller then extent size in any case, other wise you end up wasting I/O cycles... Most optimum suggestion is to keep it same as extent size, but if you are using multiplexing of data pages, it might be a good idea to keep it in multiples of extent size...
Overhead and transfer rate are two mostly ignored aspects of tablespace design and people tend to go with defaults... See generally hardware performances changes much rapidly and moreover organizations tend to change the hardware more frequently compared to s/w (at times it is vice versa)... Hence you transfer rate (which conveys to optimizer the speed @ which data will be tranferred from underlying container) and overhead (which conveys the other disk seek and various latencies related to disk) should be set properly... If they are wrong it might impact as much as deciding for a table scan instead of an index scan just cause the optimizer thinks the seek time will be too much for index pages...
At last let's try to answer the question on "If DMS/SMS/ASM???"... Well I will not bore with what's pros n cons of each as you can get lot of content on internet on this, and I do not want to copy paste stuff... :)
Well I advise going for Automatic Storage, as it is more easy to manage and handles parallelism more efficiently... It can utilize RAW devices more efficiently...
Lastly you can enable I/O parallelism with-in a tablespace (if it is spread across multiple containers)... for example If you have RAID 7 array you can set the below registry parameter using db2set
DB2_PARALLEL_IO=*:7
I shall continue the topic in next post... Where I will discuss on sizing and increment for tablespaces and also the temporary tablespaces...
RUNSTATS and REORG-3: An effective REORG policy
Hi guys... so I am back with my blog sequence on reorgs and runstats... So just a recap my last two posts in this sequence had just explained "what is the importance of using RUNSTATs and REORGs" and then I discussed "How to 'effectively' automate RUNSTATS"...
So I will end the "saga" with REORG policy and it could not have come at a better time, when I had just fixed some major I/O anomalies with REORG (how many of you are thinking I am boasting?)...
Ok lets start with a problem statement...
I was told about about two problems, firstly Sudden Spikes in Reads at DB Server and Sudden Spikes in Writes at DB Server...
I really could not justify the Writes (but it struck me much later) but had an idea about reads... Also, at the design I had done for tablespace included some reserve for prefetching/blocked I/O... They were performing poor... Ooopss!!!
What helped me find it out about the bad performance of blocked I/O was this snapshot:
SELECT BP_NAME,
CAST(VECTORED_IOS as decimal(24,10)) / NULLIF((POOL_DATA_P_READS + POOL_INDEX_P_READS), 0) AS VECTORED_IO_FRACTION,
CAST (UNREAD_PREFETCH_PAGES as decimal(24,10)) / NULLIF(PAGES_FROM_BLOCK_IOS,0) AS WASTEFULL_PREFETCH_FRACTION, CAST((POOL_ASYNC_DATA_READ_REQS + POOL_ASYNC_INDEX_READ_REQS) as decimal(24,10)) / NULLIF((POOL_DATA_P_READS+POOL_INDEX_P_READS) - VECTORED_IOS,0) AS ANSYNC_PREFETCH_EFFICIENCY FROM SNAPBP;
So I had to do some analytic... First thing I did was running the runstats on tables and indexes and then I tried to find the amount of defragmentation:
SELECT I.TABNAME TABLE_NAME, I.INDNAME INDEX_NAME, REPLACE(SUBSTR(I.COLNAMES, 2) ,'+',',') AS MEM_COLS,
T.LASTUSED AS TAB_LASTUSED, I.LASTUSED AS INDX_LASTUSED,
I.NUM_EMPTY_LEAFS/NULLIF(I.NLEAF+NUM_EMPTY_LEAFS,0) INDX_EMPTY_LEAF_FRACTION,
T.FPAGES-T.NPAGES TAB_EMPTY_PAGES_CNT, I.NLEAF+NUM_EMPTY_LEAFS/(num_partitions*extent_size) AS INDX_REORG_NEEDED_IF_GT_1,
T.FPAGES/(num_partitions*extent_size) TAB_REORG_NEEDED_IF_GT_1
FROM SYSCAT.INDEXES I JOIN SYSCAT.TABLES T ON T.TABNAME=I.TABNAME AND T.TABSCHEMA=I.TABSCHEMA AND T.TABSCHEMA IN (schemalist) AND T.TYPE='T';
I guess the column names are pretty self explainatory and I need not explain what I got was huge no of index leaves scattered around and tables which were defragemented... So to explain the problems in terms of observation:
1) Too many disk seeks happening for read (even for index scans)
2) The sequential bloced IO fails miserably as the blocks pertaining to same table/index are scatterd
3) The writes, a record inserted/updated might have to re-balance the indexes, which is scattered so write cost increases
4) Finally the free leaves (for indexes)/blocks (for tables) are not available sequentially...
So I decided to do a REORG.. Most of the times people re-org with "REORG TABLE SCHEMA.TABLENAME" Guys it is hardly gonna help... Actually the one should arrange data in such fashion with will be usefull... So it is always good to reorg based on an index which can help you fetch data faster when you are going by that index (so choose the index you use more often)... Wait that is not enough... Before doing a REORG on your tables, you should have REORGed all your indexes on every table...
So I wrote a stored proc to dynamially select an index for every table and generate re-org statements for "REORG INDEXES ALL FOR TABLE" and "REORG TABLE USING INDEX"... Guess what!!! those momentary spikes were past and we had a good stable bufferpool utilization...
Many people while reading my last post would have thought AUTO_REORG as an equally useful method to do reorgs... i.e. to leave it to the database manager... But you might end up messing your OLTP performance and even your batch jobs which do bulk inserts/deletes... So best way is to keep checking weekly/fortnightly/monthly with the above queries on syscat tables to check if reorg is required or not... I really do not rely much on REORGCHCK utility from DB2... It is also important to run reorg on a regular interval based on about of data inflows and gets modified in your system...
So what next??? Probably some SQL Tuning tips!!! Till then HAPPY PREFETCHING!!!
So I will end the "saga" with REORG policy and it could not have come at a better time, when I had just fixed some major I/O anomalies with REORG (how many of you are thinking I am boasting?)...
Ok lets start with a problem statement...
I was told about about two problems, firstly Sudden Spikes in Reads at DB Server and Sudden Spikes in Writes at DB Server...
I really could not justify the Writes (but it struck me much later) but had an idea about reads... Also, at the design I had done for tablespace included some reserve for prefetching/blocked I/O... They were performing poor... Ooopss!!!
What helped me find it out about the bad performance of blocked I/O was this snapshot:
SELECT BP_NAME,
CAST(VECTORED_IOS as decimal(24,10)) / NULLIF((POOL_DATA_P_READS + POOL_INDEX_P_READS), 0) AS VECTORED_IO_FRACTION,
CAST (UNREAD_PREFETCH_PAGES as decimal(24,10)) / NULLIF(PAGES_FROM_BLOCK_IOS,0) AS WASTEFULL_PREFETCH_FRACTION, CAST((POOL_ASYNC_DATA_READ_REQS + POOL_ASYNC_INDEX_READ_REQS) as decimal(24,10)) / NULLIF((POOL_DATA_P_READS+POOL_INDEX_P_READS) - VECTORED_IOS,0) AS ANSYNC_PREFETCH_EFFICIENCY FROM SNAPBP;
So I had to do some analytic... First thing I did was running the runstats on tables and indexes and then I tried to find the amount of defragmentation:
SELECT I.TABNAME TABLE_NAME, I.INDNAME INDEX_NAME, REPLACE(SUBSTR(I.COLNAMES, 2) ,'+',',') AS MEM_COLS,
T.LASTUSED AS TAB_LASTUSED, I.LASTUSED AS INDX_LASTUSED,
I.NUM_EMPTY_LEAFS/NULLIF(I.NLEAF+NUM_EMPTY_LEAFS,0) INDX_EMPTY_LEAF_FRACTION,
T.FPAGES-T.NPAGES TAB_EMPTY_PAGES_CNT, I.NLEAF+NUM_EMPTY_LEAFS/(num_partitions*extent_size) AS INDX_REORG_NEEDED_IF_GT_1,
T.FPAGES/(num_partitions*extent_size) TAB_REORG_NEEDED_IF_GT_1
FROM SYSCAT.INDEXES I JOIN SYSCAT.TABLES T ON T.TABNAME=I.TABNAME AND T.TABSCHEMA=I.TABSCHEMA AND T.TABSCHEMA IN (schemalist) AND T.TYPE='T';
I guess the column names are pretty self explainatory and I need not explain what I got was huge no of index leaves scattered around and tables which were defragemented... So to explain the problems in terms of observation:
1) Too many disk seeks happening for read (even for index scans)
2) The sequential bloced IO fails miserably as the blocks pertaining to same table/index are scatterd
3) The writes, a record inserted/updated might have to re-balance the indexes, which is scattered so write cost increases
4) Finally the free leaves (for indexes)/blocks (for tables) are not available sequentially...
So I decided to do a REORG.. Most of the times people re-org with "REORG TABLE SCHEMA.TABLENAME" Guys it is hardly gonna help... Actually the one should arrange data in such fashion with will be usefull... So it is always good to reorg based on an index which can help you fetch data faster when you are going by that index (so choose the index you use more often)... Wait that is not enough... Before doing a REORG on your tables, you should have REORGed all your indexes on every table...
So I wrote a stored proc to dynamially select an index for every table and generate re-org statements for "REORG INDEXES ALL FOR TABLE" and "REORG TABLE USING INDEX"... Guess what!!! those momentary spikes were past and we had a good stable bufferpool utilization...
Many people while reading my last post would have thought AUTO_REORG as an equally useful method to do reorgs... i.e. to leave it to the database manager... But you might end up messing your OLTP performance and even your batch jobs which do bulk inserts/deletes... So best way is to keep checking weekly/fortnightly/monthly with the above queries on syscat tables to check if reorg is required or not... I really do not rely much on REORGCHCK utility from DB2... It is also important to run reorg on a regular interval based on about of data inflows and gets modified in your system...
So what next??? Probably some SQL Tuning tips!!! Till then HAPPY PREFETCHING!!!
SQL Anti logic!!!
Well I am just breaking my sequence of RUNSTATS and REORG for a while (I promise my next blog will be having that)...
Actually I have just been back from an interview session... I took a few interviews for filling up vacancies for java developer... I just asked few basic SQLs, the general approach people took for those SQLs raises a smile across my face... Actually those were the most obvious approach that comes to our mind when we try to reach/approach such problems... I have seen such patterns quite often in reports and prepared statement during my reviews (of SQLs used by development)... But if you stretch a little bit, try thinking the Anti conditions (wondering what they are??? Just wait...), you might get a more efficient option...
Let me share a few to guide you thru this...
I will try to skip the table structure etc (unless it is very much reqd) in order to avoid boring you guys/gals...
1) OK, if you have to find out all those customers (from a CUSTOMER table in bank database) whose PINCODE (an integet field) starts with 56 (i.e. of a particular city/state)... The most obvious response is:
select * from customer wherecast(pincode as varchar(6)) like '56%'
Weren't you thinking exactly that? But unfortunately that case will avoid an index scan and has a casting overhead... Put your Matriculation Hat on your head... Think of the max and min no having 6 digits (i.e. in INDIA pincodes are 6 digit) which can starts with 56??? Does that help??? So now you got a better query:
select * from customer where pincode between 560000 and 569999
If you thought that off pretty early (before I gave the hint), hats off to you...
2)Now if you have a timestamp/date column (in DB2 9.7 date will store time as well till the seconds) to store the transaction date for a banking application (sorry guys but that is the only domain I have worked with), and you want to list all the transactions that happened on a particular date? Obvious query:
select * from transaction_posting_table where date(txn_posting_date)='2010/04/30'
Some one who has attended an Oracle Usability/Oracle Enablement session for DB2 might think
select * from transaction_posting_table wheretrunc(txn_posting_date)='2010/04/30'
'2010/04/30' is '2010/04/30' with time 12:00:00.0000AM and trunc will also convert all the date/timestamps to that day's 12:00:00.0000AM... Hence we get all the transactions of '2010/04/30' irrespective of its time...
As these queries go for table scan, people suggest of adding an extra column which is always computed based on date(txn_posting_date) and then index... Ahhh!!! Before that check this out:
select * from transaction_posting_table where txn_posting_date)>='2010/04/30' and txn_posting_date)<'2010/04/30'+1 Kwel???
'2010/04/30' is '2010/04/30' with time 12:00:00.0000AM
and '2010/04/30' +1 is '2010/05/01' with time 12:00:00.0000AM...
So you pick up any time on '2010/04/30' it will satisfy above condition... The trick is to move the computation from LHS (i.e. instead of converting all the date/timestamp to 12 midnight, search for all date/timestamps b/e two midnights, one inclusive and other exclusive )... :)
3) Another famous interview question is (I have never asked this one) "Write a query to find the 2nd max salary from emp table". But I know it will send across a smile on many faces... (Yeah I know that, I did that in my graduation viva!!!...) So is this what you are thinking?
select max(sal) as secnd_max_sal from emp where sal<(select max(sal) from emp)
Yeah??? that is what you said in your graduation viva? Atleast I said that, but in an interview I will say differently... Well the problem is when you generate a plan you see two sort operation (I would frown at a query with one sort...)
select sal as secnd_max_sal from (select dense_rank() over(order by sal) rnk,sal from emp)tmp_rslt wher tmp_rslt.rnk=2;
(more extensible and works with just one sort and an rid scan!!! :) )
4) Another one that I have seen quite often, that people JOIN two table and the JOIN result is joined with third table on a condition where the key of third can be either matched to first or the second table... This happens more commonly when the first query is left outer join and the join condition can be on the key from first table or if it is null then on second column...
so a few optimization that comes to my mind (I will just write the JOIN clause):
>>A LEFT OUTER JOIN B on A.Aid=B.Bid JOIN C on(A.Aid=C.Cid or B.Bid=C.Cid)
Better way to write is:
>>A LEFT OUTER JOIN B on A.Aid=B.Bid JOIN C on (C.Cid COALESCE(A.Aid,B.Bid))
Another scenario is
>>A JOIN B on A.Aid=B.Bid JOIN C on(A.a1=C.Cid or B.b1=C.Cid)
This again might not go for an index scan on either of the column and might go for a hash join. A better way would be to Use IN... Wondering how???
>>A JOIN B on A.Aid=B.Bid JOIN C on C.Cid IN( B.b1,A.a1)
What say??? I hope this blog would have helped people understand negative/anti condition (I had talked just in brief about this in my ppt in DB2 has talent)... I will try to put up more such content if I come across one... Esp a lot of stuffs can be optimized with CASE statements...
So I hope next time a database developer writes a query, he will think that extra mile and a DBA reviews a SQL he will be more critical...
For those who want more before they can do on their own... Spend a few bucks and get this book on SQL Antipatterns... I havent yet read this but some one suggested it is on the same lines (and the Title of the book suggests the same)...
Actually I have just been back from an interview session... I took a few interviews for filling up vacancies for java developer... I just asked few basic SQLs, the general approach people took for those SQLs raises a smile across my face... Actually those were the most obvious approach that comes to our mind when we try to reach/approach such problems... I have seen such patterns quite often in reports and prepared statement during my reviews (of SQLs used by development)... But if you stretch a little bit, try thinking the Anti conditions (wondering what they are??? Just wait...), you might get a more efficient option...
Let me share a few to guide you thru this...
I will try to skip the table structure etc (unless it is very much reqd) in order to avoid boring you guys/gals...
1) OK, if you have to find out all those customers (from a CUSTOMER table in bank database) whose PINCODE (an integet field) starts with 56 (i.e. of a particular city/state)... The most obvious response is:
select * from customer where
Weren't you thinking exactly that? But unfortunately that case will avoid an index scan and has a casting overhead... Put your Matriculation Hat on your head... Think of the max and min no having 6 digits (i.e. in INDIA pincodes are 6 digit) which can starts with 56??? Does that help??? So now you got a better query:
select * from customer where pincode between 560000 and 569999
If you thought that off pretty early (before I gave the hint), hats off to you...
2)Now if you have a timestamp/date column (in DB2 9.7 date will store time as well till the seconds) to store the transaction date for a banking application (sorry guys but that is the only domain I have worked with), and you want to list all the transactions that happened on a particular date? Obvious query:
select * from transaction_posting_table where date(txn_posting_date)='2010/04/30'
Some one who has attended an Oracle Usability/Oracle Enablement session for DB2 might think
select * from transaction_posting_table where
'2010/04/30' is '2010/04/30' with time 12:00:00.0000AM and trunc will also convert all the date/timestamps to that day's 12:00:00.0000AM... Hence we get all the transactions of '2010/04/30' irrespective of its time...
As these queries go for table scan, people suggest of adding an extra column which is always computed based on date(txn_posting_date) and then index... Ahhh!!! Before that check this out:
select * from transaction_posting_table where txn_posting_date)>='2010/04/30' and txn_posting_date)<'2010/04/30'+1 Kwel???
'2010/04/30' is '2010/04/30' with time 12:00:00.0000AM
and '2010/04/30' +1 is '2010/05/01' with time 12:00:00.0000AM...
So you pick up any time on '2010/04/30' it will satisfy above condition... The trick is to move the computation from LHS (i.e. instead of converting all the date/timestamp to 12 midnight, search for all date/timestamps b/e two midnights, one inclusive and other exclusive )... :)
3) Another famous interview question is (I have never asked this one) "Write a query to find the 2nd max salary from emp table". But I know it will send across a smile on many faces... (Yeah I know that, I did that in my graduation viva!!!...) So is this what you are thinking?
select max(sal) as secnd_max_sal from emp where sal<
Yeah??? that is what you said in your graduation viva? Atleast I said that, but in an interview I will say differently... Well the problem is when you generate a plan you see two sort operation (I would frown at a query with one sort...)
select sal as secnd_max_sal from (select dense_rank() over(order by sal) rnk,sal from emp)tmp_rslt wher tmp_rslt.rnk=2;
(more extensible and works with just one sort and an rid scan!!! :) )
4) Another one that I have seen quite often, that people JOIN two table and the JOIN result is joined with third table on a condition where the key of third can be either matched to first or the second table... This happens more commonly when the first query is left outer join and the join condition can be on the key from first table or if it is null then on second column...
so a few optimization that comes to my mind (I will just write the JOIN clause):
>>A LEFT OUTER JOIN B on A.Aid=B.Bid JOIN C on
Better way to write is:
>>A LEFT OUTER JOIN B on A.Aid=B.Bid JOIN C on (C.Cid COALESCE(A.Aid,B.Bid))
Another scenario is
>>A JOIN B on A.Aid=B.Bid JOIN C on
This again might not go for an index scan on either of the column and might go for a hash join. A better way would be to Use IN... Wondering how???
>>A JOIN B on A.Aid=B.Bid JOIN C on C.Cid IN( B.b1,A.a1)
What say??? I hope this blog would have helped people understand negative/anti condition (I had talked just in brief about this in my ppt in DB2 has talent)... I will try to put up more such content if I come across one... Esp a lot of stuffs can be optimized with CASE statements...
So I hope next time a database developer writes a query, he will think that extra mile and a DBA reviews a SQL he will be more critical...
For those who want more before they can do on their own... Spend a few bucks and get this book on SQL Antipatterns... I havent yet read this but some one suggested it is on the same lines (and the Title of the book suggests the same)...
RUNSTATS and REORG-2: How to automate runstats
Hi guys!!! I am back... n back as an IBM Information Champion (yup got selected for 2011 :) ) !!!
Please rate this blog as informative/interesting/cool/all/two of these once you are done... Any comments on improvement are appreciated and will be welcomed...
Well in last post I talked about importance of runstats and reorg for a db2 database...
just a recap, reorg keeps the data pages near by for same table hence reducing disk seek n runstats will keep the stats updated so helping the optimizer make best use of available indexes and generate better plan...
well... automating runstats is pretty simpler... I will take you little deep into this automation n its impact...
Following are the automatic maintainence parameters in db2...
__________________________________________________________________
Automatic maintenance (AUTO_MAINT)
|
|-- Automatic database backup (AUTO_DB_BACKUP)
|
|-- Automatic table maintenance (AUTO_TBL_MAINT)
|
|---- Automatic runstats (AUTO_RUNSTATS)
|
|-------- Automatic statement statistics (AUTO_STMT_STATS)
|
|---- Automatic statistics profiling (AUTO_STATS_PROF)
|
|--------- Automatic profile updates (AUTO_PROF_UPD)
|
|---- Automatic reorganization (AUTO_REORG)
__________________________________________________________________
So it must be clear that above is an hierarchy... To set ON a child you need to set ON the parent first...
Now lets understand the parameters we are concerned about...
1) First one is AUTO_RUNSTATS
If you set it effective (not just ON... guess what i mean by this???), it will automatically keep doing runstats on tables...
And do not worry, this will not impact your other db2 processes by more that 7-10% (no matter what you have set as UTIL_IMPACT_PRIORITY)... and it will not keep doing this every time you do a CRUD operation... It will do a runstats only on those tables whose data has changed more than 10-15%...good news is db2 luw v9.7 will switch it on automatically by default for any new db created...
2) Next will be automatic statement statistics
If you set this ON, it will try to collect the stats synchronously for any CRUD operation you do... In fact at times it may cause your CRUD operation to wait... It is a real time stats gather...
Well here are a few SNAP DB monitor elements, which might help you decide best about these parameters...
i) STATS_FABRICATIONS Vs ASYNC_RUNSTATS+SYNC_RUNSTATS is STATS usage TO STATS GATHER ratio,
ii) SYNC_RUNSTATS vs SYNC_RUNSTATS+ASYNC_RUNSTATS is FRACTION of SYNC RUNSTAT
iii) STATS_FABRICATE_TIME Vs SYNC_RUNSTATS_TIME is usage stats compared to SYNC update of STATS
iv) SYNC_RUNSTATS_TIME Vs ELAPSED_EXEC_TIME_S & ELAPSED_EXEC_TIME_MS will give you impact of SYNC STAT COLLECTION ON STMT execution
BY default his parameter is ON... Well I had used the monitor elements above and was surprised that 22% of txn were getting delayed due to synchronous stats updates... 30% of stats were being updated synchronously...I leave it to you to decide what to do when you run into this...
3) Third is Automatic statistics profiling and Automatic profile updates
Switching on the automatic stats profiling, will turn on stats profile generation...
but you can not set it on if your db cfg section_actual is ON...
Would like to make a point that generally auto_runstats will respect the user profile if user has run a manual runstats with some profile...
Automatic Profile update will update the runstats profile with recommendations...
If it is off profile recommendations generated are stored in opt_feedback_ranking...
Both of these are off by default...
Will suggest keeping them off to let auto_runstats pick up the default profile... or run a manual runstats with a specific profile for a table n auto_runstats will keep it in mind...
One more way of statistics gathering, is to do a run-stats in idle time, with a background scheduled job... A few months ago, I would have preferred that, when my seniors explained me that...
So this is all about automating the runstats...
I will get back on auto maint parameters for automatic reorg and how to identify when to do a reorg...
For people interested in exploring more...
--> Go thru this info center page for more content on auto_maint parameters...
--> Go thru this article on dev works... It explains the automatic maintainence parameters for statistics gathering quit well...
Please rate this blog as informative/interesting/cool/all/two of these once you are done... Any comments on improvement are appreciated and will be welcomed...
Well in last post I talked about importance of runstats and reorg for a db2 database...
just a recap, reorg keeps the data pages near by for same table hence reducing disk seek n runstats will keep the stats updated so helping the optimizer make best use of available indexes and generate better plan...
well... automating runstats is pretty simpler... I will take you little deep into this automation n its impact...
Following are the automatic maintainence parameters in db2...
__________________________________________________________________
Automatic maintenance (AUTO_MAINT)
|
|-- Automatic database backup (AUTO_DB_BACKUP)
|
|-- Automatic table maintenance (AUTO_TBL_MAINT)
|
|---- Automatic runstats (AUTO_RUNSTATS)
|
|-------- Automatic statement statistics (AUTO_STMT_STATS)
|
|---- Automatic statistics profiling (AUTO_STATS_PROF)
|
|--------- Automatic profile updates (AUTO_PROF_UPD)
|
|---- Automatic reorganization (AUTO_REORG)
__________________________________________________________________
So it must be clear that above is an hierarchy... To set ON a child you need to set ON the parent first...
Now lets understand the parameters we are concerned about...
1) First one is AUTO_RUNSTATS
If you set it effective (not just ON... guess what i mean by this???), it will automatically keep doing runstats on tables...
And do not worry, this will not impact your other db2 processes by more that 7-10% (no matter what you have set as UTIL_IMPACT_PRIORITY)... and it will not keep doing this every time you do a CRUD operation... It will do a runstats only on those tables whose data has changed more than 10-15%...good news is db2 luw v9.7 will switch it on automatically by default for any new db created...
2) Next will be automatic statement statistics
If you set this ON, it will try to collect the stats synchronously for any CRUD operation you do... In fact at times it may cause your CRUD operation to wait... It is a real time stats gather...
Well here are a few SNAP DB monitor elements, which might help you decide best about these parameters...
i) STATS_FABRICATIONS Vs ASYNC_RUNSTATS+SYNC_RUNSTATS is STATS usage TO STATS GATHER ratio,
ii) SYNC_RUNSTATS vs SYNC_RUNSTATS+ASYNC_RUNSTATS is FRACTION of SYNC RUNSTAT
iii) STATS_FABRICATE_TIME Vs SYNC_RUNSTATS_TIME is usage stats compared to SYNC update of STATS
iv) SYNC_RUNSTATS_TIME Vs ELAPSED_EXEC_TIME_S & ELAPSED_EXEC_TIME_MS will give you impact of SYNC STAT COLLECTION ON STMT execution
BY default his parameter is ON... Well I had used the monitor elements above and was surprised that 22% of txn were getting delayed due to synchronous stats updates... 30% of stats were being updated synchronously...I leave it to you to decide what to do when you run into this...
3) Third is Automatic statistics profiling and Automatic profile updates
Switching on the automatic stats profiling, will turn on stats profile generation...
but you can not set it on if your db cfg section_actual is ON...
Would like to make a point that generally auto_runstats will respect the user profile if user has run a manual runstats with some profile...
Automatic Profile update will update the runstats profile with recommendations...
If it is off profile recommendations generated are stored in opt_feedback_ranking...
Both of these are off by default...
Will suggest keeping them off to let auto_runstats pick up the default profile... or run a manual runstats with a specific profile for a table n auto_runstats will keep it in mind...
One more way of statistics gathering, is to do a run-stats in idle time, with a background scheduled job... A few months ago, I would have preferred that, when my seniors explained me that...
"db2 does it automatically n won't impact the current processes by much fraction... More over if you automate it urself, you will update stats for all the tables, even if they did not change much (or not at all) so its better we leave it to db2..."
So this is all about automating the runstats...
I will get back on auto maint parameters for automatic reorg and how to identify when to do a reorg...
For people interested in exploring more...
--> Go thru this info center page for more content on auto_maint parameters...
--> Go thru this article on dev works... It explains the automatic maintainence parameters for statistics gathering quit well...
RUNSTATS and REORG-1: How important they are
Hi guys...
So I am back... little quick this time...
So this is final post in my pseudo participation blog for DB2 Night Show (I had an early exit :-( after qualifying for finals)...
Congrats to Norberto for winning the competitions... Check his blog for tips...
Congrats to JB, Momi and Thiru as well for making to final...
I am looking forward to more such opportunities in future...
So I have come with some tips for people (people as the DBA community) to understand how important is the usage of two DB2 commands RUNSTATS and REORG, more importantly how you can determine when to REORG/RUNSTATS on which tables...
1) RUNSTATS are important for your tables, as they keep the stats updated for your table. This will always help your optimizer generate a better plan, rather the apt plan for fetching the data. I have seen situation when a filtered fetch goes for a table scan for 0.1-1million records even though column in filter is indexed...
2) REORG is equally important as it helps in putting the data in continous chunks... Generally for better insert performance, the data is put where you have space rather than putting it in a place (or near it) where other data of the table... The placement of data is also dependent on availability of space in nearest extent/block... If your data is too scattered across, there might be too much of disk seek involved and might as well degrade the performance of sequential and concurrent fetch...
Well you can AUTOMATE your task to certain extent and use automatic maintainance database configuration for auto runstats and auto reorg...
I shall come up with more on pros and cons of using those parameters and ways to optimize your RUNSTATS and REORG, in my future blogs...
I would like to thank Susan and Sathey for their continuous support, encouragement and tips for improvement on by blogging...
I will keep my blogs little short and will split the massive overdose of info over several blogs (thanks to Sathey)...
Latter I will also try to split my previous long posts into several blogs, which shall help my new subscriber...
So I am back... little quick this time...
So this is final post in my pseudo participation blog for DB2 Night Show (I had an early exit :-( after qualifying for finals)...
Congrats to Norberto for winning the competitions... Check his blog for tips...
Congrats to JB, Momi and Thiru as well for making to final...
I am looking forward to more such opportunities in future...
So I have come with some tips for people (people as the DBA community) to understand how important is the usage of two DB2 commands RUNSTATS and REORG, more importantly how you can determine when to REORG/RUNSTATS on which tables...
1) RUNSTATS are important for your tables, as they keep the stats updated for your table. This will always help your optimizer generate a better plan, rather the apt plan for fetching the data. I have seen situation when a filtered fetch goes for a table scan for 0.1-1million records even though column in filter is indexed...
2) REORG is equally important as it helps in putting the data in continous chunks... Generally for better insert performance, the data is put where you have space rather than putting it in a place (or near it) where other data of the table... The placement of data is also dependent on availability of space in nearest extent/block... If your data is too scattered across, there might be too much of disk seek involved and might as well degrade the performance of sequential and concurrent fetch...
Well you can AUTOMATE your task to certain extent and use automatic maintainance database configuration for auto runstats and auto reorg...
I shall come up with more on pros and cons of using those parameters and ways to optimize your RUNSTATS and REORG, in my future blogs...
I would like to thank Susan and Sathey for their continuous support, encouragement and tips for improvement on by blogging...
I will keep my blogs little short and will split the massive overdose of info over several blogs (thanks to Sathey)...
Latter I will also try to split my previous long posts into several blogs, which shall help my new subscriber...
My db2 success story: Five things to check when you see high memory usage on db server
Hi, so I am back with my blog sequence of db2 has talent... I know I have been little late with this one... But can not help it when India is playing cricket world cup... They finally won it... After 28 long years... First host nation to have won it... So had given up everything else for following the team create history...
by thw way the grand finale concluded recently and the last four finalists are waiting for your votes... There are some really cool tips to adopt in your dba life and that can help you lift your quality of work... so do see the replay and vote before 7th april...
I hope Scott and DBI will surely gonna come up with more of this... Some of you might find it interesting that JB (of the contestant who made it to mega finals) has been invited by Susan Visser to write a book... TO get a glimpse of her check this out, she was Susan's guest blogger...
Well let me continue with my would had been presentation in the episode just before the grand finales... All the contestants talked about their success story, where they concentrated on one single thing with DB2 that made them hero (well this is what I assume from all the presentations)...
So, for me being a junior DBA, there had not been many such moments... One i have already shared in my previous blog... Let me attempt to share another one...
We had a client running db2 8.2.8, and we had planned for migration to db2 9.5... But before the transition could kick-off there was a big performance bottleneck... All of a sudden they were reporting high I/O, high memory consumption and very high swap-in swap-out activity...
Eyebrow were raised and much like any other issue, this too made people point towards database... Our team was consulted, and my seniors being on leave on that day, I was involved in lot of mail exchanges that day... Let me share the information I requested for:
First Step: I requested for snapshot of memory usage by db2 instance and db2 database hosting the production db:
db2mtrk -i -d [-v]
It confirmed that the db was not using too much of memory, it was using whatever was allocated to bufferpool and cache-pool and db shared memory...
Second thing I did was a request to check the tablespaces where the tables are lying and the bufferpool used by all the different tablespaces we had suggested
This confirmed that not all the tables are in the same tablespace, and also all tablespaces are not pointing to the same bufferpool. This helped me overcome the doubt that, may be only one bufferpool is being used for all the I/O, other bufferpools have simply reserved there share of memory but are not using it... Moreover one bufferpool being used for all tabels/tablespaces means too many flush events...
third step of my investigation was to check if there is CIO (oncurrent I/O) enabled at the filesystem level and to check if the filesystem chaching is switched off at tablespace level...
the result confirmed the cause for high I/O and too much of memory usage...
we saw that there was filesystem caching enabled for all the tablespaces (shit!!! the db2 8.2.8 version had this as default, so guys take my advice and migrate to new version, where default is to create tablespaces with filesystem level caching off)... Moreover the CIO was disabled at Unix level as well...
If you have filesystem level caching enabled, what happens is your data is once cached by the OS in memory and then database (i.e. DB2 application) will cache is again in the bufferpool area created by db2 in the application memory allocated to it by kernel... firstly it causes to much of memory to be used, and secondly there is more time wasted in double caching :P...
Correcting this helped alot...
fourth thing to be inspected was prefetch size at db and tablespace level, if this value is not apt there might be too much of I/O waits involved (though this was not in the problem definition, i thought it will be good to check it) the monitor element tablespace_prefetch_size shall help you get that info (i had to use the table-function interface, as I was working with 8.2.8 version, it is much easier to get in v9.7)... Ideally 1.5-3 times your block size is a good number for prefetch data...
fifth thing I confirmed was to check if there is a scope given for block I/O or not... Actually you can reserve some space in your bufferpool for blocked I/O while creating the bufferpool... Blocked I/O is when DB2 performs sequential prefetching of pages into the block area of the buffer pool...
you can check the performance of blocked I/O using block_ios and vertored_ios monitor elements belonging to your bufferpool...
For optimal performance, it is recommended (source db2 info center for v8) that you bind tablespaces with the same extent size to a block-based buffer pool with a block size equal to the extent size...
These all things can help you as well, when you see a shooting high increase in memory usage by db2, or high memory usage on server hosting db2 database or a very high I/O or too much of I/O waits...
querying all these parameters regularly and fixing them (even when, rather esp when you are not facing any issues) will help you keep memory and I/O issues away...
My mantra for db2 DBAs in production is a snapshot a day keeps performance issues aways... i.e. run one snapshot a day and work on the results, fix the problems you anticipate out of their results...
Well I am myself looking forward to DBI's most awaited webinar on DB2 LUW Performance: 10 SQL Snapshots You MUST Run Today!
Register Now!!!
To end it I hope DBI won't mind me pretending to be a pseudo contestant despite being out of the show now... With all due regards I apologize, if in any way I have overlooked any term/conditions/legality of this event which is a mega success... there will be many to follow...
by thw way the grand finale concluded recently and the last four finalists are waiting for your votes... There are some really cool tips to adopt in your dba life and that can help you lift your quality of work... so do see the replay and vote before 7th april...
I hope Scott and DBI will surely gonna come up with more of this... Some of you might find it interesting that JB (of the contestant who made it to mega finals) has been invited by Susan Visser to write a book... TO get a glimpse of her check this out, she was Susan's guest blogger...
Well let me continue with my would had been presentation in the episode just before the grand finales... All the contestants talked about their success story, where they concentrated on one single thing with DB2 that made them hero (well this is what I assume from all the presentations)...
So, for me being a junior DBA, there had not been many such moments... One i have already shared in my previous blog... Let me attempt to share another one...
We had a client running db2 8.2.8, and we had planned for migration to db2 9.5... But before the transition could kick-off there was a big performance bottleneck... All of a sudden they were reporting high I/O, high memory consumption and very high swap-in swap-out activity...
Eyebrow were raised and much like any other issue, this too made people point towards database... Our team was consulted, and my seniors being on leave on that day, I was involved in lot of mail exchanges that day... Let me share the information I requested for:
First Step: I requested for snapshot of memory usage by db2 instance and db2 database hosting the production db:
db2mtrk -i -d [-v]
It confirmed that the db was not using too much of memory, it was using whatever was allocated to bufferpool and cache-pool and db shared memory...
Second thing I did was a request to check the tablespaces where the tables are lying and the bufferpool used by all the different tablespaces we had suggested
This confirmed that not all the tables are in the same tablespace, and also all tablespaces are not pointing to the same bufferpool. This helped me overcome the doubt that, may be only one bufferpool is being used for all the I/O, other bufferpools have simply reserved there share of memory but are not using it... Moreover one bufferpool being used for all tabels/tablespaces means too many flush events...
third step of my investigation was to check if there is CIO (oncurrent I/O) enabled at the filesystem level and to check if the filesystem chaching is switched off at tablespace level...
the result confirmed the cause for high I/O and too much of memory usage...
we saw that there was filesystem caching enabled for all the tablespaces (shit!!! the db2 8.2.8 version had this as default, so guys take my advice and migrate to new version, where default is to create tablespaces with filesystem level caching off)... Moreover the CIO was disabled at Unix level as well...
If you have filesystem level caching enabled, what happens is your data is once cached by the OS in memory and then database (i.e. DB2 application) will cache is again in the bufferpool area created by db2 in the application memory allocated to it by kernel... firstly it causes to much of memory to be used, and secondly there is more time wasted in double caching :P...
Correcting this helped alot...
fourth thing to be inspected was prefetch size at db and tablespace level, if this value is not apt there might be too much of I/O waits involved (though this was not in the problem definition, i thought it will be good to check it) the monitor element tablespace_prefetch_size shall help you get that info (i had to use the table-function interface, as I was working with 8.2.8 version, it is much easier to get in v9.7)... Ideally 1.5-3 times your block size is a good number for prefetch data...
fifth thing I confirmed was to check if there is a scope given for block I/O or not... Actually you can reserve some space in your bufferpool for blocked I/O while creating the bufferpool... Blocked I/O is when DB2 performs sequential prefetching of pages into the block area of the buffer pool...
you can check the performance of blocked I/O using block_ios and vertored_ios monitor elements belonging to your bufferpool...
For optimal performance, it is recommended (source db2 info center for v8) that you bind tablespaces with the same extent size to a block-based buffer pool with a block size equal to the extent size...
These all things can help you as well, when you see a shooting high increase in memory usage by db2, or high memory usage on server hosting db2 database or a very high I/O or too much of I/O waits...
querying all these parameters regularly and fixing them (even when, rather esp when you are not facing any issues) will help you keep memory and I/O issues away...
My mantra for db2 DBAs in production is a snapshot a day keeps performance issues aways... i.e. run one snapshot a day and work on the results, fix the problems you anticipate out of their results...
Well I am myself looking forward to DBI's most awaited webinar on DB2 LUW Performance: 10 SQL Snapshots You MUST Run Today!
Register Now!!!
To end it I hope DBI won't mind me pretending to be a pseudo contestant despite being out of the show now... With all due regards I apologize, if in any way I have overlooked any term/conditions/legality of this event which is a mega success... there will be many to follow...
db2exfmt
So in last episode (#45) of db2 night show, people presented db2look, db2move, db2this, db2that (sorry Scott to have stolen your words)... So i continue my thread of my pseudo-presentation... But again i emphasize that do view the actual vedio... now final no 3 vdo has come... It has some really kwel tips... tomorrow is final no 4 (i know I am lagging behind :))... watch it live or catch up with the replay latter... But do watch n vote for whom u find the best...
Well getting back to my "would have been" presentation...
db2exfmt
Well lot many times a query will be slapped back on your face telling you that it is non-performant... its taking too much of CPU or may be I/O or may be sorting (see previous post to know how u can list such queries)...
Well in such cases you always wonder what is it that is causing the problem... Which part of the query is causing the issue!!! :-o
More than that generally what is reported is an part of application or a module which has more response time...
So how do we proceed...
Here is a db2something to rescue you... db2exfmt
This tool will help you format the content of db2 EXPLAIN tables. AIX users can locate this in DB2_HOME/sqllib/misc
Let's see how you can use this tool, we will discuss two scenarios, one where we know the bad performing query and other where we know a part of application which has perormance issues:
Prerequisite
-- You need to create EXPLAIN tables using EXPLAIN.DDL in the above mentioned directory
For first scenario:
-- Use the EXPLAIN statement to generate explain information for a query
Following are the valid db2 queries/commands which can be used in explain statement:
* CALL, Compound SQL (Dynamic), DELETE, INSERT
* MERGE
* REFRESH
* SELECT
* SELECT INTO
* SET INTEGRITY
* UPDATE
* VALUES
* VALUES INTO
Example:
>>> EXPLAIN ALL FOR SELECT Col1 FROM Test_Tab
In explain statement use WITH SNAPSHOT to ensure that snapshot is also recorded with the explain info...
This step will capture the explain information in the explain tables.
-- In case you of scenario 2, that is you want to see performance hotspots in your application... Use SET EXPLAIN SNAPSHOT and EXPLAIN MODE to set /EXPLAINregistry/ /EXPLAIN SNAPSHOT This will enable capturing snapshot/explain info for all the queries which will be fired by application in that session...
-- Once you have the EXPLAIN info captured use db2exfmt to format the explain data
db2exfmt can be either invoked with arguments or can be invoked in interactive mode.
Now lets explore what the various info you can get from the explain tables using exfmt:
>> You can use -g option to generate graph. There are vaiour sub-options to include more detailed info in the graph:
-gT will include cost for each operation in graph
-gI will include I/O for every operation in graph
-gF will include first tuple cost
-gC will include the expected cardinality
One can combine any of the above options e.g. -gCI shall give cardinality and I/O cost for every operation in graph
You can either choose to format the latest explain info or can -w timestamp for explaining a specific timestmp's explain info...
Or you can specify SOURCE_SCHEMA/SOURCE NAME to narrow down you search for hotspots... using -s or -n
-o / -t will let you re-direct the output to an output file or to your terminal.
Well now it is worth mentioning about other tools in db2 available for similar usage
>> with db2expln you can get the explain plan in form of a graph with less hassles, but you need to use it for individual queries. It is much better when you have few queries and when you exactly know the query, else
it can not be used to capture the explain info of all queries being fired on db from application
>> with db2caem (db2 create activity event monitor) tool you can do all these stuff with much more ease... But you need to be on db2 9.7 fix pack 3... Trust me this tool is the best you would have ever used for getting explain plan... An do watch the replay of episode #45, where some one has explain this tool quite well...
Well next time I will be back with a success story... Probably I will share how we monitored and got rid of high I/O contention and memory usage issues...
Well getting back to my "would have been" presentation...
db2exfmt
Well lot many times a query will be slapped back on your face telling you that it is non-performant... its taking too much of CPU or may be I/O or may be sorting (see previous post to know how u can list such queries)...
Well in such cases you always wonder what is it that is causing the problem... Which part of the query is causing the issue!!! :-o
More than that generally what is reported is an part of application or a module which has more response time...
So how do we proceed...
Here is a db2something to rescue you... db2exfmt
This tool will help you format the content of db2 EXPLAIN tables. AIX users can locate this in DB2_HOME/sqllib/misc
Let's see how you can use this tool, we will discuss two scenarios, one where we know the bad performing query and other where we know a part of application which has perormance issues:
Prerequisite
-- You need to create EXPLAIN tables using EXPLAIN.DDL in the above mentioned directory
For first scenario:
-- Use the EXPLAIN statement to generate explain information for a query
Following are the valid db2 queries/commands which can be used in explain statement:
* CALL, Compound SQL (Dynamic), DELETE, INSERT
* MERGE
* REFRESH
* SELECT
* SELECT INTO
* SET INTEGRITY
* UPDATE
* VALUES
* VALUES INTO
Example:
>>> EXPLAIN ALL FOR SELECT Col1 FROM Test_Tab
In explain statement use WITH SNAPSHOT to ensure that snapshot is also recorded with the explain info...
This step will capture the explain information in the explain tables.
-- In case you of scenario 2, that is you want to see performance hotspots in your application... Use SET EXPLAIN SNAPSHOT and EXPLAIN MODE to set /EXPLAINregistry/ /EXPLAIN SNAPSHOT This will enable capturing snapshot/explain info for all the queries which will be fired by application in that session...
-- Once you have the EXPLAIN info captured use db2exfmt to format the explain data
db2exfmt can be either invoked with arguments or can be invoked in interactive mode.
Now lets explore what the various info you can get from the explain tables using exfmt:
>> You can use -g option to generate graph. There are vaiour sub-options to include more detailed info in the graph:
-gT will include cost for each operation in graph
-gI will include I/O for every operation in graph
-gF will include first tuple cost
-gC will include the expected cardinality
One can combine any of the above options e.g. -gCI shall give cardinality and I/O cost for every operation in graph
You can either choose to format the latest explain info or can -w timestamp for explaining a specific timestmp's explain info...
Or you can specify SOURCE_SCHEMA/SOURCE NAME to narrow down you search for hotspots... using -s or -n
-o / -t will let you re-direct the output to an output file or to your terminal.
Well now it is worth mentioning about other tools in db2 available for similar usage
>> with db2expln you can get the explain plan in form of a graph with less hassles, but you need to use it for individual queries. It is much better when you have few queries and when you exactly know the query, else
it can not be used to capture the explain info of all queries being fired on db from application
>> with db2caem (db2 create activity event monitor) tool you can do all these stuff with much more ease... But you need to be on db2 9.7 fix pack 3... Trust me this tool is the best you would have ever used for getting explain plan... An do watch the replay of episode #45, where some one has explain this tool quite well...
Well next time I will be back with a success story... Probably I will share how we monitored and got rid of high I/O contention and memory usage issues...
db2 performance: How to convey performance metrics from SNAPSHOT vi...
db2 performance: How to convey performance metrics from SNAPSHOT vi...: "I had recently participated in db2 has talent and was lucky to be selected for finals... I missed the finals though due to my travel schedul..."
How to convey performance metrics from SNAPSHOT views...
I had recently participated in db2 has talent and was lucky to be selected for finals... I missed the finals though due to my travel schedule... But I will write my next few blog posts with the content I would have presented if I would have reached till last episode... And you do watch the actual show (it has tips much more kwel)
To start with I will write on Performance metrics from snapshot info which can help you identify performance issues (especially before you hit any bottle necks)...
Sometime back I had been involved in a performance tuning exercise. We tuned all the top expensive SQL, we added a few new indexes as well (a lot is identified during development itself, a good practice to have), we had fixed all the individual SQLs which were taking time more than the benchmark. Despite all this, overall system performance still could not come up... I decided to take up the task of monitoring the database while performance load tests... That was an adventurous experience... I had decided to track the following:
1) SQLs which are although below the benchmark but are taking more time compared to
what they should be taking
2) SQLs which are spending too much of time in sorting the data
3) SQLs which are executed with a misguided plan (i.e. not getting proper statistics)
4) SQLs which are spending too much time on I/O and fetching lesser no of rows i.e.
which very high I/O time per row fetched
5) I started looking for no of data pages and index pages in bufferpool that are read
and written by a query (trust me you can not get a better metrics for identifying
table scans)
6) One thing which I had not earlier not thought of but eventually became my concern
was SQL which get executed only once or twice
7) SQLs which are executed over and over and total execution time was too much
8) How efficient is log write and log reads
9) Are there too long transactions and what is the frequency of commit
After this I had to explore a lot on which snapshot view has what info, which can help me arriving at the correct conclusion regarding above points... Going thru all the snapshot views and their columns helped me adding a few of above parameters (which i didnt had on my list when I started)
Following metrics helped me get the stats I was looking for:
Following statements helped me get some metrics for statements:
1) SUM(TOTAL_SORT_TIME)/NULLIF(SUM((STMT_USR_CPU_TIME_S*1000000) + STMT_USR_CPU_TIME_MS) + SUM(TOTAL_SORT_TIME),0 )
AS TOTAL_SORT_TIME_FRACTION
2) SUM((POOL_TEMP_INDEX_L_READS+POOL_TEMP_DATA_L_READS) - (POOL_TEMP_DATA_P_READS+POOL_TEMP_INDEX_P_READS))/NULLIF(SUM(POOL_TEMP_INDEX_L_READS+POOL_TEMP_DATA_L_READS),0)
AS TOTAL_TEMP_BP_UTIL
3) SUM(POOL_DATA_P_READS)/(NULLIF(SUM(POOL_DATA_L_READS),0)) TOTAL_DATA_BP_REFRESH
4)QUERY_CARD_ESTIMATE AS EXPECTED_CARDINALITY,
ROWS_READ, where EXPECTED_CARDINALITY/ROWS_READ>=1.5
5) SUM(CAST(total_sort_time as decimal(24,10)))/NULLIF(SUM((STMT_USR_CPU_TIME_S*1000000) + STMT_USR_CPU_TIME_MS),0 )
AS TOTAL_SORT_TIME_FRACTION
Following metrics helped me check the dynamic sqls (from SNAPDYN_SQL snapshot view):
6) CAST(TOTAL_SORT_TIME*(NUM_EXECUTIONS)as decimal(24,10))/NULLIF(PREP_TIME_BEST * NUM_COMPILATIONS,0) AS SORT_TO_COMPILE_RATIO
7) CAST(((PREP_TIME_WORST + PREP_TIME_BEST)*NUM_COMPILATIONS)as decimal(24,10))/NULLIF(2*NUM_EXECUTIONS,0) COMPILE_EFFICIENCY,
8) cast(SUM((POOL_TEMP_INDEX_L_READS+POOL_TEMP_DATA_L_READS) - (POOL_TEMP_DATA_P_READS+POOL_TEMP_INDEX_P_READS)) as decimal(24,10))/NULLIF(SUM(POOL_TEMP_INDEX_L_READS+POOL_TEMP_DATA_L_READS),0)
AS TOTAL_TEMP_BP_UTIL
Following are helpful metrics for monitoring locking performance (from SNAPDB):
9) LOCK_ESCALS LOCK_ESCALATIONS,
10) DEADLOCKS/NULLIF(LOCK_WAITS,0) DEADLOCK_PER_LOCK,
11) X_LOCK_ESCALS EXCLUSIVE_LOCL_ESCAL,
12) LOCKS_WAITING/NULLIF(LOCKS_HELD,0) LOCK_ISSUES_PER_LOCK,
13) DEADLOCKS/NULLIF(COMMIT_SQL_STMTS,0) DEADLOCK_PER_STMNT
Following are some good metrics to look for while measuring i/o efficiency (from SNAPDB)
14) cast(POOL_DRTY_PG_STEAL_CLNS*1000 as decimal(24,10))/ NULLIF(COMMIT_SQL_STMTS,0)
DIRTY_PAGE_STEAL_FRACTION,
15) cast(DIRECT_READS as decimal(20,10)) / NULLIF(DIRECT_READ_REQS,0) DIRECT_READ_EFFECIENCY,
16) cast(float(NUM_LOG_WRITE_IO)/NULLIF(LOG_WRITES,0) as decimal(20,10)) LOG_IO_PER_WRITE,
17) cast(float(NUM_LOG_READ_IO)/NULLIF(LOG_READS,0) as decimal(20,10)) LOG_IO_PER_READ,
18) cast(NUM_LOG_BUFFER_FULL as decimal(20,10))/NULLIF(LOG_WRITES,0) LOG_BUFF_UTIL,
19) CAST(LOG_WRITES AS DECIMAL(20,10))/NULLIF(COMMIT_SQL_STMTS,0) LOG_WRITE_EFFECIENCY,
20) cast(NUM_LOG_DATA_FOUND_IN_BUFFER as decimal(20,10))/NULLIF(NUM_LOG_READ_IO+NUM_LOG_DATA_FOUND_IN_BUFFER,0) AS LOG_BUFF_READ_EFFICIENCY,
21) cast(LOG_HELD_BY_DIRTY_PAGES *100 as decimal(24,10))/(10240*4096) AS LOG_IOCLEANERS_EFFICIENCY,
22) cast(UNREAD_PREFETCH_PAGES as decimal(24,10))/NULLIF(PREFETCH_WAIT_TIME,0) AS WASTEFULL_PREFETCH_PER_WAIT_MSEC
23) cast(POOL_NO_VICTIM_BUFFER as decimal(24,10))/NULLIF((POOL_INDEX_P_READS+POOL_DATA_P_READS),0) AS BP_CLEANER_FAILURE_FRACTION
24) --Overall Bufferpool Utilization per bufferpool
SELECT
SNAPSHOT_TIMESTAMP,
DB_NAME,
BP_NAME,
TOTAL_LOGICAL_READS,
TOTAL_PHYSICAL_READS,
TOTAL_HIT_RATIO_PERCENT,
INDEX_HIT_RATIO_PERCENT,
DATA_HIT_RATIO_PERCENT,
DATA_LOGICAL_READS,
DATA_PHYSICAL_READS,
INDEX_LOGICAL_READS,
INDEX_PHYSICAL_READS
FROM BP_HITRATIO
where DATA_HIT_RATIO_PERCENT<90 OR INDEX_HIT_RATIO_PERCENT<95;
25)
SELECT
SNAPSHOT_TIMESTAMP,
BP_NAME,
CAST(VECTORED_IOS as decimal(24,10))/NULLIF((POOL_DATA_P_READS+POOL_INDEX_P_READS),0) AS VECTORED_IO_FRACTION,
CAST(PAGES_FROM_VECTORED_IOS as decimal(24,10))/NULLIF((PAGES_FROM_VECTORED_IOS+PAGES_FROM_BLOCK_IOS),0) AS VECTORED_PAGE_READ_FRACTION,
CAST(UNREAD_PREFETCH_PAGES as decimal(24,10))/NULLIF(PAGES_FROM_BLOCK_IOS,0) AS WASTEFULL_PREFETCH_FRACTION,
CAST((POOL_ASYNC_DATA_READ_REQS + POOL_ASYNC_INDEX_READ_REQS) as decimal(24,10))/NULLIF((POOL_DATA_P_READS+POOL_INDEX_P_READS)-VECTORED_IOS,0) AS ANSYNC_PREFETCH_EFFICIENCY,
CAST(POOL_NO_VICTIM_BUFFER as decimal(24,10))/NULLIF((POOL_INDEX_P_READS+POOL_DATA_P_READS),0) AS BP_CLEANER_FAILURE_FRACTION
FROM SNAPBP;
Following parameters helped me get some imp points about how fine are the automatic maintainence parameters working
26)
cast(STATS_FABRICATIONS as decimal(24,10))/NULLIF((ASYNC_RUNSTATS+SYNC_RUNSTATS),0) AS STATS_USE_TO_GATHER_RATIO,
27)
cast(SYNC_RUNSTATS as decimal(24,10)) /NULLIF((SYNC_RUNSTATS+ASYNC_RUNSTATS),0) SYNC_RUNSTAT_FRACTION,
28)
cast(STATS_FABRICATE_TIME as decimal(24,10))/NULLIF(SYNC_RUNSTATS_TIME,0) AS STATS_SYNC_COMPUTE_UTIL,
29)
cast(SYNC_RUNSTATS_TIME*1000000 as decimal(24,10))/NULLIF(ELAPSED_EXEC_TIME_S+ELAPSED_EXEC_TIME_MS*1000000,0) AS SYNC_STAT_COLLECT_TIME_FRACTION
30) Two parameters in SYSIBMADM.SNAPDYN_SQL which can help you see if the queries provided by development is properly parameterized or not:
NUM_EXECUTIONS and NUM_COMPILATIONS
Following are some important monitor elements, which can help you see dynamic (SNAPDYN_SQL) or static sql (from SNAPSTMT) which are having heavy table scan operation
31)
CAST((POOL_DATA_L_READS-POOL_DATA_P_READS) as decimal(24,10))/NULLIF(POOL_DATA_L_READS,0) DATA_BP_UTIL,
32)
CAST(POOL_DATA_P_READS as decimal(24,10))/NULLIF(POOL_DATA_L_READS,0) DATA_BP_REFRESH,
33)
CAST((POOL_INDEX_L_READS-POOL_INDEX_P_READS)as decimal(24,10))/NULLIF(POOL_INDEX_L_READS,0) INDX_BP_UTIL,
34)
CAST(POOL_INDEX_P_READS as decimal(24,10))/NULLIF(POOL_INDEX_L_READS,0) INDX_BP_REFRESH,
35)
CAST((POOL_TEMP_INDEX_L_READS+POOL_TEMP_DATA_L_READS) - (POOL_TEMP_DATA_P_READS+POOL_TEMP_INDEX_P_READS) as decimal(24,10))/NULLIF(POOL_TEMP_INDEX_L_READS+POOL_TEMP_DATA_L_READS,0)
AS TOTAL_TEMP_BP_UTIL,
I hope above metrics and monitor elements help any one who reads this blog. I have kept the column alias as elaborate (to convey the meaning they carry) as possible. I might have ignored the parameters like STMT_USR_CPU_TIME_S and STMT_USR_CPU_TIME_MS but they are equally important. These snapshot elements mentioned by me will help you super tune your application, once you have already tuned the long running sqls and have indexed the database properly!!!
Would like to acknowledge the encouragements received from reader of previous blogs (the increasing no of views every time i opened this page is a boost factor) and Susan Visser (whose tweets inspired me to continue writing the blog)!!!
Will continue writing!!! db2expln is next to come!!!
To start with I will write on Performance metrics from snapshot info which can help you identify performance issues (especially before you hit any bottle necks)...
Sometime back I had been involved in a performance tuning exercise. We tuned all the top expensive SQL, we added a few new indexes as well (a lot is identified during development itself, a good practice to have), we had fixed all the individual SQLs which were taking time more than the benchmark. Despite all this, overall system performance still could not come up... I decided to take up the task of monitoring the database while performance load tests... That was an adventurous experience... I had decided to track the following:
1) SQLs which are although below the benchmark but are taking more time compared to
what they should be taking
2) SQLs which are spending too much of time in sorting the data
3) SQLs which are executed with a misguided plan (i.e. not getting proper statistics)
4) SQLs which are spending too much time on I/O and fetching lesser no of rows i.e.
which very high I/O time per row fetched
5) I started looking for no of data pages and index pages in bufferpool that are read
and written by a query (trust me you can not get a better metrics for identifying
table scans)
6) One thing which I had not earlier not thought of but eventually became my concern
was SQL which get executed only once or twice
7) SQLs which are executed over and over and total execution time was too much
8) How efficient is log write and log reads
9) Are there too long transactions and what is the frequency of commit
After this I had to explore a lot on which snapshot view has what info, which can help me arriving at the correct conclusion regarding above points... Going thru all the snapshot views and their columns helped me adding a few of above parameters (which i didnt had on my list when I started)
Following metrics helped me get the stats I was looking for:
Following statements helped me get some metrics for statements:
1) SUM(TOTAL_SORT_TIME)/NULLIF(SUM((STMT_USR_CPU_TIME_S*1000000) + STMT_USR_CPU_TIME_MS) + SUM(TOTAL_SORT_TIME),0 )
AS TOTAL_SORT_TIME_FRACTION
2) SUM((POOL_TEMP_INDEX_L_READS+POOL_TEMP_DATA_L_READS) - (POOL_TEMP_DATA_P_READS+POOL_TEMP_INDEX_P_READS))/NULLIF(SUM(POOL_TEMP_INDEX_L_READS+POOL_TEMP_DATA_L_READS),0)
AS TOTAL_TEMP_BP_UTIL
3) SUM(POOL_DATA_P_READS)/(NULLIF(SUM(POOL_DATA_L_READS),0)) TOTAL_DATA_BP_REFRESH
4)QUERY_CARD_ESTIMATE AS EXPECTED_CARDINALITY,
ROWS_READ, where EXPECTED_CARDINALITY/ROWS_READ>=1.5
5) SUM(CAST(total_sort_time as decimal(24,10)))/NULLIF(SUM((STMT_USR_CPU_TIME_S*1000000) + STMT_USR_CPU_TIME_MS),0 )
AS TOTAL_SORT_TIME_FRACTION
Following metrics helped me check the dynamic sqls (from SNAPDYN_SQL snapshot view):
6) CAST(TOTAL_SORT_TIME*(NUM_EXECUTIONS)as decimal(24,10))/NULLIF(PREP_TIME_BEST * NUM_COMPILATIONS,0) AS SORT_TO_COMPILE_RATIO
7) CAST(((PREP_TIME_WORST + PREP_TIME_BEST)*NUM_COMPILATIONS)as decimal(24,10))/NULLIF(2*NUM_EXECUTIONS,0) COMPILE_EFFICIENCY,
8) cast(SUM((POOL_TEMP_INDEX_L_READS+POOL_TEMP_DATA_L_READS) - (POOL_TEMP_DATA_P_READS+POOL_TEMP_INDEX_P_READS)) as decimal(24,10))/NULLIF(SUM(POOL_TEMP_INDEX_L_READS+POOL_TEMP_DATA_L_READS),0)
AS TOTAL_TEMP_BP_UTIL
Following are helpful metrics for monitoring locking performance (from SNAPDB):
9) LOCK_ESCALS LOCK_ESCALATIONS,
10) DEADLOCKS/NULLIF(LOCK_WAITS,0) DEADLOCK_PER_LOCK,
11) X_LOCK_ESCALS EXCLUSIVE_LOCL_ESCAL,
12) LOCKS_WAITING/NULLIF(LOCKS_HELD,0) LOCK_ISSUES_PER_LOCK,
13) DEADLOCKS/NULLIF(COMMIT_SQL_STMTS,0) DEADLOCK_PER_STMNT
Following are some good metrics to look for while measuring i/o efficiency (from SNAPDB)
14) cast(POOL_DRTY_PG_STEAL_CLNS*1000 as decimal(24,10))/ NULLIF(COMMIT_SQL_STMTS,0)
DIRTY_PAGE_STEAL_FRACTION,
15) cast(DIRECT_READS as decimal(20,10)) / NULLIF(DIRECT_READ_REQS,0) DIRECT_READ_EFFECIENCY,
16) cast(float(NUM_LOG_WRITE_IO)/NULLIF(LOG_WRITES,0) as decimal(20,10)) LOG_IO_PER_WRITE,
17) cast(float(NUM_LOG_READ_IO)/NULLIF(LOG_READS,0) as decimal(20,10)) LOG_IO_PER_READ,
18) cast(NUM_LOG_BUFFER_FULL as decimal(20,10))/NULLIF(LOG_WRITES,0) LOG_BUFF_UTIL,
19) CAST(LOG_WRITES AS DECIMAL(20,10))/NULLIF(COMMIT_SQL_STMTS,0) LOG_WRITE_EFFECIENCY,
20) cast(NUM_LOG_DATA_FOUND_IN_BUFFER as decimal(20,10))/NULLIF(NUM_LOG_READ_IO+NUM_LOG_DATA_FOUND_IN_BUFFER,0) AS LOG_BUFF_READ_EFFICIENCY,
21) cast(LOG_HELD_BY_DIRTY_PAGES *100 as decimal(24,10))/(10240*4096) AS LOG_IOCLEANERS_EFFICIENCY,
22) cast(UNREAD_PREFETCH_PAGES as decimal(24,10))/NULLIF(PREFETCH_WAIT_TIME,0) AS WASTEFULL_PREFETCH_PER_WAIT_MSEC
23) cast(POOL_NO_VICTIM_BUFFER as decimal(24,10))/NULLIF((POOL_INDEX_P_READS+POOL_DATA_P_READS),0) AS BP_CLEANER_FAILURE_FRACTION
24) --Overall Bufferpool Utilization per bufferpool
SELECT
SNAPSHOT_TIMESTAMP,
DB_NAME,
BP_NAME,
TOTAL_LOGICAL_READS,
TOTAL_PHYSICAL_READS,
TOTAL_HIT_RATIO_PERCENT,
INDEX_HIT_RATIO_PERCENT,
DATA_HIT_RATIO_PERCENT,
DATA_LOGICAL_READS,
DATA_PHYSICAL_READS,
INDEX_LOGICAL_READS,
INDEX_PHYSICAL_READS
FROM BP_HITRATIO
where DATA_HIT_RATIO_PERCENT<90 OR INDEX_HIT_RATIO_PERCENT<95;
25)
SELECT
SNAPSHOT_TIMESTAMP,
BP_NAME,
CAST(VECTORED_IOS as decimal(24,10))/NULLIF((POOL_DATA_P_READS+POOL_INDEX_P_READS),0) AS VECTORED_IO_FRACTION,
CAST(PAGES_FROM_VECTORED_IOS as decimal(24,10))/NULLIF((PAGES_FROM_VECTORED_IOS+PAGES_FROM_BLOCK_IOS),0) AS VECTORED_PAGE_READ_FRACTION,
CAST(UNREAD_PREFETCH_PAGES as decimal(24,10))/NULLIF(PAGES_FROM_BLOCK_IOS,0) AS WASTEFULL_PREFETCH_FRACTION,
CAST((POOL_ASYNC_DATA_READ_REQS + POOL_ASYNC_INDEX_READ_REQS) as decimal(24,10))/NULLIF((POOL_DATA_P_READS+POOL_INDEX_P_READS)-VECTORED_IOS,0) AS ANSYNC_PREFETCH_EFFICIENCY,
CAST(POOL_NO_VICTIM_BUFFER as decimal(24,10))/NULLIF((POOL_INDEX_P_READS+POOL_DATA_P_READS),0) AS BP_CLEANER_FAILURE_FRACTION
FROM SNAPBP;
Following parameters helped me get some imp points about how fine are the automatic maintainence parameters working
26)
cast(STATS_FABRICATIONS as decimal(24,10))/NULLIF((ASYNC_RUNSTATS+SYNC_RUNSTATS),0) AS STATS_USE_TO_GATHER_RATIO,
27)
cast(SYNC_RUNSTATS as decimal(24,10)) /NULLIF((SYNC_RUNSTATS+ASYNC_RUNSTATS),0) SYNC_RUNSTAT_FRACTION,
28)
cast(STATS_FABRICATE_TIME as decimal(24,10))/NULLIF(SYNC_RUNSTATS_TIME,0) AS STATS_SYNC_COMPUTE_UTIL,
29)
cast(SYNC_RUNSTATS_TIME*1000000 as decimal(24,10))/NULLIF(ELAPSED_EXEC_TIME_S+ELAPSED_EXEC_TIME_MS*1000000,0) AS SYNC_STAT_COLLECT_TIME_FRACTION
30) Two parameters in SYSIBMADM.SNAPDYN_SQL which can help you see if the queries provided by development is properly parameterized or not:
NUM_EXECUTIONS and NUM_COMPILATIONS
Following are some important monitor elements, which can help you see dynamic (SNAPDYN_SQL) or static sql (from SNAPSTMT) which are having heavy table scan operation
31)
CAST((POOL_DATA_L_READS-POOL_DATA_P_READS) as decimal(24,10))/NULLIF(POOL_DATA_L_READS,0) DATA_BP_UTIL,
32)
CAST(POOL_DATA_P_READS as decimal(24,10))/NULLIF(POOL_DATA_L_READS,0) DATA_BP_REFRESH,
33)
CAST((POOL_INDEX_L_READS-POOL_INDEX_P_READS)as decimal(24,10))/NULLIF(POOL_INDEX_L_READS,0) INDX_BP_UTIL,
34)
CAST(POOL_INDEX_P_READS as decimal(24,10))/NULLIF(POOL_INDEX_L_READS,0) INDX_BP_REFRESH,
35)
CAST((POOL_TEMP_INDEX_L_READS+POOL_TEMP_DATA_L_READS) - (POOL_TEMP_DATA_P_READS+POOL_TEMP_INDEX_P_READS) as decimal(24,10))/NULLIF(POOL_TEMP_INDEX_L_READS+POOL_TEMP_DATA_L_READS,0)
AS TOTAL_TEMP_BP_UTIL,
I hope above metrics and monitor elements help any one who reads this blog. I have kept the column alias as elaborate (to convey the meaning they carry) as possible. I might have ignored the parameters like STMT_USR_CPU_TIME_S and STMT_USR_CPU_TIME_MS but they are equally important. These snapshot elements mentioned by me will help you super tune your application, once you have already tuned the long running sqls and have indexed the database properly!!!
Would like to acknowledge the encouragements received from reader of previous blogs (the increasing no of views every time i opened this page is a boost factor) and Susan Visser (whose tweets inspired me to continue writing the blog)!!!
Will continue writing!!! db2expln is next to come!!!
Stuck with a packaged app???
So are you a db2 dba in a production environment and got stuck with a packaged app... You can not change the application nor you can change the database schema... oops...
May be the application is using non-parameterized queries!!! or may be they are sorting the data too much to fetch the first few records say on the basis of some date column... Too much of data is being stored in the same table, the record count forces a table scan!!! :(
and many more problems which could have been avoided at the design time, but you can not help it now... You have to pay for any changes... :-o
What to do???
a few tips... (* make sure you are on latest db2 version)
1) If you find that the application is not using parameterized queries and the same query is being compiled again and again, db2 9.7 has something special for you: db cfg stmt_conc. This ensures that the query parser replaces the literals with a bind variable if a similar query is identified it is used instead of compiling again...
update db cfg using STMT_CONC LITERAL
those of you who are coming from an Oracle Background can refer to cursor_sharing
2) If your application has too many fetches for first few rows and you use ROW_NUMBER for windowing and then filtering the top result... Please do not do that... Use optimize for first N rows or fetch first N rows... Those of you who are do so for ANSI compatibility or are simple stuck with a packaged application, you can got for setting OPTIMIZEFORNROWS in db2cli.ini
3) Well this one is a bit of trade-off... If you feel that the application is using queries which could have been optimized in a much better way, and they are nt ready to accept a change without funds... set the optimization level little higher to allow db2 use more brain for queries....
4) If you see that the issue is with only a few queries, do not change the optimization level rather use optimization profile
Well it is something oracle hints but not exactly the same... I can not explain the whole concept here, but yeah go thru this article on developer works...
5) If you feel the table should have been broken down into several entities, say account table could have been divided in to CREDIT ACCOUNTS and DEBITS ACCOUNT. But clubbing them together has caused an enormous row count... you can either actually break the table and create view on it having the same (older )table name... or a better way is to use db2 range partitioning feature (oh baby you gotta upgrade to v9)
6) You see that a table goes to high no of records with in a single transaction and the same part/module of the application will delete all the rows. Now the problem with this is
i) at any given point of time you table will have 0 records unless
this module is running
ii) Whenver RUNSTATS run (either auto or your cron job) the table
has got 0 records
iii) now when your module populates data all of a sudden the stats
will not be effective immediately but a fetch (probably in same
module) may be immediate
iv) The optimizer thinks the table is empty and generates a wrong
plan (a costly one) and avoids any index scan
well this is a problem you will face with temporary processing tables. The actual business logic could have been achieved with global temp tables or may by retaining the rows for next run of module (rows are deleted in starting and not at end hence your runstats always picks up a non-zero value as record count)... But that can not be done now...
a trick... say
alter table tabname volatile
this will enforce an index scan if the table is indexed
7) Use query compiler environment variable as below to optimize the queries before getting executed:
db2set db2_antijoin=EXTENDED
db2set db2_inlist_to_nljn=yes
8) Use db2_workload to set proper category of your application, e.g. it can be set at once to group several environment variable hence to facilitate a better performance with that application... e.g. it can be set to cognos or sap etc...
Well now m feeling sleeepy... will modify the post if i can recollect more tips for dbas STUCK with packaged apps...
May be the application is using non-parameterized queries!!! or may be they are sorting the data too much to fetch the first few records say on the basis of some date column... Too much of data is being stored in the same table, the record count forces a table scan!!! :(
and many more problems which could have been avoided at the design time, but you can not help it now... You have to pay for any changes... :-o
What to do???
a few tips... (* make sure you are on latest db2 version)
1) If you find that the application is not using parameterized queries and the same query is being compiled again and again, db2 9.7 has something special for you: db cfg stmt_conc. This ensures that the query parser replaces the literals with a bind variable if a similar query is identified it is used instead of compiling again...
update db cfg using STMT_CONC LITERAL
those of you who are coming from an Oracle Background can refer to cursor_sharing
2) If your application has too many fetches for first few rows and you use ROW_NUMBER for windowing and then filtering the top result... Please do not do that... Use optimize for first N rows or fetch first N rows... Those of you who are do so for ANSI compatibility or are simple stuck with a packaged application, you can got for setting OPTIMIZEFORNROWS in db2cli.ini
3) Well this one is a bit of trade-off... If you feel that the application is using queries which could have been optimized in a much better way, and they are nt ready to accept a change without funds... set the optimization level little higher to allow db2 use more brain for queries....
4) If you see that the issue is with only a few queries, do not change the optimization level rather use optimization profile
Well it is something oracle hints but not exactly the same... I can not explain the whole concept here, but yeah go thru this article on developer works...
5) If you feel the table should have been broken down into several entities, say account table could have been divided in to CREDIT ACCOUNTS and DEBITS ACCOUNT. But clubbing them together has caused an enormous row count... you can either actually break the table and create view on it having the same (older )table name... or a better way is to use db2 range partitioning feature (oh baby you gotta upgrade to v9)
6) You see that a table goes to high no of records with in a single transaction and the same part/module of the application will delete all the rows. Now the problem with this is
i) at any given point of time you table will have 0 records unless
this module is running
ii) Whenver RUNSTATS run (either auto or your cron job) the table
has got 0 records
iii) now when your module populates data all of a sudden the stats
will not be effective immediately but a fetch (probably in same
module) may be immediate
iv) The optimizer thinks the table is empty and generates a wrong
plan (a costly one) and avoids any index scan
well this is a problem you will face with temporary processing tables. The actual business logic could have been achieved with global temp tables or may by retaining the rows for next run of module (rows are deleted in starting and not at end hence your runstats always picks up a non-zero value as record count)... But that can not be done now...
a trick... say
alter table tabname volatile
this will enforce an index scan if the table is indexed
7) Use query compiler environment variable as below to optimize the queries before getting executed:
db2set db2_antijoin=EXTENDED
db2set db2_inlist_to_nljn=yes
8) Use db2_workload to set proper category of your application, e.g. it can be set at once to group several environment variable hence to facilitate a better performance with that application... e.g. it can be set to cognos or sap etc...
Well now m feeling sleeepy... will modify the post if i can recollect more tips for dbas STUCK with packaged apps...
10 Things to have in DB2: which shall improve performance..
Well I support development of a product which "claims" to be RDBMS independent... I know wat it takes to put forward a claim like that... I an more than me my senior colleagues have to break there head every time we have a porting... I remember the things I had faced, right from my senior porting the ER design from one db to other db and then creating the physical design of db and more than that security architecture... And haash how can i forget those incompatible functions and operators... No offences Mr Gates... But SQL Server has some out of the blue function arguement (what will be the most logical sequence of arguments if you wanna convert an expression to other data type, i bet most of you will miss the actual sequence used by SQL Server) and that concatenation operator X-( ... Well all of them have some or the things different... I can go on and on in blog post on that topic... Well lets see what features are good to have in DB2 (may or may not be inspired by other vendors), especially to from the performance angle... Lets try to list 10 of them (it's gonna be tuff for me, esp after the viper and cobra series)...
1) SQL Server 2008 has something called filter based indexes: Many of you might have faced an issue where you have multiple status to be stored in same column, but you will generally query on one/two of them as filter, other status are basically for display in inquiry. So how good it wud had been if you can index only those two status which are your filter criteria and save indexing cost and space... Your queries will be optimized for those two status only (like you say check and do not to enforce it and query is optimized for valid values)
2) Hierarchical Queries: I have worked with Oracle Hierarchical queries and have found them to be performing better than the recursive SQLs used to write pseudo-Hierarchical queries (yes that is what i will call them, for records recursive sql is available in Oracle 11g onwards)
3) Something as good as OEM in Oracle to monitor the database (and it should be free, hence optim is ruled out :P)
4) Forced and offline re-org after table/column alter should be fixed (no other RDBMS i have worked with requires this and yes i will count this as a performance issue in addition to being an availability issue)
5) Well there are queries generated by hibernate which uses ROW_NUMBER to window a table and fitler based on rownum<=N to select top N rows over a criteria, say last update date time. These kind of statements are automatically converted to SELECT TOP N for sql server 2008. It would had been nice if DB2 did the same, in an easier way (it is still possible with OPTIMIZEFORNROWS in db2cli.ini)
6) The optimizer should be able to guess, if the column(s) in join predicate is also part of some filter (where) predicate, and should apply the filter twice to get a better cardinality expectation. Well, I am not very sure if any RDBMS does this currently. But try it out and see the difference in expected cardinality yourself...
7) It will be good to have a better concurrency control and locking mechanism which can compete with that of oracle
8) Query Hints should be made more flexible and easy to use in prepared statements, on lines with Oracle and SQL Server
9) There should be function based indexes (currently the feature can be implemented with a little bit of work around by adding a computed column and indexing it)
10) Some good way of indexing low cardinality tables (like oracle has bit map indexes)
Well let's see how much db2 galelio has to offer!!! Have look:
DB2 Galileo Early Adoption Program
1) SQL Server 2008 has something called filter based indexes: Many of you might have faced an issue where you have multiple status to be stored in same column, but you will generally query on one/two of them as filter, other status are basically for display in inquiry. So how good it wud had been if you can index only those two status which are your filter criteria and save indexing cost and space... Your queries will be optimized for those two status only (like you say check and do not to enforce it and query is optimized for valid values)
2) Hierarchical Queries: I have worked with Oracle Hierarchical queries and have found them to be performing better than the recursive SQLs used to write pseudo-Hierarchical queries (yes that is what i will call them, for records recursive sql is available in Oracle 11g onwards)
3) Something as good as OEM in Oracle to monitor the database (and it should be free, hence optim is ruled out :P)
4) Forced and offline re-org after table/column alter should be fixed (no other RDBMS i have worked with requires this and yes i will count this as a performance issue in addition to being an availability issue)
5) Well there are queries generated by hibernate which uses ROW_NUMBER to window a table and fitler based on rownum<=N to select top N rows over a criteria, say last update date time. These kind of statements are automatically converted to SELECT TOP N for sql server 2008. It would had been nice if DB2 did the same, in an easier way (it is still possible with OPTIMIZEFORNROWS in db2cli.ini)
6) The optimizer should be able to guess, if the column(s) in join predicate is also part of some filter (where) predicate, and should apply the filter twice to get a better cardinality expectation. Well, I am not very sure if any RDBMS does this currently. But try it out and see the difference in expected cardinality yourself...
7) It will be good to have a better concurrency control and locking mechanism which can compete with that of oracle
8) Query Hints should be made more flexible and easy to use in prepared statements, on lines with Oracle and SQL Server
9) There should be function based indexes (currently the feature can be implemented with a little bit of work around by adding a computed column and indexing it)
10) Some good way of indexing low cardinality tables (like oracle has bit map indexes)
Well let's see how much db2 galelio has to offer!!! Have look:
DB2 Galileo Early Adoption Program
Well,
Performance Engineering for Databases... it is not just about identifying and tuning the issues... or removing the bottlenecks... More than that it is avoiding those bottleneck to occur... More of guessing the issues beforehand rather than identifying it latter...
So it involves following...
Having a more scalable Physical design...
A more robust logical schema design...
A more performing application design...
Let me discuss in brief
To start with Physical Design...
PHYSICAL DESING
With DB2 you need to ensure following things in physical design
1) You have tablespaces distributed on different disks
2) With my personal experience I suggest usage of ASM spread over several disks
3) Always have indexes and Data on saperate disks
4) It is good to categorize table and indexes as below
i) Online Transaction Data
ii) Configuraiton Data
iii) Static data for your application to work and load
Place tables of above category in three different tablespaces and their indexes in three
different
5) Always create separate bufferpools for configuration & static data and transaction data
and indexes
6) Always create tablespaces with NO FS option to avoid double caching
7) Always better to use STMM for better memory management
8) Set a proper value for TRANSFERRATE and OVERHEAD for tablespaces, as they tell the optimizer about the time taken for seek and transfer of data from disk. A proper value can at times help the optimizer choose index scan over tables scan (say in a situation when table data may not be too scattered but index leaves are)
SCHEMA DESING
1) Avoid having foreign keys if your application takes care of integrity
2) Always index the columns used for
i) WHERE clause filter
ii) JOIN predicates
iii) SORT operation (ORDER BY, MAX(), MIN() etc)
3) Always keep the STATISTICS updated for all the tables to ensure index scans
4) If the cardinality of any table changes abruptly, consider making the table VOLATILE. this will help you ensure and index scan
APPLICATION DESIGN
1) Always use ANSI JOIN operator for joining tables. This avoids any accidental cross product
2) Try not to use too many sort operations in your query eg multiple MAX etc. instead see if you can use RANKING or windowing functions
3) Avoid doing JOINs with VIEWs, instead join with tables, this will avoid JOINing with any unnecessary underlaying table of view.
4) Avoid self joins and prefer case statements over self joins if possible
5) Use FETCH FIRST N rows to optimize selection for few rows if using a packaged application set OPTIMIZEFORNROWS in db2cli.ini.
6) As a thumb of rule, use parmeterized queries. When working with a packaged application you can set statement concentrator db cfg to force parameterization
7) When working with reporting tools like Crystal Report, use a single query to fetch the data. Never use links and joins provided by tool. The tool might be doing a full fetch and a join locally. This has an impact on IO and network as well.
8) Have a better concurrency in application
CONCURRENCY CONTROL
To improve the concurrency of the application use following:
1) Use Cursor Stability Isolation Level
2) Use LOCKSIZE row for all the tables
3) Use optimistic locking by using ROW CHANGE TIMESTAMP expression
4) Set following parameter
i) db2set db2_skipinterted=ON
ii) db2set db2_skipdeleted=ON
iii) db2set db2_evaluncommitted=ON
iv) db cfg cur_commit
5) Avoid long transaction and ensure the operations are committed regularly
I will wind up this post with a note that setting this right will always help you avoiding any performance issues in future....
I will try to come up with some SQL Tuning Tips...
Subscribe to:
Posts (Atom)