How to explain a SQL statement using bind variables

Sometimes I am asked to analyze an SQL statement picked up from Enterprise Manager Cloud Control.
These SQL statements only reveal the bind variables used, and not their actual values.

To get a more realistic explain plan, ask your developers to provide you with the actual literal values used in the SQL statement, and then prepare a script that contains the following:

connect username/password
SET TIMING ON
SET LINESIZE 200
SET PAGESIZE 0

var b9 number;
var b8 number;
var b7 number;
var b6 varchar2(3);
var b5 varchar2(3);
var b4 varchar2(4);
var b3 varchar2(1);
var b2 varchar2(2);
var b1 varchar2(2);

exec :B9 := 12345;
exec :B8 := 6127737;
exec :B7 := 2013;
exec :B6 := 'KOC';
exec :B5 := 'PPQ';
exec :B4 := 'OOPQ';
exec :B3 := '7';
exec :B2 := 'YM';
exec :B1 := 'WT';

EXPLAIN PLAN FOR
  SELECT ...
  FROM ...
  WHERE COL1 NOT IN (:B6, :B5, :B4)
  AND COL2 = :8
  AND COL3 = :9;


SELECT * FROM table(DBMS_XPLAN.DISPLAY);

Run the script using sqlplus.

Such a script can also come in handy if you're asked to perform changes to the tables' structure, and then execute the actual script to see how much time it takes to execute.
In this case, remove the EXPLAIN PLAN clause and the call to DBMS_XPLAN.DISPLAY, and you'll have all you need to execute the actual SQL statements you're tuning.

How to move a partition online from Oracle 12.1 and onwards

From Oracle version 12.1 and ownwards, moving partitions and subpartitions becomes non-blocking DDL operations. DML against the table can continue while the subobject is being moved. Oracle guarantees that global indexes are maintained during the move operation, so you no longer have to specify the "UPDATE INDEXES ONLINE"-clause.

Remember that skipping this clause will trigger Asynchronous Global Index Maintenance during the nightly maintenance window.

To move a subpartition to a new tablespace:

ALTER TABLE MYUSER.MYTABLE 
MOVE SUBPARTITION P_201312 ONLINE
TABLESPACE MYTABSPC;

To compress the subpartition data at the same time, use

ALTER TABLE MYUSER.MYTABLE 
MOVE SUBPARTITION P_201312_P_OPLSUM ONLINE 
TABLESPACE MYTABSPC 
ROW STORE COMPRESS ADVANCED;

To move a partition to a new tablespace using a DOP of 4 while updating any global indexes defined on the table:

ALTER TABLE MYUSER.MYTABLE
MOVE PARTITION P_2018_05 ONLINE
TABLESPACE NEWTABLESPACE
PARALLEL 4;

To compress the partition data at the same time, use

ALTER TABLE MYUSER.MYTABLE
MOVE PARTITION P_2018_06 ONLINE
TABLESPACE NEWTABLESPACE
ROW STORE COMPRESS ADVANCED
PARALLEL 4;

Remember to change the properties for the partition and the table, too:

ALTER TABLE MYUSER.MYTABLE
MODIFY DEFAULT ATTRIBUTES FOR PARTITION P201312
TABLESPACE MYTABSPC;

ALTER TABLE MYUSER.MYTABLE 
MODIFY DEFAULT ATTRIBUTES 
TABLESPACE MYTABSPC;

A simple query to find how much space the partitions are using would be:

select partition_name,tablespace_name, round(sum(bytes)/1024/1024/1024,1) "GB"
from dba_segments
where segment_name='MYTABLE'
group by partition_name,tablespace_name
order by 1;

Example output:

PARTITION_NAME	TABLESPACE_NAME	GB
SYS_SUBP102302	DATA2	8,9
SYS_SUBP102303	DATA2	2
SYS_SUBP102304	DATA2	0
SYS_SUBP120071	DATA2	7,9
SYS_SUBP120072	DATA	1,8
SYS_SUBP120073	DATA2	0
SYS_SUBP12401	DATA	129,5

Source: Oracle Documentation, New Features list, Version 12.1.0.1. See section "2.2.3.4 ONLINE Move Partition"

What exactly is a LOB index object?

This question was answered by Tom Kyte in an old post from 2001.


A LOB is simply a pointer. It points to an index. The index points to the chunks that make up the LOB.
Hence when you create a LOB, you will always get a lob index created (to find the chunks for the LOB fast) and a segment that holds the LOB data (chunks).

