当前位置：网站首页>Common views of Oracle database performance analysis

Common views of Oracle database performance analysis

2022-04-23 06:59:00 【Sebastien23】

Oracle Common views of database performance analysis

`v$session` & `gv$session`
`v$active_session_history`
`dba_hist_active_sess_history`

`v$session` & `gv$session`

v$session and gv$session Only in RAC Obvious difference in ： Yes g Is a global （global）, contain RAC The contents of two examples of ;v$session Only the data of the current instance of this node is included . In terms of content ,gv$session Only than v$session More than a INST_ID It's just a field .

v$session The dynamic performance view records the information of each session connected to the database instance , Include users session、 And background processes 、 Application process and so on .

The following table shows some important columns in this view .

Column	describe
SADDR	session Address
SID	session identifier
SERIAL#	session Serial number , Used to uniquely identify a session Object to which . To ensure the session Level commands will not be in session Apply to another with the same session ID Conversation
PADDR	session The address of the process to which it belongs
USER#	Oracle User identifier
USERNAME	Oracle user name
COMMAND	Running command . This column is a number n, perform `select command_name from v$sqlcommand where command_type = n` You can query the command name
TADDR	The address of the transaction status object
LOCKWAIT	The session The address of the lock waiting .NULL It means that there is no
STATUS	session state ：ACTIVE It means that it is executing SQL;INACTIVE Indicates inactive session;KILLED Express session Marked as killed; CACHED Express session Temporarily cached ;SNIPED Indicates inactive session Some configuration limits have been exceeded , For example, resource constraints 、 Free time limit, etc , In this way session Will not be allowed to return to active
SERVER	Server type , Include Dedicated、Shared、Pseudo、Pooled、None
SCHEMA#	Schema User identifier
SCHEMANAME	Schema user name
OSUSER	Operating system client user name
PROCESS	Operating system client process PID
PORT	Client slogans
PROGRAM	Operating system program name
TYPE	session type
SQL_ADDRESS	And SQL_HASH_VALUE Together, it can be used to determine what is currently being performed SQL sentence
SQL_HASH_VALUE	And SQL_ADDRESS Together, it can be used to determine what is currently being performed SQL sentence
SQL_ID	What is currently being implemented SQL Identifier of the statement
SQL_CHILD_NUMBER	What is currently being implemented SQL Subnumber of the statement
SQL_EXEC_START	The session Currently executed SQL The time when the statement begins to execute
PLSQL_ENTRY_OBJECT_ID	At the top of the stack PL/SQL The object of the subroutine ID
PLSQL_OBJECT_ID	Currently executed PL/SQL The object of the subroutine ID
ROW_WAIT_OBJ#	Contains ROW_WAIT_ROW# Specifies the object of the table for the row ID
ROW_WAIT_FILE#	Contains ROW_WAIT_ROW# Specifies the identifier of the row's data file
ROW_WAIT_BLOCK#	Contains ROW_WAIT_ROW# Specifies the identifier of the data block of the row
ROW_WAIT_ROW#	Currently locked row , This column only corresponds to session Waiting for another transaction to commit 、 And ROW_WAIT_OBJ# The value of is not equal to -1 Only when effective
LOGON_TIME	Time logged in
LAST_CALL_ET	The session Be active / The time of inactivity （elapsed time）, The unit is seconds
CLIENT_IDENTIFIER	The session User identifier for
BLOCKING_SESSION_STATUS	session Blocked state ：VALID Express session Blocked ;NO HOLDER It means that we should session Not by other session Blocking ; NOT IN WAIT Express session Not in a waiting state ;UNKNOWN It means unknown
BLOCKING_INSTANCE	Causing obstruction session Instance identifier of
BLOCKING_SESSION	Causing obstruction session Identifier
SEQ#	Sequence number that uniquely identifies the current or previous wait event
EVENT#	The session The sequence number of the waiting resource or event ; If it's time to session Not in a waiting state , Indicates the sequence number of the last waiting resource or event
EVENT	The session Waiting resources or events ; If it's time to session Not in a waiting state , Indicates the last waiting resource or event
P1	The first waiting time parameter , Decimal means
P1TEXT	Description of the first wait event parameter
WAIT_CLASS_ID	Class identifier of the waiting event
WAIT_TIME	If session Is waiting for , The value is 0; If session Not waiting , And the value is a positive number , Indicates the duration of the last wait , It's in hundredths of a second
SECONDS_IN_WAIT	If session Is waiting for , Indicates the waiting time ; If session Not waiting , Indicates the elapsed time since the last wait
STATE	Wait state ：WAITING Waiting for ;WAITED UNKNOWN TIME Indicates the last waiting time is unknown ;WAITED SHORT TIME Indicates that the last wait was less than one hundredth of a second ;WAITED KNOWN TIME Indicates that the last waiting time was WAIT_TIME
SERVICE_NAME	The session Service name of
SQL_TRACE	SQL Trace Open or not

