Re: Too bad performance of nested table insert operation

Min-Koo Seo wrote:

>
> BTW, could you possibly why I have to commit when I really have to?
>
> By commit at a regular basis, I can achieve some goals:
> - I can see the progress of my application by using select comands.

You can also use autonomous transactions and/or DBMS_PIPE to provide such information outside of the current transaction. This helps isolate the transaction from the instrumentation.

> - No *large* rollback segments are needed.

At the risk of having a ORA-1555. And the 'large rollback segment' ends up providing a potentially significant performance benefit.

Is disk really that expensive (for example, compared to your wage in trying to come up with a workaround) that the size of the rollback segment is significant?

IIRC, typically a commercial grade 80GB SCSI disk is under the $1000 mark.

> - If my application fails, I can continue the running of the
> application from that point.

In my experience, this frequently means the original application really was not a true transaction but rather a series of transactions that happen to run in a tight sequence. For example, a payroll run for 100 employees might be a single 100-employee transaction or 100 single-employee transactions. There are significant differences in the way each should be set up.

Have you had a chance to look at either of Thomas Kyte's books?

Effective Oracle by Design (Osborne ORACLE Press Series)  by Thomas Kyte (Author) ISBN: 0072230657  

Expert One-on-One Oracle
 by Thomas Kyte (Author) ISBN: 1590592433  

These walk through the implications of your frequent commit proposal in great detail, especially addressing performance and resource impacts. Both are relevant and each shows different things that a skilled Oracle programmer needs to watch.

/Hans Received on Mon May 31 2004 - 00:25:28 CDT