'분류 전체보기' 카테고리의 글 목록 (22 Page)

'분류 전체보기'에 해당되는 글 829건

2012.03.08 오라클 12년도 교육 과정
2012.03.06 Memory Notification: Library Cache Object loaded into SGA
2012.01.10 [ Oracle - Admin ] Linux 커널 매게변수 및 Shell Limit
2012.01.10 SQL 실행 계획 확인방법
2012.01.02 Oracle Hang 상황에서 액티브 세션 리스트 얻기 1
2011.12.28 ora-4031 and shared pool duration
2011.12.28 "Pmon Failed To Acquire Latch" Messages in Alert Log -Database Hung [ID 468740.1]
2011.12.28 오라클 버전간의 파라미터 변경사항 비교 (Oracle 9i, 10g, 11g)
2011.12.21 ORACLE Session Statistics 쿼리
2011.12.21 오라클 모니터링 쿼리

ORACLE2012. 3. 8. 11:12

오라클 12년도 교육 과정

cy2012_상반기전체일정표_v.3[1].pdf

'ORACLE' 카테고리의 다른 글

화일의 손상 여부를 확인하는 dbv 사용 방법 (0)	2012.05.11
Oracle 데이터베이스 신규 취약점 주의 (0)	2012.05.10
Oracle Shared Server 튜닝 (0)	2011.12.13
쿼리문이 제대로 Index를 타는지 확인하는 방법 (0)	2008.03.27
Oracle ERP에서 사용하는 DB관련 작업 (0)	2007.10.22

Posted by [PineTree]

ORACLE/TroubleShooting2012. 3. 6. 16:37

Memory Notification: Library Cache Object loaded into SGA

SELECT a.ksppinm "Parameter", b.ksppstvl "Session Value",
2         c.ksppstvl "Instance Value"
3    FROM x$ksppi a, x$ksppcv b, x$ksppsv c
4   WHERE a.indx = b.indx AND a.indx = c.indx
5         AND a.ksppinm LIKE '%kgl_large_heap%';

Parameter
--------------------------------------------------------------------------------

Session Value
--------------------------------------------------------------------------------
--------------------
Instance Value
--------------------------------------------------------------------------------
--------------------
_kgl_large_heap_warning_threshold
52428800
52428800

출처 : http://gampol.tistory.com/entry/Memory-Notification-Library-Cache-Object-loaded-into-SGA

조치방법: (다음과 같이 조치후 서버 restart)
sqlplus “/as sysdba”
alter system set "_kgl_large_heap_warning_threshold"=8388608 scope=spfile ;
shutdown immediate
startup

원인: (Cause 항목 참조)
해당 오류메시지는 Error 가 아닌 Warning 입니다.
이는 shared pool 영역중 libraray cache 영역의 단편화가 심해 Free Memory를 찾는데 오랜 시간이 걸릴 때
위와 같은 오류가 발생합니다.
새로운 트랜잭션이 발생시 SQL의 파싱결과 등이 저장되는 library cache영역에 단편화로 인한 메시지로
10g R2의 메모리 관리 메커니즘에 따른 메시지로 보시면됩니다.

=======================================================================
Applies to:
Oracle Server - Enterprise Edition - Version:
This problem can occur on any platform.
Symptoms
The following messages are reported in alert.log after 10g Release 2 is installed.
       Memory Notification: Library Cache Object loaded into SGA
       Heap size 2294K exceeds notification threshold (2048K)
Changes
Installed / Upgraded to 10g Release 2
Cause
These are warning messages that should not cause the program
responsible for these errors to fail.
They appear as a result of new event messaging mechanism
and memory manager in 10g Release 2. The meaning is that the
process is just spending a lot of time in finding free memory
extents during an allocate as the memory may be heavily fragmented.
Real memory messages should be ORA-403x when a real memory allocation problem
occurs.
Solution
In 10g we have a new undocumented parameter that sets the KGL heap size
warning threshold.   This parameter was not present in 10gR1.
Warnings are written if heap size exceeds this threshold.

Set _kgl_large_heap_warning_threshold to a reasonable high value or zero
to prevent these warning messages. Value needs to be set in bytes.
If you want to set this to 8192 (8192 * 1024) and are using an spfile:
(logged in as "/ as sysdba")
SQL> alter system set "_kgl_large_heap_warning_threshold"=8388608 scope=spfile ;
SQL> shutdown immediate SQL> startup
SQL> show parameter _kgl_large_heap_warning_threshold
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_kgl_large_heap_warning_threshold integer 8388608
If using an "old-style" init parameter,
Edit the init parameter file and add
_kgl_large_heap_warning_threshold=8388608
In 10.2.0.2, the threshold is increased to 50MB after regression tests, so this should be a reasonable and recommended value.
References

http://surachartopun.com/2008/07/memory-notification-library-cache.html

10.2.0.1 Annoying message "Memory Notification: Library Cache Object loaded into SGA"
Posted: March 5, 2006 by Frits Hoogland in Oracle EE, Oracle XE
9In the 10.2.0.1 version of the oracle database a new heap checking mechanism, together with a new messaging system is introduced.
This new mechanism reports memory allocations above a threshold in the alert.log, together with a tracefile in the udump directory.

In certain situations it can be handy to know if large allocations are being done in the sga heap (shared pool),
but only to troubleshoot memory allocation problems (which eventually will appear as the infamous ORA-4031).
For normal day to day work, I doubt the effectivity of these messages.

The default limit is set at 2048K. It appears normal usage can result in messages from the memory manager.
During my last installation (with all options set default, all options installed),
this resulted in 125 messages from the memory manager.

Oracle gives a solution on metalink in note 330239.1:
Set _kgl_large_heap_warning_threshold to a “reasonable high” value. This parameter is set in bytes,
and oracle recommends the value 8388608 (8192K).

Wednesday, July 30, 2008
Memory Notification: Library Cache Object loaded into SGA

Today, I checked alert.log file. I found:

Memory Notification: Library Cache Object loaded into SGA
Heap size 9426K exceeds notification threshold (8192K)

When I found out on metalink:
These messages are report in alert.log after 10g Release 2 is installed.

Cause:

These are warning messages that should not cause the program responsible for these errors to fail. They appear as a result of new event messaging mechanism and memory manager in 10g Release 2.

The meaning is that the process is just spending a lot of time in finding free memory extents during an allocate as the memory may be heavily fragmented. Fragmentation in memory is impossible to eliminate completely, however, continued messages of large allocations in memory indicate there are tuning opportunities on the application.

The messages do not imply that an ORA-4031 is about to happen.

Solution:

In 10g we have a new undocumented parameter that sets the KGL heap size warning threshold. This parameter was not present in 10gR1. Warnings are written if heap size exceeds this threshold.

Set _kgl_large_heap_warning_threshold to a reasonable high value or zero to prevent these warning messages. Value needs to be set in bytes.

If you want to set this to 8192 (8192 * 1024) and are using an spfile:

(logged in as "/ as sysdba")

SQL> alter system set "_kgl_large_heap_warning_threshold"=10485760 scope=spfile ;

SQL> shutdown immediate

SQL> startup

SQL> show parameter _kgl_large_heap_warning_threshold

NAME TYPE VALUE

--------------------------------- ------- ------------------------------
_kgl_large_heap_warning_threshold integer 10485760

If using an "old-style" init parameter,

Edit the init parameter file and add

_kgl_large_heap_warning_threshold=10485760

[출처] Memory Notification: Library Cache Object loaded into SGA|작성자 한여름

저작자표시 비영리

'ORACLE > TroubleShooting' 카테고리의 다른 글

Unable To Start Instance due to ORA-7445 Dump In KSBNFY on Power Linux [ID 563895.1] (0)	2012.04.20
Ora-600 [Unable To Load Xdb Library] in AIX [ID 559911.1] (0)	2012.04.20
ora-4031 and shared pool duration (0)	2011.12.28
"Pmon Failed To Acquire Latch" Messages in Alert Log -Database Hung [ID 468740.1] (0)	2011.12.28
ORA-04031 (0)	2011.12.06

Posted by [PineTree]

ORACLE/INSTALL2012. 1. 10. 23:46

[ Oracle - Admin ] Linux 커널 매게변수 및 Shell Limit

[ Oracle - Admin ] Linux 커널 매게변수 및 Shell Limit(리소스 제한)

