The client cache statistics are
available from a dynamic view called
This view includes information such as the number of result
sets cached, number of cached result sets that were
invalidated, and the number of cache hits.
SQL> select cache_id, name, value from
-------- ------------------------------ --------
55 Block Size
55 Block Count Max
55 Block Count Current
55 Hash Bucket Count
55 Create Count Success
55 Create Count Failure
55 Find Count
55 Invalidation Count
55 Delete Count Invalid
55 Delete Count Valid
A few important values to monitor in
this dynamic view are as follows:
Block Count Max - a computed value that
shows the maximum number of blocks that can be allocated
in the result cache. This is based on the
Block Count Current - the number of
blocks currently being used by the client result cache.
The ratio of Block Count Max to
Block Count Current shows the utilization of the result
Create Count Success - the
number of cached result sets that did not get
invalidated prior to caching the result set.
Invalidation Count - the number of
cached result sets that got invalidated due to database
changes that could affect the result set.
The ratio of Create Count
Success to Invalidation Count will provide a metric which
determines if the use of the result cache is inefficient
due to frequent changes on the database object. As
mentioned previously, the client cache should be used for
tables that are either read-only or near read-only.
If the result cache is used for tables which have frequent
changes like inserts, updates and deletes, it will force
the database to spend resources to ensure consistency
between the client cache and the database object.
Further detail about this dynamic view
can be found in the documentation for Oracle Database
A virtual column is an expression based
on one or more existing columns in the table. As
previously discussed in the section for Virtual Column-Based
Partitioning, using a virtual column in a table provides
advantages in disk space utilization. While a virtual
column is only stored as metadata, and does not consume
physical space, it can be indexed. The virtual column
also contains optimizer statistics and histograms.
Using a virtual column also simplifies
the use of derived columns. This is done by transparently
deriving the values instead of requiring the application to
calculate and insert an additional value. It also
prevents the need to use a trigger on the table to provide
an alternate implementation of this functionality.
Another benefit of using virtual columns in tables is
eliminating the need to use views to display derived column
To define a new table with a virtual
column, use the following syntax:
create table <table_name>(
<column_name> [<data_type>] [generated always] as (<column_expression>)
In order to add a virtual column to an
existing table, use the alter table syntax:
alter table <table_name>
(<column_name> [<data_type>] [generated always] as (<column_expression>)
When defining a virtual column in a
table, a DBA could either include the datatype or let the
database determine the datatype based on the expression.
The phrases ?generated always? and ?virtual? can be
optionally used for syntactic clarity. Keep in mind that the
column expression must reference columns defined on the same
table. However, the column expression can refer to a
PL/SQL function if the function is designated DETERMINISTIC
during its creation.
As an example, a virtual column might be
helpful in a table that stores employee information:
SQL> create table employees(
2 employee_name varchar2(30),
3 start_date date,
4 end_date date,
5 hourly_rate generated always as (annual_salary/2080),
6 annual_salary number,
7 active as (case when end_date is null then 'Y'
else 'N' end));
SQL> insert into employees
7 ('V.J. JAIN', '01-AUG-2005', NULL, 100000);
SQL> select * from employees
EMPLOYEE_NAME START_DATE END_DATE
HOURLY_RATE ANNUAL_SALARY ACTIVE
--------------- ---------- --------- -----------
V.J. JAIN 01-AUG-05
While the insert statement required to
create a new record in this table only requires four values,
a query of this table displays all six values. This
includes the two virtual columns derived from other columns.
The first virtual column, hourly rate, is an expression of
the annual salary divided by 2080 hours in a year. The
second virtual column displays if the employee is active by
examining the row's end date.
Virtual columns can be used for
partitioning, indexing, constraints and foreign keys.
However, virtual columns cannot be used for index-organized,
external, object, cluster, or temporary tables.
Note that you cannot perform updates or
deletes directly on virtual columns since they are