Skip to Main Content
Forums

SQL Developer for VS Code

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Runtime statistics

Monika LewandowskaFeb 27 2024 — edited Feb 28 2024

I found a problem with calculating runtime statistics in VS Code. When table has less then 250 rows (arraysize, so query is fully fetched)- > the statistics are populated. If more or equal than 250 rows (not fully fetched) - they are not populated.

In SQL dev - statistics are populated if you run display_cursor twice.

Scripts and steps: HR schema, oracle xe 21c

If there is less than 250 rows

drop table emp;

create table emp as
with data as (select level lvl from dual connect by level <= 3)
select  EMPLOYEE_ID,
    FIRST_NAME,
    LAST_NAME,
    EMAIL,
    PHONE_NUMBER,
    HIRE_DATE,
    JOB_ID,
    SALARY,
    COMMISSION_PCT,
    MANAGER_ID,
    DEPARTMENT_ID
 from employees, data
 where rownum < 250;


select /*+ gather_plan_statistics */ /* MM524 */
*
from  emp;

SELECT   t.*
FROM  v$sql s,  
     TABLE(DBMS_XPLAN.DISPLAY_CURSOR(
                sql_id => s.sql_id,
                cursor_child_no => s.Child_number,
                format => 'allstats last' )) t
where sql_text like '%MM524%';

if 250 or more rows:

drop table emp;


create table emp as
with data as (select level lvl from dual connect by level <= 3)
select  EMPLOYEE_ID,
    FIRST_NAME,
    LAST_NAME,
    EMAIL,
    PHONE_NUMBER,
    HIRE_DATE,
    JOB_ID,
    SALARY,
    COMMISSION_PCT,
    MANAGER_ID,
    DEPARTMENT_ID
 from employees, data
 where rownum < 251;


select /*+ gather_plan_statistics */ /* MM525*/
*
from  emp;


SELECT   t.*
FROM  v$sql s,  
     TABLE(DBMS_XPLAN.DISPLAY_CURSOR(
                sql_id => s.sql_id,
                cursor_child_no => s.Child_number,
                format => 'allstats last' )) t
where sql_text like '%MM525%';

With the help of Stefan Koehler, we checked trace 10051 and 10046, and it seems that SQL DEV retrieves the first 200 rows but closes the cursor at the end. VS Code retrieves 250 rows in sets of 50, but it does not close the cursor. Maybe closing the cursor in SQL DEV may trigger statistics calculation.

I cannot add trace file or zip file so I copied some parts of it:

script to run from sql dev and vs code

ALTER SESSION SET TRACEFILE_IDENTIFIER = 'SQL_DEV_10051_46_7';

alter session set events '10051 trace name context forever, level  1';
alter session set events '10046 trace name context forever, level  8';

select /*+ gather_plan_statistics */ /* MM607 */
*
from  emp;
alter session set events '10046 trace name context off, level 0';
alter session set events '10051 trace name context off, level 0';

TRACE - SQL DEV session

PARSING IN CURSOR #3111430592544 len=60 dep=0 uid=109 oct=3 lid=109 tim=309217507355 hv=3759942071 ad='7ffc2c909f08' sqlid='ag854gvh1sadr'
select /*+ gather_plan_statistics */ /* MM607 */
*
from  emp
END OF STMT
PARSE #3111430592544:c=0,e=11039,p=0,cr=197,cu=0,mis=1,r=0,dep=0,og=1,plh=3956160932,tim=309217507355
EXEC #3111430592544:c=0,e=17,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3956160932,tim=309217507431
WAIT #3111430592544: nam='SQL*Net message to client' ela= 6 driver id=1413697536 #bytes=1 p3=0 obj#=40 tim=309217507475
WAIT #3111430592544: nam='SQL*Net more data to client' ela= 94 driver id=1413697536 #bytes=8101 p3=0 obj#=40 tim=309217507696
FETCH #3111430592544:c=0,e=234,p=0,cr=4,cu=0,mis=0,r=200,dep=0,og=1,plh=3956160932,tim=309217507741

*** 2024-02-27T11:18:15.399313+01:00 (XEPDB1(3))
WAIT #3111430592544: nam='SQL*Net message from client' ela= 1475173 driver id=1413697536 #bytes=1 p3=0 obj#=40 tim=309218982946
OPI CALL: type=105 argc= 2 cursor=  0 name=Cursor close all
CLOSE #3111435572840:c=0,e=11,dep=0,type=0,tim=309218983136
OPI CALL: type=94 argc=38 cursor=  0 name=V8 Bundled Exec
=====================
PARSING IN CURSOR #3111435572840 len=64 dep=0 uid=109 oct=42 lid=109 tim=309218983216 hv=1509974352 ad='0' sqlid='0up5ynpd00sah'
alter session set events '10046 trace name context off, level 0'
END OF STMT
PARSE #3111435572840:c=0,e=15,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=309218983216
EXEC #3111435572840:c=0,e=270,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=309218983538

