XTended Oracle SQL

Subscribe to XTended Oracle SQL feed
XTended Oracle SQL
Updated: 6 hours 22 min ago

Another bug with lateral

Fri, 2019-02-15 18:32

Compare the results of the following query with the clause “fetch first 2 rows only”

with 
 t1(a) as (select * from table(odcinumberlist(1,3)))
,t2(a,b) as (select * from table(ku$_objnumpairlist(
                                 sys.ku$_objnumpair(1,1),
                                 sys.ku$_objnumpair(1,2),
                                 sys.ku$_objnumpair(1,3),
                                 sys.ku$_objnumpair(3,1),
                                 sys.ku$_objnumpair(3,2),
                                 sys.ku$_objnumpair(3,3)
                                 )))
,t(id) as (select * from table(odcinumberlist(1,2,3,4,5,6,7)))
select
  *
from t,
     lateral(select t1.a,t2.b
             from t1,t2 
             where t1.a = t2.a 
               and t1.a = t.id
             order by t2.b
             fetch first 2 rows only
             )(+)
order by id;

        ID          A          B
---------- ---------- ----------
         1          1          1
         1          3          1
         2          1          1
         2          3          1
         3          1          1
         3          3          1
         4          1          1
         4          3          1
         5          1          1
         5          3          1
         6          1          1
         6          3          1
         7          1          1
         7          3          1

14 rows selected.

with this one (i’ve just commented out the line with “fetch-first-rows-only”:

with 
 t1(a) as (select * from table(odcinumberlist(1,3)))
,t2(a,b) as (select * from table(ku$_objnumpairlist(
                                 sys.ku$_objnumpair(1,1),
                                 sys.ku$_objnumpair(1,2),
                                 sys.ku$_objnumpair(1,3),
                                 sys.ku$_objnumpair(3,1),
                                 sys.ku$_objnumpair(3,2),
                                 sys.ku$_objnumpair(3,3)
                                 )))
,t(id) as (select * from table(odcinumberlist(1,2,3,4,5,6,7)))
select
  *
from t,
     lateral(select t1.a,t2.b
             from t1,t2 
             where t1.a = t2.a 
               and t1.a = t.id
             order by t2.b
--             fetch first 2 rows only
             )(+)
order by id;

        ID          A          B
---------- ---------- ----------
         1          1          2
         1          1          3
         1          1          1
         2
         3          3          2
         3          3          1
         3          3          3
         4
         5
         6
         7

11 rows selected.

Obviously, the first query should return less rows than second one, but we can see that it returned more rows and join predicate “and t1.a = t.id” was ignored, because A and B are not empty and “A” is not equal to t.ID.

Categories: Development

Lateral view decorrelation(VW_DCL) causes wrong results with rownum

Fri, 2019-02-15 17:45

Everyone knows that rownum in inline views blocks many query transformations, for example pushing/pulling predicates, scalar subquery unnesting, etc, and many people use it for such purposes as a workaround to avoid unwanted transformations(or even CBO bugs).

Obviously, the main reason of that is different calculation of rownum:

If we pull the predicate “column_value = 3” from the following query to higher level

select * 
from (select * from table(odcinumberlist(1,1,1,2,2,2,3,3,3)) order by 1)
where rownum <= 2
  and column_value = 3;


COLUMN_VALUE
------------
           3
           3

we will get different results:

select * 
from (select *
      from (select * from table(odcinumberlist(1,1,1,2,2,2,3,3,3)) order by 1)
      where rownum <= 2
     )
where column_value = 3;

no rows selected

Doc ID 62340.1

[collapse]

But we recently encountered a bug with it: lateral view with ROWNUM returns wrong results in case of lateral view decorrelation.
Compare results of this query with and without no_decorrelation hint:

with 
 t1(a) as (select * from table(odcinumberlist(1,3)))
,t2(b) as (select * from table(odcinumberlist(1,1,3,3)))
,t(id) as (select * from table(odcinumberlist(1,2,3)))
select
  *
from t,
     lateral(select/*+ no_decorrelate */ rownum rn 
             from t1,t2 
             where t1.a=t2.b and t1.a = t.id
            )(+)
order by 1,2;

        ID         RN
---------- ----------
         1          1
         1          2
         2
         3          1
         3          2
with 
 t1(a) as (select * from table(odcinumberlist(1,3)))
,t2(b) as (select * from table(odcinumberlist(1,1,3,3)))
,t(id) as (select * from table(odcinumberlist(1,2,3)))
select
  *
from t,
     lateral(select rownum rn 
             from t1,t2 
             where t1.a=t2.b and t1.a = t.id
            )(+)
order by 1,2;

        ID         RN
---------- ----------
         1          1
         1          2
         2
         3          3
         3          4

Of course, we can draw conclusions even from these results: we can see that in case of decorrelation(query with hint) rownum was calculated before the join. But to be sure we can check optimizer’s trace 10053:

Final query after transformations:

******* UNPARSED QUERY IS *******
SELECT VALUE(KOKBF$2) "ID", "VW_DCL_76980902"."RN" "RN"
  FROM TABLE("ODCINUMBERLIST"(1, 2, 3)) "KOKBF$2",
       (SELECT ROWNUM "RN_0", VALUE(KOKBF$0) "ITEM_3"
          FROM TABLE("ODCINUMBERLIST"(1, 3)) "KOKBF$0",
               TABLE("ODCINUMBERLIST"(1, 1, 3, 3)) "KOKBF$1"
         WHERE VALUE(KOKBF$0) = VALUE(KOKBF$1)
        ) "VW_DCL_76980902"
 WHERE "VW_DCL_76980902"."ITEM_3"(+) = VALUE(KOKBF$2)
 ORDER BY VALUE(KOKBF$2), "VW_DCL_76980902"."RN"

*************************

[collapse]

I’ll modify it a bit just to make it more readable:
we can see that

select
  *
from t,
     lateral(select rownum rn 
             from t1,t2 
             where t1.a=t2.b and t1.a = t.id)(+)
order by 1,2;

was transformed to

select
  t.id, dcl.rn
from t,
     (select rownum rn 
      from t1,t2 
      where t1.a=t2.b) dcl
where dcl.a(+) = t.id
order by 1,2;

And it confirms that rownum was calculated on the different dataset (t1-t2 join) without join filter by table t.
I created SR with Severity 1 (SR #3-19117219271) more than a month ago, but unfortunately Oracle development do not want to fix this bug and moreover they say that is not a bug. So I think this is a dangerous precedent and probably soon we will not be able to be sure in the calculation of rownum and old fixes…

Categories: Development

Oracle Linux hangs after “probing EDD” in Oracle Cloud

Tue, 2019-02-05 03:38

Just short note: If your imported Oracle Linux image hangs on boot in the Oracle cloud, just set GRUB_DISABLE_UUID=”true” in /etc/default/grub

Categories: Development

Top-N again: fetch first N rows only vs rownum

Sun, 2018-12-30 05:04

Three interesting myths about rowlimiting clause vs rownum have recently been posted on our Russian forum:

  1. TopN query with rownum<=N is always faster than "fetch first N rows only" (ie. row_number()over(order by ...)<=N)
  2. “fetch first N rows only” is always faster than rownum<=N
  3. “SORT ORDER BY STOPKEY” stores just N top records during sorting, while “WINDOW SORT PUSHED RANK” sorts all input and stores all records sorted in memory.

Interestingly that after Vyacheslav posted first statement as an axiom and someone posted old tests(from 2009) and few people made own tests which showed that “fetch first N rows” is about 2-3 times faster than the query with rownum, the final decision was that “fetch first” is always faster.

First of all I want to show that statement #3 is wrong and “WINDOW SORT PUSHED RANK” with row_number works similarly as “SORT ORDER BY STOPKEY”:
It’s pretty easy to show using sort trace:
Let’s create simple small table Tests1 with 1000 rows where A is in range 1-1000 (just 1 block):

create table test1(a not null, b) as
  select level, level from dual connect by level<=1000;

alter session set max_dump_file_size=unlimited;
ALTER SESSION SET EVENTS '10032 trace name context forever, level 10';

ALTER SESSION SET tracefile_identifier = 'rownum';
select * from (select * from test1 order by a) where rownum<=10;

ALTER SESSION SET tracefile_identifier = 'rownumber';
select * from test1 order by a fetch first 10 rows only;

And we can see from the trace files that both queries did the same number of comparisons:

rownum:
----- Current SQL Statement for this session (sql_id=bbg66rcbt76zt) -----
select * from (select * from test1 order by a) where rownum<=10

---- Sort Statistics ------------------------------
Input records                             1000
Output records                            10
Total number of comparisons performed     999
  Comparisons performed by in-memory sort 999
Total amount of memory used               2048
Uses version 1 sort
---- End of Sort Statistics -----------------------

[collapse]
row_number

----- Current SQL Statement for this session (sql_id=duuy4bvaz3d0q) -----
select * from test1 order by a fetch first 10 rows only

---- Sort Statistics ------------------------------
Input records                             1000
Output records                            10
Total number of comparisons performed     999
  Comparisons performed by in-memory sort 999
Total amount of memory used               2048
Uses version 1 sort
---- End of Sort Statistics -----------------------

[collapse]

Ie. each row (except first one) was compared with the biggest value from top 10 values and since they were bigger than top 10 value, oracle doesn’t compare it with other TopN values.

And if we change the order of rows in the table both of these queries will do the same number of comparisons again:

from 999 to 0

create table test1(a not null, b) as
  select 1000-level, level from dual connect by level<=1000;

alter session set max_dump_file_size=unlimited;
ALTER SESSION SET EVENTS '10032 trace name context forever, level 10';

ALTER SESSION SET tracefile_identifier = 'rownum';
select * from (select * from test1 order by a) where rownum<=10;


ALTER SESSION SET tracefile_identifier = 'rownumber';
select * from test1 order by a fetch first 10 rows only;

[collapse]
rownum

----- Current SQL Statement for this session (sql_id=bbg66rcbt76zt) -----
select * from (select * from test1 order by a) where rownum<=10

---- Sort Statistics ------------------------------
Input records                             1000
Output records                            1000
Total number of comparisons performed     4976
  Comparisons performed by in-memory sort 4976
Total amount of memory used               2048
Uses version 1 sort
---- End of Sort Statistics -----------------------

[collapse]
row_number

----- Current SQL Statement for this session (sql_id=duuy4bvaz3d0q) -----
select * from test1 order by a fetch first 10 rows only

---- Sort Statistics ------------------------------
Input records                             1000
Output records                            1000
Total number of comparisons performed     4976
  Comparisons performed by in-memory sort 4976
Total amount of memory used               2048
Uses version 1 sort
---- End of Sort Statistics -----------------------

[collapse]

We can see that both queries required much more comparisons(4976) here, that’s because each new value is smaller than the biggest value from the topN and even smaller than lowest value, so oracle should get right position for it and it requires 5 comparisons for that (it compares with 10th value, then with 6th, 3rd, 2nd and 1st values from top10). Obviously it makes less comparisons for the first 10 rows.

Now let’s talk about statements #1 and #2:
We know that rownum forces optimizer_mode to switch to “first K rows”, because of the parameter “_optimizer_rownum_pred_based_fkr”

SQL> @param_ rownum

NAME                               VALUE  DEFLT  TYPE      DESCRIPTION
---------------------------------- ------ ------ --------- ------------------------------------------------------
_optimizer_rownum_bind_default     10     TRUE   number    Default value to use for rownum bind
_optimizer_rownum_pred_based_fkr   TRUE   TRUE   boolean   enable the use of first K rows due to rownum predicate
_px_rownum_pd                      TRUE   TRUE   boolean   turn off/on parallel rownum pushdown optimization

while fetch first/row_number doesn’t (it will be changed after the patch #22174392) and it leads to the following consequences:
1. first_rows disables serial direct reads optimization(or smartscan on Exadata), that’s why the tests with big tables showed that “fetch first” were much faster than the query with rownum.
So if we set “_serial_direct_read”=always, we get the same performance in both tests (within the margin of error).

2. In cases when index access (index full scan/index range scan) is better, CBO differently calculates the cardinality of underlying INDEX FULL(range) SCAN:
the query with rownum is optimized for first_k_rows and the cardinality of index access is equal to K rows, but CBO doesn’t reduce cardinality for “fetch first”, so the cost of index access is much higher, compare them:

rownum
SQL> explain plan for
  2  select *
  3  from (select * from test order by a,b)
  4  where rownum<=10;

--------------------------------------------------------------------------------------------
| Id  | Operation                     | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |            |    10 |   390 |     4   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY                |            |       |       |            |          |
|   2 |   VIEW                        |            |    10 |   390 |     4   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| TEST       |  1000K|    12M|     4   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN           | IX_TEST_AB |    10 |       |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<=10)

[collapse]
fetch first

SQL> explain plan for
  2  select *
  3  from test
  4  order by a,b
  5  fetch first 10 rows only;

-----------------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |      |    10 |   780 |       |  5438   (1)| 00:00:01 |
|*  1 |  VIEW                    |      |    10 |   780 |       |  5438   (1)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK|      |  1000K|    12M|    22M|  5438   (1)| 00:00:01 |
|   3 |    TABLE ACCESS FULL     | TEST |  1000K|    12M|       |   690   (1)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=10)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY "TEST"."A","TEST"."B")<=10)

[collapse]
fetch first + first_rows

SQL> explain plan for
  2  select/*+ first_rows */ *
  3  from test
  4  order by a,b
  5  fetch first 10 rows only;

--------------------------------------------------------------------------------------------
| Id  | Operation                     | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |            |    10 |   780 | 27376   (1)| 00:00:02 |
|*  1 |  VIEW                         |            |    10 |   780 | 27376   (1)| 00:00:02 |
|*  2 |   WINDOW NOSORT STOPKEY       |            |  1000K|    12M| 27376   (1)| 00:00:02 |
|   3 |    TABLE ACCESS BY INDEX ROWID| TEST       |  1000K|    12M| 27376   (1)| 00:00:02 |
|   4 |     INDEX FULL SCAN           | IX_TEST_AB |  1000K|       |  2637   (1)| 00:00:01 |
--------------------------------------------------------------------------------------------

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

   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=10)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY "TEST"."A","TEST"."B")<=10)

[collapse]
fetch first + index

SQL> explain plan for
  2  select/*+ index(test (a,b)) */ *
  3  from test
  4  order by a,b
  5  fetch first 10 rows only;

--------------------------------------------------------------------------------------------
| Id  | Operation                     | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |            |    10 |   780 | 27376   (1)| 00:00:02 |
|*  1 |  VIEW                         |            |    10 |   780 | 27376   (1)| 00:00:02 |
|*  2 |   WINDOW NOSORT STOPKEY       |            |  1000K|    12M| 27376   (1)| 00:00:02 |
|   3 |    TABLE ACCESS BY INDEX ROWID| TEST       |  1000K|    12M| 27376   (1)| 00:00:02 |
|   4 |     INDEX FULL SCAN           | IX_TEST_AB |  1000K|       |  2637   (1)| 00:00:01 |
--------------------------------------------------------------------------------------------

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

   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=10)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY "TEST"."A","TEST"."B")<=10)

[collapse]

So in this case we can add hints “first_rows” or “index”, or install the patch #22174392.

ps. I thought to post this note later, since I hadn’t time enough to add other interesting details about the different TopN variants, including “with tie”, rank(), etc, so I’ll post another note with more details later.

Categories: Development

Docker with Oracle database: install patches automatically

Thu, 2018-12-06 15:10

Recently I had to install the patch for fixing cross-platform PDB transport bug onto the docker images with Oracle, so these are easy way how to do it:

1. create directory “patches” and create “install_patches.sh”:

#!/bin/bash

