Oracle MVA

Tales from a Jack of all trades

BPEL dehydration store and HW contention

with 2 comments

Today I spent some time on fixing performance issues with the BPEL dehydration store. The database is a three (3) node RAC cluster, running RBDMS 10.2.0.4 on ASM 11.1.0.7. BPEL connects to a service name that has one (1) preferred instance and two (2) available instances.

HW contention usually means: trouble with lobs. Just to be sure, I started to check metalink. First of all I discovered I had to set event 44951 to fix bug 6376915 (setting an event without restarting the database means setting events =, next set the event in the spfile). This didn’t clear my HW contention problems though.  What did solve the problems, was moving the lobsegments to a separate tablespace. Here’s the script I used to move the lobsegments (with some extra segment space shrinking):


WHENEVER SQLERROR EXIT ROLLBACK

PROMPT “Enter the dehydration store owner:”
DEFINE _owner = &1

PROMPT “Enter the name for the tables tablespace:”
DEFINE _tablespace = &2

PROMPT “Enter the name for the lob tablespace:”
DEFINE _lob_tablespace = &3

VARIABLE rundate NUMBER
COLUMN run_date NEW_VALUE rundate

SELECT to_char(SYSDATE,’RRRRMMDD’) as run_date
FROM dual
/

PROMPT “Step 1”
PROMPT “Purge data, see $ORACLE_HOME/bpel/system/database/scripts/purge_instances_oracle.sql”
PROMPT “This might take some time…. ”
ALTER SESSION SET CURRENT_SCHEMA=&_owner ;
EXECUTE purge_instances( CAST( SYSDATE – 5  AS TIMESTAMP));

PROMPT “Step 2”
PROMPT “Create tablespace for lobs”
CREATE TABLESPACE &_lob_tablespace
DATAFILE SIZE 2G AUTOEXTEND ON NEXT 512M MAXSIZE UNLIMITED
NOLOGGING EXTENT MANAGEMENT LOCAL UNIFORM SIZE 32768K
SEGMENT SPACE MANAGEMENT MANUAL
/

ALTER USER &_owner
QUOTA UNLIMITED ON &_lob_tablespace
/

PROMPT “Create backup of tables”

PROMPT “cube_scope”
ALTER TABLE &_owner..cube_scope
RENAME TO cube_scope_&rundate
/

ALTER TABLE &_owner..cube_scope_&rundate
RENAME CONSTRAINT cs_pk TO cs_pk_&rundate
/

ALTER INDEX &_owner..cs_pk
RENAME TO cs_pk_&rundate
/

PROMPT “sync_store”
ALTER TABLE &_owner..sync_store
RENAME TO sync_store_&rundate
/

ALTER INDEX &_owner..ss_fk
RENAME TO ss_fk_&rundate
/

PROMPT “XML_document”
ALTER TABLE &_owner..xml_document
RENAME TO xml_document_&rundate
/

ALTER TABLE &_owner..xml_document_&rundate
RENAME CONSTRAINT xml_doc_pk TO xml_doc_pk_&rundate
/

ALTER INDEX &_owner..xml_doc_pk
RENAME TO xml_doc_pk_&rundate
/

PROMPT “Audit_details”
ALTER TABLE &_owner..audit_details
RENAME TO audit_details_&rundate
/

ALTER TABLE &_owner..audit_details_&rundate
RENAME CONSTRAINT ad_pk TO ad_pk_&rundate
/

ALTER INDEX &_owner..ad_pk
RENAME TO ad_pk_&rundate
/

PROMPT “Create new tables”

PROMPT “cube_scope”
CREATE TABLE &_owner..cube_scope
(    cikey        INTEGER NOT NULL,
domain_ref   INTEGER,
modify_date  TIMESTAMP (6),
scope_bin    BLOB
)
TABLESPACE &_tablespace
PCTFREE 10
PCTUSED 1
INITRANS 1
MAXTRANS 255
STORAGE(INITIAL 32M PCTINCREASE 0 FREELISTS 6 FREELIST GROUPS 5 BUFFER_POOL DEFAULT)
NOCOMPRESS
LOGGING
LOB (scope_bin)
STORE AS ( TABLESPACE &_lob_tablespace
DISABLE STORAGE IN ROW
CHUNK 8192
PCTVERSION 0
CACHE
STORAGE(INITIAL 32M NEXT 4M PCTINCREASE 0 FREELISTS 6 FREELIST GROUPS 5 BUFFER_POOL DEFAULT)
)
NOCACHE,
NOPARALLEL,
MONITORING
/

ALTER TABLE &_owner..cube_scope
ADD CONSTRAINT cs_pk PRIMARY KEY (cikey)
USING INDEX
TABLESPACE &_tablespace
PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
/

PROMPT “sync_store”
CREATE TABLE &_owner..sync_store
(    cikey        INTEGER NOT NULL,
domain_ref   INTEGER,
bin_csize    INTEGER,
bin_usize    INTEGER,
bin          BLOB
)
TABLESPACE &_tablespace
PCTFREE 10
PCTUSED 1
INITRANS 1
MAXTRANS 255
STORAGE(INITIAL 32M PCTINCREASE 0 FREELISTS 6 FREELIST GROUPS 5 BUFFER_POOL DEFAULT)
NOCOMPRESS
LOGGING
LOB (bin)
STORE AS ( TABLESPACE &_lob_tablespace
DISABLE STORAGE IN ROW
CHUNK 8192
PCTVERSION 0
CACHE
STORAGE(INITIAL 32M NEXT 4M PCTINCREASE 0 FREELISTS 6 FREELIST GROUPS 5 BUFFER_POOL DEFAULT)
)
NOCACHE,
NOPARALLEL,
MONITORING
/

