Oracle八大性能视图之v$sqlarea

 

1、v$sqlarea表的结构

SQL> desc v$sqlarea

 名称                                      是否为空? 类型

 ----------------------------------------- -------- ------------------------

 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(38)

 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(882)

 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)

 CHILD_LATCH                                        NUMBER

 SQL_PROFILE                                        VARCHAR2(64)

 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

 

 

 2、查V$SQLAREA表相关信息语句

 

 select sql_id,address,hash_value,

       executions  as 累计的执行次数executions,

       buffer_gets as 逻辑读buffer_gets,

       disk_reads  as 物理读disk_reads,

       sql_text

  from v$sqlarea

 order by buffer_gets desc;

 

 备注：sql_text字段存的这个sql的前1000个字符。查找整个的sql还需去v$sqltext或者v$sqltext_with_newlines。

 如：

 select * from v$sqltext where sql_id='685jucmq3q7nd';

 select * from v$sqltext_with_newlines where sql_id='685jucmq3q7nd';