Query hangs when executed through perl

Hi,

Here is the problem I am facing.

A perl script hangs when executing the following piece of sql

$lsql = "SELECT NVL2(partition_name,

                            segment_name || ':' || partition_name,
                            segment_name)
                  FROM user_segments
                 WHERE segment_type IN ('TABLE', 'TABLE PARTITION') 
AND
                       segment_name NOT IN
                       (SELECT object_name
                          FROM recyclebin bin) AND
                       segment_name NOT IN (?,?,?,?,?) order by desc;

However the query went through fine if
- the "use encoding;" (that enforces utf8 encoding) is commented out
in the script or
- if the bind variables are hard-coded or

• if less than 5 bind variables are used

Execution plan showed that a cartesian join was implemented when use encoding or no bind varaibles or less than 5 bind variables are used whereas hash join
was implemented when no encoding is used. Let me know if you need the execution plans for the scenarios.

What is even more confusing is that the exact same query works in an environment which uses oracle 9i whereas fails in the env which uses oracle 10g.

Tried hinting FIRST_ROWS, then NO_MERGE. Didn't help.

Kindly help.

Regards,
Anand. Received on Tue Jan 30 2007 - 19:50:26 CST