How to use EXPLAIN PLAN statement and view the results using the DBMS_XPLAN.DISPLAY function

This table function simply displays the contents of the plan table.

Execute an explain plan command on a SELECT statement:

EXPLAIN PLAN FOR
   SELECT * FROM emp e, dept d
   WHERE e.deptno = d.deptno
   AND   e.ename  = 'benoit';

Display the plan using the DBMS_XPLAN.DISPLAY table function:

SET LINESIZE 130
SET PAGESIZE 0
SELECT * FROM table(DBMS_XPLAN.DISPLAY);

This query produces the following output:

Plan hash value: 3693697075
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    57 |     6  (34)| 00:00:01 |
|*  1 |  HASH JOIN         |      |     1 |    57 |     6  (34)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| EMP  |     1 |    37 |     3  (34)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| DEPT |     4 |    80 |     3  (34)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("E"."DEPTNO"="D"."DEPTNO")
   2 - filter("E"."ENAME"='benoit')

15 rows selected.

You can also use the SET STATEMENT_ID directive to identify a specific plan:

EXPLAIN PLAN SET STATEMENT_ID='EXPL1' FOR
SELECT * FROM emp e, dept d
   WHERE e.deptno = d.deptno
   AND e.ename='benoit';

SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('plan_table','EXPL1','serial'));

If no statement_id is specified, the function will show you the plan of the most recent explained statement.

Source: Oracle Documentation

How to create a Range-Partitioned Global Index

You have the following table:

CREATE TABLE sales
  ( prod_id       NUMBER(6)
  , cust_id       NUMBER
  , time_id       DATE
  , channel_id    CHAR(1)
  , promo_id      NUMBER(6)
  , quantity_sold NUMBER(3)
  , amount_sold   NUMBER(10,2)
  )
 PARTITION BY RANGE (time_id)
 ( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('01-APR-2006','dd-MON-yyyy'))
    TABLESPACE tsa
 , PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('01-JUL-2006','dd-MON-yyyy'))
    TABLESPACE tsb
 , PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy'))
    TABLESPACE tsc
 , PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy'))
    TABLESPACE tsd
 )
 ENABLE ROW MOVEMENT; 

Note: The ENABLE ROW MOVEMENT clause is specified to allow the automatic migration of a row to a new partition if an update to a key value is made that would place the row in a different partition.

The rules for creating range-partitioned global indexes are similar to those for creating range-partitioned tables.
Each index partition is named but is stored in the default tablespace for the index.

CREATE INDEX amount_sold_ix ON sales(amount_sold)
   GLOBAL PARTITION BY RANGE(sale_month)
      ( PARTITION p_100 VALUES LESS THAN (100)
      , PARTITION p_1000 VALUES LESS THAN (1000)
      , PARTITION p_10000 VALUES LESS THAN (10000)
      , PARTITION p_100000 VALUES LESS THAN (100000)
      , PARTITION p_1000000 VALUES LESS THAN (1000000)
      , PARTITION p_greater_than_1000000 VALUES LESS THAN (maxvalue)
      );

Source: Oracle Documentation

Some useful asmcmd commands

When you need to work with ASM disks, you can use the asmcmd (ASM Command Line Utility) utility. I have found the following commands very useful:

asmcmd -p 
(-p option gives you the current directory at any given time - convenient)

lsdg
--> lists disk groups

lsdsk -s
--> -s shows various I/O related statistics of the disks

lsdsk --candidate -p
--> lists all available ASM disk candidates

lsdsk --member -p
--> lists all the current member of a disk group, as opposed to the candidates.

or simply

lsdsk -p

Script for checking the database flashback settings

I normally use the following script to gather the most important facts about my flashback settings and readyness for a potential flashback database operation:

alter session set nls_date_format='DD.MM.YYYY HH24:MI:SS';
set linesize 300
set trimspool on
col name format a35
col time format a35
col guaranteed format a10
col "oldest flback SCN" format 9999999999999
col SCN format 9999999999999
set pagesize 200
spool chk_flashb.log
prompt =================================================
Prompt give me the
prompt * estimated flashback log size
prompt * retention target
prompt * current accumulated size of all flashback logs
prompt =================================================
select estimated_flashback_size/1024/1024 "Estimated Flbacklog Size mb",
       retention_target/60                "Hours of flback logs",
       flashback_size/1024/1024           "Current Flbacklog Size mb"
from v$flashback_database_log
/

prompt ===============================================
Prompt How far back can the database be flashed back?
prompt ===============================================
select oldest_flashback_scn  "oldest flback SCN",
       oldest_flashback_time "oldest flback time"
from v$flashback_database_log
/

prompt =================================================
prompt show the restore points created the last 2 weeks
prompt =================================================
SELECT NAME,
       SCN,
       TIME,
       DATABASE_INCARNATION#,
       GUARANTEE_FLASHBACK_DATABASE "guaranteed",
       STORAGE_SIZE
FROM V$RESTORE_POINT
where time >= SYSDATE-14;
Prompt ===================================
prompt Show the flash recovery area usage
Prompt ===================================
select * from v$flash_recovery_area_usage;

Prompt ======================================
prompt I/O information for flashback logging
Prompt ======================================
select begin_time "Begin time",
        end_time "End time",
        round(flashback_data/1024/1024) "MB of flbk data written",
        round(redo_data/1024/1024)      "MB of redo data"
from   v$flashback_database_stat
order by begin_time asc;
exit

How to use the dbms_explan.display_awr function to find the execution plan of a query in the AWR

set linesize 200
set pagesize 0
select * from table 
(dbms_xplan.display_awr( '44snxh96pfuxb',1084138060,null, 'TYPICAL'));

Where
- the first argument is the SQL id
- the second argument is plan hash value (optional)
- the third argument the DB_ID. If not specified, the value from V$DATABASE is used
- the fourth argument is the format string. TYPICAL is the default value.

Source: Oracle Documentation

How to use dbms_xplan.display_sql_plan_baseline to reveal execution plan for a SQL in the SMB

1. Find the plan_name:

SELECT sql_text,plan_name
FROM DBA_SQL_PLAN_BASELINES
WHERE sql_text LIKE '%

2. Use the dbms_xplan package to generate information + execution plan about this specific SQL plan:

SET LONG 10000
SET LINES 200
SET PAGES 300
SELECT *
FROM   TABLE(DBMS_XPLAN.display_sql_plan_baseline(plan_name=>'SYS_SQL_PLAN_2f5ea4968dd47f8f'));

How to find the plan for a particular SQL in the SMB

SELECT sql_text,plan_name,sql_handle,creator,origin,created, last_executed,enabled,accepted,fixed,autopurge,module
FROM   DBA_SQL_PLAN_BASELINES
WHERE  sql_text LIKE '%your sql text%';

Joined with v$sql, to reveal the SQL ID, and with some formatting:

select  b.SQL_handle,
        b.plan_name,
        b.origin,
        to_char(b.created, 'dd.mm.rrrr hh24:mi') created,
        to_char(b.last_modified, 'dd.mm.rrrr hh24:mi')last_mod, 
        to_char(b.last_executed, 'dd.mm.rrrr hh24:mi') last_exe,
        b.enabled,
        b.accepted, 
        b.fixed,
        b.optimizer_cost,
        b.executions,
        b.cpu_time,
        b.buffer_Gets,
        b.disk_Reads,
        round((b.elapsed_time/b.executions)/1000000,1) "seconds per execution",
        a.sql_id 
FROM DBA_SQL_PLAN_BASELINES b left outer join v$sql a
ON (b.plan_name = a.sql_plan_baseline)
where b.sql_text LIKE 'SELECT * FROM( SELECT KLM.VIEWTYPE KLMTYPE , TS%';

Result may look like the following:

SQL_HANDLE	PLAN_NAME	ORIGIN	CREATED	LAST_MOD	LAST_EXE	ENABLED	ACCEPTED	FIXED	OPTIMIZER_COST	EXECUTIONS	CPU_TIME	BUFFER_GETS	DISK_READS	seconds pr execution	SQL_ID
SQL_5bdd86105a3f22a2	SQL_PLAN_5rrc621d3y8p2a8f06e05	MANUAL-LOAD	11.08.2016 14:42	12.08.2016 07:36	12.08.2016 07:36	YES	YES	YES	10948	27	35862546	7299186	297	1,4	5h0syvrgsjf0j
SQL_5bdd86105a3f22a2	SQL_PLAN_5rrc621d3y8p23e45ab6f	MANUAL-LOAD	11.08.2016 14:42	11.08.2016 14:47		NO	YES	NO	30537	1	3549461	434057	5371	10,8
SQL_5bdd86105a3f22a2	SQL_PLAN_5rrc621d3y8p28dd60076	MANUAL-LOAD	11.08.2016 14:42	11.08.2016 15:02		NO	YES	NO	1324	2	18519310636	1131813	0	9253,6

From the output above, we can see that the fixed plan is the most efficient, that's why it was marked as FIXED in the SMB.