scripts

Scripts:curheaps.sql

December 17, 2011 Internals, oracle No comments

该script 显示各个cursor占用的heap大小

——————————————————————————–

— File name: curheaps.sql
— Purpose: Show main cursor data block heap sizes and their contents
— (heap0 and heap6)

— Author: Tanel Poder
— Copyright: (c) http://www.tanelpoder.com

— Usage: @curheaps

— @curheaps 942515969 % — shows a summary of cursor heaps
— @curheaps 942515969 0 — shows detail for child cursor 0

— Other: “Child” cursor# 65535 is actually the parent cursor

——————————————————————————–

col curheaps_size0 heading SIZE0 for 9999999
col curheaps_size1 heading SIZE1 for 9999999
col curheaps_size2 heading SIZE2 for 9999999
col curheaps_size3 heading SIZE3 for 9999999
col curheaps_size4 heading SIZE4 for 9999999
col curheaps_size5 heading SIZE5 for 9999999
col curheaps_size6 heading SIZE6 for 9999999
col curheaps_size7 heading SIZE7 for 9999999

col KGLOBHD0 new_value v_curheaps_kglobhd0 print
col KGLOBHD1 new_value v_curheaps_kglobhd1 noprint
col KGLOBHD2 new_value v_curheaps_kglobhd2 noprint
col KGLOBHD3 new_value v_curheaps_kglobhd3 noprint
col KGLOBHD4 new_value v_curheaps_kglobhd4 print
col KGLOBHD5 new_value v_curheaps_kglobhd5 noprint
col KGLOBHD6 new_value v_curheaps_kglobhd6 print
col KGLOBHD7 new_value v_curheaps_kglobhd7 noprint

select
KGLNAHSH,
KGLHDPAR,
kglobt09 CHILD#,
KGLHDADR,
KGLOBHD0, KGLOBHS0 curheaps_size0,
KGLOBHD1, KGLOBHS1 curheaps_size1,
KGLOBHD2, KGLOBHS2 curheaps_size2,
KGLOBHD3, KGLOBHS3 curheaps_size3,
KGLOBHD4, KGLOBHS4 curheaps_size4,
KGLOBHD5, KGLOBHS5 curheaps_size5,
KGLOBHD6, KGLOBHS6 curheaps_size6,
KGLOBHD7, KGLOBHS7 curheaps_size7,
— KGLOBT00 CTXSTAT,
KGLOBSTA STATUS
from
X$KGLOB
— X$KGLCURSOR_CHILD
where
KGLNAHSH in (&1)
and KGLOBT09 like (‘&2’)
order by
KGLOBT09 ASC
/

— Cursor data block summary
select
‘HEAP0’ heap
, ksmchcls class
, ksmchcom alloc_comment
, sum(ksmchsiz) bytes
, count(*) chunks
from
x$ksmhp
where
KSMCHDS = hextoraw(‘&v_curheaps_kglobhd0’)
group by
‘HEAP0’
, ksmchcls
, ksmchcom
order by
sum(ksmchsiz) desc
/

select
‘HEAP4’ heap
, ksmchcls class
, ksmchcom alloc_comment
, sum(ksmchsiz) bytes
, count(*) chunks
from
x$ksmhp
where
KSMCHDS = hextoraw(‘&v_curheaps_kglobhd4’)
group by
‘HEAP6’
, ksmchcls
, ksmchcom
order by
sum(ksmchsiz) desc
/

select
‘HEAP6’ heap
, ksmchcls class
, ksmchcom alloc_comment
, sum(ksmchsiz) bytes
, count(*) chunks
from
x$ksmhp
where
KSMCHDS = hextoraw(‘&v_curheaps_kglobhd6’)
group by
‘HEAP0’
, ksmchcls
, ksmchcom
order by
sum(ksmchsiz) desc
/

— Cursor data block details

— select * from x$ksmhp where KSMCHDS = hextoraw(‘&v_curheaps_kglobhd0’);
— select * from x$ksmhp where KSMCHDS = hextoraw(‘&v_curheaps_kglobhd6’);

For example:

