How to find high CPU using sessions in Oracle Database

If the problem relates to CPU bound applications then CPU information for each session can be examined to determine the culprits. The v$sesstat  view can be queried to find high cpu using sessions and then SQL can be listed.

1.Find the 'CPU used by this session' statistic.

SQL>SELECT name ,statistic# FROM v$statname WHERE  name LIKE '%CPU%session';

    NAME                                STATISTIC#
    ----------------------------------- ----------
    CPU used by this session                    14

2. Then determine which session is using most of the cpu.

SQL>SELECT * FROM v$sesstat WHERE statistic# = 14;

           SID STATISTIC#      VALUE
    ---------- ---------- ----------
             1         14          0
             2         14          0
             3         14          0
             4         14          0
             5         14          0
             6         14          0
             7         14          0
             8         14          0
             9         14          0
            10         14          0
            11         14          0
            12         14          0
            16         14       1930

3. Lookup details for the session which is using most of the cpu.

SQL>SELECT address ,SUBSTR(sql_text,1,20) Text, buffer_gets, executions,buffer_gets/executions AVG
    FROM   v$sqlarea a, v$session s
    WHERE  sid = 16
    AND    s.sql_address = a.address
    AND    executions > 0
    ORDER BY 5;

4. Use v$sqltext to extract the whole SQL text.

set pages 50000 lines 32767
col SPID for a10
col PROGRAM for a15
col OSUSER for a10
col ACTION for a10
col EVENT for a25
col SQL_TEXT for a25
col MACHINE for a10
col P1TEXT for a10
col P2TEXT for a10
col P3TEXT for a10
SELECT b.sid, b.serial#, a.spid, b.sql_id, b.program, b.osuser, b.machine, b.type,
b.event, b.action, b.p1text, b.p2text, b.p3text, b.state, c.sql_text,b.logon_time
FROM v$process a, v$session b, v$sqltext c WHERE a.addr=b.paddr
AND b.sql_hash_value = c.hash_value
AND b.sid = '&sid' ORDER BY a.spid, c.piece
/

5. Once the whole SQL statement has been identified it can be tuned.

Explain the queries and examine their access paths.
Autotrace is a useful tool for examining access paths.

Syntax:-

Explain plan for 'sql statement';

How to Move ASM DATABASE FILES from ONE DISKGROUP TO ANOTHER

Steps to Move ASM DATABASE FILES from ONE DISKGROUP TO ANOTHER

Since ASM files cannot be accessed through normal operating system interfaces, RMAN is the preferred means of copying ASM file.

The steps to moving a datafile from a diskgroup to another is as below, using RMAN.

1) Identify the datafile to be moved.
2) Identify the diskgroup on to which the datafile has to be moved.
3) Take the datafile offline.
4) Copy the datafile to new diskgroup using Either RMAN or DBMS_FILE_TRANSFER.
5) Rename the datafile to point to new location.
6) Recover the datafile.
7) Bring the datafile online.
8) Verify the new datafile locations.
9) Delete the datafile from its original location.

1) Identify the datafile to be moved.

SQL> SELECT FILE_NAME FROM DBA_DATA_FILES;
+ASMDISK2/orcl/datafile/users.256.565313879 <======= Move this to ASMDISK1.
+ASMDISK1/orcl/sysaux01.dbf
+ASMDISK1/orcl/undotbs01.dbf
+ASMDISK1/orcl/system01.dbf

2) Identify the diskgroup on to which the datafile has to be moved.

SQL> SELECT GROUP_NUMBER, NAME FROM V$ASM_DISKGROUP;
GROUP_NUMBER NAME
------------ ---------
1 ASMDISK1
2 ASMDISK2

3) Take the datafile offline.
SQL> ALTER DATABASE DATAFILE '+ASMDISK2/orcl/datafile/users.256.565313879' OFFLINE;

4) Copy the datafile to new diskgroup using Either RMAN or DBMS_FILE_TRANSFER.

   a)   DBMS_FILE_TRANSFER package or
   b)   RMAN
       
