"One's Too Many" <on*******@yahoo.com> wrote in message
news:80**************************@posting.google.c om...
Ran into a strange problem today:
8.1.7 on AIX 4.3.3
Database and applications had been working fine for two years and all
of a sudden a couple of regularly-run queries are now no longer coming
out in correct sort order specified in the ORDER clauses of the select
statements. Behavior is erratic, about half the time the sort order is
correct, and the other half is not. All expected rows are being
returned (no data is missing) and the "out-of-sort-order-ness" appears
in contiguous blocks of rows returned. For a crude analogy of what
we're seeing: the bad results would come back like first all the A's,
then all the B's then all the C's then all the E's, then all the D's,
then the F's, etc (the values sorted erroneously are actually mixed
alphanumerics, not pure alphas). The tables being queried contain
several hundreds of thousands to a few millions of rows each. The
queries had worked fine for two years, and there have been no schema
changes or application code changes at all. The only thing that has
changed is that a very large quantity of new rows have been inserted
into most of the tables very recently. The client is reluctant to let
me take the database down long enough for a complete
export/import/diskspace re-organization, which it really needs, so I'm
about to suggest that we at least drop and re-create all the indexes
on the tables involved. All indexes are showing "valid" status in
sys.dba_objects, however.
You don't need a reorg, that isn't going to fix the problem. Here are a
couple of things you can try (non-destructive, db can stay up)(no particular
order):
1. Export those tables that are giving you a problem. You can export to a
null device as you really don't need the export file(s). Do use a parameter
file and specify where to put the log. Look in the log and see if the
export failed. I have seen instances where a disk error happened and Oracle
didn't catch it , but the export does a full table scan and thus reads every
block that the table occupies. If there is some sort of block corruption
then the export will fail and it will show up in the log file.
2. You can do an
analyze table foo validate cascade;
See if you get an error there. This will lock the table and its indexes as
it runs and and so you have to be careful as to how it will effect the other
users that are accessing the data.
3. Look at one of the queries that is demonstrating this unusual behavior
and see what the explain plan is. If an index is not being used to do the
sort; it is unlikely that than index is the problem. Check the nls_sort
parameter and see what it is set to on the client. (this could happen and
give wierd results) Are you explicitly setting it when the application
connects to the back end? (AFIK which would override what the client is set
to )
4. Do a trace and see if in fact there is an order by statement. I've seen
people think group by should implicitly do an order by (and it shouldn't be
relied on to).
Jim
