enterprise manager Network

[Roshan]

Hi,

users are making deployments on APEX. They are experiencing slowness in system. I found the Network peak is high.

Does it mean the deployment process requires a lot of back and forth between the DB and the client? (Many single row inserts maybe?)

Do I need to contact a network admin for this?

Capture.PNG

Thanks,
Roshan

[Roshan]

SQL> select name from v$event_name where wait_class = 'Network';
 
 
NAME
----------------------------------------------------------------
remote db operation
remote db file read
remote db file write
ARCH wait for net re-connect
LGWR wait on ATTACH
ARCH wait on ATTACH
ARCH wait for netserver start
LNS wait on ATTACH
LNS wait on SENDREQ
LNS wait on DETACH
LGWR wait on SENDREQ
 
 
NAME
----------------------------------------------------------------
LGWR wait on DETACH
ARCH wait on SENDREQ
ARCH wait on DETACH
ARCH wait for netserver init 2
LNS wait on LGWR
LGWR wait on LNS
ARCH wait for flow-control
ARCH wait for netserver detach
TCP Socket (KGAS)
virtual circuit wait
dispatcher listen timer
 
 
NAME
----------------------------------------------------------------
dedicated server timer
SQL*Net message to client
SQL*Net message to dblink
SQL*Net more data to client
SQL*Net more data to dblink
SQL*Net more data from client
SQL*Net message from dblink
SQL*Net more data from dblink
SQL*Net vector data to client
SQL*Net vector data from client
SQL*Net vector data to dblink
 
 
NAME
----------------------------------------------------------------
SQL*Net vector data from dblink
TEXT: URL_DATASTORE network wait
 
 
35 rows selected.
 
 
SQL>
 
SQL> select event, count(*) FROM v$active_session_history where sample_time='27-JUN-16 04.00.42.147 PM' and wait_class = 'Network' group by event;
 
 
EVENT                                                              COUNT(*)
---------------------------------------------------------------- ----------
virtual circuit wait                                                      3

[ErmanArslansOracleBlog]

What kind of deployments they are? Network waits is a general term. Need to identify the network type wait event name.
Take an awr report and analyze. also check the db sessions, check the active ones, see their current waits and see what the network related wait event is. It can be sqlnet from server, it can be sqlnet from dblink or it can be something else. Need to identify this first.

[Roshan]

sqlnet is taking more time.

waits.xlsx
ASH_report_1467031492643.html

[ErmanArslansOracleBlog]

obtain a 1 hour or 30 min AWR(taken from the snapshots which are created while you were having this problem) and send me.

Document:748642.1 How to Generate an AWR Report and Create Baselines

Also, check this note: "High Virtual Circuit Waits When Working with the Apex Application Using XDB (Doc ID 1136313.1)" -- this is for Apex.!

[Roshan]

Thanks. I already sent you AWR from 16.00 to 16.30.

[ErmanArslansOracleBlog]

It was ASH, not AWR.

Also, check this note: "High Virtual Circuit Waits When Working with the Apex Application Using XDB (Doc ID 1136313.1)" -- this is for Apex.! 

2016-06-27 15:19 GMT+03:00 Roshan [via Erman Arslan's Oracle Forum] <[hidden email]>:

Thanks. I already sent you AWR from 16.00 to 16.30.

---

If you reply to this email, your message will be added to the discussion below:

http://erman-arslan-s-oracle-forum.2340467.n4.nabble.com/enterprise-manager-Network-tp1092p1098.html

To start a new topic under Database, email [hidden email]
To unsubscribe from Erman Arslan's Oracle Forum, click here.
NAML

--

[Roshan]

Please find attached.
report1.lst

[ErmanArslansOracleBlog]

Following query coming from Realtime Connection SQL module is the problem:

select O.name objname, O.type# objtype, U.name objowner, L2.*, O.subname from (select /*+ ordered */ 0, S.sid, S.serial#, nvl(S.sql_id, 0) , DECODE(L.type, 'TM', L.id1, 'TX', decode(L.request, 0, NVL(LO.object_id, -1), S.row_wait_obj#), -1) AS object_id , S.username, S.row_wait_obj#, S.row_wait_block# , S.row_wait_row#, S.row_wait_file#, L.type , decode(L.lmode, 0, 'NONE', 1, 'NULL', 2, 'ROW SHARE', 3, 'ROW EXCLUSIVE', 4, 'SHARE', 5, 'SHARE ROW EXCLUSIVE', 6, 'EXCLUSIVE', '?') , decode(L.request, 0, 'NONE', 1, 'NULL', 2, 'ROW SHARE', 3, 'ROW EXCLUSIVE', 4, 'SHARE', 5, 'SHARE ROW EXCLUSIVE', 6, 'EXCLUSIVE', '?') , L.id1, L.id2, L.ctime , P.spid, S.sql_hash_value from v$lock L, v$session S, v$process P, (select object_id, session_id, xidsqn from v$locked_object where xidsqn >0) LO where S.sid = L.sid and P.addr = S.paddr and L.type != 'MR' and L.sid = LO.session_id(+) and L.id2 = LO.xidsqn(+) ) L2 , sys."_CURRENT_EDITION_OBJ" O , sys.user$ U where O.obj#(+) = L2.object_id and O.owner# = U.user#(+) and U.type# != 2

[ErmanArslansOracleBlog]

İs it yours or some developers query for detecting locks? Disable that module and your db performance will increase.

[Roshan]

The developers' query.

O.k  I will inform them

[Roshan]

Can I run the command from Doc ID 1136313.1 with APEX 5.0?

[ErmanArslansOracleBlog]

Yes, that s why I sent you that document.

[Roshan]

Thanks a lot for your help :)

[Roshan]

I have executed the command in the APEX doc and i see there is an improvement in the virtual circuit time.

If I decrease the call-timeout from 300 to 100, will it be ok?

[ErmanArslansOracleBlog]

İt cant be answered. You should test and see.

30 Haz 2016 12:06 tarihinde "Roshan [via Erman Arslan's Oracle Forum]" <[hidden email]> yazdı:

I have executed the command in the APEX doc and i see there is an improvement in the virtual circuit time.

If I decrease the call-timeout from 300 to 100, will it be ok?

---

If you reply to this email, your message will be added to the discussion below:

http://erman-arslan-s-oracle-forum.2340467.n4.nabble.com/enterprise-manager-Network-tp1092p1133.html

To start a new topic under Database, email [hidden email]
To unsubscribe from Erman Arslan's Oracle Forum, click here.
NAML

[Roshan]

Thanks.