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文本
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
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');