木匠的天空 Database Architect and Developer

Question to AskTom on Oracle Magazine

2011-06-07T14:31:00.000-07:00

Just threw a random question to Tom after read his column in Oracle Magazine, to my surprise, Tom replied, and it made me happy for a couple weeks.
AskTom link www.oracle.com/technetwork/issue-archive/2011/11-may/o31asktom-354139.html

My question to Tom,

Just read you Q&A on "Bulking Up" on May 2011 Oracle Magazine.

It looks like UPDATE is different than INSERT when comparing PL/SQL BULK to single SQL.

Single SQL INSERT is about same as BULK INSERT when comparing the resource usage,
But based on your benchmark, Single SQL UPDATE is way more better than array bulk UPDATE.
I'll build a test case and run benchmark of UPDATE myself and blog it later.

FYI, single SQL INSERT vs. array BULK INSERT.
http://mujiang.blogspot.com/2009/06/single-insert-vs-array-bulk-insert.html

Replied by Tom, on 31-May-11 12:46 PM, opubedit wrote:

Charlie,

With the single insert, you can easily use /*+ APPEND */ - allowing for direct path load, basic compression (comes with enterprise edition), and redo/undo generation being skipped.

with the bulk insert - until 11.2 - you cannot. In 11.2 you can use the append_values hint to do the direct path load, but then you have to size your batches very carefully (since direct pathing never reuses existing space, you'll have "chunks of data")

Further, you show a measurable different in cpu/elapsed time even without these other advantages.

I will stick with my advice of "do it in a single sql statement if you can, if not do it in as little procedural code as possible". Unless there is no way to do it in a single insert as select - there should be NO procedural code whatsoever.

My reply:

Hi Tom,

Thanks for the reply. I implement your mantra all the time, I’m thrifty to develop procedure code out of single SQL.

Context is the king.

. Our data process logic cannot be done in single SQL.

.. We will update different target table columns based different comparison scenarios, with different source data,

(I asked a question on AskTom, about how to only update the columns with changed value. J

e.g. 70% of source data only update book price or qty.

it’s one of the update scenario. )

.. We will insert intermediate processed data to many staging tables based the each comparison scenarios,

.. I tried multi-table INSERT, it still couldn’t solve our problem.

. Our primary database is forced logging to support standby database.

So, I did a benchmark to see how the bulk INSERT can be worse. The bulk UPDATE vs. single SQL UPDATE will be benchmarked too in near future.

Thanks,

Charlie 木匠 | Data Solution Architect/Developer

- simple is beautiful and please consider the environment before printing this e-mail.

Explore MongoDB

2011-05-10T15:39:00.000-07:00

Here we go,

http://mongly.com/tutorial/index

Bravo!

Hopefully you learnt something about MongoDB and Unicorns

There's a lot we didn't cover, like embedded documents, aggregation, indexes, management, etc

But the idea was to help get you confortable with the basics

Feel free to play around, insert new data, new combinations, and what not

Or, better yet, head over to the mongodb download page, grab the version for your OS and experiment on your local machine

The precompiled binaries available for Linux, Windows and OS X make MongoDB extremely easy to get started with

Don't forget to check out my free MongoDB ebook

You are now web scale

db.unicorns.find()

_id
name
loves
dob
weight
gender
vampires
4dc9bbfbbc7a0515660052c8
Aurora
["carrot","grape"]
1991-01-25T13:14:00Z
450
f
43
4dc9bbfbbc7a0515660052cb
Solnara
["apple","carrot","chocolate"]
1985-07-05T02:01:00Z
550
f
80
4dc9bbfbbc7a0515660052cc
Ayna
["strawberry","lemon"]
1998-03-08T08:30:00Z
733
f
40
4dc9bbfbbc7a0515660052cf
Leia
["apple","watermelon"]
2001-10-09T14:53:00Z
601
f
33
4dc9bbfbbc7a0515660052d1
Nimue
["grape","carrot"]
1999-12-20T16:15:00Z
540
f
49
4dc9bbfbbc7a0515660052d3
Lois
55
f
0
4dc9bbfbbc7a0515660052c7
Horny
["carrot","papaya","orange"]
1992-03-14T07:47:00Z
600
m
63
4dc9bbfbbc7a0515660052c9
Unicrom
["energon","redbull","orange"]
1973-02-10T22:10:00Z
984
m
182
4dc9bbfbbc7a0515660052ca
Roooooodles
["apple","orange"]
1979-08-18T18:44:00Z
575
m
99
4dc9bbfbbc7a0515660052cd
Kenny
["grape","lemon","orange"]
1997-07-02T10:42:00Z
690
m
39
4dc9bbfbbc7a0515660052ce
Raleigh
["apple","sugar","orange"]
2005-05-04T00:57:00Z
421
m
2
4dc9bbfbbc7a0515660052d0
Pilot
["apple","watermelon","orange"]
1997-03-27T05:03:00Z
650
m
54
4dc9bbfbbc7a0515660052d2
Dunx
["grape","watermelon","orange"]
1976-07-19T19:18:00Z
704
m
165

_id	name	loves	dob	weight	gender	vampires
4dc9bbfbbc7a0515660052c8	Aurora	["carrot","grape"]	1991-01-25T13:14:00Z	450	f	43
4dc9bbfbbc7a0515660052cb	Solnara	["apple","carrot","chocolate"]	1985-07-05T02:01:00Z	550	f	80
4dc9bbfbbc7a0515660052cc	Ayna	["strawberry","lemon"]	1998-03-08T08:30:00Z	733	f	40
4dc9bbfbbc7a0515660052cf	Leia	["apple","watermelon"]	2001-10-09T14:53:00Z	601	f	33
4dc9bbfbbc7a0515660052d1	Nimue	["grape","carrot"]	1999-12-20T16:15:00Z	540	f	49
4dc9bbfbbc7a0515660052d3	Lois			55	f	0
4dc9bbfbbc7a0515660052c7	Horny	["carrot","papaya","orange"]	1992-03-14T07:47:00Z	600	m	63
4dc9bbfbbc7a0515660052c9	Unicrom	["energon","redbull","orange"]	1973-02-10T22:10:00Z	984	m	182
4dc9bbfbbc7a0515660052ca	Roooooodles	["apple","orange"]	1979-08-18T18:44:00Z	575	m	99
4dc9bbfbbc7a0515660052cd	Kenny	["grape","lemon","orange"]	1997-07-02T10:42:00Z	690	m	39
4dc9bbfbbc7a0515660052ce	Raleigh	["apple","sugar","orange"]	2005-05-04T00:57:00Z	421	m	2
4dc9bbfbbc7a0515660052d0	Pilot	["apple","watermelon","orange"]	1997-03-27T05:03:00Z	650	m	54
4dc9bbfbbc7a0515660052d2	Dunx	["grape","watermelon","orange"]	1976-07-19T19:18:00Z	704	m	165

What should the top five priorities be for new DBAs on the job?

2011-04-14T11:42:00.000-07:00

Thought it's helpful to read again and again.

Be on fire. You have to want to learn everything, do everything, consume everything. So you got the DBA job, now is not the time to rest in your new chair and enjoy your success. List out your goals and what you want to learn. A five year plan is a great idea.

Listen! Ask Questions! Be involved! Don't just sit back waiting for the create table requests.

When it comes to theory, don't believe anything you hear or read until you have tried it yourself. Database rule number one, in my opinion, is that no rule of thumb applies all the time.

If you are solely responsible for a database, make darned sure, before you leave your job on day one, that your database can be recovered. Nothing else matters if you can't get that database back.

Document everything.

And, a baker's half-dozen...

Learn to use your voice of authority. You are the DBA, and this database is your responsibility. As you learn the right way, and the wrong way to do things (like, say, database design), you need to be an advocate for best practices and for good design. If you succeed, you can enjoy the rapture of success. If they don't listen, you will get the joy of "I told you so."

http://searchoracle.techtarget.com/news/1077095/So-you-want-to-be-a-DBA

effective index selectivity 2

2010-10-29T11:39:00.001-07:00

File name...: A:\SQL\CBO\index_selectivity2.sql
Usage.......: @file_name
Description.: Why we need to avoid implicit data conversions on index columns in predicate.
Notes.......: index on VarChar2 columns
Parameters..:
Package.....: ._pkg

Modification History:

Date Who What
29-Oct-2010: Charlie(Yi): Create the file,

Goal
----
Show how implicit data conversions impact Effective Index Selectivity.
Prove that developers rely on implicit conversions is a bad practice.

Solution
--------
Create index on VarChar2 column(s), put number value in the predicate that use this index,
cause implicitly data type conversion.
E.g. TO_NUMBER(index_column) = n

Spec
----
cost = blevel +
ceiling(leaf_blocks * effective index selectivity) +
ceiling(clustering_factor * effective table selectivity)

Logical Reads(LIO) of index access = BLevel + leaf_blocks * effective index selectivity

Buffers is LIO in function dbms_xplan.display_cursor output.

See Page 62(91) of Book: [Cost-Based Oracle Fundamentals]

Result
------
cost = 1 , LIO = 2 , No data conversion
cost = 26, LIO = 28, data conversion, from char to number

Data flow
---------

Test case
---------
* ,
* ,

Setup
-----
See below SQL code.

Reference
---
QA..: You can send feedbacks or questions about this script to charlie.zhu1 gmail.com
blog: http://mujiang.blogspot.com/

ALTER SESSION SET STATISTICS_LEVEL=TYPICAL;

create table t
(
c1 VarChar2(5),
c2 VarChar2(7)
)
nologging;

create table t1
(
c1 varchar2(5),
n1 number(5)
)
nologging;

insert into t1(c1,n1) values('501',501);

insert /*+ append */ into t(c1,c2)
select mod(rownum,5), rownum
from dual
connect by level <=50000;

commit;

create unique index t_u1 on t(c1,c2) nologging;

exec dbms_stats.gather_table_stats(user,'t');
exec dbms_stats.gather_table_stats(user,'t1');

set serveroutput off
set linesize 200
ALTER SESSION SET STATISTICS_LEVEL=ALL;

REM -- use number value on VarChar2 index columns,

select * from t
where c1='1' and c2=(select n1 from t1);

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

----------------------------------------------------------
| Id  | Operation          | Name | Cost (%CPU)| Buffers |
----------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    29 (100)|      35 |
|*  1 |  INDEX RANGE SCAN  | T_U1 |    26   (0)|      35 |
|   2 |   TABLE ACCESS FULL| T1   |     3   (0)|       7 |
----------------------------------------------------------

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

   1 - access("C1"='1')
       filter(TO_NUMBER("C2")=)

REM -- use string value on VarChar2 index columns,

select * from t
where c1='1' and c2=(select c1 from t1);

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

----------------------------------------------------------
| Id  | Operation          | Name | Cost (%CPU)| Buffers |
----------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     4 (100)|       9 |
|*  1 |  INDEX UNIQUE SCAN | T_U1 |     1   (0)|       9 |
|   2 |   TABLE ACCESS FULL| T1   |     3   (0)|       7 |
----------------------------------------------------------

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

   1 - access("C1"='1' AND "C2"=)
.

Throttle big result set

2010-09-30T11:42:00.000-07:00

Goal

To process a big query result set from database, many a time the application server is limited by memory footprint, so we need to throttle the output chunk by chunk.

Solution
Q: How do you eat an elephant? A: One piece at a time.
The staging table will put into a tablespace with EXTENT MANAGEMENT LOCAL UNIFORM SIZE 4M. you may choose other uniform size based on your result size limit.
And then we will read data one extent at a time, after finish one extent, mark it as processed.
When client application crashed or failed, we can restart at failed extent.

We may slow make the chunk across 2 or many extents to make it more flexible.

Reference
On the use of DBMS_ROWID.rowid_create
[http://bit.ly/bphHJb]

Setup

drop table rowid_range_job purge;

create table rowid_range_job
tablespace data_auto nologging
as
select e.extent_id, e.block_id, e.block_id+blocks-1 block_id_end,
 cast(dbms_rowid.rowid_create( 1, o.data_object_id, e.file_id, e.block_id, 0 ) as urowid) min_rowid,
 cast(dbms_rowid.rowid_create( 1, o.data_object_id, e.file_id, e.block_id+e.blocks-1, 10000 ) as urowid) max_rowid,
 cast(o.object_name as varchar2(30)) table_name,
 cast(null as varchar2(30)) partition_name,
 cast('isbn_extract' as varchar2(30)) job_name,
 cast(null as date)         process_date,
 cast(null as number(1))    is_processed
from dba_extents e, user_objects o
 where o.object_name = 'T'
   and e.segment_name = 'T'
   and e.owner = user
   and e.segment_type = 'TABLE'
 order by e.extent_id
;

drop table t purge;
create table t
(
n1 number(10),
d1 date,
c1 varchar2(2000)
);

insert --+ append
 into t(n1,d1,c1)
select rownum, sysdate, rpad('a',1200)
from dual
connect by level <= 10000;
commit;

Generate and check rowid range split,

with data as
(
select e.extent_id, e.block_id, e.block_id+blocks-1,
       dbms_rowid.rowid_create( 1, o.data_object_id, e.file_id, e.block_id, 0 ) min_rowid,
       dbms_rowid.rowid_create( 1, o.data_object_id, e.file_id, e.block_id+e.blocks-1, 10000 ) max_rowid
  from dba_extents e,
       user_objects o
 where o.object_name = 'T'
   and e.segment_name = o.object_name
   and e.owner = user
   and e.segment_type = 'TABLE'
)
select extent_id, count(*) cnt
  from data, T t
 where t.rowid between data.min_rowid and data.max_rowid
 group by rollup (extent_id)
;

delete rowid_range_job where job_name = 'test_output_job';

INSERT INTO rowid_range_job
   SELECT   e.extent_id, e.block_id, e.block_id + blocks - 1 block_id_end,
            CAST
               (DBMS_ROWID.rowid_create (1,
                                         o.data_object_id,
                                         e.file_id,
                                         e.block_id,
                                         0
                                        ) AS UROWID
               ) min_rowid,
            CAST
               (DBMS_ROWID.rowid_create (1,
                                         o.data_object_id,
                                         e.file_id,
                                         e.block_id + e.blocks - 1,
                                         32000
                                        ) AS UROWID
               ) max_rowid,
            CAST (o.object_name AS VARCHAR2 (30)) table_name,
            CAST (NULL AS VARCHAR2 (30)) partition_name,
            CAST ('test_output_job' AS VARCHAR2 (30)) job_name,
            CAST (NULL AS DATE) process_date, 0 is_processed
       FROM dba_extents e, user_objects o
      WHERE o.object_name LIKE 'T'
        AND e.segment_name = o.object_name
        AND e.owner = USER
        AND e.segment_type = 'TABLE'
   ORDER BY o.object_name, e.extent_id;

commit;

Query rowid extent range split metadata

select EXTENT_ID,
BLOCK_ID,
BLOCK_ID_END,
MIN_ROWID,
MAX_ROWID
--,TABLE_NAME
--,PARTITION_NAME
--,JOB_NAME
--,PROCESS_DATE
--,IS_PROCESSED 
from invdb.rowid_range_job where job_name = 'test_output_job';

with data as
(
  select extent_id, block_id, block_id_end, min_rowid, max_rowid, table_name, job_name, process_date, is_processed
  from rowid_range_job
  where job_name = 'test_output_job'
    and table_name = 'T'
)
select extent_id, count(*) cnt
  from data, T t
 where t.rowid between data.min_rowid and data.max_rowid
 group by rollup (extent_id)
/

IN_EXISTS Correlated Subquery

2010-08-16T14:05:00.000-07:00

Goal

To show how IN and EXISTS Correlated Subquery works, and how they changed in Oracle 11g CBO SQL Engine. Also covered the NOT IN and NOT EXISTS difference.

Setup

drop table t1 cascade constraints purge;
drop table t2 purge;

create table t1(x number);
create table t2(y number);

insert into t1(x) values(1);
insert into t1(x) values(2);
insert into t1(x) values(Null);

insert into t2(y) values(1);
insert into t2(y) values(Null);
commit;

create index t1_x on t1(x);
create index t2_y on t2(y);

IN

t2 is small, index on t1.x, the subquery ( select y from T2 ) is small, probable full scan t2

Select * from T1 where x in
 ( select y from T2 );
=
select * 
  from t1, ( select distinct y from t2 ) t2
 where t1.x = t2.y;

Small t2,

set autot trace exp

Select /*+ cardinality(t1,50000) */ * from T1 where x in
 ( select /*+ cardinality(t2, 200) */ y from T2 );
------------------------------------------
| Id  | Operation         | Name | Rows  |
------------------------------------------
|   0 | SELECT STATEMENT  |      |   224 |
|   1 |  NESTED LOOPS     |      |   224 |
|   2 |   SORT UNIQUE     |      |   200 |
|   3 |    INDEX FULL SCAN| T2_Y |   200 |
|*  4 |   INDEX RANGE SCAN| T1_X |     1 |
------------------------------------------

Small t1

Select /*+ cardinality(t1,500) */ * from T1 where x in
 ( select /*+ cardinality(t2, 20000) */ y from T2 );
------------------------------------------
| Id  | Operation         | Name | Rows  |
------------------------------------------
|   0 | SELECT STATEMENT  |      |   500 |
|   1 |  NESTED LOOPS SEMI|      |   500 |
|   2 |   INDEX FULL SCAN | T1_X |   500 |
|*  3 |   INDEX RANGE SCAN| T2_Y | 20000 |
------------------------------------------

Exists

t1 is small, index on t2(y), full scan t1,

select * from t1 
 where exists ( select null from t2 where y = x );
=
   for x in ( select * from t1 )
   loop
      if ( exists ( select null from t2 where y = x.x )
      then 
         OUTPUT THE RECORD
      end if
   end loop

Small t2,

select /*+ cardinality(t1,50000) */ * from t1 
 where exists ( select /*+ cardinality(t2, 700) */ null from t2 where y = x );
------------------------------------------
| Id  | Operation         | Name | Rows  |
------------------------------------------
|   0 | SELECT STATEMENT  |      |   782 |
|   1 |  NESTED LOOPS     |      |   782 |
|   2 |   SORT UNIQUE     |      |   700 |
|   3 |    INDEX FULL SCAN| T2_Y |   700 |
|*  4 |   INDEX RANGE SCAN| T1_X |     1 |
------------------------------------------

Small t1

select /*+ cardinality(t1,500) */ * from t1 
 where exists ( select /*+ cardinality(t2, 70000) */ null from t2 where y = x );
------------------------------------------
| Id  | Operation         | Name | Rows  |
------------------------------------------
|   0 | SELECT STATEMENT  |      |   500 |
|   1 |  NESTED LOOPS SEMI|      |   500 |
|   2 |   INDEX FULL SCAN | T1_X |   500 |
|*  3 |   INDEX RANGE SCAN| T2_Y | 70000 |
------------------------------------------

If both the subquery and the outer table are huge — either might work as well as the
other — depends on the indexes and other factors.

Not in and Not exists are different.

select * from t1 outer
where outer.x not in (select y from t2);

is NOT the same as

select * from t1 outer
where not exists (select null from t2 where t2.y = outer.x);

UNLESS the expression "y" is not null. That said:

select * from t1 outer
where outer.x not in (select y from t2 where y is not null);

is the same as

select * from t1 outer
where not exists (select null from t2 where t2.y = outer.x);

If t.y is null-able,

select * from t1 outer
where outer.x not in (select y from t2 where y is null);

select * from t1 outer
where outer.x not in (2,3,Null);

return no rows.

select * from t1 outer
where outer.x not in (2,3);

IN & EXISTS, AskTom, http://bit.ly/aLvOeS

Set Stats

declare
 m_distcnt  number;
 m_density  number;
 m_nullcnt  number;
 m_avgclen  number;

begin

 m_distcnt := 200100;
 m_density := 0.002;
 m_nullcnt := 0;
 m_avgclen := 5;

 dbms_stats.set_column_stats(
  ownname  => user,
  tabname  => 't1',
  colname  => 'x',
  distcnt  => m_distcnt,
  density  => m_density,
  nullcnt  => m_nullcnt,
  avgclen  => m_avgclen
 );

 dbms_stats.set_column_stats(
  ownname  => user,
  tabname  => 't2',
  colname  => 'y',
  distcnt  => m_distcnt,
  density  => m_density,
  nullcnt  => m_nullcnt,
  avgclen  => m_avgclen
 );

  dbms_stats.set_table_stats( user, 't1', numrows => 2000000, numblks => 1000000);
  dbms_stats.set_table_stats( user, 't2', numrows => 2000000, numblks => 1000000);

end;
/

select  table_name,
 num_rows,
 blocks
from
 user_tables
where
 table_name in ('T1','T2')
;

select
 num_distinct,
 low_value,
 high_value,
 density,
 num_nulls,
 num_buckets,
 histogram
from
 user_tab_columns
where
 table_name in ('T1','T2')
and column_name in ('X','Y')
;

Collection_Array

2010-05-18T11:09:00.000-07:00

PL/SQL has three collection types, Tom often demos with Nested Table and Associative Array.
Associative Array is most flexible on element indexing. Nested Table is good enough for Bulk Fetching.

But Bryn Llewellyn, Oracle PL/SQL Product Manager likes to use VArray in his demo.
"the collection is best declared as a varray with a maximum size equal to Batchsize."
See http://www.oracle.com/technology/tech/pl_sql/pdf/doing_sql_from_plsql.pdf
Probably he knows that VARRAY implemented with the most efficient internal storage, and delivery the best performance, when correctly used; it may meet most of the requirements for loop batch bulk fetching.
To find out the details, you may do a simple RunStats benchmark.

Associative Array type(or index-by table)

TYPE population IS TABLE OF NUMBER INDEX BY VARCHAR2(64);

Nested Tables

TYPE nested_type IS TABLE OF VARCHAR2(30);

invoke EXTEND method to add elements later

Collection of ADT = UDT, Abstract datatype, User defined datatype:

CREATE OR REPLACE TYPE INVDB.NUMBER_TAB_TYPE is table of number;
/

select ... from TABLE(ADT_Table_Instance);

Comments, Bulk fetch into ADT is not efficient, you may see the workaround in paper doing_sql_from_plsql.pdf

Variable-size array (varray)

-- Code_30 Many_Row_Select.sql
Batchsize constant pls_integer := 1000;

type Result_t is record(PK t.PK%type, v1 t.v1%type);
type Results_t is varray(1000) of Result_t;
Results Results_t;

Concept

Associative Array: sparse array.
Nested Table or ADT/UDT : dense array

REM Associative Array
declare
   type date_aat is table of date index by binary_integer;
   l_data date_aat;
begin
   l_data(-200) := sysdate;
   l_data(+200) := sysdate+1;
end;
/

collection(elements), e.g. collection_instance(element_unique_subscript_index_number)
record_name.field_name

Benchmark with RunStats

2010-05-03T11:48:00.000-07:00

There are many approaches and tools to benchmark Oracle application.
E.g.
* mystat.sql and mystat2.sql
* SQL session trace and tkprof
* SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
* ASH/AWR
* SQL hint /*+ gather_plan_statistics */ and dbms_xplan.display_cursor(NULL,NULL, 'iostats memstats last partition');
* ALTER SESSION SET STATISTICS_LEVEL=ALL;
* Real-Time SQL Monitoring

My favorite one is Tom's RunStats. Here is the one I enhanced from Tom's original version.

/*
Goal
----
Persistent the benchmark stats, and then developer can query the report later.

Solution
--------
Add an IP column to utility.run_stats_save table, get computer IP by SYS_CONTEXT function.

Reference
---------
file://a:/Tuning/Trace/RunStatsSave.sql

http://tkyte.blogspot.com/2009/10/httpasktomoraclecomtkyte.html

How to build a simple test harness (RUNSTATS) (HOWTO) to test two different approaches from a performance perspective.

Runstats.sql
This is the test harness I use to try out different ideas. It shows two vital sets of statistics for me
The elapsed time difference between two approaches. It very simply shows me which approach is faster by the wall clock
How many resources each approach takes. This can be more meaningful then even the wall clock timings. For example, if one approach is faster then the other but it takes thousands of latches (locks), I might avoid it simply because it will not scale as well.
The way this test harness works is by saving the system statistics and latch information into a temporary table. We then run a test and take another snapshot. We run the second test and take yet another snapshot. Now we can show the amount of resources used by approach 1 and approach 2.
Requirements

In order to run this test harness you must at a minimum have:

Access to V$STATNAME, V$MYSTAT, v$TIMER and V$LATCH
You must be granted select DIRECTLY on SYS.V_$STATNAME, SYS.V_$MYSTAT, SYS.V_$TIMER and SYS.V_$LATCH. It will not work to have select on these via a ROLE.
The ability to create a table -- run_stats -- to hold the before, during and after information.
The ability to create a package -- rs_pkg -- the statistics collection/reporting piece
You should note also that the LATCH information is collected on a SYSTEM WIDE basis. If you run this on a multi-user system, the latch information may be technically "incorrect" as you will count the latching information for other sessions - not just your session. This test harness works best in a simple, controlled test environment.

*/

CREATE USER utility
  IDENTIFIED BY ?
  DEFAULT TABLESPACE USERS quota unlimited on users
ACCOUNT UNLOCK;

grant unlimited tablespace to utility;

create role schema_admin;

grant create session, create table, create view, 
 create procedure, create trigger, create any directory,
 CREATE SEQUENCE, CREATE TYPE, CREATE SYNONYM,
 create materialized view, create dimension,
 SELECT_CATALOG_ROLE,
 create database link,create public database link, drop public database link,
 create job
to schema_admin;

grant schema_admin to utility;
-- grant create procedure, create table to utility;

grant select on SYS.V_$STATNAME to utility;
grant select on SYS.V_$MYSTAT to utility;
grant select on SYS.V_$TIMER to utility;
grant select on SYS.V_$LATCH to utility;

drop table utility.run_stats;
drop table utility.run_stats_save;

create global temporary table utility.run_stats
( runid varchar2(15),
  name varchar2(80),
  value int )
on commit preserve rows;

-- Store the stats for later reporting
create table utility.run_stats_save
( 
runid varchar2(15),
name varchar2(80),
value int,
IP varchar2(30),
hostname varchar2(30)
) tablespace users;

create or replace view utility.stats
as select 'STAT...' || a.name name, b.value
      from v$statname a, v$mystat b
     where a.statistic# = b.statistic#
    union all
    select 'LATCH.' || name,  gets
      from v$latch
 union all
 select 'STAT...Elapsed Time', hsecs from v$timer;
/*
SYS_CONTEXT

The SYS_CONTEXT function is able to return the following host and IP address information for the current session:

TERMINAL - An operating system identifier for the current session. This is often the client machine name. 
HOST - The host name of the client machine. 
IP_ADDRESS - The IP address of the client machine. 
SERVER_HOST - The host name of the server running the database instance. 

SELECT SYS_CONTEXT('USERENV','HOST') FROM dual;
----------
GATES2\SKY

SELECT terminal, machine FROM v$session 
where sid = (select sid from v$mystat where rownum <= 1);
*/

CREATE OR REPLACE package utility.runstats_pkg
as
  TYPE print_tab     IS TABLE OF varchar2(200);
  --l_print dbms_sql.VARCHAR2_TABLE; 
    procedure rs_start;
    procedure rs_middle;
    procedure rs_stop( p_difference_threshold in number default 0 );
    procedure rs_report( p_difference_threshold in number default 0, p_host in varchar2 default Null );
  function rs_report( p_difference_threshold in number default 0, p_host in varchar2 default Null )
  return print_tab PIPELINED DETERMINISTIC;
end;
/

CREATE OR REPLACE package body utility.runstats_pkg
as

g_start number;
g_run1  number;
g_run2  number;
g_host varchar2(30);
g_ip varchar2(30);

procedure rs_start
is
begin

  g_host := substr(SYS_CONTEXT('USERENV','TERMINAL'),1,30);
  g_ip := substr(SYS_CONTEXT('USERENV','IP_ADDRESS'),1,30);
  delete from run_stats_save where hostname = g_host;

    execute immediate 'truncate table run_stats';
    delete from run_stats;

    insert into run_stats
    select 'before', stats.* from stats;

    g_start := dbms_utility.get_time;
end;

procedure rs_middle
is
begin
    g_run1 := (dbms_utility.get_time-g_start);

    insert into run_stats
    select 'after 1', stats.* from stats;
    g_start := dbms_utility.get_time;

end;

procedure rs_stop(p_difference_threshold in number default 0)
is
begin
    g_run2 := (dbms_utility.get_time-g_start);

    dbms_output.put_line
    ( 'Run1 ran in ' || g_run1 || ' hsecs' );
    dbms_output.put_line
    ( 'Run2 ran in ' || g_run2 || ' hsecs' );
    dbms_output.put_line
    ( 'run 1 ran in ' || round(g_run1/g_run2*100,2) ||
      '% of the time' );
    dbms_output.put_line( chr(9) );

    insert into run_stats
    select 'after 2', stats.* from stats;

    insert into run_stats_save (RUNID,NAME,VALUE,IP,HOSTNAME)
    select RUNID,NAME,VALUE, g_ip, g_host from run_stats;

    commit;

    dbms_output.put_line
    ( rpad( 'Name', 40 ) || lpad( 'Run1', 14 ) ||
      lpad( 'Run2', 14 ) || lpad( 'Diff', 14 ) );

    for x in
    ( select rpad( a.name, 40 ) ||
             to_char( b.value-a.value, '99999,999,999' ) ||
             to_char( c.value-b.value, '99999,999,999' ) ||
             to_char( ( (c.value-b.value)-(b.value-a.value)), '99999,999,999' ) data
        from run_stats a, run_stats b, run_stats c
       where a.name = b.name
         and b.name = c.name
         and a.runid = 'before'
         and b.runid = 'after 1'
         and c.runid = 'after 2'
         -- and (c.value-a.value) > 0
         and abs( (c.value-b.value) - (b.value-a.value) )
               > p_difference_threshold
       order by abs( (c.value-b.value)-(b.value-a.value)), data
    ) loop
        dbms_output.put_line( x.data );
    end loop;

    dbms_output.put_line( chr(9) );
    dbms_output.put_line
    ( 'Run1 latches total versus run2 -- difference and pct' );
    dbms_output.put_line
    ('.'|| lpad( 'Run1', 14 ) || lpad( 'Run2', 14 ) ||
      lpad( 'Diff', 14 ) || lpad( 'Pct', 11 ) );

    for x in
    ( select '.'||
             to_char( run1, '99999,999,999' ) ||
             to_char( run2, '99999,999,999' ) ||
             to_char( diff, '99999,999,999' ) ||
             to_char( round( run1/run2*100,2 ), '99,999.99' ) || '%' data
        from ( select sum(b.value-a.value) run1, sum(c.value-b.value) run2,
                      sum( (c.value-b.value)-(b.value-a.value)) diff
                 from run_stats a, run_stats b, run_stats c
                where a.name = b.name
                  and b.name = c.name
                  and a.runid = 'before'
                  and b.runid = 'after 1'
                  and c.runid = 'after 2'
                  and a.name like 'LATCH%'
                )
    ) loop
        dbms_output.put_line( x.data );
    end loop;
end;

procedure rs_report( p_difference_threshold in number default 0, p_host in varchar2 default Null )
is
begin
  g_host := substr(SYS_CONTEXT('USERENV','TERMINAL'),1,30);
  g_ip := substr(SYS_CONTEXT('USERENV','IP_ADDRESS'),1,30);

    g_run2 := (dbms_utility.get_time-g_start);

    dbms_output.put_line
    ( 'Run1 ran in ' || g_run1 || ' hsecs' );
    dbms_output.put_line
    ( 'Run2 ran in ' || g_run2 || ' hsecs' );
    dbms_output.put_line
    ( 'run 1 ran in ' || round(g_run1/g_run2*100,2) ||
      '% of the time' );
    dbms_output.put_line( chr(9) );

    dbms_output.put_line
    ( rpad( 'Name', 40 ) || lpad( 'Run1', 14 ) ||
      lpad( 'Run2', 14 ) || lpad( 'Diff', 14 ) );

    for x in
    ( select rpad( a.name, 40 ) ||
             to_char( b.value-a.value, '99999,999,999' ) ||
             to_char( c.value-b.value, '99999,999,999' ) ||
             to_char( ( (c.value-b.value)-(b.value-a.value)), '99999,999,999' ) data
        from run_stats_save a, run_stats_save b, run_stats_save c
       where a.name = b.name
         and b.name = c.name
         and a.runid = 'before'
         and b.runid = 'after 1'
         and c.runid = 'after 2'
         -- and (c.value-a.value) > 0
         and abs( (c.value-b.value) - (b.value-a.value) )
               > p_difference_threshold
         and a.hostname = g_host
         and a.hostname = b.hostname
         and a.hostname = c.hostname
       order by abs( (c.value-b.value)-(b.value-a.value))
    ) loop
        dbms_output.put_line( x.data );
    end loop;

    dbms_output.put_line( chr(9) );
    dbms_output.put_line
    ( 'Run1 latches total versus run2 -- difference and pct' );
    dbms_output.put_line
    ( lpad( 'Run1', 14 ) || lpad( 'Run2', 14 ) ||
      lpad( 'Diff', 14 ) || lpad( 'Pct', 11 ) );

    for x in
    ( select to_char( run1, '99999,999,999' ) ||
             to_char( run2, '99999,999,999' ) ||
             to_char( diff, '99999,999,999' ) ||
             to_char( round( run1/run2*100,2 ), '99,999.99' ) || '%' data
        from ( select sum(b.value-a.value) run1, sum(c.value-b.value) run2,
                      sum( (c.value-b.value)-(b.value-a.value)) diff
                 from run_stats_save a, run_stats_save b, run_stats_save c
                where a.name = b.name
                  and b.name = c.name
                  and a.runid = 'before'
                  and b.runid = 'after 1'
                  and c.runid = 'after 2'
                  and a.name like 'LATCH%'
         and a.hostname = g_host
         and a.hostname = b.hostname
         and a.hostname = c.hostname
               )
    ) loop
        dbms_output.put_line( x.data );
    end loop;
end;

-- select * from TABLE(runstats_pkg.rs_report);

function rs_report( p_difference_threshold in number default 0, p_host in varchar2 default Null )
return print_tab PIPELINED DETERMINISTIC
as
begin
  g_host := substr(SYS_CONTEXT('USERENV','TERMINAL'),1,30);
  g_ip := substr(SYS_CONTEXT('USERENV','IP_ADDRESS'),1,30);

    g_run2 := (dbms_utility.get_time-g_start);

    PIPE ROW
    ( 'Run1 ran in ' || g_run1 || ' hsecs' );
    PIPE ROW
    ( 'Run2 ran in ' || g_run2 || ' hsecs' );
    PIPE ROW
    ( 'run 1 ran in ' || round(g_run1/g_run2*100,2) ||
      '% of the time' );
    PIPE ROW( chr(9) );

    PIPE ROW
    ( rpad( 'Name', 40 ) || lpad( 'Run1', 14 ) ||
      lpad( 'Run2', 14 ) || lpad( 'Diff', 14 ) );

    for x in
    ( select rpad( a.name, 40 ) ||
             to_char( b.value-a.value, '99999,999,999' ) ||
             to_char( c.value-b.value, '99999,999,999' ) ||
             to_char( ( (c.value-b.value)-(b.value-a.value)), '99999,999,999' ) data
        from run_stats_save a, run_stats_save b, run_stats_save c
       where a.name = b.name
         and b.name = c.name
         and a.runid = 'before'
         and b.runid = 'after 1'
         and c.runid = 'after 2'
         -- and (c.value-a.value) > 0
         and abs( (c.value-b.value) - (b.value-a.value) )
               > p_difference_threshold
         and a.hostname = g_host
         and a.hostname = b.hostname
         and a.hostname = c.hostname
       order by abs( (c.value-b.value)-(b.value-a.value)), abs(c.value-b.value)
    ) loop
        PIPE ROW( x.data );
    end loop;

    PIPE ROW( chr(9) );
    PIPE ROW
    ( 'Run1 latches total versus run2 -- difference and pct' );
    PIPE ROW
    ( lpad( 'Run1', 14 ) || lpad( 'Run2', 14 ) ||
      lpad( 'Diff', 14 ) || lpad( 'Pct', 11 ) );

    for x in
    ( select to_char( run1, '99999,999,999' ) ||
             to_char( run2, '99999,999,999' ) ||
             to_char( diff, '99999,999,999' ) ||
             to_char( round( run1/run2*100,2 ), '99,999.99' ) || '%' data
        from ( select sum(b.value-a.value) run1, sum(c.value-b.value) run2,
                      sum( (c.value-b.value)-(b.value-a.value)) diff
                 from run_stats_save a, run_stats_save b, run_stats_save c
                where a.name = b.name
                  and b.name = c.name
                  and a.runid = 'before'
                  and b.runid = 'after 1'
                  and c.runid = 'after 2'
                  and a.name like 'LATCH%'
         and a.hostname = g_host
         and a.hostname = b.hostname
         and a.hostname = c.hostname
               )
    ) loop
        PIPE ROW( x.data );
    end loop;
end;

end;
/

grant execute on utility.runstats_pkg to public;
create or replace public synonym runstats_pkg for utility.runstats_pkg; 

/*
--Usage: to benchmark two approaches
--you may just leave approach 2 code part empty, to get resources of code 1 take.

set serveroutput on

execute runStats_pkg.rs_start;
 
execute runStats_pkg.rs_middle;
 
execute runStats_pkg.rs_stop;

begin
runStats_pkg.rs_start;
 for c in ()
 loop
   Null;
 end loop;
runStats_pkg.rs_middle;
 for c in ()
 loop
   Null;
 end loop;
runStats_pkg.rs_stop;

end;

--To get the report after benchmark:
select * from TABLE(runstats_pkg.rs_report);
OR
exec runStats_pkg.rs_report(10);

*/

Find and delete duplicate rows by Analytic Function

2010-04-26T10:07:00.000-07:00

The intuitive way will be create a temp table, with Min(RowID) and Count(*)>1, then join it back to target table to do the delete.

You can get duplicate rows by Analytic SQL:

SELECT rid, deptno, job, rn
  FROM
  (SELECT /*x parallel(a) */
        ROWID rid, deptno, job,
        ROW_NUMBER () OVER (PARTITION BY deptno, job ORDER BY empno) rn
   FROM scott.emp a
  )
WHERE rn <> 1;

Get duplicate row count with Count(*) > 0.

SELECT /*x parallel(a,8) */
 MAX(ROWID) rid, deptno, job, COUNT(*)
FROM scott.emp a
GROUP BY deptno, job
HAVING COUNT(*) > 1;

To delete them:

DELETE FROM scott.emp
WHERE ROWID IN
 (
  SELECT rid
    FROM (SELECT /*x parallel(a) */
                 ROWID rid, deptno, job,
                 ROW_NUMBER () OVER (PARTITION BY deptno, job ORDER BY empno) rn
            FROM scott.emp a)
  WHERE rn <> 1
);

Reclaim deleted LOB data storage

2010-04-19T10:29:00.000-07:00

I was helping a client purge obsolete data and reclaim some space in an OLAP database.
We stuck on a BLOB column segment. It took me a couple hours to find the solution.

Here is the solution demo.

Create a table with BLOB column,

drop table t2 purge;

CREATE TABLE t2
(
 n1 NUMBER(10),
 d1 date,
 b1 BLOB,
 CONSTRAINT t2_PK PRIMARY KEY (n1)
  USING INDEX TABLESPACE index_auto
) 
TABLESPACE data_auto;

Displays the large objects (LOBs) contained in tables

select b.TABLE_NAME, b.COLUMN_NAME, b.SEGMENT_NAME, b.TABLESPACE_NAME, b.INDEX_NAME
from user_lobs b;

SELECT SEGMENT_NAME, segment_type, TABLESPACE_NAME, BYTES, BLOCKS, EXTENTS
FROM user_SEGMENTS
WHERE segment_type like '%LOB%'
ORDER BY SEGMENT_NAME;

SELECT b.TABLE_NAME, b.COLUMN_NAME, b.SEGMENT_NAME, b.TABLESPACE_NAME, b.INDEX_NAME
 ,s.bytes, s.blocks, s.extents
FROM user_lobs b, user_segments s
WHERE b.table_name = 'T2'
and b.column_name = 'B1'
and s.segment_type like 'LOB%'
and s.segment_name = b.segment_name;

Test Shrink a BASICFILE LOB segment only

truncate table t2;

declare
    l_blob blob;
    l_size number := 32700;
begin
  for i in 1 .. 15
  loop
    insert into t2(n1,b1) values (i, empty_blob() ) returning b1 into l_blob;
    dbms_lob.writeappend( l_blob, l_size, utl_raw.cast_to_raw(rpad('*',l_size,'*')));
  end loop;
  commit;
end;
/

delete t2;
commit;

SELECT b.TABLE_NAME, b.COLUMN_NAME, b.SEGMENT_NAME, b.TABLESPACE_NAME, b.INDEX_NAME
 ,s.bytes, s.blocks, s.extents
FROM user_lobs b, user_segments s
WHERE b.table_name = 'T2'
and b.column_name = 'B1'
and s.segment_type like 'LOB%'
and s.segment_name = b.segment_name;

     BYTES     BLOCKS    EXTENTS
---------- ---------- ----------
    720896         88         11

ALTER TABLE t2 MODIFY LOB (b1) (SHRINK SPACE);

SELECT b.TABLE_NAME, b.COLUMN_NAME, b.SEGMENT_NAME, b.TABLESPACE_NAME, b.INDEX_NAME
 ,s.bytes, s.blocks, s.extents
FROM user_lobs b, user_segments s
WHERE b.table_name = 'T2'
and b.column_name = 'B1'
and s.segment_type like 'LOB%'
and s.segment_name = b.segment_name;

     BYTES     BLOCKS    EXTENTS
---------- ---------- ----------
     65536          8          1

Note

Shrink command will generate about same size of redo/archive logs as LOB storage space size.

Reference.

Shrink a table and all of its dependent segments (including BASICFILE LOB segments):

ALTER TABLE t2 ENABLE ROW MOVEMENT;
ALTER TABLE t2 SHRINK SPACE CASCADE;

Shrink a BASICFILE LOB segment only:

ALTER TABLE t2 MODIFY LOB (b1) (SHRINK SPACE);

Pl/SQL Development Workflow

2010-04-12T11:26:00.000-07:00

Here is the notes taking from book <<Oracle PL/SQL Best Practices>>.

Four steps of preparing an application, special for PL/SQL transactional database API.

Validate program requirements

ask lots of questions
what users ask for is not always the easiest way to solve a problem
consider other approaches, include business processes and programming algorithms

Implement header of the program

good name for the program, accurately represent the purpose of the program
inputs and outputs
overload sub-procedure ?

Define the test cases

Verify it works
how will I know when I am done with this program

Build test code

Testing for correctness:

Have you tested with good and all the different possibilities of bad data
Does the code do the right thing, ... and nothing more.

Why Transactional Database API approach?

2010-04-06T15:28:00.000-07:00

We like Transactional Database API approach, because it:

1. make software components modular, I'm totally into modular programming
2. software modules must carry out a very specific task
(and be very efficient at carrying it out)
3. each software module should be loosely coupled (to limit dependencies)
4. It removes the need for triggers as all inserts, updates and deletes are wrapped in APIs. Instead of writing triggers you simply add the code into the API. I loath triggers.
5. It prevents people who don't understand SQL writing stupid queries.
All SQL would be written by PL/SQL developers or DBAs, reducing the likelihood of dodgy queries.
Profession = Efficiency + High Quality

Anything that generates SQL on-the-fly worries me, not just Java. I want to be able to cut and paste the SQL, not try and capture or trace it during a run.

6. The underlying structure of the database is hidden from the users, so I can make structural changes without client applications being changed.The API implementation can be altered and tuned without affecting the client application.
7. The same APIs are available to all applications that access the database. No duplication of effort.
8. Eliminate SQL Parse in host language. Parse consume client host CPU and Server CPU and Latches. PL/SQL keep the SQL cursor cached and opened.
9. Eliminate data round trip; data type conversion, the context switch.
10. Tightly couple the data model and data process design, database world favor of Up Front Big Design.
Maximum the data share and reuse.
...
This list goes on and on.

Our concept is "build the data API in the database, you call the data API".
The data API encapsulate a transaction in a bit of code. Here we agree - no SQL in the client application, just call stored procedures - well tuned and developed explicitly to do that one thing.

Database API has been layered by different UI technologies over time.

All about API's. The applications don't do table level(select/insert/update/delete) stuff, the apps don't even really know about tables.

On top of database schema we have an API that does the data stuff.
(generally, functions or Ref cursor to retrieve data, procedures to change data)

In the application we call this API but have our own application logic as well
(only application logic is in the application, data logic is - well, right where it belongs - next to the data, waiting for the 'next great programming paradigm to come along')

The fact that our UI is in Java isn't really relevant. You could pretty much see how you would use this package from C#, Java/JSP, Swing, VB, Pro*C, Forms, Powerbuilder, Perl, PHP, Python, a mobile phone, <whatever the heck you want>.

Setup StatsPack to monitor standby database performance

2010-03-29T13:51:00.000-07:00

AWR/ADDM are not supported (and don't work) against a read only standby database, so they are not useful for diagnostic for an Active Data Guard environment.

A modifed version of statspack availabe on metalink (note 454848.1) can be deployed on the primary production database. This modified version uses a STDBYPERF schema which writes locally but reads remotely from PERFSTAT@standbydb.

Initial Setup (Oracle 11.1.0.7)

export ORACLE_SID=standbydb
cd $ORACLE_HOME/rdbms/admin
sql
CREATE SMALLFILE TABLESPACE PERFSTAT DATAFILE '+DATA1/perfstat01.dbf' SIZE 4G
 LOGGING 
 EXTENT MANAGEMENT LOCAL 
 SEGMENT SPACE MANAGEMENT AUTO;
@spdrop
@spcreate
exit;

sql
@sbcreate

--wait a second or two, the account has to propagate to the standby

@sbaddins

Configure Snapshots

Remember that PERFSTAT is not automatically purged/managed by the database, so this must be done by the DBA or scheduled.

-- workaround to avoid serious performance problem due to Bug 8323663
. setdb ordprod2
sql
execute dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'X$KCCTS', estimate_percent => 20 );
execute dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'X$KCFIO', estimate_percent => 20 );
execute dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'X$KCBFWAIT', estimate_percent => 20 );
execute dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'X$KCCFN', estimate_percent => 20 );
execute dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'X$KCCFE', estimate_percent => 20 );