CREATE INDEX &_owner..ss_fk ON &_owner..sync_store (cikey)
TABLESPACE &_tablespace
PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
NOPARALLEL
/

PROMPT “XML_Document”
CREATE TABLE &_owner..xml_document
(    dockey       VARCHAR2(200 CHAR) NOT NULL,
domain_ref   INTEGER,
bin_csize    INTEGER,
bin_usize    INTEGER,
bin          BLOB,
modify_date  TIMESTAMP,
bin_format   INTEGER
)
TABLESPACE &_tablespace
PCTFREE 10
PCTUSED 1
INITRANS 1
MAXTRANS 255
STORAGE(INITIAL 32M PCTINCREASE 0 FREELISTS 6 FREELIST GROUPS 5 BUFFER_POOL DEFAULT)
NOCOMPRESS
LOGGING
LOB (bin)
STORE AS ( TABLESPACE &_lob_tablespace
DISABLE STORAGE IN ROW
CHUNK 8192
PCTVERSION 0
CACHE
STORAGE(INITIAL 32M NEXT 4M PCTINCREASE 0 FREELISTS 6 FREELIST GROUPS 5 BUFFER_POOL DEFAULT)
)
NOCACHE,
NOPARALLEL,
MONITORING
/

ALTER TABLE &_owner..xml_document
ADD CONSTRAINT xml_doc_pk PRIMARY KEY (dockey)
USING INDEX
TABLESPACE &_tablespace
PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
/

PROMPT “Audit_details”
CREATE TABLE &_owner..audit_details
(    cikey        INTEGER NOT NULL,
domain_ref   INTEGER,
detail_id    INTEGER NOT NULL,
bin_csize    INTEGER,
bin_usize    INTEGER,
doc_ref      VARCHAR2(300 CHAR),
bin          BLOB
)
TABLESPACE &_tablespace
PCTFREE 10
PCTUSED 1
INITRANS 1
MAXTRANS 255
STORAGE(INITIAL 32M PCTINCREASE 0 FREELISTS 6 FREELIST GROUPS 5 BUFFER_POOL DEFAULT)
NOCOMPRESS
LOGGING
LOB (bin)
STORE AS ( TABLESPACE &_lob_tablespace
DISABLE STORAGE IN ROW
CHUNK 8192
PCTVERSION 0
CACHE
STORAGE(INITIAL 32M NEXT 4M PCTINCREASE 0 FREELISTS 6 FREELIST GROUPS 5 BUFFER_POOL DEFAULT)
)
NOCACHE,
NOPARALLEL,
MONITORING
/

ALTER TABLE &_owner..audit_details
ADD CONSTRAINT ad_pk PRIMARY KEY (cikey,detail_id)
USING INDEX
TABLESPACE &_tablespace
PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
/

PROMPT “Populate cube_scope”
INSERT INTO &_owner..cube_scope
( SELECT * FROM &_owner..cube_scope_&rundate )
/

COMMIT;

PROMPT “Polulate sync_store”
INSERT INTO &_owner..sync_store
( SELECT * FROM &_owner..sync_store_&rundate )
/

COMMIT;

PROMPT “Populate XML_Document”
INSERT INTO &_owner..xml_document
( SELECT * FROM &_owner..xml_document_&rundate )
/

COMMIT;

PROMPT “Polulate audit_details”
INSERT INTO &_owner..audit_details
( SELECT * FROM &_owner..audit_details_&rundate )
/

COMMIT;

PROMPT “Step 3”
PROMPT “Reclaim space”

set linesize 142
set pagesize 10000
set trimspool on
set heading off
set feedback off

WHENEVER SQLERROR CONTINUE

SELECT ‘alter table &_owner..’ || table_name || ‘ deallocate unused; ‘   || chr(10) ||
‘alter table &_owner..’ || table_name || ‘ enable row movement; ‘  || chr(10) ||
‘alter table &_owner..’ || table_name || ‘ shrink space compact; ‘ || chr(10) ||
‘alter table &_owner..’ || table_name || ‘ shrink space; ‘         ||chr(10) ||
‘alter table &_owner..’ || table_name || ‘ disable row movement;’
FROM  dba_tables
WHERE owner = upper(‘&_owner’)

spool shrink.sql
/
spool off
@shrink.sql

PROMPT “Step 4”
PROMPT “Calculate statistics”
EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(ownname => upper(‘&_owner’), cascade => TRUE, method_opt => ‘for all columns size repeat’);

PROMPT “Done”

Advertisements

Written by Jacco H. Landlust

September 17, 2009 at 9:26 pm

Posted in BPEL, RAC, RDBMS

2 Responses

Subscribe to comments with RSS.

  1. Awesome blog! Thanks your sharing!

    訂做手機殼

    April 7, 2015 at 9:10 pm

  2. Thanks for your sharing:)

    qsire

    January 20, 2016 at 11:43 am


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: