Oracle SQL
  • LICENSE

Yearly Archives: 2016

How even empty trigger increases redo generation

Posted on September 22, 2016 by Sayan Malakshinov Posted in oracle 1,674 Page views 2 Comments

Very simple example:

Test case

[sourcecode language=”sql”]
set feed on;
— simple table:
create table xt_curr1 as select level a,level b from dual connect by level<=1e4;
— same table but with empty trigger:
create table xt_curr2 as select level a,level b from dual connect by level<=1e4;
create or replace trigger tr_xt_curr2 before update on xt_curr2 for each row
begin
null;
end;
/

set autot trace stat;
update xt_curr1 set b=a;
set autot off;

set autot trace stat;
update xt_curr2 set b=a;
set autot off;
set feed off

drop table xt_curr1 purge;
drop table xt_curr2 purge;
[/sourcecode]

[collapse]

Update 2017/11/19: I have posted more detailed test cases with logfile dumps here.

SQL> -- simple table:
SQL> create table xt_curr1 as select level a,level b from dual connect by level<=1e4;

Table created.

SQL> -- same table but with empty trigger:
SQL> create table xt_curr2 as select level a,level b from dual connect by level<=1e4;

Table created.

SQL> create or replace trigger tr_xt_curr2 before update on xt_curr2 for each row
  2  begin
  3    null;
  4  end;
  5  /

Trigger created.

SQL> update xt_curr1 set b=a;

10000 rows updated.


Statistics
----------------------------------------------------------
         25  recursive calls
      10553  db block gets
         91  consistent gets
         18  physical reads
    3101992  redo size
        560  bytes sent via SQL*Net to client
        491  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      10000  rows processed

SQL> update xt_curr2 set b=a;

10000 rows updated.


Statistics
----------------------------------------------------------
         11  recursive calls
      20384  db block gets
         59  consistent gets
         18  physical reads
    4411724  redo size
        560  bytes sent via SQL*Net to client
        491  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      10000  rows processed
trigger

8 queens chess problem: solution in Oracle SQL

Posted on June 13, 2016 by Sayan Malakshinov Posted in curious, oracle, SQL 1,878 Page views 1 Comment

This is just another solution of this problem for a chessboard, but you can choose any size of the checkerboard:

with 
 t as (select level i, cast(level as varchar2(1)) c from dual connect by level<=&d)
,x(l,s,n) as (
       select 1 l, c s, chr(97)||c||' ' from t
       union all
       select l+1, x.s||t.c, n||chr(97+l)||i||' '
       from x
            join t
                 on instr(s,c)=0
                    and not exists(select 0 from dual 
                                   where L+1 - t.i = level - substr(s,level,1)
                                      or L+1 + t.i = level + substr(s,level,1)
                                   connect by level<=length(s))
       where L<&d
 )
select n
from x
where l=&d

8x8:

[sourcecode language=”sql”]
SQL> @tests/f
Size[8]: 8

N
——————————————————————————–
a1 b5 c8 d6 e3 f7 g2 h4
a1 b6 c8 d3 e7 f4 g2 h5
a1 b7 c4 d6 e8 f2 g5 h3
a1 b7 c5 d8 e2 f4 g6 h3
a2 b4 c6 d8 e3 f1 g7 h5
a2 b5 c7 d1 e3 f8 g6 h4
a2 b5 c7 d4 e1 f8 g6 h3
a2 b6 c1 d7 e4 f8 g3 h5
a2 b6 c8 d3 e1 f4 g7 h5
a2 b7 c3 d6 e8 f5 g1 h4
a2 b7 c5 d8 e1 f4 g6 h3
a2 b8 c6 d1 e3 f5 g7 h4
a3 b1 c7 d5 e8 f2 g4 h6
a3 b5 c2 d8 e1 f7 g4 h6
a3 b5 c2 d8 e6 f4 g7 h1
a3 b5 c7 d1 e4 f2 g8 h6
a3 b5 c8 d4 e1 f7 g2 h6
a3 b6 c2 d5 e8 f1 g7 h4
a3 b6 c2 d7 e1 f4 g8 h5
a3 b6 c2 d7 e5 f1 g8 h4
a3 b6 c4 d1 e8 f5 g7 h2
a3 b6 c4 d2 e8 f5 g7 h1
a3 b6 c8 d1 e4 f7 g5 h2
a3 b6 c8 d1 e5 f7 g2 h4
a3 b6 c8 d2 e4 f1 g7 h5
a3 b7 c2 d8 e5 f1 g4 h6
a3 b7 c2 d8 e6 f4 g1 h5
a3 b8 c4 d7 e1 f6 g2 h5
a4 b1 c5 d8 e2 f7 g3 h6
a4 b1 c5 d8 e6 f3 g7 h2
a4 b2 c5 d8 e6 f1 g3 h7
a4 b2 c7 d3 e6 f8 g1 h5
a4 b2 c7 d3 e6 f8 g5 h1
a4 b2 c7 d5 e1 f8 g6 h3
a4 b2 c8 d5 e7 f1 g3 h6
a4 b2 c8 d6 e1 f3 g5 h7
a4 b6 c1 d5 e2 f8 g3 h7
a4 b6 c8 d2 e7 f1 g3 h5
a4 b6 c8 d3 e1 f7 g5 h2
a4 b7 c1 d8 e5 f2 g6 h3
a4 b7 c3 d8 e2 f5 g1 h6
a4 b7 c5 d2 e6 f1 g3 h8
a4 b7 c5 d3 e1 f6 g8 h2
a4 b8 c1 d3 e6 f2 g7 h5
a4 b8 c1 d5 e7 f2 g6 h3
a4 b8 c5 d3 e1 f7 g2 h6
a5 b1 c4 d6 e8 f2 g7 h3
a5 b1 c8 d4 e2 f7 g3 h6
a5 b1 c8 d6 e3 f7 g2 h4
a5 b2 c4 d6 e8 f3 g1 h7
a5 b2 c4 d7 e3 f8 g6 h1
a5 b2 c6 d1 e7 f4 g8 h3
a5 b2 c8 d1 e4 f7 g3 h6
a5 b3 c1 d6 e8 f2 g4 h7
a5 b3 c1 d7 e2 f8 g6 h4
a5 b3 c8 d4 e7 f1 g6 h2
a5 b7 c1 d3 e8 f6 g4 h2
a5 b7 c1 d4 e2 f8 g6 h3
a5 b7 c2 d4 e8 f1 g3 h6
a5 b7 c2 d6 e3 f1 g4 h8
a5 b7 c2 d6 e3 f1 g8 h4
a5 b7 c4 d1 e3 f8 g6 h2
a5 b8 c4 d1 e3 f6 g2 h7
a5 b8 c4 d1 e7 f2 g6 h3
a6 b1 c5 d2 e8 f3 g7 h4
a6 b2 c7 d1 e3 f5 g8 h4
a6 b2 c7 d1 e4 f8 g5 h3
a6 b3 c1 d7 e5 f8 g2 h4
a6 b3 c1 d8 e4 f2 g7 h5
a6 b3 c1 d8 e5 f2 g4 h7
a6 b3 c5 d7 e1 f4 g2 h8
a6 b3 c5 d8 e1 f4 g2 h7
a6 b3 c7 d2 e4 f8 g1 h5
a6 b3 c7 d2 e8 f5 g1 h4
a6 b3 c7 d4 e1 f8 g2 h5
a6 b4 c1 d5 e8 f2 g7 h3
a6 b4 c2 d8 e5 f7 g1 h3
a6 b4 c7 d1 e3 f5 g2 h8
a6 b4 c7 d1 e8 f2 g5 h3
a6 b8 c2 d4 e1 f7 g5 h3
a7 b1 c3 d8 e6 f4 g2 h5
a7 b2 c4 d1 e8 f5 g3 h6
a7 b2 c6 d3 e1 f4 g8 h5
a7 b3 c1 d6 e8 f5 g2 h4
a7 b3 c8 d2 e5 f1 g6 h4
a7 b4 c2 d5 e8 f1 g3 h6
a7 b4 c2 d8 e6 f1 g3 h5
a7 b5 c3 d1 e6 f8 g2 h4
a8 b2 c4 d1 e7 f5 g3 h6
a8 b2 c5 d3 e1 f7 g4 h6
a8 b3 c1 d6 e2 f5 g7 h4
a8 b4 c1 d3 e6 f2 g7 h5

92 rows selected.
[/sourcecode]

[collapse]

Solution for N between 10 and 100

