2009/7/29 Radek Šťastný <ded...@gmail.com> > Hello, > I have several cases on client where the select from the query works > as expected while when less conditions > are set and more rows should be returned it locks and waits until I > terminate it. > On server the same script works fine! > In sqlplus from clienr the query without WHERE works - there is 6050 > rows selected.
It's been a long time since I've worked with Oracle but I'll take a stab. Have you looked at V$SESSION_WAIT while the frozen query is running? This will tell you what the session is hung on (lock wait, disk I/O, etc). Also, have you run EXPLAIN PLAN over these queries? Assuming that the issue is not some sort of locking issue, it sounds a lot like the optimizer is choosing a severely sub-optimal execution path for the query without the additional WHERE condition. Providing your schema (including indexes) for these tables would help. This little fragment alone on the one successful query makes me suspicious: STAT #1 id=1 cnt=860 pid=0 pos=1 obj=0 op='SORT GROUP BY ' STAT #1 id=2 cnt=30280 pid=1 pos=1 obj=0 op='HASH JOIN OUTER ' STAT #1 id=3 cnt=860 pid=2 pos=1 obj=31704 op='TABLE ACCESS FULL OBJ#(31704) ' STAT #1 id=4 cnt=537911 pid=2 pos=2 obj=31705 op='TABLE ACCESS FULL OBJ#(31705) ' Two full-table scans on tables with that many rows is usually a sign that either the schema needs work, or that the optimizer has faulty statistics for these tables. -- Stephen Clouse <stephenclo...@gmail.com>
