Hello,

I wrote a large query on an huge database. In a normal editor it runs under 7 sec to fetch all records.

If I run it with a TOracleQuery it take up to 45 sec to return. I only replaced some WHERE-condition with Variable which i fill by the programe. The type of this variables are DateTime.

In some other cases we had a performance-leck from 6 sec to 1 hour!!

Do you have any clou to speed up this query? The query you can find below.

Thanks a lot


SELECT
d.prod_info_id,
d.prod_info_name,
d.typ_kennung,
d.prodinfo_typ,
d.element_id,
d.element_kennung,
trunc(f.zeit,'dd') as datum,
sum(f.wert) as wert,
f.mat_id,
m.kurz_bez,
f.dimension,
a.kza_aggregation_id,
nk.materialnomenklatur
FROM
zo_kza_pi z,
fakten_prod f,
doku_pi_lovtyp d,
kza_aggregation a,
vw_materialnomenklatur nk,
material m
WHERE z.prod_info_id = f.prod_info_id
and f.prod_info_id = d.prod_info_id
and a.kza_aggregation_id = z.kza_aggregation_id
and f.mat_id = m.mat_id
and f.mat_id = nk.mat_id (+)
and f.prod_info_id = nk.prod_info_id (+)
and f.prod_info_id in (select mf.prod_info_id from massenfluss mf where mf.q_werkstrukturelement_id = a.werkstrukturelement_id )

and d.typ_kennung =2
and f.gueltig = 1

and f.zeit_anfang_ext between :Anfang and :Ende
and a.kza_aggregation_id = :kza_aggregation_id

group by
d.prod_info_id,
d.prod_info_name,
d.typ_kennung,
d.prodinfo_typ,
d.element_id,
d.element_kennung,
trunc(f.zeit,'dd'),
f.mat_id,
m.kurz_bez,
f.dimension,
a.kza_aggregation_id,
nk.materialnomenklatur
order by 1

ohh - I forgot we are using DOA 3.4.6.1

If you are using bind variables, then the cost based optimizer may take a different approach compared to using literal values in the SQL. Perhaps you need to update the statistics for the tables involved (analyze table ... compute statistics)?

I don't think it, there are problems with the optimizer because if I query it with the PL/SQL-Developer (6.0.5.926) it is very fast 6 sec. If I use the TOracleDataSet it is very slow (46 sec).

I have the 9.0.1.1.1 Oracle-client, the server is a 8.1.7. database on VMS.

Do you have any other idear?

Do you use bind variables in PL/SQL Developer?

No I didn't used. I tried it and the query was also runinng slow :-(.

Is there something else we can do? We could not remove the variables, they are important for the programm.

You can use substitution variables (otSubst) instead of bind variables. You will have to make sure that the substitution variable value uses the right format though (for numbers, dates, and quoted strings).

Updating statistics by running some "analyze table compute statistics" statements might also work though.

Removing bind variables is not very good solution (it can compromise scalability if you have many concurrent users)
Try to look at the plan used by Oracle
For example in sqlplus

set autotrace on
spool Plan.log
<your query here>
spool off

Then see in Plan.log the plan used.

Also for gathering statistics is recommended to use
dbms_stats.gather_table_stats
instead of analyze
in recent versions
I don't know if it is applicable on that platform/version (maybe have a look on metalink on this)

Hope this helps
Nicola