V$SQLTEXT and V$SQLSTATS in Oracle

I will explain v$sqltext and v$sqlstats in Oracle in this post.

You can read the following post to learn v$sql views.

V$SQL and GV$SQL Views in Oracle

V$SQLTEXT in Oracle

V$SQLTEXT is very important view for Oracle DBA that contains the text of SQL statements belonging to shared SQL cursors in the SGA.

SQL> desc v$sqltext;
Name Null?         Type
----------------------------------------- -------- ----------------------------
ADDRESS           RAW(8)
HASH_VALUE        NUMBER
SQL_ID            VARCHAR2(13)
COMMAND_TYPE      NUMBER
PIECE             NUMBER
SQL_TEXT          VARCHAR2(64)
CON_ID            NUMBER

SQL>

GV$SQLTEXT Columns

You can find the sqltext from sql_id as follows.

SQL> select * from gv$sqltext where sql_id='86tvxatxqc003';

INST_ID ADDRESS HASH_VALUE SQL_ID COMMAND_TYPE PIECE SQL_TEXT CON_ID
---------- ---------------- ---------- ------------- ------------ ---------- ---------------------------------------------------------------- ----------
1 00000001450380A0 2070282243 86tvxatxqc003 3 14 STEM_KODU"=8) AND ("A"."IS_YADA_ABONE_NO"=144494)) innerQuery 0
1 00000001450380A0 2070282243 86tvxatxqc003 3 13 7295 AS C2, COUNT(*) AS C3 FROM "ORT_BAKIYE" "A" WHERE ("A"."SI 0

You can use the v$sqltext with v$session as follows.

select a.sid, a.serial#, a.username, a.osuser, b.sql_text,b.sql_id
from v$session a, v$sqltext b
where a.sql_hash_value = b.hash_value
and a.sql_address = b.address
and a.sid in (select sid
from v$session_wait
where event = 'db file sequential read')
order by a.sid, b.hash_value, b.piece;

V$SQLSTATS in Oracle

V$SQLSTATS is very import for Oracle DBA that displays basic performance statistics for SQL cursors and contains one row per SQL statement (that is, one row per unique value of SQL_ID).

The column definitions for columns in V$SQLSTATS are identical to those in the V$SQL and V$SQLAREA views. However, the V$SQLSTATS view differs from V$SQL and V$SQLAREA in that it is faster, more scalable, and has a greater data retention (the statistics may still appear in this view, even after the cursor has been aged out of the shared pool).

Note that V$SQLSTATS contains a subset of columns that appear in V$SQL and V$SQLAREA.

V$SQLSTATS Columns

SQL> desc v$sqlstats
Name Null?                        Type
----------------------------------------- -------- ----------------------------
SQL_TEXT                          VARCHAR2(1000)
SQL_FULLTEXT                      CLOB
SQL_ID                            VARCHAR2(13)
LAST_ACTIVE_TIME                  DATE
LAST_ACTIVE_CHILD_ADDRESS         RAW(8)
PLAN_HASH_VALUE                   NUMBER
PARSE_CALLS                       NUMBER
DISK_READS                        NUMBER
DIRECT_WRITES                     NUMBER
BUFFER_GETS                       NUMBER
ROWS_PROCESSED                    NUMBER
SERIALIZABLE_ABORTS               NUMBER
FETCHES                           NUMBER
EXECUTIONS                        NUMBER
END_OF_FETCH_COUNT                NUMBER
LOADS                             NUMBER
VERSION_COUNT                     NUMBER
INVALIDATIONS                     NUMBER
PX_SERVERS_EXECUTIONS             NUMBER
CPU_TIME                          NUMBER
ELAPSED_TIME                      NUMBER
AVG_HARD_PARSE_TIME               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
SORTS                             NUMBER
SHARABLE_MEM                      NUMBER
TOTAL_SHARABLE_MEM                NUMBER
TYPECHECK_MEM                     NUMBER
IO_CELL_OFFLOAD_ELIGIBLE_BYTES    NUMBER
IO_INTERCONNECT_BYTES             NUMBER
PHYSICAL_READ_REQUESTS            NUMBER
PHYSICAL_READ_BYTES               NUMBER
PHYSICAL_WRITE_REQUESTS           NUMBER
PHYSICAL_WRITE_BYTES              NUMBER
EXACT_MATCHING_SIGNATURE          NUMBER
FORCE_MATCHING_SIGNATURE          NUMBER
IO_CELL_UNCOMPRESSED_BYTES        NUMBER
IO_CELL_OFFLOAD_RETURNED_BYTES    NUMBER
DELTA_PARSE_CALLS                 NUMBER
DELTA_DISK_READS                  NUMBER
DELTA_DIRECT_WRITES               NUMBER
DELTA_BUFFER_GETS                 NUMBER
DELTA_ROWS_PROCESSED              NUMBER
DELTA_FETCH_COUNT                 NUMBER
DELTA_EXECUTION_COUNT             NUMBER
DELTA_PX_SERVERS_EXECUTIONS       NUMBER
DELTA_END_OF_FETCH_COUNT          NUMBER
DELTA_CPU_TIME                    NUMBER
DELTA_ELAPSED_TIME                NUMBER
DELTA_APPLICATION_WAIT_TIME       NUMBER
DELTA_CONCURRENCY_TIME            NUMBER
DELTA_CLUSTER_WAIT_TIME           NUMBER
DELTA_USER_IO_WAIT_TIME           NUMBER
DELTA_PLSQL_EXEC_TIME             NUMBER
DELTA_JAVA_EXEC_TIME              NUMBER
DELTA_SORTS                       NUMBER
DELTA_LOADS                       NUMBER
DELTA_INVALIDATIONS               NUMBER
DELTA_PHYSICAL_READ_REQUESTS      NUMBER
DELTA_PHYSICAL_READ_BYTES         NUMBER
DELTA_PHYSICAL_WRITE_REQUESTS     NUMBER
DELTA_PHYSICAL_WRITE_BYTES        NUMBER
DELTA_IO_INTERCONNECT_BYTES       NUMBER
DELTA_CELL_OFFLOAD_ELIG_BYTES     NUMBER
DELTA_CELL_UNCOMPRESSED_BYTES     NUMBER
CON_ID                            NUMBER
CON_DBID                          NUMBER
OBSOLETE_COUNT                    NUMBER

SQL>

You can use the v$sqlstats to find out SQL Statements that are doing high I/O.

SELECT sql_text, disk_reads FROM
(SELECT sql_text, buffer_gets, disk_reads, sorts,
cpu_time/1000000 cpu, rows_processed, elapsed_time
FROM v$sqlstats
ORDER BY disk_reads DESC)
WHERE rownum <= 5;




SELECT schema, sql_text, disk_reads, round(cpu,2) FROM
(SELECT s.parsing_schema_name schema, t.sql_id, t.sql_text, t.disk_reads,
t.sorts, t.cpu_time/1000000 cpu, t.rows_processed, t.elapsed_time
FROM v$sqlstats t join v$sql s on(t.sql_id = s.sql_id)
WHERE parsing_schema_name = 'HR'
ORDER BY disk_reads DESC)
WHERE rownum <= 5;

Do you want to learn Oracle Database Performance Tuning detailed, then Click this link.

Performance Tuning and SQL Tuning Tutorial in the Oracle Database