Amit Oracle DBA Blog

Tuesday 26 May 2015

Obtaining Execution Plan in Oracle

Obtaining Execution Plan in Oracle

What is execution plan?
An execution plan describes the list of operations which are performed by SQL Engine for SQL statement. The order of operations and their implementation is decided by the oracle query optimizer. An execution plan also shows the estimated number of rows, cost require to perform the operation, how many bytes oracle will read the data and estimated time required for the operations.

Sample Execution Plan:

1. Access Method & Join type of data access.

2. Name of the objects on which operation is going to perform.

3. Cardinality: Estimated number of rows.

4. Bytes: How much byte of data needs to process for each operation.

5. Cost: Cost require to perform operation.

6. Time: Estimated time require to complete the operation.

Displaying Execution plan:

Below are the two mostly used methods to display an execution plan.

1. EXPLAIN PLAN command: This displays an execution plan for SQL statement without actually executing it.

2. V$SQL_PLAN: This dynamic performance view shows execution plan for statement that has been compiled into cursor and stored in the cursor cache.

DBMS_XPLAN Package: This package provides the different PL/SQL interfaces to display the plan from different sources.

Obtaining Execution Plan from EXPLAIN PLAN Command and DBMS_XPLAN.DISPLAY function:

Below example shows how to use EXLAIN_PLAN command and DBMS_XPLAN.DISPLAY function to display the plan.

SQL> explain plan for select * from test1 a,test2 b where a.emp_no=b.emp_no;

Explained.

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 497311279

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

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

| 0 | SELECT STATEMENT | | 1 | 40 | 5 (20)| 00:00:01 |

|* 1 | HASH JOIN | | 1 | 40 | 5 (20)| 00:00:01 |

| 2 | TABLE ACCESS FULL| TEST1 | 1 | 20 | 2 (0)| 00:00:01 |

| 3 | TABLE ACCESS FULL| TEST2 | 1 | 20 | 2 (0)| 00:00:01 |

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

PLAN_TABLE_OUTPUT

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

Predicate Information (identified by operation id):

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

1 - access("A"."EMP_NO"="B"."EMP_NO")

Note

-----

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

19 rows selected.

Obtaining Plan from DBMS_XPLAN.DISPLAY_CURSOR function:

Execution plan for executed statement can be displayed by using DBMS_XPLAN.DISPLAY_CURSOR function. It takes input as SQL_ID of the statement.

SQL> select * from table(dbms_xplan.display_cursor('0kkhhb2w93cx0'));

PLAN_TABLE_OUTPUT

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

SQL_ID 0kkhhb2w93cx0, child number 0

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

update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,exts

ize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL,

:13),groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16,

spare1=DECODE(:17,0,NULL,:17),scanhint=:18, bitmapranges=:19 where

ts#=:1 and file#=:2 and block#=:3

Plan hash value: 2170058777

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

PLAN_TABLE_OUTPUT

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

| 0 | UPDATE STATEMENT | | | | 3 (100)| |

| 1 | UPDATE | SEG$ | | | | |

| 2 | TABLE ACCESS CLUSTER| SEG$ | 1 | 72 | 3 (0)| 00:00:01 |

|* 3 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# | 1 | | 2 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

PLAN_TABLE_OUTPUT

3 - access("TS#"=:1 AND "FILE#"=:2 AND "BLOCK#"=:3)

Obtaining History Plan from DBMS_XPLAN.DISPLAY_AWR function

Sometimes you will not get plan from DISPLAY_CURSOR function as it might have flushed from the cursor cache. In this case we can obtain the plan using DISPLAY_AWR function. This function gives us all the execution plans that the statement had used in the past. It takes input as SQL_ID of the statement.

SQL> select * from table(dbms_xplan.display_awr('0kkhhb2w93cx0'));

PLAN_TABLE_OUTPUT

SQL_ID 0kkhhb2w93cx0

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

update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,exts

ize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL,

:13),groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16,

spare1=DECODE(:17,0,NULL,:17),scanhint=:18, bitmapranges=:19 where