SQL> @cursor 2781999655 65535
old 20: KGLNAHSH in (&1)
new 20: KGLNAHSH in (2781999655)
old 21: and KGLOBT09 like (‘&2’)
new 21: and KGLOBT09 like (‘65535’)

KGLNAHSH KGLHDPAR CHILD# KGLHDADR KGLOBHD0 SIZE0 SIZE1 SIZE2
———- ——– ———- ——– ——– ——– ——– ——–
SIZE3 KGLOBHD4 SIZE4 SIZE5 KGLOBHD6 SIZE6 SIZE7 STATUS
——– ——– ——– ——– ——– ——– ——– ———-
2781999655 2C8E8408 65535 2C8E8408 2CAF987C 1781 0 0
0 00 0 0 00 0 0 1

old 10: KSMCHDS = hextoraw(‘&v_curheaps_kglobhd0’)
new 10: KSMCHDS = hextoraw(‘2CAF987C’)

HEAP CLASS ALLOC_COMMENT BYTES CHUNKS
—– ——– —————- ———- ———-
HEAP0 perm permanent memor 1608 2
HEAP0 free free memory 364 2
HEAP0 freeabl kgltbtab 76 1
HEAP0 freeabl kksfbc:hash1 40 2

old 10: KSMCHDS = hextoraw(‘&v_curheaps_kglobhd4′)
new 10: KSMCHDS = hextoraw(’00’)

no rows selected

old 10: KSMCHDS = hextoraw(‘&v_curheaps_kglobhd6′)
new 10: KSMCHDS = hextoraw(’00’)

no rows selected

Script: search datafile segments in order For resizing datafiles

November 27, 2011 maintain, oracle 1 comment

下面脚本可以检测datafile 中创建segment的次序,以利于以后move object 进而resize datafile大小之用

block_id * 8代表 blocksize=8092

SQL>SET ECHO off FEEDBACK off HEADING on NUMWIDTH 10
SQL>DEFINE TABLESPACE=’&1′;
SQL>DEFINE FILE_ID=&2;

———————————
DECLARE
v_prior_file_name VARCHAR2(513);
v_prior_owner VARCHAR2(30);
v_prior_segment VARCHAR2(30);
v_segment_kb NUMBER := 0;
v_start_kb NUMBER := 0;
BEGIN
DBMS_OUTPUT.PUT_LINE(RPAD(‘.’, 60, ‘ ‘) || ‘ Start Pos Length’);
DBMS_OUTPUT.PUT_LINE(RPAD(‘Owner and Segment Name’, 60) || ‘ (KB) (KB)’);
DBMS_OUTPUT.PUT_LINE(RPAD(‘-‘, 60, ‘-‘) || ‘ ———– ———‘);
FOR c_extents IN (SELECT e.*, d.file_name
FROM dba_data_files d
, (SELECT owner, segment_name, file_id, (block_id * 8) start_kb
, ((block_id + blocks – 1) * 8) end_kb
FROM dba_extents
WHERE tablespace_name = ‘&TABLESPACE’
AND file_id = &FILE_ID
UNION ALL
SELECT ‘*****Free’, ‘Space*****’, file_id, (block_id * 8) start_kb
, ((block_id + blocks – 1) * 8) end_kb
FROM dba_free_space
WHERE tablespace_name = ‘&TABLESPACE’
AND file_id = &FILE_ID) e
WHERE d.file_id = e.file_id
ORDER BY start_kb DESC)
LOOP
/*
* If the segment is the same as the previous, continue adding it’s space.
*/
IF c_extents.owner = v_prior_owner AND c_extents.segment_name = v_prior_segment
THEN
v_segment_kb := v_segment_kb + (c_extents.end_kb – c_extents.start_kb);
ELSE
/*
* If v_prior_owner IS NULL, then this is the first row read.
*/
IF v_prior_owner IS NOT NULL
THEN
DBMS_OUTPUT.PUT_LINE(RPAD(v_prior_owner || ‘.’ ||v_prior_segment, 60) || ‘ ‘ ||
TO_CHAR(v_start_kb, ‘9999999999’) || ‘ ‘ ||
TO_CHAR(v_segment_kb, ‘99999999’));
END IF;
v_start_kb := c_extents.start_kb;
v_prior_file_name := c_extents.file_name;
v_prior_owner := c_extents.owner;
v_prior_segment := c_extents.segment_name;
v_segment_kb := (c_extents.end_kb – c_extents.start_kb);
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE(RPAD(v_prior_owner || ‘.’ ||v_prior_segment, 60) || ‘ ‘ ||
TO_CHAR(v_start_kb, ‘9999999999’) || ‘ ‘ ||
TO_CHAR(v_segment_kb, ‘99999999’));
END;
/

