Re: Simple Insert tuning

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_at_gmail.com> 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_at_gmail.com> 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_at_dunbar-it.co.uk> 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.    

--

http://www.freelists.org/webpage/oracle-l Received on Fri Dec 01 2017 - 02:22:49 CET