How to find digree of a table and number of rows in a table

Query:
=====

set lines 300 pages 3000
col table_name for a50
col owner for a20
col degree for a9
set feed off
alter session set nls_date_format='DD-MM-YYYY HH24:MI:SS';
set feed on
select owner,table_name,degree,num_rows,last_analyzed FROM dba_tables
WHERE upper(owner) = upper('&owner') AND upper(table_name) = upper('&table_name');

Input: Owen and Table name

Note: This result is accurate only when statistics gathered the respective table.

How to get the details about scheduler jobs

Query to know about job details:

========================

set lines 300

col owner for a10

col JOB_NAME for a30

col LAST_RUN_DURATION for a30

col LAST_START_DATE for a35

col NEXT_RUN_DATE for a35

select owner,JOB_CREATOR,job_name,LAST_START_DATE,LAST_RUN_DURATION,NEXT_RUN_DATE,state,FAILURE_COUNT "F_C" from dba_scheduler_jobs where job_name=upper('&job_name');

Input: Job Name

Query to know the current running jobs:

==============================

col ELAPSED_TIME for a30

set lines 300

select JOB_NAME,OWNER,SESSION_ID,ELAPSED_TIME,RUNNING_INSTANCE from dba_scheduler_running_jobs;

Query To know the job history details:

=============================

SET LINES 250

SET PAGES 1000

col LOG_ID format 9999999

col JOB_NAME format a30

col status format a15 trunc

col LOG_DATE format a30

col ADDITIONAL_INFO format a50

col RUN_DURATION for a30

SELECT log_id, job_name, status, to_char(log_date,'DD-MON-YYYY HH24:MI') log_date,RUN_DURATION,ADDITIONAL_INFO

FROM dba_scheduler_job_run_details WHERE

job_name='&job_name' and

(log_date) > (sysdate - &Duration) ORDER BY LOG_DATE;

Input: Job name and history duration(in days)

How to get the Process ID using Session ID in oracle

Query:
=====


REM checking  Timing details, Client PID of associated oracle SID
REM ============================================================
set head off
set verify off
set echo off
set pages 1500
set linesize 100
set lines 120
prompt
prompt Details of SID / SPID / Client PID
prompt ==================================
select /*+ CHOOSE*/
'Session  Id.............................................: '||s.sid,
'Serial Num..............................................: '||s.serial#,
'User Name ..............................................: '||s.username,
'Session Status .........................................: '||s.status,
'Client Process Id on Client Machine ....................: '||'*'||s.process||'*'  Client,
'Server Process ID ..................(Kill This PID).....: '||p.spid Server,
'Sql_Address ............................................: '||s.sql_address,
'Sql_hash_value .........................................: '||s.sql_hash_value,
'Schema Name ..... ......................................: '||s.SCHEMANAME,
'Program  ...............................................: '||s.program,
'Module .................................................: '|| s.module,
'Action .................................................: '||s.action,
'Terminal ...............................................: '||s.terminal,
'Client Machine .........................................: '||s.machine,
'Sql_id .................................................: '||s.sql_id,
'LAST_CALL_ET ...........................................: '||s.last_call_et,
'S.LAST_CALL_ET/3600 ....................................: '||s.last_call_et/3600
from v$session s, v$process p
where p.addr=s.paddr and
s.sid=nvl('&sid',s.sid) and
p.spid=nvl('&spid',p.spid) and
nvl(s.process,-1) = nvl('&ClientPid',nvl(s.process,-1));

Input: SID or PID or Client PID

How to find dependent objects in oracle

Query to find out the dependent objects:
=============================

set lines 400
set pages 2000
set verify off
set termout on
set feedback on

col OWNER format a20
col NAME format a30
col TYPE format a20
col REFERENCED_TYPE format a20
col DEPENDENCY_TYPE format a10
col OBJECT_NAME format a30
col STATUS format a10
col REFERENCED_OWNER for a20
col REFERENCED_NAME for a20

SELECT X.OWNER,
       X.NAME,
       X.TYPE,
       Y.STATUS,
       X.REFERENCED_OWNER,
       X.REFERENCED_NAME,
       X.REFERENCED_TYPE,
       X.DEPENDENCY_TYPE
