Different state of an oracle session -

While goggling I got a good notes on different state and status of a oracle session .So I thought of sharing it here . 

First, we have V$SESSION.STATUS, which can be ACTIVE or INACTIVE. A session is ACTIVE if it's in a database call. So, think of this from the client side point of view. A session does a parse call, or an execute call, or a fetch call, etc. For the duration of that call, till control returns back to the client, that session is said to be ACTIVE.From the time the calls returns, till the time of the next call, the session is INACTIVE.

In other words: what is an inactive session? It depends of the architecture of your applications, but just some examples:
- interactive sqlplus session of a user: the user sends statements, receives results, thinks about next statement, ... The session is INACTIVE most of the time (active only during the execution of the statements)
- batch job: statements are followed by each other quickly, the session is often "active", except if there is an interaction with the client.
- usual 3-tier application with or without session pooling: most of the time the sessions are "idle", waiting the next instruction from the client (who for example is filling a form on the screen before clicking on the "submit" button) 

Consider an batch job fetching large amounts of data from the database in 1000 row chunks, and then processing those rows on a middle tier before fetching the next 1000 rows. If the middle tier takes any length of time to process those rows, then the database will show the session as INACTIVE ie waiting on SQL*NET message from client. If you kill "INACTIVE" processes then you would likely kill this and have to rerun the batch job.

So simply being connected to the database DO NOT make your session active. You can check it by making a simple connection to a database and by checking it's status . It should be in INACTIVE status .

A session is active ONLY when it executes a STATEMENT in the database,and becomes inactive again when that statement is finished. INACTIVE session usually DOES NOT consume resources like CPU, IO. It may consume some memory, for example for session level package data, but this is usually not significant. The biggest problem with INACTIVE sessions might be that such a session can hold data locks that block other sessions and prevent them from proceeding.

A session with STATUS of INACTIVE, will always be in STATE of WAITING, waiting on the 'SQL*NET message from client' wait. So, in that case, it means the server process is waiting around for work to do. It's in between calls, so, STATUS is INACTIVE, and it's waiting on that network port, to receive the next call from the client.

Inactive sessions are waiting, Most likely they are waiting on a user who may be in the middle of a multi step transaction, between transactions, or idle.So its not always a good idea to kill INACTIVE session , as it may contains session that are not yet committed. So Forget about the idea of killing any inactive session in the database if you don't exactly know WHY you need to kill it.

Next, we have V$SESSION.STATE. This is probably more useful to think of from the server process point of view. This refers to whether the server process is currently running, i.e. on the CPU, or WAITING, i.e., waiting on a resource. Possible values for this column are WAITING, WAITED KNOWN TIME, WAITED SHORT TIME, and WAITED UNKNOWN TIME. Of those possibilities, a session is only actually waiting if STATE is WAITING. All the other values mean that it's no longer waiting, but is running on CPU.
 
You can establish a policy to automatically disconnect sessions that were IDLE for more than a given time ( by using PROFILES ), but I am not sure that the users will thank you for doing this .An Oracle session status will show as SNIPED when you set the idle_time parameter to disconnect inactive sessions. 

An example of a session that's ACTIVE and has STATE of WAITING, would be a session that's, for example, doing a full table scan , gc cr multi block request etc . So for full table scan, it's got lots of data to read from disk. While the session waits for the read from disk to complete, the session waits on 'db file scattered read'.
 
Finally, for completeness, the difference between the different possible values of the STATE column. I already covered WAITING. If a session is not waiting, it's now on CPU, and it previously waited. If so, it either waited more than 10 ms, in which case it will report WAITED KNOWN TIME, or less than 10 ms, in which case it reports WAITED SHORT TIME, or timed_statistics is false, in which case this column will always be WAITED UNKNOWN TIME. Also, it's important to pay attention to this column, when trying to interpret the WAIT_TIME and SECONDS_IN_WAIT columns. 

How to Refresh a Materialized View in Parallel

Parallel execution enables multiple processes to work simultaneously to refresh the materializedview, resulting in speeding up the refresh process. Before you enable parallel execution in your database, you should ensure that you have enough resources (CPU and Memory) to run multiple processes in parallel. Once you decide to use parallel execution, you should set the initialization parameter PARALLEL_AUTOMATIC_TUNING to TRUE. This enables Oracle to perform automatic tuning of the parallel execution environment.

