Re: tuning advisor for an insert statement
- From: niall.litchfield@xxxxxxxxx
- To: Sayan Malakshinov <xt.and.r@xxxxxxxxx>
- Date: Mon, 21 Oct 2019 21:34:08 +0100
On Mon, Oct 21, 2019 at 9:27 PM Sayan Malakshinov <xt.and.r@xxxxxxxxx>
Yes, as far as I remember it executes DML, but then it executes rollback.
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.
Oracle performance tuning engineer
Oracle ACE Associate
Other related posts: