Query occasionally returning empty results in 8i

From: BoukeB <bbruinsma_at_ortec.nl>
Date: 8 Jun 2006 00:39:23 -0700
Message-ID: <1149752363.529466.119530@j55g2000cwa.googlegroups.com>

We have a simple query (SELECT * FROM..,no WHERE clause) in 8i that sometimes returns empty results and ORA-01403: no data found. We have clues that it might be caused by settings in the init.ora, particularly for the sort area or the optimizer. The result of the query should be some 800 rows.

We have found a workaround by chance and that is to add a blank to the query at the end. The results are never empty then!

In fact, there are two 8i databases A and B on different machines each having their own particular init.ora. On B, the query sometimes comes back empty, on A never. A and B have been synchronized by copying B's init.ora onto A's. We now sometimes see empty query results for A also!

Are there any experiences with this phenomenon? Can anyone comment on our presumption that the problem is caused by sort area size or the optimizer settings? The fact that the problem disappears when we add a blank might suggest the optimizer as the cause. We would be thankful for any help or suggestions.

Below is an overview of differences in the init.ora.

           Old                                        New

buffer_pool_keep = 100
buffer_pool_recycle = 100
db_block_lru_latches = 6
db_block_max_dirty_target = 16384
distributed_transactions = 10
dml_locks = 800
fast_start_io_target = 16384

java_pool_size = 1048576                       java_pool_size = 0
                                               job_queue_processes=5
                                               large_pool_size =
20971520
max_dump_file_size = 40960                     max_dump_file_size =

1024000
max_enabled_roles = 40

open_cursors = 500                             open_cursors = 100000

optimizer_features_enable = 8.1.7
optimizer_mode = CHOOSE

processes = 200                                processes = 100

remote_login_passwordfile=exclusive
                                               session_cached_cursors =
100
shared_pool_size = 45000000                    shared_pool_size =
314572800
sort_area_retained_size = 131072               sort_area_retained_size
= 65536
sort_area_size = 2097152                       sort_area_size = 1048576

star_transformation_enabled = FALSE

Kind regards,
Bouke Bruinsma Received on Thu Jun 08 2006 - 02:39:23 CDT