코봉이와 오라클: OWI

레이블이 OWI인 게시물을 표시합니다. 모든 게시물 표시

2009년 7월 25일 토요일

Hidden Prameter 보는법

▣ 검색 키워드 : show_param.sql

→ 일반+hidden 같이 나옴

▣ 관련질의

col KSPPINM form a40;

col KSPPSTVL form a40;

select ksppinm,ksppstvl

from x$ksppi x,x$ksppcv y

where(x.indx=y.indx)

and (translate(ksppinm,'_','#') like '%&1%')

2009년 7월 23일 목요일

V$SESSION_WAIT_HISTORY

V$SESSION_WAIT_HISTORY

V$SESSION_WAIT_HISTORY displays the last 10 wait events for each active session.

Column	Datatype	Description
SID	NUMBER	Session identifier
SEQ#	NUMBER	Sequence of wait events; 1 is the most recent
EVENT#	NUMBER	Event number
EVENT	VARCHAR2(64)	Resource or event for which the session is waiting
P1TEXT	VARCHAR2(64)	Description of the first additional parameter
P1	NUMBER	First additional parameter
P2TEXT	VARCHAR2(64)	Description of the second additional parameter
P2	NUMBER	Second additional parameter
P3TEXT	VARCHAR2(64)	Description of the third additional parameter
P3	NUMBER	Third additional parameter
WAIT_TIME	NUMBER	A nonzero value is the session's last wait time. A zero value means the session is currently waiting.

V$EVENT_HISTOGRAM displays a histogram of the number of waits, the maximum wait, and total wait time on an event basis. The histogram has buckets of time intervals from < 1 ms, < 2 ms, < 4 ms, < 8 ms, ... < 2^21 ms, < 2^22 ms, >= 2^22 ms.

The histogram will not be filled unless the TIMED_STATISTICS initialization parameter is set to true.

Column	Datatype	Description
EVENT#	NUMBER	Event number
EVENT	VARCHAR2(64)	Name of the Event
WAIT_TIME_MILLI	NUMBER	Amount of time the bucket represents (in milliseconds). If the duration = num, then this column represents waits of duration < num that are not included in any smaller bucket.
WAIT_COUNT	NUMBER	Number of waits of the duration belonging to the bucket of the histogram

질의

select wait_time_milli,wait_count from V_$EVENT_HISTOGRAM

where event='db file sequential read'

V_$SESSION_WAIT 예

※ 참고

락발생

v$event_name

Wait_class가 application 인 event 검색

※ 참고

v$event_name과 v$session_wait의 관계

V_$SESSION_WAIT

V$SESSION_WAIT

V$SESSION_WAIT displays the resources or events for which active sessions are waiting.

The following are tuning considerations:

P1RAW, P2RAW, and P3RAW display the same values as the P1, P2, and P3 columns, except that the numbers are displayed in hexadecimal.
The WAIT_TIME column contains a value of -2 on platforms that do not support a fast timing mechanism. If you are running on one of these platforms and you want this column to reflect true wait times, then you must set the TIMED_STATISTICS initialization parameter to true. Remember that doing this has a small negative effect on system performance.
In previous releases, the WAIT_TIME column contained an arbitrarily large value instead of a negative value to indicate the platform did not have a fast timing mechanism.
The STATE column interprets the value of WAIT_TIME and describes the state of the current or most recent wait.

Column	Datatype	Description
SID	NUMBER	Session identifier
SEQ#	NUMBER	Sequence number that uniquely identifies this wait. Incremented for each wait.
EVENT	VARCHAR2(64)	Resource or event for which the session is waiting See Also: Appendix C, "Oracle Wait Events"
P1TEXT	VARCHAR2(64)	Description of the first additional parameter
P1	NUMBER	First additional parameter
P1RAW	RAW(4)	First additional parameter
P2TEXT	VARCHAR2(64)	Description of the second additional parameter
P2	NUMBER	Second additional parameter
P2RAW	RAW(4)	Second additional parameter
P3TEXT	VARCHAR2(64)	Description of the third additional parameter
P3	NUMBER	Third additional parameter
P3RAW	RAW(4)	Third additional parameter
WAIT_CLASS_ID	NUMBER	Identifier of the wait class
WAIT_CLASS#	NUMBER	Number of the wait class
WAIT_CLASS	VARCHAR2(64)	Name of the wait class
WAIT_TIME	NUMBER	A nonzero value is the session's last wait time. A zero value means the session is currently waiting.
SECONDS_IN_WAIT	NUMBER	If WAIT_TIME = 0, then SECONDS_IN_WAIT is the seconds spent in the current wait condition. If WAIT_TIME > 0, then SECONDS_IN_WAIT is the seconds since the start of the last wait, and SECONDS_IN_WAIT - WAIT_TIME / 100 is the active seconds since the last wait ended.
STATE	VARCHAR2(19)	Wait state: 0 - WAITING (the session is currently waiting) -2 - WAITED UNKNOWN TIME (duration of last wait is unknown) -1 - WAITED SHORT TIME (last wait <1/100th of a second) >0 - WAITED KNOWN TIME (WAIT_TIME = duration of last wait)