and

You control the placement of the LOB segment. You have NO control over the LOB index, it is an internal structure and goes where it goes. The LOB index is considered "part of the LOB" itself.

How to split a default subpartition in a table using a subpartition template

Here is an example on how to split a subpartition utilizing a "subpartition template".

Before the change, the table looked as follows:

CREATE TABLE RECEIVED_DOCUMENTS
(
  DOCID                       VARCHAR2(160 BYTE)     NULL,
  FISCAL_YEAR                 NUMBER(4)             NULL,
  DOCUMENT_TYPE               VARCHAR2(100 CHAR)     NULL,
  DOCUMENT_NAME               VARCHAR2(1000 CHAR)     NULL,
  VALID                       CHAR(1 BYTE)          NULL,
  CREATED_TIMESTAMP           NUMBER(20)            NULL,
  VERSION                     NUMBER(20)            NULL,
  DOC_XML                     CLOB                  NULL,
  PERIOD                      VARCHAR2(1000 CHAR)     NULL,
  TRANSACTION_ID              VARCHAR2(1000 BYTE)     NULL
)
LOB (DOC_XML) STORE AS SECUREFILE RECEIVED_DOCUMENT_XML_LOB (TABLESPACE  LOB_DATA)
TABLESPACE USERS
PARTITION BY LIST (DOCUMENT_TYPE)
SUBPARTITION BY LIST (PERIOD)
SUBPARTITION TEMPLATE
  (SUBPARTITION SP_2014 VALUES ('2014') TABLESPACE DIV_2014,
   SUBPARTITION SP_2015 VALUES ('2015') TABLESPACE DIV_2015,
   SUBPARTITION SP_2016 VALUES ('2016') TABLESPACE DIV_2016,
   SUBPARTITION SP_DEFAULT VALUES (default) TABLESPACE DIV_DEFAULT
   )
  (  
  PARTITION CUSTOMS_DEC VALUES ('EU_CUST', 'NONEU_CUST'),
  PARTITION VAT_REF VALUES ('EU_VAT_REF', 'EES_VAT_REF','NONEU_VAT_REF'),
  PARTITION DIV_DOCUMENTS VALUES (default)
  )
;

First, alter the subpartition template. I also take the opportunity to create template for subpartitions in the years to come, up until 2020:

ALTER TABLE RECEIVED_DOCUMENTS
SET SUBPARTITION TEMPLATE
  (SUBPARTITION SP_2014 VALUES ('2014') TABLESPACE DIV_2014,
   SUBPARTITION SP_2015 VALUES ('2015') TABLESPACE DIV_2015,
   SUBPARTITION SP_2016 VALUES ('2016') TABLESPACE DIV_2016,
   SUBPARTITION SP_2017 VALUES ('2017') TABLESPACE DIV_2017,
   SUBPARTITION SP_2018 VALUES ('2018') TABLESPACE DIV_2018,
   SUBPARTITION SP_2019 VALUES ('2019') TABLESPACE DIV_2019,
   SUBPARTITION SP_2020 VALUES ('2020') TABLESPACE DIV_2020,
   SUBPARTITION SP_DEFAULT VALUES (default) TABLESPACE DIV_DEFAULT
  );

Then, split the resulting default subpartition DIV_DOCUMENTS_SP_DEFAULT, into multiple other subpartitions. In this example, I am splitting out rows in the DIV_DOCUMENTS_SP_DEFAULT subpartition which have their PERIOD value set to the string '2017':

