RE: Tuning Advice
Date: Fri, 27 Apr 2018 18:19:20 +0000
Message-ID: <1c27649e3f2d4ceebf00b97a40ba4cc5_at_fpl.com>
Jonathan,
Thanks for the reply. I really appreciate it.
This is a non-production environment and running in noarchivelog mode. I don't think it has anything to do with logging. When using CTAS or creating a MV (essentially the same thing), the whole process will finish in under 10 minutes. When using insert as select I have let it run for hours and seen very little progress (checked the size of the table and it doesn't grow).
There are no indexes on the table (none needed) either so updating indexes doesn't cause any additional slowdowns.
Thanks,
Jeremy
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis
Sent: Friday, April 27, 2018 1:59 PM
To: ORACLE-L <oracle-l_at_freelists.org>
Subject: Re: Tuning Advice
CAUTION - EXTERNAL EMAIL Another ought about select vs, insert as select, vs. CTAS.
Even in the absence of indexes the middle one will generate a lot of redo, but if the database is in noarchivelog mode the CTAS will be a nologging operation. It's possible that could make some difference to run time.
Regards
Jonathan Lewis
From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Sheehan, Jeremy <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 - 20:19:20 CEST