| Poor performance database.. pls help me [message #148407] |
Thu, 24 November 2005 01:23  |
genialsenthil
Messages: 26
Registered: June 2005
Location: Chennai
|
Junior Member |
|
|
Hai,
This senthil kumar working as oracle DBA. I am new to this field and our company has three branches. Headoffice is in Chennai & all other branches are in various cities (Bangalore, Hyderabad & Gurgaon).
My server database is Oracle 10g and client is Oracle 9i. There are totaly 35 systerms in Headoffice and 10 systems in each branches in other cities. My application is developed in Visual Basic 6.0. In local client machine i.e in headoffice the performance is somewhat better compared with other branches. But it is very slow in all other cities. Application takes too much time to open. (Even for execution of single query like validating user name & password with server). I have configured 3 various listeners for all branches in various cities.
The connectivity between local client machine & server is very fast for simple query. But for multiple joins & sub queries it takes time to display the result in local machines. It is very slow in other cities. I have optimized poor performance query with maximum possibilities. I have create compress index for all the tables. i have increased the oracle sga and other sizes (file enclosed).
I have taken two statspack & sqlplan at time 12-13,14-15. I have enclosed the statspack report, sqlplan, my server configuration details and init.ora changes i did.
I can't able to have solution of it. Please any body guide what to do increase in sga or system configuration. Also please tell me that Gtemp in 10g database will speed up my database.
Let me know these solutions...
* Slow access in Other cities for simple select,update,insert statement.
* Slower in resultset for Multiple joins & sub queries... even in local machines.
* Sometimes client machines get hanged when accessing database and Server takes CPU memory upto 100%.
1.ENHANCEMENTS IN PFILE;
DB_CACHE_SIZE:
16777216 (OLD VALUE) =16 MB
440401920 (NEW VALUE)= 420MB
JAVA_POOL_SIZE:
50331648 (OLD VALUE) =48 MB
20971520 (NEW VALUE)= 20MB
LARGE_POOL_SIZE:
8388608 (OLD VALUE)= 8 MB
20971520 (NEW VALUE)=20 MB
SHARED_POOL_SIZE:
83886080(OLD VALUE)= 80 MB
440401920 (NEW VALUE)= 420 MB
PGA_AGGREGATE_TARGET:
52428800(OLD VALUE)=50 MB
26214400(NEW VALUE)=25 MB
SORT_AREA_SIZE:
65536(OLD VALUE)=64K
524288(NEW VALUE)=512K
MEMORY TAKEN BY ORACLE :
ORACLE.EXE=164644K(OLD VALUE)
ORACLE.EXE=222524K(NEW VALUE)
LOG BUFFER SIZE:
512K (UN CHANGED)
STD BLOCK SIZE:
8K
SYSTEM CONFIGURATION ( IBM SERVER):
O.S : WINDOWS 2003
HARD DISK: 120 GB (SCSI CABLE).
MEMORY: 1.5 GB.
PROCESSOR: P4 3.1GHZ, NO DUAL PROCESSOR.
Thankz in Advance & Regards,
Senthil Kumar.
-
Attachment: Report.zip
(Size: 71.01KB, Downloaded 1248 times)
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