ts#=:1 and file#=:2 and block#=:3

Plan hash value: 2170058777

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

PLAN_TABLE_OUTPUT

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

| 0 | UPDATE STATEMENT | | | | 2 (100)| |

| 1 | UPDATE | SEG$ | | | | |

| 2 | TABLE ACCESS CLUSTER| SEG$ | 1 | 71 | 2 (0)| 00:00:01 |

| 3 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# | 1 | | 1 (0)| 00:00:01 |

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

19 rows selected.

Conclusion: In this article we have seen the methods to obtain an execution plan from the cursor cache as well as from AWR. I will write an article on interpreting these execution plans very soon.

Thursday 30 April 2015

How to lock/unlock statistics on a table

In certain cases you may want to lock statistics in a table in certain cases, for example if you want a table not be analyzed by automatic statistics job but analyze it later or in cases where you want prevent from analyzing statistics in cases where data in the table doesn’t change.
The following example shows how to lock table statistics and what happens when one tries to gather statistics on table that has statistics locked.

STEP 1: Create Table for test

SQL> create table test ( x number );

Table created.

STEP2: Create index on table.

SQL> create index test_idx on test(x);

Index created.

STEP3: Check stats are not locked on table.

When stats is not locked the value of stattype_locked is NULL

SQL> SELECT stattype_locked FROM dba_tab_statistics WHERE table_name = 'TEST' and owner = 'SCOTT';

STATT
—–

STEP4: Lock the statistics

SQL> exec dbms_stats.lock_table_stats('scott', 'test');

PL/SQL procedure successfully completed.

STEP5: Now check stats are locked status

When stats is locked the value of stattype_locked is ALL

SQL> SELECT stattype_locked FROM dba_tab_statistics WHERE table_name = 'TEST' and owner = 'SCOTT';

STATT
—–
ALL

STEP 6: Try to gather statistics on locked table and index

SQL> exec dbms_stats.gather_index_stats('scott', 'test_idx');

BEGIN dbms_stats.gather_index_stats('scott', 'test_idx'); END;
*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at “SYS.DBMS_STATS”, line 10640
ORA-06512: at “SYS.DBMS_STATS”, line 10664
ORA-06512: at line 1

SQL> analyze index ajaffer.test_idx compute statistics;

analyze index ajaffer.test_idx compute statistics
*
ERROR at line 1:
ORA-38029: object statistics are locked

STEP7: Unlock the statistics

SQL> exec dbms_stats.unlock_table_stats('scott', 'test');

PL/SQL procedure successfully completed.

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!

Tuesday 28 April 2015

Restore the database from tape backup of the Database

Suppose the entire server is lost,

we have the tape copy of the backup of the database , which includes RMAN backup pieces and control file backup
Assuming that were the case, we would have to rebuild the entire server, reinstall the OS and the Oracle Software, then restore the backup of the database from tape. . If the Archives and online redo are lost, the recovered database will not include all transactions,
but it will be current up to the last backup.
Steps Required
1.copy the tape backup of the database to disk
2.create the init.ora file
3.create the password file
4.start the instance in nomount
5.restore the spfile from autobackup spfile backup piece
6.shutdown immediate
7.startup nomount
8.RESTORE CONTROLFILE FROM AUTOBACKUP;
9.alter database mount;
10.catalog all the backup piece into RMAN
11.restore database
12.recover database;
13.alter database open resetlogs;
14. check the status of listener and if any connection issues then workout and fix the problem
Detailed Description of Steps
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@localhost Stasdb_database]$ . oraenv

ORACLE_SID = [statsdb] ?
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 is /u01/app/oracle

RMAN> connect target /
connected to target database (not started)

RMAN> startup nomount
Oracle instance started
Total System Global Area     893562880 bytes
Fixed Size                     2218512 bytes
Variable Size                243271152 bytes
Database Buffers             641728512 bytes
Redo Buffers                   6344704 bytes