*** 2024-02-27T11:18:17.610343+01:00 (XEPDB1(3))
OPI CALL: type=105 argc= 2 cursor=  0 name=Cursor close all
OPI CALL: type=94 argc=38 cursor=  0 name=V8 Bundled Exec

trace for VS Code session

PARSING IN CURSOR #3111374579104 len=60 dep=0 uid=109 oct=3 lid=109 tim=309462166275 hv=3054442173 ad='7ffc35fe7540' sqlid='0wrndquv0y5px'
select /*+ gather_plan_statistics */ /* MM708 */
*
from  emp
END OF STMT
PARSE #3111374579104:c=0,e=14988,p=0,cr=199,cu=0,mis=1,r=0,dep=0,og=1,plh=3956160932,tim=309462166275
EXEC #3111374579104:c=0,e=11,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3956160932,tim=309462166340
WAIT #3111374579104: nam='SQL*Net message to client' ela= 5 driver id=1413697536 #bytes=1 p3=0 obj#=40 tim=309462166376
FETCH #3111374579104:c=0,e=53,p=0,cr=2,cu=0,mis=0,r=50,dep=0,og=1,plh=3956160932,tim=309462166458
WAIT #3111374579104: nam='SQL*Net message from client' ela= 1425 driver id=1413697536 #bytes=1 p3=0 obj#=40 tim=309462167921
OPI CALL: type= 5 argc= 2 cursor= 28 name=FETCH
WAIT #3111374579104: nam='SQL*Net message to client' ela= 3 driver id=1413697536 #bytes=1 p3=0 obj#=40 tim=309462168091
FETCH #3111374579104:c=0,e=107,p=0,cr=2,cu=0,mis=0,r=50,dep=0,og=1,plh=3956160932,tim=309462168171
WAIT #3111374579104: nam='SQL*Net message from client' ela= 1646 driver id=1413697536 #bytes=1 p3=0 obj#=40 tim=309462169864
OPI CALL: type= 5 argc= 2 cursor= 28 name=FETCH
WAIT #3111374579104: nam='SQL*Net message to client' ela= 5 driver id=1413697536 #bytes=1 p3=0 obj#=40 tim=309462170165
FETCH #3111374579104:c=0,e=83,p=0,cr=1,cu=0,mis=0,r=50,dep=0,og=1,plh=3956160932,tim=309462170211
*** 2024-02-27T11:22:19.389771+01:00 (XEPDB1(3))
WAIT #3111374579104: nam='SQL*Net message from client' ela= 803099 driver id=1413697536 #bytes=1 p3=0 obj#=40 tim=309462973351
OPI CALL: type= 5 argc= 2 cursor= 28 name=FETCH
WAIT #3111374579104: nam='SQL*Net message to client' ela= 3 driver id=1413697536 #bytes=1 p3=0 obj#=40 tim=309462973612
FETCH #3111374579104:c=0,e=100,p=0,cr=2,cu=0,mis=0,r=50,dep=0,og=1,plh=3956160932,tim=309462973665
WAIT #3111374579104: nam='SQL*Net message from client' ela= 1047 driver id=1413697536 #bytes=1 p3=0 obj#=40 tim=309462974769
OPI CALL: type= 5 argc= 2 cursor= 28 name=FETCH
WAIT #3111374579104: nam='SQL*Net message to client' ela= 3 driver id=1413697536 #bytes=1 p3=0 obj#=40 tim=309462974974
FETCH #3111374579104:c=0,e=59,p=0,cr=1,cu=0,mis=0,r=50,dep=0,og=1,plh=3956160932,tim=309462975009
WAIT #3111374579104: nam='SQL*Net message from client' ela= 675239 driver id=1413697536 #bytes=1 p3=0 obj#=40 tim=309463650282
*** CLIENT ID:(monik) 2024-02-27T11:22:20.067455+01:00

OPI CALL: type=94 argc=38 cursor=  0 name=V8 Bundled Exec
PARSE #3111223101648:c=0,e=7,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=309463650447
EXEC #3111223101648:c=0,e=264,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=309463650728
OPI CALL: type=105 argc= 2 cursor=  0 name=Cursor close all
This post has been answered by Monika Lewandowska on Apr 15 2025
Jump to Answer
Comments
Post Details
Added on Feb 27 2024
14 comments
423 views
1 mention