| Sql tuning, Waits [message #65403] |
Thu, 09 September 2004 12:05  |
Sam
Messages: 255
Registered: April 2000
|
Senior Member |
|
|
Hi, I have 2 prod instances running on a server.
I am trying to tune some sql's which originate on
instance 1 and also get data from instnce 2 thru dblinks.
I tried to trace the session using oradebug utility , when i look the trace file while the trace is being generated, i observed the following happening most of the time .
can anyone help me understand whats going on
----------
WAIT #1: nam='SQL*Net message to dblink' ela= 0 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message from dblink' ela= 0 p1=1413697536 p2=1 p3=0
--------------
WAIT #1: nam='latch free' ela= 0 p1=17182333928 p2=66 p3=0
WAIT #1: nam='latch free' ela= 0 p1=17182333928 p2=66 p3=0
----------
Thanx in Advance
Sam
|
|
|
|
| Re: Sql tuning, Waits [message #65418 is a reply to message #65403] |
Thu, 16 September 2004 07:00  |
Frank Naude
Messages: 4587
Registered: April 1998
|
Senior Member |
|
|
Hi,
This is either because your network is too slow; or you try to send too much data across the network.
Some suggestions:
1. Implement data replication (Materialized Views, Advanced Replication or Streams) to keep data local to the users using it.
2. Upgrade the network cards/ links
3. Have multiple network connections between the databases.
Best regards.
Frank
|
|
|
|
| Re: Sql tuning, Waits [message #65419 is a reply to message #65418] |
Thu, 16 September 2004 09:23 |
Sam
Messages: 255
Registered: April 2000
|
Senior Member |
|
|
Thanx for the reply Frank, The two instances are running on the same server, so the network problem doesnt arise i suppose.
And regarding the materialized views, do u think will it make any diff, as they r on the same server.
The problem is , the session firing these sql's are using lot of CPU. Do u think Creating the MV's will help me resolving that?
Thanks in advance.
Sam.
|
|
|
|
| Re: Sql tuning, Waits [message #65420 is a reply to message #65419] |
Thu, 16 September 2004 16:33 |
Bruce McCartney
Messages: 7
Registered: September 2004
|
Junior Member |
|
|
Sam,
check that you have TIMED_STATISTICS to TRUE (your data showed ela[[psed time]]=0; this will ensure that you don't just count the most frequent wait but focus on the time component. Latch waits are usually very, very small...
Excessive CPU usage is likely due to excessive 'logical reads' that can be reduced by tuning the application sql...
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
