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$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
Column | Datatype | Description |
---|---|---|
ADDRESS | RAW(4 | 8) | Used with HASH_VALUE to uniquely identify a cached cursor |
HASH_VALUE | NUMBER | Used with ADDRESS to uniquely identify a cached cursor |
SQL_ID | VARCHAR2(13) | SQL identifier of a cached cursor |
COMMAND_TYPE | NUMBER | Code for the type of SQL statement (SELECT , INSERT , and so on) |
PIECE | NUMBER | Number used to order the pieces of SQL text |
SQL_TEXT | VARCHAR2(64) | A column containing one piece of the SQL text |
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