| emx |
Posted: Nov 03, 2007 11:03:17 AM |
Total Post: 5
Joined: Nov, 2007
|
Hello all.
I have been tasked with the optimization & tuning of a large Oracle RAC (8 servers on HP/9000).
The goal is to improve the performance of our database. This database is used for the Prepaid system of a medium-sized GSM mobile network. It has been installed with default parameters, which probably worked fine in the beginning but now that we have reached 4.5 million subscribers we notice a decline in performance.
The thing is, I don't know where to start... I have read this forum and other sources of information, and the task seem daunting!
I have started looking at the rollback segments, am thinking of putting the Undo Management to AUTO, as a start. We're getting rollback segments errors, snapshot too old.
I also want to check the various memory sizes. Any help with this on 9i? I heard that in 10 it can be done automatically, but in 9i I still have to do it manually. Anyone has pointers on this?
I am also hearing about StatPack. Is it part of the Oracle software loaded on our UNIX servers? Or is it an add-on? Could this help me with my tuning needs? I guess I should read more on it.
Thanks for any hints on getting me started.
|
|
|
 Mohammed Taj |
| Posted: Nov 03, 2007 11:11:55 AM |  |
|
Total Post: 694
Joined: Jul, 2007
|
I have started looking at the rollback segments, am thinking of putting the Undo Management to AUTO, as a start. We're getting rollback segments errors, snapshot too old.
You should keep undo management mode "AUTO" and for snapshot too old error you have to set "undo_retention" parameter properly to skip this error.
what is current value for undo_retention ?
default is 900 second means 15 minutes.
I also want to check the various memory sizes. Any help with this on 9i? I heard that in 10 it can be done automatically, but in 9i I still have to do it manually. Anyone has pointers on this?
Yes, it is correct we can use automatic memory mgmt option in 10g.
but for oracle 9i can you post your SGA value.
1. db cache size
2. shared pool size
3. pga size
I am also hearing about StatPack. Is it part of the Oracle software loaded on our UNIX servers? Or is it an add-on? Could this help me with my tuning needs? I guess I should read more on it.
Stats pack is part of Oracle DB Server.
you have to configure perfstat user for generate stats pack report.
go through below link for how to generate stats pack report
http://dbataj.blogspot.com/2007/05/statspack.html
send me your stats pack report at star_taj@yahoo.com
anyother question ?
|
|
|
|
|
| emx |
| Posted: Nov 03, 2007 11:21:35 AM | |
|
Total Post: 5
Joined: Nov, 2007
|
Thanks for the hints. I will go through the StatPack procedure and post my results.
|
|
|
|
|
| emx |
| Posted: Nov 06, 2007 06:12:42 AM | |
|
Total Post: 5
Joined: Nov, 2007
|
Trying install STATSPACK starts well but then ends up with:
If this script is automatically called from spcreate (which is
the supported method), all STATSPACK segments will be created in
the PERFSTAT user's default tablespace.
Using PERFSTAT tablespace to store Statspack objects
... Creating STATS$SNAPSHOT_ID Sequence
create sequence STATS$SNAPSHOT_ID
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning and Real Application Clusters options
JServer Release 9.2.0.8.0 - Production
oracle@twsmp1:/home/oracle>
|
|
|
|
|
| emx |
| Posted: Nov 06, 2007 07:35:46 AM | |
|
Total Post: 5
Joined: Nov, 2007
|
The errors above did not prevent me from running things according to Taj's procedure.
I submitted the generated report to http://www.statspackanalyzer.com and it gave me some recommendations (sometimes conflicting) for improving DB performance.
|
|
|
|
|
| emx |
| Posted: Nov 06, 2007 07:38:16 AM | |
|
Total Post: 5
Joined: Nov, 2007
|
Here are my current memory parameters for our cluster:
twsmp1:/uorabin/app/oracle/product/9.2/dbs#grep size initSMS.ora
*.db_block_size=4096
*.java_pool_size=150M
*.large_pool_size=1000000
*.max_dump_file_size='10240'
*.shared_pool_reserved_size=10000000
*.shared_pool_size=512M
*.sga_max_size=4500M
*.db_cache_size=4000M
*.sort_area_retained_size=65536
*.sort_area_size=65536
|
|
|
|
|
| Mohammed Taj |
| Posted: Nov 06, 2007 08:02:53 AM | |
|
Total Post: 694
Joined: Jul, 2007
|
If you successfully generated statspack report then please send to me at star_taj@yahoo.com.
i will check and give you custom recommendation.
|
|
|
|
|
| moni |
| Posted: Jul 04, 2008 08:56:59 AM | |
|
Total Post: 4
Joined: Jul, 2008
|
You got this error because when you were trying to run the scripts earlier at that time it was not successfully installed. Its because of that problem perfstat user is created and some tables is also created. The use of spcreate script is to create the perfstat user and tables inside that user's schema. Please drop that user using this command DROP USER PERFSTAT CASCADE and then run the script again. Make sure you should have your temporary tablespace empty before running this script.
Hope this will help you.
Moni
|
|
|
|
|
| Time Zone: EDT |
Send this thread to your friend |