unzip  -u ./*.zip
CURDIR=`pwd`

for D in *; do
    if [ -d "${D}" ]; then
        echo =================================================
        echo " *** Processing patch # ${D}... "   # your processing here
        cd "${D}"
        opatch apply -silent
    fi
    cd $CURDIR
done

2. add the following commands into the dockerfile:

USER root

# Copy DB install file
COPY --chown=oracle:dba patches $INSTALL_DIR/patches

# Install DB software binaries
USER oracle
RUN chmod ug+x $INSTALL_DIR/patches/*.sh && \
    sync && \
    cd $INSTALL_DIR/patches && \
    ./install_patches.sh

3. put downloaded patches into the “patches” directory and build image.

For example, dockerfile for 18.3:

FROM oracle/database:18.3.0-ee

MAINTAINER Sayan Malakshinov <sayan@orasql.org>

USER root

# Copy patches:
COPY --chown=oracle:dba patches $INSTALL_DIR/patches

# Install patches:
USER oracle
RUN chmod ug+x $INSTALL_DIR/patches/*.sh && \
    sync && \
    cd $INSTALL_DIR/patches && \
    ./install_patches.sh

ps. I’ve also create the request for that in the official Docker github: https://github.com/oracle/docker-images/issues/1070

Categories: Development

Top N biggest tables (with lobs, indexes and nested table)

Fri, 2017-12-15 17:37

Script for SQL*Plus: https://github.com/xtender/xt_scripts/blob/master/tops/top_seg_by_size.sql

with 
  seg as (
     select 
       owner,segment_name
      ,segment_type
      ,tablespace_name
      ,sum(blocks) blocks
      ,sum(bytes)  bytes
     from dba_segments s
     where  segment_type not in (
       'TYPE2 UNDO'
      ,'ROLLBACK'
      ,'SYSTEM STATISTICS'
     )
     and segment_name not like 'BIN$%' --not in recyclebin
     and owner like '&owner_mask' -- you can specify schema here
     group by owner,segment_name,segment_type,tablespace_name
  )
 ,segs as (
     select 
       owner,segment_name
      ,case when segment_name like 'DR$%$%' then 'CTX INDEX' else segment_type end segment_type
      ,tablespace_name
      ,case 
         when segment_name like 'DR$%$%' 
           then (select table_owner||'.'||table_name from dba_indexes i where i.owner=s.owner and i.index_name = substr(segment_name,4,length(segment_name)-5))
         when segment_type in ('TABLE','TABLE PARTITION','TABLE SUBPARTITION')
            then owner||'.'||segment_name
         when segment_type in ('INDEX','INDEX PARTITION','INDEX SUBPARTITION')
            then (select i.table_owner||'.'||i.table_name from dba_indexes i where i.owner=s.owner and i.index_name=s.segment_name)
         when segment_type in ('LOBSEGMENT','LOB PARTITION','LOB SUBPARTITION')
            then (select l.owner||'.'||l.TABLE_NAME from dba_lobs l where l.segment_name = s.segment_name and l.owner = s.owner)
         when segment_type = 'LOBINDEX'
            then (select l.owner||'.'||l.TABLE_NAME from dba_lobs l where l.index_name = s.segment_name and l.owner = s.owner)
         when segment_type = 'NESTED TABLE'
            then (select nt.owner||'.'||nt.parent_table_name from dba_nested_tables nt where nt.owner=s.owner and nt.table_name=s.segment_name)
         when segment_type = 'CLUSTER'
            then (select min(owner||'.'||table_name) from dba_tables t where t.owner=s.owner and t.cluster_name=s.segment_name and rownum=1)
       end table_name
      ,blocks
      ,bytes
     from seg s
  )
 ,so as (
     select
       segs.owner
      ,substr(segs.table_name,instr(segs.table_name,'.')+1) TABLE_NAME
      ,sum(segs.bytes)  total_bytes
      ,sum(segs.blocks) total_blocks
      ,sum(case when segs.segment_type in ('TABLE','TABLE PARTITION','TABLE SUBPARTITION','NESTED TABLE','CLUSTER') then segs.bytes end) tab_size
      ,sum(case when segs.segment_type in ('INDEX','INDEX PARTITION','INDEX SUBPARTITION','CTX INDEX') then segs.bytes end) ind_size
      ,sum(case when segs.segment_type in ('CTX INDEX') then segs.bytes end) ctx_size
      ,sum(case when segs.segment_type in ('LOBSEGMENT','LOBINDEX','LOB PARTITION','LOB SUBPARTITION') then segs.bytes end) lob_size
     from segs
     group by owner,table_name
  )
 ,tops as (
     select 
           dense_rank()over (order by total_bytes desc) rnk
          ,so.*
     from so
  )
select *
from tops
where rnk<=50 -- top 50
/
Categories: Development

“Collection iterator pickler fetch”: pipelined vs simple table functions

Tue, 2017-12-12 19:08

Alex R recently discovered interesting thing: in SQL pipelined functions work much faster than simple non-pipelined table functions, so if you already have simple non-pipelined table function and want to get its results in sql (select * from table(fff)), it’s much better to create another pipelined function which will get and return its results through PIPE ROW().

A bit more details:

Assume we need to return collection “RESULT” from PL/SQL function into SQL query “select * from table(function_F(…))”.
If we create 2 similar functions: pipelined f_pipe and simple non-pipelined f_non_pipe,

create or replace function f_pipe(n int) return tt_id_value pipelined 
as
  result tt_id_value;
begin
  ...
  for i in 1..n loop
    pipe row (result(i));
  end loop;
end f_pipe;
/
create or replace function f_non_pipe(n int) return tt_id_value 
as
  result tt_id_value;
begin
  ...
  return result;
end f_non_pipe;
/
Full functions definitions
create or replace type to_id_value as object (id int, value int)
/
create or replace type tt_id_value as table of to_id_value
/
create or replace function f_pipe(n int) return tt_id_value pipelined 
as
  result tt_id_value;
  
  procedure gen is
  begin
     result:=tt_id_value();
     result.extend(n);
     for i in 1..n loop
        result(i):=to_id_value(i, 1);
     end loop;
  end;    
begin
  gen();
  for i in 1..n loop
    pipe row (result(i));
  end loop;
end f_pipe;
/
create or replace function f_non_pipe(n int) return tt_id_value 
as
  result tt_id_value;
  
  procedure gen is
  begin
     result:=tt_id_value();
     result.extend(n);
     for i in 1..n loop
        result(i):=to_id_value(i, 1);
     end loop;
  end;    
begin
  gen();
  return result;
end f_non_pipe;
/
create or replace function f_pipe_for_nonpipe(n int) return tt_id_value pipelined 
as
  result tt_id_value;
begin
  result:=f_non_pipe(n);
  for i in 1..result.count loop
    pipe row (result(i));
  end loop;
end;
/
create or replace function f_udf_pipe(n int) return tt_id_value pipelined 
as
  result tt_id_value;
  
  procedure gen is
  begin
     result:=tt_id_value();
     result.extend(n);
     for i in 1..n loop
        result(i):=to_id_value(i, 1);
     end loop;
  end;    
begin
  gen();
  for i in 1..n loop
    pipe row (result(i));
  end loop;
end;
/
create or replace function f_udf_non_pipe(n int) return tt_id_value 
as
  result tt_id_value;
  
  procedure gen is
  begin
     result:=tt_id_value();
     result.extend(n);
     for i in 1..n loop
        result(i):=to_id_value(i, 1);
     end loop;
  end;    
begin
  gen();
  return result;
end;
/

[collapse]
Test queries

set echo on feed only timing on;
--alter session set optimizer_adaptive_plans=false;
--alter session set "_optimizer_use_feedback"=false;

select sum(id * value) s from table(f_pipe(&1));
select sum(id * value) s from table(f_non_pipe(&1));
select sum(id * value) s from table(f_pipe_for_nonpipe(&1));
select sum(id * value) s from table(f_udf_pipe(&1));
select sum(id * value) s from table(f_udf_non_pipe(&1));
with function f_inline_non_pipe(n int) return tt_id_value 
as
  result tt_id_value;
begin
     result:=tt_id_value();
     result.extend(n);
     for i in 1..n loop
        result(i):=to_id_value(i, 1);
     end loop;
     return result;
end;
select sum(id * value) s from table(f_inline_non_pipe(&1));
/
set timing off echo off feed on;

[collapse]

we’ll find that the function with simple “return result” works at least twice slower than pipelined function:

Function 1 000 000 elements 100 000 elements F_PIPE 2.46 0.20 F_NON_PIPE 4.39 0.44 F_PIPE_FOR_NONPIPE 2.61 0.26 F_UDF_PIPE 2.06 0.20 F_UDF_NON_PIPE 4.46 0.44

I was really surprised that even “COLLECTION ITERATOR PICKLER FETCH” with F_PIPE_FOR_NONPIPE that gets result of F_NON_PIPE and returns it through PIPE ROW() works almost twice faster than F_NON_PIPE, so I decided to analyze it using stapflame by Frits Hoogland.

I added “dbms_lock.sleep(1)” into both of these function after collection generation, to compare the difference only between “pipe row” in loop and “return result”:

Modified functions

create or replace function f_pipe(n int) return tt_id_value pipelined 
as
  result tt_id_value;
  
  procedure gen is
  begin
     result:=tt_id_value();
     result.extend(n);
     for i in 1..n loop
        result(i):=to_id_value(i, 1);
     end loop;
  end;    
begin
  gen();
  dbms_lock.sleep(1);
  for i in 1..n loop
    pipe row (result(i));
  end loop;
end f_pipe;
/
create or replace function f_non_pipe(n int) return tt_id_value 
as
  result tt_id_value;
  
  procedure gen is
  begin
     result:=tt_id_value();
     result.extend(n);
     for i in 1..n loop
        result(i):=to_id_value(i, 1);
     end loop;
  end;    
begin
  gen();
  dbms_lock.sleep(1);
  return result;
end f_non_pipe;
/

[collapse]

And stapflame showed that almost all overhead was consumed by the function “kgmpoa_Assign_Out_Arguments”:

I don’t know what this function is doing exactly, but we can see that oracle assign collection a bit later.
From other functions in this stack(pmucpkl, kopp2isize, kopp2colsize, kopp2atsize(attribute?), kopuadt) I suspect that is some type of preprocessiong of return arguments.
What do you think about it?

Full stapflame output:
stapflame_nonpipe
stapflame_pipe

Categories: Development

SQL*Plus tips #8: How to read the output of dbms_output without “serveroutput on”

Sat, 2017-12-09 16:49

When “serveroutput” is enabled, SQL*Plus executes “BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;” after each command.
That’s why I don’t like when it is always enabled: it adds extra calls and round-trips and it is inconvenient when I want to get a plan of the last executed query:

SQL> set serverout on;
SQL> select * from dual;

D
-
X

SQL> select * from table(dbms_xplan.display_cursor('','','allstats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
SQL_ID  9babjv8yq8ru3, child number 0

BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;

NOTE: cannot fetch plan for SQL_ID: 9babjv8yq8ru3, CHILD_NUMBER: 0
      Please verify value of SQL_ID and CHILD_NUMBER;
      It could also be that the plan is no longer in cursor cache (check v$sql_plan)

So usually I switch “serveroutput” on only if needed, but sometimes I can forget to enable it. In such cases I use very simple script that reads the output using dbms_output.get_lines and prints it using refcursor:
https://github.com/xtender/xt_scripts/blob/master/output_print.sql

When you set “serveroutput on“, SQL*Plus also executes “dbms_output.enable” and if you set “serverout off” it executes “dbms_output.disable”, that’s why my glogin.sql contains “call dbms_output.enable(1e6);” and you need to execute it after each “set serverout off” if you want to use this script.

Categories: Development

Bug with integer literals in PL/SQL

Fri, 2017-12-08 15:04

This interesting question was posted on our russian forum yesterday:

We have a huge PL/SQL package and this simple function returns wrong result when it’s located at the end of package body:

create or replace package body PKGXXX as
  ...
  function ffff return number is
  nRes number;
  begin        
    nRes :=  268435456;
    return nRes;
  end;
end;
/

But it works fine in any of the following cases:
* replace 268435456 with power(2, 28), or
* replace 268435456 with small literal like 268, or
* move this function to the beginning of package body

The one of the interesting findings was that the returned value is equal to the one of literals in another function.
We can reproduce this bug even with an anonymous pl/sql block. The following test case uses 32768 integer literals from 1000001 to 1032768 and prints 5 other integers:

declare n number;
begin
  n:=1000001; -- this part
  n:=1000002; -- creates
  n:=1000003; -- 32768 
   ...        -- integer
  n:=1032768; -- literals
    dbms_output.put_line('100000='||100000); -- it should print: 100000=100000
    dbms_output.put_line('32766 ='||32766);
    dbms_output.put_line('32767 ='||32767);    
    dbms_output.put_line('32768 ='||32768);
    dbms_output.put_line('32769 ='||32769);
end;
Test code
declare
   c clob:='declare n number;begin'||chr(10);
   f varchar2(100):='n:=%s;'||chr(10);
   v varchar2(32767);
   n number:=32768;
begin
   for i in 1..n loop
      v:=v||utl_lms.format_message(f,to_char(1e7+i));
      if length(v)>30000 then
         c:=c||v;
         v:='';
      end if;
   end loop;
   v:=v||q'[
    dbms_output.put_line('100000='||100000);
    dbms_output.put_line('32766 ='||32766);
    dbms_output.put_line('32767 ='||32767);    
    dbms_output.put_line('32768 ='||32768);
    dbms_output.put_line('32769 ='||32769);
   end;
   ]';
   c:=c||v;
   execute immediate c;
end;
/

[collapse]
It produces the following output:

100000=10000001
32766 =32766
32767 =32767
32768 =10000002
32769 =10000003

This test case well demonstrates wrong results:
* instead of 100000 we get 10000001, which is the value from first line after “begin”, ie 1st integer literal in the code,
* for 32766 and 32767 oracle returns right values
* instead of 32768 (==32767+1) it returns 10000002, which is the integer from 2nd line, ie 2nd integer literal in the code,
* instead of 32769 (==32767+2) it returns 10000003, which is the integer from 3rd line, ie 3rd integer literal in the code
After several tests I can make a conclusion:

  • It doesn’t matter what plsql_optimize_level or plsql_code_type you set, was debug enabled or not, the behaviour is the same.
  • It seems that this is a kind of PL/SQL optimization: during parsing, oracle leaves integer literal in place if its value is in range -32768..32767 (16bit signed int), but if its value is out of this range, oracle adds this value into array of integers’ constants and replaces the value with the index of this element in this array. But because of index value overflow in cases when a count of such integer literals becomes larger than 32768, instead of Nth element of this array, oracle returns Mth element, where M is mod(N,32767).

So we can describe this behaviour using first test case:

declare n number;
begin
  n:=1000001; -- this part
  n:=1000002; -- creates
  n:=1000003; -- 32768 
   ...        -- integer
  n:=1032768; -- literals
    dbms_output.put_line('100000='||100000); -- it should print 100000, ie 32768th element of array, but prints 10000001
                                             -- where 10000001 is the 1st element of array (1==mod(32768,32767))
    dbms_output.put_line('32766 ='||32766);  -- these 2 lines print right values,
    dbms_output.put_line('32767 ='||32767);  -- because their values are in the range of -32768..32767
    dbms_output.put_line('32768 ='||32768);  -- this line contains 32769th element and prints 2nd element of array (2==mod(32769,32767))
    dbms_output.put_line('32769 ='||32769);  -- this line contains 32770th element and prints 3nd element of array (3==mod(32770,32767))
end;

The following query can help you to find objects which can potentially have this problem:

select
  s.owner,s.name,s.type
 ,sum(regexp_count(text,'(\W|^)3\d{4,}([^.0-9]|$)')) nums_count -- this regexp counts integer literals >= 30000
from dba_source s 
where 
    owner='&owner'
and type in ('FUNCTION','PROCEDURE','PACKAGE','PACKAGE BODY')
group by s.owner,s.name,s.type
having sum(regexp_count(text,'(\W|^)3\d{4,}([^.0-9]|$)'))>32767 -- filter only objects which have >=32767 integer literal

Workaround:
You may noticed that I wrote about INTEGER literals only, so the easiest workaround is to make them FLOAT – just add “.” to the end of each literal:

declare n number;
begin
  n:=1000001.;
  n:=1000002.;
  n:=1000003.;
   ...       
  n:=1032768.;
    dbms_output.put_line('100000='||100000.);
    dbms_output.put_line('32766 ='||32766.);
    dbms_output.put_line('32767 ='||32767.);    
    dbms_output.put_line('32768 ='||32768.);
    dbms_output.put_line('32769 ='||32769.);
end;
Fixed test cases

declare
   c clob:='declare n number;begin'||chr(10);
   f varchar2(100):='n:=%s.;'||chr(10); -- I've added here "."
   v varchar2(32767);
   n number:=32768;
begin
   for i in 1..n loop
      v:=v||utl_lms.format_message(f,to_char(1e7+i));
      if length(v)>30000 then
         c:=c||v;
         v:='';
      end if;
   end loop;
   v:=v||q'[
    dbms_output.put_line('100000='||100000.); -- .
    dbms_output.put_line('32766 ='||32766.);
    dbms_output.put_line('32767 ='||32767.);    
    dbms_output.put_line('32768 ='||32768.);
    dbms_output.put_line('32769 ='||32769.);
   end;
   ]';
   c:=c||v;
   execute immediate c;
end;
/

[collapse]
Categories: Development

Oracle issues after upgrade to 12.2

Thu, 2017-11-23 16:18

Sometimes it’s really hard even to create reproducible test case to send it to oracle support, especially in case of intermittent errors.
In such cases, I think it would be really great to have access to similar service requests or bugs of other oracle clients.
So while my poll about knowledge sharing is still active, I want to share a couple of bugs we have faced after upgrade to 12.2 (and one bug from Eric van Roon). I’m going to remove the bugs from this list when they become “public” or “fixed”.
If you want to add own findings into this list, you can add them into comments. To make this process easier, you can provide just symptomps, short description and the link to own post with details – I’ll add it just as a link.

th.c_symptomps { min-width:100px; max-width:100px; } th.c_description { min-width:200px } .c_links { min-width:150px; max-width:220px; } .c_links ul { margin: 0 0 5px 0 !important; -webkit-padding-start: 5px; } .c_links ul li { margin-left: 0px; -webkit-padding-start: 0px; } td.c_symptomps { font-size:12px;} td.c_description { font-size:12px;} td.c_links { font-size:10px;} .c_body td { vertical-align: text-top; } div.hints_wrapper { border-style: solid; border-width: 1px; padding: 2px; overflow: scroll !important; } div.hints_content { width: 1175px; min-width:1175px; padding: 2px; }


Symptomps Description Links Intermittent ORA-01483 After upgrade to 12.2 we started getting “ORA-01483: invalid length for DATE or NUMBER bind variable” in different applications using different oracle drivers.
Interestingly, that after reconnect, Oracle processes the same statement with same bind variables successfully.
Looking into errorstack dump (alter system set events ‘1483 trace name errorstack level 3, lifetime 5’;) we have found that oracle mixed up all values.
The only similar bug we found in MOS was “OCI Application Fails With ORA-01483/ORA-01461 When Inserting VARCHAR2 Field From 12.2 Database Using Database Link To Lower Database Version. (Doc ID 2309285.1)”, but it shows different symptomps.
Nevertheless, we have tried second workaround from this doc and it helped us.

Workaround:
set “_qkslvc_extended_bind_sz” to 0 and bounce the database. Doc ID 2309285.1 Periodically never ending SQL_ID f1xfww55nj0xp with SYNC(on commit) and SMALL_R_ROW enabled It seems that sometimes ctxsys.syncrn(:idxownid, :idxoname, :idxid, :ixpid, :rtabnm, :flg, :smallr)
falls into infinite loop when you have ctx domain text indexes with SYNC(on commit) and SMALL_R_ROW option enabled

Workaround:
Recreate indexes as TRANSACTIONAL Near real-time indexes with SYNC(every…) option. ORA-07445: exception encountered: core dump [keswxCurNbRows()+61] with CURSOR() and DBMS_XMLGEN.getXML() Workaround:
use the following parameters:

  • “_optimizer_use_feedback”=false
  • “_optimizer_gather_feedback”=false
  • “_optimizer_dsdir_usage_control”=0
  • “_iut_enable”=false
“Non-public” Bug 26696342 ORA-01722 on quering ctx_preference_values After upgrade to Oracle 12.2.0.1 simple query “select * from ctx_preference_values” returns error ORA-01722: invalid number

details

If we look into the source of this view ctx_preference_values:

create or replace view ctxsys.ctx_preference_values as 
select /*+ ORDERED INDEX(dr$preference_value) */ 
u.name prv_owner 
,pre_name prv_preference 
,oat_name prv_attribute 
,decode(oat_datatype, 'B', decode(prv_value, 1, 'YES', 'NO'), 
nvl(oal_label, prv_value)) prv_value 
from 
sys."_BASE_USER" u 
,dr$preference 
,dr$preference_value 
,dr$object_attribute 
,dr$object_attribute_lov 
where prv_value = nvl(oal_value, prv_value) 
and oat_id = oal_oat_id (+) 
and oat_id = prv_oat_id 
and prv_pre_id = pre_id 
and pre_owner# = u.user#; 

