Re: Tuning Advice
Date: Fri, 27 Apr 2018 19:26:26 +0000
Message-ID: <CY1PR10MB068383E50ADADC0274F11115D88D0_at_CY1PR10MB0683.namprd10.prod.outlook.com>
Not sure if anyone brought it up already.. see if there are any triggers on the tables where you are inserting the data.
From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Sheehan, Jeremy <JEREMY.SHEEHAN_at_fpl.com> Sent: Friday, April 27, 2018 2:25 PM
To: christopherdtaylor1994_at_gmail.com; jonathan_at_jlcomp.demon.co.uk Cc: ORACLE-L
Subject: RE: Tuning Advice
One time there was 2mm records in the table, but most of the time the table was truncated prior to trying it out. Either way, it runs with the bad execution plan and never finishes.
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Chris Taylor
Sent: Friday, April 27, 2018 2:20 PM
To: jonathan_at_jlcomp.demon.co.uk
Cc: ORACLE-L <oracle-l_at_freelists.org>
Subject: Re: Tuning Advice
CAUTION - EXTERNAL EMAIL Jonathan touches on another important question.
How much data is in this table you're inserting into AND has the table been having "DELETES" on it? You might could try a simple "INSERT /*+ append */ hint if the problem is looking for free space in the table below the HWM.
Chris
On Fri, Apr 27, 2018 at 12:54 PM, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk<mailto:jonathan_at_jlcomp.demon.co.uk>> wrote:
The commonest resolution of that type of problem is that "select" may be running under first_rows(n) optimization which changes to all_rows as soon as you change the query to "insert as select"; alternatively the plan for a distributed select can do some optimizations that "insert as distributed select" can't.
In your case, though, you say a CTAS is just as fast as a select - and in both the above CTAS would have the same problem as "insert as select".
This isn't just a case of your insert maintaining indexes while your CTAS doesn't have any indexes ?
Regards
Jonathan Lewis
From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> <oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>> on behalf of Sheehan, Jeremy <JEREMY.SHEEHAN_at_fpl.com<mailto:JEREMY.SHEEHAN_at_fpl.com>> Sent: 27 April 2018 16:50
To: ORACLE-L
Subject: Tuning Advice
Hello Gurus,
Oracle 12.1.0.2
AIX 7.1
I have a query that runs fairly well. Takes about 8 minutes to run, nothing terrible about it (no huge FTS, joins seem to be in place correctly). When I try to do an insert into TABLE as SELECT, it picks a completely different execution plan and never finishes. The really odd thing is that it works great when using CTAS or initial creation of a MV.
Any suggestion on how I can have the optimizer not use a specific execution plan or any session level parameters that would have it use a different execution plan?
Thanks in advance!
Jeremy
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Fri Apr 27 2018 - 21:26:26 CEST