grant create job to stdbyperf;
grant manage scheduler to stdbyperf;

conn stdbyperf/***

-- if this takes more than ~40 seconds there is a performance issue that will need to be traced
exec statspack_ordprod1.snap;

-- automate stats collection every 30 min during Mon-Fri 7am-5pm

exec dbms_scheduler.disable('ordrpt_statspack_snap_daily');

begin
   dbms_scheduler.create_job
   (
      job_name => 'ordrpt_statspack_snap_daily',
      job_type => 'PLSQL_BLOCK',
      job_action => 'begin statspack_ordprod1.snap; end;',
      repeat_interval => 'FREQ=MINUTELY; INTERVAL=30; BYHOUR=7,8,9,10,11,12,13,14,15,16,17; BYDAY=MON,TUE,WED,THU,FRI',
      enabled => true,
      comments => 'Take statspack snapshot on remote standby db ordrpt daily Mon to Fri 7am to 5pm'
   );
end;
/

exec dbms_scheduler.enable('ordrpt_statspack_snap_daily');

Create Reports

Similar to AWR reports, run reports from caldbrac02

SQL> conn stbyperf/***

SQL> @?/rdbms/admin/sbreport

-- follows prompts to enter dbid, instanceid, snapshot intervals, output is text file

Author: Blair Boadway | Production DBA

Query paging row count

2010-03-16T12:28:00.000-07:00

Do we need to return the accurate query row count?

We will always evaluate the query performance and cost to get the row count, and based on function/features priority and data accuracy requirements, then decide which option to go.

Also assess which option list below that the end users value more?

The quick response time to return the first page

to get the exact row count.

Here are some options I know so far:

Materialize the row count in MV(Summary table), schedule a regular job to refresh the summary data.
Estimate the row count, by CBO parse

Here is the idea, check the plan output Rows column.

The CBO (Cost Base Optimizer) estimate it when generate the SQL execution plan.
CBO get it from object(table/index) stats, e.g. dba_tables.num_rows .
But not touching the table.

scott@txdev_FLURRY> set autot trace exp
scott@txdev_FLURRY>
scott@txdev_FLURRY> select * from emp;
Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |   518 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |   518 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Estimate the row count, with oracles SAMPLE clause
Hard limit in Transactional database API,

Show user that there could be more rows than hard code limit, and always enable [Next Page] button, and dynamic change or increase the count number as paging forward.

SELECT count(*) from scott.emp where rownum <= 100;

SOA - Transactional Database API - Database access guideline

2010-03-10T10:44:00.000-08:00

"Applications come, applications go, they crash, they change, they are fleeting."
The data however -- that'll be here long.

Programming language come, programming languages go, DBMSs come, DBMSs go,

In fact, I've seen successful Cobol, Fortran, VB, Perl, PHP, Java, Python, whatever language; FoxPro, Sybase, DB2, Oracle, MySQL, Cassandra HBase, whatever database.

The data, well, Our AbePOs(orders) and Clients data have been in a database for over 12 years now -- the applications that access (should say "have accessed in the past for they no longer exist") it come and they go and there will be many many many more in the future.

The solution is there: SOA (Modular programming)

SOA is a design principle, whereas web services is an implementation technology. You can build a Service-Oriented Application with .Net, C, Java, or PL/SQL, T-SQL.

The basic building block... is the service. A service is a self-contained software module that performs a predetermined task: e.g. a business transaction to submit order, audit the changes, update inventory, update finance data, queue the changes to asynchronous batch notice buyers ...; that comes to the Transactional database API.

The main theme behind SOA is to find the appropriate modularity ... where the modules don't have too much tight coupling. such as UI, Business rule and DAL.

An typical bad example is the Buyer Order Tracker UI, the reporting UI rule is tightly coupled with data access, make it so hard to refactor the data access part. BOT has made database performance plunged many times.

If we implement the SOA, the database handle the data access, and the Java handle the UI, it'll be a piece of cake to fix the BOT database performance issue.

When we want to migrate to Sybase, just write a new DB API on Sybase, UI application call the new API.
When we want to migrate to .Net, just write a new UI module, that calls the same DB API to access the data.

Notes: many statements are referenced from AskTom

Turn on INTERVAL PARTITIONING on a range partition table

2009-12-07T10:44:00.001-08:00

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

2009-11-18T14:29:00.001-08:00

/*********
-- 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

2009-11-15T21:42:00.000-08:00

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

2009-11-11T21:21:00.001-08:00

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

2009-06-01T17:01:00.000-07:00

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

2009-04-15T09:50:00.000-07:00

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

2009-04-14T10:17:00.000-07:00

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

*/

