Share

DBA

A Higher-Level Perspective on SQL Tuning, Part 2, Listing 1

The V$SQL performance view lists SQL statements in the shared SQL area and presents numerous performance measurements alongside each.

Listing 1: V$SQL performance view (in Oracle Database 18c) 
SQL> desc V$SQL
 Name                                Null?    Type
 ----------------------------------- -------- -------------------
 SQL_TEXT                                     VARCHAR2(1000)
 SQL_FULLTEXT                                 CLOB
 SQL_ID                                       VARCHAR2(13)
 SHARABLE_MEM                                 NUMBER
 PERSISTENT_MEM                               NUMBER
 RUNTIME_MEM                                  NUMBER
 SORTS                                        NUMBER
 LOADED_VERSIONS                              NUMBER
 OPEN_VERSIONS                                NUMBER
 USERS_OPENING                                NUMBER
 FETCHES                                      NUMBER
 EXECUTIONS                                   NUMBER
 PX_SERVERS_EXECUTIONS                        NUMBER
 END_OF_FETCH_COUNT                           NUMBER
 USERS_EXECUTING                              NUMBER
 LOADS                                        NUMBER
 FIRST_LOAD_TIME                              VARCHAR2(19)
 INVALIDATIONS                                NUMBER
 PARSE_CALLS                                  NUMBER
 DISK_READS                                   NUMBER
 DIRECT_WRITES                                NUMBER
 DIRECT_READS                                 NUMBER
 BUFFER_GETS                                  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
 ROWS_PROCESSED                               NUMBER
 COMMAND_TYPE                                 NUMBER
 OPTIMIZER_MODE                               VARCHAR2(10)
 OPTIMIZER_COST                               NUMBER
 OPTIMIZER_ENV                                RAW(2000)
 OPTIMIZER_ENV_HASH_VALUE                     NUMBER
 PARSING_USER_ID                              NUMBER
 PARSING_SCHEMA_ID                            NUMBER
 PARSING_SCHEMA_NAME                          VARCHAR2(128)
 KEPT_VERSIONS                                NUMBER
 ADDRESS                                      RAW(8)
 TYPE_CHK_HEAP                                RAW(8)
 HASH_VALUE                                   NUMBER
 OLD_HASH_VALUE                               NUMBER
 PLAN_HASH_VALUE                              NUMBER
 FULL_PLAN_HASH_VALUE                         NUMBER
 CHILD_NUMBER                                 NUMBER
 SERVICE                                      VARCHAR2(64)
 SERVICE_HASH                                 NUMBER
 MODULE                                       VARCHAR2(64)
 MODULE_HASH                                  NUMBER
 ACTION                                       VARCHAR2(64)
 ACTION_HASH                                  NUMBER
 SERIALIZABLE_ABORTS                          NUMBER
 OUTLINE_CATEGORY                             VARCHAR2(64)
 CPU_TIME                                     NUMBER
 ELAPSED_TIME                                 NUMBER
 OUTLINE_SID                                  NUMBER
 CHILD_ADDRESS                                RAW(8)
 SQLTYPE                                      NUMBER
 REMOTE                                       VARCHAR2(1)
 OBJECT_STATUS                                VARCHAR2(19)
 LITERAL_HASH_VALUE                           NUMBER
 LAST_LOAD_TIME                               VARCHAR2(19)
 IS_OBSOLETE                                  VARCHAR2(1)
 IS_BIND_SENSITIVE                            VARCHAR2(1)
 IS_BIND_AWARE                                VARCHAR2(1)
 IS_SHAREABLE                                 VARCHAR2(1)
 CHILD_LATCH                                  NUMBER
 SQL_PROFILE                                  VARCHAR2(64)
 SQL_PATCH                                    VARCHAR2(128)
 SQL_PLAN_BASELINE                            VARCHAR2(128)
 PROGRAM_ID                                   NUMBER
 PROGRAM_LINE#                                NUMBER
 EXACT_MATCHING_SIGNATURE                     NUMBER
 FORCE_MATCHING_SIGNATURE                     NUMBER
 LAST_ACTIVE_TIME                             DATE
 BIND_DATA                                    RAW(2000)
 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
 OPTIMIZED_PHY_READ_REQUESTS                  NUMBER
 LOCKED_TOTAL                                 NUMBER
 PINNED_TOTAL                                 NUMBER
 IO_CELL_UNCOMPRESSED_BYTES                   NUMBER
 IO_CELL_OFFLOAD_RETURNED_BYTES               NUMBER
 CON_ID                                       NUMBER
 IS_REOPTIMIZABLE                             VARCHAR2(1)
 IS_RESOLVED_ADAPTIVE_PLAN                    VARCHAR2(1)
 IM_SCANS                                     NUMBER
 IM_SCAN_BYTES_UNCOMPRESSED                   NUMBER
 IM_SCAN_BYTES_INMEMORY                       NUMBER
 DDL_NO_INVALIDATE                            VARCHAR2(1)
 IS_ROLLING_INVALID                           VARCHAR2(1)
 IS_ROLLING_REFRESH_INVALID                   VARCHAR2(1)
 RESULT_CACHE                                 VARCHAR2(1)
Connor McDonald

Connor McDonald is a former Oracle ACE Director who has joined the Oracle Developer Advocates Team. Over the past 25 years, he has worked with systems in Australia, the United Kingdom, Southeast Asia, Western Europe, and the United States. He has co-authored three books and has been a popular speaker at Oracle conferences around the world, specializing in topics regarding the database engine and PL/SQL. 

DISCLAIMER: We've captured these popular historical magazine articles for your reference. Links etc contained within these article possibly won't work. These articles are provided as-is with no guarantee that the information within them is the best advice for current versions of the Oracle Database.