콩나물 :: Oracle

어플리케이션/데이터베이스일반2009. 6. 3. 10:46

Oracle - Tunning

튜닝관련 Excel Tool

s_엑셀+DB+진단+툴+샘플화면.GIF

아직 사용해보진 않음

관련 Query^#

SELECT SUM(pins) "Executions",
SUM(reloads) "Cache Misses while Executing"
FROM v$librarycache;

SELECT SUM(GETS) " DATA DICTIONARY GETS"
SUM(GETMISSES) " DATA DICTIONARY GET MISSES"
FROM V$ROWCACHE;

SELECT SUM(value) || ' bytes' "Total memory for all sessions"
FROM v$sesstat, v$statname
WHERE NAME = 'session pga memory'
AND v$sesstat.statistic# = v$statname.statistic#;

SELECT name, value
FROM v$sysstat
WHERE name in ( 'db block gets','consistent gets','physical reads');

SELECT 250*"TRUNC(indx/250)+1||' to '||250*(TRUNC(indx/250)+1)
"Interval", SUM(count) "Buffer Cache Hits"
FROM sys.x$kcbrbh
GROUP BY TRUNC(indx/250);

SELECT name, phyrds, phywrts
FROM v$datafile df , v$filestat fs
WHERE df.file# = fs.file#

select sql_text,buffer_gets,disk_reads
from v$sqlarea
where buffer_gets > 10000
or disk_reads > 10000000
order by disk_reads desc

SELECT class, count
FROM v$waitstat
WHERE CLASS IN ( 'system undo header','system undo block','undo header','undo block');

SELECT ln.name, gets, misses, immediate_gets, immediate_misses
FROM v$latch l, v$latchname ln
WHERE ln.name IN ('redo allocation','redo copy')
AND ln.latch#=l.latch#;

SELECT name, value
FROM v$sysstat
WHERE name = 'redo log space requests';

SELECT name, value
FROM v$sysstat
WHERE name IN ('sorts(memory)','sorts(disk)');

SELECT class, count
FROM V$waitstat
WHERE class = 'free list'

Select name, optsize, shrinks, aveshrink, wraps, extends
From V$ROLLSTAT, V$ROLLNAME
where V$ROLLSTAT.usn = V$ROLLNAME.usn;

select a.tablespace_name, a.file_name, b.phyrds “Physical Read#”,
b.phywrts “Physical Write#”
from dba_data_files a, v$filestat b
where a.file_id = b.file# and a.tablespace_name like ‘DATA%’;

select a.group#, a.member, b.status , b.bytes
from v$logfile a, v$log b
where a.group# = b.group#;

아래는 시스템이 시작된 후의 CPU사용량.(순간적인 측정이 아님)
select a.sid,spid,status,substr(a.program,1,10) prog,a.terminal, osuser, value/60/100 value
from v$session a, v$process b, v$sesstat c
where c.statistic# = 12
and c.sid = a.sid
and a.paddr = b.addr
order by value desc;

select sid, event,p1,p2,p3
from v$session_wait
where event not like 'SQL%'
and event not like 'rdbms%'
and event not like '%timer'

UNIX의 PID로 SID를 찾아 내어 sql문장을 찾아냄
select sid
from v$session a, v$process b
where a.paddr = b.addr
and b.spid = 3179-PID;

select sql_text
from v$sqltext a, v$session b
where a.hash_value = b.sql_hash_value
and b.sid = 176

위의 것을 합치면 아래
select c.sql_text
from v$process a, v$session b, v$sqltext c
where a.addr = b.paddr and b.sql_hash_value = c.hash_value
and a.spid = <<PID>>;

select min(a.user_name),min(c.sql_text)
from v$open_cursor a, v$session b,v$sqltext c
where a.sid = b.sid and b.sql_hash_value = c.hash_value
having count(a.sid) > 5
group by a.sid

SELECT b.file_name, b.bytes FILE_SIZE, sum(a.bytes) FREE_SPACE
FROM dba_free_space a, dba_data_files b
WHERE a.file_id = b.file_id
AND a.tablespace_name = 'PMSUSER'
GROUP BY b.file_name, b.bytes

SELECT sql_text,
       sorts,
       executions,
       users_executing,
       first_load_time,
       invalidations,
       parse_calls,
       disk_reads,
       s.buffer_gets,
       rows_processed,
       optimizer_mode,
       optimizer_cost,
       RAWTOHEX(address) address ,
       hash_value,
       u.username parsing_username,
       s.buffer_gets*100/total.buffer_gets pct_total_gets,
       s.buffer_gets/DECODE(s.rows_processed,0,1,s.rows_processed)
