Performance issue with new 9i database

[Shankar]

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

[Mark D Powell]

shankarvs@gmail.com (Shankar) wrote in message news:<3dc522c8.0406241350.444042e3@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 --

[Steve Howard]

shankarvs@gmail.com (Shankar) wrote in message news:<3dc522c8.0406241350.444042e3@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

[Ed prochak]

shankarvs@gmail.com (Shankar) wrote in message news:<3dc522c8.0406241350.444042e3@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