and check PRV_VALUE from ctxsys.dr$preference_value:

select pre_name, prv_pre_id,prv_value 
from ctxsys.dr$preference 
, ctxsys.dr$preference_value 
, ctxsys.dr$object_attribute 
where prv_pre_id = pre_id 
and oat_id = prv_oat_id 
and oat_datatype='B' 
and not regexp_like(prv_value,'^\d*$') 
/ 
PRE_NAME                          PRV_PRE_ID PRV_VALUE
--------------------------------- ---------- ----------
CTXSYS.JSONREST_GERMAN_LEXER            1098 YES
CTXSYS.JSONREST_GERMAN_DIN_LEXER        1104 YES

we can find that the root cause of the problem is the line with decode(prv_value, 1, ‘YES’, ‘NO’)
and values ‘YES’ for preferences ‘CTXSYS.JSONREST_GERMAN_LEXER’, ‘CTXSYS.JSONREST_GERMAN_DIN_LEXER’.

Oracle tries to compare ‘YES’ with 1 in decode() and raises ORA-01722.

[collapse]

Workaround:

To add predicates to avoid these 2 preferences:

select * from ctx_preference_values
where prv_preference not in (‘CTXSYS.JSONREST_GERMAN_LEXER’,’CTXSYS.JSONREST_GERMAN_DIN_LEXER’); Wrong results with DETERMINISTIC functions in subquery factoring clause Description from Eric van Roon
Workaround:
alter session set “_plsql_cache_enable”=false; Examples

