Turn on INTERVAL PARTITIONING on a range partition table

Create a range partition table.

SQL:

drop _TABLE_ date_range_t purge;

CREATE TABLE date_range_t
(  
d1   DATE,
n1   NUMBER
) 
PARTITION BY RANGE ( d1 ) 
SUBPARTITION BY LIST ( n1 ) 
SUBPARTITION TEMPLATE ( 
SUBPARTITION s_1    VALUES ( 1,2 ) , 
SUBPARTITION s_2    VALUES ( 3,4 ) 
)
( PARTITION p200911  VALUES LESS THAN ( TO_DATE('2009/12/01', 'YYYY/MM/DD') )
)
;

Turn on INTERVAL PARTITIONING by month on a range partition table.

SQL:

ALTER TABLE date_range_t SET INTERVAL ( NUMTOYMINTERVAL ( 1, 'MONTH' ) );

Effective table selectivity index selectivity and index column sequence

/*********
-- file://A:/SQL/CBO/index_selectivity.sql

| Modification History:
| Date Who What
| 18-Nov-2009: Charlie(Yi): Create the file,
|

Effective table selectivity selectivity and index column sequence

Goal
----
Research the order of the index columns and the effective index selectivity.

Solution
--------
Create a table with number column and date column,
Create 2 indexes on the table, one with ID in index first position, one with date_col on first position.
Benchmark the query with equal scan and range scan predicate on each index.

Outcome
-------
The logical reads on index access is: BLevel + leaf_blocks * effective index selectivity.

* LIO is 5 when access index with column ID on first position
select 2 + Ceil(1520 * 0.2 * (40 * 100)/500000) from dual;

* LIO is 15 when access index with date_col on first position,
select 2 + Ceil(1520 * (40 * 100)/500000) from dual;


"Because as soon as we have a range scan on a column used somewhere in the
middle of an index definition or fail to supply a test on such a column, the predicates on later
columns don’t restrict the selection of index leaf blocks that we have to examine."

From page74 of book Cost Based Oracle Fundamentals

For 2nd query, the effective index selectivity has to be calculated from the predicate on just the
date_col column. Because the test on date_col is range-based, the predicates on ID do not
restrict the number of index leaf blocks we have to walk.

Note
----
In plan output, Buffers = Logical Reads.

*******/

Setup
-----

create table date_index_sel
(
id number,
date_col date,
n2 number
)
nologging;

insert --+ append
into date_index_sel(id,date_col)
select mod(rownum,5), sysdate + rownum/100
from dual
connect by level <= 500000;

commit;

create index date_index_sel_i1 on date_index_sel(id,date_col) nologging;
create index date_index_sel_i2 on date_index_sel(date_col,id) nologging;

exec dbms_stats.gather_table_stats(user,'date_index_sel');

select INDEX_NAME, BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS, CLUSTERING_FACTOR, NUM_ROWS
from user_indexes
where index_name like 'DATE_INDEX_SEL%';

INDEX_NAME BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR NUM_ROWS

DATE_INDEX_SEL_I1 2 1520 500000 5475 500000
DATE_INDEX_SEL_I2 2 1520 500000 1095 500000


set serveroutput off
set linesize 370
set pagesize 0

REM TEST1, date_col is NOT the first index column, range scan on date_col,

select --+ index(a,date_index_sel_i1) gather_plan_statistics
count(*) from date_index_sel a
where id=3 
and date_col between sysdate + 100 and sysdate + 140;

--SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL, 'basic iostats memstats last partition'));
SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL, 'basic cost allstats last partition'));

Plan hash value: 341633530

-------------------------------------------------------------------------------
| Id  | Operation          | Name              |Cost (%CPU)| A-Rows | Buffers |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                   |    3 (100)|      1 |       5 |
|   1 |  SORT AGGREGATE    |                   |           |      1 |       5 |
|*  2 |   FILTER           |                   |           |    800 |       5 |
|*  3 |    INDEX RANGE SCAN| DATE_INDEX_SEL_I1 |    3   (0)|    800 |       5 |
-------------------------------------------------------------------------------

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

   2 - filter(SYSDATE+100<=SYSDATE+140)
   3 - access("ID"=3 AND "DATE_COL">=SYSDATE+100 AND "DATE_COL"<=SYSDATE+140)

REM TEST2, date_col is the first index column,

select --+ index(a,date_index_sel_i2) gather_plan_statistics
count(*) from date_index_sel a
where id=3 
and date_col between sysdate + 100 and sysdate + 140
;

SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL, 'basic cost iostats memstats last partition'));

Plan hash value: 1386725119

-------------------------------------------------------------------------------
| Id  | Operation          | Name              | Cost (%CPU)|A-Rows | Buffers |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                   |    15 (100)|     1 |      15 |
|   1 |  SORT AGGREGATE    |                   |            |     1 |      15 |
|*  2 |   FILTER           |                   |            |   800 |      15 |
|*  3 |    INDEX RANGE SCAN| DATE_INDEX_SEL_I2 |    15   (0)|   800 |      15 |
-------------------------------------------------------------------------------

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

   2 - filter(SYSDATE+100<=SYSDATE+140)
   3 - access("DATE_COL">=SYSDATE+100 AND "ID"=3 AND "DATE_COL"<=SYSDATE+140)
       filter("ID"=3)

***.

row level timestamp implementation

Goal
----
Identify latest row in one Bulk SQL INSERT,
e.g. JDBC Update Batching, Batch Value is 1000,
or PL/SQL FORALL statement.

Case
----
One Bulk SQL insert 1000 rows, select from an Oracle external table,
De-duplicate by primary key, identify latest row in the file.

Constraint: All rows get same SCN or timestamp value if they are in one INSERT SQL.

Solution
-- -- ---
plain simple Oracle Sequence, for row update timestamp implementation.

drop table t purge;
create table t(id number, ts timestamp(6), seq number);
drop sequence s;
create sequence s;

declare
lt_id dbms_sql.number_table;
begin
select rownum bulk collect into lt_id
from dual
connect by level <= 10;

forall i in 1..lt_id.count
insert into t(id, ts)
values(lt_id(i), systimestamp);

commit;

end;
/

select id, ts from t;


--
-- With update_change_sequence

