pipelined function without return or close cursor - what happens?
511510Nov 13 2008 — edited Nov 15 2008I'm using 10g at my client's office..I've developed an update to a process which is taking a long time on their server. I beleive its
due to the SAN. But I have endevoured to attempt to cut down on table scans as much as I can.
So I developed a process using a pipelined function - this brought a huge performance jump on my home server (which is 11g BTW).
I put it on my client's UAT server and the new process is slower on their server...I can't determine why. I'm unable to run autotrace
due to their policies and have to ask the DBA for the AWR report..which give some information but nothing about the pipelined function.
I've noticed that in my code I don't close the passed in ref cursor, nor do I have a return; at the end. Doesn't seem to bother 11g. but will
it trouble 10g?
Any tips how I can identify if the pipelined function is actually working as it should. ie: can I put a timestamp column to see the rows coming
out. I'd like to know if the data is coming out in batches or in one big lump. If in one big lump it definitly will be slower...the process
is only fast pipelined.