V$SQLAREA

Lists statistics on shared SQL area and contains one row per SQL string. It provides statistics on SQL statements that are in memory, parsed, and ready for execution. The statistics are cumulative, if the same SQL statement has been run more than once, it will show total values for all executions of the statement.

Columns


   SQL_TEXT        (first 1000 characters)
   SQL_FULLTEXT    (CLOB)
   SHARABLE_MEM
   PERSISTENT_MEM
   RUNTIME_MEM
   SORTS
   VERSION_COUNT
   LOADED_VERSIONS
   OPEN_VERSIONS
   USERS_OPENING
   FETCHES
   EXECUTIONS      (How many times the statement has been executed)
   USERS_EXECUTING
   LOADS
   FIRST_LOAD_TIME (When the statement was first loaded into the SQL area)
   INVALIDATIONS
   PARSE_CALLS     (How many times Oracle has had to re-parse the statement)
   DISK_READS      (Cumulative total of disk blocks read for this statement)
   BUFFER_GETS     (Cumulative total of memory blocks read for this statement)
   ROWS_PROCESSED  (Cumulative total of rows processed by this statement)
   COMMAND_TYPE
   OPTIMIZER_MODE
   PARSING_USER_ID
   PARSING_SCHEMA_ID
   KEPT_VERSIONS
   ADDRESS
   HASH_VALUE   (A hash value for the statement which is guaranteed to be unique)
   MODULE
   MODULE_HASH
   ACTION
   ACTION_HASH
   SERIALIZABLE_ABORTS
   CPU_TIME
   ELAPSED_TIME
   IS_OBSOLETE
   CHILD_LATCH

Note that V$SQL can often be used in place of V$SQLAREA and may have less impact (fewer gets on the library cache latches)

Examples:

-- Count the number of recent UPDATE statements:
SELECT max(command_type), count (*)
FROM v$sqlarea
WHERE command_type =6
AND open_versions > 0
AND rows_processed > 0;

-- Count the number of recent UPDATE statements:
SELECT count (*)
FROM SYS.v_$sqlarea
WHERE sql_text LIKE 'UPDATE %'
AND open_versions > 0
AND rows_processed > 0;

-- Show SQL statements that are running right now:
SELECT sql_text
FROM v$sqlarea
WHERE users_executing > 0;

-- List recent SQL activity grouped by type:
SELECT decode(command_type, 1,'CRE TAB', 2,'INSERT', 3,'SELECT', 6,'UPDATE', 7,'DELETE', 9,'CRE INDEX', 12,'DROP TABLE', 15,'ALT TABLE',39,'CRE TBLSPC', 42,'DDL', 44,'COMMIT', 45,'ROLLBACK', 47,'PL/SQL EXEC', 48,'SET XACTN', 62,'ANALYZE TAB', 63,'ANALYZE IX', 71,'CREATE MLOG', 74,'CREATE SNAP',79,'ALTER ROLE', 85,'TRUNC TAB', to_char(command_type)) SQLcmd,
count (*)
FROM v$sqlarea
WHERE open_versions > 0
AND rows_processed > 0
Group By decode(command_type, 1,'CRE TAB', 2,'INSERT', 3,'SELECT', 6,'UPDATE', 7,'DELETE', 9,'CRE INDEX', 12,'DROP TABLE', 15,'ALT TABLE',39,'CRE TBLSPC', 42,'DDL', 44,'COMMIT', 45,'ROLLBACK', 47,'PL/SQL EXEC', 48,'SET XACTN', 62,'ANALYZE TAB', 63,'ANALYZE IX', 71,'CREATE MLOG', 74,'CREATE SNAP',79,'ALTER ROLE', 85,'TRUNC TAB', to_char(command_type));

-- Track the progress of a specific (long running) statement:
SELECT SUBSTR(sql_text, 1, 60) "SQL Text",
rows_processed "Total Rows Processed",
ROUND((SYSDATE - TO_DATE(first_load_time, 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60, 1) "Total Time (Min)",
TRUNC(rows_processed /((SYSDATE - TO_DATE(first_load_time, 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60)) "Rows/Min",
TRUNC(rows_processed /((SYSDATE - TO_DATE(first_load_time, 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60 * 60)) "Rows/Sec"
FROM v$sqlarea
WHERE sql_text LIKE 'INSERT INTO TABLE T_BLAH VALUES 1,2,3%'
AND open_versions > 0
AND rows_processed > 0;