RMAN> RESTORE SPFILE FROM '/u02/app/Stasdb_database/c-782188580-20120613-02';
Starting restore at 13-JUN-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u02/app/Stasdb_database/c-782188580-20120613-02
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 13-JUN-12

RMAN> shutdown immediate;
Oracle instance shut down

RMAN> startup nomount;
connected to target database (not started)
Oracle instance started
Total System Global Area     893562880 bytes
Fixed Size                     2218512 bytes
Variable Size                243271152 bytes
Database Buffers             641728512 bytes
Redo Buffers                   6344704 bytes

RMAN> RESTORE CONTROLFILE FROM '/u02/app/Stasdb_database/c-782188580-20120613-02';
Starting restore at 13-JUN-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=17 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/statsdb/control01.ctl
output file name=/u01/app/oracle/flash_recovery_area/statsdb/control02.ctl
Finished restore at 13-JUN-12

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> catalog start with '/u01/app/oracle/flash_recovery_area/statsdb';
Starting implicit crosscheck backup at 13-JUN-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=17 device type=DISK
Crosschecked 100 objects
Finished implicit crosscheck backup at 13-JUN-12
Starting implicit crosscheck copy at 13-JUN-12
using channel ORA_DISK_1
Finished implicit crosscheck copy at 13-JUN-12
searching for all files in the recovery area
cataloging files...
no files cataloged
searching for all files that match the pattern /u01/app/oracle/flash_recovery_area/statsdb
List of Files Unknown to the Database
=====================================
File Name: /u01/app/oracle/flash_recovery_area/statsdb/hhndeia8_1_1
File Name: /u01/app/oracle/flash_recovery_area/statsdb/hindeimj_1_1
File Name: /u01/app/oracle/flash_recovery_area/statsdb/hgndeia5_1_1
Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/app/oracle/flash_recovery_area/statsdb/hhndeia8_1_1
File Name: /u01/app/oracle/flash_recovery_area/statsdb/hindeimj_1_1
File Name: /u01/app/oracle/flash_recovery_area/statsdb/hgndeia5_1_1

RMAN> restore database;
Starting restore at 13-JUN-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/statsdb/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/statsdb/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/statsdb/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/statsdb/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/statsdb/example01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/statsdb/tripointmonitor01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/statsdb/hhndeia8_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/statsdb/hhndeia8_1_1 tag=TAG20120613T144216
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:05:56
Finished restore at 13-JUN-12

RMAN> recover database;
Starting recover at 13-JUN-12
using channel ORA_DISK_1
starting media recovery
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=2345
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/statsdb/hindeimj_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/statsdb/hindeimj_1_1 tag=TAG20120613T144851
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/flash_recovery_area/STATSDB/archivelog/2012_06_13/o1_mf_1_2345_7xl72djp_.arc thread=1 sequence=2345
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/flash_recovery_area/STATSDB/archivelog/2012_06_13/o1_mf_1_2345_7xl72djp_.arc RECID=1851 STAMP=785865820
unable to find archived log
archived log thread=1 sequence=2346
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/13/2012 16:03:42
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 2346 and starting SCN of 92188455

RMAN> alter database open resetlogs;
database opened

RMAN>

Your database is ready to use.

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!

Friday 24 April 2015

Perfomance Tuning Related Queries.

Listed below are some SQL queries which I find particularly useful for performance tuning. These are based on the Active Session History V$ View to get a current perspective of performance and the DBA_HIST_* AWR history tables for obtaining performance data pertaining to a period of time in the past.

Top Recent Wait Events

col EVENT format a60

select * from (
select active_session_history.event,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history
where active_session_history.event is not null
group by active_session_history.event
order by 2 desc)
where rownum < 6
/
Top Wait Events Since Instance Startup
col event format a60

select event, total_waits, time_waited
from v$system_event e, v$event_name n
where n.event_id = e.event_id
and n.wait_class !='Idle'
and n.wait_class = (select wait_class from v$session_wait_class
where wait_class !='Idle'
group by wait_class having
sum(time_waited) = (select max(sum(time_waited)) from v$session_wait_class
where wait_class !='Idle'
group by (wait_class)))
order by 3;

