Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1450212
  • 博文数量: 239
  • 博客积分: 5909
  • 博客等级: 大校
  • 技术积分: 2715
  • 用 户 组: 普通用户
  • 注册时间: 2010-07-24 20:19
文章分类

全部博文(239)

文章存档

2014年(4)

2013年(22)

2012年(140)

2011年(14)

2010年(59)

我的朋友

分类: Oracle

2012-05-16 14:18:52

分析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)

阅读(1960) | 评论(2) | 转发(1) |
给主人留下些什么吧!~~

啦哆A梦2012-05-17 21:20:06

还不错的文章~顶一个吧~

布毫铯2012-05-16 21:26:47

有使用的例子么?