[sourcecode language=”sql”]
with
t as (select level i, to_char(level,’fm00′) c from dual connect by level<=&d)
,x(l,s,n) as (
select 1 l, c s, chr(97)||c||’ ‘ from t
union all
select l+1, x.s||t.c, n||chr(97+l)||to_char(i,’fm00′)||’ ‘
from x
join t
on instr(s,c)=0
and not exists(select 0 from dual
where L+1 – t.i = level – substr(s,length(c)*level-1,length(c))
or L+1 + t.i = level + substr(s,length(c)*level-1,length(c))
connect by level<=length(s))
where L<&d
)
select n
from x
where l=&d
[/sourcecode]

[collapse]

It works quite fast:
8*8 ~ 0.1s
9*9 ~ 0.6s
10*10 ~4s

script for sqlplus

[sourcecode language=”sql”]
set arrays 1000;
col n for a80;
accept d prompt "Size[8]: " default 8;
with
t as (select/*+inline*/ level i, cast(level as varchar2(2)) c from dual connect by level<=&d)
,x(l,s,n) as (
select 1 l, c s, chr(97)||c||’ ‘ from t
union all
select l+1, x.s||t.c, n||chr(97+l)||i||’ ‘
from x
join t
on instr(s,c)=0
and not exists(select 0 from dual
where L+1 – t.i = level – substr(s,level,1)
or L+1 + t.i = level + substr(s,level,1)
connect by level<=length(s))
where L<&d
)
select n
from x
where l=&d
/
col n clear;
[/sourcecode]

[collapse]

Update: Fixed the typo, thanks to Brian Fitzgerald (@ExaGridDba)

quiz recursive sql recursive_subquery_clause

Maven: how to copy files after a build into several distribution directories

Posted on May 12, 2016 by Sayan Malakshinov Posted in java 3,092 Page views Leave a comment

Sometimes it is convenient to copy jar-files automatically after a build into several different directories, for example if you have different config files for local tests and for office test server, then you may want to copy these files into local test directory, internal office test server and public distribution directory.
This short part of pom.xml contains 2 different methods to build UBER-JAR and copying them into 3 different directories: localtest, officetest and public

    <build>
        <plugins>
            <plugin>
                <artifactId>maven-assembly-plugin</artifactId>
                <version>2.6</version>
                <configuration>
                    <archive>
                        <manifest>
                            <mainClass>tv.tmd.YourMainClass</mainClass>
                        </manifest>
                        <manifestEntries>
                            <Class-Path>.</Class-Path>
                        </manifestEntries>
                    </archive>
                    <descriptorRefs>
                        <descriptorRef>jar-with-dependencies</descriptorRef>
                    </descriptorRefs>
                </configuration>
                <executions>
                    <execution>
                        <id>make-assembly</id>
                        <phase>package</phase>
                        <goals>
                            <goal>single</goal>
                        </goals>
                    </execution>
                </executions>
            </plugin>

            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-shade-plugin</artifactId>
                <version>2.4.3</version>
                <executions>
                    <execution>
                        <phase>package</phase>
                        <goals>
                            <goal>shade</goal>
                        </goals>
                        <configuration>
                            <transformers>
                                <transformer implementation="org.apache.maven.plugins.shade.resource.ManifestResourceTransformer">
                                    <manifestEntries>
                                        <Main-Class>tv.tmd.YourMainClass</Main-Class>
                                        <Build-Number>2</Build-Number>
                                    </manifestEntries>
                                </transformer>
                            </transformers>
                        </configuration>
                    </execution>
                </executions>
            </plugin>

            <plugin>
                <artifactId>maven-antrun-plugin</artifactId>
                <version>1.8</version>
                <executions>
                    <execution>
                        <id>copy</id>
                        <phase>package</phase>
                        <configuration>
                            <target>
                                <echo>ANT TASK - copying files....</echo>
                                <copy todir="${basedir}/distribution/localtest" overwrite="true" flatten="true">
                                    <fileset dir="${basedir}" includes="*.bat" >
                                        <include name="*.bat" />
                                        <include name="ReadME.*" />
                                        <include name="target/*.jar" />
                                    </fileset>
                                </copy>

                                <copy todir="${basedir}/distribution/officetest" overwrite="true" flatten="true">
                                    <fileset dir="${basedir}" includes="*.bat" >
                                        <include name="*.bat" />
                                        <include name="ReadME.*" />
                                        <include name="target/*.jar" />
                                    </fileset>
                                </copy>

                                <copy todir="${basedir}/distribution/public" overwrite="true" flatten="true">
                                    <fileset dir="${basedir}" includes="*.bat" >
                                        <include name="*.bat" />
                                        <include name="ReadME.*" />
                                        <include name="target/*.jar" />
                                    </fileset>
                                </copy>
                            </target>
                        </configuration>
                        <goals>
                            <goal>run</goal>
                        </goals>
                    </execution>
                </executions>
            </plugin>
        </plugins>
    </build>
