Monday, November 11, 2013

Database -- Oracle Database 12C Briefing -- notes, comments.

I attended a Partner Briefing about Oracle Database 12C recently. The briefing was delivered by François Pons, who is a Senior Principal Technologist at Oracle .. The briefing was based on several presentations about the new features of Oracle 12c , as well as the comparions between Oracle 12c and the older releases. I must say that, this was one of the most useful  Partner organization I have ever attended. Mr Pons clearly described and covered all the topics in the context in 2 days, with a fast but effective way.. He even showed and proved the concepts with real examples by doing little workshops at the end of each lecture.
During the lectures, most of the time, I've written down the hints and tricks that Mr Pons have mentioned, and the comments (which are very important in my opinon) that he have made.
In this post, you will find my notes related to Oracle Database 12C Partner Briefing in Istanbul ..

Note that: This post covers my notes , and sometimes my comments. If there are any mistakes, you can get in contact with me, I can correct them.

  • Total Recall no longer exists. There is Flashback Archive, and it is even available in Standard Edition..
  • In 12C , there is an option called In Database Archiving.. This option can be used the reach the old data, which is the data that is not frequently queried ..
  • The In-Memory-Option introduced in 12C and TimeSten are two different things.

Following is a comparison between In-Memory Option and Timesten..

  • Oracle 6i introduced Row Level Locking
  • Oracle 7 introduced PLSQL.
  • Oracle 9i introduced too many changes and features.. These were there for RAC actually.. Undo segments instead of Rollback Segments, dictionary to local managed tbs..These changes and features were produced, because they were suited for Rac actually.
  • With Oracle 10g, the managebility is improved. (for example automatic stats gathering)
  • Oracle 11g brought us an improved environment to service for Large Databases as well as Small databases. 11g handles the databases with different sizes very well, Everything was better.
  • Oracle 12c improved the capabilties of Oracle 11g to support Big Data, large Db's and Engineered Systems(Especially Exadata). 12c can handle Larger Clusters and have Larger Rac support.. It introduced the Container Databases and Pluggable Databases to support consolidation.. It has brought a new architecture.. (PDB and CDB) Optimizer also have improved .. There are major changes in Oracle Optimizer.. The optimizer have become a self-learning beast.. It learns the right way of doing something by looking its mistakes ..
Following picture describes the Oracle Database 12C in a different way.. As you see, CDB/PDB is in the chest of the man in the picture.. This is because, Container Database and Pluggable Database concepts are the major innovations in 12c architecture.. 

12C or 11gR2 software can be installed in to Exadata Compute Nodes.. In fact, 12c and 11gR2 can be installed into Exadata at the same time , but It seems it s better to install and use 11gR2 software on Exadata for now, because the storage cell's cant do Smart Scan if the software in compute nodes is 12c. This is because of the incompatability..(compute nodes 12c, storage cell 11gR2).. Especially if the databases in Exadata are Data Warehouses, this will create a performance impact, because DW type workload takes advantage from the Smart Scan feature of Exadata. On the other hand; if the databases in Exadata are OLTP, 12C can be used, because OLTP type workload dont use Smart Scan so often..

Container/Pluggable Databases can be used even in Stardard Edition ,but only one Pluggable Database can be created..
Normally, 252 Pluggable Databases (PDB)can be created in one Container Database(CDB). Every PDB has a unique PDB number. This PDB number is stored in one bayt. There are also a root DB and a seed PDB in every CDB.. Seed PDB is like a template, used when a new PDB is created..  These PDB 's have unique PDB numbers  too..In addition to that, there may be one more reserved PDB number.. So if we calculate > 1 byte's worth - reserved pdb numbers makes 252 PDB numbers available..

12C supports both old architecture(non-cdb), and new architecture(cdb/pdb)..
The new architecture is not supported with some of the options .. For example ADO is not compatible with Pluggable Databases.. If you want to use ADO, you need to use the old architecture..
On the other hand, 12Cr2 seems promising about the support.. All the options will be compatible with 12Cr2..

In 12C v$ views have extra columns. For example, you can understand whether you are connected to a PDB or a non-CDB database.. You can gather this kind of information by querying V$ views.

PDB's in 12C use a common SGA and a common set of background processes. Every PDB has a service name. That 'is, OS authentication is not supported for connecting to PDB's. With SGA, the Buffer cache is shared among the PDB's.  So, there are blocks from multiple PDB's in the Buffer cache .. Every Block holds a PDB ID information, Oracle uses this information to decide,  which PDB a particular block belongs.. LRU applies as always.
PDB and non-CDB databases are fully compatible.For example, you can create a database link in a PDB, just like you create a database link in a non-CDB database.. 

In the new Architecture, every PDB has its own metadata to store the user information..
The old architecture does not scale as well as the new architecture..

DataGuard in new Architecture is provided on CDB level. DataGuard is a thing that is hard to configure.. Configuring and managing it on CDB level at least minimizes the work.. The same thing applies for redologs and undo, they are on CDB level too.. Dataguard and tools like Goldengate use redologs to operate.. All the other thing can be on PDB level.. Every PDB has SYSTEM,SYSAUX and TEMP tablespaces ( TEMP is optional, it can be on CDB level, too.. Undo and Redologs must be on CDB level..)

By using Enterprise Manager 12c, dba s can do a lot of admin work for managing CDB's and PDB's. (For example: creating PDB)
PDB's can be cloned .. A new PDB can be created by taking a clone of an existing PDB..

If the CDB is on a filesystem which has copy-on-write feature,cloning can be an instant operation..
Of course, cloning can be always done using the old copy method, too(cp from dir to dir).

Copy-on-write supplies the snapshots ( Like in Netapp SAN). If a filesystem has the ability to make copy on write, then it support snapshots, too..
Copy-on-write : Writes to the disk does not update the existing block.. The new data is written in to a new block and a pointer is created to the existing block, recording and keeping the old block available for the snapshots.. Thus, you can create a snapshot from the old blocks using these pointers.. You can quickly mount these snapshots to the server and use the old data if needed.. An example for the snapshot concept is ZFS(solaris)..

In 12C, database files can be placed in ACFS, but there can be a performance impact.. Note that , placing datafles in ACFS is not supported in 11G..

Every PDB has a DNA. That is , every PDB knows its history (For example, PDB knows its own creation time)..
PDB's can not be migrated from version to version.
PDB's can be migrated from CDB to CDB.. (to a remote CDB or local CDB does not matter)
If you are using a shared filesystem(like ASM), a PDB can be migrated from a local CDB to remote CDB without even copying the files..

PDB is put into quiesce mode , while cloning.
To create a local clone -> create plugable database ERMAN from ERMAN2;
To create a remote clone -> create plugable database ERMAN from ERMAN2@ERMAN.WORLD;

Every CDB has a seed PDB inside of it. The new PDB's are created from the seed PDB. Actually, seed PDB is copied when you create a new PDB. That is why, the PDB creation process is very fast.. Seed PDB 's are like templates. You can create your own seed PDB's(template) if needed.

PDB 's can be unplugged and plugged. Unplug/plug operation differs from cloning.. Unplug & Plug PDB operations are something like migrating a database. For example , if you need to upgrade your database version from 12CR1 to 12CR2 , you can unplug your DB's from 12CR1 and plug them into the 12CR2.. During this upgrade operation , your data will not be touched. If your data is on a shared disk, the operation can even be logical..( without a need to copy anyting) 
The command for this operation is something like below;

Alter pluggable database HCM unplug into ‘full path of a file/erman.xml’,
When you unplug a pluggable databse, the information needed to plug HCM database is written to a xml file.. When you plug the database into a new CDB, that xml file is read by the Oracle.

There are users on PDB level(local users) as well as CDB level (common users).

Common users are supplied by Oracle.(SYS,SYSTEM...) These users are created when the CDB is created. Common Users can connect to any PDB and access the tables on the PDB's. To prevent this, you should use the Database Vault..
On the other hand; Local users on a PDB can not access the tables on other PDB's , even if they are granted as DBA's.
You can create users in CDB level if needed. The usernames of these kind of users should start with C##.. For example: create user c##DBA identified by pwd container=all

In the new architecture, sessions are created in the same way as they are created in old architecture. Only thing you need to supply is the service name, when you are connecting to a PDB. The connection requests are still handled by the Listener..

You can assign a PDB to work on a specific Rac node. You can dedicate one specific node for a particular PDB..  In addition, You can say " dont share my sga with others"  ..

Patching get easy with the new architecture..You can upgrade in place by patching the CDB, or you can create a patched CDB and use unplug&plug method for upgrade..

If you need extra resources for your PDB's, you can add more nodes on the fly .( this is already a  RAC feature)

You can use Resource Manager to control the CPU,IO,Sessions and Parallel Execution Servers of the PDB's.

To upgrade your database from 11g to new 12c architecture;
First; upgrade your 11g database to 12c (non-cdb)
Next,put your database into readonly mode and create PDB manifest file.(this file will be used in plugging operation)
Finally, purge your  old medata , as it will be obsolete after upgrading to 12c new architecture.. 
Note that, as an alternative; you can always use export/import method for these kind operations..

Full transportable database option is introduced in 12C. (note that: expdb/impdb, and exp/imp can be used as an alternative.)
Expdb is a new feature that can be used to export the whole database. It is introduced in 12C and it is backported to or

ASM is introduced in 10g for performance and managebility..
A filesystem can be created on ASM, this is called ACFS. ACFS is a copy-on-write filesystem that supports snapshots.

Why ASM has a good performance?

1) ASM uses block devices. It does not use a filesystem.. So, when you use ASM, you get through a very thin layer of code on the OS, as ASM sees the disks as block devices.  Thus, less code is executed in the OS layer.
2)Allocation units(Multiple of MB) are used for read/write operations. ASM writes blocks in multiple of MB. Larger IO's but fewer number IO's.
3)ASM uses Direct IO .. It does not use a cache. Without ASM, yhen you write, OS writes it into the cache, but Oracle has a cache too.. These caches ignore eachother.. This situation ends up with double buffering.. We dont have double buffering in ASM.
4)Code Path

Whey ASM is more managable?

You can add more storage, take off disks and rebalance on the fly.
Automatic rebalancing makes you not to optimize IO's anymore.
On Linux , we have asm libraries. Before the installations , you can prepare disks for ASM configuration. By doing so, you will not encounter any problems when you install ASM.

In 10G, ASM uses the same binaries, which the database software uses..
In 11G, this has changed. ASM uses its own binaries. They are supplied with clusterware - grid infrastructure.
This is because;
1)Seperation of duties; You can give the control of ASM to the system engineers.. (not dba 's)
2)Rac..  You can build a RAC with or without ASM. You install the clusterware to be able to do that.
On the other hand; the clusteware puts its files in to the ASM .(voting disks , OCR and etc.) So if you want to install the clusterware, there should be ASM to put this files.. To address this problem, Oracle has put Oracle ASM, Clusterware and even the listeners together and build a single infrastructure, called Grid infrastructure.
Before 12c, there was a communication between ASM and Database.. For example, when you create a database object...
Actually, database writes to disks directly, so ASM is not a bottleneck.. It works like a listener..
When ASM crashes, database does not crash immediately.. The database crash after a time as the ASM works like a listener.. (When a new connection needed.) 
This seems like a problem..
For solution, Oracle introduced a new feature called Flex ASM in 12C.
Now, when you install the ASM, you can select the Flex ASM option.
In 11g , the authentication method used in ASM connection was the OS authentication.On the other hand; In 12C, DB and ASM communicate through Oracle Net authentication. By this means; a database can connect to other remote ASM's too.. Now, the password file must not be on the database Oracle Home for remote sys connection.. This need was because the OS authentication.. In 12C, we dont put the password file in to Oracle Home in 12C.  Even, the normal ASM (not FLEX) uses Oracle Net communication  in 12C.

You can consider a FLEX ASM as a RAC environment.. When a ASM node crashes , the related ASM instance can start in another ASM node.. As you predict ,Databases can connect to any ASM instance in a FLEX ASM environments..

With FLEX ASM, you can create 511 Disk Groups..(The limit was 63 in the past)
Note: Creating diskgroups per database is not the best practice in ASM. Diskgroups should be created per environment. For example : 1 diskgroup for PROD databases, 1 diskgroup for TEST databases..

In FLEX ASM, there are hub nodes and leaf nodes. Databases and ASM can not be run on the Leaf nodes. Leaf nodes are used for applications. Leaf nodes have VIP'S associated with them.
Clusterware controls the applications using this VIP's. It can stop/start applications if a database is just opened or closed.(this is the main reason) So , clusterware can control the applications in a way.

In 11G, read operations can be done using the primary copy.. In 12C, read operations can be done using all available copies on the environment. This is an optimization, that can improve the performance of ASM IO's.

Note: When you use external redundancy in ASM, ASM does not do mirroring, it spreads the data in to disks, as the data is mirrored on a different level ..(on storage)

In 11G, the database file can not be placed on the ACFS. ACFS is licence-free..
In 12C , there a filesystem called CloudFS. It s like an ACFS,technically. But in Cloudfs, you can place datafile on the filesystem too.. ACFS is free , but if you need to place your datafiles on it, you have to use avanced options .. These options requires licences.. (Options like snapshots, reset database from snapshot and etc..)
ACFS can be mounted to several nodes, but these are the nodes that the grid infrastruce is running on..
In 12C, ACFS/CLOUDFS can be mounted to the servers that does not have a grid infrastructure.. This supplied by the HAN(High Availability) File System.
HANFS can be exported to the clients using Vip addresses. By the use of the Vip's, High availability is supplied .

In brief, we cant say it s a revolution but ASM and RAC in 12C bring a lot of enhancements ..

ASM is a very important recommendation if your database is a critical database. In addition, if your database is big, paritioning is another important recommendation.. Partitioning increases both the managebility and performance.. Compression is also another recommendation made by Oracle for these kind of databases..

So the recommendations are as follows;

When you combine these 3 recommendations , you obtain the ILM(Information Lifecycle Management)

12C brings Automatic Data Optimization(ADO).. ADO is used for automating the compression and movement activites in the database. For example: ADO automatically moves a partition from Diskgroup to Diskgroup, or it compress a partition automatically. In 11g these kind of operations have been done manually.

Oracle 9i introduced the basic compression. Basic compression have been useful for queries and had constraints.. For example, you could only query a compressed table , you could not do an update on it.
Advanced Compression was introduced in 11g(11.1) -- free on enterprise edition.  DML operations can be done on a table which is compressed with Advanced Compression.
11.1 -> alter table compress for all operations.. (old syntax, 1st version of advanced compression)

Advanced compression is transperent, efficient and works asynchronously. That s why, it does not decrease the performance of update/insert/delete operations.. When you insert a data in to the block, you dont need to wait for the block to be compressed, you just continue inserting to the next block...Compression is done asynchronously.. 

Hybrid Columnar Compression on Exadata can compress the data for oltp  (15x), and for archive (50x)
In HCC, the data is reorganized in the columnar form. The data is seperated in to chunks and it s reorganized chunk bu chunk.. That is why it s called Hybrid.. By doing so; the repeating records can be compressed better..
In 12C , there is a new syntax for compression.. alter table row compress or alter table column compress;
There a re new names for compression methods.. Advanced Row Compression, Lob Compression and etc.
Little by Little, Oracle start to do some favours for some spesific platforms like Exadata.. Platform specific options are the proofs for that.
Using Logminer and GoldenGate against compressed data, become supported with Oracle 12c.. Before 12c ,for example: Goldengate could not read the compressed blocks..
In 12c the limitations like max column count (255) are no longer exist..
Heat Map(hot->cold) is introduced in 12C.Heat Map is used for tracking the changes in data blocks to decide whether a block is hot or cold.. Oracle decides what to do with the blocks according to Heat Map's findings.. Note that: Heat Map is not supported in pluggable databases.If you want to use Heat Map, you need to use the old architecture)
Heat Map is disabled by default. You can enable it with the following;
Alter system set heat_map=’ON’ scope=both;

Automatic Data Optimization works through policies. For example : A policy can be the following;
"Compress partitions which have not been  modified for 30 days. If this particular partition have not been modified for 120 days, compress it further. If this partition have not beed modified for 180 days, make the related tablespace readonly".. Note that : this requires an advanced compression license.

Oracle can compress the data online. In 12c, moving a partition or a database file are online operations,too.
Note that: Queue tables should not be compressed, as it is pointles.. 

Interval Reference partitioning(master and child relationship) is introduced in 12c. 
Partition drop and truncate operations complete immediately. You dont need the statement "update indexex) anymore, Oracle does it automatically and asynchronously. In 11.2 , suppose an update indexes operation completes in 2 minutes. In 12C, the same operation completes in 2 seconds. Oracle does this index maintanence operation thorugh a list. In other words, Oracle knows the index blocks those need to be touched, and operate on those blocks only..

Before 12c, every select operation you execute was a little bit in the past..(consistency)
Actually, you see the data in the past -- in the point of time that you executed the select(by the help of undo)
Note: Undo supplies an isolation mechanism to the Oracle Database. Not commited transactions should not be seen by others. Flashback uses Undo , too(as of timestamp, Scn queries)
Flashback Data Archive holds the undo for a long time . Thus , you can query the very old data..  
What happens here is, when the undo data become obsolete/aged out, Oracle puts this undo data to the Flashback archive...(note that : a retention window mechanism applies for Flashback archives,too..)

In 12C, when you put a table into archive, you can still add columns (no schema limitations) .. That is, you are not blocked from modifying the table strructure just because the table is in a archive.
In addition, user context tracking is supplied. Information like the username that modified the table, can be put into the archive..
Temporal Support>Valid Time Temporal > Querying with Time validity clause is used for historically querying .. But these feature is not like a Flashback query. The mechanism works based on the Start and End columns and returns the data. These columns are hidden, but when you query the table you get the valid rows based on these column values.. This feature makes the life of a custom application developer easier. It minimizes the custom code, especially if the application does its job based on some time intervals..

12C Note : When you create a new column with a default value, we do not modify the table itself..

In-Database Archiving introduced in 12C is the best new feature for some of the big customers of Oracle.
When you execute a command like alter table – row archival > it automatically creates a hidden column > ORA_ARCHIVE_STATE = 1. When you select from that table, just like a VPD, oracle adds a ora_archive_state=0 to the end of your select statement.

Identity Columns in 12C are like a combination of the sequences and triggers. The columns are automatically gets valued , it actually create  sequences, but the identity column feature is more efficient than the triggers.

12C introduced extended size limit (32K) for some column types;
Create table with Varchar 32k >alter system set max_string_size = EXTENDED.
Note that : The COMPATIBLE initialization parameter must be set to or higher to set MAX_STRING_SIZE = EXTENDED.
max_string_size controls the maximum size of VARCHAR2, NVARCHAR2, and RAW data types in SQL.
standard means that the length limits for Oracle Database releases prior to Oracle Database 12c apply (for example, 4000 bytes for VARCHAR2 andNVARCHAR2, and 2000 bytes for RAW).
Extended means that the 32767 byte limit introduced in Oracle Database 12c applies.
Note that : When you set the max_string_size , you can not take it back.. This approach is more efficient than CLOBs.

A Swiss knife can be a good synonym for Oracle Sql Developer .

We have 2 options for managing the Rac Nodes. 1 )Admin managed Rac Datatabase 2) Policy Managed Rac Database.

