Performance issue with new 9i database

Hello,
I am seeing huge performance problems on the queries executed against 9i database. I am not too familiar with 9i, But I would like to ask the DBA to check whether all the parameters are set right to gain optimum performance.

Currently the default optimizer is set to first_rows.

Most of the queries executed against this database have group by clause

The tables that I am joining have records less than 5000 (in most cases)

• Another finding is, for example: select ... from .. (select ... from ... ((select a,b,c from ADF, def,ghf where adf.1 = def.1 etc) 11 (select b,d,e from ADF,def,gjh where .... ) 22 11.a = 22.a )

 The inner most queries ( that is queries against the tables directly) comes back very fast. But if I combine them to run the whole query, it never comes back
Number of records in each table is not more than 3000 and after the conditions, each of the inner queries comes back with only few hundred records.

so here is what I did:
I created the table 11 and table 22 with the result set from each of the sqls above taged as 11 and 22.

Then joined the table 11 and 22 to get the final result set,( by saying 11.a = 22.a) its very fast and it had only 700 records in total.

So what could be the problem?

Temp area? sort area? sga? any other parameter that's set wrong?

Our dba is a lazy guy who is not willing to help.

Thanks in advance
Shankar Received on Thu Jun 24 2004 - 16:50:45 CDT