Home » RDBMS Server » Performance Tuning » Remove SQL*Net waitevent from top 5 list (Oracle 11.2.0.2)
Remove SQL*Net waitevent from top 5 list [message #566572] Mon, 17 September 2012 11:39 Go to next message
sathik
Messages: 196
Registered: August 2009
Location: INDIA
Senior Member
Hi,

I saw the below Top 5 waitevents in AWR report. The below queries are using db links. For the 3rd query, I am planning to create a view for the select portion in remote database. I feel it would help me to remove the SQL*Net weitevents from the top 5 list. I need your suggestion on this.


Top 5 Timed Foreground Events

Event 	                       Waits Time(s) Avg wait (ms) % DB time Wait Class 
DB CPU   			      6,437                  24.69   
SQL*Net message from dblink  146,277  6,337         43       24.30   Network 
db file sequential read      327,987  2,938          9       11.27   User I/O 
log file sync                 97,437  2,404         25        9.22   Commit 
SQL*Net more data from dblink 16,682  1,421         85        5.45   Network 



Query 1:
======== 

SELECT SERIAL_NUM FROM IDA.S_ASSET@KEN_LINK.WORLD 
WHERE (OWNER_ACCNT_ID = :B1 ) 
AND STATUS_CD = 'Active' 
AND PROD_ID IN ('1-1XT7XU', '1-SRL6LA', '1-P74IP6') 
AND SUBSTR(SERIAL_NUM, 1, 1)='0'

Query 2:
======== 

SELECT DISTINCT A.SERIAL_NUM FROM IDA.S_ASSET@KEN_LINK.WORLD A, IDA.S_ORG_EXT@KEN_LINK.WORLD B 
WHERE A.BILL_ACCNT_ID=B.ROW_ID 
AND B.OU_NUM=:B1 
AND (TO_CHAR (SUBSTR (A.SERIAL_NUM, 1, 1)) = '0') 
AND LENGTH (A.SERIAL_NUM) > 9 
AND LENGTH (A.SERIAL_NUM) < 14 
AND LENGTH (A.SERIAL_NUM)- LENGTH (TRANSLATE (A.SERIAL_NUM, CHR (1)|| 
TRANSLATE (A.SERIAL_NUM, CHR (1) || '1234567890', CHR (1)), CHR (1)))=0 
AND A.SERIAL_NUM IS NOT NULL;


Query 3:
======== 
	 INSERT ALL
         INTO FETCHPROCESS.TMP_ORDERDETAILS_TAB_ORD
         (
         ORDERID,
         ORDERITEMSUBTYPE,
         ORDERVERSIONNUMBER,
         ORDERDATE,
         ORDERITEMID,
         ORDERITEMORDERID,
         ORDERITEMREQSHIPDATE,
         ORDERITEMACTIONCODE,
         ORDERITEMASSETID,
         ORDERITEMPARENTITEMID,
         ORDERITEMROOTITEMID,
         ORDERITEMSERVICENUM,
         ORDERITEMSTATUS,
         ORDERITEMPROMOID,
         ORDERITEMAPPOINTMENTID,
         ORDERITEMSUBSTATUS,
         ORDERITEMSHIPTOCONTACTID,
         ORDERITEMPRODUCTSCODE,
         ORDERITEMPREVIOUSSERVICEID,
         SERVICEPOINTSERVICEID,
         SERVICEPOINTSTATUS,
         SERVICEPOINTSUBSTATUS,
         SERVICEPOINTHIGHBANDSUBSTATUS,
         ORDERITEMATTRIBNAME,
         ORDERITEMATTRIBCHARVALUE,
         ORDERITEMCREATED,
         ORDERITEMLASTUPDATED,
         ORDERSERVACCTID
         )
         values (
         ORDERID,
         ORDERITEMSUBTYPE,
         ORDERVERSIONNUMBER,
         ORDERDATE,
         ORDERITEMID,
         ORDERITEMORDERID,
         ORDERITEMREQSHIPDATE,
         ORDERITEMACTIONCODE,
         ORDERITEMASSETID,
         ORDERITEMPARENTITEMID,
         ORDERITEMROOTITEMID,
         ORDERITEMSERVICENUM,
         ORDERITEMSTATUS,
         ORDERITEMPROMOID,
         ORDERITEMAPPOINTMENTID,
         ORDERITEMSUBSTATUS,
         ORDERITEMSHIPTOCONTACTID,
         ORDERITEMPRODUCTSCODE,
         ORDERITEMPREVIOUSSERVICEID,
         SERVICEPOINTSERVICEID,
         SERVICEPOINTSTATUS,
         SERVICEPOINTSUBSTATUS,
         SERVICEPOINTHIGHBANDSUBSTATUS,
         ORDERITEMATTRIBNAME,
         ORDERITEMATTRIBCHARVALUE,
         ORDERITEMCREATED,
         ORDERITEMLASTUPDATED,
         ORDERSERVACCTID
         )
         INTO FETCHPROCESS.TMP_OVORDERDETAILS_TAB
         (
         ORDERID,
         ORDERITEMSUBTYPE,
         ORDERVERSIONNUMBER,
         ORDERDATE,
         ORDERITEMID,
         ORDERITEMORDERID,
         ORDERITEMREQSHIPDATE,
         ORDERITEMACTIONCODE,
         ORDERITEMASSETID,
         ORDERITEMPARENTITEMID,
         ORDERITEMROOTITEMID,
         ORDERITEMSERVICENUM,
         ORDERITEMSTATUS,
         ORDERITEMPROMOID,
         ORDERITEMAPPOINTMENTID,
         ORDERITEMSUBSTATUS,
         ORDERITEMSHIPTOCONTACTID,
         ORDERITEMPRODUCTSCODE,
         ORDERITEMPREVIOUSSERVICEID,
         SERVICEPOINTSERVICEID,
         SERVICEPOINTSTATUS,
         SERVICEPOINTSUBSTATUS,
         SERVICEPOINTHIGHBANDSUBSTATUS,
         ORDERITEMATTRIBNAME,
         ORDERITEMATTRIBCHARVALUE,
         ORDERITEMCREATED,
         ORDERITEMLASTUPDATED,
         ORDERSERVACCTID
         )
         values
         (
         ORDERID,
         ORDERITEMSUBTYPE,
         ORDERVERSIONNUMBER,
         ORDERDATE,
         ORDERITEMID,
         ORDERITEMORDERID,
         ORDERITEMREQSHIPDATE,
         ORDERITEMACTIONCODE,
         ORDERITEMASSETID,
         ORDERITEMPARENTITEMID,
         ORDERITEMROOTITEMID,
         ORDERITEMSERVICENUM,
         ORDERITEMSTATUS,
         ORDERITEMPROMOID,
         ORDERITEMAPPOINTMENTID,
         ORDERITEMSUBSTATUS,
         ORDERITEMSHIPTOCONTACTID,
         ORDERITEMPRODUCTSCODE,
         ORDERITEMPREVIOUSSERVICEID,
         SERVICEPOINTSERVICEID,
         SERVICEPOINTSTATUS,
         SERVICEPOINTSUBSTATUS,
         SERVICEPOINTHIGHBANDSUBSTATUS,
         ORDERITEMATTRIBNAME,
         ORDERITEMATTRIBCHARVALUE,
         ORDERITEMCREATED,
         ORDERITEMLASTUPDATED,
         ORDERSERVACCTID
         )
         Select
         Ord.Order_Num Orderid,
         S_Order_X.Attrib_36 Orderitemsubtype,
         Ord.Rev_Num Orderversionnumber,
         Ord.Order_Dt Orderdate,
         Ord_Itm.Row_Id Orderitemid,
         Ord_Itm.Order_Id Orderitemorderid,
         Ord_Itm.Req_Ship_Dt Orderitemreqshipdate,
         Ord_Itm.Action_Cd Orderitemactioncode,
         Ord_Itm.Asset_Id Orderitemassetid,
         Ord_Itm.Par_Order_Item_Id Orderitemparentitemid,
         Ord_Itm.Root_Order_Item_Id Orderitemrootitemid,
         Ord_Itm.Service_Num Orderitemservicenum,
         Ord_Itm.Status_Cd Orderitemstatus,
         Ord_Itm.Promotion_Id Orderitempromoid,
         Ord_Itm.X_Bt_Appointment_Id Orderitemappointmentid,
         Ord_Itm.X_Bt_Sub_Status_Cd Orderitemsubstatus,
         Ord_Itm.Ship_Con_Id Orderitemshiptocontactid,
         Prd_Int.Part_Num Orderitemproductscode,
         Ord_Itm_Om.Prev_Service_Num Orderitempreviousserviceid,
         Sp.Serial_Num Servicepointserviceid,
         Sp.Status_Cd Servicepointstatus,
         Sp.Sub_Status_Cd Servicepointsubstatus,
         Sp.X_Bt_High_Band_Sub_Status Servicepointhighbandsubstatus,
         Xa.Attr_Name Orderitemattribname,
         Xa.Char_Val Orderitemattribcharvalue,
         Ord_Itm.Created Orderitemcreated,
         Ord_Itm.Last_Upd Orderitemlastupdated,
         Ord_Itm.Serv_Accnt_Id Orderservacctid
         FROM 	IDA.S_ORDER_ITEM@bs_prod.world ord_itm,
         	IDA.S_PROD_INT@bs_prod.world  prd_int,
         	IDA.S_ORDER@bs_prod.world ord,
         	IDA.S_ORDER_ITEM_OM@bs_prod.world ord_itm_om,
         	IDA.S_ASSET@bs_prod.world sp,
         	IDA.s_Order_x@bs_prod.world s_order_x,
         	IDA.S_ORDER_ITEM_XA@bs_prod.world XA
         WHERE  ord_itm.prod_id = prd_int.row_id
         AND    ord_itm.row_id = ord_itm_om.par_row_id
         AND    ord_itm.order_id = ord.row_id
         AND    ord_itm.asset_id = sp.row_id(+)
         AND    ord.row_id = s_order_x.par_row_id(+)
         AND    ord_itm.row_id = XA.ORDER_ITEM_ID(+)
         AND    ord.X_BT_EXTERNAL_SYSTEM_TYPE IS NULL
         AND    ord.ACTIVE_FLG = 'Y'
         AND    (EXP_TO_ICTXN_FLG = 'Y' OR FULFIL_LOCKED_FLG = 'Y')
         AND    ord_itm.LAST_UPD BETWEEN :start_date AND :end_date;


[Updated on: Mon, 17 September 2012 12:03] by Moderator

Report message to a moderator

Re: Remove SQL*Net waitevent from top 5 list [message #566573 is a reply to message #566572] Mon, 17 September 2012 11:47 Go to previous messageGo to next message
BlackSwan
Messages: 23146
Registered: January 2009
Senior Member
> I feel it would help me to remove the SQL*Net weitevents from the top 5 list. I need your suggestion on this.
stop using DBLINKS.
Re: Remove SQL*Net waitevent from top 5 list [message #566574 is a reply to message #566573] Mon, 17 September 2012 11:51 Go to previous messageGo to next message
sathik
Messages: 196
Registered: August 2009
Location: INDIA
Senior Member
I am fetching data from remote db. how can I avoid db links here?
Re: Remove SQL*Net waitevent from top 5 list [message #566575 is a reply to message #566574] Mon, 17 September 2012 12:02 Go to previous messageGo to next message
BlackSwan
Messages: 23146
Registered: January 2009
Senior Member
>how can I avoid db links here?
the data must be local or accept the overhead to access remote data
Re: Remove SQL*Net waitevent from top 5 list [message #566598 is a reply to message #566575] Mon, 17 September 2012 15:40 Go to previous messageGo to next message
sathik
Messages: 196
Registered: August 2009
Location: INDIA
Senior Member
if i create a view for the select query, will it reduce the network overhead?
Re: Remove SQL*Net waitevent from top 5 list [message #566600 is a reply to message #566598] Mon, 17 September 2012 17:17 Go to previous messageGo to next message
BlackSwan
Messages: 23146
Registered: January 2009
Senior Member
>if i create a view for the select query, will it reduce the network overhead?
where does view reside?
where does data reside?
Re: Remove SQL*Net waitevent from top 5 list [message #566606 is a reply to message #566598] Mon, 17 September 2012 23:35 Go to previous messageGo to next message
Michel Cadot
Messages: 59991
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
sathik wrote on Mon, 17 September 2012 22:40
if i create a view for the select query, will it reduce the network overhead?


A view does not change anything to data accesses.
It is just a name for a query.

Regards
Michel
Re: Remove SQL*Net waitevent from top 5 list [message #566607 is a reply to message #566606] Tue, 18 September 2012 00:07 Go to previous messageGo to next message
Littlefoot
Messages: 19887
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
However, if you can, create a materialized view (by saying that, I don't mean that you are granted the CREATE MV privileges, but whether business requirements allow you that) which might improve performance.
Re: Remove SQL*Net waitevent from top 5 list [message #566638 is a reply to message #566606] Tue, 18 September 2012 04:29 Go to previous messageGo to next message
sathik
Messages: 196
Registered: August 2009
Location: INDIA
Senior Member
if I create a inline view, wont it cut down the number of rows that are transferred over dblink?
Re: Remove SQL*Net waitevent from top 5 list [message #566645 is a reply to message #566638] Tue, 18 September 2012 05:08 Go to previous messageGo to next message
Michel Cadot
Messages: 59991
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No.
As I said:

Quote:
A view does not change anything to data accesses.
It is just a name for a query.


You can repeat the same thing in other ways, this will not change the facts.

Regards
Michel
Re: Remove SQL*Net waitevent from top 5 list [message #566706 is a reply to message #566645] Tue, 18 September 2012 14:02 Go to previous messageGo to next message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
Create a materialized view on the remote database with the results of the join. Then simply select the results from the materialized view via the dblink.
Re: Remove SQL*Net waitevent from top 5 list [message #566708 is a reply to message #566706] Tue, 18 September 2012 14:04 Go to previous message
Littlefoot
Messages: 19887
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'd rather create it here (in my own schema) and refresh when appropriate (such as during the night hours), than access the materialized view over the database link.
Previous Topic: FOR Loop and Driving Site
Next Topic: HIBERNATE query runs slow but ok in SQLPLUS
Goto Forum:
  


Current Time: Fri Dec 19 04:10:24 CST 2014

Total time taken to generate the page: 0.07156 seconds