debug.f to instrument PL/SQL code

2009-04-08T10:51:00.000-07:00

Purpose
-------
Code Instrumentation is the fine art of making every other line of code be debug.
Here we show you how to install and implement debug.f in you PL/SQL code.

Download
--------
debugf: db.zip contains the code, then extract and save all files to a local folder.
http://asktom.oracle.com/~tkyte/debugf

Discussion
----------
"Instrumentation overhead"
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:10445025326812

"The 10 (Oracle Development) Commandments"
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:51960184066540

Setup
-----
suggest you

create a utility user:


create user utility identified by utility;
grant create session, create procedure, create table, create trigger to utility;
alter user utility default tablespace users quota unlimited on users;

connect sys as sysdba
grant execute on utl_file to utility;
grant create public synonym, create any DIRECTORY to utility;

run install_debug in that schema 
create a public synonym for debug 
And then you can use it. 


connect utility/utility
@install_debug.sql
--@A:\plsql\debugf\install_debug.sql

create public synonym debug for utility.debug;
grant execute on utility.debug to public;

DROP DIRECTORY debug_dir;
CREATE or replace DIRECTORY debug_dir AS '/data1/debug';
CREATE or replace DIRECTORY debug_dir AS '/u01/debug';

CREATE or replace DIRECTORY debug_dir AS 'c:/temp/log';

--GRANT READ, WRITE ON DIRECTORY debug_dir TO PUBLIC; 
--exec debug.clear;
--exec debug.init(p_file=>'DEBUG_DIR/system.dbg');

-- Enable debug trace for current login user
declare
  ls_user varchar2(30) DEFAULT USER;
begin
 debug.clear;
 debug.init(p_file=>'DEBUG_DIR/'||ls_user||'.dbg');
 debug.status;
end;
/


-- Set module name to trace, p_modules=>''
-- Enable trace for a user, p_user=>'SYSTEM'
declare
 ls_trace_user varchar2(30) := 'CS_USER';
BEGIN
 debug.clear(p_user=>ls_trace_user);
 debug.init(p_modules=>'ALL', p_user=>ls_trace_user,
   --p_date_format=>'YYYY/MM/DD HH24:MI:SS',
   p_show_sesid=>'YES',
   p_file=>'DEBUG_DIR/'||ls_trace_user||'.dbg');
 debug.status(p_user=>ls_trace_user);
end;
/

-- batch setup logging users
declare
 ls_trace_user ct_type;
BEGIN

 ls_trace_user := ct_type ('SYS','SYSTEM','ABEDBA','ABELISTING');

 FOR i IN ls_trace_user.FIRST .. ls_trace_user.LAST
 LOOP
  debug.clear(p_user=>ls_trace_user(i));
  debug.init(p_modules=>'ALL', p_user=>ls_trace_user(i),
    --p_date_format=>'YYYY/MM/DD HH24:MI:SS',
    p_show_sesid=>'NO',
    p_file=>'DEBUG_DIR/'||ls_trace_user(i)||'.dbg');
  debug.status(p_user=>ls_trace_user(i));
 End Loop;
end;
/

Demo 1
------

Begin
  debug.f('start %s : %s', 1, sysdate);
  debug.f(SubStr(TO_CHAR(SQLCODE)||': '||SQLERRM,1,100));
Exception
    WHEN OTHERS THEN
      debug.f(SubStr(TO_CHAR(SQLCODE)||': '||SQLERRM,1,100));
End;
/

Demo 2
------

@connect "/ as sysdba"
grant execute on dbms_lock to ops$tkyte;
@connect /

set echo on
/*
first we'll start by enabling debug - this would be done once, sets up a 
table with values...

After that, we'll watch the debug....
*/
exec debug.init
exec debug.f( 'hello world' );
pause
set define off
host xterm -e tail -f /tmp/OPS\$TKYTE.dbg &
set define on

create or replace procedure want_to_trace
as
begin
    for i in 1 .. 10
    loop
        if ( mod(i,2) = 0 )
        then
            debug.f( 'processing step %s of 10 - %s', i , 'odd');
            dbms_lock.sleep(1);
        else
            debug.f( 'processing step %s of 10', i );
            dbms_lock.sleep(1);
        end if;
    end loop;
end;
/

exec want_to_trace

monitor
-------


To trace it:
tail -f [DEBUG_DIR]/[LOGIN USER].dbg

For example:
tail -f /data1/debug/SYSTEM.dbg

New efficient HASH FULL OUTER join in 11g

2009-04-07T14:39:00.000-07:00

Purpose:
--
Oracle 11g query optimizer introduced a new HASH JOIN FULL OUTER execution method.

Starting with Oracle Database 11g Release 1 (11.1), Oracle Database automatically uses a native execution method based on a hash join for executing full outer joins whenever possible. When the new method is used to execute a full outer join, the execution plan for the query contains HASH JOIN FULL OUTER.

To instruct the optimizer not to consider using the hash full outer join execution method, apply the NO_NATIVE_FULL_OUTER_JOIN hint. The NO_NATIVE_FULL_OUTER_JOIN hint instructs the optimizer to exclude the native execution method when joining each specified table. Instead, the full outer join is executed as a union of left outer join and an anti-join.

Result
--
The new HASH JOIN FULL OUTER execution method logical reads(Buffers in execution plan output) cost equals too two base table FULL scan logical I/O adds up, nothing more.
The old full outer join cost 4 times more LIO on table scan.

Benchmark
---------


drop table t1 purge;
drop table t2 purge;

create table t1
as
select
 rownum + 5 id,
 rpad('x',500,'x') c1
from dual
connect by level <= 5000;

create table t2
as
select
 rownum + 15 id,
 rpad('x',500,'x') c1
from dual
connect by level <= 5000;

set serveroutput off
set linesize 170
set pagesize 0

select max(t1c1), max(t2c1)
from (
select /*+ gather_plan_statistics */ t1.id, t2.id, t1.c1 t1c1, t2.c1 t2c1
from t1 full outer join t2
on t1.id = t2.id
);

-- 10g way
SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL, 'runstats_last'));

-- 11g way
SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL, 'iostats memstats last partition'));

-----------------------------------------------------------------------------------------
| Id  | Operation               | Name | A-Rows | Buffers | Reads  | Writes| A-Time     |
-----------------------------------------------------------------------------------------
|   1 | SORT AGGREGATE          |      |      1 |     732 |    101 |     0 |00:00:00.15 |
|   2 |  VIEW                   |      |   5010 |     732 |    101 |     0 |00:00:00.06 |
|   3 |   UNION-ALL             |      |   5010 |     732 |    101 |     0 |00:00:00.05 |
|*  4 |    HASH JOIN RIGHT OUTER|      |   5000 |     366 |    101 |     0 |00:00:00.04 |
|   5 |     TABLE ACCESS FULL   | T2   |   5000 |     183 |     56 |     0 |00:00:00.01 |
|   6 |     TABLE ACCESS FULL   | T1   |   5000 |     183 |     45 |     0 |00:00:00.01 |
|*  7 |    HASH JOIN RIGHT ANTI |      |     10 |     366 |      0 |     0 |00:00:00.01 |
|   8 |     TABLE ACCESS FULL   | T1   |   5000 |     183 |      0 |     0 |00:00:00.01 |
|   9 |     TABLE ACCESS FULL   | T2   |   5000 |     183 |      0 |     0 |00:00:00.01 |
-----------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |      1 |00:00:00.09 |     184 |       |       |          |
|   1 |  SORT AGGREGATE        |          |      1 |00:00:00.09 |     184 |       |       |          |
|   2 |   VIEW                 | VW_FOJ_0 |   5010 |00:00:00.03 |     184 |       |       |          |
|*  3 |    HASH JOIN FULL OUTER|          |   5010 |00:00:00.03 |     184 |  3239K|  1058K| 3539K (0)|
|   4 |     TABLE ACCESS FULL  | T2       |   5000 |00:00:00.01 |      92 |       |       |          |
|   5 |     TABLE ACCESS FULL  | T1       |   5000 |00:00:00.01 |      92 |       |       |          |
------------------------------------------------------------------------------------------------------

Reference
--
Oracle® Database Performance Tuning Guide 11g Release 1 (11.1)
- 11 The Query Optimizer, 11.6.7 Outer Joins
OracleQueryOptimizer11g_20081202.pdf by Christian Antognini

PL/SQL instrument utility u$err package

2009-04-01T10:32:00.000-07:00

Purpose
-------
1. PL/SQL utility to Instrument and Trace.
2. Err_Trap.

debug.f() write to db server trace file
u$err.trc() write to utility.error_log table, with autonomous transaction.

u$err.err() write error stack to utility.error_log table, and DBMS_OUTPUT to std_io.

File: file://A:/PLSQL/debugf/u$err_pkg.sql

Setup
-----


drop table utility.error_log;
create table utility.error_log
(err_code varchar2(10), err_module varchar2(30), err_action varchar2(30), 
 create_date date default SYSDATE, create_user varchar2(30) default USER,
 err_text varchar2(4000)
)
partition by range (create_date) INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
(
 PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2009', 'DD-MM-YYYY'))
)
;

grant select on utility.error_log to qa_admin;


Code
----

CREATE OR REPLACE PACKAGE utility.u$err AS

/***
DEFUG.F : log trace information
***/

 PROCEDURE trc (p_msg IN VARCHAR2);

 PROCEDURE err (p_msg IN VARCHAR2 default '');

END;
/

CREATE OR REPLACE PACKAGE BODY utility.u$err AS

/***
Goal: 1. Trace, 2. Err_Trap

Who| Date         | What
Charlie(Yi 木匠) | 12-June-2008 | combine FORMAT_ERROR_BACKTRACE and DBMS_UTILITY.FORMAT_ERROR_STACK to one string

DEFUG.F

set serveroutput on size 200000

alter package utility.u$err compile;
grant execute on utility.u$err to public;
create or replace public synonym u$err for utility.u$err;

File: A:\PLSQL\debugf\u$err_pkg.sql

***/

 PROCEDURE trc (p_msg IN VARCHAR2)
 IS
   PRAGMA AUTONOMOUS_TRANSACTION;
   ls_module varchar2(32767);
   ls_action varchar2(32767);
   ls_code varchar2(10);
 BEGIN
   DBMS_APPLICATION_INFO.READ_MODULE ( 
    module_name => ls_module, 
    action_name => ls_action); 
   ls_code := to_char(sqlcode);

   INSERT INTO error_log (err_code,err_module,err_action,err_text)
                  VALUES(ls_code,substr(ls_module,1,30),substr(ls_action,1,30), p_msg);
   COMMIT;
   DBMS_OUTPUT.PUT_LINE('trc:'||ls_code||':'||ls_action||':'||ls_module||':'||p_msg);
 EXCEPTION
   WHEN OTHERS THEN
     ROLLBACK;
     DBMS_OUTPUT.PUT_LINE('Error in log_err: '||SUBSTR(SQLERRM,1,120));
 END;

 PROCEDURE err (p_msg IN VARCHAR2)
 IS
  ls_err varchar2(32767);
 BEGIN
   ls_err := SubStr(p_msg||'.'||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE||'<---'||
     chr(10)||DBMS_UTILITY.FORMAT_ERROR_STACK, 1, 2000);
   debug.f(ls_err);
   u$err.trc (ls_err);
   DBMS_APPLICATION_INFO.SET_MODULE(Null, Null);
 EXCEPTION
   WHEN OTHERS THEN
     u$err.trc (SubStr(DBMS_UTILITY.FORMAT_ERROR_STACK,1,500));
     DBMS_OUTPUT.PUT_LINE('Error stack in log_err: '||SUBSTR(SQLERRM,1,120));
 END;

END;
/

alter package utility.u$err compile;
grant execute on utility.u$err to public;
create or replace public synonym u$err for utility.u$err;



Test u$err.err and u$err.trc
----
exec u$err.err;

declare
 li pls_integer;
 procedure p_in
 is
 begin
   li := 5 / 0;
 exception
  when others then
   u$err.err;
   raise;
 end;
 
begin
 u$err.trc('start');
 p_in;
 u$err.trc('end');
exception
 when others then
  u$err.err('outer error');
  u$err.trc('before raise');
  raise;
  u$err.trc('after raise');
end;
/

-- Query data
select * from 
(
select * from utility.error_log
 -- PARTITION FOR(to_date('11-mar-2009','dd-mon-yyyy'))
order by create_date desc
) where rownum < 10;


Housekeeping
----
--Purge old log
delete utility.error_log where create_date < sysdate - 100;
commit;

ALTER TABLE utility.error_log DROP PARTITION FOR(to_date('11-mar-2009','dd-mon-yyyy'));

Reference
---------
Oracle PL/SQL Best Practices - Steven Feuerstein
http://www.oreilly.com/catalog/9780596514105

How to display SQL code in text format exactly

