Confusion about update statistics

Submitted by prabaldas on Thu, 2009-09-10 16:41

A confusion about when to use update statistics high medium or low for any table.

Suppose I have unique index on (col.1, col.2)
and composite index on (col.3, col.4, col.5)

where to use high/medium/low and when to use
FOR DISTRIBUTION ONLY ?

Kindly help

Prab

‹ onaudit + onshowaudit Utility (How auditing is made in the IDS 11.5) Informix Online vs. IDS ›

It's always useful to specify

Submitted by Spokey on Thu, 2009-09-10 18:47.

It's always useful to specify what version of IDS you are using, because the recommendations do change over time. However, assuming you are using IDS 11, then the manual pages are here and contain much more detail.

However, in general, the rules are:

To generate statistics on a table

1. Identify the set of all columns that appear in any single-column or multi-column index on the table.
2. Identify the subset that includes all columns that are not the leading column of any index.
3. Run UPDATE STATISTICS LOW on each column in that subset.

To build data distributions for each table that your query accesses

1. Run a single UPDATE STATISTICS MEDIUM for all columns in a table that do not head an index.

Use the default parameters unless the table is very large, in which case you should use a resolution of 1.0 and confidence of 0.99.
2. Run the following UPDATE STATISTICS statement to create distributions for non-index join columns and non-index filter columns:

UPDATE STATISTICS MEDIUM DISTRIBUTIONS ONLY;

3. Run UPDATE STATISTICS HIGH for all columns that head an index. For the fastest execution time of the UPDATE STATISTICS statement, you must execute one UPDATE STATISTICS HIGH statement for each column.
4. If you have indexes that begin with the same subset of columns, run UPDATE STATISTICS HIGH for the first column in each index that differs.
5. For each single-column or multi-column index on the table:

1. Identify the set of all columns that appear in the index.
2. Identify the subset that includes all columns that are not the leading column of any index.
3. Run UPDATE STATISTICS LOW on each column in that subset. (LOW is the default.)
6. For the tables on which indexes were created in Step 3, run an UPDATE STATISTICS statement to update the sysindexes and syscolumns system catalog tables, as shown in the following example:

UPDATE STATISTICS FOR TABLE t1(a,b,e,f);

7. For small tables, run UPDATE STATISTICS HIGH, for example:

UPDATE STATISTICS HIGH FOR TABLE t2;

If you don't say DISTRIBUTIONS ONLY, then the statement does the work of UPDATE STATISTICS LOW as well.

So, for your specific example, I would:
UPDATE STATISTICS LOW on (col2, col4, col5);
UPDATE STATISTICS MEDIUM on (col2, col4, col5) DISTRIBUTIONS ONLY;
UPDATE STATISTICS HIGH on (col1);
UPDATE STATISTICS HIGH on (col3);

Do bear in mind that updating statistics can take a long time and may not always be needed. There are many occasions when just doing UPDATE STATISTICS LOW can provide adequate performance, particularly if the data has a normal distribution. Also bear in mind that unless the data distribution has changed significantly, you may only need to run UPDATE STATISTICS LOW as the existing distributions may provide the optimiser with a "good enough" approximation.

I hope this helps.

RE: Confusion about Update Stats

Submitted by prabaldas on Fri, 2009-09-11 09:34.

Thanks for the detailed idea on Update Statistics.

Informix version is 5.xx and it has no performance problem.
The problem is with space information. Some of the tables does not accept any insert and I have to delete data to have enough space. tbstat -d output shows that there is plenty of space in all the dbsps. I am not very sure whether it is providing correct info.

Will update statistics for all tables in the db (single db) help in this case to determine the actual space occupied? Also its a very busy databse.

Aha! OK, just as well I asked

Submitted by Spokey on Sun, 2009-09-13 15:00.

Aha! OK, just as well I asked then, isn't it?

OK, UPDATE STATISTICS has absolutely nothing to do with your problem. UPDATE STATISTICS statistics are the ones used by the optimiser to decide how to process queries, and while they're important, they have nothing to do with not being able to insert data or the data reported by tbstat -d.

I suspect that you will probably find that you have run out of extents or logs or (and I really hope it's not this!) a long transaction.

Have you run a logical log backup recently? If not, try that and see if it frees things up.

If it doesn't, can you provide me with your tbconfig file and the output from tbstat -l as a starting point? Oh, and the tbstat -d output as well?

Info you required

Submitted by prabaldas on Mon, 2009-09-14 18:08.

Hi,

tbcheck -pt dbname / tbcheck -pT dbname:tabname is giving error : Database not found
but tbcheck -pe is working well. It shows that only 6 tables have more than 8 extents and those are not problem table

Recently I had the same issue with space. Just at that time I took the following output. hex(partnum) shows the table is residing in dbs no. 3

tbcheck -pe shows that the table is appearing in 3 chunks 7 times

tbstat -l

RSAM Version 5.11.HC1 -- On-Line -- Up 23 days 09:09:52 -- 83332 Kbytes

Physical Logging
Buffer bufused bufsize numpages numwrits pages/io
P-2 0 16 14859 931 15.96
phybegin physize phypos phyused %used
101f8c 153600 130815 0 0.00

Logical Logging
Buffer bufused bufsize numrecs numpages numwrits recs/pages pages/io
L-2 0 16 422 52 52 8.1 1.0

address number flags uniqid begin size used %used
a29285cc 1 F------ 0 264844 14950 0 0.00
a29285e8 2 F------ 0 2682aa 14950 0 0.00
a2928604 3 F------ 0 26bd10 14950 0 0.00
a2928620 4 F------ 0 201f82 14950 0 0.00
a292863c 5 F------ 0 2059e8 14950 0 0.00
a2928658 6 U---C-L 1566 20944e 14950 5786 38.70
a2928674 7 F------ 0 20ceb4 14950 0 0.00
a2928690 8 F------ 0 21091a 14950 0 0.00
a29286ac 9 F------ 0 214380 14950 0 0.00
a29286c8 10 F------ 0 217de6 14950 0 0.00
a29286e4 11 F------ 0 21b84c 14950 0 0.00
a2928700 12 F------ 0 21f2b2 14950 0 0.00
a292871c 13 F------ 0 222d18 14950 0 0.00
a2928738 14 F------ 0 22677e 14950 0 0.00
a2928754 15 F------ 0 22a1e4 14950 0 0.00
a2928770 16 F------ 0 22dc4a 14950 0 0.00
a292878c 17 F------ 0 2316b0 14950 0 0.00
a29287a8 18 F------ 0 235116 14950 0 0.00
a29287c4 19 F------ 0 238b7c 14950 0 0.00
a29287e0 20 F------ 0 23c5e2 14950 0 0.00
a29287fc 21 F------ 0 240048 14950 0 0.00
a2928818 22 F------ 0 243aae 14950 0 0.00
a2928834 23 F------ 0 247514 14950 0 0.00
a2928850 24 F------ 0 24af7a 14950 0 0.00
a292886c 25 F------ 0 24e9e0 14950 0 0.00
a2928888 26 F------ 0 252446 14950 0 0.00
a29288a4 27 F------ 0 255eac 14950 0 0.00
a29288c0 28 F------ 0 259912 14950 0 0.00
a29288dc 29 F------ 0 25d378 14950 0 0.00
a29288f8 30 F------ 0 260dde 14950 0 0.00

Dbspaces
address number flags fchunk nchunks flags owner name
a096fd8c 1 1 1 1 N informix rootdb
a096fdbc 2 1 2 1 N informix logcent
a096fdec 3 1 3 1 N informix rootdal
a096fe1c 4 1 4 1 N informix rootnf
a096fe4c 5 1 5 1 N informix rootnw
a096fe7c 6 1 6 1 N informix datada
a096feac 7 1 7 1 N informix datanf
a096fedc 8 1 8 1 N informix datanw
8 active, 12 total

Chunks
address chk/dbs offset size free bpages flags pathname
a096f66c 1 1 0 450000 288136 PO- /dev/informix/rootcent
a096f704 2 2 0 480000 23434 PO- /dev/informix/logcent
a096f79c 3 3 0 160000 155200 PO- /dev/informix/rootdal
a096f834 4 4 0 160000 154792 PO- /dev/informix/rootnf
a096f8cc 5 5 0 160000 155480 PO- /dev/informix/rootnw
a096f964 6 6 0 650000 403277 PO- /dev/informix/datada
a096f9fc 7 7 0 650000 345048 PO- /dev/informix/datanf
a096fa94 8 8 0 650000 502766 PO- /dev/informix/datanw
8 active, 12 total

tbstat -t

RSAM Version 5.11.HC1 -- On-Line -- Up 23 days 13:48:56 -- 83332 Kbytes

Tblspaces
n address flgs ucnt tblnum physaddr npages nused npdata nrows nextns
0 a287c7cc 1 1 1000001 10000e 8063 8063 0 0 1
1 a287c8dc 1 1 2000001 200004 8063 2 0 0 1
2 a287c9ec 1 1 3000001 300004 3205 32 0 0 1
3 a287cafc 1 1 4000001 400004 3205 32 0 0 1
4 a287cc0c 1 1 5000001 500004 3205 32 0 0 1
5 a287cd1c 1 1 6000001 600004 8063 171 0 0 1
6 a287ce2c 1 1 7000001 700004 8063 168 0 0 1
7 a287cf3c 1 1 8000001 800004 8063 167 0 0 1
8 active, 2000 total, 512 hash buckets

( The root dbspace is full right now)

tbstat -c

# Root Dbspace Configuration

ROOTNAME rootdb # Root dbspace name
ROOTPATH /dev/informix/rootcentral
# Path for device containing root dbspa
ROOTOFFSET 0 # Offset of root dbspace into device (Kbytes)
ROOTSIZE 900000 # Size of root dbspace (Kbytes)

# Disk Mirroring Configuration

MIRROR 0 # Mirroring flag (Yes = 1, No = 0)
MIRRORPATH # Path for device containing root dbspace mirror
MIRROROFFSET 0 # Offset into mirror device (Kbytes)

# Physical Log Configuration

PHYSDBS rootdb # Name of dbspace that contains physical log
PHYSFILE 307200 # Physical log file size (Kbytes)

# Logical Log Configuration

LOGFILES 30 # Number of logical log files
LOGSIZE 29900 # Size of each logical log file (Kbytes)

# Message Files

MSGPATH /usr/informix/turbo.cst # OnLine message log pathname
CONSOLE /dev/null # System console message pathname

# Archive Tape Device

TAPEDEV /informix_archive/archive_cst/cst_db.archive
# Archive tape device pathname
TAPEBLK 16 # Archive tape block size (Kbytes)
TAPESIZE 2200000 # Max amount of data to put on tape (Kbytes)

# Logical Log Backup Tape Device

LTAPEDEV /informix_archive/archive_cst/cst.log
# Logical log tape device pathname
LTAPEBLK 16 # Logical log tape block size (Kbytes)
LTAPESIZE 2200000 # Max amount of data to put on log tape (Kbytes

# Identification Parameters

SERVERNUM 14 # Unique id associated with this OnLine instance
DBSERVERNAME fsac_cst # Unique name of this OnLine instance

# Shared Memory Parameters

RESIDENT 0 # Forced residency flag (Yes = 1, No = 0)
USERS 300 # Maximum number of concurrent user processes
TRANSACTIONS 300 # Maximum number of concurrent transactions
LOCKS 768000 # Maximum number of locks
BUFFERS 21600 # Maximum number of shared memory buffers
TBLSPACES 2000 # Maximum number of active tblspaces
CHUNKS 12 # Maximum number of chunks
DBSPACES 12 # Maximum number of dbspaces and blobspaces
PHYSBUFF 32 # Size of physical log buffers (Kbytes)
LOGBUFF 32 # Size of logical log buffers (Kbytes)
LOGSMAX 30 # Maximum number of logical log files
CLEANERS 4 # Number of page-cleaner processes
SHMBASE 0x0 # Shared memory base address
CKPTINTVL 300 # Checkpoint interval (in seconds)
LRUS 8 # Number of LRU queues
LRU_MAX_DIRTY 15 # LRU modified begin-cleaning limit (percent)
LRU_MIN_DIRTY 10 # LRU modified end-cleaning limit (percent)
LTXHWM 50 # Long TX high-water mark (percent)
LTXEHWM 60 # Long TX exclusive high-water mark (percent)

# Machine- and Product-Specific Parameters

DYNSHMSZ 0 # Dynamic shared memory size (Kbytes)
GTRID_CMP_SZ 32 # Number of bytes to use in GTRID comparision
DEADLOCK_TIMEOUT 60 # Max time to wait for lock in distributed env.
TXTIMEOUT 300 # Transaction timeout for I-STAR (in seconds)
SPINCNT 0 # No. of times process tries for latch
STAGEBLOB # Reserved for INFORMIX-OnLine/Optical

# System Page Size

BUFFSIZE 2048 # Page size (do not change!)

Two things: 1. Have you run

Submitted by Spokey on Mon, 2009-09-14 23:42.

Two things:

1. Have you run out of tablespaces?

2. Have you reported this to technical support?

1. Frequently facing the

Submitted by prabaldas on Tue, 2009-09-15 10:50.

1. Frequently facing the issue cannot insert new row and have to delete some old data
tbstat -d shows that there is enough space

2. We dont have tech support

Regds
Prab

1. What happens if you

Submitted by Spokey on Tue, 2009-09-15 14:29.

1. What happens if you increase TABLESPACES in your tbconfig and reboot the engine?

2. Well, perhaps you should get some then? As you can now see, there are times when you need it. :-)

