Oakies Blog Aggregator

Create constraints in your datawarehouse – why and how

We still see some developers not declaring referential integrity constraints in datawarehouse databases because they think they don’t need it (integrity of data has been validated by the ETL). Here is a small demo I did to show why you need to declare them, and how to do it to avoid any overhead on the ETL.

Test case

I create 3 dimension tables and 1 fact table:

21:01:18 SQL> create table DIM1 (DIM1_ID number, DIM1_ATT1 varchar2(20));
Table DIM1 created.
 
21:01:19 SQL> create table DIM2 (DIM2_ID number, DIM2_ATT1 varchar2(20));
Table DIM2 created.
 
21:01:20 SQL> create table DIM3 (DIM3_ID number, DIM3_ATT1 varchar2(20));
Table DIM3 created.
 
21:01:21 SQL> create table FACT (DIM1_ID number, DIM2_ID number, DIM3_ID number,MEAS1 number);
Table FACT created.

I insert 10 million rows into the fact table:

21:01:22 SQL> insert into FACT select mod(rownum,3),mod(rownum,5),mod(rownum,10),rownum from xmltable('1 to 10000000');
10,000,000 rows inserted.
 
Elapsed: 00:00:18.983

and fill the dimension tables from it:

21:01:42 SQL> insert into DIM1 select distinct DIM1_ID,'...'||DIM1_ID from FACT;
3 rows inserted.
 
Elapsed: 00:00:01.540
 
21:01:52 SQL> insert into DIM2 select distinct DIM2_ID,'...'||DIM2_ID from FACT;
5 rows inserted.
 
Elapsed: 00:00:01.635
 
21:01:57 SQL> insert into DIM3 select distinct DIM3_ID,'...'||DIM3_ID from FACT;
10 rows inserted.
 
Elapsed: 00:00:01.579
 
21:01:58 SQL> commit;
Commit complete.

Query joining fact with one dimension

I’ll run the following query:

21:01:58 SQL> select count(*) from FACT join DIM1 using(DIM1_ID);
 
COUNT(*)
--------
10000000
 
Elapsed: 00:00:01.015

Here is the execution plan:

21:02:12 SQL> select * from dbms_xplan.display_cursor();
 
PLAN_TABLE_OUTPUT
-----------------
SQL_ID 4pqjrjkc7sn17, child number 0
-------------------------------------
select count(*) from FACT join DIM1 using(DIM1_ID)
 
Plan hash value: 1826335751
 
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7514 (100)| |
| 1 | SORT AGGREGATE | | 1 | 26 | | |
|* 2 | HASH JOIN | | 10M| 253M| 7514 (2)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DIM1 | 3 | 39 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| FACT | 10M| 126M| 7482 (1)| 00:00:01 |
----------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
2 - access("FACT"."DIM1_ID"="DIM1"."DIM1_ID")
 
Note
-----
- dynamic statistics used: dynamic sampling (level=2)

Actually, we don’t need that join. A dimension table has two goals:

  • filter facts on the dimension attributes. Example: filter on customer last name
  • add dimension attributes to the result. Example: add customer first name

Here, there is no WHERE clause on DIM1 columns, and no columns from DIM1 selected. We don’t need to join to DIM1. However, we often see those useless joins for two reasons:

  • We query a view that joins the fact with all dimensions
  • The query is generated by a reporting tool which always join to dimensions

Join elimination

The Oracle optimizer is able to remove those kinds of unnecessary joins. But one information is missing here for the optimizer. We know that all rows in the fact table have a matching row in each dimension, but Oracle doesn’t know that. And if there is no mathing row, then the inner join should not return the result. For this reason, the join must be done.

Let’s give this information to the optimizer: declare the foreign key from FACT to DIM1 so that Oracle knows that there is a many-to-one relationship:

21:02:17 SQL> alter table DIM1 add constraint DIM1_PK primary key(DIM1_ID);
Table DIM1 altered.
 
Elapsed: 00:00:00.051
 
21:02:20 SQL> alter table FACT add constraint DIM1_FK foreign key(DIM1_ID) references DIM1;
Table FACT altered.
 
Elapsed: 00:00:03.210

I’ve spent 3 seconds here to create this foreign key (would have been much longer with a real fact table and lot of columns and rows) but now, the optimizer is able to eliminate the join:

21:02:24 SQL> select count(*) from FACT join DIM1 using(DIM1_ID);
 
COUNT(*)
--------
10000000
 
21:02:25 SQL> select * from dbms_xplan.display_cursor();
 
PLAN_TABLE_OUTPUT
-----------------
SQL_ID 4pqjrjkc7sn17, child number 0
-------------------------------------
select count(*) from FACT join DIM1 using(DIM1_ID)
 
Plan hash value: 3735838348
 
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7488 (100)| |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | TABLE ACCESS FULL| FACT | 10M| 126M| 7488 (1)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
2 - filter("FACT"."DIM1_ID" IS NOT NULL)
 
Note
-----
- dynamic statistics used: dynamic sampling (level=2)

No join needed here, the query is faster. This is exactly the point of this blog post: to show you that declaring constraints improve performance of queries. It adds information to the optimizer, like statistics. Statistics gives estimated cardinalities. Foreign keys are exact cardinality (many-to-one).

No validate

When loading a datawarehouse, you usually don’t need to validate the constraints because data was bulk loaded from a staging area where all data validation has been done. You don’t want to spend time validating constraints (the 3 seconds in my small example above) and this is why some datawarehouse developers do not declare constraints.

However, we can declare constraints without validating them. Let’s do that for the second dimension table:

21:02:34 SQL> alter table DIM2 add constraint DIM2_PK primary key(DIM2_ID) novalidate;
Table DIM2 altered.
 
Elapsed: 00:00:00.018
%nbsp;
21:02:35 SQL> alter table FACT add constraint DIM2_FK foreign key(DIM2_ID) references DIM2 novalidate;
Table FACT altered.
 
Elapsed: 00:00:00.009

That was much faster than the 3 seconds we had for the ‘validate’ constraint which is the default. Creating a constraint in NOVALIDATE is immediate and do not depend on the size of the table.

However this is not sufficient to get the join elimination:

21:02:39 SQL> select count(*) from FACT join DIM2 using(DIM2_ID);
 
COUNT(*)
--------
10000000
 
21:02:40 SQL> select * from dbms_xplan.display_cursor();
 
PLAN_TABLE_OUTPUT
-----------------
SQL_ID 4t9g2n6duw0jf, child number 0
-------------------------------------
select count(*) from FACT join DIM2 using(DIM2_ID)
 
Plan hash value: 3858910383
 
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7518 (100)| |
| 1 | SORT AGGREGATE | | 1 | 26 | | |
|* 2 | HASH JOIN | | 10M| 253M| 7518 (2)| 00:00:01 |
| 3 | INDEX FULL SCAN | DIM2_PK | 5 | 65 | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| FACT | 10M| 126M| 7488 (1)| 00:00:01 |
-------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
2 - access("FACT"."DIM2_ID"="DIM2"."DIM2_ID")
 
Note
-----
- dynamic statistics used: dynamic sampling (level=2)

The constraint ensures that no rows will be inserted without a matching row in the dimension. However, because Oracle has not validated the result itself, it does not apply the join elimination, just in case a previously existing row has no matching dimension.

Rely novalidate

If you want the optimizer to do the join elimination on a ‘novalidate’ constraint, then it has to trust you and rely on the constraint you have validated.

RELY is an attribute of the constraint that you can set:

21:02:44 SQL> alter table DIM2 modify constraint DIM2_PK rely;
Table DIM2 altered.
 
Elapsed: 00:00:00.016
 
21:02:45 SQL> alter table FACT modify constraint DIM2_FK rely;
Table FACT altered.
 
Elapsed: 00:00:00.010

But this is not sufficient. You told Oracle to rely on your constraint, but Oracle must trust you.

Trusted

The join elimination is a rewrite of the query and, by default, rewrite is enabled but only when integrity is enforced by Oracle:

21:02:50 SQL> show parameter query_rewrite
NAME TYPE VALUE
----------------------- ------ --------
query_rewrite_enabled string TRUE
query_rewrite_integrity string ENFORCED

Let’s allow our session to have rewrite transformations to trust our RELY constraints:

21:02:52 SQL> alter session set query_rewrite_integrity=trusted;
Session altered.

Now, joining to DIM2 without using DIM2 columns outside of the join allows join elimination:

21:02:57 SQL> select count(*) from FACT join DIM2 using(DIM2_ID);
 
COUNT(*)
--------
10000000
 
Elapsed: 00:00:00.185
21:02:58 SQL> select * from dbms_xplan.display_cursor();
 
PLAN_TABLE_OUTPUT
-----------------
SQL_ID 4t9g2n6duw0jf, child number 0
-------------------------------------
select count(*) from FACT join DIM2 using(DIM2_ID)
 
Plan hash value: 3735838348
 
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7494 (100)| |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | TABLE ACCESS FULL| FACT | 10M| 126M| 7494 (1)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
2 - filter("FACT"."DIM2_ID" IS NOT NULL)
 
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- rely constraint used for this statement

In 12.2 the execution plan has a note to show that the plan depends on RELY constraint.

From this example, you can see that you can, and should, create RELY NOVALIDATE constraints on tables where you know the existing data is valid. They are immediately created, without any overhead on the load process, and helps to improve queries generated on your dimensional model.

Rely Disable

I said that a NOVALIDATE constraint has no overhead when created, but you may have further inserts or updates in your datawarehouse. And then, those constraints will have to be verified and this may add a little overhead. In this case, you can go further and disable the constraint:

21:03:04 SQL> alter table DIM3 add constraint DIM3_PK primary key(DIM3_ID) rely;
Table DIM3 altered.
 
