Friday, January 24, 2014

Predicate N = N-1 returns rows?

Following a tweet from  "Quiz Q for the day: "select * from T where N = N - 1;" returns rows - how?", I did a small setup to try to answer the question.

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

BANNER
----------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

--number

SQL> drop table tt
  2  ;

Table dropped.

SQL> create table tt as select 1/0f N from dual;

Table created.

SQL> desc tt;
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------
 N                                                  BINARY_FLOAT

SQL> select * from tt where N=N-1;

         N
----------
       Inf

SQL>

Tuesday, January 7, 2014

EM12c - Deploy the Latest Database Plugin to the OMS in offline mode

As of the date this was written, the latest EM12c database plugin is 12.1.0.5.0.

If you are a fan of command line utilities over the GUI interfaces (like me), you will love emcli which will help accomplish many operations in EM12c.

The steps would be:
1.       Aquire the latest database plug-in software using Self Update
2.       Deploy the plugin on the management server aka OMS

We will now synchronize emcli with the OMS
[oracle@em12c bin]$ ./emcli sync
Error: Session expired. Run emcli login to establish a session.

Let’s check the status:

[oracle@em12c bin]$ ./emcli status
Oracle Enterprise Manager 12c EMCLI12.1.0.3.0.
Copyright (c) 1996, 2013 Oracle Corporation and/or its affiliates. All rights reserved.

Instance Home          : /u01/app/oracle/gc_inst/em/EMGC_OMS1/sysman/emcli/setup/.emcli
Verb Jars Home         : null
Status                 : Configured
EMCLI Home             : /u01/app/oracle/Middleware2/oms/bin/.
EMCLI Version          : 12.1.0.3.0
Java Home              : /u01/app/oracle/Middleware2/jdk16/jdk/jre
Java Version           : 1.6.0_43
Log file               : /u01/app/oracle/gc_inst/em/EMGC_OMS1/sysman/emcli/setup/.emcli/.emcli.log
EM URL                 : https://em12c.localdomain:7801/em
EM user                : SYSMAN
Auto login             : false
Trust all certificates : true

[oracle@em12c bin]$ ./emcli login -username=SYSMAN -password=***
Login successful


[oracle@em12c bin]$ ./emcli sync
Synchronized successfully

1)      Download the latest updates catalog in offline mode
Login to EM12c -> Setup -> Extensibility -> Self Update
In the page, select Plugins and Click “Check Updates”

A popup with the link to download will appear:
https://updates.oracle.com/Orion/Download/download_patch/p9348486_112000_Generic.zip

Now we need to import the master catalog with is: Patch 9348486: master catalog

[oracle@em12c bin]$ ./emcli import_update_catalog -file=/home/oracle/p9348486_112000_Generic.zip -omslocal
Processing catalog for Middleware Profiles and Gold Images
Processing catalog for Agent Software
Processing catalog for Management Connector
Processing catalog for Extensibility Development Kit
Processing update: Extensibility Development Kit - 'Extensibility Development Kit (EDK) is a standalone tool designed to help development of Enterprise Manager extensions (For example: Plug-ins)'
Processing catalog for Plug-in
Processing update: Plug-in - Enterprise Manager for Oracle Utilities provides management and monitoring for the Oracle Utilities area.
Processing update: Plug-in - Enterprise Manager for Oracle Enterprise Taxation and Policy Management provides management and monitoring for the Oracle Enterprise Taxation and Policy Management area.
Processing update: Plug-in - Enterprise Manager for Oracle Exadata provides comprehensive management for Oracle Exadata and related targets such as Database Machine,  etc.
Processing update: Plug-in - Enables cloud self service framework and portal in Oracle Enterprise Manager.
Processing update: Plug-in -
        Enterprise Manager Storage Management Framework provides Snap Clone functionality by leveraging the snapshot and Copy-on-Write (CoW) features of the underlying storage.
 
