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