Re :

Submitted by prabaldas on Wed, 2009-09-16 09:53.

Ok, I'll do that.

Shall it produce any benefit if I

1. Increase CHUNKS and DBSPACES in tbconfig

2. Add chunks to existing dbspaces

3. Create fresh dbspaces

4. Drop problem tables and recreate them in a fresh dbspace with extent size of 512
(presently extent size is 16 )

Well, I don't see the point

Submitted by Spokey on Wed, 2009-09-16 11:54.

Well, I don't see the point in increasing chunks or creating new dbspaces at the moment.

address chk/dbs offset size free bpages flags pathname
a096f66c 1 1 0 450000 288136 PO- /dev/informix/rootcent
a096f704 2 2 0 480000 23434 PO- /dev/informix/logcent
a096f79c 3 3 0 160000 155200 PO- /dev/informix/rootdal
a096f834 4 4 0 160000 154792 PO- /dev/informix/rootnf
a096f8cc 5 5 0 160000 155480 PO- /dev/informix/rootnw
a096f964 6 6 0 650000 403277 PO- /dev/informix/datada
a096f9fc 7 7 0 650000 345048 PO- /dev/informix/datanf
a096fa94 8 8 0 650000 502766 PO- /dev/informix/datanw

All your dbspaces seem to have enough space in them at the moment. Dropping tables and recreating them with an EXTENT SIZE that is big enough for the whole table and a NEXT SIZE of 512 may also help.

I suspect (but I can't prove it without access to your system) that the total number of TABLESPACES in your database is bigger than the number allowed in your onconfig. When you add rows to a table with full TABLESPACES, it's trying to create a new TABLESPACE and isn't allowed to, which is why it works if you delete some rows to make space. Something else you may want to try is to permanently drop any old tables that you don't need any more (once you've backed them up somehow, of course! :-)