2009-03-13T09:47:00.000-07:00

In a blog post, we often need to display the program code, e.g. PL/SQL, SQL, and pure text output and trace file contents in text format exactly as it is, including space.

It had been bothering me for a long time. Finally, I found the solution, to use < pre > and < /pre > HTML tags.

Here is an example for RunStats output:
(Tested on http://www.blogger.com)

Without format:
..............................Fixed-Length...Delimited........Diff STAT...CPU used by this sessio 179 202 23 *** STAT...Elapsed Time 179 91 -88 *** STAT...DB time 0 421 421 xxx LATCH.process queue reference 1 58,878 58,877 *** STAT...physical read bytes 212,992 0 -212,992 STAT...session uga memory max 604,728 115,320 -489,408 STAT...session pga memory max 720,896 131,072 -589,824 Run1 latches total versus runs -- difference and pct Run1 Run2 Diff Pct 7,629 64,444 56,815 11.84%

With < pre > and < /pre > tags to format text:


..............................Fixed-Length...Delimited........Diff
STAT...CPU used by this sessio         179         202          23  ***
STAT...Elapsed Time                    179          91         -88  ***
STAT...DB time                           0         421         421  xxx
LATCH.process queue reference            1      58,878      58,877  ***
STAT...physical read bytes         212,992           0    -212,992
STAT...session uga memory max      604,728     115,320    -489,408
STAT...session pga memory max      720,896     131,072    -589,824

Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
7,629      64,444      56,815     11.84%

I learned something new today, I'm so happy.

Reference

http://www.echoecho.com/htmltext04.htm

NO_INDEX hint and deprecated ROWID hint

2009-02-12T16:38:00.000-08:00

Scope
-----
Many people asked how to specify ROWID hint for a SQL statement after Oracle 10g, and there is no answer by search Google.
The RowID access path is helpful when you do iterative update(query the rows, do some changes, and then update back).

Goal
----
Guide SQL access TABLE BY ROWID where there is a possible index access path.

Solution
--------

*) approach 1: NO_INDEX sql hint
*) approach 2: Suppress index scan by add expression/function to predicate column.

Result
------
After benchmark, we can see that SQL optimizer and SQL run time engine select the "TABLE ACCESS BY USER ROWID" access path by implement the our solutions.
The Logical reads(Buffers in the execution plan) reduced from 2 to 1.

Setup
-----


drop table yi01;
create table yi01
(myid number,
mycol varchar2(30),
constraint yi01_pk primary key(myid) using index
);

truncate table yi01;

insert into yi01(myid, mycol) values(1, Null);
insert into yi01(myid, mycol) values(2, Null);
insert into yi01(myid, mycol) values(3, Null);
insert into yi01(myid, mycol) values(4, 'a');
insert into yi01(myid, mycol) values(5, Null);
insert into yi01(myid, mycol) values(6, 'b');
insert into yi01(myid, mycol) values(7, Null);

insert into yi01(myid, mycol)
select rownum + 7, Null
from dual
connect by level <= 5010; 

commit;

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

Benchmark
--------- 

SQL>

set linesize 120
set pagesize 0
set serveroutput off

COLUMN rid NEW_VALUE l_rid
COLUMN myid NEW_VALUE l_id

select rowid rid, myid from yi01
where rownum <= 1;  

*) index range/unique scan

update /*+ gather_plan_statistics index(a) */ YI01 a
set MYCOL = 'F'
WHERE rowid = '&l_rid' and MYID = &l_id;

SELECT * FROM table(CAST (dbms_xplan.display_cursor(NULL,NULL, 'runstats_last') AS SYS.DBMS_XPLAN_TYPE_TABLE));

-----------------------------------------------------------------
| Id  | Operation         | Name    | E-Rows | A-Rows | Buffers |
-----------------------------------------------------------------
|   1 | UPDATE            |         |        |      1 |       2 |
|*  2 |  INDEX UNIQUE SCAN| YI01_PK |      1 |      1 |       2 |
-----------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("MYID"=1165)
       filter(ROWID='AAAYH+AAEAAAAhoASM')

*) approach 1: NO_INDEX sql hint

update /*+ gather_plan_statistics no_index(a) */ YI01 a
set MYCOL = 'F'
WHERE rowid = 'AAA5znAG2AAAPm0AAF' and MYID = 7;

SELECT * FROM table(CAST (dbms_xplan.display_cursor(NULL,NULL, 'runstats_last') AS SYS.DBMS_XPLAN_TYPE_TABLE));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------
| Id  | Operation                  | Name | E-Rows | A-Rows | Buffers |
-----------------------------------------------------------------------
|   1 | UPDATE                     |      |        |      1 |       1 |
-----------------------------------------------------------------------
|*  2 |  TABLE ACCESS BY USER ROWID| YI01 |      1 |      1 |       1 |
-----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
  2 - filter("MYID"=:SYS_B_2)

rollback;

*) approach 2: Surpress index scan by add expression/function to predicate column.

update /*+ gather_plan_statistics */ YI01 a
set MYCOL = 'F'
WHERE rowid = '&l_rid' and MYID + 0 = &l_id;

SELECT * FROM table(CAST (dbms_xplan.display_cursor(NULL,NULL, 'runstats_last') AS SYS.DBMS_XPLAN_TYPE_TABLE));

-----------------------------------------------------------------------
| Id  | Operation                  | Name | E-Rows | A-Rows | Buffers |
-----------------------------------------------------------------------
|   1 | UPDATE                     |      |        |      1 |       1 |
|*  2 |  TABLE ACCESS BY USER ROWID| YI01 |      1 |      1 |       1 |
-----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
  2 - filter("MYID"+0=1954)

Reference
---------

*) To get the current SQL execution plan in shared_pool.

Oracle 11g
SELECT * FROM table(dbms_xplan.display_cursor);

Oracle 10.1
SELECT * FROM table(CAST (dbms_xplan.display_cursor(NULL,NULL, 'runstats_last') AS SYS.DBMS_XPLAN_TYPE_TABLE));

-- 10gR1
SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL, 'runstats_last'));

-- 11g
SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL, 'iostats memstats last partition'));

update and undo

2009-02-03T16:40:00.000-08:00

Goal
----
Research how to skip the update on some columns.
Logically, Update table set column = column is an option.
Need to check the resource usage.

Result
------
Update column without change the value, still generate same amount of undo and redo.
The undo is proportion to target value size.

Tested on Oracle 11.1.0.7

Setup
-----


drop table t;
drop table t1;

create table t nologging
as
select rownum id, lpad('x',500,'x') c1
from dual
connect by level <= 50000;

create table t1 nologging
as
select rownum id, lpad('x',500,'x') c1
from dual
connect by level <= 50000;

set serveroutput on

Benchmark
---------
exec runstats_pkg.rs_start;
update t set id = id, c1 = c1;
commit;
exec runstats_pkg.rs_middle;
update t1 set id = id;
commit;
exec runstats_pkg.rs_stop;


Run1 ran in 414 hsecs
Run2 ran in 119 hsecs
run 1 ran in 347.9% of the time

Name                                  Run1        Run2        Diff
LATCH.shared pool                      117         120           3
STAT...redo synch time                  23           7         -16
STAT...redo buffer allocation           16           0         -16
LATCH.DML lock allocation               22           5         -17
STAT...physical reads                1,786       1,808          22
LATCH.redo writing                      62          22         -40
LATCH.redo allocation                   78          23         -55
STAT...CPU used by this sessio          73          16         -57
LATCH.enqueues                         152          84         -68
STAT...commit cleanouts                979       1,120         141
STAT...rollback changes - undo         142           0        -142
LATCH.cache buffers lru chain        4,909       5,184         275
STAT...Elapsed Time                    414         119        -295
STAT...DB time                         418         119        -299
STAT...IMU Redo allocation siz       9,412      15,396       5,984
STAT...consistent changes           48,013          30     -47,983
STAT...consistent gets              51,861       1,878     -49,983
STAT...redo entries                 54,046       3,579     -50,467
STAT...db block gets                56,883       2,111     -54,772
STAT...IMU undo allocation siz      63,992       1,080     -62,912
STAT...db block changes            110,023       7,330    -102,693
STAT...session logical reads       108,744       3,989    -104,755
STAT...physical read bytes      29,261,824  29,622,272     360,448
LATCH.cache buffers chains         487,509      26,861    -460,648
STAT...undo change vector size  32,362,024   2,399,528 -29,962,496
STAT...redo size                69,525,648   5,092,152 -64,433,496

Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
521,401      45,711    -475,690  1,140.65%

External table benchmark fixed-length vs. delimited format

2009-02-02T16:43:00.000-08:00

Goal
----
Compare the External Table read performance on Fix length format vs Tab(any char) Delimited format.

Result
------
Fixed-length format flat file use up about 10% less CPU and 7 times less Latches resource than delimited format.
But Delimited format took 50% less Elapsed Time in my test case.
And one interesting point is that DB time for Fixed-length format is 0.

Tested on Oracle 11.1.0.7


..............................Fixed-Length...Delimited........Diff
STAT...CPU used by this sessio         179         202          23  ***
STAT...Elapsed Time                    179          91         -88  ***
STAT...DB time                           0         421         421  xxx
LATCH.process queue reference            1      58,878      58,877  ***
STAT...physical read bytes         212,992           0    -212,992
STAT...session uga memory max      604,728     115,320    -489,408
STAT...session pga memory max      720,896     131,072    -589,824

Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
7,629      64,444      56,815     11.84%

# Fixed-length records are processed faster than records terminated by a string.
# Fixed-length fields are processed faster than delimited fields.

Advice
------
When runing single batch job, Delimited format is 2 times faster, a little more CPU used, less PGA memory.
When runing multi concurrent batch jobs, Fixed-length format is better, and good for scale out,
but Fixed-length format require more PGA memory and more physical reads, since the file size is bigger to hold the space.

Solution
--------
Set up a typical emp test table, generate 700,000 rows, and run the benchmark.

setup
-----


drop TABLE EMP;
CREATE TABLE EMP
(
  EMPNO     NUMBER(4)                           NOT NULL,
  ENAME     VARCHAR2(10 BYTE),
  JOB       VARCHAR2(9 BYTE),
  MGR       NUMBER(4),
  HIREDATE  DATE,
  SAL       NUMBER(7,2),
  COMM      NUMBER(7,2),
  DEPTNO    NUMBER(2),
  DML_TIME  TIMESTAMP(3)                        DEFAULT systimestamp
)
;

alter table emp add CONSTRAINT EMP_PK
 PRIMARY KEY
 (EMPNO) using INDEX NOLOGGING
;

SET DEFINE OFF;

