Is there a better way to do this - dbms_sql

[Adrian]

11.2.0.3 Enterprise Edition

I've been asked to put together an application that will take a
query, run it in the back ground, and then make the result set available
to the requestor at a later date. We don't know what the query will be
until it is raised, so I think we are firmly into dbms_sql territory.
What I'm doing is using the dbms_sql.column_value procedures to get each
value, and then appending the value to a CLOB (with odd bits of wrapping
to make it look like a csv file), and the CLOB is then written back to
the database, ready for the user to recover at a time of their choosing.

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
--
To Reply :
replace "bulleid" with "adrian" - all mail to bulleid is rejected
Sorry for the rigmarole, If I want spam, I'll go to the shops
Every time someone says "I don't believe in trolls", another one dies.

[Mladen Gogala]

On Mon, 22 Apr 2013 22:45:20 +0100, Adrian wrote:


>
> 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

[Adrian]

Mladen writes

Thanks for the reply.

I'll look at putting a trace on tomorrow. The background job is being
run by a PL/SQL stored procedure called by a DBMS_SCHEDULER job. The
job runs using the default job type.

I did some more digging around today, I added a few extra lines to a
copy of the procedure the scheduler calls, and those lines now write out
to a table with the systimestamp and where it has got to (it will write
out the status for each row). I appreciate that this should add an
overhead to the execution, but it doesn't seem to make that much
difference. What it did confirm was that the "execute" phase takes a
little over a second, and the rest of the time is spent processing the
results. Most rows are handled in under 1 millisecond, but roughly
every couple of thousand rows (I've done several runs, and it varies),
I'll get a row that takes 400 milliseconds. The performance seems to be
roughly linear across the result set (no getting slower as the CLOB gets
bigger). With this particular query (a little over 6100 rows), from
SQLPlus I'm getting 6-7 seconds, with the backgrounded version ~3.5
minutes.

This is leading me to think that the problem is in the handling of the
result set, rather than the execution of the select.