declare
lt_id dbms_sql.number_table;
begin
select rownum bulk collect into lt_id
from dual
connect by level <= 10;

forall i in 1..lt_id.count
insert into t(id, ts, seq)
values(lt_id(i), systimestamp, s.nextval );

commit;

end;
/

select id, seq, ts from t;

Tune SQL with SQL plan baseline

Goal
----
Tune SQL plan without change the source code(SQL text),

A:\SQL\CBO\tune_sql_by_baseline01.sql

| Modification History:
| Date Who What
| 11-Nov-2009: Charlie(Yi): Create the file,
| 18-Nov-2009: Charlie(Yi): Show binding variable value,


Solution
--------
* tune SQL execution plan by SQL plan baseline, load_plans_from_cursor_cache to
load the hint/changed resulting plan(s) into the SQL plan baseline of the original SQL statement,

Step
----

REM display current "bad" sql plan,

SQL>
set serveroutput off
set linesize 300

var l_bad_sql_id varchar2(30);
var l_sql_text varchar2(200);
exec :l_bad_sql_id := 'cbzfmb3mc9047';
exec :l_sql_text := q'{FROM WANTS WHERE CLIENTID = :1 AND ROWDF = 'F' AND UPPERTITLE >= :2}';

REM 2nd option to get SQL_ID,
SELECT b.executions EXEC, INST_ID, b.sql_id, CHILD_NUMBER,
ROUND(b.ROWS_PROCESSED/b.executions,1) rows_per,
ROUND(b.BUFFER_GETS/b.executions,1) gets_per,
ROUND(b.ELAPSED_TIME/b.executions/1000000,3) ela_per,
b.module, parse_calls, first_load_time, disk_reads,
b.sql_text
FROM gv$sql b
WHERE UPPER(b.sql_text) LIKE UPPER('%'||:l_sql_text||'%')
AND b.executions > 0
and b.sql_text not like 'SELECT b.executions EXEC%'
ORDER BY b.executions DESC;


select * from table(dbms_xplan.display_awr(sql_id=>:l_bad_sql_id, format=>'basic rows cost partition note last'));

or

select * from table(dbms_xplan.display_cursor(sql_id=>:l_bad_sql_id, format=>'basic rows cost partition note last'));


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------

SQL_ID cbzfmb3mc9047
--------------------
SELECT * FROM WANTS WHERE CLIENTID = :1 AND ROWDF = 'F' AND UPPERTITLE >= :2 ORDER BY UPPERTITLE;

Plan hash value: 1289812832

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 363 (100)|
| 1 | TABLE ACCESS BY INDEX ROWID| WANTS | 38233 | 363 (0)|
| 2 | INDEX RANGE SCAN | WANTSUPPERTITLE | 530 | 12 (0)|
----------------------------------------------------------------------------

REM - show binding variable value,
select name, position,datatype_string, last_captured, value_string, b.child_address, b.address
from v$sql_bind_capture b
where sql_id = :l_bad_sql_id
and was_captured <> 'NO';


REM create sql plan baseline for the SQL,

declare
l_pls number;
begin
l_pls := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
sql_id => :l_bad_sql_id,
plan_hash_value => NULL,
fixed => 'NO',
enabled => 'NO'
);
u$err.trc('Number of plans loaded:'||l_pls);

end;
/

REM get sql text, sql_handle
select sql_text, sql_handle, plan_name, enabled, accepted, fixed, autopurge from dba_sql_plan_baselines
where sql_text like '%'||:l_sql_text||'%';

var l_sql_handle varchar2(30);
exec :l_sql_handle := 'SYS_SQL_ce7588e9ccd926d0';

REM change sql hint to tune SQL execution plan,
set serveroutput off
set linesize 300
var l_sql_id varchar2(30);
var l_sql_plan_hash_value number;
var l_sql_child_number number;

var B1 number;
exec :B1 := 123;
var B2 varchar2(30);
exec :B2 := '1S & 2ND KINGS';

SELECT --+ index_rs_asc(wants WANTSCLIENTID)
* FROM WANTS WHERE CLIENTID = :B1 AND ROWDF = 'F' AND UPPERTITLE >= :B2 ORDER BY UPPERTITLE;

REM capture new SQL_ID and plan_hash_value,
begin
with m as (select sid from v$mystat where rownum <= 1)
select prev_sql_id, prev_child_number
into :l_sql_id, :l_sql_child_number
from m, v$session s
where s.sid = m.sid;
select plan_hash_value into :l_sql_plan_hash_value from v$sql
where sql_id = :l_sql_id
and rownum <= 1;

end;
/

print :l_sql_id;
print :l_sql_plan_hash_value;
print :l_sql_child_number;

REM verify the new SQL plan,
select * from table(dbms_xplan.display_cursor(sql_id=>:l_sql_id, cursor_child_no=>:l_sql_child_number,
format=>'basic rows cost partition note last'));

REM load new sql plan into plan baseline, with same sql_handle,
declare
l_pls number;
begin

l_pls := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
sql_id => :l_sql_id,
plan_hash_value => :l_sql_plan_hash_value,
sql_handle => :l_sql_handle,
fixed => 'NO',
enabled => 'NO'
);
u$err.trc('Number of plans:'||l_pls);
end;
/

REM display sql plan baseline,
SELECT *
FROM table(dbms_xplan.display_sql_plan_baseline(
sql_handle => :l_sql_handle,
format => 'typical'
))
;


PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------
SQL handle: SYS_SQL_ce7588e9ccd926d0
SQL text: SELECT * FROM WANTS WHERE CLIENTID = :1 AND ROWDF = 'F' AND UPPERTITLE
>= :2 ORDER BY UPPERTITLE
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_ccd926d0604f1815
Enabled: NO Fixed: NO Accepted: YES Origin: MANUAL-LOAD
--------------------------------------------------------------------------------

Plan hash value: 1289812832

-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 217 | 17021 (1)| 00:03:08 |
|* 1 | TABLE ACCESS BY INDEX ROWID| WANTS | 1 | 217 | 17021 (1)| 00:03:08 |
|* 2 | INDEX RANGE SCAN | WANTSUPPERTITLE | 19756 | | 361 (0)| 00:00:04 |
-----------------------------------------------------------------------------------------------

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

