Home » RDBMS Server » Performance Tuning » Statspack frequency
Statspack frequency [message #283948] Wed, 28 November 2007 07:44 Go to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
Hi,

Can you please convey what should be the ideal frequency for generatign Statspack reports? - We have production dbas generating statspack report for 12 hours period. But I read that over a lengthy interval, the report is not useful. Can you convey what are the disadvantages of lengthy interval for the STatspack and what should be the ideal interval?

Thanks,
Nirav
Re: Statspack frequency [message #283951 is a reply to message #283948] Wed, 28 November 2007 07:53 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

You should generate statspack report in 15-30 minutes Interval period.

Re: Statspack frequency [message #283968 is a reply to message #283948] Wed, 28 November 2007 08:37 Go to previous messageGo to next message
martijn
Messages: 283
Registered: December 2006
Location: Netherlands
Senior Member
I can imagine that you make statspack around the peak times (if you have the opportunity).

If there is a peak of about 1 hour every day (24 hours) and you make snapshots every 12 hours then probably your statspack is a bit biased.

Also I can imagine that (let's say) the nigthly processing of data has a different bottleck/SQL-usage/..... then the bulk-insert on in the morning (Just making up a scenario Smile )

So I would suggest creating snapshot around the peaks so you can make some delta's.

Ofcourse there are (a lot of) reasons to have another strategy.

Best Regards,

Martijn
Re: Statspack frequency [message #283972 is a reply to message #283968] Wed, 28 November 2007 08:59 Go to previous messageGo to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
I interacted with the DBAs who decided this interval. They said that , they decided this interval because the purpose is just to identify the SQLs that that are resource intensive. And so there is not much use of a shorter interval . They could have opted for a shorter interval if the users had complained of any performance issues. Since there is no such complaint, they asked the Prod. DBAs to collect these statspack manually at about 10 to 12 hours interval.

Can you comment whether this approach is right? - I just read at couple of places that the frequence should be in half an hour range and so I am getting the doubt whether this is appropriate or not.

Also thess setting are for production environment. Can you convey whether frequent collection of Statspack itself causes significant resource consumption in Production?

Thanks a lot,
Nirav

[Updated on: Wed, 28 November 2007 09:03]

Report message to a moderator

Re: Statspack frequency [message #283974 is a reply to message #283972] Wed, 28 November 2007 09:02 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

>Can you comment whether this approach is right?
No, this is not right approach to use statspack report ifyou still generate then report is USELESS.
Re: Statspack frequency [message #283976 is a reply to message #283974] Wed, 28 November 2007 09:04 Go to previous messageGo to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
Thanks! But can you also help with an example or logic as to why this is useless?

With thanks,
Nirav
Re: Statspack frequency [message #283986 is a reply to message #283976] Wed, 28 November 2007 09:20 Go to previous message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

>But can you also help with an example or logic as to why this >is useless?

Statspack report give us complete database peformance status.
like :
Load Profile section on statspack report.
1. redo log how much generate in per second or per transaction
2. logical reads
3. physical reads
4. parse call
5. hard parse


Top 5 Timed events

Top SQL statement by
1. logical reads
2. physical reads
3. parse call
4. execution
5. cpu

Now if you generate statspack report for 10 to 12 hrs duration.

and you face performance problem in middle of statspack report generation period then how can you identify bottleneck of problem. because statspack report show complete 12 hrs activity on database.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:7641015793792
Hope this clear.
Previous Topic: Snap Shot Error on User_Tables
Next Topic: oracle sequence is causing high cpu usage?
Goto Forum:
  


Current Time: Fri Dec 02 22:48:49 CST 2016

Total time taken to generate the page: 0.22142 seconds