What is the diff between:

v$sql 
v$sqlarea 
v$sqltext 
v$sqltext_with_newlines 

when I query v$sql joining with v$session and filter by a SID I get more than 
one SQL (expected) but if I join v$sqltext with v$session with same conditions I 
get one sql statement only (last SQL issued) why is this?

Thanks
Alan 

v$sql the details -- if you have multiple copies of the query:

"select * from T" 

in your shared pool, v$sql will have a row per query.  This can happen if user 
U1 and user U2 both have a table T and both issue "select * from T".  Those are 
entirely different queries with different plans and so on.  v$sql will have 2 
rows. 

v$sqlarea is a aggregate of v$sql.  It selects out DISTINCT sql.  "select * from 
T" will appear there.

It is not clear to me how you are joing v$session to v$sql to get more then one 
row.  If you wish to see the queries a session has open (maybe open, we cache 
cursors so you might see some queries that are closed) use v$open_cursor by sid.

v$sqltext is simply a way to see the entire query.  the v$sql and v$sqlarea 
views only show the first 1000 bytes.  newlines and other control characters are 
replace with whitespace.

v$sqltext_with_newlines is v$sqltext without the whitespace replacment.

 

	Reviews
GOTO a page to Bookmark Review | Bottom | Top This always cunfuses me ! March 20, 2001 Reviewer: Andre Whittick Nasser from Rio de Janeiro, Brazil Thanks for the explanation. I always confuse these views. Especially the other day, I had problems reading past the first 1000 characters of the query, which now I remember is solved by v$sql_text. GOTO a page to Bookmark Review | Bottom | Top March 21, 2001 Reviewer: Helena Markova from Bratislava, Slovakia GOTO a page to Bookmark Review | Bottom | Top March 21, 2001 Reviewer: Ramesh Vasudevan from Ruwi, Sultanate Of Oman GOTO a page to Bookmark Review | Bottom | Top March 21, 2001 Reviewer: Ramesh Vasudevan from Ruwi, Sultanate Of Oman GOTO a page to Bookmark Review | Bottom | Top Makes a nice reference October 30, 2002 Reviewer: A reader from Ct, USA Handy explanation to lookup. GOTO a page to Bookmark Review | Bottom | Top sort column value in v$sql February 25, 2005 Reviewer: Parag Jayant Patankar from India Hi Tom, I have done sorting of all_objects by status in SQL in Oracle9iR2. I have following details when I am executing query USERNAME USER SESSION_ ------------------------------ ------------------------------ -------- SESSION_NUM SQLADDR SQLHASH TABLESPACE CONTENTS ----------- -------- ---------- ------------------------------- --------- SEGTYPE SEGFILE# SEGBLK# EXTENTS BLOCKS SEGRFNO# --------- ---------- ---------- ---------- ---------- ---------- PARAG PARAG 1AA51B78 1251 191AF560 1188991948 PARAGTEMP TEMPORARY SORT 202 265 4 512 1 But in v$sql it is showing sort value only 2 14:26:40 SQL> select sql_text, sorts from v$sql where sql_text like 'select * from all_objects%'; SQL_TEXT -------------------------------------------------------------------------------- SORTS ---------- select * from all_objects order by status 2 Q1. Can you explain in detail how sorts value calculated in v$sql or v$sqlarea ? How the value 2 is comming in sorts column for sql statment ? My sort parameters are 14:28:16 SQL> show parameters sort NAME TYPE VALUE ------------------------------------ ----------- ------- nls_sort string sort_area_retained_size integer 0 sort_area_size integer 1048576 14:33:10 SQL> regards & thanks pjp regards & thanks pjp Followup: How many sorts at a time do you think you might be doing (simultaneously....) one or two.... GOTO a page to Bookmark Review | Bottom | Top Who in memory May 17, 2005 Reviewer: A reader Tom, I can find out the top sqls in memory using v$sql. But how do I find who ran it? (v$sql). can you please help? Thanks. Followup: you would have had to of had auditing (like DBMS_FGA) enabled. Anyone and Everyone could have run the SQL in v$sql -- is is the "shared" pool. Their cursors come and go but the v$sql stuff stays. GOTO a page to Bookmark Review | Bottom | Top May 17, 2005 Reviewer: A reader I have third party GUI tool and it gives SQLs from SQL Area when a parsing user is identified .. I was wondering how does it do and was trying to find the corresponding query. You think, it is not possible? TIA. Followup: there is a parsing id associated with the sql in v$sql, but that doesn't mean that is who RAN the sql. GOTO a page to Bookmark Review | Bottom | Top v$sql.executions = 0 May 17, 2005 Reviewer: neil from salt lake city How is it possible that the execution count in v$sql can be 0? Followup: programmer parsed SQL statement programmer never asked us to actually run the thing. GOTO a page to Bookmark Review | Bottom | Top What do these columns mean? June 14, 2005 Reviewer: A reader Tom, In v$sql, what is the meaning of shareable memory, persistent memory and runtime memory? I have read the description from the Reference manual, but cannot grasp the meaning of it. Why should a two line sql take up 64k of shareable memory? Why do some statements have 0 for persistent and runtime memory? Are all these memory allocations made in shared pool or is the runtime memory allocated in PGA (dedicated server)? Thanks GOTO a page to Bookmark Review | Bottom | Top June 19, 2006 Reviewer: Nadir from Karachi Tom, Is there any way we can find that indexes are properly used by these queries shown in V$sql view. Followup: eh? clarify what you mean by that. GOTO a page to Bookmark Review | Bottom | Top V$sql* VIEWS July 24, 2006 Reviewer: M. Moses from Charlotte, NC USA I wish Oracle views, both dynamic and static, were demystified the way Tom explained those v$SQL views. It realy gave a bearing in uncharted waters.

