skumarpenugonda
Profile Answers by skumarpenugonda

Jul 7th, 2006

Hi,
I just started reading 10g. What I came to know that there are many features added and w.r.t. statistics, below are the changes, or new features.
1. AWR ( Automatics Work Load repository )- which collects statistics every one hour by default and write them into a number tables and stores them in SYSAUX tablespace. in 9i, we use DBMS_STATS package and schedule a job to collect stats at specified intervals of time. But this package still exists.
2. ASH ( Active Session History ) You can store the session statistics data and use them later on for analysis. This was not possible in 9i. Once session is disconnected or instance is bounced, all the session stats were gone. If you want to know what kind of queries where executed at XX:YY time, then it is easy to find, using ASH
3. Time Model statistics are also collected. System statistics are also collected
4. It is possible to collect statistics only for changed tables ( yet to know what it is..i will get back to you soon )
I will come with more info some time later.

Why statistics are different in oracle9i and oracle 10g?

Not sure about this, better we could try again with right sample
size in both the versions. Use DBMS_STATS.AUTO_SAMPLE_SIZE.

Execute
DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT',estimate_percent=>DBMS_STATS.A
UTO_SAMPLE_SIZE,GRANULARITY=>'ALL',CASCADE=>TRUE);