Now that you have enabled parallel execution in the database, you can employ it while

refreshing the materialized view. In the following sections, we will discuss various ways to

invoke parallel refresh.

• The PARALLELISM parameter of the DBMS_MVIEW.REFRESH procedure

• PARALLEL attribute of the materialized view

• PARALLEL attribute of the master table(s)

• PARALLEL hint in the query defining the materialized view

The PARALLELISM Parameter of the DBMS_MVIEW.REFRESH Procedure

The REFRESH procedure of the supplied package DBMS_MVIEW can be used to refresh a

materialized view. Among other parameters, this procedure takes one parameter

PARALLELISM. You can invoke this procedure as:

EXECUTE DBMS_MVIEW.REFRESH(LIST=>'MV_PART_SALES',PARALLELISM=>4);

At the outset, it appears that the PARALLELISM parameter will invoke a parallel refresh of the

materialized view. However, it doesn’t. The dynamic performance views V$PX_PROCESS and

V$PX_SESSION provide information on the parallel execution processes and the sessions

using parallel execution respectively. When the above refresh process is running, if we query

these views, we will see that there are no parallel execution processes in use.

PARALLEL Attribute of the Materialized View

Now let’s create the materialized view with the PARALLEL attribute, and investigate the refresh

behavior.

CREATE MATERIALIZED VIEW MV_PART_SALES

PARALLEL 4

AS

SELECT PART_ID, SALE_DATE, SUM(QUANTITY)

FROM SALES_HISTORY

GROUP BY PART_ID, SALE_DATE;

EXECUTE DBMS_MVIEW.REFRESH(LIST=>'MV_PART_SALES',PARALLELISM=>4);

EXECUTE DBMS_MVIEW.REFRESH(LIST=>'MV_PART_SALES');

When the materialized view is created with the PARALLEL clause, the creation process is

parallelized, but the refresh process is not. Whether or not you specify the PARALLELISM

parameter in the REFRESH clause, it doesn’t matter. The refresh will be executed in serial.

PARALLEL Attribute of the Master Table

Now let’s examine the impact of the PARALLEL attribute of the master table, instead of the

materialized view. We will alter the master table to set the PARALLEL attribute to 4, and then

create the materialized view without a PARALLEL clause.

ALTER TABLE SALES_HISTORY PARALLEL (DEGREE 4);

• Alter the table (or index) to indicate that Oracle should try to parallelize operations performed against it

DROP MATERIALIZED VIEW MV_PART_SALES;

CREATE MATERIALIZED VIEW MV_PART_SALES

AS

SELECT PART_ID, SALE_DATE, SUM(QUANTITY)

FROM SALES_HISTORY

GROUP BY PART_ID, SALE_DATE;

EXECUTE DBMS_MVIEW.REFRESH(LIST=>'MV_PART_SALES',PARALLELISM=>4);

EXECUTE DBMS_MVIEW.REFRESH(LIST=>'MV_PART_SALES');

When the master table of the materialized view has the PARALLEL attribute set to > 1, then the

creation as well as the refresh processes will be parallelized. Whether or not you specify the

PARALLELISM parameter in the REFRESH clause, it doesn’t matter.

PARALLEL Hint in the Query Defining the Materialized View

Now let’s examine the refresh behavior by putting a parallel hint in the materialized view

definition.

CREATE MATERIALIZED VIEW MV_PART_SALES

AS

SELECT /*+ PARALLEL(SALES_HISTORY, 4) */

 PART_ID, SALE_DATE, SUM(QUANTITY)

FROM SALES_HISTORY

GROUP BY PART_ID, SALE_DATE;

EXECUTE DBMS_MVIEW.REFRESH(LIST=>'MV_PART_SALES',PARALLELISM=>4);

EXECUTE DBMS_MVIEW.REFRESH(LIST=>'MV_PART_SALES');

When the materialized view is created with a PARALLEL hint, then the creation as well as the

refresh processes will be parallelized. Whether or not you specify the PARALLELISM parameterin the REFRESH clause, it doesn’t matter.

Source: Sanjay Mishra's sql tuning book 

Materialized view a study notes