FROM DBA_DEPENDENCIES X,DBA_OBJECTS Y
WHERE X.OWNER=Y.OWNER
AND X.NAME=Y.OBJECT_NAME
AND X.REFERENCED_OWNER=UPPER('&OWNER')
AND X.REFERENCED_NAME=UPPER('&OBJECT_NAME')
order by X.TYPE;


INPUT: REFERENCED_OWNER and REFERENCED_NAME (these are the parent object details)

Query to find out the objects which is depend on:
======================================

set lines 400
set pages 2000
set verify off
set termout on
set feedback on
col OWNER format a20
col NAME format a30
col TYPE format a20
col REFERENCED_TYPE format a20
col DEPENDENCY_TYPE format a10
col OBJECT_NAME format a30
col STATUS format a10
col REFERENCED_OWNER for a20
col REFERENCED_NAME for a20
col REFERENCED_NAME for a30
set lines 300 pages 300
SELECT X.OWNER,
       X.NAME,
       X.TYPE,
       Y.STATUS,
       X.REFERENCED_OWNER,
       X.REFERENCED_NAME,
       X.REFERENCED_TYPE,
       X.DEPENDENCY_TYPE
FROM DBA_DEPENDENCIES X,DBA_OBJECTS Y
WHERE X.OWNER=Y.OWNER
AND X.NAME=Y.OBJECT_NAME
AND X.OWNER=UPPER('&owner')
AND X.NAME=UPPER('&NAME')

order by REFERENCED_OWNER;

INPUT: OWNER and NAME (these are the Child object details)

How to find UNUSABLE Index

Query:
======

set lines 300
set pages 3000


prompt Index Status:
select owner,index_name,TABLE_OWNER,TABLE_NAME,STATUS from dba_indexes where status='UNUSABLE';


Prompt Partition Index Status:
select INDEX_OWNER,INDEX_NAME,PARTITION_NAME,TABLESPACE_NAME,STATUS from dba_ind_partitions where STATUS='UNUSABLE';


Prompt Sub-partition Index Status:
select INDEX_OWNER,INDEX_NAME,PARTITION_NAME,STATUS,SUBPARTITION_NAME from dba_ind_subpartitions where STATUS='UNUSABLE';

How to check UNDO tablespace usage and who is using more undo

Query To check UNDO tablespace usage:
================================

set linesize 152
col tablespace_name for a20
col status for a10
select tablespace_name,status,count(extent_id) "Extent Count",
sum(blocks) "Total Blocks",sum(bytes)/(1024*1024*1024) spaceInGB
from   dba_undo_extents
group by  tablespace_name, status having tablespace_name=upper('&TSNAME')
order by tablespace_name;

Query To check who is using more UNDO space:
=====================================

SET termout ON
SET heading ON
SET PAGESIZE   6000
SET LINESIZE   200

COLUMN pgm_notes    FORMAT a80        HEADING 'Notes'
COLUMN rbs          FORMAT a16         HEADING 'RBS'             JUST center
COLUMN oracle_user  FORMAT a12        HEADING 'Oracle|Username'
COLUMN sid_serial   FORMAT a12        HEADING 'SID,Serial'
COLUMN unix_pid     FORMAT a6         HEADING 'O/S|PID'
COLUMN Client_User  FORMAT a14        HEADING 'Client|Username'
COLUMN Unix_user    FORMAT a12        HEADING 'O/S|Username'
COLUMN login_time   FORMAT a17        HEADING 'Login Time'
COLUMN last_txn     FORMAT a17        HEADING 'Last Active'
COLUMN undo_kb      FORMAT 999,999,999,999 HEADING 'Undo KB'
COLUMN sql_text     FORMAT a140        HEADING 'Sql Text'


