Things worth to mention and remember (III) - Parallel Execution Control 3

Continuing from the previous part of this series I'll cover in this post some further basics about parallel execution control:

- Keep in mind that there are two classes of parallel hints: PARALLEL and PARALLEL_INDEX. One is about the costing of parallel full table / index fast full scans, the other one about costing (driving) parallel index scans, which are only possible with partitioned indexes (PX PARTITION granule vs. PX BLOCK granule)

- The same applies to the opposite, NO_PARALLEL (or NOPARALLEL in older releases) and NO_PARALLEL_INDEX. It is in particular important to realize that specifying a NO_PARALLEL hint only tells the optimizer to not evaluate a parallel full table scan / index fast full scan, however it might still evaluate a parallel index scan if feasible and therefore still might go parallel. This is particularly important if you are under the impression that using the NO_PARALLEL hint will ensure that no parallel slaves will be used by the statement at execution time. This is not entirely correct - it still might go for a parallel index scan. You would need to use different means, either use a NO_PARALLEL_INDEX hint in addition to NO_PARALLEL, use the OPT_PARAM hint to set the optimizer parameter PARALLEL_EXECUTION_ENABLED to FALSE at statement level or disabling PARALLEL QUERY on session level.

- Furthermore note that the PARALLEL and PARALLEL_INDEX hints merely tell the optimizer to evaluate the costing of a plan with the determined parallel degree. If the optimizer however finds a serial plan with a lower cost, it will prefer that serial execution plan despite the usage of the parallel hints. This even applies to a session "forced" to use parallel query via ALTER SESSION FORCE PARALLEL QUERY.

- Remember that with DML / CTAS DDL there are actually (at least) two potential parallel parts involved: The Create Table/DML part and the query part (except for a single row INSERT INTO...VALUES DML). Both parts independently from each other can be performed in parallel or serial, so you could end up with:

- Serial DDL/DML + serial query
- Serial DDL/DML + parallel query
- Parallel DDL/DML + serial query
- Parallel DDL/DML + parallel query

Whether some combinations make sense or not is a different question, however from a technical point of view all of them are possible.

You therefore need to carefully check which part you want to execute in parallel and control it accordingly (and remember the fact that parallel DML needs to be explicitly enabled in the session). Check the (actual) execution plan to ensure you get the desired parallel execution.

ORA-14404 / 14405 and Deferred Segment Creation

Have a look at the following SQL*Plus output snippet:

SQL>> select count(*) from dba_segments where tablespace_name in ('TS_TO_DROP', 'TS_TO_NOT_DROP');

COUNT(*)
----------
0

SQL>> drop tablespace ts_to_drop including contents and datafiles cascade constraints;
drop tablespace ts_to_drop including contents and datafiles cascade constraints
*
ERROR at line 1:
ORA-14404: partitioned table contains partitions in a different tablespace

How is it possible to get a message about an object that is supposed to have partitions in the tablespace to be dropped and in some other tablespaces if there is no segment contained in the tablespace to be dropped?

May be a dictionary corruption, or a problem with the recyclebin?

Possibly yes, but in this case the answer is simple and can be found in the title: It is a feature, not a bug, because you are running (at least) 11.2.0.2 Enterprise Edition and make use of deferred segment creation, which is enabled by default.

The 11.2.0.2 patch set extended the deferred segment creation to partitioned objects, which means that it adds just another twist to the potential "problems" caused by deferred segment creation.

In this case, although no segment actually exists in the tablespace, a partition is supposed to be created in the tablespace to be dropped once you add data to it or materialize the segment explicitly. Furthermore the object has other partitions that are assigned to different tablespaces (and again potentially have not been materialized yet).

Obviously Oracle treats this the same as if the segment(s) existed and therefore throws this error message although the tablespace is effectively empty.

The same applies to partitioned indexes, by the way, the only difference is the error message raised (ORA-14405).

For a complete walkthrough, see this demo script:

set echo on linesize 200

drop tablespace ts_to_drop including contents and datafiles;

drop tablespace ts_to_not_drop including contents and datafiles;

column file_path new_value file_path

select
substr(
file_name
, 1
, instr(
file_name
, case when dbms_utility.port_string like '%WIN%' then '\' else '/' end
, -1)
) as file_path
from
dba_data_files
where
tablespace_name = (select tablespace_name from dba_tablespaces where contents = 'PERMANENT' and tablespace_name not in ('SYSTEM', 'SYSAUX') and block_size = (select value from v$parameter where name = 'db_block_size') and rownum <= 1)
and rownum <= 1;

