- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- PERFSTATreport
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-11-2003 02:27 AM
тАО04-11-2003 02:27 AM
PERFSTATreport
I did a PERFSTAT report with spreport and I have attached it.
I would like to know if my database is working fine. I would like to learn understanding this kind of report so it would be useful if someone could explain to me how to do it and which are the parameter that I have to look.
I'm quite new to Oracle tuning so every advice is useful for me
Thank you
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-11-2003 03:20 AM
тАО04-11-2003 03:20 AM
Re: PERFSTATreport
Your window size is too large for this type of STATSPACK analysis.
A Statspack report generated with a 15 minutes window is as valid as one with a one-hour window (or an even larger timeframe). In fact, as the window gets larger, it may become difficult to come to definitive conclusions given the raw numeric data.
If you take a level 10 every 15 minutes for a day. Then, pick a representative 15 minute window when performance was "felt to be at its worst", we can try to analyze that one.
Anyway, if i analyze the first part of the report:
=======================================================================
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 1,138.73 30,173.91
Logical reads: 1,052.11 27,878.63
Block changes: 2.21 58.52
Physical reads: 63.58 1,684.71
Physical writes: 63.71 1,688.20
User calls: 4.33 114.80
Parses: 1.04 27.46
Hard parses: 0.04 1.12
Sorts: 0.45 11.81
Logons: 0.06 1.70
Executes: 1.67 44.38
Transactions: 0.04
=======================================================================
In this section, we get a great deal of information, in a very small amount of space. We can see how much REDO is generated on average every second and for every transaction.
here, we can see that there are about 1 KN of redo per second. The average transaction generates 30KB of redo!
Very difficult to interprete these values.
Hence, i recommend that you take other snapshots and post again.
Best Regards
Yogeeraj
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-11-2003 05:03 AM
тАО04-11-2003 05:03 AM
Re: PERFSTATreport
-A
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-11-2003 01:51 PM
тАО04-11-2003 01:51 PM
Re: PERFSTATreport
Looking at the database, the rollback segments are very large for what is being put through them. The average active is around 3m, while the segement size is 52m, and your optimal is 52m. I would suggest keeping the optimal at 50m, and setting the segment initial/next extent to 5m with a min extent of 2-4.
For future reference, a good performance analysis tool is http://www.oraperf.com that allows you to upload statspack reports.
Thanks,
Brian
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-11-2003 11:06 PM
тАО04-11-2003 11:06 PM
Re: PERFSTATreport
As Brian mentioned above, your cache size parameters seems to have been "oversized".
Comparing your cache sizes with mine. watch the huge differences!
Mine is from an 24x7 OLTP with 400 concurrent users at peak period!
Cache Sizes
~~~~~~~~~~~
db_block_buffers: 50000 log_buffer: 262144
db_block_size: 8192 shared_pool_size: 209715200
Also, concerning the rollback segments:
I would recommend that you move to LMT. I like to create my rollback segments with around 20-25 minextents and set maxextents to 255 or so. You should place individual rollback segments into their own tablespaces with INITIAL = NEXT = 1M and allow each tablespace to have 1 autoextendable datafile allocated to it.
In this fashion, the rollback segments will grow as needed, if space is available on the system -- upto 255 extents. Periodically (typically before backups) I manually offline and then drop each rollback segment, shrink the datafile associated with its tablespace back to the right size for 20 extents and recreate the rollback segment. This allows for the infrequent "large" transaction to dynamically grow the rollback segment without having to interject a "set transaction use rollback segment BIG_RBS" into it.
E.g.
create tablespace RBS_01
datafile '/xxx/xxx/xxx/rbs_01.dbf'
size 25m reuse
autoextend on
maxsize 100m
extent management local uniform size 1m;
Then:
Create rollback segment rbs_01 tablespace rbs_01;
alter rollback segment rbs_01 online;
why all this?:
o rollback segments must have equi-sized extents.
o rollback segments should be allocated large to avoid 1555's (which I've never hit).
o rollback segments should be plentiful for the amount of transactions you do.
The above rules of thumb to be a good starting point for databases in general. Some need more -- few need less. It is easy to understand, trivial to manage. I never use optimial, prefering to resize them manually before a backup or something.
hope this helps too!
Regards
Yogeeraj
PS. Don't forget to upload your statspack report - 15 minutes window/at High load.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-12-2003 12:20 PM
тАО04-12-2003 12:20 PM
Re: PERFSTATreport
Brian
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-12-2003 08:03 PM
тАО04-12-2003 08:03 PM
Re: PERFSTATreport
to add to Brian's note above, a few info about 9i and undo tablespaces:
An undo tablespace is a different way of managing rollback in 9i. Instead of you figuring out you need N rollback segments each of M bytes/extents in size -- you just create one undo tablespace. In this one undo tablespace Oracle manages all undo. You specify how long you want undo to be retained (eg: if your longest running query is about 15minutes, you might say "please retain undo for 20minutes before overwriting it"). Oracle will create, manage, and grow this undo space for you.
So, an undo tablespace is an alternative to a rollback segment.
As for the non-standard block sizes -- that refers to 9i's ability to have more then one block size in a database. When you create the database, SYSTEM will have a block size. That is the "standard" block size for that database. All other block sizes found in that database are "non-standard".
Rollback segments should have equi-sized extents. Automatically sized extents are by definition not equi sized.
hth
Yogeeraj