작성자 : 박상수
작성일자 : 2010.12.08
작업환경 : VMware7 [ RHEL3 , Oracle 10g 기준 ]
참고자료 : 서진수 샘, 정성재 샘, 리눅스공유메모리, 보안구성설정(PAM&Ulimit)

오라클의 공유 메모리는 커널 작업을 수반하지 않으며 프로세스 간의 데이터 복제 작업이 불필요 하기 때문에 , IPC(inter-Process Communication)를 위한 가장 빠른 방법을 선호 되고 있습니다.
오라클은 인스턴스 구조를 보면 SGA(System Global Area)와 백 그라운드 프로세스로 구성 되어 있습니다. 여기에서 SGA는 Shared Pool, DB Cache, Redo Log Buffer 등의 저장에 활용되므로 SGA 크기에 따라 오라클 성능이 크게 달라집니다.

그럼 오라클을 설치하기 전에 Linux에서 사용하는 커널 매개변수와 Shell Limit에 대해서 알아 보도록 하겠습니다.

1. Linux 커널 매개변수의 설정

다른 UNIX 시스템과 달리, Linux 운영체제에서는 시스템이 실행 중인 상태에서 대부분의 커널 매개변수를 수정할 수 있습니다. 커널 매개변수를 변경한 뒤에 시스템을 리부팅할 필요도 없습니다. Oracle Database 10g가 요구하는 커널 매개변수 설정할 수 있으나 시스템에 이미 커널 메게변수가 높은 수치로 설정 되어있을 경우에는 변경 하지 마시기 바랍니다.

[ 매개변수 - kernel.shmmax ] SHMMAX 매개변수는 공유 메모리 세그먼트의 최대 크기(바이트 단위)를 정의하는데 사용됩니다. 오라클 SGA 메모리로 구성되며, SHMMAX가 올바르게 설정되지 않을 경우 SGA의 크기가 제약될 수도 있습니다. 따라서 SGA의 크기보다 작지 않도록 SHMMAX를 설정합니다.
총 10GB의 메모리에서 오라클이 사용할 수 있는 최대 한도를 정의 하는 것을 의미 합니다.
먄약 SHMMAX 매개변수가 잘못 설정될 경우에는 아래와 같은 오라클 에러 메시지가 발생 됩니다.

ORA-27123: unable to attach to shared memory segment

기본적으로 SHMMAX 기본 값은 32MB 입니다. SGA로 사용하기에는 너무 적은 공간이기 때문에 적당한 크기로 변경 해야 합니다.
[2010-12-09 03:26:18]-[root@calmmass:/proc/sys/kernel]
# cat /proc/sys/kernel/shmmax |sed -e :a -e 's/$.*[0-9]$$[0-9]\{3\}$/\1.\2/;ta'
33.554.432

[ 매개변수 - kernel.shmall ] SHMALL 커널 매개변수는 특정 시점에 시스템에서 사용 가능한 공유 메모리의 최대 크기(페이지 단위)를 설정하는데 사용됩니다. 따라서 이 매개변수는 최소한 아래 값보다 커야 합니다.
ceil(SHMAX/PAGE_SIZE)
우선 페이지 사이즈를 확인 해보겠습니다. Red Hat Linux의 페이지 사이즈는 기본적으로 4,096 바이트 입니다.
[2010-12-09 02:51:29]-[root@calmmass:~]
# getconf PAGESIZE |sed -e :a -e 's/$.*[0-9]$$[0-9]\{3\}$/\1.\2/;ta'
4.096

SHMALL의 디폴트 사이즈는 아래와 같습니다. sed를 이용해서 3자리로 구분한 결과 2MB를 사용합니다.
# cat /proc/sys/kernel/shmall |sed -e :a -e 's/$.*[0-9]$$[0-9]\{3\}$/\1.\2/;ta'
2.097.152

[ 매개변수 - kernel.shmmni ] SHMMNI는 공유 메모리 식별자의 개수를 의미 하며 권장 값은 100 이상 으로 설정하셔야 합니다. 아래와 같이 확인 하시기 바랍니다.
[2010-12-09 03:26:07]-[root@calmmass:/proc/sys/kernel]
# cat shmmni
4096

[ 매개변수 - kernel.sem ] 세마포어(Semaphore)란 공유 리소스의 사용 과정에서 프로세스(프로세스 내 쓰레드) 간의 동기화를 위해 사용되는 일종의 카운터(counter) 입니다. 애플리케이션의 세마포어를 이용하면, 운영 체제는 SET를 통해 세마포어를 지원하게 됩니다.
그럼 세마포어 설정 값을 먼저 확인 해보겠습니다.
[2010-12-09 03:39:32]-[root@calmmass:/proc/sys/kernel]
# ipcs -lm
------ Shared Memory Limits --------
max number of segments = 4096
max seg size (kbytes) = 32768
max total shared memory (kbytes) = 8388608
min seg size (bytes) = 1

SEM 매개변수는 세마포어 최대값을 조정 가능하며 의미는 아래와 같습니다.
의미 : {배열당 최대 SEM 수}{최대SEM시스템넓이}{SEMOP호출당최대OP수}{최대배열수}
[2010-12-09 03:37:24]-[root@calmmass:/proc/sys/kernel]
# cat /proc/sys/kernel/sem |sed -e :a -e 's/$.*[0-9]$$[0-9]\{3\}$/\1.\2/;ta'
250 32.000 32 128
1 2         3   4 (필드 라고 정의 하겠습니다.)
1 필드 값(250) : SEMMSL 매개변수는 세미포어 set 당 세마포어의 최대 개수를 정의합니다. 오라클을 예로 init[SID].ora 파일(pfile, spfile 동일)의 PROCESS 인스턴스 매개변수의 (전체 데이터베이스 중) 최대값에 10을 더한 값을 사용할 것을 권장하고 있습니다. 그래서 100이상의 값을 정의하는 것을 권장 합니다.

2 필드 값(32.000) : SEMMNI 매개변수는 전체 Linux 시스템의 세마포어 set의 개수를 정의 합니다. 오라클에서는 100이상의 값을 정의하는 것을 권장합니다.

3 필드 값(32) : SEMMNS 매개변수는 전체 Linux 시스템의 ("세마포어 set"이 아닌) 세마포어의 최대 개수를 정의 합니다. 오라클은 인스턴스 매개변수의 값을 모두 더한 뒤, 가장 큰 Processes값을 두 차례 더하고, 마지막으로 각 데이터베이스 별로 10을 더한 값을 SEMMNS를 설정하도록 권장 합니다.

4. 필드 값(128) : SEMOPM 매개변수는 semop 시스템 호출(system call) 별로 수행될 수 있는 세마포어 작업의 수를 설정하는데 사용됩니다.SEMOPM을 SEMMSL과 동일하게 설정하는 것을 권장합니다. 오라클에서는 100이상의 값으로 설정할 것을 권장 하고 있습니다.

[ 매개변수 - fs.file-max ] Red Hat Linux 서버를 구성하는 과정에서, 사용 가능한 파일 핸들(file handle)의 수가 충분한지 확인하는 것을 매우 중요합니다. 파일 핸들의 최대 개수 설정에 따라 Linux 시스템에서 동시에 오픈할 수 있는 파일의 수가 달라지게 됩니다.
오라클은 전체 시스템의 파일 핸들 수를 최소 65536개 이상으로 설정 할 것을 권장 하고 있습니다.
전체 시스템에서 사용 가능한 파일 핸들의 최대 값을 확인 해보겠습니다.
[2010-12-09 04:06:03]-[root@calmmass:/proc/sys/fs]
# cat /proc/sys/fs/file-max |sed -e :a -e 's/$.*[0-9]$$[0-9]\{3\}$/\1.\2/;ta'
104.857

[ 매개변수 - net.Ipv4.Ip_local_port_range ] 이부분은 새 접속에서 사용할 수 있는 포트의 사용 가능 범위를 확장하는 의미를 가지고 있습니다.
시스템에서 사용하는 포트번호는 /etc/services 파일에 구성 되어있습니다.
[2010-12-09 04:06:24]-[root@calmmass:/proc/sys/fs]
# tail /etc/services
binkp  24554/udp   # Binkley
asp  27374/tcp   # Address Search Protocol
asp  27374/udp   # Address Search Protocol
.. 생략 ..