example:

SQL> create table ceshi01 tablespace users as select * from dba_objects;

Table created.

SQL> set serveroutput on;
SQL>
SQL> SET ECHO off FEEDBACK off HEADING on NUMWIDTH 10
SQL> DEFINE TABLESPACE=’&1′;
SQL> DEFINE FILE_ID=&2
SQL> DECLARE
2 v_prior_file_name VARCHAR2(513);
3 v_prior_owner VARCHAR2(30);
4 v_prior_segment VARCHAR2(30);
5 v_segment_kb NUMBER := 0;
6 v_start_kb NUMBER := 0;
7 BEGIN
8 DBMS_OUTPUT.PUT_LINE(RPAD(‘.’, 60, ‘ ‘) || ‘ Start Pos Length’);
9 DBMS_OUTPUT.PUT_LINE(RPAD(‘Owner and Segment Name’, 60) || ‘ (KB) (KB)’);
10 DBMS_OUTPUT.PUT_LINE(RPAD(‘-‘, 60, ‘-‘) || ‘ ———– ———‘);
11 FOR c_extents IN (SELECT e.*, d.file_name
12 FROM dba_data_files d
13 , (SELECT owner, segment_name, file_id, (block_id * 16) start_kb
14 , ((block_id + blocks – 1) * 16) end_kb
15 FROM dba_extents
16 WHERE tablespace_name = ‘&TABLESPACE’
17 AND file_id = &FILE_ID
18 UNION ALL
19 SELECT ‘*****Free’, ‘Space*****’, file_id, (block_id * 16) start_kb
20 , ((block_id + blocks – 1) * 16) end_kb
21 FROM dba_free_space
22 WHERE tablespace_name = ‘&TABLESPACE’
23 AND file_id = &FILE_ID) e
24 WHERE d.file_id = e.file_id
25 ORDER BY start_kb DESC)
26 LOOP
27 /*
28 * If the segment is the same as the previous, continue adding it’s space.
29 */
30 IF c_extents.owner = v_prior_owner AND c_extents.segment_name = v_prior_segment
31 THEN
32 v_segment_kb := v_segment_kb + (c_extents.end_kb – c_extents.start_kb);
33 ELSE
34 /*
35 * If v_prior_owner IS NULL, then this is the first row read.
36 */
37 IF v_prior_owner IS NOT NULL
38 THEN
39 DBMS_OUTPUT.PUT_LINE(RPAD(v_prior_owner || ‘.’ ||v_prior_segment, 60) || ‘ ‘ ||
40 TO_CHAR(v_start_kb, ‘9999999999’) || ‘ ‘ ||
41 TO_CHAR(v_segment_kb, ‘99999999’));
42 END IF;
43 v_start_kb := c_extents.start_kb;
44 v_prior_file_name := c_extents.file_name;
45 v_prior_owner := c_extents.owner;
46 v_prior_segment := c_extents.segment_name;
47 v_segment_kb := (c_extents.end_kb – c_extents.start_kb);
48 END IF;
49 END LOOP;
50 DBMS_OUTPUT.PUT_LINE(RPAD(v_prior_owner || ‘.’ ||v_prior_segment, 60) || ‘ ‘ ||
51 TO_CHAR(v_start_kb, ‘9999999999’) || ‘ ‘ ||
52 TO_CHAR(v_segment_kb, ‘99999999’));
53 END;
54 /

. Start Pos Length
Owner and Segment Name (KB) (KB)
———————————————————— ———– ———
*****Free.Space***** 70656 31736
SYS.CESHI03 69632 17152
SYS.CESHI02 52224 17152
SYS.CESHI 34816 17152
SYS.CESHI01 17408 17152

PL/SQL procedure successfully completed.