Invisible Indexes in Oracle 11g

An invisible index is an index that is ignored by the optimizer unless we explicitly set the OPTIMIZER_USE_INVISIBLE_INDEXES initialization parameter to TRUE at the session or system level.   One use of the invisible index feature is to test the removal of an index before dropping it.  Prior to 11g, this was typically achieved by making an index unusable during a set period of time.  During this observation period, the DBA would monitor the database performance to determine whether or not to drop the index.  If performance was negatively affected, the index would need to be rebuilt before it could be used again.  Beginning with Oracle 11g, the DBA has the option of making the index  invisible as opposed to unusable during this observation period.  If performance degradation is observed, the index can be made visible again without having to rebuild the indexes.

Another potential use for invisible indexes is in situations where specific applications require an index temporarily.  An index can be created as invisible to allow specific SQL statements to use the index while leaving the rest of the database unaffected.  

The effective usage of an index is always a big question. There are instances in production environments, where an index may help the performance of just a single query, while it can degrade the performance of a number of queries. Always the Optimizer can’t guarantee the best suitable index required for the query in an environment which is cluttered by too many indexes on a table. Below is  demo of invisble Indexes 

SQL> create table test1 as select * from all_objects;
Table created.

SQL> select count(*) from test1;
COUNT(*)
-------------
 71480

SQL> create index obj_idx on test1(object_name) invisible;
Index created.

SQL> select index_name,VISIBILITY from user_indexes where index_name='OBJ_IDX';
INDEX_NAME                     VISIBILITY
-------------------                    --------------
OBJ_IDX                             INVISIBLE

There is a new initialization parameter modifiable at system or session level called 

OPTIMIZER_USE_INVISIBLE_INDEXES. This parameter is FALSE as default, meaning the optimizer will not consider invisible indexes when evaluating alternative execution plans.

SQL> sho parameter OPTIMIZER_USE_INVISIBLE_INDEXES
NAME                                                          TYPE          VALUE
---------------------------------------                 -----------      -------------
optimizer_use_invisible_indexes              boolean         FALSE

We can alter an existing index to become invisible or visible.

SQL> alter index OBJ_IDX visible;
Index altered.

SQL> select index_name,VISIBILITY from user_indexes where index_name='OBJ_IDX';
INDEX_NAME                     VISIBILITY
-------------------                 --------------
OBJ_IDX                            VISIBLE

SQL> alter index OBJ_IDX invisible;
Index altered.

SQL> select index_name,VISIBILITY from user_indexes where index_name='OBJ_IDX';
INDEX_NAME                     VISIBILITY
--------------------                   --------------
OBJ_IDX                            INVISIBLE

We can see that the invisible index is not considered by the optimizer using a 10053 trace.

SQL> alter session set events '10053 trace name context forever, level 1';
Session altered.

sql> explain plan for select * from TEST1 where OBJECT_NAME='ALL_OBJECTS';
Explained.

***************************************
BASE STATISTICAL INFORMATION
***************************************
Table Stats::
  Table: OBJ$  Alias: ITO
    #Rows: 73375  #Blks:  905  AvgRowLen:  83.00
Index Stats::
  Index: I_OBJ1  Col#: 1 3 7
    LVLS: 1  #LB: 206  #DK: 73375  LB/K: 1.00  DB/K: 1.00  CLUF: 963.00
UNUSABLE

SQL> explain plan for select * from test1 where object_name='ALL_OBJECTS';
Explained.

SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Plan hash value: 4122059633
--------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     2 |   194 |   286   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| TEST1 |     2 |   194 |   286   (1)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   1 - filter("OBJECT_NAME"='ALL_OBJECTS')

13 rows selected.

We can change the parameter OPTIMIZER_USE_INVISIBLE_INDEXES to see if the index will be used.

SQL> alter session set optimizer_use_invisible_indexes=true;
Session altered.

SQL> explain plan for select * from test1 where object_name='ALL_OBJECTS';
Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
Plan hash value: 1376202287
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 316 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 2 | 316 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IND | 2 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_NAME"='ALL_OBJECTS')
Note   ::    dynamic sampling used for this statement

14 rows selected.


Enjoy    :-)  

Virtual Column in Oracle 11g

Oracle 11g has introduced a new feature that allows us to create a "virtual column", an empty column that contains a function upon other table columns (the function itself is stored in the data dictionary). Oracle 11g enables us to store expressions directly in the base tables themselves as virtual columns.Virtual columns are more flexible than any of their prior alternatives.

The syntax for defining a virtual column is listed below.
column_name [datatype] [GENERATED ALWAYS] AS (expression) [VIRTUAL]

Creating a virtual column :     Here we will create a table witha single column as follows .

SQL> create table vir_tab 
     ( id number , 
       sal number , 
       bonus number generated always as (id + sal) virtual 
     );
 Table created.

We can see that the virtual column is generated from a simple expression involving the other columns in our table. Note that the VIRTUAL keyword is optional and is included for what Oracle calls "syntactic clarity"

Virtual column values are not stored on disk. They are generated at runtime using their associated expression (in our example, N1 + N2). This has some implications for the way we insert data into tables with virtual columns, as we can see below.

