Dontcheff

Archive for the ‘Databases’ Category

Automatic Zone Maps in the Oracle Database

In Data, Database tuning, Databases, DBA, New features, Oracle database on May 18, 2020 at 06:18

A zone is a set of a contiguous data blocks on disk.

A zone map is an index-like structure built on a table and stores information about the zones of that table.

There are 2 major differences between indexes and zone maps:

– A zone map stores information per zone instead of per row which makes it much more compact than an index
– A zone map is not actively managed the way an index is kept in sync with the DML on the table

Zone maps are closer as a concept to Exadata’s storage indexes than to B-tree indexes.

Before going into how Automatic Zone Maps work in Oracle 20c, let me explain the concept with an example. Consider a small table containing basic information about some relational databases from db-engines.com (rank, score, initial and last release, cloud based):

The RDBMS_BRANDS segment has 6 data blocks with 2 rows per block:

Let us now create the zonemap on the RDBMS_BRANDS table (on 3 columns only):

 
SQL> CREATE MATERIALIZED ZONEMAP rdbms_zmap ON 
rdbms_brands (db_engines_rank, db_engines_score, initial_release); 

Materialized zonemap RDBMS_ZMAP created.

We have now 3 zones and each zone contains two blocks and stores the minimum and maximum of db_engines_rank, db_engines_score and initial_release:

Next, let us run a query returning all RDBMS brands with ranking score more than 1000:

Looking at the execution plan below we see that Oracle is scanning only Zone 1 as the maximum score in all other zone is smaller than 1000:

That is how zone maps work … but what is new in Oracle 20c?

We can now enable automatic creation and maintenance of basic zone maps for both partitioned and non-partitioned tables. But for now, the creation is not available for join zone maps, IOTs, external tables or temporary tables!

In 20c, you can use the new package DBMS_AUTO_ZONEMAP to enable Automatic Zone Maps in the database. Automatic zone map creation is turned off by default.

These four values are allowed for the parameter AUTO_ZONEMAP_MODE:

ON: Turns on auto zone map feature completely. Both for foreground and background zone map creation and maintenance
OFF: Turns off auto zone map feature completely. Both for foreground and background zone map creation and maintenance
FOREGROUND: Turns on only for foreground zone map creation and maintenance
BACKGROUND: Turns on only for background zone map creation and maintenance

You may use the ACTIVITY_REPORT function to view auto zone map activity for a given time window. Note that the background job that performs automatic zone map processing starts once per hour and each run may last up to three hours.

 
SET LONG 100000
SELECT dbms_auto_zonemap.activity_report() report FROM dual;

These 2 zonemaps related views show the most important information DBAs need:

DBA_ZONEMAPS displays all zone maps in the database
DBA_ZONEMAP_MEASURES displays the measures for all zone maps in the database

On a final note: Automatic Zone Maps are available for now only on Exadata and requires the Oracle Partitioning option.

Automatic Index Optimization in Oracle Database 20c

In Database tuning, Databases, DBA, New features, Oracle database on May 11, 2020 at 05:47

Oracle database 20c came with 138 new features and one of them is directly related to the indexes in the database. It is called Automatic Index Optimization.

For some DBAs indexes in the database do not need extra care and they don’t bother much about rebuilding, compressing, coalescing or shrinking them. I have administered in the past a 24×7 mission critical database with size of 5TB where indexes were occupying 4.5TB of all that. Correct, real data was less than 500GB granted that you have SYSTEM, SYSAUX, etc.

Automatic Index Optimization does not mean optimization of the Automatic Indexes in the database but rather making now the Index Optimization an automatic process. Here is how it works and what you have to do in order to enable it and make it work.

First, in order to implement an ILM strategy, you have to enable Heat Maps in the database to track data access and modification. You can enable and disable heat map tracking at the system or session level with the ALTER SYSTEM or ALTER SESSION statement using the HEAT_MAP init.ora parameter, for example:

SQL> alter system set HEAT_MAP = ON;

Like ADO for data segments, Automatic Index Optimization works via ILM on indexes by enabling policies that automatically optimize indexes by compressing, shrinking and rebuilding them. Oracle is using the existing Heat Maps and collects activity statistics on the indexes.

So next, add ADO policies for indexes in order to enable their compression and optimization using the existing Automatic Data Optimization (ADO) framework. You can do it for newly created indexes as well as for already existing indexes.

There are 2 options:

– ADD POLICY TIER in order to perform the operation on a say low cost/ tier 2 tablespace when tier 1 storage is under space pressure
– ADD POLICY OPTIMIZE in order to kick off the process after a certain number of days passes without accessing the index

Here are few examples:

SQL> create index julian.price_idx ON julian.sales(price)
ILM ADD POLICY OPTIMIZE AFTER 31 DAYS OF NO MODIFICATION;

SQL> alter index julian.price_idx ILM ADD POLICY TIER TO BC_DATA;

SQL> alter index julian.price_idx
ILM ADD POLICY OPTIMIZE AFTER 3 DAYS OF NO ACCESS;

SQL> SELECT POLICY_NAME, POLICY_TYPE, ENABLED FROM DBA_ILMPOLICIES;

POLICY_NAME             POLICY_TYPE    ENA
---------------------   -------------  ---
P1                      DATA MOVEMENT  YES

Note that the Oracle documentation has the tier syntax wrong: instead of “ILM ADD POLICY SEGMENT TIER” use “ILM ADD POLICY TIER”.

The optimization process includes actions such as compressing, shrinking or rebuilding the indexes:

Compress: Compresses portions of the key values in an index segment (~3 times)
Shrink: Merges the contents of index blocks where possible to free blocks for reuse
Rebuild: Rebuilds an index to improve space usage and access speed

Notice that you cannot decide which of the 3 above to use. Oracle automatically determines which action is optimal for the index and implements that action as part of the optimization process.

But can we have Automatic Index optimization for Automatic Indexes and not have to rebuild them manually any longer? I did try indeed and here is the result:

SQL> alter index julian."SYS_AI_abrca2u9qmxt7"
ILM ADD POLICY OPTIMIZE AFTER 7 DAYS OF NO ACCESS;

Error starting at line : 17 in command -
alter index julian."SYS_AI_abrca2u9qmxt7"
ILM ADD POLICY OPTIMIZE AFTER 7 DAYS OF NO ACCESS
Error report -
ORA-65532: cannot alter or drop automatically created indexes

Clearly not possible but the error message is sort of misleading because actually you can alter and even drop automatic indexes:

SQL> alter index julian."SYS_AI_abrca2u9qmxt7" rebuild online;
Index altered.
SQL> alter index julian."SYS_AI_abrca2u9qmxt7" coalesce;
Index altered.
SQL> alter index julian."SYS_AI_abrca2u9qmxt7" shrink space;
Index altered.

How to drop the Auto Index? Just rebuild it a new tablespace and run “drop tablespace … including contents and datafiles” – yes it works.

Also, while administering ADO policies for indexes, you cannot manually disable these policies but you can delete an index policy. An ADO policy for indexes executes only one time. After the policy executes successfully, the policy is disabled and is not evaluated again.

SQL> alter index julian.price_idx ILM DELETE POLICY p1;

Moreover, such policies for indexes on partition level are not yet supported. The ADO policy is cascaded to all partitions. So, if we have hybrid tables in the Cloud, we cannot move local indexes automatically to object storage but it should work for global indexes. Note that we can use Automatic Data Optimization (ADO) policies with hybrid partitioned tables under some conditions.

Here are some other limitations:

– ADO does not perform checks for storage space in a target tablespace when using storage tiering
– ADO is not supported on materialized views
– ADO is not supported with index-organized tables or clusters
– ADO concurrency depends on the concurrency of the Oracle scheduler meaning if a policy job for ADO fails more than two times, then the job is marked disabled and the job must be manually enabled later

The feature is available in almost all flavors of the database, i.e., EE, Database Cloud Service, Exadata and ODA: but it requires the Oracle Advanced Compression option.

The 20c Database Upgrade Mystery

In Cloud, Databases, DBA, Oracle database on April 27, 2020 at 14:31

Oracle Database 20c is available only for preview. It is not available for production use. Upgrades to or from Oracle Database 20c are not supported.

So, upgrades are not supported but there exists an Oracle Database 20c Upgrade Guide. The 20c upgrade pdf file is 461 pages long!

Let me shed some light on this “mystery”.

As of today, April 27th 2020 (Oracle Database 19c is today 1 year old on Linux), Oracle 20c is only available from the Oracle Public Cloud in preview mode. To try out this 20c preview, you will need an Oracle Public Cloud tenancy with cloud credits (paid or promotional), as the DBCS instance will require an OCI Compute VM. And, by the way, 20c does *not* run on Exadata.

Once 20c becomes generally available also in non-preview only mode, upgrades will be also available.

You can perform a direct upgrade to the new release from the following releases:

• 19c
• 18c
• 12.2.0

Here is what we need to know about compatibility:

– Before upgrading to Oracle Database 20c, you must set the COMPATIBLE initialization parameter to at least 12.2.0
– In Oracle Database 20c, when the COMPATIBLE initialization parameter is not set in your parameter file, the COMPATIBLE parameter value defaults to 20.0.0
– Installing earlier releases of Oracle Database on the same computer that is running Oracle Database 20c can cause issues with client connections

The most important point about the 20c architecture is that starting with the first release of Oracle Database 20c, non-CDB Oracle Database upgrades to non-CDB architecture are desupported. Meaning you need a container and your data will be in a pluggable database.

Starting with Oracle 19c you can have up to 3 pluggable databases (PDBs) per container database (CDB) without requiring additional multitenant license. See page 11 of Oracle Database Licensing Information User Manual for all the details (it is a 361 page pdf file).

So, when upgrading to 20c, we have 2 options:

Option 1: Convert the non-CDB to a PDB before upgrade

With this option, you plug in the non-CDB Oracle Database release to the same release CDB. For example, plug in a non-CDB Oracle Database Release 19c into an Oracle Database 19c release CDB. Finish converting the non-CDB Oracle Database to a PDB. Then, upgrade the entire CDB, with its PDBs, to Oracle Database 20c.

Option 2: Plug in the non-CDB, upgrade, and finish converting the non-CDB to a PDB after upgrade

With this option, you plug in a non-CDB Oracle Database release to an OracleDatabase 20c CDB. Upgrade the plugged-in non-CDB Oracle Database to Oracle Database 20c. Then, finish converting the non-CDB Oracle Database to a PDB.

Here is what you need to know about the DBUA and the Oracle home:

– Starting with Oracle Database 20c, Database Upgrade Assistant (DBUA) is replaced by the AutoUpgrade utility
– Starting with Oracle Database 20c, the default network administration directory changes from the previous default in the local Oracle home, Oracle_home/network (for example, /u01/app/oracle/product/19.1.0/dbhome_1/network), to a new location.
– The new default location is the shared Oracle Base Home, in the path ORACLE_BASE/ homes/HOME_NAME/network/admin
– Starting with Oracle Database 20c, an Oracle Database installation configures all Oracle Database homes in read-only mode by default

And here is what you need to know before the upgrade about security and the init.ora parameters:

– Starting with Oracle Database 20c, the data types DBMS_CRYPTO_TOOLKIT_TYPES and package DBMS_CRYPTO_TOOLKIT are desupported
– The init.ora parameters UNIFIED_AUDIT_SGA_QUEUE_SIZE, UNIFIED_AUDIT_SGA_QUEUE_SIZE, AUDIT_FILE_DEST, AUDIT_SYS_OPERATIONS, AUDIT_SYSLOG_LEVEL an d AUDIT_TRAIL have been desupported
– Desupport of IGNORECASE parameter for passwords: starting in Oracle Database 20c, the IGNORECASE parameter for the orapwd file is desupported and all newly created password files are case-sensitive
– Desupport of DISABLE_DIRECTORY_LINK_CHECK: the DISABLE_DIRECTORY_LINK_CHECK parameter is desupported, with no replacement

Further info and links were listed in detail by the King of Database Upgrades Mike Dietrich – his blog is dedicated to Oracle Database upgrades!

Now, how about long term support? Do you care only about LTS database releases when planning upgrades? As Tim Hall says: “It’s important to check out what is happening in the 20c release, because it may alter how you use the earlier releases now. There is no point launching into a new development using a feature that is about to disappear. Remember Oracle Streams anyone?” Yes, indeed Tim: Streams, Advanced Replication… I rather be on the latest release and use the new features – live and learn…

Tablespaces in Oracle Database 20c

In Databases, DBA, New features, Oracle database on April 8, 2020 at 08:52

Oracle 20c, released recently with 138 new features, is still only available from the cloud but it brings one major change for DBAs which they can no longer avoid: use containers and pluggable databases. And this changes slightly the way how DBAs look also at tablespaces.

But what is new in 20c about tablespaces? Now, DBAs can set the default tablespace encryption algorithm:

The new TABLESPACE_ENCRYPTION_DEFAULT_ALGORITHM dynamic init.ora parameter defines the default encryption algorithm for tablespace creation operations. The default value in 20.2.0 is AES128.

If you set TABLESPACE_ENCRYPTION_DEFAULT_ALGORITHM to say 3DES168, then all future tablespace creation operations will use 3DES168 as the default encryption algorithm. And this is both for offline and online tablespace encryption operations.

The supported values for the encryption algorithms are AES128, AES192, AES256, ARIA128, ARIA192, ARIA256, GOST256, SEED128 and 3DES168.

The CREATE TABLESPACE command in 20c is not more complex than before. Tablespaces can be permanent, undo or temporary; bigfile (one data file or temp file up to approx. 4 billion blocks, max. size is 128 terabytes (TB) for a tablespace with 32K blocks and 32TB for a tablespace with 8K blocks) or smallfile (max. 1022 data files or temp files each with max. 4 million blocks).

The concept of tablespace sets is not new but not highly adopted.

But going back to tablespaces and PDBs. Here is the output from DBA_TABLESPACES run from a PDB as SYS:

And here is the the output from DBA_TABLESPACES run from the root container as SYS:

Of course, DBA_TABLESPACES is no longer the place from which the DBA will find all tablespaces from the database. The correct one is CDB_TABLEPSPACES but if you run the “select * from cdb_tablepsaces;” as SYS from within the PDB, you will still get the same output.

However, run is as SYS from the root container, you get what you are looking for:

From 20c onwards, DBA should get used to the new CDB_ views. And then, less and less people will ask the question “Why do we have 2 tablespaces with the same name?”….

Here is something else which is new in 20c: Oracle automatically shrinks an Oracle ACFS file system based on policy providing there is enough free storage in the volume. ACFS stands for Automatic Storage Management Cluster File System.

In 20c, you can use now File Group templates by customizing and setting default file group properties for automatically created file groups. Without file group templates if you wanted to change properties for an automatically created file group you would have to manually change the properties after the associated files are created which triggers an unnecessary rebalance. The file group templates feature provides a much better option.

New is also the SecureFiles defragmentation, it provides online defragmentation of allocated and freed space in SecureFiles segments for all types of SecureFiles LOBs – compressed deduplicated encrypted. Defragmentation can be done automatically by a background process and the segment advisor can estimate the fragmentation levels and how much space can be saved. Defragmentation can be done mostly in-place with some temp segment space needed to hold intermediate results.

Next, there are 2 new features of Data Pump related to tablespaces:

– Starting with Oracle Database 20c, Data Pump improves transportable tablespace metadata operations with parallelism. Oracle Data Pump now supports parallel export and import operations for Transportable Tablespace (TTS) metadata. This is the information that associates the tablespace data files with the target database in a TTS migration. Parallelism improves TTS export and import performance especially when there are millions of database objects in the data files including tables indexes partitions and subpartitions.

– Starting with Oracle Database 20c, Data Pump resumes transportable tablespace export and import jobs that are stopped. Oracle Data Pump has the capacity to resume transportable tablespace export and import jobs. Due to errors or other problems you can find that transportable tablespace export or import jobs are stopped. Oracle Data Pump’s capacity to resume these stopped jobs helps to save you time and makes the system more available.

Another new 20c feature is Automatic Zone Maps which are created and maintained for any user table without any customer intervention. Zone maps allow the pruning of blocks and partitions based on the predicates in the queries without any user intervention. Automatic zone maps are maintained for direct loads and are maintained and refreshed for any other DML operation incrementally and periodically in the background. Automatic zone maps are improving the performance of any query transparently and automatically without the need of any user action. However, this feature is available only on Exadata.

And finally, something which I find interesting in the good way: starting with Oracle Database 20c, you can specify an expression as the value for some initialization parameters, which enables the database to automatically adjust the parameter value in response to environmental changes. This is especially useful in Oracle Autonomous Database environments.

Example: CPU_COUNT = $SYSTEM_CPU/4 or SESSIONS = MAX(200, PROCESSES * 1.5)

Blockchain tables in Oracle Database 20c

In Databases, DBA, Oracle database on March 16, 2020 at 10:58

Blockchain tables are insert-only tables that organize rows into a number of chains and is a new concept starting with Oracle 20c. Each row in a chain, except the first row, is chained to the previous row in the chain by using a cryptographic hash. For each Oracle RAC instance a blockchain table contains thirty two chains, ranging from 0 through 31.

This is an example of how a blockchain table is created in 20c:

Let me first point out the main restrictions:

– Blockchain tables cannot be created in the root container and in an application root container: ORA-05729: blockchain table cannot be created in root container
– You cannot update the rows: ORA-05715: operation not allowed on the blockchain table
– In general, you cannot delete rows, truncate the table or drop the blockchain table: ORA-05723: drop blockchain table NDA_RECORDS not allowed
– Don’t even try to drop the tablespace containing blockchain tables, here is what happens:

DROP TABLESPACE BC_DATA INCLUDING CONTENTS AND DATAFILES;
ORA-00604: error occurred at recursive SQL level 1
ORA-05723: drop blockchain table NDA_RECORDS not allowed

The most important new view in 20c related to blockchain tables is DBA_BLOCKCHAIN_TABLES:

The 4 (non-trivial) columns of DBA_BLOCKCHAIN_TABLES contain the following information:

1. ROW_RETENTION: The minimum number of days a row must be retained after it is inserted into the table – if the value of this column is NULL, then rows can never be deleted from the table. In the example above, the row can be deleted after 16 days. Otherwise, you will get: ORA-05715: operation not allowed on the blockchain table

2. ROW_RETENTION_LOCKED: 2 possible values (YES and NO) showing if the row retention period for the blockchain table is locked.

YES: The row retention period is locked. You cannot change the row retention period.
NO: The row retention period is not locked. You can change the row retention period to a value higher than the current value with the SQL statement ALTER TABLE … NO DELETE UNTIL n DAYS AFTER INSERT.

3. TABLE_INACTIVITY_RETENTION: Number of days for which the blockchain table must be inactive before it can be dropped, that is, the number of days that must pass after the most recent row insertion before the table can be dropped. A table with no rows can be dropped at any time, regardless of this column value. In the example above, a year of inactivity must pass before the table can be dropped.

4. HASH_ALGORITHM: The algorithm used for computing the hash value for each table row.

To each row you add/insert to the blockchain table, Oracle adds values to the hidden columns of the blockchain table. Hidden columns are populated after you commit. They are used to implement sequencing of rows and verify that data is tamper-resistant. You can create indexes on hidden columns. In order to view the values of the hidden columns, you should explicitly include their names in the SQL, just like this:

Hidden Columns in Blockchain Tables will give you more details about the subject.

The following additional operations are not allowed with blockchain tables:

– Adding, dropping, and renaming columns
– Dropping partitions
– Defining BEFORE ROW triggers that fire for update operations (other triggers are allowed)
– Direct-path loading
– Inserting data using parallel DML
– Converting a regular table to a blockchain table (or vice versa)

There is a new PL/SQL procedure DBMS_BLOCKCHAIN_TABLE which contains 5 procedures, one of which VERIFY_ROWS is used to validate he data in the blockchain table.

Use DBMS_BLOCKCHAIN_TABLE.DELETE_EXPIRED_ROWS to remove rows that are beyond the retention period of the blockchain table.

For DBAs:

– For each chain in a database instance, periodically save the current hash and the corresponding sequence number outside the database.
– In an Oracle Data Guard environment, consider using the maximum protection mode or maximum availability mode to avoid loss of data.

You can use certificates to verify the signature of a blockchain table row. Check here on how to add and delete certificates to blockchain table rows.

Final note: you really have a good eye if you noticed the new 20c datatype I used in the table creation at the top of this blog post.

Oracle 20.2.0 new features for DBAs

In Autonomous, Cloud, Databases, New features, Oracle database on February 25, 2020 at 09:38

Oracle 20c is now available in preview mode from the Oracle Public Cloud. Preview version databases are not intended for production use and have limited functionality.

After testing some of the new features of 20c, here is what might be of interest for most DBAs:

1. Provisioning the database is relatively simple. You need an SSH Public key, create a VCN (Virtual Cloud Network) and a client subnet in your compartment. A hostname prefix is also mandatory. Note that the administrator password must be 9 to 30 characters and contain at least 2 uppercase, 2 lowercase, 2 special, and 2 numeric characters. The special characters must be _, #, or -. You cannot bypass that. The shape type must be “Virtal Machine” and the SMS (Storage Management Software) must be “Logical Volume Manager”.

Once provisioned you need the IP address which is under “Nodes” (bottom left, just under “Resources”). As you can see, it is no longer under “General Information”:

And … you can stop the database only manually (as of today, February 25th, 2020) – there is no button for stopping the node. Just terminate in case you are on a tight budget.

2. Blockchain tables

Blockchain tables are append-only tables in which only insert operations are allowed. Deleting rows is either prohibited or restricted based on time. Rows in a blockchain table are made tamper-resistant by special sequencing & chaining algorithms. Users can verify that rows have not been tampered. Have a look at an example I used to create a blockchain table:

Here is how to manage blockchain tables.

