Andy
Actually, this is simple plan insert into a table and data is also not coming
as select but as values been passed with bind variables. I don't have data and
so cannot test the detail and so want to check as of how much effect object
Stats can have. I can see that sample size are different in both env stats.
TxSanjay
On Thursday, November 30, 2017 7:05 PM, Andy Sayer <andysayer@xxxxxxxxx>
wrote:
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.