Update Stats Issue
MARK JALKIEWICZ saw different optimizer plans for one query on two identical 11.50.FC8 AIX ER servers. He then found in the update statistics log that a distributions-only run failed with error 312 and ISAM error 107 (record locked) on sysdistrib, and said things were much better. Art Kagel suggested a rebuilt index on only one server or differing on-disk table layout as other causes.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
11.50.FC8 AIX We have two identical servers (ER pairs), the database/tables are exact mirror images of each other with respect to data, table schema etc. The layout of the instances are the same etc. as are the $ONCONFIGS. But, for one query I get two different optimization plans, one very wrong and one correct. As far as I can tell, both of these tables are identical with respect to data and is their schema's including indexes as is their data (ER pairs). Update stats is run once weekly at the same time per the guidelines (update stats low, high for col heading an index and med for non-heading). Anyone know how this can/could happen ? Thanks, Mark
never mind.
found the following buried deep in the update stats log:
update statistics high for tabletbd1 ( cust_no ) distributions only;
312: Cannot update system catalog (sysdistrib).
107: ISAM error: record is locked.
Error in line 860Near character position 33
things much better now.
Mark
never mind.
found the following buried deep in the update stats log:
update statistics high for tabletbd1 ( cust_no ) distributions only;
312: Cannot update system catalog (sysdistrib).
107: ISAM error: record is locked.
Error in line 860Near character position 33
things much better now.
Mark
You may have rebuilt an index on one and not the other so that the index's internal layout is more efficient on the one server than the other prompting a different query plan. Also, if the rows were all added from one server and replicated to the other then the on-disk layout of the tables may be different with different actual extents. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Apr 1, 2013 at 4:01 PM, MARK JALKIEWICZ <mark.jalkiewicz@verizon.net > wrote: > 11.50.FC8 AIX > > We have two identical servers (ER pairs), the database/tables are exact > mirror > images of each other with respect to data, table schema etc. The layout of > the > instances are the same etc. as are the $ONCONFIGS. > > But, for one query I get two different optimization plans, one very wrong > and > one correct. As far as I can tell, both of these tables are identical with > respect to data and is their schema's including indexes as is their data > (ER > pairs). Update stats is run once weekly at the same time per the guidelines > (update stats low, high for col heading an index and med for non-heading). > > Anyone know how this can/could happen ? > > Thanks, > > Mark > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --f46d0408395d2d3a7304d96bf740