Insert into SCOTT.EMP   (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO, DML_TIME)
 Values   (7369, 'SMITH', 'CLERK', 7902, TO_DATE('12/17/1980 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 30847.56, 20, TO_TIMESTAMP('2/2/2009 2:56:51.766 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
Insert into SCOTT.EMP   (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DML_TIME)
 Values   (7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE('02/20/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1600, 301, 30, TO_TIMESTAMP('2/2/2009 2:56:51.766 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
Insert into SCOTT.EMP   (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DML_TIME)
 Values   (7521, 'WARD', 'SALESMAN', 7698, TO_DATE('02/22/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1250, 501, 30, TO_TIMESTAMP('2/2/2009 2:56:51.766 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
Insert into SCOTT.EMP   (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO, DML_TIME)
 Values   (7566, 'JONES', 'MANAGER', 7839, TO_DATE('04/02/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 2975, 20, TO_TIMESTAMP('2/2/2009 2:56:51.766 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
Insert into SCOTT.EMP   (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DML_TIME)
 Values   (7654, 'MARTIN', 'SALESMAN', 7698, TO_DATE('09/28/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1250, 1401, 30, TO_TIMESTAMP('2/2/2009 2:56:51.766 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
Insert into SCOTT.EMP   (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO, DML_TIME)
 Values   (7698, 'BLAKE', 'MANAGER', 7839, TO_DATE('05/01/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 2850, 30, TO_TIMESTAMP('2/2/2009 2:56:51.766 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
Insert into SCOTT.EMP   (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO, DML_TIME)
 Values   (7782, 'CLARK', 'MANAGER', 7839, TO_DATE('06/09/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 2450, 10, TO_TIMESTAMP('2/2/2009 2:56:51.766 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
Insert into SCOTT.EMP   (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO, DML_TIME)
 Values   (7788, 'SCOTT', 'ANALYST', 7566, TO_DATE('12/09/1982 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 3000, 20, TO_TIMESTAMP('2/2/2009 2:56:51.766 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
Insert into SCOTT.EMP   (EMPNO, ENAME, JOB, HIREDATE, SAL, DEPTNO, DML_TIME)
 Values   (7839, 'KING', 'PRESIDENT', TO_DATE('11/17/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 5000, 10, TO_TIMESTAMP('2/2/2009 2:56:51.766 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
Insert into SCOTT.EMP   (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DML_TIME)
 Values   (7844, 'TURNER', 'SALESMAN', 7698, TO_DATE('09/08/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1500, 1, 30, TO_TIMESTAMP('2/2/2009 2:56:51.766 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
Insert into SCOTT.EMP   (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO, DML_TIME)
 Values   (7876, 'ADAMS', 'CLERK', 7788, TO_DATE('01/12/1983 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1100, 20, TO_TIMESTAMP('2/2/2009 2:56:51.766 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
Insert into SCOTT.EMP   (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO, DML_TIME)
 Values   (7900, 'JAMES', 'CLERK', 7698, TO_DATE('12/03/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 950, 30, TO_TIMESTAMP('2/2/2009 2:56:51.766 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
Insert into SCOTT.EMP   (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO, DML_TIME)
 Values   (7902, 'FORD', 'ANALYST', 7566, TO_DATE('12/03/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 3000, 20, TO_TIMESTAMP('2/2/2009 2:56:51.766 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
Insert into SCOTT.EMP   (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO, DML_TIME)
 Values   (7934, 'MILLER', 'CLERK', 7782, TO_DATE('01/23/1982 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1300, 10, TO_TIMESTAMP('2/2/2009 2:56:51.766 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
COMMIT;

CREATE OR REPLACE DIRECTORY data_dir AS '/u01/exp';

set linesize 1000
set pagesize 0
set serveroutput off
set trimspool on
set trimout on
set head off
set echo off
set term off
set verify off
set feedback off

-- Fixed length format external table

spool emp.txt
with a as (select rownum from dual connect by level <= 50000)
select 
rpad(EMPNO, 10, ' ')||rpad(ENAME, 20, ' ')||rpad(job, 20, ' ')||trim(to_char(sal,'00000.00'))||' '
||rpad(to_char(HIREDATE,'MM/DD/YYYY HH24:MI:SS'), 21, ' ')
||rpad(to_char(systimestamp,'MM/DD/YYYY HH24:MI:SS.FF3'), 25, ' ')
from scott.emp, a;
spool off

drop table emp_ext_fix;
create table emp_ext_fix
(
  EMPNO     NUMBER(4) ,
  ENAME     VARCHAR2(10 BYTE),
  JOB       VARCHAR2(9 BYTE),
  SAL       Number(7,2),
  HIREDATE date,
  DML_Time TIMESTAMP
)
organization external
(type oracle_loader
 default directory data_dir
 access parameters
 (
 records delimited by newline
 DATE_CACHE 3000
 badfile data_dir:'emp.bad' 
 logfile data_dir:'emp.log' 
 fields LRtrim
 missing field values are null
 REJECT ROWS WITH ALL NULL FIELDS
  ( 
   empno position(1:10) INTEGER EXTERNAL,
   ename position(*:+20) CHAR ,
   job   position(*:+20)  char ,
   sal   position(*:+9) char ,
   HIREDATE position(*:+21) DATE "MM/DD/YYYY HH24:MI:SS",
   DML_TIME position(*:+25) CHAR(25) date_format TIMESTAMP MASK "MM/DD/YYYY HH24:MI:SS.FF3"
  )
 ) 
 LOCATION ('emp.txt') 
) 
NoPARALLEL 
REJECT LIMIT UNLIMITED; 

select * from EMP_ext_fix where rownum < 5;


--Tab delimited or csv file

spool emp.csv
with a as (select rownum from dual connect by level <= 50000)
select EMPNO||chr(9)||ENAME||chr(9)||job||chr(9)||sal||chr(9)
||to_char(HIREDATE,'MM/DD/YYYY HH24:MI:SS')||chr(9)
||to_char(systimestamp,'MM/DD/YYYY HH24:MI:SS.FF3')
from scott.emp,a;
spool off

drop TABLE SCOTT.EMP_ext_csv;
CREATE TABLE SCOTT.EMP_ext_csv
(
  EMPNO     NUMBER(4) ,
  ENAME     VARCHAR2(10 BYTE),
  JOB       VARCHAR2(9 BYTE),
  SAL       Number(7,2),
  HIREDATE date,
  DML_Time TIMESTAMP
)
ORGANIZATION EXTERNAL
    (
           TYPE ORACLE_LOADER 
           DEFAULT DIRECTORY data_dir
           ACCESS PARAMETERS 
           ( 
             records delimited by newline 
             badfile data_dir:'emp.bad' 
             logfile data_dir:'emp.log' 
             fields terminated by 0x'09' 
             missing field values are null 
             ( 
             EMPNO, ENAME, JOB, SAL, HIREDATE DATE "MM/DD/YYYY HH24:MI:SS",
             dml_time char(25) date_format TIMESTAMP MASK "MM/DD/YYYY HH24:MI:SS.FF3"
      ) 
           ) 
           LOCATION ('emp.csv') 
         ) 
         PARALLEL 
REJECT LIMIT UNLIMITED; 
select /* */ * from emp_ext_csv a where rownum < 5;

alter system flush buffer_cache;

Benchmark
---------

exec runstats_pkg.rs_start;
select count(HIREDATE) from emp_ext_csv;
exec runstats_pkg.rs_middle;
select count(HIREDATE) from emp_ext_fix;
exec runstats_pkg.rs_stop;

begin
runstats_pkg.rs_start;
for c in (select ename,sal,hiredate from emp_ext_csv)
loop
  Null;
end loop;
runstats_pkg.rs_middle;
for c in (select ename,sal,hiredate from emp_ext_fix)
loop
  Null;
end loop;
runstats_pkg.rs_stop;
end;
/

begin
runstats_pkg.rs_start;
for c in (select * from emp_ext_fix)
loop
  Null;
end loop;
runstats_pkg.rs_middle;
for c in (select * from emp_ext_csv)
loop
  Null;
end loop;
runstats_pkg.rs_stop;
end;
/

*) Output for last test case:

Run1 ran in 179 hsecs
Run2 ran in 91 hsecs
run 1 ran in 196.7% of the time

Name                                  Run1        Run2        Diff
STAT...opened cursors current            1           0          -1
STAT...user I/O wait time                3           2          -1
STAT...db block changes                 36          37           1
STAT...shared hash latch upgra           4           3          -1
STAT...calls to kcmgas                   0           1           1
STAT...redo ordering marks               0           1           1
STAT...redo subscn max counts            0           1           1
STAT...Batched IO single block           1           0          -1
STAT...Batched IO block miss c           1           0          -1
STAT...Batched IO double miss            1           0          -1
STAT...active txn count during           0           1           1
STAT...cleanout - number of kt           0           1           1
STAT...queries parallelized              0           1           1
STAT...DFO trees parallelized            0           1           1
STAT...Parallel operations not           0           1           1
LATCH.process allocation                 0           1           1
LATCH.ksuosstats global area             0           1           1
LATCH.OS process allocation              1           0          -1
LATCH.KMG MMAN ready and start           1           0          -1
LATCH.mostly latch-free SCN              0           1           1
LATCH.lgwr LWN SCN                       0           1           1
LATCH.Consistent RBA                     0           1           1
LATCH.active checkpoint queue            1           0          -1
LATCH.archive process latch              0           1           1
LATCH.redo allocation                    3           4           1
LATCH.list of block allocation           0           1           1
LATCH.Change Notification Hash           1           0          -1
LATCH.space background task la           1           0          -1
LATCH.session timer                      1           0          -1
LATCH.In memory undo latch               3           1          -2
LATCH.job_queue_processes para           2           0          -2
LATCH.ASM db client latch                0           2           2
STAT...db block gets                    30          27          -3
STAT...db block gets from cach          30          27          -3
STAT...shared hash latch upgra           0           3           3
LATCH.undo global data                   1           4           3
LATCH.channel operations paren           7          11           4
LATCH.redo writing                       1           5           4
STAT...table scans (short tabl           1           6           5
STAT...lob reads                         0           5           5
STAT...undo change vector size       2,472       2,480           8
STAT...session cursor cache hi           1          10           9
STAT...free buffer requested            17           7         -10
LATCH.messages                          16           6         -10
LATCH.cache buffers lru chain           12           1         -11
STAT...physical read total IO           13           0         -13
STAT...physical reads                   13           0         -13
STAT...physical reads cache             13           0         -13
STAT...physical read IO reques          13           0         -13
LATCH.enqueues                          82          68         -14
LATCH.object queue header heap          16           1         -15
LATCH.resmgr group change latc           0          16          16
LATCH.resmgr:actses change gro           1          17          16
LATCH.compile environment latc           0          16          16
LATCH.PL/SQL warning settings            0          16          16
LATCH.Real-time plan statistic           0          17          17
LATCH.kokc descriptor allocati           2          20          18
LATCH.parallel query stats               1          20          19
STAT...CPU used by this sessio         179         202          23  ***
STAT...recursive cpu usage             171         199          28
LATCH.object queue header oper          34           6         -28
STAT...enqueue releases                  5          34          29
LATCH.session state list latch           3          32          29
LATCH.simulator hash latch              14          45          31
STAT...enqueue conversions               0          32          32
LATCH.parameter table manageme           0          32          32
LATCH.resmgr:free threads list           0          32          32
LATCH.dummy allocation                   1          33          32
LATCH.resmgr:active threads              0          32          32
LATCH.error message lists                0          33          33
STAT...session cursor cache co           7          43          36
LATCH.JS queue state obj latch          36           0         -36
STAT...enqueue requests                  5          50          45
STAT...parse count (total)               7          53          46
STAT...execute count                     7          53          46
LATCH.enqueue hash chains               86         133          47
LATCH.checkpoint queue latch            17          65          48
STAT...workarea memory allocat         -26          37          63
STAT...user calls                        0          64          64
LATCH.query server freelists             1          68          67
STAT...redo size                     2,976       3,044          68
STAT...opened cursors cumulati           7          78          71
LATCH.active service list                8          82          74
LATCH.SQL memory manager worka           5          82          77
STAT...Elapsed Time                    179          91         -88  ***
STAT...index scans kdiixs1              18         108          90
STAT...table scan blocks gotte          18         114          96
LATCH.client/application info            1          97          96
LATCH.process queue                      1          99          98
LATCH.parallel query alloc buf           1         161         160
LATCH.session idle bit                   3         167         164
LATCH.session allocation                 3         189         186
STAT...recursive calls               7,043       7,300         257
STAT...no work - consistent re          77         474         397
STAT...consistent gets from ca          75         490         415
STAT...DB time                           0         421         421  xxx
LATCH.shared pool                       14         517         503
STAT...table fetch by rowid          1,695         186      -1,509
STAT...calls to get snapshot s       1,697         129      -1,568
LATCH.MinActiveScn Latch             1,689         106      -1,583
STAT...rows fetched via callba       1,676          78      -1,598
STAT...index fetch by key            1,676          68      -1,608
LATCH.row cache objects                104       1,881       1,777
STAT...table scan rows gotten          360       2,280       1,920
STAT...buffer is not pinned co       3,415         533      -2,882
LATCH.cache buffers chains           5,332       1,320      -4,012
STAT...PX local messages recv            0       4,262       4,262
STAT...PX local messages sent            0       4,262       4,262
STAT...consistent gets               5,112         706      -4,406
STAT...consistent gets from ca       5,112         706      -4,406
STAT...session logical reads         5,142         733      -4,409
STAT...consistent gets - exami       5,028         210      -4,818
LATCH.process queue reference            1      58,878      58,877  ***
STAT...cell physical IO interc     212,992           0    -212,992
STAT...physical IO disk bytes      212,992           0    -212,992
STAT...physical read total byt     212,992           0    -212,992
STAT...physical read bytes         212,992           0    -212,992
STAT...session uga memory max      604,728     115,320    -489,408
STAT...session uga memory          604,728      84,664    -520,064
STAT...session pga memory          720,896     131,072    -589,824
STAT...session pga memory max      720,896     131,072    -589,824

Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
7,629      64,444      56,815     11.84%

PL/SQL procedure successfully completed.

Reference
---------
Oracle® Database Utilities 11g Release 1 (11.1)
12 External Tables Concepts: Performance Hints When Using External Tables

10 mile point

Output from 10053 trace and dbms_xplan.display_cursor

2009-01-22T21:36:00.000-08:00

Oracle 10.1.0.4 on RedHat Linux 3.0

*************************************
PARAMETERS WITH ALTERED VALUES
******************************
parallel_threads_per_cpu = 4
db_file_multiblock_read_count = 32
optimizer_mode = first_rows_10
cursor_sharing = similar
optimizer_index_caching = 25
query_rewrite_enabled = false
query_rewrite_integrity = stale_tolerated
optimizer_dynamic_sampling = 0
*************************************
PARAMETERS WITH DEFAULT VALUES
******************************
optimizer_features_enable = 10.1.0.4

***************************************
BASE STATISTICAL INFORMATION
***********************
Table stats Table: PO_ITEM Alias: PO_ITEM
TOTAL :: CDN: 81294 NBLKS: 3257 AVG_ROW_LEN: 132
Index stats
Index: PO_ITEM_POID COL#: 2
TOTAL :: LVLS: 2 #LB: 365 #DK: 80100 LB/K: 1 DB/K: 1 CLUF: 3191
Index: PO_ITEM_STATUS_RANDOM_UPDDT COL#: 4 6
TOTAL :: LVLS: 2 #LB: 507 #DK: 80100 LB/K: 1 DB/K: 1 CLUF: 45647
Index: PO_ITEM_STATUS_UPDDT COL#: 3 6
TOTAL :: LVLS: 2 #LB: 507 #DK: 80100 LB/K: 1 DB/K: 1 CLUF: 80100
***************************************
SINGLE TABLE ACCESS PATH
COLUMN: POID(NUMBER) Col#: 2 Table: PO_ITEM Alias: PO_ITEM
Size: 5 NDV: 80100 Nulls: 0 Density: 1.2484e-05 Min: 40 Max: 88988
COLUMN: STATUS_RAN(CHARACTER) Col#: 4 Table: PO_ITEM Alias: PO_ITEM
Size: 3 NDV: 20 Nulls: 0 Density: 6.1505e-06
Histogram: Freq #Bkts: 20 UncompBkts: 3985 EndPtVals: 20
TABLE: PO_ITEM Alias: PO_ITEM
Original Card: 81294 Rounded: 1 Computed: 0.00 Non Adjusted: 0.00
Access Path: table-scan Resc: 393 Resp: 393
Access Path: index (equal)
Index: PO_ITEM_POID
rsc_cpu: 24314 rsc_io: 4
ix_sel: 1.2670e-05 ix_sel_with_filters: 1.2670e-05
Access Path: index (scan)
Index: PO_ITEM_STATUS_RANDOM_UPDDT
rsc_cpu: 23778 rsc_io: 4
ix_sel: 6.2422e-06 ix_sel_with_filters: 6.2422e-06
Access Path: index (equal)
Index: PO_ITEM_POID
rsc_cpu: 17832 rsc_io: 3
ix_sel: 1.2670e-05 ix_sel_with_filters: 1.2670e-05
Access Path: index (index-only)
Index: PO_ITEM_STATUS_RANDOM_UPDDT
rsc_cpu: 17632 rsc_io: 3
ix_sel: 6.2422e-06 ix_sel_with_filters: 6.2422e-06
SORT resource Sort statistics
Sort width: 148 Area size: 524288 Max Area size: 26214400
Degree: 1
Blocks to Sort: 1 Row size: 21 Total Rows: 1
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 15225730
Total Temp space used: 0
BEST_CST: 4.00 PATH: 4 Degree: 1
***************************************
GENERAL PLANS
***********************
Join order[1]: PO_ITEM[PO_ITEM]#0
ORDER BY sort
SORT resource Sort statistics
Sort width: 148 Area size: 524288 Max Area size: 26214400
Degree: 1
Blocks to Sort: 1 Row size: 156 Total Rows: 1
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 15225730
Total Temp space used: 0
Best so far: TABLE#: 0 CST: 5 CDN: 1 BYTES: 132
(newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:80000
SORT resource Sort statistics
Sort width: 148 Area size: 524288 Max Area size: 26214400
Degree: 1
Blocks to Sort: 1 Row size: 156 Total Rows: 1
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 15225730
Total Temp space used: 0
Final - All Rows Plan:
JOIN ORDER: 1
CST: 5 CDN: 1 RSC: 5 RSP: 5 BYTES: 132
IO-RSC: 4 IO-RSP: 4 CPU-RSC: 15249507 CPU-RSP: 15249507

SQL> SELECT /*+ gather_plan_statistics */ * FROM po_item WHERE poid = :a AND STATUS_random = :b ORDER BY poitemid;

SQL> SELECT * FROM table (Cast(dbms_xplan.display_cursor(NULL,NULL, 'runstats_last') as SYS.DBMS_XPLAN_TYPE_TABLE));

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

SQL_ID cp44zcmqma34f, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ * FROM po_item WHERE poid = :a AND STATUS_random = :b ORDER BY poitemid

Plan hash value: 3970202189

------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | A-Rows | Buffers | Reads | Writes | A-Time |
------------------------------------------------------------------------------------------------------------------------------
| 1 | SORT ORDER BY | | 1 | 0 | 2377 | 0 | 0 |00:00:00.01 |
|* 2 | TABLE ACCESS BY INDEX ROWID| PO_ITEM | 1 | 0 | 2377 | 0 | 0 |00:00:00.01 |
|* 3 | INDEX RANGE SCAN | PO_ITEM_STATUS_RANDOM_UPDDT | 1 | 4153 | 29 | 0 | 0 |00:00:00.02 |
------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1
2 - SEL$1 / PO_ITEM@SEL$1
3 - SEL$1 / PO_ITEM@SEL$1

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

2 - filter("POID"=:A)
3 - access("STATUS_RANDOM"=:B)

27 rows selected.

SQL> set autot on
SQL>
SELECT /* */ * FROM po_item WHERE poid = :a AND STATUS_random = :b ORDER BY poitemid;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 3115544074

---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 132 | 5 (20)| 00:00:01 |
| 1 | SORT ORDER BY | | 1 | 132 | 5 (20)| 00:00:01 |
|* 2 | TABLE ACCESS BY INDEX ROWID| PO_ITEM | 1 | 132 | 4 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | PO_ITEM_POID | 1 | | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

2 - filter("STATUS_RANDOM"=:B)
3 - access("POID"=TO_NUMBER(:A))

Fix SQL index access path issue with 10053 trace and hack the stats

2009-01-22T20:33:00.000-08:00

Goal
----
Find out why SQL optimizer select a bad index access path when there are 2 predicates,
and each predicate column got an index created on it.

# SQL
SELECT /* */ * FROM po_item WHERE poid = :a AND STATUS = :b ORDER BY poitemid;

INDEX PO_ITEM_POID ON PO_ITEM(POID)
INDEX PO_ITEM_STATUS_UPDDT ON PO_ITEM(STATUS, STATUSUPDDT)

It should access data by index on POID column, but it suddenly changed to use index on STATUS column.

Solution
--------
Understand CBO,experiment,observe and interpret.
With SQL session 10053 trace and dbms_xplan.display_cursor().

Result
------
When database collect histogram stats on a column, it calculate a new density for the column,
and it will be extreme smaller, CBO use density as selectivity.
After we hack the column density, database will clear the histogram stats,
the CBO will use 1/NumberOfDistinct, ignore density.

When we check the output of 10053 trace,
both IO costs are same, but index selectivity (ix_sel) and table selectivity (ix_sel_with_filters)
on Index: PO_ITEM_STATUS_RANDOM_UPDDT is smaller than than it on Index: PO_ITEM_POID.

Access Path: index (equal)
Index: PO_ITEM_POID
rsc_cpu: 17832 rsc_io: 3
ix_sel: 1.2670e-05 ix_sel_with_filters: 1.2670e-05
Access Path: index (index-only)
Index: PO_ITEM_STATUS_RANDOM_UPDDT
rsc_cpu: 17632 rsc_io: 3
ix_sel: 6.2422e-06 ix_sel_with_filters: 6.2422e-06

*) Fix: hack the density, make it accurate.

10053 trace output and dbms_xplan page link.

Experiment
----------

InBox
-----

Next Action
-----------

Complete
--------
create test table po_item
- evenly distribution Mod(), value from '01' to '20'
- random distribution dbms_random.value() '01' to '20'
- normal distribution dbms_random.normal() '01' to '99'
create index
collect table stats
collect column STATUS histogram stats
-- This step skipped: run SQL UPDATE to generate required data distribution
run SQL
- enable 10053 trace
- dbms_xplan.display_cursor()
- set autotrace on

*) Purchase_Order Item table

drop TABLE PO_ITEM;
CREATE TABLE PO_ITEM
(
POITEMID NUMBER(8),
POID NUMBER(8),
STATUS CHAR(2 BYTE),
STATUS_random CHAR(2 BYTE),
STATUS_normal CHAR(2 BYTE),
STATUSUPDDT DATE,
ITEMTITLE VARCHAR2(50 BYTE),
ITEMAUTHOR VARCHAR2(50 BYTE),
ITEMPRICE NUMBER(8,2)
)
nologging;

execute dbms_random.seed(0)

insert /*+ append */ into po_item
with g as (
select -- materialize
level
from dual
connect by level <= 2000
)
select
/*+ leading(v1,v2,v3) use_nl(v2) use_nl(v3) */
rownum
,Round(rownum/0.9,0)
,To_Char(Round(Mod(rownum,100)))
,To_Char(Round(dbms_random.value(1,20)))
,LTrim(To_Char(ABS(Mod(10 * dbms_random.normal,100)),'99'))
,To_Date('1999','YYYY') + rownum/86400
,rpad(rownum,50,'x')
,rpad(rownum,50,'*')
,Round(dbms_random.value(1,100),2)
from
g v1
,g v2
,g v3
where
rownum <= 80100;

commit;
exec dbms_stats.gather_table_stats(user,'PO_ITEM');

-- Index on POID column

CREATE INDEX PO_ITEM_POID ON PO_ITEM
(POID)
NoLogging;

-- Index on column STATUS and STATUS_UPD_DATE

CREATE INDEX PO_ITEM_STATUS_UPDDT ON PO_ITEM
(STATUS, STATUSUPDDT)
NoLogging;

-- Index on column STATUS_random and STATUS_UPD_DATE

CREATE INDEX PO_ITEM_STATUS_RANDOM_UPDDT ON PO_ITEM
(STATUS_random, STATUSUPDDT)
NoLogging;

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

*) init parameter
alter system set optimizer_index_cost_adj=25;
alter system set optimizer_index_cost_adj=100;
alter system set optimizer_index_caching=0;
alter system set optimizer_index_caching=25;
alter system set optimizer_mode = FIRST_ROWS_10;
alter system flush shared_pool;
alter system flush BUFFER_CACHE;

set autot off
set serveroutput off
variable a NUMBER;
variable b VARCHAR2(2);

exec :a := 72036876;
exec :a := 70100;
exec :b := '98';
exec :b := '17';

*) enable 10053 trace

ALTER SESSION SET TRACEFILE_IDENTIFIER="i";
ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';

SELECT /* */ * FROM po_item WHERE poid = :a AND STATUS = :b ORDER BY poitemid;
SELECT /* */ * FROM po_item WHERE poid = :a AND STATUS_random = :b ORDER BY poitemid;
SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR);

alter session set events '10053 trace name context off';

*) display SQL execution plan

-- uses the hint gather_plan_statistics to enable the generation of the execution statistics.
set linesize 300
SELECT /*+ gather_plan_statistics */ * FROM po_item WHERE poid = :a AND STATUS = :b ORDER BY poitemid;
SELECT * FROM table (Cast(dbms_xplan.display_cursor(NULL,NULL, 'runstats_last') as SYS.DBMS_XPLAN_TYPE_TABLE));

SELECT /*+ gather_plan_statistics */ * FROM po_item WHERE poid = :a AND STATUS_random = :b ORDER BY poitemid;
SELECT * FROM table (Cast(dbms_xplan.display_cursor(NULL,NULL, 'runstats_last') as SYS.DBMS_XPLAN_TYPE_TABLE));

set autot trace exp
SELECT /*+ gather_plan_statistics */ * FROM po_item WHERE poid = :a AND STATUS = :b ORDER BY poitemid;

-- gather histogram stats on column STATUS
begin
dbms_stats.gather_table_stats(
user,
'po_item',
cascade => true,
estimate_percent => 0.1,
method_opt => 'for columns size 254 status,status_random,status_normal size 100'
);
end;
/

-- hack column stats
begin
DBMS_STATS.SET_COLUMN_STATS (
ownname => USER,
tabname => 'PO_ITEM',
colname => 'STATUS',
-- distcnt => 20,
density => 0.05,
force => True
);
end;
/

-- Compare index access on right index
set autot trace

SELECT /*+ index(po_item,PO_ITEM_POID) */ * FROM PO_ITEM
WHERE poid = :a AND STATUS = :b ORDER BY poitemid;

Reference
---------
*) origianl table and SQL:
abedba.ABEPOITEMS
SELECT /* */ * FROM ABEPOITEMS WHERE ABEPOID = :a AND STATUS = :b ORDER BY ABEPOITEMID;

*) Get stats

-- Display table stats
set autot off
set SQLPATH=A:\maint\monitor;A:\adm\maint

@table scott po_item

-- check CBO parameter
select name,value,isdefault from v$sql_optimizer_env
where sql_id = 'cp44zcmqma34f';

-- Query to check the value of input hidden "parameter_name"
col indx format 9999
col inst_id heading "INST" format 9999
col ksppinm heading "NAME" format a30
col ksppdesc heading "DESC" format a40
col ksppstvl heading "CURR VAL" format a15
col ksppstdvl heading "DEFAULT VAL" format a15

set echo on
set autot off
select v.indx,v.inst_id,ksppinm p_name,ksppstvl curr_val,ksppstdvl default_val,ksppdesc p_desc
from x$ksppi i ,x$ksppcv v
where i.indx=v.indx and ksppinm like '%sort_elimination_cost_ratio%';

-- display real value for low_value and high_value on view user_tab_col_statistics
DECLARE
l_low_value user_tab_col_statistics.low_value%TYPE;
l_high_value user_tab_col_statistics.high_value%TYPE;
l_val1 ABEPOITEMS.STATUS%TYPE;
BEGIN
SELECT low_value, high_value
INTO l_low_value, l_high_value
FROM user_tab_col_statistics
WHERE table_name = 'PO_ITEM'
AND column_name = 'STATUS';

dbms_stats.convert_raw_value(l_low_value, l_val1);
dbms_output.put_line('low_value: ' || l_val1);
dbms_stats.convert_raw_value(l_high_value, l_val1);
dbms_output.put_line('high_value: ' || l_val1);

dbms_output.put_line('high_value: ' || l_low_value||', '||l_high_value);

END;
/

exec DBMS_STATS.UnLOCK_TABLE_STATS(user,'ABEPOitemS');

-- Verify data distribution
select status,count(*)
from abedba.po_item
group by status;

STATUS COUNT(*)
01 2795
04 619
05 252
10 1334827
11 7298222
12 232920
14 313380
15 1912919
16 8764
17 233443
18 116935
19 549803
24 748465
25 356031
35 12905639
45 7594712
50 235
51 75338
52 334234
53 22
54 22325
55 5110
56 12311
60 3
66 245
90 215
97 54017
98 83
--

*) hack object stats

-- hack index stats. --old 3931, new 40100
begin
DBMS_STATS.SET_INDEX_STATS (
ownname => USER,
indname => 'PO_ITEM_POID',
clstfct => 50);
end;
/

begin
DBMS_STATS.SET_INDEX_STATS (
ownname => 'SCOTT',
indname => 'PO_ITEM_STATUS_UPDDT',
clstfct => 1000);
end;
/

-- hack column stats
begin
DBMS_STATS.SET_COLUMN_STATS (
ownname => USER,
tabname => 'PO_ITEM',
colname => 'STATUS',
density => 0.05);
end;
/

*) Export and Import the table stats

begin
dbms_stats.create_stat_table(ownname => user,
stattab => 'MYSTATS',
tblspace => 'USERS');
end;
/

begin
DBMS_STATS.EXPORT_TABLE_STATS (
ownname => USER,
tabname => 'PO_ITEM',
stattab => 'MYSTATS');
end;
/

exec dbms_stats.delete_table_stats(ownname => user,tabname => 'PO_ITEM')

begin
DBMS_STATS.IMPORT_TABLE_STATS (
ownname => USER,
tabname => 'PO_ITEM',
stattab => 'MYSTATS',
force => true);
end;
/

*) others

-- Tune sort index over the access predicate index
alter session set "_sort_elimination_cost_ratio" = 0;

SELECT /*x index(po_item,PO_ITEM_STATUS_UPDDT) */ * FROM PO_ITEM
WHERE poid = :a AND STATUS = :b ORDER BY poitemid;

alter session set "_sort_elimination_cost_ratio" = 1;

SELECT /*x index(po_item,PO_ITEM_STATUS_UPDDT) */ * FROM PO_ITEM
WHERE poid = :a AND STATUS = :b ORDER BY poitemid;

QingDao, China

Overview asynchronous process change data

2009-01-14T11:27:00.000-08:00

Goal
----
Split function :F1 from funcation :F2, asynchronous the process,
to gain scalability(performance and reliability) and availability.

:F1 = check deleted book
:F2 = upload inventory data

*) Business logic background for this example

In our 4 node RAC database, :F2 = upload inventory data runs on node 4,
:F1 = check deleted book runs on node 1 frequently, 50 times/second;
:F1 is hitting the data randomly, 30 rows a page will probably touch 30 data blocks.
This caused lots of Global Cache inter-connect traffic from node 4 to node 1.
Based on our AWR report, it is

Network band width:
= Network requests * db_block_size
= 218104 * 4096
= 893,353,984 (Transfer about 900Mbytes data in 15 minutes)

After implement asynchronous process, the GC transfer volume reduced 200 times for this function,

The F1 check deleted book performance SLA improved 100 times.

Figure-1: Topology process diagram

*) Keyword: Loose coupling, Function split, Partition data by function and range/key list.

Solution
--------
Queue the change data (deleted book listing_id) into a staging table,
batch process and merge it to target table (book_delete_check) every N minutes,
to reduce the network traffic.

*) Understanding: we will lose unprocessed data if we use SQL start date as end tag timestamp
*) Experiment and Observation:
- replace Synonym will not require DDL lock, not safe
- in MERGE SQL, DELETE share UPDATE predicate, and will not delete INSERT rows
- query gv$ view may fail in a RAC database when there is not enough parallel processes on each instance
*) Interpretation: use predicate stage_table.update_date < v$transaction.start_date

Approach
--------

*) mark rowid into a temporary table, process and delete by rowid in the temporary table
*) update_date < Least(SysDate,v$transaction.start_date)
*) SCN_TO_TIMESTAMP(ORA_ROWSCN) < sysdate
- http://www.ningoo.net/html/2009/about_ora_rowscn.html
*) Exchange one data partition with a swap table
*) RETURNING rows BULK COLLECT INTO UDT(User Define Type schema object collection)
*) Many partitions on Mod(to_number(to_char(update_date,'MI')),10)
- Partition table to spread the DDL/DML lock contention
- so N partitions on a centain interval based on your transaction volume and DML lock frequency
- exchange each partion to each swap table
- process the swap table
- truncate the swap table
- the DDL lock will not happen very often
- if partition locked, insert a row to stage_proc_lock table
- if partition locked 4 times continuously
# process one partition with one of above single table method
# delete the locked partition log row
*) Partition view on heap tables for Oracle Standard Edition
*) Oracle Stream
*) Oracle AQ
*) Other Messaging tool

Setup
-----


-- target table
drop table book_del_check;

create table book_del_check
(
  listingsid  number(20),
  upd_dd      number(2)
, constraint  book_del_check_pk primary key (listingsid )
)
organization index
including upd_dd
overflow
;

-- staging table
-- Option n+2, for small TX volume
drop table book_delete_stage;

create table book_delete_stage(
listingsid number(20),
qty number(5),
delete_flag number(1),
update_date date default sysdate
)
ROWDEPENDENCIES
nologging;

create or replace view book_delete_stage_v
as
 select listingsid, delete_flag, To_Number(To_Char(update_date,'DD')) upd_day, upd_time
 from
 (SELECT listingsid, delete_flag,update_date,
   SCN_TO_TIMESTAMP(ORA_ROWSCN) upd_time,
   Row_Number() OVER (PARTITION BY listingsid order by update_date desc) rn
  FROM book_delete_stage b
 )
 where rn = 1
;


drop TYPE book_chg_tab;
drop TYPE book_chg_rec;

create or replace TYPE book_chg_rec AS OBJECT
(
listingsid number(20),
qty number(5),
delete_flag number(1),
update_date timestamp(3)
);
/

create or replace TYPE book_chg_tab IS TABLE OF book_chg_rec;
/

drop table gt_book_delete_stage_i;
drop table gt_book_delete_stage_d;
drop table gt_book_delete_stage_rid;

create global temporary table gt_book_delete_stage_i
(
listingsid number(20),
qty number(5)
)
ON COMMIT DELETE ROWS
;
create global temporary table gt_book_delete_stage_d
(
listingsid number(20),
qty number(5)
)
ON COMMIT DELETE ROWS
;

create global temporary table gt_book_delete_stage_rid
(
rid rowid,
listingsid number(20)
)
ON COMMIT DELETE ROWS
;

drop table stage_proc_lock;
create table stage_proc_lock
(
stage_table varchar2(30),
part_key number(2)
)
tablespace index_auto;

create index stage_proc_lock_i1 on stage_proc_lock(stage_table,part_key)
nologging tablespace index_auto;

DROP TABLE book_delete_stage_part;

CREATE TABLE book_delete_stage_part (
listingsid number(20),
qty number(5),
delete_flag number(1),
update_date timestamp(3) default SysTimestamp,
part_mi number(2) default mod(to_number(to_char(sysdate,'MI')),10)
)
PARTITION BY LIST (part_mi) (
  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 data_part_6 VALUES (6),
  PARTITION data_part_7 VALUES (7),
  PARTITION data_part_8 VALUES (8),
  PARTITION data_part_9 VALUES (9),
  PARTITION dummy_null  VALUES (NULL),
  PARTITION dummy_other VALUES (DEFAULT)
)
tablespace data_auto;

DROP TABLE book_delete_stage_swap_0;
DROP TABLE book_delete_stage_swap_1;
DROP TABLE book_delete_stage_swap_2;
DROP TABLE book_delete_stage_swap_3;
DROP TABLE book_delete_stage_swap_4;
DROP TABLE book_delete_stage_swap_5;
DROP TABLE book_delete_stage_swap_6;
DROP TABLE book_delete_stage_swap_7;
DROP TABLE book_delete_stage_swap_8;
DROP TABLE book_delete_stage_swap_9;

CREATE TABLE book_delete_stage_swap_0 (listingsid number(20),qty number(5),delete_flag number(1),update_date timestamp(3),part_mi number(2)) tablespace data_auto;
CREATE TABLE book_delete_stage_swap_1 (listingsid number(20),qty number(5),delete_flag number(1),update_date timestamp(3),part_mi number(2)) tablespace data_auto;
CREATE TABLE book_delete_stage_swap_2 (listingsid number(20),qty number(5),delete_flag number(1),update_date timestamp(3),part_mi number(2)) tablespace data_auto;
CREATE TABLE book_delete_stage_swap_3 (listingsid number(20),qty number(5),delete_flag number(1),update_date timestamp(3),part_mi number(2)) tablespace data_auto;
CREATE TABLE book_delete_stage_swap_4 (listingsid number(20),qty number(5),delete_flag number(1),update_date timestamp(3),part_mi number(2)) tablespace data_auto;
CREATE TABLE book_delete_stage_swap_5 (listingsid number(20),qty number(5),delete_flag number(1),update_date timestamp(3),part_mi number(2)) tablespace data_auto;
CREATE TABLE book_delete_stage_swap_6 (listingsid number(20),qty number(5),delete_flag number(1),update_date timestamp(3),part_mi number(2)) tablespace data_auto;
CREATE TABLE book_delete_stage_swap_7 (listingsid number(20),qty number(5),delete_flag number(1),update_date timestamp(3),part_mi number(2)) tablespace data_auto;
CREATE TABLE book_delete_stage_swap_8 (listingsid number(20),qty number(5),delete_flag number(1),update_date timestamp(3),part_mi number(2)) tablespace data_auto;
CREATE TABLE book_delete_stage_swap_9 (listingsid number(20),qty number(5),delete_flag number(1),update_date timestamp(3),part_mi number(2)) tablespace data_auto;


-- Package
@BOOK_CHG_SYNC_PKG.SQL

alter package book_chg_sync_pkg compile;
-- to avoid ORA-4061
alter package list_trigger_pkg compile;

-- to avoid ORA-4061
-- Synonym
create or replace synonym BOOK_CHG_SYNC_api for BOOK_CHG_SYNC_PKG;

grant execute on book_chg_sync_api to app_role;

-- Schedule job
exec dbms_scheduler.stop_job(job_name=> 'merge_book_del_change_delta', force => True);
exec dbms_scheduler.DROP_job ( job_name => 'merge_book_del_change_delta' );

begin
   dbms_scheduler.create_job
   (
      job_name      => 'merge_book_del_change_delta',
      job_type      => 'PLSQL_BLOCK',
      job_action    => 'begin book_chg_sync_pkg.upd_delete_track; end;',
      repeat_interval => 'FREQ=MINUTELY; INTERVAL=3',
      enabled         => true,
      comments        => 'To clear all the pending delta data, merge into book_del_check table'
   );
end;
/

Idea
----
-- Option n, online alter synonym pointer, no DDL lock, inconsistent data, unsafe
drop table book_delete_stage;

drop table book_delete_stage_t1;

create table book_delete_stage_t1(
listingsid number(20),
qty number(5),
delete_flag number(1),
update_date date default sysdate
)
tablespace data_auto;

create or replace synonym book_delete_stage for book_delete_stage_t1;

drop table book_delete_stage_t2;

create table book_delete_stage_t2(
listingsid number(20),
qty number(5),
delete_flag number(1),
update_date date default sysdate
)
tablespace data_auto;

-- no DDL lock after DML done.
insert into book_delete_stage
values(1,1,1,sysdate);
commit;

create or replace synonym book_delete_stage for book_delete_stage_t2;

select * from book_delete_stage_t1;
select * from book_delete_stage_t2;
select * from book_delete_stage;

-- Option n+1, online exchange table partition
-- for large TX volume
-- Goal: Partition table to spread the contention

*) Constraint : Must be run serially

*) Process

swap out the partitions Mod(minute,10) are not locked by DML 
insert into temporary table
process
commit
truncate the swap table

*) Test Case