SELECT s.inst_id,
        r.name                   rbs,
        nvl(s.username, 'None')  oracle_user,
        s.osuser                 client_user,
        p.username               unix_user,
        to_char(s.sid)||','||to_char(s.serial#) as sid_serial,
        p.spid                   unix_pid,
        TO_CHAR(s.logon_time, 'mm/dd/yy hh24:mi:ss') as login_time,
        t.used_ublk * 8192  as undo_BYTES,
                st.sql_text as sql_text
   FROM gv$process     p,
        v$rollname     r,
        gv$session     s,
        gv$transaction t,
        gv$sqlarea     st
  WHERE p.inst_id=s.inst_id
    AND p.inst_id=t.inst_id
    AND s.inst_id=st.inst_id
    AND s.taddr = t.addr
    AND s.paddr = p.addr(+)
    AND r.usn   = t.xidusn(+)
    AND s.sql_address = st.address
 --   AND t.used_ublk * 8192 > 10000
  AND t.used_ublk * 8192 > 1073741824
  ORDER
       BY undo_BYTES desc
/

How to find the TEMP Tablespace usage and who is using more TEMP Sapce

Query to check TEMP Tablespace usage:
==============================


 set pagesize 10000
 set linesize 200


SELECT   A.tablespace_name , D.mb_total TOTAL_MB,
         SUM (A.used_blocks * 8192) / 1024 / 1024 USED_MB,
         D.mb_total - SUM (A.used_blocks * 8192) / 1024 / 1024 FREE_MB,((SUM (A.used_blocks * 8192) / 1024 / 1024)/D.mb_total)*100 Percentage
FROM     gv$sort_segment A,
         (
         SELECT   c.tablespace_name,  SUM (C.bytes) / 1024 / 1024 mb_total
         FROM      dba_temp_files C  group by c.tablespace_name
         ) D
WHERE    A.tablespace_name = D.tablespace_name
GRoUP by A.tablespace_name, D.mb_total
having ROUND((SUM (A.used_blocks * 8192) / 1024 / 1024)*100/D.mb_total,2) >0;

Query to check who is using more TEMP space:
=====================================

set pagesize 10000
set linesize 200
column tablespace format a11 heading 'Tablespace Name'
column segfile# format 9,999 heading 'File|ID'
column spid format 9,999 heading 'Unix|ID'
column segblk# format 999,999,999 heading 'Block|ID'
column size_mb format 999,999,990.00 heading "Mbytes|Used"
column username format a22
column program format a25
column osuser format a14
column sid format 9999
column event format a30

select a.inst_id,b.tablespace,round(((b.blocks*8192)/1024/1024),2) size_mb,
        a.sid,a.serial#,c.spid,a.username,a.osuser,a.program,a.status,a.event,a.sql_id
from    gv$session a ,gv$sort_usage b ,gv$process c
where
    a.inst_id=b.inst_id
  and  a.inst_id=c.inst_id
  and  a.saddr = b.session_addr
  and  a.paddr=c.addr
  and round(((b.blocks*8192)/1024/1024),2)>100

 order by 3 desc;

How to get the backup of a package or procedure

Query For Package:
===============

SET LONG 9999999
SET HEAD OFF
set lines 300 pages 3000
col aaa for a300
SELECT DBMS_METADATA.GET_ddl('PACKAGE','&PACKAGE_NAME','&OWNER') aaa FROM DUAL;

INPUT: OWNER and PACKAGE_NAME

Procedure backup:

==============

SET LONG 9999999

SET HEAD OFF

set lines 300 pages 3000

col aaa for a300

SELECT DBMS_METADATA.GET_ddl('PROCEDURE','&PROCEDURE_NAME','&OWNER') aaa FROM DUAL;

INPUT: OWNER and PROCEDURE_NAME

How to check complete details of a table

How to check complete details of a table: This may be useful for performance tuning
===============================

Query:
======

Copy the below query in a file and then run the file.

accept table_name prompt 'Enter table_name: '

set pagesize 999
set linesize 132
set verify off

column column_name format a40
column value format a65
column object_name format a60

select owner, object_name, object_type
from dba_objects
where upper(object_name) = upper('&table_name')
order by owner, object_name, object_type
/

accept owner prompt 'Enter table owner: '

REM
REM Table Definition
REM
describe &owner..&table_name

REM
REM Index Definition
REM
select distinct c.index_name, i.uniqueness, c.column_name, c.column_position
from dba_ind_columns c, dba_indexes i
where upper(c.table_name) = upper('&table_name')
and upper(c.table_owner) = upper('&owner')
and c.index_name = i.index_name
and c.index_owner = i.owner
order by index_name, column_position
/

REM
REM Column Definitions
REM
select column_name, num_distinct, num_nulls, num_buckets, density, sample_size
from dba_tab_columns
WHERE upper(owner) = upper('&owner')
AND upper(table_name) = upper('&table_name')
order by column_name
/

REM
REM Row Counts
REM
SELECT table_name, num_rows, degree, last_analyzed
FROM dba_tables
WHERE upper(owner) = upper('&owner')
AND upper(table_name) = upper('&table_name')
/


INPUT: Table_owner and Table_name

How to find the index details of a table

Query:
=====

set lines 300 pages 300
col OWNER for a15
col INDEX_NAME for a25
col TABLE_NAME for a25
col COLUMN_NAME for a25
col TABLE_OWNER for a15
col INDEX_TYPE for a15

select distinct c.index_name, i.uniqueness, c.column_name, c.column_position

from dba_ind_columns c, dba_indexes i
where upper(c.table_name) = upper('&table_name')
and upper(c.table_owner) = upper('&owner')
and c.index_name = i.index_name
and c.index_owner = i.owner
order by index_name, column_position
/

INPUT: Table_owner and Table_name

Partition Index:

===========

set lines 300 pages 3000

col high_value for a100

col INDEX_OWNER for a15

select INDEX_OWNER,INDEX_NAME,PARTITION_NAME,TABLESPACE_NAME,STATUS from dba_ind_partitions where INDEX_OWNER='&INDEX_OWNER' and INDEX_NAME='&INDEX_NAME'

order by PARTITION_NAME;

INPUT: Index_Owner and Index_name

Index on which Columns:

==================

set lines 300 pages 300

col INDEX_OWNER for a15

col INDEX_NAME for a25

col TABLE_NAME for a25

col COLUMN_NAME for a25

col TABLE_OWNER for a15

select INDEX_OWNER,INDEX_NAME,TABLE_OWNER,TABLE_NAME,COLUMN_NAME,COLUMN_POSITION from dba_ind_columns where INDEX_OWNER='&INDEX_OWNER' and INDEX_NAME='&INDEX_NAME';

INPUT: Index_Owner and Index_name

How to find the bind variables of a query

Query:
=====


set lines 300 pages 3000
col NAME for a20
col VALUE_STRING for a40
select INST_ID,SQL_ID,NAME,POSITION,VALUE_STRING from gv$sql_bind_capture where SQL_ID='&SQL_ID';


INPUT: SQL_ID

How to check the uptime of a database

Query:
=====

col STIME for a30
col HOST_NAME for a20
col UPTIME for a40
set lines 300
set pages 200
select
    host_name
   , instance_name
   ,status
   , to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') stime
   , floor(sysdate - startup_time) || ' days ' ||
   trunc( 24*((sysdate-startup_time) -
   trunc(sysdate-startup_time))) || ' hour ' ||
   mod(trunc(1440*((sysdate-startup_time) -
   trunc(sysdate-startup_time))), 60) ||' minute ' ||
   mod(trunc(86400*((sysdate-startup_time) -
   trunc(sysdate-startup_time))), 60) ||' seconds' uptime
from

sys.gv_$instance;

OutPut:

======

HOST_NAME        INSTANCE_NAME    STATUS       STIME                          UPTIME

--------------- ---------------- ------------ ------------------------------ --------------------

testpdb01          TEST1           OPEN         20-NOV-2015 21:32:56           12 days 6 hour 52 minute

How to find out the sessions running from a package or procedure

Query:
=====

set lines 300
col SID for 0000
col USERNAME for a15
col EVENT for a32
col OSUSER for a10
col MACHINE for a15
col OBJECT_NAME for a40
select osuser,inst_id,sid,username,event,state,sql_id, last_call_et/60,prev_sql_id,s.status,PLSQL_ENTRY_OBJECT_ID,o.object_name  from gv$session s,dba_objects o where
s.PLSQL_ENTRY_OBJECT_ID=o.object_id and
s.PLSQL_ENTRY_OBJECT_ID is not null and o.object_name=upper('&object_name');

INPUT: Package/Procedure Name

Result: The above query shows the session details running from given package/procedure.

How to check when the RMAN backup will complete

Query:
=====

col TARGET for a25
set lines 300
col OPNAME for a50
SELECT target,OPNAME,sum(sofar), sum(totalwork),sum(sofar)*100/sum(totalwork) Percntg,sum(ELAPSED_SECONDS/60) ETIME,max(time_remaining/60) RTIME
FROM v$session_longops WHERE time_remaining > 0 and sofar!=totalwork and OPNAME like '%RMAN%' group by target,OPNAME;

How to check when the query execution compltes

Query:
=====

set linesize 333
select SID, opname,START_TIME,TOTALWORK, sofar, (sofar/totalwork) * 100 done,TIME_REMAINING/60,
sysdate + TIME_REMAINING/3600/24 end_at
from gv$session_longops

where totalwork > sofar;

How to check the Child sessions of a parent session

How to check the Child sessions of a parent session:
=======================================

Query:
=====

col EVENT for a30
col MACHINE for a25
col USERNAME for a15
set lines 300 pages 300
col wait_class for a10
col EVENT for a32
col osuser for a15
col SERVICE_NAME for a12
col blocking_session for 9999
col blocking_instance for 9999
col sid for 9999
select a.inst_id,a.sid,b.qcinst_id,b.qcsid,a.MACHINE,a.event,a.sql_id,a.osuser,a.USERNAME,a.last_call_et/60 R_TIME,
seconds_in_wait W_TIME,a.status from Gv$session a ,gv$px_session b where status='ACTIVE'
and b.qcsid=&qcsid and a.sid=b.sid(+)  and a.inst_id=b.inst_id(+)
 order by last_call_et/60;


INPUT: QCSID

QCSID is the parent session id

How to check the sessions on database

Query to check All Active sessions in database:
====================================
col osuser for a15
col MACHINE for a25
col USERNAME for a15
set lines 300 pages 3000
col wait_class for a10
col EVENT for a33
col STATE for a17
col SERVICE_NAME for a12
col B_S for 9999
col B_I for 9999
col sid for 9999
col inst_id for 9999
col QCINST_ID for 9999
col QCSID for 9999
select a.inst_id,a.sid,b.qcinst_id,b.qcsid,a.MACHINE,a.event,a.sql_id,a.osuser,a.USERNAME,
a.last_call_et/60 RUN_TIME,a.seconds_in_wait Wait_time
,a.status,a.blocking_session B_S,a.blocking_instance B_I,service_name
from
Gv$session a ,gv$px_session b
where status='ACTIVE' and
USERNAME not in('SYS','SYSTEM','DBSNMP') and a.sid=b.sid(+)
 and a.inst_id=b.inst_id(+) and username is not null
group by a.inst_id,a.sid,b.qcinst_id,b.qcsid,a.MACHINE,a.event,a.sql_id,a.osuser,a.USERNAME,
a.last_call_et/60,a.seconds_in_wait,a.status,a.blocking_session,a.blocking_instance ,service_name

order by last_call_et/60 desc;

Query to check All sessions in database:
===============================

col osuser for a15
col MACHINE for a25
col USERNAME for a15
set lines 300 pages 3000
col wait_class for a10
col EVENT for a33
col STATE for a17
col SERVICE_NAME for a12
col B_S for 9999
col B_I for 9999
col sid for 9999
col inst_id for 9999
col QCINST_ID for 9999
col QCSID for 9999
select a.inst_id,a.sid,b.qcinst_id,b.qcsid,a.MACHINE,a.event,a.sql_id,a.osuser,a.USERNAME,
a.last_call_et/60 RUN_TIME,a.seconds_in_wait Wait_time
,a.status,a.blocking_session B_S,a.blocking_instance B_I,service_name
from
Gv$session a ,gv$px_session b
where
USERNAME not in('SYS','SYSTEM','DBSNMP') and a.sid=b.sid(+)
 and a.inst_id=b.inst_id(+) and username is not null
group by a.inst_id,a.sid,b.qcinst_id,b.qcsid,a.MACHINE,a.event,a.sql_id,a.osuser,a.USERNAME,
a.last_call_et/60,a.seconds_in_wait,a.status,a.blocking_session,a.blocking_instance ,service_name

order by last_call_et/60 desc;

Query to check All Active sessions in database running from a particular USER :   
=============================================================

INPUT: username
======

col osuser for a10
col MACHINE for a25
col USERNAME for a15
set lines 300 pages 3000
col wait_class for a10
col EVENT for a33
col STATE for a17
col SERVICE_NAME for a12
col B_S for 9999
col B_I for 9999
col sid for 9999
col inst_id for 9999
col QCINST_ID for 9999
col QCSID for 9999
select a.inst_id,a.sid,b.qcinst_id,b.qcsid,a.MACHINE,a.event,a.sql_id,a.osuser,a.USERNAME,a.last_call_et/60 RUN_TIME,
a.seconds_in_wait Wait_time,a.status,a.blocking_session B_S,a.blocking_instance B_I
from Gv$session a ,gv$px_session b where status='ACTIVE'
and USERNAME =upper('&USERNAME') and a.sid=b.sid(+)  and a.inst_id=b.inst_id(+)
group by a.inst_id,a.sid,b.qcinst_id,b.qcsid,a.MACHINE,a.event,a.sql_id,a.osuser,a.USERNAME,a.last_call_et/60,a.seconds_in_wait,a.status,a.blocking_session,a.blocking_instance

order by last_call_et/60;

Query to check All Active sessions in database running from a particular Machine :   
===============================================================

INPUT: machine
======

col osuser for a15
col MACHINE for a25
col USERNAME for a15
set lines 300 pages 3000
col wait_class for a10
col EVENT for a33
col STATE for a17
col SERVICE_NAME for a12
col B_S for 9999
col B_I for 9999
col sid for 9999
select a.inst_id,a.sid,b.qcinst_id,b.qcsid,a.MACHINE,a.event,a.sql_id,a.osuser,a.USERNAME,a.last_call_et/60 RUN_TIME,
a.seconds_in_wait Wait_time,a.status,a.blocking_session B_S,a.blocking_instance B_I
from Gv$session a ,gv$px_session b
where machine='&machine' and status='ACTIVE' and a.sid=b.sid(+)  and a.inst_id=b.inst_id(+) and username is not null
group by a.inst_id,a.sid,b.qcinst_id,b.qcsid,a.MACHINE,a.event,a.sql_id,a.osuser,a.USERNAME,a.last_call_et/60,a.seconds_in_wait,a.status,a.blocking_session,a.blocking_instance
order by last_call_et/60 desc;

Query to check All Active sessions in database running from a particular SQL_ID:   
==============================================================

INPUT: sql_id
======

col osuser for a15

col MACHINE for a25

col USERNAME for a15

set lines 300 pages 3000

col wait_class for a10

col EVENT for a33

col STATE for a17

col SERVICE_NAME for a12

col B_S for 9999

col B_I for 9999

col sid for 9999

select a.inst_id,a.sid,b.qcinst_id,b.qcsid,a.MACHINE,a.event,a.sql_id,a.osuser,a.USERNAME,a.last_call_et/60 RUN_TIME,

a.seconds_in_wait Wait_time,a.status,a.blocking_session B_S,a.blocking_instance B_I

from Gv$session a ,gv$px_session b

where sql_id='&sql_id' and a.sid=b.sid(+)  and a.inst_id=b.inst_id(+) and username is not null

group by a.inst_id,a.sid,b.qcinst_id,b.qcsid,a.MACHINE,a.event,a.sql_id,a.osuser,a.USERNAME,a.last_call_et/60,a.seconds_in_wait,a.status,a.blocking_session,a.blocking_instance

order by last_call_et/60 desc;

INPUT: sid
======

col osuser for a15

col MACHINE for a25

col USERNAME for a15

set lines 300 pages 3000

col wait_class for a10

col EVENT for a33

col STATE for a17

col SERVICE_NAME for a12

col B_S for 9999

col B_I for 9999

col sid for 9999

col q_i for 99

col qcsid for 99999

select a.inst_id,a.sid,a.serial#,b.qcinst_id q_i,b.qcsid,a.MACHINE,a.event,a.sql_id,a.osuser,a.USERNAME,a.last_call_et/60 RUN_TIME,

a.seconds_in_wait Wait_time,a.status,a.prev_sql_id

from Gv$session a ,gv$px_session b

where a.sid=&sid and a.inst_id=&inst_id and a.sid=b.sid(+)  and a.inst_id=b.inst_id(+) and username is not null

group by a.inst_id,a.sid,a.serial#,b.qcinst_id,b.qcsid,a.MACHINE,a.event,a.sql_id,a.osuser,a.USERNAME,a.last_call_et/60,a.seconds_in_wait,a.status,a.prev_sql_id

order by last_call_et/60 desc;

How to check the execution plan of the Query

There are three ways to check the execution plan:
1) Using Cursor
2) Using AWR
3) Using explain plan set <sql>

