18c–If you can’t wait

You’ve seen the tweet !!

image

but perhaps the accompanying blog post has tempered your enthusiasm Sad smile

image

You might be thinking:

“I’m not on Exadata – how can I play with 18c?”

Well, there is still a way to get ahead of the game and skill up on 18c.  We’ve upgraded livesql.oracle.com to 18c, and we’ve pre-loaded some demo scripts for you as well.

image

So get cracking! Oracle Database 18c builds upon the foundation set by 12.2.

Enjoy!

 

Execution plans on LiveSQL

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;