Insert 5 rows to each partition
DML lock on 2 partitions
exec book_chg_sync_pkg.merge_delta_multi_part;
- Swap out other 8 partitions

execute book_chg_sync_pkg.merge_delta_rowscn;

Test case
---------
*) Function

- Delete some listings
- Wait 5 minutes, run book_delete_check query, these listings should disappear.

-) Restorea some listings
-) Wait 5 minutes, run book_delete_check query, these listings should come out.

*) Network performance - Test by Production DBA

To check
Global Cache and Enqueue Services - Workload Characteristics
-Avg global enqueue get time (ms): 0.2
-Avg global cache cr block receive time (ms): 3.7
-Avg global cache current block receive time (ms): 2.5
-Avg global cache cr block build time (ms): 0.0
-Avg global cache cr block send time (ms): 0.1
-Avg global cache current block pin time (ms): 0.0
-Avg global cache current block send time (ms):0.1
Global Cache and Enqueue Services - Messaging Statistics
-Avg message sent queue time (ms): 50.8
-Avg message sent queue time on ksxp (ms): 1.0
-Avg message received queue time (ms): 0.0

Test code
---------

-- Option n+1, online exchange table partition
-- for large TX volume
-- Goal: Partition table to spread the contention

-- Test DELETE in MERGE. (D.upd_dd <> S.upd_day or S.delete_flag = 0).
insert into book_delete_stage_part(listingsid,qty,delete_flag,update_date, part_mi)
values(7,5,0,trunc(sysdate,'DD') + 0, 4);

insert into book_delete_stage_part(listingsid,qty,delete_flag,update_date, part_mi)
values(7,5,0,trunc(sysdate,'DD') + 4, 4);
commit;
insert into book_delete_stage_part(listingsid,qty,delete_flag,update_date, part_mi)
values(7,0,1,trunc(sysdate,'DD') + 4, 4);
commit;

select * from book_delete_stage_part;
select * from book_delete_stage_swap_7;
select * from book_del_check where listingsid = 7;

SQL> Exec book_chg_sync_pkg.merge_delta_multi_part;

select * from book_delete_stage_part;
select * from book_delete_stage_swap_7;
select * from book_del_check where listingsid = 7;

--Generate testing data
insert into book_delete_stage_part(listingsid,delete_flag,update_date, part_mi)
select trunc(dbms_random.value(1,5000)), mod(level,2), trunc(sysdate,'DD') + mod(level,60)/1440,
mod(level,10)
from dual
connect by level <= 7000;

SQL> Exec book_chg_sync_pkg.merge_delta_multi_part;

Reference
---------

-- Exchange partition


--DDL With the WAIT Option, number of seconds, 11g new
alter session set DDL_LOCK_TIMEOUT=120;

-- ORA-00054: resource busy and acquire with NOWAIT specified
ALTER TABLE book_delete_stage_part
EXCHANGE PARTITION data_part_5
WITH TABLE book_delete_stage_swap_5
INCLUDING INDEXES
WITHOUT VALIDATION
UPDATE GLOBAL INDEXES
;

--DDL With the WAIT Option, number of seconds, 11g new
alter session set DDL_LOCK_TIMEOUT=0;

-- check TX view, 1000 LIO
    SELECT to_char(t.start_date,'MI'), Min(t.start_date), count(*)
    FROM   gv$transaction t
          ,gv$locked_object lo
--    FROM   v$transaction t
--          ,v$locked_object lo
            ,sys.obj$ do
    WHERE  do.NAME   = 'BOOK_DELETE_STAGE'
    AND    do.obj#   = lo.object_id
    AND    lo.xidusn = t.xidusn
group by to_char(t.start_date,'MI');

-- Get partition number, skip locked partition
-- check TX view, 1000 LIO
   select Trim(to_char(part_num)) part_num bulk collect into lt_part_num from
   (
    select (level - 1) part_num from dual connect by level <= 10
    minus
    SELECT Mod(to_number(to_char(t.start_date,'MI')),10) mi --, count(*)
    FROM   gv$transaction t
          ,gv$locked_object lo
--    FROM   v$transaction t
--          ,v$locked_object lo
            ,sys.obj$ do
    WHERE  do.NAME   = 'BOOK_DELETE_STAGE_PART'
    AND    do.obj#   = lo.object_id
    AND    lo.xidusn = t.xidusn
    group by Mod(to_number(to_char(t.start_date,'MI')),10)
   );

*)AWR report RAC section anylysis

15 minutes interval in the morning.

The :F1 book_delete_check query used 90% network resource. Make all other database request that compete for network resource (Global cache transfer) worse and worse.
Begin Snap: 13-Nov-08 10:45:20
End Snap: 13-Nov-08 11:00:10

Our practical target is to reduce the network usage 10 to 30 times by applying BASE solution.
DBA can explain why in the story gathering meeting

Network requests :
= (CR Blocks Received + Current Blocks Received) + messages sent + messages received
= (96884 + 12168 ) * 2
= 218104

Note: CR is consistent reads, Current is reads on current data block from disk

Network band width:
= Network requests * db_block_size
= 218104 * 4096
= 893,353,984 (Transfer about 900Mbytes in 15 minutes)

Avg book_delete_check query wait time on network delay:
= ( CR Blocks Received * Avg global cache cr block receive time ms
+ Current Blocks Received * Avg global cache current block receive time ms
+ Global Cache Grant messages * Avg message sent queue time ms
) / Executions / 1000 ms
= (96884 * 60.6 + 12168 * 72.8 + (96884 + 12168) * 201) /45211/1000
= 0.634280436 Seconds

How to generate testing data

2009-01-12T14:50:00.000-08:00

Goal
----
Generate testing data

Interpret
---------
Many times you need to build a test case to prove your ideas or prototype, benchmark, ask questions ... etc.

Tom: You would better supply very very simple create tables and insert statements.
The SMALLEST create table possible (no tablespaces, no schema names, just like I do in my examples for you)

Solution
--------

*) 500 distinct value, to get centain data distribution

Mod(rownum,500)
dbms_random.value(1,500)
dbms_random.normal()

*) string

rpad('x',500,'x')
lpad(RowNum,200,'*')
dbms_random.string('l',500)
# 'u', 'U' - returning string in uppercase alpha characters
# 'l', 'L' - returning string in lowercase alpha characters
# 'p': any printable char. this one is slow

*) source

# dual connect by level <= 3000
# all_objects
# WITH subquery factor

*) Random order by

ORDER BY dbms_random.random;

*) Example

SQL>
--Resets the seed
exec dbms_random.seed(1);

drop table t1;
create table t1
nologging -- adjust as necessary
as
with g as (
select -- materialize
level
from dual
connect by level <= 2000
)
select
/*x leading(v1,v2,v3) use_merge(v2) use_merge(v3) */
/*+ leading(v1,v2,v3) use_nl(v2) use_nl(v3) */
rownum id,
Round(dbms_random.normal * 1000,3) n1,
Mod(rownum,500) n5,
Mod(rownum,400) n4,
Round(dbms_random.value(1,700)) n7b
,rpad(rownum,500,'x') s5
-- ,dbms_random.string('l',20) s5b
,Trunc(sysdate,'YYYY') + Mod(rownum,1440)/1440 update_date
from
g v1
,g v2
,g v3
where
rownum <= 111222
-- order by dbms_random.random
;

create index t1_i1 on t1(n5) nologging ;

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

# MuJiang

DROP TABLE abelisting.book_delete_stage_part;

CREATE TABLE abelisting.book_delete_stage_part (
listingsid number(20),
qty number(5),
delete_flag number(1),
update_date timestamp(3) default SysTimestamp,
part_mi number(2) default mod(to_number(to_char(sysdate,'MI')),10)
)
PARTITION BY LIST (part_mi) (
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 data_part_6 VALUES (6),
PARTITION data_part_7 VALUES (7),
PARTITION data_part_8 VALUES (8),
PARTITION data_part_9 VALUES (9),
PARTITION dummy_null VALUES (NULL),
PARTITION dummy_other VALUES (DEFAULT)
)
tablespace data_auto;

-- 5000 distinct values, 2000 duplicate values
insert into abelisting.book_delete_stage_part(listingsid,delete_flag,update_date, part_mi)
select trunc(dbms_random.value(1,5000)), mod(level,2), trunc(sysdate,'DD') + mod(level,60)/1440,
mod(level,10)
from dual
connect by level <= 7000;

Reference
---------

# Christian Antognini
drop table t;
CREATE TABLE t
AS
SELECT rownum AS id,
round(5678+dbms_random.normal*1234) AS n1,
mod(255+trunc(dbms_random.normal*1000),255) AS n2,
dbms_random.string('p',255) AS pad
FROM dual
CONNECT BY level <= 10000
ORDER BY dbms_random.value;

# Jonathan Lewis

drop table t1;
create table t1
nologging -- adjust as necessary
as
with generator as (
select --+ materialize
rownum id,
substr(dbms_random.string('U',4),1,4) sortcode
from all_objects
where rownum <= 5000
)
select
/*+ ordered use_nl(v2) */
substr(v2.sortcode,1,4) || substr(v1.sortcode,1,2) sortcode,
substr(v1.sortcode,2,2) v2,
substr(v2.sortcode,2,3) v3
from
generator v1,
generator v2
where
rownum <= 1048576
;

# Tanel Põder
Generating lots of rows using connect by - safely!

select rownum r
from
(select rownum r from dual connect by rownum <= 1000) a,
(select rownum r from dual connect by rownum <= 1000) b,
(select rownum r from dual connect by rownum <= 1000) c
where rownum <= 100000000;

AskTom

AskTom

ops$tkyte@ORA9IR2>

CREATE TABLE t1
(
dt date,
x int,
y varchar2(25)
)
PARTITION BY RANGE (dt)
subpartition by hash(x) subpartitions 8
(
PARTITION part1 VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy')) ,
PARTITION part2 VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy')) ,
PARTITION junk VALUES LESS THAN (MAXVALUE)
);

insert /*+ APPEND */ into t1
select to_date('12-mar-2003','dd-mon-yyyy')+mod(rownum,4), rownum, rpad('*',25,'*')
from all_objects;

drop table t;
create table t as select * from all_objects;
create index t_idx on t(object_name);

Why asynchronous process?

2009-01-08T21:58:00.000-08:00

This article is major for traditional internal business data owners, product managers and programmers.

Then why we bother to consider asynchronous process?

# transactional growth, data storage and workload become bottleneck in one single system.
# moving the application to larger computers. there are 2 main limitations:
- outgrowing the capacity of the largest system available
- expensive
# Try distribution transparency(2PC) to insure consistency and accuracy.
- it was better to fail the complete system than to break this transparency.
# Three properties of shared-data systems—data Consistency, system Availability, and tolerance to network Partition—only two can be achieved at any given time.
- Network partition is a given.
- consistency and availability cannot be achieved at the same time.
^ making consistency a priority means that under certain conditions the system will not be available.
^ relaxing consistency will allow the system to remain highly available under the partition network conditions.

Solution

Focus on the trade-offs between high availability and data consistency and large-scale data replication through messaging system, batch process the staging data with transaction Database API, we can deliver durability, availability,performance and cost effectiveness large-scale distributed database systems and infrastructure services.

BASE allows for availability in a partitioned database, then opportunities to relax consistency have to be identified. This is often difficult because the tendency of both business stakeholders and developers is to assert that consistency is paramount to the success of the application. Temporal inconsistency cannot be hidden from the end user, so both engineering and product owners must be involved in picking the opportunities for relaxing consistency.

Practical example:

Book qty, 2 input sources, it is not accurate in fact.
- update sequence
- priority
- conflict resolution
Consistency can be relaxed and use just don't care the consistency
take advantage of user-perceived consistency window between write and read
# Finance batch process
# Orders and Credit card batch update
# Return items batch process

ACID: transfer money from one account to another account, maintain total amount same.

The requirement ratio of ACID vs. BASE is about 1:100, like 100 searches generate 1 order.

Example:
a: Flight ticket booking system
b: 1% performance decrease caused 5% orders lost
c: publish a new book and make it online for search
d: user data, e.g. sale_amt, purchase_amt

Reference:

Good things come to who can wait.
* Scalability Best Practices: Lessons from eBay (Partition and Asynchronously)
* BASE: AN ACID ALTERNATIVE
* Eventually Consistent
* Latency is Everywhere and it Costs You Sales - How to Crush it

...

My cubicle in office.

cross rows comparing

2009-01-07T21:55:00.000-08:00

Goal
----
Try many approaches to do cross rows comparing.

Solution
--------
1. Hash Join
eagle’s home http://www.dbafan.com/blog/?p=176

