Oracle 10 Admin question

[Mike_Ca Li]

How to get the DB activity report (like sar in UNIX), like number of queries, history of queries, % utilization of SGA_TARGET, % utilization of CPU, %IO, etc from within Oracle 10.
Can the info be obtained via sql queries against Oracle admin views? I don't have access to OEM.
Please advise.
Thanks.

[James Brand]

Oracle has a tool called "STATSPACK" that has been around since version 8. They released a new tool for Oracle 10G called "AWR" but I believe STATSPACK still works. I'm not a DBA and have never used either tool. I'd suggest searching the Oracle web site for more information.

[patrik rybar_1]

you have AWR automaticaly running there
to change it's period of snapshots creation or retention you can do with
exec dbms_workload_repository.modify_snapshot_settings (retention => 7200,interval => 30);

for creating reports (html or text) you can use
on *nix
@?/rdbms/admin/awrrpti.sql
on M$
@?\rdbms\admin\awrrpt.sql

add. info you can find here
http://www.oracle-base.com/articles/10g/AutomaticWorkloadRepository10g.php

[Volker Borowski]

Hi,

AWR belongs to Oracle Diagnostics.
If you use it, it requires an
"extra cost" license.
Check first if you really want to use it.

As far as I know, statspack still works in 10g and does not require a separate fee for
an additional component.

Volker

[TwoProc]

Use Statspack, its free, recommended and gives exactly what you're asking for.