V$SQL and V$SQLAREA

SYS@newtest > DESC V$SQL

Is the name empty? Types

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

The table has been created.

SYS@clonepdb_plug > set autotrace on

SYS@clonepdb_plug > select count (*) from emp

COUNT (*) 12 execution Plan

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 | Notedynamic statistics used: dynamic sampling (level=2) Statistics 4 recursive calls 5 db block gets 17 consistent gets 0 physical reads 0 redo size 5507 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 execution Plan

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 | Statistics 8 recursive calls 2 db block gets 15 consistent gets 0 physical reads 0 redo size 5507 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