Experience with Auto Update Statistics (AUS)

Introduction

This is based on my previous blog post, Working with auto update statistics which I’ve expanded and significantly improved. I presented this at the Informix International User Group Conference 2015.

Let’s start at the beginning. Why do we run UPDATE STATISTICS at all? When we write an SQL query and send it to the database engine to execute, there may be several ways that the engine can run the query. For example if there are two tables the engine can start with the first table and join the second to it or begin with the second and join the first. There may be also two filter conditions, one of which may very specific and pick out only a few rows; the other may be very general. It should be apparent that some ways are more efficient than others, sometimes by several orders of magnitude.

Informix uses a cost-based optimizer to determine how to run queries. This relies on metadata to provide information about how large tables are, how many distinct values there are and other information about your data. We call these pieces of information statistics and if we also have a histogram of a column showing the abundance of specific values or ranges we call this a distribution.

The optimizer looks at possible query plans and chooses the one with the lowest costs assigned to it, according to the statistics and distributions. This may or may not be the best plan possible; if not you may see poor performance. Inaccurate cost predictions could be because your statistics are inadequate or out of date.

Maintaining the statistics and distributions is a key DBA responsibility.

What statistics and distributions should you maintain? The purpose is to ensure the optimizer selects the most efficient query plan for your queries.

These query plans should be stable over time. Normally stability is achieved through not changing things: this is why your Change Manager likes to say no (sometimes). However, with UPDATE STATISTICS stability comes from regularly refreshing your statistics and distributions: this is a change to your system you may be doing daily.

The Forth Bridge

A slide from my presentation to the IIUG 2015 Conference.

What statistics do you need? The Informix Performance Guide offers a general set of recommendations. However:

  • They may be more than you need and therefore more to maintain, which could be a headache with a large system.
  • In some specific cases they may not be good enough. The result of this can be an application codebase full of query directives (instructions to the optimizer to run queries in a particular way).
  • The guide doesn’t say much about how frequently you should run UPDATE STATISTICS.

Statistics and distributions

Statistics Distributions
  • Updated by UPDATE STATISTICS [LOW].
  • systables:
    • nrows: number of rows.
    • npused: number of pages used on disk.
    • ustlowts: when UPDATE STATISTICS was last run.
  • syscolumns: for indexed columns only:
    • colmin: the second lowest value.
    • colmax: the second highest value.
  • sysindices:
    • levels: number of B-Tree levels.
    • leaves: number of leaves.
    • nunique: number of unique values in the first column.
    • clust – incremented when values in the index are different to the last: max value is the number of rows; a low number indicates lots of duplicates.
    • nrows: number of rows.
    • ustlowts: when UPDATE STATISTICS was last run.
    • ustbuildduration: time to build index statistics.
  • Updated by UPDATE STATISTICS MEDIUM or HIGH.
  • Consist of histograms for values or value ranges in equally sized buckets in sysdistrib.
  • Fragment-level statistics are stored in sysfragdist.

Auto Update Statistics (AUS) basics

Auto Update Statistics (AUS) consists of two database scheduler jobs. These are stored in the sysadmin database in table ph_task with configuration settings in ph_threshold.

Auto Update Statistics Evaluation
This calls a set of stored procedures which populate the sysadmin:aus_command table with a prioritized list of UPDATE STATISTICS commands to run. The code for these procedures is in $INFORMIXDIR/etc/sysadmin/sch_aus.sql
Auto Update Statistics Refresh
This is a UDR that does the work of calling the UPDATE STATISTICS commands. In older versions this was done with SPL code. Auto Update Statistics Refresh cannot be called even manually without the database scheduler running.

If your instance has a stop file ($INFORMXDIR/etc/sysadmin/stop) to prevent the scheduler initialising with the instance you must remove it. Before you do so it’s a good idea to review which jobs are enabled (tk_enable='t') in the ph_task table. (Using a stop file is a bad idea with 12.10 because running without the scheduler stops some system processes from functioning, so if you’re doing this, you ought to change it even if you don’t want to use AUS.)

One advantage of AUS is that it works on all of your databases, including sysmaster.

Instance parameters affecting UPDATE STATISTICS

Three onconfig parameters affect statistics maintenance, independently of the tool you use to update statistics:

Parameter Description
AUTO_STAT_MODE Controls whether to only update statistics or distributions the engine considers stale when running UPDATE STATISTICS.
STATCHANGE Controls the percentage change beyond which statistics are considered stale if AUTO_STAT_MODE is enabled. This can be overridden at table level.
USTLOW_SAMPLE Whether to use sampling for UPDATE STATISTICS LOW.

AUS specific parameters

AUS’s configuration settings stored in ph_threshold work independently of these system level settings.

These can be updated via SQL without using OpenAdmin Tool:

UPDATE
  sysadmin:ph_threshold
SET
  value='0'
WHERE
  name='AUS_CHANGE';

Parameter Description
AUS_AGE The number of days after gathering statistics that they are considered stale.
AUS_CHANGE Prioritises tables based on the percentage of rows that have changed. This is not to be confused with STATCHANGE.
AUS_PDQ The PDQPRIORITY to use while updating statistics. If you are using workgroup edition set this to 0 to avoid annoying messages in the online log about not being allowed to use PDQ.
AUS_SMALL_TABLES The small table row threshold. These tables are considered volatile and updated on every run.
AUS_RULES
0
When set to zero, AUS updates any existing distributions only and won’t ensure that a minimum set of distributions exists according to rules. This allows you more fine-grained control of what you maintain. Be aware that creating an index automatically builds HIGH mode distributions on leading columns with data so if you don’t want these you would need to drop them.
1
Where no existing distributions exist, AUS will create:

  • HIGH mode distributions on all leading index columns.
  • MEDIUM mode distributions on other indexed columns.

For most systems ‘1’ is the value you should use. If in doubt, use this.

You’ll notice in the description for AUS_RULES=1 that AUS does not automatically generate any distributions on non-indexed columns. However it will maintain any existing distributions regardless of the value of AUTO_AUTO_RULES, even if AUS wouldn’t have created them if they didn’t exist.

Non-indexed columns and distributions

If you are migrating from dostats you will have MEDIUM mode distributions on all non-indexed columns. It’s possible to drop these on individual columns using:

UPDATE STATISTICS LOW FOR TABLE <tab name> (<colname>) DROP DISTRIBUTIONS ONLY;

I’d test before doing anything like this so you may just choose to leave them in and let AUS maintain them. If UPDATE STATISTICS MEDIUM doesn’t take very long on your system this is probably the best choice.

Are these distributions important? My answer is that it depends but, more often than not, no. The Informix Performance Guide recommends you have them but this is just a general recommendation. At this point it’s important not to lose sight of the fact that the goal is not to have the most comprehensive, high-resolution and up to date statistics possible; it is to ensure you can guarantee that your queries always run efficiently. Given that we can’t be updating statistics and distributions on all columns all of the time some compromises are inevitable.

Often when running a query the main risk is the optimizer choosing to use sequential scans instead of an index. This risk is greatly reduced, if not eliminated, if the onconfig parameter OPTCOMPIND is set 0. The downside of this is that the optimizer won’t select a sequential scan when it is the best query plan available unless there are no other options.

In general distributions on a column are more useful if there is some skew in the data. However be aware that for non-indexed columns syscolumns.colmin and syscolumns.colmax are never populated by UPDATE STATISTICS LOW so the optimizer is truly blind about the data ranges without a distribution.

I’m going to run through an example now using this table:

people table indices
person_id serial not null ix_people1 (person_id)
name varchar(254) not null ix_people2 (age)
gender char(1) not null
age int not null

The table will be populated with random ersatz data as follows:

  • 1 million rows.
  • Ages evenly distributed between 5 and 84.
  • 75% female, 25% male.

I’ll be running this query:

SELECT
  name
FROM
  people
WHERE
  age BETWEEN 18 AND ? AND
  gender=?

And tweaking these parameters:

  • Upper age limit in the query.
  • Gender in the query.
  • Whether I have a medium mode distribution on gender.
  • The value of OPTCOMPIND in the onconfig.

My results were as follows:

Upper age Gender Actual rows returned Medium mode distribution on gender? OPTCOMPIND Estimated rows Estimated cost Plan
25 f 74817 No 0 9988 21104 Index path
2 21104 Index path
Yes 0 75539 21104 Index path
2 21104 Index path
25 m 25061 No 0 9988 21104 Index path
2 21104 Index path
Yes 0 24539 21104 Index path
2 21104 Index path
30 f 121748 No 0 16232 38477 Index path
2 33923 Sequential scan
Yes 0 122439 38477 Index path
2 33923 Sequential scan
30 m 40572 No 0 16232 38477 Index path
2 33923 Sequential scan
Yes 0 39881 38477 Index path
2 33923 Sequential scan

What conclusions can we draw from this example?

  • OPTCOMPIND was a determining factor, not the presence of a medium mode distribution on gender.
  • Having the distribution gave a much better estimate of the number of rows returned.
  • The optimizer never used a different plan for the male or female queries.

Of course this is one example and you may have some different ones.

Columns with incrementing values

Let’s illustrate a different point with another example.

Maintaining distributions on any sort of log or journal where there is a timestamp field can be a problem. The highest value in your distribution is wrong almost immediately after calculating it because new rows are being added all the time with later timestamps. This means that if you do a query over recently added data your distributions may tell the optimizer that there’s no data.

ledger table indices
line_id serial not null ix_ledger1 (line_id)
account_id int not null ix_ledger2 (account_id, creation_time)
creation_time datetime year to second not null ix_ledger3 (creation_time)
ix_ledger4 (account_id)
for_processing table indices
line_id int not null ix_fp1 (line_id)
creation_time datetime year to second not null ix_fp2 (creation_time)

Both of these tables have over 1 million rows and new rows being added continuously.

I am going to run this query:

SELECT

  FIRST 5

  l.line_id,
  l.creation_time

FROM

  ledger l,

  for_processing f

WHERE

  f.line_id = l.line_id AND

  l.account_id = 50 AND

  f.creation_time BETWEEN

'2015-02-02 17:00:00' and '2015-02-02 21:00:00';

There are two conceivable ways to run this query:

  1. Use the index on creation_time on for_processing, join to the ledger table on line_id and then filter on the account_id column.
  2. Use the index on account_id on the ledger table, join by line_id and then filter on the creation_time column.

The risk with the first one is that a lot of rows are read, only to eliminate the vast majority of them when the account_id criterion is applied.

The optimizer may prefer to drive off creation_date, particularly if the distribution indicates there are no data past the time the distribution was gathered. This is because it believes (wrongly) that selecting from a date range in the for_processing table where it believes there is no data at all, avoiding the need to do any work, is more efficient than selecting out an account from the ledger and then joining the for_processing table.

This can be very slow for large date ranges. This is particularly true when there are a large number of accounts.

Can AUS help you here? Not really, this example is more to point out a danger. The risk of running into a problem like this can be massively increased if you use the default STATCHANGE value of 10. This is because here it is the age of the distributions that matters, not how much the data has changed.

My recommendation is:

In your onconfig either:

set AUTO_STAT_MODE to 0.

Or set STATCHANGE to 0 if AUTO_STAT_MODE is 1.

If there are tables for which this is not appropriate, do so at the table level:

ALTER TABLE <tabname> STATCHANGE <percent>;

In my view restricting updates only when there have been absolutely no changes is the only safe way.

There is an undocumented feature that can help here:

IC91678: Informix optimizer vulnerable to poor query response on incrementing columns.

However the fix is not switched on unless you set onconfig parameter: SQL_DEF_CTRL 0x2. This can be switched on dynamically with onmode -wm. With this switched on, date/time distributions are effectively extended into the future by up to 30 days. While the answer to the question how old can my statistics and distributions be is nearly always it depends, with this switched on there is a hard limit.

In Informix 12.10.FC5 the fix is included and is now the default behaviour.

The date or datetime column concerned must have a default value of TODAY or CURRENT. The code also compares the last timestamp in the distribution with the time the distribution was gathered. The two must be close together to activate the feature.

This fix also works on serial, serial8 and bigserial fields.

This feature is a little tricky to test because you must:

  • Populate the tables with a large volume of data.
  • Update the distributions.
  • Add some more data with later timestamps.
  • Wait a while!

Here are my results:

Date range
Within the distribution bounds Beyond the distribution upper bound Beyond the distribution upper bound with IC91678
Query drives off account_id creation_time account_id
Costs: lead with account_id 264 398 398
Costs: lead with creation_time 79004 4 398
Estimated rows 20 1 1
Actual rows 10 1 1

Wildcard queries and insufficient distribution resolution

Another problem you may have with the standard distributions created by UPDATE STATISTICS HIGH is insufficient resolution. By default the data are divided into 200 equal-sized buckets (resolution 0.5) and this may not suffice for some wildcard queries. The optimizer may be grossly wrong in its estimates for the number of rows returned and this can be improved by increasing the number of buckets.

customer table indices
customer_id serial not null ix_customer1 (customer_id)
user_name varchar(50) not null ix_customer2 (user_name)

Again this table will be populated with random ersatz data as follows:

  • 9 million rows.
  • This gives a distribution bucket size of 45000 with 0.5 resolution.

And my query:

SELECT
  FIRST 1000
  customer_id,
  user_name
FROM
  customer
WHERE
user_name
  LIKE 'BAN%'
ORDER BY
  customer_id;

Look at the query carefully. It’s the sort of query you might get from a web customer search form. The ORDER BY on customer_id is important because it gives the engine the option of avoiding sorting any data if this index is used to select the data. If the optimizer thinks the user_name criterion is not very selective, i.e. there are a lot more than 1000 customers whose user name begins with the letters BAN and it will find them quickly without reading many rows, it may prefer this query plan.

There are two credible plans for this query:

  1. Use the index on user_name, read all the rows it points to and then sort on customer_id, return the first 1000.
  2. Use the unique index on customer_id, read the rows it points to, filter, stop at 1000 rows. This plan does not require a sort.

There is a third option of scanning the entire table but it’s unlikely the optimizer will choose this.

Let’s look at the results of running this query with a standard HIGH mode distribution on customer_name, a second distribution with ten times the resolution and no distribution at all.

Distribution
Variable HIGH 0.5 resolution HIGH 0.05 resolution No distributions
Selected index (no directive) customer_id customer_name customer_id
Costs: lead with customer_id 3480 313249 174
Costs: lead with user_name 567525 1214 12435040
Estimated rows 90000 1000 1800000
Actual rows 1028 1028 1028

This example is real but also carefully selected because different three letter combinations may give different and more sane results, even with the standard distribution resolution.

If instead I run this query:

SELECT
  FIRST 1000
  customer_id,
  user_name
FROM
  customer
WHERE
user_name
  LIKE 'TDM%'
ORDER BY
  customer_id;

I get:

Distribution
Variable HIGH 0.5 resolution HIGH 0.05 resolution No distributions
Selected index (no directive) customer_name customer_name customer_id
Costs: lead with customer_id 135723 313249 174
Costs: lead with user_name 2909 1214 12435040
Estimated rows 2308 1000 1800000
Actual rows 801 801 801

AUS makes maintaining custom resolutions (or confidence levels with UPDATE STATISTICS MEDIUM) very easy. Simply run UPDATE STATISTICS manually to gather the desired distribution. AUS will maintain this resolution for you.

How frequently should I run UPDATE STATISTICS?

A common question a DBA may ask is how often is it necessary to UPDATE STATISTICS? A straightforward if unhelpful answer is often enough to ensure efficient query plans. More specifically:

  • On tables without columns with incrementing values, by which I mean that new values lie inside the range of the existing data, or where these columns are not used in the selection criteria for queries, it may be safe to use STATCHANGE (with AUTO_STAT_MODE) to regulate the frequency based on percentage change at the table level.
  • With incrementing values the working set can quickly get beyond the min/max values in the statistics or distributions. Here I’d recommend being much more aggressive and update based on age, regardless of the volume of change. This especially applies to your distributions.

If your system is small enough that you can run a full UPDATE STATISTICS every day there is no harm in doing this. It is probably overkill but it is one way of playing safe. To do this set AUS_CHANGE to 0 and make sure both the scheduler and the evaluator run daily. For larger systems you do need to be more selective about how often you run UPDATE STATISTICS.

Monitoring AUS

Does AUS just work? Well yes, it was aimed at the embedded market and it is pretty robust. On a larger system there is more to go wrong and so I’d recommend you check that:

  • the parameters are set correctly (in case they change if you rebuild the sysadmin database, for example).
  • there are no errors running UPDATE STATISTICS.
  • all statistics and distributions are sufficiently up to date.
  • the window you give the refresh job to run in is long enough.
Check Notes
Correct parameters
  • Job scheduler running and dbScheduler thread exists.
  • Evaluator and refresh tasks are enabled and have the expected schedules.
  • AUS_AGE, AUS_CHANGE, AUS_AUTO_RULES, AUS_PDQ and AUS_SMALL_TABLES set correctly.
No errors
  • No errors reported in the aus_command table (aus_cmd_state='E').
Statistics and distributions sufficiently up to date
  • Query system tables to get the age of statistics and distributions.
  • Fairly complex check, particularly if using STATCHANGE>0.
  • Then need to consider ninserts, nupdates and ndeletes columns.
Statistics:
Find tables where systables.ustlowts older than your threshold and then check whether it is in the result set of this SQL:

SELECT {+ORDERED}
  st.tabname
FROM
  sysdistrib sd,
  systables st,
  sysmaster:sysptnhdr sp
WHERE
  sd.tabid = st.tabid AND
  st.partnum = sp.partnum AND
  sd.tabid > 99 AND
  st.ustlowts > <days old> AND
  (sp.ninserts - sd.ninserts) + (sp.nupdates - sd.nupdates) + (sp.ndeletes - sd.ndeletes) > 0 AND
  (
  st.statchange IS NOT NULL AND (
  st.statchange = 0 OR
  ((sp.ninserts - sd.ninserts) + (sp.nupdates - sd.nupdates) + (sp.ndeletes - sd.ndeletes))/st.nrows*100 > st.statchange
  ) OR
  (
  st.statchange IS NULL AND
  (
  <onconfig STATCHANGE parameter> = 0 OR
  ((sp.ninserts - sd.ninserts) + (sp.nupdates - sd.nupdates) + (sp.ndeletes - sd.ndeletes))/st.nrows*100 > <onconfig STATCHANGE parameter>)
  )
  )
GROUP BY
  st.tabname
UNION
SELECT {+ORDERED}
  st.tabname
FROM
  sysdistrib sd,
  systables st,
  sysfragments sf,
  sysmaster:sysptnhdr sp
WHERE
  sd.tabid = st.tabid AND
  st.tabid = sf.tabid AND
  sf.partn = sp.partnum AND
  sd.tabid > 99 AND
  st.statchange is not null AND
  sf.fragtype='T' AND
  st.ustlowts > <days old>
GROUP BY
  st.tabname
  HAVING
  (SUM(sp.ninserts) - AVG(sd.ninserts)) + (SUM(sp.nupdates) - AVG(sd.nupdates)) + (SUM(sp.ndeletes) - AVG(sd.ndeletes)) > 0 AND
  (
  MIN(st.statchange) = 0 OR
  (SUM(sp.ninserts) - AVG(sd.ninserts)) + (SUM(sp.nupdates) - AVG(sd.nupdates)) + (SUM(sp.ndeletes) - AVG(sd.ndeletes))/AVG(st.nrows)*100 > MIN(st.statchange)
  )

Distributions:
SELECT DISTINCT
  tabname,
  colname,
  TODAY - sd.constr_time
  st.nrows
FROM
  sysdistrib sd JOIN
  systables st ON (sd.tabid = st.tabid) JOIN
  syscolumns sc ON (sd.tabid = sc.tabid AND sd.colno = sc.colno) JOIN
  sysmaster:sysptnhdr sph ON (sph.partnum = st.partnum)
WHERE
  sd.tabid > 99 AND
  (sph.ninserts - sd.ninserts) + (sph.nupdates - sd.nupdates) + (sph.ndeletes - sd.ndeletes) > 0 AND
  (
  st.statchange IS NOT NULL AND
  (
  st.statchange = 0 OR
  ((sph.ninserts - sd.ninserts) + (sph.nupdates - sd.nupdates) + (sph.ndeletes - sd.ndeletes))/st.nrows*100 > st.statchange
  ) OR
  (
  st.statchange IS NULL AND
  (
  <onconfig STATCHANGE parameter> = 0 OR
  ((sph.ninserts - sd.ninserts) + (sph.nupdates - sd.nupdates) + (sph.ndeletes - sd.ndeletes))/st.nrows*100 > <onconfig STATCHANGE parameter>)
  )
  ) AND
  (TODAY - DATE(sd.constr_time)) >= <days old>
