| Poor performance database.. pls help me [message #148412] |
Thu, 24 November 2005 01:35  |
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 and database is datawarehouse database. 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:
25165824(OLD VALUE)=24 MB
33554432(NEW VALUE)=32 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 1317 times)
|
|
|
|
|
|
|
|
| Re: Poor performance database.. pls help me [message #148618 is a reply to message #148436] |
Fri, 25 November 2005 06:24  |
genialsenthil
Messages: 26
Registered: June 2005
Location: Chennai
|
Junior Member |
|
|
Hai,
Thanks for your reply, and you told that to make PGA automatic
can u tell me how to make it, and whether we hake to make the
1.WORKAREA_SIZE_POLICY=manual option in init.ora file,
2.and database tempfile in extending to 17GB
3.and i want stop that can i alter that same temporary tablespace with keyword 'Reuse'
4. or i have to create new temporary tablespace
5. whether Gtemp in oracle10g will help me.
Thanks in Advance,
Senthil Kumar.S
|
|
|
|
|
|
| Re: Poor performance database.. pls help me [message #148641 is a reply to message #148632] |
Fri, 25 November 2005 13:39 |
sunil_v_mishra
Messages: 506
Registered: March 2005
|
Senior Member |
|
|
hi
genialsenthil I agree with maheshji words ...see below...
How about the network?
Since your client applications are in different locations, networks should play an important role.
If those applications are executing good locally, then the issue is with network
Regards
Sunilkumar
|
|
|
|
| Error in Clob Retrival .. Pls help me [Urgent] [message #149851 is a reply to message #148632] |
Mon, 05 December 2005 05:41 |
genialsenthil
Messages: 26
Registered: June 2005
Location: Chennai
|
Junior Member |
|
|
Hi Mahesh Rajendran,
Thankz for all those replies. Pls let me know solution for these errors in oracle 10g.
I am using Oracle 10g as server and Oracle 9i as client. My application is created in Visual Basic 6.0. I am storing the RTF Files in Oracle using CLOB column. Input format is different from file to file(which is .doc file or .html or .rtf). Some of the files contains Photos and some text area. I have convert these files in rtf format and assign the richtext to the clob column by sing "RichText Box" in Vb6.
The problem is on retrival of the clob column. I am getting the column by using the select query and assign the entire contents to the "Rich Textbox" in vb6 application. when i am attempt to assign the clob rtf text contents to the richtext box the applcation gets hanged or it will takes too much time to display the data. This kind of problem which comes for some file only. But for most of files retrival this error doesn't comes. Anyone pls assist me how to rectify this kind of problem. I think some errors in the file storage. Let me know what to do in this wheather insertion changes or retrival changes.
For insertion i have directly assign the text to Clob Column.
For eg....
<<<<< rsResume!FileContent = RtfResumeContent.TextRtf >>>>>>
For Retrival i have directly retrive the text from, Clob Column.
For eg....
<<<<< RtfResumeContent.TextRtf = rsResume!FileContent >>>>>>
With Thankz in advance...
Senthil Kunmar .S
|
|
|
|
|
|
| Re: Poor performance database.. pls help me [message #149907 is a reply to message #148412] |
Mon, 05 December 2005 12:10 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
|
I don't know the answer, but as a next step I would try to identify whether it only happened with certain files, or only happened with files over a certain size, or only happened during certain times of the day no matter the file. Basically, try to identify more of a pattern of behavior.
|
|
|
|
| Re: Error in Clob Retrival .. Pls help me [Urgent] [message #149998 is a reply to message #149907] |
Mon, 05 December 2005 23:25 |
genialsenthil
Messages: 26
Registered: June 2005
Location: Chennai
|
Junior Member |
|
|
Hi,
Thankz for all your replies. I think the problem is on size, but i am not sure about this. Most of the files which are hanged due to having high dpi pictures in the files. Pls let me know how to retrive these...
With Advanced Thankz & Regards,
Senthil Kumar
|
|
|
|
| Re: Error in Clob Retrival .. Pls help me [Urgent] [message #150417 is a reply to message #149998] |
Wed, 07 December 2005 16:58 |
goryunov
Messages: 5
Registered: December 2005
|
Junior Member |
|
|
Hi,
there are 80 per cent of library cache lock event in reach.lst report. Possibly, it due to a lot of implicit recompilation of objects. Many create table commands in the report might be the reason of it.
Can you shed any light on how the application works?
Also what type of connection the application uses (dedicated or shared)? How does VB connect to the database - using ODBC or oracle client?
Andrey
|
|
|
|
| Re: Poor performance database.. pls help me [message #150423 is a reply to message #148412] |
Wed, 07 December 2005 17:21 |
goryunov
Messages: 5
Registered: December 2005
|
Junior Member |
|
|
Since you use 10g database, statspack is supported but Oracle advises to run AWR report ($ORACLE_HOME/rdbms/admin/awrrpt.sql).
The default statistics gathering time is 1 hour.
It would be interesting to see the report.
Andrey
|
|
|
|
| Re: Error in Clob Retrival .. Pls help me [Urgent] [message #151773 is a reply to message #150417] |
Mon, 19 December 2005 02:47 |
genialsenthil
Messages: 26
Registered: June 2005
Location: Chennai
|
Junior Member |
|
|
Hi Andrey,
Thankz for ur reply. I have furnished the following as per your request...
Application Flow
-----------------
* This application is basically comprises of maintanenace of Candidates profile in document format and searching for the same to suit certain requirements. Operators are importing the Document files to the database and key in the candidate details. Applcation stores the various file format in CLOB field. Application converts the various file format to RTf format and stores it to CLOB. Other operators used to search in the database for matching candidates. Search includes Key Search, Company, Industry, Functional, Qualification and Location. Each criterias given here are stored in different tables and have seperate master. Search includes 2 joins and multiple sub queries with "and" condition. Consultant searches the profiles and can mail directly from the application to the candidates and clients.
Type of Connection
------------------
* Connection Type is --- Shared Server..
Type of Connection
------------------
* DB Connection Type is --- Shared Server..
* VB Application Connection -- Created "User DSN" from Data Source "Oracle in OraHome92". and Connectivity using ADO "Provider=msdasql.1;Data Source=OraSource;Uid=orausr;Pwd=orausr;" in Application.
With Thankz in advance & Regards,
Senthil Kumar .S
|
|
|
|
| Re: Error in Clob Retrival .. Pls help me [Urgent] [message #151914 is a reply to message #151773] |
Mon, 19 December 2005 17:38 |
goryunov
Messages: 5
Registered: December 2005
|
Junior Member |
|
|
Hi,
thanks for update.
I would recommend to do the following:
1. Decrease the shared pool down to ~120Mb (in the report you can see the same values for almost all diapason of shared pool sizes)
2. Start using bind variables. If you can not change the application right now set cursor_sharing parameter to similar.
3. There are some sql statements that create the same table (Tbl_Search_kiren). I think the reason of high values of library cache lock is using procedures that have code connected with tables that periodically dropped and created. That invalidates procedures/packages(headers) and forces the server to recompile the objects. You can prevent invalidation using either dynamic sql or code in package body.
4.Since the application uses shared servers, the situation when all dispatchers are busy might happened and new users are waiting to get connected. Try to increase the number of dispatchers or switch to using dedicated connections.
5.Set pga_aggregate_target to ~300Mb and workarea_size_policy to auto
Andrey
|
|
|
|
|
|
|
|
| Re: Error in Clob Retrival .. Pls help me [Urgent] [message #154445 is a reply to message #152109] |
Sun, 08 January 2006 10:26 |
SQLAREA
Messages: 21
Registered: January 2006
Location: Belgium
|
Junior Member |
|
|
Hi,
Since you are running in shared server mode - as far as I know - the UGA - user global area - is in the SGA. As far as I know the sizing of the session' s workarea' s should be done by sort_area_size, hash_area_size and bitmap_merge_area_size and NOT with the pga_aggregate_target when running in shared server mode. Pga_aggregate_target is only relevant when you run in dedicated server mode, since only then the UGA is in the PGA. (as far as I know).
It might be dangerous to decrease the shared_pool and the large_pool since the memory allocation for sorts and hash joins comes form the SGA. (when running in shared server mode)
Most of your problems seems to be network related.
You may want to adjust the sizing of the session data unit with SDU_SIZE. Take a look here http://web.umr.edu/~ora9i/network.901/a90154/advcfg.htm#475479
Set CURSOR_SHARING=SIMILAR or maybe even CURSOR_SHARING=FORCE
Your soft parse ratio is bad. Better is to rewrite the sql using bind variables (as already said)
You have also a lot of read and writes to and from the temp tablespace which explains the direct path read & write wait events, can be of poor indexing or your lob segments have the nocache attribute (which can be the best option anyway)
Regards
Guy Lambregts
[Updated on: Sun, 08 January 2006 11:18] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