ALTER TABLE RECEIVED_DOCUMENTS SPLIT SUBPARTITION DIV_DOCUMENTS_SP_DEFAULT INTO
     (  SUBPARTITION DIV_SP_2017 VALUES ('2017') TABLESPACE DIV_2017
      , SUBPARTITION DIV_SP_2017_01 VALUES ('2017-01') TABLESPACE DIV_2017
      , SUBPARTITION DIV_SP_2017_02 VALUES ('2017-02') TABLESPACE DIV_2017
      , SUBPARTITION DIV_SP_2017_03 VALUES ('2017-03') TABLESPACE DIV_2017
      , SUBPARTITION DIV_SP_2017_04 VALUES ('2017-04') TABLESPACE DIV_2017
      , SUBPARTITION DIV_SP_2017_05 VALUES ('2017-05') TABLESPACE DIV_2017
      , SUBPARTITION DIV_SP_2017_06 VALUES ('2017-06') TABLESPACE DIV_2017
      , SUBPARTITION DIV_SP_2017_07 VALUES ('2017-07') TABLESPACE DIV_2017
      , SUBPARTITION DIV_SP_2017_08 VALUES ('2017-08') TABLESPACE DIV_2017
      , SUBPARTITION DIV_SP_2017_09 VALUES ('2017-09') TABLESPACE DIV_2017
      , SUBPARTITION DIV_SP_2017_10 VALUES ('2017-10') TABLESPACE DIV_2017
      , SUBPARTITION DIV_SP_2017_11 VALUES ('2017-11') TABLESPACE DIV_2017
      , SUBPARTITION DIV_SP_2017_12 VALUES ('2017-12') TABLESPACE DIV_2017
      , SUBPARTITION DIV_DOCUMENTS_SP_DEFAULT) UPDATE INDEXES PARALLEL 8;

Notice in the above code that I also consider the other commonly used strings to indicate the year 2017. Of course, a row with a value of 'January 2017' in the PERIOD column will not find an appropriate partition according to the template, and thus end up in the default subpartition DIV_DOCUMENTS_SP_DEFAULT.

Let's add more partitions, this time to be prepared for the year 2018:

ALTER TABLE RECEIVED_DOCUMENTS SPLIT SUBPARTITION DIV_DOCUMENTS_SP_DEFAULT INTO
     (  SUBPARTITION DIV_SP_2018 VALUES ('2017') TABLESPACE DIV_2018
      , SUBPARTITION DIV_SP_2018_01 VALUES ('2017-01') TABLESPACE DIV_2018
      , SUBPARTITION DIV_SP_2018_02 VALUES ('2017-02') TABLESPACE DIV_2018
      , SUBPARTITION DIV_SP_2018_03 VALUES ('2017-03') TABLESPACE DIV_2018
      , SUBPARTITION DIV_SP_2018_04 VALUES ('2017-04') TABLESPACE DIV_2018
      , SUBPARTITION DIV_SP_2018_05 VALUES ('2017-05') TABLESPACE DIV_2018
      , SUBPARTITION DIV_SP_2018_06 VALUES ('2017-06') TABLESPACE DIV_2018
      , SUBPARTITION DIV_SP_2018_07 VALUES ('2017-07') TABLESPACE DIV_2018
      , SUBPARTITION DIV_SP_2018_08 VALUES ('2017-08') TABLESPACE DIV_2018
      , SUBPARTITION DIV_SP_2018_09 VALUES ('2017-09') TABLESPACE DIV_2018
      , SUBPARTITION DIV_SP_2018_10 VALUES ('2017-10') TABLESPACE DIV_2018
      , SUBPARTITION DIV_SP_2018_11 VALUES ('2017-11') TABLESPACE DIV_2018
      , SUBPARTITION DIV_SP_2018_12 VALUES ('2017-12') TABLESPACE DIV_2018
      , SUBPARTITION DIV_DOCUMENTS_SP_DEFAULT) UPDATE INDEXES PARALLEL 8;

This time, the split was considerably faster, since the resulting partitions for 2018 ended up with zero rows in them. Oracle can therefore perform a "fast split".

Read more in the 12.2 documentation

Some important tuning parameters in Oracle 12c

Here are some parameters that you will have to deal with when tuning an Oracle 12c database. Execute the following SQL to view their current setting:

col name format a40
col value format a20
col description format a80
set lines 200

SELECT NAME,VALUE,DESCRIPTION 
FROM   V$SYSTEM_PARAMETER
WHERE NAME IN ( 'optimizer_adaptive_features',
                'optimizer_adaptive_reporting_only',
                'optimizer_features_enable',
                'optimizer_use_sql_plan_baselines',
                'optimizer_capture_sql_plan_baselines',
                'optimizer_dynamic_sampling',
                'optimizer_adaptive_plans',
                'optimizer_adaptive_statistics');

OPTIMIZER_ADAPTIVE_FEATURES enables or disables all of the adaptive optimizer features, including

* adaptive plan (adaptive join methods and bitmap pruning)
* automatic re-optimization
* SQL plan directives
* adaptive distribution Methods

OPTIMIZER_DYNAMIC_SAMPLING controls both when the database gathers dynamic statistics, and the size of the sample that the optimizer uses to gather the statistics.

