How to check the disk utilization where ASMLIB is configured

Get the list all physical disk that are mapped to ASM disk by using following shell script,

[root@todbex01 mp]# cat asm_to_dik.sh#!/bin/bash
for asmlibdisk in `ls /dev/oracleasm/disks/*`
  do
    echo "ASMLIB disk name: $asmlibdisk"
    asmdisk=`kfed read $asmlibdisk | grep dskname | tr -s ' '| cut -f2 -d' '`
    echo "ASM disk name: $asmdisk"
    majorminor=`ls -l $asmlibdisk | tr -s ' ' | cut -f5,6 -d' '`
    device=`ls -l /dev | tr -s ' ' | grep -w "$majorminor" | cut -f10 -d' '`
    echo "Device path: /dev/$device"
  done

[root@todbex01 mp]# sh asm_to_dik.sh
ASMLIB disk name: /dev/oracleasm/disks/COR01
ASM disk name: OCR_VOTE_0000
Device path: /dev/dm-40
ASMLIB disk name: /dev/oracleasm/disks/COR02
ASM disk name: OCR_VOTE_0001
Device path: /dev/dm-37
ASMLIB disk name: /dev/oracleasm/disks/COR03
ASM disk name: OCR_VOTE_0002
Device path: /dev/dm-34
ASMLIB disk name: /dev/oracleasm/disks/DATA01
ASM disk name: ORADATA_0000
Device path: /dev/dm-24

You can get the same result by using oracleasm utility too,

[root@todbex01 mp]# /usr/sbin/oracleasm querydisk -v DATA01
Disk "DATA01" is a valid ASM disk
[root@todbex01 mp]# /usr/sbin/oracleasm querydisk -v -d DATA01
Disk "DATA01" is a valid ASM disk on device [252,24]
[root@todbex01 mp]# ls -l /dev | grep 252,|grep 24
brw-rw----  1 root root     252,  24 May 26  2015 dm-24
[root@todbex01 mp]#

Once you find out the physical disk associated with your diskgroup you can make use of iostat to get the I/O statistics for that particular disk .

Some information about iostat:-

iostat - Report Central Processing Unit (CPU) statistics and input/output statistics for devices and partitions.

Commonly used option:-
-c     The -c option is exclusive of the -d option and displays only the CPU usage report.

        that is iostat option -c, displays only the CPU usage statistics as shown below.
-d     The -d option is exclusive of the -c option and displays only the device utilization report.
-k    Display statistics in kilobytes per second instead of blocks per second. 

-m   Display statistics in megabyte per second instead of blocks per second.  

-n    Displays only the device and NFS statistics.
-x     Display  extended statistics.

Eg,
       iostat -d 2
              Display a continuous device report at two second intervals.(until you press Ctl-C).
       iostat -d 2 6
              Display six reports at two second intervals for all devices.

       iostat -x hda hdb 2 6
              Display six reports of extended statistics at two second intervals for devices hda and hdb.

       iostat -p sda 2 6
              Display six reports at two second intervals for device sda and all its partitions (sda1, etc.)

   
[root@todbex01 mp]#iostat -dkx /dev/dm-24
Linux 2.6.39-400.214.4.el5uek (todbex01)       03/02/2016

Device:         rrqm/s   wrqm/s   r/s   w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await  svctm  %util
dm-24             0.00     0.00 174.69  9.00 16721.00    41.19   182.50     0.16    0.88   0.12   2.20

The main figure that we have consider is the %util field,

%util: When this figure is consistently approaching above 80% you will need to take any of the following actions -

    increasing RAM so dependence on disk reduces
    increasing RAID controller cache so disk dependence decreases
    increasing number of disks so disk throughput increases (more spindles working parallely)

[root@todbex01 mp]#

To get the input/output statistics for all disks at two second intervals use the following command

[root@todbex01 mp]#iostat -dkx 2 

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 

Increasing Processes, Sessions and Transactions in Oracle

If your maximum number of process and/or sessions exceeds the limit ,oracle will throws following errors 
ORA-12516: TNS:listener could not find available handler with matching protocol stack
ORA-00020: maximum number of processes (%s) exceeded .
When this occurs, the service handlers for the TNS listener become "Blocked" and no new connections can be made.

Below query gives present allocation 
SQL>select name,value from v$parameter where name in ('processes','sessions','transactions');  

Below quey gives current number of process
SQL>select count(*) from v$process; 

Below quey gives current number of session
SQL>select count(*) from v$session;

Below quey gives current number of transaction
SQL>select count(*) from v$transaction;

General formula to calculate process,session and transaction
PROCESSES = 40 to Operating System Dependant
SESSIONS = (1.1 * PROCESSES) + 5
TRANSACTIONS = 1.1 * SESSIONS

ie For increasing process parameter you should consider increasing sesson and transactions parameter as well.

if we need to change do the following ,

SQL>alter system set processes = 1000 scope = spfile;

SQL>alter system set sessions = 1105 scope = spfile;

SQL>alter system set transactions = 1215 scope = spfile;

SQL>shutdown immediate;

SQL>startup;

For checking the current utilization , maximum utilization of process and session
SQL>SELECT upper(resource_name) as resource_name,current_utilization,max_utilization,initial_allocation FROM v$resource_limit WHERE resource_name in ('processes', 'sessions');

Partitioning an existing table using datapump - a sample demo

