Oracle SQL | OraSQL: Oracle Performance Tuning and Troubleshooting

Friday prank: select from join join join

Posted on February 21, 2014 by Sayan Malakshinov Posted in curious, oracle 2,820 Page views 3 Comments

Valid funny queries

select the join from join join join using(the,some) 
/
select some join from left join join right using(some,the) 
/
select 1 join from join join join join join using(the) on 1=1
/
select the some from join
where the=some( the(select some from join) 
               ,the(select the  from join)
               ) 
/ 

tables

Little addition

update two tables set join=2;
select join from two tables;
select first, second, random from two tables join three tables on 1=1;

Another one

select first
     , case when the=some(some,some) then join end true
from two tables join three tables using(random);

….
and…

select random columns from two tables join four tables on the=some(some,some);

Inlist iterator again

Posted on February 21, 2014 by Sayan Malakshinov Posted in curious, oracle, query optimizing 3,089 Page views 0 Comments

Several months ago I wrote about avoiding inlist iterator, but this post about how to force inlist iterator in queries like that:

select *
from xt_test1 t1
    ,xt_test2 t2
where
     t1.a = :a 
 and t2.id in (a,b)

i.e. when we need to get rows from big table using index by list of values from another table.
A couple of workarounds

Oracle 12c: scalar subqueries

Posted on February 11, 2014 by Sayan Malakshinov Posted in 12c, CBO, oracle, undocumented 2,818 Page views 0 Comments

We already know that the CBO transformation engine in 12c can unnest scalar subqueries from select-list.
So it’s not very surprising, that CBO is now able to add scalar subqueries costs to total query cost (even if “_optimizer_unnest_scalar_sq” = false):

Before 12.1

12.1

But it’s interesting that correlated subquery can reference now to a column from parent tables more
than one level above:

Before 12.1

12.1

SYS_OP_MAP_NONNULL is in the documentation now

Posted on February 10, 2014 by Sayan Malakshinov Posted in 12c, documentation, oracle, undocumented 4,277 Page views 0 Comments

Interesting, that SYS_OP_MAP_NONNULL appeared in the Oracle 12c documentation: Choosing Indexes for Materialized Views

Lazy tip: By the way, with length limitations, we can also use documented dump function:

SQL> with
  2    t(a,b) as (
  3               select *
  4               from table(ku$_vcnt(null,'FF','A'))
  5                   ,table(ku$_vcnt(null,'FF','B'))
  6              )
  7  select
  8      a,b
  9     ,case when sys_op_map_nonnull(a) = sys_op_map_nonnull(b) then '=' else '!=' end comp1
 10     ,case when dump(a,1017)          = dump(b,1017)          then '=' else '!=' end comp2
 11     ,sys_op_map_nonnull(a) s_o_m_n_a
 12     ,sys_op_map_nonnull(b) s_o_m_n_b
 13     ,dump(a,  17) dump_a
 14     ,dump(b,  17) dump_b -- it is preferably sometimes to use 1017 - for charset showing
 15  from t;
 
A     B     COMP1 COMP2 S_O_M_N_A  S_O_M_N_B  DUMP_A                DUMP_B
----- ----- ----- ----- ---------- ---------- --------------------- ---------------------
            =     =     FF         FF         NULL                  NULL
      FF    !=    !=    FF         464600     NULL                  Typ=1 Len=2: F,F
      B     !=    !=    FF         4200       NULL                  Typ=1 Len=1: B
FF          !=    !=    464600     FF         Typ=1 Len=2: F,F      NULL
FF    FF    =     =     464600     464600     Typ=1 Len=2: F,F      Typ=1 Len=2: F,F
FF    B     !=    !=    464600     4200       Typ=1 Len=2: F,F      Typ=1 Len=1: B
A           !=    !=    4100       FF         Typ=1 Len=1: A        NULL
A     FF    !=    !=    4100       464600     Typ=1 Len=1: A        Typ=1 Len=2: F,F
A     B     !=    !=    4100       4200       Typ=1 Len=1: A        Typ=1 Len=1: B
 
9 rows selected.

Little example of index creation on extended varchars

Posted on November 15, 2013 by Sayan Malakshinov Posted in 12c, oracle, undocumented 2,165 Page views 0 Comments

-- it's just for fun:
SQL> alter system set "_scalar_type_lob_storage_threshold"=32000;
 
System altered.
SQL> create table t_varchar32000(v varchar2(32000 byte));
 
Table created.
 
