分析DB的问题及performance时,经常需要查询v$lock, v$locked_object, v$session, v$sqlarea, v$process, v$session_wait.
通过v$lock可以找到SID, 通过v$session可以找到sqlid,通过v$sqlarea可以找到sql text.
ps -ef|grep PID
SQL> desc v$lock
Name Null? Type
----------------------------------------- -------- ----------------------------
ADDR RAW(8)
KADDR RAW(8)
SID NUMBER
TYPE VARCHAR2(2)
ID1 NUMBER
ID2 NUMBER
LMODE NUMBER
REQUEST NUMBER
CTIME NUMBER
BLOCK NUMBER
CON_ID NUMBER
SQL> desc v$locked_object
Name Null? Type
----------------------------------------- -------- ----------------------------
XIDUSN NUMBER
XIDSLOT NUMBER
XIDSQN NUMBER
OBJECT_ID NUMBER
SESSION_ID NUMBER
ORACLE_USERNAME VARCHAR2(30)
OS_USER_NAME VARCHAR2(30)
PROCESS VARCHAR2(24)
LOCKED_MODE NUMBER
CON_ID NUMBER
SQL> desc v$process
Name Null? Type
----------------------------------------- -------- ----------------------------
ADDR RAW(8)
PID NUMBER
SOSID VARCHAR2(24)
SPID VARCHAR2(24)
STID VARCHAR2(24)
EXECUTION_TYPE VARCHAR2(10)
PNAME VARCHAR2(5)
USERNAME VARCHAR2(15)
SERIAL# NUMBER
TERMINAL VARCHAR2(30)
PROGRAM VARCHAR2(48)
TRACEID VARCHAR2(255)
TRACEFILE VARCHAR2(513)
BACKGROUND VARCHAR2(1)
LATCHWAIT VARCHAR2(16)
LATCHSPIN VARCHAR2(16)
PGA_USED_MEM NUMBER
PGA_ALLOC_MEM NUMBER
PGA_FREEABLE_MEM NUMBER
PGA_MAX_MEM NUMBER
CON_ID NUMBER
SQL> desc v$session
Name Null? Type
----------------------------------------- -------- ----------------------------
SADDR RAW(8)
SID NUMBER
SERIAL# NUMBER
AUDSID NUMBER
PADDR RAW(8)
USER# NUMBER
USERNAME VARCHAR2(30)
COMMAND NUMBER
OWNERID NUMBER
TADDR VARCHAR2(16)
LOCKWAIT VARCHAR2(16)
STATUS VARCHAR2(8)
SERVER VARCHAR2(9)
SCHEMA# NUMBER
SCHEMANAME VARCHAR2(30)
OSUSER VARCHAR2(30)
PROCESS VARCHAR2(24)
MACHINE VARCHAR2(64)
PORT NUMBER
TERMINAL VARCHAR2(30)
PROGRAM VARCHAR2(48)
TYPE VARCHAR2(10)
SQL_ADDRESS RAW(8)
SQL_HASH_VALUE NUMBER
SQL_ID VARCHAR2(13)
SQL_CHILD_NUMBER NUMBER
SQL_EXEC_START DATE
SQL_EXEC_ID NUMBER
PREV_SQL_ADDR RAW(8)
PREV_HASH_VALUE NUMBER
PREV_SQL_ID VARCHAR2(13)
PREV_CHILD_NUMBER NUMBER
PREV_EXEC_START DATE
PREV_EXEC_ID NUMBER
PLSQL_ENTRY_OBJECT_ID NUMBER
PLSQL_ENTRY_SUBPROGRAM_ID NUMBER
PLSQL_OBJECT_ID NUMBER
PLSQL_SUBPROGRAM_ID NUMBER
MODULE VARCHAR2(64)
MODULE_HASH NUMBER
ACTION VARCHAR2(64)
ACTION_HASH NUMBER
CLIENT_INFO VARCHAR2(64)
FIXED_TABLE_SEQUENCE NUMBER
ROW_WAIT_OBJ# NUMBER
ROW_WAIT_FILE# NUMBER
ROW_WAIT_BLOCK# NUMBER
ROW_WAIT_ROW# NUMBER
TOP_LEVEL_CALL# NUMBER
LOGON_TIME DATE
LAST_CALL_ET NUMBER
PDML_ENABLED VARCHAR2(3)
FAILOVER_TYPE VARCHAR2(13)
FAILOVER_METHOD VARCHAR2(10)
FAILED_OVER VARCHAR2(3)
RESOURCE_CONSUMER_GROUP VARCHAR2(32)
PDML_STATUS VARCHAR2(8)
PDDL_STATUS VARCHAR2(8)
PQ_STATUS VARCHAR2(8)
CURRENT_QUEUE_DURATION NUMBER
CLIENT_IDENTIFIER VARCHAR2(64)
BLOCKING_SESSION_STATUS VARCHAR2(11)
BLOCKING_INSTANCE NUMBER
BLOCKING_SESSION NUMBER
FINAL_BLOCKING_SESSION_STATUS VARCHAR2(11)
FINAL_BLOCKING_INSTANCE NUMBER
FINAL_BLOCKING_SESSION NUMBER
SEQ# NUMBER
EVENT# NUMBER
EVENT VARCHAR2(64)
P1TEXT VARCHAR2(64)
P1 NUMBER
P1RAW RAW(8)
P2TEXT VARCHAR2(64)
P2 NUMBER
P2RAW RAW(8)
P3TEXT VARCHAR2(64)
P3 NUMBER
P3RAW RAW(8)
WAIT_CLASS_ID NUMBER
WAIT_CLASS# NUMBER
WAIT_TIME NUMBER
SECONDS_IN_WAIT NUMBER
STATE VARCHAR2(19)
WAIT_TIME_MICRO NUMBER
TIME_REMAINING_MICRO NUMBER
TIME_SINCE_LAST_WAIT_MICRO NUMBER
SERVICE_NAME VARCHAR2(64)
SQL_TRACE VARCHAR2(8)
SQL_TRACE_WAITS VARCHAR2(5)
SQL_TRACE_BINDS VARCHAR2(5)
SQL_TRACE_PLAN_STATS VARCHAR2(10)
SESSION_EDITION_ID NUMBER
CREATOR_ADDR RAW(8)
CREATOR_SERIAL# NUMBER
ECID VARCHAR2(64)
SQL_TRANSLATION_PROFILE_ID NUMBER
CON_ID NUMBER
SQL> desc v$session_wait
Name Null? Type
----------------------------------------- -------- ----------------------------
SID NUMBER
SEQ# NUMBER
EVENT VARCHAR2(64)
P1TEXT VARCHAR2(64)
P1 NUMBER
P1RAW RAW(8)
P2TEXT VARCHAR2(64)
P2 NUMBER
P2RAW RAW(8)
P3TEXT VARCHAR2(64)
P3 NUMBER
P3RAW RAW(8)
WAIT_CLASS_ID NUMBER
WAIT_CLASS# NUMBER
WAIT_CLASS VARCHAR2(64)
WAIT_TIME NUMBER
SECONDS_IN_WAIT NUMBER
STATE VARCHAR2(19)
WAIT_TIME_MICRO NUMBER
TIME_REMAINING_MICRO NUMBER
TIME_SINCE_LAST_WAIT_MICRO NUMBER
CON_ID NUMBER
SQL> desc v$sqlarea
Name Null? Type
----------------------------------------- -------- ----------------------------
SQL_TEXT VARCHAR2(1000)
SQL_FULLTEXT CLOB
SQL_ID VARCHAR2(13)
SHARABLE_MEM NUMBER
PERSISTENT_MEM NUMBER
RUNTIME_MEM NUMBER
SORTS NUMBER
VERSION_COUNT NUMBER
LOADED_VERSIONS NUMBER
OPEN_VERSIONS NUMBER
USERS_OPENING NUMBER
FETCHES NUMBER
EXECUTIONS NUMBER
PX_SERVERS_EXECUTIONS NUMBER
END_OF_FETCH_COUNT NUMBER
USERS_EXECUTING NUMBER
LOADS NUMBER
FIRST_LOAD_TIME VARCHAR2(76)
INVALIDATIONS NUMBER
PARSE_CALLS NUMBER
DISK_READS NUMBER
DIRECT_WRITES NUMBER
BUFFER_GETS NUMBER
APPLICATION_WAIT_TIME NUMBER
CONCURRENCY_WAIT_TIME NUMBER
CLUSTER_WAIT_TIME NUMBER
USER_IO_WAIT_TIME NUMBER
PLSQL_EXEC_TIME NUMBER
JAVA_EXEC_TIME NUMBER
ROWS_PROCESSED NUMBER
COMMAND_TYPE NUMBER
OPTIMIZER_MODE VARCHAR2(10)
OPTIMIZER_COST NUMBER
OPTIMIZER_ENV RAW(2000)
OPTIMIZER_ENV_HASH_VALUE NUMBER
PARSING_USER_ID NUMBER
PARSING_SCHEMA_ID NUMBER
PARSING_SCHEMA_NAME VARCHAR2(30)
KEPT_VERSIONS NUMBER
ADDRESS RAW(8)
HASH_VALUE NUMBER
OLD_HASH_VALUE NUMBER
PLAN_HASH_VALUE NUMBER
MODULE VARCHAR2(64)
MODULE_HASH NUMBER
ACTION VARCHAR2(64)
ACTION_HASH NUMBER
SERIALIZABLE_ABORTS NUMBER
OUTLINE_CATEGORY VARCHAR2(64)
CPU_TIME NUMBER
ELAPSED_TIME NUMBER
OUTLINE_SID VARCHAR2(40)
LAST_ACTIVE_CHILD_ADDRESS RAW(8)
REMOTE VARCHAR2(1)
OBJECT_STATUS VARCHAR2(19)
LITERAL_HASH_VALUE NUMBER
LAST_LOAD_TIME DATE
IS_OBSOLETE VARCHAR2(1)
IS_BIND_SENSITIVE VARCHAR2(1)
IS_BIND_AWARE VARCHAR2(1)
CHILD_LATCH NUMBER
SQL_PROFILE VARCHAR2(64)
SQL_PATCH VARCHAR2(30)
SQL_PLAN_BASELINE VARCHAR2(30)
PROGRAM_ID NUMBER
PROGRAM_LINE# NUMBER
EXACT_MATCHING_SIGNATURE NUMBER
FORCE_MATCHING_SIGNATURE NUMBER
LAST_ACTIVE_TIME DATE
BIND_DATA RAW(2000)
TYPECHECK_MEM NUMBER
IO_CELL_OFFLOAD_ELIGIBLE_BYTES NUMBER
IO_INTERCONNECT_BYTES NUMBER
PHYSICAL_READ_REQUESTS NUMBER
PHYSICAL_READ_BYTES NUMBER
PHYSICAL_WRITE_REQUESTS NUMBER
PHYSICAL_WRITE_BYTES NUMBER
OPTIMIZED_PHY_READ_REQUESTS NUMBER
LOCKED_TOTAL NUMBER
PINNED_TOTAL NUMBER
IO_CELL_UNCOMPRESSED_BYTES NUMBER
IO_CELL_OFFLOAD_RETURNED_BYTES NUMBER
CON_ID NUMBER
IS_REOPTIMIZABLE VARCHAR2(1)
IS_RESOLVED_DYNAMIC_PLAN VARCHAR2(1)
阅读(1953) | 评论(2) | 转发(1) |
