OraFAQ Forum: Server Administration » Performance Related Question

Home » RDBMS Server » Server Administration » Performance Related Question

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

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.

Report message to a moderator

Re: Performance Related Question [message #57143 is a reply to message #57124]

Thu, 22 May 2003 07:13

Mahesh Rajendran
Messages: 10708
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.

Report message to a moderator

Previous Topic:	Remote startup
Next Topic:	SQL LOADER

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Dec 30 15:33:48 CST 2024