1 - filter("CLIENTID"=TO_NUMBER(:1) AND "ROWDF"='F')
2 - access("UPPERTITLE">=:2)

--------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_ccd926d0ff894fe1
Enabled: YES Fixed: YES Accepted: YES Origin: MANUAL-LOAD
--------------------------------------------------------------------------------

Plan hash value: 1555025420

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 217 | 8 (13)| 00:00:01 |
| 1 | SORT ORDER BY | | 1 | 217 | 8 (13)| 00:00:01 |
|* 2 | TABLE ACCESS BY INDEX ROWID| WANTS | 1 | 217 | 7 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | WANTSCLIENTID | 7 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

2 - filter("UPPERTITLE">=:2 AND "ROWDF"='F')
3 - access("CLIENTID"=TO_NUMBER(:1))


REM enable new plan in plan baseline,
REM changes an attribute of a single plan or all plans,
declare
l_pls number;
begin

l_pls := dbms_spm.ALTER_SQL_PLAN_BASELINE(
sql_handle => :l_sql_handle,
plan_name => 'SYS_SQL_PLAN_ccd926d0ff894fe1',
attribute_name => 'enabled',
attribute_value => 'YES'
);
u$err.trc('Number of plans:'||l_pls);
end;
/

REM fixed new plan in plan baseline,
REM if it is realy a good plan,
declare
l_pls number;
begin

l_pls := dbms_spm.ALTER_SQL_PLAN_BASELINE(
sql_handle => :l_sql_handle,
plan_name => 'SYS_SQL_PLAN_ccd926d0ff894fe1',
attribute_name => 'fixed',
attribute_value => 'YES'
);
u$err.trc('Number of plans:'||l_pls);
end;
/

REM verify attribute ENABLED and FIXED,
select sql_text, sql_handle, plan_name, enabled, accepted, fixed, autopurge
from dba_sql_plan_baselines
where sql_handle = :l_sql_handle;


REM verify new plan is picked up by CBO optimizer,
explain plan for
SELECT * FROM WANTS WHERE CLIENTID = :1 AND ROWDF = 'F' AND UPPERTITLE >= :2 ORDER BY UPPERTITLE;

select * from table(dbms_xplan.display());

single INSERT vs array BULK INSERT

Goal
----
Compare the performance of single INSERT vs array BULK INSERT.

Result
------
After you run benchmark a few more times, you will get about same result as I saw.

Bulk DML spend 20% more CPU time,
Latches are about same. No big difference.
db block gets, physical reads, consistent gets, session logical reads are all about same.
Bulk DML take 2MB more session pga memory is expected, to cache association array (1200 bytes * 1000 rows) in shared_pool memory. no big deal.

Conclusion
-----------
Consider single SQL first.
Only when the data process logic is too complicated, we may go with Bulk DML, there will be a little performance impact, e.g. 20% more CPU used.

