Re: ORACLE 7.3.2/NT 4.0 problem

Ilyse Mazza wrote:
>
> We have just installed Oracle 7.3.2.2.0 on a NT 4.0 Compaq Proliant 5000
> server (384M RAM, 4 P6 processors). All of our older (< 1G) databases
> work fine. However, we created a new instance with @14G of data, of
> which 9G (23 million rows) is in one table.
>
> The instance will start up and run smaller queries on this large
> table(<75000 rows) correctly. Any query against more data than this
> will complete, but the RDBMS will continue to process something instead
> of returning to an idle state. All 4 processors will spike up to 100%
> at various intervals (one processor is always maxed when this happens),
> and the instance will not release the memory it used during the query.
> The trace logs from these SQL queries show that the process is "Waiting
> for SMON to release tx recovery." There are 3 redo logs of 10M each, 5
> rollback segments of 3M each and temp datafiles of 500M total. None of
> these tablespaces come close to being fully used.
>
> In addition, the database becomes unusable at this point. It will not
> accept new connections and can only be shutdown with the ABORT
> parameter. A shutdown immediate or normal produces the same SMON error.
> However it starts right back up again. Please email me if there are any
> ideas, suggestions, etc. Thanks.
>
> Philip Mazza

Hi there:
What is the size of your SGA and the following init.ora parameters

1. db_multiblock_read_count -- try to set to 32K
2. shared_pool_size -- at least 40% of SGA
3. small_table_threshold -- something similar to this -- bump this up
4. spin_count == set to 4-1=3 # no; of cpus -1

Also, split the query to use the parallel hint and using union or union all clause.

Let me know what happens
Venkat
Principal Consultant
Enterprise Systems Consulting,
# std disclaimer applies Received on Thu Nov 21 1996 - 00:00:00 CST