v$session_longops and long running query

Hi ,

I have a performance problem joining two tables . The tables contain 3,000,000 rows and i join them on a date column. (in fact it does not matter if i change the column.) i have an index on the columns also .

The query finishes on my production server which is rac , oracel 9.2.0.2 , linux itanium , 8gb.ram etc.. No problem.

But it does not finish neither on a 4 cpu , 2gb ram windows 2000 server nor a 512mb.ram , 1 cpus laptop. I think there must be some tuning parameters for the windows servers , i am looking for that:

The problem is , this simple query does not finish:

select count(*)
from table_a a,table_b b
where a.tarih=b.tarih

inda is index on a.tarih
indb is index on b.tarih

i created tables and indexes by using parallel 8 , nologging.

If i use this sql:

select /*+index(a,inda) index(b,indb) use_nl(a,b)*/

        count(*)
from table_a a,table_b b
where a.tarih=b.tarih

This query retrieves the first rows very quickly , but if i replace the * with count(*) and try to read all data , it does not finish. I checked v$session_longops , after i run the query with use_nl (nested loop) , v$session_lonops immediately shows it is %99 completed. But that %1 percent does not finish. And the query starts to make lots of disk read.(v$session_Wait)

if i use hash join , the query neither gets the first rows quickly nor return the results.

I tried changing pga_aggregate_target , shared_buffers etc but it did not help.

I monitored v$sesstat also , the query does lots of consistent gets .

My parallel_max_servers , parallel_min_servers are 20,5 respectively. My parallel_min_percet is 50.

Bith of the windows servers are empty , i am the only user.

I will be appreciated if someone can show me the way ....

Kind Regards,
hope Received on Thu Apr 08 2004 - 00:51:02 CDT