java.lang.OutOfMemoryError: Java heap space

JVM Tuning

1.Xms建議等於Xmx
2.Xmn要小於Xmx
3.Heap size 不能決定JVM真正使用記憶體的大小，實際上會更大
Memory usage=Stack Size+Heap Size+Code Size
4.調整Stack size的參數是Xss

Java Virtual Machine Statistics Monitoring Tool

Using the gcutil option

This example attaches to lvmid 21891 and takes 7 samples at 250 millisecond intervals and displays the output as specified by the -gcutil option.

jstat -gcutil 21891 250 7
S0 S1 E O P YGC YGCT FGC FGCT GCT
12.44 0.00 27.20 9.49 96.70 78 0.176 5 0.495 0.672
12.44 0.00 62.16 9.49 96.70 78 0.176 5 0.495 0.672
12.44 0.00 83.97 9.49 96.70 78 0.176 5 0.495 0.672
0.00 7.74 0.00 9.51 96.70 79 0.177 5 0.495 0.673
0.00 7.74 23.37 9.51 96.70 79 0.177 5 0.495 0.673
0.00 7.74 43.82 9.51 96.70 79 0.177 5 0.495 0.673
0.00 7.74 58.11 9.51 96.71 79 0.177 5 0.495 0.673

Java內存洩露

temp

update OSCOUNTRYKIT l
set L.OCKEXPIRE_DATE =(
SELECT MAX(P.OCKEFFECTIVE_DATE) - 1
FROM OSCOUNTRYKIT P
WHERE NVL(P.OCKVENDOR,'OCKODM') = NVL(L.OCKVENDOR,'OCKODM') AND
NVL(P.OCKBRAND,'NULL') = NVL(L.OCKBRAND,'NULL') AND
NVL(P.OCKCOUNTRYCODE,'NULL') = NVL(L.OCKCOUNTRYCODE,'NULL') AND
p.OCKEXPIRE_DATE = l.OCKEXPIRE_DATE
GROUP BY P.OCKBRAND, P.OCKCOUNTRYCODE, P.OCKVENDOR, P.OCKEXPIRE_DATE
HAVING COUNT(*) > 1 and min(P.OCKEFFECTIVE_DATE) = l.OCKEFFECTIVE_DATE
)
where exists(
SELECT MAX(P.OCKEFFECTIVE_DATE) - 1
FROM OSCOUNTRYKIT P
WHERE NVL(P.OCKVENDOR,'OCKODM') = NVL(L.OCKVENDOR,'OCKODM') AND
NVL(P.OCKBRAND,'NULL') = NVL(L.OCKBRAND,'NULL') AND
NVL(P.OCKCOUNTRYCODE,'NULL') = NVL(L.OCKCOUNTRYCODE,'NULL') AND
p.OCKEXPIRE_DATE = l.OCKEXPIRE_DATE
GROUP BY P.OCKBRAND, P.OCKCOUNTRYCODE, P.OCKVENDOR, P.OCKEXPIRE_DATE
HAVING COUNT(*) > 1 and min(P.OCKEFFECTIVE_DATE) = L.OCKEFFECTIVE_DATE
)

oracle 更新截止日

