| application hangs..!!! [message #138353] |
Wed, 21 September 2005 09:00  |
balavignesh
Messages: 180
Registered: March 2005
|
Senior Member |
|
|
hai ,
see our application is hanging which is retrieving records from around 35 tables each table having lacs of record.what might be the overhead..???plz help out.
db -->oracle 8i
os-->linux
we have also added considerable amount of datafiles to DATA/LOB/TEMP.
regards
bala
|
|
|
|
|
|
|
|
| Re: application hangs..!!! [message #138367 is a reply to message #138362] |
Wed, 21 September 2005 09:58  |
balavignesh
Messages: 180
Registered: March 2005
|
Senior Member |
|
|
the waits are
Event Waits Wait Time (cs) % Total Wt Time
-----------------------------------------------------------------------------------------
db file sequential read 26,385 0 .00
db file scattered read 12,547 0 .00
direct path write 3,010 0 .00
SQL*Net more data to client 2,409 0 .00
direct path read 2,047 0 .00
the application was fine till last week but now hangs..!!
[Updated on: Wed, 21 September 2005 09:59] Report message to a moderator |
|
|
|
|
|
| Re: application hangs..!!! [message #138373 is a reply to message #138368] |
Wed, 21 September 2005 10:05 |
balavignesh
Messages: 180
Registered: March 2005
|
Senior Member |
|
|
yes ..the wait events above said was so reduced which was previously very large as below
Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait % Total
Event Waits Time (cs) Wt Time
-------------------------------------------- ------------ ------------ -------
db file sequential read 356,923 0 .00
db file scattered read 152,014 0 .00
direct path read 59,100 0 .00
SQL*Net more data to client 56,168 0 .00
direct path write 45,048 0 .00
-------------------------------------------------------------
i have gathered stats for the tables involved in the application and also increase buffer_cache size.
anything else to do...now the application is not hanging , it is running slowly...
what to do further..?
|
|
|
|
|
|
| Re: application hangs..!!! [message #138377 is a reply to message #138376] |
Wed, 21 September 2005 10:30 |
alliejane
Messages: 59
Registered: July 2005
Location: Glasgow
|
Member |
|
|
| Quote: |
ALways delete the old stats and gather new one ( as mentioned in the sticky)
|
May I ask - why delete first, it's not something I've ever recommended - gather new YES but remove first seems redundant since they are overwritten anyway.
Cheers
Allie
|
|
|
|
| Re: application hangs..!!! [message #138378 is a reply to message #138377] |
Wed, 21 September 2005 10:35 |
balavignesh
Messages: 180
Registered: March 2005
|
Senior Member |
|
|
i have gathered stats for the tables involved in the app using
dbms_stats.gather_table_stats() to trigger the CBO in the server.
am i right..?now it seems quite good but slow...
|
|
|
|
|
|
| Re: application hangs..!!! [message #138871 is a reply to message #138368] |
Sat, 24 September 2005 15:13 |
vjeedigunta
Messages: 201
Registered: March 2005
Location: Hyderabad
|
Senior Member |
|
|
Considering the below statement of yours ..
retrieving records from around 35 tables each table having lacs of record
I would recommend create a materialized view with the query and have it refresh (depending upon the frequncy at which u want to view the updated data ) and have your app. select from this mat view ...that way it will decrease the overhead of processsing this monster query every time the app wants to display this page ..
HTH
-Sai Sreenivas Jeedigunta
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
