OraFAQ Forum: Performance Tuning » Statspack frequency

Home » RDBMS Server » Performance Tuning » Statspack frequency

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Statspack frequency [message #283948]

Wed, 28 November 2007 07:44

orausern
Messages: 826
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

Report message to a moderator

Re: Statspack frequency [message #283951 is a reply to message #283948]

Wed, 28 November 2007 07:53

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

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

Report message to a moderator

Re: Statspack frequency [message #283968 is a reply to message #283948]

Wed, 28 November 2007 08:37

martijn
Messages: 286
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

Report message to a moderator

Re: Statspack frequency [message #283972 is a reply to message #283968]

Wed, 28 November 2007 08:59

orausern
Messages: 826
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

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.

Report message to a moderator

Re: Statspack frequency [message #283976 is a reply to message #283974]

Wed, 28 November 2007 09:04

orausern
Messages: 826
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

Report message to a moderator

Re: Statspack frequency [message #283986 is a reply to message #283976]

Wed, 28 November 2007 09:20

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.

Report message to a moderator

Previous Topic:	Snap Shot Error on User_Tables
Next Topic:	oracle sequence is causing high cpu usage?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 17 04:08:10 CDT 2025