Oracle SQL
  • LICENSE

Yearly Archives: 2021

Where does the commit or rollback happen in PL/SQL code?

Posted on September 12, 2021 by Sayan Malakshinov Posted in diagnostic event 10046, oracle, PL/SQL, trace, troubleshooting, undocumented 2,196 Page views 1 Comment

One of the easiest ways is to use diagnostic events:

alter session set events 'sql_trace {callstack: fname xctend} errorstack(1)';
Image
Image
oracle pl/sql troubleshooting undocumented oracle

:1 and SP2-0553: Illegal variable name “1”.

Posted on September 7, 2021 by Sayan Malakshinov Posted in SQL, SQL*Plus, SQL*PLus tips, SqlCL 2,291 Page views Leave a comment

You may know that some applications generate queries with bind variables’ names like :1 or :”1″, and neither SQL*Plus nor SQLCl support such variables:

SQLPlus:

SQL> var 1 number;
SP2-0553: Illegal variable name "1".

SQLCL:

SQL> var 1 number;
ILLEGAL Variable Name "1"

So we can’t run such queries as-is, but, obviously, we can wrap them into anonymous PL/SQL blocks and even create a special script for that:

Continue reading→
bind variable sql SQL*Plus SqlCL

ORA exceptions that can’t be caught by exception handler

Posted on August 12, 2021 by Sayan Malakshinov Posted in curious, Funny, oracle, PL/SQL, SQL, troubleshooting 2,379 Page views Leave a comment

I know 2 “special” exceptions that can’t be processed in exception handler:

  • “ORA-01013: user requested cancel of current operation”
  • “ORA-03113: end-of-file on communication channel”
  • and + “ORA-00028: your session has been killed” from Matthias Rogel

Tanel Poder described the first one (ORA-01013) in details here: https://tanelpoder.com/2010/02/17/how-to-cancel-a-query-running-in-another-session/ where Tanel shows that this error is based on SIGURG signal (kill -URG):

-- 1013 will not be caught:
declare
 e exception;
 pragma exception_init(e,-1013);
begin
  raise e;
exception when others then dbms_output.put_line('caught');
end;
/

declare
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
ORA-06512: at line 5
Continue reading→
exceptions ora-errors oracle oracle undocumented behaviour pl/sql troubleshooting undocumented oracle

Another interesting troubleshooting case

Posted on May 29, 2021 by Sayan Malakshinov Posted in oracle, PL/SQL, PL/SQL optimization, trace, troubleshooting 2,008 Page views Leave a comment

Got an interesting question today in RuOUG:

Some very simple PL/SQL procedures usually are completed within ~50ms, but sometimes sporadically longer than a second. For example, the easiest one from these procedures:

create or replace PROCEDURE XXXX (
  P_ORG_NUM IN number,
  p_result OUT varchar2, 
  p_seq OUT number 
) AS 
BEGIN
  p_seq := P_ORG_NUM; p_result:='';
END;

sql_trace shows that it was executed for 1.001sec and all the time was “ON CPU”:

Continue reading→
dtrace nanosleep native compilation pl/sql troubleshooting truss

SQL*Plus tips #9: Reading traces and incident files

Posted on May 28, 2021 by Sayan Malakshinov Posted in diagnostic event 10046, oracle, SQL*Plus, SQL*PLus tips, trace, troubleshooting 2,678 Page views Leave a comment

@tracefile_read_last_by_mask filemask [regexp] [ignore_regexp]
– finds last trace by filemask and filters rows by regexp and filters out rows by ignore_regexp:

@ tracefile_by_mask.sql [mask] – finds and shows last 10 trace files by mask

Continue reading→
SQL*Plus sqlplus traces

Oracle diagnostic events — Cheat sheet

Posted on May 20, 2021 by Sayan Malakshinov Posted in diagnostic event 10046, oracle, statistics, trace, troubleshooting, undocumented 9,438 Page views Leave a comment

Oracle diagnostic events is a great feature, but unfortunately poorly documented and nonintuitive, so it’s difficult to remember all events/actions/parameters and even read its internal documentation using oradebug. So I decided to compile its internal doc as a more convenient html-version (https://orasql.org/files/events/) and make a cheat sheet of some unknown or little-known use cases.

Example 1:

alter system set events 
   'kg_event[1476]
        {occurence: start_after 1, end_after 3}
            trace("stack is: %\n", shortstack())
            errorstack(2)
    ';
  1. kg_event[errno] – Kernel Generic event in library Generic for error number events, which instructs to trace ORA-errno errors;
  2. {occurence: start_after X, end_after Y} – is a filter, which instructs to skip X event checks and trace just Y times;
  3. trace(format, str1, str2, …, str15) – is a function from ACTIONS for printing into a trace file;
  4. shortstack() – is a function from ACTIONS , which returns a short call stack as a string;
  5. errorstack(level) – is a function from ACTIONS, which prints extended info (level: 0 – errorstack only, 1 – errorstack + call stack, 2 – as level 1 + processtate, 3 – as level 2 + context area). You can get more details with  PROCESSSTATE or SYSTEMSTATE. If you need just a call stack, you can use CALLSTACK(level) , with function arguments in case of level>1.

Example 2:

alter system set events 
    'trace[SQL_Compiler.* | SQL_Execution.*]
        [SQL: ...]
            {process: ospid = ...}
            {occurence:end_after 3}
                controlc_signal()';
Continue reading→
diagnostic events oracle oradebug

v$blog #funny #friday

Posted on April 25, 2021 by Sayan Malakshinov Posted in curious, Funny, oracle 1,832 Page views Leave a comment
select title, short_url 
from v$blog 
where pubDate>=systimestamp - interval '5' month;
with v$blog as (
    select  
        title
       ,to_timestamp_tz(pubDate,'DY, dd mon yyyy hh24:mi:ss TZR') pubDate
       ,short_url
       ,description
    from xmltable(
            '/rss/channel/item'
            passing
                xmltype(httpuritype('https://orasql.org/feed/').getclob())
            columns
                 title       varchar2(70)  path 'title'
                ,pubDate     varchar2(40)   path 'pubDate'
                ,url         varchar2(128)  path 'link'
                ,short_url   varchar2(50)   path 'guid'
                ,description varchar2(500)  path 'fn:substring(./description,1,500)'
    ) 
)
select title, short_url from v$blog where pubDate>=systimestamp - interval '5' month;
funny HTTP HTTPS httpuritype

Triaging Smart Scan

Posted on April 8, 2021 by Roger MacNicol Posted in adaptive serial direct path reads, cell_offload, oracle, SmartScan, trace 2,810 Page views Leave a comment

This document is my attempt to bring together the available options that can be used to determine the root cause of an issue in order to create a roadmap to help support engineers narrow down the cause of concern.

It is a living document and will be edited and amended as time goes by. Please do check back again in the future.

Warning: these parameters should only be used in conjunction with an Oracle Support Engineer and are not intended for DBAs to self-triage; also they should not be left set after triage without discussion with an Oracle Support Engineer.

Continue reading→
Cell Offloading direct path reads oracle Roger MacNicol SmartScan traces

pySyncOracleStandby – Simple sync service for Oracle manual standby

Posted on January 7, 2021 by Sayan Malakshinov Posted in oracle 1,663 Page views Leave a comment

I created this simple service a couple of years ago. It’s pretty simple, small and intuitive Python app, so you can easily modify it to suit your own needs and run on any platform: https://github.com/xtender/pySync

manual standby oracle Oracle SE oracle standard edition
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