Further to may last post , Oracle 11g new Features – Part 1 , I am covering some more features here.

Client Query Result Cache

Oracle 11g allows client’s memory to cache query result sets which significantly improves the performance of repetitive queries by OCI-based clients.

Benefits of Client Cache feature

• Extending server-side query caching to client side memory, by leveraging cheaper client-side memory and caching each application’s working set locally.
• Ensuring better performance by eliminating round trips to the server.
• Improving server scalability by saving server resources
• offering transparent cache management: memory management, concurrent access of result-sets.

To start using client cache – set following initialization parameter:
CLIENT_RESULT_CACHE_SIZE = 1G 

Note: This is the combined size of cache in all the clients, have a static value and need to bounce the database incase need to change this parameter.

Usage :

• Client-Side Query Result Cache comes in very handy for lookup tables
• Helpful in reducing CPU consumption on server and greatly increases query response time for mostly read-only tables

How to find result cache information:
V$CLIENT_RESULT_CACHE_STATS – Contain info about all application client query.

CLIENT_RESULT_CACHE_STATS$  – Provide statistics about how many client result cache exits.

Invisible Indexes

11g introduces invisible indexes, which can be used effectively to test performance of the query – whether removing indexes will improve the performance, without dropping them or marking them as unusable.

Invisible indexes ignored by optimizer unless the OPTIMIZER_USE_INVISIBLE_INDEXES parameter is set to TRUE at instance or session level.

Indexes can be created as invisible by using INVISIBLE keyword and be toggled by using ALTER INDEX command.

Syntax:
CREATE INDEX index_name ON table_name(column_name) INVISIBLE;
ALTER INDEX index_name INVISIBLE;
ALTER INDEX index_name VISIBLE;

Significance of Invisible indexes
Performance testing – can use invisible indexes for checking various explain plans without creating / dropping actual indexes by resetting VISIBLE / INVISIBLE parameters.
Save time in dropping and recreating indexes.
A visible index works as normal index and have no affect of optimizer parameter.

Dictionary Views
The current visibility status of an index is indicated by the VISIBILITY column of the  [DBA|ALL|USER]_INDEXES views.

Example:

CREATE TABLE test (
id NUMBER
);

BEGIN
FOR i IN 1 .. 10000 LOOP
INSERT INTO test VALUES (i);
END LOOP;
COMMIT;
END;
/

CREATE INDEX test_id ON test(id) INVISIBLE;

Note: A query using the indexed column in the WHERE clause ignores the index and does a full table scan.

SELECT * FROM test WHERE id = 9999;
—————————————————————————————————————-
| Id  | Operation             | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————————————————–
|   0 | SELECT STATEMENT      |            |     1     |     3   |     7   (0)    | 00:00:01 |
|*  1 |  TABLE ACCESS FULL| Itest    |     1     |     3   |     7   (0)    | 00:00:01 |
—————————————————————————————————————

ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE;
SELECT * FROM test WHERE id = 9999;
—————————————————————————————————————
| Id  | Operation            | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————————————————-
|   0 | SELECT STATEMENT     |               |     1      |     3     |     1   (0)       | 00:00:01 |
|*  1 |  INDEX RANGE SCAN| Itest_id     |     1      |     3     |     1   (0)       | 00:00:01 |
—————————————————————————————————————-

Virtual Columns

Virtual columns are derived by evaluating an expression. The expression can include column from same table, constants, SQL functions or user defined PL/SQL functions.

Syntax:
column_name [datatype] [GENERATED ALWAYS] AS (expression) [VIRTUAL]

Restrictions on virtual column expressions:
- Cannot refer another virtual column by name.
- Can refer only columns define on the same table.
- Output of the expression must be a scalar value.
-  If expression is user defined function then Virtual column cannot be used as a part of partition key.

Example:

CREATE TABLE employees (
id          NUMBER,
first_name  VARCHAR2(10),
last_name   VARCHAR2(10),
salary      NUMBER(9,2),
comm1       NUMBER(3),
comm2       NUMBER(3),
salary1     AS (ROUND(salary*(1+comm1/100),2)),
salary2     NUMBER GENERATED ALWAYS AS (ROUND(salary*(1+comm2/100),2)) VIRTUAL,
CONSTRAINT employees_pk PRIMARY KEY (id)
);

INSERT INTO employees (id, first_name, last_name, salary, comm1, comm2)
VALUES (1, ‘JOHN’, ‘DOE’, 100, 5, 10);

SELECT * FROM employees;
ID FIRST_NAME LAST_NAME      SALARY      COMM1      COMM2    SALARY1    SALARY2
———- ———- ———- ———- ———- ———- ———- ———-
1 JOHN       DOE                       100          5             10        105            110

Trigger Enhancements

here are three major enhancements in triggers:

ENABLE / DISABLE Triggers
11g allows to create triggers in disabled state, vs prior release allow only creation in enable mode.

CREATE TRIGGER test_trig
BEFORE INSERT ON EMP
DISABLED/ENABLED

Ordering of triggers
Oracle 11g – “FOLLOWS” clause controls the order in which the triggers fire when you have multiple triggers of same type on the same table.

CREATE OR REPLACE TRIGGER check_sal
BEFORE UPDATE OF sal
ON emp
FOR EACH ROW
FOLLOWS check_update_job
DECLARE

Compound Triggers

Oracle 11g introduced a new trigger which implements actions for all of the DML timing points in a single trigger.

A compound trigger allows different blocks within a trigger to be executed at different timing points. It has a declaration section and a section for each of its timing points.

Syntax
CREATE OR REPLACE TRIGGER  TRG1  FOR UPDATE ON T1
COMPOUND TRIGGER  total number(5);
BEFORE STATEMENT IS
BEGIN total := 0;
END     BEFORE STATEMENT;

Example:
In a product table, we need to ensure the total amount of increase in prices must not cross 1000 for all products put together in a single UPDATE statement.

Create table and insert data in database.
create table products (name varchar(10), price number(5));
insert into products values(‘a’,1000);
insert into products values(‘b’,3000);
insert into products values(‘c’,2300);
commit;
Update data in table
SQL> update products set price = price + 100;
3 rows updated.

SQL> update products set price = price + 500;
update products set price = price + 500
*
ERROR at line 1:
ORA-20100: Total increase in price cannot cross 1000

I hope you found these tips helpful. Give us a feedback of what you think and if I should have covered some more topics.

And if you are looking for interview questions , see it right here Oracle11g Interview questions

If preparing for IZ0- 0150 certification , you can find OCP 11g certification practice test