V$SERVICE_WAIT_CLASS

V$SERVICE_WAIT_CLASS

V$SERVICE_WAIT_CLASS displays aggregated wait counts and wait times for each wait statistic. An aggregation of these wait classes is used when thresholds are imported.

Column	Datatype	Description
SERVICE_NAME	VARCHAR2(64)	Service name from V$SERVICES
SERVICE_NAME_HASH	NUMBER	Service name hash from V$SERVICES
WAIT_CLASS_ID	NUMBER	Identifier of the wait class
WAIT_CLASS#	NUMBER	Number of the wait class
WAIT_CLASS	VARCHAR2(64)	Name of the wait class
TOTAL_WAITS	NUMBER	Number of times waits of the class occurred for this client
TIME_WAITED	NUMBER	Amount of time, in hundreths of a second, spent in the class by this session

V_$SERVICE_EVENT

컬럼명	설명	예
SERVICE_NAME	Service name from V$SERVICES
SERVICE_NAME_HASH	Service name hash from V$SERVICES
EVENT	Name of the wait event; derived statistic name from V$EVENT_NAME
EVENT_ID	Identifier of the event
TOTAL_WAITS	Total amount of time waited for the event by this service (in hundredths of a second)
TOTAL_TIMEOUTS	Total number of timeouts for the event by this service
TIME_WAITED	Time waited for the event (in hundredths of a second)
AVERAGE_WAIT	Average amount of time waited for the event by this service (in hundredths of a second)
MAX_WAIT	Maximum time (in hundredths of a second) waited for the event by this service
TIME_WAITED_MICRO	Total time waited for the event (in microseconds)

V$SESSION_WAIT_CLASS

V$SESSION_WAIT_CLASS

V$SESSION_WAIT_CLASS displays the time spent in various wait event operations on a per-session basis.

Column	Datatype	Description
SID	NUMBER	Session identifier
SERIAL#	NUMBER	Serial number
WAIT_CLASS_ID	NUMBER	Identifier of the wait class
WAIT_CLASS#	NUMBER	Number of the wait class
WAIT_CLASS	VARCHAR2(64)	Name of the wait class
TOTAL_WAITS	NUMBER	Number of times waits of the class occurred for the session
TIME_WAITED	NUMBER	Amount of time spent in the wait class by the session

V_$SESSION_EVENT

컬럼명	설명	예
SID	ID of the session
EVENT	Name of the wait event
TOTAL_WAITS	Total number of waits for the event by the session
TOTAL_TIMEOUTS	Total number of timeouts for the event by the session
TIME_WAITED	Total amount of time waited for the event by the session (in hundredths of a second)
AVERAGE_WAIT	Average amount of time waited for the event by the session (in hundredths of a second)
MAX_WAIT	Maximum time waited for the event by the session (in hundredths of a second)
TIME_WAITED_MICRO	Total amount of time waited for the event by the session (in microseconds)
EVENT_ID	Identifier of the wait event
WAIT_CLASS_ID
WAIT_CLASS#
WAIT_CLASS

Sys> get sessionEvent

col event form a50;

select event,total_waits,wait_class from v$session_event

where sid=&sid

Sys> get topSessionEvent

col event form a50;

col wait_class form a20;

select * from (

select sid,event,total_waits,wait_class

from v$session_event s

where (select username from v$session where sid=s.sid) is not null

and sid not in (

select sid from v$session where

username in ('SYS','SYSMAN','DBSNMP')

)

order by total_waits desc

)

where rownum<10

V$SYSTEM_WAIT_CLASS

V$SYSTEM_WAIT_CLASS

V$SYSTEM_WAIT_CLASS displays the instance-wide time totals for each registered wait class.

Column	Datatype	Description
WAIT_CLASS_ID	NUMBER	Identifier of the wait class
WAIT_CLASS#	NUMBER	Number of the wait class
WAIT_CLASS	VARCHAR2(64)	Name of the wait class
TOTAL_WAITS	NUMBER	Number of times waits of the class occurred
TIME_WAITED	NUMBER	Amount of time spent in the wait by all sessions in the instance

V_$SYSTEM_EVENT

컬럼명	설명	예
EVENT	Name of the wait event
TOTAL_WAITS	Total number of waits for the event
TOTAL_TIMEOUTS	Total number of timeouts for the event
TIME_WAITED	Total amount of time waited for the event (in hundredths of a second)
AVERAGE_WAIT	Average amount of time waited for the event (in hundredths of a second)
TIME_WAITED_MICRO	Total amount of time waited for the event (in microseconds)
EVENT_ID	Identifier of the wait event
WAIT_CLASS_ID
WAIT_CLASS#
WAIT_CLASS