Collection_Array

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

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

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

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

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

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

Implement header of the program

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

Define the test cases

1. Verify it works
2. 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?

.

We like (Transactional) Database API approach, because it:

..
We like Transactional Database API approach, because it:

DRY, do not repeat yourself

*. make software components modular, I am totally into modular programming.
*. software modules must carry out a very specific task (and be very efficient at carrying it out).
*. The same APIs are available to all applications in any Language that access the database. No duplication of effort.

Orthogonality

*. each software module should be loosely coupled (to limit dependencies)
  -- Put SQL scattered willy-nilly around in Java/C*.JavaScript is high coupled.
  -- Schema change, adding table/column should only be changed in database, one place.
  -- B calls A, changed A, do not bother to touch B.
*. Make test simpler, easy to setup function and load test.
*. Easy to deploy.
E.g. PL/SQL installation only, no need to touch Java/C*.JavaScript mid tier and UI.
*. Defined clear interface contract.

*. 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 loathe triggers.

*. Clearly separate all database access code (APIs Are King)

*. To understand the consequences of database refactorings, it is important to be able to see how the database is used by the application. If SQL is scattered willy-nilly around the code base, this is very hard to do. As a result, it is important to have a clear database access layer to show where the database is being used and how. To do this we suggest Database API approach.
*. 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.

Control and Responsibility, DevOPS

*. It prevents people who do not understand SQL writing stupid queries.
  -- All SQL would be written by Database developers or DBAs, reducing the likelihood of dodgy queries.
*. SDLC: 80% is maintenance, Dealing with Change.
  -- Changing the database schema.
  -- Migrating the data in the database.
  -- Online data fix,
  -- Changing the database access code / data process logic.
*. Troubleshooting and firefighting
  -- Database developer and DBA can easily get and fix the SQL. Do not bother Java programmers.
*. Tuning SQL. Do not bother C# programmers.

*. Having a clear database layer (APIs Are King) has a number of valuable side benefits. It minimizes the areas of the system where developers need SQL knowledge to manipulate the database, which makes life easier to developers who often are not particularly skilled with SQL. For the database expert it provides a clear section of the code that he can look at to see how the database is being used. This helps in preparing indexes, database optimization, and also looking at the SQL to see how it could be reformulated to perform better. This allows the database expert to get a better understanding of how the database is used.

Profession = High Efficiency + High Quality

*. (ORM) 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.


*. More database features and functions
  -- Partition
*. Less code, less bug, easy to maintain.

*. 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.
*. Eliminate data round trip; data type conversion, the context switch.
*. Tightly couple the data model and data process design. Database world favor of Up Front Big Design.

*. Maximum the data share and reuse.

Suggestion

*. Business logic, Validation and lots of IF statements can be put and refined in Java with advanced language features, such as OO.

..

...

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>.

Reference

http://stackoverflow.com/questions/1588149/orm-for-oracle-pl-sql?lq=1

ORM is flawed

• http://seldo.com/weblog/2010/07/12/in_defence_of_sql

Performance Anti-Patterns in Database-Driven Applications ,
http://www.infoq.com/articles/Anti-Patterns-Alois-Reitbauer

• Misuse of O/R Mappers
• Load More Data Then Needed
• Inadequate Usage of Resources
• One Bunch of Everything

http://tonyandrews.blogspot.ca/search/label/business%20logic

Setup StatsPack to monitor standby database performance

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