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;
/

How to rebuild an index subpartition

To limit a rebuild to a specific index subpartition only, use the following syntax:

ALTER INDEX SCOTT.SALES_IDX3
  REBUILD SUBPARTITION SALES_IDX3_SP_2017_06 ONLINE
  TABLESPACE DATA2;

Notice the use of the ONLINE keyward, which will allow DML against the table (and thus update the index).

To generate rebuild-statements, one index at a time, I have used the script below.

It takes as arguments
1. The index owner
2. The index name
3. The new tablespace where you want to place your subpartitions
4. The old tablespace from which you want to move out

set termout off
select 'alter session set nls_language=''american'';' from dual;
set termout on
accept index_owner prompt 'Index owner: '
accept index_name prompt 'Index name: '
accept new_tabspc prompt 'New tablespace: '
accept old_tabspc prompt 'Old tablespace: '

set lines 300
set heading off
set feedback off
set verify off
set echo off
set pages 0
set trimspool on
spool exec_move_subpart_&&index_owner..&&index_name..sql
select 'alter session set nls_language=''american'';' from dual;

select 'alter session force parallel ddl;' from dual;
select 'set timing on' from dual;
select 'set lines 200' from dual;
select 'set trimspool on' from dual;
select 'spool exec_rebuild_subpart_&&index_owner..&&index_name..log' from dual;
select 'exec DBMS_APPLICATION_INFO.SET_MODULE(module_name=>''Move'', action_name=>''Rebuild_supbart_&&index_name'');' from dual;
select 'exec DBMS_APPLICATION_INFO.SET_CLIENT_INFO (client_info=>''Rebuild_supbart_t_&&index_name'');' from dual;
select 'exec DBMS_SESSION.SET_IDENTIFIER (client_id=>''Rebuild_supbart_&&index_name''); ' from dual;

select 'prompt moving the index subpartitions from &&old_tabspc to &&new_tabspc,' from dual;
select 'prompt and setting default attributes for the involved partitions. ' from dual;
SELECT 'ALTER INDEX ' ||  IDXSUBPART.INDEX_OWNER || '.' || IDXSUBPART.INDEX_NAME || ' rebuild subpartition ' || IDXSUBPART.SUBPARTITION_NAME || ' tablespace &&new_tabspc ONLINE;'
FROM   DBA_IND_SUBPARTITIONS IDXSUBPART JOIN DBA_SEGMENTS S
ON     (IDXSUBPART.SUBPARTITION_NAME = S.PARTITION_NAME )
WHERE  IDXSUBPART.INDEX_NAME='&&index_name'
AND    IDXSUBPART.TABLESPACE_NAME='&&old_tabspc'
AND    S.SEGMENT_TYPE='INDEX SUBPARTITION'
union
select distinct 'alter index ' ||   IDXSUBPART.INDEX_OWNER || '.' || IDXSUBPART.INDEX_NAME || ' modify default attributes for partition ' || idxsubpart.partition_name || ' tablespace &&new_tabspc;'
FROM   DBA_IND_SUBPARTITIONS IDXSUBPART JOIN DBA_SEGMENTS S
ON     (IDXSUBPART.SUBPARTITION_NAME = S.PARTITION_NAME )
WHERE  IDXSUBPART.INDEX_NAME='&&index_name'
AND    IDXSUBPART.TABLESPACE_NAME='&&old_tabspc'
AND    S.SEGMENT_TYPE='INDEX SUBPARTITION';

select 'exit' from dual;
exit

Run it as a user with access to the data dictionary:

sqlplus / as sysdba @gen_move_idx_subpart.sql

It will generate a new script called exec_move_subpart_<username>.<index_name>.sql which will actually perform the rebuild:

sqlplus / as sysdba @exec_move_subpart_<username>.<index_name>.sql

I also added som calls to dbms_application_info so that the session can be easily found in v$session

How to solve "INS-32035: The chosen installation conflicts with software already installed in the given oracle home" when installing into an old directory

Workaround for the error

INS-32035: The chosen installation conflicts with software already installed in the given oracle home

from the Oracle Universal Installer:

1. detatch the home from the ORACLE_HOME you install from:

cd $current_oracle_home/oui/bin
./runInstaller -detachHome ORACLE_HOME=/u01/oracle/product/11204 invPtrLoc=/u01/oracle/product/11201/oraInst.loc

2. edit the file /u01/oraInventory/ContentsXML/inventory.xml

In my case from:

<HOME_LIST>
<HOME NAME="OraDb11g_home1" LOC="/u01/oracle/product/db/11201" TYPE="O" IDX="1"/>
<HOME NAME="OraDb11g_home2" LOC="/u01/oracle/product/db/11204" TYPE="O" IDX="2"/>
</HOME_LIST>

to

<HOME_LIST>
<HOME NAME="OraDB11g_home1" LOC="/u01/oracle/product/db/11201" TYPE="O" IDX="1"/>
</HOME_LIST> 

3. Delete the files in /u01/oracle/product/db/11204 physically from disk, before you try again.

That's it.
Universal Installer should now recognize the selected directory, and not give you any complaints when reinstalling into the same directory.

Automatic startup and shutdown script for Oracle databases on the Linux Platform

On most installations of the Oracle database, it is desirable to add a script for automatic startup and shutdown.
This is how to do it.
The instructions holds true for both 11g and 12c.

1. Edit the /etc/oratab file, so that the entry for your database has a Y at the end:

testdb01:/u01/oracle/product/db/112:Y

2. cd /etc/init.d

3. create a file called dbora, and add the following lines to it. Note that you should enter the name of your ORACLE_HOME path and the oracle software installation owner as values for ORA_HOME and ORA_OWNER, respectively:

#! /bin/sh 
# description: Oracle auto start-stop script.
#
# Set ORACLE_HOME to be equivalent to the $ORACLE_HOME
# from which you wish to execute dbstart and dbshut;
#
# Set ORA_OWNER to the user id of the owner of the
# Oracle database in ORACLE_HOME.

ORA_HOME=/u01/oracle/product/db/112
ORA_OWNER=oracle

case "$1" in
'start')
    # Start the Oracle databases:
    # The following command assumes that the oracle login
    # will not prompt the user for any values
    # Remove "&" if you don't want startup as a background process.
    su - $ORA_OWNER -c "$ORA_HOME/bin/dbstart $ORA_HOME" &
    touch /var/lock/subsys/dbora
    ;;

'stop')
    # Stop the Oracle databases:
    # The following command assumes that the oracle login
    # will not prompt the user for any values
    su - $ORA_OWNER -c "$ORA_HOME/bin/dbshut $ORA_HOME" &
    rm -f /var/lock/subsys/dbora
    ;;
esac

4. Set permissions:

# chgrp dba dbora
# chmod 750 dbora

5. Create symlinks to the different run level script directories:

ln -s /etc/init.d/dbora /etc/rc.d/rc0.d/K01dbora
ln -s /etc/init.d/dbora /etc/rc.d/rc3.d/S99dbora
ln -s /etc/init.d/dbora /etc/rc.d/rc5.d/S99dbora

Verify the symlinks. They should all point back to /etc/init.d/dbora

ls -la /etc/rc.d/rc0.d/K01dbora
ls -la /etc/rc.d/rc3.d/S99dbora
ls -la /etc/rc.d/rc5.d/S99dbora

What about the listener?
According to Oracle, as long as it has the default name of "LISTENER", you don't need to add it to your startup/shutdown script. If you use a different name, you must add the following line to the /etc/init.d/dbora script:

$ORACLE_HOME/bin/lsnrctl {start|stop} listener_name

The original instructions from Oracle can be found here

Possible solution to unix error "X11 connection rejected because of wrong authentication"

I was trying to start an X window Application on my server as user Oracle, but received the error

oracle@myserver:[testdb01]# xclock
X11 connection rejected because of wrong authentication.
Error: Can't open display: localhost:10.0

The DISPLAY variable was set to localhost:10.0.

The solution was to logon as root and execute

root@myserver~]# xauth list $DISPLAY

The result from this was

myserver.mydomain.com/unix:10  MIT-MAGIC-COOKIE-1  83f9f76d03106db6fc0880edfb005607

Then change user back to oracle and add the cookie to oracle's authorization file:

[root@myserver ~]# su - oracle
oracle@myserver:[testdb01]# xauth add :10 MIT-MAGIC-COOKIE-1 83f9f76d03106db6fc0880edfb005607

If you get the error:

xauth:  file /home/oracle/.Xauthority does not exist

create the file first:

touch /home/oracle/.Xauthority

Then retry the "xauth add" command above.
Verify with:

oracle@myserver:[testdb01]# xauth list $DISPLAY
myserver.mydomain.com/unix:10  MIT-MAGIC-COOKIE-1  83f9f76d03106db6fc0880edfb005607
oracle@myserver:[testdb01]# echo $DISPLAY
localhost:10.0

I could now execute X Applications:

oracle@myserver:[testdb01]# xclock