v$sqltext
　　存储的是完整的SQL,SQL被分割
　　
　　SQL> desc v$sqltext
　　Name Null? Type
　　----------------------------------------- -------- ----------------------------
　　ADDRESS RAW(4) ---------
　　HASH_VALUE NUMBER --------- 和 address 一起唯一标志一条sql
　　COMMAND_TYPE NUMBER
　　PIECE NUMBER ---------- 分片之后的顺序编号
　　SQL_TEXT VARCHAR2(64) -------------- 注意长度
　　
　　v$sqlarea --------- 存储的SQL 和一些相关的信息，比如累计的执行次数，逻辑读，物理读等统计信息
　　SQL> desc v$sqlarea
　　Name Null? Type
　　----------------------------------------- -------- ----------------------------
　　SQL_TEXT VARCHAR2(1000)
　　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
　　USERS_EXECUTING NUMBER
　　LOADS NUMBER
　　FIRST_LOAD_TIME VARCHAR2(38)
　　INVALIDATIONS NUMBER
　　PARSE_CALLS NUMBER
　　DISK_READS NUMBER
　　BUFFER_GETS NUMBER
　　ROWS_PROCESSED NUMBER
　　COMMAND_TYPE NUMBER
　　OPTIMIZER_MODE VARCHAR2(25)
　　PARSING_USER_ID NUMBER
　　PARSING_SCHEMA_ID NUMBER
　　KEPT_VERSIONS NUMBER
　　ADDRESS RAW(4)
　　HASH_VALUE NUMBER
　　MODULE VARCHAR2(64)
　　MODULE_HASH NUMBER
　　ACTION VARCHAR2(64)
　　ACTION_HASH NUMBER
　　SERIALIZABLE_ABORTS NUMBER
　　CPU_TIME NUMBER
　　ELAPSED_TIME NUMBER
　　IS_OBSOLETE VARCHAR2(1)
　　CHILD_LATCH NUMBER
　　
　　v$sql ---------- 存储的是具体的SQL 和执行计划相关信息，实际上，v$sqlarea 可以看做 v$sql 根据 sqltext 等 做了 group by 之后的信息
　　
　　SQL> desc v$sql
　　Name Null? Type
　　----------------------------------------- -------- ----------------------------
　　SQL_TEXT VARCHAR2(1000)
　　SHARABLE_MEM NUMBER
　　PERSISTENT_MEM NUMBER
　　RUNTIME_MEM NUMBER
　　SORTS NUMBER
　　LOADED_VERSIONS NUMBER
　　OPEN_VERSIONS NUMBER
　　USERS_OPENING NUMBER
　　FETCHES NUMBER
　　EXECUTIONS NUMBER
　　USERS_EXECUTING NUMBER
　　LOADS NUMBER
　　FIRST_LOAD_TIME VARCHAR2(38)
　　INVALIDATIONS NUMBER
　　PARSE_CALLS NUMBER
　　DISK_READS NUMBER
　　BUFFER_GETS NUMBER
　　ROWS_PROCESSED NUMBER
　　COMMAND_TYPE NUMBER
　　OPTIMIZER_MODE VARCHAR2(10)
　　OPTIMIZER_COST NUMBER
　　PARSING_USER_ID NUMBER
　　PARSING_SCHEMA_ID NUMBER
　　KEPT_VERSIONS NUMBER
　　ADDRESS RAW(4)
　　TYPE_CHK_HEAP RAW(4)
　　HASH_VALUE NUMBER
　　PLAN_HASH_VALUE NUMBER
　　CHILD_NUMBER 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 -------------- 注意这里跟 outline 有关
　　CHILD_ADDRESS RAW(4)
　　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
　　
　　另外注意这个
　　QL> desc v$sql_plan
　　Name Null? Type
　　----------------------------------------- -------- ----------------------------
　　ADDRESS RAW(4)
　　HASH_VALUE NUMBER
　　CHILD_NUMBER NUMBER ------------ 注意这个和 v$sql 里面的相同字段
　　OPERATION VARCHAR2(60)
　　OPTIONS VARCHAR2(60)
　　OBJECT_NODE VARCHAR2(20)
　　OBJECT# NUMBER
　　OBJECT_OWNER VARCHAR2(30)
　　OBJECT_NAME VARCHAR2(64)
　　OPTIMIZER VARCHAR2(40)
　　ID NUMBER
　　PARENT_ID NUMBER
　　DEPTH NUMBER
　　POSITION NUMBER
　　SEARCH_COLUMNS NUMBER
　　COST NUMBER
　　CARDINALITY NUMBER
　　BYTES NUMBER
　　OTHER_TAG VARCHAR2(70)
　　PARTITION_START VARCHAR2(10)
　　PARTITION_STOP VARCHAR2(10)
　　PARTITION_ID NUMBER
　　OTHER VARCHAR2(4000)
　　DISTRIBUTION VARCHAR2(40)
　　CPU_COST NUMBER
　　IO_COST NUMBER
　　TEMP_SPACE NUMBER
　　ACCESS_PREDICATES VARCHAR2(4000)
　　FILTER_PREDICATES VARCHAR2(4000)
　　
　　
　　实际上，看起来同样的一句SQL ，往往具有不同的执行计划
　　如果是不同的数据库用户，那么相应的涉及的 对象 可能都不一样，注意v$sql 中
　　OBJECT# NUMBER
　　OBJECT_OWNER VARCHAR2(30)
　　OBJECT_NAME VARCHAR2(64)
　　OPTIMIZER VARCHAR2(40)
　　
　　即使是相同的数据库用户，若 session 的优化模式、session 级的参数 等不一样，执行计划也能不同。所以即使相同的sql，也可能具有不同的执行计划！
　　
　　v$sql join to v$sql_plan 就代表了具体的sql的执行计划,通过下面3个字段做连接
　　
　　ADDRESS RAW(4)
　　HASH_VALUE NUMBER
　　CHILD_NUMBER NUMBER
　　而v$SQLAREA 忽略了 执行计划 等差异，只是在形式上sql文本看起来一样！相当于做了个聚合，是多个不同执行计划的sql的聚合和累计信息