2. Lead()/Lag() Analytic function

3. SQL Model

Outcome
-------
Hash Join is a good option, Lead() Analytic function is better, less db time, 8 times less Latches.
I could not make SQL Model work efficiently.
For large data set, SQL Model asks much more PGA and temporary tablespace to do sort.
Reduce the columns from Measure will help a little.

Who can help to tune SQL Model?

Setup
-----


drop table scott.t_range ;
create table scott.t_range nologging
as
select level id, level * 10 low_value, level * 10 + Trunc(dbms_random.value(1,11)) high_value
from dual
connect by level <= 300000
order by dbms_random.value;

exec dbms_stats.gather_table_stats('SCOTT','T_RANGE');

Notes: do not do it in production database.

SQL code
--------
1. Hash Join

drop table scott.t_range_tmp;
create table scott.t_range_tmp nologging as
select rownum line_no,low_value,high_value
from
(
select low_value,high_value
from scott.t_range 
order by low_value asc
);

select a.low_value,a.high_value,b.low_value,b.high_value
from scott.t_range_tmp a,scott.t_range_tmp b
where a.line_no=b.line_no - 1 and a.high_value>=b.low_value;

2. Lead()/Lag() Analytic function

select * from
(
select id,low_value,high_value,
Lead(low_value,1) OVER (ORDER BY low_value) next_low_value
from scott.t_range
)
where next_low_value <= high_value;

3. SQL Model, slow, need tuning

with a as (SELECT rownum line_no,low_value,high_value FROM scott.t_range order by low_value asc)
select line_no,low_value,next_low_value
FROM a
MODEL
  --RETURN UPDATED ROWS
  MAIN simple_model
  --PARTITION BY (country)
  DIMENSION BY (line_no)
  MEASURES (low_value,high_value, 0 AS next_low_value)
  RULES
   (next_low_value[ANY] = low_value[cv(line_no) + 1])
)
where next_low_value <= high_value;

Benchmark
---------
* RunStats

SQL> set arraysize 1000

exec runStats_pkg.rs_start;

drop table scott.t_range_tmp;
create table scott.t_range_tmp nologging as
select rownum line_no,low_value,high_value
from
(
select low_value,high_value
from scott.t_range 
order by low_value asc
);

select count(*) from (
select a.low_value,a.high_value,b.low_value,b.high_value
from scott.t_range_tmp a,scott.t_range_tmp b
where a.line_no=b.line_no - 1 and a.high_value>=b.low_value
);

exec runStats_pkg.rs_middle;

select count(*) from
(
select * from
(
select id,low_value,high_value,
Lead(low_value,1) OVER (ORDER BY low_value) next_low_value
from scott.t_range
)
where next_low_value <= high_value
);

exec runStats_pkg.rs_stop(20);

Run1 ran in 270 hsecs
Run2 ran in 201 hsecs
run 1 ran in 134.33% of the run2 time

Name                                     Run1           Run2           Diff
LATCH.JS queue state obj latch             90             60            -30
STAT...redo ordering marks                 33              1            -32
STAT...CPU used when call star            209            177            -32
STAT...workarea memory allocat            -32              0             32
STAT...CPU used by this sessio            209            177            -32
STAT...consistent changes                  82             47            -35
LATCH.redo allocation                      39              0            -39
LATCH.begin backup scn array               41              0            -41
LATCH.child cursor hash table              45              0            -45
LATCH.redo writing                         46              0            -46
STAT...data blocks consistent              47              0            -47
STAT...rollbacks only - consis             47              0            -47
STAT...CR blocks created                   47              0            -47
STAT...deferred (CURRENT) bloc             55              8            -47
STAT...DB time                            225            177            -48
STAT...cluster key scans                   51              0            -51
STAT...commit cleanouts succes             67              0            -67
STAT...Elapsed Time                       272            203            -69
STAT...session cursor cache hi             79              4            -75
LATCH.SQL memory manager worka             81              6            -75
STAT...commit cleanouts                    75              0            -75
STAT...index fetch by key                  76              0            -76
LATCH.messages                             95             12            -83
STAT...cluster key scan block              99              0            -99
STAT...calls to kcmgas                    111              1           -110
STAT...opened cursors cumulati            121              4           -117
LATCH.checkpoint queue latch              118              0           -118
STAT...parse count (total)                123              4           -119
STAT...physical write IO reque            126              0           -126
STAT...execute count                      131              5           -126
LATCH.dml lock allocation                 130              0           -130
LATCH.cache buffers lru chain             140              0           -140
STAT...enqueue requests                   157              1           -156
STAT...enqueue releases                   158              0           -158
STAT...consistent gets - exami            245             12           -233
STAT...physical read IO reques            238              0           -238
LATCH.undo global data                    274             14           -260
LATCH.library cache lock                  263              0           -263
LATCH.multiblock read objects             282              0           -282
LATCH.enqueues                            411            128           -283
STAT...buffer is not pinned co            305              0           -305
STAT...calls to get snapshot s            318              9           -309
STAT...bytes sent via SQL*Net           1,098            662           -436
LATCH.enqueue hash chains                 609            130           -479
STAT...redo entries                       568             19           -549
LATCH.shared pool                         696             38           -658
STAT...db block changes                   817             68           -749
LATCH.library cache pin                   817             50           -767
STAT...db block gets from cach            944             43           -901
LATCH.simulator hash latch                978              1           -977
STAT...bytes received via SQL*          2,070          1,058         -1,012
LATCH.library cache                     1,155             62         -1,093
STAT...physical reads direct t          1,275              0         -1,275
STAT...physical writes direct           1,275              0         -1,275
STAT...physical reads direct            1,275              0         -1,275
STAT...recursive calls                  1,509              1         -1,508
LATCH.row cache objects                 1,535             18         -1,517
STAT...physical reads cache pr          1,640              0         -1,640
STAT...db block gets direct             1,793              0         -1,793
STAT...physical reads cache             1,793              0         -1,793
STAT...free buffer requested            1,894              1         -1,893
LATCH.session allocation                2,030              0         -2,030
STAT...db block gets                    2,737             43         -2,694
STAT...physical reads                   3,068              0         -3,068
STAT...physical writes                  3,068              0         -3,068
STAT...physical writes direct           3,068              0         -3,068
STAT...physical writes non che          3,068              0         -3,068
STAT...table scan blocks gotte          5,605          1,795         -3,810
STAT...no work - consistent re          5,663          1,795         -3,868
STAT...consistent gets                  6,147          1,824         -4,323
STAT...consistent gets from ca          6,147          1,824         -4,323
LATCH.object queue header oper          5,777              2         -5,775
STAT...session logical reads            8,884          1,867         -7,017
LATCH.cache buffers chains             22,633          3,872        -18,761
STAT...sorts (rows)                   322,072        302,880        -19,192
STAT...undo change vector size         28,368          3,372        -24,996
STAT...session pga memory             -65,536              0         65,536
STAT...redo size                      100,132          4,880        -95,252
STAT...table scan rows gotten         930,309        300,000       -630,309

Run1 latches total versus Run2 -- difference and pct
Run1           Run2           Diff       Pct
38,553          4,446        -34,107    867.14%


exec runStats_pkg.rs_start;
select count(*) from
(
with a as (SELECT rownum line_no,low_value,high_value FROM scott.t_range order by low_value asc)
select line_no,low_value,next_low_value
FROM a
MODEL
  --RETURN UPDATED ROWS
  MAIN simple_model
  --PARTITION BY (country)
  DIMENSION BY (line_no)
  MEASURES (low_value,high_value, 0 AS next_low_value)
  RULES
   (next_low_value[ANY] = low_value[cv(line_no) + 1])
)
where next_low_value <= high_value
;

exec runStats_pkg.rs_middle;
select count(*) from
(
select * from
(
select id,low_value,high_value,
Lead(low_value,1) OVER (ORDER BY low_value) next_low_value
from scott.t_range
)
where next_low_value <= high_value
);

exec runStats_pkg.rs_stop(20);

Reference
---------
Oracle® Database Data Warehousing Guide
11g Release 1 (11.1)
Part Number B28313-02

21 SQL for Analysis and Reporting
22 SQL for Modeling

3 PL/SQL approaches fetch data into ADT collection

2009-01-06T20:55:00.000-08:00

Goal
----
There are 3 approaches to fetch data into schema object collection type.
Benchmark to find the best one.

Solution
--------
Setup test case, benchmark with RunStats and SQL trace.

Result
------
PL/SQL native collection(Associative Array and Nested Table) is the fastest way to get data from SQL.
But some time you need to query against a Collection/Array, you have to declare it as an schema object type.

To fetch data into schema object type collection, here list 3 approaches Pros and Cons:

1) Convert multi columns to object type, bulk collect into collection
Pros: code is simple.
Cons: 50% more CPU time compare to approach 2; about same amount of latches.

2) Use multiset, constructs the required collection of ADTs directly in SQL as a select list element in a single row
Pros: Faster, 50% better than approach 1 in my test case.
Cons: code is a little complex, not support SQL Returning clause in 10.1.

3) bulk collect into PL/SQL native collection, copy data into object type collection
Pros: Fastest, 67% better than approach 1 in my test case, 50% less latches.
Cons: use 100% more PGA memory, code is a little complex and not easy to maintain.

Note: ADT: Abstract Data Type, also calls User Define Datatype

Setup
-----


drop TYPE abelisting.book_chg_tab;
drop TYPE abelisting.book_chg_rec;

create or replace TYPE abelisting.book_chg_rec  AS OBJECT
(
listingsid number(20),
qty number(5),
delete_flag number(1),
update_date date
);
/

create or replace TYPE abelisting.book_chg_tab IS TABLE OF abelisting.book_chg_rec;
/

DROP TABLE abelisting.book_delete_stage_part;

CREATE TABLE abelisting.book_delete_stage_part (
listingsid number(20),
qty number(5),
delete_flag number(1),
update_date timestamp(3) default SysTimestamp,
part_mi number(2) default mod(to_number(to_char(sysdate,'MI')),10)
)
PARTITION BY LIST (part_mi) (
  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 data_part_6 VALUES (6),
  PARTITION data_part_7 VALUES (7),
  PARTITION data_part_8 VALUES (8),
  PARTITION data_part_9 VALUES (9),
  PARTITION dummy_null  VALUES (NULL),
  PARTITION dummy_other VALUES (DEFAULT)
)
tablespace data_auto
nologging;

insert into /*+ append */ abelisting.book_delete_stage_part(listingsid,delete_flag,update_date, part_mi)
select trunc(dbms_random.value(1,20000)), mod(level,2), trunc(sysdate,'DD') + mod(level,60)/1440,
 mod(level,10)
from dual
connect by level <= 30000;
commit;

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

Code
----

*) RunStats

 # multiset vs. bulk collect into object_array

declare
   lt_chg abelisting.book_chg_tab; 
--   type my_book_chg_tab is table of abelisting.book_delete_stage%ROWTYPE;
--   lt_chg my_book_chg_tab;
   type tab_chg_array is table of abelisting.book_delete_stage_part%rowtype;
   lt_chg_array tab_chg_array;
begin
 runStats_pkg.rs_start;
select cast(multiset(
select book_chg_rec(LISTINGSID, QTY, DELETE_FLAG, UPDATE_DATE) from abelisting.book_delete_stage_part
) as book_chg_tab)
into lt_chg 
from dual;
end;
/

declare
   lt_chg abelisting.book_chg_tab; 
   type item_chg_array is table of abelisting.book_delete_stage_part%rowtype;
   lt_chg_array item_chg_array;
begin
 runStats_pkg.rs_middle;
select book_chg_rec(LISTINGSID, QTY, DELETE_FLAG, UPDATE_DATE)
bulk collect into lt_chg
from abelisting.book_delete_stage_part
;
 runStats_pkg.rs_stop(5);
end;
/

Run1 ran in 24 hsecs
Run2 ran in 32 hsecs
run 1 ran in 75% of the time

Name                                  Run1        Run2        Diff
STAT...recursive calls                  11           4          -7
LATCH.active service list                0           7           7
LATCH.cache buffers chains             909         902          -7
LATCH.session idle bit                   8           0          -8
STAT...Elapsed Time                     26          35           9
STAT...recursive cpu usage              23          34          11
STAT...db block gets                    63          51         -12
STAT...db block gets from cach          63          51         -12
STAT...session logical reads           390         377         -13
STAT...workarea memory allocat         -34          -9          25
STAT...CPU used when call star          28           0         -28
STAT...CPU used by this sessio          28           0         -28
STAT...DB time                          28           0         -28
LATCH.library cache lock                41          12         -29
LATCH.JS queue state obj latch           0          30          30
LATCH.library cache pin                 64          24         -40
LATCH.row cache objects                 69          27         -42
LATCH.shared pool                       58          15         -43
LATCH.enqueues                           8          68          60
LATCH.enqueue hash chains                6          69          63
LATCH.library cache                    130          36         -94
STAT...undo change vector size       2,808       2,908         100
STAT...redo size                     3,956       4,064         108
LATCH.simulator hash latch               2         131         129
STAT...bytes sent via SQL*Net          218           0        -218
STAT...bytes received via SQL*         760           0        -760
STAT...buffer is pinned count       29,796           0     -29,796
STAT...session uga memory max      196,500           0    -196,500
STAT...session pga memory max    6,815,744     393,216  -6,422,528
STAT...session pga memory                0   7,208,960   7,208,960

Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
1,337       1,356          19     98.60%

 # multiset vs. plsql_collection copy to object array
 
declare
   lt_chg abelisting.book_chg_tab; 
   type tab_chg_array is table of abelisting.book_delete_stage_part%rowtype;
   lt_chg_array tab_chg_array;
begin
 runStats_pkg.rs_start;
select cast(multiset(
select book_chg_rec(LISTINGSID, QTY, DELETE_FLAG, UPDATE_DATE) from abelisting.book_delete_stage_part
) as book_chg_tab)
into lt_chg 
from dual;
end;
/

declare
   lt_chg abelisting.book_chg_tab; 
   type item_chg_array is table of abelisting.book_delete_stage_part%rowtype;
   lt_chg_array item_chg_array;
begin
 runStats_pkg.rs_middle;

select LISTINGSID, QTY, DELETE_FLAG, UPDATE_DATE, Null
bulk collect into lt_chg_array
from abelisting.book_delete_stage_part;

 lt_chg := abelisting.book_chg_tab();
 lt_chg.extend(lt_chg_array.count());
 For i in lt_chg_array.First() .. lt_chg_array.Last()
 Loop
   lt_chg(i)  := book_chg_rec(lt_chg_array(i).LISTINGSID,
    lt_chg_array(i).QTY  ,
    lt_chg_array(i).DELETE_FLAG,
    lt_chg_array(i).UPDATE_DATE
   );
 End Loop;
 runStats_pkg.rs_stop(5);
end;
/

Run1 ran in 27 hsecs
Run2 ran in 17 hsecs
run 1 ran in 158.82% of the time

Name                                  Run1        Run2        Diff
STAT...opened cursors cumulati           8           2          -6
LATCH.active service list                0           7           7
LATCH.child cursor hash table            7           0          -7
STAT...recursive calls                  12           4          -8
LATCH.session idle bit                   8           0          -8
STAT...Elapsed Time                     29          20          -9
STAT...db block gets                    63          53         -10
STAT...db block gets from cach          63          53         -10
STAT...recursive cpu usage              23          12         -11
STAT...workarea memory allocat          12          -7         -19
STAT...calls to get snapshot s          62          38         -24
STAT...table fetch by rowid             24           0         -24
STAT...index fetch by key               24           0         -24
LATCH.checkpoint queue latch             8          32          24
STAT...rows fetched via callba          24           0         -24
STAT...CPU used when call star          28           0         -28
STAT...DB time                          28           0         -28
STAT...CPU used by this sessio          28           0         -28
LATCH.JS queue state obj latch           0          30          30
STAT...free buffer inspected             0          38          38
STAT...hot buffers moved to he           0          48          48
STAT...buffer is not pinned co          48           0         -48
LATCH.enqueues                          10          70          60
LATCH.enqueue hash chains                8          71          63
LATCH.SQL memory manager worka           4          71          67
STAT...consistent gets                 397         327         -70
LATCH.cache buffers chains             973         903         -70
STAT...consistent gets from ca         397         327         -70
STAT...consistent gets - exami          72           1         -71
STAT...session logical reads           460         380         -80
STAT...undo change vector size       2,804       2,904         100
LATCH.simulator hash latch             130           3        -127
STAT...redo size                     3,952       4,120         168
LATCH.row cache objects                193          18        -175
STAT...bytes sent via SQL*Net          218           0        -218
LATCH.library cache lock               234          12        -222
LATCH.library cache pin                256          25        -231
LATCH.shared pool                      258          13        -245
LATCH.library cache                    559          39        -520
STAT...bytes received via SQL*       1,067           0      -1,067
STAT...buffer is pinned count       29,796           0     -29,796
STAT...session uga memory           65,464           0     -65,464
STAT...session uga memory max      193,032      68,932    -124,100
STAT...session pga memory max    6,815,744   9,895,936   3,080,192
STAT...session pga memory           65,536  16,646,144  16,580,608

Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
2,691       1,330      -1,361    202.33%

*) SQL Trace

ALTER SESSION SET TRACEFILE_IDENTIFIER="yi";
EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(waits => TRUE, binds => True);

declare
   lt_chg abelisting.book_chg_tab; 
begin
select cast(multiset(
select book_chg_rec(LISTINGSID, QTY, DELETE_FLAG, UPDATE_DATE) from abelisting.book_delete_stage_part
) as book_chg_tab)
into lt_chg 
from dual;
end;
/

declare
   lt_chg abelisting.book_chg_tab; 
begin
select book_chg_rec(LISTINGSID, QTY, DELETE_FLAG, UPDATE_DATE)
bulk collect into lt_chg 
from abelisting.book_delete_stage_part
;
end;
/

EXECUTE DBMS_MONITOR.SESSION_TRACE_DISABLE();


tkprof report
-------------
SELECT book_chg_rec(LISTINGSID, QTY, DELETE_FLAG, UPDATE_DATE)
FROM
 ABELISTING.BOOK_DELETE_STAGE_PART

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.34       0.33          0        316          0       30000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.34       0.33          0        316          0       30000

SELECT CAST(MULTISET( SELECT book_chg_rec(LISTINGSID, QTY, DELETE_FLAG,
  UPDATE_DATE)
FROM
 ABELISTING.BOOK_DELETE_STAGE_PART ) AS BOOK_CHG_TAB) FROM DUAL

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.21       0.22          0        316          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.21       0.22          0        316          0           1

Misses in library cache during parse: 0
Optimizer mode: FIRST_ROWS
Parsing user id: 35     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  FAST DUAL  (cr=0 pr=0 pw=0 time=9 us)

SELECT LISTINGSID, QTY, DELETE_FLAG, UPDATE_DATE, NULL
FROM
 ABELISTING.BOOK_DELETE_STAGE_PART

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.11       0.10          0        316          0       30000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.11       0.10          0        316          0       30000

Misses in library cache during parse: 1
Optimizer mode: FIRST_ROWS
Parsing user id: 35     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
  30000  PARTITION LIST ALL PARTITION: 1 12 (cr=316 pr=0 pw=0 time=90091 us)
  30000   TABLE ACCESS FULL BOOK_DELETE_STAGE_PART PARTITION: 1 12 (cr=316 pr=0 pw=0 time=30379 us)
.

Good practice: Middle-tier DAL or Database API – Database access guideline

2007-09-10T12:04:00.000-07:00

The reason I don't use "Best Practice" is that will prevent rest of people create some better new ideas, and let you out the 'Keep an open mind' zone.

So I turned to Good practice:

Middle-tier DAL or Database API – Database access guideline
(Hibernate vs Store procedure)

• Maximise SQL and minimise PL/SQL
• Minimise client code and maximise database server code.

Generally servers are more powerful and built for this type of work. You also want to minimise trips back and forth to the server.

Reference:

Business Logic - PL/SQL Vs Java
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:883929178230

I prefer to put all logic that deals with data in PLSQL. There is no more natural language to interact with SQL data then PLSQL. None.

For example -- native compilation was added -- turned plsql into object code that runs natively on the OS.

Database API, build the data API in the database, you call the data API
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:25405782527721

When to use Hibernate DAL
• One step SQL (SELECT OR UPDATE) to finish your goal
• Heavy and forced interaction in mid tier, like CCBB encryption, that means high coulping too, and hard to encapsulate the process logic.

Jave developer can help us on this list.

When to use Database API and Store Procedure Package
• Complex SQL
For example, book delete check, reference Single Hash Table and list of IDs.
• Many (more than 2) steps data machinations logic in a sigle module
• Reture rows more than one page, let's say 20 rows from database.

We like Database API approach, because it:
1.. make software components modular, I'm totally into modular programming
2.. software modules must carry out a very specific task (and be very efficient at carrying it out)
3.. each software module should be loosly coupled (to limit dependencies)
4.. It removes the need for triggers as all inserts, updates and deletes are wrapped in APIs. Instead of writing triggers you simply add the code into the API. I loath triggers.
5.. It prevents people who don't understand SQL writing stupid queries.
All SQL would be written by PL/SQL developers or DBAs, reducing the likelyhood of dodgy queries.
6.. The underlying structure of the database is hidden from the users, so I can make structural changes without client applications being changed.The API implementation can be altered and tuned without affecting the client application.
7.. The same APIs are available to all applications that access the database. No duplication of effort.

Anything that generates SQL on-the-fly worries me, not just Java. I want to be able to cut and paste the SQL, not try and capture or trace it during a run.

Our concept is "build the data API in the database, you call the data API" .
Database API has been layered by different UI technologies over time.

All about API's. The applications don't do table level(select/insert/update/delete) stuff, the apps don't even really know about tables.

On top of database schema we have an API that does the data stuff.

In the application we call this API but have our own application logic as well
(only application logic is in the application, data logic is – well, right where it belongs – next to the data, waiting for the 'next great programming paradigm to come along')

The fact that our UI is in Java isn't really relevant. You could pretty much see how you would use this package from C#, Java/JSP, Swing, VB, Pro*C, Forms, Powerbuilder, a mobile phone, .

–
Storing application code in the database has had it's champions and detractors.

When you start putting application code in the database, you are in the thoroughly non-portable arena. That code, were you to port your application to another database, would have to be rewritten. But think about your application mid-tier layer, will you port Java code to C#/.Net,-> Pascal -> C -> VB, then to Python, Ruby on Rail ...etc., will you do that?

Its very specificity to that database means it can also take advantage of, and wire close to that engine. There are situations where stored code in the database can be notably faster. Supposed you have to update some chunk of a million rows after doing some machinations on the data.

In a stored procedure the data is read, manipulated, and updated in one step. Meanwhile if you did the same in your middle tier application code, you would have to send that data set over the network, do your manipulations, and send it back. Not only would it make this one task slower, but other transactions vying for that same data could potentially have to wait while that data is in transit, and being manipulated. Also, stored code can serve to encapsulate specific requests which can be invaluable at simplifying your overall application. All three databases support stored procedures and functions. Oracle also supports packages, or collections of stored procedures as well as various object oriented features, which almost no one ever uses. An additional note, a database engine actually context switches between stored code, and the SQL code embedded therein. As since of 9i, Oracle introduced bulk binding, so you can do work on large sets of rows, and update them all in one go, instead of each loop iteration. This feature can even further improve performance quite dramatically.

Document the SQL access database
We'll build a small application for this target, on Oracle APEX (HTML DB).
then QA (and Developer) need to record all the SQL access db in the new developing applications.
* SQL execution path
* SQL performance statistics

我年轻时所不了解的事情

2007-07-24T11:44:00.000-07:00

-- 转贴子从生活帮-LifeBang
-- 以供自我勉励

那些你曾经为之激动、兴奋，消耗了你无数时间和精力的事情，大多数是无足轻重的。

那些等着万事俱备才去做的事情往往永远也没机会做。
-- 立刻行动! 行动上热情!

如果大家都抱怨你说你太特例独行与别人格格不入时，你一定在正确的轨道上。
如果工作是你全部的生活，你的生活就是一项艰苦的工作。

任何一个成功的人都有失败的时候。不要企图永远占据成功者的位置。

每件事都要花费计划中两倍的时间而只得到期望中一半的成果。没必要为此沮丧，认清现实，继续前进。

无论你怎么尝试改变，你总是你自己。
世界上最响的声音就是人们的抱怨声。别再让它更响了。

PL/SQL 在 Oracle 11g 里面的功能完善: "continue"

2007-07-14T21:16:00.001-07:00

Oracle 终于支持 CONTINUE 了, 像我一样做 Development DBA 的人用得着了.
原来老得翻翻文档, 找出 Label 和 GoTo的语法, 特别烦人.

这个东西实在算不上一个新特性, 只能叫作功能完善而已.

使用"CONTINUE" 的 PL/SQL程序样本, 请参考:
http://technology.amis.nl/blog/?p=2261

Database developers and Database API

2006-12-08T17:51:00.001-08:00

I’ve come up with an idea while the new 6 months Oracle DBA contractor is being interviewed to back fill for Antoinette while she is on maternity leave.

The idea is we may take this Opportunity to introduce a new database developer join our team.

We pay 6 months salary to the contractor and only get an actual 3 months of work since they need 3 months to get up to speed on our environment (together with the increased cost of hiring a contractor).

It’s better to give this opportunity to the new employee as a database developer which can learn our business, and then contribute to our company in long term.

Why we need a Database Developer and Database API?

Today some GUI logic is in the middle tier but most of the data logic is still in PL/SQL (let’s say eBay here).

Performance
Performance is directly connected to stability and functionality, when db node running to it’s knee and hangs, you can do nothing, and cause some potential locks and bugs.

Dodgy SQL examples.

1) Filter data in java side, treat db as a black data store.
We should always filter data from the database side, I knew it 10 years ago as a database developer.

SELECT I.*

FROM ABELISTING.XBNIDENTS I

WHERE I.PROVIDER <> 44

What we did before:

SELECT I.* FROM ABELISTING.XBNIDENTS I;

Get all the data returned from database,

And then filter the row <> 44 in Java side.

2) Cursor fetches
What we are doing:

• 1. get Max and Min xbnidentsid

SELECT MIN(XBNIDENTSID) AS MIN, MAX(XBNIDENTSID) AS MAX

FROM ABELISTING.XBNIDENTS

• 2. Split it into many 100 rows chunks by calculate each xbnidentsid range

• 3. run below SELECT many times, each run get 100 rows

SELECT I.*, R.RECOMMENDATIONSET

FROM ABELISTING.XBNIDENTS I

LEFT OUTER JOIN LIBRARYTHING.ISBNRECOMMENDATIONS R

ON I.XBNIDENTIFIER = R.ISBN

WHERE I.PROVIDER <> 44

AND I.XBNIDENTTYPECODE = 1

AND I.XBNIDENTSID < #xbnidentsidMax#

AND I.XBNIDENTSID >= #xbnidentsidMin#

It’s a nest loop join, cost 5 times more resource and latches.

Why not run a simple single SQL SELECT, hash join 2 tables, and use bulk cursor prefetch?

It’s been implementing to X-Team, the rows Prefetching.

2.1) change a simple UPDATE to 2 steps.
1) select the PK_ID by some predicates, cached into Java server side, maybe run the SQL many times, return 100 rows each time.

2) process row by row by PK_ID

Why not run a single UPDATE SQL here?

3) Treat database as a black box or a human brain; actually, we control how database works, we need to have:
• knowledge of what the database is capable of doing

• knowledge of all of the intricacies of SQL

• knowledge a solid understanding of goal- what the question is

Here is an example:

This SELECT takes 60 seconds.

SELECT MIN(XBNIDENTSID) AS MIN, MAX(XBNIDENTSID) AS MAX

FROM ABELISTING.XBNIDENTS;

-----------------------------------------------------------------------

| ID | Operation | NAME | ROWS | Bytes | COST (%CPU)| TIME |

-----------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 6 | 4564 (1)| 00:00:51 |

| 1 | SORT AGGREGATE | | 1 | 6 | | |

| 2 | INDEX FAST FULL SCAN| XBNIDENTS_PK | 6888K| 39M| 4564 (1)| 00:00:51 |

-----------------------------------------------------------------------

By simply break it down to 2 SELECT SQL, just 0.01 second

SELECT MIN(XBNIDENTSID) AS MIN

FROM ABELISTING.XBNIDENTS

UNION ALL

SELECT MAX(XBNIDENTSID) AS MAX

FROM ABELISTING.XBNIDENTS;

-----------------------------------------------------------------------

| ID | Operation | NAME | ROWS | Bytes | COST (%CPU)| TIME |

-----------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 2 | 12 | 6 (50)| 00:00:01 |

| 1 | UNION-ALL | | | | | |

| 2 | SORT AGGREGATE | | 1 | 6 | | |

| 3 | INDEX FULL SCAN (MIN/MAX)| XBNIDENTS_PK | 6888K| 39M| 3 (0)| 00:00:01 |

| 4 | SORT AGGREGATE | | 1 | 6 | | |

| 5 | INDEX FULL SCAN (MIN/MAX)| XBNIDENTS_PK | 6888K| 39M| 3 (0)| 00:00:01 |

-----------------------------------------------------------------------

Stability
RDBMS database and store procedure have been around for more than 20 years, and is proven stable. Building our core data process logic inside the database will be stable.

More components means more complexity, more coding and bugs, more maintenance job, and it’s not linear, it’s exponential.

All about database API's. The applications don't do table level(select/insert/update/delete) stuff, the apps don't even really *know* about tables.

On top of database schema we have an API that does the data stuff.
In the application we call this API but have our own application logic as well
(only application logic is in the application, data logic is -- well, right where it belongs -- next to the data, waiting for the 'next great programming paradigm to come along')

make software components modular
software modules must carry out a very specific task
(and be very efficient at carrying it out)
each software module should be loosely coupled (to limit dependencies)
It removes the need for triggers as all inserts, updates and deletes are wrapped in APIs. Instead of writing triggers you simply add the code into the API. I loath triggers.
It prevents people who don't understand SQL writing stupid queries.
All SQL would be written by PL/SQL developers or DBAs, reducing the likelyhood of dodgy queries.
The underlying structure of the database is hidden from the users, so I can make structural changes without client applications being changed.The API implementation can be altered and tuned without affecting the client application.
The same APIs are available to all applications that access the database. No duplication of effort.
Flexibility and Elegant programming
• PL/SQL is the most efficient language for data manipulation

– PL/SQL datatypes are SQL datatypes collection/array, nest object table … – no conversions

– Tight coupling between the language and the database (cursor for loops for example)

– We don’t need to open/close queries – manage lots of stuff

– We are somewhat protected from changes in the database

– You have sub-optimizations like cursor caching done for you.
(Native prefetch 100 rows, with FOR lc_cursor IN (select * from table_name) LOOP)

–

• Don’t forget the dependency mechanism you get!
Drop/add columns and select * from table_name,
Deploy at anytime after QA time, no build, no server restart, transparent when change table structure (physical database model)

• In PL/SQL it is very hard to not use bind variables correctly.

• In Java is it trivially easy to do it wrong – and it usually is.

• In PL/SQL – parse once, execute many is inherit in the environment.

• In Java, it must be coded – and it hardly ever is.

• In PL/SQL – “select *” can be safe (might not be the best approach, but is is “safe”)

• In Java – “select *” is deadly – but frequently used.

• PL/SQL can survive database schema changes such as a varchar2(80) -> varchar2(255) change

• Java cannot, you will have to inspect all code and change variable sizes.

• PL/SQL has that nice builtin dependency mechanism to avoid the “I didn’t know you used that” syndrome

• Java does not.

• … a long list goes from her…

• Embed SQL complexity into store procedure, give a simple interface to Jave developer

For example, book delete check:

Packaged Store PROCEDURE list_df(p_cursor IN OUT listidcurtyp, p_string VARCHAR2)

AS

i PLS_INTEGER;

BEGIN

OPEN p_cursor FOR

WITH sq

AS

(

SELECT SUBSTR(x, INSTR(x,',',1,LEVEL)+1, INSTR(x,',',1,LEVEL+1) - INSTR(x,',',1,LEVEL) -1) token

FROM (SELECT ','||l_str||',' x,l_str FROM (SELECT p_string l_str FROM dual))

CONNECT BY LEVEL <= LENGTH(l_str)-LENGTH(REPLACE(l_str,',',''))+1

)

SELECT A.listingsid listingsid

FROM abelisting.listings A, sq

WHERE A.listingsid = TO_NUMBER(sq.token)

AND A.rowdf = 'T';

Java side:

SELECT COLUMN_VALUE listingsid

FROM TABLE(abelisting.Sp_Sql_Query.list_df_tabf(:l_string));

Less code, simple is beautiful!
Beat me if you can write less Java code for below PL/SQL store procedure to reach same goal, just to compare the simplicity, not mention the performance yet:

1) For Loop
CREATE OR REPLACE

PROCEDURE process_data( p_inputs IN VARCHAR2 )

AS

BEGIN

FOR x IN ( SELECT * FROM EMP WHERE ename LIKE p_inputs )

LOOP

Process( X );

END LOOP;

END;

2) Enforce read/write consistency, update and delete in one transaction
PROCEDURE clear_pending_list_qty_forall

AS

TYPE listingsidList IS TABLE OF abelisting.list_qty_offset.listingsid%TYPE;

lt_listingsid listingsidList;

TYPE pendingList IS TABLE OF abelisting.list_qty_offset.pending%TYPE;

lt_pending pendingList;

l_cnt NUMBER;

BEGIN

DBMS_APPLICATION_INFO.SET_MODULE('ListLoad', 'Clear pending offset');

--To clear all the pending ordered books qty, into listigns

--Using RETURNING collection to store processing data, make sure you can always get an consistent image of listings.qty

--that is, never delete the extra rows, between update and delete.

-- Options 2 is DELETE with RETURNING list, then bulk FORALL UPDATE, 2 times faster

DELETE FROM abelisting.list_qty_offset

RETURNING listingsid, pending BULK COLLECT INTO lt_listingsid, lt_pending;

DEBUG.f('delete %s rows on abelisting.list_qty_offset', SQL%ROWCOUNT);

FORALL i IN lt_listingsid.FIRST .. lt_listingsid.LAST

UPDATE ABELISTING.listings SET quantity = quantity + lt_pending(i)

WHERE LISTINGSID = lt_listingsid(i);

l_cnt := SQL%ROWCOUNT;

DEBUG.f('updated qty on listing %s rows', l_cnt);

COMMIT;

/*

--Err logging

FOR i IN 1 .. lt_listingsid.COUNT

LOOP

DBMS_OUTPUT.PUT_LINE( 'updated listingsid = ' || lt_listingsid(i)||', qty = '||lt_pending(i) );

DEBUG.f( 'updated listingsid = ' || lt_listingsid(i)||', qty = '||lt_pending(i) );

END LOOP;

*/

EXCEPTION

WHEN OTHERS THEN

DEBUG.f( SUBSTR(TO_CHAR(SQLCODE)||': '||SQLERRM,1,100) );

RAISE;

END;

3) INSERT … SELECT from external_table (flat file), data loading from flat file
PROCEDURE isbn_recomd_full_load(p_add_rows IN OUT NUMBER)

IS

l_cnt NUMBER;

BEGIN

DBMS_APPLICATION_INFO.SET_MODULE('LB', 'isbn_recomd_full_load');

DEBUG.f('truncate table');

EXECUTE IMMEDIATE 'alter session set optimizer_mode=all_rows';

EXECUTE IMMEDIATE 'alter session set skip_unusable_indexes=true';

EXECUTE IMMEDIATE 'TRUNCATE TABLE librarything.isbnRecommendations';

DEBUG.f('unusable index');

--execute immediate 'alter index librarything.isbnRecommendations_isbn unusable';

DEBUG.f('INSERT +APPEND direct load');

INSERT /*+ APPEND */ INTO librarything.isbnRecommendations NOLOGGING

(isbn,recommendationSet)

SELECT isbn,recommendationSet

FROM librarything.isbnRecommendations_ext; -- the external table

l_cnt := SQL%RowCount;

DEBUG.f('INSERTed %s rows', l_cnt);

COMMIT;

p_add_rows := l_cnt;

DEBUG.f('rebuild index on isbn column');

---execute immediate 'alter index librarything.isbnRecommendations_isbn rebuild online';

DEBUG.f('rebuild index done');

DBMS_APPLICATION_INFO.SET_MODULE('LB', 'isbn_recomd_full_load done');

EXCEPTION

WHEN OTHERS THEN

DEBUG.f( SUBSTR(TO_CHAR(SQLCODE)||': '||SQLERRM,1,100) );

RAISE;

END;

My Utopian Development Environment
There is no SELECT/INSERT/UPDATE/DELETE/MERGE in Java code, the database access layer; only call a packaged database Store Procedure for a specific goal.

Communication
Efficiency is achieved by specializing and profession, classify into Java developer, database developer and backend developer. JAVA handle file processing and UI logic, database process logic is just inside the database, wrapped into database store procedure.

I know some backend developer can improve PL/SQL performance 8 times again by calling Pro*C or OCI*C code (Using SQL with array binds of 100 for Key Lookup and Insert), for the raw speed.

A general knowledge to know all other party doing is also necessary and important.

Data modeling
Design a sound data model is one of the most critical stages in the development of a computerized information system.

We’re pursuing a Complete, no Redundancy, Enforce business rule, Data Reusable, flexible and stable, Elegant data model, also for Communication, Integration and

Balance the Conflicting Objectives.

Please check attached email to see the problem could be easily solved by a fitted data model design; and how complex it is when you tackle this issue from Java world.

For database developer, data model is part of their life.

Cascade DELETE

2006-11-10T11:40:00.000-08:00

# cascade delete -- that is a database feature.
--
# validate foreign keys -- all should be (have to be) RI in the database as well.
# cascade update -- well, won't even go there
(which means you have an ill designed model in the first place as you
are updating a primary key -- meaning it is not really a primary key at all).
# for simple ins,upd,del,lck -- it is just as easy to code....., into Data API.

I don't like it(cascade delete) personally -- I'd rather have an error when I delete a parent
that has child records. Doesn't "seem" right to have that stuff just disappear.

It is useful in limited cases.
if and only if EVERY time you delete from parent you want to delete from child,
on delete cascade is OK.
but, if there are sometimes that is not the expected behavior -- don't do that, --such as customer and orders.
use a stored procedure/(Database API).

All access would be provided by PL/SQL APIs. I like this because:
It removes the need for triggers as all inserts, updates and deletes are wrapped in APIs. Instead of writing triggers you simply add the code into the API. I loath triggers.

And, at last, make sure to index those foreign keys, else either approach would be slow slow slow.

You can only have on delete cascade when creating the constraint
(so you drop and recreate the constraint)

Cascade DELETE example:

drop table t1;
create table t1 ( x int primary key );
create table t2 ( y int primary key, x
references t1 on delete cascade );
create table t3 ( y references t2 on delete
cascade, x references t1 );

insert into t1 values ( 1 );
insert into t2 values ( 1 , 1 );
insert into t3 values ( 1 , 1 );

delete from t1;

select * from t1;
select * from t2;
select * from t3;

Formal way to add cascade delete constraint:

ALTER table c
add constraint c_fk_parent_table
foreign key(x) references p(x)
on delete cascade;

General Knowledge

2006-11-01T22:04:00.000-08:00

唉, 我就爱尝鲜, 对每个新特性如获至宝, 记得2001年秋天, 就给世界第二大长话公司上了Oracle 9i on HP-UX.
我的老板(Borland J Builder 的首席构架师)总喜欢问: 啥时候换MySQL呀,要不MS-SQL 2005 也行 (整天宣传SQL-Server2005的优越性), 前两天又来了个 Enterprise DB (基于Postgres)
像我这样只懂Oracle的咋办呀?!!(我就建议老板试试DB2还行我有认证)
还好-最近的统计显示Oracle市场占有率超过50%.

Oracle Data Guard and 11g database

2006-11-01T10:00:00.000-08:00

浏览了Fenng的一篇 Oracle Data Guard practice, 被 control file 搞懵了.
看来我比你幸运,从来没有考虑过copy standby db control file.
我们的配置是, 4 nodes RAC 10.1.0.4, 1 real time standby, 1 read-only standby, for reporting, refresh every night.跑了一年多了啥问题也没有可惜Logcial stdby 不支持宽表(100+ columns)等升级到10.2.0.3,会加一个Logical Standby.

根据我的经验,Oracle 整体稳定,但总有一些莫名其妙的Bug,我们的宗旨是: "绕过去, 别较劲"

读了一些Blog, 谈论Oracle database的下一个版本.
Oracle db 11g
Partition
(a. Time dimension and
b. Child table partition based on master tables partition columns
)
和数据压缩, 令人神往. 嘻嘻.

Data API -- Data access and process interface

2006-10-27T14:50:00.000-07:00

Some other2 reasons to implement Database API
I) Binding Variable, naturally, implicitly.
II) do set based process (100 rows array prefetch by default) to FOR LOOP.
III) debug.f : to Instrument the code

Compulsory PL/SQL APIs
Client application developers would have no direct access to tables. Not even for queries. All access would be provided by PL/SQL APIs (Include database Views).

I like this because:

It removes the need for triggers as all inserts, updates and deletes are wrapped in APIs. Instead of writing triggers you simply add the code into the API. I loath triggers.
It prevents people who don't understand SQL writing stupid queries.
All SQL would be written by PL/SQL developers or DBAs, reducing the likelyhood of dodgy queries.
The underlying structure of the database is hidden from the users, so I can make structural changes without client applications being changed.The API implementation can be altered and tuned without affecting the client application.
The same APIs are available to all applications that access the database. No duplication of effort.

Anything that generates SQL on-the-fly worries me, not just Java. I want to be able to cut and paste the SQL, not try and capture or trace it during a run.

Our concept is "build the data API in the database, you call the data API".
Database API has been layered by different UI technologies over time.

All about API's. The applications don't do table level(select/insert/update/delete) stuff, the apps don't even really *know* about tables.

On top of database schema we have an API that does the data stuff.
In the application we call this API but have our own application logic as well
(only application logic is in the application, data logic is -- well, right where it belongs -- next to the data, waiting for the 'next great programming paradigm to come along')

make software components modular
software modules must carry out a very specific task
(and be very efficient at carrying it out)
each software module should be loosly coupled (to limit dependencies)

木匠铺子开张啦

2006-10-27T14:22:00.000-07:00

Oracle 技术版