Processing update: Plug-in - Enterprise Manager for Oracle Siebel consists of monitoring and management functionalities for Oracle Siebel.
Processing update: Plug-in - Enterprise Manager for Fusion Apps consists of monitoring and management for Oracle Fusion and diagnostics in fusion application area.
Processing update: Plug-in - Enterprise Manager for Oracle Consolidation Planning and Chargeback provides metering, chargeback and consolidation planning for various Enterprise Manager targets.
Processing update: Plug-in - Enterprise Manager for Fusion Middleware consists of monitoring and management for Oracle Fusion Middleware and diagnostics in middleware area.
Processing update: Plug-in - Enterprise Manager for Oracle Database provides comprehensive management for Oracle Database and related targets such as Real Application Clusters, Automatic Storage Management (ASM) etc.
Processing update: Plug-in - Enterprise Manager for Big Data Appliance  provides comprehensive management for Oracle Big Data Appliance and related targets of Hadoop
Processing update: Plug-in - Enterprise Management Plug-in for Oracle ZFS Storage Appliances
Processing update: Plug-in - Enterprise Manager for Oracle Instant Messaging Server is used for monitoring/managing the Oracle Instant Messaging Server
Processing update: Plug-in - Enterprise Manager for Oracle Virtual Networking
Processing update: Plug-in - Enterprise Manager for Ops Center Infrastructure stack
Processing update: Plug-in - Enterprise Manager for Engineered System Healthcheck provides proactive healthcheck alerts for Oracle Engineered Systems
Processing update: Plug-in - Enterprise Manager for Oracle Audit Vault and Database Firewall (AVDF) provides monitoring and management of AVDF system.
Processing update: Plug-in - Enterprise Manager for Oracle Communications enables management of Oracle Communications products
Processing update: Plug-in - Oracle Application Management Pack for Oracle E-Business Suite consists of System Management and Change Management Feature Sets
Processing catalog for Test Data Management templates
Processing catalog for Oracle Virtual Templates and Oracle Virtual Assemblies
Processing update: Oracle Virtual Templates and Oracle Virtual Assemblies - Oracle Application Testing Suite Functional Version 12.3.0.1.400
Processing update: Oracle Virtual Templates and Oracle Virtual Assemblies - Oracle Application Testing Suite Version 12.3.0.1.376
Processing update: Oracle Virtual Templates and Oracle Virtual Assemblies - Oracle Application Testing Suite Agent Version 12.3.0.1.376
Processing catalog for Informational
Processing catalog for Provisioning Bundle

Successfully uploaded the Self Update catalog to Enterprise Manager. Use the Self Update Console to view and manage updates.
[oracle@em12c bin]$

Now Click on Plug-In Updates -> choose Oracle Database (it will show Available) -> Download
You will be prompted with a message like the below.


You shall be downloading it from the URL provided.
Now you can import it to Enterprise Manager using the below command:

 [oracle@em12c bin]$ ./emcli import_update -omslocal -file=/home/oracle/p17628065_112000_Generic.zip
Processing update: Plug-in - Enterprise Manager for Oracle Database provides comprehensive management for Oracle Database and related targets such as Real Application Clusters, Automatic Storage Management (ASM) etc.
Successfully uploaded the update to Enterprise Manager. Use the Self Update Console to manage this update.

Let’s now check the existing plugins:

[oracle@em12c bin]$ ./emcli list_plugins_on_server
OMS name is em12c.localdomain:4890_Management_Service
Plug-in Name                                 Plugin-id                     Version [revision]

Oracle Engineered System Healthchecks        oracle.em.sehc                12.1.0.3.0
Oracle Database                              oracle.sysman.db              12.1.0.4.0
Oracle Fusion Middleware                     oracle.sysman.emas            12.1.0.4.0
Oracle Consolidation Planning and Chargeback oracle.sysman.emct            12.1.0.4.0
Oracle MOS (My Oracle Support)               oracle.sysman.mos             12.1.0.5.0
Oracle Storage Management Framework          oracle.sysman.smf             12.1.0.2.0
Oracle Cloud Application                     oracle.sysman.ssa             12.1.0.6.0
Oracle Virtualization                        oracle.sysman.vt              12.1.0.5.0
Oracle Exadata                               oracle.sysman.xa              12.1.0.4.0
Oracle Utilities                             oracle.tugbu.outl             12.1.0.2.0




2)      Deployment
Login to EM12c -> Setup -> Extensibility -> Go to Plugins

Expand the option Databases -> Oracle Databases -> Deploy on -> Choose Management Server
 Input the password for SYS (on the repository database of course)


Wait for <5 minutes for the prerequisites to complete:


Check the box which asks if you have backed up the repository and configuration of first management server and click Deploy:

 As OMS will be taken down during deployment of plug-in, you can monitor the progress using

