Re: Quite interesting 11.2.0.4 performance problem.
From: William Robertson <william_at_williamrobertson.net>
Date: Fri, 19 Jun 2015 14:44:16 +0100
Message-ID: <55841CB0.5090505_at_williamrobertson.net>
I'm not sure what was wrong with the code in principle though, and I'm not sure we should "commit often".
William Robertson
Date: Fri, 19 Jun 2015 14:44:16 +0100
Message-ID: <55841CB0.5090505_at_williamrobertson.net>
If the GTT was ON COMMIT DELETE ROWS, that might explain the performance improvement ;)
I'm not sure what was wrong with the code in principle though, and I'm not sure we should "commit often".
William Robertson
| Howard
Latham 19 June 2015 13:06 |
RH Linux 64bit E4
Oracle 11.2
In case anyone runs into this problem I thought I'd share it. We have some code that ran fine on Oracle 11.2.0.1 in about 30 minutes - when we moved to
11.2.0.4 it took over 3 hours. It wrote 3/4 million rows to a temp table then wrote it to disk. Using UTL_FILE
Nothing in the traces no tuning fiddling with spin count, moving disks, analyzing tables, watching spotlight for hours, nothing helped.
Eventually our developer tried to reproduce the problem and discovered there was no commit after writing the temp table. Adding a commit returned the speed to what we used to get. I agree "commit little and often" is or should be a developer's mantra - along with "beware of nulls". However clearly some behaviour in a temporary buffer has been changed. But I acknowledge that the code was 'wrong' in the first place.
Oracle 11.2
In case anyone runs into this problem I thought I'd share it. We have some code that ran fine on Oracle 11.2.0.1 in about 30 minutes - when we moved to
11.2.0.4 it took over 3 hours. It wrote 3/4 million rows to a temp table then wrote it to disk. Using UTL_FILE
Nothing in the traces no tuning fiddling with spin count, moving disks, analyzing tables, watching spotlight for hours, nothing helped.
Eventually our developer tried to reproduce the problem and discovered there was no commit after writing the temp table. Adding a commit returned the speed to what we used to get. I agree "commit little and often" is or should be a developer's mantra - along with "beware of nulls". However clearly some behaviour in a temporary buffer has been changed. But I acknowledge that the code was 'wrong' in the first place.
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Jun 19 2015 - 15:44:16 CEST