??: Saving STATSPACK Reports

  • From: "Serol_Luo \(BJ\)" <Serol_Luo@xxxxxxxxx>
  • To: <bnsarma@xxxxxxxxx>, "_oracle_L_list" <oracle-l@xxxxxxxxxxxxx>
  • Date: Mon, 11 Sep 2006 14:20:16 +0800

Actually, I create a standalone database (just call it Oracle performance 
repository) to hold all my production database's statspack data.
1.       Create one schema in the repository database for each production 
2.       Export from production database's perfstat schema and import it into 
repository database.
3.       Production database snap statspack data as normal
4.       Create a db link from repository database to production, fetch data 
from production to repository
Insert into stats$,,,1 as select * from stats$.. @Production where snap_id > ...
Insert into stats$,,,2 as select * from stats$.. @Production where snap_id > ...
Insert into stats$,,,3 as select * from stats$.. @ production where snap_id > 
5.       purge data in production database, keep data in production for only 1 
week or just one day.
6.       You can do any thing in the repository database. Run summary script, 
run spreport.sql, or generate trend data for analyze, any other things... just 
as you want. 
7.       In my enviroment, I build my statspack report website, viewing most 
thing via web, with very friendly interface......
Serol Luo, From Peking,China
Or call me RollingPig
???: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]?? BN
????: 2006?9?8? 23:57
???: _oracle_L_list
??: Saving STATSPACK Reports

Have oracle, 9iRel2, and 10g Database running on HP-UX, AIX, and SUN 

We run Statspack every hour on all the prod servers and reatin only this weeks 
data, the rest of the data is purged. 

I am looking for a way to save the previous reports, like run spreport every 
hour from cron job and pull reports every hour automatically.

I was also thinking of exporting the perfstat schema from all the 50 DB prod 
servers and saving them in some other database 
Not sure If run into  resource issues to this.

Appreciate your suggestions in advance.

If some body has a script to do this I apppreciate if you can share with me.

Regards & Thanks 

Other related posts: