What happens behind the scenes when querying through SQL Developer?

Question

I always wondered what happens behind the scenes when I query my DB through SQL Developer.

Regardless of the size of the table, I always get no more than 50 rows by default. I am allowed to scroll through the results table, and, apparently, it will be somehow lazy to load the rest of the results.

The display of the first 50 results does not seem to depend on the size of the table (at least for simple SELECT * FROM t), so it seems to me that SQL Developer concludes my statement SELECT swith a SELECT * FROM s WHERE rownum <= 50. If this is the case, as I think, it does not appear on the tab Explain Plan.

How does the SQL developer then lazily get the rest of the results if necessary? Does it use some kind of bias? Will it always repeat the request, but instead of taking only lines with rownum <= 50, will it do it for rownum <= 100, 150, etc.? It will certainly be ineffective. But, if this is not so, then he risks getting the wrong set of rows (since the arrangement of the row tables can be changed between them!).

I also assume that by default SQL Developer only actually retrieves the minimum amount of data from the server, i.e. it doesn’t actually extract everything, although it only shows some results. Is that the case?

Can anyone shed some light on this issue?

Answer 1

, SQL Developer SELECT s SELECT * FROM s WHERE rownum <= 50

, , , , .

, JDBC ODP, . SQL Developer SQL array fetch Size (Tools\Preferences\Database\Advanced) , . , 100 array fetch size ​​ 50, 2 + 1 .

, 50 . , SQL Developer 50 . , 100 , f9 or Ctrl+Enter, +1.

Answer 2

, .

SQL Developer run

 ALTER SESSION SET tracefile_identifier = mytest;
 alter session set events '10046 trace name context forever, level 1';

, .

. docs

, .

 select owner, object_name from dba_objects

, . 130. :

 =====================
 PARSING IN CURSOR #210335064 len=42 dep=0 uid=48 oct=3 lid=48 tim=20962225002 hv=3579237936 ad='b7ef8180' sqlid='114vazvapdpjh'
 select owner, object_name from dba_objects
 END OF STMT
 PARSE #210335064:c=15600,e=31487,p=0,cr=4,cu=0,mis=1,r=0,dep=0,og=1,plh=2354722397,tim=20962225000
 EXEC #210335064:c=0,e=46,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2354722397,tim=20962225198
 FETCH #210335064:c=0,e=1650,p=0,cr=39,cu=0,mis=0,r=50,dep=0,og=1,plh=2354722397,tim=20962226907

 *** 2016-10-24 17:36:49.734

, , 50 (r = 50). (# 210335064) :

 FETCH #210335064:c=0,e=515,p=0,cr=16,cu=0,mis=0,r=50,dep=0,og=1,plh=2354722397,tim=20970608847

 *** 2016-10-24 17:37:03.376

50 ...

 FETCH #210335064:c=0,e=304,p=0,cr=6,cu=0,mis=0,r=50,dep=0,og=1,plh=2354722397,tim=20976035474

 *** 2016-10-24 17:37:19.866

50 , ,

 CLOSE #210335064:c=0,e=300,dep=0,type=0,tim=20992663336

130 , 3 50 200 - .