tag:blogger.com,1999:blog-13471370459803884922024-03-19T04:58:23.025-07:00db2 performanceI will be discussing my daily experiences and tips I have learned (from google, my seniors and various other blogs) for better db2 performance...Sameerhttp://www.blogger.com/profile/04380280581835257131noreply@blogger.comBlogger24125tag:blogger.com,1999:blog-1347137045980388492.post-8930441979801950742012-06-25T10:09:00.000-07:002012-06-26T06:51:52.239-07:00An Encounter with chain of lockwait!!!<div dir="ltr" style="text-align: left;" trbidi="on"><br />
I have been aways from blogging for quite a long time, the office hours have been really hectic and then some personal engagements. The weather is getting awesome with <i>Maunsoon</i> approaching.<br />
<br />
So here I am back with all my energy to share some piece of knowledge which I myself acquired during this hectic office schedule. So let me start with "a story'.<br />
<br />
There was some part of application which was facing lock timeout issues, people blamed the application code and tried to figure out/locate the piece of code which may be causing this, no one could succeed. Later it could be observed in other places as well. Now it was more important to find out the set of queries which may be causing this.<br />
<br />
To find out details of lockwaits you can query the view SYSIBMADM.MON_LOCKWAITS to get details of locks which may be causing lockwaits...<br />
These details though will be only level one, and will give only immediate blocked query, if the actual query which is suffering is in-turn dependent on this query, you will have to again query the same view. There are very good chances that by the time you get to second level of query the query would have either committed/forcefully rolledback (in case of a deadlock victim) or timed out.<br />
<br />
So I decided to write a piece of SQL which can give me details of the lock and chain of lock wait.<br />
So I wrote a view (please download the definition from <a href="https://docs.google.com/open?id=0B0SmwbWPbul9U0lSRWxWUmZNUFE">here</a>).<br />
<br />
<br />
So I tried to test the dead lock scenario. I created below tables:<br />
<br />
<table border="1" ><tr> <td bgcolor="#C0C0C0"> <b> <u> Tables :</u> TEST and TEST2 </b></br><br />
</td> </tr>
<tr> <td><br />
create table test2 (col1 int, col2 int);<br />
create unique index ind_test2 on test2 (col1);<br />
commit;<br />
</td> </tr>
<tr> <td><br />
create table test (col1 int, col2 int);<br />
create unique index ind_test on test (col1);<br />
commit;<br />
</tr><br />
<br />
<br />
<br />
<br />
<br />
<br />
</td> </tr>
</table><br />
<br />
Once the tables were created, I fired some queries from three different session so that each one would block other:<br />
<br />
<table border="1" ><tr> <td bgcolor="#C0C0C0"><br />
<u>Session1</u><br />
</td> <td bgcolor="#C0C0C0"><br />
<u>Session2</u><br />
</td> <td bgcolor="#C0C0C0"><br />
<u>Session3</u><br />
</td> </tr>
<tr> <td ><br />
insert into test2 (col1 , col2) values (1,2);<br />
</td> <td><br />
insert into test (col1 , col2) values (1,2);<br />
insert into test2 (col1 , col2) values (1,2);<br />
</td> <td><br />
insert into test (col1 , col2) values (1,2);<br />
</td> </tr>
</table><br />
<br />
Then from a forth session I fired "select * from lockChain_INFO"<br />
Which gave an output similar to below one:<br />
<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi84eZngsdG5FzyqcC9hdL219V_7MFFng1VLflVE7aO7VvBnOWh33kbkxFEsSK19NIQ6FPYicZMiAyadV_IQgAMsRdO3iJdwuk_XrA2__awEk30CMYnqNYOQcIZytT7hS0WPsJgOnpa7Mk/s1600/Lock_wait_chain.JPG" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="107" width="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi84eZngsdG5FzyqcC9hdL219V_7MFFng1VLflVE7aO7VvBnOWh33kbkxFEsSK19NIQ6FPYicZMiAyadV_IQgAMsRdO3iJdwuk_XrA2__awEk30CMYnqNYOQcIZytT7hS0WPsJgOnpa7Mk/s400/Lock_wait_chain.JPG" /></a><br />
<br />
<br />
<br />
Then from session1 I fired one more query which was:<br />
<i>insert into test (col1 , col2) values (1,2);<br />
</i><br />
After this when I again queried the same view I got below result:<br />
<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgXVSHPx3WGnYBtaCq4mY-lNRVc_StO7yn-0qg4Y8k94KEIPmO61PoSzzx7InvttUd-15vzmXo68fI3ZH8uSN0d24vlhFtjN7_BRqPDzFA0CCBnsN3tTNQA2iFYmaVL62f48seW2X4i-lQ/s1600/DeadLock.JPG" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="257" width="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgXVSHPx3WGnYBtaCq4mY-lNRVc_StO7yn-0qg4Y8k94KEIPmO61PoSzzx7InvttUd-15vzmXo68fI3ZH8uSN0d24vlhFtjN7_BRqPDzFA0CCBnsN3tTNQA2iFYmaVL62f48seW2X4i-lQ/s400/DeadLock.JPG" /></a><br />
<br />
<br />
<br />
These are insert statements and are static ones, and can easily give you the cause of locking (i.e. clashing index keys). Imagine if you are trying to update the same row from two session (and that in application which uses dynamic query so you are not sure of the row).<br />
<br />
To help you there is a function available which will provide you the lock info: <b>MON_FORMAT_LOCK_NAME<br />
</b><br />
<br />
<i>SELECT SUBSTR(NAME,1,20) AS NAME, SUBSTR(VALUE,1,50) AS VALUE FROM TABLE(MON_FORMAT_LOCK_NAME('LOCK_ID')) as LOCK_DTL;<br />
</i><br />
<br />
when lock_id is BLOCKING_LOCK received from view. This query gave descriptive info about the lock as in which row_id and which table etc:<br />
<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh9Skl8lF_Sof6bjAy-Ui9-Jmj1xIaLOJDn6djZe3dzfCku89Ez7CS3bYlH_YYtxi8nnZqyCIxldn45dzS8kLGQQt3Rs6A1SKTtah9kpp7i5R7PUeyDpiqvxGsITUU6EXWGqn9L0Gv8Xis/s1600/lock_details.JPG" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="81" width="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh9Skl8lF_Sof6bjAy-Ui9-Jmj1xIaLOJDn6djZe3dzfCku89Ez7CS3bYlH_YYtxi8nnZqyCIxldn45dzS8kLGQQt3Rs6A1SKTtah9kpp7i5R7PUeyDpiqvxGsITUU6EXWGqn9L0Gv8Xis/s400/lock_details.JPG" /></a><br />
<br />
<br />
This granular info helped me figure out that the issue was not actually in application or database, there was an issue out of configuration created by the client, which forced usage/update of same row for multiple purpose.<br />
<br />
Once this issue was identified, we suggested configuration changes which smoothed the things.<br />
<br />
hope this will be equally useful for you guys! :-)<br />
<br />
I had worked on couple of more similar snapshot views, which I will be sharing in blogs to come!<br />
<br />
<br />
<table border="1" ><tr> <td bgcolor="#C0E0E0"> <b> <u> Download View:</u></b><br />
</td> <td><b><br />
<a href="https://docs.google.com/open?id=0B0SmwbWPbul9U0lSRWxWUmZNUFE">lockChain_INFO</a> </br><br />
</b><br />
</td> </tr>
</table></div>Sameerhttp://www.blogger.com/profile/04380280581835257131noreply@blogger.com5tag:blogger.com,1999:blog-1347137045980388492.post-31024116973869126802012-05-01T08:08:00.002-07:002012-05-01T12:04:16.403-07:00DB2 10 is here: What's new? The performance aspect!!!<div dir="ltr" style="text-align: left;" trbidi="on">Hi guys... So DB2 10 is here and is <a href="http://www14.software.ibm.com/webapp/download/search.jsp?pn=DB2">available for download</a>...<br />
<br />
I was involved in DB2 10 beta EAP and you can listen to <a href="http://www.ibm.com/developerworks/data/events/db210champions/index.html">my podcast</a> with industry experts (which quite an honor)...<br />
<br />
Well lets see what's new in v10 which can help in improving product performance...<br />
<br />
<b>It has an improved index scan which is termed as jump scan</b>, for people from Oracle world this is similar to skip scan... I tried this out during EAP and have seen significant improvement in CPU timings and if you have wisely chosen your tablespace sizing e.g. extentsize and pagesize I/O also will decrease... This feature is specifically helpful if you have an index on two columns and you have queries on second key of the index... This wisely chooses the index keys which shall be evaluated for the match! <br />
<br />
<b>Second improvement is in terms of I/O, Smart Prefetching</b>... This is particularly helpful for data spread over a highly clustered disk or if index pages have low density... In such cases it can automatically switch from sequential to readahead (a new type of prefetching) prefetch... This is more efficient and automatically switched to readahead if optimizer see that sequential prefetch is wasting I/O...<br />
<br />
<br />
<b>There are a lot of new SQL optimization enhancements</b>, these are automatically taken care by optimizer when needed... For example now hash joins will be used by optimizer even if data types do not match, Early Aggregation of data, early removal of distincts, improvement in estimation of performance cost... There are various new indicators introduced in EXPLAIN_TABLES and EXPLAIN info generated by db2expln, which shall indicate if any of these features have been used or now... More on that latter...<br />
<br />
<br />
Another good improvement is that <b>RUNSTATS will now support index sampling</b>, which shall avoid the need to scan the whole index for collecting statistic... Also, the <b>new parameter auto_sampling</b> will enable sapling for all background statistics gathering... Another improvement from statistics gathering perspective is the <b>Automatic Stats gather for Statistical view when you set auto_stats_view</b>... Talking of statistical view reminds that there are few other improvements e.g. now actual statistics from expression columns will be respected for generation of a more apt plan...<br />
<br />
<br />
There are some <b>improvements in intra-parallelism for query</b>... Most catchy one is the <b>ability to do parallel scans on range partitioned tables and indexes</b>... Also the <b>intra parallelism can be controlled from application</b> by calling ADMIN_SET_INTRA_PARALLEL... Also <b>degree of parallelism now can be controlled for a particular workload</b> by using MAXIMUM DEGREE option in ALTER WORKLOAD...<br />
<br />
<br />
<br />
There is an <b>important improvement in STORAGE and I/O</b> i.e. <b>introduction of STORAGE GROUPS</b>... Earlier till v9.7 if I was chosing ASM tablespaces, I had to loose on distribution of I/O... i.e. all the tablespaces with ASM were created in the path designated for automatic storage (which is provided at the time of DB creation or later by alter DB)... But now one has to create storage groups and attach one or more storage path to a group... At the time of tablespace creation one will have to attach them to a storage group... So <b>now I can create all my ASM tablespaces on different disks</b>... So if you have already read my previous posts on tablespace design (<a href="http://db2performance.blogspot.in/2011/07/optimize-your-tablespaces-and.html">1</a>, <a href="http://db2performance.blogspot.in/2011/07/optimize-your-tablespaces-and_24.html">2</a>, and <a href="http://db2performance.blogspot.in/2011/10/optimize-your-tablespaces-and.html">3</a>) then you will know how good is this feature...<br />
<br />
I will wind up this post now and may be will try to put up all these features in little detail... <br />
<br />
Till then keep optimizing...<br />
<br />
So all those people who are running on v9 or prior should <a href="http://www-01.ibm.com/support/docview.wss?uid=swg21575777">definitely plan to migrate to this new version</a>... <br />
<br />
Oh yeah, the compatibility as well has been improved and is as good as 98%... well do not trust me??? read for yourself <a href="https://www.ibm.com/developerworks/mydeveloperworks/blogs/SQLTips4DB2LUW/entry/ninetyeightpercent?lang=en">this post</a> by <a href="https://twitter.com/#!/srielau">Serge Rielau</a>...<br />
<br />
<br />
Other posts/blogs regarding the new release:<br />
<br />
<a href="https://twitter.com/#!/srielau">Serge Rielau</a> has done a series of post on new features... Follow him on twitter to read more...<br />
<br />
<br />
DB2Night Show hosted by Scott Hayes <a href="http://www.dbisoftware.com/blog/db2nightshow.php?id=353"> has done an episode on same </a> and there is a sequel to follow...<br />
<br />
<br />
</div>Sameerhttp://www.blogger.com/profile/04380280581835257131noreply@blogger.com0tag:blogger.com,1999:blog-1347137045980388492.post-48951831440428102502012-04-06T11:35:00.000-07:002012-04-21T11:49:25.727-07:00A few tips for faster Bulk Refresh of a Table<br />
So I am back with some tips to improve insert performance... I am wondering why I did not choose this for my 2nd chance @ db2night show's DB2 has talent...<br />
Well a lot has happened in DB2 world meanwhile... <a href= "www.ibm.com/developerworks/data/events/db210champions/index.html ">There was a new LUW release</a> and I shall be soon writing some stuff about that..<br />
So there are various scenarios when you have to do a bulk insert and I too had to deal with one of those... The story and various solutions which came to me may bore you, but stick with it so that you know what you should not do and I will share some piece of code at the end... :)<br />
<br />
There are obviously other tips for improvement of bulk inserts or any type inserts but those are more to do with your DB CFG/ DB physical design... i will here share more on how you can handle it programatically...<br />
<br />
<br />
So there was this very complex query which read data from 6-7 tables all of which had around 4-5 million of records... With all the filters and everything the record returned by the query was around 6-7million... Obviously you can not process this chunk of data at once at your server and hence you pagenate the data and read only in bits... But again reading page by page means the view query is fired again and again and then sorting also happens for every new page read...<br />
So a better way is to either use a MQT or a just a table which is loaded with this data and rowsequence and then you can read in bits based on rowsequence... We had used the table approach in our scenario...<br />
<br />
I believe usage of MQT is good when you do not have to access MQT directly and your queries written over under-lying tables automatically use MQT... <br />
But the problem was the problem was the insert of data in the temporary table was taking a huge amount of time... Also we had indexes on a few columns which were accessed during processing... This is was not a single case and there were few more cases of such processes which did bulk delete and bulk insert which were slow... Moreover these temporary tables were to be marked/updated so that one know how the failure recovery should happen in case the process is Aborted/terminated abnormally... Another problem which complicated it, whatever solution I had to give should be generic and should work on Oracle and SQL Server as well (well that comes with my job)...<br />
<br />
Someone suggested that one can create a MQT for the query which populates data in the temporary table (and counter parts Materialized View and Indexed in Oracle and SQL Server respectively)... <br />
The problem was update of MQT counterparts in Oracle and SQL Server is either difficult/not possible/require special license...<br />
<br />
So the suggestion was extended to use this MQT (and MV and IV in Oracle and SQL Server) to load data in temporary table...<br />
But I discarded it...<br />
You first load 7million records in one table (MQT is essentially a table) and then load a table from this table... As a DBA you are doing your best to screw yourself down the line...<br />
<br />
<br />
So I thought if MQT is also maintained as a Table and DB2 internally populates data when doing a full refresh, so lets try to think how DB2 would be optimizing this process...<br />
<br />
So I thought <br />
<b>1) May be it truncates the table instead of deleting the records...<br />
So i created a stored proc to truncate the table but retain the storage (I can't just use the truncate statement cause I had to support Oracle as well, where syntax is different, BTW anyone has an idea why DB2 mandates the use of IMMEDIATE keyword?)<br />
</b><br />
<i>CREATE OR REPLACE PROCEDURE TESTPROC <br />
( TABLENAME VARCHAR(30), SCHEMANAME VARCHAR(15))<br />
LANGUAGE SQL <br />
BEGIN<br />
DECLARE STATEMENT1 VARCHAR(1024);--<br />
SET STATEMENT1='SET CURRENT_SCHEMA=' || SCHEMANAME; --<br />
EXECUTE IMMEDIATE STATEMENT1;--<br />
<br />
set CNT=1;--<br />
SET STATEMENT1='TRUNCATE TABLE ' || <br />
TRIM(SCHEMANAME) || <br />
'.' || <br />
TRIM(TABLENAME) ||<br />
' REUSE STORAGE IMMEDIATE';--<br />
commit;--<br />
EXECUTE IMMEDIATE STATEMENT1;-- <br />
commit;-- <br />
END;<br />
</i><br />
<br />
<br />
But this still resolved issue of deletion... Inserts were still taking alot of time... So second thought which came to my mind was...<br />
<b>2) May be it will drop the indexes on MQT before populating data(disable index is a requirement which DB2 team should start looking at)<br />
</b><br />
So modified the above stored proc...<br />
<i>CREATE OR REPLACE PROCEDURE TESTPROC <br />
( TABLENAME VARCHAR(30), SCHEMANAME VARCHAR(15), COLUMNLIST VARCHAR(1024), INSERTVAL VARCHAR(1024))<br />
SPECIFIC TESTPROC<br />
LANGUAGE SQL <br />
BEGIN<br />
DECLARE STATEMENT1 VARCHAR(1024);--<br />
DECLARE STATEMENT2 VARCHAR(2048);--<br />
DECLARE INDEX_STMT WASADMIN.VARCHAR_ARR;--<br />
DECLARE CNT INTEGER;--<br />
<br />
SET STATEMENT1='SET CURRENT_SCHEMA=' || SCHEMANAME; --<br />
EXECUTE IMMEDIATE STATEMENT1;--<br />
<br />
<b>-- Truncate the table before loading/refreshing with new data</b><br />
set CNT=1;--<br />
SET STATEMENT1='TRUNCATE TABLE ' || TRIM(SCHEMANAME) || '.' || TRIM(TABLENAME) || ' REUSE STORAGE IMMEDIATE';--<br />
commit;--<br />
EXECUTE IMMEDIATE STATEMENT1;--<br />
<br />
<b>-- Get a copy of index creation statements...</b><br />
<br />
FOR CR1 AS a CURSOR <br />
WITH HOLD <br />
FOR <br />
(SELECT INDNAME FROM SYSCAT.INDEXES WHERE TABNAME=TABLENAME)<br />
DO<br />
SET INDEX_STMT[CNT]='CREATE INDEX ' || <br />
CR1.INDNAME || <br />
' ON ' || <br />
TABLENAME || <br />
'( ';--<br />
<br />
FOR CR2 AS a CURSOR WITH HOLD FOR <br />
(<br />
SELECT I.INDNAME, <br />
IC.COLNAME || <br />
CASE WHEN IC.COLORDER='A' THEN ' ASC ,' ELSE ' DESC ,' END AS COL<br />
, IC.COLSEQ <br />
FROM SYSCAT.INDEXES I JOIN SYSCAT.INDEXCOLUSE IC ON I.INDNAME=IC.INDNAME <br />
AND I.INDSCHEMA=IC.INDSCHEMA<br />
AND I.INDNAME=CR1.INDNAME <br />
AND I.TABNAME=TABLENAME<br />
ORDER BY IC.COLSEQ<br />
)<br />
DO<br />
SET INDEX_STMT[CNT]=INDEX_STMT[CNT] || CR2.COL ;--<br />
END FOR;--<br />
<br />
set INDEX_STMT[CNT]=SUBSTR(INDEX_STMT[CNT],1,length(INDEX_STMT[CNT])-1);--<br />
<br />
set INDEX_STMT[CNT]=INDEX_STMT[CNT] || ')';--<br />
<br />
SET CNT=CNT+1;--<br />
<br />
<b>-- Drop the index once its definition is stored...</b><br />
<br />
set STATEMENT1='drop index ' || CR1.INDNAME;--<br />
EXECUTE IMMEDIATE STATEMENT1;--<br />
commit;--<br />
END FOR;--<br />
<br />
<br />
<b>-- Once definition is captured for all indexes (and they are dropped) insert the data in the table using the <br />
-- Column list provided in the arguement<br />
-- and values <br />
--(which can be VALUES (ValueFor_Column1, Val_ForColumn2, .... Val_forColumnN) <br />
--or can be a select stmt<br />
</b><br />
set STATEMENT2='INSERT INTO ' <br />
|| trim(SCHEMANAME) ||<br />
'.' || trim(TABLENAME) || <br />
' ( ' || COLUMNLIST || <br />
' ) ' || <br />
INSERTVAL;--<br />
EXECUTE IMMEDIATE STATEMENT2;--<br />
commit;--<br />
<br />
<b>-- Now re-create all the indexes...</b><br />
FOR CR3 as a CURSOR <br />
WITH HOLD <br />
for <br />
( SELECT T.STMT FROM UNNEST(INDEX_STMT) WITH ORDINALITY AS T(STMT,ID))<br />
DO<br />
EXECUTE IMMEDIATE STMT;--<br />
END FOR;--<br />
END;</i><br />
<br />
<br />
<br />
I could have used files to store the index creation instead of variable, but i wanted to make it generic so that it works even when new indexes are added and wanted to avoid I/O.<br />
<br />
<br />
Now a tip to ensure that for such tables, when cardinality shoots high during a bulk insert, you can mark them as VOLATILE by<br />
<br />
"ALTER TABLE TABLNAME VOLATILE"<br />
<br />
Further performance gains can be achieved if you are ready to compromise on recovery of the table being loaded. In that case you can mark the table not logged just before the insert:<br />
<br />
"ALTER TABLE TABLENAME NOT LOGGED INITIALLY"<br />
<br />
If anyone can help me figure out what other optimizations are done by MQT for refreshing a table, I can try to write a piece of code for that as well...<br />
<br />
Keep optimizing...Sameerhttp://www.blogger.com/profile/04380280581835257131noreply@blogger.com4tag:blogger.com,1999:blog-1347137045980388492.post-65953181541020709672012-03-14T11:16:00.002-07:002012-03-14T11:34:27.800-07:00Virtual Indexes: My 2nd Chance in DB2 Has talent 2012I will share the story of my 2nd chance at DB2 has talent... A better experience and managed the time well... But everyone was at their best in that episode and for a moment I thought have a got a wildcard entry in final round of DB2 has talent...
Lot of people discussed the interested topics and a few discussed some features/tools which are not well documented... I too had chosen a topic which is not much discussed: "Virtual Indexes"... A way by which you can evaluate your indexes without even creating those...
So let me start with it (and I will keep a check on my typos)...
</br></br>
</br></br>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiv4uLfWpzA2JMemYgunckdFtX4APRvExYHYIpzUr9vbP_zUfnEzXa-j_FwyvKhkmKA52QEInZzATYUCSWTXPwUOp7trLOVfrNDvYE-pAsDpwIDX7dNQCwfY_YI7ku68x2Ooae8gYtPbnM/s1600/Slide1.JPG" imageanchor="1" style="clear:left; float:center;margin-right:1em; margin-bottom:1em"><img border="0" height="300" width="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiv4uLfWpzA2JMemYgunckdFtX4APRvExYHYIpzUr9vbP_zUfnEzXa-j_FwyvKhkmKA52QEInZzATYUCSWTXPwUOp7trLOVfrNDvYE-pAsDpwIDX7dNQCwfY_YI7ku68x2Ooae8gYtPbnM/s400/Slide1.JPG" /></a></div>
</br></br>
So how many times you have faced an issue of poor performing queries and you feel probably adding an index will help improve your queries performance... But then you realize one index will not be sufficient for all the queries and you are wondering which columns to be combined for indexing... The permutation combination is generally good enough for you to not try actual creation of indexes as that will take some time and if it is not good you need to drop and start over...
</br></br>
</br></br>
So I made up a story... I had a table design, pretty simple one...
</br></br>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjkO0fRoqT0dSU_R2J0UgHclFcLrElGMlujmOlgb7-9x7Grdziuuyemwm3vBdK1LDRhjFrHrZ9MhHnQXkieRfHTbCBay1isoXbLrHBiIGrSXVDTLIK5YNk_nb1ETlsL7VBkbzjzmVqBnzk/s1600/Slide2.JPG" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="300" width="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjkO0fRoqT0dSU_R2J0UgHclFcLrElGMlujmOlgb7-9x7Grdziuuyemwm3vBdK1LDRhjFrHrZ9MhHnQXkieRfHTbCBay1isoXbLrHBiIGrSXVDTLIK5YNk_nb1ETlsL7VBkbzjzmVqBnzk/s400/Slide2.JPG" /></a></div>
</br></br>
Three tables, two of those mapped to each other in the third table by their key columns... The mapping table has a self referencing relationship of parent-child...
</br></br>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiAMkavKgmuuY6yC7aOYkMgdNMFjNygkDVAOF8puv8uju3-8iox-JGRz6mIhQYEev_LfEt_DuG8d9BqpPK9HZsWull5untvPR9yKjtNUt407nDrxar_RAm-q0MMiunQuIhbRLENWglUGFU/s1600/Slide3.JPG" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="300" width="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiAMkavKgmuuY6yC7aOYkMgdNMFjNygkDVAOF8puv8uju3-8iox-JGRz6mIhQYEev_LfEt_DuG8d9BqpPK9HZsWull5untvPR9yKjtNUt407nDrxar_RAm-q0MMiunQuIhbRLENWglUGFU/s400/Slide3.JPG" /></a></div>
</br></br>
I had a query which was being executed on the tables, again a very simple query... Join between 2 tables through the third table and then the third table was also involved in a self-join...
But still the query was taking a lot of time to be executed...
I guessed probably the indexes are missing and though of using index Adviser...
</br></br>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhFRP3FibAbK0OWEBwe6ISXRZ2l3S-8uuey12drtgEQiMDmuBlAIGPkmxTuuy9P09qFcMGn9OmLMGrE0zlfuvOkUOHmLJn8qC91yCmMKER5RqmMikfAxCk5iG8WyimQCfCFOnbfHfTFWlk/s1600/Slide4.JPG" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="300" width="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhFRP3FibAbK0OWEBwe6ISXRZ2l3S-8uuey12drtgEQiMDmuBlAIGPkmxTuuy9P09qFcMGn9OmLMGrE0zlfuvOkUOHmLJn8qC91yCmMKER5RqmMikfAxCk5iG8WyimQCfCFOnbfHfTFWlk/s400/Slide4.JPG" /></a></div>
</br></br>
I could have achieved the same by setting my current explain mode=advise indexes and then run the queries and see what are the new indexes advised in ADVISE_INDEX table... But I was surprised to see the result in the table...
</br></br>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhTAtwfwxZQ69zcODemawpqKCOR04THUQIRLXxfwoDSVEyNVq2k92IR9B_O3ln0V9YZBVUAJbFnxKyE_W-0TwJgIIA00y6kgrUSEBSiwQHeoR35VlVk_pGFpeVwrT77fLO8dW2g8ldZdCk/s1600/Slide5.JPG" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="300" width="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhTAtwfwxZQ69zcODemawpqKCOR04THUQIRLXxfwoDSVEyNVq2k92IR9B_O3ln0V9YZBVUAJbFnxKyE_W-0TwJgIIA00y6kgrUSEBSiwQHeoR35VlVk_pGFpeVwrT77fLO8dW2g8ldZdCk/s400/Slide5.JPG" /></a></div>
</br></br>
I was confused, every other column in my select list or in my where clause made a place in one or the other advised index...
</br></br>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhMgrHLDtIIsEvBicypYyfJIwj79VXfyfxl2IAnHw46_PJ8tNz7Sz8yaYar-J-yIsChwKF2v25wzI2yu4v0caLnJMCL8xyLQqpB-rnf4p1NVIgXsYZpd7IHVXxiRWULZFARjI7D7JJeolo/s1600/Slide6.JPG" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="300" width="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhMgrHLDtIIsEvBicypYyfJIwj79VXfyfxl2IAnHw46_PJ8tNz7Sz8yaYar-J-yIsChwKF2v25wzI2yu4v0caLnJMCL8xyLQqpB-rnf4p1NVIgXsYZpd7IHVXxiRWULZFARjI7D7JJeolo/s400/Slide6.JPG" /></a></div>
</br></br>
I was not able to figure out which index, I shall use and which one I shall ignore... The major problem was creating all these indexes will slow down performance for my CRUD operations... A dba won't have all the time in this world to try out all the indexes in every possible permutation and go into the loop of "create index"-"generate plan"-"check usage"-"drop index"-"repeat"...
</br></br>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgDFC5u0TuHMYJrHh8lX00CGCrnlHj4Mu6dZHiQ13d1b5jzCpEGRy5uMdBrYbZrubyAlZ2c9tuOC-E2t6pT53eaTeZCEWEGZH-P08CERBRPdHgsOp2TivwGR98tbVj3yOBnKwVoX2_CJWU/s1600/Slide7.JPG" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="300" width="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgDFC5u0TuHMYJrHh8lX00CGCrnlHj4Mu6dZHiQ13d1b5jzCpEGRy5uMdBrYbZrubyAlZ2c9tuOC-E2t6pT53eaTeZCEWEGZH-P08CERBRPdHgsOp2TivwGR98tbVj3yOBnKwVoX2_CJWU/s400/Slide7.JPG" /></a></div>
</br></br>
I was wondering if there is a way to force optimizer to assume as if an index is there and then generate a plan using the index if possible... Then I came across an option in db2advis and a value for "current explain mode"...
</br></br>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgsHhwta429LIr98KLNCX0Qnmy6eD6VMyqFCDlHwsWXRYEBXFk-eKszkSaOPAhyphenhyphenZTwmN6XHf3Ub-pCRbIf4QAn9nMrWnBA8xS_1SFepkRknrk9vRx5WpDKSABmOh92a0cJKY9O7T52k_1s/s1600/Slide8.JPG" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="300" width="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgsHhwta429LIr98KLNCX0Qnmy6eD6VMyqFCDlHwsWXRYEBXFk-eKszkSaOPAhyphenhyphenZTwmN6XHf3Ub-pCRbIf4QAn9nMrWnBA8xS_1SFepkRknrk9vRx5WpDKSABmOh92a0cJKY9O7T52k_1s/s400/Slide8.JPG" /></a></div>
</br></br>
Once you have got the index suggestions, you can set your current explain mode to evaluate indexes and then run the queries which you think will be benefited by suggested indexes... then use db2exfmt to format the explain info...
Alternatively using -p option in db2advis you can force the adviser to store explain info for the suggested changes... Then use db2exfmt you can generated the explain plan and see if suggested indexes are used or not...
But I was not satisfied with the plan generated for any of those suggestions, specially when I ran the update queries for those tables in "evaluate indexes" as current explain mode, I was more convinced not to create all of them...
</br></br>
I took a backup of suggested indexes and then deleted the rows from ADVISE_INDEX and repopulated them from backup in various permutations to check which combination is most effective but none of them were as convincing... So I decided to insert my own rows in ADVISE_INDEX indexes i thought will benefit the select queries without having much impact on updates and inserts...
</br></br>
Something which I could not discuss during the presentation is I had a some challenges in trying out my own indexes, cause initially the optimizer ignored the indexes I imagined (or virtually created)... Then I had to work out the proper values for NLEAFS i.e. no of leaf nodes for your index tree (roughly no of distinct values in your column combination), NLEVELS means no of level in your index tree (is one of most difficult parameters to decide), FULLKEYCARD i.e. distinct key values in your index (a little easy compared to other a select distinct should do), FIRSTKEYCARD/FIRST2KEYCARD/FIRST3KEYCARD are for cardinality in first column, 2nd column and 3rd column in your index (again a little easy)... NEVELS can be decided by equation 2^x=no of distinct values where x is nlevels... SYSSTAT views can help you in deciding these parameters... COLCOUNT defines the no of column in the index...COLNAMES will define the columns which are part of index... INDEXTYPE (CLUS/REG/DIM/BLOK ) defines the type of index...
</br></br>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjD5I0Ja3ieOIK998k91MsKlpOH2i2Kk_arP4_-swEje203M45DbuKDKxS1AMwD4AqNoj4cGML_aNPo3Jld1_t6Kt0Y7igiUeSq4ZfwsUJf3YMqcvdOrsdKJHzjC5GiQrJPeR22uscdVtY/s1600/Slide9.JPG" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="300" width="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjD5I0Ja3ieOIK998k91MsKlpOH2i2Kk_arP4_-swEje203M45DbuKDKxS1AMwD4AqNoj4cGML_aNPo3Jld1_t6Kt0Y7igiUeSq4ZfwsUJf3YMqcvdOrsdKJHzjC5GiQrJPeR22uscdVtY/s400/Slide9.JPG" /></a></div>
</br></br>
Finally with a little struggle and little time I could decide the best indexes for my tables... On exploring more about this feature I realized that I can virtually drop an index and check how the query performance is impacted before I actually drop it... All I have to do is insert a row for an existing index USE_INDEX column set to āNā and EXISTS as āYā...
</br>
I wrapped up with putting a slide for advantages of this (I will just put the slide here and will not be explaining)...
</br></br>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgJXUF1_joUOhghMA0guSmEFtFJmwJZj9KsaXsrrMnTQqZkq_Hg3Yy5NrxPV8h9vdIjsOm6Eknz4ni-6sPG8ruTZkx-ly809GIx5Gt9dHERnDYpGwk3tMEswTGMoHUdLKOAPaJsRaP3SaY/s1600/Slide10.JPG" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="300" width="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgJXUF1_joUOhghMA0guSmEFtFJmwJZj9KsaXsrrMnTQqZkq_Hg3Yy5NrxPV8h9vdIjsOm6Eknz4ni-6sPG8ruTZkx-ly809GIx5Gt9dHERnDYpGwk3tMEswTGMoHUdLKOAPaJsRaP3SaY/s400/Slide10.JPG" /></a></div>
</br></br>
To end this blog I will just mention that my DB2 z/OS folks can also use the same feature <a href="http://ibmsystemsmag.blogs.com/db2utor/2009/12/virtual-index-cost-analysis.html">DSN_VIRTUAL_INDEXES
</a>... Oracle user can also try the feature of Virtual indexes by using <a href="http://www.oracle-base.com/articles/misc/VirtualIndexes.php">NOSEGMENT clause in index creation</a>...
I will sign off this blog entry... Might take a few days to return, I have to enhance my skills on HADR and DB2 Internals (process and memory model)... Might have a short blog for insert enhancements and piece of code that you can probably re-use...Sameerhttp://www.blogger.com/profile/04380280581835257131noreply@blogger.com6tag:blogger.com,1999:blog-1347137045980388492.post-47897457523558577302012-02-29T09:10:00.000-08:002012-03-14T11:34:56.782-07:00My Experience @ DB2 Has Talent: DB Design BasicsHi people, I recently participated in DB2 has talent and it is still going on [now am out of it :( ]...</br>
Make a point to <a href="http://www.dbisoftware.com/db2nightshow/db2sgottalent.php">register</a> for rest of the episodes of this, I assure you of great DB2 tips...
</br>
I will be sharing my first presentation for the show!!! Which was about "db physical design"... I will avoid putting much text here, as this is more or less based on my blog series "Optimize your Tablespaces and Bufferpools"... You can check them here
</br>
</br>
<a href="http://db2performance.blogspot.in/2011/07/optimize-your-tablespaces-and.html">Optimize your Tablespaces and Bufferpools-1: Tablespace Design design
</a>
</br>
</br>
<a href="http://db2performance.blogspot.in/2011/07/optimize-your-tablespaces-and_24.html">Optimize your Tablespaces and Bufferpools-2: Tablespace Sizing
</a>
</br>
</br>
<a href="http://db2performance.blogspot.in/2011/10/optimize-your-tablespaces-and.html">Optimize your Tablespaces and Bufferpools-3: Bufferpool Optimization
</a>
</br>
</br>
I could not complete the presentation for obvious reasons (it was a bad choice, a topic you have yourselves spread over 3 blogs)...
Additionally there were too many typos (which I do quite often in my blogs as well)...
You can read <a href="https://www.ibm.com/developerworks/mydeveloperworks/blogs/SusanVisser/entry/session_4_of_the_db2_s_got_talent_competition1?lang=en_us">Susan's blog</a> to get more specific presentation tips!!!
I will share the slides I had used for presentation...
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjQ4dS8EHlfljw03V0tV5ptF4-QHFyJuXC36aaPQwIHz2aBiOuocneQVcWHfk8sMj_0Hfe6qkSlo4v6le4J3lLkDytp_863m33mwj6t-lBsvVR8H4XhpOddb2tLkr0bPoXXM7AdbBonA70/s1600/Slide01.jpg" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="300" width="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjQ4dS8EHlfljw03V0tV5ptF4-QHFyJuXC36aaPQwIHz2aBiOuocneQVcWHfk8sMj_0Hfe6qkSlo4v6le4J3lLkDytp_863m33mwj6t-lBsvVR8H4XhpOddb2tLkr0bPoXXM7AdbBonA70/s400/Slide01.jpg" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjBKxZqLkIjci6FbmDXWoyNifRNFJLoc-vhwji4jGGb1QsOPMBwmF6QSpSlGyMGeLcVxN33VJDelTs7KPRWtpZuoxKux5gLZuD0plSU5A_tkM4YiXnGp3KlUW9xvximbW3TMuPVtyAbifM/s1600/Slide02.jpg" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="300" width="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjBKxZqLkIjci6FbmDXWoyNifRNFJLoc-vhwji4jGGb1QsOPMBwmF6QSpSlGyMGeLcVxN33VJDelTs7KPRWtpZuoxKux5gLZuD0plSU5A_tkM4YiXnGp3KlUW9xvximbW3TMuPVtyAbifM/s400/Slide02.jpg" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiAhA6XfA45OxjCucvqJeQM0EqXTOIywUBsjumN09iIV1sCx-m9w_iYDzo5b4h4pMxAoh_Cw9a9SorOvgEU6T5rYBGw5gohkSUeYuXjBzcFNM6PUSizQXovwTj6d-ZiwnEUOv2vDUfG3qg/s1600/Slide03.jpg" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="300" width="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiAhA6XfA45OxjCucvqJeQM0EqXTOIywUBsjumN09iIV1sCx-m9w_iYDzo5b4h4pMxAoh_Cw9a9SorOvgEU6T5rYBGw5gohkSUeYuXjBzcFNM6PUSizQXovwTj6d-ZiwnEUOv2vDUfG3qg/s400/Slide03.jpg" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiqVRlQTeTDEAOoj5jXq7wAVTyW5xccp3nauCF2SgofpTq9dGTGKDAgZxz3bVb4sMJb_deBhY3AIL575Q4fA4rzbp3gxLr2QPGfWBl81ERvrUxwhYaXFGBgxwpVBUf59xJBQOXzpppFxE0/s1600/Slide04.jpg" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="300" width="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiqVRlQTeTDEAOoj5jXq7wAVTyW5xccp3nauCF2SgofpTq9dGTGKDAgZxz3bVb4sMJb_deBhY3AIL575Q4fA4rzbp3gxLr2QPGfWBl81ERvrUxwhYaXFGBgxwpVBUf59xJBQOXzpppFxE0/s400/Slide04.jpg" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhz9vEhIwhCfLNzdlUlG4PyaC3oSuYMS3Lo78QTLdBMYcLLj9SqgZcJwlJhEa1IXdHAHLbU0_-VojIEdcAWHWvYsHPxyjy7qp3oydHvFdfV80pSVLUe3bCqi02j-PrU8Fpin0EeWNPN10E/s1600/Slide05.jpg" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="300" width="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhz9vEhIwhCfLNzdlUlG4PyaC3oSuYMS3Lo78QTLdBMYcLLj9SqgZcJwlJhEa1IXdHAHLbU0_-VojIEdcAWHWvYsHPxyjy7qp3oydHvFdfV80pSVLUe3bCqi02j-PrU8Fpin0EeWNPN10E/s400/Slide05.jpg" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgTrmiDg6DOtHnUwOKr_zq8GoHTjOHdsQiK9kYkAf7RXzOuuBYvZpvKK0aBDNbSVpyeie9VxYjoga_3DM22MBus9oe5O-2HHYihF4Lr9NrycPHor0HppSTI3Vy-yE7T-JbDlhjgdA5Em1M/s1600/Slide06.jpg" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="300" width="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgTrmiDg6DOtHnUwOKr_zq8GoHTjOHdsQiK9kYkAf7RXzOuuBYvZpvKK0aBDNbSVpyeie9VxYjoga_3DM22MBus9oe5O-2HHYihF4Lr9NrycPHor0HppSTI3Vy-yE7T-JbDlhjgdA5Em1M/s400/Slide06.jpg" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiwQ-5nyP9ggbQ3TSqt0N8BoHD9D_MUUg__JVioVBCDPTCTECvd7opk5pEMadxPHLN9lux8HAT27Z7v4g2n1zOld7rPNqMA-Y02GlHP0xJqDK296xgmNGwd6i1aR-SYUdVa7vEGkLT0Sz0/s1600/Slide07.jpg" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="300" width="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiwQ-5nyP9ggbQ3TSqt0N8BoHD9D_MUUg__JVioVBCDPTCTECvd7opk5pEMadxPHLN9lux8HAT27Z7v4g2n1zOld7rPNqMA-Y02GlHP0xJqDK296xgmNGwd6i1aR-SYUdVa7vEGkLT0Sz0/s400/Slide07.jpg" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjubfTPtLjBFY3TIfUw2RJTRIv33k0lDL_uzZIsdMgF1ccC5cjNjl9wju0TB14eMh_GiWfM-u-HGZIS64qplZJqxSZ0SderIgCW2HE-8BRLqQU-9EpQj37PEzj6o_jnLG8GJP0Bm_IaVkc/s1600/Slide08.jpg" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="300" width="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjubfTPtLjBFY3TIfUw2RJTRIv33k0lDL_uzZIsdMgF1ccC5cjNjl9wju0TB14eMh_GiWfM-u-HGZIS64qplZJqxSZ0SderIgCW2HE-8BRLqQU-9EpQj37PEzj6o_jnLG8GJP0Bm_IaVkc/s400/Slide08.jpg" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgKWHbY9NUIeB_ZtrlYTWsoIUEqkPEHHWISlBLdFRPviVNzZzANJFpHJ85suXlaG59UCuoY8xDXWM5D7gLVOcT56nM8GfDHbxetDG0Trpk3-T8qIX2b-y38wuf6zPJhHv0FYx4VJMkkO1A/s1600/Slide09.jpg" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="300" width="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgKWHbY9NUIeB_ZtrlYTWsoIUEqkPEHHWISlBLdFRPviVNzZzANJFpHJ85suXlaG59UCuoY8xDXWM5D7gLVOcT56nM8GfDHbxetDG0Trpk3-T8qIX2b-y38wuf6zPJhHv0FYx4VJMkkO1A/s400/Slide09.jpg" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiJesejRD5SuYC8uY275_6EglGli2DgEMPEPfT5HD4oYdUsOnkDq6r1OzarlXMB3fMMMXxWTfsjI95nwra_ISHqrdlK8aZ-JAcnXuSJMR0bH1wZYKQo9ejo78KWLRlmkWuj94y7mRMBxYQ/s1600/Slide10.jpg" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="300" width="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiJesejRD5SuYC8uY275_6EglGli2DgEMPEPfT5HD4oYdUsOnkDq6r1OzarlXMB3fMMMXxWTfsjI95nwra_ISHqrdlK8aZ-JAcnXuSJMR0bH1wZYKQo9ejo78KWLRlmkWuj94y7mRMBxYQ/s400/Slide10.jpg" /></a></div>Sameerhttp://www.blogger.com/profile/04380280581835257131noreply@blogger.com0tag:blogger.com,1999:blog-1347137045980388492.post-46173080259147557132012-01-10T10:22:00.000-08:002012-01-10T10:38:55.072-08:00Be critical of what you did n be Logical to what you gonna doThis is going to be an offbeat post!!! You might not find a very good technical tip in this but I hope this will help you improve your attitude towards performance tuning...<br />
<br />
Nothing n trust me nothing can subsidize a poorly designed application... <br />
<br />
I will just discuss some basic mistakes which people commit, which should be revisited:<br />
<br />
<b>1) indexing <br />
</b>If you have not indexed properly you should not expect too much out of a processor even if it is say 10GHz!!! You have to live with skewed I/O<br />
<br />
<b>2) Placement of Tables</b><br />
If you have not segregated your tables as per their nature and volume of data, using too many container is not going to help you<br />
<br />
<b>3) Level of Normalization </b><br />
Some redundancy won't ruin your application esp if the data is write once, read always... I know this is going to be debated...<br />
I heard someone saying "It is better to have something which works rather than something Architecturally correct and does not work!!!"<br />
<br />
<b>4) access queries </b><br />
do you expect a magical h/w solution to improve performance of a query "select * from my_Blogs where upper(Title) like '%CRITICAL%LOGICAL%' "<br />
No hardware can help you scale when actual problem is with your Application and Database design...<br />
<br />
<b>5) Designing at a per-mature phase in Development Life cycle</b><br />
An even more basic problem... You did not care to understand business, and screwed up the db design and now you have to fire a count(*) to get count of records for a blog reads from a given ACCOUNT, if you knew business may be you had planned this better...<br />
<br />
<b>6)improper molding of business scenarios in your architecture </b><br />
Business defines requirements and not other way round... As a DBA understand business and it is absolutely normal to question business requirements...<br />
My user requirement might ask me for a new functionality, but I need to see how it fits in my current solution and its architecture...<br />
Just do not try to solve a problem with an advanced feature, which could have been solved in a much simpler way... For Instance many people think that Partitioning or MDC (on a column which has even distribution of data) is gonna help them get an awesome performance for tables which has huge volume of data...<br />
<br />
<b>7)Get out of conventional notions...</b><br />
I have seen well experienced people thinking that Stored Procs will perform better compared to a prepared statement wrapped in a java code... or application caching will always help... These are myth... Stored procs are definitely preferable in certain instances, but not always... Putting too much load on application cache leaves too less memory with your application for itself and for any data which is not cached and will be read online...<br />
<br />
<br />
Please guys, these were just examples and I do not want people to carry the notion that I have committed these blunders...<br />
<br />
I really pity people who think that performance is more of an infrastructure and hardware issue... Or going for some advanced features of a s/w will help them... If you are suggesting these to your clients, BEWARE your client will curse you first for providing a bad solution and second for increasing its IT cost to support a poorly designed application...<br />
<br />
I hope this post will help think more rationally... So when you hit a performance bottleneck, be critical of what you have already developed and be logical about what solution you are going to propose...Sameerhttp://www.blogger.com/profile/04380280581835257131noreply@blogger.com0tag:blogger.com,1999:blog-1347137045980388492.post-39719522408541615642011-12-24T10:28:00.000-08:002011-12-24T10:28:18.247-08:00MERGE: MAKE Your UPSERTs quick!!!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...<br />
<br />
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>I need to tell people there is something like MERGE</i>"... 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...<br />
<br />
Let's see some example...<br />
<br />
<i>UPDATE Table1 <br />
SET T1Col1=(select T2Col1 from Tables2 where Table1.T1IDPK=T2IDPK);</i><br />
<br />
So the problem with this query is the inner query will be executed for every row of table Table1...<br />
<br />
So I wrote them a query MERGE, which shall first create a HASH JOIN and then do an RID based update:<br />
<br />
<i>MERGE INTO Table1<br />
USING Table2 on (Table1.T1IDPK=Table2.T2IDPK)<br />
WHEN MATCHED THEN UPDATE<br />
SET Table1.T1Col1=Table2.T2Col1;</i><br />
<br />
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...<br />
<br />
<i>UPDATE PagedTab t1<br />
set SEQNUM=(select row_number() over(order by PagedTab_IDPKColumn) from PagedTab t2 where t1.PagedTab_IDPKColumn=t2.PagedTab_IDPKColumn);</i><br />
<br />
So I just changed it to below:<br />
<i><br />
MERGE INTO PagedTab t1<br />
USING (select PagedTab_IDPKColumn,row_number() over(order by PagedTab_IDPKColumn) as rnum from PagedTab ) t2<br />
on t1.PagedTab_IDPKColumn=t2.PagedTab_IDPKColumn<br />
WHEN MATCHED THEN UPDATE<br />
SET t1.SEQNUM=t2.rnum;</i><br />
<br />
I have simulated the below scenrio to make it more easy to be understood as the actual was more complex...<br />
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...<br />
<br />
<i>UPDATE MasterTable outTab<br />
SET STATUS=<br />
(select STATUS from DetailsTable t2 join<br />
(select ACCOUNTID, MAX(LASTACCESSDATE) DT from DetailTable group by ACCOUNTID)t1<br />
on t1.ACCOUNTID=t2.ACCOUNTID and t2.LASTACCESSDATE=t1.DT and t2.IDPK=outTab.IDPK<br />
)temp;</i><br />
<br />
<br />
Actually the query which was written had used one more IN clause which I have converted to JOIN to avoid more confusion...<br />
<br />
I modified it...<br />
<br />
<i>MERGE into MasterTable t1<br />
USING <br />
(select IDPK, DENSE_RANK() over(PARTITION BY ACCOUNTID order by LASTACCESSDATE) rn, STATUS from DetailsTable) t2<br />
ON (t1.IDPK=t2.IDPK and rn=1)<br />
WHEN MATCHED THEN UPDATE<br />
SET STATUS=t2.STATUS;</i><br />
<br />
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!!!<br />
<br />
One final instance where I saw something like this:<br />
<br />
<i>UPDATE TableX X<br />
SET <br />
Col1=(select Col1 from TableY Y where X.IDPK=Y.IDPK),<br />
Col2=(select Col2 from TableY Y where X.IDPK=Y.IDPK),<br />
Col3=(select Col3 from TableY Y where X.IDPK=Y.IDPK),<br />
Col4=(select Col4 from TableY Y where X.IDPK=Y.IDPK)<br />
;</i><br />
<br />
and then<br />
<br />
<i>INSERT INTO TableX (COl1, COl2, COl3, COl4)<br />
Select COl1, COl2, COl3, Col4 from TableY Y<br />
where not exists (select 1 from TableX X where X.IDPK=Y.IDPK);<br />
</i><br />
<br />
and finally...<br />
<br />
<i>DELETE FROM TableX X where not exists (select 1 from TableY where X.IDPK=Y.IDPK);<br />
</i><br />
I will leave it upto the readers to interpret the purpose of these statements... I will just give an alternate query...<br />
<br />
<i><br />
MERGE INTO TableX X<br />
USING TableY Y on (X.IDPK=Y.IDPK)<br />
WHEN MATCHED then UPDATE<br />
SET <br />
X.COl1=Y.COl1,<br />
X.COl2=Y.COl2,<br />
X.COl3=Y.COl3,<br />
X.COl4=Y.COl4<br />
WHEN NOT MATCHED THEN INSERT(COl1, COl2, COl3, COl4)<br />
VALUES (Y.COl1, Y.COl2, Y.COl3, Y.COl4)<br />
;</i><br />
<br />
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...<br />
<br />
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:<br />
<br />
<br />
<i>MERGE INTO TableX X<br />
USING TableY Y on (X.IDPK=Y.IDPK)<br />
WHEN MATCHED then UPDATE<br />
SET <br />
X.COl1=Y.COl1,<br />
X.COl2=Y.COl2,<br />
X.COl3=Y.COl3,<br />
X.COl4=Y.COl4<br />
WHEN NOT MATCHED THEN INSERT(COl1, COl2, COl3, COl4)<br />
VALUES (Y.COl1, Y.COl2, Y.COl3, Y.COl4)<br />
<b>WHEN NOT MATCHED BY SOURCE THEN DELETE</b><br />
;<br />
</i><br />
<br />
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...<br />
<br />
BTW thanks to all my readers... I feel lucky and honored to be listed in "My Favorite DB2 Blogs" by Troy Coleman @ <a href="http://ibmsystemsmag.blogs.com/db2utor/2011/12/my-favorite-db2-blogs.html">db2tutor</a> <br />
<br />
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...Sameerhttp://www.blogger.com/profile/04380280581835257131noreply@blogger.com12tag:blogger.com,1999:blog-1347137045980388492.post-34769459158412339302011-12-10T09:29:00.000-08:002011-12-10T09:40:29.350-08:00SQL Anti Logic: Usage of CASE StatementHello 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 <a href="http://db2performance.blogspot.com/2011/04/sql-anti-logic.html">"Anti SQL Logic"</a><br />
<br />
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...<br />
<br />
Probably a naive approach that come first in mind i below:<br />
<br />
1) I get a union of all the possible combinations for previous period and current period. Below is basic query for that...<br />
<br />
<i><br />
SELECT AST.* FROM {?schemaName}.ACCSTATSTABLE AST, {?schemaName}.ACCOUNTTABLE ACC <br />
WHERE ACC.ACCOUNTNUMBER = AST.ACCOUNTNUMBER <br />
AND ACC.ACCOUNTNUMBER = '{?inputACCOUNTNUMBER}' <br />
AND 'Q' = (SELECT VALUE FROM CONFIGTABLE WHERE CONFIGNAME='StatsPeriod' ) <br />
AND {?FORMONTH} > (AST.STATSPERIOD - 1) * 3 +1 //for current period<br />
--AND {?FORMONTH} <= (AST.STATSPERIOD-1) * 3 //for previous period
AND AST.FORYEAR = '{?FORYEAR}' </i><br />
<br />
<i>UNION</i><br />
<br />
<i>SELECT AST.* FROM {?schemaName}.ACCSTATSTABLE AST, {?schemaName}.ACCOUNTTABLE ACC<br />
WHERE ACC.ACCOUNTNUMBER = AST.ACCOUNTNUMBER<br />
AND ACC.ACCOUNTNUMBER = '{?inputACCOUNTNUMBER}'<br />
AND 'H' = (SELECT VALUE FROM CONFIGTABLE WHERE CONFIGNAME='StatsPeriod' )<br />
AND {?FORMONTH} > (AST.STATSPERIOD - 1) * 6 +1 //for current period<br />
AND {?FORMONTH} <= AST.STATSPERIOD * 6 // for previous period
AND AST.FORYEAR = {?FORYEAR} </i><br />
<br />
<i>UNION</i><br />
<br />
<i> SELECT AST.* FROM {?schemaName}.ACCSTATSTABLE AST, {?schemaName}.ACCOUNTTABLE ACC<br />
WHERE ACC.ACCOUNTNUMBER = AST.ACCOUNTNUMBER<br />
AND ACC.ACCOUNTNUMBER = '{?inputACCOUNTNUMBER}'<br />
AND 'Y' = (SELECT VALUE FROM CONFIGTABLE WHERE CONFIGNAME='StatsPeriod' )<br />
AND AST.FORYEAR ={?FORYEAR} //for curr period<br />
-- AND AST.FORYEST={?FORYEAR}-1 //for prev period</i><br />
<br />
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 )<br />
<br />
3) I join these results sets<br />
<br />
4) Use an inner query to get what is the configured period code and then I get stats only for that period code...<br />
<br />
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)...<br />
<br />
Well I wrote a Structured Query to get what was required making the best I can make of CASE:<br />
<br />
<b>1) Select ALL the columns for statitics twice</b><br />
i) Once actual value if it your period value (e.g. 4th quarter) else select it as 0<br />
ii) Select stats column as 0 if it matches your period value else select actual value<br />
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.<br />
<br />
<b>2) now you need to filter the current period and previous data<br />
i) Filtering current period data is pretty easy as you know the period number<br />
ii) For filtering the period number for previous period I used below case logic</b><br />
<br />
<i>(CASE 'M'<br />
WHEN 'M' THEN 2<br />
WHEN 'Q' THEN (2-1/3)+1 <br />
WHEN 'H' THEN (2-1/6)+1<br />
WHEN 'Y' THEN AST.STATSPERIOD<br />
END) in<br />
( {PeriodNumber}, //Current Period number<br />
(CASE {?FORMONTH} WHEN 1 THEN //Logic to arrive at previous period<br />
case {PERIODCODE} when 'M' THEN 12 //if monthly then prev month is 12<br />
WHEN 'Q' THEN 4 //if quarterly then prev quarter is 3<br />
WHEN 'H' THEN 2 //if halfyearly then prev period is 2<br />
ELSE {PeriodNumber} //if yearly, prev period number does not matter<br />
END<br />
ELSE {PeriodNumber}-1 end) //finally it should be input period number-1 <br />
)<br />
AND AST.FORYEAR IN( {?FORYEAR},<br />
(CASE {?PERIODCODE} WHEN 'Y' THEN {?FORYEAR}-1<br />
else <br />
(case when {PeriodNumber}=1 then {?FORYEAR}-1 <br />
//If my input number is 1 then I need to consider prev year stats as well<br />
ELSE {?FORYEAR} END)<br />
END)<br />
</i><br />
<b>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</b><br />
<i>SELECT <br />
SUM(temp.CURR_MAXIMUM_BALANCE),<br />
SUM(temp.CURR_MINIMUM_BALANCE),<br />
SUM(temp.CURR_AVG_BALANCE),<br />
SUM(temp.CURR_CREDIT_COUNTER ),<br />
SUM(temp.CURR_INTERESTPAID),<br />
SUM(temp.CURR_CHARGES),<br />
SUM(temp.PREV_MAXIMUM_BALANCE),<br />
SUM(temp.PREV_MINIMUM_BALANCE),<br />
SUM(temp.PREV_AVG_BALANCE),<br />
SUM(temp.PREV_CREDIT_COUNTER ),<br />
SUM(temp.PREV_INTERESTPAID),<br />
SUM(temp.PREV_CHARGES <br />
from<br />
(select <br />
case when AST.STATSPERIOD=2 then MAXIMUM_BALANCE else 0 END as CURR_MAXIMUM_BALANCE,<br />
case when AST.STATSPERIOD=2 then MINIMUM_BALANCE else 0 END as CURR_MAXIMUM_BALANCE,<br />
case when AST.STATSPERIOD=2 then AVG_BALANCE else 0 END as CURR_AVG_BALANCE,<br />
case when AST.STATSPERIOD=2 then CREDIT_COUNTER else 0 END as CURR_CREDIT_COUNTER ,<br />
case when AST.STATSPERIOD=2 then INTERESTPAID else 0 END as CURR_INTERESTPAID,<br />
case when AST.STATSPERIOD=2 then CURR_CHARGES else 0 END as CURR_CHARGES,<br />
case when AST.STATSPERIOD=2 then 0 ELSE MAXIMUM_BALANCE END as PREV_MAXIMUM_BALANCE,<br />
case when AST.STATSPERIOD=2 then 0 ELSE MINIMUM_BALANCE END as PREV_MAXIMUM_BALANCE,<br />
case when AST.STATSPERIOD=2 then 0 ELSE AVG_BALANCE END as PREV_AVG_BALANCE,<br />
case when AST.STATSPERIOD=2 then 0 ELSE CREDIT_COUNTER END as PREV_CREDIT_COUNTER ,<br />
case when AST.STATSPERIOD=2 then 0 ELSE INTERESTPAID END as PREV_INTERESTPAID,<br />
case when AST.STATSPERIOD=2 then 0 ELSE PREV_CHARGES END as PREV_CHARGES,<br />
FROM {?schemaName}.ACCOUNTSTATISTICS AST <br />
[where statement discussed above]<br />
group by ACCOUNTID</i><br />
<br />
<br />
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...<br />
<br />
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)...Sameerhttp://www.blogger.com/profile/04380280581835257131noreply@blogger.com0tag:blogger.com,1999:blog-1347137045980388492.post-4106383718632276932011-12-08T08:48:00.000-08:002012-02-21T19:31:24.989-08:00An Alternative to truncate for pre v9.7So 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<br />
<br />
<br />
1) Which is generic for all platforms...<br />
<br />
ALTER TABLE tablename ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE;<br />
commit;<br />
<br />
<br />
but I am not very sure if space acquired by table is retained in this case<br />
<br />
2) Obviously<br />
<br />
ALTER TABLE tablename ACTIVATE NOT LOGGED INITIALLY;<br />
delete from tablename;<br />
commit;<br />
<br />
<br />
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:<br />
<br />
i) *NIX --> import from /dev/null of del replace into tablename<br />
ii) Windows --> import from nul of del replace into tablename<br />
<br />
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 <b>not</b> 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...Sameerhttp://www.blogger.com/profile/04380280581835257131noreply@blogger.com6tag:blogger.com,1999:blog-1347137045980388492.post-58349063916353191212011-11-12T11:04:00.000-08:002011-11-13T06:59:59.268-08:00Improvise your Backup-RestoreSomehow I am managing to keep-up with my busy office life and on call support... Which it seems is literally 24X7...<br />
<br />
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... :)<br />
<br />
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)...<br />
<br />
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...<br />
<br />
Your throttle the priority by <br />
<b>SET UTIL_IMPACT_PRIORITY FOR</b> <i>utility_id</i> <b>TO</b> <i>num</i><br />
<br />
which will throttle the backup utility or you can set the same by specifying<br />
"<i>UTIL_IMPACT_PRIORITY</i>" clause in the backup command as well...<br />
We should also recollect that if your <i>UTIL_IMPACT_LIM</i> 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...<br />
<br />
To have the backup operation faster it is good to increase your BACKUP BUFFER SIZE/RESTORE BUFFER SIZE <i>"BACKBUFSZ"/"RESTBUFSZ"</i> dbm cfg or you can override it in backup/restore command with <i>"BUFFER buffer_size"</i>... 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 <i>"WITH n BUFFERS"</i>... Multiple buffers specially boost up the performance of backup when using <i>PRALLELISM</i>... Prallelism decides the number of buffer handlers... <br />
Then comes <b>compression</b>, 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...<br />
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... <br />
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...<br />
<br />
<br />
Incremental/delta backups helps you take smaller backups which saves time backing up only <br />
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)...<br />
<br />
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...<br />
<br />
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...Sameerhttp://www.blogger.com/profile/04380280581835257131noreply@blogger.com2tag:blogger.com,1999:blog-1347137045980388492.post-70049433497021058752011-10-15T08:43:00.000-07:002011-10-16T19:54:56.626-07:00Optimize your Tablespaces and Bufferpools-3: Bufferpool OptimizationHi all...<br />
<br />
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...<br />
We have already seen <a href="http://db2performance.blogspot.com/2011/07/optimize-your-tablespaces-and.html">how to wisely choose optimum value for various tablespace related parameters</a> and <a href="http://db2performance.blogspot.com/2011/07/optimize-your-tablespaces-and_24.html">how to decide on tablespace sizing...</a> 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)...<br />
<br />
TO start with as a rule of thumb you should <b>have a separate bufferpool for temporary tablespace</b> and if you have different <b>user temp tablespace(which I strongly recommend) you should have a diff bufferpool for that too</b>... 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...<br />
<br />
Also you should ideally <b>have different bufferpools for index and data tablespaces</b>... This is help you avoid situations where an index tree forced to flushed out of bufferpool to make space for a table read operation... <br />
<br />
<b>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...</b> 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...<br />
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%...<br />
<br />
<br />
<strike>Generally</strike> <i>Even in an OLTP system,</i> transactional data is read sequentially <i>when working with a report...</i> This helps making best of I/O cycles <i>(obviously this decision should be based on queries you have in you system)</i>... 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...<br />
<br />
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... <i>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><br />
<br />
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...Sameerhttp://www.blogger.com/profile/04380280581835257131noreply@blogger.com8tag:blogger.com,1999:blog-1347137045980388492.post-62667278150193034992011-07-24T10:13:00.000-07:002011-07-24T10:13:02.973-07:00Optimize your Tablespaces and Bufferpools-2: Tablespace SizingHey people...<br />
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...<br />
<br />
We will discuss sizing of tablespaces and their separation... We will wind up with little on temp tablespaces...<br />
<br />
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...<br />
<br />
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...<br />
<br />
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)...<br />
<br />
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...<br />
<br />
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)...<br />
<br />
Now wat is the way out??? This is something I have learnt from my seniors at work...<br />
<br />
1) List the tables, and list the tablespaces they belog to. List the tables' row size and its cardinality as well<br />
<br />
2) Sum up the above data per tablespace and see at what rate the data increases for every table<br />
<br />
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... <br />
<br />
This will really help you get rid of time lags for your write operations...<br />
<br />
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...<br />
<br />
Last... People tend to ignore the importance of system temporary tablespace and user temporary tablespace...<br />
<br />
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)...<br />
<br />
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...<br />
<br />
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... <br />
<br />
Meanwhile I have got a some content on backup recovery... Planning to write it some time...Sameerhttp://www.blogger.com/profile/04380280581835257131noreply@blogger.com4tag:blogger.com,1999:blog-1347137045980388492.post-65013244364759672132011-07-05T10:43:00.000-07:002011-07-05T10:43:14.480-07:00Optimize your Tablespaces and Bufferpools-1: Tablespace Design designhey 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!!!<br />
<br />
Well so getting back to db2... In this post I will discussing little bit on the physical database design...<br />
<br />
Lets discuss the tablesapces first... <br />
<br />
So lets start with <b>pagesize</b>... 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)... <br />
<br />
Next is <b>file-system caching</b>... 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)... <br />
<br />
<b>Exten Size</b> 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 <i>dft_extent_sz db cfg</i>... A thumb of rule is to use following formula:<br />
<i>RAID disk drives * Disk strip size</i><br />
<br />
<b>Prefetch Size</b> 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...<br />
<br />
<b>Overhead and transfer rate</b> 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...<br />
<br />
At last let's try to answer the question on <b>"If DMS/SMS/ASM???"</b>... 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... :)<br />
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...<br />
<br />
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<br />
<i><br />
DB2_PARALLEL_IO=*:7</i><br />
<br />
I shall continue the topic in next post... Where I will discuss on sizing and increment for tablespaces and also the temporary tablespaces...Sameerhttp://www.blogger.com/profile/04380280581835257131noreply@blogger.com7tag:blogger.com,1999:blog-1347137045980388492.post-74676286508114208282011-05-31T10:51:00.000-07:002011-05-31T11:18:50.921-07:00RUNSTATS and REORG-3: An effective REORG policyHi 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"...<br />
<br />
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?)... <br />
Ok lets start with a problem statement...<br />
<br />
I was told about about two problems, firstly Sudden Spikes in Reads at DB Server and Sudden Spikes in Writes at DB Server...<br />
<br />
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!!!<br />
<br />
What helped me find it out about the bad performance of blocked I/O was this snapshot:<br />
<i><br />
SELECT BP_NAME, <br />
CAST(VECTORED_IOS as decimal(24,10)) / NULLIF((POOL_DATA_P_READS + POOL_INDEX_P_READS), 0) AS VECTORED_IO_FRACTION,<br />
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;</i><br />
<br />
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:<br />
<br />
<i>SELECT I.TABNAME <b>TABLE_NAME</b>, I.INDNAME <b>INDEX_NAME</b>, REPLACE(SUBSTR(I.COLNAMES, 2) ,'+',',') AS <b>MEM_COLS</b>, <br />
T.LASTUSED AS <b>TAB_LASTUSED</b>, I.LASTUSED AS <b>INDX_LASTUSED</b>,<br />
I.NUM_EMPTY_LEAFS/NULLIF(I.NLEAF+NUM_EMPTY_LEAFS,0) <b>INDX_EMPTY_LEAF_FRACTION</b>,<br />
T.FPAGES-T.NPAGES <b>TAB_EMPTY_PAGES_CNT</b>, I.NLEAF+NUM_EMPTY_LEAFS/<i>(num_partitions*extent_size)</i> AS <b>INDX_REORG_NEEDED_IF_GT_1</b>,<br />
T.FPAGES/<i>(num_partitions*extent_size)</i> <b>TAB_REORG_NEEDED_IF_GT_1</b> <br />
FROM SYSCAT.INDEXES I JOIN SYSCAT.TABLES T ON T.TABNAME=I.TABNAME AND T.TABSCHEMA=I.TABSCHEMA AND T.TABSCHEMA IN <i>(schemalist)</i> AND T.TYPE='T';</i><br />
<br />
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:<br />
<b><br />
1)</b> Too many disk seeks happening for read (even for index scans)<br />
<b>2)</b> The sequential bloced IO fails miserably as the blocks pertaining to same table/index are scatterd<br />
<b>3)</b> The writes, a record inserted/updated might have to re-balance the indexes, which is scattered so write cost increases<br />
<b>4)</b> Finally the free leaves (for indexes)/blocks (for tables) are not available sequentially...<br />
<br />
So I decided to do a REORG.. Most of the times people re-org with <b>"REORG TABLE SCHEMA.TABLENAME"</b> 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... <br />
<br />
So I wrote a stored proc to dynamially select an index for every table and generate re-org statements for <i>"REORG INDEXES ALL FOR TABLE"</i> and <i>"REORG TABLE USING INDEX"</i>... Guess what!!! those momentary spikes were past and we had a good stable bufferpool utilization...<br />
<br />
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...<br />
<br />
So what next??? Probably some SQL Tuning tips!!! Till then HAPPY PREFETCHING!!!Sameerhttp://www.blogger.com/profile/04380280581835257131noreply@blogger.com8tag:blogger.com,1999:blog-1347137045980388492.post-52608910719400399512011-04-30T11:45:00.000-07:002011-05-03T11:42:40.967-07:00SQL Anti logic!!!<div dir="ltr" style="text-align: left;" trbidi="on">Well I am just breaking my sequence of RUNSTATS and REORG for a while (I promise my next blog will be having that)... <br />
<br />
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...<br />
Let me share a few to guide you thru this...<br />
<br />
I will try to skip the table structure etc (unless it is very much reqd) in order to avoid boring you guys/gals...<br />
<br />
<b>1)</b> 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:<br />
<br />
<i> select * from customer where <strike>cast(pincode as varchar(6)) like '56%'</strike></i><br />
<br />
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:<br />
<i>select * from customer where pincode between 560000 and 569999</i><br />
If you thought that off pretty early (before I gave the hint), hats off to you...<br />
<br />
<b>2)</b>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:<br />
<br />
<i>select * from transaction_posting_table where date(txn_posting_date)='2010/04/30'</i><br />
<br />
Some one who has attended an Oracle Usability/Oracle Enablement session for DB2 might think<br />
<br />
<i>select * from transaction_posting_table where <strike>trunc(txn_posting_date)='2010/04/30'</strike></i><br />
<br />
'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...<br />
<br />
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:<br />
<br />
<i>select * from transaction_posting_table where txn_posting_date)>='2010/04/30' and txn_posting_date)<'2010/04/30'+1 </i> Kwel???<br />
'2010/04/30' is '2010/04/30' with time 12:00:00.0000AM<br />
and '2010/04/30' +1 is '2010/05/01' with time 12:00:00.0000AM...<br />
<br />
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 )... :)<br />
<br />
<b>3)</b> 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?<br />
<br />
<i>select max(sal) as secnd_max_sal from emp where sal< <strike>(select max(sal) from emp)</strike> </i><br />
<br />
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...)<br />
<br />
<i>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; </i><br />
<br />
(more extensible and works with just one sort and an rid scan!!! :) )<br />
<br />
<b>4)</b> 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... <br />
so a few optimization that comes to my mind (I will just write the JOIN clause):<br />
<br />
>><i>A LEFT OUTER JOIN B on A.Aid=B.Bid JOIN C on <strike>(A.Aid=C.Cid or B.Bid=C.Cid</strike>)</i><br />
<br />
Better way to write is:<br />
>><i>A LEFT OUTER JOIN B on A.Aid=B.Bid JOIN C on (C.Cid COALESCE(A.Aid,B.Bid))</i><br />
<br />
Another scenario is <br />
>><i>A JOIN B on A.Aid=B.Bid JOIN C on <strike>(A.a1=C.Cid or B.b1=C.Cid)</strike></i><br />
<br />
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???<br />
>><i>A JOIN B on A.Aid=B.Bid JOIN C on C.Cid IN( B.b1,A.a1)<br />
</i><br />
<br />
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 <a href="http://www.dbisoftware.com/blog/db2nightshow.php?id=239">DB2 has talent</a>)... 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...<br />
<br />
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...<br />
<br />
For those who want more before they can do on their own... Spend a few bucks and get <a href="http://www.amazon.com/SQL-Antipatterns-Programming-Pragmatic-Programmers/dp/1934356557/ref=sr_1_1?ie=UTF8&s=books&qid=1304188540&sr=8-1">this book on SQL Antipatterns</a>... I havent yet read this but some one suggested it is on the same lines (and the Title of the book suggests the same)...<br />
</div>Sameerhttp://www.blogger.com/profile/04380280581835257131noreply@blogger.com6tag:blogger.com,1999:blog-1347137045980388492.post-44339473046131009122011-04-22T13:23:00.000-07:002011-04-23T15:04:10.235-07:00RUNSTATS and REORG-2: How to automate runstats<div dir="ltr" style="text-align: left;" trbidi="on">Hi guys!!! I am back... n back as an IBM Information Champion (yup got selected for 2011 :) ) !!!<br />
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... <br />
<br />
Well in <a href="http://db2performance.blogspot.com/2011/04/runstats-and-reorg-1-how-important-they.html">last post I talked about importance of runstats and reorg for a db2 database</a>...<br />
<br />
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...<br />
<br />
well... automating runstats is pretty simpler... I will take you little deep into this automation n its impact...<br />
Following are the automatic maintainence parameters in db2...<br />
__________________________________________________________________<br />
Automatic maintenance (AUTO_MAINT) <br />
| <br />
|-- Automatic database backup (AUTO_DB_BACKUP)<br />
|<br />
|-- Automatic table maintenance (AUTO_TBL_MAINT) <br />
|<br />
|---- Automatic runstats (AUTO_RUNSTATS) <br />
| <br />
|-------- Automatic statement statistics (AUTO_STMT_STATS) <br />
| <br />
|---- Automatic statistics profiling (AUTO_STATS_PROF) <br />
| <br />
|--------- Automatic profile updates (AUTO_PROF_UPD) <br />
|<br />
|---- Automatic reorganization (AUTO_REORG) <br />
__________________________________________________________________<br />
So it must be clear that above is an hierarchy... To set ON a child you need to set ON the parent first...<br />
Now lets understand the parameters we are concerned about... <br />
<br />
<b>1) First one is AUTO_RUNSTATS</b><br />
If you set it effective (not just ON... guess what i mean by this???), it will automatically keep doing runstats on tables...<br />
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 <b>db2 luw v9.7 will switch it on automatically by default for any new db created</b>... <br />
<br />
<b>2) Next will be automatic statement statistics</b><br />
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...<br />
Well here are a few SNAP DB monitor elements, which might help you decide best about these parameters...<br />
<i>i) STATS_FABRICATIONS Vs ASYNC_RUNSTATS+SYNC_RUNSTATS is STATS usage TO STATS GATHER ratio, <br />
ii) SYNC_RUNSTATS vs SYNC_RUNSTATS+ASYNC_RUNSTATS is FRACTION of SYNC RUNSTAT<br />
iii) STATS_FABRICATE_TIME Vs SYNC_RUNSTATS_TIME is usage stats compared to SYNC update of STATS<br />
iv) SYNC_RUNSTATS_TIME Vs ELAPSED_EXEC_TIME_S & ELAPSED_EXEC_TIME_MS will give you impact of SYNC STAT COLLECTION ON STMT execution<br />
</i>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...<br />
<br />
<b>3) Third is Automatic statistics profiling and Automatic profile updates</b><br />
Switching on the automatic stats profiling, will turn on stats profile generation... <br />
but you can not set it on if your db cfg section_actual is ON... <br />
<i>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...</i><br />
Automatic Profile update will update the runstats profile with recommendations...<br />
If it is off profile recommendations generated are stored in opt_feedback_ranking...<br />
<i>Both of these are off by default... </i><br />
<b>Will suggest keeping them off</b> 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...<br />
<br />
<i><b>One more way of statistics gathering</b>, is to do a run-stats in idle time, with a background scheduled job...</i> A few months ago, I would have preferred that, when my seniors explained me that... <br />
<blockquote>"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..."</blockquote><br />
So this is all about automating the runstats... <br />
<i>I will get back on auto maint parameters for automatic reorg and how to identify when to do a reorg...</i><br />
<br />
<b><i>For people interested in exploring more... </i></b><br />
--> Go thru <a href="http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=/com.ibm.db2.luw.admin.config.doc/doc/r0011479.html">this info center page</a> for more content on auto_maint parameters...<br />
--> Go <a href="http://www.ibm.com/developerworks/data/library/techarticle/dm-0706tang/">thru this article on dev works</a>... It explains the automatic maintainence parameters for statistics gathering quit well...</div>Sameerhttp://www.blogger.com/profile/04380280581835257131noreply@blogger.com6tag:blogger.com,1999:blog-1347137045980388492.post-12341040705764531512011-04-08T13:24:00.000-07:002011-04-08T13:24:56.343-07:00RUNSTATS and REORG-1: How important they are<div dir="ltr" style="text-align: left;" trbidi="on">Hi guys...<br />
<br />
So I am back... little quick this time... <br />
So this is final post in my pseudo participation blog for DB2 Night Show (I had an early exit :-( after qualifying for finals)...<br />
Congrats to Norberto for <a href="https://www.ibm.com/developerworks/mydeveloperworks/blogs/SusanVisser/entry/and_the_winner_of_the_db2_s_got_talent_competition_is?lang=en_us">winning the competitions</a>... <a href="https://www.ibm.com/developerworks/mydeveloperworks/blogs/norbertogf/?lang=en">Check his blog for tips</a>...<br />
Congrats to JB, Momi and Thiru as well for making to final...<br />
I am looking forward to more such opportunities in future... <br />
<br />
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... <br />
<br />
<b>1) </b><b>RUNSTATS</b> 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...<br />
<br />
<b>2) </b><b>REORG</b> 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...<br />
<br />
Well you can AUTOMATE your task to certain extent and use automatic maintainance database configuration for auto runstats and auto reorg...<br />
<br />
I shall come up with more on pros and cons of using those parameters and ways to optimize your <b>RUNSTATS and REORG</b>, in my future blogs...<br />
<br />
I would like to thank <a href="https://www.ibm.com/developerworks/mydeveloperworks/blogs/SusanVisser/?lang=en_us">Susan</a> and <a href="http://twitter.com/@sathyaram_s">Sathey</a> for their continuous support, encouragement and tips for improvement on by blogging...<br />
<br />
I will keep my blogs little short and will split the massive overdose of info over several blogs (thanks to <a href="http://twitter.com/@sathyaram_s">Sathey</a>)...<br />
<br />
Latter I will also try to split my previous long posts into several blogs, which shall help my new subscriber...</div>Sameerhttp://www.blogger.com/profile/04380280581835257131noreply@blogger.com0tag:blogger.com,1999:blog-1347137045980388492.post-11764563866589465802011-04-04T05:44:00.000-07:002011-04-04T05:44:10.423-07:00My db2 success story: Five things to check when you see high memory usage on db serverHi, 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...<br />
<br />
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 <a href="http://www.dbisoftware.com/db2nightshow/20110325DB2Night47.wmv">see the replay</a> and <a href="http://www.surveymonkey.com/Home_Landing.aspx?sm=2aMi%2fU4OfncZPk60OVopFg8MIlkaOpn9Hgz0v1HSKME%3d">vote before 7th april</a>... <br />
<br />
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 <a href="http://ibm.co/i4g3Ey">Susan's guest blogger</a>...<br />
<br />
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)... <br />
<br />
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...<br />
<br />
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...<br />
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:<br />
<br />
<b>First Step:</b> I requested for snapshot of <i>memory usage by db2 </i> instance and db2 database hosting the production db:<br />
db2mtrk -i -d [-v]<br />
<br />
<b>It confirmed </b> 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...<br />
<br />
<b>Second thing</b> I did was a request to check the <i>tablespaces where the tables are lying and</i> the <i>bufferpool used by all the different tablespaces </i> we had suggested<br />
<br />
<b>This confirmed</b> 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...<br />
<br />
<b>third step</b> of my investigation was to check if there is <i>CIO (oncurrent I/O) enabled at the filesystem </i> level and to check if the <i>filesystem chaching is switched off at tablespace </i> level... <br />
<br />
<b>the result confirmed the cause for high I/O and too much of memory usage...</b><br />
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...<br />
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... <i>firstly it causes to much of memory to be used, and secondly there is more time wasted in double caching :P...</i><br />
<br />
<b>Correcting this helped alot...</b><br />
<br />
<b>fourth thing </b> 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)...<i> Ideally 1.5-3 times your block size is a good number for prefetch data...</i><br />
<br />
<b> fifth thing I confirmed </b> 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...<br />
you can check the performance of blocked I/O using block_ios and vertored_ios monitor elements belonging to your bufferpool...<br />
<i>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...</i><br />
<br />
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...<br />
<br />
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...<br />
<br />
<i>My mantra for db2 DBAs in production is a snapshot a day keeps performance issues aways...</i> i.e. run one snapshot a day and work on the results, fix the problems you anticipate out of their results...<br />
<br />
Well I am myself looking forward to DBI's most awaited webinar on <a href="http://www.dbisoftware.com/events.php">DB2 LUW Performance: 10 SQL Snapshots You MUST Run Today!</a><br />
<a href="https://www2.gotomeeting.com/register/843300803">Register Now!!!</a><br />
<br />
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...Sameerhttp://www.blogger.com/profile/04380280581835257131noreply@blogger.com1tag:blogger.com,1999:blog-1347137045980388492.post-32167488181830033592011-03-23T09:25:00.000-07:002011-03-26T13:12:48.858-07:00db2exfmtSo in last episode (#45) of <a href="http://www.dbisoftware.com/db2nightshow/20110311DB2Night45.wmv">db2 night show</a>, 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... <a href="http://www.dbisoftware.com/db2nightshow/20110318DB2Night46.wmv">now final no 3 vdo has come</a>... It has some really kwel tips... tomorrow is final no 4 (i know I am lagging behind :))... <a href="http://www.dbisoftware.com/db2nightshow/index.php">watch it live </a>or catch up with the replay latter... But do watch n vote for whom u find the best...<br /><br /><br />Well getting back to my "would have been" presentation...<br /><br /><span style="font-weight:bold;">db2exfmt</span><br /><br />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)... <br />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<br />More than that generally what is reported is an part of application or a module which has more response time...<br /><br />So how do we proceed... <br />Here is a db2something to rescue you... <span style="font-weight:bold;">db2exfmt</span><br /><br />This tool will help you format the content of db2 <span style="font-style:italic;">EXPLAIN</span> tables. AIX users can locate this in DB2_HOME/sqllib/misc<br /><br />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:<br /><br />Prerequisite<br />-- You need to create EXPLAIN tables using <span style="font-weight:bold;">EXPLAIN.DDL</span> in the above mentioned directory<br /><br />For first scenario:<br /><span style="font-weight:bold;">-- Use the EXPLAIN statement to generate explain information for a query</span><br /><br />Following are the valid db2 queries/commands which can be used in explain statement:<br /> * CALL, Compound SQL (Dynamic), DELETE, INSERT<br /> * MERGE<br /> * REFRESH<br /> * SELECT<br /> * SELECT INTO<br /> * SET INTEGRITY<br /> * UPDATE<br /> * VALUES<br /> * VALUES INTO<br /><br />Example: <br /> >>> EXPLAIN ALL FOR SELECT Col1 FROM Test_Tab<br /><br />In explain statement use <span style="font-weight:bold;">WITH SNAPSHOT</span> to ensure that snapshot is also <span style="font-style:italic;">recorded</span> with the explain info...<br /> <br />This step will capture the explain information in the <a href="http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=/com.ibm.db2.luw.admin.perf.doc/doc/c0005137.html">explain tables</a>. <br /><br />-- 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 <a href="http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=/com.ibm.db2.luw.admin.perf.doc/doc/c0005739.html">/EXPLAINregistry</a>/ /<a href="http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=/com.ibm.db2.luw.admin.perf.doc/doc/c0005739.html">EXPLAIN SNAPSHOT</a> This will enable capturing snapshot/explain info for all the queries which will be fired by application in that session...<br /><br /><span style="font-weight:bold;">-- Once you have the EXPLAIN info captured use db2exfmt to format the explain data</span><br /><br />db2exfmt can be either invoked with arguments or can be invoked in interactive mode.<br /><br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEibMQuoVIfN_Hv9Lbqu2jh46o8kl0jnbv5bOpmtbzfGNB-PFUYnLIXgcD-04AN4kxs_OiuvXsjUYRUVwfNvpAfuSt68VE8l9AsIuO3SDwowQQa00gpU0ZRu91fI_tZkCVDFcufVFdyMv3M/s1600/s7_i8_3_db2exfmt.gif"><img style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;width: 320px; height: 130px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEibMQuoVIfN_Hv9Lbqu2jh46o8kl0jnbv5bOpmtbzfGNB-PFUYnLIXgcD-04AN4kxs_OiuvXsjUYRUVwfNvpAfuSt68VE8l9AsIuO3SDwowQQa00gpU0ZRu91fI_tZkCVDFcufVFdyMv3M/s320/s7_i8_3_db2exfmt.gif" border="0" alt=""id="BLOGGER_PHOTO_ID_5588094217580059154" /></a><br /><br /><br />Now lets explore what the various info you can get from the explain tables using exfmt:<br /><br />>> You can use -g option to generate graph. There are vaiour sub-options to include more detailed info in the graph:<br /> -gT will include cost for each operation in graph<br /> -gI will include I/O for every operation in graph<br /> -gF will include first tuple cost<br /> -gC will include the expected cardinality<br /> One can combine any of the above options e.g. -gCI shall give cardinality and I/O cost for every operation in graph<br /><br />You can either choose to format the latest explain info or can -w <span style="font-style:italic;">timestamp</span> for explaining a specific timestmp's explain info... <br /><br />Or you can specify SOURCE_SCHEMA/SOURCE NAME to narrow down you search for hotspots... using -s or -n<br /><br />-o / -t will let you re-direct the output to an output file or to your terminal.<br /><br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhValabvBHKz3yiqrf2GFPV7dCkzCXgvUXoJTw2nPGMSVRSx6qfqtV488InOr5tYHF9crJrRRJXxEfS_MnGED7vrm3FDPps9yFshGt9DTjNZ4XxsEdw423Kq17Er-WOpisgsN83UttmL-8/s1600/1283500815_ddvip_4860.jpeg"><img style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;width: 307px; height: 400px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhValabvBHKz3yiqrf2GFPV7dCkzCXgvUXoJTw2nPGMSVRSx6qfqtV488InOr5tYHF9crJrRRJXxEfS_MnGED7vrm3FDPps9yFshGt9DTjNZ4XxsEdw423Kq17Er-WOpisgsN83UttmL-8/s400/1283500815_ddvip_4860.jpeg" border="0" alt=""id="BLOGGER_PHOTO_ID_5588096690975019730" /></a><br /><br />Well now it is worth mentioning about other tools in db2 available for similar usage<br /><br /><span style="font-weight:bold;">>></span> with <a href="http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=/com.ibm.db2.luw.admin.cmd.doc/doc/r0005736.html">db2expln</a> 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 <br />it can not be used to capture the explain info of all queries being fired on db from application<br /><br /><span style="font-weight:bold;">>></span> with <a href="http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=/com.ibm.db2.luw.admin.perf.doc/doc/c0005739.html">db2caem </a>(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...<br /><br />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...Sameerhttp://www.blogger.com/profile/04380280581835257131noreply@blogger.com5tag:blogger.com,1999:blog-1347137045980388492.post-75271639362130538692011-03-15T13:14:00.000-07:002011-03-15T13:14:14.835-07:00db2 performance: How to convey performance metrics from SNAPSHOT vi...<a href="http://db2performance.blogspot.com/2011/03/how-to-convey-performance-metrics-from.html?spref=bl">db2 performance: How to convey performance metrics from SNAPSHOT vi...</a>: "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..."Sameerhttp://www.blogger.com/profile/04380280581835257131noreply@blogger.com0tag:blogger.com,1999:blog-1347137045980388492.post-66454254011216066102011-03-11T11:42:00.000-08:002011-03-15T13:06:11.215-07:00How 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)<br /><br />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)... <br /><br />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:<br /><br /><span style="font-weight:bold;">1)</span> SQLs which are although below the benchmark but are taking more time compared to <br /> what they should be taking<br /><span style="font-weight:bold;">2)</span> SQLs which are spending too much of time in sorting the data<br /><span style="font-weight:bold;">3)</span> SQLs which are executed with a misguided plan (i.e. not getting proper statistics)<br /><span style="font-weight:bold;">4)</span> SQLs which are spending too much time on I/O and fetching lesser no of rows i.e. <br /> which very high I/O time per row fetched<br /><span style="font-weight:bold;">5)</span> I started looking for no of data pages and index pages in bufferpool that are read <br /> and written by a query (trust me you can not get a better metrics for identifying <br /> table scans)<br /><span style="font-weight:bold;">6)</span> One thing which I had not earlier not thought of but eventually became my concern <br /> was SQL which get executed only once or twice<br /><span style="font-weight:bold;">7)</span> SQLs which are executed over and over and total execution time was too much<br /><span style="font-weight:bold;">8)</span> How efficient is log write and log reads<br /><span style="font-weight:bold;">9)</span> Are there too long transactions and what is the frequency of commit<br /><br /><br />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)<br /><br />Following metrics helped me get the stats I was looking for:<br />Following statements helped me get some metrics for statements:<br /><br />1) <span style="font-style:italic;">SUM(TOTAL_SORT_TIME)/NULLIF(SUM((STMT_USR_CPU_TIME_S*1000000) + STMT_USR_CPU_TIME_MS) + SUM(TOTAL_SORT_TIME),0 )<br /> <span style="font-weight:bold;">AS TOTAL_SORT_TIME_FRACTION</span></span><br /><br />2) <span style="font-style:italic;">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)<br /><span style="font-weight:bold;">AS TOTAL_TEMP_BP_UTIL</span></span><br /><br />3) <span style="font-style:italic;">SUM(POOL_DATA_P_READS)/(NULLIF(SUM(POOL_DATA_L_READS),0)) <span style="font-weight:bold;">TOTAL_DATA_BP_REFRESH</span></span><br /><br />4)QUERY_CARD_ESTIMATE AS EXPECTED_CARDINALITY,<br />ROWS_READ, where EXPECTED_CARDINALITY/ROWS_READ>=1.5 <br /><br />5) <span style="font-style:italic;">SUM(CAST(total_sort_time as decimal(24,10)))/NULLIF(SUM((STMT_USR_CPU_TIME_S*1000000) + STMT_USR_CPU_TIME_MS),0 )<br /> AS<span style="font-weight:bold;"> TOTAL_SORT_TIME_FRACTION</span></span><br /><br />Following metrics helped me check the dynamic sqls (from SNAPDYN_SQL snapshot view):<br /><br />6) <span style="font-style:italic;">CAST(TOTAL_SORT_TIME*(NUM_EXECUTIONS)as decimal(24,10))/NULLIF(PREP_TIME_BEST * NUM_COMPILATIONS,0) AS <span style="font-weight:bold;">SORT_TO_COMPILE_RATIO</span></span><br /><br />7) <span style="font-style:italic;">CAST(((PREP_TIME_WORST + PREP_TIME_BEST)*NUM_COMPILATIONS)as decimal(24,10))/NULLIF(2*NUM_EXECUTIONS,0) <span style="font-weight:bold;">COMPILE_EFFICIENCY</span>,</span><br /><br />8) <span style="font-style:italic;">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)<br /><span style="font-weight:bold;">AS TOTAL_TEMP_BP_UTIL</span></span><br /><br />Following are helpful metrics for monitoring locking performance (from SNAPDB):<br /><br />9) LOCK_ESCALS LOCK_ESCALATIONS,<br /><br />10) DEADLOCKS/NULLIF(LOCK_WAITS,0) DEADLOCK_PER_LOCK,<br /><br />11) X_LOCK_ESCALS EXCLUSIVE_LOCL_ESCAL,<br /><br />12) LOCKS_WAITING/NULLIF(LOCKS_HELD,0) LOCK_ISSUES_PER_LOCK,<br /><br />13) DEADLOCKS/NULLIF(COMMIT_SQL_STMTS,0) DEADLOCK_PER_STMNT<br /><br />Following are some good metrics to look for while measuring i/o efficiency (from SNAPDB)<br /><br />14) <span style="font-style:italic;">cast(POOL_DRTY_PG_STEAL_CLNS*1000 as decimal(24,10))/ NULLIF(COMMIT_SQL_STMTS,0)<br /><span style="font-weight:bold;">DIRTY_PAGE_STEAL_FRACTION</span>,<br /></span><br /><br />15) <span style="font-style:italic;">cast(DIRECT_READS as decimal(20,10)) / NULLIF(DIRECT_READ_REQS,0) <span style="font-weight:bold;">DIRECT_READ_EFFECIENCY</span>,<br /></span><br /><br />16)<span style="font-style:italic;"> cast(float(NUM_LOG_WRITE_IO)/NULLIF(LOG_WRITES,0) as decimal(20,10)) LOG_IO_PER_WRITE,<br /></span><br /><br />17)<span style="font-style:italic;"> cast(float(NUM_LOG_READ_IO)/NULLIF(LOG_READS,0) as decimal(20,10)) LOG_IO_PER_READ,<br /></span><br /><br />18) <span style="font-style:italic;">cast(NUM_LOG_BUFFER_FULL as decimal(20,10))/NULLIF(LOG_WRITES,0)<span style="font-weight:bold;"> LOG_BUFF_UTIL</span>,<br /></span><br /><br />19)<span style="font-style:italic;"> CAST(LOG_WRITES AS DECIMAL(20,10))/NULLIF(COMMIT_SQL_STMTS,0) <span style="font-weight:bold;">LOG_WRITE_EFFECIENCY</span>,<br /></span><br /><br />20) <span style="font-style:italic;">cast(NUM_LOG_DATA_FOUND_IN_BUFFER as decimal(20,10))/NULLIF(NUM_LOG_READ_IO+NUM_LOG_DATA_FOUND_IN_BUFFER,0) <span style="font-weight:bold;">AS LOG_BUFF_READ_EFFICIENCY</span>,<br /></span><br />21) cast(LOG_HELD_BY_DIRTY_PAGES *100 as decimal(24,10))/(10240*4096) <span style="font-weight:bold;">AS LOG_IOCLEANERS_EFFICIENCY</span>,<br /><br />22) <span style="font-style:italic;">cast(UNREAD_PREFETCH_PAGES as decimal(24,10))/NULLIF(PREFETCH_WAIT_TIME,0) <span style="font-weight:bold;">AS WASTEFULL_PREFETCH_PER_WAIT_MSEC</span><br /></span><br /><br />23) <span style="font-style:italic;">cast(POOL_NO_VICTIM_BUFFER as decimal(24,10))/NULLIF((POOL_INDEX_P_READS+POOL_DATA_P_READS),0) AS <span style="font-style:italic;"><span style="font-weight:bold;">BP_CLEANER_FAILURE_FRACTION</span><span style="font-weight:bold;"></span></span></span><br /><br /><br /><br />24) <span style="font-style:italic;">--Overall Bufferpool Utilization per bufferpool<br />SELECT <br />SNAPSHOT_TIMESTAMP,<br />DB_NAME,<br />BP_NAME,<br />TOTAL_LOGICAL_READS,<br />TOTAL_PHYSICAL_READS,<br />TOTAL_HIT_RATIO_PERCENT,<br />INDEX_HIT_RATIO_PERCENT,<br />DATA_HIT_RATIO_PERCENT,<br />DATA_LOGICAL_READS,<br />DATA_PHYSICAL_READS,<br />INDEX_LOGICAL_READS,<br />INDEX_PHYSICAL_READS<br />FROM BP_HITRATIO<br />where DATA_HIT_RATIO_PERCENT<90 OR INDEX_HIT_RATIO_PERCENT<95;<br /></span><br /><br /><br />25)<br /><span style="font-style:italic;">SELECT<br />SNAPSHOT_TIMESTAMP,<br />BP_NAME,<br />CAST(VECTORED_IOS as decimal(24,10))/NULLIF((POOL_DATA_P_READS+POOL_INDEX_P_READS),0) AS <span style="font-weight:bold;">VECTORED_IO_FRACTION</span>,<br />CAST(PAGES_FROM_VECTORED_IOS as decimal(24,10))/NULLIF((PAGES_FROM_VECTORED_IOS+PAGES_FROM_BLOCK_IOS),0) AS <span style="font-weight:bold;">VECTORED_PAGE_READ_FRACTION</span>,<br />CAST(UNREAD_PREFETCH_PAGES as decimal(24,10))/NULLIF(PAGES_FROM_BLOCK_IOS,0) AS <span style="font-weight:bold;">WASTEFULL_PREFETCH_FRACTION</span>,<br />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 <span style="font-weight:bold;">ANSYNC_PREFETCH_EFFICIENCY</span>,<br />CAST(POOL_NO_VICTIM_BUFFER as decimal(24,10))/NULLIF((POOL_INDEX_P_READS+POOL_DATA_P_READS),0) AS <span style="font-weight:bold;">BP_CLEANER_FAILURE_FRACTION</span><br />FROM SNAPBP;</span><br /><br />Following parameters helped me get some imp points about how fine are the automatic maintainence parameters working<br /><br />26) <br /><span style="font-style:italic;">cast(STATS_FABRICATIONS as decimal(24,10))/NULLIF((ASYNC_RUNSTATS+SYNC_RUNSTATS),0) AS <span style="font-weight:bold;">STATS_USE_TO_GATHER_RATIO</span>, <br /></span><br /> <br />27) <br /><span style="font-style:italic;">cast(SYNC_RUNSTATS as decimal(24,10)) /NULLIF((SYNC_RUNSTATS+ASYNC_RUNSTATS),0) <span style="font-weight:bold;">SYNC_RUNSTAT_FRACTION</span>,<br /></span><br /><br />28)<br /><span style="font-style:italic;">cast(STATS_FABRICATE_TIME as decimal(24,10))/NULLIF(SYNC_RUNSTATS_TIME,0) AS <span style="font-weight:bold;">STATS_SYNC_COMPUTE_UTIL</span>,<br /></span><br /><br />29)<br /><span style="font-style:italic;">cast(SYNC_RUNSTATS_TIME*1000000 as decimal(24,10))/NULLIF(ELAPSED_EXEC_TIME_S+ELAPSED_EXEC_TIME_MS*1000000,0) AS <span style="font-weight:bold;">SYNC_STAT_COLLECT_TIME_FRACTION</span></span><br /><br />30) Two parameters in SYSIBMADM.SNAPDYN_SQL which can help you see if the queries provided by development is properly parameterized or not:<br /><br /><span style="font-weight:bold;">NUM_EXECUTIONS and NUM_COMPILATIONS</span><br /><br /><br />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 <br /><br />31)<br /><span style="font-style:italic;"> CAST((POOL_DATA_L_READS-POOL_DATA_P_READS) as decimal(24,10))/NULLIF(POOL_DATA_L_READS,0) <span style="font-weight:bold;">DATA_BP_UTIL</span>,<br /></span><br /><br />32) <br /><span style="font-style:italic;">CAST(POOL_DATA_P_READS as decimal(24,10))/NULLIF(POOL_DATA_L_READS,0) <span style="font-weight:bold;">DATA_BP_REFRESH</span>,<br /></span><br /><br />33)<br /><span style="font-style:italic;"> CAST((POOL_INDEX_L_READS-POOL_INDEX_P_READS)as decimal(24,10))/NULLIF(POOL_INDEX_L_READS,0) <span style="font-weight:bold;">INDX_BP_UTIL</span>,<br /></span><br /><br />34)<br /><span style="font-style:italic;"> CAST(POOL_INDEX_P_READS as decimal(24,10))/NULLIF(POOL_INDEX_L_READS,0) <span style="font-weight:bold;">INDX_BP_REFRESH</span>,<br /></span><br /><br />35)<br /><span style="font-style:italic;"> 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)<br /> AS <span style="font-weight:bold;">TOTAL_TEMP_BP_UTIL</span>,</span><br /><br /><br />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!!!<br /><br />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)!!!<br /><br />Will continue writing!!! db2expln is next to come!!!Sameerhttp://www.blogger.com/profile/04380280581835257131noreply@blogger.com0tag:blogger.com,1999:blog-1347137045980388492.post-51308459935616942012011-02-18T12:00:00.000-08:002011-02-18T13:04:19.226-08:00Stuck 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... <br />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!!! :(<br />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<br /><br />What to do??? <br /><br />a few tips... (* make sure you are on latest db2 version)<br /><br /><span style="font-weight:bold;">1)</span> 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...<br /><br /><span style="font-style:italic;">update db cfg using STMT_CONC LITERAL</span><br /><br />those of you who are coming from an Oracle Background can refer to <span style="font-style:italic;">cursor_sharing</span><br /><br /><span style="font-weight:bold;">2)</span> 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 <span style="font-style:italic;">optimize for first N rows</span> or <span style="font-style:italic;">fetch first N rows</span>... 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<br /><br /><span style="font-weight:bold;">3)</span> 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....<br /><br /><span style="font-weight:bold;">4)</span> If you see that the issue is with only a few queries, do not change the optimization level rather use <span style="font-style:italic;">optimization profile</span><br />Well it is something oracle hints but not exactly the same... I can not explain the whole concept here, but yeah go thru this <a href="http://www.ibm.com/developerworks/data/library/techarticle/dm-0612chen/index.html">article </a>on developer works...<br /><br /><span style="font-weight:bold;">5)</span> 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)<br /><br /><span style="font-weight:bold;">6)</span> 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<br /> i) at any given point of time you table will have 0 records unless <br /> this module is running<br /> ii) Whenver RUNSTATS run (either auto or your cron job) the table <br /> has got 0 records<br /> iii) now when your module populates data all of a sudden the stats <br /> will not be effective immediately but a fetch (probably in same <br /> module) may be immediate<br /> iv) The optimizer thinks the table is empty and generates a wrong <br /> plan (a costly one) and avoids any index scan<br /><br />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...<br />a trick... say <br /> <span style="font-style:italic;">alter table tabname volatile</span><br /><br />this will enforce an index scan if the table is indexed<br /><br /><span style="font-weight:bold;">7)</span> Use query compiler environment variable as below to optimize the queries before getting executed:<br /> <span style="font-style:italic;">db2set db2_antijoin=EXTENDED<br /> <br /> db2set db2_inlist_to_nljn=yes</span><br /><br /><span style="font-weight:bold;">8)</span> 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...<br /><br /><br />Well now m feeling sleeepy... will modify the post if i can recollect more tips for dbas STUCK with packaged apps...Sameerhttp://www.blogger.com/profile/04380280581835257131noreply@blogger.com0tag:blogger.com,1999:blog-1347137045980388492.post-72444493910384666562011-02-09T10:34:00.000-08:002011-02-15T11:58:33.214-08:0010 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)...<br /><br /><span style="font-weight:bold;">1)</span> 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)<br /><br /><span style="font-weight:bold;">2)</span> 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)<br /><br /><span style="font-weight:bold;">3)</span> Something as good as OEM in Oracle to monitor the database (and it should be free, hence optim is ruled out :P)<br /><br /><span style="font-weight:bold;">4)</span> 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)<br /><br /><span style="font-weight:bold;">5)</span> 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 <span style="font-style:italic;">SELECT TOP N</span> for sql server 2008. It would had been nice if DB2 did the same, in an easier way (it is still possible with <span style="font-weight:bold;">OPTIMIZEFORNROWS </span>in db2cli.ini)<br /><br /><span style="font-weight:bold;">6)</span> 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...<br /><br /><span style="font-weight:bold;">7)</span> It will be good to have a better concurrency control and locking mechanism which can compete with that of oracle<br /><br /><span style="font-weight:bold;">8)</span> Query Hints should be made more flexible and easy to use in prepared statements, on lines with Oracle and SQL Server <br /><br /><span style="font-weight:bold;">9)</span> 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)<br /><br /><span style="font-weight:bold;">10)</span> Some good way of indexing low cardinality tables (like oracle has bit map indexes)<br /><br /><br />Well let's see how much db2 galelio has to offer!!! Have look:<br /><br /><a href="http://freedb2.com/db2-early-experience-program-on-the-cloud/">DB2 Galileo Early Adoption Program</a>Sameerhttp://www.blogger.com/profile/04380280581835257131noreply@blogger.com7tag:blogger.com,1999:blog-1347137045980388492.post-59837209499952648682011-02-08T10:14:00.000-08:002011-02-08T10:48:38.523-08:00Well,<div>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...</div><div><br /></div><div>So it involves following...</div><div>Having a more scalable Physical design...</div><div>A more robust logical schema design...</div><div>A more performing application design...</div><div><br /></div><div>Let me discuss in brief </div><div>To start with Physical Design...</div><div><br /></div><div><span class="Apple-style-span" ><b>PHYSICAL DESING</b></span></div><div>With DB2 you need to ensure following things in physical design</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>1) You have tablespaces distributed on different disks</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>2) With my personal experience I suggest usage of ASM spread over several disks</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>3) Always have indexes and Data on saperate disks</div><div> 4) It is good to categorize table and indexes as below</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>i) Online Transaction Data</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>ii) Configuraiton Data</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>iii) Static data for your application to work and load</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>Place tables of above category in three different tablespaces and their indexes in three </div><div><span class="Apple-tab-span" style="white-space:pre"> </span>different</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>5) Always create separate bufferpools for configuration & static data and transaction data </div><div><span class="Apple-tab-span" style="white-space:pre"> </span> and indexes</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>6) Always create tablespaces with NO FS option to avoid double caching</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>7) Always better to use STMM for better memory management</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>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)</div><div><br /></div><div><b><span class="Apple-style-span" >SCHEMA DESING</span></b></div><div><span class="Apple-tab-span" style="white-space:pre"> </span>1) Avoid having foreign keys if your application takes care of integrity</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>2) Always index the columns used for</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>i) WHERE clause filter</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>ii) JOIN predicates</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>iii) SORT operation (ORDER BY, MAX(), MIN() etc)</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>3) Always keep the STATISTICS updated for all the tables to ensure index scans</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>4) If the cardinality of any table changes abruptly, consider making the table VOLATILE. this will help you ensure and index scan</div><div><span class="Apple-tab-span" style="white-space:pre"> </span></div><div><br /></div><div><b><span class="Apple-style-span" >APPLICATION DESIGN</span></b></div><div><br /></div><div><span class="Apple-tab-span" style="white-space:pre"> </span>1) Always use ANSI JOIN operator for joining tables. This avoids any accidental cross product</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>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</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>3) Avoid doing JOINs with VIEWs, instead join with tables, this will avoid JOINing with any unnecessary underlaying table of view.</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>4) Avoid self joins and prefer case statements over self joins if possible</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>5) Use FETCH FIRST N rows to optimize selection for few rows if using a packaged application set OPTIMIZEFORNROWS in db2cli.ini.</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>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</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>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.</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>8) Have a better concurrency in application</div><div><br /></div><div><b><span class="Apple-style-span" >CONCURRENCY CONTROL</span></b></div><div>To improve the concurrency of the application use following:</div><div><br /></div><div><span class="Apple-tab-span" style="white-space:pre"> </span>1) Use Cursor Stability Isolation Level</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>2) Use LOCKSIZE row for all the tables</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>3) Use optimistic locking by using ROW CHANGE TIMESTAMP expression</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>4) Set following parameter</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>i) db2set db2_skipinterted=ON</div><div><span class="Apple-tab-span" style="white-space: pre; "> </span>ii) db2set db2_skipdeleted=ON</div><div><span class="Apple-tab-span" style="white-space: pre; "> </span>iii) db2set db2_evaluncommitted=ON</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>iv) db cfg cur_commit</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>5) Avoid long transaction and ensure the operations are committed regularly</div><div><br /></div><div>I will wind up this post with a note that setting this right will always help you avoiding any performance issues in future....</div><div><br /></div><div><br /></div><div>I will try to come up with some SQL Tuning Tips...</div><div><br /></div>Sameerhttp://www.blogger.com/profile/04380280581835257131noreply@blogger.com0