Re: v$session_longops and long running query

"utkanbir" <hopehope_123_at_yahoo.com> wrote in message news:f6c90ebe.0404072151.517bb292_at_posting.google.com...
> 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 ....
>

I have two suggestions.

One is to investigate the explain plans of both statements in the differing environments and see if any differences spring out.

The other is to trace the session and determine exactly what your poor query is waiting on. Once you know what the cause of the waits are, you'll hopefully have some idea on how to attack the problem.

alter session set events '10046 trace name context forever, level 12';

should do the trick.

What you've done so far is take a whole bunch of guesses in the hope that one of them would do the trick. Although it's a method preached by various so-called "Oracle Experts" whom I won't mention, it's a rather inefficient and risky approach. As you've shown, you've been unlucky with your guesses and have gone nowhere.

A far better approach is to take the guess work out of the equation and simply find out exactly what your session is waiting on. It kinda sounds like a more logical approach doesn't it ?

A quick check on google (or metalink) should provide you with enough info on how to interpret the generated trace file to get you going.

Good Luck

Richard Received on Thu Apr 08 2004 - 09:35:46 CDT