Thursday, April 28, 2011

Materialized View


Everytime you use a view oracle has to execute the sql statement defined for that view (called view resolution), it must be done each time the view is used. If the view is complex this can take sometime, this is where a materialized views comes in, unlike a view it contains space and storage just like a regular table. You can even partition them and create indexes on them. Materialized views take a snapshot of the underlying tables which means that data may not represent the source data. To get the materialized view data up to date you must refresh it. Creating materialized views is simple but optimizing it can be tricky, keeping the data up to date and also getting the CBO (cost based optimizer) to use the view. As with view materialized views can be inserted, updated and deleted from.

There are 3 types of materialized views:

Readonly Materialized view
Cannot be updated and complex materialized views are supported
Updateable Materialized view
can be updated even when disconnected from the master site, are refreshed on demand and consume fewer resources but requires advanced replication option to be installed
Writeable Materialized view
are created with the for update clause, any changes are lost when the view is updated this also requiresadvanced replication option to be installed.

When creating a materialized view, you have the option of specifying whether the refresh occurs ON DEMAND or ON COMMIT. In the case of ON COMMIT, the materialized view is changed every time a transaction commits, thus ensuring that the materialized view always contains the latest data. Alternatively, you can control the time when refresh of the materialized views occurs by specifying ON DEMAND. In this case, the materialized view can only be refreshed by calling one of the procedures in the DBMS_MVIEW package.

DBMS_MVIEW provides three different types of refresh operations:
DBMS_MVIEW.REFRESH ------Refresh one or more materialized views.
DBMS_MVIEW.REFRESH_ALL_MVIEWS -----Refresh all materialized views.
DBMS_MVIEW.REFRESH_DEPENDENT ----Refresh all materialized views that depend on a specified master table or materialized view or list of master tables or materialized views. 

Materialized View Refresh
To ensure that a materialized view is consistent with its master table or master materialized view, you must refresh the materialized view periodically. Oracle provides the following three methods to refresh materialized views:
    ■ Fast refresh uses materialized view logs to update only the rows that have changed since the last refresh.
    ■ Complete refresh updates the entire materialized view.
    ■ Force refresh performs a fast refresh when possible. When a fast refresh is not possible,force refresh performs a complete refresh.

1. INTRODUCTION
===============
A materialized view is a replica of a target master from a single point in time.
The concept was first introduced with Oracle7 termed as SNAPSHOT.
NOTE :   Materialized views can be used for many purposes, including:
·         Denormalization
·         Validation
·         Data Warehousing
·         Replication.

2. Usage of Materialized Views
=======================

Materialized views can be used both for:
   - creating summaries to be utilized in data warehouse environments
   - replicating data in distributed environments


3. Refreshing Materialized Views
==========================

Initially, a materialized view contains the same data as in the master table.
After the materialized view is created, changes can be made to the master table, and possibly also to the materialized view. To keep a materialized view's data relatively current with the data in the master table, the materialized view must be periodically refreshed. Refresh can be accomplished by one of the following procedures

   dbms_mview.refresh( '<mview list>', '<Refresh Type>' )

   dbms_refresh.refresh( '<refresh group>' )

You can choose between Complete, Fast, and Force refresh types.

4. Materialized View Types
======================

Read-Only Materialized Views
----------------------------------------
As the name implies it is not possible to perform DML on snapshots in this category.

Updatable Materialized Views
----------------------------------------
Updatable materialized views eliminate the restriction of DMLs on snapshots. Users are allowed to insert, update and delete rows of the updatable materialized view.

Subquery Materialized Views
---------------------------------------
Materialized views that are created with subqueries in the WHERE clause of the mview query are referred to as subquery materialized views.

Multitier Materialized Views
----------------------------
A multitier materialized view is a materialized view whose master table is itself a materialized view. This feature enables fast refresh of materialized views that have materialized views as their masters.


5. Data type Support
================

