Archive for July, 2010

July 26th 2010

Script Shared Pool Healthy Check

Script untuk melakukan check terhadap shared pool, semoga bermanfaat.

1). Library Cache

1
2
3
4
5
6
7
8
COLUMN gethitratio format 999.99 heading "GHR"
COLUMN pinhitratio format 999.99 heading "PHR"
COLUMN namespace format a10 
 
SELECT  namespace, gets, gethits, gethitratio, pins, pinhits,
pinhitratio, reloads, invalidations
FROM    v$librarycache
/

2). Free Memory

1
2
3
SELECT  *
FROM    v$sgastat
WHERE   name LIKE '%free%'

3). v$sql area statistics

1
2
3
4
SELECT  MAX(version_count), MAX(invalidations), MAX(loaded_versions),
MAX(sharable_mem)
FROM    v$sqlarea
/

4). Rate of flushing

1
2
3
SELECT  ksmlrcom, ksmlrsiz, ksmlrnum, ksmlrhon, ksmlrohv, ksmlrses
FROM    x$ksmlru WHERE ksmlrnum > 0
/

5). Session waiting for non idle events

1
2
3
4
5
6
7
8
9
10
COLUMN p1 format 999999999999
COLUMN p2 format 999999999999
COLUMN event format a35
SELECT  sid,event,p1,p2,p3,seq#
FROM    v$session_wait
WHERE   wait_time = 0 AND event NOT IN
('smon timer', 'pmon timer', 'rdbms ipc reply', 'rdbms ipc message',
'SQL*Net message from client')
ORDER BY sid
/

6). Memory consumed by objects

1
2
3
SELECT  MAX(sharable_mem), MAX(loads)
FROM    v$db_object_cache
/

7). Least Recently Used (LRU) statistics

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
COLUMN kghlurcr heading "RECURRENT|CHUNKS"
COLUMN kghlutrn heading "TRANSIENT|CHUNKS"
COLUMN kghlufsh heading "FLUSHED|CHUNKS"
COLUMN kghluops heading "PINS AND|RELEASES"
COLUMN kghlunfu heading "ORA-4031|ERRORS"
COLUMN kghlunfs heading "LAST ERROR|SIZE"
SELECT  kghlurcr,
kghlutrn,
kghlufsh,
kghluops,
kghlunfu,
kghlunfs
FROM    sys.x$kghlu
WHERE   inst_id = userenv('Instance')
/
SELECT  DECODE (SIGN (ksmchsiz - 812),
-1, (ksmchsiz - 16) / 4,
DECODE (SIGN (ksmchsiz - 4012),
-1, TRUNC ((ksmchsiz + 11924) / 64),
DECODE (SIGN (ksmchsiz - 65548),
-1, TRUNC (1 / LOG (ksmchsiz - 11, 2)) + 238,
254
)
)
) bucket,
SUM (ksmchsiz) free_space, COUNT (*) free_chunks,
TRUNC (AVG (ksmchsiz)) average_size, MAX (ksmchsiz) biggest
FROM    SYS.x$ksmsp
WHERE   inst_id = USERENV ('Instance') AND ksmchcls = 'free'
GROUP   BY DECODE (SIGN (ksmchsiz - 812),
-1, (ksmchsiz - 16) / 4,
DECODE (SIGN (ksmchsiz - 4012),
-1, TRUNC ((ksmchsiz + 11924) / 64),
DECODE (SIGN (ksmchsiz - 65548),
-1, TRUNC (1 / LOG (ksmchsiz - 11, 2)) + 238,
254
)
)
)
/

8). Total open cursor

1
2
3
4
col "Total Open Cursors" FOR 999,999,999,999
SELECT  COUNT(*) "Total Open Cursors"
FROM    v$open_cursor
/

9). Permanent memory allocations SGA Heap - x$ksmsp