a).Using DBMS_FILE_TRANSFER package 
           
SQL> create or replace directory orcl1 as '+ASMDISK1/orcl/datafile';

SQL> Alter disgroup ASMDISK2 add directory  '+ASMDISK2/test';
       
SQL> create or replace directory orcl2 as '+ASMDISK2/test';

SQL>
BEGIN
     DBMS_FILE_TRANSFER.COPY_FILE(
     source_directory_object => 'ORCL1',
     source_file_name => 'users.259.565359071',
     destination_directory_object => 'ORCL2',
     destination_file_name => 'USERS01.DBF');
END;  
Database altered.

--------------------  OR   --------------------
  
b).Using RMAN copy the file to new diskgroup.

$ rman target /

connected to target database: ORCL (DBID=1020304050)

RMAN> COPY DATAFILE '+ASMDISK2/orcl/datafile/users.256.565313879' TO '+ASMDISK1';

Starting backup at 03-AUG-98
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=146 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=+ASMDISK2/orcl/datafile/users.256.565313879
output filename=+ASMDISK1/orcl/datafile/users.259.565359071 tag=TAG19980803T12110
9 recid=2 stamp=565359071
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 03-AUG-98

5) Rename the datafile to point to new location.

If you have used DBMS_FILE_TRANSFER (method 4 a)) use the following command to rename:
SQL> ALTER DATABASE RENAME FILE '+ASMDISK2/orcl/datafile/users.256.565313879' TO                                  

'+ASMDISK1/orcl/datafile/users.259.565359071';

Database altered.

If you have used RMAN (method 4 b) use the following option of RMAN
RMAN>
run
{
set newname for datafile '+ASMDISK2/orcl/datafile/users.256.565313879'
to '+ASMDISK1/orcl/datafile/users.259.565359071';
switch datafile all;
}

6) Recover the datafile.

SQL> RECOVER DATAFILE '+ASMDISK1/orcl/datafile/users.259.565359071';
Media recovery complete.

7) Bring the datafile online.

SQL> ALTER DATABASE DATAFILE '+ASMDISK1/orcl/datafile/users.259.565359071' ONLINE;
Database altered.

8) Verify the new datafile locations.

SQL> SELECT FILE_NAME FROM DBA_DATA_FILES;

FILE_NAME
-------------------------------------------------------------------------------
+ASMDISK1/orcl/datafile/users.259.565359071
+ASMDISK1/orcl/sysaux01.dbf
+ASMDISK1/orcl/undotbs01.dbf
+ASMDISK1/orcl/system01.dbf

9) Delete the datafile from its original location.

SQL> ALTER DISKGROUP ASMDISK2 DROP FILE users.256.565313879;
or
ASMCMD> rm -rf <filename>


Note:-
====

The steps provided above assume that the database is open and in Archivelog mode.
Besides these steps are not appropriated for system or sysaux datafiles.

For System and Sysaux an approach similar to the one given below can be used:-

1. Create a Copy of datafile in target Diskgroup
RMAN> backup as copy tablespace system format '<New DG>';
RMAN> backup as copy tablespace sysaux format '<New DG>';

2. Then shutdown the database and restart to a mounted state
RMAN> shutdown immediate;
RMAN> startup mount;

3. switch the datafiles to the copy
RMAN> switch tablespace system to copy;
RMAN> switch tablespace sysaux to copy;

4. Recover the changes made to these tablespaces
RMAN> recover database;

Note:-
====

Most ASM files do not need to be manually deleted because, as Oracle managed files, they are removed automatically

when they are no longer needed.

However, if you need to drop an Oracle Managed File (OMF) manually you should use the fully qualified filename if

you reference the file. Otherwise you will get an error (e.g. ORA-15177).

ALTER DISKGROUP ASMDISK2 DROP FILE '+ASMDISK2/orcl/datafile/users.256.565313879';