For more: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/refrn/V-SESSION.html#GUID-28E2DC75-E157-4C0A-94AB-117C205789B9

`v$active_session_history`

Dynamic performance view v$active_session_history and gv$active_session_history Meeting Per second The clock will all nodes of the database Active Session Take a sample . similarly , With g The first view contains RAC Information of all instances in the cluster .

The following table shows some important columns in this view , Most of them are listed in v$session There have been... In the view .

Column	describe
SAMPLE_ID	sampling ID
SAMPLE_TIME	The time of sampling
IS_AWR_SAMPLE	Whether the sample has been or will be brushed into the automatic load warehouse （AWR） in
SESSION_ID	session identifier , Mapping to v$session.SID
SESSION_SERIAL#	session Serial number , Mapping to v$session.SERIAL#
SESSION_TYPE	session type , It is divided into FOREGROUND and BACKGROUND
SQL_ID	When sampling ,session Ongoing SQL Identifier of the statement
SQL_PLAN_HASH_VALUE	Cursors SQL plan The number of represents .v$session There is no data in this column
EVENT	If SESSION_STATE=WAITING, Indicates when sampling session Waiting Events ; If SESSION_STATE=ON CPU, The column value is NULL
SEQ#	The serial number that uniquely identifies the waiting event
XID	When sampling ,session Transactions being processed ID.v$session There is no data in this column
IN_SQL_EXECUTION	Indicates when sampling ,session Are you executing SQL sentence
…	…

For more: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/refrn/V-ACTIVE_SESSION_HISTORY.html#GUID-69CEA3A1-6C5E-43D6-982C-F353CD4B984C

`dba_hist_active_sess_history`

Static data dictionary view dba_hist_active_sess_history Will v$active_session_history The data in Every time 10 second Take a snapshot （snapshot） and Persistence preservation .

The following table shows some important columns in this view , Many of them are listed in v$active_session_history There have been... In the view .

Column	describe
SNAP_ID	The only snapshot ID
DBID	Snapshot database ID
INSTANCE_NUMBER	The instance number of the snapshot
SMAPLE_ID	sampling ID
SAMPLE_TIME	Sampling time
SAMPLE_TIME_UTC	World standard time of sampling
USECS_PER_ROW	Last active session Time elapsed since historical sampling , The unit is microseconds
SESSION_ID	session identifier
USER_ID	Oracle User identifier
SQL_ID	What is currently being implemented SQL Identifier of the statement
EVENT	Events wait , Mapping to v$active_session_history.EVENT
SEQ#	The unique identification serial number of the waiting event
XID	When sampling ,session Transactions being processed ID
IN_SQL_EXECUTION	Indicates when sampling ,session Are you executing SQL sentence
…	…

For more: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/refrn/DBA_HIST_ACTIVE_SESS_HISTORY.html#GUID-335EC838-FEA0-4872-9E14-67C5A1908B35

版权声明
本文为[Sebastien23]所创，转载请带上原文链接，感谢
https://yzsam.com/2022/04/202204230557416040.html