Sunday, November 6, 2016

Useful SQLs to get Oracle RAC stats Data for Daily Job

1.get snap ID by time

SELECT snap_id
  FROM DBA_HIST_SNAPSHOT x
 WHERE     x.END_INTERVAL_TIME >= TO_DATE ('201610251500', 'yyyymmddhh24mi')
       AND x.begin_interval_time <=
              TO_DATE ('201610251510', 'yyyymmddhh24mi');


2.get blocking session by snap ID
SELECT  
                 LEVEL lv,
                  CONNECT_BY_ISLEAF isleaf,
                  CONNECT_BY_ISCYCLE iscycle,
                  t.session_id,
                  t.sql_id,
                  t.session_type,
                  t.event,
                  t.session_state,
                  t.blocking_inst_id,
                  t.blocking_session,
                  t.blocking_session_status,
                  t.p1text,
                  t.p1,
                  t.p2text,
                  t.p2,
                  t.p3text,
                  t.p3,
 t.time_waited
             FROM dba_hist_active_sess_history t
where  snap_id > 10875
                  AND snap_id <= 10876
 and dbid=3104116170
       START WITH  blocking_session IS NOT NULL
       CONNECT BY NOCYCLE  PRIOR dbid = dbid
                          AND PRIOR sample_time = sample_time
                          AND PRIOR blocking_inst_id = instance_number
                          AND PRIOR blocking_session = session_id
                          AND PRIOR blocking_session_serial# = session_serial#
ORDER SIBLINGS BY sample_time;

