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

Have you tried any hints? FIRST_ROWS isn't really a good choice for a GROUP BY query.

>
> 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 above has 3levels of select with two levels of in-line views. And the parentheses don't seem to match up. What does the real query look like?
>
> 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

Never has no meaning. Do you mean there's no results after  a minute? an hour? a day? How long did you wait?

> 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.

Have you tried looking at the explain plan? And do you really know how many rows are in the base tables? First you mention under 5000, and here it's 3000. Get some facts together.
>
> 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 happens logically in the main query next? (There's one more level of SELECT... in your "sample")

>
> So what could be the problem?

need more details.

>
> 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

Before blaming the DB or the DBA, you need to do more research.

A complex query, missing one join condition can easily blow up. You have at least 3 base tables, used in at least 2 in-line views, judging from your sample above. If they each have one thousand rows then the first level seems to have potentially 1,000,000,000 in each of 2 views which could mean a full join on the next view could have about 1,000,000,000,000,000,000 rows.

Show us the real query or a reasonably small example that has the same proformance problem.

hth,
  ed Received on Fri Jun 25 2004 - 18:49:55 CEST