update PRICELIST l
set L.PLEXPIRE_DATE =(
SELECT MAX(P.PLEFFECTIVE_DATE) - 1

FROM PRICELIST P
WHERE NVL(P.PLRO,'PLRO') = NVL(L.PLRO,'PLRO') AND
NVL(P.PLNS,'PLNS') = NVL(L.PLNS,'PLNS') AND
NVL(P.PLODM,'PLODM') = NVL(L.PLODM,'PLODM') AND
NVL(P.PLBRAND,'NULL') = NVL(L.PLBRAND,'NULL') AND
NVL(P.PLMRK_NAME,'NULL') = NVL(L.PLMRK_NAME,'NULL') AND
NVL(P.PLBOM_NAME,'NULL') = NVL(L.PLBOM_NAME,'NULL') AND
NVL(P.PLODD,'PLODD') = NVL(L.PLODD,'PLODD') AND
NVL(P.PLOS,'PLOS') = NVL(L.PLOS,'PLOS') AND
NVL(P.PLCPU,'PLCPU') = NVL(L.PLCPU,'PLCPU') AND
NVL(P.PLLCD,'PLLCD') = NVL(L.PLLCD,'PLLCD') AND
NVL(P.PLBEZEL,'PLBEZEL') = NVL(L.PLBEZEL,'PLBEZEL') AND
NVL(P.PLCHASSIS,'PLCHASSIS') = NVL(L.PLCHASSIS,'PLCHASSIS') AND
NVL(P.PLMS_SPECIAL,'PLMS_SPECIAL') = NVL(L.PLMS_SPECIAL,'PLMS_SPECIAL') AND
NVL(P.PLITEMNAME,'PLITEMNAME') = NVL(L.PLITEMNAME,'PLITEMNAME') AND
p.PLEXPIRE_DATE = l.PLEXPIRE_DATE
--and P.PLEFFECTIVE_DATE = l.PLEFFECTIVE_DATE
GROUP BY p.PLRO, p.PLNS, p.PLODM, p.PLBRAND, p.PLMRK_NAME, p.PLBOM_NAME, p.PLOS, p.PLCPU, p.PLLCD, p.PLODD,
p.PLBEZEL, p.PLCHASSIS, p.PLMS_SPECIAL, p.PLITEMNAME, p.PLEXPIRE_DATE
HAVING COUNT(*) > 1 and min(P.PLEFFECTIVE_DATE) = l.PLEFFECTIVE_DATE

)
where exists(
SELECT MAX(P.PLEFFECTIVE_DATE) - 1

FROM PRICELIST P
WHERE NVL(P.PLRO,'PLRO') = NVL(L.PLRO,'PLRO') AND
NVL(P.PLNS,'PLNS') = NVL(L.PLNS,'PLNS') AND
NVL(P.PLODM,'PLODM') = NVL(L.PLODM,'PLODM') AND
NVL(P.PLBRAND,'NULL') = NVL(L.PLBRAND,'NULL') AND
NVL(P.PLMRK_NAME,'NULL') = NVL(L.PLMRK_NAME,'NULL') AND
NVL(P.PLBOM_NAME,'NULL') = NVL(L.PLBOM_NAME,'NULL') AND
NVL(P.PLODD,'PLODD') = NVL(L.PLODD,'PLODD') AND
NVL(P.PLOS,'PLOS') = NVL(L.PLOS,'PLOS') AND
NVL(P.PLCPU,'PLCPU') = NVL(L.PLCPU,'PLCPU') AND
NVL(P.PLLCD,'PLLCD') = NVL(L.PLLCD,'PLLCD') AND
NVL(P.PLBEZEL,'PLBEZEL') = NVL(L.PLBEZEL,'PLBEZEL') AND
NVL(P.PLCHASSIS,'PLCHASSIS') = NVL(L.PLCHASSIS,'PLCHASSIS') AND
NVL(P.PLMS_SPECIAL,'PLMS_SPECIAL') = NVL(L.PLMS_SPECIAL,'PLMS_SPECIAL') AND
NVL(P.PLITEMNAME,'PLITEMNAME') = NVL(L.PLITEMNAME,'PLITEMNAME') AND
p.PLEXPIRE_DATE = l.PLEXPIRE_DATE
--and P.PLEFFECTIVE_DATE = l.PLEFFECTIVE_DATE
GROUP BY p.PLRO, p.PLNS, p.PLODM, p.PLBRAND, p.PLMRK_NAME, p.PLBOM_NAME, p.PLOS, p.PLCPU, p.PLLCD, p.PLODD,
p.PLBEZEL, p.PLCHASSIS, p.PLMS_SPECIAL, p.PLITEMNAME, p.PLEXPIRE_DATE
HAVING COUNT(*) > 1 and min(P.PLEFFECTIVE_DATE) = l.PLEFFECTIVE_DATE

)

Oracle FlashBack

alter table CM_CODE enable row movement;
flashback table CM_CODE to timestamp to_timestamp('2009-05-18 23:10:00','yyyy-mm-dd hh24:mi:ss');

Oracle 英文大小寫function

upper() 轉大寫

lower() 轉小寫

INITCAP() 首字大寫
PS:以底線分隔的字，首字也是大寫

example

SELECT INITCAP ('JAMES_jan') FROM dual;
---------
James_Jan
1 row selected.

java的四捨五入

import java.math.BigDecimal;

public class jamesMath {

private static final int DEF_DIV_SCALE = 10;

public static double round(double v,int scale){

if(scale<0){

throw new IllegalArgumentException(

"The scale must be a positive integer or zero");

}

BigDecimal b = new BigDecimal(Double.toString(v));

BigDecimal one = new BigDecimal("1");

return b.divide(one,scale,BigDecimal.ROUND_HALF_UP).doubleValue();

}

/**

* 兩個Double數相加

* @param v1

* @param v2

* @return double

*/

public static double add(Double v1,Double v2){

BigDecimal b1 = new BigDecimal(v1.toString());

BigDecimal b2 = new BigDecimal(v2.toString());

return b1.add(b2).doubleValue();

}

/**

* 兩個Double數相減

* @param v1

* @param v2

* @return double

*/

public static double sub(Double v1,Double v2){

BigDecimal b1 = new BigDecimal(v1.toString());

BigDecimal b2 = new BigDecimal(v2.toString());

return b1.subtract(b2).doubleValue();

}

/**

* 兩個Double數相乘

* @param v1

* @param v2

* @return double

*/

public static double mul(Double v1,Double v2){

BigDecimal b1 = new BigDecimal(v1.toString());

BigDecimal b2 = new BigDecimal(v2.toString());

return b1.multiply(b2).doubleValue();

}

/**

* 兩個Double數相除

* @param v1

* @param v2

* @return double

*/

public static double div(Double v1,Double v2){

BigDecimal b1 = new BigDecimal(v1.toString());

BigDecimal b2 = new BigDecimal(v2.toString());

return b1.divide(b2,DEF_DIV_SCALE,BigDecimal.ROUND_HALF_UP).doubleValue();

}

/**

* 兩個Double數相除，並保留scale位小數

* @param v1

* @param v2

* @param scale

* @return double

*/

public static double div(Double v1,Double v2,int scale){

if(scale<0){

throw new IllegalArgumentException(

"The scale must be a positive integer or zero");

}

BigDecimal b1 = new BigDecimal(v1.toString());

BigDecimal b2 = new BigDecimal(v2.toString());

return b1.divide(b2,scale,BigDecimal.ROUND_HALF_UP).doubleValue();

}

}

無關連表格 Join

因需要出一份報表，列出所有的資料，
但是資料分存不同表格且無相關聯。
單純是列印出來而已。

例如
==========================

CPU	HDD					OS
C2QQ8200	D1500GB7.2KS					DOS
A64X25050e	D150GB10KS					FreeDOS
A64X250940	D160GB7.2KS					LinuxCM
A64X244940	D200GB7.2KS					LinuxXW
A64X246940						MCE
A64X24850e
A64X248940
A64X25050e
A64X250940

一般join會是相乘的結果，
要像上面這樣顯示，
需要使用rownum來關聯。

WITH
TA AS (
SELECT ROWNUM AS R, CPU FROM (SELECT DISTINCT CPU FROM TABLE_A ORDER BY CPU)
),
TB AS (
SELECT ROWNUM AS R, HDD FROM (SELECT DISTINCT HDD FROM TABLE_B ORDER BY HDD)
)
SELECT TA.*, TB.*
FROM TA
FULL OUTER JOIN TB
ON TA.R = TB.R
ORDER BY 1;

Oracle 表格名稱&欄位名稱

紀錄Table的資訊
Select * form USER_ALL_TABLES;


紀錄Column的資訊
Select * form USER_ALL_TABLES;

Oracle 刪除重複資料

DELETE
FROM TABLE_NAME
WHERE ROWID = ANY(
SELECT ROW_ID
FROM (COLUMN_A, COLUMN_B ,COUNT(*), MAX(ROWID) AS ROW_ID
FROM TABLE_NAME
GROUP BY COLUMN_A, COLUMN_B
HAVING COUNT(*) > 1
)
)

重複執行數次即可，
另一種寫法執行一次，不過比較複雜一點。

Oracle INSTR

SELECT INSTR('abc','b') FROM DUAL;

INSTR('ABC','B')
----------------
2
1 row selected.




SELECT INSTR('abc','a') FROM DUAL;

INSTR('ABC','A')
----------------
1
1 row selected.