ORDER BY 4, 3 DESC
Long enough window in which to run commands
  • No pending commands at the end of the refresh task window (aus_command.aus_cmd_state='P');

UPDATE STATISTICS FOR PROCEDURE

How does AUS deal with stored procedure plans as stored in sysprocplan? Well it doesn’t directly and does not call UPDATE STATISTICS FOR PROCEDURE [procname].

My take on this is that routines referencing updated tables will be recompiled anyway the first time they are called immediately after running UPDATE STATISTICS. On a busy OLTP system this will probably happen before you have chance to update procedure plans manually. If you do have reason to do this, you will need to do it manually and if you do don’t set PDQPRIORITY.

If your system does have dead time there may be a small advantage to running this but I don’t think it really matters that much.

Method for running UPDATE STATISTICS [LOW]

Let’s now look at how AUS actually calls UPDATE STATISTICS. As discussed earlier the evaluation task creates a list of commands to run and these are run by the refresh task exactly as you see them when you query the aus_command table.

Let’s start by considering UPDATE STATISTCS [LOW].

AUS simply calls UPDATE STATISTICS [LOW] as one command without specifying any column names and I have seen it suggested that this is a bad thing (I don’t agree). The popular alternative, dostats, runs UPDATE STATISTICS LOW separately for each index key. Performance-wise there is not as much difference as you might expect, I suspect this is because data tend to be in the buffer cache on repeated calls. But is there any difference to the end result?

Using Informix 11.70.FC7W1 I performed the following test:

  • Created two identical empty tables with twelve indices and loaded both with the same data.
  • Ran AUS on one, dostats on the other.
  • Unloaded and compared systables, syscolumns and sysindices for both tables.

Apart from tabid, tabname, index names and build times, the unloads were identical. My conclusion is that is no difference and AUS is slightly faster because it is done with one command in a single pass.

Performance of UPDATE STATISTICS [LOW]

In terms of performance there is actually very little you can do to influence the speed of UPDATE STATISTICS LOW. The parameters DBUPSPACE, PSORT_NPROCS and even PDQPRIORITY have no effect.

Setting USTLOW_SAMPLE in the onconfig file is the only performance optimisation available apart from general server tuning.

It is supposed to be possible to get parallel scan threads for all fragmented indices when PDQPRIORITY is set to at least 10 but I can’t reproduce this. This only works with USTLOW_SAMPLE is switched off.

Performance of UPDATE STATISTICS MEDIUM or HIGH

For UPDATE STATISTICS HIGH and MEDIUM there are a few parameters that can influence performance and with the exception of PDQPRIORITY you can’t use any of them with AUS. In summary they are:

  • DBUPSPACE: three parameters in one controlling four things:
    • how much memory and disk are available for sorting when PDQPRIORITY is not set.
    • whether to use indices for sorting.
    • whether to print the explain plan.
  • DBSPACETEMP: overrides default onconfig value.
  • PSORT_DBTEMP: allows you to specify a filesystem instead of DBSPACETEMP.
  • PSORT_NPROCS: specifies number of threads for sorting.

Having done tests with this parameter:

  • The disk and memory parameters don’t have any effect if you set PDQPRIORITY. If you do up to DS_TOTAL_MEMORY multiplied by the effective PDQPRIORITY (as a percentage) can be allocated.
  • Even if set, they only have the effect of restricting the number of columns that can be processed in a single pass so a lot of the time they make no difference. For a better understanding of how this works see John Miller III’s article on Understanding and Tuning Update Statistics.
  • Setting the explain output via the directive setting works a bit weirdly: you need to run set explain on separately to enable the explain plan even if set here but disabling it with this parameter does work.
  • The default is to use indices for sorting. Switching this off (directive 1 or 2) was up to 5x slower on my test system.

Here are some performance tests showing the effects of different DBUPSPACE settings on UPDATE STATISTICS HIGH. My performance tests are all carried out on a powerful 32 core Linux x86_64 server with 256 Gb RAM and fast SAN storage (not my laptop). For my tests I did a dummy run first to try and even out any effects of caching in the SAN. I then did a run immediately after initialising my instance (cold) and a repeat run (warm) for each result.

10 million row non-partitioned table with 12 single-column indices:

DBUPSPACE PDQPRIORITY Memory allocated (Mb) No. of scans Indices used for sorting? Light scans used? Time elapsed cold (s) Time elapsed warm (s)
1024:15:0 (default) 0 15 12 Yes No 111 97
0:50:0 0 50 12 Yes No 108 97
0:50:1 0 50 12 No No 259 254
0:50:0 100 2462.7 1 Yes Yes 172 180
0:50:1 100 2462.7 1 No Yes 176 172

74 million row table with 8-way round-robin partitioning and 3 single-column indices:

DBUPSPACE PDQPRIORITY Memory allocated (Mb) No. of scans Indices used for sorting? Light scans used? Time elapsed cold (s) Time elapsed warm (s)
1024:15:0 (default) 0 15 3 Yes No 227 169
0:50:0 0 50 3 Yes No 224 168
0:50:1 0 50 3 No No 501 494
0:50:0 100 3064.7 1 Yes Yes 432 426
0:50:1 100 3064.7 1 No Yes 425 428

What conclusions can we draw from these results?

  • Using PDQPRIORITY actually makes it slower.
  • There is no significant difference between using 15 Mb of memory for sorts and 50 Mb of memory for sorts. I suspect this is because the number of scans is the same.
  • Using indices for sorting (the default) is faster than not using indices for sorting when not using PDQ.
  • The use of light scans (avoiding the buffer cache) reduces the variation between the cold and warm results.

Despite having a partitioned table and PDQPRIORITY set, the interesting thing here is that during the execution I observed no parallelism. You can see this for yourself by identifying the session running UPDATE STATISTICS and looking at the threads the session is using. I get something like this for my partitioned table:

IBM Informix Dynamic Server Version 11.70.FC7W1 -- On-Line -- Up 00:01:11 -- 228250944 Kbytes

session           effective                            #RSAM    total      used       dynamic 
id       user     user      tty      pid      hostname threads  memory     memory     explain 
14       informix -         -        28798    guebobdb 1        11563008   10603832   off 

Program :
/opt/informix-11.70.FC7W1/bin/dbaccess

tid      name     rstcb            flags    curstk   status
201      sqlexec  26e1fbbfc8       ---PR--  24752    running-

Memory pools    count 4
name         class addr              totalsize  freesize   #allocfrag #freefrag 
14           V     26e4c59040       139264     10008      176        12        
14_SORT_0    V     26e573d040       4108288    294896     34865      4         
14_SORT_1    V     26e5748040       3657728    326176     32934      4         
14_SORT_2    V     26e5730040       3657728    325936     32937      4         

name           free       used           name           free       used      
overhead       0          13152          scb            0          144       
opentable      0          10640          filetable      0          3016      
ru             0          600            misc           0          160       
log            0          16536          temprec        0          21664     
blob           0          832            keys           0          664       
ralloc         0          19320          gentcb         0          1592      
ostcb          0          2944           sort           0          1577072   
sqscb          0          20224          sql            0          72        
srtmembuf      0          817320         hashfiletab    0          552       
osenv          0          3000           buft_buffer    0          8736      
sqtcb          0          9680           fragman        0          3640      
shmblklist     0          8074432        

sqscb info
scb              sqscb            optofc   pdqpriority optcompind  directives
e28f91c0         e4c5a028         0        100         0           1         

Sess       SQL            Current            Iso Lock       SQL  ISAM F.E. 
Id         Stmt type      Database           Lvl Mode       ERR  ERR  Vers  Explain    
14         UPDATE STATIST testdb             DR  Wait 15    0    0    9.24  On         

Current SQL statement (6) :
  update statistics high for table large_8-way_table
    (id_col1,id_col2,date_col) distributions only force

Last parsed SQL statement :
  update statistics high for table large_8-way_table
    (id_col1,id_col2,date_col) distributions only force

In the above example we can see that the engine has allocated three memory pools, one each for the three columns we are updating in a single pass with PDQPRIORITY set.

If you do see parallel sort (PSORT) threads like this:

Program :
/opt/informix-11.70.FC7W1/bin/dbaccess

tid      name     rstcb            flags    curstk   status
370      sqlexec  26e1fbaf28       ---PR--  20688    running-
371      psortpro 26e1fde468       Y------  912      cond wait  incore    -
372      psortpro 26e1fcda68       Y------  912      cond wait  incore    -
373      psortpro 26e1fd88f8       Y------  912      cond wait  incore    -
374      psortpro 26e1fd1498       Y------  912      cond wait  incore    -
375      psortpro 26e1fc6608       Y------  912      cond wait  incore    -
376      psortpro 26e1fdc328       Y------  912      cond wait  incore    -
377      psortpro 26e1fd4ec8       Y------  912      cond wait  incore    -
378      psortpro 26e1fca038       Y------  912      cond wait  incore    -
379      psortpro 26e1fbe958       Y------  912      cond wait  incore    -
380      psortpro 26e1fdbad8       Y------  912      cond wait  incore    -
381      psortpro 26e1fcb0d8       Y------  912      cond wait  incore    -
382      psortpro 26e1fda1e8       Y------  912      cond wait  incore    -
383      psortpro 26e1fc97e8       Y------  912      cond wait  incore    -
384      psortpro 26e1fd5718       Y------  912      cond wait  incore    -
385      psortpro 26e1fcd218       Y------  912      cond wait  incore    -
386      psortpro 26e1fc2388       Y------  912      cond wait  incore    -
387      psortpro 26e1fd80a8       Y------  912      cond wait  incore    -
388      psortpro 26e1fd0c48       Y------  912      cond wait  incore    -
389      psortpro 26e1fc5db8       -------  1632     running-
390      psortpro 26e1fdf508       Y------  912      cond wait  incore    -
391      psortpro 26e1fd7858       Y------  912      cond wait  incore    -
392      psortpro 26e1fc6e58       Y------  912      cond wait  incore    -
393      psortpro 26e1fd35d8       Y------  912      cond wait  incore    -
394      psortpro 26e1fdcb78       -------  8        running-
395      psortpro 26e1fca888       -------  1632     running-
396      psortpro 26e1fbf9f8       -------  1632     running-
397      psortpro 26e1fcc9c8       -------  8        running-
398      psortpro 26e1fc1b38       -------  8        running-
399      psortpro 26e1fd1ce8       -------  8        running-

Memory pools    count 4
name         class addr              totalsize  freesize   #allocfrag #freefrag 
41           V     270a6cd040       954368     61560      438        53        
41_SORT_0    V     2706fc2040       3915776    2184       33415      10        
41_SORT_1    V     2706fbb040       4222976    14488      31589      27        
41_SORT_2    V     2706fbc040       4222976    14248      31592      27

It is either because PSORT_NPROCS is set in your environment or set in the database engine’s environment when it was started.

Let’s now looking at the effect of PSORT_NPROCS. The only way to use this with AUS is to set PSORT_NPROCS when you start the database engine, which will of course affect all sessions.

Setting PSORT_NPROCS is the only way to get any parallel processing with UPDATE STATISTICS HIGH or MEDIUM. It has no effect on UPDATE STATISTICS LOW. Setting PDQPRIORITY only provides more memory and allows HIGH and MEDIUM mode distributions on multiple columns to be built in a single pass, if enough memory is available. There will be one SORT memory pool per column being processed regardless of PDQPRIORITY. Sorting with PSORT_NPROCS set can be faster as we’ll see now.

Carrying on with the same examplse as above, I get these results:

10 million row non-partitioned table with 12 single-column indices:

DBUPSPACE PDQPRIORITY PSORT_ NPROCS Memory allocated (Mb) No. of scans Light scans used? Time elapsed cold (s) Time elapsed warm (s)
0:50:0 0 Not set 50 12 No 108 97
0:50:0 0 24 50 12 No 123 97
0:50:0 100 Not set 2462.7 1 Yes 172 180
0:50:0 100 24 2538.4 1 Yes 80 83

74 million row table with 8-way round-robin partitioning and 3 single-column indices:

DBUPSPACE PDQPRIORITY PSORT_ NPROCS Memory allocated (Mb) No. of scans Light scans used? Time elapsed cold (s) Time elapsed warm (s)
0:50:0 0 Not set 50 3 No 224 168
0:50:0 0 24 50 3 No 223 170
0:50:0 100 Not set 3064.7 1 Yes 432 426
0:50:0 100 24 3083.7 1 Yes 156 145

What conclusions can we draw this time?

  • The fastest UPDATE STATISTICS HIGH (or MEDIUM) performance is with PDQ and PSORT_NPROCS set. (Seasoned DBAs might have expected this result).
  • But it’s not much faster than running without either of these parameters set, probably with fewer server resources.
  • It’s worth bearing in mind that PDQ enables light scans which may avoid buffer cache churn.

Adding additional AUS Refresh tasks for greater throughput

There is another way to achieve parallelism with AUS and that is to add additional scheduler tasks so that more than one table can be worked on at once, for example:

INSERT INTO
  sysadmin:ph_task (
    tk_name,
    tk_description,
    tk_type,
    tk_dbs,
    tk_execute,
    tk_delete,
    tk_start_time,
    tk_stop_time,
    tk_frequency,
    tk_monday,
    tk_tuesday,
    tk_wednesday,
    tk_thursday,
    tk_friday,
    tk_saturday,
    tk_sunday,
    tk_group,
    tk_enable
  )
  VALUES (
    'Auto Update Statistics Refresh #2',
    'Refreshes the statistics and distributions which were recommended by the evaluator.',
    'TASK',
    'sysadmin',
    'aus_refresh_stats',
    '0 01:00:00',
    '03:11:00',
    '12:45:00',
    '1 00:00:00',
    't',
    't',
    't',
    't',
    't',
    'f',
    'f',
    'PERFORMANCE',
    't'
  );

It is vital that the task name begins with Auto Update Statistics Refresh as shown here otherwise some of the internal code that stops the evaluator from running at the same time as the refresh tasks will not work.

Think of it as a batch process where the goal is to update all your statistics and distributions, not doing an individual table as fast as possible.

I recommend this method if you need extra grunt! Then run without PDQ (AUS_PDQ = 0).

Pros and cons of using PSORT_DBTEMP

Let’s move to consider another environment variable you can’t easily use: PSORT_DBTEMP. Is using a filesystem for sorts faster than temporary dbspaces? For this I am not going to do any performance tests largely because a comparison between the local SATA disks used for filesystems and the battery-backed, part solid state SAN used for the database storage on my server is not a fair fight.

If you want to use PSORT_DBTEMP with AUS, again you will need to set it in your environment when initialising the server and use it across your system.

The only definitive benefit of pointing to a filesystem using PSORT_DBTEMP instead of using DBSPACETEMP is that the Linux filesystem cache has an effect. This means that your temporary files may never be committed to disk, giving you a performance advantage. Another interesting alternative is to use a RAM disk.

Otherwise when considering whether to use a filesystem over temporary dbspaces, I would consider your hardware.

Recent UPDATE STATISTICS defects

Below are some defects relating to UPDATE STATISTICS that I have personally logged with IBM. All are general UPDATE STATISTICS issues and whether you use AUS or not does not affect your chances of hitting these. I have written some comments underneath.

IT06767 UPDATE STATISTICS HIGH ON TABLE BIGGER THAN 1.1 BIO ROWS CAN CAUSE EXCEPTION IN SRTQUICK
This defect can only be hit if you use PDQ and a large amount of memory is allocated.
IT06726 Assert Warning Invalid index statistics found when using statistics sampling with index having many deleted items
Requires USTLOW_SAMPLE to be switched on. It is fairly harmless but does write some garbage to sysfragments. Re-running UPDATE STATISTICS LOW usually fixes it.
IT02679 UPDATE STATISTICS HIGH ON A FRAGMENTED TABLE LEADS TO ERROR MESSAGE 9810
This can only be seen if using fragment level statistics.
IT05463 Fragment based update stats high consumes a huge amount of sblobspace
This can only be seen if using fragment level statistics. It is not worth trying to make your sbspace extremely large to work around this problem.
IT05639 -768: Internal error in routine mbmerge2bin when running update statistics on a fragmented table (running fragment-level statistics)

Another fragment level statistics one. With the fixes for IT02679, IT05463 and IT05639 this feature is solid. I wouldn’t enable this for a table unless you have all of these in your version.

Most of these defects are fixed in 12.10.FC5.

UPDATE STATISTICS performance summary

Overall then what seems to be the best balance between speed and use of resources on your system is:

  • In terms of AUS, leave AUS_PDQ at 0.
  • If you need more throughput add more Refresh tasks to the scheduler.
  • That’s it.
  • Set DBUPSPACE=0:50:0 in the server environment.

I hope this has been useful. I’ll end with a quick summary of some of the more interesting performance points from this presentation which apply regardless of whether you use AUS or not:

  • Setting PDQPRIORITY only provides more memory for UPDATE STATISTICS HIGH or MEDIUM and does not provide parallel processing. It may even make it run slower.
  • If you want multi-threaded processing, this only works by setting PSORT_NPROCS and then only with UPDATE STATISTICS HIGH or MEDIUM. Because it does not require PDQ you can use this with workgroup edition.
  • In my tests only when used together do PDQPRIORITY and PSORT_NPROCS improve performance. PDQ does switch on light scans though which avoid churning your buffer cache..
  • Not using indices for sorting for UPDATE STATISTICS HIGH or MEDIUM can be significantly slower.
  • The performance of UPDATE STATISTICS [LOW] can be improved by setting USTLOW_SAMPLE.
Advertisements

Temporary tables

Again, this is another blog post about nothing new at all but an attempt to put down my understanding of temporary tables down in a way that will help me when I refer back to it and hopefully be of more wider use.

When looking at things like this a test system is always essential for checking things and finding some surprising results. If you don’t have one you can soon set one up with VMWare or Virtual Box and a copy of Informix Developer Edition. For this post I am going to set up a simple test instance with four specific dbspaces:

  • A logged rootdbs with plenty of free space, called rootdbs.
  • A logged dbspace for my data and indices, called datadbs.
  • A temporary dbspace without logging, called tmp2k_unlogged.
  • A temporary dbspace with logging, called tmp2k_logged.

What’s the difference between a temporary dbspace with logging and a normal dbspace? Nothing except that one appears in the DBSPACETEMP onconfig setting.

Consider the SQL statements which explicitly create temporary tables in a session:

create temp table unlogged (col1 int) with no log;
insert into unlogged values (1);

and:

create temp table logged (col1 int);
insert into logged values (1);

For these tests we must have TEMPTAB_NOLOG set to 0 in our onconfig otherwise the second statement will silently have a with no log criterion added to it.

Let’s run these, use oncheck -pe to see which dbspace they get placed in and then use onlog -n <starting log unique identifier> to see if changes to these tables get logged or not:

DBSPACETEMP setting unlogged table logged table
dbspace used logged operations dbspace used logged operations
tmp2k_unlogged:tmp2k_logged tmp2k_unlogged no tmp2k_logged yes
tmp2k_unlogged tmp2k_unlogged no datadbs yes
tmp2k_logged tmp2k_logged no tmp2k_logged yes
NONE datadbs no datadbs yes

So already a few interesting results drop out:

  1. We can specify both logged and unlogged dbspaces using the DBSPACETEMP parameter.
  2. The engine will prefer logged dbspaces for logged tables and unlogged dbspaces for unlogged tables.
  3. If an unlogged dbspace is not available the engine can use a logged dbspace and create unlogged tables in it.
  4. If a logged dbspace is not available the engine will use an alternative dbspace because an unlogged dbspace does not logging at all. In this case it has chosen datadbs, because this is the dbspace in which I created my database.

At this point it’s worth referring to an IBM technote on this subject. This suggests some more tests but already my results are not in agreement with the first example given:

If we have created a dbspace named tmpdbs, but we could not see it was marked as ‘T’ in the result of onstat -d. We set DBSPACETEMP configuration parameter to tmpdbs. On this condition, tmpdbs will be used for logged temporary tables. That means if a temp table is created with ‘WITH NO LOG’ option, the server will not use it.

This is implying that my tmp2k_logged dbspace (it will not have the ‘T’ flag) cannot be used for unlogged temporary tables. You can see from my table that this isn’t true and I invite you to test this for yourself.

As part proof here is the onstat -d and oncheck -pe output:

$ onstat -d | grep tmp2k
7f9d7310         3        0x60001    3        1        2048     N  BA    informix tmp2k_logged
7f9d74b8         4        0x42001    4        1        2048     N TBA    informix tmp2k_unlogged