Every time you use a normal 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 (also known as snapshots in prior releases), unlike a view it contains space and storage just like a regular table.You can either use materialized view against a local table or a remote table .Using materialized views against remote tables is the simplest way to achieve replication of data between sites.

When we see the performance of Materialized view it is better than normal View because the data of materialized view will stored in table and table may be indexed so faster for joining also joining is done at the time of materialized views refresh time so no need to every time fire join statement as in case of view.

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.

Materialized Views are mainly used for two reasons,
1) Replication of data to separate remote databases.
2) For improving the performance of queries by computing and storing the results of complex aggregations of data.

With Materialized Views the performance can be improved significantly, because when a materialized view is created it stores all the data along with the execution plans. 

Basic syntax:-
CREATE MATERIALIZED VIEW view-name
BUILD [IMMEDIATE | DEFERRED]
REFRESH [FAST | COMPLETE | FORCE ]
ON [COMMIT | DEMAND ]
[[ENABLE | DISABLE] QUERY REWRITE]
[ON PREBUILT TABLE]
AS
SELECT ...;

This is a very basic syntax , even you can specify storage level parameter while configuring materialized view .

The BUILD clause options are shown below,
IMMEDIATE(Default) : The materialized view is populated immediately.
DEFERRED : The materialized view is populated on the first requested refresh.

The REFRESH clause options are shown below,
FAST : A fast refresh is attempted. If materialized view logs are not present against the source tables in advance, the creation fails.

COMPLETE : The table segment supporting the materialized view is truncated and repopulated completely using the associated query.- and its time consuming .
Note: If a materialized view is complete refreshed, then set it's PCTFREE to 0 and PCTUSED to 99 for maximum efficiency.

FORCE (Default): A fast refresh is attempted. If one is not possible a complete refresh is performed.If you do not specify a refresh method (FAST, COMPLETE, or FORCE), then FORCE is the default.

Note:- A materialized view get locked while its being refreshed .

A refresh can be triggered in one of two ways,
ON COMMIT : Specify ON COMMIT to indicate that a fast refresh is to occur whenever the database commits a transaction that operates on a master table of the materialized view. This clause may increase the time taken to complete the commit, because the database performs the refresh operation as part of the commit process.

Note:- If you want to refresh materialized view automatically they you must set job_queue_processes=n 

ON DEMAND (Default)  : The refresh is initiated by a manual request or a scheduled task.
if your refresh interval is very large and you need to refresh in between use the follwoing procedure.

SQL> EXEC DBMS_MVIEW.REFRESH('mv_name', 'C');
C -> Complete refresh 
F ->FAST refresh 
? -> Force refresh

When you create a materialized view a table segment(with same name) will get automatically created to hold the data represented by the materialized view .

If you have a large table , then creating materialized through normal method take time, especially when base table on a remote box. In this case you can make use of PREBUILT TABLE . Here you can export the  table from remote box and import the table on the target box .Depends on the requirement you can either import the full table or import a part of the table by using impdp parameter QUERY.

That is ON PREBUILT TABLE clause tells the database to use an existing table segment, which must have the same name as the materialized view and support the same column structure as the query. 

One simple example  is given below , 

SQL> conn hr/hr
Connected.
SQL> create table my_objects as select object_name,object_type,created from user_objects;

Table created.

SQL> create materialized view mv_objects on prebuilt table as select object_name,object_type,created from user_objects;
create materialized view my_objectsd on prebuilt table as select object_name,object_type,created from user_objects
     *
ERROR at line 1:
ORA-12059: prebuilt table "HR"."MV_OBJECTS" does not exist

That is for materialized view we need to use the same name that we used for prebuilt table.
SQL> create materialized view my_objects on prebuilt table as select object_name,object_type,created from user_objects;

Materialized view created.

SQL> create table t as select * from employees;

Table created.

SQL> col object_name for a15
SQL> set lines 222
SQL> select object_name,object_type,created from user_objects where object_name='T';

OBJECT_NAME     OBJECT_TYPE             CREATED
--------------- ----------------------- ---------
T               TABLE                   07-FEB-16

Check the contents of the materialized view 

SQL>  select object_name,object_type,created from my_objects where object_name='T';

no rows selected

As Materialized view is not yet refreshed , I did a manual refresh .