Most important is to specify the Retention Period for the Blockchain Table by using the NO DROP clause in the CREATE BLOCKCHAIN TABLE statement. Also specify the Retention Period for Rows in the Blockchain Table: use the NO DELETE clause in a CREATE BLOCKCHAIN TABLE statement.

3. A multitenant container database is the only supported architecture in Oracle Database 20c. While the documentation is being revised, legacy terminology may persist. In most cases, “database” and “non-CDB” refer to a CDB or PDB, depending on context. In some contexts, such as upgrades, “non-CDB” refers to a non-CDB from a previous release. Check the changes in Oracle 20c for Oracle Multitenant.

4. Data Pump

– Oracle Data Pump 20c can include and exclude objects in the same export or import operation meaning that now, Oracle Data Pump commands can include both INCLUDE and EXCLUDE parameters in the same operation. By enabling greater specificity about what is being migrated, this enhancement makes it easier to migrate to Oracle Cloud, or to another on-premises Oracle Database.

Note: when you include both parameters in a command, Oracle Data Pump processes the INCLUDE parameter first, and includes all objects identified by the parameter. Then it processes the EXCLUDE parameters, eliminating the excluded objects from the included set. Here is an example of including only 2 tables but excluding all indexes except the PKs (real use case: you want to enable Oracle Auto Indexing in ADB and while importing the data you need to drop all indexes except the PKs):

expdp julian SCHEMAS=JULIAN DUMPFILE=julian.dmp REUSE_DUMPFILES=YES 
INCLUDE=TABLE:\"IN \(\'CLIENTS\',\'SALES\'\)\" 
EXCLUDE=INDEX:\"LIKE \'IDX\%\'\"

– Oracle Data Pump 20c resumes transportable tablespace export and import jobs that are stopped

– Oracle Data Pump 20c supports parallel export and import operations for Transportable Tablespace (TTS) metadata

– Oracle Data Pump 20c supports optional index compression on imports, including for Oracle Autonomous Database

– Oracle Data Pump 20c supports adding, changing and eliminating table compression

– Oracle Database 20c supports index compression as well by introducing a new TRANSFORM parameter clause, INDEX_COMPRESSION_CLAUSE

– Oracle Data Pump 20c can perform exports from Oracle Autonomous Database into dump files in a cloud object store

– Starting with Oracle Database 20c, a checksum is now added to the dumpfile – you can use the checksum to help to confirm that the file is valid after a transfer to or from the object store and also after saving dumpfiles on on-premises and that it has no
accidental or malicious changes

5. Small improvements and changes in 20c:

– The IGNORECASE parameter for the orapwd file is desupported – all newly created password files are case-sensitive

– A new dynamic view called V$PMEM_FILESTORE displays information about Persistent Memory Filestores

– Certain predefined columns of unified audit records from common unified audit policies can be written to the UNIX SYSLOG destination – to enable this feature, you set UNIFIED_AUDIT_COMMON_SYSTEMLOG, a new CDB level init.ora parameter (added in Oracle 19c (19.3) but not included in the References)

– You now can set the TABLESPACE_ENCRYPTION_DEFAULT_ALGORITHM dynamic parameter to define the default encryption algorithm for tablespace creation operations

– Database Vault: a DV_OWNER common user in the CDB root can prevent local users from creating Oracle Database Vault controls on common objects in a PDB

– AutoShrink: Oracle Automatic Storage Management Cluster File System (Oracle ACFS) automatic shrinking automatically shrinks an Oracle ACFS file system based on policy, providing there is enough free storage in the volume

– The Oracle Grid Infrastructure feature Automatic Storage Management Cluster File System (Oracle ACFS) is desupported with Microsoft Windows

– An Oracle Database installation configures all Oracle Database homes in read-only mode by default

– Traditional auditing is deprecated in Oracle Database 20c thus Oracle recommend that we use unified auditing, which enables selective and more effective auditing inside Oracle Database

– The package DBMS_OBFUSCATION_TOOLKIT is desupported, and replaced with DBMS_CRYPTO

– Older encryption and hashing algorithms contained within DBMS_CRYPTO are deprecated

– The Large Object (LOB) features DBMS_LOB.LOADFROMFILE and LOB buffering are desupported

– You can configure database clients to maintain multiple Secure Sockets Layer (SSL) sessions using different SSL certificates

– In the DBMS_ROLLING.set_parameter(), there is a new parameter, called BLOCK_UNSUPPORTED – by default, BLOCK_UNSUPPORTED
is set to 1 [YES], indicating that operations performed on tables that are unsupported by Transient Logical Standby will be blocked on the primary database. If set to 0 [OFF], then the DBMS_ROLLING package does not block operations on unsupported tables

– In order to coordinate with the Oracle GoldenGate feature OGG EXTRACT, the LOGICAL_REPLICATION clause now provides support for automatic extract of tables

– Two new views, DBA_OGG_AUTO_CAPTURED_TABLES, and USER_OGG_AUTO_CAPTURED_TABLES, provide you with tools to query which tables are enabled for Oracle GoldenGate automatic capture

6. Finally, her are the 7 new init.ora parameters in Oracle 20.2.0:

DBNEST_ENABLE (DbNest is OS resource and file system isolation for PDBs)
DBNEST_PDB_FS_CONF
DIAGNOSTICS_CONTROL (meant to be used with Oracle Support)
MAX_IDLE_BLOCKER_TIME (maximum number of minutes before a blocking session is automatically terminated)
PMEM_FILESTORE
TABLESPACE_ENCRYPTION_DEFAULT_ALGORITHM
OPTIMIZER_REAL_TIME_STATISTICS

Memoptimized Rowstore Fast Ingest in Oracle Database 19c