DBspace Usage Report: tmp2k_logged        Owner: informix  Created: 03/08/2015


 Chunk Pathname                             Pagesize(k)  Size(p)  Used(p)  Free(p)
     3 /informix_data/sandbox/tmp2k                   2      512       61      451

 Description                                                   Offset(p)  Size(p)
 ------------------------------------------------------------- -------- --------
 RESERVED PAGES                                                       0        2
 CHUNK FREELIST PAGE                                                  2        1
 tmp2k_logged:'informix'.TBLSpace                                     3       50
 db1:'thompsonb'.unlogged                                            53        8
 FREE                                                                61      451

 Total Used:       61
 Total Free:      451

Moving on, let’s do a slightly different test:

select * from sysmaster:systables into temp mytab_unlogged with no log;

and:

select * from sysmaster:systables into temp mytab_logged;

And the results:

DBSPACETEMP setting unlogged table logged table
dbspace used logged operations dbspace used logged operations
tmp2k_unlogged:tmp2k_logged tmp2k_unlogged no tmp2k_logged yes
tmp2k_unlogged tmp2k_unlogged no datadbs yes
tmp2k_logged tmp2k_logged no tmp2k_logged yes
NONE datadbs no datadbs yes

Again my results are in disagreement with the IBM technote which says:

If DBSPACETEMP is not specified, the temporary table is placed in either the root dbspace or the dbspace where the database was created. SELECT…INTO TEMP statements place the temporary table in the root dbspace. CREATE TEMP TABLE statements place the temporary table in the dbspace where the database was created.

In both cases in my tests the engine chose datadbs and not my root dbspace.

Let’s move on a bit. How do I know what temporary tables are in use on my system as a whole?

One way is to run something like:

onstat -g sql 0 | grep -A 3 'User-created Temp tables'

This might get you something like this:

User-created Temp tables :
  partnum  tabname            rowsize 
  400003   mysystables2       500
  400002   mysystables        500

Another way is to run oncheck -pe and have a look at what is in your temporary dbspaces. Here you may also see space used by the engine for sorting, marked with SORTTEMP, or temporary tables created implicitly by the engine for query processing. However whatever type of object it is, you will find it impossible to match anything you see to a particular session by this method; it is only possible to match to a user name which would only allow positive identification if there was only a single session per user.

There is another way to match tables to sessions which works for explicitly created temporary tables, for which I don’t want to claim any credit because I cribbed it from the IIUG Software Repository. The script is called find_tmp_tbls and it its present state is broken when used with 11.70 (and hasn’t been tested since version 9.30.FC2 according to its README): at least it does not work with 64-bit Linux, mainly because the syntax for onstat -g dmp seems to have changed slightly. I managed to fix it up, however.

It’s a little complicated to follow but the basic steps are this:

  1. You need to start with a given session and check if it has any temporary tables. (Unfortunately I don’t know a way of working backwards from the temporary table to see which session it belongs to either through onstat or the SMI interface.)
  2. Get the session’s rstcb value, either from onstat -g ses <sid> or from the first column in onstat -u.
  3. Run onstat -g dmp 0x<rstcb> rstcb_t | grep scb. Note that the rstcb value must be prefixed by 0x.This should return an scb value in hex.
  4. Take this value and run onstat -g dmp <scb> scb_t | grep sqscb. Your address must start with 0x again and this is true throughout all the examples. This will return two values; take the one labelled just sqscb.
  5. Feed this value into another dmp command: onstat -g dmp <sqscb> sqscb_t | grep dicttab. This will return another value.
  6. Finally take this and get the partnum(s) of the temporary tables for the session by running: onstat -g dmp <dicttab> "ddtab_t,LL(ddt_next)" | grep partnum.

Here is all that as an example:

$ onstat -g dmp 0x2aaaab608488 rstcb_t | grep scb
    scb          = 0x2aaaabf5f1c0
$ onstat -g dmp 0x2aaaabf5f1c0 scb_t | grep sqscb
    sqscb        = 0x2aaaad6c8028
    sqscb_poolp  = 0x2aaaad6c92c8
$ onstat -g dmp 0x2aaaad6c8028 sqscb_t | grep dicttab
    dicttab      = 0x2aaaad87c4f0
$ onstat -g dmp 0x2aaaad87c4f0 "ddtab_t,LL(ddt_next)" | grep partnum
    ddt_partnum  = 4194307
    ddt_partnum  = 4194306

It’s worth emphasising that this method will only work for explicitly created temporary tables. It won’t identify temporary space used by:

  • implicitly created temporary tables created by the engine to process a query.
  • temporary sort segments.

If there is a similar method for these types, I would be interested to find out about it.

Armed with the partnum you can do whatever you want with it like run this query against the sysmaster database to see what space is being used:

SELECT
  tab.owner,
  tab.tabname,
  dbsp.name dbspace,
  te_chunk chunk_no,
  te_offset offset,
  te_size size
FROM
  systabnames tab,
  systabextents ext,
  syschunks ch,
  sysdbspaces dbsp
WHERE
  tab.partnum in (4194306, 4194307) AND
  ext.te_partnum=tab.partnum AND
  ch.chknum=ext.te_chunk AND
  dbsp.dbsnum=ch.dbsnum
ORDER BY
  tab.owner,
  tab.tabname,
  te_extnum;

Giving results like:

owner tabname dbspace chunk_no offset size
thompsonb mysystables tmp2k_unlogged 6 53 8
thompsonb mysystables2 tmp2k_unlogged 6 61 8

For reference there is information about explicit and implicit temporary tables and temporary sort space in these tables in the sysmaster database:

  • sysptnhdr
  • sysptnext
  • sysptnbit
  • sysptntab
  • sysptprof
  • systabextents
  • systabinfo
  • systabnames
  • systabpagtypes

So in conclusion I hope this post brings together some useful information about explicit temporary tables. Personally I’d like to be able to get a complete picture of which sessions what statements are using temporary space, which this doesn’t give. If I find anything it will be subject of a future blog post.


Foreign key creation performance

The Agile development model IBM has moved to for Informix development means that there’s always a few extra features in each fix pack. There are two rather nice enhancement in 11.70.FC8 when building a foreign key which alleviate some pain points when doing administration on large tables. The first is a no validate option which means that no checking of the data is done when creating the foreign key. This is appropriate in situations like migrating platforms when you know that referential integrity is already assured and you want to avoid the overhead of re-checking it all again on the new system. I want to look instead at the other enhancement: improved performance when creating a foreign key.

Nearly all of the time creating a foreign key constraint is spent checking the data; modifying the database schema to add the constraint is a very quick operation.

Prior to 11.70.FC8 creating a foreign key on a large table was a long process and with non-partitioned tables it was a single-threaded operation which did not benefit from the use of indices. Typically you’d see a thread working away like this:

tid      name     rstcb            flags    curstk   status
283      sqlexec  26e1fc3c78       --BPR--  22096    running-

Or with a fragmented table and PDQ turned on you might see multiple threads:

tid      name     rstcb            flags    curstk   status
324      sqlexec  26e1fc3c78       --BP---  19744    sleeping secs: 1     -
325      scan_1.0 26e1fc0a98       --B-R--  3248     running-
326      scan_1.1 26e1fc44c8       --B-R--  3248     running-
327      scan_1.2 26e1fc4d18       --B-R--  3248     running-
328      scan_1.3 26e1fc5568       --B-R--  3248     running-
329      scan_1.4 26e1fc5db8       --B-R--  3248     running-
330      scan_1.5 26e1fc6608       --B-R--  3248     running-
331      scan_1.6 26e1fc97e8       --B-R--  3248     running-
332      scan_1.7 26e1fcb0d8       --B-R--  3248     running-

Even with parallel operations foreign key builds could still take a long time.

With 11.70.FC8 foreign key builds are a lot faster and here is a test case which you can try. I’m going to create a 10 million row table, which is large enough to demonstrate the problem, and a referenced table with up to 32767 unique values and see how long it takes to create a foreign key.

Courtesy of the Informix FAQ I am going to create these procedures to generate random numbers.

CREATE PROCEDURE sp_setseed(n INTEGER)
  DEFINE GLOBAL seed DECIMAL(10) DEFAULT 1;

  LET seed = n;

END PROCEDURE;

CREATE PROCEDURE sp_random() RETURNING INTEGER;
  DEFINE GLOBAL seed DECIMAL(10) DEFAULT 1;
  DEFINE d DECIMAL(20,0);

  LET d = (seed * 1103515245) + 12345;
  -- MOD function does not handle 20-digit values
  LET seed = d - 4294967296 * TRUNC(d / 4294967296);

  RETURN MOD(TRUNC(seed / 65536), 32768);

END PROCEDURE;

These will generate a random number between 0 and 32767 and I’ll use these to create some data to populate my tables with.

drop procedure if exists populate;
create procedure populate ()

  define p_wid int;
  define i int;

  drop table if exists referenced;
  create table referenced (
    widget_id int not null
  );

  drop table if exists referencing;
  create table referencing (
    id_column bigserial not null,
    widget_id int not null
  );

  FOR i = 1 TO 10000000 STEP 1

    let p_wid = sp_random();
    insert into referencing (widget_id) values (p_wid);

  END FOR;

  create index ireferencing_x1 on referencing (widget_id);
  insert into referenced (widget_id) select distinct widget_id from referencing;

  create unique index ireferenced_u1 on referenced (widget_id);
  alter table referenced add constraint primary key (widget_id) constraint referenced_pk;

end procedure;

execute procedure populate();

That sets up the test. It just remains to create the foreign key, which I’ll time:

alter table referencing add constraint foreign key (widget_id) references referenced (widget_id) constraint referencing_fk;

And so for the results:

Informix version Fragmented table PDQ priority Parallel execution Foreign key build time (s)
11.70.FC7 No 0 No 40
11.70.FC7 No 100 No 40
11.70.FC7 Yes, 8-way partition by expression, evenly distributed 0 No 40
11.70.FC7 Yes, 8-way partition by expression, evenly distributed 100 Yes 40
11.70.FC7 Yes, 8-way round-robin 0 No 41
11.70.FC7 Yes, 8-way round-robin 100 Yes 67
11.70.FC8 No 0 No 0.3
11.70.FC8 No 100 No 0.3
11.70.FC8 Yes, 8-way partition by expression, evenly distributed 0 No 0.5
11.70.FC8 Yes, 8-way partition by expression, evenly distributed 100 Yes 40
11.70.FC8 Yes, 8-way round-robin 0 No 0.9
11.70.FC8 Yes, 8-way round-robin 100 Yes 68

And for comparison here is the time taken for some related SQL queries (without PDQ):