java maven maven-antrun-plugin maven-assembly-plugin maven-shade-plugin

What you need to know about Offload Quarantines

Posted on March 24, 2016 by Roger MacNicol Posted in oracle, SmartScan 1,930 Page views 1 Comment

Several questions have couple up in the last few weeks about offload quarantines which means a blog post on this topic is overdue. We work hard to stress test every new rpm that is released but on rare occasions customers can encounter an issue with the thin database layer that exists in the offload server. This layer is known externally as “Smart Scan” and internally as “FPLIB” (a.k.a. Filter Projection Library).

A crash in the thin database layer could because of either an issue with some aspect of the sql_id (for example, with the predicates) or because of an issue with the data on some region of disk (for example, with the OLTP compression symbol table). The worst, and rarest, form of crashes are where striping leads to every offload server failing simultaneously: these are known colloquially as “Railroad Crashes”). The most important thing is to make sure the retry mechanism doesn’t immediately resubmit the query and re-crash the offload server causing a halt to the business operating. In a hospital, the floor nurse would call a code and the crash team would come running with a crash cart to stabilize the patient. Two members of my family are nurses and I’m reminded that nurses are a lot like technical support engineers in that while doing their job they sometimes have to deal with abuse from frustrated patients (customers): please remember that both groups work hard to resolve your issues and be kind to them!

What are Quarantines?

The option of calling a crash cart is not available to us here so starting in early 11.2.0.3, we created a quarantine system where, after a crash, the exception handler remembers both the sql_id and the disk region being processed and creates a persistent quarantine for both. When a sql_id or a disk region is quarantined any Smart Scan operations on them will be executed in passthru mode.

Currently it is hard to see when this is happening, but an upcoming release has a new stat to make this easier. If an operation has been quarantined you can see its effects by monitoring:

  • cell num bytes in passthru due to quarantine

Listing Quarantines 

When a quarantine has been created you can look at it in detail using CellCLI:

CellCLI> list quarantine 1 detail
     name:                      1
         clientPID:             12798
         crashReason:           ORA-600[17099]
         creationTime:          2011-02-07T17:18:13-08:00
         dbUniqueID:            2022407934
         dbUniqueName:          YAMA
         incidentID:            16
         planLineID:            37
         quarantineReason:      Crash
         quarantineType:        "SQL PLAN"
         remoteHostName:        sclbndb02.us.oracle.com
         rpmVersion:            OSS_11.2.0.3.0_LINUX.X64_1012
         sqlID:                 1jw05wutgpfyf
         sqlPlanHashValue:      3386023660

The ‘list detail’ version of the command gives us everything we would need to know about exactly what has been quarantined and why it was quarantined. CellCLI also supports manually creating a quarantine using the attributes shown by ‘list detail’.

Quarantine Escalation

This is the topic that has caused the most confusion: if three new quarantines are generated within a 24 hour period the quarantine is escalated to a database quarantine. Using the ‘list detail’ option we would then see:

quarantinePlan:         SYSTEM
quarantineReason:       Escalated
quarantineType:         DATABASE

Note: the number of quarantines in 24 hours before escalation is configurable via a cellinit param: please contact Technical Support if you feel you have a valid need to change this.

The final level of escalation is where if more than one database has been escalated to a database quarantine, the system will escalate to a complete offload quarantine where Smart Scan is disabled completely and all I/O goes through regular block I/O. I’m glad to say that I’ve have never seen this happen.

Dropping Quarantines 

The next question is how and when are the quarantines removed. Any quarantine can be removed manually using CellCLI. Quarantines are also automatically dropped by certain operations:

  • Plan Step Quarantines:
    • Dropped on rpm upgrades
  • Disk Region Quarantines
    • Dropped on rpm upgrades, or on successful writes to the quarantined disk region
  • Database quarantines
    • Dropped on rpm upgrades
  • Offload quarantines
    • Dropped on rpm upgrades



What about CDB?

In 12.1, we changed the architecture of cellsrv to support multiple RDBMS versions running at the same time by introducing the concept of offload servers. When a new rpm is installed it typically contains offload servers for 11.2.0.4, 12.1.0.1, 12.1.0.2, (and 12.2.0.1). This is known internally as multi-DB. Any given operation is tagged with the RDBMS version it is coming from and routed to the offload server for that version. A crash in Smart Scan typically means that only the offload server has to restart and not the central cellsrv that maintains Storage Index and does Smart IO. A side effect of this is that all operations for that RDBMS version can revert to Block IO while the offload server restarts minimizing disruption.

