V$SQLSTATS returns basic performance statistics for SQL cursors, with each row representing the data for a unique combination of SQL text and optimizer plan (that is, unique combination of SQL_ID, and PLAN_HASH_VALUE). The column definitions for columns in V$SQLSTATS are identical to those in the V$SQL and V$SQLAREA views. However, the V$SQLSTATS view differs from V$SQL and V$SQLAREA in that it is faster, more scalable, and has a greater data retention (the statistics may still appear in this view,
select view_name, view_definition
from v$fixed_view_definition
where lower(view_definition) like '%kkssqlstat%'
VIEW_NAME
------------------------------
VIEW_DEFINITION
--------------------------------------------------------------------------------
GV$SQLSTATS
select INST_ID, SQL_TEXT, SQL_FULLTEXT, SQL_ID, LAST_ACTIVE_TIME, LAST_ACTIVE_CHILD_ADDRESS, PLAN_HASH_VALUE, PARSE_CALLS, DISK_READS, DIRECT_WRITES, BUFFER_GETS, ROWS_PROCESSED, SERIALIZABLE_ABORTS, FETCHES, EXECUTIONS, END_OF_FETCH_COUNT, LOADS, VERSION_COUNT, INVALIDATIONS, PX_SERVERS_EXECUTIONS, CPU_TIME, ELAPSED_TIME, APPLICATION_WAIT_TIME, CONCURRENCY_WAIT_TIME, CLUSTER_WAIT_TIME, USER_IO_WAIT_TIME, PLSQL_EXEC_TIME, JAVA_EXEC_TIME, SORTS, SHARABLE_MEM, TOTAL_SHARABLE_MEM FROM x$kkssqlstat
SQL> desc x$KKSSQLSTAT
Name Null? Type
----------------------------------------- -------- ----------------------
ADDR RAW(4)
INDX NUMBER
INST_ID NUMBER
SQL_TEXT VARCHAR2(1000)
SQL_FULLTEXT CLOB
SQL_ID VARCHAR2(13)
PLAN_HASH_VALUE NUMBER
LAST_ACTIVE_TIME DATE
LAST_ACTIVE_CHILD_ADDRESS RAW(4)
PARSE_CALLS NUMBER
DISK_READS NUMBER
DIRECT_WRITES NUMBER
BUFFER_GETS NUMBER
ROWS_PROCESSED NUMBER
SERIALIZABLE_ABORTS NUMBER
FETCHES NUMBER
EXECUTIONS NUMBER
PX_SERVERS_EXECUTIONS NUMBER
END_OF_FETCH_COUNT NUMBER
LOADS NUMBER
VERSION_COUNT NUMBER
INVALIDATIONS NUMBER
CPU_TIME NUMBER
ELAPSED_TIME 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
SORTS NUMBER
SHARABLE_MEM NUMBER
TOTAL_SHARABLE_MEM NUMBER
DELTA_PARSE_CALLS NUMBER
DELTA_DISK_READS NUMBER
DELTA_DIRECT_WRITES NUMBER
DELTA_BUFFER_GETS NUMBER
DELTA_ROWS_PROCESSED NUMBER
DELTA_FETCH_COUNT NUMBER
DELTA_EXECUTION_COUNT NUMBER
DELTA_PX_SERVERS_EXECUTIONS NUMBER
DELTA_END_OF_FETCH_COUNT NUMBER
DELTA_CPU_TIME NUMBER
DELTA_ELAPSED_TIME NUMBER
DELTA_APPLICATION_WAIT_TIME NUMBER
DELTA_CONCURRENCY_TIME NUMBER
DELTA_CLUSTER_WAIT_TIME NUMBER
DELTA_USER_IO_WAIT_TIME NUMBER
DELTA_PLSQL_EXEC_TIME NUMBER
DELTA_JAVA_EXEC_TIME NUMBER
DELTA_SORTS NUMBER
DELTA_LOADS NUMBER
DELTA_INVALIDATIONS NUMBER
create view v$kkssqlstat as select * from x$kkssqlstat ;
grant select on v$kkssqlstat to system;