queries slow after migration
After migrating from IDS 7.31 to 11.50.FC8, a user found some queries much slower, with the optimizer choosing a different table join order. Advice given: drop old distributions and rerun UPDATE STATISTICS per the Performance Guide, rebuild 7.31-era attached indexes as detached ones, then repack/shrink the tables. Rebuilding indexes didn't help; an {+ORDERED} directive cut one query from ~4 minutes to seconds. The explain output showed estimated vs. actual row counts wildly off, so Art Kagel and John Miller concluded the statistics/distributions were inadequate and recommended a fuller update stats run (e.g. the dostats utility). The thread ends with the user posting his schema and stats commands; no confirmed fix is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I, Recently, I've migrated our database informix from 7.31 to informix 11.50 FC8 (I've not migrated to 11.70 because I have an applicaction that is not validated with 11.70). The stage is on QA environment, before migrate on production. After the migration on QA, some queries are slow. The explain shows that the order of the tables changed. My question is: Is this "normal" ? The only way of solution is change the queries ?. In this case, is there the possibility that many queries are slow, after migrate on production ? Please, provide me any clues according your experience. Thanks in advance
Overall 11.50 is significantly faster than 7.31. Did you drop all of your distributions after the migration and run update stats as recommended in the Performance Guide afterwards? Have you looked to see if there are any old attached indexes on your tables that you should drop and recreate detached (detached indexes are much faster than attached for all but very small tables)? Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, May 24, 2011 at 7:52 PM, ROGER VILCA <rvilca@luzdelsur.com.pe>wrote: > I, > Recently, I've migrated our database informix from 7.31 to informix 11.50 > FC8 > (I've not migrated to 11.70 because I have an applicaction that is not > validated with 11.70). The stage is on QA environment, before migrate on > production. > After the migration on QA, some queries are slow. The explain shows that > the > order of the tables changed. > My question is: > Is this "normal" ? The only way of solution is change the queries ?. In > this > case, is there the possibility that many queries are slow, after migrate on > production ? > Please, provide me any clues according your experience. > Thanks in advance > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --bcaec51a7d4cea123504a4104a74
Yes, I did drop distributions.
Almost all my indexes are attached (after the migration, the dbschema shows
"in table"). The documentation (release notes 11.50 xC8) says: Indexes created
in version 7.31 remain attached until you rebuild them.
Ok, I'm going to rebuild the indexes (first, for tables in the queries slow)
and I'll tell you the results.
Thanks.
After you rebuild the indexes you may want to pack the tables to remove the
index page extents and make the table more efficient on disk:
execute function task( 'table repack shrink', 'tablename', 'databasename','owner' );
This can be done online. The shrink keyword is optional, it will return
unused pages within the table that were freed up by dropping the index to
the free space pool for other tables to use.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Wed, May 25, 2011 at 12:54 PM, ROGER VILCA <rvilca@luzdelsur.com.pe>wrote:
> Yes, I did drop distributions.
> Almost all my indexes are attached (after the migration, the dbschema shows
> "in table"). The documentation (release notes 11.50 xC8) says: Indexes
> created
> in version 7.31 remain attached until you rebuild them.
> Ok, I'm going to rebuild the indexes (first, for tables in the queries
> slow)
> and I'll tell you the results.
> Thanks.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--bcaec51a7d4c48699f04a41d1075
I don't know how large are your tables, and if you have maintenance window to
do that.
If the tables are large and if you have a maintenance window, I suggest you to
drop the index, execute the repack/shrink that Art suggested you and after
that, recreate the indexes.
If the tables are small or you don't have a maintenance window, then I suggest
you to drop and recreate indexes online and after execute the commands repack
and shrink.
Celso Cabral Coimbra
Administrador de Banco de Dados
ClearTech Ltda
"Trust at the heart of Communications"
Tel. (11) 3576-4509
-----Mensagem original-----
De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Em nome de Art Kagel
Enviada em: quarta-feira, 25 de maio de 2011 14:31
Para: ids@iiug.org
Assunto: Re: queries slow after migration [23829]
After you rebuild the indexes you may want to pack the tables to remove the
index page extents and make the table more efficient on disk:
execute function task( 'table repack shrink', 'tablename', 'databasename','owner' );
This can be done online. The shrink keyword is optional, it will return
unused pages within the table that were freed up by dropping the index to
the free space pool for other tables to use.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Wed, May 25, 2011 at 12:54 PM, ROGER VILCA <rvilca@luzdelsur.com.pe>wrote:
> Yes, I did drop distributions.
> Almost all my indexes are attached (after the migration, the dbschema shows
> "in table"). The documentation (release notes 11.50 xC8) says: Indexes
> created
> in version 7.31 remain attached until you rebuild them.
> Ok, I'm going to rebuild the indexes (first, for tables in the queries
> slow)
> and I'll tell you the results.
> Thanks.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--bcaec51a7d4c48699f04a41d1075
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
I've rebuilded the tables (this time with detached indexes) participating in a particular query, and the problem persists. I've noted (with set explain) that adding the directive +ORDERED, this query runs fast, but the cost is high: Original: Estimated Cost: 3200 time = 2.50 min. Adding the directive: Estimated Cost: 21930910 time = 1 sec. In conclusion, I don't have any confidence that my main queries run normally when I migrate into production ? Would I have analyze query by query ? Please help me. Thanks.
I've rebuilded the tables (this time with detached indexes) participating in a particular query, and the problem persists. I've noted that adding the directive +ORDERED, this query runs fast, but the cost is high: Original: Estimated Cost: 3200 time = 2.50 min. Adding the directive: Estimated Cost: 21930910 time = 1 sec. In conclusion, I don't have any confidence that my main queries run normally when I migrate into production ? Would I have analyze query by query ? Please help me. Thanks.
First, it would be nice to see an example query and the explain out for the query. Second, I would probably use OAT's query tracing to see which part of the query is having a problem. John F. Miller III STSM, Embedability Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 05/25/2011 06:16:44 PM: > From: > > "ROGER VILCA" <rvilca@luzdelsur.com.pe> > > To: > > ids@iiug.org > > Date: > > 05/25/2011 06:17 PM > > Subject: > > Re: queries slow after migration [23836] > > Sent by: > > ids-bounces@iiug.org > > I've rebuilded the tables (this time with detached indexes) > participating in a > particular query, and the problem persists. > I've noted (with set explain) that adding the directive +ORDERED, this query > runs fast, but the cost is high: > > Original: > Estimated Cost: 3200 > time = 2.50 min. > > Adding the directive: > Estimated Cost: 21930910 > time = 1 sec. > > In conclusion, I don't have any confidence that my main queries run normally > when I migrate into production ? Would I have analyze query by query? Please > help me. > Thanks. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
Thanks. This is the explain with no ORDERED (+4 min) and with ORDERED (aprox.
2 sec.)
QUERY: (OPTIMIZATION TIMESTAMP: 05-26-2011 10:50:08)
------
select
e.ano,e.mes,
sum(nvl(m.debe_ingreso,0)),sum(nvl(m.haber_ingreso,0))
from proyectos_de_inver p,inver_de_capital i,
con_combin b, con_movcom m, con_enccom e
where p.empresa = '7'
and p.proyecto_inversion = '001884'
and i.empresa = p.empresa
and i.proyecto_inver = p.proyecto_inversion
and b.empresa = i.empresa
and i.inversion_capital is not null
and i.inversion_capital <> ''
and b.aux_valor6 = i.inversion_capital
and m.empresa = b.empresa
and m.combinatoria = b.combinatoria
and m.tipo_comprobante not in ('A3001','A3008')
and e.empresa = m.empresa
and e.fecha = m.fecha
and e.tipo_comprobante = m.tipo_comprobante
and e.correlativo = m.correlativo
and e.estado = 'A'
and e.ano = 2011
and e.mes <= 4
group by 1,2
order by 1,2
Estimated Cost: 3187
Estimated # of Rows Returned: 1
Temporary Files Required For: Order By
1) informix.e: INDEX PATH
Filters: (informix.e.estado = 'A' AND informix.e.tipo_comprobante NOT IN
('A3001' , 'A3008' ))
(1) Index Name: xsic.i3_con_enccom
Index Keys: mes ano empresa (Key-First) (Serial, fragments: ALL)
Upper Index Filter: informix.e.mes <= 4
Index Key Filters: (informix.e.ano = 2011 ) AND
(informix.e.empresa = '7' )
INDEX_NAME = i3_con_enccom
2) informix.m: INDEX PATH
(1) Index Name: xsic.i3_con_movcom
Index Keys: correlativo tipo_comprobante fecha ano cuenta origen combinatoria
empresa (Key-First) (Serial, fragments: ALL)
Lower Index Filter: ((informix.e.correlativo = informix.m.correlativo AND
informix.e.fecha = informix.m.fecha ) AND informix.e.tipo_comprobante =
informix.m.tipo_comprobante )
Index Key Filters: (informix.m.empresa = '7' )
INDEX_NAME = i3_con_movcom
NESTED LOOP JOIN
3) informix.p: INDEX PATH
(1) Index Name: xsic.i_ep_proy_inver
Index Keys: proyecto_inversion empresa (Key-Only) (Serial, fragments: ALL)
Lower Index Filter: (informix.p.empresa = informix.m.empresa AND
informix.p.proyecto_inversion = '001884' )
INDEX_NAME = i_ep_proy_inver
NESTED LOOP JOIN
4) informix.b: INDEX PATH
Filters: informix.b.aux_valor6 != ''
(1) Index Name: xsic.i100_con_combin
Index Keys: combinatoria empresa (Serial, fragments: ALL)
Lower Index Filter: (informix.m.combinatoria = informix.b.combinatoria AND
informix.m.empresa = informix.b.empresa )
INDEX_NAME = i100_con_combin
NESTED LOOP JOIN
5) informix.i: INDEX PATH
Filters: (informix.b.aux_valor6 = informix.i.inversion_capital AND
informix.i.inversion_capital != '' )
(1) Index Name: xsic.proyecto_inver
Index Keys: proyecto_inver empresa (Serial, fragments: ALL)
Lower Index Filter: (informix.i.proyecto_inver = informix.p.proyecto_inversion
AND informix.i.empresa = informix.e.empresa )
INDEX_NAME = proyecto_inver
NESTED LOOP JOIN
DB_LOCALE = en_US.819
SESSION COLLATION = es_ES.819
Query statistics:
-----------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 e
t2 m
t3 p
t4 b
t5 i
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 2768 1020 58414 00:02.46 2253
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t2 256959 8077986 257749 02:28.46 1
type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 256959 7 02:31.28 2934
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t3 256959 1 256959 00:09.86 0
type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 256959 7 02:42.07 2950
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t4 20366 149874 256959 00:36.15 1
type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 20366 6 03:18.80 2957
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t5 1170 134 2830874 00:42.98 37
type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 1170 12 04:01.84 3187
type rows_prod est_rows rows_cons time est_cost
------------------------------------------------------------
group 4 1 1170 04:01.88 15
type rows_sort est_rows rows_cons time est_cost
------------------------------------------------------------
sort 4 1 4 04:01.88 0
QUERY: (OPTIMIZATION TIMESTAMP: 05-26-2011 11:37:11)
------
select {+ORDERED}
e.ano,e.mes,
sum(nvl(m.debe_ingreso,0)),sum(nvl(m.haber_ingreso,0))
from proyectos_de_inver p,inver_de_capital i,
con_combin b, con_movcom m, con_enccom e
where p.empresa = '7'
and p.proyecto_inversion = '001884'
and i.empresa = p.empresa
and i.proyecto_inver = p.proyecto_inversion
and b.empresa = i.empresa
and i.inversion_capital is not null
and i.inversion_capital <> ''
and b.aux_valor6 = i.inversion_capital
and m.empresa = b.empresa
and m.combinatoria = b.combinatoria
and m.tipo_comprobante not in ('A3001','A3008')
and e.empresa = m.empresa
and e.fecha = m.fecha
and e.tipo_comprobante = m.tipo_comprobante
and e.correlativo = m.correlativo
and e.estado = 'A'
and e.ano = 2011
and e.mes <= 4
group by 1,2
order by 1,2
DIRECTIVES FOLLOWED:ORDERED
DIRECTIVES NOT FOLLOWED:
Estimated Cost: 21930910
Estimated # of Rows Returned: 1
Temporary Files Required For: Order By Group By
1) informix.p: INDEX PATH
(1) Index Name: xsic.i_ep_proy_inver
Index Keys: proyecto_inversion empresa (Key-Only) (Serial, fragments: ALL)
Lower Index Filter: (informix.p.proyecto_inversion = '001884' AND
informix.p.empresa = '7' )
INDEX_NAME = i_ep_proy_inver
2) informix.i: INDEX PATH
Filters: informix.i.inversion_capital != ''
(1) Index Name: xsic.proyecto_inver
Index Keys: proyecto_inver empresa (Serial, fragments: ALL)
Lower Index Filter: (informix.i.proyecto_inver = informix.p.proyecto_inversion
AND informix.i.empresa = informix.p.empresa )
INDEX_NAME = proyecto_inver
NESTED LOOP JOIN
3) informix.b: INDEX PATH
(1) Index Name: xsic.i6_con_combin
Index Keys: aux_valor6 empresa (Serial, fragments: ALL)
Lower Index Filter: (informix.b.aux_valor6 = informix.i.inversion_capital AND
informix.p.empresa = informix.b.empresa )
INDEX_NAME = i6_con_combin
NESTED LOOP JOIN
4) informix.m: INDEX PATH
Filters: informix.m.tipo_comprobante NOT IN ('A3001' , 'A3008' )
(1) Index Name: xsic.i2_con_movcom
Index Keys: combinatoria empresa (Serial, fragments: ALL)
Lower Index F
The estimated rows versus actual rows produced in the explain details are
WAY OFF. That means that your update statistics levels are too low and the
optimizer does not have enough information to make good decisions. You need
to rerun the update statistics using the full protocol of commands
recommended in the Performance Guide or just get my dostats utility (in the
utils2_ak package in the IIUG Software Repository) and run it.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Thu, May 26, 2011 at 12:41 PM, ROGER VILCA <rvilca@luzdelsur.com.pe>wrote:
> Thanks. This is the explain with no ORDERED (+4 min) and with ORDERED
> (aprox.
> 2 sec.)
>
> QUERY: (OPTIMIZATION TIMESTAMP: 05-26-2011 10:50:08)
> ------
> select
> e.ano,e.mes,
> sum(nvl(m.debe_ingreso,0)),sum(nvl(m.haber_ingreso,0))
> from proyectos_de_inver p,inver_de_capital i,
> con_combin b, con_movcom m, con_enccom e
> where p.empresa = '7'
> and p.proyecto_inversion = '001884'
> and i.empresa = p.empresa
> and i.proyecto_inver = p.proyecto_inversion
> and b.empresa = i.empresa
> and i.inversion_capital is not null
> and i.inversion_capital <> ''
> and b.aux_valor6 = i.inversion_capital
> and m.empresa = b.empresa
> and m.combinatoria = b.combinatoria
> and m.tipo_comprobante not in ('A3001','A3008')
> and e.empresa = m.empresa
> and e.fecha = m.fecha
> and e.tipo_comprobante = m.tipo_comprobante
> and e.correlativo = m.correlativo
> and e.estado = 'A'
> and e.ano = 2011
> and e.mes <= 4
> group by 1,2
> order by 1,2
>
> Estimated Cost: 3187
> Estimated # of Rows Returned: 1
> Temporary Files Required For: Order By
>
> 1) informix.e: INDEX PATH
>
> Filters: (informix.e.estado = 'A' AND informix.e.tipo_comprobante NOT IN
> ('A3001' , 'A3008' ))
>
> (1) Index Name: xsic.i3_con_enccom
>
> Index Keys: mes ano empresa (Key-First) (Serial, fragments: ALL)
>
> Upper Index Filter: informix.e.mes <= 4
>
> Index Key Filters: (informix.e.ano = 2011 ) AND
>
> (informix.e.empresa = '7' )
>
> INDEX_NAME = i3_con_enccom
>
> 2) informix.m: INDEX PATH
>
> (1) Index Name: xsic.i3_con_movcom
>
> Index Keys: correlativo tipo_comprobante fecha ano cuenta origen
> combinatoria
> empresa (Key-First) (Serial, fragments: ALL)
>
> Lower Index Filter: ((informix.e.correlativo = informix.m.correlativo AND
> informix.e.fecha = informix.m.fecha ) AND informix.e.tipo_comprobante =
> informix.m.tipo_comprobante )
>
> Index Key Filters: (informix.m.empresa = '7' )
>
> INDEX_NAME = i3_con_movcom
> NESTED LOOP JOIN
>
> 3) informix.p: INDEX PATH
>
> (1) Index Name: xsic.i_ep_proy_inver
>
> Index Keys: proyecto_inversion empresa (Key-Only) (Serial, fragments: ALL)
>
> Lower Index Filter: (informix.p.empresa = informix.m.empresa AND
> informix.p.proyecto_inversion = '001884' )
>
> INDEX_NAME = i_ep_proy_inver
> NESTED LOOP JOIN
>
> 4) informix.b: INDEX PATH
>
> Filters: informix.b.aux_valor6 != ''
>
> (1) Index Name: xsic.i100_con_combin
>
> Index Keys: combinatoria empresa (Serial, fragments: ALL)
>
> Lower Index Filter: (informix.m.combinatoria = informix.b.combinatoria AND
> informix.m.empresa = informix.b.empresa )
>
> INDEX_NAME = i100_con_combin
> NESTED LOOP JOIN
>
> 5) informix.i: INDEX PATH
>
> Filters: (informix.b.aux_valor6 = informix.i.inversion_capital AND
> informix.i.inversion_capital != '' )
>
> (1) Index Name: xsic.proyecto_inver
>
> Index Keys: proyecto_inver empresa (Serial, fragments: ALL)
>
> Lower Index Filter: (informix.i.proyecto_inver =
> informix.p.proyecto_inversion
> AND informix.i.empresa = informix.e.empresa )
>
> INDEX_NAME = proyecto_inver
> NESTED LOOP JOIN
>
> DB_LOCALE = en_US.819
> SESSION COLLATION = es_ES.819
>
> Query statistics:
> -----------------
>
> Table map :
> ----------------------------
> Internal name Table name
> ----------------------------
> t1 e
> t2 m
> t3 p
> t4 b
> t5 i
>
> type table rows_prod est_rows rows_scan time est_cost
> -------------------------------------------------------------------
> scan t1 2768 1020 58414 00:02.46 2253
>
> type table rows_prod est_rows rows_scan time est_cost
> -------------------------------------------------------------------
> scan t2 256959 8077986 257749 02:28.46 1
>
> type rows_prod est_rows time est_cost
> -------------------------------------------------
> nljoin 256959 7 02:31.28 2934
>
> type table rows_prod est_rows rows_scan time est_cost
> -------------------------------------------------------------------
> scan t3 256959 1 256959 00:09.86 0
>
> type rows_prod est_rows time est_cost
> -------------------------------------------------
> nljoin 256959 7 02:42.07 2950
>
> type table rows_prod est_rows rows_scan time est_cost
> -------------------------------------------------------------------
> scan t4 20366 149874 256959 00:36.15 1
>
> type rows_prod est_rows time est_cost
> -------------------------------------------------
> nljoin 20366 6 03:18.80 2957
>
> type table rows_prod est_rows rows_scan time est_cost
> -------------------------------------------------------------------
> scan t5 1170 134 2830874 00:42.98 37
>
> type rows_prod est_rows time est_cost
> -------------------------------------------------
> nljoin 1170 12 04:01.84 3187
>
> type rows_prod est_rows rows_cons time est_cost
> ------------------------------------------------------------
> group 4 1 1170 04:01.88 15
>
> type rows_sort est_rows rows_cons time est_cost
> ------------------------------------------------------------
> sort 4 1 4 04:01.88 0
>
> QUERY: (OPTIMIZATION TIMESTAMP: 05-26-2011 11:37:11)
> ------
> select {+ORDERED}
> e.ano,e.mes,
> sum(nvl(m.debe_ingreso,0)),sum(nvl(m.haber_ingreso,0))
> from proyectos_de_inver p,inver_de_capital i,
> con_combin b, con_movcom m, con_enccom e
> where p.empresa = '7'
> and p.proyecto_inversion = '001884'
> and i.empresa = p.empresa
> and i.proyecto_inver = p.proyecto_inversion
> and b.empresa = i.empresa
> and i.inversion_capital is not null
> and i.inversion_capital <> ''
> and b.aux_valor6 = i.inversion_capital
> and m.empresa = b.empresa
> and m.combinatoria = b.combinatoria
> and m.tipo_comprobante not in ('A3001','A3008')
> and e.empresa = m.empresa
> and e.fecha = m.fecha
> and e.tipo_comprobante = m.tipo_comprobante
> and e.correlativo = m.correlativo
> and e.estado = 'A'
> and e.ano = 2011
> and e.mes <= 4
> group by 1,2
> order by 1,2
>
> DIRECTIVES FOLLOWED:> O
After looking at your statistics in the set explain I agree with Art. Please make sure that the tables have good statistics and distributions. John F. Miller III STSM, Embedability Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 05/26/2011 10:08:54 AM: > [image removed] > > Re: queries slow after migration [23847] > > Art Kagel > > to: > > ids > > 05/26/2011 10:10 AM > > Sent by: > > ids-bounces@iiug.org > > Please respond to ids > > The estimated rows versus actual rows produced in the explain details are > WAY OFF. That means that your update statistics levels are too low and the > optimizer does not have enough information to make good decisions. You need > to rerun the update statistics using the full protocol of commands > recommended in the Performance Guide or just get my dostats utility (in the > utils2_ak package in the IIUG Software Repository) and run it. > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions and > do not reflect on my employer, Advanced DataTools, the IIUG, nor any other > organization with which I am associated either explicitly, implicitly, or by > inference. Neither do those opinions reflect those of other individuals > affiliated with any entity with which I am affiliated nor those of the > entities themselves. > > On Thu, May 26, 2011 at 12:41 PM, ROGER VILCA <rvilca@luzdelsur.com.pe>wrote: > > > Thanks. This is the explain with no ORDERED (+4 min) and with ORDERED > > (aprox. > > 2 sec.) > > > > QUERY: (OPTIMIZATION TIMESTAMP: 05-26-2011 10:50:08) > > ------ > > select > > e.ano,e.mes, > > sum(nvl(m.debe_ingreso,0)),sum(nvl(m.haber_ingreso,0)) > > from proyectos_de_inver p,inver_de_capital i, > > con_combin b, con_movcom m, con_enccom e > > where p.empresa = '7' > > and p.proyecto_inversion = '001884' > > and i.empresa = p.empresa > > and i.proyecto_inver = p.proyecto_inversion > > and b.empresa = i.empresa > > and i.inversion_capital is not null > > and i.inversion_capital <> '' > > and b.aux_valor6 = i.inversion_capital > > and m.empresa = b.empresa > > and m.combinatoria = b.combinatoria > > and m.tipo_comprobante not in ('A3001','A3008') > > and e.empresa = m.empresa > > and e.fecha = m.fecha > > and e.tipo_comprobante = m.tipo_comprobante > > and e.correlativo = m.correlativo > > and e.estado = 'A' > > and e.ano = 2011 > > and e.mes <= 4 > > group by 1,2 > > order by 1,2 > > > > Estimated Cost: 3187 > > Estimated # of Rows Returned: 1 > > Temporary Files Required For: Order By > > > > 1) informix.e: INDEX PATH > > > > Filters: (informix.e.estado = 'A' AND informix.e.tipo_comprobante NOT IN > > ('A3001' , 'A3008' )) > > > > (1) Index Name: xsic.i3_con_enccom > > > > Index Keys: mes ano empresa (Key-First) (Serial, fragments: ALL) > > > > Upper Index Filter: informix.e.mes <= 4 > > > > Index Key Filters: (informix.e.ano = 2011 ) AND > > > > (informix.e.empresa = '7' ) > > > > INDEX_NAME = i3_con_enccom > > > > 2) informix.m: INDEX PATH > > > > (1) Index Name: xsic.i3_con_movcom > > > > Index Keys: correlativo tipo_comprobante fecha ano cuenta origen > > combinatoria > > empresa (Key-First) (Serial, fragments: ALL) > > > > Lower Index Filter: ((informix.e.correlativo = informix.m.correlativo AND > > informix.e.fecha = informix.m.fecha ) AND informix.e.tipo_comprobante = > > informix.m.tipo_comprobante ) > > > > Index Key Filters: (informix.m.empresa = '7' ) > > > > INDEX_NAME = i3_con_movcom > > NESTED LOOP JOIN > > > > 3) informix.p: INDEX PATH > > > > (1) Index Name: xsic.i_ep_proy_inver > > > > Index Keys: proyecto_inversion empresa (Key-Only) (Serial, fragments: ALL) > > > > Lower Index Filter: (informix.p.empresa = informix.m.empresa AND > > informix.p.proyecto_inversion = '001884' ) > > > > INDEX_NAME = i_ep_proy_inver > > NESTED LOOP JOIN > > > > 4) informix.b: INDEX PATH > > > > Filters: informix.b.aux_valor6 != '' > > > > (1) Index Name: xsic.i100_con_combin > > > > Index Keys: combinatoria empresa (Serial, fragments: ALL) > > > > Lower Index Filter: (informix.m.combinatoria = informix.b.combinatoria AND > > informix.m.empresa = informix.b.empresa ) > > > > INDEX_NAME = i100_con_combin > > NESTED LOOP JOIN > > > > 5) informix.i: INDEX PATH > > > > Filters: (informix.b.aux_valor6 = informix.i.inversion_capital AND > > informix.i.inversion_capital != '' ) > > > > (1) Index Name: xsic.proyecto_inver > > > > Index Keys: proyecto_inver empresa (Serial, fragments: ALL) > > > > Lower Index Filter: (informix.i.proyecto_inver = > > informix.p.proyecto_inversion > > AND informix.i.empresa = informix.e.empresa ) > > > > INDEX_NAME = proyecto_inver > > NESTED LOOP JOIN > > > > DB_LOCALE = en_US.819 > > SESSION COLLATION = es_ES.819 > > > > Query statistics: > > ----------------- > > > > Table map : > > ---------------------------- > > Internal name Table name > > ---------------------------- > > t1 e > > t2 m > > t3 p > > t4 b > > t5 i > > > > type table rows_prod est_rows rows_scan time est_cost > > ------------------------------------------------------------------- > > scan t1 2768 1020 58414 00:02.46 2253 > > > > type table rows_prod est_rows rows_scan time est_cost > > ------------------------------------------------------------------- > > scan t2 256959 8077986 257749 02:28.46 1 > > > > type rows_prod est_rows time est_cost > > ------------------------------------------------- > > nljoin 256959 7 02:31.28 2934 > > > > type table rows_prod est_rows rows_scan time est_cost > > ------------------------------------------------------------------- > > scan t3 256959 1 256959 00:09.86 0 > > > > type rows_prod est_rows time est_cost > > ------------------------------------------------- > > nljoin 256959 7 02:42.07 2950 > > > > type table rows_prod est_rows rows_scan time est_cost > > ------------------------------------------------------------------- > > scan t4 20366 149874 256959 00:36.15 1 > > > > type rows_prod est_rows time est_cost > > ------------------------------------------------- > > nljoin 20366 6 03:18.80 2957 > > > > type table rows_prod est_rows rows_scan time est_cost > > ------------------------------------------------------------------- > > scan t5 1170 134 2830874 00:42.98 37 > > > > type rows_prod est_rows time est_cost > > ------------------------------------------------- > > nljoin 1170 12 04:01.84 3187 > > > > type rows_prod est_rows rows_cons time est_cost > > ------------------------------------------------------------ > > group 4 1 1170 04:01.88 15 > > > > type rows_sort est_rows rows_cons time est_cost > > -----------------------------------------
I run the statistics according to the guides (I think so). Please, if there is
any addicional recommendation, thanks.
*********** proyectos_de_inver **************
create table "xsic".proyectos_de_inver
(
empresa char(2) not null ,
folio_proyecto decimal(10,0),
proyecto_inversion char(6) not null ,
descripcion char(100) not null ,
observacion char(255),
item char(4) not null ,
grupo char(1) not null ,
subgrupo char(3),
jefe_proyecto char(50) not null ,
area_gestora char(10),
area_admin char(10),
ano_lib_ppto decimal(4,0) not null ,
fecha_inicio_prog date not null ,
fecha_termino_prog date not null ,
fecha_inicio_real date,
fecha_cierre_real date,
presupuesto_pi_us decimal(12,2),
presupuesto_pi_p decimal(17,2) not null ,
ano_moned_ppto_p decimal(4,0),
mes_moned_ppto_p decimal(2,0),
estado char(1) not null ,
tasa_cambio decimal(10,4),
usuario_actualiz char(30) not null ,
fecha_actualiz date not null ,
rol_dig char(10),
fec_dig date,
rol_aut char(10),
fec_aut date,
rol_apr char(10),
fec_apr date,
rol_act char(10),
tipo_proy char(10),
indppto char(1)
default 'S'
);
revoke all on "xsic".proyectos_de_inver from "public" as "xsic";
create index "xsic".i_ep_proy_inver on "xsic".proyectos_de_inver
(proyecto_inversion,empresa) using btree ;
create index "xsic".proyecto on "xsic".proyectos_de_inver (proyecto_inversion,
ano_lib_ppto,empresa) using btree ;
DATABASE sifco;
SET PDQPRIORITY 0;
UPDATE STATISTICS MEDIUM FOR TABLE proyectos_de_inver DISTRIBUTIONS ONLY;
UPDATE STATISTICS HIGH FOR TABLE proyectos_de_inver (proyecto_inversion)DISTRIBUTIONS ONLY;
UPDATE STATISTICS LOW FOR TABLE proyectos_de_inver (proyecto_inversion,empresa);
UPDATE STATISTICS HIGH FOR TABLE proyectos_de_inver ( ano_lib_ppto )DISTRIBUTIONS ONLY;
UPDATE STATISTICS HIGH FOR TABLE proyectos_de_inver ( empresa) DISTRIBUTIONSONLY;
UPDATE STATISTICS LOW FOR TABLE proyectos_de_inver (proyecto_inversion,
ano_lib_ppto, empresa);
*********** inver_de_capital ****************
create table "xsic".inver_de_capital
(
empresa char(2) not null ,
inversion_capital char(6) not null ,
descripcion char(50),
observacion char(255),
responasable char(50),
jefe_proyecto char(50) not null ,
area_ejecutora char(10),
fecha_inicio_prog date not null ,
fecha_termino_prog date not null ,
fecha_inicio_real date,
fecha_puesta_serv date,
fecha_cierre_real date,
presupuesto_tot_ic decimal(17,2) not null ,
presupuesto_tot_us decimal(17,2) not null ,
ano_moned_ppto decimal(4,0) not null ,
mes_moned_ppto decimal(2,0) not null ,
proyecto_inver char(6),
estado char(1) not null ,
usuario_actualiz char(30),
fecha_actualiz date,
correl_registro char(10),
area_gestora char(10),
actividad char(10),
usr_reg char(12),
fec_reg date,
usr_apr char(12),
fec_apr date,
usr_aut char(12),
fec_aut date,
usr_cie char(12),
fec_cie date,
rol_reg char(12),
rol_aut char(12),
rol_apr char(12),
rol_cie char(12),
rol_rec_apr char(12),
rol_rec_aut char(12),
fec_rec_apr date,
fec_rec_aut date,
glosa_cierre varchar(20),
indexceso char(1)
default 'N' not null ,
pctexceso smallint
default 0 not null ,
version char(1)
default ''
);
revoke all on "xsic".inver_de_capital from "public" as "xsic";
create index "xsic".idx_correl on "xsic".inver_de_capital (correl_registro,
empresa) using btree ;
create index "xsic".inversion_capital on "xsic".inver_de_capital
(inversion_capital,empresa) using btree ;
create index "xsic".proyecto_inver on "xsic".inver_de_capital
(proyecto_inver,empresa) using btree ;
DATABASE sifco;
SET PDQPRIORITY 0;
UPDATE STATISTICS MEDIUM FOR TABLE inver_de_capital DISTRIBUTIONS ONLY;
UPDATE STATISTICS HIGH FOR TABLE inver_de_capital (inversion_capital)DISTRIBUTIONS ONLY;
UPDATE STATISTICS LOW FOR TABLE inver_de_capital (inversion_capital, empresa);
UPDATE STATISTICS HIGH FOR TABLE inver_de_capital (proyecto_inver)DISTRIBUTIONS ONLY;
UPDATE STATISTICS LOW FOR TABLE inver_de_capital (proyecto_inver, empresa);
UPDATE STATISTICS HIGH FOR TABLE inver_de_capital (correl_registro)DISTRIBUTIONS ONLY;
UPDATE STATISTICS LOW FOR TABLE inver_de_capital (correl_registro, empresa);
*********** con_combin ***********************
create table "xsic".con_combin
(
empresa char(10) not null ,
combinatoria decimal(8,0) not null ,
aux_valor1 char(10),
aux_valor2 char(10),
aux_valor3 char(10),
aux_valor4 char(10),
aux_valor5 char(10),
aux_valor6 char(10),
aux_valor7 char(10),
aux_valor8 char(10),
aux_valor9 char(10),
aux_valor10 char(10),
aux_valor11 char(10),
aux_valor12 char(20),
aux_valor13 char(10),
aux_valor14 char(10),
aux_valor15 char(10),
aux_valor16 char(10),
aux_valor17 char(10),
aux_valor18 char(10),
aux_valor19 char(10),
aux_valor20 char(10)
);
revoke all on "xsic".con_combin from "public" as "xsic";
create index "xsic".i1_con_combin on "xsic".con_combin (aux_valor1,
empresa) using btree ;
create index "xsic".i10_con_combin on "xsic".con_combin (aux_valor10,
empresa) using btree ;
create unique index "xsic".i100_con_combin on "xsic".con_combin
(combinatoria,empresa) using btree ;
create index "xsic".i11_con_combin on "xsic".con_combin (aux_valor11,
empresa) using btree ;
create index "xsic".i12_con_combin on "xsic".con_combin (aux_valor12,
empresa) using btree ;
create index "xsic".i13_con_combin on "xsic".con_combin (aux_valor13,
empresa) using btree ;
create index "xsic".i14_con_combin on "xsic".con_combin (aux_valor14,
empresa) using btree ;
create index "xsic".i15_con_combin on "xsic".con_combin (aux_valor15,
empresa) using btree ;
create index "xsic".i16_con_combin on "xsic".con_combin (aux_valor16,
empresa) using btree ;
create index "xsic".i2_con_combin on "xsic".con_combin (aux_valor2,
empresa) using btree ;
create index "xsic".i3_con_combin on "xsic".con_combin (aux_valor3,
empresa) using btree ;
create index "xsic".i4_con_combin on "xsic".con_c
It DOES look like you are doing the update stats correctly, but the
difference between the estimate and actual row counts is saying rather
loudly that something is not right. Hmm, Don't remember, is this 11.70? If
so, the AUTOSTATS may be preventing the update statistics commands from
actually doing anything. If this is 11.70, try running those statements
again, but add the FORCE clause to each and see if that helps.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Thu, May 26, 2011 at 3:57 PM, ROGER VILCA <rvilca@luzdelsur.com.pe>wrote:
> I run the statistics according to the guides (I think so). Please, if there
> is
> any addicional recommendation, thanks.
>
> *********** proyectos_de_inver **************
>
> create table "xsic".proyectos_de_inver
> (
>
> empresa char(2) not null ,
>
> folio_proyecto decimal(10,0),
>
> proyecto_inversion char(6) not null ,
>
> descripcion char(100) not null ,
>
> observacion char(255),
>
> item char(4) not null ,
>
> grupo char(1) not null ,
>
> subgrupo char(3),
>
> jefe_proyecto char(50) not null ,
>
> area_gestora char(10),
>
> area_admin char(10),
>
> ano_lib_ppto decimal(4,0) not null ,
>
> fecha_inicio_prog date not null ,
>
> fecha_termino_prog date not null ,
>
> fecha_inicio_real date,
>
> fecha_cierre_real date,
>
> presupuesto_pi_us decimal(12,2),
>
> presupuesto_pi_p decimal(17,2) not null ,
>
> ano_moned_ppto_p decimal(4,0),
>
> mes_moned_ppto_p decimal(2,0),
>
> estado char(1) not null ,
>
> tasa_cambio decimal(10,4),
>
> usuario_actualiz char(30) not null ,
>
> fecha_actualiz date not null ,
>
> rol_dig char(10),
>
> fec_dig date,
>
> rol_aut char(10),
>
> fec_aut date,
>
> rol_apr char(10),
>
> fec_apr date,
>
> rol_act char(10),
>
> tipo_proy char(10),
>
> indppto char(1)
>
> default 'S'
> );
>
> revoke all on "xsic".proyectos_de_inver from "public" as "xsic";>
> create index "xsic".i_ep_proy_inver on "xsic".proyectos_de_inver
>
> (proyecto_inversion,empresa) using btree ;
> create index "xsic".proyecto on "xsic".proyectos_de_inver
> (proyecto_inversion,
>
> ano_lib_ppto,empresa) using btree ;
>
> DATABASE sifco;
> SET PDQPRIORITY 0;
> UPDATE STATISTICS MEDIUM FOR TABLE proyectos_de_inver DISTRIBUTIONS ONLY;
> UPDATE STATISTICS HIGH FOR TABLE proyectos_de_inver (proyecto_inversion)> DISTRIBUTIONS ONLY;
> UPDATE STATISTICS LOW FOR TABLE proyectos_de_inver (proyecto_inversion,> empresa);
> UPDATE STATISTICS HIGH FOR TABLE proyectos_de_inver ( ano_lib_ppto )> DISTRIBUTIONS ONLY;
> UPDATE STATISTICS HIGH FOR TABLE proyectos_de_inver ( empresa)> DISTRIBUTIONS
> ONLY;
> UPDATE STATISTICS LOW FOR TABLE proyectos_de_inver (proyecto_inversion,
> ano_lib_ppto, empresa);>
> *********** inver_de_capital ****************
>
> create table "xsic".inver_de_capital
> (
>
> empresa char(2) not null ,
>
> inversion_capital char(6) not null ,
>
> descripcion char(50),
>
> observacion char(255),
>
> responasable char(50),
>
> jefe_proyecto char(50) not null ,
>
> area_ejecutora char(10),
>
> fecha_inicio_prog date not null ,
>
> fecha_termino_prog date not null ,
>
> fecha_inicio_real date,
>
> fecha_puesta_serv date,
>
> fecha_cierre_real date,
>
> presupuesto_tot_ic decimal(17,2) not null ,
>
> presupuesto_tot_us decimal(17,2) not null ,
>
> ano_moned_ppto decimal(4,0) not null ,
>
> mes_moned_ppto decimal(2,0) not null ,
>
> proyecto_inver char(6),
>
> estado char(1) not null ,
>
> usuario_actualiz char(30),
>
> fecha_actualiz date,
>
> correl_registro char(10),
>
> area_gestora char(10),
>
> actividad char(10),
>
> usr_reg char(12),
>
> fec_reg date,
>
> usr_apr char(12),
>
> fec_apr date,
>
> usr_aut char(12),
>
> fec_aut date,
>
> usr_cie char(12),
>
> fec_cie date,
>
> rol_reg char(12),
>
> rol_aut char(12),
>
> rol_apr char(12),
>
> rol_cie char(12),
>
> rol_rec_apr char(12),
>
> rol_rec_aut char(12),
>
> fec_rec_apr date,
>
> fec_rec_aut date,
>
> glosa_cierre varchar(20),
>
> indexceso char(1)
>
> default 'N' not null ,
>
> pctexceso smallint
>
> default 0 not null ,
>
> version char(1)
>
> default ''
> );
>
> revoke all on "xsic".inver_de_capital from "public" as "xsic";>
> create index "xsic".idx_correl on "xsic".inver_de_capital (correl_registro,
>
> empresa) using btree ;
> create index "xsic".inversion_capital on "xsic".inver_de_capital
>
> (inversion_capital,empresa) using btree ;
> create index "xsic".proyecto_inver on "xsic".inver_de_capital
>
> (proyecto_inver,empresa) using btree ;
>
> DATABASE sifco;
> SET PDQPRIORITY 0;
> UPDATE STATISTICS MEDIUM FOR TABLE inver_de_capital DISTRIBUTIONS ONLY;
> UPDATE STATISTICS HIGH FOR TABLE inver_de_capital (inversion_capital)> DISTRIBUTIONS ONLY;
> UPDATE STATISTICS LOW FOR TABLE inver_de_capital (inversion_capital,> empresa);
> UPDATE STATISTICS HIGH FOR TABLE inver_de_capital (proyecto_inver)> DISTRIBUTIONS ONLY;
> UPDATE STATISTICS LOW FOR TABLE inver_de_capital (proyecto_inver, empresa);
> UPDATE STATISTICS HIGH FOR TABLE inver_de_capital (correl_registro)> DISTRIBUTIONS ONLY;
> UPDATE STATISTICS LOW FOR TABLE inver_de_capital (correl_registro,> empresa);
>
> *********** con_combin ***********************
>
> create table "xsic".con_combin
> (
>
> empresa char(10) not null ,
>
> combinatoria decimal(8,0) not null ,
>
> aux_valor1 char(10),
>
> aux_valor2 char(10),
>
> aux_valor3 char(10),
>
> aux_valor4 char(10),
>
> aux_valor5 char(10),
>
> aux_valor6 char(10),
>
> aux_valor7 char(10),
>
> aux_valor8 char(10),
>
> aux_valor9 char(10),
>
> aux_valor10 char(10),
>
> aux_valor11 char(10),
>
> aux_valor12 char(20),
>
> aux_valor13 char(10),
>
> aux_valor14 char(10),
>
> aux_valor15 char(10),
>
> aux_valor16 char(10),
>
> aux_valor17 char(10),
>
> aux_valor18 char(10),
>
> aux_valor19 char(10),
>
> aux_valor20 char(10)
> );
>
> revoke all on