Dynamic statistics were called dynamic sampling in versions prior to 12c.

Range of values: 0 to 11. Default depends on the value of the parameter optimizer_features_enable:

• If OPTIMIZER_FEATURES_ENABLE is set to 10.0.0 or higher, then 2 
• If OPTIMIZER_FEATURES_ENABLE is set to 9.2.0, then 1 
• If OPTIMIZER_FEATURES_ENABLE is set to 9.0.1 or lower, then 0

At level 2, dynamic statistics are used "if at least one table in the statement has no statistics".

At level 4, Oracle is less restrictive and will use dynamic statistics when "at least one table in the statement has no statistics, the statement has one or more expressions used in the WHERE clause predicates... or the statement uses complex predicates".

When this parameter is set to 11, the optimizer will use dynamic statistics to verify cardinality estimates for all SQL operators, and it will determine an internal time limit to spend verifying the estimates.

Notice that Oracle says the following about level 11:

There are cases where the optimizer will automatically decide to use 11, for example:

•The query will run in parallel.
•The query was executed before and its history is available (from the cursor cache, Automatic Workload Repository, or the SQL Management Base).


From personal experience, this parameter is important! If it's turned off, I would recommend setting it to the default value. I have seen many cases where queries will actually perform better only by setting this parameter from 0 (off) to the default value.

Read more about the different Levels:
Oracle 12cR1
Oracle 12cR2

The two parameters appearing last in the list, optimizer_adaptive_plans and optimizer_adaptive_statistics, were added in 12.2, and replace the parameter optimizer_adaptive_features.

What does 1048575 in the Pstop column in the explain plan indicate?

The answer lies in the dictionary view ALL_PART_TABLES (or DBA_PART_TABLES).
The column partition_count is the total number of partitions, but for interval partitioned tables, this number is always 1048575.


When you explain a SQL statement that is accessing a partitioned table using interval partitioning, you may notice how the number 1048575 always shows up in the Pstop column of your EXPLAIN PLAN output.

My table and the local index is partitoned as follows:

CREATE TABLE MYTABLE
(
  SEKNUM           NUMBER(19)            DEFAULT "MYTAB_MYSEQ"."NEXTVAL" NOT NULL,
  COL1             VARCHAR2(200 CHAR)    NOT NULL,
  COL2             VARCHAR2(100 CHAR)    NOT NULL,
PARTITION BY RANGE (SEKNUM)
INTERVAL( 100000)
);

CREATE UNIQUE INDEX UK_SEK ON HENDELSER_PART_ST1_S.HENDELSER
(SEKVENSNUMMER)
  LOCAL;

This gives me a table with about 200 partitions.

When executing a query using the > operator, like below:

set lines 200
set autot on explain

SELECT count(*)
FROM   mytable
WHERE  seknum > 430000;

It would generate the following plan

--------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |            |     1 |     6 | 41342   (4)| 00:00:02 |       |       |
|   1 |  SORT AGGREGATE           |            |     1 |     6 |            |          |       |       |
|   2 |   PARTITION RANGE ITERATOR|            |    20M|   115M| 41342   (4)| 00:00:02 |     5 |1048575|
|*  3 |    INDEX RANGE SCAN       | UK_SEK     |    20M|   115M| 41342   (4)| 00:00:02 |     5 |1048575|
--------------------------------------------------------------------------------------------------------

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

   3 - access("SEKNUM">430000)

The Pstop value can be confusing. Obviously, you don't have that many partitions.
To verify that partition pruning is indeed working, change the operator from > ("greater than") to < ("less than"), and rerun the query:

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

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

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

|   0 | SELECT STATEMENT          |            |     1 |     6 |   887   (4)| 00:00:01 |       |       |

|   1 |  SORT AGGREGATE           |            |     1 |     6 |            |          |       |       |

|   2 |   PARTITION RANGE ITERATOR|            |   431K|  2525K|   887   (4)| 00:00:01 |     1 |     5 |

|*  3 |    INDEX RANGE SCAN       | UK_SEK     |   431K|  2525K|   887   (4)| 00:00:01 |     1 |     5 |

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



Predicate Information (identified by operation id):

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



3 - access("SEKNUM"<430000)

You now see that the Pstop column is 5, so Oracle search partitions 1 through 4 for the rows.

These are:

set lines 200
col partition_name format a30
col high_value format a20