Elapsed: 00:00:00.059
 
21:03:05 SQL> alter table FACT add constraint DIM3_FK foreign key(DIM3_ID) references DIM3 rely disable novalidate;
Table FACT altered.
 
Elapsed: 00:00:00.014

Note that I had to set the referenced constraint DIM3_PK to RELY here, even if it is enable and validate, or I would get: ORA-25158: Cannot specify RELY for foreign key if the associated primary key is NORELY.

My session still trusts RELY constraints for query rewrite:

21:03:07 SQL> show parameter query_rewrite
 
NAME TYPE VALUE
----------------------- ------ -------
query_rewrite_enabled string TRUE
query_rewrite_integrity string TRUSTED

Now, the join elimination occurs:

21:03:08 SQL> select count(*) from FACT join DIM3 using(DIM3_ID);
 
COUNT(*)
--------
10000000
 
21:03:09 SQL> select * from dbms_xplan.display_cursor();
 
PLAN_TABLE_OUTPUT
-----------------
SQL_ID 3bhs523zyudf0, child number 0
-------------------------------------
select count(*) from FACT join DIM3 using(DIM3_ID)
 
Plan hash value: 3735838348
 
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7505 (100)| |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | TABLE ACCESS FULL| FACT | 11M| 138M| 7505 (1)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
2 - filter("FACT"."DIM3_ID" IS NOT NULL)
 
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- rely constraint used for this statement

So, we can still benefit from the query optimization even with the RELY DISABLE NOVALIDATE.

But I would not recommend this. Be careful. Here are my foreign key constraints:

21:03:15 SQL> select table_name,constraint_type,constraint_name,status,validated,rely from all_constraints where owner='
DEMO' and table_name='FACT' order by 4 desc,5 desc,6 nulls last;
 
TABLE_NAME CONSTRAINT_TYPE CONSTRAINT_NAME STATUS VALIDATED RELY
---------- --------------- --------------- ------ --------- ----
FACT R DIM1_FK ENABLED VALIDATED
FACT R DIM2_FK ENABLED NOT VALIDATED RELY
FACT R DIM3_FK DISABLED NOT VALIDATED RELY

For DIM1_FK and DIM2_FK the constraints prevent us from inconsistencies:

21:03:17 SQL> insert into FACT(DIM1_ID)values(666);
 
Error starting at line : 1 in command -
insert into FACT(DIM1_ID)values(666)
Error report -
ORA-02291: integrity constraint (DEMO.DIM1_FK) violated - parent key not found

But the disabled one will allow inconsistencies:

21:03:19 SQL> insert into FACT(DIM3_ID)values(666);
1 row inserted.

That’s bad. I rollback this immediately:

21:03:20 SQL> rollback;
Rollback complete.

Star transformation

Join elimination is not the only transformation that needs to know about the many-to-one relationship between fact tables and dimensions. You usually create a bitmap index on each foreign key to the dimension, to get the higher selectivity when looking at the table rows from the combination of criteria on the dimension attributes.

21:03:24 SQL> create bitmap index FACT_DIM1 on FACT(DIM1_ID);
Index FACT_DIM1 created.
 
21:03:29 SQL> create bitmap index FACT_DIM2 on FACT(DIM2_ID);
Index FACT_DIM2 created.
 
21:03:33 SQL> create bitmap index FACT_DIM3 on FACT(DIM3_ID);
Index FACT_DIM3 created.

Here is the kind of query with predicates on each dimension attributes:

21:03:35 SQL> select count(*) from FACT
2 join DIM1 using(DIM1_ID) join DIM2 using(DIM2_ID) join DIM3 using(DIM3_ID)
3 where dim1_att1='...0' and dim2_att1='...0' and dim3_att1='...0';
 
COUNT(*)
--------
333333

By default, the optimizer applies those predicates on the dimension and do a cartesian join to get all accepted combinations of dimension IDs. Then the rows can be fetched from the table:

21:03:37 SQL> select * from dbms_xplan.display_cursor();
 
PLAN_TABLE_OUTPUT
-----------------
SQL_ID 01jmjv0sz1dpq, child number 0
-------------------------------------
select count(*) from FACT join DIM1 using(DIM1_ID) join DIM2
using(DIM2_ID) join DIM3 using(DIM3_ID) where dim1_att1='...0' and
dim2_att1='...0' and dim3_att1='...0'
 
Plan hash value: 1924236134
 
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5657 (100)| |
| 1 | SORT AGGREGATE | | 1 | 114 | | |
| 2 | NESTED LOOPS | | 55826 | 6215K| 5657 (1)| 00:00:01 |
| 3 | MERGE JOIN CARTESIAN | | 1 | 75 | 9 (0)| 00:00:01 |
| 4 | MERGE JOIN CARTESIAN | | 1 | 50 | 6 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | DIM1 | 1 | 25 | 3 (0)| 00:00:01 |
| 6 | BUFFER SORT | | 1 | 25 | 3 (0)| 00:00:01 |
|* 7 | TABLE ACCESS FULL | DIM2 | 1 | 25 | 3 (0)| 00:00:01 |
| 8 | BUFFER SORT | | 1 | 25 | 6 (0)| 00:00:01 |
|* 9 | TABLE ACCESS FULL | DIM3 | 1 | 25 | 3 (0)| 00:00:01 |
| 10 | BITMAP CONVERSION COUNT | | 55826 | 2126K| 5657 (1)| 00:00:01 |
| 11 | BITMAP AND | | | | | |
|* 12 | BITMAP INDEX SINGLE VALUE| FACT_DIM3 | | | | |
|* 13 | BITMAP INDEX SINGLE VALUE| FACT_DIM2 | | | | |
|* 14 | BITMAP INDEX SINGLE VALUE| FACT_DIM1 | | | | |
-------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
5 - filter("DIM1"."DIM1_ATT1"='...0')
7 - filter("DIM2"."DIM2_ATT1"='...0')
9 - filter("DIM3"."DIM3_ATT1"='...0')
12 - access("FACT"."DIM3_ID"="DIM3"."DIM3_ID")
13 - access("FACT"."DIM2_ID"="DIM2"."DIM2_ID")
14 - access("FACT"."DIM1_ID"="DIM1"."DIM1_ID")

Here rows are fetched from the fact table through a nested loop from the cartesian join on the dimensions, using the bitmap index access for each loop. If there are lot of rows to fetch, then the optimizer will chose a hash join and then will have to full scan the fact table, which is expensive. To lower that cost, the optimizer can add a ‘IN (SELECT DIM_ID FROM DIM WHERE DIM_ATT)’ for very selective dimensions. This is STAR transformation and relies on the foreign key constraints.

It is not enabled by default:

21:03:43 SQL> show parameter star
NAME TYPE VALUE
---------------------------- ------- -----
star_transformation_enabled string FALSE

We can enable it and then it is a cost based transformation:

21:03:45 SQL> alter session set star_transformation_enabled=true;
Session altered.

Here is my example:

21:03:47 SQL> select count(*) from FACT
2 join DIM1 using(DIM1_ID) join DIM2 using(DIM2_ID) join DIM3 using(DIM3_ID)
3 where dim1_att1='...0' and dim2_att1='...0' and dim3_att1='...0';
 
COUNT(*)
--------
333333

The star transformation, changing a join to an ‘IN()’ is possible only when we know that there is a many-to-one relationship. We have all constraints for that, disabled or not, validated or not, but all in RELY. Then Star Transformation can occur:

21:03:51 SQL> select * from dbms_xplan.display_cursor();
 
PLAN_TABLE_OUTPUT
-----------------
SQL_ID 01jmjv0sz1dpq, child number 1
-------------------------------------
select count(*) from FACT join DIM1 using(DIM1_ID) join DIM2
using(DIM2_ID) join DIM3 using(DIM3_ID) where dim1_att1='...0' and
dim2_att1='...0' and dim3_att1='...0'
 
Plan hash value: 1831539117
 
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 68 (100)| |
| 1 | SORT AGGREGATE | | 1 | 38 | | |
|* 2 | HASH JOIN | | 2 | 76 | 68 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | DIM2 | 1 | 25 | 3 (0)| 00:00:01 |
| 4 | VIEW | VW_ST_62BA0C91 | 8 | 104 | 65 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 8 | 608 | 56 (0)| 00:00:01 |
| 6 | BITMAP CONVERSION TO ROWIDS| | 8 | 427 | 22 (5)| 00:00:01 |
| 7 | BITMAP AND | | | | | |
| 8 | BITMAP MERGE | | | | | |
| 9 | BITMAP KEY ITERATION | | | | | |
|* 10 | TABLE ACCESS FULL | DIM1 | 1 | 25 | 3 (0)| 00:00:01 |
|* 11 | BITMAP INDEX RANGE SCAN| FACT_DIM1 | | | | |
| 12 | BITMAP MERGE | | | | | |
| 13 | BITMAP KEY ITERATION | | | | | |
|* 14 | TABLE ACCESS FULL | DIM2 | 1 | 25 | 3 (0)| 00:00:01 |
|* 15 | BITMAP INDEX RANGE SCAN| FACT_DIM2 | | | | |
| 16 | BITMAP MERGE | | | | | |
| 17 | BITMAP KEY ITERATION | | | | | |
|* 18 | TABLE ACCESS FULL | DIM3 | 1 | 25 | 3 (0)| 00:00:01 |
|* 19 | BITMAP INDEX RANGE SCAN| FACT_DIM3 | | | | |
| 20 | TABLE ACCESS BY USER ROWID | FACT | 1 | 25 | 43 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
2 - access("ITEM_1"="DIM2"."DIM2_ID")
3 - filter("DIM2"."DIM2_ATT1"='...0')
10 - filter("DIM1"."DIM1_ATT1"='...0')
11 - access("FACT"."DIM1_ID"="DIM1"."DIM1_ID")
14 - filter("DIM2"."DIM2_ATT1"='...0')
15 - access("FACT"."DIM2_ID"="DIM2"."DIM2_ID")
18 - filter("DIM3"."DIM3_ATT1"='...0')
19 - access("FACT"."DIM3_ID"="DIM3"."DIM3_ID")
 
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- star transformation used for this statement
- this is an adaptive plan