The architecture change necessitated a change to the way quarantines are created, checked, and dropped. In multi-DB, installation of a new rpm no longer drops all quarantines. Instead, system created quarantines now record the offload server’s rpm version. Manually created quarantines can also optionally specify offload rpm they are to effect. In multi-DB, a quarantine is observed if the offload rpm specified matches the actual offload rpm the operation will be sent to or if no offload rpm is specified regardless of offloadgroup.
Multi-DB quarantines are dropped if the matching offload rpm is uninstalled or a new rpm installed for that offload version. Multi-DB quarantines with no offload rpm specified must be dropped manually.

Please let me know if you have any questions. 

 Roger

Offload Quarantine oracle Roger MacNicol SmartScan

WINDOW NOSORT STOPKEY + RANK()

Posted on March 12, 2016 by Sayan Malakshinov Posted in CBO, oracle, query optimizing, SQL 2,156 Page views Leave a comment

Recently I found that WINDOW NOSORT STOPKEY with RANK()OVER() works very inefficiently: http://www.freelists.org/post/oracle-l/RANKWINDOW-NOSORT-STOPKEY-stopkey-doesnt-work
The root cause of this behaviour is that Oracle optimizes WINDOW NOSORT STOPKEY with RANK the same way as with DENSE_RANK:

rnk1

[sourcecode language=”sql” highlight=””]
create table test(n not null) as
with gen as (select level n from dual connect by level<=100)
select g2.n as n
from gen g1, gen g2
where g1.n<=10
/
create index ix_test on test(n)
/
exec dbms_stats.gather_table_stats(”,’TEST’);
select/*+ gather_plan_statistics */ n
from (select rank()over(order by n) rnk
,n
from test)
where rnk<=3
/
select * from table(dbms_xplan.display_cursor(”,”,’allstats last’));
drop table test purge;
[/sourcecode]

[collapse]

Output

[sourcecode language=”sql” highlight=”29,30,31″]
N
———-
1
1
1
1
1
1
1
1
1
1

10 rows selected.

PLAN_TABLE_OUTPUT
———————————————————————————————————————–
SQL_ID 8tbq95dpw0gw7, child number 0
————————————-
select/*+ gather_plan_statistics */ n from (select rank()over(order by
n) rnk ,n from test) where rnk<=3

Plan hash value: 1892911073

———————————————————————————————————————–
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
———————————————————————————————————————–
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 3 | | | |
|* 1 | VIEW | | 1 | 1000 | 10 |00:00:00.01 | 3 | | | |
|* 2 | WINDOW NOSORT STOPKEY| | 1 | 1000 | 30 |00:00:00.01 | 3 | 73728 | 73728 | |
| 3 | INDEX FULL SCAN | IX_TEST | 1 | 1000 | 31 |00:00:00.01 | 3 | | | |
———————————————————————————————————————–

Predicate Information (identified by operation id):
—————————————————

1 – filter("RNK"<=3)
2 – filter(RANK() OVER ( ORDER BY "N")<=3)
[/sourcecode]

[collapse]

As you can see, A-Rows in plan step 2 = 30 – ie, that is the number of rows where

DENSE_RANK<=3

but not

RANK<=3

The more effective way will be to stop after first 10 rows, because 11th row already has RANK more than 3!
But we can create own STOPKEY version with PL/SQL:

PLSQL STOPKEY version

[sourcecode language="sql"]
create or replace type rowids_table is table of varchar2(18);
/
create or replace function get_rowids_by_rank(
n int
,max_rank int
)
return rowids_table pipelined
as
begin
for r in (
select/*+ index_rs_asc(t (n)) */ rowidtochar(rowid) chr_rowid, rank()over(order by n) rnk
from test t
where t.n > get_rowids_by_rank.n
order by n
)
loop
if r.rnk <= max_rank then
pipe row (r.chr_rowid);
else
exit;
end if;
end loop;
return;
end;
/
select/*+ leading(r t) use_nl(t) */
t.*
from table(get_rowids_by_rank(1, 3)) r
,test t
where t.rowid = chartorowid(r.column_value)
/
[/sourcecode]

[collapse]
In that case the fetch from a table will stop when rnk will be larger than max_rank

cbo query optimization
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