| What is server configuration ? [message #59205] |
Tue, 04 November 2003 03:08  |
Maheswor Maharjan
Messages: 3
Registered: October 2002
|
Junior Member |
|
|
Hi,
I am working on Oracle 9i and Developer 6i (on Windows 2000 Server) for contribution management system. Currently, the company is providing us clone machine with PIV Motherboard, 2.4GHz CPU, 1 GB RAM. There are about 250 tables and one of them contains about 50,00,000 records and this table is partitioned in 5 sections.
Now, while fetching information from this table, response time is too much slow,and thus, during demonstration, there is always issues arising regarding the performance.
Now, can you kindly give me the sugesstion in following topics :
1. Minimum Hardware / Server Specification
2. Better Performance Tunning and Optimising for Those Tables.
Thanks
Maheswor Maharjan
|
|
|
|
| Re: What is server configuration ? [message #59206 is a reply to message #59205] |
Tue, 04 November 2003 04:09  |
Daljit Singh
Messages: 290
Registered: October 2003
Location: Texas
|
Senior Member |
|
|
Hi,
Well Performance/Tuning is not a kind of thing which i tell u and u will implement and all the problems will be rectified. It is an ongoing process, not only server hardware but its OS, Oracle server confg., available memory etc all play an important role.
In ur case by knowing the info provided by u its really very tough to say anything abt ur problem.
The following factors may be checked in ur case :
R u using partitions properly in ur sql statements?
wht is the size of Shared Pool and buffer pool
Wht is the hit ratio of DB blocks and library cache
R any waits occuring on the server or not if yes so why
How much deep indexes do u have
number of I/O occured while processing the statement
etc etc etc.
In respect of server configuration if u want to increase the processing without increasing resources then u can configure MULTITHREADED SERVER OPTION otherwise DEDICATED will work properly. But again it depends on the types of users u r having in ur company.
For more info see the oracle documentation.
|
|
|
|
| Re: What is server configuration ? [message #59207 is a reply to message #59206] |
Tue, 04 November 2003 04:24 |
Maheswor Maharjan
Messages: 3
Registered: October 2002
|
Junior Member |
|
|
Thanks for response.
Again, as I know, the partition is ok. Shared Pool Size, Buffer Size, Cache, Block Size etc. are all default value during oracle 9i installation. I also forcively tried 4 times to default value, even, I could not get satisfied result. Next, currently, we are testing by only 10 users, but we have to maintain upto 100 users in future.
Dear gurujee, can you elaborate much more ?
Maheswor Maharjan
|
|
|
|
| Re: What is server configuration ? [message #59208 is a reply to message #59205] |
Tue, 04 November 2003 04:44 |
Daljit Singh
Messages: 290
Registered: October 2003
Location: Texas
|
Senior Member |
|
|
Hi,
Well according to me u should increase the size of shared pool as well as buffer cache. U should run some queries on ur database to get the hit ratio of library cache and DB cache. If the hit ratio will not accurate then u have to configure ur shared pool and buffer pool. The above mentioned things are most importantin and are the first step of performance tuning. Apart from this also be sure that u have used indexes propely. moreover just analyze the table as well as corresponding indexes so that CBO can work effectively. Use the funda of Histogram if ur data is not evenly distributed.
|
|
|
|
| Re: What is server configuration ? [message #59285 is a reply to message #59207] |
Sun, 09 November 2003 06:33 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Bumping up the buffer cache or hit ratios is not the right approach to tuning queries. Tune the queries first for optimal response and if possible the data model itself(which is usually difficult after it goes into production) ...
Make sure you are using bind variables to reduce unnecessary parsing and the resulting latch contention which will inhibit scalability.
Collect schema statistics regularly to aid the Cost based optimizer in helping good execution plans...
etc
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
