Re: Is there a better way to do this - dbms_sql
Date: Tue, 23 Apr 2013 19:16:46 +0100
Message-ID: <4JPSUXDOAtdRFwB9_at_ku.gro.lloiff>
Mladen writes
>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?
>
>
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.
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.Received on Tue Apr 23 2013 - 20:16:46 CEST