Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 PL/SQL sessions  [new]
Cotton
Member

Откуда: Киев
Сообщений: 202
Подскажите, где можна почитать (или просто подскажите :) ) о значении полей в PL/SQL'ом Sessions. В частности интересуют значение полей Blocking Session status (значение "Not in wait"), P1, P2, P3 (-raw,-text), Wait class (значение "USER I/O"), State (значение "Waited short time").
--------
Любовь и боль
Покой и бой
Я как любой
Несу с собой
13 фев 07, 16:47    [3779278]     Ответить | Цитировать Сообщить модератору
 Re: PL/SQL sessions  [new]
OracleX
Member

Откуда:
Сообщений: 1998
Я не спец, но поисследуйте представление GV_$ACTIVE_SESSION_HISTORY в гриде
(например, в TOAD, чтобы быстро сортировать и переставлять колонки).
Kyle Hailey (когда 10G еще не было)
The V$ACTIVE_SESSION_HISTORY view provides sampled session activity in the instance. Active sessions are sampled every second and are stored in a circular buffer in SGA. Any session that is connected to the database and is waiting for an event that does not belong to the Idle wait class is considered as an active session. This includes any session that was on the CPU at the time of sampling.
Each session sample is a set of rows and the V$ACTIVE_SESSION_HISTORY view returns one row for each active session per sample, returning the latest session sample rows first. Because the active session samples are stored in a circular buffer in SGA, the greater the system activity, the smaller the number of seconds of session activity that can be stored in the circular buffer. This means that the duration for which a session sample appears in the V$ view, or the number of seconds of session activity that is displayed in the V$ view, is completely dependent on the database activity.
As part of the Automatic Workload Repository (AWR) snapshots, the content of V$ACTIVE_SESSION_HISTORY is also flushed to disk. Because the content of this V$ view can get quite large during heavy system activity, only a portion of the session samples is written to disk.
By capturing only active sessions, a manageable set of data is represented with the size being directly related to the work being performed rather than the number of sessions allowed on the system. Using the Active Session History enables you to examine and perform detailed analysis on both current data in the V$ACTIVE_SESSION_HISTORY view and historical data in the DBA_HIST_ACTIVE_SESS_HISTORY view, often avoiding the need to replay the workload to gather additional performance tracing information. The data present in ASH can be rolled up on various dimensions that it captures, including the following:
SQL identifier of SQL statement
Object number, file number, and block number
Wait event identifier and parameters
Session identifier and session serial number
Module and action name
Client identifier of the session
Service hash identifier

Есть закономерность:
IF (SESSION_STATE='ON CPU') OR (SESSION_STATE='WAITING' AND EVENT='null event') THEN
  BLOCKING_SESSION_STATUS:='NOT IN WAIT';
END IF;
Аналогично можно проанализировать сочетание значений в различных колонках совместно с:
  P1TEXT  VARCHAR2(64),
  P2TEXT  VARCHAR2(64),
  P3TEXT  VARCHAR2(64),
  WAIT_CLASS  VARCHAR2(64),
13 фев 07, 20:53    [3780742]     Ответить | Цитировать Сообщить модератору
 Re: PL/SQL sessions  [new]
OracleX
Member

Откуда:
Сообщений: 1998
Oracle 10g AWR (Automatic Workload Repository) -Automatic SQL Tuning Feature
13 фев 07, 21:15    [3780780]     Ответить | Цитировать Сообщить модератору
 Re: PL/SQL sessions  [new]
1
Guest
Там внизу в закладках вроде должен быть запрос прописан явно.
13 фев 07, 22:52    [3780964]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить