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 per

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);     CallableStatemen

PLSQL iteration and bind variable

How to execute anonymous PL/SQL from Java and return resultset Examples of PL/SQL While loop Loop labels Loop control bind 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

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.vdi Started the OS and cleaned it up disk: downloads temp files trash restore points etc. 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