Home » RDBMS Server » Performance Tuning » SQL*Net roundtrips (Oracle 12c on LInux)
SQL*Net roundtrips [message #644489] Sat, 07 November 2015 23:22 Go to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
Hi ,

Doing some R&D with SQL execution. Can someone please explain why "2 SQL*Net roundtrips to/from client" when my ARRAYSIZE is 15 ?







sys@MYDB1> select * from scott.bla;

	ID
----------
	 1
	 2
	 3
	 4
	 5
	 6
	 7
	 8
	 9
	10
	11
	12
	13
	14
	15

15 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2886851054

--------------------------------------------------------------------------
| Id  | Operation	  | Name | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	 |    15 |   195 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| BLA  |    15 |   195 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
	  0  recursive calls
	  0  db block gets
	  8  consistent gets
	  0  physical reads
	  0  redo size
	702  bytes sent via SQL*Net to client
	552  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	 15  rows processed

sys@MYDB1> show arraysize
arraysize 15

Report message to a moderator

Re: SQL*Net roundtrips [message #644490 is a reply to message #644489] Sat, 07 November 2015 23:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

The best way it to enable trace and see the roundtrips in the trace file.
alter session set events '10046 trace name context forever, level 12';

Then tell us. Wink

Report message to a moderator

Re: SQL*Net roundtrips [message #644491 is a reply to message #644490] Sun, 08 November 2015 00:16 Go to previous messageGo to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member


from the tracefile...



PARSING IN CURSOR #139706157550992 len=23 dep=0 uid=0 oct=3 lid=0 tim=20416017771573 hv=3255111028 ad='cdfb61930' sqlid='7amt41g10a3bn'
select * from scott.bla
END OF STMT
PARSE #139706157550992:c=1000,e=1092,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=2886851054,tim=20416017771571
EXEC #139706157550992:c=0,e=20,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2886851054,tim=20416017771710
FETCH #139706157550992:c=0,e=54,p=0,cr=7,cu=0,mis=0,r=1,dep=0,og=1,plh=2886851054,tim=20416017771801
FETCH #139706157550992:c=0,e=13,p=0,cr=1,cu=0,mis=0,r=14,dep=0,og=1,plh=2886851054,tim=20416017771989
STAT #139706157550992 id=1 cnt=15 pid=0 pos=1 obj=214733 op='TABLE ACCESS FULL BLA (cr=8 pr=0 pw=0 time=49 us cost=3 size=93 card=31)'
PARSE #139706162546360:c=0,e=19,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=20416017772299
EXEC #139706162546360:c=0,e=141,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=20416017772505N?0G~oCR1


Two FETCH calls. It supposed to be just one FETCH right ? 15rows/15 Arrasize=1 FETCH.

Report message to a moderator

Re: SQL*Net roundtrips [message #644492 is a reply to message #644491] Sun, 08 November 2015 00:18 Go to previous messageGo to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member

Tried with 14 Rows, Still Oracle thinks it need 2 FETCH for arraysize 15.


=====================
PARSING IN CURSOR #139706157479632 len=23 dep=0 uid=0 oct=3 lid=0 tim=20416288992636 hv=3255111028 ad='cdfb61930' sqlid='7amt41g10a3bn'
select * from scott.bla
END OF STMT
PARSE #139706157479632:c=0,e=85,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2886851054,tim=20416288992635
EXEC #139706157479632:c=0,e=45,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2886851054,tim=20416288992815
FETCH #139706157479632:c=0,e=98,p=0,cr=7,cu=0,mis=0,r=1,dep=0,og=1,plh=2886851054,tim=20416288992959
FETCH #139706157479632:c=0,e=13,p=0,cr=1,cu=0,mis=0,r=13,dep=0,og=1,plh=2886851054,tim=20416288993186
STAT #139706157479632 id=1 cnt=14 pid=0 pos=1 obj=214733 op='TABLE ACCESS FULL BLA (cr=8 pr=0 pw=0 time=93 us cost=3 size=93 card=31)'
PARSE #139706162546360:c=0,e=22,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=20416288993508N?6G~cBR1

EXEC #139706162546360:c=0,e=176,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=20416288993784

Report message to a moderator

Re: SQL*Net roundtrips [message #644493 is a reply to message #644492] Sun, 08 November 2015 00:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

How did you activate the trace? If you use my command you should have the wait events, especially "SQL*Net message to client" and "SQL*Net message from client" which clearly show the net round trips.

You can see how it works.
First fetch:
FETCH #139706157479632:c=0,e=98,p=0,cr=7,cu=0,mis=0,r=1,dep=0,og=1,plh=2886851054,tim=20416288992959
Second one:
FETCH #139706157479632:c=0,e=13,p=0,cr=1,cu=0,mis=0,r=13,dep=0,og=1,plh=2886851054,tim=20416288993186

The first fetch always fetches only one row, it checks if there is at least one row.

Report message to a moderator

Re: SQL*Net roundtrips [message #644494 is a reply to message #644493] Sun, 08 November 2015 00:46 Go to previous messageGo to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
I was using SQL_TRACE . Let me try 10046

Report message to a moderator

Re: SQL*Net roundtrips [message #644495 is a reply to message #644494] Sun, 08 November 2015 00:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

SQL_TRACE does not allow you to get wait events (or bind variables), this is why I posted the event.
You can also use DBMS_SESSION.SESSION_TRACE_ENABLE if you don't want to use event:
PROCEDURE SESSION_TRACE_ENABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 WAITS                          BOOLEAN                 IN     DEFAULT
 BINDS                          BOOLEAN                 IN     DEFAULT
 PLAN_STAT                      VARCHAR2                IN     DEFAULT

Report message to a moderator

Re: SQL*Net roundtrips [message #644496 is a reply to message #644495] Sun, 08 November 2015 01:25 Go to previous messageGo to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member

15 Rows .


=====================
PARSING IN CURSOR #139729047632744 len=23 dep=0 uid=0 oct=3 lid=0 tim=20419827521738 hv=3746346130 ad='b7d0bfaa0' sqlid='9wp226zgntd4k'
select * from scott.BLA
END OF STMT
PARSE #139729047632744:c=0,e=55,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2886851054,tim=20419827521738
WAIT #139729047632744: nam='Disk file operations I/O' ela= 14 FileOperation=8 fileno=0 filetype=8 obj#=-1 tim=20419827521802
EXEC #139729047632744:c=0,e=18,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2886851054,tim=20419827521833
WAIT #139729047632744: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=20419827521853
FETCH #139729047632744:c=0,e=87,p=0,cr=7,cu=0,mis=0,r=1,dep=0,og=1,plh=2886851054,tim=20419827521956
WAIT #139729047632744: nam='SQL*Net message from client' ela= 85 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=20419827522061
WAIT #139729047632744: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=20419827522082
FETCH #139729047632744:c=0,e=24,p=0,cr=1,cu=0,mis=0,r=14,dep=0,og=1,plh=2886851054,tim=20419827522100
STAT #139729047632744 id=1 cnt=15 pid=0 pos=1 obj=214761 op='TABLE ACCESS FULL BLA (cr=8 pr=0 pw=0 time=83 us cost=3 size=195 card=15)'
WAIT #139729047632744: nam='SQL*Net message from client' ela= 163 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=20419827522325N?pF~SJ+1


Yes, it FETCH 1 row first , then FETCH 14 Rows . Why it has to test before it fetch more rows ? Oracle server reads 7 blocks just to retrieve 1 row to the client ! :/

Report message to a moderator

Re: SQL*Net roundtrips [message #644497 is a reply to message #644496] Sun, 08 November 2015 01:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Statistically, on the long term, with many sessions and applications, including the case with errors, it is cheaper to do that way.

[Updated on: Sun, 08 November 2015 01:31]

Report message to a moderator

Re: SQL*Net roundtrips [message #644498 is a reply to message #644497] Sun, 08 November 2015 01:41 Go to previous message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
Thanks Mike .

Report message to a moderator

Previous Topic: dynamic sampling precedence
Next Topic: Locking modes
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Jan 30 13:41:45 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.