Wednesday, April 22, 2009

V$SQLSTATS

V$SQLSTATS displays 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, even after the cursor has been aged out of the shared pool). Note that V$SQLSTATS contains a subset of columns that appear in V$SQL and V$SQLAREA.

It is recommended that V$SQLSTATS be used in place of V$SQL when fetching statistics for SQL cursors.

Column

Datatype

Description

SQL_TEXT

VARCHAR2(1000)

First thousand characters of the SQL text for the current cursor

SQL_FULLTEXT

CLOB

Full text for the SQL statement exposed as a CLOB column. THe full text of a SQL statement can be retrieved using this column instead of joining with the V$SQLTEXT view.

SQL_ID

VARCHAR2(13)

SQL identifier of the parent cursor in the library cache

LAST_ACTIVE_TIME

DATE

Last time that statistics for a cursor with this text and plan were updated

LAST_ACTIVE_CHILD_ADDRESS

RAW(4 | 8)

Address of the cursor that last updated statistics

PLAN_HASH_VALUE

NUMBER

Numeric representation of the SQL plan for this cursor. Comparing one PLAN_HASH_VALUE to another easily identifies whether or not two plans are the same (rather than comparing the two plans line by line).

PARSE_CALLS

NUMBER

Number of parse calls for all cursors with this SQL text and plan

DISK_READS

NUMBER

Number of disk reads for all cursors with this SQL text and plan

DIRECT_WRITES

NUMBER

Number of direct writes for all cursors with this SQL text and plan

BUFFER_GETS

NUMBER

Number of buffer gets for all cursors with this SQL text and plan

ROWS_PROCESSED

NUMBER

Total number of rows the parsed SQL statement returns

SERIALIZABLE_ABORTS

NUMBER

Number of times the transaction failed to serialize, producing ORA-08177 errors, per cursor

FETCHES

NUMBER

Number of fetches associated with the SQL statement

EXECUTIONS

NUMBER

Number of executions that took place on this object since it was brought into the library cache

END_OF_FETCH_COUNT

NUMBER

Number of times this cursor was fully executed since the cursor was brought into the library cache. The value of this statistic is not incremented when the cursor is partially executed, either because it failed during the execution or because only the first few rows produced by this cursor are fetched before the cursor is closed or re-executed. By definition, the value of the END_OF_FETCH_COUNT column should be less or equal to the value of the EXECUTIONS column.

LOADS

NUMBER

Number of times the object was either loaded or reloaded

VERSION_COUNT

NUMBER

number of cursors present in the cache with this SQL text and plan

INVALIDATIONS

NUMBER

Number of times this child cursor has been invalidated

PX_SERVERS_EXECUTIONS

NUMBER

Total number of executions performed by parallel execution servers (0 when the statement has never been executed in parallel)

CPU_TIME

NUMBER

CPU time (in microseconds) used by this cursor for parsing, executing, and fetching

ELAPSED_TIME

NUMBER

Elapsed time (in microseconds) used by this cursor for parsing, executing, and fetching

AVG_HARD_PARSE_TIME

NUMBER

Average hard parse time (in microseconds) used by this cursor for parsing, executing, and fetching

APPLICATION_WAIT_TIME

NUMBER

Application wait time (in microseconds)

CONCURRENCY_WAIT_TIME

NUMBER

Concurrency wait time (in microseconds)

CLUSTER_WAIT_TIME

NUMBER

Cluster wait time (in microseconds)

USER_IO_WAIT_TIME

NUMBER

User I/O wait time (in microseconds)

PLSQL_EXEC_TIME

NUMBER

PL/SQL execution time (in microseconds)

JAVA_EXEC_TIME

NUMBER

Java execution time (in microseconds)

SORTS

NUMBER

Number of sorts that were done for the child cursor

SHARABLE_MEM

NUMBER

Total shared memory (in bytes) currently occupied by all cursors with this SQL text and plan

TOTAL_SHARABLE_MEM

NUMBER

Total shared memory (in bytes) occupied by all cursors with this SQL text and plan if they were to be fully loaded in the shared pool (that is, cursor size)

TYPECHECK_MEM

NUMBER

???

Oracle data dictionary views

Oracle dynamic performance views

Tuesday, April 21, 2009

V$BUFFER_POOL_STATISTICS

V$BUFFER_POOL_STATISTICS displays statistics about all buffer pools available for the instance.

Column

Datatype

Description

ID

NUMBER

Buffer pool identifier number

NAME

VARCHAR2(20)

Name of the buffer pool

SET_MSIZE

NUMBER

Buffer pool maximum set size

CNUM_REPL

NUMBER

Number of buffers on replacement list

CNUM_WRITE

NUMBER

Number of buffers on write list

CNUM_SET

NUMBER

Number of buffers in set

BUF_GOT

NUMBER

Number of buffers gotten by the set

SUM_WRITE

NUMBER

Number of buffers written by the set

SUM_SCAN

NUMBER

Number of buffers scanned in the set

FREE_BUFFER_WAIT

NUMBER

Free buffer wait statistic

WRITE_COMPLETE_WAIT

NUMBER

Write complete wait statistic

BUFFER_BUSY_WAIT

NUMBER

Buffer busy wait statistic

FREE_BUFFER_INSPECTED

NUMBER

Free buffer inspected statistic

DIRTY_BUFFERS_INSPECTED

NUMBER

Dirty buffers inspected statistic

DB_BLOCK_CHANGE

NUMBER

Database blocks changed statistic

DB_BLOCK_GETS

NUMBER

Database blocks gotten statistic

CONSISTENT_GETS

NUMBER

Consistent gets statistic

PHYSICAL_READS

NUMBER

Physical reads statistic

PHYSICAL_WRITES

NUMBER

Physical writes statistic

Note:

1. V$BUFFER_POOL_STATISTICS view can be used to determine the hit ratio for the buffer pools. This view maintains statistics for each pool on the number of logical reads and writes. The buffer pool hit ratio can be determined using the following formula:

1 - (physical_reads/(db_block_gets + consistent_gets))

The ratio can be calculated with the following query:

SELECT NAME, PHYSICAL_READS, DB_BLOCK_GETS, CONSISTENT_GETS,

1 - (PHYSICAL_READS / (DB_BLOCK_GETS + CONSISTENT_GETS)) "Hit Ratio"

FROM V$BUFFER_POOL_STATISTICS;

2. To determining which segments have many buffers in the pool

SELECT o.OBJECT_NAME, COUNT(*) NUMBER_OF_BLOCKS

FROM DBA_OBJECTS o, V$BH bh

WHERE o.DATA_OBJECT_ID = bh.OBJD AND o.OWNER != 'SYS'

GROUP BY o.OBJECT_NAME

ORDER BY COUNT(*);

Oracle data dictionary views

Oracle dynamic performance views