V$SQL与V$SQLAREA

SYS@newtest>DESC V$SQL 名称 是否为空? 类型

---

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(57) 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(57) 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) SYS@clonepdb_plug>create table emp as select * from scott.emp;

表已创建。

SYS@clonepdb_plug>set autotrace on SYS@clonepdb_plug>select count(*) from emp;

COUNT(*)

    12

执行计划

Plan hash value: 2083865914

---

| Id | Operation | Name | Rows | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| EMP | 12 | 2 (0)| 00:00:01 |

Note

• dynamic statistics used: dynamic sampling (level=2)

统计信息

      4  recursive calls
      5  db block gets
     17  consistent gets
      0  physical reads
      0  redo size
    550  bytes sent via SQL*Net to client
    607  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed

SYS@clonepdb_plug>select sql_text,executions,disk_reads,optimizer_mode, 2 buffer_gets,hash_value 3 from v$sql where sql_text='select count(*) from emp';

SQL_TEXT

EXECUTIONS DISK_READS OPTIMIZER_MODE BUFFER_GETS HASH_VALUE

---

select count() from emp 1 0 ALL_ROWS 22 2295140356 SYS@clonepdb_plug>select count() from emp;

COUNT(*)

    12

执行计划

Plan hash value: 2083865914

---

| Id | Operation | Name | Rows | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| EMP | 12 | 2 (0)| 00:00:01 |

统计信息

      8  recursive calls
      2  db block gets
     15  consistent gets
      0  physical reads
      0  redo size
    550  bytes sent via SQL*Net to client
    607  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      1  sorts (memory)
      0  sorts (disk)
      1  rows processed

SYS@clonepdb_plug>select sql_text,executions,disk_reads,optimizer_mode, 2 buffer_gets,hash_value 3 from v$sql where sql_text='select count(*) from emp';

SQL_TEXT

EXECUTIONS DISK_READS OPTIMIZER_MODE BUFFER_GETS HASH_VALUE

---

select count(*) from emp 2 0 ALL_ROWS 39 2295140356