Here, each dimension drives a range scan on the bitmap index: the predicate on the dimension table returns the dimension ID for the the index lookup on the fact table. The big advantage of bitmap indexes here is that when this access path is used for several dimensions, the bitmap result can be combined before going to the table. This transformation avoids the join and then you must be sure that there is a many-to-one relationship.

In summary

As you should rely on the integrity of data in your datawarehouse, you should find the following parameters to query on fact-dimension schemas:

NAME TYPE VALUE
---------------------------- ------- ------
query_rewrite_enabled string TRUE
query_rewrite_integrity string TRUSTED
star_transformation_enabled string FALSE

And you should define all constraints. When you are sure about the integrity of data, then those constraints can be created RELY ENABLE NOVALIDATE. If some processing must be optimized by not enforcing the constraint verification, then you may create those constraints as RELY DISABLE NOVALIDATE but the gain will probably minimal, and the risk high. But remember that there are not only the well-controlled processes which update data. You may have one day to do a manual update to fix something, and enabled constraint can prevent terrible errors.

I have not covered all optimizer transformations that rely on constraints. When using materialized views you, the rewrite capability also relies on constraints. Relationship cardinality is one of the most important information of database design, this information must be known by the optimizer.

Added 9-OCT-2017

Re-reading this, I realise that I forgot to mention one important thing about disabled constraints. I recommend having the constraints enabled in case there is an update. But when you bulk insert (insert /*+ append */) you will disable it or the insert will not be done in direct-path. So the idea is to disable it before the load and enabled it RELY NOVALIDATE after the load.

 

Cet article Create constraints in your datawarehouse – why and how est apparu en premier sur Blog dbi services.

The full table scan direct path read decision for version 12.2

This post is about the decision the Oracle database engine makes when it is using a full segment scan approach. The choices the engine has is to store the blocks that are physically read in the buffercache, or read the blocks into the process’ PGA. The first choice is what I refer to as a ‘buffered read’, which places the block in the database buffercache so the process itself and other processes can bypass the physical read and use the block from the cache, until the block is evicted from the cache. The second choice is what is commonly referred to as ‘direct path read’, which places the blocks physically read into the process’ PGA, which means the read blocks are stored for only a short duration and is not shared with other processes.

There are some inherent performance aspects different between a buffered and a direct path read. A buffered read can only execute a single physical read request for a single range of blocks, wait for that request to finish, fetch and process the result of the physical read request after which it can execute the next physical read request. So there is maximum of one outstanding IO for multiple (adjacent) Oracle blocks. A direct path read works differently, it submits two physical IO requests, each for a distinct range of Oracle blocks asynchronously, after which it waits one or more IOs to finish. If an IO is returned, it is processed, and an IO for another range of Oracle blocks is submitted to restore the number of IOs in flight to two. If the database engine determines (based upon a non-disclosed mechanism) that enough resources are available it can increase the amount of IO physical IO requests in flight up to 32. Other differences include a maximum for the total size of the IO request, which is 1MB for buffered requests, and 32MB for direct path requests (which is achieved by setting db_file_multiblock_read_count to 4096).

At this point should be clear that there are differences between buffered and direct path reads, and when full segment scans switch from direct path reads to buffered reads it could mean a significant performance difference. On top of this, if your database is using Exadata storage, this decision between buffered reads and direct path reads is even more important. Only once the decision for direct path reads has been made, an Exadata smartscan can be executed. I have actually witnessed cases where a mix of partitioning and HCC lead to the situation that the partitions were so small that a direct path read was not chosen, which meant a smartscan was not considered anymore, meaning that instead of the cells decompressing the compressed blocks all in parallel, the process now had to fetch them and do the decompression on the database layer.

There have been some posts on the circumstances of the decision. However, I have seen none that summarise the differences for the different versions. In order to investigate the differences between the different Oracle versions, I created a git repository at gitlab: https://gitlab.com/FritsHoogland/table_scan_decision. You can easily use the repository by cloning it: git clone https://gitlab.com/FritsHoogland/table_scan_decision.git, which will create a table_scan_decision directory in the current working directory.

Oracle version 11.2.0.2.12
Please mind this version is very old, and SHOULD NOT BE USED ANYMORE because it’s not an actively supported version. However, I do use this version, because this version has different behaviour than the versions that follow.

First determine the small table threshold of the database:

SYS@test AS SYSDBA> @small_table_threshold

KSPPINM 		       KSPPSTVL
------------------------------ ------------------------------
_small_table_threshold	       1531

Let’s create tables just below and just over 1531 blocks/small table threshold:

TS@test > @create_table table_1350 1350
...
    BLOCKS
----------
      1408
TS@test > @create_table table_1531 1531
...
    BLOCKS
----------
      1664

So the small table threshold is 1531, this means that an internal statistic that is used for determining using the direct path mechanism, medium table threshold will be approximately 1531*5=7655. Let’s create tables just below and just over that number of blocks:

TS@test > @create_table table_7000 7000
...
    BLOCKS
----------
      7168
TS@test > @create_table table_7655 7655
...
    BLOCKS
----------
      7808

For the other versions, trace event ‘nsmtio’ can be used to learn how the decision is made. However, this trace event does not exist in Oracle version 11.2.0.2. The workaround is to just execute a SQL trace and interpret the wait events. For a full table scan, the wait events ‘db file scattered read’ means a buffered read is done, and wait events ‘direct path read’ means a direct path read was done (obviously).

TS@test > alter session set events 'sql_trace level 8';
TS@test > select count(*) from table_1350;
-- main event: db file scattered read
TS@test > alter session set tracefile_identifier = 'table_1531';
TS@test > select count(*) from table_1531;
-- main event: db file scattered read
TS@test > alter session set tracefile_identifier = 'table_7000';
TS@test > select count(*) from table_7000;
-- main event: db file scattered read
TS@test > alter session set tracefile_identifier = 'table_7655';
TS@test > select count(*) from table_7655;
-- main event: direct path read

This shows that in my case, with Oracle version 11.2.0.2, the switching point is at 5 times _small_table_threshold.

Oracle 11.2.0.3.15
This version too should NOT BE USED ANYMORE because it is not in active support. This too is for reference.
Small table threshold for this database:

SYS@test AS SYSDBA> @small_table_threshold

KSPPINM 		       KSPPSTVL
------------------------------ ------------------------------
_small_table_threshold	       1531

With the small table threshold being 1531, the medium table threshold should be approximately 1531*5=7655.

TS@test > @create_table table_1350 1350
...
    BLOCKS
----------
      1408
TS@test > @create_table table_1440 1440
...
    BLOCKS
----------
      1536
TS@test > @create_table table_7000 7000
...
    BLOCKS
----------
      7168
TS@test > @create_table table_7655 7655
...
    BLOCKS
----------
      7808

Flush buffer cache and set trace events, and test the scans. By doing that I ran into something peculiar with the ‘nsmtio’ event in this version (11.2.0.3 with the latest PSU). This event does exist for this version (which you can validate by running ‘oradebug doc component’), however, it does not yield any output. This means I have to revert to the previous method of running sql_trace at level 8 and interpret the wait events.

TS@test > alter session set events 'trace[nsmtio]:sql_trace level 8'; -- no NSMTIO lines, only sql_trace!
TS@test > select count(*) from table_1350;
-- main event: db file scattered read
TS@test > alter session set tracefile_identifier = 'table_1440';
TS@test > select count(*) from table_1440;
-- main event: direct path read
TS@test > alter session set tracefile_identifier = 'table_7000';
TS@test > select count(*) from table_7000;
-- main event: direct path read
TS@test > alter session set tracefile_identifier = 'table_7655';
TS@test > select count(*) from table_7655;
-- main event: direct path read

This shows that with Oracle version 11.2.0.3, the direct path read switching point seems to have moved from 5 times small table threshold to small table threshold itself.

Oracle 11.2.0.4.170718
This version is in active support!
Small table threshold for this database:

SQL> @small_table_threshold

KSPPINM 		       KSPPSTVL
------------------------------ ------------------------------
_small_table_threshold	       1538

With the small table threshold being 1538, the medium table threshold should be approximately 1538*5=7690.

SQL> @create_table table_1350 1350
...
    BLOCKS
----------
      1408
SQL> @create_table table_1538 1538
...
    BLOCKS
----------
      1664
SQL> @create_table table_7000 7000
...
    BLOCKS
----------
      7168
SQL> @create_table table_7690 7690
...
    BLOCKS
----------
      7808

Flush buffer cache and set trace events, and test the scans.

