Re: tuning advisor for an insert statement

Hi Sayan

On Mon, Oct 21, 2019 at 9:27 PM Sayan Malakshinov <xt.and.r_at_gmail.com> wrote:

> Hi Jeffrey,
>
> Yes, as far as I remember it executes DML, but then it executes rollback.
>
>
> Niall,
>
>> if it is an INSERT...SELECT does an ALL_ROWS select help?
>
> AFAIK, CBO automatically sets ALL_ROWS mode for INSERT..SELECT
>

It does, the idea of looking at just the select portion is to separate out the necessary extra maintenance (index updates, space allocation etc) from just a bad plan for the select which may not show up with a FIRST_ROWS(_n) optimizer goal. In general, inserts might be bad because of the former - too many indexes being a prime example. insert select might in addition be subject to problems as a result of the select access paths.

I'd always want to look at the noop operation first before I looked at the thing that made a load of changes and then undid them all. Both need to be looked at potentially, but I'd suggest select first is a good order.

> --
> Best regards,
> Sayan Malakshinov
> Oracle performance tuning engineer
> Oracle ACE Associate
> http://orasql.org
>

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info

--
http://www.freelists.org/webpage/oracle-l