Oracle 12c interactive poster

April 4, 2016 Leave a comment
Categories: Uncategorized

Batched insert from java client

October 16, 2015 Leave a comment

Database side

CREATE TABLE MESSAGE (
      id                 INTEGER,
      status             INTEGER,
      CONSTRAINT pk_message PRIMARY KEY (id) USING INDEX LOCAL INITRANS 100 TABLESPACE usertbs
)
PARTITION BY HASH (id) PARTITIONS 128 INITRANS 100 TABLESPACE usertbs ;

CREATE TABLE MESSAGE_CONTENT (
  MESSAGE_ID INTEGER, 
  PIECE_ID INTEGER,
	CONTENT varchar2(4000 char), 
 	CONSTRAINT PK_MESSAGE_CONTENT PRIMARY KEY (MESSAGE_ID, PIECE_ID) USING INDEX LOCAL
  INITRANS 100 TABLESPACE USERTBS, 
  CONSTRAINT fk_message_id FOREIGN KEY (message_id) REFERENCES message(id)
 ) 
 PARTITION BY REFERENCE (fk_message_id) INITRANS 100 TABLESPACE usertbs 
 STORAGE ( INITIAL 8M NEXT 8M BUFFER_POOL RECYCLE);
    
create sequence seq_message cache 100000 ;

create or replace type varchar_table as table of varchar2(4000 char) ;
create or replace type integer_table as table of integer ;

CREATE OR REPLACE PROCEDURE insert_message_content (
  message_id integer, 
  piece_id_arr IN integer_table, 
  message_content_arr IN VARCHAR_TABLE
) IS
BEGIN
    -- this is the magic batched mode
    FORALL i IN 1..piece_id_arr.COUNT
      INSERT INTO MESSAGE_CONTENT VALUES (message_id, piece_id_arr(i), message_content_arr(i));
EXCEPTION
  WHEN OTHERS THEN RAISE ;
END ;
/

Java client side


/*
 * declare the arrays
 */ 
String bigContent[] = new String[6] ;
int bigContentIds[] = new int[6] ;

/*
 * fill the arrays as needed
 */ 

/*
 * connect to database
 */ 
OracleDataSource ods = new OracleDataSource();
ods.setURL("jdbc:oracle:thin:@(xxx)");
ods.setUser("yyy");
ods.setPassword("zzz");
dbConnection = ods.getConnection();
dbConnection.setAutoCommit(false);
((OracleConnection)dbConnection).setImplicitCachingEnabled(true);
((OracleConnection)dbConnection).setStatementCacheSize(5000);

/*
 * inserts the message content
 */
ArrayDescriptor cdes = ArrayDescriptor.createDescriptor("VARCHAR_TABLE", dbconnection);
ARRAY ca = new ARRAY(cdes,dbconnection,bigContent);

ArrayDescriptor ides = ArrayDescriptor.createDescriptor("INTEGER_TABLE", dbconnection);
ARRAY ci = new ARRAY(ides,dbconnection,bigContentIds);
		  
CallableStatement cs = dbconnection.prepareCall("call insert_message_content(:1,:2,:3)");
cs.setInt(1, messageId);
cs.setArray(2, ci);
cs.setArray(3, ca);
cs.execute();

Kill libary cache : mutex x wait

July 22, 2015 Leave a comment

check executions, parse_calls and version_count in v$sqlarea. If no issue identified :

increase _kgl_hot_object_copies (default is number of cpu).

If still not good, identify the most pinned cursors in shared pool :

SELECT * FROM (
    SELECT kglnaobj name, kglobt23 LOCKED_TOTAL,
      kglobt24 PINNED_TOTAL, kglhdexc EXECUTIONS, kglhdnsp NAMESPACE
    FROM x$kglob WHERE kglobtyd='CURSOR'
    ORDER BY kglobt24 DESC
) WHERE rownum 0;

Then mark the cursor as hot :

exec dbms_shared_pool.markhot('SYS','select name from undo$ where file#=:1 and block#=:2 and ts#=:3 and       status$ != 1',NAMESPACE=>0);

Ref :
https://jagjeet.wordpress.com/2011/12/12/library-cache-mutex-x/
https://andreynikolaev.wordpress.com/2011/05/01/divide-and-conquer-the-true-mutex-contention/

Orientdb distributed configuration

July 3, 2015 Leave a comment

RAID 10 on class t :

{
    "autoDeploy": true,
    "hotAlignment": false,
    "executionMode": "undefined",
    "readQuorum": 1,
    "writeQuorum": 2,
    "failureAvailableNodesLessQuorum": false,
    "readYourWrites": true,
    "clusters": {
        "internal": {
        },
        "index": {
        },
        "*": {
            "servers" : [ "" ]
        },
		"t_node0": {
            "servers" : [ "node0", "node1" ]
        },
		"t_node1": {
            "servers" : [ "node1", "node0" ]
        },
		"t_node2": {
            "servers" : [ "node2", "node3" ]
        },
		"t_node3": {
            "servers" : [ "node3", "node2" ]
        },
		"t_node4": {
            "servers" : [ "node4", "node5" ]
        },
		"t_node5": {
            "servers" : [ "node5", "node4" ]
        }
    }
}

Distributed graph database

April 22, 2015 Leave a comment

Getting started OrientDB

April 19, 2015 Leave a comment

Distributed multimodel (graph, json, key-value) databases

April 18, 2015 Leave a comment
Follow

Get every new post delivered to your Inbox.