Categories: Development

Triggers and Redo: changes on 12.2

Sun, 2017-11-19 09:47

In one of the previous posts I showed How even empty trigger increases redo generation, but running the test from that post, I have found that this behaviour a bit changed on 12.2:
In my old test case, values of column A were equal to values of B, and on previous oracle versions including 12.1.0.2 we can see that even though “update … set B=A” doesn’t change really “B”, even empty trigger greatly increases redo generation.
But on 12.2.0.1 in case of equal values, the trigger doesn’t increase redo, so we can see small optimization here, though in case of different values, the trigger still increases reado generation greatly.

same_dumpredo.sql
set feed on;
drop table xt_curr1 purge;
drop table xt_curr2 purge;
-- simple table:
create table xt_curr1 as select '2' a, '2' b from dual connect by level<=1000;
-- same table but with empty trigger:
create table xt_curr2 as select '2' a, '2' b from dual connect by level<=1000;
create or replace trigger tr_xt_curr2 before update on xt_curr2 for each row
begin
  null;
end;
/
-- objectID and SCN:
col obj1 new_val obj1;
col obj2 new_val obj2;
col scn  new_val scn;
select 
  (select o.OBJECT_ID from user_objects o where o.object_name='XT_CURR1') obj1
 ,(select o.OBJECT_ID from user_objects o where o.object_name='XT_CURR2') obj2
 ,d.CURRENT_SCN scn
from v$database d
/
-- logfile1:
alter system switch logfile;
col member new_val logfile;
SELECT member
FROM v$logfile
WHERE 
     is_recovery_dest_file='NO'
 and group#=(SELECT group# FROM v$log WHERE status = 'CURRENT')
 and rownum=1;
-- update1:
set autot trace stat;
update xt_curr1 set b=a;
set autot off;
commit;
-- dump logfile1:
alter session set tracefile_identifier='log1_same';
ALTER SYSTEM DUMP LOGFILE '&logfile' SCN MIN &scn OBJNO &obj1;

-- logfile2:
alter system switch logfile;
col member new_val logfile;
SELECT member
FROM v$logfile
WHERE 
     is_recovery_dest_file='NO'
 and group#=(SELECT group# FROM v$log WHERE status = 'CURRENT')
 and rownum=1;
-- update2:
set autot trace stat;
update xt_curr2 set b=a;
set autot off;
commit;
-- dump logfile2:
alter session set tracefile_identifier='log2_same';
ALTER SYSTEM DUMP LOGFILE '&logfile' OBJNO &obj2;
alter session set tracefile_identifier='off';
disc;

[collapse]

diff_dumpredo.sql

set feed on;
drop table xt_curr1 purge;
drop table xt_curr2 purge;
-- simple table:
create table xt_curr1 as select '1' a, '2' b from dual connect by level<=1000;
-- same table but with empty trigger:
create table xt_curr2 as select '1' a, '2' b from dual connect by level<=1000;
create or replace trigger tr_xt_curr2 before update on xt_curr2 for each row
begin
  null;
end;
/
-- objectID and SCN:
col obj1 new_val obj1;
col obj2 new_val obj2;
col scn  new_val scn;
select 
  (select o.OBJECT_ID from user_objects o where o.object_name='XT_CURR1') obj1
 ,(select o.OBJECT_ID from user_objects o where o.object_name='XT_CURR2') obj2
 ,d.CURRENT_SCN scn
from v$database d
/
-- logfile1:
alter system switch logfile;
col member new_val logfile;
SELECT member
FROM v$logfile
WHERE 
     is_recovery_dest_file='NO'
 and group#=(SELECT group# FROM v$log WHERE status = 'CURRENT')
 and rownum=1;
-- update1:
set autot trace stat;
update xt_curr1 set b=a;
set autot off;
commit;
-- dump logfile1:
alter session set tracefile_identifier='log1_diff';
ALTER SYSTEM DUMP LOGFILE '&logfile' SCN MIN &scn OBJNO &obj1;

-- logfile2:
alter system switch logfile;
col member new_val logfile;
SELECT member
FROM v$logfile
WHERE 
     is_recovery_dest_file='NO'
 and group#=(SELECT group# FROM v$log WHERE status = 'CURRENT')
 and rownum=1;
-- update2:
set autot trace stat;
update xt_curr2 set b=a;
set autot off;
commit;
-- dump logfile2:
alter session set tracefile_identifier='log2_diff';
ALTER SYSTEM DUMP LOGFILE '&logfile' OBJNO &obj2;
alter session set tracefile_identifier='off';
disc;

[collapse]

Equal values:
12.1.0.2:

12.2.0.1:

Different values:
12.1.0.2:

12.2.0.1:

We can easily find that trigger disables batched “Array update”:

Categories: Development

Easy(lazy) way to check which programs have properly configured FetchSize

Wed, 2017-11-15 15:37
select 
   s.module
  ,ceil(max(s.rows_processed/s.fetches)) rows_per_fetch
from v$sql s
where 
    s.rows_processed>100
and s.executions    >1
and s.fetches       >1
and s.module is not null
and s.command_type  = 3    -- SELECTs only
and s.program_id    = 0    -- do not account recursive queries from stored procs
and s.parsing_schema_id!=0 -- <> SYS
group by s.module
order by rows_per_fetch desc nulls last
/
Categories: Development

PL/SQL functions: Iterate and keys for associative arrays

Sun, 2017-10-01 17:54

Unfortunately associative arrays still require more “coding”:
we still can’t use “indices of” or “values of” in simple FOR(though they are available for FORALL for a long time), don’t have convinient iterators and even function to get all keys…
That’s why I want to show my templates for such things like iterator and keys function. You can adopt these functions and create them on schema level.

