Re: Query hangs when executed through perl

On Jan 30, 8:50 pm, ks.anan..._at_gmail.com wrote:
> 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.

Problem sounds like it just might be nothing more than a tuning issue. On your 10g system do your base tables have statistics? DBMS_STATS procedure GATHER_DICTIONARY_STATS is provided to update the SYS, SYSTEM, and rdbms component owner object statistics.

But first you might also rewrite the query. Instead of using a not in subquery on the recycle bin you might try a :"segment_name not like 'BIN$%' since all recycle bin objects are named using this pattern: BIN $$globalUID$version. This might result in a better plan. I do not have a 10g system to test with right now.

Also if you are using manual memory management then you might compare the size of the hash area to how much data is being hashed and if the data exceeds the hash area size bumping up the hash area size.

HTH -- Mark D Powell -- Received on Wed Jan 31 2007 - 07:39:33 CST