DB12c - Session-private statistics for global temporary tables

In previous releases, the database did not maintain statistics for global temporary tables and non-global temporary tables differently.

The database maintained one version of the statistics shared by all sessions, even though data in different sessions could differ.

Starting in Oracle Database 12c, you can set the table-level preference GLOBAL_TEMP_STATS to make statistics on a global temporary table shared or session-specific. If set to session-specific, then you can gather statistics for a global temporary table in one session, and then use the statistics for this session only. Meanwhile, users can continue to maintain a shared version of the statistics.

During optimization, the optimizer first checks whether a global temporary table has session-specific statistics. If yes, the optimizer uses them. Otherwise, the optimizer uses shared statistics if they exist.

SQL> select banner from v$version where rownum=1;

BANNER

--------------------------------------------------------------------------------

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

Session 1

SQL> create global temporary table new_gtt on commit delete rows

  2  as

  3  select * from all_objects where 1=2;

Table created.

SQL> insert into new_gtt select * from all_objects;

77381 rows created.

SQL> SELECT DBMS_STATS.GET_PREFS( 'GLOBAL_TEMP_TABLE_STATS','HORIA','NEW_GTT')

  2  FROM DUAL;

DBMS_STATS.GET_PREFS('GLOBAL_TEMP_TABLE_STATS','HORIA','NEW_GTT')

-------------------------------------------------------------------------------

-------------------------------------------------------------------------------

-------------------------------------------------------------------------------

SESSION

SQL>

SQL> exec dbms_stats.gather_table_Stats('HORIA','NEW_GTT',METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO')

PL/SQL procedure successfully completed.

SQL> select /*+ gather_plan_statistics*/ count(*) from new_gtt;

  COUNT(*)

----------

     77381

SQL> select * from table (dbms_xplan.display_cursor(format=>'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------

SQL_ID  8432nj7acjwpx, child number 0

-------------------------------------

select /*+ gather_plan_statistics*/ count(*) from new_gtt

Plan hash value: 1213234781

----------------------------------------------------------------------------------------

| Id  | Operation          | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |         |      1 |        |      1 |00:00:00.01 |    1313 |

|   1 |  SORT AGGREGATE    |         |      1 |      1 |      1 |00:00:00.01 |    1313 |

|   2 |   TABLE ACCESS FULL| NEW_GTT |      1 |  77381 |  77381 |00:00:00.01 |    1313 |

----------------------------------------------------------------------------------------

Note

-----

   - Global temporary table session private statistics used

18 rows selected.

We can see that the session private statistics were used.

When querying the user_tab_Statistics we can see the entry specific to this session, showing correct number of rows.

SQL> select table_name, num_rows, scope

  2  from user_tab_statistics

  3  where table_name ='NEW_GTT';

TABLE_NAME                  NUM_ROWS SCOPE

------------------------- ---------- -------

NEW_GTT                              SHARED

NEW_GTT                        77384 SESSION

Session 2

SQL> insert into new_gtt select * from all_objects where owner <> 'SYS';

42081 rows created.

SQL>

SQL> exec dbms_stats.gather_table_Stats('HORIA','NEW_GTT',METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO');

PL/SQL procedure successfully completed.

SQL> select /*+ gather_plan_statistics*/ count(*) from new_gtt;

  COUNT(*)

----------

     42081

SQL> select * from table (dbms_xplan.display_cursor(format=>'ALLSTATS LAST'))

  2  ;

PLAN_TABLE_OUTPUT

---------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------

SQL_ID  8432nj7acjwpx, child number 1

-------------------------------------

select /*+ gather_plan_statistics*/ count(*) from new_gtt

Plan hash value: 1213234781

----------------------------------------------------------------------------------------

| Id  | Operation          | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |         |      1 |        |      1 |00:00:00.01 |     722 |

|   1 |  SORT AGGREGATE    |         |      1 |      1 |      1 |00:00:00.01 |     722 |

|   2 |   TABLE ACCESS FULL| NEW_GTT |      1 |  42081 |  42081 |00:00:00.01 |     722 |

----------------------------------------------------------------------------------------

Note

-----

   - Global temporary table session private statistics used

18 rows selected.

SQL>

We can see that this second session did not share the cursor it used new one.

The optimizer used session-specific statistics.

SQL> select table_name, num_rows, scope

  2  from user_tab_statistics

  3  where table_name ='NEW_GTT';

TABLE_NAME                  NUM_ROWS SCOPE

------------------------- ---------- -------

NEW_GTT                              SHARED

NEW_GTT                        42084 SESSION

Reference

Oracle® Database

SQL Tuning Guide

12c Release 1 (12.1)

E15858-15

New background processes in DB12c

Name	Expanded Name	Description	External Properties
AQPC	AQ Process Coordinator	AQPC is responsible for performing administrative tasks for AQ Master Class Processes including commands like starting, stopping, and other administrative tasks. This process is automatically started on instance startup.	Database instances Advanced Queueing
ARSn	ASM Recovery Slave Process	The ASM RBAL background process coordinates and spawns one or more of these slave processes to recover aborted ASM transactional operations. These processes run only in the Oracle ASM instance.	Oracle ASM instances
BWnn	Database Writer Process	See the Description for the DBWn process in this table for more information about the BWnn process.	Database instances
FENC	Fence Monitor Process	CSS monitors RDBMS instances which are connected to the Oracle ASM instance and constantly doing I/Os. When the RDBMS instance terminates due to a failure, all the outstanding I/O’s from the RDBMS instance should be drained and any new I/O’s rejected. FENC receives and processes the fence request from CSSD.	Oracle ASM instances
GCRn	Global Conflict Resolution Slave Process	GCRn processes are transient slaves that are started and stopped as required by LMHB to perform synchronous or resource intensive tasks.	Database instances, Oracle ASM instances, Oracle RAC
IPC0	IPC Service Background Process	IPC0 handles very high rates of incoming connect requests, as well as, completing reconfigurations to support basic messaging and RDMA primitives over several transports such as UDP, RDS, InfiniBand and RC.	Oracle RAC
LDDn	Global Enqueue Service Daemon Helper Slave	LDDn processes are slave processes spawned on demand by LMDn processes. They are spawned to help the dedicated LMDn processes with various tasks when certain workloads start creating performance bottlenecks. These slave processes are transient as they are started on demand and they can be shutdown when no longer needed. There can be up to 36 of these slave processes (LDD0-LDDz).	Database instances, Oracle ASM instances, Oracle RAC
LGnn	Log Writer Worker	On multiprocessor systems, LGWR creates worker processes to improve the performance of writing to the redo log. LGWR workers are not used when there is a SYNC standby destination. Possible processes include LG00-LG99.	Database instances
LREG	Listener Registration Process	LREG notifies the listeners about instances, services, handlers, and endpoint.	Database instances, Oracle ASM instances, Oracle RAC
OFSD	Oracle File Server Background Process	This background process listens for new file system requests, both management (like mount, unmount, and export) and I/O requests, and executes them using Oracle threads.	Database instances, Oracle RAC
QMnn	AQ Master Class Process	Each of this type of process represents a single class of work item such as AQ notification, queue monitors, and cross process.	Database instances Advanced Queueing
RM	RAT Masking Slave Process	This background process is used with Data Masking and Real Application Testing.	Database instances
RMON	Rolling Migration Monitor Process	The RMON process is spawned on demand to run the protocol for transitioning an ASM cluster in and out of rolling migration mode.	Oracle ASM instance, Oracle RAC
RPOP	Instant Recovery Repopulation Daemon	The RPOP process is responsible for re-creating and repopulating data files from snapshots files. It works with the instant recovery feature to ensure immediate data file access. The local instance has immediate access to the remote snapshot file’s data, while repopulation of the recovered primary data files happens concurrently. Any changes in the data are managed between the instance’s DBW processes and RPOP to ensure the latest copy of the data is returned to the user.	Database instances
SAnn	SGA Allocator	A small fraction of SGA is allocated during instance startup. The SAnn process allocates the rest of SGA in small chunks. The process exits upon completion of SGA allocation.	Database instances
SCCn	ASM Disk Scrubbing Slave Check Process	SCCn acts as a slave process for SCRB and performs the checking operations. The possible processes are SCC0-SCC9.	Oracle ASM instances
SCRB	ASM Disk Scrubbing Master Process	SCRB runs in an Oracle ASM instance and coordinates Oracle ASM disk scrubbing operations.	Oracle ASM instances
SCRn	ASM Disk Scrubbing Slave Repair Process	SCRn acts as a slave process for SCRB and performs the repairing operations. The possible processes are SCR0-SCR9.	Oracle ASM instances
SCVn	ASM Disk Scrubbing Slave Verify Process	SCVn acts as a slave process for SCRB and performs the verifying operations. The possible processes are SCV0-SCV9.	Oracle ASM instances
TTnn	Redo Transport Slave Process	TTnn can run as multiple processes, where nn is 00 to ZZ.	Database instances, Data Guard

When the THREADED_EXECUTION initialization parameter is set to TRUE on Linux and UNIX, the DBW, PMON, PSP, and VKTM background processes run as operating system processes, and the other background processes run as operating system threads.

Reference:

http://docs.oracle.com/cd/E16655_01/server.121/e17615/bgprocesses.htm

Out-of-Place Materialized View Refresh Option in DB12c

There is new refresh option which has been introduced to improve materialized view refresh performance and availability in DB12c.
This is called out-of-place refresh. It uses outside tables during refresh instead of the existing in-place refresh that directly applies changes to the materialized view container table.

Out-of-place refresh is most useful in situations where there are large amounts of data changes and conventional DML statements do not scale well.

With this refresh option, the entire or affected portions of a materialized view are computed into one or more outside tables.

There are three types of out-of-place refresh:

• Out-of-place Fast Refresh
• Out-of-place PCT Refresh
• Out-of-place Complete Refresh

Using DBMS_MVIEW package, with method = ? and out_of_place = true, out-of-place fast refresh are attempted first, then out-of-place PCT refresh, and finally out-of-place complete refresh.
Here is an example:

SQL> select banner from v$version where rownum=1;

BANNER
----------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

SQL> create table my_objects as select * from all_objects;

Table created.

SQL> create materialized view my_objects_mv
  2  build immediate
  3  refresh on demand
  4  as
  5  select * from my_objects where owner<>'SCOTT';

Materialized view created.

SQL> begin
  2  dbms_mview.refresh('MY_OBJECTS_MV',
  3  method => '?',
  4  atomic_refresh => FALSE,
  5  out_of_place => TRUE);
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> select MVIEW_NAME, REFRESH_MODE, REFRESH_METHOD, FAST_REFRESHABLE, LAST_REFRESH_DATE
  2  FROM all_mviews
  3  WHERE MVIEW_NAME='MY_OBJECTS_MV';

MVIEW_NAME REFRESH_MODE  REFRESH_METHOD FAST_REFRESHABLE   LAST_REFRESH_DATE
----------------------------------------------------------- ------ -------- ------------------ --
MY_OBJECTS_MV DEMAND FORCE    NO                 02-OCT-13

Reference:

The Out-of-Place Refresh Option in 12c (Doc ID 1546437.1)

DB12c enabled Options during installation

In prior Oracle Database releases, during installation, you could deselect options which were not used.

In DB12c, you are not prompted the option to deselect and disable options any more - all are enabled by default with a few exceptions which we will show below.

The options not enabled are RAC, ASM, Unified Audit.
However, to name just a few, Active Data Guard, Partitioning, Data Mining for which you will have to pay the license are enabled.

Having these options enabled is a requirement for a CDB.

Full list below retrieved from v$option.

SQL> select banner from v$version where rownum=1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

SQL> select parameter,value from v$option order by value, parameter;

PARAMETER                                                        VALUE
---------------------------------------------------------------- ------
ASM Proxy Instance                                               FALSE
Automatic Storage Management                                     FALSE
I/O Server                                                       FALSE
Management Database                                              FALSE
Oracle Database Vault                                            FALSE
Oracle Label Security                                            FALSE
Real Application Clusters                                        FALSE
Unified Auditing                                                 FALSE
Active Data Guard                                                TRUE
Adaptive Execution Plans                                         TRUE
Advanced Analytics                                               TRUE
Advanced Compression                                             TRUE
Advanced replication                                             TRUE
Application Role                                                 TRUE
Automatic Data Optimization                                      TRUE
Backup Encryption                                                TRUE
Basic Compression                                                TRUE
Bit-mapped indexes                                               TRUE
Block Change Tracking                                            TRUE
Block Media Recovery                                             TRUE
Cache Fusion Lock Accelerator                                    TRUE
Change Data Capture                                              TRUE
Coalesce Index                                                   TRUE
Connection multiplexing                                          TRUE
Connection pooling                                               TRUE
Cross Transportable Backups                                      TRUE
DICOM                                                            TRUE
Data Mining                                                      TRUE
Data Redaction                                                   TRUE
Database queuing                                                 TRUE
Database resource manager                                        TRUE
Deferred Segment Creation                                        TRUE
Duplexed backups                                                 TRUE
Enterprise User Security                                         TRUE
Exadata Discovery                                                TRUE
Export transportable tablespaces                                 TRUE
Fast-Start Fault Recovery                                        TRUE
File Mapping                                                     TRUE
Fine-grained Auditing                                            TRUE
Fine-grained access control                                      TRUE
Flashback Data Archive                                           TRUE
Flashback Database                                               TRUE
Flashback Table                                                  TRUE
Global Data Services                                             TRUE
Heat Map                                                         TRUE
Incremental backup and recovery                                  TRUE
Instead-of triggers                                              TRUE
Java                                                             TRUE
Join index                                                       TRUE
Managed Standby                                                  TRUE
Materialized view rewrite                                        TRUE
OLAP                                                             TRUE
OLAP Window Functions                                            TRUE
Objects                                                          TRUE
Online Index Build                                               TRUE
Online Redefinition                                              TRUE
Oracle Data Guard                                                TRUE
Parallel backup and recovery                                     TRUE
Parallel execution                                               TRUE
Parallel load                                                    TRUE
Partitioning                                                     TRUE
Plan Stability                                                   TRUE
Point-in-time tablespace recovery                                TRUE
Privilege Analysis                                               TRUE
Proxy authentication/authorization                               TRUE
Real Application Security                                        TRUE
Real Application Testing                                         TRUE
Result Cache                                                     TRUE
SQL Plan Management                                              TRUE
Sample Scan                                                      TRUE
SecureFiles Encryption                                           TRUE
Server Flash Cache                                               TRUE
Snapshot time recovery                                           TRUE
Spatial                                                          TRUE
Streams Capture                                                  TRUE
Table Clustering                                                 TRUE
Transparent Application Failover                                 TRUE
Transparent Data Encryption                                      TRUE
Trial Recovery                                                   TRUE
Unused Block Compression                                         TRUE
XStream                                                          TRUE
Zone Maps                                                        TRUE

82 rows selected.