The following datatypes are supported in snapshot replication:
 - VARCHAR2
 - NVARCHAR2
 - NUMBER
 - DATE
 - TIMESTAMP
 - TIMESTAMP WITH TIME ZONE
 - TIMESTAMP LOCAL TIME ZONE
 - INTERVAL YEAR TO MONTH
 - INTERVAL DAY TO SECOND
 - RAW
 - ROWID
 - CHAR
 - NCHAR
 - User-defined data types
 - Binary LOB (BLOB)
 - Character LOB (CLOB)
 - National character LOB (NCLOB)
 - UROWID (supported only for readonly materialized views)

The following types are NOT supported in snapshot replication:
 - LONG
 - LONG RAW
 - BFILE
 - UROWID (not supported for updatable snapshots)


NOTE :   3 distinct types of users perform operations on materialized views:
                Creator: The user who creates the materialized view.
                Refresher: The user who refreshes the materialized view.
                Owner: The user who owns the materialized view. The materialized view resides in this user's schema

Read more:  
Master Note for Materialized View (MVIEW) (Doc ID 1353040.1)



Wednesday, April 27, 2011

DELETE & TRUNCATE differences

.
                    DELETE
         TRUNCATE
DML  command

NOT Auto-commit

We can ROLLBACK

WHERE clause Supported

It will fire TRIGGERS

SLOWER

Does not reset HIGH WATER MARK

can delete SELECTIVE ROWS from table using where clause

Delete will generate REDO LOG entries
 DDL command

Auto-commit

We cannot ROLLBACK

WHERE clause NOT Supported

It will NOT fire TRIGGERS

FASTER

RESETS HIGH WATER MARK




CANNOT be used for selective rows


Truncate DOES NOT Generate Redo log entries

FUNCTION & PROCEDURE Differences

.
                     FUNCTION
         PROCEDURE
functions can be used in select or update or delete statement while

A Function can be used in the SQL Queries

The functions are used to do the calculations instead of doing in query

Function should return value


Function can be called from select/where/having clause

The function cannot run independently, It has to be the part of the SQL statement


Function cannot call the stored procedures.


The temporary table cannot be created in the function.

But the function can do only select operation.

Function cannot have the transaction statements.

But the function can create, update and delete the table variable. It can return table variable.

procedure can't


procedure cannot be used in SQL queries 

The stored procedure normally used to perform a speck task. 

Stored procedures may or may not return values

Stored procedure cannot be used in the select/where/having clause

Stored procedure can run independently. It can be executed using EXECUTE or EXEC command

Stored procedure can call the user defined functions

The stored procedure allows us to create the temp tables in the stored procedure.

Stored procedures can have select and all DML operations.

Stored procedure can use transaction statements.

Stored procedures can create table variable and cannot return the table variable.

Tuesday, April 26, 2011

Latch & Cursor_sharing


What is cursor_sharing parameters ?

CURSOR_SHARING determines what kind of SQL statements can share the same cursors.

What is possible values for this parameter ?

1. EXACT (default)

Only allows statements with identical text to share the same cursor.

2. FORCE 
Forces statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect the meaning of the statement.

3. SIMILAR
Causes statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect either the meaning of the statement or the degree to which the plan is optimized.

When we have to / should use this parameter ?


Whenever you have lots of some sql statement to execute but differ in liternal and your application is not design to use BIND VARIABLE that time you can use CURSOR_SHARING=FORCE to share cursor for every sql statement which differ only in literal.

Consider setting CURSOR_SHARING to SIMILAR or FORCE if both of the following questions are true:

Are there statements in the shared pool that differ only in the values of literals? 
Is the response time low due to a very high number of library cache misses? 
Performance improvement when we set cursor_sharing=force ?

When your application use lots of similar sql statement but differ in literal then yes performance will improve when you set cursor_sharing=force.

Side Effects on database when set cursor_sharing=FORCE/SIMILAR

Forcing cursor sharing among similar (but not identical) statements can have unexpected results in some DSS applications, or applications that use stored outlines.

Oracle does not recommend setting CURSOR_SHARING to FORCE in a DSS environment or if you are using complex queries. Also, star transformation is not supported with CURSOR_SHARING set to either SIMILAR or FORCE. For more information, see the "OPTIMIZER_FEATURES_ENABLE Parameter".
We need to down our database to set this parameter