List Of Users Currently Waiting

col username format a12
col sid format 9999
col state format a15
col event format a50
col wait_time format 99999999
set pagesize 100
set linesize 120

select s.sid, s.username, se.event, se.state, se.wait_time
from v$session s, v$session_wait se
where s.sid=se.sid
and se.event not like 'SQL*Net%'
and se.event not like '%rdbms%'
and s.username is not null
order by se.wait_time;

Find The Main Database Wait Events In A Particular Time Interval

select snap_id,begin_interval_time,end_interval_time
from dba_hist_snapshot
where to_char(begin_interval_time,'DD-MON-YYYY')='14-NOV-2012'
and EXTRACT(HOUR FROM begin_interval_time) between 22 and 23;
set verify off
select * from (
select active_session_history.event,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from dba_hist_active_sess_history active_session_history
where event is not null
and SNAP_ID between &ssnapid and &esnapid
group by active_session_history.event
order by 2 desc)
where rownum < 6;

Top CPU Consuming SQL During A Certain Time Period
Note – in this case we are finding the Top 5 CPU intensive SQL statements executed between 9.00 AM and 11.00 AM

select * from (
select
SQL_ID,
sum(CPU_TIME_DELTA),
sum(DISK_READS_DELTA),
count(*)
from
DBA_HIST_SQLSTAT a, dba_hist_snapshot s
where
s.snap_id = a.snap_id
and s.begin_interval_time > sysdate -1
and EXTRACT(HOUR FROM S.END_INTERVAL_TIME) between 9 and 11
group by
SQL_ID
order by
sum(CPU_TIME_DELTA) desc)
where rownum < 6;

Which Database Objects Experienced the Most Number of Waits in the Past One Hour

set linesize 120
col event format a40
col object_name format a40
select * from
(
select dba_objects.object_name,
dba_objects.object_type,
active_session_history.event,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history,
            dba_objects
where
active_session_history.sample_time between sysdate - 1/24 and sysdate
and active_session_history.current_obj# = dba_objects.object_id
group by dba_objects.object_name, dba_objects.object_type, active_session_history.event
order by 4 desc)
where rownum < 6;

Top Segments ordered by Physical Reads

col segment_name format a20
col owner format a10
select segment_name,object_type,total_physical_reads
from ( select owner||'.'||object_name as segment_name,object_type,
value as total_physical_reads
from v$segment_statistics
where statistic_name in ('physical reads')
order by total_physical_reads desc)
where rownum < 6;

Top 5 SQL statements in the past one hour
select * from (
select active_session_history.sql_id,
dba_users.username,
sqlarea.sql_text,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history,
v$sqlarea sqlarea,
dba_users
where
active_session_history.sample_time between sysdate - 1/24 and sysdate
and active_session_history.sql_id = sqlarea.sql_id
and active_session_history.user_id = dba_users.user_id
group by active_session_history.sql_id,sqlarea.sql_text, dba_users.username
order by 4 desc )
where rownum < 6;

SQL with the highest I/O in the past one day

select * from
(
SELECT /*+LEADING(x h) USE_NL(h)*/
          h.sql_id
,        SUM(10) ash_secs
FROM   dba_hist_snapshot x
,        dba_hist_active_sess_history h
WHERE   x.begin_interval_time > sysdate -1
AND      h.SNAP_id = X.SNAP_id
AND      h.dbid = x.dbid
AND      h.instance_number = x.instance_number
AND      h.event in ('db file sequential read','db file scattered read')
GROUP BY h.sql_id
ORDER BY ash_secs desc )
where rownum < 6;

Top CPU consuming queries since past one day

select * from (
select
         SQL_ID,
         sum(CPU_TIME_DELTA),
         sum(DISK_READS_DELTA),
         count(*)
from
         DBA_HIST_SQLSTAT a, dba_hist_snapshot s
where
s.snap_id = a.snap_id
and s.begin_interval_time > sysdate -1
         group by
         SQL_ID
order by
         sum(CPU_TIME_DELTA) desc)