3.get top N hot objects for latest month peak time
SELECT day,
       object_name,
       object_type,
       LOGICAL_READS,
       PHYSICAL_READS,
       GC_CR_BLOCKS_SERVED,
       GC_CU_BLOCKS_SERVE,
       Row_lock_waits,
       DB_block_changes,
       ITL_waits
  FROM (  SELECT day,
                 object_name,
                 object_type,
                 LOGICAL_READS,
                 PHYSICAL_READS,
                 GC_CR_BLOCKS_SERVED,
                 GC_CU_BLOCKS_SERVE,
                 Row_lock_waits,
                 DB_block_changes,
                 ITL_waits,
                 Ts#,
                 ROW_NUMBER ()
                    OVER (PARTITION BY day ORDER BY Row_lock_waits DESC)
                    AS rownb
            FROM (SELECT c.object_name,
                         c.object_type,
                         a.ts#,
                         a.obj#,
                         TO_CHAR (b.begin_interval_time, 'yyyymmdd') AS day,
                         TRUNC (
                            SUM (A.ITL_WAITS_DELTA)
                               OVER (PARTITION BY A.OBJ#, a.snap_id),
                            2)
                            AS ITL_WAITS,
                         TRUNC (
                            SUM (A.ROW_LOCK_WAITS_DELTA)
                               OVER (PARTITION BY A.OBJ#, a.snap_id),
                            2)
                            AS ROW_LOCK_WAITS,
                         TRUNC (
                            SUM (A.DB_BLOCK_CHANGES_DELTA)
                               OVER (PARTITION BY A.OBJ#, a.snap_id),
                            2)
                            AS DB_BLOCK_CHANGES,
                         TRUNC (
                            SUM (A.LOGICAL_READS_DELTA)
                               OVER (PARTITION BY A.OBJ#, a.snap_id),
                            2)
                            AS LOGICAL_READS,
                         TRUNC (
                            SUM (A.PHYSICAL_READS_DELTA)
                               OVER (PARTITION BY A.OBJ#, a.snap_id),
                            2)
                            AS PHYSICAL_READS,
                         TRUNC (
                            SUM (A.GC_CR_BLOCKS_SERVED_DELTA)
                               OVER (PARTITION BY A.OBJ#, a.snap_id),
                            2)
                            AS GC_CR_BLOCKS_SERVED,
                         TRUNC (
                            SUM (A.GC_CU_BLOCKS_SERVED_DELTA)
                               OVER (PARTITION BY A.OBJ#, a.snap_id),
                            2)
                            AS GC_CU_BLOCKS_SERVE,
                         ROW_NUMBER ()
                         OVER (PARTITION BY A.OBJ#, A.snap_id
                               ORDER BY A.OBJ#, A.snap_id)
                            AS RN,
                         A.snap_id
                    FROM DBA_HIST_SEG_STAT a,
                         DBA_HIST_SNAPSHOT b,
                         DBA_HIST_SEG_STAT_OBJ c
                   WHERE     a.dbid = b.dbid
                         AND a.dbid = c.dbid
                         AND C.obj# = a.obj#
                         AND a.snap_id = b.snap_id
                         AND a.dbid = 684227036
                         AND TO_CHAR (b.begin_interval_time, 'hh24mi') >=
                                '1355'
                         AND TO_CHAR (b.begin_interval_time, 'hh24mi') <=
                                '1405'
                         AND b.BEGIN_INTERVAL_TIME >=
                                TRUNC (ADD_MONTHS (SYSDATE, -1), 'MM')
                         AND b.BEGIN_INTERVAL_TIME <
                                TRUNC (ADD_MONTHS (SYSDATE, 0), 'MM')
                         AND UPPER (
                                SUBSTR (TO_CHAR (b.begin_interval_time, 'Day'),
                                        1,
                                        3)) IN
                                ('MON', 'TUE', 'WED', 'THU', 'FRI')) D
           WHERE D.rn = 1
        ORDER BY day DESC, ROW_LOCK_WAITS DESC)
 WHERE rownb <= 10;

 4.get top n SQL stats by snap ID
SELECT * FROM
(SELECT dhs.sql_id,
        ROUND(SUM(dhs.elapsed_time_delta/1000000),0) elapsed_time_secs,
        ROUND(SUM(dhs.cpu_time_delta/1000000),0) cpu_time_secs,
        SUM(dhs.disk_reads_delta) disk_reads,
        SUM(dhs.buffer_gets_delta) buffer_gets,
        SUM(dhs.px_servers_execs_delta) px_server_execs,
        SUM(dhs.rows_processed_delta) rows_processed,
        SUM(dhs.executions_delta) executions,
        ROUND(SUM(dhs.iowait_delta/1000000),0) iowait_secs,
        ROUND(SUM(dhs.clwait_delta/1000000),0) clwait_secs,
        ROUND(SUM(dhs.ccwait_delta/1000000),0) ccwait_secs,
        ROUND(SUM(dhs.apwait_delta/1000000),0) apwait_secs
 FROM dba_hist_sqlstat  dhs,
      gv$database  d,
      gv$instance i
 WHERE dhs.dbid = d.dbid
 AND   dhs.instance_number = i.instance_number
 AND   dhs.snap_id > 10875 AND dhs.snap_id <= 10876
 GROUP BY dhs.sql_id ORDER BY 2 DESC)
WHERE ROWNUM <= 10;

5.get top N wait events/SQLs by time waited 
SELECT s.event,
         s.sql_id,
         COUNT (*) counts_waited,
         SUM (time_waited) time_waited
    FROM dba_hist_active_sess_history  s,
         dba_hist_seg_stat_obj  o,
         gv$database d,
         gv$instance i
   WHERE     s.dbid = d.dbid
         AND s.instance_number = i.instance_number
         AND o.dbid(+) = s.dbid
         AND o.obj#(+) = s.current_obj#
         AND s.snap_id > 10875
         AND s.snap_id <= 10876
GROUP BY s.event, s.sql_id
ORDER BY time_waited DESC;

Posted by at No comments:
Labels:

Sunday, October 16, 2016

Oracle RAC Monitoring Key Metrics and SQLs

Sometime, you need compare this month database change with last month to show new release impact or some key feature difference on database, in order to list changes, you must give the key metrics to express the impact on RAC, based on my experience and give below metrics and SQLs.

1.List Perk Time Work Day SQL Total Executions.
2.List Perk Time Work Day SQL Total Buffer Get.
3.List Perk Time Work Day DB Block Changes.
4.List Perk Time Work Day DB Row Lock Waits.
5.List Perk Time Work Day DB OS CPU Usage(%).

I use webchart(free to use) to show my montor metrics, the graph list as following.




















The tool is very good and it's only need write some SQLs, then it can work well, but the SQL are very complex, so let me show the detail SQLs.

#List SQL Execution change
WITH lastMonth as ( 
select StartTime,weeklyNum,dayName,Executions,LogicIO,PhysicalIO,(LogicIO+PhysicalIO) as TotalIO 
from(  
SELECT TO_CHAR (a.begin_interval_time, 'dd') AS Day, 
       TO_CHAR (a.begin_interval_time, 'mmdd') AS StartTime, 
       TRUNC (SUM (b.executions_delta) OVER (PARTITION BY a.snap_id), 2) 
          AS Executions, 
       TRUNC (SUM (b.buffer_gets_delta) OVER (PARTITION BY a.snap_id), 2) 
          AS LogicIO, 
       TRUNC (SUM (b.disk_reads_delta) OVER (PARTITION BY a.snap_id), 2) 
          AS PhysicalIO, 
           UPPER (SUBSTR (TO_CHAR (a.begin_interval_time, 'Day'), 1, 3)) as dayName, 
       to_number(TO_CHAR (a.begin_interval_time, 'iw'))+4 AS weeklyNum, 
       ROW_NUMBER () OVER (PARTITION BY a.snap_id order by a.snap_id) AS RN 
  FROM dba_hist_snapshot a, dba_hist_sqlstat b 
 WHERE     b.dbid = a.dbid   
       AND b.dbid = ********
       AND b.instance_number = a.instance_number 
       AND b.snap_id = a.snap_id 
       AND TO_CHAR (a.begin_interval_time, 'hh24mi') >= '1555' 
       AND TO_CHAR (a.begin_interval_time, 'hh24mi') <= '1605' 
       AND a.BEGIN_INTERVAL_TIME >= next_day(ADD_MONTHS(sysdate, -2), 'Monday') 
       AND a.BEGIN_INTERVAL_TIME < next_day(ADD_MONTHS(sysdate, -1), 'Monday')  
       AND UPPER (SUBSTR (TO_CHAR (a.begin_interval_time, 'Day'), 1, 3)) IN ('MON', 'TUE', 'WED', 'THU', 'FRI') 
) where RN=1 order by StartTime), 
curMonth as  
( 
select StartTime,weeklyNum,dayName,Executions,LogicIO,PhysicalIO,(LogicIO+PhysicalIO) as TotalIO 
from( 
SELECT TO_CHAR (a.begin_interval_time, 'dd') AS Day, 
       TO_CHAR (a.begin_interval_time, 'mmdd') AS StartTime, 
       TRUNC (SUM (b.executions_delta) OVER (PARTITION BY a.snap_id), 2) 
          AS Executions, 
       TRUNC (SUM (b.buffer_gets_delta) OVER (PARTITION BY a.snap_id), 2) 
          AS LogicIO, 
       TRUNC (SUM (b.disk_reads_delta) OVER (PARTITION BY a.snap_id), 2) 
          AS PhysicalIO, 
           UPPER (SUBSTR (TO_CHAR (a.begin_interval_time, 'Day'), 1, 3)) as dayName, 
       to_number(TO_CHAR (a.begin_interval_time, 'iw')) AS weeklyNum, 
       ROW_NUMBER () OVER (PARTITION BY a.snap_id order by a.snap_id) AS RN 
  FROM dba_hist_snapshot a, dba_hist_sqlstat b 
 WHERE     b.dbid = a.dbid 
       AND b.dbid = ******** 
       AND b.instance_number = a.instance_number 
       AND b.snap_id = a.snap_id 
       AND TO_CHAR (a.begin_interval_time, 'hh24mi') >= '1555' 
       AND TO_CHAR (a.begin_interval_time, 'hh24mi') <= '1605' 
       AND a.BEGIN_INTERVAL_TIME >= next_day(ADD_MONTHS(sysdate, -1), 'Monday') 
       AND a.BEGIN_INTERVAL_TIME < next_day(ADD_MONTHS(sysdate, 0), 'Monday')  
       AND UPPER (SUBSTR (TO_CHAR (a.begin_interval_time, 'Day'), 1, 3)) IN ('MON', 'TUE', 'WED', 'THU', 'FRI') 
) where RN=1 order by StartTime) 
select to_date(a.StartTime,'mmdd') as StartTime,b.StartTime as lastStartTime,a.Executions as CurrentMonth,b.Executions as LastMonth, trunc((a.Executions-b.Executions)/b.Executions,7) as "Increment" 
from curMonth a ,lastMonth b 
where a.dayname=b.dayname 
and a.weeklyNum=b.weeklyNum 
order by 1 

#List Logic IO change
WITH lastMonth as ( 
select StartTime,weeklyNum,dayName,Executions,LogicIO,PhysicalIO,(LogicIO+PhysicalIO) as TotalIO 
from(  
SELECT TO_CHAR (a.begin_interval_time, 'dd') AS Day, 
       TO_CHAR (a.begin_interval_time, 'mmdd') AS StartTime, 
       TRUNC (SUM (b.executions_delta) OVER (PARTITION BY a.snap_id), 2) 
          AS Executions, 
       TRUNC (SUM (b.buffer_gets_delta) OVER (PARTITION BY a.snap_id), 2) 
          AS LogicIO, 
       TRUNC (SUM (b.disk_reads_delta) OVER (PARTITION BY a.snap_id), 2) 
          AS PhysicalIO, 
           UPPER (SUBSTR (TO_CHAR (a.begin_interval_time, 'Day'), 1, 3)) as dayName, 
       to_number(TO_CHAR (a.begin_interval_time, 'iw'))+4 AS weeklyNum, 
       ROW_NUMBER () OVER (PARTITION BY a.snap_id order by a.snap_id) AS RN 
  FROM dba_hist_snapshot a, dba_hist_sqlstat b 
 WHERE     b.dbid = a.dbid   
       AND b.dbid = ****** 
       AND b.instance_number = a.instance_number 
       AND b.snap_id = a.snap_id 
       AND TO_CHAR (a.begin_interval_time, 'hh24mi') >= '1555' 
       AND TO_CHAR (a.begin_interval_time, 'hh24mi') <= '1605' 
       AND a.BEGIN_INTERVAL_TIME >= next_day(ADD_MONTHS(sysdate, -2), 'Monday') 
       AND a.BEGIN_INTERVAL_TIME < next_day(ADD_MONTHS(sysdate, -1), 'Monday')  
       AND UPPER (SUBSTR (TO_CHAR (a.begin_interval_time, 'Day'), 1, 3)) IN ('MON', 'TUE', 'WED', 'THU', 'FRI') 
) where RN=1 order by StartTime), 
curMonth as  
( 
select StartTime,weeklyNum,dayName,Executions,LogicIO,PhysicalIO,(LogicIO+PhysicalIO) as TotalIO 
from( 
SELECT TO_CHAR (a.begin_interval_time, 'dd') AS Day, 
       TO_CHAR (a.begin_interval_time, 'mmdd') AS StartTime, 
       TRUNC (SUM (b.executions_delta) OVER (PARTITION BY a.snap_id), 2) 
          AS Executions, 
       TRUNC (SUM (b.buffer_gets_delta) OVER (PARTITION BY a.snap_id), 2) 
          AS LogicIO, 
       TRUNC (SUM (b.disk_reads_delta) OVER (PARTITION BY a.snap_id), 2) 
          AS PhysicalIO, 
           UPPER (SUBSTR (TO_CHAR (a.begin_interval_time, 'Day'), 1, 3)) as dayName, 
       to_number(TO_CHAR (a.begin_interval_time, 'iw')) AS weeklyNum, 
       ROW_NUMBER () OVER (PARTITION BY a.snap_id order by a.snap_id) AS RN 
  FROM dba_hist_snapshot a, dba_hist_sqlstat b 
 WHERE     b.dbid = a.dbid 
       AND b.dbid = ****** 
       AND b.instance_number = a.instance_number 
       AND b.snap_id = a.snap_id 
       AND TO_CHAR (a.begin_interval_time, 'hh24mi') >= '1555' 
       AND TO_CHAR (a.begin_interval_time, 'hh24mi') <= '1605' 
       AND a.BEGIN_INTERVAL_TIME >= next_day(ADD_MONTHS(sysdate, -1), 'Monday') 
       AND a.BEGIN_INTERVAL_TIME < next_day(ADD_MONTHS(sysdate, 0), 'Monday')  
       AND UPPER (SUBSTR (TO_CHAR (a.begin_interval_time, 'Day'), 1, 3)) IN ('MON', 'TUE', 'WED', 'THU', 'FRI') 
) where RN=1 order by StartTime) 
select to_date(a.StartTime,'mmdd') as StartTime,b.StartTime as lastStartTime,a.LogicIO as CurrentMonth,b.LogicIO as LastMonth, trunc((a.LogicIO-b.LogicIO)/b.LogicIO,7) as "Increment" 
from curMonth a ,lastMonth b 
where a.dayname=b.dayname 
and a.weeklyNum=b.weeklyNum 
order by 1

#List DB block changes
With LasMonth as  
( 
select starttime,to_number(weeklyNum)+4 as weeklyNum,day,dayname,ITL_WAITS,ROW_LOCK_WAITS,DB_BLOCK_CHANGES,snap_id 
from( 
SELECT a.obj#, 
       TO_CHAR (b.begin_interval_time, 'yyyymmdd') AS starttime, 
       TO_CHAR (b.begin_interval_time, 'dd') AS day, 
       TO_CHAR (b.begin_interval_time, 'iw') AS weeklyNum,
       UPPER (SUBSTR (TO_CHAR (b.begin_interval_time, 'Day'), 1,3)) as dayName,
       TRUNC (SUM (A.ITL_WAITS_DELTA) OVER (PARTITION BY a.snap_id),2) AS ITL_WAITS,
       TRUNC (SUM (A.ROW_LOCK_WAITS_DELTA) OVER (PARTITION BY  a.snap_id),2) AS ROW_LOCK_WAITS,
       TRUNC (SUM (A.DB_BLOCK_CHANGES_DELTA)OVER (PARTITION BY  a.snap_id),2) AS DB_BLOCK_CHANGES,
       ROW_NUMBER () OVER (PARTITION BY A.snap_id ORDER BY  A.snap_id)AS RN,
       A.snap_id 
  FROM DBA_HIST_SEG_STAT a, DBA_HIST_SNAPSHOT b 
 WHERE     a.dbid = b.dbid 
       AND a.snap_id = b.snap_id 
       AND a.dbid = ******  
       AND TO_CHAR (b.begin_interval_time, 'hh24mi') >= '1555' 
       AND TO_CHAR (b.begin_interval_time, 'hh24mi') < '1605'   
       AND b.BEGIN_INTERVAL_TIME >= next_day(ADD_MONTHS(sysdate, -2), 'Monday') 
       AND b.BEGIN_INTERVAL_TIME < next_day(ADD_MONTHS(sysdate, -1), 'Monday')  
       AND UPPER (SUBSTR (TO_CHAR (b.begin_interval_time, 'Day'), 1, 3)) IN  
              ('MON', 'TUE', 'WED', 'THU', 'FRI') 
) where rn=1 
), 
currentMonth as  
( 
select starttime,day,weeklyNum,dayname,ITL_WAITS,ROW_LOCK_WAITS,DB_BLOCK_CHANGES,snap_id 
from( 
SELECT a.obj#, 
       TO_CHAR (b.begin_interval_time, 'yyyymmdd') AS starttime, 
       TO_CHAR (b.begin_interval_time, 'dd') AS day,  
        TO_CHAR (b.begin_interval_time, 'iw') AS weeklyNum, 
       UPPER (SUBSTR (TO_CHAR (b.begin_interval_time, 'Day'), 1, 3)) as dayName, 
       TRUNC (SUM (A.ITL_WAITS_DELTA) OVER (PARTITION BY a.snap_id),2) AS ITL_WAITS, 
       TRUNC (SUM (A.ROW_LOCK_WAITS_DELTA) OVER (PARTITION BY  a.snap_id),2) AS ROW_LOCK_WAITS, 
       TRUNC (SUM (A.DB_BLOCK_CHANGES_DELTA)OVER (PARTITION BY  a.snap_id),2) AS DB_BLOCK_CHANGES,
       ROW_NUMBER () OVER (PARTITION BY A.snap_id ORDER BY  A.snap_id)AS RN,
       A.snap_id 
  FROM DBA_HIST_SEG_STAT a, DBA_HIST_SNAPSHOT b 
 WHERE     a.dbid = b.dbid 
       AND a.snap_id = b.snap_id 
       AND a.dbid = ****** 
       AND TO_CHAR (b.begin_interval_time, 'hh24mi') >= '1555' 
       AND TO_CHAR (b.begin_interval_time, 'hh24mi') <'1605'   
       AND b.BEGIN_INTERVAL_TIME >= next_day(ADD_MONTHS(sysdate, -1), 'Monday') 
       AND b.BEGIN_INTERVAL_TIME < next_day(ADD_MONTHS(sysdate, 0), 'Monday') 
       AND UPPER (SUBSTR (TO_CHAR (b.begin_interval_time, 'Day'), 1, 3)) IN 
              ('MON', 'TUE', 'WED', 'THU', 'FRI') 
) where rn=1 
) 
select to_date(A.starttime,'yyyymmdd') as starttime ,b.starttime as LastStartTime, 
 b.DB_BLOCK_CHANGES as lasMonth,a.DB_BLOCK_CHANGES as currentMonth, 
 trunc((a.DB_BLOCK_CHANGES-b.DB_BLOCK_CHANGES)/b.DB_BLOCK_CHANGES,7) as "Increment" 
 from currentMonth a,LasMonth b  
 where a.weeklyNum=b.weeklyNum and a.dayname=b.dayname 
Order by 1

#For Row lock Waits
With LasMonth as  
( 
select starttime,to_number(weeklyNum)+4 as weeklyNum,day,dayname,ITL_WAITS,ROW_LOCK_WAITS,DB_BLOCK_CHANGES,snap_id 
from( 
SELECT a.obj#, 
       TO_CHAR (b.begin_interval_time, 'yyyymmdd') AS starttime, 
       TO_CHAR (b.begin_interval_time, 'dd') AS day, 
       TO_CHAR (b.begin_interval_time, 'iw') AS weeklyNum,
       UPPER (SUBSTR (TO_CHAR (b.begin_interval_time, 'Day'), 1,3)) as dayName,
       TRUNC (SUM (A.ITL_WAITS_DELTA) OVER (PARTITION BY a.snap_id),2) AS ITL_WAITS,
       TRUNC (SUM (A.ROW_LOCK_WAITS_DELTA) OVER (PARTITION BY  a.snap_id),2) AS ROW_LOCK_WAITS,
       TRUNC (SUM (A.DB_BLOCK_CHANGES_DELTA)OVER (PARTITION BY  a.snap_id),2) AS DB_BLOCK_CHANGES,
       ROW_NUMBER () OVER (PARTITION BY A.snap_id ORDER BY  A.snap_id)AS RN,
       A.snap_id 
  FROM DBA_HIST_SEG_STAT a, DBA_HIST_SNAPSHOT b 
 WHERE     a.dbid = b.dbid 
       AND a.snap_id = b.snap_id 
       AND a.dbid =******  
       AND TO_CHAR (b.begin_interval_time, 'hh24mi') >= '1555' 
       AND TO_CHAR (b.begin_interval_time, 'hh24mi') < '1605'   
       AND b.BEGIN_INTERVAL_TIME >= next_day(ADD_MONTHS(sysdate, -2), 'Monday') 
       AND b.BEGIN_INTERVAL_TIME < next_day(ADD_MONTHS(sysdate, -1), 'Monday')  
       AND UPPER (SUBSTR (TO_CHAR (b.begin_interval_time, 'Day'), 1, 3)) IN  
              ('MON', 'TUE', 'WED', 'THU', 'FRI') 
) where rn=1 
), 
currentMonth as  
( 
select starttime,day,weeklyNum,dayname,ITL_WAITS,ROW_LOCK_WAITS,DB_BLOCK_CHANGES,snap_id 
from( 
SELECT a.obj#, 
       TO_CHAR (b.begin_interval_time, 'yyyymmdd') AS starttime, 
       TO_CHAR (b.begin_interval_time, 'dd') AS day,  
        TO_CHAR (b.begin_interval_time, 'iw') AS weeklyNum, 
       UPPER (SUBSTR (TO_CHAR (b.begin_interval_time, 'Day'), 1, 3)) as dayName, 
       TRUNC (SUM (A.ITL_WAITS_DELTA) OVER (PARTITION BY a.snap_id),2) AS ITL_WAITS, 
       TRUNC (SUM (A.ROW_LOCK_WAITS_DELTA) OVER (PARTITION BY  a.snap_id),2) AS ROW_LOCK_WAITS, 
       TRUNC (SUM (A.DB_BLOCK_CHANGES_DELTA)OVER (PARTITION BY  a.snap_id),2) AS DB_BLOCK_CHANGES,
       ROW_NUMBER () OVER (PARTITION BY A.snap_id ORDER BY  A.snap_id)AS RN,
       A.snap_id 
  FROM DBA_HIST_SEG_STAT a, DBA_HIST_SNAPSHOT b 
 WHERE     a.dbid = b.dbid 
       AND a.snap_id = b.snap_id 
       AND a.dbid =****** 
       AND TO_CHAR (b.begin_interval_time, 'hh24mi') >= '1555' 
       AND TO_CHAR (b.begin_interval_time, 'hh24mi') <'1605'   
       AND b.BEGIN_INTERVAL_TIME >= next_day(ADD_MONTHS(sysdate, -1), 'Monday') 
       AND b.BEGIN_INTERVAL_TIME < next_day(ADD_MONTHS(sysdate, 0), 'Monday') 
       AND UPPER (SUBSTR (TO_CHAR (b.begin_interval_time, 'Day'), 1, 3)) IN 
              ('MON', 'TUE', 'WED', 'THU', 'FRI') 
) where rn=1 
) 
select to_date(A.starttime,'yyyymmdd') as starttime ,b.starttime as LastStartTime, 
 b.ROW_LOCK_WAITS as lasMonth,a.ROW_LOCK_WAITS as currentMonth, 
 trunc((a.ROW_LOCK_WAITS-b.ROW_LOCK_WAITS)/b.ROW_LOCK_WAITS,7) as "Increment" 
 from currentMonth a,LasMonth b  
 where a.weeklyNum=b.weeklyNum and a.dayname=b.dayname 
Order by 1

#For OS CPU Usage, must have two snap shot
WEBCHART.QUERY_5=with lastMonth as 
(
select dayName,weeklyNum,StartTime, 
round(((UserTimeDiff+SysTimeDiff)/(UserTimeDiff+IdleTimeDiff+SysTimeDiff+IOWaitTimeDiff+NiceTimeDiff))*100) BusyTimePct, 
round((UserTimeDiff/(UserTimeDiff+IdleTimeDiff+SysTimeDiff+IOWaitTimeDiff+NiceTimeDiff))*100) UserTimePct,    
round((NiceTimeDiff/(UserTimeDiff+IdleTimeDiff+SysTimeDiff+IOWaitTimeDiff+NiceTimeDiff))*100)  NiceTimePct,   
round((SysTimeDiff/(UserTimeDiff+IdleTimeDiff+SysTimeDiff+IOWaitTimeDiff+NiceTimeDiff))*100)     SystemTimePct,
round((IOWaitTimeDiff/(UserTimeDiff+IdleTimeDiff+SysTimeDiff+IOWaitTimeDiff+NiceTimeDiff))*100)  IOWaitTimePct, 
VmInDiff as VmIn,
VmOutDiff as VmOut 
From( 
select StartTime,dayName,weeklyNum,                                                                                       
      (UserTime-UserTimeBefore)       as UserTimeDiff,                                                  
      (IdleTime-IdleTimeBefore)       as IdleTimeDiff,                                                  
      (SysTime-SysTimeBefore)         as SysTimeDiff,                                                   
      (IOWaitTime-IOWaitTimeBefore)   as IOWaitTimeDiff,                                                
      (NiceTime-NiceTimeBefore)       as NiceTimeDiff,                                                  
      (VmIn-VmInBefore)               as VmInDiff,                                                      
      (VmOut-VmOutBefore)             as VmOutDiff                                                      
From(                                                                                                   
select StartTime,dayName,weeklyNum,snap_id,                                                                               
      UserTime,lag(UserTime) over(partition by StartTime order by StartTime,snap_id) as UserTimeBefore, 
      IdleTime,lag(IdleTime) over(partition by StartTime order by StartTime,snap_id) as IdleTimeBefore, 
      SysTime,lag(SysTime) over(partition by StartTime order by StartTime,snap_id)   as SysTimeBefore,   
      IOWaitTime,lag(IOWaitTime) over(partition by StartTime order by StartTime,snap_id) as IOWaitTimeBefore, 
      NiceTime,lag(NiceTime) over(partition by StartTime order by StartTime,snap_id) as NiceTimeBefore, 
      VmIn,lag(VmIn) over(partition by StartTime order by StartTime,snap_id) as VmInBefore,   
      VmOut,lag(VmOut) over(partition by StartTime order by StartTime,snap_id) as VmOutBefore 
from(   
SELECT  TO_CHAR (sn.begin_interval_time, 'mmdd') AS StartTime,    
                       TRUNC (SUM (DECODE (oss.stat_name, 'USER_TIME', VALUE, 0)/100) OVER (PARTITION BY oss.snap_id), 2) as UserTime,
                       TRUNC (SUM (DECODE (oss.stat_name, 'BUSY_TIME', VALUE, 0)/100) OVER (PARTITION BY oss.snap_id), 2) as BusyTime, 
                       TRUNC (SUM (DECODE (oss.stat_name, 'IDLE_TIME', VALUE, 0)/100) OVER (PARTITION BY oss.snap_id), 2) as IdleTime, 
                       TRUNC (SUM (DECODE (oss.stat_name, 'SYS_TIME', VALUE, 0)/100)  OVER (PARTITION BY oss.snap_id), 2) as SysTime, 
                       TRUNC (SUM (DECODE (oss.stat_name, 'NICE_TIME', VALUE, 0)/100)  OVER (PARTITION BY oss.snap_id), 2) as NiceTime,
                       TRUNC (SUM (DECODE (oss.stat_name, 'IOWAIT_TIME', VALUE, 0)/100) OVER (PARTITION BY oss.snap_id), 2) as IOWaitTime, 
                       TRUNC (SUM (DECODE (oss.stat_name, 'VM_IN_BYTES', VALUE, 0)/100) OVER (PARTITION BY oss.snap_id), 2) as VmIn,   
                       TRUNC (SUM (DECODE (oss.stat_name, 'VM_OUT_BYTES', VALUE, 0)/100) OVER (PARTITION BY oss.snap_id), 2) as VmOut, 
                       UPPER (SUBSTR (TO_CHAR (sn.begin_interval_time, 'Day'), 1, 3)) as dayName, 
                       to_number(TO_CHAR (sn.begin_interval_time, 'iw'))+4 AS weeklyNum, 
                                            sn.snap_id, 
                        ROW_NUMBER () OVER (PARTITION BY sn.snap_id order by sn.snap_id) AS RN 
FROM dba_hist_osstat oss, dba_hist_snapshot sn                           
WHERE            oss.dbid = ******                                   
                 AND   oss.dbid = sn.dbid                                
                 AND   oss.instance_number =  sn.instance_number         
                 AND   oss.snap_id = sn.snap_id                          
                 AND TO_CHAR (sn.begin_interval_time, 'hh24mi') >= '1555' 
                 AND TO_CHAR (sn.begin_interval_time, 'hh24mi') <= '1615'  
                 AND sn.BEGIN_INTERVAL_TIME >= next_day(ADD_MONTHS(sysdate, -2), 'Monday') 
                 AND sn.BEGIN_INTERVAL_TIME < next_day(ADD_MONTHS(sysdate, -1), 'Monday') 
                 AND UPPER (SUBSTR (TO_CHAR (sn.begin_interval_time, 'Day'), 1, 3)) IN ('MON', 'TUE', 'WED', 'THU', 'FRI') 
                 AND   oss.stat_name IN ('USER_TIME','BUSY_TIME','IDLE_TIME','SYS_TIME','IOWAIT_TIME','NICE_TIME','VM_IN_BYTES','VM_OUT_BYTES')
                 order by 1            
) where RN=1                           
) where UserTimeBefore is not null     
       or IdleTimeBefore is not null   
       or SysTimeBefore is not null    
       or SysTimeBefore is not null    
       or IOWaitTimeBefore is not null 
       or VmInBefore is not null  
       or VmOutBefore is not null  
       or NiceTimeBefore is not null 
) order by StartTime), 
curMonth as  
(
select weeklyNum,dayname,StartTime, 
round(((UserTimeDiff+SysTimeDiff)/(UserTimeDiff+IdleTimeDiff+SysTimeDiff+IOWaitTimeDiff+NiceTimeDiff))*100) BusyTimePct, 
round((UserTimeDiff/(UserTimeDiff+IdleTimeDiff+SysTimeDiff+IOWaitTimeDiff+NiceTimeDiff))*100) UserTimePct, 
round((NiceTimeDiff/(UserTimeDiff+IdleTimeDiff+SysTimeDiff+IOWaitTimeDiff+NiceTimeDiff))*100)  NiceTimePct, 
round((SysTimeDiff/(UserTimeDiff+IdleTimeDiff+SysTimeDiff+IOWaitTimeDiff+NiceTimeDiff))*100)     SystemTimePct,
round((IOWaitTimeDiff/(UserTimeDiff+IdleTimeDiff+SysTimeDiff+IOWaitTimeDiff+NiceTimeDiff))*100)  IOWaitTimePct,
VmInDiff as VmIn, 
VmOutDiff as VmOut 
From( 
select StartTime,weeklyNum,dayname,  
      (UserTime-UserTimeBefore)       as UserTimeDiff,
      (IdleTime-IdleTimeBefore)       as IdleTimeDiff,
      (SysTime-SysTimeBefore)         as SysTimeDiff,
      (IOWaitTime-IOWaitTimeBefore)   as IOWaitTimeDiff,
      (NiceTime-NiceTimeBefore)       as NiceTimeDiff, 
      (VmIn-VmInBefore)               as VmInDiff, 
      (VmOut-VmOutBefore)             as VmOutDiff 
From( 
select StartTime,weeklyNum,dayname,snap_id, 
      UserTime,lag(UserTime) over(partition by StartTime order by StartTime,snap_id) as UserTimeBefore,
      IdleTime,lag(IdleTime) over(partition by StartTime order by StartTime,snap_id) as IdleTimeBefore,
      SysTime,lag(SysTime) over(partition by StartTime order by StartTime,snap_id)   as SysTimeBefore, 
      IOWaitTime,lag(IOWaitTime) over(partition by StartTime order by StartTime,snap_id) as IOWaitTimeBefore,
      NiceTime,lag(NiceTime) over(partition by StartTime order by StartTime,snap_id) as NiceTimeBefore, 
      VmIn,lag(VmIn) over(partition by StartTime order by StartTime,snap_id) as VmInBefore,    
      VmOut,lag(VmOut) over(partition by StartTime order by StartTime,snap_id) as VmOutBefore   
from(  
SELECT  TO_CHAR (sn.begin_interval_time, 'mmdd') AS StartTime, 
                       TRUNC (SUM (DECODE (oss.stat_name, 'USER_TIME', VALUE, 0)/100) OVER (PARTITION BY oss.snap_id), 2) as UserTime,
                       TRUNC (SUM (DECODE (oss.stat_name, 'BUSY_TIME', VALUE, 0)/100) OVER (PARTITION BY oss.snap_id), 2) as BusyTime, 
                       TRUNC (SUM (DECODE (oss.stat_name, 'IDLE_TIME', VALUE, 0)/100) OVER (PARTITION BY oss.snap_id), 2) as IdleTime, 
                       TRUNC (SUM (DECODE (oss.stat_name, 'SYS_TIME', VALUE, 0)/100)  OVER (PARTITION BY oss.snap_id), 2) as SysTime, 
                       TRUNC (SUM (DECODE (oss.stat_name, 'NICE_TIME', VALUE, 0)/100)  OVER (PARTITION BY oss.snap_id), 2) as NiceTime, 
                       TRUNC (SUM (DECODE (oss.stat_name, 'IOWAIT_TIME', VALUE, 0)/100) OVER (PARTITION BY oss.snap_id), 2) as IOWaitTime, 
                       TRUNC (SUM (DECODE (oss.stat_name, 'VM_IN_BYTES', VALUE, 0)/100) OVER (PARTITION BY oss.snap_id), 2) as VmIn, 
                       TRUNC (SUM (DECODE (oss.stat_name, 'VM_OUT_BYTES', VALUE, 0)/100) OVER (PARTITION BY oss.snap_id), 2) as VmOut,
                       UPPER (SUBSTR (TO_CHAR (sn.begin_interval_time, 'Day'), 1, 3)) as dayName, 
                       to_number(TO_CHAR (sn.begin_interval_time, 'iw')) AS weeklyNum, 
                                           sn.snap_id, 
                       ROW_NUMBER () OVER (PARTITION BY sn.snap_id order by sn.snap_id) AS RN 
FROM dba_hist_osstat oss, dba_hist_snapshot sn 
WHERE            oss.dbid = ****** 
                 AND   oss.dbid = sn.dbid 
                 AND   oss.instance_number =  sn.instance_number 
                 AND   oss.snap_id = sn.snap_id 
                 AND TO_CHAR (sn.begin_interval_time, 'hh24mi') >= '1555' 
                 AND TO_CHAR (sn.begin_interval_time, 'hh24mi') <= '1615' 
                 AND sn.BEGIN_INTERVAL_TIME >= next_day(ADD_MONTHS(sysdate, -1), 'Monday') 
                 AND sn.BEGIN_INTERVAL_TIME < next_day(ADD_MONTHS(sysdate, 0), 'Monday') 
                                 AND UPPER (SUBSTR (TO_CHAR (sn.begin_interval_time, 'Day'), 1, 3)) IN ('MON', 'TUE', 'WED', 'THU', 'FRI') 
                 AND   oss.stat_name IN ('USER_TIME','BUSY_TIME','IDLE_TIME','SYS_TIME','IOWAIT_TIME','NICE_TIME','VM_IN_BYTES','VM_OUT_BYTES')
                 order by 1 
) where RN=1  
) where UserTimeBefore is not null 
       or IdleTimeBefore is not null  
       or SysTimeBefore is not null  
       or SysTimeBefore is not null  
       or IOWaitTimeBefore is not null 
       or VmInBefore is not null  
       or VmOutBefore is not null 
       or NiceTimeBefore is not null 
) order by StartTime) 
select to_date(a.starttime,'mmdd') as starttime, b.BusyTimePct as LastMonth,a.BusyTimePct as CurrentMonth,trunc((a.BusyTimePct-b.BusyTimePct)/b.BusyTimePct,7) as "Increment" 
from curMonth a,lastMonth b 
where a.weeklyNum=b.weeklyNum 
and   a.dayname=b.dayname 
order by 1
Posted by at No comments:
Labels:

Tuesday, August 16, 2016

How to Extract long data to Clob?

until to oracle 11G, oracle dictionary still includes some long data type, I try to use to_lob/Dbms_Lob.substr, it always raise oracle error "ORA-00932: inconsistent datatypes: expected - got LONG", so search some documents and give below function to extract the long data to clob.

select to_char(LONG2CLOB('COLUMN_EXPRESSION','ALL_IND_EXPRESSIONS','table_name=upper(''***'')')) as
from dual;


CREATE OR REPLACE FUNCTION LONG2CLOB (
   colName   IN VARCHAR2,
   tabName   IN VARCHAR2,
   whereCond IN VARCHAR2 := NULL)
RETURN CLOB
IS
   v_cur            PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;
   v_rows           PLS_INTEGER;
   v_buflen         PLS_INTEGER := 32760;
   v_curpos         NUMBER := 0;
   v_long_len       NUMBER;
   v_long_val       CLOB;
   v_return_val     CLOB;
  BEGIN
   /* dynamic to execute the SQL */
   DBMS_SQL.PARSE (
      v_cur,
      'SELECT ' || colName ||
      '  FROM ' || tabName ||
      ' WHERE ' || NVL (whereCond, '1 = 1'),
      DBMS_SQL.NATIVE);

   /* Define the long column and then execute and fetch... */
   DBMS_SQL.DEFINE_COLUMN_LONG (v_cur, 1);
   v_rows := DBMS_SQL.EXECUTE (v_cur);
   v_rows := DBMS_SQL.FETCH_ROWS (v_cur);

   /* If a row was fetched, loop through the long value until
   || all pieces are retrieved.
   */
   IF v_rows> 0
   THEN
      LOOP
         DBMS_SQL.column_value_long (v_cur,
                                     1,
                                     v_buflen,
                                     v_curpos,
                                     v_long_val,
                                     v_long_len);
       
       
         EXIT WHEN v_long_len = 0;
         v_return_val := v_return_val || v_long_val;
         v_curpos := v_curpos + v_long_len;
      END LOOP;
   END IF;
   DBMS_SQL.CLOSE_CURSOR (v_cur);
   RETURN v_return_val;
EXCEPTION
   WHEN OTHERS
   THEN
      IF DBMS_SQL.is_open (v_cur)
      THEN
         DBMS_SQL.close_cursor (v_cur);
      END IF;
      RAISE;
END LONG2CLOB;
/
Posted by at No comments:
Labels:

Monday, August 15, 2016

Redis Vs. Memcached



1.Comparison
Name
Memcached  
Redis  
Description
In-memory key-value store, originally intended for caching
In-memory data structure store, used as database, cache and message broker 
Ranking
Rank 22 
Score 32.42 
Rank 10 
Score 100.65 
Database Model
Website
Technical Documentation
Developer
Danga Interactive 
Salvatore Sanfilippo 
Initial release
2003
2009
Current release
1.4.24, April 2015
3.0.3, June 2015
License
Open Source 
Open Source 
Database as a Service (DBaaS) 
No
No
Implementation language
C
C
Server operating systems
FreeBSD
Linux
OS X
Unix
Windows
BSD
Linux
OS X
Windows 
Data Scheme
Schema-free
Schema-free
Data Types
1)Limits key names to 250 bytes, limits values to 1MB 
2)Works only with plain strings 
1)Key names and values to be as large as 512MB each,they are binary safe. 
2)Has six data types that enable more intelligent caching and manipulation of cached data.   
XML support 
No
No
Secondary indexes
No
No
SQL
No
No
APIs and other access methods
Proprietary protocol
Proprietary protocol 
Supported programming languages
.Net
C
C++
ColdFusion
Erlang
Java
Lisp
Lua
OCaml
Perl
PHP
Python
Ruby
C
C#
C++
Clojure
Crystal
D
Dart
Elixir
Erlang
Fancy
Go
Haskell
Haxe
Java
JavaScript (Node.js)
Lisp
Lua
MatLab
Objective-C
OCaml 
Perl
PHP
Prolog
Pure Data
Python
R
Rebol
Ruby
Rust
Scala
Scheme
Smalltalk
Tcl
Server-side scripts and Commands 
No
Lua Script and 160-Plus Commands
Triggers
No
No
Partitioning Methods 
Client/Proxy Sharding
Client/Proxy Sharding and Redis Cluster 
Replication Methods 
No
Master-Slave replication 
MapReduce
No
No
Consistency concepts 
No
Eventual Consistency
Foreign keys 
No
No
Transaction concepts 
No
Optimistic locking, atomic execution of commands blocks and scripts
Concurrency 
Yes
Yes 
Persistence
No
Yes, Can Disable
Eviction policies 
1)noeviction 
2)allkeys-lru: 
3)volatile-lru 
4)allkeys-random 
5)volatile-random 
6)volatile-ttl 
http://redis.io/topics/lru-cache 
Timeout keys 
1)Explicit expiration times. 
2)Delete cached objects when we know we're doing something  
that will cause them to be invalid but are not in a position to update them while we're at it. 
3)Include timestamps on cached objects and do our own expiries based on dependencies. 
[https://www.mediawiki.org/wiki/Memcached 
]
1)EXPIRE 
2)EXPIREAT 
3)TTL 
4)PERSIST 
5)SETEX 
In-memory capabilities 
Yes
Yes
Performance
good
Very good
Security
http://dustin.sallings.org/2010/08/08/memcached-security.html\\http://redis.io/topics/security\\
2.Reference
Posted by at 1 comment:
Labels:

Sunday, August 7, 2016

Upgrade Common DBCP JDBC Pool to Tomcat JDBC Pool Comments

Today Tomcat provided one better JDBC Pool,so most of application want to upgrade your-self program to use Tomcat JDBC Pool, today I'll introduce some differences between Tomcat JDBC Pool and Common DBCP JDBC Pool.

firstly, I'd like introduce some key new features at Tomcat JDBC Pool.

  • validationInterval - in addition to running validations on connections, avoid running them too frequently, it very useful to downgrade the validation frequently on database.
  • maxAge,(long) Time in milliseconds to keep this connection. When a connection is returned to the pool, the pool will check to see if the now - time-when-connected > maxAge has been reached, and if so, it closes the connection rather than returning it to the pool. 
  • jdbcInterceptors,flexible and pluggable interceptors to create any customizations around the pool, the query execution and the result set handling.
  • fairQueue,Set the fair flag to true to achieve thread fairness or to use asynchronous connection retrieval.
secondly,I'd like introduce some key difference parameters name for each other.





the end, I'd like give you one standard configuration for Tomcat JDBC Pool.

<Resource name="jdbc/my_testing_DB"
          auth="Container"
          type="javax.sql.DataSource"
          factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
          testOnBorrow="true"
          testOnReturn="false"
          validationQuery="SELECT 1 from dual"
          validationInterval="1000"
          timeBetweenEvictionRunsMillis="300000"
          maxActive="50"
          minIdle="10"
          maxWait="10000"
          initialSize="10"
          username="bossrpt"
          password="pass"
          driverClassName="com.inet.ora.OraDriver"
          url="jdbc:inetora:***?service=***;failover=true;host1=***;queryTimeout=600"
/>

Reference:
https://commons.apache.org/proper/commons-dbcp/configuration.html
https://tomcat.apache.org/tomcat-8.0-doc/jndi-datasource-examples-howto.html

Posted by at No comments:
Labels:

What happened on "java.net.SocketException: Socket closed"?

Our Application have error when connect the database by Tomcat JBDC Pool, the error description like this "com.inet.ora.Ora4SQLException: [OraDriver] #69 java.net.SocketException: Socket closed", we don't kill the connection from database side, why the connection closed?

firstly, I check firewall and Linux Socket setup whether have error,common oracle SQLPLUS can work on this, so I think Linux and firewall setup is right.

secondly, I check the Java JDBC setup whether have error,so I write one java code with JDBC setup to check whether can reproduce the error, I got the same error.

thirdly, I check the JDBC configuration, I found the root cause, because we setup the query time-out time is 600(10 minutes) but we still setup the removeAbandoned="true" and removeAbandonedTimeout="60",setup wrong on this, why?

Let's introduce the detail reason to you.
removeAbandoned is (boolean) Flag to remove abandoned connections if they exceed the removeAbandonedTimeout. If set to true a connection is considered abandoned and eligible for removal if it has been in use longer than the removeAbandonedTimeout Setting this to true can recover db connections from applications that fail to close a connection. See also logAbandoned The default value is false.

