| What are the pluses of autostats [message #144295] |
Tue, 25 October 2005 17:03 |
mjschwenger
Messages: 137
Registered: July 2005
Location: US
|
Senior Member |
|
|
I have a question I cannot answer myself.
I'm testing if the new feature in 10g gather stats automatically will do any good to my application via setting the param
optimizer_dynamic_sampling =XX - higher/lower values (2-10).
It looks that it has a positive impact when I get to the range of 7-8 mln of row with very heavy inserts and selects in the activity. If I start with empty database all it goes with full table scans, after 4-5,000 rows inserted the stats gathering makes huge difference as it changes the plans from table scanning to index usage. So far, so good. But after that the system is already using the index, and no matter if the stats are run, the exec plans do not change.So, I'm having the same exec plan before and after, but after the autostats run I have better performance. I consider that due to the constant inserts the index and data pages grow, split, new leaves branches are created and all of this reflects the statistics...
Can somebody help me to explain what is the reason for performance increase when the execution plans did not change before and after the stats run - auto or manual.
Can somebody gime an idea for how much is the overhead - if used?
Thanks a lot,
mj
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