where rownum < 6;

Find what the top SQL was at a particular reported time of day

First determine the snapshot id values for the period in question.

In thos example we need to find the SNAP_ID for the period 10 PM to 11 PM on the 14th of November, 2012.
select snap_id,begin_interval_time,end_interval_time
from dba_hist_snapshot
where to_char(begin_interval_time,'DD-MON-YYYY')='14-NOV-2012'
and EXTRACT(HOUR FROM begin_interval_time) between 22 and 23;
select * from
(
select
sql.sql_id c1,
sql.buffer_gets_delta c2,
sql.disk_reads_delta c3,
sql.iowait_delta c4
from
dba_hist_sqlstat sql,
dba_hist_snapshot s
where
s.snap_id = sql.snap_id
and
s.snap_id= &snapid
order by
c3 desc)
where rownum < 6;

Analyse a particular SQL ID and see the trends for the past day

select
s.snap_id,
to_char(s.begin_interval_time,'HH24:MI') c1,
sql.executions_delta c2,
sql.buffer_gets_delta c3,
sql.disk_reads_delta c4,
sql.iowait_delta c5,
sql.cpu_time_delta c6,
sql.elapsed_time_delta c7
from
dba_hist_sqlstat sql,
dba_hist_snapshot s
where
s.snap_id = sql.snap_id
and s.begin_interval_time > sysdate -1
and
sql.sql_id='&sqlid'
order by c7;

Do we have multiple plan hash values for the same SQL ID – in that case may be changed plan is causing bad performance

select
SQL_ID
, PLAN_HASH_VALUE
, sum(EXECUTIONS_DELTA) EXECUTIONS
, sum(ROWS_PROCESSED_DELTA) CROWS
, trunc(sum(CPU_TIME_DELTA)/1000000/60) CPU_MINS
, trunc(sum(ELAPSED_TIME_DELTA)/1000000/60) ELA_MINS
from DBA_HIST_SQLSTAT
where SQL_ID in (
'&sqlid')
group by SQL_ID , PLAN_HASH_VALUE
order by SQL_ID, CPU_MINS;

Top 5 Queries for past week based on ADDM recommendations

Top 10 SQL_ID's for the last 7 days as identified by ADDM

from DBA_ADVISOR_RECOMMENDATIONS and dba_advisor_log

*/

col SQL_ID form a16
col Benefit form 9999999999999
select * from (
select b.ATTR1 as SQL_ID, max(a.BENEFIT) as "Benefit"
from DBA_ADVISOR_RECOMMENDATIONS a, DBA_ADVISOR_OBJECTS b
where a.REC_ID = b.OBJECT_ID
and a.TASK_ID = b.TASK_ID
and a.TASK_ID in (select distinct b.task_id
from dba_hist_snapshot a, dba_advisor_tasks b, dba_advisor_log l
where a.begin_interval_time > sysdate - 7
and a.dbid = (select dbid from v$database)
and a.INSTANCE_NUMBER = (select INSTANCE_NUMBER from v$instance)
and to_char(a.begin_interval_time, 'yyyymmddHH24') = to_char(b.created, 'yyyymmddHH24')
and b.advisor_name = 'ADDM'
and b.task_id = l.task_id
and l.status = 'COMPLETED')
and length(b.ATTR4) > 1 group by b.ATTR1
order by max(a.BENEFIT) desc) where rownum < 6;

Find locks Between 5 and 6 PM on 3/10/15

REM Find locks Between 5 and 6 PM on 3/10/15
set linesize 200
set pagesize 200
col sql_text format a40
col module format a20
SELECT
            distinct a.sql_id,
            a.inst_id,
            a.blocking_session as BLK_sess,
            a.blocking_session_serial# as BLK_SESS_Serial#,
            a.user_id,
            s.sql_text,
            a.module
FROM
            GV$ACTIVE_SESSION_HISTORY a,
            gv$sql s