Query Duration (s)
select distinct widget_id from referencing; 9
select widget_id, count(*) from referencing group by widget_id; 7
select widget_id from referencing where widget_id not in (select widget_id from referenced); 37

The purpose of this table is to show the relative times of the foreign key build and not to benchmark any hardware or Informix itself so I’m not going to divulge what hardware I’m used for the tests. The server I used did have multiple processors and CPU VPs configured so that PDQ queries could scale out. The number of ready threads was very low or none during the operation.

I ran all the tests a few times and there was a small amount of variation so I’ve only used a low precision in the results.

So what conclusions can we draw from this?

  • The stand-out result is that the verification of foreign keys is much faster in FC8 but only for serial operations.
  • Serial foreign key builds in FC8 are significantly faster than any SQL query I could write that checked the referential integrity.
  • In FC7 and earlier parallel operations may be slower.
  • In FC8 there is not the same speed improvement for parallel operations and they are still much slower.

Another interesting finding is the large difference in performance between the expression-based fragmentation and round-robin fragmentation when using parallel operations. I had assumed that this kind of operation would be agnostic to the type of fragmentation used. I checked this a few times but examining the threads when it is running shows something different is definitely happening.

Round-robin fragmentation looks like the below. Notice that most of the threads are active:

tid      name     rstcb            flags    curstk   status
123535   sqlexec  15f2599e8        --BP---  29472    sleeping secs: 1     -
123536   scan_1.0 15f25e590        --B-R--  2400     running-
123537   scan_1.1 15f262068        Y-B----  496      cond wait  await_MC1 -
123538   scan_1.2 15f25c3f0        Y-B----  496      cond wait  await_MC1 -
123539   scan_1.3 15f261800        --B----  2400     running-
123540   scan_1.4 15f2628d0        --B-R--  2400     running-
123541   scan_1.5 15f263138        --B-R--  2400     running-
123542   scan_1.6 15f25edf8        --B-R--  2400     running-
123543   scan_1.7 15f25f660        --B----  2400     running-

Expression fragmentation seems to run with just one active thread for the majority of the time:

tid      name     rstcb            flags    curstk   status
123804   sqlexec  15f258918        --BP---  29472    sleeping secs: 1     -
123805   scan_1.0 15f25e590        --B-R--  2400     running-
123806   scan_1.1 15f25c3f0        Y-B----  496      cond wait  await_MC1 -
123807   scan_1.2 15f261800        Y-B----  496      cond wait  await_MC1 -
123808   scan_1.3 15f2628d0        Y-B----  496      cond wait  await_MC1 -
123809   scan_1.4 15f263138        Y-B----  496      cond wait  await_MC1 -
123810   scan_1.5 15f25edf8        Y-B----  496      cond wait  await_MC1 -
123811   scan_1.6 15f25f660        Y-B----  496      cond wait  await_MC1 -
123812   scan_1.7 15f2639a0        Y-B----  496      cond wait  await_MC1 -
123813   scan_1.8 15f260f98        Y-B----  496      cond wait  await_MC1 -

There is an extra scan thread (scan_1.8) because I have an (empty) remainder fragment in addition to my 8-way fragmentation.

This would go some way to explaining why round-robin is faster.

Finally, if I could wander into the realms of speculation for a moment, it does look like IBM has implemented the parallel foreign key constraint checking slightly differently for each fragmentation method, which might explain why the improved performance is only seen with serial operations.

To sum up, this is a wonderful enhancement but I’ll be careful to switch off parallel processing when building foreign keys. It greatly speeds up index builds on fragmented tables, which is the type of operation you might do at the same time so I can foresee scripts with parallel processing being enabled and disabled several times to ensure best speed.


Monitoring virtual segment usage and the CPU VP caches

A little while ago I was forced to look into detail into the memory usage of one of the production instances I look after. Specifically the problem was that the instance was allocating extra virtual segments to itself (via the SHMADD process) but it was a surprise because memory usage on this instance was being monitored and it clearly showed that memory usage was normal and well below the initial segment size.

Well, almost everything did. onstat -g seg was correctly reporting the memory usage. Without wishing to put in an early spoiler, this is the only way of seeing how much memory your system is using and how close you are to your instance allocating an extra segment.

With the help of a careful analysis by IBM Informix support, we looked into memory usage on our system using:

onstat -u
onstat -g mem
onstat -g mgm
onstat -g ses

We also drilled down into individual sessions using:

onstat -g afr
onstat -g ffr
onstat -g ufr

We also looked into memory usage by onbar and SQLTRACE.

The result was a massive discrepancy between the total memory in use, as reported by these tools, and what onstat -g seg was reporting. And onstat -g seg appeared to be right because when it said the memory was all used up, the engine would allocate another segment.

So where was the memory going? Was it a leak or a bug? Well no, it was a new feature or, as we later learned, a bug fix.

In response to a performance issue reported by another customer, IBM had redesigned the VP memory cache code in 11.70.FC7W1 and it turned out that this was responsible for the usage of up to 70% of the virtual memory segment on our system. This is an enterprise edition only feature so I guess if you any other edition, you can stop reading at this point and just note that monitoring the output from onstat -g seg is a great idea if you want to predict segment additions.

The CPU memory cache allocates a dedicated area of the virtual memory segment to each CPU virtual processor, which it can use exclusively without contending with other virtual processors.

So a new and undocumented feature in a W1 release? Isn’t this a bit irregular? Well, no said IBM, it was a fix for a customer issue. But it does change the behaviour from what is documented quite dramatically.

In 11.70.FC7 and before, the CPU memory cache size is controlled by the VP_MEMORY_CACHE_KB parameter and if you have, say 8 CPU VPs, this results in a fixed area of memory of 8x VP_MEMORY_CACHE_KB being allocated to CPU memory caches and this is still how the manual says it works.

In 11.70.FC7W1 this parameter merely controls the initial size of these caches, which are then free to grow (and I think shrink below the initial size) as they see fit. To improve performance memory can be allocated to these caches without having to free any first and a separate thread deals with garbage collection (or drainage). (I hope I have explained this properly as I an not a programmer.) What is certain is that if your system is very busy the caches grow faster than the garbage collection clears them down. If your system is very busy for a sustained period, they can grow and allocate memory until you hit SHMTOTAL, if you’ve set it. (I don’t think hitting this limit would be very pretty because the instance would kick out sessions to free up memory, but this is not where the problem lies. Anyway, it would need testing and I haven’t done so.)

So can you monitor it? Yes you can and I’d recommend if you’re running 11.70.FC7W1 or above and have the VP cache switched on that you do. This little code snippet does the job of calculating the total size in Mb of all the VP caches for an instance:

#!/bin/bash
vpcacheblks=0
vpcache=(`onstat -g vpcache | grep -E '^    [0-9]' | awk '{ print $2 }'`)
for ((i=0; i<${#vpcache[*]}; i++)); do
    vpcacheblks=`expr $vpcacheblks + ${vpcache[$i]}`
done
vpcachemb=`echo 'scale=1; '${vpcacheblks}' / 256' | bc`
echo $vpcachemb

You can also use the output from onstat -g vpcache to work out at the number of missed drains using the formula (free - alloc) - drains.

If you have a busy system and particularly one with heavy peak periods, graphing the size of this over time is very interesting. Equally if your system is not that busy, you may see flat memory usage. It’s worth knowing which applies to you.

So if you’re reading this article and, having done a bit of investigation on your own system to see whether it affects you and found that it does, what can you do to mitigate? Here are some options:

  • Downgrade to 11.70.FC7 or earlier.
  • Set VP_MEMORY_CACHE_KB to 0. You can actually do this dynamically using onmode -wm to clear out the cache and then reset it to its original value immediately afterwards.
  • Increase SHMVIRTSIZE to accommodate the growth. Of course you need spare memory in your server to do this.
  • Set SHMTOTAL to remove the possibility of your server swapping. If you do, also look at setting up the low memory manager.

So what are IBM doing about the situation? An APAR has been raised as follows:

IC95684 AFTER THE FIX FOR IC90645 (11.70.FC7W1 AND NEWER) THE VP PRIVATE CACHES CAN GROW UNCONTROLLABLY ON BUSY SYSTEMS

This should result (in 11.70.FC8) in the documentation being updated and a choice of STATIC or DYNAMIC modes will be available for the VP caches. DYNAMIC will be the same as the new behaviour and STATIC is more similar to how things were previously where the VP caches were a fixed size. Note I said more similar and not the same. It will be interesting to look at how this behaves when it’s available.

There’s also another issue touched on here and I’ve used the new request for enhancement (RFE) site to log it and that is that onstat -g mem does not include the VP cache sizes in its output and is therefore not a complete view of all the memory pools in your instance. The RFE requests that it is.


Working with auto update stats

This article is superseded by my more comprehensive post, Experience with Auto Update Statistics (AUS).

This article has been written based on experience with version 11.70.FC5W1 and assumes some knowledge of stats and distributions and how they affect query optimisation. I appreciate any feedback in the comments section on my WordPress blog.

Auto update stats was introduced in 11.50.xC1 and, while being partly aimed at the embedded market, meant for the first time there was a complete solution to gathering database statistics bundled inside the product.

Several other tools exist to help with gathering statistics, for example AGS’s Server Studio can produce a set of UPDATE STATISTICS commands in a script to run against your database. Anecdotally, most DBAs use Art Kagel’s dostats utility, packaged up in the utils2_ak package available from the IIUG software repository. Dostats is pretty damn good although it’s not a complete solution as some scripts are needed to control it. It comes with a partner utility, drive_dostats, to do this but many DBAs, including myself, have written their own. Because dostats is the de-facto standard, I’ll refer to it a fair bit in this article. Also version 11.70 has a number of enhancements that don’t require you to use auto update stats; I’ll cover these as well.

So if you’re happily using dostats or another method to manage statistics, should you consider changing to auto update stats? Should it be your method of choice for a new-build instance? Well maybe: this article will go through some of the advantages and things to be aware of.

Here are some of its advantages:

  • The whole solution is part of the database engine and supported by IBM support.
  • It provides a complete framework, working within defined maintenance windows and is highly configurable.
  • It can be managed through OAT, although this is not needed.
  • Auto update stats does less work and takes less time than many solutions because it does not (by default) gather distributions on non-indexed columns or do separate low stats for each entire index key.
  • It’s fully integrated with the enhancements to update stats introduced in version 11.70.
  • It works on all your databases, including the system ones.
  • Perhaps my favourite feature: if you make manual adjustments, like increasing the resolution of the distributions on a column, auto update stats notices this and maintains the distributions at the new resolution. Similarly, if you manually create a distribution on a column it will maintain this.

One reason some DBAs don’t use auto update stats is because it involves using the job scheduler, which I’m told had issues in early releases with high CPU usage. For this reason, many DBAs touch a file called $INFORMIXDIR/etc/sysadmin/stop to stop it starting when the engine comes online. With 11.70.FC5W1 we run the job scheduler without any issues. (As an aside, if you’re not using it, it’s worth looking at the jobs to see what you’re missing: post_alarm_message is particularly useful.)

So can you just enable the job scheduler and let auto update stats do its thing? Not really. The first thing to look at is these onconfig parameters, which are in 11.70 and take effect regardless of the statistics method used:

  • AUTO_STAT_MODE
  • STATCHANGE
  • USTLOW_SAMPLE

Using auto stat mode and a non-zero value for STATCHANGE is something you need to consider very carefully. Internally the engine keeps a count of the number of inserts, updates and deletes that occur on each table, something that Keshava Murthy covered in his blog. If these collectively do not exceed STATCHANGE percent of the row count, statistics or distributions are not updated. This applies even when you run an UPDATE STATISTICS command manually. Confusingly the command still returns ‘Statistics updated’ even when nothing is done; the only clue is that the prompt returns instantly. To get around this there is a new FORCE keyword for the UPDATE STATISTICS statement that reverts to the old behaviour.

I find turning AUTO_STAT_MODE on and setting STATCHANGE to zero works quite well: this just skips tables where no updates, inserts or deletes have occurred.

You can set the value of STATCHANGE manually on individual tables with a fast-alter operation:

alter table table statchange change_threshold;

As it’s an update to systables, be aware an exclusive table lock is briefly needed.

I’m not keen on setting STATCHANGE to non-zero value because we have a lot of tables with incrementing date/time fields, meaning query optimisation is often time-based. I would find the option to override the age-based AUS_AGE parameter on a per-table basis much more useful, something that can only be done by writing your own script. Fortunately, as auto update stats evaluates the tables with stale stats on a regular scheduled basis, any ad-hoc updates are taken into account in its scheduling.

Setting USTLOW_SAMPLE enables sampling for UPDATE STATISTICS LOW statements, which is generally a good thing and can dramatically the time these statements take. It can be overridden in your user environment. Sampling generally works well as long as the table is not heavily skewed in some way: if Informix thinks it is you’ll see messages like this in the online log:

Warning: update statistics low using sampling may generate inaccurate index statistics for index owner.index_name due to data skew

Whether this is an issue for you will depend very much on your queries.

The other major enhancement in 11.70 is fragment-level statistics but I shan’t cover in detail here. If your storage schema is compatible with it and your table access patterns mean that some table fragments are never updated, it’s extremely useful. Informix’s implementation is nice in that the table stats are still considered as a whole when the optimiser evaluates queries, so you don’t get into trouble with having no stats for new fragments.

Perhaps the most significant difference between dostats and auto update stats is that dostats gathers additional distributions on non-indexed columns using UPDATE STATISTICS MEDIUM. If you have such distributions already auto update stats will continue to maintain them but it won’t create any new ones. All automated tools are attempting to apply a set of general criteria and recommendations to all tables so there is no hard and fast rule about whether you need them. One case to consider is where you have some sort of status flag such as a boolean or a column with a limited set of allowed values, perhaps enforced by a check constraint. Here distributions could be useful where these columns are used as filter conditions in queries. Otherwise, I suspect that in a lot of cases they are not needed. You’ll need to decide what is appropriate for your system.

Dostats also gathers low statistics separately for different indices which takes extra time but in my tests using version 11.50.FC9W2 this didn’t make any difference to the end result.

So what about switching on auto update stats? For this you’ll need to turn on the task scheduler if it’s not running already, which can be done with:

database sysadmin;
execute function task("scheduler start");

I would strongly recommend reviewing the enabled (and/or disabled) tasks and switch off any you don’t want or are not sure about. Review the jobs with:

database sysadmin;
select tk_name, tk_description from ph_task where tk_enable='t';

The relevant jobs for auto update stats are mon_table_profile, Auto Update Statistics Evaluation and Auto Update Statistics Refresh. Most of the others are fairly benign but I disable auto_tune_cpu_vps, add_storage, Low Memory Reconfig and mon_low_storage:

database sysadmin;
update ph_task set tk_enable = 'f' where tk_name in ('auto_tune_cpu_vps', 'add_storage', 'Low Memory Reconfig', 'mon_low_storage');

You’ll also find in table ph_threshold several parameters related to auto update stats:

  • AUS_AGE
  • AUS_PDQ
  • AUS_CHANGE
  • AUS_AUTO_RULES
  • AUS_SMALL_TABLES

Most of these are well-documented in the manual but the explanation of parameter AUS_AUTO_RULES is unclear as it just talks about enforcing a base set of rules. My understanding of the parameter is that:

  • When set to zero, auto update stats just maintains whatever statistics and distributions you have already. This retains any custom resolutions and confidence values you may have.
  • When set to one, it does the above plus it also makes sure that low stats are gathered on all tables, distributions in high mode for all leading index columns and distributions in medium mode for columns that are part of an index but not a leading key.

You can just update the parameters with manual SQL updates on the ph_threshold table. Likewise you’ll need to review and possibly update the scheduled run times for the two auto update statistics tasks in the ph_task table.

By default you just get one process updating stats but it’s possible to have two or more running at the same time by inserting a new row into table ph_task. I’d make sure that the total effective PDQ priority of all these tasks does not exceed 100.

At this point we’re sort of ready to go but you’re now trusting your stats gathering to a new process and I would suggest setting up some kind of monitoring to make sure it’s working as you expect. I feel this is a slight weakness in the implementation because you are back to writing your own scripts. Maybe one answer is to use OAT but this is not a good solution in our environment.

I would suggest monitoring the following:

  • That all three tasks related to auto update stats are enabled and scheduled to run at least once a week.
  • That the db scheduler is running, perhaps by checking for its threads with onstat -g ath.
  • That the values for the various AUS* parameters are sane.
  • That UPDATE STATISTICS LOW was not run too long ago for all tables. If you set AUTO_STAT_MODE it gets a little more complicated because you’ll need to use the information in Keshava Murthy’s blog post, referenced above, to work out whether the table needs to be updated.
  • Something similar for your distributions.
  • For any issues encountered whilst running the UPDATE STATISTICS statements. For this query table aus_command and check for columns where aus_cmd_state is E for error. The SQL error code and ISAM error code will then be in columns aus_cmd_err_sql and aus_cmd_err_isam respectively.

One problem you might find is that your scheduled maintenance times are not long enough to keep pace with how frequently you require your stats to be updated. You can look at adding an extra process or extending the times in this case. Even better, consider reading John Miller’s excellent article on tuning update statistics. It’s now over ten years old but still completely relevant today.

There is a view in the sysadmin database called aus_cmd_comp which shows you all the commands run recently. It gets purged daily so if you want to keep a permanent or longer record you might want to consider writing a procedure to copy its contents elsewhere and creating a scheduled task to call it.

It’s worth noting that auto update stats doesn’t do anything with stored procedure plans, i.e. UPDATE STATISTICS FOR PROCEDURE. If there are open statement handles using procedures, doing so can risk a -710 error unless (and sometimes even if) AUTO_REPREPARE set in your onconfig. Whatever the situation on your system you’ll need to do this manually or by another means.

Finally you might be wondering how the Auto Update Statistics Evaluation task prioritises tables for updating. The answer to this is in the procedures in $INFORMIXDIR/etc/sysadmin/sch_aus.sql.

In summary I like auto update stats and recommend it as long as you have a good understanding of how it works and are aware of the points I’ve raised in this article. It integrates nicely with the new features in Informix 11.70. I like the fact that it is easy to set up, although I do believe you need to monitor it if up to date stats are critical to your system. By not gathering medium-mode distributions on non-indexed columns and not running update statistics low for leading index columns, it does significantly less work than dostats. I appreciate the nice touches it has, like retaining and maintaining the existing resolution of your statistics.

As I said at the start of the article, feedback is welcomed and encouraged.


Prepared statements and the SQL statement cache

Recently I was asked whether I use the SQL statement cache. The answer was no but the more interesting question was why not. When I thought about the reasons why not most of them were out of date or boiled down to a lack of understanding of the finer details on my part.

Thinking about it for a little longer, Informix has always been efficient at parsing or optimising SQL statements and this is an area where it seems to scale without difficulty so I have never had great cause to turn it on. However, Informix must also be using extra CPU cycles for parsing and optimising when a good plan could be ready to go in the cache.

A performance test could be the subject of a future blog post but I want to look at controlling the cache. For example, what happens if a “bad plan” gets cached? How would I get it out of there or force a re-parse and re-optimisation?

As ever, I will be using my trusty sandbox to investigate, currently running Informix 11.70.FC5W1 with STMT_CACHE set to 1 (enabled at session level).

For my tests I set up a log table, the sort of thing that could be used to record user log-ons, with two columns: a varchar for the user name and a date/time column for the log-on time. Both these columns are separately indexed. I will query the table using both columns in the where clause, giving the optimiser the choice of using either index (or a full-scan, in practice not used) to read from the table. The log-on time index is useful for queries intended to show the latest log-ons, independent of the user, but Informix may also be tempted to use it if the expected number of values returned is low and it is deemed to be more selective than the index on the user name. For the queries I am going to run, the user name index will generally be the most efficient but might not always be part of the plan chosen.

I’ll execute the same prepared statement twice with two sets of bind variables. In my tests I want to find out:

  • In the case where the statement cache is off or the statement is not in the cache, what determines the initial query plan?
  • Once the query is cached, what can I do, short of flushing the entire cache, to force a re-parse?

The schema for my test is:

create schema authorization informix
create table logons (
  logon_timestamp datetime year to second,
  username varchar(32)
) extent size 460000 next size 20000 lock mode row;

create index informix.ix_username on informix.logons (username) using btree;
create index informix.ix_logon_timestamp on informix.logons (logon_timestamp) using btree;

I have around 8 million unique rows in this table and high level statistics at resolution 0.5 on both columns.

The Perl code snippet I am going to test with is:

$$dbh->do("SET EXPLAIN ON");
$$dbh->do("SET STATEMENT CACHE OFF"); # Change to ON, as required

my $sql = "SELECT * FROM logons WHERE logon_timestamp BETWEEN ? AND ? AND username=?";
my $sth = $$dbh->prepare($sql);

# Execute prepared statement without any bind variables (forces optimisation). Comment out to test optimisation with bind values.
$sth->execute();

# Execute prepared statement with given bind variables: change as required
$sth->execute('2010-01-11 12:50:50', '2013-01-11 12:50:50', 'BTHOMPSON');
my $count = 0;
while (my @columns = $sth->fetchrow_array) {
    print "$columns[0]\n";
    $count++;
    last if $count == 10;
}

# Execute prepared statement with given bind variables: change as required
$sth->execute('2013-01-11 12:50:50', '2013-01-11 12:50:50', 'BTHOMPSON');
$count = 0;
while (my @columns = $sth->fetchrow_array) {
    print "$columns[0]\n";
    $count++;
    last if $count == 10;
}

$sth->finish();

The key thing about the code is that the statement uses bind variables and is prepared only once.

Some initial results are (with the statement cache off):

Lower timestamp bind value Upper timestamp bind value User name bind value Index used
2013-01-11 12:50:50 2013-01-11 12:50:50 BTHOMPSON ix_logon_timestamp
2010-01-11 12:50:50 2013-01-11 12:50:50 BTHOMPSON ix_username
<None> <None> BTHOMPSON ix_logon_timestamp

So far, so fiddled. But one interesting result has already dropped out. If I prepare and optimise the statement without any bind values I fix the plan. I achieve this in Perl by using the $sth->execute function but I don’t provide any bind values and don’t fetch any rows. When I re-execute the statement with bind variables I find the statement has already been optimised and subsequent executions will use the same plan. I had expected that I would have to supply some real bind variables but this appears to be the case even with no bind variables initially supplied. I am not sure what this means in practice, since you probably not do this in your code, but it is an interesting result nonetheless. It is certainly not the same as binding blank values or nulls and Informix will generate an explain plan for the query the first time $sth->execute is called.

Let’s switch on the statement cache (SET STATEMENT CACHE ON) and see if there are any differences. Well, there are not the first time the script is run but subsequent runs with different settings will re-use the initial plan. We need to flush the cache with onmode -e flush each time to force the plan to be reparsed.

We can see that there is now a statement cache entry with onstat -g ssc:

> onstat -g ssc

IBM Informix Dynamic Server Version 11.70.FC5W1 -- On-Line -- Up 6 days 02:45:27 -- 1224528 Kbytes

Statement Cache Summary:
#lrus   currsize             maxsize              Poolsize             #hits   nolimit 
4       24440                524288               40960                0       0       

Statement Cache Entries: 

lru hash ref_cnt hits flag heap_ptr      database           user
--------------------------------------------------------------------------------
  0   51       0    0   -F 83df8038      optimiser_test     thompsonb
  SELECT * FROM logons WHERE logon_timestamp BETWEEN ? AND ? AND username=?



    Total number of entries: 1.

As I mentioned before, in this example using the plan using the index on logon_timestamp is generally a poor choice for anything other than the smallest time ranges. As a common plan for this query, the index on user name would be the best. So what am I to do when the statement cache is on and the first used bind values caused the optimiser to settle on the index on logon_timestamp?

onmode -e flush is going to work but it’s a bit of a sledge hammer and might mean I have to watch for other queries being re-optimised badly. Another alternative is to perform some DDL on (one of) the table(s) in the query. This still affects all queries using that table but is more targetted than flushing the cache. A trick I have learned from Oracle, where this is a common problem as all statements are cached in the shared pool, and which also works with Informix is to perform a grant or revoke as the DDL statement, e.g.:

grant select on informix.logons to thompsonb as informix;

If done carefully, you can grant a privilege that is not needed and then revoke it again immediately afterwards.

One good thing is that when you prepare and then execute the query for which there is already a plan in the statement cache, the explain output will show you the cached plan. One clue that it is a cached plan and has not been reprepared seems to be that the estimated cost is shown as zero.

As a result of these tests, I now feel better equipped to investigate and deal with query performance issues on instances where the instance cache is switched on globally or enabled at session level.


-710: Table <table> has been dropped, altered, or renamed.

During a recent meet-up with some IBM developers, we brought up the topic of error -710: Table <table> has been dropped, altered, or renamed. This error usually occurs when a prepared SQL statement has been invalidated by a schema change of some kind and can make code deployment or adding indices difficult in a live environment while applications are running.

IBM told us they have made significant progress with this problem. Firstly, in version 11.10, the onconfig parameter AUTO_REPREPARE was added which facilitated the automatic re-preparing of an already prepared SQL statement internally by the Informix engine if it had been invalidated by a schema change. Unfortunately this fix didn’t cover all scenarios, something IBM were open about at the time and there is an article in the IDS Experts blog on developerWorks covering the progress that was made then.

However, IBM now tell us they have made further progress on this problem to the point now where the -710 error should occur less frequently, if at all.

The developerWorks article describes six scenarios where a -710 error could occur in Informix 10.00 or earlier and the first three of these scenarios are fixed in Informix 11.10 when AUTO_REPREPARE is switched on. I thought it would be useful to create a test case for each of these and see if the problem has indeed been resolved.

The article gives more information but the six scenarios are:

  1. An index created on a table where a select statement has already been prepared and executed will cause that select statement to fail when it is executed again.
  2. A similar scenario where the select statement has not been previously used, a cursor is used and the select uses select *.
  3. A more complex example involving two tables, one with a trigger, and two procedures for which some code is supplied.
  4. Changing the number and type of columns in a select statement.
  5. Executing a prepared DDL statement, presumably where other DDL has been run on the table.
  6. An unspecified race condition.

All of this, apart from the last item, the race condition, is fairly easy to script up and test, although one slight caveat is that I prefer Perl-DBI and know little about languages like ESQL/C so I don’t have explicit control of cursors.

Thanks to a chum, I got hold of a copy of Informix 10.00.FC10 and ran through tests one to five:

sandbox_shm thompsonb@informix1:~ > ./710_test_cases.pl all

Running test 1: $prepare s1 from "select c1, c2 from t710";$execute s1;$create index i1 on t710(c1);$execute s1; -------> -710 error

Dropping existing table 't710':
done.
Creating table 't710':
done.
Inserting some data into table 't710':
done.
Selecting values:

c1 c2
10 10
20 20
30 30
56 56

Creating index:
done.
Re-using prepared statement to select values:

c1 c2
DBD::Informix::st execute failed: SQL: -710: Table (thompsonb.t710) has been dropped, altered or renamed. at ./710_test_cases.pl line 80.
Closing statement handle:
done.

Running test 2: $prepare s1 from "select * from t710 where c1 = 10";$declare curs1 cursor for s1;$create index i1 on t710(c1);$open curs1; -------> -710 error

Dropping existing table 't710':
done.
Creating table 't710':
done.
Inserting some data into table 't710':
done.
Preparing select statement:
done.
Creating index:
done.
Using prepared statement to select values:

c1 c2
DBD::Informix::st execute failed: SQL: -710: Table (thompsonb.t710) has been dropped, altered or renamed. at ./710_test_cases.pl line 113.
Closing statement handle:
done.

Running test 3: create procedure p1(c_a int, c_b int) returning integer;insert into A values(1001, 1001);update A set b=c_b where A.a=c_a; ==>(You have an update trigger defined on A which inserts into table B return 0;end procedure; create procedure p2() returning integer;define i integer;let i=p1(56, 56);create index i1 on B(b);return p1(56, 56); -- > -710 error when p1 is executedend procedure;

Dropping existing table 'a':
done.
Creating table 'a':
done.
Inserting some data into table 'a':
done.
Dropping existing table 'b':
done.
Creating table 'b':
done.
Inserting some data into table 'b':
done.
Dropping existing procedure 'p1':
done.
Creating procedure 'p1':
done.
Dropping existing procedure 'p2':
done.
Creating procedure 'p2':
done.
Creating update trigger on table 'a' which inserts into table 'b':
done.
Preparing statement to execute procedure 'p2':
done.
Executing procedure 'p2':
DBD::Informix::st fetch failed: SQL: -710: Table (thompsonb.b) has been dropped, altered or renamed. at ./710_test_cases.pl line 167.
done.
Closing statement handle:
done.

Running test 4: The number and type of columns in your SELECT list have changed.

Dropping existing table 't710':
done.
Creating table 't710':
done.
Inserting some data into table 't710':
done.
Preparing select statement from table 't710':
done.
Adding extra column to table 't710':
done.
Selecting from table 't710' using prepared statement:

c1 c2
DBD::Informix::st execute failed: SQL: -710: Table (thompsonb.t710) has been dropped, altered or renamed. at ./710_test_cases.pl line 195.
done.
Closing statement handle:
done.

Running test 5: If you are executing a prepared DDL statement, you might get -710 errors.

Dropping existing table 't710':
done.
Creating table 't710':
done.
Inserting some data into table 't710':
done.
Prepare a statement to add column 'c3' to table 't710':
done.
Add a column 'c4' to table t710:
done.
Execute the prepared statement to add column 'c3' to table 't710':
DBD::Informix::st execute failed: SQL: -710: Table (thompsonb.t710) has been dropped, altered or renamed. at ./710_test_cases.pl line 227.
done.
Closing statement handle:
done.

Exiting cleanly.

Great (in a way): all the test cases give a -710 error in Informix 10.00! I’ve put my other results in a table; a fail indicates that I got the -710 error:

Test Informix 10.00.FC10 Informix 11.50.FC9W2 without AUTO_REPREPARE Informix 11.50.FC9W2 with AUTO_REPREPARE Informix 11.70.FC5W1 without AUTO_REPREPARE Informix 11.70.FC5W1 with AUTO_REPREPARE
1 Fail Fail Pass Fail Pass
2 Fail Fail Pass Fail Pass
3 Fail Fail Pass Fail Pass
4 Fail Fail Pass Fail Pass
5 Fail Fail Fail Fail Fail

Unfortunately I do not have Informix 11.10 to test with and it is no longer available for download. It would be interesting to know whether it passes test 4 or not with AUTO_REPREPARE set.

Is the failure of test 5 by the latest releases important? I would say not. Preparing a DDL statement, then preparing and executing another on the same table before executing the first prepared statement is certainly not something I’ve seen done before in a real environment.

If anyone would like to have a go at reproducing my results or has any comments on my implementation of the test cases, I have posted the code here.