Oracle SQL
  • LICENSE

Category Archives: SQL

CBO and Partial indexing

Posted on November 2, 2022 by Sayan Malakshinov Posted in bug, CBO, oracle, query optimizing, SQL, trace, troubleshooting Leave a comment

Oracle 12c introduced Partial indexing, which works well for simple partitioned tables with literals. However, it has several significant issues:

Continue reading→
cbo oracle partial indexes partial indexing query optimization troubleshooting undocumented oracle

Slow index access “COL=:N” where :N is NULL

Posted on October 31, 2022 by Sayan Malakshinov Posted in CBO, curious, Funny, oracle, query optimizing, SQL, troubleshooting Leave a comment

All Oracle specialists know that a predicate X=NULL can never be true and we should use “X is NULL” in such cases. The Oracle optimizer knows about that, so if we create a table like this:

Continue reading→
cbo oracle query optimization troubleshooting

: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 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 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

Format SQL or PL/SQL directly in Oracle database

Posted on December 23, 2020 by Sayan Malakshinov Posted in java, oracle, PL/SQL, SQL, SQL*Plus, SQL*PLus tips Leave a comment

Obviously we can format/beautify SQL or PL/SQL code using external tools, but sometimes it would be more convenient to format it directly in database, for example if we want to see few different sql_text’s from v$sqlarea. And thanks to Oracle SqlCL and Oracle SQL Developer, we can easily use oracle.dbtools.app.Format function from their Java library dbtools-common.jar, so if you use SqlCL or SQL Developer, you can use the same formatting options.

1. load appropriate java library into Oracle

You may have already installed SQLDeveloper or SqlCL on your database server, just check $ORACLE_HOME/sqldeveloper or $ORACLE_HOME/sqcl directories. If – not, you need to download appropriate SqlCL version that matches your java version in Oracle. For 12.2 – 19.8 you can download latest SqlCL 20.3. In fact we need just dbtools-common.jar from lib directory. I put it into $ORACLE_HOME/sqlcl/lib directory on the server and load it using loadjava:

Continue reading→
beautifier pl/sql formatter sql format SQL*Plus sqlformatter

Funny friday Oracle SQL quiz: query running N seconds

Posted on December 11, 2020 by Sayan Malakshinov Posted in curious, Funny, oracle, quiz, SQL Leave a comment

Write a pure SQL query with PL/SQL that stop after :N seconds, where :N is a bind variable.

My solution

with v(start_hsecs, delta, flag) as (
  select
    hsecs as start_hsecs, 0 as delta, 1 as flag
  from v$timer
  union all
  select
    v.start_hsecs,
    (t.hsecs-v.start_hsecs)/100 as delta,
    case when (t.hsecs-v.start_hsecs)/100 > :N /* seconds */ then v.flag*-1 else v.flag+1 end as flag
  from v, v$timer t
  where v.flag>0 and t.hsecs>=v.start_hsecs
)
select delta
from v
where 0>flag
/
--end

[collapse]
SQL> var N number
SQL> exec :N := 3 /* seconds */;

PL/SQL procedure successfully completed.

SQL> select...

     DELTA
----------
      3.01

1 row selected.

Elapsed: 00:00:03.01

Another my solution using sys.standard.current_timestamp, so some internal pl/sql…:

select count(*) from dual 
connect by sys.standard.current_timestamp - current_timestamp <= interval'3'second;
funny quiz sql

Simple function returning Parallel slave info

Posted on September 15, 2020 by Sayan Malakshinov Posted in oracle, parallel, query optimizing, SQL, statistics, troubleshooting Leave a comment

You can add also any information from v$rtsm_sql_plan_monitor if needed

create or replace function px_session_info return varchar2 parallel_enable as
   vSID int;
   res varchar2(30);