where
            a.sql_id=s.sql_id
            and
            blocking_session is not null
            and
            a.user_id <> 0
            and
            a.sample_time between
                        to_timestamp('2015-03-10 19:00:00','YYYY-MM-DD HH24:MI:SS') and
                        to_timestamp('2015-03-10 18:00:00','YYYY-MM-DD HH24:MI:SS');

Find Invalid Objects

COLUMN object_name FORMAT A30
set linesize 200
set pagesize 200
col object_name format a30
select
  owner,
  object_name,
  object_type, status
from
  dba_objects
where
  owner not in
      ('SYS','SYSTEM','DBSNMP','OUTLN','OLAPSYS','MDSYS','ORDPLUGINS','XDB','DMSYS')
  and
  status='INVALID'
  and
  not object_name like 'BIN$%';

Find Largest Tables in Database (row wise)

WITH BigTbl AS (
SELECT DISTINCT num_rows, table_name, owner
FROM ALL_TABLES
WHERE num_rows >= 5
ORDER BY num_rows DESC)
SELECT ROWNUM RANK, num_rows, table_name, owner
FROM BigTbl
WHERE ROWNUM <= 10;

Find TOP 10 FULL TABLE SCAN SQLs

select * from
   (select b.username,
       t.sql_id,
       sql_fulltext,
       p.operation,
       p.options
   from
       v$sqlarea t,
       v$sql_plan p,
       v$session b
   where
       t.hash_value=p.hash_value
   and
        t.sql_id=b.sql_id
   and
       p.operation='TABLE ACCESS'
   and
       p.options='FULL'
   and
       p.object_owner not in ('SYS','SYSTEM')
   order by
       DISK_READS DESC,
       EXECUTIONS DESC)
where rownum <=10;

Shows Top “Running SQLs”

select distinct spid,
       s.sid,
       s.serial#,
       to_char(sysdate - last_call_et/(24*3600),'mm/dd/yyhh24:mi:ss') "LAST_ACTIVITY",
       logon_time,
       osuser,
       s.program,
       schemaname,
       sql_text
from v$session s,
       v$process p,
       v$sql t
where s.paddr=p.addr
   and t.hash_value(+)=s.sql_hash_value
   and s.type !='BACKGROUND';

Temporary tablespace usage

SELECT
            s.sid,
            s.username,
            u.tablespace,
            s.sql_hash_value||'/'||u.sqlhash hash_value,
            u.segtype,
            u.contents,
            u.blocks
FROM
            v$session s,
            v$tempseg_usage u
WHERE
            s.saddr=u.session_addr
order by
            u.blocks;

REM DATAFILE I/O NOTES:
REM File Name - Datafile name
REM Physical Reads - Number of physical reads
REM Reads % - Percentage of physical reads
REM Physical Writes - Number of physical writes
REM Writes % - Percentage of physical writes
REM Total Block I/O's - Number of I/O blocks
REM Use this report to identify any "hot spots" or I/O contention

set linesize 200
set pagesize 200
col name format a46
set linesize 200
set pagesize 200
col name format a46
select      NAME,
      PHYRDS "Physical Reads",
      round((PHYRDS / PD.PHYS_READS)*100,2) "Read %",
      PHYWRTS "Physical Writes",
      round(PHYWRTS * 100 / PD.PHYS_WRTS,2) "Write %",
      fs.PHYBLKRD+FS.PHYBLKWRT "Total Block I/O's"
from (
      select      sum(PHYRDS) PHYS_READS,
            sum(PHYWRTS) PHYS_WRTS
      from        v$filestat
      ) pd,
      v$datafile df,
      v$filestat fs
where
      df.FILE# = fs.FILE#
order by
      fs.PHYBLKRD+fs.PHYBLKWRT desc;

I hope this article helped you. Your suggestions/feedbacks are most welcome.

Keep learning... Have a great day!!!

MYSQL

Tuesday 26 May 2015

Obtaining Execution Plan in Oracle

Thursday 30 April 2015

How to lock/unlock statistics on a table

Tuesday 28 April 2015

Restore the database from tape backup of the Database

Friday 24 April 2015

Perfomance Tuning Related Queries.