Notes: Thanks for Tom Kyte pointing out the design strategy.
(http://mujiang.blogspot.com/2011/06/question-to-asktom-in-oracle-magazine.html)

Reference
---------
Oracle® Database PL/SQL Language Reference
11g Release 1 (11.1)
12 Tuning PL/SQL Applications for Performance

Reducing Loop Overhead for DML Statements and Queries with Bulk SQL.

* Running One DML Statement Multiple Times (FORALL Statement)
* Retrieving Query Results into Collections (BULK COLLECT Clause)

Setup
-----

drop table listing_wide purge;
create table listing_wide
(
vendor_id   number(10,0)  Not Null ,
listing_id   number(20,0)  Not Null ,
match_col               varchar2(1)     Not Null ,
vendor_listing_id  VarChar2(40)  Not Null ,
hash_lite         varchar2(32) ,
hash_code  VarChar2(32) ,
qty   number(5,0)  Not Null  ,
price  number(10,2) Not Null  ,
create_date Date default sysdate Not Null ,
update_date Date default sysdate Not Null ,
bsa_vend  Varchar2(30)     ,
isbn_raw  Varchar2(25)             ,
subject  Varchar2(2000)    ,
author   varchar2(750)     ,
title  varchar2(750)   ,
vend_catalog varchar2(255)   ,
description varchar2(4000) 
)
NOLOGGING
PARTITION BY LIST (vendor_id) 
(  
PARTITION v2134736  values( 2134736 ),
PARTITION v8408184  values( 8408184 ),
PARTITION v1  values( 1 ),
PARTITION v2  values( 2 ),
PARTITION vendor_NULL  VALUES (NULL),
PARTITION vendor_small VALUES (DEFAULT)
)
NOCOMPRESS 
NOPARALLEL
ENABLE ROW MOVEMENT;

drop table listing_purge;
create table listing_purge
NOLOGGING
PARTITION BY LIST (vendor_id) 
(  
PARTITION v2134736  values( 2134736 ),
PARTITION v8408184  values( 8408184 ),
PARTITION v1  values( 1 ),
PARTITION v2  values( 2 ),
PARTITION vendor_NULL  VALUES (NULL),
PARTITION vendor_small VALUES (DEFAULT)
)
as
select a.* 
from listing_wide a
where 1=0;

alter table listing_purge modify (
create_date Date default sysdate ,
update_date Date default sysdate 
);


Test
----

truncate table listing_wide;
truncate table listing_purge;

insert /*+ append */ into listing_purge 
(listing_id, vendor_id, price, qty,hash_lite, hash_code, vendor_listing_id, author, title,subject,isbn_raw, bsa_vend
,match_col)
select
rownum listing_id, 
mod(rownum,2) + 1 vendor_id,
1.11 price,
10 qty,
rpad('x',31,'x') hash_lite,
rpad('x',31,'x') hash_code,
rownum vendor_listing_id,
rpad(rownum,400,'x') author,
rpad('x',400,'x') title,
rpad('x',300,'x') subject,
rownum isbn_raw,
mod(rownum,100)||','||mod(Round(rownum/10),100) bsa_vend
,'x' match_col
from dual
connect by level <= 80100;

commit;

set serveroutput on
set linesize 200
begin
tmp_benchmark;
end;
/

select vendor_id, count(*) from listing_wide
group by vendor_id;

select vendor_id, count(*) from listing_purge
group by vendor_id;


Code
----
CREATE OR REPLACE procedure tmp_benchmark
as
l_cnt number := 0; l_upd_rows number := 0;
l_fetch_limit PLS_INTEGER := 1000;

cursor c2 is
select listing_id, vendor_id, price, qty,hash_lite, hash_code, vendor_listing_id, author, title,subject,isbn_raw, bsa_vend, match_col
from listing_purge partition (v2);

TYPE listing_purge_tab is table of c2%rowtype;
lt_book listing_purge_tab;

BEGIN

runStats_pkg.rs_start;

INSERT INTO listing_wide
(listing_id, vendor_id, price, qty,hash_lite, hash_code, vendor_listing_id, author, title,subject,isbn_raw, bsa_vend, match_col)
select listing_id, vendor_id, price, qty,hash_lite, hash_code, vendor_listing_id, author, title,subject,isbn_raw, bsa_vend, match_col
from listing_purge partition (v1);

commit;

runStats_pkg.rs_middle;

-- Open cursor 
OPEN c2;

LOOP
fetch c2 BULK COLLECT INTO lt_book LIMIT l_fetch_limit;

FORALL j IN lt_book.FIRST..lt_book.LAST
INSERT into listing_wide
(listing_id, vendor_id, price, qty,hash_lite, hash_code, vendor_listing_id, author, title,subject,isbn_raw, bsa_vend, match_col)
values (lt_book(j).listing_id, lt_book(j).vendor_id, lt_book(j).price, lt_book(j).qty, lt_book(j).hash_lite, lt_book(j).hash_code, 
lt_book(j).vendor_listing_id, lt_book(j).author, lt_book(j).title, lt_book(j).subject, lt_book(j).isbn_raw, lt_book(j).bsa_vend
, lt_book(j).match_col)
;

l_upd_rows := l_upd_rows + SQL%RowCount;

exit when lt_book.COUNT < l_fetch_limit;
--exit when c2%notfound;

END LOOP;
close c2;

commit;

runStats_pkg.rs_stop;

EXCEPTION
WHEN others THEN
--u$err.err;
raise;
END;
/

RunStats
--------
Run1 ran in 669 hsecs
Run2 ran in 914 hsecs
run 1 ran in 73.19% of the time

Name                                  Run1        Run2        Diff
STAT...parse count (hard)                2           3           1
LATCH.DML lock allocation              328         327          -1
STAT...sorts (memory)                    1           0          -1
STAT...parse time cpu                    2           0          -2
LATCH.internal temp table obje           4           2          -2
LATCH.shared pool simulator              6           8           2
LATCH.ASM db client latch                4           6           2
STAT...enqueue releases              1,222       1,224           2
LATCH.space background task la           7           5          -2
STAT...redo log space requests           0           2           2
STAT...Heap Segment Array Upda         146         148           2
STAT...Heap Segment Array Inse          12          14           2
STAT...HSC Heap Segment Block       50,222      50,224           2
STAT...index scans kdiixs1              98          96          -2
STAT...enqueue requests              1,220       1,224           4
STAT...application wait time             0           4           4
STAT...physical read total mul         153         148          -5
STAT...redo ordering marks           1,121       1,126           5
STAT...messages sent                    97         105           8
STAT...session logical reads       147,017     147,029          12
STAT...commit cleanouts              3,364       3,376          12
STAT...consistent changes               24          38          14
STAT...db block changes            154,297     154,313          16
LATCH.Consistent RBA                    96          80         -16
STAT...parse time elapsed               67          51         -16
LATCH.lgwr LWN SCN                      97          78         -19
STAT...redo entries                102,505     102,524          19
STAT...redo buffer allocation            0          22          22
LATCH.post/wait queue                    1          31          30
STAT...recursive cpu usage              91         121          30
STAT...redo log space wait tim           0          30          30
STAT...CPU used by this sessio          91         125          34
LATCH.session allocation             7,536       7,574          38
LATCH.In memory undo latch               9          48          39
STAT...physical read total IO          275         235         -40
LATCH.row cache objects              2,688       2,642         -46
STAT...execute count                   261         310          49
STAT...consistent gets              19,683      19,632         -51
LATCH.shared pool                      138         196          58
STAT...db block gets               127,334     127,397          63
LATCH.undo global data               8,608       8,537         -71
STAT...active txn count during       8,180       8,107         -73
STAT...recursive calls               3,858       3,941          83
LATCH.enqueues                       2,034       2,132          98
STAT...user I/O wait time              564         670         106
LATCH.enqueue hash chains            2,525       2,634         109
STAT...table scan rows gotten       50,445      50,315        -130
LATCH.MinActiveScn Latch            16,931      16,796        -135
LATCH.redo allocation                  295         467         172
STAT...physical reads               10,010      10,194         184
STAT...Number of read IOs issu           0         184         184
LATCH.cache table scan latch           231          19        -212
STAT...Elapsed Time                    669         920         251
STAT...undo change vector size   3,185,292   3,185,592         300
LATCH.redo writing                     295         709         414
LATCH.multiblock read objects          464          38        -426
LATCH.object queue header heap           3         451         448
LATCH.active checkpoint queue            2         457         455
LATCH.messages                         368       1,250         882
LATCH.cache buffer handles          15,010      13,714      -1,296
STAT...free buffer inspected        10,054      11,522       1,468
LATCH.cache buffers lru chain       20,881      22,578       1,697
LATCH.simulator hash latch          13,132      10,817      -2,315
STAT...hot buffers moved to he         560       3,105       2,545
STAT...IMU Redo allocation siz       8,236      11,044       2,808
STAT...dirty buffers inspected           1       4,860       4,859
LATCH.checkpoint queue latch           201       7,974       7,773
LATCH.cache buffers chains         545,026     536,670      -8,356
LATCH.object queue header oper      34,953      44,292       9,339
STAT...physical reads cache pr       9,735         133      -9,602
STAT...free buffer requested        20,720      10,987      -9,733
STAT...consistent gets from ca      10,729         959      -9,770
STAT...physical reads cache         10,010         184      -9,826
STAT...physical reads direct             0      10,010      10,010
STAT...consistent gets direct            0      10,010      10,010
STAT...consistent gets from ca      19,683       9,622     -10,061
STAT...redo size                78,097,020  78,123,668      26,648
STAT...IMU undo allocation siz      52,228         908     -51,320
STAT...physical read total byt  82,001,920  83,509,248   1,507,328
STAT...physical IO disk bytes   82,001,920  83,509,248   1,507,328
STAT...cell physical IO interc  82,001,920  83,509,248   1,507,328
STAT...physical read bytes      82,001,920  83,509,248   1,507,328
STAT...session pga memory                0   2,162,688   2,162,688
STAT...Effective IO time                 0   6,195,022   6,195,022

Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
672,749     681,506       8,757     98.72%

PL/SQL procedure successfully completed.

Benchmark row-by-row and set based process

Goal
----
Compare the performance benchmark for row-by-row update and single SQL set based update.
The benchmark matrix supply supported information to Product Manager to prove why we should move some
row-by-row "real time"(that can be near real time) processes to Asynchronous set based bulk process.

file://T:/NextAction/benchmark_update_listing.sql

Solution
--------
Generate testing data, update some rows, benchmark with RunStats package.

Result
------
From benchmark, you can see

- Set based process vs. row-by-row process, commit out the loop.
Row-by-row process takes 3 times longer Elapsed Time, 3 times more CPU, 3 times more logical I/O,
and use 258% more Latches, latches are a type of lock, locks are serialization devices,
serialization devices inhibit scalability.

- Set based process vs. row-by-row process, commit in the loop.
Row-by-row process takes 5 times longer Elapsed Time, 5 times more CPU, 5 times more logical I/O,
and use 1,019.26% more Latches.

- If we introduce blocking row-by-row process from OIMS, by introducing more latches from different concurrent users,
it will be even worse.
A latch is a serialization device, the more people that request the same latch at the some time necessarily implies
"longer runtimes" - as the number of concurrent users go up, so will the observed run times and cpu times
(as you spin on the latch trying to get it), as we experience contention.

So,

SQL SET BASED BULK PROCESSING SLOW BY SLOW

You did good by turning slow by slow into BULK.

You could have done better by turning BULK into SET OPERATION.


Stats
-----

*) commit out the loop

