Re: Can I know how long took a select into a cursor ?

Hi Pete
Thanks for your answer,
When the developer wrote his code, we verified together that the route was the one we wanted and we forced it by a hint.

My problem is that, conceptually, this procedure has a weakness : we read (fullscan) a 15M rows table and join with another 15M table to get first 50K (where rownum < 50000) rows that match a condition on 2nd table. Then we treat these 50K rows (so they will not match the condition) and read again the 50K next.
At the begining, the first 50K rows of 1st table match, the second turn we must read 100K rows to obtain first 5K that match condition and so on. At more the procedure is run, the longer it will last, until the last run where it will have to read the 15M rows to obtain the 50K rows which match

Performance are really very good, better than I expected, but ... algorithm makes that more I'll run my prog, the longer it will take to find first 50K rows. And I would like to see how long does it takes now and tomorrow and so on to compute degradation of performance and be able to give some predictible time to my client.

Because it is in production, I prefer not using trace, by the way, I'm not sure that trace can give me just the time of the cursor to be created (can it ?)

That's why I thought more about reading some V$VIEW but I diddn't find which one (if one) can give me the info

Thanks for your helping (and please forgive my poor english)

empete2000_at_yahoo.com (Pete's) :

>Have you looked at the explain plan? Have you tried tracing the
>session? Doing this may give you insight as to performance of the
>statement you're looking at. I don't think it's a matter of how long
>it takes the cursor to execute unless you're hitting some documented
>bug in Oracle. But rather, I think that it's a problem with
>performance of the statement itself. So, I would look at the explain
>plan of the statments it runs, i.e. run a sql trace on the code.
>
>HTH,
>Pete's
>
>Frederic Payant <fpayant_at_freesurf.fr> wrote in message news:<0sqhevsqrbosk3v1q7nlbfp6j7eerkuhp5_at_4ax.com>...
>> Hi,
>>
>> [ solaris 8 Oracle Enterprise 8.1.7.0 ]
>>
>> Is there any way to know how long took a cursor to execute ?
>> I 've a binary program in which developer 1st open a cursor for X
>> first rows (where rowid < X) then for each fetch, call some
>> proprietary OCI which do some job.
>>
>> I would like to know if it is possible to know how long it takes to
>> fill the cursor (ie how long it is to select X first rows) ?
>>
>> Naturally,it's impossible to modify any part of code, so any info will
>> have to be got across Oracle's views.
>>
>> Thanks a lot and have a nice day
>> Regards
>> Frederic PAYANT - junior DBA ;-)

Amicalement
Frédéric PAYANT Received on Fri Jun 13 2003 - 15:15:21 CDT