gets_per_row
FROM v$sql s,
       all_users u,
       (SELECT SUM(buffer_gets) buffer_gets FROM v$sql) total
WHERE u.user_id=s.parsing_user_id AND executions > 0
and u.username = 'PMSDB'
ORDER BY s.buffer_gets desc

set verify off
clear break
break on column_name on constraint_type

select substr(a.column_name, 1, 15) column_name,
                decode(b.constraint_type, 'P', 'Primary key',
                                          'U', 'Unique key',
                                          'C', 'Check or Not null',
                                          'R', 'Foreign key') constraint_type,
                a.constraint_name constraint_name
from user_cons_columns a, user_constraints b
where a.table_name = upper('&1')
   and a.table_name = b.table_name
   and a.constraint_name = b.constraint_name
order by 1

[2008.05.26] Oracle Dictionary 조회^#

-- 접속한 User 보기
select username, count(sid) from V$SESSION
where STATUS = 'ACTIVE'
group by username;

-- 오라클 버젼보기
select * from v$version;

select * from product_component_version;

-- 오라클 서버의 인스턴스
select * from v$instance;

-- 오라클 서버의 Database 이름 알아내기
select * from v$database;

-- 오라클 서버의 파라미터
select * from v$parameter;

-- 오라클 서버의 테이블 스페이스 보기
select * from dba_tablespaces;

-- 캐릭터셋, 문자셋
select * from sys.props$ where name = 'NLS_CHARACTERSET';
select * from v$nls_parameters where parameter='NLS_CHARACTERSET';

-- 오라클의 리소스 상태
select * from v$resource_limit;

[2008.05.22] Table Space 정보 구하기 - Query^#

select * from (
    select    TABLESPACE_NAME,
        INITIAL_EXTENT,
        NEXT_EXTENT,
        MIN_EXTENTS,
        MAX_EXTENTS,
        PCT_INCREASE,
        STATUS,
        CONTENTS
    from     dba_tablespaces
    order     by TABLESPACE_NAME
) a,
(
    select    a.TABLESPACE_NAME,
        a.BYTES bytes_used,
        b.BYTES bytes_free,
        b.largest,
        a.FILE_NAME,
        round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) percent_used
    from
        (
            select     TABLESPACE_NAME,
                FILE_NAME,
                sum(BYTES) BYTES
            from     dba_data_files
            group     by TABLESPACE_NAME, FILE_NAME
        )
        a,
        (
            select     TABLESPACE_NAME,
                sum(BYTES) BYTES ,
                max(BYTES) largest
            from     dba_free_space
            group     by TABLESPACE_NAME
        )
        b
    where     a.TABLESPACE_NAME=b.TABLESPACE_NAME
    order     by ((a.BYTES-b.BYTES)/a.BYTES) desc
) b
where a.TABLESPACE_NAME = b.TABLESPACE_NAME(+)

[2008.05.22] 사용자 정보 구하기 - Query^#

select     USERNAME,
    CREATED,
    PROFILE,
    DEFAULT_TABLESPACE,
    TEMPORARY_TABLESPACE
from     dba_users
order     by USERNAME

[2008.05.20] - SPS (류재명) 테이블 스페이스별 프리영역을 체크 - Query^#

SELECT a.tablespace_name,
a.total "Total(Mb)",
a.total - b.free "Used(Mb)",
nvl(b.free,0) "Free(Mb)",
round((a.total - nvl(b.free,0))*100/total,0) "Used(%)"
from ( select tablespace_name,
round((sum(bytes)/1024/1024),0) as total
from dba_data_files
group by tablespace_name) a,
( select tablespace_name,
round((sum(bytes)/1024/1024),0) as free
from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name(+)
order by a.tablespace_name;

[결과]

TABLESPACE_NAME	Total(Mb)	Used(Mb)	Free(Mb)	Used(%)
CWMLITE	20	9	11	45
DRSYS	20	10	10	50
EXAMPLE	149	148	1	99
INDX	25	0	25	0
ODM	20	9	11	45
SYSTEM	400	397	3	99
TOOLS	10	6	4	60
TS_SPSIM	34		0	100
UNDOTBS1	200	6	194	3
USERS	25	0	25	0
XDB	38	38	0	100