Run1 ran in 79 hsecs
Run2 ran in 25 hsecs
run 1 ran in 316% of the time

Name                                              Run1          Run2          Diff
STAT...db block changes                          4,555         4,568            13
STAT...db block gets                             2,418         2,444            26
STAT...Elapsed Time                                 80            26           -54
STAT...CPU used by this session                     81            26           -55
LATCH.simulator hash latch                         308           181          -127
STAT...table scan rows gotten                    2,728         2,222          -506
STAT...redo size                             5,841,592     5,842,304           712
LATCH.checkpoint queue latch                     2,223           102        -2,121
STAT...execute count                             2,224             4        -2,220
STAT...sorts (memory)                            2,222             0        -2,222
LATCH.MinActiveScn Latch                         2,428           198        -2,230
LATCH.object queue header operation              2,862           393        -2,469
STAT...undo change vector size               2,837,256     2,839,872         2,616
LATCH.cache buffers lru chain                    4,445           195        -4,250
STAT...session logical reads                     9,292         2,751        -6,541
STAT...consistent gets                           6,874           307        -6,567
LATCH.cache buffers chains                      23,525        12,415       -11,110

Run1 latches total versus runs -- difference and pct
Run1          Run2          Diff        Pct
36,466        14,118       -22,348    258.29%

PL/SQL procedure successfully completed.


*) commit in the loop

Run1 ran in 129 hsecs
Run2 ran in 24 hsecs
run 1 ran in 537.5% of the time

Name                                              Run1          Run2          Diff
STAT...CPU used by this session                    130            26          -104
STAT...Elapsed Time                                132            25          -107
LATCH.Consistent RBA                             1,260             8        -1,252
STAT...commit cleanouts                          2,224           100        -2,124
LATCH.redo writing                               3,782            25        -3,757
LATCH.cache buffers lru chain                    4,454           195        -4,259
STAT...db block changes                          8,916         4,572        -4,344
LATCH.session allocation                         4,481            52        -4,429
STAT...db block gets                             8,917         2,456        -6,461
STAT...consistent gets                           6,882           408        -6,474
STAT...recursive calls                           6,714            35        -6,679
LATCH.redo allocation                            8,225            29        -8,196
LATCH.In memory undo latch                       8,893            11        -8,882
LATCH.DML lock allocation                        8,891             7        -8,884
LATCH.undo global data                          11,136           129       -11,007
STAT...session logical reads                    15,799         2,864       -12,935
LATCH.enqueue hash chains                       13,351            25       -13,326
LATCH.cache buffers chains                      60,632        12,552       -48,080
STAT...undo change vector size               2,989,432     2,841,148      -148,284
STAT...redo size                             6,391,412     5,844,136      -547,276
STAT...IMU undo allocation size              4,193,320         1,080    -4,192,240

Run1 latches total versus runs -- difference and pct
Run1          Run2          Diff        Pct
145,815        14,306      -131,509  1,019.26%

PL/SQL procedure successfully completed.


Setup
-----

