Execution plans on LiveSQL

February 20, 2018

1–2 minutes

To protect the integrity of people’s data, and isolate sessions on LiveSQL, we lock down the environment. Clearly if you are doing some testing with sensitive data, you don’t want an anonymous member of the user community mining V$SQL to see what commands you have been running. Conversely, we want to allow people to perform most of the tasks that would do on a standard database installation without having to install or configure anything. That’s the great thing about LiveSQL.

So for that reason, you don’t get access to all of the features of DBMS_XPLAN. But since we do provide access to a limited set of V$ view access, you can get execution plan details by going back to first principles. Here’s some scripts you can use


--
-- First run your query
--
select /*+ gather_plan_statistics */ .... (my_query)

--
--  Then locate the SQL_ID/CHILD_NUMBER for your just executed query
--
select * from v$sqlstats where sql_text like '...';


--
-- Plug them into the queries below.  
--
-- The first one gets the execution plan, the second one get the runtime statistics
--
with plan_table as 
(
select * from v$sql_plan_statistics_all
where sql_id = "my sql id"
and child_number = "my child"
)
select id "Id",
  rpad(
  lpad(' ',2*level) || operation || ' ' || options , 40 ) "Operation",
  object_name "Name",
  cardinality "Rows",
  bytes "Bytes", 
  cost "Cost",
  rtrim(to_char(trunc(time/3600),'fm00')||':'||
  to_char(mod(trunc(time/60),1),'fm00')||':'||
  to_char(trunc(mod(time,60)),'fm00'),':')     "Time"
from plan_table
connect by prior id = parent_id 
start with id = 0
union all
select null, null,null, null,null, null,null from dual
union all
select null, lpad(id,4) ||'-filter '||FILTER_PREDICATES,null, null,null, null,null 
from plan_table where FILTER_PREDICATES is not null
union all
select null, lpad(id,4) ||'-access '||ACCESS_PREDICATES,null, null,null, null,null 
from plan_table where ACCESS_PREDICATES is not null
order by 1 nulls last, 2 nulls first;



with plan_table as 
(
select * from v$sql_plan_statistics_all
where sql_id = 'd09nv17gy4j3z'
and child_number = 0
)
select id "Id",
  rpad(
  lpad(' ',2*level) || operation || ' ' || options , 40 ) "Operation",
  object_name "Name",
  last_starts "Starts",
  cardinality "E-Rows", 
  LAST_OUTPUT_ROWS "A-Rows",
  rtrim(to_char(trunc(time/3600),'fm00')||':'||
  to_char(mod(trunc(time/60),1),'fm00')||':'||
  to_char(trunc(mod(time,60)),'fm00'),':')     "Time",
  LAST_CR_BUFFER_GETS+LAST_CU_BUFFER_GETS "Buffers"
from plan_table p
connect by prior id = parent_id 
start with id = 0
union all
select null, null,null, null,null, null,null,null from dual
union all
select null, lpad(id,4) ||'-filter '||FILTER_PREDICATES,null, null,null, null,null,null 
from plan_table where FILTER_PREDICATES is not null
union all
select null, lpad(id,4) ||'-access '||ACCESS_PREDICATES,null, null,null, null,null,null 
from plan_table where ACCESS_PREDICATES is not null
order by 1 nulls last, 2 nulls first;

6 responses to “Execution plans on LiveSQL”

stewashton

February 20, 2018 at 3:49 pm

Where can I suggest that Livesql provide a wrapper function allowing DBMS_XPLAN for statements parsed by the current user?

Best regards, Stew

Reply
1. connormcdonald
  
  February 20, 2018 at 9:59 pm
  
  There’s a feedback button on LiveSQL itself
  
  Reply
Iudith Mentzel

March 9, 2018 at 5:47 am

Hello All,

I have just read the work-around …

However, I don’t know why should we reinvent the wheel 😦

The dbms_xplan WAS WORKING in LiveSQL before migration to 18c , and I have already posted
a request to have it restored … unfortunately not answered yet 😦 😦

It is not exactly clear for me what is the issue … probably the fact that we can pass in ANY sql_id,
not just our own …

I have discovered already for a long time that all the V$ views are redefined and restricted to return
only data for the current user.

I think it is not a problem to create a wrapper package, as Stew suggests,
as it is very hard to mimic the complete functionality of DBMS_XPLAN, with all the FORMAT variants
a.s.o.

As by the documented security model of DBMS_XPLAN:
“This package runs with the privileges of the calling user, not the package owner (SYS).”.

I suppose that this is the same as having AUTHID CURRENT_USER,
and, if so, then a wrapper package isn’t even needed, as long as the V$ views themselves
are redefined for each user.

Thanks a lot & Best Regards,
Iudith Mentzel

Reply
1. connormcdonald
  
  March 10, 2018 at 10:44 am
  
  My understanding of the issue is that DBMS_XPLAN was altered in 18c so that its internal code no longer references (for example) “V$SQL” but the fully qualified “SYS.V$SQL”, thus defeating the local definitions we have for those views in LiveSQL (and similarly stopping a simple AUTHID CURRENT_USER)
  
  Complicating matters is that DBMS_XPLAN calls a number of other internal packages. That’s what prompted me to do this post – as a stopgap.
  
  Reply
Iudith Mentzel

March 10, 2018 at 11:20 pm

Hello All,

If so, then I think that they should definitely create a simple wrapper package around DBMS_XPLAN,
with the same subprograms as the original one, but so that each subprogram that includes a SQL_ID
argument will contain an additional check against V$SQL to make sure that it belongs to (was parsed by)
the current user, before calling the original subprogram “as is”.

If you ask me, even allowing direct access to the original package would not do any harm,
since we are only reading the V$ data …

But, let’s go with them …

Anyway, having a wrapper package is A MUST, to allow access to the whole functionality
of DBMS_XPLAN, without any further changes required once this wrapper is in place.

@Connor, I strongly hope that you could help us (the community) by being our strong voice 🙂

Thanks a lot & Best Regards,
Iudith Mentzel

Reply
Как форсировать применение DYNAMIC SAMPLING на примерах LiveSQL версии 19c | Oracle mechanics

March 11, 2019 at 5:30 am

[…] , для решения которой можно использовать метод, описанный Developer Advocate Connor McDonald.Execution plans on LiveSQL: […]

Reply