Re: Performance issue with new 9i database

shankarvs_at_gmail.com (Shankar) wrote in message news:<3dc522c8.0406241350.444042e3_at_posting.google.com>...
> 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

Have you run an EXPLAIN PLAN?

Regards,

Steve Received on Fri Jun 25 2004 - 09:11:47 CDT