First create a sample  table for testing purpose, 
SQL>CREATE TABLE big_table (
  id            NUMBER(10),
  created_date  DATE,
  lookup_id     NUMBER(10),
  data          VARCHAR2(50)
);

Execute the following procedure to insert large number of rows ,

DECLARE
  l_lookup_id    NUMBER(10);
  l_create_date  DATE;
BEGIN
  FOR i IN 1 .. 1000000 LOOP
    IF MOD(i, 3) = 0 THEN
      l_create_date := ADD_MONTHS(SYSDATE, -24);
      l_lookup_id   := 2;
    ELSIF MOD(i, 2) = 0 THEN
      l_create_date := ADD_MONTHS(SYSDATE, -12);
      l_lookup_id   := 1;
    ELSE
      l_create_date := SYSDATE;
      l_lookup_id   := 3;
    END IF;
    INSERT INTO big_table (id, created_date, lookup_id, data)
    VALUES (i, l_create_date, l_lookup_id, 'This is some data for ' || i);
  END LOOP;
  COMMIT;
END;
/
-- Apply some constraints and index to the table.

SQL> ALTER TABLE big_table ADD (CONSTRAINT big_table_pk PRIMARY KEY (id));

SQL>CREATE INDEX bita_created_date_i ON big_table(created_date);

Now big_table is created 

SQL>  select table_name, tablespace_name, partitioned
      from dba_tables where table_name like 'BIG_%';

TABLE_NAME                     TABLESPACE_NAME                PAR
------------------------------ ------------------------------ ---
BIG_TABLE                      EXAMPLE                        NO  ----->(No partition)

Export the table using datapump:
C:\Windows\system32>expdp admin/admin@oradb1 tables=sample.big_table directory=ar1 dumpfile=big_table.dmp parallel=4

Screen shot of my expdp:
--------------------------
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "ADMIN"."SYS_EXPORT_TABLE_01":  admin/********@oradb1 tables=sample.big_table directory=ar1 dumpfile=big_table.dmp parallel=4
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 55 MB
. . exported "SAMPLE"."BIG_TABLE"                        46.61 MB 1000000 rows
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Master table "ADMIN"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ADMIN.SYS_EXPORT_TABLE_01 is:
  /u01/software/datapump/big_table.dmp
Job "ADMIN"."SYS_EXPORT_TABLE_01" successfully completed at 14:52:38

Now drop and recreate the table ,
SQL> drop table big_table;

Table dropped.

SQL> CREATE TABLE big_table (
    id            NUMBER(10),
    created_date  DATE,
    lookup_id     NUMBER(10),
    data          VARCHAR2(50)
)
PARTITION BY RANGE (created_date)
(PARTITION big_table_2003 VALUES LESS THAN (TO_DATE('01/01/2004', 'DD/MM/YYYY')),
 PARTITION big_table_2004 VALUES LESS THAN (TO_DATE('01/01/2005', 'DD/MM/YYYY')),
 PARTITION big_table_2005 VALUES LESS THAN (MAXVALUE));

SQL>  select table_name, tablespace_name, partitioned
      from dba_tables where table_name like 'BIG_%';

TABLE_NAME                     TABLESPACE_NAME                PAR
------------------------------ ------------------------------ ---
BIG_TABLE                      EXAMPLE                        YES ----> TABLE IS PARTITIONED

SQL> select count(*) from big_table;

  COUNT(*)
----------
         0  ----------> no data

Import the table using datapump
C:\Windows\system32> impdp admin/admin@oradb1 directory=ar1 dumpfile=big_table.dmp logfile=big_table.log table_exists_action=append parallel=4

note: job will be completed with one error because of "TABLE_EXISTS_ACTION=append" parameter.Use ignore=y if you are using old export utility.
Exclude Metadata and copy only data to avoid the error caused by the above parameter

screen shot:
-------------
Import: Release 11.2.0.1.0 - Production on Tue Jun 28 15:11:53 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "ADMIN"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "ADMIN"."SYS_IMPORT_FULL_01":  admin/********@oradb1 directory=ar1 dumpfile=big_table.dmp logfile=big_table.log table_exists_action=append parallel=4
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39152: Table "SAMPLE"."BIG_TABLE" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SAMPLE"."BIG_TABLE"                        46.61 MB 1000000 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Job "ADMIN"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 15:12:49

SQL> select count(*) from big_table;

  COUNT(*)
----------
   1000000


SQL> insert into big_table (id, created_date, lookup_id, data)
  2  values(1001,'27-jun-03',5,'This is some old data');

1 row created.


SQL> select * from big_table partition(big_table_2004);

no rows selected

SQL> select * from big_table partition(big_table_2003);

        ID CREATED_D  LOOKUP_ID DATA
---------- --------- ---------- -------------------------
      1001 27-JUN-03          5 This is some old data

TESTING PARTITION PRUNING

SQL>  SET AUTOTRACE TRACEONLY EXPLAIN
SQL> select * from big_table
  2  where created_date='27-JUN-03';

Execution Plan
----------------------------------------------------------
Plan hash value: 1710340555

----------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |     1 |    37 |     3   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|           |     1 |    37 |     3   (0)| 00:00:01 |   KEY |   KEY |
|*  2 |   TABLE ACCESS FULL    | BIG_TABLE |     1 |    37 |     3   (0)| 00:00:01 |   KEY |   KEY |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("CREATED_DATE"='27-JUN-03')

Source:- http://arundaskalathil.blogspot.in/2011/06/partitioning-existing-table-using.html