SQL> insert into t_varchar32000
  2  select rpad(rownum,31999) || `x' str from dual connect by level<=1000;
 
1000 rows created.
 
SQL> commit;
 
Commit complete.
 
SQL> create index ix_t_varchar32000 on t_varchar32000(v) tablespace users;
create index ix_t_varchar32000 on t_varchar32000(v) tablespace users
                                  *
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded
 
 
SQL> create index ix_t_varchar32000 on t_varchar32000(v) tablespace ts_32k;
create index ix_t_varchar32000 on t_varchar32000(v) tablespace ts_32k
                                  *
ERROR at line 1:
ORA-01450: maximum key length (26510) exceeded
 
-- tablespace for big varchars:
SQL> alter system set DB_32K_CACHE_SIZE=100M;
 
System altered.
 
SQL> CREATE TABLESPACE TS_32K DATAFILE '/u01/app/oracle/oradata/xtsql/pdb1/ts_32k_1.dbf' SIZE 150M
  2   EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
  3   BLOCKSIZE 32K;
 
Tablespace created.
 
SQL> create table t_varchar16000(v varchar2(16000 byte)) tablespace ts_32k;
 
Table created.
 
SQL> insert into t_varchar16000
  2  select rpad(rownum,15999,'x' ) || 'y' from dual connect by level<=1000;
 
1000 rows created.
 
SQL> create index ix_t_varchar16000 on t_varchar16000(v) tablespace ts_32k;
 
Index created.

Statistics

[sourcecode language=”sql”]
SQL> begin
2 dbms_stats.gather_table_stats(
3 ownname => user
4 ,tabname => ‘T_VARCHAR16000’
5 ,method_opt => ‘for all columns size auto’
6 ,cascade => true
7 );
8 end;
9 /

PL/SQL procedure successfully completed.

SQL> @stats/tab t_varchar16000

OWNER TABLE_NAME PARTITION_NAME # ST_LOCK STALE_STA GLOBAL_ST USER_STAT NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_ROW_LEN AVG_SPACE LAST_ANALYZED
————— —————————— ——————– —- ——- ——— ——— ——— ———- ———- ———— ———– ———- —————
XTENDER T_VARCHAR16000 NO YES NO 1000 3016 0 16001 0 14-NOV-13

OWNER INDEX_NAME NUM_ROWS DISTINCT_KEYS BLEVEL LEAF_BLOCKS CL_FACTOR LAST_ANALYZED GLOBAL_ST USER_STAT
————— —————————— ———- ————- ———- ———– ———- ————— ——— ———
XTENDER IX_T_VARCHAR16000 1000 1000 1 1000 1000 14-NOV-13 YES NO

———————————————————————————————————————————————————————————————
| column_name | num_distinct| low_value | high_value | num_nulls | num_bucket| last_analyzed | sample_size| globa| user_| avg_c| histogram |
———————————————————————————————————————————————————————————————
| V | 1000 | 1000xxxxxxxxxxxxxx| 9xxxxxxxxxxxxxxxxx| 0 | 1 | 2013-11-14 21:11 | 1000 | YES | NO | 16001| NONE |
———————————————————————————————————————————————————————————————

[/sourcecode]

[collapse]

Just another SQL beautifier

Posted on October 30, 2013 by Sayan Malakshinov Posted in oracle, SQL*Plus, SQL*PLus tips 3,257 Page views 10 Comments

Previously i wrote beautifier in perl, but it was not so good, so i decided to write it in java using popular BlancoSQLFormatter library.
So you can download it now: https://orasql.org/scripts/SQLBeautifier.jar
Usage:

1	`java -jar SQLBeautifier.jar your_file.sql`

1	`echo` `select` `*` `from` `dual \| java -jar SQLBeautifier.jar`

You certainly can conveniently use it within sql*plus with script like that:

set timing off head off termout off
col qtext format a150
prompt ################################  Original query text:  ################################################;
#spool &_SPOOLS/to_format.sql
spool to_format.sql
select
    coalesce(
        (select sql_fulltext from v$sqlarea a where a.sql_id='&1')
    ,   (select sql_text from dba_hist_sqltext a where a.sql_id='&1' and dbid=(select dbid from v$database))
    ) qtext
from dual
;
spool off
 
prompt ################################  Formatted query text #################################################;
#host perl inc/sql_format_standalone.pl &_SPOOLS/to_format.sql
host java -jar SQLBeautifier.jar to_format.sql
prompt ################################  Formatted query text End #############################################;
set termout on head on

Example:

Links:

Patch for “Bug 16516751 : Suboptimal execution plan for query with join and in-list using composite index” is available now

Posted on October 7, 2013 by Sayan Malakshinov Posted in 12c, bug, CBO, oracle, query optimizing 2,585 Page views 0 Comments

Bug about which i wrote previously is fixed now in 12.2, and patch 16516751 is available now for 11.2.0.3 Solaris64.
Changes:
1. CBO can consider filters in such cases now
2. Hint NUM_INDEX_KEYS fixed and works fine

UPD: Very interesting solution by Igor Usoltsev(in russian):
Ignored hint USE_CONCAT(OR_PREDICATES(N)) allows to avoid inlist iterator.
Example:

select--+ USE_CONCAT(OR_PREDICATES(32767))
 * from xt1,xt2
where
     xt1.b=10
 and xt1.a=xt2.a
 and xt2.b in (1,2)
/
 
Plan hash value: 2884586137          -- good plan:
  
----------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |       |       |   401 (100)|          |
|   1 |  NESTED LOOPS                 |        |       |       |            |          |
|   2 |   NESTED LOOPS                |        |   100 | 36900 |   401   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| XT1    |   100 | 31000 |   101   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | IX_XT1 |   100 |       |     1   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN           | IX_XT2 |     1 |       |     2   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID | XT2    |     1 |    59 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
  
Predicate Information (identified by operation id):
---------------------------------------------------
  
   4 - access("XT1"."B"=10)
   5 - access("XT1"."A"="XT2"."A")
       filter(("XT2"."B"=1 OR "XT2"."B"=2)) 

From 10053 trace on nonpatched 11.2.0.3:

Just link to my old package for os commands execution

Posted on September 19, 2013 by Sayan Malakshinov Posted in oracle, PL/SQL 2,120 Page views 0 Comments

I just noticed that os_command.zip from old oracle white paper was lost, so i decided to post link to my old package, which is like os_command but with timeout parameter: http://github.com/xtender/xt_shell

Oracle 12c: behavior tests of the Inline functions, “Identities” and “defaults”

Posted on July 13, 2013 by Sayan Malakshinov Posted in 12c, curious, undocumented 2,930 Page views 4 Comments

I have done several minitests:
1. SQL and PL/SQL engines: which functions will be executed if there are two functions with same name as in SQL, as in PL/SQL (like “USER”, LPAD/RPAD, etc..)
– PL/SQL.

PL/SQL

2. Will there be any context switches if we call the inline functions which contain another pl/sql functions/procedures?
– Yes

Test 1

Test 2

[sourcecode language=”sql”]
SQL> @trace_pl_on

Session altered.

SQL> create or replace function f_standalone(p varchar2) return varchar2 is
2 begin
3 return lpad(‘x’,3)||p;
4 end;
5 /

Function created.

SQL> with
2 function blabla(p_str varchar2) return varchar2 is
3 s varchar2(32767);
4 begin
5 s:= lpad(p_str, 100, ‘1’);
6 s:= s||s;
7 s:= s||lpad(p_str, 100, ‘3’);
8 s:= s||s;
9 s:= s||(1+10);
10 s:= f_standalone(s);
11 s:= f_standalone(s);
12 s:= f_standalone(s);
13 return s;
14 end;
15 select
16 length(blabla(dummy)) lpad_plsql
17 from dual
18 /

LPAD_PLSQL
———-
611

SQL> @trace_pl_last.sql

RUNID EVENT_SEQ EVENT_COMMENT ———- ———- ——————————– —————– ————
2 1 PL/SQL Trace 2 2 Trace flags 2 3 PL/SQL Virtual Machine started 2 4 PL/SQL Internal Call 2 5 PL/SQL Virtual 2 6 PL/SQL Virtual Machine started 2 7 PL/SQL Virtual Machine started 2 8 PL/SQL Internal Call 2 9 PL/SQL Virtual 2 10 PL/SQL Virtual 2 11 PL/SQL Virtual Machine started 2 12 PL/SQL Virtual Machine started 2 13 PL/SQL Internal Call 2 14 PL/SQL Virtual 2 15 PL/SQL Virtual 2 16 PL/SQL Virtual Machine started 2 17 PL/SQL Internal Call 2 18 PL/SQL Internal Call 2 19 Procedure Call 2 20 PL/SQL Internal Call 2 21 Return from procedure call 2 22 Procedure Call 2 23 PL/SQL Internal Call 2 24 Return from procedure call 2 25 Procedure Call 2 26 PL/SQL Internal Call 2 27 Return from procedure call 2 28 PL/SQL Virtual EVENT_UNIT_OWNER EVENT_UNIT
Tool started
changed
<anonymous>
<anonymous>
Machine stopped
<anonymous>
<anonymous>
<anonymous>
Machine stopped
Machine stopped
<anonymous>
<anonymous>
<anonymous>
Machine stopped
Machine stopped
<anonymous>
<anonymous>
<anonymous>
<anonymous>
XTENDER F_STANDALONE
XTENDER F_STANDALONE
<anonymous>
XTENDER F_STANDALONE
XTENDER F_STANDALONE
<anonymous>
XTENDER F_STANDALONE
XTENDER F_STANDALONE
Machine stopped

28 rows selected.
[/sourcecode]

[collapse]

Test 3

3. How IDENTITY works?
For all identity columns Oracle creates a sequence with name like “ISEQ$$_XXX”, where “XXX” is the object_id of the table. All identities we can get through DBA_TAB_IDENTITY_COLS.
All Identity sequences:

select i.*
      ,tab.owner       tab_owner
      ,tab.object_name tab_name
      ,sq.object_name  sequence_name
from sys.idnseq$ i
    ,dba_objects tab
    ,dba_objects sq
where tab.object_id=i.obj#
  and sq.object_id = i.seqobj#

And we can see usage of this sequence in plans:

4. When executes “default seq.nextval”?

Test

[sourcecode language=”sql”]
SQL> create sequence xt_sq1;
SQL> create sequence xt_sq2;
SQL> create table xt_default(
2 id1 int default xt_sq1.nextval
3 , pad varchar2(30)
4 , id2 int default xt_sq2.nextval
5 );

Table created.

SQL> insert into xt_default(pad) values(‘1’);

1 row created.

SQL> select xt_sq1.currval, xt_sq2.currval from dual;

CURRVAL CURRVAL
———- ———-
1 1

SQL> insert into xt_default(pad) values(1/0);
insert into xt_default(pad) values(1/0)
*
ERROR at line 1:
ORA-01476: divisor is equal to zero

SQL> select xt_sq1.currval, xt_sq2.currval from dual;

CURRVAL CURRVAL
———- ———-
2 2
[/sourcecode]

[collapse]

12c undocumented oracle

Oracle 12c: Extended varchars

Posted on July 13, 2013 by Sayan Malakshinov Posted in 12c 3,101 Page views 10 Comments

Tim Hall perfectly describes in his excellent post how new extended datatypes are stored on Oracle 12c.
I just found interesting parameter “_scalar_type_lob_storage_threshold“ – “threshold for VARCHAR2, NVARCHAR2, and RAW storage as BLOB” – This parameter is the max size in bytes, at which these data types will be stored “inline” as simple datatypes, without creation of the lob segments.
See little example:

Controlling store extended varchars as lob

[sourcecode language=”sql” highlight=”5,15,20,28,54″]
SQL> @param_ _scalar_type_lob_storage_threshold;

NAME VALUE DEFLT TYPE DESCRIPTION
—————————————- ———— ———— ———— ————————————————————
_scalar_type_lob_storage_threshold 4000 TRUE number threshold for VARCHAR2, NVARCHAR2, and RAW storage as BLOB

SQL> select * from user_lobs;

no rows selected

SQL> create table T_4000(
2 i int generated always as identity
3 ,v1000 varchar2(1000)
4 ,v4000 varchar2(4000)
5 ,v4500 varchar2(4500)
6 );

Table created.

SQL> alter system set "_scalar_type_lob_storage_threshold"=5000;

System altered.

SQL> create table T_5000(
2 i int generated always as identity
3 ,v1000 varchar2(1000)
4 ,v4000 varchar2(4000)
5 ,v4500 varchar2(4500)
6 );

Table created.

SQL> select table_name,column_name ,data_type,data_type_mod,data_length,char_col_decl_length,char_length,char_used
2 from user_tab_columns;

TABLE_NAME COLUMN_NAM DATA_TYPE DAT DATA_LENGTH CHAR_COL_DECL_LENGTH CHAR_LENGTH C
———- ———- ———- — ———– ——————– ———– –
T_4000 V4500 VARCHAR2 4500 4500 4500 B
T_4000 V4000 VARCHAR2 4000 4000 4000 B
T_4000 V1000 VARCHAR2 1000 1000 1000 B
T_4000 I NUMBER 22 0
T_5000 V4500 VARCHAR2 4500 4500 4500 B
T_5000 V4000 VARCHAR2 4000 4000 4000 B
T_5000 V1000 VARCHAR2 1000 1000 1000 B
T_5000 I NUMBER 22 0

8 rows selected.

SQL> select table_name,column_name,chunk,retention,cache,logging,encrypt,compression,deduplication,in_row,securefile
2 from user_lobs;

TABLE_NAME COLUMN_NAM CHUNK RETENTION CACHE LOGGING ENCR COMPRE DEDUPLICATION IN_ SEC
———- ———- ———- ———- ———- ——- —- —— ————— — —
T_4000 V4500 8192 YES YES NO NO NO YES YES

[/sourcecode]

[collapse]

Note that there are no lobs for table t_5000!