매개변수의 값을 확인 해보겠습니다. 오라클은 1024 ~ 65000까지 허용하도록 권장 합니다.
[2010-12-09 04:14:10]-[root@calmmass:/proc/sys/net/ipv4]
# cat /proc/sys/net/ipv4/ip_local_port_range |sed -e :a -e 's/$.*[0-9]$$[0-9]\{3\}$/\1.\2/;ta'
32.768 61.000

[ 매개변수 - net.core.rmem_default, net.core.rmem_max ] 일반적인 경우의 대용량 Serving을 위한 설정 합니다. 오라클은 기본적으로 대용량 데이터베이스를 기준으로 하기 때문에 기본 설정 된 값을 변경해야 합니다. 아래와 같은 경우에는 기본 설정 값입니다.
[2010-12-09 04:18:03]-[root@calmmass:/proc/sys/net/core]
# cat /proc/sys/net/core/rmem_default |sed -e :a -e 's/$.*[0-9]$$[0-9]\{3\}$/\1.\2/;ta'
65.535
[2010-12-09 04:18:29]-[root@calmmass:/proc/sys/net/core]
# cat /proc/sys/net/core/rmem_max |sed -e :a -e 's/$.*[0-9]$$[0-9]\{3\}$/\1.\2/;ta'
131.071

[ 매개변수 - net.core.wmem_default, net.core.wmem_max] 소켓 출력 큐의 기본 값과 최대 값을 조정하기 위한 매개변수 입니다. 일반적인 경우의 대용량 Serving을 위한 설정이므로 역시 rmem_default, rmem_max의 값과 동일하게 구성해는 것이 바랍직 합니다. 기본적으로 설정 되어있는 값을 확인 해보겠습니다.
# cat /proc/sys/net/core/wmem_default |sed -e :a -e 's/$.*[0-9]$$[0-9]\{3\}$/\1.\2/;ta'
65.535
[2010-12-09 04:23:05]-[root@calmmass:/proc/sys/net/core]
# cat /proc/sys/net/core/wmem_max |sed -e :a -e 's/$.*[0-9]$$[0-9]\{3\}$/\1.\2/;ta'
131.071

[ 커널 매개변수 반영 ]
이상으로 매개변수의 용도와 설정값을 확인 하는 방법을 실습 했습니다.
이번에는 커널 매개변수를 오라클 기준으로 변경해서 반영 해보도록 하겠습니다. 기본적으로는 위에 나와있는 설정 파일에 값을 변경 해도 되며, sysctl -w [매개변수명]=value 명령어를 이용 할 수 있습니다. 하지만 이번 문서에서는 리눅스의 장점을 살려 환경설정 파일로 매개변수를 반영 하겠습니다.

오라클(바로가기) 설치 메뉴얼에는 아래와 같이 설정 하길 권장합니다.
cat >> /etc/sysctl.conf <<EOF
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
EOF
/sbin/sysctl -p

파라매터를 더 추가해보겠습니다. (강추 입니다.)
kernel.shmmax=536870912
kernel.shmmni=4096
kernel.shmall=2097152
kernel.sem=250 32000 100 128
fs.file-max=65536
net.ipv4.ip_local_port_range=1024 65000
net.core.rmem_default=262144
net.core.rmem_max=262144
net.core.wmem_default=262144
net.core.wmem_max=262144

2. Shell Limit 설정

오라클은 Linux 계정 별로 실행되는 프로세스와 열린 파일의 수를 제한하는 것을 권장합니다. 이를 위해, root 계정에서 아래 내용을 /etc/security/limits.conf 설정파일에 oracle10g 라는 특정 프로그램을 제어하는 방법을 설명 하겠습니다.

[참고] /lib/security/pam_limits.so 모듈이 사용할 설정 파일이 /etc/security/limits.conf 파일 입니다. 그중에서 각각 설정에 대해서 아래와 같이 정리 하였습니다.
domain : 제한을 할 대상으로써 사용자 이름이나 그룹 이름, 그리고 와일드 문자를 사용할 수 있습니다.
type : 강하게 제한할 것인지 아니면 어느 정도 여유를 줄 것인지 결정을 합니다.(soft, hard)
item : 제한을 할 항목입니다.
    - core : core 파일 사이즈(KB)
    - data : 최대 데이터 크기(KB)
    - fsize : 최대 파일 사이즈(KB)
    - memlock : 최대 locked-in-memory 주소 공간(KB)
    - nofile : 한번에 열 수 있는 최대 파일 수
    - rss : 최대 resident set size(KB)
    - stack : 최대 스택 사이즈(KB)
    - cpu : 최대 CPU 점유 시간(MIN)
    - nproc : 최대 프로세스 개수
    - as : 주소 공간 제한
    - maxlogins : 동시 로그인 최대 수
    - priority : 사용자 프로세스가 실행되는 우선 순위

[참고] ulimit를 이용하여 리소스를 제한하는 방법입니다. ulimit는 PAM과는 조금 다르게 root계정까지도 제한을 할 수 있습니다. 여기에서 제한에는 두가지 종류가 있습니다.
Soft Limit : 새로운 프로그램이 생성되면 디폴트로 적용되는 제한
Hard Limit : Soft Limit에서 최대 늘릴 수 있는 제한
단, Hard Limit는 root 유저에 의해 조정 가능하지만 무한히 늘리 수 없기 때문에 커널에서 해당 설정 값을 조정 후 늘릴 수 있습니다.

[ oracle10g soft nproc 2047 ] oracle10g 라는 프로그램이 처음 수행시 기본값으로 사용하는 프로세스는 최대 2047로 제한한다는 의미 입니다.
[ oracle10g hard nproc 16384 ] oracld10g 라는 프로그램이 수행시 사용하는 프로세스는 최대 16384로 제한 한다는 의미 입니다.
[ oracle10g soft nofile 1024 ] oracle10g 라는 프로그램이 처음 수행시 기본값으로 사용하는 파일은 최대 1024로 제한 한다는 의미입니다.
[ oracle10g hard nofile 65536 ] oracle10g 라는 프로그램이 수행시 사용하는 파일은 최대 65536으로 제한 한다는 의미 입니다.

[의문점] 어떤 방식으로 해야 이상적인 설정 일까요?
이상적인 설정은 실제로 oracle database가 가동 되고 나서 개수 제한으로 에러가 발생 하지 않게 하는 것이 가장 이상적인 설정이라고 볼 수 있습니다. 그래서 최대한 해당 프로세스의 개수가 어느정도인지 파일의 개수는 평균적으로 어느정도 사용하는지 파악후 설정 해주는것이 바람직합니다.

그럼 특정 프로그램이 사용하는 프로세스의 개수를 테스트 해보도록 하겠습니다.
[2010-12-09 05:38:43]-[root@calmmass:/proc/sys/net/core]
# lsof -p 3094 |wc -l
    1370

열려있는 파일 개수 확인 하는 방법
[2010-12-09 05:39:01]-[root@calmmass:/proc/sys/net/core]
# sysctl -a |grep file
fs.file-max = 104857
fs.file-nr = 1995 472 104857

마지막으로 /etc/security/limits.conf 리소스 제한 설정파일에 oracle 설치시 필요한 설정 내역은 아래와 같습니다.
oracle10g soft nproc 2047
oracle10g hard nproc 16384
oracle10g soft nofile 1024
oracle10g hard nofile 65536

Oracle을 각 OS별 설치하면서 느낀 점은 위와 같은 설정 내역이 조금씩 다르다는 점입니다.
그렇다면 이러한 설정 내역을 모두 외우는것은 무의미하고 어떤 작업에 의해서 설정을 해야 하는지를 먼저 파악하는 것이 우선이라고 생각 합니다.
각 설정 파일의 역활과 오라클이 구동 되었을때 사용하는 용도를 보면 조금 접근하기기 편하지 않을까 생각 해봅니다.

저작자표시 비영리

'ORACLE > INSTALL' 카테고리의 다른 글

오라클 11G upgrade관련문서 (0)	2013.10.29
oracle silent install (0)	2012.05.11
오라 클 9.2.0.1에서 Patch 셋 올리기 (0)	2010.05.31
oracle enterprise manager 한글 버튼 깨짐 문제 (0)	2010.01.11
RAC 패치 절차 (0)	2009.12.15

Posted by [PineTree]

ORACLE/TUNING2012. 1. 10. 20:49

SQL 실행 계획 확인방법

SQL*Plus Autotrace 기능 활성화
1-1 EXPLAIN PLAN 이란?
사용자들이 SQL 문의 액세스 경로를 확인하고 튜닝을 할 수 있도록 SQL 문을 분석하고 해석하여 실행 계획을 수립한 후 실행 계획을 테이블(PLAN_TABLE)에 저장하도록 해주는 명령어 입니다.

1-2 PLUSTRACE 권한 생성
SQL> conn / as sysdba
Connected.
SQL> @?/sqlplus/admin/plustrce.sql
SQL>
SQL> drop role plustrace;
Role dropped.
SQL> create role plustrace;
Role created.
SQL>
SQL> grant select on v_$sesstat to plustrace;
Grant succeeded.
SQL> grant select on v_$statname to plustrace;
Grant succeeded.
SQL> grant select on v_$mystat to plustrace;
Grant succeeded.
SQL> grant plustrace to dba with admin option;
Grant succeeded.
SQL>
SQL> set echo off

1-3 해당 사용자에게 PLUSTRACE 권한 부여
SQL> grant PLUSTRACE to scott;
Grant succeeded.

1-4 해당 사용자로 로그인 후 PLAN Table 생성
SQL> conn scott/oracle
Connected.
SQL> @?/rdbms/admin/utlxplan.sql
Table created.

2. SQL*Plus Autotrace 사용 방법
2-1 Autotrace Mode 설정
SQL> show user;
USER is "SCOTT"
SQL> set autotrace on
SQL> set autotrace traceonly
SQL> set autotrace traceonly explain
SQL> set autotrace traceonly statistics

2-2 EXPLAIN PLAN SQL 실행
SQL> conn scott/oracle
Connected.
SQL> select dname from dept;
DNAME
--------------
ACCOUNTING
RESEARCH
SALES
OPERATIONS

Execution Plan
----------------------------------------------------------
Plan hash value: 3383998547
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 40 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DEPT | 4 | 40 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
68 recursive calls
0 db block gets
22 consistent gets
8 physical reads
0 redo size
488 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed

2-3 Autotrace Mode 해제
SQL> set autotrace off;

SQL 튜닝을 하게 되면 기본적을 가장 많이 사용 하는 툴입니다.
다음은 SQL Trace의 기능에 대해서 설명 하도록 하겠습니다.

저작자표시 비영리

'ORACLE > TUNING' 카테고리의 다른 글

SQL 튜닝 대상선정(10g)하기 (0)	2012.03.08
V$SQL BIND CAPTURE (0)	2012.03.08
SGA/PGA 튜닝 시 고려(검토)할 오라클 factor (0)	2011.12.16
SHARED POOL SIZE의 계산방법 (0)	2011.11.23
OWI를 활용한 shared pool 진단 및 튜닝 (0)	2011.11.23

Posted by [PineTree]

ORACLE/ADMIN2012. 1. 2. 14:32

Oracle Hang 상황에서 액티브 세션 리스트 얻기

Maxgauge와 같은 툴의 가장 큰 장점은 Oracle Hang 상황에서도 액티브 세션의 리스트를 얻을 수 있다는 것입니다. 대기 이벤트와 SQL 정보가 포함된 액티브 세션 목록이야말로 모든 성능 트러블슈팅의 시작입니다.

Maxgauge와 같은 툴이 Oracle Hang 상황에서도 데이터를 수집할 수 있는 것은 DMA(Direct Memory Access)를 사용하기 때문입니다. 이 방법은 Oracle의 COE(Center Of Expertise) 팀이 극한의 상황, 즉 SQL로 필요한 정보를 수집하지 못할 때 사용하던 방법입니다. 이것이 Maxgauge와 같은 툴 덕분에 보편화된 것입니다.

만일 Maxgauge와 같은 툴이 없다면 어떻게 해야 할까요? Oracle이 제공하는 ASHDUMP 기능을 Preliminary Connection과 함께 사용할 수 있습니다.

Preliminary Connection이란 SQL*Net 방식이 아닌 Direct Memory Access 방식으로 Connection을 맺는 것을 말합니다.
ASHDUMP는 액티브 세션 목록의 메모리 버전인 ASH(Active Session History)를 텍스트 파일에 기록하는 기능입니다.

따라서 위 두 기능을 같이 사용하면 마치 DMA 방식으로 액티브 세션의 목록을 얻는 것과 동일한 효과가 있습니다.

아래에 간단한 예제가 있습니다.

우선 Preliminary Connection을 맺습니다.

1
#> sqlplus -prelim sys/oracle@ukja1106 as sysdba 

2
  
3
SQL*Plus: Release 11.1.0.6.0 - Production on Mon Apr 26 09:43:35 2010 

4
  
5
Copyright (c) 1982, 2007, Oracle.  All rights reserved.

일반적인 쿼리는 동작하지 않습니다.

1
alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss'

2
* 

3
ERROR at line 1: 

4
ORA-01012: not logged on

5
Process ID: 0 

6
Session ID: 0 Serial number: 0

Preliminary Connection 상태에서 ASHDUMP를 수행합니다. 레벨(10)은 10분을 의미합니다. 즉 지난 10분간의 ASH를 의미합니다.

1
SYS@ukja1106> oradebug setmypid 

2
Statement processed. 

3
SYS@ukja1106> oradebug dump ashdump 10 

4
Statement processed. 

5
SYS@ukja1106> oradebug tracefile_name 

6
c:\oracle\diag\rdbms\ukja1106\ukja1106\trace\ukja1106_ora_12152.trc

Dump 파일의 내용은 다음과 같습니다. 11g부터는 액티브 세션 목록과 함께 해당 목록을 테이블로 로딩하는 스크립트까지 함께 제공합니다. Hang의 악몽이 지나간 후(대부분 리스타트) 사후 분석을 위한 것입니다.

001
Processing Oradebug command 'dump ashdump 10'

002
ASH dump 

003
<<<ACTIVE BEGIN HEADER DUMP TRACE PROCESS - HISTORY SESSION>>> 

004
**************** 

005
SCRIPT TO IMPORT 

006
**************** 

007
------------------------------------------ 

008
Step 1: Create destination table <ASHDUMP> 

009
------------------------------------------ 

010
CREATE TABLE ashdump AS

011
SELECT * FROM SYS.WRH$_ACTIVE_SESSION_HISTORY WHERE rownum < 0 

012
---------------------------------------------------------------- 

013
Step 2: Create the SQL*Loader control file <ASHLDR.CTL> as below 

014
---------------------------------------------------------------- 

015
load data  

016
infile * "str '\n####\n'"

017
append 

018
into table ashdump 

019
fields terminated by ',' optionally enclosed by '"'

020
(                                

021
SNAP_ID  CONSTANT 0           ,  

022
DBID                          ,  

023
INSTANCE_NUMBER               ,  

024
SAMPLE_ID                     ,  

025
SAMPLE_TIME TIMESTAMP ENCLOSED BY '"' AND '"' "TO_TIMESTAMP(:SAMPLE_TIME   ,'MM-DD-YYYY HH24:MI:SSXFF')"   ,  

026
SESSION_ID                    ,  

027
SESSION_SERIAL#               ,  

028
SESSION_TYPE                  ,  

029
USER_ID                       ,  

030
SQL_ID                        ,  

031
SQL_CHILD_NUMBER              ,  

032
SQL_OPCODE                    ,  

033
FORCE_MATCHING_SIGNATURE      ,  

034
TOP_LEVEL_SQL_ID              ,  

035
TOP_LEVEL_SQL_OPCODE          ,  

036
SQL_PLAN_HASH_VALUE           ,  

037
SQL_PLAN_LINE_ID              ,  

038
SQL_PLAN_OPERATION#           ,  

039
SQL_PLAN_OPTIONS#             ,  

040
SQL_EXEC_ID                   ,  

041
SQL_EXEC_START DATE 'MM/DD/YYYY HH24:MI:SS' ENCLOSED BY '"' AND '"' ":SQL_EXEC_START"   ,  

042
PLSQL_ENTRY_OBJECT_ID         ,  

043
PLSQL_ENTRY_SUBPROGRAM_ID     ,  

044
PLSQL_OBJECT_ID               ,  

045
PLSQL_SUBPROGRAM_ID           ,  

046
QC_INSTANCE_ID                ,  

047
QC_SESSION_ID                 ,  

048
QC_SESSION_SERIAL#            ,  

049
EVENT_ID                      ,  

050
SEQ#                          ,  

051
P1                            ,  

052
P2                            ,  

053
P3                            ,  

054
WAIT_TIME                     ,  

055
TIME_WAITED                   ,  

056
BLOCKING_SESSION              ,  

057
BLOCKING_SESSION_SERIAL#      ,  

058
CURRENT_OBJ#                  ,  

059
CURRENT_FILE#                 ,  

060
CURRENT_BLOCK#                ,  

061
CURRENT_ROW#                  ,  

062
CONSUMER_GROUP_ID             ,  

063
XID                           ,  

064
REMOTE_INSTANCE#              ,  

065
TIME_MODEL                    ,  

066
SERVICE_HASH                  ,  

067
PROGRAM                       ,  

068
MODULE                        ,  

069
ACTION                        ,  

070
CLIENT_ID                        

071
)                                

072
--------------------------------------------------- 

073
Step 3: Load the ash rows dumped in this trace file 

074
--------------------------------------------------- 

075
sqlldr userid/password control=ashldr.ctl data=<THIS_TRACE_FILENAME> errors=1000000 

076
--------------------------------------------------- 

077
<<<ACTIVE HEADER DUMP TRACE PROCESS - HISTORY SESSION END>>> 

078
<<<ACTIVE BEGIN DUMP TRACE PROCESS - HISTORY SESSION>>> 

079
#### 

080
58646642,1,12519562,"04-26-2010 09:44:01.822000000",160,1,2,0, 

081
"",0,0,0,"",0,0,0,0,0, 

082
0,"",0,0,0,0,0,0,0, 

083
3213517201,9858,0,3,1,0,69788,4294967295,0, 

084
4294967295,0,0,0,0,,0,0,165959219, 

085
"ORACLE.EXE (CKPT)","", 

086
"","" 

087
#### 

088
58646642,1,12519486,"04-26-2010 09:42:45.808000000",160,1,2,0, 

089
"",0,0,0,"",0,0,0,0,0, 

090
0,"",0,0,0,0,0,0,0, 

091
3213517201,9767,1,1,1,0,38825,4294967295,0, 

092
4294967295,0,0,0,0,,0,0,165959219, 

093
"ORACLE.EXE (CKPT)","", 

094
"","" 

095
#### 

096
58646642,1,12519416,"04-26-2010 09:41:35.770000000",160,1,2,0, 

097
"",0,0,0,"",0,0,0,0,0, 

098
0,"",0,0,0,0,0,0,0, 

099
4078387448,9683,3,3,3,0,11083,4294967295,0, 

100
4294967295,0,0,0,0,,0,0,165959219, 

101
"ORACLE.EXE (CKPT)","", 

102
"","" 

103
#### 

104
58646642,1,12519384,"04-26-2010 09:41:03.774000000",163,1,2,0, 

105
"",0,0,0,"",0,0,0,0,0, 

106
0,"",0,0,0,0,0,0,0, 

107
3176176482,40612,5,1,1000,999888,0,4294967291,0, 

108
4294967295,0,0,0,0,,0,0,165959219, 

109
"ORACLE.EXE (DIA0)","", 

110
"","" 

111
#### 

112
58646642,1,12519304,"04-26-2010 09:39:43.719000000",160,1,2,0, 

113
"",0,0,0,"",0,0,0,0,0, 

114
0,"",0,0,0,0,0,0,0, 

115
3213517201,9551,1,1,1,0,38798,4294967295,0, 

116
4294967295,0,0,0,0,,0,0,165959219, 

117
"ORACLE.EXE (CKPT)","", 

118
"","" 

119
#### 

120
58646642,1,12519265,"04-26-2010 09:39:04.663000000",163,1,2,0, 

121
"",0,0,0,"",0,0,0,0,0, 

122
0,"",0,0,0,0,0,0,0, 

123
3176176482,40493,5,1,1000,999941,0,4294967291,0, 

124
4294967295,0,0,0,0,,0,0,165959219, 

125
"ORACLE.EXE (DIA0)","", 

126
"","" 

127
#### 

128
58646642,1,12519183,"04-26-2010 09:37:42.554000000",160,1,2,0, 

129
"",0,0,0,"",0,0,0,0,0, 

130
0,"",0,0,0,0,0,0,0, 

131
3213517201,9406,0,1,1,0,54077,4294967295,0, 

132
4294967295,0,0,0,0,,0,0,165959219, 

133
"ORACLE.EXE (CKPT)","", 

134
"","" 

135
#### 

136
<<<ACTIVE DUMP TRACE PROCESS - HISTORY SESSION END>>> 

137
  
138
*** 2010-04-26 09:45:51.625 

139
Oradebug command 'dump ashdump 10' console output: <NONE>

사후 분석 용도로 사용되면 대단히 유용할 것입니다. Preliminary Connection에서는 전통적인 트러블슈팅 데이터인 Hang Analyze나 System State Dump도 사용 가능합니다. 단, Preliminary Connection와 oradebug는 비공식적인 지원 기능이기 때문에 철저한 테스트 후 사용해야 되며, 가능한 오라클 지원 엔지니어의 승인하에 사용해야 합니다.

더 자세한 정보들은 아래 문서를 참조하세요.

Preliminary Connection과 Direct Access
ML 노트: 243132.1

출처 : http://ukja.tistory.com/310

저작자표시 비영리

'ORACLE > ADMIN' 카테고리의 다른 글

Automatic Tuning of Undo_retention Causes Space Problems [ID 420525.1] (0)	2012.05.15
Configuring the Oracle Network Environment (0)	2012.05.11
오라클 버전간의 파라미터 변경사항 비교 (Oracle 9i, 10g, 11g) (0)	2011.12.28
오라클 모니터링 쿼리 (0)	2011.12.21
oracle inactive session 정리 (0)	2011.12.15

Posted by [PineTree]

ORACLE/TroubleShooting2011. 12. 28. 23:59

ora-4031 and shared pool duration

ora-4031_and_shared_pool_duration.pdf

저작자표시 비영리

'ORACLE > TroubleShooting' 카테고리의 다른 글

Ora-600 [Unable To Load Xdb Library] in AIX [ID 559911.1] (0)	2012.04.20
Memory Notification: Library Cache Object loaded into SGA (0)	2012.03.06
"Pmon Failed To Acquire Latch" Messages in Alert Log -Database Hung [ID 468740.1] (0)	2011.12.28
ORA-04031 (0)	2011.12.06
Share pool과 성능문제 (0)	2011.12.06

Posted by [PineTree]

ORACLE/TroubleShooting2011. 12. 28. 23:54

"Pmon Failed To Acquire Latch" Messages in Alert Log -Database Hung [ID 468740.1]

"Pmon Failed To Acquire Latch" Messages in Alert Log -Database Hung [ID 468740.1]

		수정 날짜 15-SEP-2010 유형 PROBLEM 상태 MODERATED

In this Document
  Symptoms
  Cause
  Solution
  References

Platforms: 1-914CU;

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 11.1.0.7 - Release: 10.2 to 11.1
Information in this document applies to any platform.
***Checked for relevance on 15-Sep-2010***

Symptoms

Database Instance hangs and connections to database using 'sqlplus' are also not possible.

Checking alert.log we see following messages

PMON failed to acquire latch, see PMON dump

Fri Oct 5 10:33:00 2007

PMON failed to acquire latch, see PMON dump

Fri Oct 5 10:34:05 2007

PMON failed to acquire latch, see PMON dump

Errors in file /dwrac/BDUMP/dwhp_pmon_1912834.trc:

This will also dump a systemstate dump and the location will be mentioned in alert.log

Also at OS level, we see that MMAN is consuming lot of CPU.

.

Cause

This issue was worked upon by development in

Bug 6488694 - DATABASE HUNG WITH PMON FAILED TO ACQUIRE LATCH MESSAGE

Bug 6488694 was closed as a duplicate of Bug 7039896.

Solution

Apply the patch for Bug 7039896.

Issue is fixed in:

Workarounds that can be used:

