Oracle SQL
  • LICENSE

Monthly Archives: July 2019

Reading and analyzing trace file contents using just SQL

Posted on July 11, 2019 by Sayan Malakshinov Posted in 12.2, oracle, trace, troubleshooting 1,688 Page views Leave a comment

Simple example: tracefiles for the last 5 days:

select fc.* 
from v$diag_trace_file f
     join v$diag_trace_file_contents fc
          on f.adr_home=fc.adr_home
          and f.trace_filename=fc.trace_filename
where f.modify_time >= systimestamp - interval'5' minute
  and fc.timestamp  >= systimestamp - interval'5' minute
  and fc.component_name = 'SQL_Trace'
  --and fc.section_name like 'kests%'
  ;
--or:
select tr.*
  from v$diag_app_trace_file tf,
       v$diag_sql_trace_records tr
 where tf.sql_trace = 'Y'
   and tf.modify_time > systimestamp - interval'5'minute
   and tr.adr_home = tf.adr_home
   and tr.trace_filename = tf.trace_filename
   and tr.timestamp > systimestamp - interval'5'minute;
10046 10053 diag traces troubleshooting

Laterals: is (+) documented for laterals?

Posted on July 9, 2019 by Sayan Malakshinov Posted in bug, CBO, documentation, oracle 1,553 Page views Leave a comment

I know this syntax for a long time, since when lateral() was not documented yet, but recently I found a bug: the following query successfully returns 1 row:

with a as (select level a from dual connect by level<10)
    ,b as (select 0 b from dual)
    ,c as (select 0 c from dual)
select
  *
from a,
     lateral(select * from b where a.a=b.b)(+) bb
     --left outer join c on c.c=bb.b
where a=1;

         A          B
---------- ----------
         1

But doesn’t if we uncomment “left join”:

with a as (select level a from dual connect by level<10)
    ,b as (select 0 b from dual)
    ,c as (select 0 c from dual)
select
  *
from a,
     lateral(select * from b where a.a=b.b)(+) bb
     left outer join c on c.c=bb.b
where a=1;

no rows selected

And outer apply works fine:

with a as (select level a from dual connect by level<10)
    ,b as (select 0 b from dual)
    ,c as (select 0 c from dual)
select
  *
from a
     outer apply (select * from b where a.a=b.b) bb
     left outer join c on c.c=bb.b
where a=1;

         A          B          C
---------- ---------- ----------
         1

oracle oracle undocumented behaviour sql undocumented oracle
photo Sayan Malakshinov

Oracle ACE Pro Oracle ACE Pro Alumni

DEVVYOracle Database Developer Choice Award winner

Oracle performance tuning expert

UK / Cambridge

LinkedIn   Twitter
sayan@orasql.org

Recent Posts

  • Oracle Telegram Bot
  • Partition Pruning and Global Indexes
  • Interval Search: Part 4. Dynamic Range Segmentation – interval quantization
  • Interval Search Series: Simplified, Advanced, and Custom Solutions
  • Interval Search: Part 3. Dynamic Range Segmentation – Custom Domain Index

Popular posts

Recent Comments

  • Oracle SQL | Interval Search: Part 4. Dynamic Range Segmentation – interval quantization on Interval Search: Part 3. Dynamic Range Segmentation – Custom Domain Index
  • Oracle SQL | Interval Search: Part 4. Dynamic Range Segmentation – interval quantization on Interval Search: Part 2. Dynamic Range Segmentation – Simplified
  • Oracle SQL | Interval Search: Part 4. Dynamic Range Segmentation – interval quantization on Interval Search: Optimizing Date Range Queries – Part 1
  • Oracle SQL | Interval Search Series: Simplified, Advanced, and Custom Solutions on Interval Search: Part 2. Dynamic Range Segmentation – Simplified
  • Oracle SQL | Interval Search: Part 2. Dynamic Range Segmentation – Simplified on Interval Search: Part 3. Dynamic Range Segmentation – Custom Domain Index

Blogroll

  • Alex Fatkulin
  • Alexander Anokhin
  • Andrey Nikolaev
  • Charles Hooper
  • Christian Antognini
  • Coskan Gundogar
  • David Fitzjarrell
  • Igor Usoltsev
  • Jonathan Lewis
  • Karl Arao
  • Mark Bobak
  • Martin Bach
  • Martin Berger
  • Neil Chandler
  • Randolf Geist
  • Richard Foote
  • Riyaj Shamsudeen
  • Tanel Poder
  • Timur Akhmadeev
  • Valentin Nikotin
©Sayan Malakshinov. Oracle SQL