drop table listing_wide purge;
create table listing_wide
(
vendor_id   number(10,0)  Not Null ,
listing_id   number(20,0)  Not Null ,
vendor_listing_id  Varchar(40)  Not Null ,
hash_code  VarChar2(32)  Not Null  ,
qty   number(5,0)  Not Null  ,
price  number(10,2) Not Null  ,
currency  varchar2(3) default 'USD' Not Null,
listingtype  number(4,0) default 1001  Not Null,
book_lang varchar2(3) default ' ' Not Null ,
create_date Date default sysdate Not Null ,
update_date Date default sysdate Not Null ,
abe_cat0 varchar2(100)     ,
abe_cat1  varchar2(100)      ,
abe_cat2  varchar2(100)      ,
vend_bsa  Varchar2(30)     ,
abe_bsa  Varchar2(30)     ,
binding  varchar2(30)    ,
booktype varchar2(30)     ,
bookcondition varchar2(30)     ,
cat_providerid Number(5,0)    ,
cat_providerkey Varchar2(50)    ,
edition_num  number(10)      ,
edition_txt  varchar2(40)      ,
editor  varchar(255)    ,
illustrator varchar2(255)    ,
inscription varchar2(50)    ,
isbn_raw  Varchar2(25)             ,
isbn_vendor13 Number(13,0)     ,
isbn_matched13 Number(13,0)     ,
jacketcondition Varchar2(30)    ,
pictures Varchar2(255)    ,
printing  Varchar2(20)      ,
publisher_name  varchar2(750)      ,
publisher_year  Number(4)      ,
publisher_location  varchar2(750)     ,
item_size   Varchar2(50)     ,
subject  Varchar2(2000)     ,
synopsis Varchar2(255)    ,
author   varchar(750)      ,
title  varchar2(750)    ,
vend_cat varchar2(255)    ,
volume  Varchar2(50)    ,
weight  Varchar2(40)    ,
description varchar2(4000)
--, constraint listing_wide_pk primary key(listing_id)
)
-- TABLESPACE data_auto
NOLOGGING
PARTITION BY LIST (vendor_id) 
(  
PARTITION v2134736  values( 2134736 ),
PARTITION v8408184  values( 8408184 ),
PARTITION v999  values( 999 ),
PARTITION vendor_NULL  VALUES (NULL),
PARTITION vendor_small VALUES (DEFAULT)
)
NOCOMPRESS 
NOPARALLEL
ENABLE ROW MOVEMENT;


create index listing_wide_i_hs on listing_wide(vendor_listing_id, hash_code) local nologging;
create index listing_wide_i_id on listing_wide(listing_id) local nologging;

truncate table listing_wide;

insert /*+ append */ into listing_wide 
(listing_id, vendor_id, price, qty, hash_code, vendor_listing_id, author, title,description,isbn_raw, vend_bsa)
select
rownum listing_id, 
mod(rownum,8) vendor_id,
10.55 price,
30 qty,
rpad('x',31,'x') hash_code,
rownum vendor_listing_id,
rpad(rownum,400,'x') author,
rpad('x',400,'x') title,
rpad('x',300,'x') description,
rownum isbn_raw,
mod(rownum,100)||','||mod(Round(rownum/10),100) bsa
from dual
connect by level <= 100100;

commit;

select vendor_id, isbn_raw, hash_code, author
from listing_wide where rownum <= 3;

exec dbms_stats.gather_table_stats(user,'listing_wide');

drop table listing_file;

create table listing_file
PARTITION BY LIST (time_part_key)
SUBPARTITION BY LIST (match_col)
SUBPARTITION TEMPLATE 
(SUBPARTITION vendor_id_part  VALUES (1) ,
SUBPARTITION hash_code_part  VALUES (2) ,
SUBPARTITION listing_id_part VALUES (3) ,
SUBPARTITION other_part VALUES (DEFAULT)
)
(
PARTITION data_part_0 VALUES (0),
PARTITION data_part_1 VALUES (1),
PARTITION data_part_2 VALUES (2),
PARTITION data_part_3 VALUES (3),
PARTITION data_part_4 VALUES (4),
PARTITION data_part_5 VALUES (5),
--PARTITION dummy_null  VALUES (NULL),
PARTITION dummy_other VALUES (DEFAULT)
)
--tablespace data_auto
nologging
as
select a.* 
--, cast(null as timestamp(3)) update_date
, cast(null as number(2))    time_part_key
, 9 dest_queue
, 9 match_col
from listing_wide a
where 1=0;

alter table listing_file modify (
currency  varchar2(3) default 'USD' ,
listingtype  number(4,0) default 1001  ,
book_lang varchar2(3) default ' '  ,
create_date Date default sysdate  ,
update_date Date default sysdate 
);

alter table listing_file modify
(
update_date    default systimestamp,
time_part_key  default mod(floor(to_number(to_char(sysdate,'mi'))/10),10)
);

truncate table listing_file;

insert /*+ append */ into listing_file
(listing_id, vendor_id, price, qty, hash_code, vendor_listing_id, author, title,description,isbn_raw, vend_bsa
, match_col)
select
rownum listing_id, 
mod(rownum,8) vendor_id,
10.55 price,
30 qty,
rpad('x',31,'x') hash_code,
rownum vendor_listing_id,
rpad(rownum,400,'x') author,
rpad('x',400,'x') title,
rpad('x',300,'x') description,
rownum isbn_raw,
mod(rownum,100)||','||mod(Round(rownum/10),100) bsa,
3 match_col
from dual
connect by level <= 2222;

commit;

exec dbms_stats.gather_table_stats(user,'listing_file');

Test
----

set linesize 120
set serveroutput on

Begin
runstats_pkg.rs_start;

for s in (select * from listing_file)
loop
update  listing_wide d
set
d.price      = s.price    ,
d.qty       = s.qty    ,
d.hash_code      = s.hash_code   ,
d.vendor_listing_id = s.vendor_listing_id,
d.author      = s.author    ,
d.title      = s.title   ,
d.description     = s.description  ,
d.isbn_raw      = s.isbn_raw   ,
d.vend_bsa     = s.vend_bsa   
where s.vendor_id = d.vendor_id and s.listing_id = d.listing_id
;
--commit;

end loop;
commit;

runstats_pkg.rs_middle;

MERGE INTO listing_wide d
using listing_file s
on (s.vendor_id = d.vendor_id and s.listing_id = d.listing_id)
when matched then
update set
d.price      = s.price    ,
d.qty       = s.qty    ,
d.hash_code      = s.hash_code   ,
d.vendor_listing_id = s.vendor_listing_id,
d.author      = s.author    ,
d.title      = s.title   ,
d.description     = s.description  ,
d.isbn_raw      = s.isbn_raw   ,
d.vend_bsa     = s.vend_bsa   
;
commit;

runstats_pkg.rs_stop(5);
End;
/

Reference
---------
http://tkyte.blogspot.com/2006/10/slow-by-slow.html

• bulking it up with BULK COLLECT,  bulk-update them back into the database, using the FORALL statement with the UPDATE

http://www.oracle.com/technetwork/issue-archive/2011/11-may/o31asktom-354139.html

table stats tool script

Since I saw Ningoo show his dandy tool tbsql and made my mouth watering, and he was reluctant to share his favorite tool, so I have to create one myself.
The good news is he promised he will open source it in someday.