SQL> insert into vir_tab values (100,11000,11100);
INSERT INTO t VALUES (10, 20, 30)
            *
ERROR at line 1:
ORA-54013: INSERT operation disallowed on virtual columns

Hence ,we cannot explicitily add data to virtual columns, so we will attempt an insert into the physical columns only as follows

SQL> insert into vir_tab values(100,11000);
INSERT INTO t VALUES (10, 20)
            *
ERROR at line 1:
ORA-00947: not enough values

Here we see that we cannot insert columns,they are still considered part of the table's column list.This means that we must explicitily reference the physical columns in our insert statements, as follows .

SQL> insert into vir_tab (id,sal) values (100,11000);
1 row created.

Now we have successfully inserted the data and can query the table.

SQL> select * from vir_tab;

  ID        SAL      BONUS
---- ---------- ----------
 100      11000      11100


Indexes and Constraints on virtual columns :

SQL> create index sal_idx on vir_tab(sal);

For Constraint :

SQL> alter table vir_tab add constraint vir_id_pk primary key(id);

Benefits of Virtual Columns :

• Automatic re-computation of derived columns for ad-hoc query tools
• Reduction in redundant disk space for columns that must be derived from other columns (e.g. a MONTH column that is derived from another DATE column).
• Easier for interval partitioning

There are few restrictions on the virtual columns.
1.   We cannot write/insert the data into virtual columns.
2.   There is no support for index_organized, external, object, cluster, temporary tables.
3.   There is no support for Oracle-supplied datatypes, user-defined datatypes, LOBs, or LONG RAWs.

4.   We can partition the table based on virtual column .We can use below query to define virtual columns     defined in the users schema.

SQL> SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, HIDDEN_COLUMN
FROM USER_TAB_COLS   WHERE VIRTUAL_COLUMN = ‘YES’;
5. we can not create virtual columns on Temporary tables, object types, clusters, External tables and Index Organized Tables

Reference ::  http://www.oracle-developer.net/display.php?id=510


Enjoy     :-) 

Database Resident Connection Pooling in Oracle 11g

In traditional client/server architectures, there is a one-to-one correspondence between a user session and a database connection. In Web-based systems however, this may not be the case.

Web based systems are “stateless” in nature--when we visit a page, a database connection is established with the database and when the page loading is over, the connection to the database is severed. Later, when the user clicks again on the page, a new connection is established that is severed after the desired effect. This process makes it unnecessary to maintain a large number of simultaneous connections.

According to the Tom Kytes : 

Connection pooling is generally the practice of a middle tier (application server) getting N connections to a database.These connections are stored in a pool in the middle tier, an "array" if we will.  Each connection is set to "not in use" . When a user submits a web page to the application server, it runs a piece of code, our code says "i need to get to the database", instead of connecting right there and then (that takes time), it just goes to this pool and says "give me a connection please". the connect pool software marks the connection as "in use" and gives it to us.  We generate the page, format the html whatever -- and then return the connection to the pool where someone else can use it.In this fashion, using connections to the database, we can avoid the connect/disconnect overhead.

Establishing connections is expensive in overhead, so connection pooling is an important requirement in the apps. when a page needs database access, it allocates one of the already established connections out of the pool. After the work is done, the Web session returns the connection back to the pool.

The problem with traditional client-side or middle-tier connection pooling, however, is:

• Each pool is confined to a single middle-tier node.

• A proliferation of pools results in excessive number of pre-allocated database servers and excessive database server memory consumption.

• Workload is distributed unequally across pools.

To obviate these problems, Oracle Database 11g provides a server-side pool called Database Resident Connection Pool (DRCP). DRCP is available to all database clients that use the OCI driver including C, C++, and PHP.

Oracle Database 11g comes preinstalled with a default connection pool but it is shut down. To start it, use:
SQL> execute dbms_connection_pool.start_pool;
Now to connect to the pooled connections instead of a regular session, all we have to do is add a line (SERVER=POOLED) to the TNS entry as shown below :
e.g;

PRONE3_POOL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = xxxx)(PORT = 1521))
(CONNECT_DATA =
(SERVER = POOLED)
(SID = PRONE3)
)
)

The clients can connect to the connection pool, using the connect string PRONE3_POOL. That’s it. Now our apps will connect to the pool instead of the server. If we use the standard connect string without the TNSNAMES.ORA file, we can use the POOLED clause. For instance, in PHP, you will connect as:

$c = oci_pconnect(‘myuser’, ‘mypassword’,’xxxx/PRONE3:POOLED’);
or
$c = oci_pconnect(‘myuser’, ‘mypassword’,’PRONE3_POOLED’);

In the above description we started the default pool that comes with Oracle with the default options. we can use the procedure CONFIGURE_POOL in the supplied package DBMS_CONNECTION_POOL :

For More detail visit below links :
http://download.oracle.com/docs/cd/B14117_01/win.101/b10117/features001.htm
http://download.oracle.com/docs/cd/B10501_01/java.920/a96654/connpoca.htm


Enjoy     :-)