./emctl status oms -details
Oracle Enterprise Manager Cloud Control 12c Release 3 
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
Enter Enterprise Manager Root (SYSMAN) Password :
Console Server Host        : em12c.localdomain
HTTP Console Port          : 7789
HTTPS Console Port         : 7801
HTTP Upload Port           : 4890
HTTPS Upload Port          : 4901
EM Instance Home           : /u01/app/oracle/gc_inst/em/EMGC_OMS1
OMS Log Directory Location : /u01/app/oracle/gc_inst/em/EMGC_OMS1/sysman/log
OMS is not configured with SLB or virtual hostname
Agent Upload is unlocked.
OMS Console is unlocked.
Active CA ID: 1
Console URL: https://em12c.localdomain:7801/em
Upload URL: https://em12c.localdomain:4901/empbs/upload

WLS Domain Information
Domain Name            : GCDomain
Admin Server Host      : em12c.localdomain
Admin Server HTTPS Port: 7102

Managed Server Information
Managed Server Instance Name: EMGC_OMS1
Managed Server Instance Host: em12c.localdomain
WebTier is Down

Unable to deploy or undeploy the plug-in. Oracle Management Server is down possibly because the following plug-ins are being deployed or undeployed from it. Try again after some time.
Plug-in Deployment/Undeployment Status

Destination          : Management Server - em12c.localdomain:4890_Management_Service
Plug-in Name         : Oracle Database                        
Version              : 12.1.0.5.0                             
ID                   : oracle.sysman.db                       
Content              : Plug-in                                 
Action               : Deployment                             
Status               : Deploying                              
Steps Info:
---------------------------------------- ------------------------- ------------------------- ----------
Step                                     Start Time                End Time                  Status    
---------------------------------------- ------------------------- ------------------------- ----------
Submit job for deployment                1/7/14 7:20:00 AM EST     1/7/14 7:20:00 AM EST     Success   

Initialize                               1/7/14 7:20:08 AM EST     1/7/14 7:20:26 AM EST     Success   

Install software                         1/7/14 7:20:28 AM EST     1/7/14 7:20:30 AM EST     Success   

Validate plug-in home                    1/7/14 7:20:34 AM EST     1/7/14 7:20:34 AM EST     Success   

Perform custom preconfiguration          1/7/14 7:20:34 AM EST     1/7/14 7:20:34 AM EST     Success   

Check mandatory patches                  1/7/14 7:20:34 AM EST     1/7/14 7:20:34 AM EST     Success   

Generate metadata SQL                    1/7/14 7:20:34 AM EST     1/7/14 7:20:34 AM EST     Success   

Preconfigure Management Repository       1/7/14 7:20:34 AM EST     1/7/14 7:20:34 AM EST     Success   

Preregister DLF                          1/7/14 7:20:34 AM EST     1/7/14 7:20:34 AM EST     Success   

Stop management server                   1/7/14 7:20:39 AM EST     N/A                       Running   

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

What interest us are the actions following the stop of management server. We need to rerun the above command; we cannot see the progress as we do with tail –f for example.


Stop management server                   1/7/14 7:20:39 AM EST     1/7/14 7:22:19 AM EST     Success   

Configure Management Repository          1/7/14 7:22:21 AM EST     N/A                       Running   

Configure middle tier                    1/7/14 7:22:23 AM EST     N/A                       Running   

Register DLF                             1/7/14 7:22:23 AM EST     N/A                       Running   

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

…More details on the progress…

Stop management server                   1/7/14 7:20:39 AM EST     1/7/14 7:22:19 AM EST     Success   

Configure Management Repository          1/7/14 7:22:21 AM EST     N/A                       Running   

Configure middle tier                    1/7/14 7:22:23 AM EST     1/7/14 7:28:33 AM EST     Success   

Register DLF                             1/7/14 7:22:23 AM EST     1/7/14 7:29:25 AM EST     Success   

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

…More details on the progress…

Stop management server                   1/7/14 7:20:39 AM EST     1/7/14 7:22:19 AM EST     Success   

Configure Management Repository          1/7/14 7:22:21 AM EST     1/7/14 7:46:15 AM EST     Success   

Configure middle tier                    1/7/14 7:22:23 AM EST     1/7/14 7:28:33 AM EST     Success   