Disable Automatic Shared Memory Management (ASMM) i.e Setting SGA_TARGET=0

Set the init.ora parameter _enable_shared_pool_durations=false

References

'ORACLE > TroubleShooting' 카테고리의 다른 글

Memory Notification: Library Cache Object loaded into SGA (0)	2012.03.06
ora-4031 and shared pool duration (0)	2011.12.28
ORA-04031 (0)	2011.12.06
Share pool과 성능문제 (0)	2011.12.06
KGH: NO ACCESS" Memory Allocation (0)	2011.11.23

Posted by [PineTree]

ORACLE/ADMIN2011. 12. 28. 23:33

오라클 버전간의 파라미터 변경사항 비교 (Oracle 9i, 10g, 11g)

오라클이 버전업이 되면, 오라클의 내부 동작 방식을 제어하는 파라미터에 대한 변경사항이 발생하게 된다. 즉, 파라미터의 기본설정값이 변경되거나, 새로운 기능 추가로 인해 필요한 파라미터가 추가되거나, 더 이상 불필요한 파라미터가 사라지게 된다. 따라서 오라클 업그레이드시에는 이러한 파라미터의 변경사항들을 체크해보는 것이 필요하다. 본 문서는 오라클 9i, 10g, 11g에 대해서 각각의 버전간의 파라미터 값의 변경사항/추가/삭제 사항들을 정리하는것을 목적으로 한다.

오라클 9i, 10g, 11g 간의 파라미터 기본설정 값의 변경 내역

아래의 파일에 각 버전간에 기본설정 값이 변경된 내역을 정리하였다.

Oracle 버전별 기본설정값변경내역.xls

오라클 9i, 10g, 11g에서 추가되거나/사라진 파라미터 내역

오라클이 버전업되면 추가되어지는 파라미터가 수백개가 넘는다. 따라서 본 문서에 그 내용을 담는 대신 첨부되어진 파일을 참조하기 바란다.

Oracle 버전별 Parameter 비교.xls

저작자표시 비영리

'ORACLE > ADMIN' 카테고리의 다른 글

Configuring the Oracle Network Environment (0)	2012.05.11
Oracle Hang 상황에서 액티브 세션 리스트 얻기 (1)	2012.01.02
오라클 모니터링 쿼리 (0)	2011.12.21
oracle inactive session 정리 (0)	2011.12.15
oracle Shared Server 와 Dedicate Server 의 접속 방식 (0)	2011.12.13

Posted by [PineTree]

ORACLE/SCRIPT2011. 12. 21. 10:24

ORACLE Session Statistics 쿼리

Session Statistics

Here are some scripts related to Session Statistics .

Session I/O By User

SESSION I/O BY USER NOTES:

Username - Name of the Oracle process user

OS User - Name of the operating system user

PID - Process ID of the session

SID - Session ID of the session

Serial# - Serial# of the session

Physical Reads - Physical reads for the session

Block Gets - Block gets for the session

Consistent Gets - Consistent gets for the session

Block Changes - Block changes for the session

Consistent Changes - Consistent changes for the session

select	nvl(ses.USERNAME,'ORACLE PROC') username,
	OSUSER os_user,
	PROCESS pid,
	ses.SID sid,
	SERIAL#,
	PHYSICAL_READS,
	BLOCK_GETS,
	CONSISTENT_GETS,
	BLOCK_CHANGES,
	CONSISTENT_CHANGES
from	v$session ses, 
	v$sess_io sio
where 	ses.SID = sio.SID
order 	by PHYSICAL_READS, ses.USERNAME

CPU Usage By Session

CPU USAGE BY SESSION NOTES:

Username - Name of the user

SID - Session id

CPU Usage - CPU centiseconds used by this session (divide by 100 to get real CPU seconds)

select 	nvl(ss.USERNAME,'ORACLE PROC') username,
	se.SID,
	VALUE cpu_usage
from 	v$session ss, 
	v$sesstat se, 
	v$statname sn
where  	se.STATISTIC# = sn.STATISTIC#
and  	NAME like '%CPU used by this session%'
and  	se.SID = ss.SID
order  	by VALUE desc

Resource Usage By User

RESOURCE USAGE BY USER NOTES:

SID - Session ID

Username - Name of the user

Statistic - Name of the statistic

Value - Current value

select 	ses.SID,
	nvl(ses.USERNAME,'ORACLE PROC') username,
	sn.NAME statistic,
	sest.VALUE
from 	v$session ses, 
	v$statname sn, 
	v$sesstat sest
where 	ses.SID = sest.SID
and 	sn.STATISTIC# = sest.STATISTIC#
and 	sest.VALUE is not null
and 	sest.VALUE != 0            
order 	by ses.USERNAME, ses.SID, sn.NAME

Session Stats By Session

SESSION STAT NOTES:

Username - Name of the user

SID - Session ID

Statistic - Name of the statistic

Usage - Usage according to Oracle

select  nvl(ss.USERNAME,'ORACLE PROC') username,
	se.SID,
	sn.NAME stastic,
	VALUE usage
from 	v$session ss, 
	v$sesstat se, 
	v$statname sn
where  	se.STATISTIC# = sn.STATISTIC#
and  	se.SID = ss.SID
and	se.VALUE > 0
order  	by sn.NAME, se.SID, se.VALUE desc

Cursor Usage By Session

CURSOR USAGE BY SESSION NOTES:

Username - Name of the user

Recursive Calls - Total number of recursive calls

Opened Cursors - Total number of opened cursors

Current Cursors - Number of cursor currently in use

select 	user_process username,
	"Recursive Calls",
	"Opened Cursors",
	"Current Cursors"
from  (
	select 	nvl(ss.USERNAME,'ORACLE PROC')||'('||se.sid||') ' user_process, 
			sum(decode(NAME,'recursive calls',value)) "Recursive Calls",
			sum(decode(NAME,'opened cursors cumulative',value)) "Opened Cursors",
			sum(decode(NAME,'opened cursors current',value)) "Current Cursors"
	from 	v$session ss, 
		v$sesstat se, 
		v$statname sn
	where 	se.STATISTIC# = sn.STATISTIC#
	and 	(NAME  like '%opened cursors current%'
	or 	 NAME  like '%recursive calls%'
	or 	 NAME  like '%opened cursors cumulative%')
	and 	se.SID = ss.SID
	and 	ss.USERNAME is not null
	group 	by nvl(ss.USERNAME,'ORACLE PROC')||'('||se.SID||') '
)
orasnap_user_cursors
order 	by USER_PROCESS,"Recursive Calls"

User Hit Ratios

USER HIT RATIO NOTES:

Username - Name of the user

Consistent Gets - The number of accesses made to the block buffer to retrieve data in a consistent mode.

DB Blk Gets - The number of blocks accessed via single block gets (i.e. not through the consistent get mechanism).

Physical Reads - The cumulative number of blocks read from disk.

Logical reads are the sum of consistent gets and db block gets.

The db block gets statistic value is incremented when a block is read for update and when segment header blocks are accessed.

Hit ratio should be > 90%

select	USERNAME,
	CONSISTENT_GETS,
        BLOCK_GETS,
        PHYSICAL_READS,
        ((CONSISTENT_GETS+BLOCK_GETS-PHYSICAL_READS) / (CONSISTENT_GETS+BLOCK_GETS)) Ratio
from 	v$session, v$sess_io
where 	v$session.SID = v$sess_io.SID
and 	(CONSISTENT_GETS+BLOCK_GETS) > 0
and 	USERNAME is not null
order	by ((CONSISTENT_GETS+BLOCK_GETS-PHYSICAL_READS) / (CONSISTENT_GETS+BLOCK_GETS))

저작자표시 비영리

'ORACLE > SCRIPT' 카테고리의 다른 글

oracle 날짜 넣어서 익스포트 받기 (0)	2010.11.24
ORACLE 중복 데이터 확인 (0)	2010.05.28
OPTEIMIZING ORACLE OPTIMIZER 스크립트 모음 (0)	2010.04.28
ORACLE INDEX,TABLE정보 조회 (0)	2010.04.28
과도한 I/O 유발 쿼리 찾기 (0)	2010.04.15

Posted by [PineTree]

ORACLE/ADMIN2011. 12. 21. 10:17

오라클 모니터링 쿼리

모니터링스크립트.txt

오라클모니터링.doc

