Simulate lock manager with hash cluster

December 12, 2016 Leave a comment

Avoids contentions generated by b-tree indexed primary keys.

CREATE CLUSTER APPLOCK_HCL (LKEY VARCHAR2(128)) SIZE 512 SINGLE TABLE HASHKEYS 10000 ;

CREATE TABLE APPLOCK
(
  LKEY VARCHAR2(128) not null,
  DESCRIPTION VARCHAR2(256)
) CLUSTER APPLOCK_HCL( LKEY ) ;

create or replace trigger trg_applock_un
BEFORE INSERT on applock
FOR EACH ROW
BEGIN
for i in (
    select nullfrom applock where lkey=:new.lkey
)
loop 
    raise DUP_VAL_ON_INDEX ;
end loop ;
END;
/

Explain optimizer choices without 10053 trace

December 12, 2016 Leave a comment
begin
	dbms_sqldiag.dump_trace(p_sql_id => :si,
		p_child_number => :cn,
		p_component => 'Compiler',
		p_file_id => 'DSDT1'
	);
end;
/
Categories: Optimizer, Oracle Database

Goldengate init load

November 7, 2016 Leave a comment
Categories: Goldengate

PostgreSQL autovacuum tuning

November 5, 2016 Leave a comment
Categories: PostgreSQL

Replace string in file with perl

October 21, 2016 Leave a comment

perl -i -pe ‘s/this/that/g;’ file1

Categories: Administration

Oracle RMAN parallel backup (without archives)

October 14, 2016 Leave a comment

CONFIGURE RETENTION POLICY TO REDUNDANCY 10 ;
CONFIGURE DEVICE TYPE DISK PARALLELISM 12 ;
run {
allocate channel t1 type disk;
allocate channel t2 type disk;
allocate channel t3 type disk;
allocate channel t4 type disk;
allocate channel t5 type disk;
allocate channel t6 type disk;
allocate channel t7 type disk;
allocate channel t8 type disk;
allocate channel t9 type disk;
allocate channel t10 type disk;
allocate channel t11 type disk;
allocate channel t12 type disk;
backup as compressed backupset section size 500m database format ‘/df_%U’ ;
backup as compressed backupset current controlfile format ‘/cf_%U’ ;
}

Create baseline from AWR

September 7, 2016 Leave a comment
-- creates sql set
BEGIN
	dbms_sqltune.create_sqlset(sqlset_name => :sql_id_id_to_tune ||'_sqlset');
END ;
/

-- loads plans from AWR in SQL set
DECLARE
	cur sys_refcursor;
BEGIN
	OPEN cur FOR SELECT VALUE(P)
		FROM TABLE(
		   dbms_sqltune.select_workload_repository(
			begin_snap=> :begin_snap_id, 
			end_snap=> :end_snap_id,
			basic_filter=>'sql_id = '''||:sql_id_id_to_tune ||''' and plan_hash_value = '''||:sql_id_good_plan||'''',attribute_list=>'ALL')
				  ) p;
		DBMS_SQLTUNE.LOAD_SQLSET( sqlset_name=> :sql_id_id_to_tune ||'_sqlset', populate_cursor=>cur);
	CLOSE cur;
END;
/

-- loads baseline from SQL set 
DECLARE
	my_plans pls_integer;
BEGIN
	my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
		sqlset_name => :sql_id_id_to_tune ||'_sqlset'
    );
END;
/

-- drops SQL set
BEGIN
	dbms_sqltune.drop_sqlset(sqlset_name => :sql_id_id_to_tune ||'_sqlset');
END ;
/