removeAbandonedTimeoutremoveAbandonedTimeout is (int) Timeout in seconds before an abandoned(in use) connection can be removed. The default value is 60 (60 seconds). The value should be set to the longest running query your applications might have.

logAbandoned is logAbandoned(boolean) Flag to log stack traces for application code which abandoned a Connection. Logging of abandoned Connections adds overhead for every Connection borrow because a stack trace has to be generated. The default value is false.

because Tomcat JDBC pool will remove abandoned connections if they exceed the removeAbandonedTimeout, we setup the query time-out is 10 minutes and removeAbandonedTimeout=60(seconds), so Tomcat JDBC Pool remove abandoned connection by 60 seconds, but this connection still query the data, because our report database have many data on this, it always more than 60 seconds to get the result. 


Java testing codes for Tomcat JDBC Pool:

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import org.apache.tomcat.jdbc.pool.DataSourceFactory;
import org.apache.tomcat.jdbc.pool.DataSource;
import org.apache.tomcat.jdbc.pool.PoolProperties;

public class TestJDBC {
private static String driver = "com.inet.ora.OraDriver";
private static String url = "jdbc:inetora:***?service=***&failover=true&host1=**&port1=1521&loginTimeout=10&queryTimeout=600";
private static String userName = "***";
private static String password = "pass";

private static DataSource ds = null;

public static void main(String[] args) throws Exception {
initDataSource();

String sql = "INSERT INTO ......";

Connection con = getConnection();
PreparedStatement pstmt = con.prepareStatement(sql);

long beginM = System.currentTimeMillis();
System.out.println("Begin:" + beginM);

try {

pstmt.execute();

} catch (Exception e) {
e.printStackTrace();
} finally {
closeCon(null, pstmt, con);
}

long endM = System.currentTimeMillis();
System.out.println("End:" + endM);
System.out.println("Total:" + (endM - beginM));
}


public static void initDataSource() throws Exception {
if (ds == null) {
System.out.println("???????:[driver:" + driver + ",url:" + url
+ ",userName:" + userName + ",password:" + password + "]");

PoolProperties p = new PoolProperties();
p.setDriverClassName(driver);
p.setUrl(url);
p.setUsername(userName);
p.setPassword(password);
p.setMaxActive(50);
p.setInitialSize(10);
p.setMaxIdle(10);
p.setMaxWait(10000);
p.setConnectionProperties("streamstolob=true");
p.setTestOnBorrow(true);
p.setTimeBetweenEvictionRunsMillis(300000);
p.setRemoveAbandonedTimeout(60);
p.setRemoveAbandoned(true);
p.setValidationQuery("SELECT instance_name FROM v$instance");
p.setLogAbandoned(true);
p.setValidationInterval(1000);

ds = new DataSource();
ds.setPoolProperties(p);
}

}

public static void shutDownDataSource() throws Exception {
if (ds != null) {
ds.close();
}
}

public static Connection getConnection() {
Connection con = null;
try {
con = ds.getConnection();
} catch (Exception e) {
e.printStackTrace();
}
return con;
}

public static void closeCon(ResultSet rs, PreparedStatement ps,
Connection con) {
if (rs != null) {
try {
rs.close();
} catch (Exception e) {
e.printStackTrace();
}
}
if (ps != null) {
try {
ps.close();
} catch (Exception e) {
e.printStackTrace();
}
}
if (con != null) {
try {
con.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
Posted by at No comments:
Labels:
Subscribe to: Posts (Atom)