Purpose:
Display table and its index statistics by specify Owner and Table_Name.
It can help you investigate the SQL query optimizer issue, cooperate with 10053 trace tool. And it can do more...

Reference:
http://www.ningoo.net/html/2009/how_to_get_extent_id_from_rowid.html

Here is the script:

/*

-- File name:   table.sql
-- Goal: display table and index statistics by specify Owner and Table_Name
--
-- Author: Yi (Charlie) Zhu
-- update: 29-Jan-2009
-- Blog:   http://mujiang.blogspot.com/
--              
-- Usage:       @table [owner] [table_name]

29-Jan-2009: Charlie: Show popular value chart for height balance histogram


Reference
--
http://www.ningoo.net/html/2009/how_to_get_extent_id_from_rowid.html

*/

define owner='&1'
define table_name='&2'

set serveroutput off
set timing off
set verify off
set linesize 200
set define &

COLUMN SEGMENT_NAME FORMAT A40 HEADING 'Segment Name'
COLUMN SEGMENT_TYPE FORMAT A25 HEADING 'Segment Type'
COLUMN SEGMENT_SUBTYPE FORMAT A25 HEADING 'Segment SubType'
COLUMN Tablespace_Name FORMAT A30 HEADING 'Tablespace_Name'
COLUMN Partition_NAME FORMAT A25 HEADING 'Partition Name'
COLUMN subpartition_name FORMAT A22 HEADING 'SubPartition Name'
COLUMN INDEX_NAME FORMAT A30 HEADING 'INDEX Name'
COLUMN COLUMN_NAME FORMAT A30 HEADING 'COLUMN NAME'
COLUMN DATA_TYPE FORMAT A14 HEADING 'DATA TYPE'
COLUMN LOW_VALUE FORMAT A14 HEADING 'LOW_VALUE'
COLUMN HIGH_VALUE FORMAT A14 HEADING 'HIGH_VALUE'
COLUMN owner FORMAT A14
COLUMN table_name FORMAT A30
COLUMN HISTOGRAM FORMAT A9
COLUMN endpoint_actual_value format A30

COLUMN BLOCKS FORMAT 999,999,999 HEADING 'Blocks'
COLUMN MBytes FORMAT 999,999,999 HEADING 'MBytes'
COLUMN TOTAL_BYTES FORMAT 999,999 HEADING 'TotByte_MB'
COLUMN extents FORMAT 999,999
COLUMN LAST_ANALYZED FORMAT A11 HEADING 'LAST|ANALYZED'
COLUMN pop_chart FORMAT A11 HEADING 'Pop Chart'

COLUMN unf         FORMAT 999,999.99 HEADING 'unf_K'
COLUMN fs1         FORMAT 999,999.99 HEADING 'fs1_K'
COLUMN fs2         FORMAT 999,999.99 HEADING 'fs2_K'
COLUMN fs3         FORMAT 999,999.99 HEADING 'fs3_K'
COLUMN fs4         FORMAT 999,999.99 HEADING 'fs4_K'
COLUMN full        FORMAT 999,999.99 HEADING 'full_K'
COLUMN total_blocks    FORMAT 999,999.99 HEADING 'total_blocks_K'
COLUMN unused_blocks   FORMAT 999,999.99 HEADING 'unused_blocks_K'
COLUMN unfb        FORMAT 999,999 HEADING 'unfb_M'
COLUMN fs1b        FORMAT 999,999 HEADING 'fs1b_M'
COLUMN fs2b        FORMAT 999,999 HEADING 'fs2b_M'
COLUMN fs3b        FORMAT 999,999 HEADING 'fs3b_M'
COLUMN fs4b        FORMAT 999,999 HEADING 'fs4b_M'
COLUMN fullb        FORMAT 999,999 HEADING 'fullb_M'
COLUMN total_bytes     FORMAT 999,999 HEADING 'total_bytes_M'
COLUMN unused_bytes    FORMAT 999,999 HEADING 'unused_bytes_M'
COLUMN last_extf       FORMAT 999,999 HEADING 'last_extf_id'
COLUMN last_extb       FORMAT 9999999999 HEADING 'last_extb_id'
COLUMN lastusedblock/1024   FORMAT 9999999999 HEADING 'lastusedblock'

col num_rows new_value l_num_rows;


spool table_info.log

prompt table info

SELECT owner,table_name,pct_free,num_rows,blocks,chain_cnt,
avg_row_len,to_char(last_analyzed,'yyyy-mon-dd') last_analyzed,buffer_pool
FROM dba_tables
WHERE owner = (Upper('&&owner'))
and table_name IN (Upper('&&table_name'))
ORDER BY blocks desc;

prompt table stats

SELECT partition_name,subpartition_name,num_rows, blocks, empty_blocks, avg_space, chain_cnt, avg_row_len
FROM dba_tab_statistics
WHERE owner = (Upper('&&owner'))
and table_name IN (Upper('&&table_name'))
order by table_name,partition_position;

-- IOT come with Primary Key index name
select owner||'.'||segment_name segment_name,partition_name,round(bytes/1024/1024) mbytes,
 blocks,extents,segment_type,tablespace_name
from dba_segments
WHERE owner = (Upper('&&owner'))
and segment_name IN (Upper('&&table_name'))
;

prompt column data type

SELECT --OWNER,TABLE_NAME,COLUMN_ID,
 COLUMN_NAME,DATA_TYPE,DATA_LENGTH,DATA_PRECISION
FROM dba_tab_cols
WHERE owner = (Upper('&&owner'))
and TABLE_NAME IN (Upper('&&table_name'))
order by owner,table_name,column_name
;

prompt column stats

SELECT
 column_name AS "NAME",
 num_distinct AS "#DST",
 low_value,
 high_value,
 density AS "DENS",
 num_nulls AS "#NULL",
 avg_col_len AS "AVGLEN",
 histogram,
 num_buckets AS "#BKT"
FROM dba_tab_col_statistics
WHERE owner = (Upper('&&owner'))
  and TABLE_NAME IN (Upper('&&table_name'))
order by column_name
;

prompt column frequency histogram

