| Performance Related Question [message #57124] |
Wed, 21 May 2003 14:31  |
Sankar B. Mandalika
Messages: 20
Registered: November 2002
|
Junior Member |
|
|
Hi All,
I have a question related to performance. I have two servers that have almost identical data and they are identically set. I have listed the
differences below:
1. etc/system settings (slow system):
set shmsys:shminfo_shmmax=0xffffffff
set shmsys:shminfo_shmmin=1
set shmsys:shminfo_shmmni=100
set shmsys:shminfo_shmseg=10
set semsys:seminfo_semmni=70
set semsys:seminfo_semmns=4000
set semsys:seminfo_semmsl=1500
etc/system (fast system):
========================
set shmsys:shminfo_shmmax=131072000
set shmsys:shminfo_shmmin=1
set shmsys:shminfo_shmmni=200
set shmsys:shminfo_shmseg=10
set semsys:seminfo_semmni=300
set semsys:seminfo_semmns=600
set semsys:seminfo_semmsl=600
set semsys:seminfo_semmap=250
set semsys:seminfo_semmnu=500
set semsys:seminfo_semume=100
2. init.ora parameter differences:
==============================
slow fast
1. shared pool size : 385875968 369098752
2. sga_max_size : 1780450784 432374784
3. shared_pool_reserved_size : 19273798 5872025
4. db_block_buffers : 153600 57600
5. log_checkpoint_interval : 40960 20971520
6. log_checkpoint_timeout : 1800 900
7. transactions : 368 308
8. transactions_per_rollback_segment : 10 5
9. max_rollback_segments : 36 61
Other points:
============
I am not seeing any difference when I run the SQL queries.
Slowness can be seen only when I access the application.
The App was running fine for a while and then this slowdown started to show.
Both servers are running Oracle 9.2.0.2.0 and are on a Solari 9 box.
On the face of it, it looks like the fast system has all the better settings.
Also, I have noticed that none of these tables are analyzed on either one of the systems. Would analyzing the tables help? Can I drop the stats for the schema owner? Would it cause any problems?
I am not aware of any network related issues.
Any help/suggestions/pointers would be greatly appreciated.
Thanks,
Sankar.
|
|
|
|
| Re: Performance Related Question [message #57143 is a reply to message #57124] |
Thu, 22 May 2003 07:13  |
 |
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
What kind of application is this?
Becuase application tuning is as important as database tuning.
in JDBC connections you need to care or connection pooling and closing the cursors and commiting the cursors( no too frequent commits).
In J2EE applications if an EJB container is used make sure it is not making a persistent conection (wiz, all the records are queried during instantiation, and served accordingly).
Analyzing the tabes, estimating the statisitcs and setting the optimizer mode WILL HELP THE PERFORMANCE IMPROVEMENT.
You can also rebuild the indexes.
A database should be rebuilt with indexes and analyzed frequently.
Regarding the database parameter settings,
Make sure u have considerable ( depending on need) value set in SORT_AREA_SIZE and make it equal to SORT_AREA_RETAINED_SIZE.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