불량SQL을 찾을때와 락문제를 해결할때 도움이 될 것입니다.

유용하게 사용되길 바라며...

10분이내에 수행된 쿼리중 세션에 남아있는 쿼리

SELECT sql_text
  FROM v$sqltext a, v$session b
 WHERE a.hash_value = b.sql_hash_value
   AND logon_time <= to_date(to_char(sysdate,'yyyymmddhh24mi')-10,'yyyymmddhh24mi');

Buffer Cache Hit Ratio

SELECT ROUND(((1-(SUM(DECODE(name, 'physical reads', value,0))/
(SUM(DECODE(name, 'db block gets', value,0))+
(SUM(DECODE(name, 'consistent gets', value, 0))))))*100),2) || '%' "Buffer Cache Hit Ratio"
FROM V$SYSSTAT;

Library Cache Hit Ratio

SELECT (1-SUM (reloads)/SUM(pins))*100 "Library Cache Hit Ratio"
FROM V$LIBRARYCACHE;

Data Dictionary Cache Hit Ratio

SELECT (1-SUM(getmisses)/SUM(gets))*100 "Data Dictionary Hit Ratio"
FROM V$ROWCACHE;

테이블 스페이스 사용량

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 ;

오라클서버의 메모리

SELECT * FROM v$sgastat
 
SELECT pool, sum(bytes) "SIZE"
FROM v$sgastat
WHERE pool = 'shared pool'
GROUP BY pool

cpu를 많이 사용하는 쿼리문과 프로세스아이디,시리얼번호,머신 알아내기

SELECT c.sql_text
,b.SID
, b.SERIAL#
,b.machine
,b.OSUSER
,b.logon_time --이 쿼리를 호출한 시간
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 = '675958'
ORDER BY c.PIECE

cpu를 많이 사용하는 쿼리문과 프로세스아이디,시리얼번호,머신 알아내기2

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 = '171'
ORDER BY c.PIECE

프로세스 아이디를 이용하여 쿼리문 알아내기

SELECT c.sql_text
,b.SID
, b.SERIAL#
,b.machine
,b.OSUSER
,b.logon_time --이 쿼리를 호출한 시간
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 = '1708032' --1912870/
ORDER BY c.PIECE