No, we can set this parameter when our database is open.

Alter system set CURSOR_SHARING=force SCOPE=both;


What is Latch ?

A mechanism to protect shared data structures in the System Global Area.
For Example: latches protect the list of users currently accessing the database and protect the data structures describing the blocks in the buffer cache.

A server or background process acquires a latch for a very short time while manipulating or looking at one of these structures.


During DB performance we will see LATCH event ...so what is latch event and how many types of latch events?

A latch is a low-level internal lock used by 
Oracle to protect memory structures.

The latch free event is updated when a server process attempts to get a latch, and the latch is unavailable on the first attempt.
Most common type latch is Cache Buffer Latch and Cache buffer LRU chain latch which are caused for highly accessing blocks, called hot blocks. Contention on these latches is typically caused by concurrent access to a very hot block. The most common type of such hot block is index root or block branch.

Virtual IP in RAC


How new connection establish in Oracle RAC?

For failover configuration we should need to configure our physical ip of host name in listener configuration. Listener process is accepting new connection request and handover user process to server process or dispatcher process in Oracle.

Means using listener new connection is being established by Oracle. Once connection gets established there is no need of listener process. If new connection is trying to get session in database and listener is down then what will be happening. User process gets error message and connection fails. Because listener is down in same host or something else problem. But in Oracle RAC database environment database is in sharing mode. Oracle RAC database is shared by all connected nodes. Means more than 1 listeners are running in various nodes.

In Oracle RAC database if user process is trying to get connection with some listener and found listener is down or node is down then Oracle RAC automatically transfer this request to another listener on another node. Up to Oracle 9i we use physical IP address in listener configuration. Means if requested connection gets failed then it will be diverting to another node using physical IP address of another surviving node. But during this automatically transfer, connection should need to wait up to get error message of node down or listener down using TCP/IP connection timeout. Means session should need to wait up to getting TCP/IP timeout error dictation. Once error message is received oracle RAC automatically divert this new connection request to another surviving node.

Using physical IP address there is biggest gap to get TCP/IP timeout for failover suggestion. Session should need to wait for same timeout. High availability of Oracle RAC depends on this time wasting error message.

Why VIP (Virtual IP) needs in Oracle RAC?

From Oracle 10g, virtual IP considers to configure listener. Using virtual IP we can save our TCP/IP timeout problem because Oracle notification service maintains communication between each nodes and listeners. Once ONS found any listener down or node down, it will notify another nodes and listeners with same situation. While new connection is trying to establish connection to failure node or listener, virtual IP of failure node automatically divert to surviving node and session will be establishing in another surviving node. This process doesn't wait for TCP/IP timeout event. Due to this new connection gets faster session establishment to another surviving nodes/listener.

Characteristic of Virtual IP in Oracle RAC:

Virtual IP (VIP) is for fast connection establishment in failover dictation. Still we can use physical IP address in Oracle 10g in listener if we have no worry for failover timing. We can change default TCP/IP timeout using operating system utilities or commands and kept smaller. But taking advantage of VIP (Virtual IP address) in Oracle 10g RAC database is advisable. There is utility also provided to configure virtual IP (vip) with RAC environment called VIPCA. Default path is $ORA_CRS_HOME/bin. During installation of Oracle RAC, it is executed.

Advantage of Virtual IP deployment in Oracle RAC:

Using VIP configuration, client can be able to get connection fast even fail over of connection request to node. Because vip automatically assign to another surviving node faster and it can't wait for TNS timeout old fashion.

Disadvantage of Virtual IP deployment in Oracle RAC:

Some more configurations is needed in system for assign virtual IP address to nodes like in /etc/hosts and others. Some misunderstanding or confusion may occur due to multiple IP assigns in same node.

Important for VIP configuration:

The VIPs should be registered in the DNS. The VIP addresses must be on the same subnet as the public host network addresses. Each Virtual IP (VIP) configured requires an unused and resolvable IP address.


Auto Scroll Stop Scroll