declare 
   type numbers  is table of number;
   type anumbers is table of number index by pls_integer;
   a anumbers;
   i pls_integer;

   function iterate( idx in out nocopy pls_integer, arr in out nocopy anumbers) 
      return boolean
   as pragma inline;
   begin
      if idx is null 
         then idx:=arr.first; 
         else idx:=arr.next(idx);
      end if;
      return idx is not null;
   end;

  function keys(a in out nocopy anumbers) return numbers as
     res numbers:=numbers();
     idx number;
     pragma inline;
  begin
     while iterate(idx,a) loop
        res.extend;
        res(res.count):=idx;
     end loop;
     return res;
  end;

begin
   a(1):=10;
   a(3):=30;
   a(5):=50;
   a(8):=80;
   -- iterate:
   while iterate(i,a) loop
      dbms_output.put_line(a(i));
   end loop;

   -- keys:
   for i in 1..keys(a).count loop
      dbms_output.put_line(a(keys(a)(i)));
   end loop;

end;
Categories: Development

How to group connected elements (or pairs)

Thu, 2017-09-28 22:03

I see quite often when developers ask questions about connected components:

Table “MESSAGES” contains fields “SENDER” and “RECIPIENT”, which store clients id.
How to quickly get all groups of clients who are connected even through other clients if the table has X million rows?
So for this table, there should be 4 groups:

  • (1, 2, 4, 8, 16)
  • (3, 6, 12)
  • (5, 10, 20)
  • (7, 14)
  • (9, 18)
SENDERRECIPIENT122436485106127148169181020

Of course, we can solve this problem using SQL only (model, recursive subquery factoring or connect by with nocycle), but such solutions will be too slow for huge tables.

Example of SQL solution

with 
   t(sender,recipient) as (select level,level*2 from dual connect by level<=10)
,  v1 as (select rownum id,t.* from t)
,  v2 as (select id, account
          from v1
           unpivot (
             account for x in (sender,recipient)
           ))
, v3 as (
           select
              id
             ,account
             ,dense_rank()over(order by account) account_n
             ,count(*)over() cnt
           from v2)
, v4 as (
           select distinct grp,account
           from v3
           model
                dimension by (id,account_n)
                measures(id grp,account,cnt)
                rules
                iterate(1e6)until(iteration_number>cnt[1,1])(
                   grp[any,any] = min(grp)[any,cv()]
                  ,grp[any,any] = min(grp)[cv(),any]
                )
)
select
   listagg(account,',')within group(order by account) s
from v4
group by grp

[collapse]

In such situations it’s much better to adopt standard algorithms like Quick-find or Weighted quick-union for PL/SQL.
The first time I wrote such solution about 5 years ago and I even posted here one of the latest solutions, but all of them were not universal, so I’ve created the package today with a couple of functions for most common problems: XT_CONNECTED_COMPONENTS