In Data, Database tuning, Databases, DBA, Oracle database, Oracle internals on November 25, 2019 at 15:53

“A filmmaker should never assume he’s superior to his subject. I often find that even the simplest topic remains an enigma. The best film portraits not only evoke that enigma but ingest it in a process that renders what’s invisible visible.” – Damian Pettigrew, a documentary SME

The same statement above holds for database SMEs using the new fast ingest process. Because their data might be at one point neither visible nor invisible. From DBA point of view I mean…

Memoptimized Rowstore fast ingest is a new feature of Oracle 19c and is all about high performance data streaming. Here is an example from Oracle which is ultra fast, arguably 25 million inserts per second on two socket server:

Memoptimized Rowstore was introduced in Oracle 18c and here are few good blog posts on the subject:

Oracle Database 18c: Introduction to Memoptimized RowStore (Memory of a Free Festival) by Richard Foote
MemOptimized RowStore in Oracle Database 18c by Philipp Salvisberg

The fast ingest functionality of Memoptimized Rowstore enables fast data inserts into an Oracle Database from applications that ingest small but high volume transactions with almost no transactional overhead. The inserted data is temporarily buffered in the large pool before writing it to disk in bulk in a deferred, asynchronous manner.

Memoptimized Rowstore provides the following two functionalities:

Fast ingest:
– Fast ingest optimizes the processing of high-frequency, single-row data inserts into a database
– Fast ingest uses the large pool for buffering the inserts before writing them to disk, so as to improve data insert performance

Fast lookup:
– Fast lookup enables fast retrieval of data from for high-frequency queries
– Fast lookup uses a separate memory area in the SGA called the memoptimize pool for buffering the data queried from tables
– For using fast lookup, you must allocate appropriate memory size to the memoptimize pool using MEMOPTIMIZE_POOL_SIZE

The memoptimize pool contains two parts: (1) the memoptimize buffer area which occupies 75% of the memoptimize pool and (2) a hash index that is a non-persistent segment data structure having several units containing a number of hash buckets. The hash index occupies 25% of the memoptimize pool.

Memoptimize buffer area

There are two steps for using fast ingest for inserting data:

1. Enable a table for fast ingest: specify the MEMOPTIMIZE FOR WRITE clause in the CREATE TABLE or ALTER TABLE statement: Example on how to enable a table for Fast Ingest

2. Specify a hint for using fast ingest for an insert operation by specifying the MEMOPTIMIZE_WRITE hint in the INSERT statement: Example on specifying a hint for using Fast Ingest for data inserts

Note that a table can be configured for using both fast ingest and fast lookup but these 4 points should be always considered:

– Because fast ingest buffers data in the large pool, there is a possibility of data loss in the event of a system failure. To avoid data loss, a client must keep a local copy of the data after performing inserts, so that it can replay the inserts in the event of a system failure before the data is written to disk. A client can use the DBMS_MEMOPTIMIZE package subprograms to track the durability of the inserts. After inserts are written to disk, a client can destroy its local copy of the inserted data.

Use the GET_APPLY_HWM_SEQID function to find the low high-water mark of sequence numbers of the records that have been successfully written to disk across all the sessions:

select DBMS_MEMOPTIMIZE.GET_APPLY_HWM_SEQID from dual;

Equivalently, use the GET_WRITE_HWM_SEQID function to return the high-water mark sequence number of the record that has been written to the large pool for the current session.

select DBMS_MEMOPTIMIZE.GET_WRITE_HWM_SEQID from dual;

– Queries do not read data from the large pool, hence data inserted using fast ingest cannot be queried until it is written to disk.

If you need to flush all the fast ingest data from the large pool to disk for the current session, just run:

exec DBMS_MEMOPTIMIZE.WRITE_END;

– Parent-child transactions must be synchronized to avoid errors. For example, foreign key inserts and updates of rows inserted into the large pool can return errors, if the parent data is not yet written to disk.

– Index operations are supported by fast ingest similar to the regular inserts. However, for fast ingest, database performs index operations while writing data to disk, and not while writing data into the large pool.

You can disable a table for fast ingest by specifying the NO MEMOPTIMIZE FOR WRITE clause in the ALTER TABLE statement. You can view the fast ingest data in the large pool using the V$MEMOPTIMIZE_WRITE_AREA view. You can also view and control the fast ingest data in the large pool using the subprograms of the packages DBMS_MEMOPTIMIZE and DBMS_MEMOPTIMIZE_ADMIN.

The DBMS_MEMOPTIMIZE_ADMIN package has only one procedure WRITES_FLUSH which does not accept any input or output parameters.

exec DBMS_MEMOPTIMIZE.WRITES_FLUSH;

Here is one more example from Oracle:

As you see, you can lose data using this new technique. Use it with caution unless you can afford that or re-insert the data.

If you need to remove or populate fast lookup data for a certain table from the memoptimize pool, use DBMS_MEMOPTIMIZE.DROP_OBJECT and DBMS_MEMOPTIMIZE.POPULATE respectively.

Check MemOptimized RowStore in Oracle Database 19c by Philipp Salvisberg for a good performance comparison between PL/SQL and Java Thin.

Oracle Autonomous Database: Dedicated vs Serverless

In Autonomous, Databases, DBA, Exadata, Oracle database, Oracle Engineered Systems on July 22, 2019 at 11:45

This blog post describes the 5 main differences between ATP-D & ATP-S and the 5 main ATP-D physical characteristics and constraints.

Autonomous Transaction Processing Dedicated (ATP-D) is a deployment choice that enables us to provision autonomous databases into dedicated Exadata cloud infrastructure instead of a shared infrastructure with other tenants.

