学习动态视图(四) v$sql + v$sqltext + v$sqlarea

很早写的，内容相似，拿来凑数了！

V$SQL               Child cursor details for V$SQLAREA 。即使SQL完全不同，也可能因为所属用户不同、session的优化模式不同等原因导致执行计划不同。
V$SQLAREA     Shared pool details for statements/anonymous blocks , 忽略了执行计划的差异，是形式上相同的SQL的一个聚合。
V$SQLTEXT      SQL text of statements in the shared pool , 完整的SQL文本

这三个视图都可以通过address,hash_value来定位SQL.

1. 对不同用户下执行相同的SQL，v$sql中会出现两条信息，而v$sqlarea和v$text只出现一条。

SQL> create table tt (col1 number,col2 number); SQL> create table scott.tt (col1 number,col2 number); SQL> select count(*) from tt t1,tt t2 where t1.col1=t2.col2; COUNT(*) ---------- 0 SQL> SQL> connect scott/tiger 已连接。 SQL> select count(*) from tt t1,tt t2 where t1.col1=t2.col2; COUNT(*) ---------- 0 SQL> connect sys/oracle as sysdba 已连接。 --查询到两条address和hash_value相同的纪录，这两条纪录的child_address(对应v$sql_plan中)不同 SQL> select SQL_TEXT,ADDRESS, HASH_VALUE from v$sql 2 where sql_text not like 'select SQL_TEXT,%' 3 and sql_text like '%tt t1%'; SQL_TEXT ADDRESS HASH_VALUE -------------------------------------------------- -------- ---------- select count(*) from tt t1,tt t2 where t1.col1=t2. 680B9564 100474950 col2 select count(*) from tt t1,tt t2 where t1.col1=t2. 680B9564 100474950 col2 --查询到一条纪录，虽然所查询的schema不同，但是查询SQL本身相同。v$sqlarea忽略了执行计划 SQL> select SQL_TEXT,ADDRESS, HASH_VALUE from v$sqlAREA 2 where sql_text not like 'select SQL_TEXT,%' 3 and sql_text like '%tt t1%'; SQL_TEXT ADDRESS HASH_VALUE -------------------------------------------------- -------- ---------- select count(*) from tt t1,tt t2 where t1.col1=t2. 680B9564 100474950 col2 SQL> select SQL_TEXT,ADDRESS, HASH_VALUE from v$sqlTEXT 2 where sql_text not like 'select SQL_TEXT,%' 3 and sql_text like '%tt t1%'; SQL_TEXT ADDRESS HASH_VALUE -------------------------------------------------- -------- ---------- select count(*) from tt t1,tt t2 where t1.col1=t2. 680B9564 100474950 col2

2、v$sql和v$sql_plan连接可以查询到具体的执行计划：

SQL> desc v$sql  名称                                      是否为空? 类型  ----------------------------------------- -------- -------------------------  SQL_TEXT                                           VARCHAR2(1000)  SQL_FULLTEXT                                       CLOB  SQL_ID                                             VARCHAR2(13)  SHARABLE_MEM                                       NUMBER  PERSISTENT_MEM                                     NUMBER  RUNTIME_MEM                                        NUMBER  SORTS                                              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(797)  OPTIMIZER_ENV_HASH_VALUE                           NUMBER  PARSING_USER_ID                                    NUMBER  PARSING_SCHEMA_ID                                  NUMBER  PARSING_SCHEMA_NAME                                VARCHAR2(30)  KEPT_VERSIONS                                      NUMBER  ADDRESS                                            RAW(4)  TYPE_CHK_HEAP                                      RAW(4)  HASH_VALUE                                         NUMBER  OLD_HASH_VALUE                                     NUMBER  PLAN_HASH_VALUE                                    NUMBER  CHILD_NUMBER                                       NUMBER  SERVICE                                            VARCHAR2(64)  SERVICE_HASH                                       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                                        NUMBER  CHILD_ADDRESS                                      RAW(4)      --和v$sql_plan做连接  SQLTYPE                                            NUMBER  REMOTE                                             VARCHAR2(1)  OBJECT_STATUS                                      VARCHAR2(19)  LITERAL_HASH_VALUE                                 NUMBER  LAST_LOAD_TIME                                     VARCHAR2(38)  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)

SQL> select SQL_TEXT,ADDRESS, HASH_VALUE,child_address from v$sql   2  where sql_text not like 'select SQL_TEXT,%'   3  and sql_text like '%tt t1%'; SQL_TEXT                                           ADDRESS  HASH_VALUE CHILD_AD -------------------------------------------------- -------- ---------- -------- select count(*) from tt t1,tt t2 where t1.col1=t2. 680B9564  100474950 682C6E44 col2 select count(*) from tt t1,tt t2 where t1.col1=t2. 680B9564  100474950 68F416D0 col2 SQL> select a.address,a.child_address,b.operation, b.options, b.object_owner,b.object_name, b.cost   2  from v$sql a,v$sql_plan b   3  where a.child_address=b.child_address   4  and a.child_address in ('682C6E44','68F416D0'); ADDRESS  CHILD_AD OPERATION            OPTIONS    OBJECT OBJECT_NAM       COST -------- -------- -------------------- ---------- ------ ---------- ---------- 680B9564 682C6E44 SELECT STATEMENT                                           5 680B9564 682C6E44 SORT                 AGGREGATE 680B9564 682C6E44 HASH JOIN                                                  5 680B9564 682C6E44 TABLE ACCESS         FULL       SYS    TT                  2 680B9564 682C6E44 TABLE ACCESS         FULL       SYS    TT                  2 680B9564 68F416D0 SELECT STATEMENT                                           5 680B9564 68F416D0 SORT                 AGGREGATE 680B9564 68F416D0 HASH JOIN                                                  5 680B9564 68F416D0 TABLE ACCESS         FULL       SCOTT  TT                  2 680B9564 68F416D0 TABLE ACCESS         FULL       SCOTT  TT                  2 已选择10行。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/55472/viewspace-265120/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/55472/viewspace-265120/