v$sql summary

<< V$SQL>>

Copy from http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2113.htm

V$SQL lists statistics on shared SQL area without the GROUP BY clause and contains one row for each child of the original SQL text entered. Statistics displayed in V$SQL are normally updated at the end of query execution. However, for long running queries, they are updated every 5 seconds. This makes it easy to see the impact of long running SQL statements while they are still in progress.

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

<<V$SQL Examples>>

Copy from http://www.ss64.com/orav/V$SQL.html

Information about Sql processes running now.

--Examples

 

Select

  substr(sql_text,0,80),

  decode(command_type,2,'INSERT'),

  executions

From V$SQL

Where command_type = 2

Order By executions;

 

Select

  substr(sql_text,0,80),

  decode(command_type,3,'SELECT'),

  executions

From V$SQL

Where command_type = 3

Order By executions;

/*

List of command_type values with meaning:

Insert 2

Select 3

Update 6

Delete 7

Lock Table 26

Alter Database 35

Alter Session 42

Commit 44

Rollback 45

Savepoint 46

Begin/Declare 47

*/

PS:EXECUTIONS:Number of executions that took place on this object since it was brought into the library cache

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

<<Show currently executing SQL>>

Copy from http://www.oracle-training.cc/oracle_tips_v$sql.htm

Whenever your database is experiencing a performance problem, it is very useful to have a script that displays all of the currently executing SQL statements. The following script joins the v$sql and v$session tables to show you all of the SQL that is executing at that moment in your database.

set pagesize  24

set newpage    1

set linesize 125

 

column sql_text  format a100

column user_name format a12

 

  SELECT   u.sid, SUBSTR (u.username, 1, 12) user_name, s.sql_text

    FROM   v$sql s, v$session u

   WHERE   s.hash_value = u.sql_hash_value

           AND sql_text NOT LIKE '%from v$sql s, v$session u%'

ORDER BY   u.sid; 

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

<<See what SQL users are running on the system>>

Copy from http://www.dba-oracle.com/concepts/query_active_users_v%24session.htm

Once we figure out who is on the system, we will probably want to know what they are doing. In this case, we will join the v$session view we just queried with another view, the V$SQL view. The V$SQL view will provide us with the SQL that is being executed on our system. Let's see GRUMPY's session details:

SQL> select a.sid, a.serial#, b.sql_text

  2  from v$session a, v$sqlarea b

  3  where a.sql_address=b.address

  4  and a.username='GRUMPY';

 

       SID    SERIAL# SQL_TEXT

 

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

       122      61521 select count(*) from gen_person where gen_person_id=95000

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

<<Whats the difference between the v$sql* views>>

Copy from http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:385254862843

You Asked

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

 

and we said...

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.

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

Some Definition

Copy from http://articles.techrepublic.com.com/5100-10878_11-6164425.html

V$SQL: This view gives you the full text of the SQL statement in a character large object (CLOB) column, plus the first 1,000 characters of it in a convenient VARCHAR2 column. The statistics available are extensive: number of parses and invalidations, disk reads and writes, execution times, wait times, and optimizer data. You can also tell the user and schema that created the statement, and how many users are currently executing it.

V$SQLAREA: This view contains many of the same statistics as V$SQL. However, V$SQL contains one row for each child of an original SQL statement, whereas this view contains only one row for each SQL string entered.

V$SQLSTATS: New in 10gR2, this view is faster and more convenient than V$SQL and V$SQLAREA; however, it contains only a subset of the other views' columns. In particular, it does not link to user information. There is one row for each combination of SQL and execution plan hash value. As a bonus, the entries in this view may persist longer than the other views. You may find a statement here even after it has aged out of the shared pool (and, therefore, it disappears from V$SQLAREA).

V$SQLTEXT: There are times that you want each line of a SQL statement separately, instead of one big object as in V$SQL; V$SQLTEXT gives you this. The COMMAND_TYPE column is a code you can use to select certain statement types (i.e., only INSERT or SELECT.) The SQL_TEXT column has one piece of the text, and the PIECE column numbers them for sorting via ORDER BY. A variant, V$SQLTEXT_WITH_NEWLINES, keeps the original linefeeds to improve readability (V$SQLTEXT replaces them with spaces). Neither V$SQLTEXT nor V$SQLTEXT_WITH_NEWLINES tells you who executed the statement or provides you with any statistics. To get that information, you'll have to join back to V$SQL or V$SQLAREA using the ADDRESS and HASH_VALUE columns.