SQL> EXEC DBMS_MVIEW.REFRESH('MY_OBJECTS','C');

PL/SQL procedure successfully completed.

SQL> select object_name,object_type,created from my_objects where object_name='T';

OBJECT_NAME     OBJECT_TYPE             CREATED
--------------- ----------------------- ---------
T               TABLE                   07-FEB-16

SQL>

Note:- You can't do any DML on the underlying  table that hold the data of the materialized view . if you tried to do so , you will get error like following .

SQL> insert into MY_OBJECTS values ('SAMPLE','TABLE','07-FEB-16');
insert into MY_OBJECTS values ('SAMPLE','TABLE','07-FEB-16')
            *
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view
SQL>

ENABLE QUERY REWRITE
A materialized view can be stored in the same database as it's base table(s) or in a different database. Materialized views stored in the same database as their base tables can improve query performance through query rewrites. Query rewrites are particularly useful in a data warehouse environment.

The QUERY REWRITE clause tells the optimizer if the materialized view should be consider for query rewrite operations. 

The following query does an aggregation of the data in the EMP table.

CONN scott/tiger
SET AUTOTRACE TRACE EXPLAIN

SELECT deptno, SUM(sal)
FROM   emp
GROUP BY deptno;

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     3 |    21 |     4  (25)| 00:00:01 |
|   1 |  HASH GROUP BY     |      |     3 |    21 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |    98 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Create a materialized view to perform the aggregation in advance, making sure you specify the ENABLE QUERY REWRITE clause.

CREATE MATERIALIZED VIEW emp_aggr_mv
BUILD IMMEDIATE 
REFRESH FORCE
ON DEMAND
ENABLE QUERY REWRITE 
AS
SELECT deptno, SUM(sal) AS sal_by_dept
FROM   emp
GROUP BY deptno;

EXEC DBMS_STATS.gather_table_stats(USER, 'EMP_AGGR_MV');

The same query is now rewritten to take advantage of the pre-aggregated data in the materialized view, instead of the session doing the work for itself.

--ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED; 
--ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE;
SET AUTOTRACE TRACE EXPLAIN

SELECT deptno, SUM(sal)
FROM   emp
GROUP BY deptno;

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |     3 |    21 |     3   (0)| 00:00:01 |
|   1 |  MAT_VIEW REWRITE ACCESS FULL| EMP_AGGR_MV |     3 |    21 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

More example of the query rewrite functionality is given in  below link.
http://www.sqlsnippets.com/en/topic-12918.html

CREATE MATERIALIZED VIEW LOG
A complete refreshes of materialized views can be expensive operations. Fortunately there is a way to refresh only the changed rows in a materialized view's base table. This is called fast refreshing. Before a materialized view can perform a fast refresh however it needs a mechanism to capture any changes made to its base table. This mechanism is called a Materialized View Log. 

Common usage syntax:- 
create materialized view log on table with primarykey|rowid ;
where ,
PRIMARY KEY 
Specify PRIMARY KEY to indicate that the primary key of all rows changed should be recorded in the materialized view log(this is default)
Note:- Specify WITH PRIMARY KEY to create a primary key materialized view. This is the default and should be used in all cases except those described for WITH ROWID. 
ROWID 
Specify ROWID to indicate that the rowid of all rows changed should be recorded in the materialized view log.

Note:- The materialized view log supports fast refresh for primary key materialized views only. If you omit this clause, or if you specify the clause without PRIMARY KEY, ROWID, or OBJECT ID, then the database stores primary key values by default. However, the database does not store primary key values implicitly if you specify only OBJECT ID or ROWID at create time.

If your table have a PRIMARY KEY, you don't need to creata materialized view log WITH ROWID. If you don't have a PRIMARY KEY, you have to add WITH ROWID.

As more often rowids can change.Partitioned tables with enable row movement allow rowids  to change.  ALTER TABLE t MOVE will change rowids.  In 10g more and more things will  change rowids.So oracle recommented to create primary key based materialized view log.

Eg:- create materialized view log on employees with primary key;

Note how the materialized view log is not given a name. This is because a table can only ever have one materialized view log related to it at a time, so a name is not required.

Basic example for creating a materialized view ,