Register DLF                             1/7/14 7:22:23 AM EST     1/7/14 7:29:25 AM EST     Success   

OPSS jazn policy migration               1/7/14 7:31:30 AM EST     1/7/14 7:31:55 AM EST     Success    

Register metadata                        1/7/14 7:46:16 AM EST     1/7/14 7:49:53 AM EST     Success   

Perform custom postconfiguration         1/7/14 7:49:53 AM EST     N/A                       Running   

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

…And the last step of starting management server…

Stop management server                   1/7/14 7:20:39 AM EST     1/7/14 7:22:19 AM EST     Success   

Configure Management Repository          1/7/14 7:22:21 AM EST     1/7/14 7:46:15 AM EST     Success   

Configure middle tier                    1/7/14 7:22:23 AM EST     1/7/14 7:28:33 AM EST     Success   

Register DLF                             1/7/14 7:22:23 AM EST     1/7/14 7:29:25 AM EST     Success   

OPSS jazn policy migration               1/7/14 7:31:30 AM EST     1/7/14 7:31:55 AM EST     Success   

Register metadata                        1/7/14 7:46:16 AM EST     1/7/14 7:49:53 AM EST     Success   

Perform custom postconfiguration         1/7/14 7:49:53 AM EST     1/7/14 7:49:55 AM EST     Success   

Update inventory                         1/7/14 7:49:55 AM EST     1/7/14 7:49:59 AM EST     Success   

Start management server                  1/7/14 7:49:59 AM EST     N/A                       Running   

---------------------------------------- ------------------------- ------------------------- ----------
The deployment completes after approximately 40 minutes. The OMS is up again.

[oracle@em12c bin]$ ./emctl status oms -details
Oracle Enterprise Manager Cloud Control 12c Release 3 
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
Enter Enterprise Manager Root (SYSMAN) Password :
Console Server Host        : em12c.localdomain
HTTP Console Port          : 7789
HTTPS Console Port         : 7801
HTTP Upload Port           : 4890
HTTPS Upload Port          : 4901
EM Instance Home           : /u01/app/oracle/gc_inst/em/EMGC_OMS1
OMS Log Directory Location : /u01/app/oracle/gc_inst/em/EMGC_OMS1/sysman/log
OMS is not configured with SLB or virtual hostname
Agent Upload is unlocked.
OMS Console is unlocked.
Active CA ID: 1
Console URL: https://em12c.localdomain:7801/em
Upload URL: https://em12c.localdomain:4901/empbs/upload

WLS Domain Information
Domain Name            : GCDomain
Admin Server Host      : em12c.localdomain
Admin Server HTTPS Port: 7102
Admin Server is RUNNING

Managed Server Information
Managed Server Instance Name: EMGC_OMS1
Managed Server Instance Host: em12c.localdomain
WebTier is Up
Oracle Management Server is Up

We now list the plugins on server

[oracle@em12c bin]$ ./emcli list_plugins_on_server
OMS name is em12c.localdomain:4890_Management_Service
Plug-in Name                                 Plugin-id                     Version [revision]

Oracle Engineered System Healthchecks        oracle.em.sehc                12.1.0.3.0
Oracle Database                              oracle.sysman.db              12.1.0.5.0
Oracle Fusion Middleware                     oracle.sysman.emas            12.1.0.4.0
Oracle Consolidation Planning and Chargeback oracle.sysman.emct            12.1.0.4.0
Oracle MOS (My Oracle Support)               oracle.sysman.mos             12.1.0.5.0
Oracle Storage Management Framework          oracle.sysman.smf             12.1.0.2.0
Oracle Cloud Application                     oracle.sysman.ssa             12.1.0.6.0
Oracle Virtualization                        oracle.sysman.vt              12.1.0.5.0
Oracle Exadata                               oracle.sysman.xa              12.1.0.4.0
Oracle Utilities                             oracle.tugbu.outl             12.1.0.2.0

We can see that Oracle Database shows correctly it is at version 12.1.0.5

In EM, the same information is visible


Reference:
How to Deploy the Latest Database Plugin to the OMS and the Agent in 12C Cloud Control (Doc ID 1388143.1)

Friday, October 18, 2013

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

Friday, October 4, 2013

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:

Wednesday, October 2, 2013

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:

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.