begin
   vSID:=userenv('sid');
   select 
           to_char(s.server_group,'fm000')
    ||'-'||to_char(s.server_set,'fm0000')
    ||'-'||to_char(s.server#,'fm0000')
    ||'('||s.sid||','||s.degree||'/'||s.req_degree||')'
    into res
   from v$px_session s 
   where s.sid=vSID;
   return res;
exception when no_data_found then
   return 'no_parallel';
end;
/

Simple example:

select--+ parallel
  px_session_info, count(*)
from sys.obj$
group by px_session_info
/
PX_SESSION_INFO           COUNT(*)
------------------------  --------
001-0002-0001(630,2/2)     38298
001-0002-0002(743,2/2)     34706
oracle parallel presentations troubleshooting

Android Oracle Client 2.0

Posted on June 28, 2020 by Sayan Malakshinov Posted in Android, java, oracle, SQL Leave a comment

I’ve just released new version of my Simple Android Oracle Client.

New features:

  • Supported Oracle versions: 11.2, 12.1, 12.2, 18, 19, 20.
  • SQL Templates: now you can save and load own script templates
  • Server output (dbms_output)
  • Export results as JSON, CSV and HTML files (long tap on results)
  • Copy results to the Clipboard as JSON or CSV

I use it just for basic troubleshooting and small fixes, but, please, let me know if you need anything else.
Screenshots:

android android oracle client java oracle client

PL/SQL functions and statement level consistency

Posted on December 30, 2019 by Sayan Malakshinov Posted in deterministic functions, oracle, PL/SQL, PL/SQL optimization, query optimizing, SQL Leave a comment

You may know that whenever you call PL/SQL functions from within SQL query, each query in the function is consistent to the SCN of its start and not to the SCN of parent query.

Simple example:

create table test as 
  select level a, level b from dual connect by level<=10;

create or replace function f1(a int) return int as
  res int;
begin
  select b into res 
  from test t 
  where t.a=f1.a;
  dbms_lock.sleep(1);
  return res;
end;
/

As you can see we created a simple PL/SQL function that returns the result of the query select b from test where a=:input_var

But lets check what does it return if another session changes data in the table:

-- session 2:
begin
    for i in 1..30 loop
      update test set b=b+1;
      commit;
      dbms_lock.sleep(1);
    end loop;
end;
/
-- session 1:
SQL> select t.*, f1(a) func from test t;

         A          B       FUNC
---------- ---------- ----------
         1          1          1
         2          2          3
         3          3          5
         4          4          7
         5          5          9
         6          6         11
         7          7         13
         8          8         15
         9          9         17
        10         10         19

10 rows selected.

As you can see we got inconsistent results in the column FUNC, but we can easily fix it using OPERATORs:

CREATE OPERATOR f1_op
   BINDING (INT) 
   RETURN INT 
   USING F1;

Lets revert changes back and check our query with new operator now:

--session 1:
SQL> update test set b=a;

10 rows updated.

SQL> commit;

Commit complete.

-- session 2:
begin
    for i in 1..30 loop
      update test set b=b+1;
      commit;
      dbms_lock.sleep(1);
    end loop;
end;
/

-- session 1:
SQL> select t.*, f1(a) func, f1_op(a) op from test t;

         A          B       FUNC         OP
---------- ---------- ---------- ----------
         1          2          2          2
         2          3          5          3
         3          4          8          4
         4          5         11          5
         5          6         14          6
         6          7         17          7
         7          8         20          8
         8          9         23          9
         9         10         26         10
        10         11         29         11

10 rows selected.

As you can see, all values in the column OP are equal to the values of B, while, in turn, function F1 returns inconsistent values.

operators oracle pl/sql functions pl/sql optimization

Just short note for myself: OJPPD limitations

Posted on December 2, 2019 by Sayan Malakshinov Posted in CBO, collect, oracle, SQL, troubleshooting Leave a comment

As of Oracle 19c OJPPD doesn’t support connect-by and TABLE():

OJPPD: OJPPD bypassed: query block contains START WITH/CONNECT BY.
OJPPD: OJPPD bypassed: View contains TABLE expression.
cbo JPPD ojppd
photo Sayan Malakshinov

Oracle ACE Pro Oracle ACE Pro

DEVVYOracle Database Developer Choice Award winner

Oracle performance tuning expert

UK / Cambridge

LinkedIn   Twitter
sayan@orasql.org

Recent Posts

  • CBO and Partial indexing
  • Slow index access “COL=:N” where :N is NULL
  • Where does the commit or rollback happen in PL/SQL code?
  • :1 and SP2-0553: Illegal variable name “1”.
  • ORA exceptions that can’t be caught by exception handler

Recent Comments

  • Oracle SGA 값을 증가 시킬 때 발생 장애 원인 – DBA의 정석 on Example of controlling “direct path reads” decision through SQL profile hints (index_stats/table_stats)
  • Oracle SQL | Oracle diagnostic events — Cheat sheet on Where does the commit or rollback happen in PL/SQL code?
  • Functions & Subqueries | Oracle Scratchpad on Deterministic function vs scalar subquery caching. Part 3
  • Materialized views state turns into compilation_error after refresh - kranar.top - Answering users questions... on Friday prank: select from join join join
  • Exadata Catalogue | Oracle Scratchpad on When bloggers get it wrong – part 1
  • Exadata Catalogue | Oracle Scratchpad on Serial Scans failing to offload
  • lateral join – decorrelation gone wrong – svenweller on Lateral view decorrelation(VW_DCL) causes wrong results with rownum
  • 255 column catalogue | Oracle Scratchpad on Intra-block row chaining optimization in 12.2
  • 255 column catalogue | Oracle Scratchpad on row pieces, 255 columns, intra-block row chaining in details
  • opt_estimate catalogue | Oracle Scratchpad on Correct syntax for the table_stats hint

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

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org
  • 1
  • 2
  • 3
  • Next
©Sayan Malakshinov. Oracle SQL