create tablespace ts_to_drop datafile '&file_path.ts_to_drop.dbf' size 10M reuse autoextend off;

create tablespace ts_to_not_drop datafile '&file_path.ts_to_not_drop.dbf' size 10M reuse autoextend off;

drop table t;

purge table t;

create table t
(id number)
segment creation deferred
partition by list (id)
(
partition p_1 values (1) tablespace ts_to_not_drop,
partition p_2 values (2)
)
tablespace ts_to_drop
;

create index t_idx on t (id) local;

select count(*) from dba_segments where tablespace_name in ('TS_TO_DROP', 'TS_TO_NOT_DROP');

drop tablespace ts_to_drop including contents and datafiles cascade constraints;

select def_tablespace_name from user_part_tables where table_name = 'T';

select partition_name, tablespace_name from user_tab_partitions where table_name = 'T';

select def_tablespace_name from user_part_indexes where table_name = 'T';

select partition_name, tablespace_name from user_ind_partitions where index_name = 'T_IDX';

alter table t move partition p_2 tablespace ts_to_not_drop segment creation deferred;

select count(*) from dba_segments where tablespace_name in ('TS_TO_DROP', 'TS_TO_NOT_DROP');

drop tablespace ts_to_drop including contents and datafiles cascade constraints;

alter index t_idx rebuild partition p_2 tablespace ts_to_not_drop;

drop tablespace ts_to_drop including contents and datafiles cascade constraints;

alter table t add partition p_3 values (3);

drop tablespace ts_to_not_drop including contents and datafiles cascade constraints;

Which gives me this output in 11.2.0.2:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
SQL> drop tablespace ts_to_drop including contents and datafiles;
drop tablespace ts_to_drop including contents and datafiles
*
ERROR at line 1:
ORA-00959: tablespace 'TS_TO_DROP' does not exist


SQL>
SQL> drop tablespace ts_to_not_drop including contents and datafiles;
drop tablespace ts_to_not_drop including contents and datafiles
*
ERROR at line 1:
ORA-00959: tablespace 'TS_TO_NOT_DROP' does not exist


SQL>
SQL> column file_path new_value file_path
SQL>
SQL> select
2 substr(
3 file_name
4 , 1
5 , instr(
6 file_name
7 , case when dbms_utility.port_string like '%WIN%' then '\' else '/' end
8 , -1)
9 ) as file_path
10 from
11 dba_data_files
12 where
13 tablespace_name = (select tablespace_name from dba_tablespaces where contents = 'PERMANENT' and tablespace_name not in ('SYSTEM', 'SYSAUX') and block_size = (select value from v$parameter where name = 'db_block_size') and rownum <= 1)
14 and rownum <= 1;

FILE_PATH
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
C:\APP\ORACLE\ORADATA\ORCL112\

SQL>
SQL> create tablespace ts_to_drop datafile '&file_path.ts_to_drop.dbf' size 10M reuse autoextend off;
old 1: create tablespace ts_to_drop datafile '&file_path.ts_to_drop.dbf' size 10M reuse autoextend off
new 1: create tablespace ts_to_drop datafile 'C:\APP\ORACLE\ORADATA\ORCL112\ts_to_drop.dbf' size 10M reuse autoextend off

Tablespace created.

SQL>
SQL> create tablespace ts_to_not_drop datafile '&file_path.ts_to_not_drop.dbf' size 10M reuse autoextend off;
old 1: create tablespace ts_to_not_drop datafile '&file_path.ts_to_not_drop.dbf' size 10M reuse autoextend off
new 1: create tablespace ts_to_not_drop datafile 'C:\APP\ORACLE\ORADATA\ORCL112\ts_to_not_drop.dbf' size 10M reuse autoextend off

Tablespace created.

SQL>
SQL> drop table t;
drop table t
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>
SQL> purge table t;
purge table t
*
ERROR at line 1:
ORA-38307: object not in RECYCLE BIN


SQL>
SQL> create table t
2 (id number)
3 segment creation deferred
4 partition by list (id)
5 (
6 partition p_1 values (1) tablespace ts_to_not_drop,
7 partition p_2 values (2)
8 )
9 tablespace ts_to_drop
10 ;

Table created.

SQL>
SQL> create index t_idx on t (id) local;

Index created.

SQL>
SQL> select count(*) from dba_segments where tablespace_name in ('TS_TO_DROP', 'TS_TO_NOT_DROP');

COUNT(*)
----------
0

SQL>
SQL> drop tablespace ts_to_drop including contents and datafiles cascade constraints;
drop tablespace ts_to_drop including contents and datafiles cascade constraints
*
ERROR at line 1:
ORA-14404: partitioned table contains partitions in a different tablespace


SQL>
SQL> select def_tablespace_name from user_part_tables where table_name = 'T';

DEF_TABLESPACE_NAME
------------------------------
TS_TO_DROP

SQL>
SQL> select partition_name, tablespace_name from user_tab_partitions where table_name = 'T';

PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
P_1 TS_TO_NOT_DROP
P_2 TS_TO_DROP

SQL>
SQL> select def_tablespace_name from user_part_indexes where table_name = 'T';

DEF_TABLESPACE_NAME
------------------------------


SQL>
SQL> select partition_name, tablespace_name from user_ind_partitions where index_name = 'T_IDX';

PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
P_1 TS_TO_NOT_DROP
P_2 TS_TO_DROP

SQL>
SQL> alter table t move partition p_2 tablespace ts_to_not_drop segment creation deferred;

Table altered.

SQL>
SQL> select count(*) from dba_segments where tablespace_name in ('TS_TO_DROP', 'TS_TO_NOT_DROP');

COUNT(*)
----------
0

SQL>
SQL> drop tablespace ts_to_drop including contents and datafiles cascade constraints;
drop tablespace ts_to_drop including contents and datafiles cascade constraints
*
ERROR at line 1:
ORA-14405: partitioned index contains partitions in a different tablespace


SQL>
SQL> alter index t_idx rebuild partition p_2 tablespace ts_to_not_drop;

Index altered.

SQL>
SQL> drop tablespace ts_to_drop including contents and datafiles cascade constraints;

Tablespace dropped.

SQL>
SQL> alter table t add partition p_3 values (3);
alter table t add partition p_3 values (3)
*
ERROR at line 1:
ORA-00959: tablespace 'TS_TO_DROP' does not exist


SQL>
SQL> drop tablespace ts_to_not_drop including contents and datafiles cascade constraints;

Tablespace dropped.

SQL>

The script also shows that the default tablespace that can be assigned to (sub-) partitions is not relevant in this case. It also shows that you can now move and rebuild segments without materializing them - I think this is also a feature that has been added in 11.2.0.2.

Delayed Block Cleanout / ORA-01555

Here is a link to a collection of scripts that can be used for some entertainment (well, it probably depends on your personal definition of "entertainment"...) regarding "Delayed Block Cleanout". These scripts are meant to be used in a playground environment - do not attempt to get them close to anything important as they might have some undesirable side-effects. Please read the comments in the file header description before attempting to run them.

The scripts allow to gain some insights into the different variants of delayed block cleanout and how to force an ORA-01555 error caused by it. The scripts among others allow to demonstrate that blocks generated via direct-path inserts are not "clean" and subject to a special kind of delayed block cleanout and therefore - although unlikely - can still cause an ORA-01555 error due to delayed block cleanout. This has been discussed recently here.

It is amazing how much effort has Oracle put into minimizing the overheads of storing the lock information in the block and still offering fast commits:

- There are fast cleanouts for small transactions that leave blocks in a "clean enough" state (which means that only the commit SCN of the ITL slot is updated but everything else is left behind for others to clean up) but do not generate redo although they modify a block. The block will be dirty and written (possibly again) by DBWR

- There are "immediate" (as part of consistent / current mode gets) and "deferred" (piggy-back as part of subsequent block modifications) delayed block cleanouts for blocks left behind by larger transactions that will tidy up the block including any lock bytes and ITL slot information. These will generate redo and dirty blocks.

- There is a special treatment of blocks that are generated by direct-path inserts that do not need to be cleaned up as blocks modified by conventional DML but still miss the "commit SCN" information from the ITL slots. Only a single block will be cleaned out (small amount of redo and block dirtied) and the "commit SCN" obtained will be "cached" in the session for further blocks accessed by this session

For further reading regarding the topic I recommend these links as starting points:

- "Clean it up" by Jonathan Lewis
- "Impact of Direct Reads on Delayed Block Cleanouts" by Christian Antognini

Some final food for thought regarding delayed block cleanout:

- Direct path reads performed by parallel execution slaves
- Adaptive serial direct reads introduced in Oracle 11g
- Readonly tablespaces
- Index blocks modified by DML