It contains 2 functions based on Weighted quick-find quick-union algorithm:

  • function get_strings(cur in sys_refcursor, delim varchar2:=’,’) return strings_array pipelined;
    It accepts a cursor and returns found connected components as table of varchar2(v_size). You can change v_size in the package definition.
    Input cursor should contain one Varchar2 column with linked strings, for example: ‘a,b,c’.
    You can also specify list delimiter, by default it is comma.
    Examples:

    select * from table(xt_connected_components.get_strings( cursor(select ELEM1||','||ELEM2 from TEST));
    select * 
    from
     table(
       xt_connected_components.get_strings( 
         cursor(select 'a,b,c' from dual union all
                select 'd,e,f' from dual union all
                select 'e,c'   from dual union all
                select 'z'     from dual union all
                select 'X,Y'   from dual union all
                select 'Y,Z'   from dual)));
    COLUMN_VALUE
    -----------------------------------------
    STRINGS('X', 'Y', 'Z')
    STRINGS('a', 'b', 'c', 'd', 'e', 'f')
    STRINGS('z')
    
    
  • function get_numbers(cur in sys_refcursor) return numbers_array pipelined;
    This function also returns connected components, but for numbers.
    Input cursor should contain two columns with linked numbers.
    Examples:

    select * 
    from table(
            xt_connected_components.get_numbers( 
              cursor(
                select sender_id, recipient_id from messages
            )));
    select * 
    from
      table(
        xt_connected_components.get_numbers( 
           cursor(
              select level   account1
                   , level*2 account2 
              from dual 
              connect by level<=10
        )));
    SQL> select *
      2  from
      3    table(
      4      xt_connected_components.get_numbers(
      5         cursor(
      6            select level   account1
      7                 , level*2 account2
      8            from dual
      9            connect by level<=10
     10*     )))
    SQL> /
    
    COLUMN_VALUE
    ------------------------
    NUMBERS(1, 2, 4, 8, 16)
    NUMBERS(3, 6, 12)
    NUMBERS(5, 10, 20)
    NUMBERS(7, 14)
    NUMBERS(9, 18)
    

How to install:
Download all files from Github and execute “@install” in SQL*Plus or execute them in another tool in the following order:
xt_connected_components_types.sql
xt_connected_components_pkg.sql
xt_connected_components_bdy.sql

Download URL: https://github.com/xtender/xt_scripts/tree/master/extra/xt_connected_components

Categories: Development

Ampersand instead of colon for bind variables

Wed, 2017-09-27 10:22

I’ve troubleshooted one query today and I was very surprised that bind variables in this query were specified with &ampersand instead of :colon! I have never seen this before and I couldn’t find anything about this in documentation…
Unfortunately SQL*Plus doesn’t support ampersand yet, even if you disable define (“set define off”),
so I’ve tested such behaviour with this code:

set def off serverout on
exec declare s varchar2(1); begin execute immediate 'select 1 from dual where dummy=&var' into s using 'X'; dbms_output.put_line(s); end;

And it really works! //at least on 11.2.0.2 and 12.2.0.1

SQL> set def off serverout on
SQL> exec declare s varchar2(1); begin execute immediate 'select 1 from dual where dummy=&var' into s using 'X'; dbms_output.put_line(s); end;
1

PL/SQL procedure successfully completed.

SQL> select substr(sql_text,1,40) stext,sql_id,executions,rows_processed from v$sqlarea a where sql_text like '%dual%&var';

STEXT                                 SQL_ID        EXECUTIONS ROWS_PROCESSED
------------------------------------- ------------- ---------- --------------
select 1 from dual where dummy=&var   ckkw4u3atxz02          3              3

SQL> select * from table(dbms_xplan.display_cursor('ckkw4u3atxz02'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
SQL_ID  ckkw4u3atxz02, child number 0
-------------------------------------
select 1 from dual where dummy=&var

Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     2 (100)|          |
|*  1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("DUMMY"=:VAR)


18 rows selected.

Update: Btw, it works for SQL only, not for PL/SQL:

SQL> var v varchar2(1);
SQL> begin &v = 'Z'; end;
  2  /
begin &v = 'Z'; end;
      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00103: Encountered the symbol "&" when expecting one of the following:

SQL> exec &v := 'X';
BEGIN &v := 'X'; END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00103: Encountered the symbol "&" when expecting one of the following:
The symbol "&" was ignored.
SQL> exec :v := 'X';

PL/SQL procedure successfully completed.

SQL> select * from dual where dummy=&v
  2  ;

D
-
X

And we can can use mixed placeholders:

SQL> select * from dual where dummy=&v and &v=:v;

D
-
X
Categories: Development

Simple regexp to check that string contains word1 and word2 and doesn’t contain word3

Wed, 2017-09-13 17:15
with tst as (
  select 'qwe word1 asd ...............' s from dual
  union all
  select 'qwe word1 asd word2 .........' s from dual
  union all
  select 'qwe word1 asd word2 zxc word3' s from dual
  union all
  select 'qwe word2 asd word1 zxc word4' s from dual
)
select
  s
  ,regexp_replace(s, '(word1)|(word2)|(word3)|(.)','`\3') subst
  ,case when regexp_like(regexp_replace(s, '(word1)|(word2)|(word3)|(.)','`\3') , '^`+$') then 'matched' end tst2
from tst
where 1=1
--and regexp_like(regexp_replace(s, '(word1)|(word2)|(word3)|(.)','`\3') , '^`+$')
Categories: Development

Book advice: ORACLE SQL & PL/SQL Golden Diary by Asim Chowdhury

Thu, 2017-08-10 06:11

I’ve reviewed this book recently, and I highly recommend it as it has almost all that needed to become strong Oracle developer. You can check at least the table of contents:

ORACLE SQL & PL/SQL Golden Diary: by Asim Chowdhury
New Book Demystifies Complex Cross-Version Oracle Problem Solving

Compiled by veteran computer scientist and data modeler, Asim Chowdhury, ‘ORACLE SQL & PL/SQL Golden Diary: Refactoring, Interoperability of Versions & Integration of related concepts for High Performance’ is the first book on the market that comprehensively allows data architects to unravel any concepts in SQL and PL/sql till oracle 12c. It’s poised to remove much confusion from the many versions of Oracle SQL now on the market; a Godsend for the computer science industry.

Categories: Development

SQL validation during PL/SQL compilation

Sun, 2017-04-30 18:27

A recent posting on SQL.RU asked why Oracle doesn’t raise such errors like “ORA-00979 not a group by expression” during PL/SQL compilation. Since I couldn’t find a link to the answer (though I read about it many years ago, but I don’t remember where…), I’ve decided to post short answer:

During PL/SQL compilation Oracle checks static SQL using only:

  1. Syntactic analysis – Oracle verifies that keywords, object names, operators, delimiters, and so on are placed correctly in your SQL statement. So such queries like “select * foRm dual” will fail during this validation. For example, we can get here such errors like:
    ORA-00900: invalid SQL statement
    ORA-00923: FROM keyword not found where expected
    ORA-00924: missing BY keyword
    ORA-00933: SQL command not properly ended
  2. Semantic analysis – it verifies that references to host variables and database objects are valid(including their grants) and that host-variable datatypes are correct. For example, “select * from nonexisting_table” will fail this validation.

And since Oracle doesn’t validate all other types of errors during Syntactic and Semantic analysis, we can detect them only during optimization* or execution*.
For example, Oracle detects “ORA-00979 not a group by expression” during optimization phase.

* Read “Chapter Chapter 7: Parsing and Optimizing” in Oracle Core by Jonathan Lewis

NB. It doesn’t not apply to CREATE or ALTER VIEW, because Oracle executes optimization step for them. You can check it using trace 10053.

Simple example:

-- fORm instead of "from" - syntactic validation fails:
SQL> create table t(a int, b int, c int);
SQL> create or replace procedure p_syntactic is
  2     cursor c is select a,b,sum(c) sum_c fORm t group by a;
  3  begin
  4     null;
  5  end;
  6  /

Warning: Procedure created with compilation errors.

SQL> sho error;
Errors for PROCEDURE P_SYNTACTIC:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/16     PL/SQL: SQL Statement ignored
2/40     PL/SQL: ORA-00923: FROM keyword not found where expected

-- semantic validation fails:
SQL> create or replace procedure p_semantic is
  2     cursor c is select a,b,sum(blabla) sum_c from t group by a;
  3  begin
  4     null;
  5  end;
  6  /

Warning: Procedure created with compilation errors.

SQL> sho error;
Errors for PROCEDURE P_SEMANTIC:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/16     PL/SQL: SQL Statement ignored
2/31     PL/SQL: ORA-00904: "BLABLA": invalid identifier

-- As you can see this procedure passes successfully both syntactic and semantic analysis,
-- though query is not valid: it should raise "ORA-00979: not a GROUP BY expression"
SQL> create or replace procedure p_valid is
  2     cursor c is select a,b,sum(c) sum_c from t group by a;
  3  begin
  4     null;
  5  end;
  6  /

SQL> sho error;
No errors.

-- Oracle checks such errors for "CREATE VIEW", because it runs optimization for the query text:
SQL> create view v_cursor as select a,b,sum(c) sum_c from t group by a order by a;
create view v_cursor as select a,b,sum(c) sum_c from t group by a order by a
                                 *
ERROR at line 1:
ORA-00979: not a GROUP BY expression
Categories: Development

How to speed up slow unicode migration of a table with xmltype columns

Sun, 2017-02-19 18:46

Recently I have had an issue with slow unicode migration of the database upgraded from 10g to 12.1.0.2. The main problem was a table with xmltype: we spent about 4 hours for this table(~17GB) during test migration, though all other tables (~190GB) migrated just for about 20 minutes.
We used DMU(Database Migration Assistant for Unicode), and the root cause of the problem was update statement generated by DMU for this table:

update  /*+ PARALLEL(A,16)*/ "RRR"."T_XMLDATA" A  set A."SYS_NC00011$" = SYS_OP_CSCONV(A."SYS_NC00011$", 'AL16UTF16')

“SYS_NC00011$” was internal hidden CLOB column used to store XMLTYPE. As you can see DMU added PARALLEL hint, but though oracle can use parallel dml for xmltype since 12.1.0.1, we can’t use it because of its’ limitations:

Changes in Oracle Database 12c Release 1 (12.1.0.1) for Oracle XML DB

Parallel DML Support for XMLType
Support for parallel DML has been improved for XMLType storage model binary XML using SecureFiles LOBs. The performance and scalability have been improved for both CREATE TABLE AS SELECT and INSERT AS SELECT.

Restrictions on Parallel DML

Parallel DML can be done on tables with LOB columns provided the table is partitioned. However, intra-partition parallelism is not supported.

For non-partitioned tables with LOB columns, parallel INSERT operations are supported provided that the LOB columns are declared as SecureFiles LOBs. Parallel UPDATE, DELETE, and MERGE operations on such tables are not supported.

Btw, Oracle didn’t support parallel dml for xmltype on previous releases:

No Parallel DML for XMLType – DML operations on XMLType data are always performed in serial. Parallel DML is not supported for XMLType. (Parallel query and DDL are supported for XMLType.)

So I had to use manual parallelization:
1. Monitor “Convert application tables” step through “View Table Conversion progress” and press “Stop” button during conversion of this table.
2. Create table with ROWIDs of this table and split them into 16 groups:

create table tmp_rids as 
select rowid rid, ntile(16)over(order by rowid) grp 
from t_xmldata;

3. Execute

ALTER SYSTEM SET EVENTS '22838 TRACE NAME CONTEXT LEVEL 1,FOREVER'; 

to avoid “ORA-22839: Direct updates on SYS_NC columns are disallowed”
4. Start 16 sessions and each of them have to update own part:

update t_xmldata A 
set A."SYS_NC00011$" = SYS_OP_CSCONV(A."SYS_NC00011$", 'AL16UTF16') 
where rowid in (select rid from tmp_rids where grp=&grp);
commit;

5. Disable event 22838:

ALTER SYSTEM SET EVENTS '22838 TRACE NAME CONTEXT OFF'; 

6. Open “View Table Conversion progress” window, click on this table and change “Retry” to “Skip” option for the update step.

This simple method allowed to make unicode migration about 16 times faster.

Categories: Development

row pieces, 255 columns, intra-block row chaining in details

Sat, 2017-02-11 20:54

You may know about Intra-block row chaining which may occur when the number of columns in a table are more than 255 columns.
But do you know that intra-block chaining works with inserts only? not updates!

Documentation says:

When a table has more than 255 columns, rows that have data after the 255th column are likely to be chained within the same block. This is called intra-block chaining. A chained row’s pieces are chained together using the rowids of the pieces. With intra-block chaining, users receive all the data in the same block. If the row fits in the block, users do not see an effect in I/O performance, because no extra I/O operation is required to retrieve the rest of the row.

A bit more details:
1. One row piece can store up to 255 columns
2. Oracle splits fields by row pieces in reverse order
3. Oracle doesn’t store trailing null fields in a row (not in row piece)
4. Next row piece can be stored in the same block only with inserts. When you run update, oracle will place new row piece into another block.

I’ll show in examples with dumps:

Example 1:
1. Create table TEST with 355 columns (c_1, c_2, …, c_355)
2. insert into test(c_300) values(2)
3. dump data blocks

test code

drop table test purge;
set serverout on;
alter session set tracefile_identifier='test1';
declare
   c varchar2(32000);
   v varchar2(32000);
   cols varchar2(32000):='c_1 number(1,0)';
   vals varchar2(32000):='null';
   
   ndf int;
   nbl int;
begin
   for i in 2..355 loop
      cols:=cols||',c_'||i||' number(1,0)';
      vals:=vals||','||case 
                         when i = 300 then '2'
                         else 'null'
                       end;
   end loop;
   c:='create table test('||cols||')';
   v:='insert into test values('||vals||')';
   dbms_output.put_line(c);
   dbms_output.put_line(v);
   execute immediate (c);
   execute immediate (v);
   
   for r in (select 'alter system dump datafile '||file#||' block '||block# cmd 
             from (
                   select distinct file#,block# 
                   from v$bh 
                   where class#=1 
                   and objd in (select o.data_object_id from user_objects o where object_name='TEST') 
                   order by 1,2
                  )
            )
   loop 
       execute immediate r.cmd;
   end loop;
end;
/
disc;

[collapse]
grep -P “^(bdba|block_row_dump|tl: |col )” test1.trc

bdba: 0x018019f3
block_row_dump:

bdba: 0x018019f4
block_row_dump:

bdba: 0x018019f5
block_row_dump:

bdba: 0x018019f6
block_row_dump:

bdba: 0x018019f7
    block_row_dump:
        tl: 260 fb: -----L-- lb: 0x1  cc: 255
            col  0: *NULL*
            col  1: *NULL*
            col  2: *NULL*
            col  3: *NULL*
               ...
            col 252: *NULL*
            col 253: *NULL*
            col 254: [ 2]  c1 03
        tl: 54 fb: --H-F--- lb: 0x1  cc: 45
            col  0: *NULL*
            col  1: *NULL*
            col  2: *NULL*
            col  3: *NULL*
               ...
            col 42: *NULL*
            col 43: *NULL*
            col 44: *NULL*

The resulted dump file shows us:
1. Both row pieces are in the same block 0x018019f4
2. They contain only first 300 columns, (trailing 55 columns are NULLs)
3. First row piece contains columns c_46 – c_300,
4. Second row piece contains columns c_1 – c_45 (they all are NULLs)

Example 2.
But let’s test an update with the same table:
1. Create table TEST with 355 columns (c_1, c_2, …, c_355)
2. insert into test(c_1) values(null)
3. update test set c_300=2
4. dump data blocks

Test code 2

drop table test purge;
set serverout on;
alter session set tracefile_identifier='test2';
declare
   c varchar2(32000);
   cols varchar2(32000):='c_1 number(1,0)';
begin
   for i in 2..355 loop
      cols:=cols||',c_'||i||' number(1,0)';
   end loop;
   c:='create table test('||cols||')';
   execute immediate (c);
   execute immediate ('insert into test(c_1) values(null)');
   execute immediate 'update test set c_300=3';
   commit;
   for r in (select 'alter system dump datafile '||file#||' block '||block# cmd 
             from (
                   select distinct file#,block# 
                   from v$bh 
                   where class#=1 
                   and objd in (select o.data_object_id from user_objects o where object_name='TEST') 
                   order by 1,2
                  )
            )
   loop 
       execute immediate r.cmd;
   end loop;
end;
/
disc;

[collapse]
Dump:

bdba: 0x018019f3
    block_row_dump:
        tl: 260 fb: -----L-- lb: 0x1  cc: 255
            col  0: *NULL*
            col  1: *NULL*
            col  2: *NULL*
               ...
            col 251: *NULL*
            col 252: *NULL*
            col 253: *NULL*
            col 254: [ 2]  c1 04

bdba: 0x018019f4
block_row_dump:

bdba: 0x018019f5
block_row_dump:

bdba: 0x018019f6
block_row_dump:

bdba: 0x018019f7
    block_row_dump:
        tl: 54 fb: --H-F--- lb: 0x1  cc: 45
            col  0: *NULL*
            col  1: *NULL*
            col  2: *NULL*
               ...
            col 42: *NULL*
            col 43: *NULL*
            col 44: *NULL*

As you can see, there is no intra-block chaining – second row piece was created in another block.

Example 3.
Now I want to show how oracle splits already chained rows:
1. Create table TEST with 355 columns (c_1, c_2, …, c_355)
2. insert into test(c_1) values(1)
3. update test set c_300=2
4. update test set c_301=3
5. update test set c_302=4
6. dump data blocks

Test code 3

drop table test purge;
set serverout on
alter session set tracefile_identifier='test3';

declare
   cols varchar2(32000):='c_1 number(1,0)';
   
   procedure print_and_exec(c varchar2) as
   begin
      dbms_output.put_line(c);
      execute immediate c;
   end;
begin
   for i in 2..355 loop
      cols:=cols||',c_'||i||' number(1,0)';
   end loop;
   print_and_exec ('create table test('||cols||')');
   print_and_exec ('insert into test(c_1) values(1)');
   print_and_exec ('update test set c_300=2');
   print_and_exec ('update test set c_301=3');
   print_and_exec ('update test set c_302=4');
   commit;
   execute immediate 'alter system flush buffer_cache';
   execute immediate 'select count(*) from test';
   for r in (select 'alter system dump datafile '||file#||' block '||block# cmd 
             from (
                   select distinct file#,block# 
                   from v$bh 
                   where class#=1 
                   and objd in (select o.data_object_id from user_objects o where object_name='TEST') 
                   order by 1,2
                  )
            )
   loop 
       execute immediate r.cmd;
   end loop;
end;
/
disc;

[collapse]

bdba: 0x018019f3
    block_row_dump:
        tl: 10 fb: -------- lb: 0x1  cc: 1
            col  0: *NULL*

bdba: 0x018019f4
    block_row_dump:
        tl: 264 fb: -----L-- lb: 0x1  cc: 255
            col  0: *NULL*
            col  1: *NULL*
               ...
            col 249: *NULL*
            col 250: *NULL*
            col 251: *NULL*
            col 252: [ 2]  c1 03
            col 253: [ 2]  c1 04
            col 254: [ 2]  c1 05

bdba: 0x018019f5
block_row_dump:

bdba: 0x018019f6
    block_row_dump:
        tl: 10 fb: -------- lb: 0x1  cc: 1
            col  0: *NULL*

bdba: 0x018019f7
    block_row_dump:
        tl: 56 fb: --H-F--- lb: 0x1  cc: 45
            col  0: [ 2]  c1 02
            col  1: *NULL*
            col  2: *NULL*
            col  3: *NULL*
               ...
            col 42: *NULL*
            col 43: *NULL*
            col 44: *NULL*

This dump shows us 4 row pieces: First row piece contains 255 columns, second – 45, and 2 row pieces – just by one row.
So we can analyze it step-by-step:
2. insert into test(c_1) values(1)
After insert we have just one row piece with 1 field.

3. update test set c_300=2
After this update, we have 2 row pieces:
1) c_1-c_45
2) c_46-c_300

4. update test set c_301=3
This update split row piece c_46-c_300 into 2 row pieces:
1) c_46
2) c_47-c_301
So we have 3 row pieces now: c_1-c_45, c_46, c_47-c_301

