查看: 1839|回复: 0

[Oracle数据库] V$SQL与V$SQLAREA

发表于 2018-1-25 08:00:00

V$SQL与V$SQLAREA
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)
V$SQL与V$SQLAREA
V$SQL与V$SQLAREA
V$SQL与V$SQLAREA
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(*)
  1. 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) 统计信息
  1. 4 recursive calls
  2. 5 db block gets
  3. 17 consistent gets
  4. 0 physical reads
  5. 0 redo size
  6. 550 bytes sent via SQL*Net to client
  7. 607 bytes received via SQL*Net from client
  8. 2 SQL*Net roundtrips to/from client
  9. 0 sorts (memory)
  10. 0 sorts (disk)
  11. 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(*)
  1. 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 | 统计信息
  1. 8 recursive calls
  2. 2 db block gets
  3. 15 consistent gets
  4. 0 physical reads
  5. 0 redo size
  6. 550 bytes sent via SQL*Net to client
  7. 607 bytes received via SQL*Net from client
  8. 2 SQL*Net roundtrips to/from client
  9. 1 sorts (memory)
  10. 0 sorts (disk)
  11. 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

V$SQL与V$SQLAREA
V$SQL与V$SQLAREA
V$SQL与V$SQLAREA




上一篇:内存管理
下一篇:回滚与撤销
回复

使用道具 举报