CREATE MATERIALIZED VIEW mv_emp_pk
 REFRESH FAST START WITH SYSDATE 
 NEXT  SYSDATE + 1/1440 ---- > every one minute 
 WITH PRIMARY KEY -- this is default , no need to specify though 
 AS SELECT employee_id,name,salary FROM emp@remote_db;

ON COMMIT
In some situations it would be convenient to have Oracle refresh a materialized view automatically whenever changes to the base table are committed. This is possible using the ON COMMIT refresh mode. Here is an example.

Assuming that employee table  have primary key , so create a materialized view log on employee table.If your table does'nt have a primary key defined you will get error like ORA-12014: table does not contain primary key constraint.
sql>conn hr/hr
Connected
SQL> create materialized view log on employees with primary key;
Materialized view log created.

You can see following table got created while creating materialized view log on employees table ,
MLOG$_EMPLOYEES:- This is a table created along with the materialized view. It contains data that has changed in the base table.
RUPD$_EMPLOYEES:- This table is created when a materialized view uses primary key for fast refresh. This is used to support updatable materialized views.

SQL> conn / as sysdba
Connected.
SQL> grant select any table to mahi;

Grant succeeded.

SQL> grant create any materialized view to mahi;

Grant succeeded.

SQL> create database link hrlink connect to hr identified by hr using 'study';

Database link created.

SQL> conn mahi/mahi
Connected.
SQL>
SQL> create materialized view mv_hr_employee REFRESH FAST ON COMMIT as select * from employees@hrlink;
create materialized view mv_hr_employee
*
ERROR at line 1:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

While googling I learned following - 
Things learned:- This materialized view is selecting from a remote table over a database link (a distributed materialized view). For "on commit", you can use only if you have your master table in the same database where you are creating the materialized view. Therefore, on commit is not supported in remote databases. 

SQL> conn hr/hr
Connected.
SQL> create materialized view mv_employee REFRESH FAST ON COMMIT as select * from employees;

Materialized view created.

SQL> select count(1) from employees;

  COUNT(1)
----------
       107
SQL> select count(1) from mv_employee;

  COUNT(1)
----------
       107
SQL> delete from employees where EMPLOYEE_ID=206;

1 row deleted.

SQL> select count(1) from employees;

  COUNT(1)
----------
       106
SQL> select count(1) from mv_employee;

  COUNT(1)
----------
       107
SQL> commit;

Commit complete.

SQL> select count(1) from mv_employee;

  COUNT(1)
----------
       106
SQL>

As soon as I put commit , my materialized view got reflected 

Check the details of the materialized view using following query ,
SQL> select mview_name, refresh_method, refresh_mode, build_mode, fast_refreshable from user_mviews where mview_name = 'MV_EMPLOYEE';

How to know when was the last refresh happened on materialized views:
SQL> select MVIEW_NAME, to_char(LAST_REFRESH_DATE,'YYYY-MM-DD HH24:MI:SS') from dba_mviews;
(or)
SQL> select MVIEW_NAME, to_char(LAST_REFRESH_DATE,'YYYY-MM-DD HH24:MI:SS') from dba_mview_analysis;
(or)
SQL> select NAME, to_char(LAST_REFRESH,'YYYY-MM-DD HH24:MI:SS') from dba_mview_refresh_times;

Difference between View vs Materialized View in database
Based upon on our understanding of View and Materialized View, Let’s see, some short difference between them :

1) First difference between View and materialized view is that, In Views query result is not stored in the disk or database but Materialized view allow to store query result in disk or table.

2) Another difference between View vs materialized view is that, when we create view using any table,  rowid of view is same as original table but in case of Materialized view rowid is different.

3) One more difference between View and materialized view in database is that, In case of View we always get latest data but in case of Materialized view we need to refresh the view for getting latest data.

4) Performance of View is less than Materialized view.

5) Last difference between View vs Materialized View is that, In case of Materialized view we need extra trigger or some automatic method so that we can keep MV refreshed, this is not required for views in database.

Points to note:- 
*It is  always recommended to gather the statistics of the underlying table after materialized view got created .

*Although materialized view logs improve the performance of materialized view refreshes, they do increase the work needed to perform DDL on the base table.

*If regular refreshes are not performed, materialized view logs can grow very large, potentially reducing the performance of their maintenance and blowing tablespace limits