5. update test set c_302=4
This update split row piece c_47-c_301 into 2 row pieces:
1) c_47
2) c_48-c_302
And we’ve got 4 row pieces: c_1-c_45, c_46, c_47, c_48-c_302

You can try Example 4 and see how many blocks you can get, and all of them (except last one) will have only 1 column each:

Test code 4

drop table test purge;
set serverout on
alter session set tracefile_identifier='test4';

declare
   cols varchar2(32000):='c_1 char(3)';
   
   procedure print_and_exec(c varchar2) as
   begin
      dbms_output.put_line(c);
      execute immediate c;
   end;
begin
   for i in 2..355 loop
      cols:=cols||',c_'||i||' char(3)';
   end loop;
   print_and_exec ('create table test('||cols||')');
   print_and_exec ('insert into test(c_1) values(null)');
   commit;
   for i in 256..355 loop
      execute immediate 'update test set c_'||i||'='||i;
   end loop;
   commit;
   execute immediate 'alter system flush buffer_cache';
   for r in (select 'alter system dump datafile '||file#||' block '||block# cmd 
             from (
                   select distinct file#,block# 
                   from v$bh 
                   where class#=1 
                   and objd in (select o.data_object_id from user_objects o where object_name='TEST') 
                   order by 1,2
                  )
            )
   loop 
       execute immediate r.cmd;
   end loop;
end;
/
disc;

[collapse]

grep

bdba: 0x01801281
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x01801282
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x01801283
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x01801284
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x01801285
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x01801286
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x01801287
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x01801288
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x01801289
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x0180128a
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x0180128b
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x0180128c
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x0180128d
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x0180128e
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x0180128f
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x01801291
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x01801292
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x01801293
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x01801294
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x01801295
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x01801296
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x01801297
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x01801298
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x01801299
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x0180129a
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x0180129b
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x0180129c
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x0180129d
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x0180129e
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x0180129f
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012a1
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012a2
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012a3
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012a4
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012a5
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012a6
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012a7
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012a8
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012a9
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012aa
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012ab
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012ac
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012ad
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012ae
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012af
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012b1
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012b2
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012b3
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012b4
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012b5
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012b6
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012b7
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012b8
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012b9
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012ba
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012bb
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012bc
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012bd
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012be
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012bf
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012c1
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012c2
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012c3
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012c4
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012c5
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012c6
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012c7
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012c8
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012c9
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012ca
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012cb
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012cc
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012cd
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012ce
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012cf
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012d1
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012d2
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012d3
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012d4
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012d5
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012d6
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012d7
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012d8
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012d9
block_row_dump:
bdba: 0x018012da
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012db
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012dc
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012dd
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012de
block_row_dump:
tl: 558 fb: -----L-- lb: 0x1  cc: 255
col  0: *NULL*
col  1: *NULL*
col  2: *NULL*
   ...
col 152: *NULL*
col 153: *NULL*
col 154: *NULL*
col 155: [ 3]  32 35 36
col 156: [ 3]  32 35 37
col 157: [ 3]  32 35 38
col 158: [ 3]  32 35 39
  ...
col 251: [ 3]  33 35 32
col 252: [ 3]  33 35 33
col 253: [ 3]  33 35 34
col 254: [ 3]  33 35 35
bdba: 0x018012df
block_row_dump:
bdba: 0x018019f3
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018019f4
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018019f5
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018019f6
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018019f7
block_row_dump:
tl: 10 fb: --H-F--- lb: 0x2  cc: 1
col  0: *NULL*
bdba: 0x018019f8
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018019f9
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018019fa
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018019fb
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018019fc
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018019fd
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018019fe
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018019ff
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*

[collapse]

Categories: Development

Pages