| how oracle calculates statistics from within [message #141016] |
Thu, 06 October 2005 11:15 |
mjschwenger
Messages: 137
Registered: July 2005
Location: US
|
Senior Member |
|
|
I have run statistics with DBMS_STATS.GATHER_???_STATS for years now but now I'm running into something strange.
My usual syntax for computing stats is:
for table:
exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'name', TABNAME=>'CODES', estimate_percent=> 40, degree=> 4, CASCADE=> TRUE, block_sample=> TRUE,method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO');
for schema:
DBMS_STATS.gather_schema_stats (
ownname => 'RR',
estimate_percent => null,
block_sample => false,
method_opt => 'FOR ALL COLUMNS SIZE AUTO',
degree => null,
granularity => 'ALL',
cascade => true,
options => 'GATHER AUTO'); (or sometimes 'STALE')
My problems is that if I compute the stats with the statememtns above, I do not get a performance increase. When Oracle runs them, form within, as the stats are set to auto-gather/update statistics, the results is performance buster by 20%. Here's what I'm doing: Running an app with 48 processes everyone does some inserts in few tables, then selects, then inserts again, then 2 updates, and inserts into an audit table. I'm looking at the last audit insert to measure how much work the processes have been done per minute. If I run the stats, there's no performance difference, but with Oracle doing it, the inserts jump a lot.
What Oracle is doing that is different from my syntax and better?
Also, is there a way to tell Oracle to gather the stats at certain time not only when it decided to?
Thanks a lot for the help,mj
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