1
2
3
4
5
6
7
8
SELECT  ksmchsiz        "Chunk Size",
COUNT(ksmchsiz) "Chunks",
SUM(ksmchsiz)   "Total"
FROM    x$ksmsp
WHERE   ksmchcls LIKE '%perm'
GROUP BY ksmchsiz
ORDER BY ksmchsiz
/

*reference : Alejandro Vargas (http://blogs.oracle.com/AlejandroVargas)

No Comments yet »

July 22nd 2010

Script tablespace usage

Cek free space :
1
2
3
4
5
6
7
8
9
10
11
12
SELECT   ddf.tablespace_name, ddf.BYTES "Bytes",
ROUND (((ddf.BYTES - dfs.BYTES) / ddf.BYTES) * 100, 2) "Used %",
ROUND ((1 - ((ddf.BYTES - dfs.BYTES) / ddf.BYTES)) * 100,
2) "Free %"
FROM (SELECT   tablespace_name, SUM (BYTES) BYTES
FROM dba_data_files
GROUP BY tablespace_name) ddf,
(SELECT   tablespace_name, SUM (BYTES) BYTES
FROM dba_free_space
GROUP BY tablespace_name) dfs
WHERE ddf.tablespace_name = dfs.tablespace_name
ORDER BY ((ddf.BYTES - dfs.BYTES) / ddf.BYTES) DESC
Cek tablespace growth / hari :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT   TO_CHAR (sp.begin_interval_time, 'DD-MM-YYYY') days, ts.tsname,
MAX (ROUND ((tsu.tablespace_size * dt.block_size) / (1024 * 1024), 2)
) cur_size_mb,
MAX (ROUND (  (tsu.tablespace_usedsize * dt.block_size)
/ (1024 * 1024),
2
)
) usedsize_mb
FROM dba_hist_tbspc_space_usage tsu,
dba_hist_tablespace_stat ts,
dba_hist_snapshot sp,
dba_tablespaces dt
WHERE tsu.tablespace_id = ts.ts#
AND tsu.snap_id = sp.snap_id
AND ts.tsname = dt.tablespace_name
AND ts.tsname NOT IN ('SYSAUX', 'SYSTEM')
GROUP BY TO_CHAR (sp.begin_interval_time, 'DD-MM-YYYY'), ts.tsname
ORDER BY ts.tsname, days

Incoming search terms:

  • dba_hist_tbspc_space_usage
  • dba_hist_tbspc_space_usage monitoring growth
  • dba_hist_tbspc_space_usage 11g

No Comments yet »

July 21st 2010

Unwrap PL/SQL code

Masih inget akan kenangan masa lalu, waktu ngeliat stored procedure yang scriptnya hanya bertuliskan huruf-huruf aneh, mencoba mencari tahu ternyata itu stored procedure yang sudah di encrypt agar tidak dapat dibaca isinya. Ternyata untuk membuat encrypted pl/sql tersebut menggunakan utilitas fungsi wrap yang ada di oracle. Lalu bagaimana cara melakukan unwrap terhadap store procedure /plsql tersebut ?. Just wanna share, karena dulu nanya bagaimana cara unwrapnya ternyata jawabannya kurang memuaskan alias ngga pernah dikasih tahu sama senior…happy.

Cara pertama :

Memanfaatkan fungsi unwrap yang ada di tersedia di internet, contohnya di alamat ini : http://hz.codecheck.ch/UnwrapIt/

Cara kedua :

Dengan mendownload sebuah utility tools,  script unwrap yang ditulis oleh Niels Teusink menggunakan bahasa python dan bisa didownload di http://www.teusink.net/unwrap.py.  Cara penggunaannya sendiri yaitu :

1
bash$ ./unwrap.py obfuscation_code.txt

Anehnya juga kenapa store procedure make di wrap segala, apa biar ngga bisa dibaca, dicontek orang. Repot juga kalo package yang didalamnya mengatur flow proses dari aplikasi musti di wrap, bingung cara debugnya kalo ada error…happy

Incoming search terms:

  • bagaimana cara membuat pl/sql

No Comments yet »