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

Please do not cross-post

run an explain plan and look to see how Oracle is solving the query

Make sure the Oracle statistics are up to date

run an explain plan on the query after updaing the statistics and compare to the prior explain

switch the session to all_rows

re-run the explain plan

Now tune the SQL

HTH -- Mark D Powell -- Received on Fri Jun 25 2004 - 16:09:51 CEST