HPUX
OE 10.2B03
After running update statistics on a DB, disk I/O went through the roof and performance tanked. Some VERY simple queries were taking forever even though the correct index was being used. I had to delete the statistics and then everything went back to OK.
Example query (using sports.customer table)
Index: order.custnum + Order.OrderDate (BOTH CHAR fields)
Query: SELECT * FROM order WHERE custnum = 123 AND OrderDate > '20141206' AND OrderDate < '20141231'
In the 4GL this returns maybe 100 rows. In SQL it ran and ran and ran....
I did not keep detailed notes during the actual PROD incident but I seem to remember that doing the SELECT with just one OrderDate condition was fine.
Dev is running 10.2B08 and while I can't say I explicitely tested this query, there were no issues reported. I will be going back to the DEV box and testing 10.2B03 and 10.2B08 with and w/out statistics but in the meanwhile I would love to hear from you guys.
SQL statistics are a very troublesome thing on older versions (older than V11.3) and there are quite a few bugs in the 10.2 product line. I know of one place that is still running 10.2A0305 in production because of the number of bugs they had fixed for SQL. Some still existed or were recreated in early versions of V11.
What did the explain plan look like?
Did you run the statistics for all columns or just the indexed columns? I know there are several bugs with updating the column statistics in older versions.
News to me. The KB is void of any mention for recent versions except one about complex views.
I do not have the explain plan but I will (hopefully) when I attempt to reproduce in DEV/TEST.
I ran UPDATE STATS for all tables, indexes and columns.
Severe performance degradation of SQL queries after running full update statistics
Thanks Rob. But this says "...against a complex view" which is not my case.
The KB is somewhat lacking on details on some of the SQL bugs/issues. I can't seem to find any of the issues that specific 10.2A03 hot fixes were created for either.
I have been bit by the "update statistics on all columns" on simple queries before (not just complex views). A lot of it seems to depend on the skew of your data and the indexes that are defined.
We had an issue a few months back with ranges on character fields that either caused the query to run forever or just crash with complaints of a corrupted index. It seemed to depend on the exact where clause, like including extra fields or changing the values for the indexed columns made the bug appear/disappear.
Of course the same 4GL query worked as expected and the index wasn't really corrupted.
For example... I can't seem to find anything about these in the KB
10.2B06 release notes:
Note: OE00217412 Type: Product Notice
Use UPDATE INDEX STATISTICS to resolve performance problem
---------------------------------
Release 10.2B06 contains enhancements to resolve the type of performance
problem related to non-uniform key value distribution.
10.2B08 release notes:
PSC00248453
=============================
SQL query slowdown after update index statistics
****************************************
Prior to 11.2 release, a query that normally runs in 3 or 4 seconds without
updating statistics, it takes about 20 seconds after update them involving some
tables and joins. Following is the update statistics command.
UPDATE TABLE STATISTICS AND INDEX STATISTICS AND ALL COLUMN STATISTICS FOR
pub.tableName;
Paul you may still be hitting that defect since the issue was only fixed in 11.3.2. So what happens if you do not update the column statistics ?
[mention:52bf50c6d46847059bd782ee700fdefc:e9ed411860ed4f2ba0265705b8793d05] I have not tried yet. Next week. We rolled back all the stats in the SYS tables.