V$SQL , V$SQLTEXT,V$SQLAREA, V$SQL_PLAN, V$SESSION_LONGOPS

V$SQL , 
V$SQLAREA, 
V$SQLTEXT,

V$SQLTEXT_WITH_NEWLINES 

V$SQL_PLAN,
V$SESSION_LONGOPS

===============================
V$SQL 
===============================
V$SQL lists statistics on shared SQL area.
One row for each child cursor per SQL string.  
Statistics displayed in V$SQL are normally updated at the end of query execution. 
For long running queries , statistics are updated every 5 seconds. 
V$SQL Reference

===============================
V$SQLAREA
===============================
V$SQLAREA lists statistics on shared SQL area. 
One row for all child cursors, per SQL string. 
It provides statistics on SQL statements that are in memory.
V$SQLAREA Reference

V$SQL and V$SQLAREA main fields:

sql_id       - Id of the parent cursor in the library cache

child_number - Only for V$SQL: Number of this child cursor

version_count- Only for V$SQLAREA: Number of child cursors

hash_value   - Hash value of the parent statement in the library cache

address      - Address of the handle to the parent for this cursor

sql_fulltext - CLOB - full sql text

sql_text     - VARCHAR2(1000) - first 1000 characters.

to get sql_fulltext


V$SQLAREA main fields
executions   - Total number of executions
disk_reads   - Sum of physical disk reads
buffer_gets  - Sum of DB block gets (memory+physical)
cpu_time     - CPU time in microseconds
parse_calls  - Sum of all parse calls. (number of times SQL was re-parsed)
first_load_time - Timestamp of parent cursor creation

===============================
V$SQLTEXT
===============================
V$SQLTEXT holds text of SQL statements from shared SQL cursors in the SGA.
In V$SQLTEXT newlines and other control characters are replaced with whitespaces.



V$SQLTEXT Reference

V$SQLTEXT fields:
address      - Used with hash_value to uniquely identify a cached cursor
hash_value   - Used with address to uniquely identify a cached cursor
sql_id       - SQL identifier of a cached cursor
command_type - Code for the type of SQL statement (SELECT, INSERT, and so on)
piece        - Number used to order the pieces of SQL text
sql_text     - One piece of the SQL text is only VARCHAR2(64)


===============================
V$SQLTEXT_WITH_NEWLINES
===============================
V$SQLTEXT is same as V$SQLTEXT, only newlines and other control characters are NOT replaced with whitespaces.

===============================
V$SQL_PLAN
===============================
V$SQL_PLAN holds the execution plan information per each child cursor.
V$SQL_PLAN Reference

V$SQL_PLAN fields
address      - Address of the handle to the parent for this cursor
hash_value   - Hash value of the parent cursor.
sql_id       - SQL identifier of the parent cursor.
plan_hash_value - Unique identifier for SQL plan.
child_number - Number of the child cursor.

To get Explain Plan:
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('1qqtru155tyz8',2));

Join to V$SQLAREA
with address and hash_value

Join to V$SQL
with address, hash_value, and child_number.

===============================
V$SESSION_LONGOPS
===============================
This table lists long running sessions, with their sql_address, sql_hash_value, sql_id, sql_plan_hash_value, and the time consuming step (full_scan, fast full scan, etc.)

SELECT SQLAREA.sql_text,
       LONG.sid,
       LONG.serial#,
       LONG.time_remaining time_remaining_sec,
       LONG.sql_plan_operation,
       LONG.sql_plan_options
WHERE LONG.sql_id = SQLAREA.sql_id
AND LONG.start_time > TRUNC(SYSDATE)
AND SQLAREA.sql_text LIKE 'SELECT kuku%'

===============================
Common fields
===============================
sql_id       - VARCHAR2(13)
               Unique identifier, per SQL TEXT.('1qqtru155tyz8')
address      - RAW(4 | 8)
hash_value   - NUMBER
               address + hash_value Uniquely identify cursor.
child_number - NUMBER
               Number of child cursor(0,1,2,3,4...)

===============================
What is Child Cursor?
===============================
Child Cursors are simply cursors that reference the same exact sql_text - but are different in some fashion.
The first child cursor is numbered 0 (the parent), then 1 (first child), then 2 and so on. 
In what way do cursors different? - Well, this varies. 
For example:
A. Two users have table EMPLOYEES - and both run SELECT * FROM EMPLOYEES;
sql_id - would be same, It is same sql text.
child_cursor would be different.
B. For some reason - same SQLs have different execution plan. 

===============================
What is unique SQL identifier?
===============================
address + hash_value + child_number

===============================
How to connect V$SQLAREA with V$SESSION?
===============================
hash_value and address fields uniquely identify the SQL cursor.
They are used to connect to V$SESSION

===============================
Top SQLs from V$SQL and V$SQLAREA
===============================

Get top SQLs with the V$SESSION info.

SELECT * 
FROM(
SELECT SESSIONS.sid,
       SESSIONS.username,
       SESSIONS.osuser,
       SESSIONS.machine,       
       SESSIONS.module,
       SQLAREA.executions,
       SQLAREA.disk_reads,
       SQLAREA.buffer_gets,
       ROUND((SQLAREA.buffer_gets-SQLAREA.disk_reads)/DECODE(SQLAREA.buffer_gets,0,1,SQLAREA.buffer_gets)*100,2) AS memory_gets_pct,
       ROUND(SQLAREA.cpu_time/1000/1000) AS cpu_time_sec,
       ROUND(SQLAREA.user_io_wait_time/1000/1000) AS io_time_sec,             
       SQLAREA.parse_calls,
       SQLAREA.first_load_time,
       SQLAREA.sql_text
  FROM 
       V$SQL SQLAREA,
--       V$SQLAREA SQLAREA, 
       V$SESSION SESSIONS
 WHERE SESSIONS.sql_hash_value = SQLAREA.hash_value
   AND SESSIONS.sql_address    = SQLAREA.address
   AND SESSIONS.USERNAME IS NOT NULL
   AND SQLAREA.executions > 0
   ORDER BY 
--   SQLAREA.disk_reads DESC
--   ROUND(SQLAREA.cpu_time/1000) DESC
--   ROUND(SQLAREA.user_io_wait_time/1000) DESC
--   SQLAREA.executions DESC  
     SQLAREA.parse_calls DESC
--   ROUND((SQLAREA.buffer_gets-SQLAREA.disk_reads)/DECODE(SQLAREA.buffer_gets,0,1,SQLAREA.buffer_gets)*100,2) ASC
)WHERE ROWNUM < 21;


===============================
Get SQL Text from V$SQLTEXT
===============================
SELECT SQLTEXT.sql_text, 
       SQLTEXT.piece ,
       SQLTEXT.address,  
       SS.sid, 
       SS.username,  
       SS.schemaname, 
       SS.osuser, 
       SS.process, 
       SS.machine, 
       SS.terminal, 
       SS.program, 
       SS.type, 
       SS.module, 
       SS.logon_time, 
       SS.event, 
       SS.service_name, 
       SS.seconds_in_wait
  FROM V$SESSION SS, 
       V$SQLTEXT SQLTEXT
 WHERE SS.sql_address = SQLTEXT.address(+)
   AND SS.service_name = 'SYS$USERS'
ORDER BY SQLTEXT.address, SQLTEXT.piece

===============================
Oracle memory structures.
===============================

V$SQL, V$SQLAREA, V$SQLTEXT - All query Shared SQL area.
V$SQL_PLAN - Query Library Cache.

===============================
Reference
===============================
Ask Tom: What is the diference between V$SQL* views
Ask Tom: On Seeing Double in V$SQL