Posts

Showing posts from July, 2012

Java programmatic memory analysis

public static void main(String[] args)
  {
    MemoryMXBean mbean = ManagementFactory.getMemoryMXBean();
    System.out.println(mbean.getHeapMemoryUsage());
    System.out.println("Avail " + (mbean.getHeapMemoryUsage().getMax() - mbean.getHeapMemoryUsage().getUsed()) / 1024 + "KB");

    List mbeans = ManagementFactory.getMemoryPoolMXBeans();
    for (MemoryPoolMXBean bean : mbeans)
    {
        String name = bean.getName();
        MemoryType type = bean.getType();
        MemoryUsage usage = bean.getUsage();
        MemoryUsage peak = bean.getPeakUsage();
        MemoryUsage coll = bean.getCollectionUsage();

        System.out.println(String.format("%s, type=%s, usage=%s, peak=%s, coll=%s", name, type, usage, peak, coll));
    }
  }

JDBC drivers and implicit statement caching

http://www.oracle.com/technetwork/database/enterprise-edition/memory.pdf

11.2 JDBC Developers Guide HTML

11.2 JDBC Developers Guide - cache configuration and use PDF

The property can either be set as a System property via -D or as a connection property on the getConnection method. Note that setting freeMemoryOnEnterImplicitCache does not cause the parameter value buffers to be released, only the row data buffers.



oracle.jdbc.freeMemoryOnEnterImplicitCache=true/false


The default is Integer.MAX_VALUE. This is the maximum size for a buffer which will be stored in the internal buffer cache. A single size is used for both the char[] and byte[] buffers. The size is in chars for the char[] buffers and bytes for the byte[] buffers. It is the maximum buffer size, not a predefined size.


oracle.jdbc.maxCachedBufferSize=Integer.MAX_VALUE
oracle.jdbc.useThreadLocalBufferCache=true/false
With the 11.2 driver setting maxCachedBufferSize can sometimes improve performance in very large systems with larg…

Java to Oracle multi-dimensional array example

This Java code creates a multi-dimensional (2d) array and passes it to an Oracle PL/SQL Stored Procedure as varray or varrays which then iterates the multidimensional varray collections and inserts the data into a table.

Oracle 11g R2 (11.2) Documentation

package oracleMultiDimensionalArrays;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;

public class Test
{
  public static void send() throws SQLException
  {
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
    Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@dbIP:1521:SID", "troy_a", "troy_a");

    ArrayDescriptor desc = ArrayDescriptor.createDescriptor("VARRAY2", conn);

    int[][] elements = {{11, 12}, {21, 22}, {31, 32}};
    ARRAY array2 = new ARRAY(desc, conn, elements);

    CallableStatement cstmt = conn.prepareCall…

PLSQL iteration and bind variable

How to execute anonymous PL/SQL from Java and return resultset

Examples of PL/SQL

While loopLoop labelsLoop controlbind variables in Dynamic execution
set serveroutput on;
declare
  entityId data_table.entity_id%TYPE := 123;
  delimiter char := '';
  more char := 'T';
  cnt NUMBER := 1;
  lastTs data_table.timestamp%TYPE := get_ms_from_date(sysdate);
  maxTs data_table.timestamp%TYPE;
  timestampInClause VARCHAR2(100);
  queryStr VARCHAR2(2000);
  queryCnt NUMBER;
begin
  <<ts_loop>>
  while more = 'T' and cnt <= 5
  LOOP
    select max(timestamp) into maxTs from data_table where entity_id = deviceId and timestamp < lastTs;

    IF (maxTs IS NOT NULL) THEN
      timestampInClause := timestampInClause || delimiter || maxTs;
      lastTs := maxTs;
      delimiter := ',';
      cnt := cnt + 1;
    ELSE
      EXIT ts_loop;
    END IF;
  END LOOP ts_loop;

  DBMS_OUTPUT.PUT_LINE('timestampInClause ' || timestampInClause);

  queryStr :=…

Oracle query last 5 records where sparsely populated

Walk the last 'n' max(timestamp) records:

AND id = 123
AND a.timestamp   >=
(SELECT MAX(TIMESTAMP) FROM data_table WHERE id = 123 AND TIMESTAMP   <
(SELECT MAX(TIMESTAMP) FROM data_table WHERE id = 123 AND TIMESTAMP   <
(SELECT MAX(TIMESTAMP) FROM data_table WHERE id = 123 AND TIMESTAMP   <
(SELECT MAX(TIMESTAMP) FROM data_table WHERE id = 123 AND TIMESTAMP   <
(SELECT MAX(TIMESTAMP) FROM data_table WHERE id = 123)))))

This method was chosen because the data must be ordered descending and using any particular time range may cover an enormous amount of high activity data or little. Always wanted the last 5 records and didn't want to incur high sort costs when lots of data was in the window.

The index columns included (id, timestamp) This is a very fast query because the timestamp b-tree can be walked quickly.

Shrink VBox image

To shrink/compact VirtualBox image:

Was .vmdk and compact only works on .vdi.

Clone the image and format to .vdi:

First created another partition with enough space then cloned and formatted:

/usr/lib/virtualbox/VBoxManage clonehd /media/winXpVM/tcollinsworth-lt/tcollinsworth-lt.vmdk /media/winXpVM2/tcollinsworth-lt/tcollinsworth-lt.vdi  --format VDI

Added the disk to VM in VirtualBox Manager and removed the old one. Only change was to switch the disk to primary master.

To convert .img to .vdi:
VBoxManage convertdd  winXP.img winXP.vdiStarted the OS and cleaned it up disk:
downloadstemp filestrashrestore pointsetc.Zeroed unused disk blocks with: 
sdelete -z
Shutdown OS
Compact the .vdi
/usr/lib/virtualbox/VBoxManage modifyhd /media/winXpVM2/tcollinsworth-lt/tcollinsworth-lt.vdi --compact