1) Using Cursor:
============

Query:
=====
set lines 300 pages 300
select * from table(dbms_xplan.display_cursor('&sql_id'))
/

Input: SQL_ID

Output:

======

Plan hash value: 1357081020

----------------------------------------------------------------------------------

| Id  | Operation                 | Name | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------

|   0 | SELECT STATEMENT          |      |       |       |     3 (100)|          |

|   1 |  TABLE ACCESS STORAGE FULL| TEST |     5 |   175 |     3   (0)| 00:00:01 |

----------------------------------------------------------------------------------

Note

-----

   - dynamic sampling used for this statement (level=2)

2) Using AWR:

Query:

======

set lines 300 pages 3000

select * from table(dbms_xplan.display_awr('&sql_id'))

/

Input: SQL_ID

Output:

=======

Plan hash value: 1357081020

----------------------------------------------------------------------------------

| Id  | Operation                 | Name | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------

|   0 | SELECT STATEMENT          |      |       |       |     3 (100)|          |

|   1 |  TABLE ACCESS STORAGE FULL| TEST |     5 |   175 |     3   (0)| 00:00:01 |

----------------------------------------------------------------------------------

Note

-----

   - dynamic sampling used for this statement (level=2)

3) Using explain plan set <sql>  :

========================

Query:

======

SQL> explain plan for select * from test;

Explained.

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

Output:

=======

PLAN_TABLE_OUTPUT

-------------------

Plan hash value: 1357081020

----------------------------------------------------------------------------------

| Id  | Operation                 | Name | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------

|   0 | SELECT STATEMENT          |      |     5 |   175 |     3   (0)| 00:00:01 |

|   1 |  TABLE ACCESS STORAGE FULL| TEST |     5 |   175 |     3   (0)| 00:00:01 |

----------------------------------------------------------------------------------

Note

-----

   - dynamic sampling used for this statement (level=2)

How to check the Query performance using history tables

Query to check the Query Performance:
===============================

col Date_Time form a20

col PARSING_SCHEMA_NAME for a20

set line 300

set pages 20

col SQL_PROFILE for a50

SELECT distinct

--s.INSTANCE_NUMBER n,

s.snap_id ,SQL.parsing_schema_name,

--sql_id,

PLAN_HASH_VALUE,

--to_char(s.BEGIN_INTERVAL_TIME,'mm/dd/yy hh24:mi') BEGIN_INTERVAL_TIME,

to_char(s.BEGIN_INTERVAL_TIME,'mm/dd/yy_hh24mi')|| to_char(s.END_INTERVAL_TIME,'_hh24mi') Date_Time,

SQL.executions_delta,

SQL.buffer_gets_delta/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta) avg_lio,

--SQL.ccwait_delta,

(SQL.cpu_time_delta/1000000)/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta) avg_cputime ,

(SQL.elapsed_time_delta/1000000)/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta) avg_etime,

SQL.DISK_READS_DELTA/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta) avg_pio,

SQL.rows_processed_delta/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta) avg_row,

SQL.sql_profile

FROM

dba_hist_sqlstat SQL,

dba_hist_snapshot s

WHERE

 s.snap_id = SQL.snap_id

AND sql_id in

('&sql_id')

order by

--SQL.buffer_gets_delta desc,

s.snap_id

/

Input: SQL_ID

What to see:

=========

Above Query will give the performance history of a Query.

We can compare the values with old snapshots.

Below columns are the important columns to check.

What you can find:

===============

* Whether plan changed

* Whether Logical IO is more

* Whether Physical IO is more

* Whether query is taking more CPU

Important columns to see:

====================

PLAN_HASH_VALUE L: Plan hash value

EXECUTIONS_DELTA: How many times query has been executed the one snapshot.

AVG_LIO: This will give the logical IO.

AVG_PIO : This will give the Physical IO.

AVG_ETIME: Estimated time to complete.