세션 죽이기(SID,SERAIL#)
```
ALTER SYSTEM KILL SESSION '8,4093'
```

오라클 세션과 관련된 테이블

SELECT count(*) FROM v$session WHERE machine ='머신이름' AND schemaname ='스키마이름'

현재 커서 수 확인

SELECT sid, count(sid) cursor
FROM V$OPEN_CURSOR
WHERE user_name = 'ilips'
GROUP BY sid
ORDER BY cursor DESC
 
SELECT sql_text, count(sid) cnt
FROM v$OPEN_CURSOR
GROUP BY sql_text
ORDER BY cnt DESC
 
SELECT * FROM v$session_wait
 
SELECT sid, serial#, username, taddr, used_ublk, used_urec
FROM v$transaction t, v$session s
WHERE t.addr = s.taddr;
 
SELECT *  FROM sys.v_$open_cursor

V$LOCK 을 사용한 잠금 경합 모니터링

SELECT s.username, s.sid, s.serial#, s.logon_time,
    DECODE(l.type, 'TM', 'TABLE LOCK',
      'TX', 'ROW LOCK',
       NULL) "LOCK LEVEL",
    o.owner, o.object_name, o.object_type
FROM v$session s, v$lock l, dba_objects o
WHERE s.sid = l.sid
AND o.object_id = l.id1
AND s.username IS NOT NULL

락이 걸린 세션 자세히 알아보기

SELECT a.sid, a.serial#,a.username,a.process,b.object_name,
decode(c.lmode,2,'RS',3,'RX',4,'S',5,'SRX',8,'X','NO') "TABLE LOCK",
decode (a.command,2,'INSERT',3,'SELECT',6,'UPDATE',7,'DELETE',12,'DROP TABLE',26,'LOCK TABLE','UNknown') "SQL",
decode(a.lockwait, NULL,'NO wait','Wait') "STATUS"
FROM v$session a,dba_objects b, v$lock c
WHERE a.sid=c.sid AND b.object_id=c.id1
AND c.type='TM'

락이 걸린 세션 간단히 알아보기

SELECT a.sid, a.serial#, b.type, c.object_name, a.program, a.lockwait,
       a.logon_time, a.process, a.osuser, a.terminal
FROM v$session a, v$lock b, dba_objects c
WHERE a.sid = b.sid
  AND b.id1 = c.object_id
  AND b.type = 'TM';
SELECT a.sid, a.serial#, a.username, a.process, b.object_name
FROM v$session a , dba_objects b, v$lock c
WHERE a.sid=c.sid AND b.object_id = c.id1
AND c.type = 'TM'

락이 걸린 세션을 찾아 내어 세션을 죽이려고 해도 죽지 않는 경우
아래 쿼리문으로 OS단의 PROCESS ID를 찾아내어 OS에서 죽인다.

 # kill -9 프로세스아이디

SELECT substr(s.username,1,11) "ORACLE USER", p.pid "PROCESS ID",
s.sid "SESSION ID", s.serial#, osuser "OS USER",
p.spid "PROC SPID",s.process "SESS SPID", s.lockwait "LOCK WAIT"
FROM v$process p, v$session s, v$access a
WHERE a.sid=s.sid AND
p.addr=s.paddr AND
s.username != 'SYS'

위 쿼리문의 결과가 있다면 락이 걸린 세션이 있다는것이므로 아래의 쿼리문으로 세션을 죽인다.

ALTER SYSTEM KILL SESSION '11,39061'

출처 : http://adminschool.net/wiki/doku.php?id=dbms:oracle:admin:managequery:sql01

SELECT * FROM dba_External_Locations
SELECT * FROM nls_database_parameters

--1. Buffer Cache Hit Ratio

SELECT ROUND(((1-(SUM(DECODE(name, 'physical reads', value,0))/
(SUM(DECODE(name, 'db block gets', value,0))+
(SUM(DECODE(name, 'consistent gets', value, 0))))))*100),2) || '%' "Buffer Cache Hit Ratio"
FROM V$SYSSTAT;

--2. Library Cache Hit Ratio

SELECT (1-SUM (reloads)/SUM(pins))*100 "Library Cache Hit Ratio"
From V$LIBRARYCACHE;

--3. Data Dictionary Cache Hit Ratio

SELECT (1-SUM(getmisses)/SUM(gets))*100 "Data Dictionary Hit Ratio"
FROM V$ROWCACHE;

-- 테이블 스페이스 사용량

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;

--오라클서버의 메모리

select * from v$sgastat

select pool, sum(bytes) "SIZE"
from v$sgastat
where pool = 'shared pool'
group by pool

--cpu를 많이 사용하는 쿼리문과 프로세스아이디,시리얼번호,머신 알아내기

select c.sql_text
,b.SID
, b.SERIAL#
,b.machine
,b.OSUSER
,b.logon_time --이 쿼리를 호출한 시간
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 = '675958'
order by c.PIECE

--cpu를 많이 사용하는 쿼리문과 프로세스아이디,시리얼번호,머신 알아내기

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 = '171'
order by c.PIECE

--프로세스 아이디를 이용하여 쿼리문 알아내기

--세션 죽이기(SID,SERAIL#)

--ALTER SYSTEM KILL SESSION '8,4093'

--오라클 세션과 관련된 테이블*/

--select count(*) from v$session where machine ='머신이름' and schemaname ='스키마이름'

--현재 커서 수 확인

SELECT sid, count(sid) cursor
FROM V$OPEN_CURSOR
WHERE user_name = 'ilips'
GROUP BY sid
ORDER BY cursor DESC

SELECT sql_text, count(sid) cnt
FROM v$OPEN_CURSOR
GROUP BY sql_text
ORDER BY cnt DESC

select * from v$session_wait

select sid, serial#, username, taddr, used_ublk, used_urec
from v$transaction t, v$session s
where t.addr = s.taddr;

select * from sys.v_$open_cursor

--V$LOCK 을 사용한 잠금 경합 모니터링

SELECT s.username, s.sid, s.serial#, s.logon_time,
    DECODE(l.type, 'TM', 'TABLE LOCK',
          'TX', 'ROW LOCK',
       NULL) "LOCK LEVEL",
    o.owner, o.object_name, o.object_type
FROM v$session s, v$lock l, dba_objects o
WHERE s.sid = l.sid
AND o.object_id = l.id1
AND s.username IS NOT NULL

--락이 걸린 세션 자세히 알아보기

select a.sid, a.serial#,a.username,a.process,b.object_name,
decode(c.lmode,2,'RS',3,'RX',4,'S',5,'SRX',8,'X','NO') "TABLE LOCK",
decode (a.command,2,'INSERT',3,'SELECT',6,'UPDATE',7,'DELETE',12,'DROP TABLE',26,'LOCK TABLE','UNknown') "SQL",
decode(a.lockwait, NULL,'NO wait','Wait') "STATUS"
from v$session a,dba_objects b, v$lock c
where a.sid=c.sid and b.object_id=c.id1
and c.type='TM'

--락이 걸린 세션 간단히 알아보기

select a.sid, a.serial#, b.type, c.object_name, a.program, a.lockwait,
a.logon_time, a.process, a.osuser, a.terminal
from v$session a, v$lock b, dba_objects c
where a.sid = b.sid
and b.id1 = c.object_id
and b.type = 'TM';

select a.sid, a.serial#, a.username, a.process, b.object_name
from v$session a , dba_objects b, v$lock c
where a.sid=c.sid and b.object_id = c.id1
and c.type = 'TM'

--락이 걸린 세션을 찾아 내어 세션을 죽이려고 해도 죽지 않는 경우
--아래 쿼리문으로 OS단의 PROCESS ID를 찾아내어 OS에서 죽인다
--kill -9 프로세스아이디

select substr(s.username,1,11) "ORACLE USER", p.pid "PROCESS ID",
s.sid "SESSION ID", s.serial#, osuser "OS USER",
p.spid "PROC SPID",s.process "SESS SPID", s.lockwait "LOCK WAIT"
from v$process p, v$session s, v$access a
where a.sid=s.sid and
p.addr=s.paddr and
s.username != 'SYS'

--위 쿼리문의 결과가 있다면 락이 걸린 세션이 있다는것이므로 아래의 쿼리문으로 세션을 죽인다

ALTER SYSTEM KILL SESSION '11,39061'

alter session으로 죽지않는 프로세스 죽이기

1.oracle이 설치된 서버에 텔넷으로 root로 접속한다
2.su -오라클계정
3.sqlplus '/as sysdba''
4.connect system/sys
5.ALTER SYSTEM KILL SESSION '137,1723'

-- 특정 테이블에 사용된 쿼리
SELECT * FROM V$SQLAREA
WHERE parsing_schema_name = 'ING'
AND lower(sql_text) LIKE '%order_result_report_tbl%'
ORDER BY last_active_time DESC

-- 현재 세션에서 사용되는 쿼리
SELECT S.SID
     , S.SERIAL#
     , S.STATUS
     , S.PROCESS
     , S.OSUSER
     , A.SQL_TEXT
     , P.PROGRAM
FROM V$SESSION S
     , V$SQLAREA A
     , V$PROCESS P
WHERE S.SQL_HASH_VALUE = A.HASH_VALUE
AND S.SQL_ADDRESS    = A.ADDRESS
AND S.PADDR          = P.ADDR
AND S.SCHEMANAME     = 'ING'
AND S.STATUS         = 'ACTIVE'

--
select
sql_text,
sharable_mem + persistent_mem + runtime_mem memory,
sorts,
executions,
first_load_time,
invalidations,
parse_calls,
disk_reads,
buffer_gets,
rows_processed,
round(rows_processed/greatest(executions,1)) row_ratio,
round(disk_reads/greatest(executions,1)) disk_ratio,
round(buffer_gets/greatest(executions,1)) buffer_ratio
from v$sqlarea
where
executions > 100
or disk_reads > 1000
or buffer_gets > 1000
or rows_processed > 1000
order by
executions * 250 + disk_reads * 25 + buffer_gets desc

--
SELECT
    PARSING_SCHEMA_NAME,
    executions,
    SQL_TEXT,
disk_reads,
buffer_gets,
    elapsed_time / executions AS elapsed_time,
    buffer_gets / decode(executions,0,1,executions) / 500 AS runtime,
    bind_data,
    MODULE,
    sharable_mem,
    persistent_mem,
    runtime_mem,
rows_processed,
round(rows_processed/greatest(executions,1)) row_ratio,
round(disk_reads/greatest(executions,1)) disk_ratio,
round(buffer_gets/greatest(executions,1)) buffer_ratio,
    LAST_LOAD_TIME,
    LAST_ACTIVE_TIME
FROM
    V$SQLAREA
WHERE
    LAST_LOAD_TIME BETWEEN SYSDATE - 1 AND SYSDATE
    AND PARSING_SCHEMA_NAME IN('WORLDGATE')
    AND SQL_TEXT LIKE 'SELECT%'
ORDER BY
    buffer_gets / decode(executions,0,1,executions) / 500 DESC



-- 10초 이상
select translate(sql_text,'&',':') ,
' Expected Run Time = '||
buffer_gets / decode(executions,0,1,executions) / 500 runt

from v$sqlarea
where buffer_gets / decode(executions,0,1,executions) / 500 > 10
and upper(sql_text) not like '%BEGIN%'
and upper(sql_text) not like '%SQLAREA%'
and upper(sql_text) not like '%DBA_%'
and upper(sql_text) not like '%USER_%'
and upper(sql_text) not like '%ALL_%'
order by executions desc

평균 메모리 사용량이 많은 SQL (상위 N개)

SELECT BUFFER_GETS,DISK_READS,

EXECUTIONS,BUFFER_PER_EXEC,SQL_TEXT

FROM ( SELECT BUFFER_GETS,DISK_READS, EXECUTIONS,

BUFFER_GETS/DECODE(EXECUTIONS,0,1,EXECUTIONS) BUFFER_PER_EXEC,

SQL_TEXT

FROM V$SQLAREA

WHERE LAST_ACTIVE_TIME > trunc(SYSDATE)-8/24
AND ROWNUM <= 5
ORDER BY BUFFER_GETS/DECODE(EXECUTIONS,0,1,EXECUTIONS) DESC )

총 메모리 사용량이 많은 SQL (상위 N개)

SELECT BUFFER_GETS,DISK_READS,EXECUTIONS,SQL_TEXT

FROM (SELECT BUFFER_GETS,DISK_READS,EXECUTIONS,SQL_TEXT

FROM V$SQLAREA

ORDER BY BUFFER_GETS DESC )

WHERE ROWNUM <= 5

저작자표시 비영리

'ORACLE > ADMIN' 카테고리의 다른 글

Oracle Hang 상황에서 액티브 세션 리스트 얻기 (1)	2012.01.02
오라클 버전간의 파라미터 변경사항 비교 (Oracle 9i, 10g, 11g) (0)	2011.12.28
oracle inactive session 정리 (0)	2011.12.15
oracle Shared Server 와 Dedicate Server 의 접속 방식 (0)	2011.12.13
Shared Server (MTS) Diagnostics [ID 1005259.6] (0)	2011.12.08

Posted by [PineTree]

«이전 1 ··· 19 20 21 22 23 24 25 ··· 83 다음»

'분류 전체보기'에 해당되는 글 829건

'ORACLE' 카테고리의 다른 글

'ORACLE > TroubleShooting' 카테고리의 다른 글

'ORACLE > INSTALL' 카테고리의 다른 글

'ORACLE > TUNING' 카테고리의 다른 글

'ORACLE > ADMIN' 카테고리의 다른 글

'ORACLE > TroubleShooting' 카테고리의 다른 글

Applies to:

Symptoms

Cause

Solution

References

'ORACLE > TroubleShooting' 카테고리의 다른 글

'ORACLE > ADMIN' 카테고리의 다른 글

Session Statistics

Session I/O By User

CPU Usage By Session

Resource Usage By User

Session Stats By Session

Cursor Usage By Session

User Hit Ratios

'ORACLE > SCRIPT' 카테고리의 다른 글

'ORACLE > ADMIN' 카테고리의 다른 글

카테고리

공지사항

태그목록

최근에 올라온 글

최근에 달린 댓글

최근에 받은 트랙백

글 보관함

달력

링크

티스토리툴바