很早写的，内容相似，拿来凑数了！
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.
- 对不同用户下执行相同的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博客 ” ，链接：https://blog.itpub.net/55472/viewspace-265120/
，如需转载，请注明出处，否则将追究法律责任。