ATP-D can be used for both OLTP or hybrid workloads for databases of any size. ATP-D is specifically good when you need highest governance, consistent performance and operational control.

For now, dedicated infrastructure means a Quarter Exadata Rack. Half and Full will be soon available too.

Besides complete physical storage isolation, ATP-D provides private IP networking, secure connections using transport layer security (TLS) credentials, and customization of software image lifecycle to align with application lifecycle.

The Fleet Administrator (think of a DBA for Autonomous Cloud) needs to create first the Exadata Infrastructure, then the CDB and only at the end the PDB. Recall that for ATP-S, you directly create the PDB.

These are the 5 main differences between ATP-D and ATP-S:

– Private IPs are not yet supported for serverless ADB deployments but they are on the short-term roadmap
– Private IPs are supported with ATP-Dedicated

– Serverless edition has no minimums or maximums for terms of usage
– Dedicated edition has a minimum term of one month and the minimum OCPU purchase is 1 OCPU per database node and up to the maximum number of OCPUs per rack: $26.88 per hours which means about $645 per day

– Loading data from object stores via DBMS_CLOUD is the recommended method for loading large data sets
– DBMS_CLOUD to load data is not applicable for ATP-D because DBMS_CLOUD is not available on ATP-D

– In ATP-D, the database version is 19c which is required for Auto-Indexing which is on by default
– Support for 19c / Auto-Indexing on ATP-S is on the roadmap

– ADB (serverless) does have auto-scaling – you can select auto scaling during provisioning or later using the Scale Up/Down button on the Oracle Cloud Infrastructure console
– ATP-D does not have auto-scaling support

Here are the 5 main ATP-D physical characteristics and constraints:

1. Quarter rack X7 Exadata Infrastructure:
– 2 severs: 92 OCPUs and 1.44TB RAM
– 3 Storage Servers: 76.8TB Flash and 107TB Disk

2. Cluster / Virtual Cloud Network:
– 1 Cluster per quarter rack

3. Autonomous Container Database:
– Maximum of 4 CDBs per Cluster
– The default data and temporary tablespaces for the database are configured automatically
– The name of the default data tablespace is DATA
– The database character set is Unicode AL32UTF8
– Compression is not enabled by default – use the table_compression clause if needed

4. Autonomous Database:
– High Availability SLA – Maximum 200 DBs
– Extreme Availability SLA – Maximum 25 DBs
– Placement Logic – Open on 1 server < 16 OCPU

5. Types of Users: Fleet Admin, Database Admin and Database User as Fleet Admin activities separated from DB Admin using IAM privileges

Note that now, there are 2 tabs for possible database connections – the serverless style DB connection and application connection:

Fleet Administrator: Fleet administrators create, monitor and manage Autonomous Exadata Infrastructure and Autonomous Container Database resources – a fleet administrator must be an Oracle Cloud user whose permissions permit the management of these resources and permit the use of the networking resources that need to be specified when creating these resources

Database Administrator: DBAs create, monitor and manage Autonomous Databases. Additionally, they create and manage Oracle Database users within these databases, and provide others the information necessary access the database – when creating an Autonomous Database resource, the DBA defines and gains access to the ADMIN administrative user account for the database

Database User: Database users are the developers who write applications that connect to and use an Autonomous Database to store and access the data. Database users do not need Oracle Cloud accounts: they gain network connectivity to and connection authorization information for the database from the database administrator

Few useful links:

A Using Oracle Database Features in Autonomous Transaction Processing
FAQs for Autonomous Transaction Processing – Dedicated
Data Center Regions for PaaS and IaaS
Oracle broadens the audience for Automated Transaction Database
2 Ways Oracle’s Autonomous Database Just Got More Useful

Bottom line: even with Autonomous, DBAs will be still needed!

Automatic Indexing in 19c

In Autonomous, Database tuning, Databases, DBA, Oracle database on February 18, 2019 at 17:38

One of the most impressive new features of Oracle Database 19c is Automatic Indexing. Arguably, this is the most interesting innovation in the database world for a rather long time.

I remember some years ago when a DBA asked me at an Oracle conference: “Julian, why are half of the presentations at Oracle database conferences only about performance tuning? Is the Oracle database performing that badly that people should tune it all the time?” Sigh…

With 19c and ADB (Oracle Autonomous Database), things look very different now, don’t they? Automatic Indexing provides what database systems need: continuous optimization of the database workload, stable & solid performance and almost no human interaction. Let me share some of my early experience with Automatic Indexing and where human interaction is needed.

For now (February 18th, 2019), Oracle 19c is only available on Exadata (Linux 7.4) and in order to enable Automatic Indexing you need to do the following:

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');

As you can guess from the picture above, the so called expert system of Automatic Indexing runs every 15th minute for as long as one hour. Note that I disabled the job from 4:43 till 5:56. The Resource Manager plan limits the task to 1 CPU only and the next run is skipped if the job has not been completed within the 15 minutes.

Here are details on how Automatic Indexing works but what is most important to remember is as follows:

– The auto index candidates are created as invisible auto indexes
– If the performance of SQL statements is not improved from the auto indexes, then the indexes are marked as unusable and the corresponding SQL statements are blacklisted
– Auto indexes cannot be used for any first time SQL run against the database
– Auto indexes are created as either single, concatenated indexes or function-based indexes and they all use advanced low compression
– The unused auto indexes are deleted after 373 days (can be changed)
– The unused non-auto indexes (manual indexes) are never deleted by the automatic indexing process but can be deleted automatically if needed

The Auto Indexing can be disabled at any time or can be set to set to reporting mode (new auto indexes as created asinvisible indexes, so that they cannot be used in SQL) with the following commands:

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','OFF');

 

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','REPORT ONLY');

Here is a way to ask Oracle to create new auto indexes in a separate tablespace called AUTO_INDEX_TS:

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_DEFAULT_TABLESPACE','AUTO_INDEX_TS');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01

You can easily check the configuration for Automatic Indexing for the root container and the PDBs from CDB_AUTO_INDEX_CONFIG;

If you need a report of what happened during the expert system activity (either last 3 days or during the last activity), here is a way to generate it:

set long 300000
select DBMS_AUTO_INDEX.REPORT_ACTIVITY(SYSTIMESTAMP-3,SYSTIMESTAMP,'TEXT','ALL','ALL') from dual;
select DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY('TEXT','ALL','ALL') from dual;

These are the most important views about Auto Indexing:

DBA_AUTO_INDEX_EXECUTIONS: history of execution of automatic indexing tasks
DBA_AUTO_INDEX_STATISTICS: statistics related to auto indexes
DBA_AUTO_INDEX_IND_ACTIONS: actions performed on auto indexes
DBA_AUTO_INDEX_SQL_ACTIONS: actions performed on SQL statements for verifying auto indexes
DBA_AUTO_INDEX_CONFIG: configuration settings related to auto indexes
DBA_AUTO_INDEX_VERIFICATIONS: stats about PLAN_HASH_VALUE, AUTO_INDEX_BUFFER_GETS, etc.

The new package DBMS_AUTO_INDEX can be used for 3 main things:

1. Configuration of the parameters related to Auto Indexing
2. Drop *all* the indexes except the ones used for constraints
3. Report the activity of the “expert system”:

Finally, here are some additional resources:

Automatic Indexing in Oracle Database 19c
Oracle Database 19c is now available!
Managing Auto Indexes

How to check if I have any auto indexes in my database: select auto, count(*) from dba_indexes group by auto;

Few interesting facts about Oracle ADB, Redshift and Snowflake

In Autonomous, Data Warehouse, Databases, DBA on January 14, 2019 at 16:17

Building a new data warehouse in the cloud or migrating an existing one to cloud requires careful consideration and the answer to the question “Which cloud should I use?” is often “It depends”.

An interesting comparison of system properties comparing Amazon Redshift vs. Oracle vs. Snowflake can be found on db-engines.com

There are several other options too: Azure SQL Data Warehouse, Presto, Google BigQuery, etc.

An interesting benchmark paper called “Data Warehouse Benchmark: Redshift, Snowflake, Azure, Presto and BigQuery” by Fivetran is worth reading!

Another comparison called Interactive Analytics: Redshift vs Snowflake vs BigQuery is already more than 2 years old but still interesting.

Recently, things have changed. Oracle’s Autonomous Data Warehouse Cloud has been in GA for almost 1 year (since March 2018). ADW is for enterprise loads and mission critical systems arguably the best solution right now.

Viscosity compared both Oracle Autonomous and Amazon Redshift. The result? Check it here: Amazon vs Oracle: Data Warehouse Services, How do They Compare?

In short, the conclusion of the research above is:

– Oracle’s ADW was able to achieve data retrieval at the lowest latencies, and achieved the highest volume of queries per hour. In terms of serial query execution and multi-user query throughput.
– Oracle’s ADW consistently outperformed Redshift by a factor of 4x in both sets of tests.

And do not ignore the db-engines ranking! Only one of the three is in the Top 10.

What is interesting to know on top of all papers above are these 10 differences or let us call them less known technical facts (in no order of importance) between Oracle Autonomous, Amazon Redshift and Snowflake:

1. Snowflake compute usage is billed on a per-second basis, with a minimum of 60 seconds. Amazon Redshift is based on PostgreSQL 8.0.2 and is built on top of technology from the MPP data warehousing company ParAccel. Oracle Autonomous Database is based on Exadata and 18c.

2. In Oracle Autonomous Cloud, you can provision up to 128 CPUs and 128TB directly from the cloud console but you can provision more if needed.

3. Snowflake manages all aspects of how data is stored in S3 including data organization, file sizes, structure, compression, and statistics.

4. The only things needed for BYOL in Oracle Autonomous Database are Multitenant and RAC (only when using more than sixteen OCPUs). The standby option (not yet available) will require Active Data Guard as well.

5. Snowflake does not disclose the information about processing power and memory. Oracle do disclose the information via internal views but you cannot directly define the SGA or PGA size.

6. Redshift is not built as a high-concurrency database with several concurrent running queries and AWS recommends that you execute no more than 15 queries at a time. The number of concurrent user connections that can be made to a cluster is 500.

7. Oracle ADW and ATP allow you to partition both indexes and tables. In Snowflake partitioning is handled internally. Amazon Redshift does not support tablespaces, table partitioning, inheritance, and even certain constraints. Amazon Redshift Spectrum supports table partitioning using the CREATE EXTERNAL TABLE command.

8. The maximum number of tables in Amazon Redshift is 9,900 for large and xlarge cluster node types and 20,000 for 8xlarge cluster node types. The limit includes temporary tables. An Oracle database does not have a limit for the number of tables.

9. Oracle automatically applies all security updates (and online!) to ensure data is not vulnerable to known attack vectors. Additional in-database features like Virtual Private Database and Data Redaction are also available.

10. There is no operation in Snowflake for collecting database statistics. It is handled by the engine. In Oracle, database statistics collection is allowed. Both Oracle Autonomous and Amazon Redshift monitor changes to your workload and automatically update statistics in the background.

Finally, here are official URLs of all three products:

Oracle Autonomous Database
Amazon Redshift
Snowflake Database