Home » RDBMS Server » Performance Tuning » Sql tuning, Waits
Sql tuning, Waits [message #65403] Thu, 09 September 2004 12:05 Go to next message
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 Go to previous messageGo to next message
Frank Naude
Messages: 4578
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 Go to previous messageGo to next message
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 Go to previous message
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...
Previous Topic: which to tune first
Next Topic: display no. of seconds elapsed
Goto Forum:
  


Current Time: Wed Feb 28 02:35:30 CST 2024