SELECT column_name, endpoint_value, endpoint_actual_value, endpoint_number,
 endpoint_number - lag(endpoint_number,1,0) OVER (partition by column_name ORDER BY endpoint_number) AS frequency
FROM dba_tab_histograms
WHERE owner = (Upper('&&owner'))
and table_name = (Upper('&&table_name'))
and column_name in 
(select column_name FROM dba_tab_col_statistics 
 where owner = (Upper('&&owner'))
 and TABLE_NAME IN (Upper('&&table_name'))
 and histogram = 'FREQUENCY'
)
ORDER BY column_name, endpoint_number;

prompt column height-balanced histogram

SELECT column_name, endpoint_value, endpoint_number
, Max(endpoint_number) OVER (partition by column_name) Max_EPN
, endpoint_number - lag(endpoint_number,1,0) OVER (partition by column_name ORDER BY endpoint_number) AS popular_count
, &l_num_rows * (endpoint_number - lag(endpoint_number,1,0) OVER (partition by column_name ORDER BY endpoint_number))
  /(Max(endpoint_number) OVER (partition by column_name))  AS Est_Card
, rpad('.',endpoint_number - lag(endpoint_number,1,0) OVER (partition by column_name ORDER BY endpoint_number) - 1,'x') pop_chart
FROM dba_tab_histograms
WHERE owner = (Upper('&&owner'))
and table_name = (Upper('&&table_name'))
and column_name in 
(select column_name FROM dba_tab_col_statistics 
 where owner = (Upper('&&owner'))
 and TABLE_NAME IN (Upper('&&table_name'))
 and histogram = 'HEIGHT BALANCED'
)
ORDER BY column_name, endpoint_number;

prompt Index name,physical size,including columns and selectivity

SELECT m.INDEX_NAME,COLUMN_NAME,
 COLUMN_POSITION,DISTINCT_KEYS,LEAF_BLOCKS,NUM_ROWS, COLUMN_LENGTH
FROM DBA_INDEXES m,dba_ind_columns d
WHERE m.owner = (Upper('&&owner'))
AND m.INDEX_NAME = d.INDEX_NAME
AND m.OWNER = d.TABLE_OWNER
AND m.TABLE_NAME = d.TABLE_NAME
AND m.TABLE_NAME IN (Upper('&&table_name'))
ORDER BY index_name,column_position;

prompt index stats

SELECT
 index_name,
 blevel,
 leaf_blocks AS leaf_blks,
 distinct_keys AS dst_keys,
 num_rows,
 clustering_factor AS clust_fact,
 avg_leaf_blocks_per_key AS leaf_per_key,
 avg_data_blocks_per_key AS data_per_key
FROM dba_ind_statistics
WHERE owner = (Upper('&&owner'))
AND TABLE_OWNER = (Upper('&&owner'))
AND TABLE_NAME IN (Upper('&&table_name'));

VARIABLE total_blocks NUMBER
VARIABLE total_bytes NUMBER
VARIABLE unused_blocks NUMBER
VARIABLE unused_bytes NUMBER
VARIABLE last_extf NUMBER
VARIABLE last_extb NUMBER
VARIABLE lastusedblock NUMBER
begin
 DBMS_SPACE.UNUSED_SPACE(Upper('&&owner'), Upper('&&table_name'), 'TABLE',:total_blocks,
    :total_bytes,:unused_blocks,:unused_bytes,:last_extf,
    :last_extb,:lastusedblock);
end;
/

select 
:total_bytes  /1024/1024 total_bytes  ,
:unused_bytes /1024/1024 unused_bytes ,
:total_blocks /1024 total_blocks ,
:unused_blocks/1024 unused_blocks,
:lastusedblock/1024 lastusedblock     ,
:last_extf          last_extf     ,
:last_extb          last_extb
from dual;

variable unf number; 
variable unfb number; 
variable fs1 number; 
variable fs1b number; 
variable fs2 number; 
variable fs2b number; 
variable fs3 number; 
variable fs3b number; 
variable fs4 number; 
variable fs4b number; 
variable full number; 
variable fullb number; 

-- shows the space usage of data blocks under the segment High Water Mark

begin 
dbms_space.space_usage(Upper('&&owner'),Upper('&&table_name'),
                        'TABLE', 
                        :unf,:unfb,
                        :fs1,:fs1b,
                        :fs2,:fs2b,
                        :fs3,:fs3b,
                        :fs4,:fs4b,
                        :full,:fullb);
end; 
/ 

select 
 :unf /1024 unf
,:fs1 /1024 fs1
,:fs2 /1024 fs2
,:fs3 /1024 fs3
,:fs4 /1024 fs4
,:full /1024 full
,:unfb /1024/1024 unfb
,:fs1b /1024/1024 fs1b
,:fs2b /1024/1024 fs2b
,:fs3b /1024/1024 fs3b
,:fs4b /1024/1024 fs4b
,:fullb /1024/1024 fullb
from dual;

spool off

/*
-- unf: unformatted_blocks

print unf ; 
print unfb ; 
print fs4 ; 
print fs4b; 
print fs3 ; 
print fs3b; 
print fs2 ; 
print fs2b; 
print fs1 ; 
print fs1b; 
print full; 
print fullb; 


-- Returns the list of segments that are associated with the object
select * from Table(
DBMS_SPACE.OBJECT_DEPENDENT_SEGMENTS(
   objowner => Upper('&&owner'),
   objname  => Upper('&&table_name'),
   partname => Null,
   objtype  => 1)
);

SQL> print

TOTAL_BLOCKS  TOTAL_BYTES UNUSED_BLOCKS UNUSED_BYTES   LASTEXTF  LAST_EXTB LASTUSEDBLOCK
------------  ----------- ------------- ------------ ---------- ---------- -------------
        1536     12582912             0            0          5       1545           128


       UNF       UNFB
---------- ----------
         0          0

FreeSpace1: 0 to 25% free space
FreeSpace2: 25 to 50% free space
...

       FS1        FS2        FS3        FS4       FULL
---------- ---------- ---------- ---------- ----------
       386         25         16        945        132


      FS1B       FS2B       FS3B        FS4B      FULLB
---------- ---------- ----------  ---------- ----------
   3162112     204800     131072     7741440    1081344

*/