 | |
#1
 
| |||
| |||
|
Index not used by merge -
09-13-2011
, 02:08 PM
#2
| |||
| |||
|
|
Hi all, DB2 V9.5 Fixpack 7 LUW. My company got a application that receives a lot of information about softwares installed in desktops ( 80k desktops ) Recently, we detected that load average of server was very high. After investigation, we found the bad guy: a merge statement *with parameter marker. The access plan, returns a Total cost of 3282.93 ( using parameter marker ), but if we use literal, in this case the cost decreases to 50.0416. Why the merge ( with parameter marker ) do not use the *unique index created? Can you help? Following, there are the informations about access plan, merge and indexes. https://v2.automatos.com/download/Query_Merge.rar Thanks Bruno. |
in your first query, the values in the markers are unknow. This values
may be different, and then you need search 240 values in the table.
This means 2 pages read per value using the index (total 480 pages and
24000 timerons), and 795 pages (3225 timerons) using the table.
In the second query, all the values are the same, and this mean that
you need to read only 2 pages. In this case read from index is better.
The asynchronous read of the table do that the cost of read from table
is less than read from index.
The question is why read two pages in the index have a cost of 50
timerons. I think that this is very high. May be that the index needs
a reorg, or the configuration of the IDX tablespace is not good.
You can use a dedicated tablespace and bufferpool for indexes, and try
to keep indexes in memory.
Ricardo Marcos Alonso
www.blogdb2.com
#3
| |||
| |||
|
|
On Sep 13, 9:08*pm, Bruno Almeida <bruno.alme... (AT) automatos (DOT) com> wrote: Hi all, DB2 V9.5 Fixpack 7 LUW. My company got a application that receives a lot of information about softwares installed in desktops ( 80k desktops ) Recently, we detected that load average of server was very high. After investigation, we found the bad guy: a merge statement *with parameter marker. The access plan, returns a Total cost of 3282.93 ( using parameter marker ), but if we use literal, in this case the cost decreases to 50.0416. Why the merge ( with parameter marker ) do not use the *unique index created? Can you help? Following, there are the informations about access plan, merge and indexes. https://v2.automatos.com/download/Query_Merge.rar Thanks Bruno. Hi, in your first query, the values in the markers are unknow. This values may be different, and then you need search 240 values in the table. This means 2 pages read per value using the index (total 480 pages and 24000 timerons), and 795 pages (3225 timerons) using the table. In the second query, all the values are the same, and this mean that you need to read only 2 pages. In this case read from index is better. The asynchronous read of the table do that the cost of read from table is less than read from index. The question is why read two pages in the index have a cost of 50 timerons. I think that this is very high. May be that the index needs a reorg, or the configuration of the IDX tablespace is not good. You can use a dedicated tablespace and bufferpool for indexes, and try to keep indexes in memory. Ricardo Marcos Alonsowww.blogdb2.com |
#4
| |||
| |||
|
|
On Sep 14, 12:46*am, rmarcosa <rmarc... (AT) gmail (DOT) com> wrote: On Sep 13, 9:08*pm, Bruno Almeida <bruno.alme... (AT) automatos (DOT) com> wrote: Hi all, DB2 V9.5 Fixpack 7 LUW. My company got a application that receives a lot of information about softwares installed in desktops ( 80k desktops ) Recently, we detected that load average of server was very high. After investigation, we found the bad guy: a merge statement *with parameter marker. The access plan, returns a Total cost of 3282.93 ( using parameter marker ), but if we use literal, in this case the cost decreases to 50.0416. Why the merge ( with parameter marker ) do not use the *unique index created? Can you help? Following, there are the informations about access plan, merge and indexes. https://v2.automatos.com/download/Query_Merge.rar Thanks Bruno. Hi, in your first query, the values in the markers are unknow. This values may be different, and then you need search 240 values in the table. This means 2 pages read per value using the index (total 480 pages and 24000 timerons), and 795 pages (3225 timerons) using the table. In the second query, all the values are the same, and this mean that you need to read only 2 pages. In this case read from index is better. The asynchronous read of the table do that the cost of read from table is less than read from index. The question is why read two pages in the index have a cost of 50 timerons. I think that this is very high. May be that the index needs a reorg, or the configuration of the IDX tablespace is not good. You can use a dedicated tablespace and bufferpool for indexes, and try to keep indexes in memory. Ricardo Marcos Alonsowww.blogdb2.com besides this you can use char(255) instead of varchar(255). |
Thank´s for reply.
Question: Is there a easy way to move a table to another tablespace?
Like:
alter table X alter tablespace <newtablespace>
This table has a lot of dependents, drop / recreate it will be hard
work.
Bruno.
#5
| |||
| |||
|
|
On 14 set, 06:26, rmarcosa <rmarc... (AT) gmail (DOT) com> wrote: On Sep 14, 12:46*am, rmarcosa <rmarc... (AT) gmail (DOT) com> wrote: On Sep 13, 9:08*pm, Bruno Almeida <bruno.alme... (AT) automatos (DOT) com wrote : Hi all, DB2 V9.5 Fixpack 7 LUW. My company got a application that receives a lot of information about softwares installed in desktops ( 80k desktops ) Recently, we detected that load average of server was very high. After investigation, we found the bad guy: a merge statement *with parameter marker. The access plan, returns a Total cost of 3282.93 ( using parameter marker ), but if we use literal, in this case the cost decreases to 50.0416. Why the merge ( with parameter marker ) do not use the *unique inde x created? Can you help? Following, there are the informations about access plan, merge and indexes. https://v2.automatos.com/download/Query_Merge.rar Thanks Bruno. Hi, in your first query, the values in the markers are unknow. This values may be different, and then you need search 240 values in the table. This means 2 pages read per value using the index (total 480 pages and 24000 timerons), and 795 pages (3225 timerons) using the table. In the second query, all the values are the same, and this mean that you need to read only 2 pages. In this case read from index is better. The asynchronous read of the table do that the cost of read from table is less than read from index. The question is why read two pages in the index have a cost of 50 timerons. I think that this is very high. May be that the index needs a reorg, or the configuration of the IDX tablespace is not good. You can use a dedicated tablespace and bufferpool for indexes, and try to keep indexes in memory. Ricardo Marcos Alonsowww.blogdb2.com besides this you can use char(255) instead of varchar(255). Ricardo, Thank´s for reply. Question: Is there a easy way to move a table to another tablespace? Like: alter table X alter tablespace <newtablespace This table has a lot of dependents, drop / recreate it will be hard work. Bruno. |
you may want to have a look at the ADMIN_MOVE_TABLE procedure for this:
http://publib.boulder.ibm.com/infoce.../com.ibm.db2.l
uw.sql.rtn.doc/doc/r0055069.html
It has quite a lot restrictions though (shown near the end of the
document above), so you should carefully check (and test!) whether it
will work in your situation.
One of the restrictions is that you need to drop/recreate foreign
keys...
HTH.
Cheers!
--
Jeroen
#6
| |||
| |||
|
|
Bruno Almeida <bruno.alme... (AT) automatos (DOT) com> wrote innews:73eba63b-eb0d-4d38-b9a5-cc55cf81773c (AT) l7g2000vbz (DOT) googlegroups.com: On 14 set, 06:26, rmarcosa <rmarc... (AT) gmail (DOT) com> wrote: On Sep 14, 12:46*am, rmarcosa <rmarc... (AT) gmail (DOT) com> wrote: On Sep 13, 9:08*pm, Bruno Almeida <bruno.alme... (AT) automatos (DOT) com wrote : Hi all, DB2 V9.5 Fixpack 7 LUW. My company got a application that receives a lot of information about softwares installed in desktops ( 80k desktops ) Recently, we detected that load average of server was very high. After investigation, we found the bad guy: a merge statement *with parameter marker. The access plan, returns a Total cost of 3282.93 ( using parameter marker ), but if we use literal, in this case the cost decreases to 50.0416. Why the merge ( with parameter marker ) do not use the *unique inde x created? Can you help? Following, there are the informations about access plan, merge and indexes. https://v2.automatos.com/download/Query_Merge.rar Thanks Bruno. Hi, in your first query, the values in the markers are unknow. This values may be different, and then you need search 240 values in the table. This means 2 pages read per value using the index (total 480 pages and 24000 timerons), and 795 pages (3225 timerons) using the table. In the second query, all the values are the same, and this mean that you need to read only 2 pages. In this case read from index is better. The asynchronous read of the table do that the cost of read from table is less than read from index. The question is why read two pages in the index have a cost of 50 timerons. I think that this is very high. May be that the index needs a reorg, or the configuration of the IDX tablespace is not good. You can use a dedicated tablespace and bufferpool for indexes, and try to keep indexes in memory. Ricardo Marcos Alonsowww.blogdb2.com besides this you can use char(255) instead of varchar(255). Ricardo, Thank´s for reply. Question: Is there a easy way to move a table to another tablespace? Like: alter table X alter tablespace <newtablespace This table has a lot of dependents, drop / recreate it will be hard work. Bruno. Hi Bruno, you may want to have a look at the ADMIN_MOVE_TABLE procedure for this: http://publib.boulder.ibm.com/infoce.../com.ibm.db2.l uw.sql.rtn.doc/doc/r0055069.html It has quite a lot restrictions though (shown near the end of the document above), so you should carefully check (and test!) whether it will work in your situation. One of the restrictions is that you need to drop/recreate foreign keys... HTH. Cheers! -- Jeroen |
V9.5. Export data, drop the constraints, drop the table, recreate it,
load data and recreate the constraints.
Ricardo Marcos Alonso
http://www.blogdb2.com
#7
| |||
| |||
|
|
On Sep 14, 11:51*pm, TheBoss <TheB... (AT) invalid (DOT) nl> wrote: Bruno Almeida <bruno.alme... (AT) automatos (DOT) com> wrote innews:73eba63b-eb0d-4d 38-b9a5-cc55cf81773c (AT) l7g2000vbz...oglegroups.com: On 14 set, 06:26, rmarcosa <rmarc... (AT) gmail (DOT) com> wrote: On Sep 14, 12:46*am, rmarcosa <rmarc... (AT) gmail (DOT) com> wrote: On Sep 13, 9:08*pm, Bruno Almeida <bruno.alme... (AT) automatos (DOT) com wrote : Hi all, DB2 V9.5 Fixpack 7 LUW. My company got a application that receives a lot of information about softwares installed in desktops ( 80k desktops ) Recently, we detected that load average of server was very high. After investigation, we found the bad guy: a merge statement *with parameter marker. The access plan, returns a Total cost of 3282.93 ( using parameter marker ), but if we use literal, in this case the cost decreases to 50.0416. Why the merge ( with parameter marker ) do not use the *unique inde x created? Can you help? Following, there are the informations about access plan, merge and indexes. https://v2.automatos.com/download/Query_Merge.rar Thanks Bruno. Hi, in your first query, the values in the markers are unknow. This values may be different, and then you need search 240 values in the table. This means 2 pages read per value using the index (total 480 pages and 24000 timerons), and 795 pages (3225 timerons) using the table. In the second query, all the values are the same, and this mean that you need to read only 2 pages. In this case read from index is better. The asynchronous read of the table do that the cost of read from table is less than read from index. The question is why read two pages in the index have a cost of 50 timerons. I think that this is very high. May be that the index needs a reorg, or the configuration of the IDX tablespace is not good. You can use a dedicated tablespace and bufferpool for indexes, and try to keep indexes in memory. Ricardo Marcos Alonsowww.blogdb2.com besides this you can use char(255) instead of varchar(255). Ricardo, Thank´s for reply. Question: Is there a easy way to move a table to another tablespace? Like: alter table X alter tablespace <newtablespace This table has a lot of dependents, drop / recreate it will be hard work. Bruno. Hi Bruno, you may want to have a look at the ADMIN_MOVE_TABLE procedure for this: http://publib.boulder.ibm.com/infoce...ic/com.ibm.db2 .l uw.sql.rtn.doc/doc/r0055069.html It has quite a lot restrictions though (shown near the end of the document above), so you should carefully check (and test!) whether it will work in your situation. One of the restrictions is that you need to drop/recreate foreign keys... HTH. Cheers! -- Jeroen This is only for V9.7. I don't know an easy way to move tables for V9.5. Export data, drop the constraints, drop the table, recreate it, load data and recreate the constraints. Ricardo Marcos Alonso http://www.blogdb2.com |
I must have misinterpreted OP's statement he is on 9.5 FP7 to read 9.7.
To make up for my mistake I want to point the OP to an earlier thread on
this very same subject:
http://groups.google.com/group/comp.databases.ibm-
db2/browse_frm/thread/5f529382fbe465d7/f2eb55a787e60fb6
It refers to an article by Serge Rielau that may help:
http://www.ibm.com/developerworks/da...echarticle/dm-
0602rielau/
It also refers to a discussion on the DevWorks forum that may give
additional info:
http://www.ibm.com/developerworks/fo...hreadID=214937
&tstart=
Cheers!
--
Jeroen
 |
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.