slow views on IDS 11.50FC1
Hi,
changing from 10.00FC4 to 11.50FC1 causes a "select * from view_XXX"
to run more than 2 hours. On 10.00FC4 this select took not more than
10 minutes.An unload directly from the tables with the same select
statement doesn't take more than 10 minutes on 11.50FC1
The reason to use the view is that the HPL does the unload job..
Here is the view:
create view iv_op_inexclusion_old
(de_old_typsoacode, de_old_vtypsoacode, soacode, vsoacode,
inexclude_flag, price_euro_exl_tax, price_euro_incl_tax,
price_local_exl_tax, price_local_incl_tax, euromodus)as
select --+ ORDERED
basis_time.de_old_typsoacode,
linked_time.de_old_typsoacode,
basis_tsa.soa_code, v.vsoa_code,
inexclude_flag, v.price_euro_exl_tax,
v.price_euro_incl_tax,
v.price_local_exl_tax, v.price_local_incl_tax,
v.euromodus
from
op_j_time_vehicle_option basis_time,
op_inexclusion v,
op_vehicle_option basis_tsa,
op_j_time_vehicle_option linked_time
where
basis_time.typsoacode = v.typsoacode and
basis_tsa.typsoacode = basis_time.typsoacode and
linked_time.time_id = basis_time.time_id and
linked_time.soa_code = v.vsoa_code
The tables contain the following number of rows:
op_j_time_vehicle_option basis_time = 35.000.000
op_inexclusion = 7.600.000
op_vehicle_option = 9.100.000
op_j_time_vehicle_option = 34.500.000
Here is my config:
###################################################################
# Licensed Material - Property Of IBM
#
# "Restricted Materials of IBM"
#
# IBM Informix Dynamic Server
# Copyright IBM Corporation 1996, 2008 All rights reserved.
#
# Title: onconfig.std
# Description: IBM Informix Dynamic Server Configuration Parameters
#
# Important: $INFORMIXDIR now resolves to the environment
# variable INFORMIXDIR. Replace the value of the INFORMIXDIR
# environment variable only if the path you want is not under
# $INFORMIXDIR.
#
# For additional information on the parameters:
# http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp
###################################################################
###################################################################
# Root Dbspace Configuration Parameters
###################################################################
# ROOTNAME - The root dbspace name to contain reserved pages and
# internal tracking tables.
# ROOTPATH - The path for the device containing the root dbspace
# ROOTOFFSET - The offset, in KB, of the root dbspace into the
# device. The offset is required for some raw devices.
# ROOTSIZE - The size of the root dbspace, in KB. The value of
# 200000 allows for a default user space of about
# 100 MB and the default system space requirements.
# MIRROR - Enable (1) or disable (0) mirroring
# MIRRORPATH - The path for the device containing the mirrored
# root dbspace
# MIRROROFFSET - The offset, in KB, into the mirrored device
#
# Warning: Always verify ROOTPATH before performing
# disk initialization (oninit -i or -iy) to
# avoid disk corruption of another instance
###################################################################
ROOTNAME rootdbs
ROOTPATH /opt/informix/dev/rootdbs
ROOTOFFSET 0
ROOTSIZE 500000MIRROR 0
# MIRRORPATH $INFORMIXDIR/tmp/demo_on.root_mirror
# MIRROROFFSET 0
###################################################################
# Physical Log Configuration Parameters
###################################################################
# PHYSFILE - The size, in KB, of the physical log on disk.
# If RTO_SERVER_RESTART is enabled, the
# suggested formula for the size of PHSYFILE
# (up to about 1 GB) is:
# PHYSFILE = Size of BUFFERS * 1.1
# PLOG_OVERFLOW_PATH - The directory for extra physical log files
# if the physical log overflows during recovery
# or long transaction rollback
# PHYSBUFF - The size of the physical log buffer, in KB
###################################################################
PHYSFILE 1000000
PLOG_OVERFLOW_PATH $INFORMIXDIR/tmp
PHYSBUFF 128
###################################################################
# Logical Log Configuration Parameters
###################################################################
# LOGFILES - The number of logical log files
# LOGSIZE - The size of each logical log, in KB
# DYNAMIC_LOGS - The type of dynamic log allocation.
# Acceptable values are:
# 2 Automatic. IDS adds a new logical log to the
# root dbspace when necessary.
# 1 Manual. IDS notifies the DBA to add new logical
# logs when necessary.
# 0 Disabled
# LOGBUFF - The size of the logical log buffer, in KB
###################################################################
LOGFILES 6
LOGSIZE 10000
DYNAMIC_LOGS 2
LOGBUFF 64
###################################################################
# Long Transaction Configuration Parameters
###################################################################
# If IDS cannot roll back a long transaction, the server hangs
# until more disk space is available.
#
# LTXHWM - The percentage of the logical logs that can be
# filled before a transaction is determined to be a
# long transaction and is rolled back
# LTXEHWM - The percentage of the logical logs that have been
# filled before the server suspends all other
# transactions so that the long transaction being
# rolled back has exclusive use of the logs
#
# When dynamic logging is on, you can set higher values for
# LTXHWM and LTXEHWM because the server can add new logical logs
# during long transaction rollback. Set lower values to limit the
# number of new logical logs added.
#
# If dynamic logging is off, set LTXHWM and LTXEHWM to
# lower values, such as 50 and 60 or lower, to prevent long
# transaction rollback from hanging the server due to lack of
# logical log space.
#
# When using Enterprise Replication, set LTXEHWM to at least 30%
# higher than LTXHWM to minimize log overruns.
###################################################################
LTXHWM 70
LTXEHWM 80
###################################################################
# Server Message File Configuration Parameters
###################################################################
# MSGPATH - The path of the IDS message log file
# CONSOLE - The path of the IDS console message file
###################################################################
MSGPATH $INFORMIXDIR/tmp/online.log
CONSOLE $INFORMIXDIR/tmp/online.con
###################################################################
# Tblspace Configuration Paramete
On 7 Oct, 22:30, ralf.blankenb...@eurotaxschwacke.de wrote:
> Hi,
> changing from 10.00FC4 to 11.50FC1 causes a "select * from view_XXX"
> to run more than 2 hours. On 10.00FC4 this select took not more than
> 10 minutes.An unload directly from the tables with the same select
> statement doesn't take more than 10 minutes on 11.50FC1
> The reason to use the view is that the HPL does the unload job..
>
> Here is the view:
>
> create view iv_op_inexclusion_old
> (de_old_typsoacode, de_old_vtypsoacode, soacode, vsoacode,
> inexclude_flag, price_euro_exl_tax, price_euro_incl_tax,
> price_local_exl_tax, price_local_incl_tax, euromodus)> as
> select --+ ORDERED
> basis_time.de_old_typsoacode,
> linked_time.de_old_typsoacode,
> basis_tsa.soa_code, v.vsoa_code,
> inexclude_flag, v.price_euro_exl_tax,
> v.price_euro_incl_tax,
> v.price_local_exl_tax, v.price_local_incl_tax,
> v.euromodus
> from
> op_j_time_vehicle_option basis_time,
> op_inexclusion v,
> op_vehicle_option basis_tsa,
> op_j_time_vehicle_option linked_time
> where
> basis_time.typsoacode = v.typsoacode and
> basis_tsa.typsoacode = basis_time.typsoacode and
> linked_time.time_id = basis_time.time_id and
> linked_time.soa_code = v.vsoa_code
>
> The tables contain the following number of rows:
> op_j_time_vehicle_option basis_time = 35.000.000
> op_inexclusion = 7.600.000
> op_vehicle_option = 9.100.000
> op_j_time_vehicle_option = 34.500.000
>
> Here is my config:
>
> ###################################################################
> # Licensed Material - Property Of IBM
> #
> # "Restricted Materials of IBM"
> #
> # IBM Informix Dynamic Server
> # Copyright IBM Corporation 1996, 2008 All rights reserved.
> #
> # Title: onconfig.std
> # Description: IBM Informix Dynamic Server Configuration Parameters
> #
> # Important: $INFORMIXDIR now resolves to the environment
> # variable INFORMIXDIR. Replace the value of the INFORMIXDIR
> # environment variable only if the path you want is not under
> # $INFORMIXDIR.
> #
> # For additional information on the parameters:
> #http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp
> ###################################################################
>
> ###################################################################
> # Root Dbspace Configuration Parameters
> ###################################################################
> # ROOTNAME - The root dbspace name to contain reserved pages and
> # internal tracking tables.
> # ROOTPATH - The path for the device containing the root dbspace
> # ROOTOFFSET - The offset, in KB, of the root dbspace into the
> # device. The offset is required for some raw devices.
> # ROOTSIZE - The size of the root dbspace, in KB. The value of
> # 200000 allows for a default user space of about
> # 100 MB and the default system space requirements.
> # MIRROR - Enable (1) or disable (0) mirroring
> # MIRRORPATH - The path for the device containing the mirrored
> # root dbspace
> # MIRROROFFSET - The offset, in KB, into the mirrored device
> #
> # Warning: Always verify ROOTPATH before performing
> # disk initialization (oninit -i or -iy) to
> # avoid disk corruption of another instance
> ###################################################################
>
> ROOTNAME rootdbs
> ROOTPATH /opt/informix/dev/rootdbs
> ROOTOFFSET 0
> ROOTSIZE 500000> MIRROR 0
> # MIRRORPATH $INFORMIXDIR/tmp/demo_on.root_mirror
> # MIRROROFFSET 0
>
> ###################################################################
> # Physical Log Configuration Parameters
> ###################################################################
> # PHYSFILE - The size, in KB, of the physical log on disk.
> # If RTO_SERVER_RESTART is enabled, the
> # suggested formula for the size of PHSYFILE
> # (up to about 1 GB) is:
> # PHYSFILE = Size of BUFFERS * 1.1
> # PLOG_OVERFLOW_PATH - The directory for extra physical log files
> # if the physical log overflows during recovery
> # or long transaction rollback
> # PHYSBUFF - The size of the physical log buffer, in KB
> ###################################################################
>
> PHYSFILE 1000000
> PLOG_OVERFLOW_PATH $INFORMIXDIR/tmp
> PHYSBUFF 128>
> ###################################################################
> # Logical Log Configuration Parameters
> ###################################################################
> # LOGFILES - The number of logical log files
> # LOGSIZE - The size of each logical log, in KB
> # DYNAMIC_LOGS - The type of dynamic log allocation.
> # Acceptable values are:
> # 2 Automatic. IDS adds a new logical log to the
> # root dbspace when necessary.
> # 1 Manual. IDS notifies the DBA to add new logical
> # logs when necessary.
> # 0 Disabled
> # LOGBUFF - The size of the logical log buffer, in KB
> ###################################################################
>
> LOGFILES 6
> LOGSIZE 10000
> DYNAMIC_LOGS 2
> LOGBUFF 64>
> ###################################################################
> # Long Transaction Configuration Parameters
> ###################################################################
> # If IDS cannot roll back a long transaction, the server hangs
> # until more disk space is available.
> #
> # LTXHWM - The percentage of the logical logs that can be
> # filled before a transaction is determined to be a
> # long transaction and is rolled back
> # LTXEHWM - The percentage of the logical logs that have been
> # filled before the server suspends all other
> # transactions so that the long transaction being
> # rolled back has exclusive use of the logs
> #
> # When dynamic logging is on, you can set higher values for
> # LTXHWM and LTXEHWM because the server can add new logical logs
> # during long transaction rollback. Set lower values to limit the
> # number of new logical logs added.
> #
> # If dynamic logging is off, set LTXHWM and LTXEHWM to
> # lower values, such as 50 and 60 or lower, to prevent long
> # transaction rollback from hanging the server due to lack of
> # logical log space.
> #
> # When using Enterprise Replication, set LTXEHWM to at least 30%
> # higher than LTXHWM to minimize log overruns.
> ###################################################################
>
> LTXHWM 70
> LTXEHWM 80>
> ###################################################################
> # Server Message File Configuration Parameters
> ###################################################################
> # MSGPATH - T
>
> Run update statistics
> Run set explain on and check the query plan for both versions.
> Check the database schema and actual data is the same for both
> versions..
Update statistics is done with Art's dostats utility. The scheme ofthe database is exactly the same on both IDS Versions because the
databsae on 11.50FC1 is an imported database from 10.00FC4. Query plan
is not checked with the different IDS-Versions, but you are right, the
optimizer seems to work different on the IDS-Versions. But why does
the directly initiated select statement on IDS 11.50FC1 run as
approximately the same speed as the "select * from view XXX", it
should use the same statistics information as the view.
This does'nt make any sense for me, statistics of the underlying
tables are up to date, distributions were dropped and recreated on
both IDS-Versions.
Thanks Ralf
> This does'nt make any sense for me, statistics of the underlying
> tables are up to date, distributions were dropped and recreated on
> both IDS-Versions.
Bug??
Superboer.
On 8 okt, 00:31, ralf.blankenb...@eurotaxschwacke.de wrote:
> > Run update statistics
> > Run set explain on and check the query plan for both versions.
> > Check the database schema and actual data is the same for both
> > versions..
>
> Update statistics is done with Art's dostats utility. The scheme of> the database is exactly the same on both IDS Versions because the
> databsae on 11.50FC1 is an imported database from 10.00FC4. Query plan
> is not checked with the different IDS-Versions, but you are right, the
> optimizer seems to work different on the IDS-Versions. But why does
> the directly initiated select statement on IDS 11.50FC1 run as
> approximately the same speed as the "select * from view XXX", it
> should use the same statistics information as the view.
> This does'nt make any sense for me, statistics of the underlying
> tables are up to date, distributions were dropped and recreated on
> both IDS-Versions.
>
> Thanks Ralf