In 11G, when you create a RAC, you can select admin managed. With admin managed, you can  adjust the system. You can declare your services to run on specific nodes or on several nodes.. As you see, these kind of declerations are made by the admin, so this is called admin managed. This approach is good if you have a cluster with a few number of nodes (4 for example)
On the other hand; If you have several number of nodes in the cluster, you need to choose Policy Managed. Server Pools are used in this method. You can declare policies like below;
I have a Production database. This database has a production server pool. This server pool has 4 servers. Minimum number of servers in server pool is 3, max number of servers in server pool is 6.
So, by the use of these kind of policies, you gain flexibility. In the above example, If the environment has available number of servers, 6 servera can be dedicated to production server pool.. If the environment has difficulties,  3 servers can be dedicated to production server pool.
Even you can declare the following;
I have an application in production env.. If this application needs some resources, detach the servers from the test server pool, and attach them into the production server pool..

Note: In Rac, if you want to break the comminucation between an application and a database; stop the related service not the listener. Thus, admin operations can still be done by the administrators using the listener..

Rac supplies database continuity not application continuity..
Transaction Guard can track the transactions.. It takes the transaction at the lowest level and store it on the application side. It can then supply a mechanism to Oracle to be able to ask to question:
Was my transaction commited, or not? 
By the help of this mechanism, you can prevent the same update to be done twice.. 
Transaction Guard is an Api. Application Continuity, on the other hand; is a higher level thing..  Application Continutiy uses Transaction Guard to track the transactions .( using jdbc-thin,OCI,OCCI,,
In the absence of Transaction Guad > In worst case scenario> network comminucation problem can make the client to execute the same transaction again, altough the first transaction had been commited... The reason here is that the commit message could not be transmitted to the client, it was lost on the network layer and this situation could not be handled..
In 12C, we have replay. The jdbc thin driver is intelligent. Suppose you made insert and the database crashed just after that. Jdbc driver handles this by creating connection to one of the remaining nodes. It executes the commit of the first statement again. Then it continues with the second transaction. After this points it delivers the message to the client. End user only experiences a delay..  This was not possible in 11g. Application Continuity in 12C works like this..
Note that , this is not supported in .Net, yet. It s only supported for java.. 
Note that, this method can not be implemented for every transactions, even in 12C..

UCP stands for Universal Connection Pool. It supports multi database connection(sqlserver ,oracle and etc..) It s delivered in a jar file. It s an extraordinary feature. UCP is a requirement if you want to use Application Continuity and Replay.

Data Guard Far Sync in 12c reduces the risk for Data Loss. You use the sync method between primay and far sync instances.. You use async method between far sync and standby instances.  Thus, you supply near-zero data loss without affecting the performance..
The new Data Guard can supply level 2 standby mechanism , better than the old releases..
In 12C dataguard, when the archive is transmitted, an acknowledgenment message is delivered immediately. In other words, primary does not have to wait for archives to be written into local redologs.( Oracle guarantees this write operation and continues asyncronously..)  That's why , 12C 's DataGuard is faster than 11G and older releases.
In 12C, Rolling upgrade can be done easier using DBMS_ROLLING package . 
As you know, Active DataGuard can open the database readonly.. On the other hand, the sophistaced reporting tools can not work with a readonly database. Because, this kind of tools want to write in to database.. To address this problem, you can use global temporary tables.

12C Rman can recover  a tablespace in point time.
12C Rman can recover  a tables in point time. recover tabe command..
12C Rman can migrate a database accross platforms using backup and recovery operations, even between the platforms with different the endians..

In the old good times, we use sysdba.. 11g introduced sysasm. 12C introduced sysbackup for rman, sysdataguard for dg broker.. 

Enterprise Manager 12C does not use any middleware product.. It works completely in the database. It uses XMLDB infrastructure for webservices. It uses listeners for connections.. Note that listeners can listen on http and https.
There is no more need for Enterprise Manager configuratin assistant. RAC, SI, SE and EE support EM.
Enterprise Manager Database Express 12C is not fully compatible with PDB and CDB architecture. Maybe in next release, it will be..  

In 12C , ADDM can run on almost real time. ADDM is triggered through some conditions. For example,  if run queue length is 3x CPU count > ADDM automatically runs.. This mechanism supplies an opportunity for  diagnosing the problems instantaneously.

Data Redaction is used for masking the sensitive data. It can be implemented on the fly without touching the applications. Redaction means automatically change..
Redaction can be Full , Partial , Regexp or Random .
For example : With partial redaction, you can display the last 4 digits of the data -> XX-XXX-1111
You use DBMS_REDACT.ADD_POLICY to add this kind of policies.
When you declare a redaction for a column, it is immediately activated by Oracle.
Note that: you cant use Create Table as Select from a table, if your user is affected by a Redaction policy which is associated with that table.. If you do this, you end up with insufficient privileges error.
Redaction have been backported to 11g(11.2.04)..

You can use privilege analysis to determine the needed privileges for a database user ..You can use a PLSQL for tracking the user activity and the usage of the privileges.. On this method, the user privilege data is monitored and captured for some time.. Then, by analyzing this data , you can revoke the unnecessary but granted privileges from the user in question.
Privilege analysis can not be used without a Database Vault.. To able to use the privilege analysis , you need to have a Database Vault license.

12C improves Optimizer, too.
In the old releases, Optimizer was executing the first plan that it generates.
When the CBO was introduced, Optimizer became more intelligent. CBO have used the statistics and calculated a cost for every plan..By looking these cost, CBO have been able to choose the best plan for executing an operation.
Being fast is an important need for Oracle optimizer. Optimizer have to decide very fast. This need have always created some problems, like choosing the wrong plans or not being stable..For example, when you upgrade your database, you end up with changed execution plans, which can lead performance issues.
In 12C, optimer becomes a proactive and self-learning thing.. Oracle's have been always  to use memory and not to use Cpu's.
12C introduces adaptive query optimization for optimizer.This is a big change in Optimizer.With this feature, optimizer can make adjustments on runtime. For example, Optimizer decides  a query to use a nested join.. After a while, when the query is running, Optimizer detected that a hash join may be better for this query .. In this case, after last nested join operation, Oracle can switch to hash join to achieve a better peformance on this particular query execution. This is called using an adaptive plan.. When an adaptive plan is used, you can see it in Execution plan.. So in brief , the final decision is made by the statistics gathered during the runtime.
You can disable adaptive query optimization by setting , OPTIMIZER_ADAPTIVE_REPORTING_ONLY parameter to TRUE.
OPTIMIZER_ADAPTIVE_REPORTING_ONLY controls reporting-only mode for adaptive optimizatons.
When OPTIMIZER_ADAPTIVE_REPORTING_ONLY is set to FALSE, reporting-only mode is off, and the adaptive optimizations are enabled as usual.
When OPTIMIZER_ADAPTIVE_REPORTING_ONLY is set to TRUE, adaptive optimizations run in reporting-only mode. With this setting, the information required for an adaptive optimization is gathered, but no action is taken to change the plan. For instance, an adaptive plan will always choose the default (optimizer-chosen) plan, but information is collected on what plan to adapt to in non-reporting mode. This information can be viewed in the adaptive plan report.
This parameter affects only adaptive optimizations that are enabled.

There are improvement in deciding whether to use parallel execution servers. Before 12c, we have decided the object that are scanned in parallel ( with a hint, or alter table parallel)
In fact, If the jobs can not be distributed equally among the parallel servers, parallel server mechanism becomes inefficient. 
For example: Parallel server 1 scans partition 1 which has 10 rows, Parallel server 2 scan scans partition 2 which has 1 million rows.
In 12C There are improvements in this distribution process, too. There is reoptimization by using cardinality feedback ( it is there for 11g also)
Optimizer counts the rows on runtime. Suppose , we expect 3 rows, but the rows we got is more that 3. So in this situation the related cursor is marked as unusable. The next execution of the same statement triggers a new explain plan to be generated. In 11g , this is not done for the join operations, it is done only for tables.. In 12c, it can be done for joins, too.. It works even with the binds variables..

Important Note about CDB:  You can license in container level. For example: you can license partitioning in CDB level. This license become valid for all PDB's in that CDB. There is a trick here. Based on this example, we should put the databases which use partitioning together in to the same CDB. By doing so, we will only have to buy a partition license for the particular CDB. Otherwise , we need to buy more licenses as the PDB's,which require partitioning, will be on different CDB's.

No comments :

Post a Comment