If cost is different then it's because the cost of the query you are
running to generate the rows is different. Indexes on the target table play
zero role in the cost of the insert as far as the optimizer (and therefore
plan) is concerned. Although, the work required to update the indexes will
effect the statement duration.
Statistics for the tables involved in the query will effect the cost, the
costs will ultimately decide the plan used. The same plan can be used with
different costs as long as it was the cheapest one the optimizer checked
out. The actual execution time for the statement will depend on the plan
used, the data behind the plan (e.g how many times it's really doing a
nested loop, how large the table that's getting full scanned really is..),
the speed of the resources (your storage access, your cpu..), competition
with other processes, locks, indexes that need maintaining... and that's
just off the top of my head.
If the statistics represent the data, then a slow statement is usually due
to a lack of decent execution plan for the statement. Maybe you're missing
an index or the query is actually asking to do a lot of work. Hard to tell
without seeing the SQL and execution plan.
With the 10046 you can see exactly where the time is going, no need to work
through a list of guesses.
Regards,
Andrew
On 30 November 2017 at 23:42, Ashish Lunawat <ashish.lunawat@xxxxxxxxx>
wrote:
Check if both the environment has same indexes. Excessive Indexing does
contribute to slow inserts and updates.
-A
On Fri, Dec 1, 2017 at 6:49 AM, Norman Dunbar <oracle@xxxxxxxxxxxxxxx>
wrote:
Evening Sanjay,
I recently had a similar problem to work out for a friend who is a
developer but doesn't/didn't have a dba to consult. His problem was an
insert taking 2048 (spooky number) seconds to insert the first row, all the
following rows were almost instant.
A 10046, level 12 trace showed the problem was related to dynamic stats
being gathered prior to the first EXEC of the insert. The stats were being
gathered on various SYS objects. It appeared that the sys schema and
dictionary stats were never gathered on his database. After gathering
(both) the insert problem vanished.
I fully agree with the other poster (sorry, forgot your name) who
suggested tracing as the way to find out. It will show you exactly where
Oracle spent its time.
Good luck.
Cheers,
Norm.
--
Sent from my Android device with K-9 Mail. Please excuse my brevity.