SQL> alter session set events 'trace[nsmtio]:sql_trace level 8';
SQL> select count(*) from table_1350;
-- nsmtio lines:
NSMTIO: qertbFetch:NoDirectRead:[- STT < OBJECT_SIZE < MTT]:Obect's size: 1378 (blocks), Threshold: MTT(7693 blocks),
-- main event: db file scattered read
SQL> alter session set tracefile_identifier = 'table_1538';
SQL> select count(*) from table_1538;
-- nsmtio lines:
NSMTIO: qertbFetch:[MTT < OBJECT_SIZE < VLOT]: Checking cost to read from caches(local/remote) and checking storage reduction factors (OLTP/EHCC Comp)
NSMTIO: kcbdpc:DirectRead: tsn: 4, objd: 14422, objn: 14422
-- main event: direct path read
SQL> alter session set tracefile_identifier = 'table_7000';
SQL> select count(*) from table_7000;
-- nsmtio lines:
NSMTIO: qertbFetch:[MTT < OBJECT_SIZE < VLOT]: Checking cost to read from caches(local/remote) and checking storage reduction factors (OLTP/EHCC Comp)
NSMTIO: kcbdpc:DirectRead: tsn: 4, objd: 14423, objn: 14423
-- main event: direct path read
SQL> alter session set tracefile_identifier = 'table_7690';
SQL> select count(*) from table_7690;
-- nsmtio lines:
NSMTIO: qertbFetch:[MTT < OBJECT_SIZE < VLOT]: Checking cost to read from caches(local/remote) and checking storage reduction factors (OLTP/EHCC Comp)
NSMTIO: kcbdpc:DirectRead: tsn: 4, objd: 14424, objn: 14424
-- main event: direct path read

This shows that with Oracle version 11.2.0.4, the direct path read switching is at small table threshold, which was changed starting from 11.2.0.3.

Oracle version 12.1.0.2.170718
Small table threshold for this database:

SQL> @small_table_threshold

KSPPINM 		       KSPPSTVL
------------------------------ ------------------------------
_small_table_threshold	       1440

SQL>

With small table threshold being 1440, the medium table threshold is approximately 1440*5=7200.

SQL> @create_table table_1350 1350
...
    BLOCKS
----------
      1408
SQL> @create_table table_1440 1440
...
    BLOCKS
----------
      1536
SQL> @create_table table_7000 7000
...
    BLOCKS
----------
      7168
SQL> @create_table table_7200 7200
...
    BLOCKS
----------
      7424

Now flush the buffer cache, and use the ‘nsmtio’ trace event together with ‘sql_trace’ to validate the read method used:

SQL> alter session set events 'trace[nsmtio]:sql_trace level 8';
SQL> select count(*) from table_1350;
-- nsmtio lines:
NSMTIO: qertbFetch:NoDirectRead:[- STT < OBJECT_SIZE < MTT]:Obect's size: 1378 (blocks), Threshold: MTT(7203 blocks),
-- main events: db file scattered read
SQL> alter session set tracefile_identifier = 'table_1440';
SQL> select count(*) from table_1440;
-- nsmtio lines:
NSMTIO: qertbFetch:[MTT < OBJECT_SIZE < VLOT]: Checking cost to read from caches(local/remote) and checking storage reduction factors (OLTP/EHCC Comp)
NSMTIO: kcbdpc:DirectRead: tsn: 4, objd: 20489, objn: 20489
-- main events: direct path read
SQL> alter session set tracefile_identifier = 'table_7000';
SQL> select count(*) from table_7000;
-- nsmtio lines:
NSMTIO: qertbFetch:[MTT < OBJECT_SIZE < VLOT]: Checking cost to read from caches(local/remote) and checking storage reduction factors (OLTP/EHCC Comp)
NSMTIO: kcbdpc:DirectRead: tsn: 4, objd: 20490, objn: 20490
-- main events: direct path read
SQL> alter session set tracefile_identifier = 'table_7200';
SQL> select count(*) from table_7200;
NSMTIO: qertbFetch:[MTT < OBJECT_SIZE < VLOT]: Checking cost to read from caches(local/remote) and checking storage reduction factors (OLTP/EHCC Comp)
NSMTIO: kcbdpc:DirectRead: tsn: 4, objd: 20491, objn: 20491
-- main events: direct path read

This is in line with the switch in version 11.2.0.3 to small table threshold as the switching point between buffered reads and direct path reads.

Oracle 12.2.0.1.170814
Small table threshold for this database:

SQL> @small_table_threshold

KSPPINM 		       KSPPSTVL
------------------------------ ------------------------------
_small_table_threshold	       1444

SQL>

With small table threshold being 1444, the medium table threshold is approximately 1444*5=7220.

SQL> @create_table table_1350 1350
...
    BLOCKS
----------
      1408
SQL> @create_table table_1440 1440
...
    BLOCKS
----------
      1536
SQL> @create_table table_7000 7000
...
    BLOCKS
----------
      7168
SQL> @create_table table_7200 7200
...
    BLOCKS
----------
      7424

Now flush the buffer cache, and use the ‘nsmtio’ trace event together with ‘sql_trace’ to validate the read method used:

SQL> alter session set events 'trace[nsmtio]:sql_trace level 8';
SQL> select count(*) from table_1350;
-- nsmtio lines:
NSMTIO: qertbFetch:NoDirectRead:[- STT < OBJECT_SIZE < MTT]:Obect's size: 1378 (blocks), Threshold: MTT(7222 blocks),
-- main events: db file scattered read
SQL> alter session set tracefile_identifier = 'table_1440';
SQL> select count(*) from table_1440;
-- nsmtio lines:
NSMTIO: qertbFetch:NoDirectRead:[- STT < OBJECT_SIZE < MTT]:Obect's size: 1504 (blocks), Threshold: MTT(7222 blocks),
-- main events: db file scattered read
SQL> alter session set tracefile_identifier = 'table_7000';
SQL> select count(*) from table_7000;
-- nsmtio lines:
NSMTIO: qertbFetch:NoDirectRead:[- STT < OBJECT_SIZE < MTT]:Obect's size: 7048 (blocks), Threshold: MTT(7222 blocks),
-- main events: db file scattered read
SQL> alter session set tracefile_identifier = 'table_7200';
SQL> select count(*) from table_7200;
NSMTIO: qertbFetch:[MTT < OBJECT_SIZE < VLOT]: Checking cost to read from caches(local/remote) and checking storage reduction factors (OLTP/EHCC Comp)
NSMTIO: kcbdpc:DirectRead: tsn: 4, objd: 22502, objn: 22502
-- main events: direct path read

Hey! With 12.2.0.1 the direct path read switching point reverted back to pre-11.2.0.3 behaviour of switching on 5 times small table threshold instead of small table threshold itself.

Update!
Re-running my tests shows differences in the outcome between buffered and direct path reads. My current diagnosis is that the scan type determination uses a step based approach:

– The first determination of size is done with ‘NSMTIO: kcbism’ (kcb is medium). If islarge is set to 1, it means the segment is bigger than STT. If islarge is set to 0 it means the segment is smaller than STT, and the segment will be read buffered, and the line ‘qertbFetch:NoDirectRead:[- STT < OBJECT_SIZE < MTT]' is shown in the NSMTIO output.

– The next line is 'NSMTIO: kcbimd' (kcb is medium determination?) It shows the size of the segment (nblks), STT (kcbstt), MTT (kcbpnb) and is_large, which in my tests always is set to 0. Here, there are 4 options that I could find:

1) Segment size between STT and MTT and a buffered read is executed.
If the segment is between STT and MTT, the Oracle engine uses a non-disclosed costing mechanism, which probably is externalised in the line 'NSMTIO: kcbcmt1'. The outcome can be a buffered read, for which the line 'qertbFetch:NoDirectRead:[- STT < OBJECT_SIZE < MTT]' is shown.

2) Segment size between STT and MTT and the direct path code path is chosen.
If the segment is between STT and MTT, the Oracle engine uses a non-disclosed costing mechanism, probably externalised in the line 'NSMTIO: kcbcmt1'. If the costing determines it would be beneficial to use a direct path mechanism, it seems it switches to the direct path with cache determination code, which is also used for any table scan that is smaller than VLOT. Because of switching to that code, it will determine if the segment is bigger than VLOT: 'NSMTIO: kcbivlo', which of course in this case isn't true. Then, it will show the line 'NSMTIO: qertbFetch:[MTT < OBJECT_SIZE < VLOT]'

3) Segment size bigger than MTT but smaller than VLOT.
If the segment is between MTT and VLOT, the Oracle engine does not apply the costing mechanism (which is means the kcbcmt1 line is not shown). It will determine if the segment is bigger than VLOT ('NSMTIO: kcbivlo'), and then show 'NSMTIO: qertbFetch:[MTT VLOT]’, and there is no kcbdpc to analyse choosing doing a buffered or direct path read.

4) Segment size bigger than VLOT.
If the segment is bigger than VLOT, the Oracle engine execute the functions kcbimd and kcbivlo, the NSMTIO line for kcbivlo will show is_large 1 to indicate it’s a very large object (VLOT by default is ‘500’, which is 5 times the total number of buffers in the buffer cache. The qertbFetch line will say ‘NSMTIO: qertbFetch:DirectRead:[OBJECT_SIZE>VLOT]’, and there is no kcbdpc to analyse choosing doing a buffered or direct path read.

In the cases where ‘NSMTIO: qertbFetch:[MTT < OBJECT_SIZE < VLOT]' is shown, which is either a segment between STT and MTT which switched to this code path, or between MTT and VLOT, the code will apply a second determination and potential switching point from buffered to direct path or vice versa, which is shown with the line 'kcbdpc' (kcb direct path check). The outcome can be:

– NSMTIO: kcbdpc:NoDirectRead:[CACHE_READ] to indicate it will use a buffered read.
– NSMTIO: kcbdpc:DirectRead to indicate it will use a direct path read.

I have verified the above 'decision tree' in 11.2.0.2, 11.2.0.3, 11.2.0.4, 12.1.0.2 and 12.2.0.1. It all seems to work this way consistently. I derived this working by looking at the NSMTIO tracing of 12.2, and then gone back in version. You will see that going lower in versions, there is lesser (nsmtio) tracing output; 11.2.0.4 does show way lesser information, for example, it does not show the kcbcmt1 line, and of course 11.2.0.3 and 11.2.0.2 do not show NSMTIO lines altogether. In order to verify the working, I used gdb and quite simply breaked on the kcbism, kcbimd, kcbcmt1, kcbivlo and kcbdpc functions in the versions where this information was missing in the trace.

Still, at the kcbcmt1 point:
– 11.2.0.2 seems to quite consistently take MTT as the direct path switching point.
– 11.2.0.3-12.1.0.2 seem to quite consistently take STT as the direct path switching point.
– 12.2.0.1 varies.

Conclusion.
This article first explained the differences between buffered and direct path reads, and why this is important, and that it is even more important with Exadata for smartscans.

The next part shows how to measure the switching point. The most important message from this blog article is that starting from 11.2.0.3 up to 12.1.0.2 the direct path read switching point is small table threshold, and with Oracle database version 12.2.0.1, the direct path switching point is changed back to pre-11.2.0.3 behaviour which means 5 times the small table threshold of the instance.
The next part shows measurements of the switching point. The addition shows that between STT and MTT there is a cost based decision to go direct path or buffered path. Once the direct path is chosen, it still can go buffered if the majority of the blocks are in the cache.

If you look closely at the output of the nsmtio lines for version 11.2.0.3-12.1.0.1 for tables that had a size between small table threshold and medium table threshold, it seemed a bit weird, because the nsmtio trace said ‘[MTT < OBJECT_SIZE < VLOT]', which to me means that Oracle detected the object size to be between medium table threshold and very large object threshold, which was not true. I can't tell, but it might be a bug that is solved for measuring the wrong size.
The text description in the NSMTIO qertbFetch line is bogus, it simply is a code path; ‘[- STT < OBJECT_SIZE < MTT]' means it's a buffered read, and could be chosen when < STT or in between STT and MTT, '[MTT < OBJECT_SIZE < VLOT]' means it's a direct path read, and could be chosen when in between STT and MTT or MTT and VLOT.

I added the scripts and examples of the tracing events so you can measure this yourself in your environment.

Tagged: debug, internals, IO, nsmtio, oracle, performance, trace events, wait event

12.2 New Feature: the FLEX ASM disk group part 5

Some time ago I had a very interesting twitter conversation after publishing the first part of this series. The question was whether using ASM templates, which admittedly exist since Oracle 10.1, didn’t provide similar functionality as Flex Disk Groups. In other words, wouldn’t using ASM templates allow you to have high redundancy files on normal redundancy disk groups anyway?

This question has been answered by Alex Fatkulin in a blog post some time ago. In this post I would like to replay his test with my 12.2 setup. Initially I had hoped to compare the approach using ASM templates with the Flex ASM Disk Group but the post has become too long again … The actual comparison will be done with the next instalment of the series.

Templates

You may not be aware of the fact that you are using ASM templates, but you do. Each disk group has a set of system-generated, common ASM templates. Consider this example for my current lab environment (Oracle 12.2.0.1). Queries and commands are executed as SYSASM while connected to the ASM instance unless stated otherwise:

SQL> select b.name, a.redundancy, a.stripe, a.system
  2   from v$asm_template a, v$asm_diskgroup b
  3  where a.group_number = b.group_number
  4    and a.name = 'DATAFILE';

NAME                           REDUND STRIPE S
------------------------------ ------ ------ -
DATA                           MIRROR COARSE Y
MGMT                           UNPROT COARSE Y
OCR                            MIRROR COARSE Y
RECO                           UNPROT COARSE Y
FLEX                           MIRROR COARSE Y

Templates, among other disk-group meta-information, define how a supported file type is stored in ASM using 2 criteria:

  • Mirroring
  • Striping

You can see there are plenty of templates, one for each supported ASM file type:

SQL> select b.name as dg_name, a.name as template_name, a.system
  2    from v$asm_template a, v$asm_diskgroup b
  3  where a.group_number = b.group_number
  4   and b.name = 'DATA';

DG_NAME 		       TEMPLATE_NAME		      S
------------------------------ ------------------------------ -
DATA			       PARAMETERFILE		      Y
DATA			       ASMPARAMETERFILE 	      Y
DATA			       OCRFILE			      Y
DATA			       DATAGUARDCONFIG		      Y
DATA			       AUDIT_SPILLFILES 	      Y
DATA			       AUTOLOGIN_KEY_STORE	      Y
DATA			       KEY_STORE		      Y
DATA			       FLASHBACK		      Y
DATA			       CHANGETRACKING		      Y
DATA			       XTRANSPORT		      Y
DATA			       AUTOBACKUP		      Y
DATA			       INCR XTRANSPORT BACKUPSET      Y
DATA			       XTRANSPORT BACKUPSET	      Y
DATA			       BACKUPSET		      Y
DATA			       TEMPFILE 		      Y
DATA			       DATAFILE 		      Y
DATA			       ONLINELOG		      Y
DATA			       ARCHIVELOG		      Y
DATA			       FLASHFILE		      Y
DATA			       CONTROLFILE		      Y
DATA			       DUMPSET			      Y
DATA			       VOTINGFILE		      Y

22 rows selected.

Does that strike any resemblance with v$asm_filegroup_property? It does so for me. Except that within a Flex ASM Disk Group properties are defined per File Group. And there are different file groups per (N)CDB, or PDB. With other ASM disk group types the mapping is global.

Custom Templates

According to the ASM documentation (Storage Administrator’s Guide 12c Release 2 chapter 5 Administering Oracle ASM Files, Directories, and Templates) a template can be used to define attributes for file types.

If there’s a column named SYSTEM in v$asm_template, there surely is a way to create one’s own templates. And this is where I circle back to the original question: can I have high-redundancy files in a normal redundancy disk group?

You sure can! I will use the DATA disk group for this, which is created using NORMAL redundancy. Here is some useful background information:

SQL> select group_number, name, type, compatibility, database_compatibility
  2  from v$asm_diskgroup;

GROUP_NUMBER NAME       TYPE   COMPATIBILITY   DATABASE_COMPAT
------------ ---------- ------ --------------- ---------------
           1 DATA       NORMAL 12.2.0.1.0      12.2.0.1.0
           2 MGMT       EXTERN 12.2.0.1.0      10.1.0.0.0
           3 OCR        NORMAL 12.2.0.1.0      10.1.0.0.0
           4 RECO       EXTERN 12.2.0.1.0      10.1.0.0.0
           5 FLEX       FLEX   12.2.0.1.0      12.2.0.1.0

SQL> select count(*) from v$asm_disk where group_number = 
  2   (select group_number from v$asm_diskgroup where name = 'DATA');

  COUNT(*)
----------
         3

Each new datafile on the DATA disk group will be created based on the default template:

SQL> select b.name as dg_name, a.redundancy, a.stripe, a.system
  2  from v$asm_template a, v$asm_diskgroup b
  3  where a.group_number = b.group_number
  4   and a.name = 'DATAFILE'
  5   and b.name = 'DATA';

DG_NAME                        REDUND STRIPE S
------------------------------ ------ ------ -
DATA                           MIRROR COARSE Y

In other words, each extent is mirrored, and the striping is coarse. Again, I won’t be touching the striping mechanism as explained in an earlier post.

To enable high redundancy another template must be created, which is simple:

SQL> alter diskgroup data add template high_red_on_normal_dg attribute (high);

Diskgroup altered.

SQL> select b.name as dg_name, a.redundancy, a.stripe, a.system
  2  from v$asm_template a, v$asm_diskgroup b
  3  where a.group_number = b.group_number
  4   and a.name = 'HIGH_RED_ON_NORMAL_DG'
  5   and b.name = 'DATA'
  6  /

DG_NAME                        REDUND STRIPE S
------------------------------ ------ ------ -
DATA                           HIGH   COARSE N

Back in the RDBMS instance, I can now make use of that template:

SQL> create tablespace high_red_tbs 
  2  datafile '+data(high_red_on_normal_DG)' size 50m;

Tablespace created.

SQL> select name from v$datafile where name like '%high_red_tbs%';

NAME
----------------------------------------------------------------------------------
+DATA/CDB/586EF9CC43B5474DE0530A64A8C0F287/DATAFILE/high_red_tbs.286.953971189

The question is: is this file created with high redundancy?

SQL> select redundancy, type, remirror, redundancy_lowered
  2  from v$asm_file where file_number = 286 and incarnation = 953971189;

REDUND TYPE            R R
------ --------------- - -
HIGH   DATAFILE        N U

That looks like a yes to me. Using a different, random other file from the disk group shows that other files use normal redundancy:

SQL> select name from v$asm_alias 
  2   where file_number = 261 and file_incarnation = 953928133;

NAME
------------------------------------------------------------
UNDO_2.261.953928133

SQL> select redundancy, type, remirror, redundancy_lowered
  2  from v$asm_file where file_number = 261 and incarnation = 953928133;

REDUND TYPE            R R
------ --------------- - -
MIRROR DATAFILE        N U

But does it help?

Now I have high redundancy files on a normal redundancy disk group, which gives me extra protection from disk corruption. From an availability point of view you don’t win much though, as Alex has already pointed out. Removing 2 of the 3 disks that make up the DATA disk group should result in a dismount of the disk group (which a true high redundancy disk would survive). Here is proof.

The disk failures are visible in many places. For example, in /var/log/messages

Sep  6 10:42:37 rac122pri1 kernel: sd 3:0:0:0: [sdg] Synchronizing SCSI cache
Sep  6 10:42:37 rac122pri1 kernel: sd 3:0:0:0: [sdg] Synchronize Cache(10)
 failed: Result: hostbyte=DID_OK driverbyte=DRIVER_SENSE
Sep  6 10:42:37 rac122pri1 kernel: sd 3:0:0:0: [sdg] Sense Key : Illegal  
 Request [current] 
Sep  6 10:42:37 rac122pri1 kernel: sd 3:0:0:0: [sdg] Add. Sense: 
 Logical unit not supported
Sep  6 10:42:41 rac122pri1 kernel: sd 4:0:0:2: [sdl] Synchronizing SCSI cache
Sep  6 10:42:41 rac122pri1 kernel: sd 4:0:0:2: [sdl] Synchronize Cache(10) 
 failed: Result: hostbyte=DID_OK driverbyte=DRIVER_SENSE
Sep  6 10:42:41 rac122pri1 kernel: sd 4:0:0:2: [sdl] Sense Key : Illegal 
 Request [current] 
Sep  6 10:42:41 rac122pri1 kernel: sd 4:0:0:2: [sdl] Add. Sense: Logical 
 unit not supported

And the ASM instance’s alert.log:

...
ERROR: no read quorum in group: required 1936606968, found 1937207795 disks
ERROR: Could not read PST for grp 1. Force dismounting the disk group.
NOTE: detected orphaned client id 0x10001.
2017-09-06 10:42:45.101000 +01:00
Errors in file /u01/app/oracle/diag/asm/+asm/+ASM1/trace/+ASM1_rbal_2730.trc:
ORA-15130: diskgroup "" is being dismounted
GMON dismounting group 1 at 96 for pid 37, osid 32385
NOTE: Disk DATA_0000 in mode 0x1 marked for de-assignment
NOTE: Disk DATA_0001 in mode 0x7f marked for de-assignment
NOTE: Disk DATA_0002 in mode 0x7f marked for de-assignment
SUCCESS: diskgroup DATA was dismounted
SUCCESS: alter diskgroup DATA dismount force /* ASM SERVER:2133858021 */
SUCCESS: ASM-initiated MANDATORY DISMOUNT of group DATA
NOTE: diskgroup resource ora.DATA.dg is offline

It is truly gone:

SQL> select group_number, name, state from v$asm_diskgroup where name = 'DATA';

GROUP_NUMBER NAME                           STATE
------------ ------------------------------ -----------
           0 DATA                           DISMOUNTED

Summary: ASM Templates

When I initially worked out how to use custom templates and creating high redundancy files in a normal redundancy disk group I was all excited. However during testing disk failure that excitement made way to a more rational assessment of the situation.

So while you might gain on data integrity you lose on storage (triple mirroring requires more space) and don’t have added benefit on availability.

In the next post I’ll repeat this test with my FLEX ASM Disk Group.

Please keep your foreign keys

I came across an interesting blog post the other day about whether databases should be (declaratively) enforcing the foreign key relationships between tables.  The blog post discussed the issue of foreign keys being temporarily disabled to perform data loading, and then encountering the problem of what to do when those foreign keys cannot be re-enabled due to bad data.  Perhaps they should just be omitted altogether ?  I don’t want to put words in the author’s mouth, because he stressed he was not taking sides in the “should we” or “shouldn’t we” debate on declarative foreign keys, but the concluding part of the blog was:

image

I find that is a bit like saying:

“Well, seat belts are compulsory but people are still getting injured in car accidents, so you may as well just not bother with seat belts at all”

So here’s some information from the other perspective – why constraints are so important for your database in terms of both data correctness and performance.

Firstly here is an AskTOM magazine article that demonstrates the benefits of declarative constraints to both the optimizer, and to the availability of technologies such as materialized view rewrite.

Also, to respond to the particular topic in the blog post about handling data loads, here is a video from the Real World Performance group about how you can validate massive sets of data for referential integrity, and then still apply the constraints efficiently to give the optimizer all those benefits for subsequent query processing.

But if all of that seems to complicated to read and digest, perhaps an easier way of understanding the importance of constraints is to watch my simple video about Australian wildlife Smile

impdp content=metadata_only locks the stats

With Oracle you can learn something every day. Today, preparing a migration to 12.2, I found all tables had locked statistics. I learned that it is the expected behavior since 10.2 when importing metadata_only including statistics, to avoid that the automatic job gathering comes and replaces the stats by ‘0 rows’.

It is documented in DataPump Import Without Data Locks Table Statistics (Doc ID 415081.1) but as I was really surprised about that (and also frustrated to learn a 10.2 thing when migrating to 12.2) that I wanted to test myself.

I create a table DEMO with statistics:

SQL> connect sys/oracle@//localhost/pdb1 as sysdba
Connected.
SQL> create table demo.demo as select * from dual;
Table created.
 
SQL> create index demo.demo on demo.demo(dummy);
Index created.
 
SQL> exec dbms_stats.gather_table_stats('DEMO','DEMO');
PL/SQL procedure successfully completed.
 
SQL> create or replace directory TMP as '/tmp';
Directory created.
 
SQL> select count(*) from DEMO.DEMO;
 
COUNT(*)
----------
1
 
SQL> select object_type from dba_objects where owner='DEMO' and object_name='DEMO';
 
OBJECT_TYPE
-----------------------
TABLE
INDEX
 
SQL> select owner,table_name,last_analyzed,stattype_locked,num_rows from dba_tab_statistics where owner='DEMO' and table_name='DEMO';
 
OWNER TABLE_NAME LAST_ANALYZED STATTYPE_LOCKED NUM_ROWS
---------- -------------------- -------------------- -------------------- ----------
DEMO DEMO 06-SEP-17 1

I export it:

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SYS"."SYS_EXPORT_TABLE_01": "sys/********@//localhost/pdb1 AS SYSDBA" tables=DEMO.DEMO directory=TMP
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
. . exported "DEMO"."DEMO" 5.054 KB 1 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/tmp/expdat.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Wed Sep 6 19:14:44 2017 elapsed 0 00:00:09

And drop it:

SQL> connect sys/oracle@//localhost/pdb1 as sysdba
Connected.
SQL> drop table demo.demo;
Table dropped.

Now import metadata only (for example because I want to change NLS semantics before importing the data)

Import: Release 12.2.0.1.0 - Production on Wed Sep 6 19:21:28 2017
 
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
 
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01": "sys/********@//localhost/pdb1 AS SYSDBA" tables=DEMO.DEMO directory=TMP content=metadata_only exclude=index
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Wed Sep 6 19:21:39 2017 elapsed 0 00:00:11

If I check the statistics:

SQL> select owner,table_name,last_analyzed,stattype_locked,num_rows from dba_tab_statistics where owner='DEMO' and table_name='DEMO';
 
OWNER TABLE_NAME LAST_ANALYZED STATTYPE_LOCKED NUM_ROWS
---------- -------------------- -------------------- -------------------- ----------
DEMO DEMO 06-SEP-17 ALL 1

Stats are locked. I suppose that the idea is that you have the tables with same statistics as production for example, and you can load them with a subset of data but expect the same execution plans as in production. But this is not what I want for a migration.

One possibility is to unlock the stats once you have imported the data.

The other possibility is to import metadata without the statistics:

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01": "sys/********@//localhost/pdb1 AS SYSDBA" tables=DEMO.DEMO directory=TMP content=metadata_only exclude=index exclude=table_statistics
Processing object type TABLE_EXPORT/TABLE/TABLE
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Wed Sep 6 21:11:03 2017 elapsed 0 00:00:03

Then the table statistics are not locked:

OWNER TABLE_NAME LAST_ANALYZED STATTYPE_LOCKED NUM_ROWS
---------- -------------------- -------------------- -------------------- ----------
DEMO DEMO

Once you have changed what you want on the tables, you import the data (table_exists_action=truncate) and then you import the remaining: indexes, ref_constraints, triggers.
This is where you can also add include=table_statistics:

Starting "SYS"."SYS_IMPORT_TABLE_01": "sys/********@//localhost/pdb1 AS SYSDBA" tables=DEMO.DEMO directory=TMP table_exists_action=truncate include=index include=table_statistics
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

So that you have the statistics from the source, unlocked.

OWNER TABLE_NAME LAST_ANALYZED STATTYPE_LOCKED NUM_ROWS
---------- -------------------- -------------------- -------------------- ----------
DEMO DEMO 06-SEP-17 1

 

Cet article impdp content=metadata_only locks the stats est apparu en premier sur Blog dbi services.

Oracle Security Training In York - October 30 - 31st 2017

I will be running my two day Oracle security training course - How to Perform a Security Audit of an Oracle Database - Here in my home city of York, UK on the 30th to 31st October 2017 this year....[Read More]

Posted by Pete On 06/09/17 At 09:33 AM

Understanding Data Gravity as a DBA

Data gravity and the friction it causes within the development cycle is an incredibly obvious problem in my eyes.

Data gravity suffers from the Von Newmann Bottleneck. It’s a basic limitation on how fast computers can be. Pretty simple, but states that the speed of where data resides and where it’s processed is the limiting factor in computing speed.

OLAP, DSS and VLDB DBAs are constantly in battle with this challenge.  How much data is being consumed in a process, how much must be brought from disk and will the processing required to create the results end up “spilling” to disk vs. completing in memory.

Microsoft researcher Jim Gray has spend most of his career looking at the economics of data, which is one of the most accurate terms of this area of technical study.  He started working at Microsoft in 1995 and although passionate about many areas of technology, his research on large databases and transactional processing speeds is one of great respect in my world.

Now some may say this has little to do with being a database administrator, but how many of us spend significant time on the cost based optimizer, as moving or getting data has cost- so economics of data it is.

And this is the fundamental principle of data gravity and why DBAs get the big bucks.

If you’re interested in learning more about data gravity, DevOps and the future of DBAs, register for the upcoming webinar.

https://i0.wp.com/dbakevlar.com/wp-content/uploads/2017/09/Screen-Shot-2... 300w, https://i0.wp.com/dbakevlar.com/wp-content/uploads/2017/09/Screen-Shot-2... 768w, https://i0.wp.com/dbakevlar.com/wp-content/uploads/2017/09/Screen-Shot-2... 1226w" sizes="(max-width: 600px) 100vw, 600px" data-recalc-dims="1" />



Tags:  


Del.icio.us



Facebook

TweetThis

Digg

StumbleUpon




Copyright © DBA Kevlar [Understanding Data Gravity as a DBA], All Right Reserved. 2017.

The post Understanding Data Gravity as a DBA appeared first on DBA Kevlar.

12c dbms_stats.gather_table_stats on GTT do not commit

In my UKOUG OracleScene article on 12c online statistics and GTT I mentioned the following:

A final note about those 12c changes in statistics gathering on GTT. In 11g the dbms_stats did a commit at the start. So if you did gather stats after the load, you had to set the GTT as ON COMMIT PRESERVE ROWS. Or you just vacuum what you’ve loaded. That has changed in 12c. If you now choose to do a conventional insert followed by dbms_stats (having set private stats of course) then you don’t need to set on commit preserve rows anymore.

Today, I realized that I’ve never explained exactly when dbms_stats.gather_table_stats commits the transaction or not. Because, of course, it depends. In summary: 12c non-SYS owner GTT with private statistics.

Here is an example. I connect as non-SYS user:

SQL> connect demo/demo@//localhost/pdb1
Connected.
SQL> show user
USER is "DEMO"

I create a permanent table and a global temporary table:

SQL> create table DEMO(text varchar2(20));
Table created.
 
SQL> create global temporary table DEMOGTT(text varchar2(20));
Table created.

In the permanent table, I insert my row. The goal is to be sure that this insert is not commited and can be rolled back at the end:

SQL> insert into DEMO values('Forget me, please!');
1 row created.

In the global temporary table I insert one row. The goal is to be sure that the row remains until the end of my transaction (on commit delete rows):

SQL> insert into DEMOGTT values('Preserve me, please!');
1 row created.

Here it is:

SQL> select * from DEMO;
 
TEXT
--------------------
Forget me, please!
 
SQL> select * from DEMOGTT;
 
TEXT
--------------------
Preserve me, please!

Then, I gather statistics on the GTT:

SQL> exec dbms_stats.gather_table_stats(user,'DEMOGTT');
PL/SQL procedure successfully completed.

I check that my rows in the GTT are still there, which is a proof that no commit happened:

SQL> select * from DEMOGTT;
 
TEXT
--------------------
Preserve me, please!

And I check that, as no commit happened, I can rollback my previous insert on the permanent table:

SQL> rollback;
Rollback complete.
 
SQL> select * from DEMO;
no rows selected

This is the new behavior in 12c. The same in 11g would have committed my transaction before and after the call to dbms_stats.

GTT only

Here is the same example when gathering the stats on the permanent table:
SQL> show user
USER is "DEMO"
SQL> exec dbms_stats.gather_table_stats(user,'DEMO');
PL/SQL procedure successfully completed.
&nbsp:
SQL> select * from DEMOGTT;
no rows selected
&nbsp:
SQL> rollback;
Rollback complete.
&nbsp:
SQL> select * from DEMO;
&nbsp:
TEXT
--------------------
Forget me, please!

The transaction was committed by dbms_stats here: no rows from GTT (on commit delete rows), and the insert in permanent table was commited before my rollback.

Not for SYS

When connected as SYS:
SQL> show user
USER is "SYS"
SQL> exec dbms_stats.gather_table_stats(user,'DEMOGTT');
PL/SQL procedure successfully completed.
 
SQL> select * from DEMOGTT;
no rows selected
 
SQL> rollback;
Rollback complete.
 
SQL> select * from DEMO;
 
TEXT
--------------------
Forget me, please!

The transaction was committed by dbms_stats here: when the table is owned by SYS, dbms_stats commits.

I mean, not for SYS owner

If I’m connected by SYS but gather stats on a non-SYS table, dbms_stats do not commit:

SQL> show user
USER is "SYS"
SQL> exec dbms_stats.gather_table_stats('DEMO','DEMOGTT');
PL/SQL procedure successfully completed.
 
SQL> select * from DEMOGTT;
 
TEXT
--------------------
Preserve me, please!
 
SQL> rollback;
Rollback complete.
 
SQL> select * from DEMO;
no rows selected

The behaviour is not related to the user who runs dbms_stats, but the owner of the GTT.

Private statistics only

The default in 12c for GTT is private statistics, visible to session only. Trying the same with shared statistics (as in 11g):
SQL> show user
USER is "DEMO"
 
SQL> select dbms_stats.get_prefs(ownname=>user,tabname=>'DEMO_GTT',pname=>'GLOBAL_TEMP_TABLE_STATS') from dual;
 
DBMS_STATS.GET_PREFS(OWNNAME=>USER,TABNAME=>'DEMO_GTT',PNAME=>'GLOBAL_TEMP_TABLE
--------------------------------------------------------------------------------
SESSION
 
SQL> exec dbms_stats.set_table_prefs(user,'DEMO_GTT','GLOBAL_TEMP_TABLE_STATS','SHARED' );
PL/SQL procedure successfully completed.
 
SQL> exec dbms_stats.gather_table_stats(user,'DEMOGTT');
PL/SQL procedure successfully completed.
&nbsp:
SQL> select * from DEMOGTT;
no rows selected
&nbsp:
SQL> rollback;
Rollback complete.
&nbsp:
SQL> select * from DEMO;
&nbsp:
TEXT
--------------------
Forget me, please!
 
SQL> exec dbms_stats.set_table_prefs(user,'DEMO_GTT', 'GLOBAL_TEMP_TABLE_STATS',null);
PL/SQL procedure successfully completed.

The dbms_stats did commit my transaction here.

So what?

Private session statistics for GTT is a great feature. Use it: gather statistics after filling the GTT. And don’t worry about on commit delete rows GTT (the default) because this statistic gathering do not commit the transation.

 

Cet article 12c dbms_stats.gather_table_stats on GTT do not commit est apparu en premier sur Blog dbi services.

Upgrade with no effort

We all know what upgrades mean.  Installation…Configuration…Patching…Testing…Deployment…Change approvals…Meetings…Long hours…Perhaps pulling an “overnighter” but hopefully never having to enact the dreaded “Backout Plan”.

Or…. that stuff can “just happen” without you worrying about it at all! 

I logged on to my Exadata Cloud Express database on the weekend, and lo and behold, it is not Oracle Database 12c anymore.  It is 12c Release 2 !

A look back through my email Inbox, and I saw that I had indeed been notified of the upcoming upgrade some time ago, but I was too lazy to pay attention to it Smile

image

Now that’s the kind of upgrade I like !

When PDB name conflicts with CDB name

Going to multitenant architecture is not a big change. The administration things (DBA, monitoring, backups) connect to the CDB and the application things connect to the PDB. Without the multitenant option, it is still recommended to go to the CDB architecture. The non-CDB is deprecated and the multitenant architecture brings interesting features. People often ask how to name the CDB and the PDB, especially when they have naming rules or policies in the company. My recommendation is to name the PDB as you are used to naming the databases: the name often gives an idea of the data that is inside, the application, and the environment. The CDB is the container, and in my opinion, you should apply the same naming rules as for servers. Don’t forget that pluggable databases are made to be moved across CDB, so the CDB name should not depend on the content.

But, with single tenant, you have a one-to-one relationship between the CDB and the PDB and then may come the idea to set the same name for CDB and PDB… I’m not sure if it is supported or not and please, don’t do that.

Service Name

There’s one rule: the service name must be unique on a server, especially when registered to the same listener. The PDB name will be the default service name registered by the PDB. And the DB_UNIQUE_NAME of the CDB will be the default service name registered by the CDB. Then the PDB name must be different than the DBA_UNIQUE_NAME.

With this rule, it should be possible to have the same name for the CDB (the DB_NAME) and the PDB, given that we have set a different DB_UNIQUE_NAME.

Here is an example. The name of my Container Database is CDB1. But as it is part of a Data Guard configuration I changed the unique name to CDB1A (and standby will be CDB1B).

Here are the services from by CDB:

SQL> select * from v$services;
 
SERVICE_ID NAME NAME_HASH NETWORK_NAME CREATION_DATE CREATION_DATE_HASH GOAL DTP AQ_HA_NOTIFICATION CLB_GOAL COMMIT_OUTESSION_STATE_CONSISTENCY GLOBAL PDB SQL_TRANSLATION_PROFILE MAX_LAG_TIME STOP_OPTION FAILOVER_RESTORE DRAIN_TIMEOUT CON_ID
---------- ---- --------- ------------ ------------- ------------------ ---- --- ------------------ -------- ---------------------------------- ------ --- ----------------------- ------------ ----------- ---------------- ------------- ------
7 CDB1A 3104886812 CDB1A 27-AUG-17 1962062146 NONE N NO LONG NO NO CDB$ROOT NONE NONE 0 1
1 SYS$BACKGROUND 165959219 26-JAN-17 1784430042 NONE N NO SHORT NO NO CDB$ROOT NONE NONE 0 1
2 SYS$USERS 3427055676 26-JAN-17 1784430042 NONE N NO SHORT NO NO CDB$ROOT NONE NONE 0 1
0 pdb1 1888881990 pdb1 0 NONE N NO SHORT NO NO PDB1 NONE NONE 0 4
6 CDB1XDB 1202503288 CDB1XDB 27-AUG-17 1962062146 NONE N NO LONG NO NO CDB$ROOT NONE NONE 0 1

All are default services: CDB1A is the DB_UNIQUE_NAME, SYS$BACKGROUND for background processes, SYS$USERS when connecting without a service name, CDB1XDB is used to connec to XDB dispathers. PDB1 is the default service of my pluggable database PDB1.

I can also look at the services registred in the listener:


SQL> host lsnrctl status
 
LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 28-AUG-2017 20:34:36
 
Copyright (c) 1991, 2016, Oracle. All rights reserved.
 
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date 27-AUG-2017 20:41:33
Uptime 0 days 23 hr. 53 min. 3 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Log File /u01/app/oracle/diag/tnslsnr/VM104/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=VM104)(PORT=1521)))
Services Summary...
Service "57c2283990d42152e053684ea8c05ea0" has 1 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
Service "CDB1A" has 1 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
Service "CDB1XDB" has 1 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
Service "pdb1" has 1 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
The command completed successfully

There is just one additional service here: the GUI of my PDB (see https://blog.dbi-services.com/service-696c6f76656d756c746974656e616e74-has-1-instances/)

ORA-65149

Do you see any service named ‘CDB1′ here? No. Then I should be able to create a PDB with this name.

SQL> create pluggable database CDB1 admin user admin identified by covfefe file_name_convert=('pdbseed','cdb1');
 
Error starting at line : 1 in command -
create pluggable database CDB1 admin user admin identified by covfefe file_name_convert=('pdbseed','cdb1')
Error report -
ORA-65149: PDB name conflicts with existing service name in the CDB or the PDB
65149. 00000 - "PDB name conflicts with existing service name in the CDB or the PDB"
*Cause: An attempt was made to create a pluggable database (PDB) whose
name conflicts with the existing service name in the container
database (CDB) or the PDB.
*Action: Choose a different name for the PDB.

Ok. This is impossible. However, the error message is not correct. My PDB name does not conflict with existing service names. It may conflict with instance name or DB_NAME, but not with any service.

NID

As I’m not satisfied with this, I try to find another way to have the same name for CDB and PDB. I have a pluggable database named ‘PDB1′ and I’ll try to change the CDB name to this:


[oracle@VM104 ~]$ nid dbname=PDB1 target=sys/oracle
 
DBNEWID: Release 12.2.0.1.0 - Production on Mon Aug 28 20:40:08 2017
 
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
 
Connected to database CDB1 (DBID=926862412)
 
Connected to server version 12.2.0
 
Control Files in database:
/u01/oradata/CDB1A/control01.ctl
/u01/fast_recovery_area/CDB1A/control02.ctl
 
The following datafiles are read-only:
/u01/oradata/CDB1A/PDB1/USERS2.db (17)
These files must be writable by this utility.
 
Change database ID and database name CDB1 to PDB1? (Y/[N]) => Y
 
Proceeding with operation
Changing database ID from 926862412 to 3460932968
Changing database name from CDB1 to PDB1
Control File /u01/oradata/CDB1A/control01.ctl - modified
Control File /u01/fast_recovery_area/CDB1A/control02.ctl - modified
Datafile /u01/oradata/CDB1A/system01.db - dbid changed, wrote new name
Datafile /u01/oradata/CDB1A/sysaux01.db - dbid changed, wrote new name
Datafile /u01/oradata/CDB1A/undotbs01.db - dbid changed, wrote new name
Datafile /u01/oradata/CDB1A/pdbseed/system01.db - dbid changed, wrote new name
Datafile /u01/oradata/CDB1A/pdbseed/sysaux01.db - dbid changed, wrote new name
Datafile /u01/oradata/CDB1A/users01.db - dbid changed, wrote new name
Datafile /u01/oradata/CDB1A/pdbseed/undotbs01.db - dbid changed, wrote new name
Datafile /u01/oradata/CDB1A/PDB1/system01.db - dbid changed, wrote new name
Datafile /u01/oradata/CDB1A/PDB1/sysaux01.db - dbid changed, wrote new name
Datafile /u01/oradata/CDB1A/PDB1/undotbs01.db - dbid changed, wrote new name
Datafile /u01/oradata/CDB1A/PDB1/USERS.db - dbid changed, wrote new name
Datafile /u01/oradata/CDB1A/PDB1/USERS2.db - dbid changed, wrote new name
Datafile /u01/oradata/CDB1A/temp01.db - dbid changed, wrote new name
Datafile /u01/oradata/CDB1A/pdbseed/temp012017-08-27_18-30-16-741-PM.db - dbid changed, wrote new name
Datafile /u01/oradata/CDB1A/PDB1/temp012017-08-27_18-30-16-741-PM.db - dbid changed, wrote new name
Control File /u01/oradata/CDB1A/control01.ctl - dbid changed, wrote new name
Control File /u01/fast_recovery_area/CDB1A/control02.ctl - dbid changed, wrote new name
Instance shut down
 
Database name changed to PDB1.
Modify parameter file and generate a new password file before restarting.
Database ID for database PDB1 changed to 3460932968.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.
 
SQL> startup
ORACLE instance started.
 
Total System Global Area 859832320 bytes
Fixed Size 8798552 bytes
Variable Size 784338600 bytes
Database Buffers 58720256 bytes
Redo Buffers 7974912 bytes
ORA-01103: database name 'PDB1' in control file is not 'CDB1'
 
SQL> alter system set db_name=PDB1 scope=spfile;
 
System altered.
 
SQL> shutdown immediate
ORA-01507: database not mounted
 
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.
 
Total System Global Area 859832320 bytes
Fixed Size 8798552 bytes
Variable Size 784338600 bytes
Database Buffers 58720256 bytes
Redo Buffers 7974912 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
 
SQL> alter database open resetlogs;
 
Database altered.

That’s done.
My CDB is named PDB1:
SQL> select * from v$database;
 
DBID NAME CREATED RESETLOGS_CHANGE# RESETLOGS_TIME PRIOR_RESETLOGS_CHANGE# PRIOR_RESETLOGS_TIME LOG_MODE CHECKPOINT_CHANGE# ARCHIVE_CHANGE# CONTROLFILE_TYPE CONTROLFILE_CREATED CONTROLFILE_SEQUENCE# CONTROLFILE_CHANGE# CONTROLFILE_TIME OPEN_RESETLOGS VERSION_TIME OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL REMOTE_ARCHIVE ACTIVATION# SWITCHOVER# DATABASE_ROLE ARCHIVELOG_CHANGE# ARCHIVELOG_COMPRESSION SWITCHOVER_STATUS DATAGUARD_BROKER GUARD_STATUS SUPPLEMENTAL_LOG_DATA_MIN SUPPLEMENTAL_LOG_DATA_PK SUPPLEMENTAL_LOG_DATA_UI FORCE_LOGGING PLATFORM_ID PLATFORM_NAME RECOVERY_TARGET_INCARNATION# LAST_OPEN_INCARNATION# CURRENT_SCN FLASHBACK_ON SUPPLEMENTAL_LOG_DATA_FK SUPPLEMENTAL_LOG_DATA_ALL DB_UNIQUE_NAME STANDBY_BECAME_PRIMARY_SCN FS_FAILOVER_STATUS FS_FAILOVER_CURRENT_TARGET FS_FAILOVER_THRESHOLD FS_FAILOVER_OBSERVER_PRESENT FS_FAILOVER_OBSERVER_HOST CONTROLFILE_CONVERTED PRIMARY_DB_UNIQUE_NAME SUPPLEMENTAL_LOG_DATA_PL MIN_REQUIRED_CAPTURE_CHANGE# CDB CON_ID PENDING_ROLE_CHANGE_TASKS CON_DBID FORCE_FULL_DB_CACHING
---- ---- ------- ----------------- -------------- ----------------------- -------------------- -------- ------------------ --------------- ---------------- ------------------- --------------------- ------------------- ---------------- -------------- ------------ --------- --------------- ---------------- -------------- ----------- ----------- ------------- ------------------ ---------------------- ----------------- ---------------- ------------ ------------------------- ------------------------ ------------------------ ------------- ----------- ------------- ---------------------------- ---------------------- ----------- ------------ ------------------------ ------------------------- -------------- -------------------------- ------------------ -------------------------- --------------------- ---------------------------- ------------------------- --------------------- ---------------------- ------------------------ ---------------------------- --- ------ ------------------------- -------- ---------------------
3460932968 PDB1 27-AUG-17 1495032 28-AUG-17 1408558 27-AUG-17 ARCHIVELOG 1495035 0 CURRENT 27-AUG-17 2574 1496538 28-AUG-17 NOT ALLOWED 27-AUG-17 READ WRITE MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE ENABLED 3460947145 3460947145 PRIMARY 0 DISABLED NOT ALLOWED DISABLED NONE NO NO NO NO 13 Linux x86 64-bit 3 3 1497050 NO NO NO CDB1A 0 DISABLED 0 NO NO YES 0 NOT APPLICABLE 3460932968 NO

And I have a PDB with the same name:

SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
------ -------- ---- ---- ----------
2 PDB$SEED READ ONLY NO
4 PDB1 MOUNTED
 
SQL> alter pluggable database PDB1 open;
 
Pluggable database PDB1 altered.
 
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
------ -------- ---- ---- ----------
2 PDB$SEED READ ONLY NO
4 PDB1 READ WRITE NO

What was forbidden with a wrong error message was made possible with this other way.

So what?

Please, do not take this as a solution. There is clearly a problem here. Maybe the documentation and error message are wrong. Maybe the NID has a bug, allowing to do something that should be blocked. Or the create pluggable database has a bug, blocking something that should be possible. Until this is fixed (SR opened) I would recommend that the PDB name is always different than the CDB name, independently of service names. Well, I would recommend it anyway as it brings a lot of confusion: when you mention a database name, people will not know whether you are referring to the CDB or the PDB.

 

Cet article When PDB name conflicts with CDB name est apparu en premier sur Blog dbi services.