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