select partition_name,partition_position,high_value
from dba_tab_partitions 
where table_NAME='MYTABLE'
and table_owner='SCOTT'
and partition_position between 1 and 4;

PARTITION_NAME                 PARTITION_POSITION HIGH_VALUE          
------------------------------ ------------------ --------------------
P1_INIT                                         1 100001              
SYS_P9545                                       2 200001              
SYS_P9548                                       3 300001              
SYS_P9551                                       4 400001              

4 rows selected.

How to use the 12c Privilege Analysis feature

One of the many New features in Oracle 12c is the ability to perform analysis of privileges that are assigned to a user.

Privilege Analysis sorts under Oracles "Database Vault" option, but can be used without enabling Database Vault.
Note that you need a valid License for Database Vault option to use the privilege analysis package.

To check if it's allready in use:

SELECT
   NAME             ,
   DETECTED_USAGES  ,
   FIRST_USAGE_DATE ,
   LAST_USAGE_DATE,
   LAST_SAMPLE_DATE,
   CURRENTLY_USED,
   DESCRIPTION   
 FROM
   DBA_FEATURE_USAGE_STATISTICS
 WHERE
   FIRST_USAGE_DATE IS NOT NULL
   AND NAME LIKE 'Privilege%';

From my example database, this is the output:

NAME	DETECTED_USAGES	FIRST_USAGE_DATE	LAST_USAGE_DATE	LAST_SAMPLE_DATE	CURRENTLY_USED	DESCRIPTION
Privilege Capture	11	27.05.2017 03:41:35	05.08.2017 03:46:46	05.08.2017 03:46:46	TRUE	Privilege Capture is being used

Here's how to set it up:

1. Create the capture:

BEGIN
  DBMS_PRIVILEGE_CAPTURE.create_capture(
    name        => 'my_policy',
    type        => DBMS_PRIVILEGE_CAPTURE.g_context,
    condition   => 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') = ''SCOTT'''
  );
END;
/

2. Check that the capture was created:

COLUMN name FORMAT A15
COLUMN roles FORMAT A20
COLUMN context FORMAT A30
SET LINESIZE 100

SELECT name,
       type,
       enabled,
       roles,
       context
FROM   dba_priv_captures
ORDER BY name;

3. Enable the capture

BEGIN
  DBMS_PRIVILEGE_CAPTURE.enable_capture('my_policy');
END;
/

When a representative time has passed, you can disable the capture and generate results:

BEGIN
  DBMS_PRIVILEGE_CAPTURE.disable_capture(
    name        => 'saga2_felles_policy'
  );

  DBMS_PRIVILEGE_CAPTURE.generate_result(
    name        => 'saga2_felles_policy'
  );
END;
/

You can check the results of the capture by using these queries:

SET LINESIZE 200
COLUMN username FORMAT A20
COLUMN username FORMAT A20
COLUMN used_role FORMAT A30
COLUMN path FORMAT A50
COLUMN sys_priv FORMAT A30
COLUMN obj_priv FORMAT A8
COLUMN object_owner FORMAT A15
COLUMN object_name FORMAT A30
COLUMN object_type FORMAT A11

prompt ================================
prompt Which privileges have been used?
prompt Look in DBA_USED_SYSPRIVS
prompt ================================
SELECT username, sys_priv
FROM   dba_used_sysprivs
WHERE  capture = 'my_policy'
ORDER BY username, sys_priv;

prompt ================================
prompt How were the privileges granted
prompt to the user?
prompt Look in DBA_USED_SYSPRIVS_PATH
prompt ================================
SELECT username, sys_priv, used_role, path
FROM   dba_used_sysprivs_path
WHERE  capture='my_policy'
order by username,sys_priv;

prompt ================================
prompt What object privileges were necessary?
prompt Look in DBA_USED_OBJPRIVS
prompt ================================
SELECT username, obj_priv, object_owner, object_name, object_type
FROM   dba_used_objprivs
WHERE  capture = 'my_policy';

prompt ================================
prompt How were the object privileges granted to the user?
prompt Look in DBA_USED_OBJPRIVS_PATH
prompt ================================

SELECT username, obj_priv, object_owner, object_name, used_role, path
FROM   dba_used_objprivs_path
WHERE  capture = 'my_policy';

exit

To drop the usage of Privilage Capture all together:

BEGIN
  DBMS_PRIVILEGE_CAPTURE.drop_capture(
    name        => 'my_policy'
  );
END;
/