Re: statspack problem

On Fri, 14 Jan 2005 10:23:18 -0500, "hastenthunder" <hastenthunder_at_hotmail.com> wrote:

>Hello,
>
>I'm encountering difficulites on getting statspack to generate reports on
>Oracle 10g + RedHat Enterprise 3.
>
>After taking 2 snapshots, I tried to generate report by calling
>$ORACLE_HOME/rdbms/admin/spreport, and entered the appropriate snapshot ids
>upon the prompt.
>
>The reports runs for couple seconds, then encounters a divide-by-zero error,
>and terminates:
>
>
> Statistic Total per Hour
>--------------------------------- ------------------ ---------
>log switches (derived) 0 .00
> -------------------------------------------------------------
> , 'D' , size_for_estimate*1024/:def_cache
> *
>ERROR at line 10:
>ORA-01476: divisor is equal to zero
>
>
>Can someone help me out here?
>
>Thanks.
>
>

It's a bug in 10g, in which the spreport.sql is puzzled by the new __db_cache_size parameter. Metalink claim it to be fixed in 10.1.0.3, but I still experience it with this patch on w2k....

But don't mind! While Statspack being the preferred tuning tool in 8i and 9i, 10g comes with an excellent substitute, the AWR (Automatic Workload Repository).

Use AWR instead, since

1. It is preinstalled and thus does not require explicit installation and the extra space that Statspack installation requires.
2. Cleanup of it's repository is automatized, unlike Statspack.
3. Snapshots in AWR are taken automatically pr. default.
4. You just run $ORACLE_HOME/rdbsm/admin/awrrpt.sql and get a report very much like the statspack report.
5. Oracle recommends using AWR instead of Statspack, so don't expect Statspack to be developed furter.

You control the snapshot interval and the cleanout by simply

SQL>exec dbms_workload_repository.modify_snapshot_settings(interval => 240) -- run snapshot every 4 hours
SQL> exec
dbms_workload_repository.modify_snapshot_settings(retention=> 7*24*60) -- Keeps old snapshots for one week.

• Kenneth Koenraadt