Re: Is there a better way to do this - dbms_sql

>
> I've got something working, and the users are happy with how it
> works, apart from one thing, the speed. If I run a non-trivial query
> from the command line, I get the results back in maybe 30 seconds (for
> ~100,000 rows), running the same query in the background is taking 6-7
> minutes. For queries that return a low number of rows the time taken is
> pretty much the same which ever way we do it. Our suspicion is that the
> extra time is taken up by the appending it to the CLOB, rather than the
> query itself. Is there any practical way of speeding this up ?
>
> Thanks in advance
>
> Adrian

Adrian, I can't tell you what the cause is, but the first step should be to trace both the command line and the background, however that background may be submitted, and compare results. How is that "background" submitted? DBMS_SCHEDULER? Some kind of script? The environment is probably not the same. You can also try with 10053, level 1, it will include all the parameters relevant to the optimizer. Maybe there is a significant difference there?

-- 
Mladen Gogala
The Oracle Whisperer
http://mgogala.byethost5.com