Monthly Archives: August 2013

Collecting stats during bulk load

Oracle August 26, 2013

Starting with Oracle 12c, the default behavior of database is to gathers statistics during bulk loads like CTAS or INSERT SELECT.  However for some reason , if you want to go back to pre-12c behavior , you can do so with a new hint NO_GATHER_OPTIMIZER_STATISTICS hint.

See illustration of examples comparing Oracle11g and Oracle12c

Oracle 11g

SQL> SELECT * FROM V$VERSION;

BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
PL/SQL Release 11.2.0.2.0 – Production
CORE    11.2.0.2.0      Production
TNS for Linux Version 11.2.0.2.0 – Production
NLSRTL Version 11.2.0.2.0 – Production

SQL> CREATE TABLE MY_OBJECTS_NOSTATS AS SELECT * FROM MY_OBJECTS;

Table created.

SQL> CREATE TABLE MY_OBJECTS_STATS AS SELECT /*+ GATHER_OPTIMIZER_STATISTICS */  * FROM MY_OBJECTS; ==> Database is 11g, Hint is ignored

Table created.

SQL>  CREATE TABLE MY_OBJECTS_NOSTATS_HINT  AS SELECT /*+ NO_GATHER_OPTIMIZER_STATISTICS */  * FROM MY_OBJECTS; ==> Database is 11g, Hint is ignored

Table created.

SQL> SELECT TABLE_NAME, LAST_ANALYZED , NUM_ROWS, BLOCKS FROM DBA_TABLES WHERE TABLE_NAME LIKE ‘MY_OBJECTS%’; ==> Database is 11g, Stats are not gathered for all tables

TABLE_NAME                     LAST_ANAL   NUM_ROWS     BLOCKS
—————————— ——— ———- ———-
MY_OBJECTS
MY_OBJECTS_STATS
MY_OBJECTS_NOSTATS
MY_OBJECTS_NOSTATS_HINT

Oracle12c

SQL> SELECT BANNER FROM V$VERSION;

BANNER
——————————————————————————–
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production
PL/SQL Release 12.1.0.1.0 – Production
CORE    12.1.0.1.0      Production
TNS for Linux: Version 12.1.0.1.0 – Production
NLSRTL Version 12.1.0.1.0 – Production

SQL> CREATE TABLE MY_OBJECTS_NOSTATS AS SELECT * FROM MY_OBJECTS;

Table created.

SQL> CREATE TABLE MY_OBJECTS_STATS AS SELECT /*+ GATHER_OPTIMIZER_STATISTICS */  * FROM MY_OBJECTS; ==> Default behavior ;hint is of no help here.

Table created.

SQL> CREATE TABLE MY_OBJECTS_NOSTATS_HINT  AS SELECT /*+ NO_GATHER_OPTIMIZER_STATISTICS */  * FROM MY_OBJECTS; ==> Hint instructing Oracle not to collect Stats

Table created.

SQL> SET LINESIZE 200
SQL> COLUMN TABLE_NAME FOR A40
SQL> SELECT TABLE_NAME, LAST_ANALYZED , NUM_ROWS, BLOCKS FROM DBA_TABLES WHERE TABLE_NAME LIKE ‘MY_OBJECTS%’; ==> Stats are gathered for all tables except table MY_OBJECTS_NOSTATS_HINT which was created with NO_GATHER_OPTIMIZER_STATISTICS hint

TABLE_NAME                               LAST_ANAL   NUM_ROWS     BLOCKS
—————————————————————————————————————
MY_OBJECTS                                26-AUG-13      90908       1561
MY_OBJECTS_NOSTATS                26-AUG-13      90908       1561
MY_OBJECTS_STATS                    26-AUG-13      90908       1561
MY_OBJECTS_NOSTATS_HINT

4 rows selected.

SQL>

TABLE ACCESS BY INDEX ROWID BATCHED

Oracle August 24, 2013

TABLE ACCESS BY INDEX ROWID BATCHED is new execution plan operation that helps improve performance.  It is generally used for range ( > or < ) queries.  For this new operation, Oracle selects few  ROWIDs from the index and then try to access the rows in blocks. This significantly reduces the number of times Oracle must access the blocks thereby improving performance.

Sample Execution plan

Execution Plan
———————————————————-
Plan hash value: 525114061

—————————————————————————————————————————-
| Id  | Operation                                                   | Name     | Rows   | Bytes   | Cost (%CPU)| Time
—————————————————————————————————————————–
|   0 | SELECT STATEMENT                                   |                 |    10  |  3020  |     3   (0)| 00:00:01
|*  1 |  COUNT STOPKEY                                      |                 |          |           |              |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED | EMP          | 28012 |  8261K|     3   (0)| 00:00:01
|*  3 |    INDEX RANGE SCAN                                | EMP_SAL_I |          |           |     2   (0)| 00:00:01
—————————————————————————————————————————–

Oracle12c:Temporal Validity (Part-1)

Oracle August 23, 2013 1 Comment

You will really appreciate this feature if you have called some of the cable companies to terminate your cable connection.   This is the response you generally get to hear ” Please call us on so and so date; I cannot update the system now”. Hopefully some of those companies can use these feature now.

Temporal Validity  associates one or more valid time dimensions to your data making it visible or invisible  depending on the time as set by the user. If adopted , may be with this feature, you can change your address or phone number months in advance, don’t have to wait for the last minute.

So how do you implement it?

PERIOD FOR clause of  CREATE/ALTER table command is used to implement temporal validity.

The syntax for PERIOD FOR clause is as below.

PERIOD FOR dimension_name [( start_time_column , end_time_column)]

You can explicitly  specify  columns “start_time_column”  & “end_time_column”  and when specified  they must be existing columns in the table.

If  “start_time_column”  & “end_time_column”   is skipped , Oracle will create  two invisible columns  dimension_name_start & dimension_name_end for time dimensions. The invisible columns cane be used in INSERT  & UPDATE statement like like any defined table columns.

 

start_time_column —–end_time_column ——-Result
NULL NULL Valid for all Times
NULL Value Valid for all times before  end_time_column values
Value NULL Valid for all times after start_time_column values
Value Value Valid for all times between start_time_column and end_time_column values

See examples below for creating tables

In the 1st example below, I am creating a time dimension using columns of the table itself. Two columns SERVICE_START and SERVICE_END  specifies the time dimension for the row.

SQL> CREATE TABLE CUSTOMER_1
2  (
3  CUST_NO    NUMBER(12) PRIMARY KEY,
4  CUST_NAME  VARCHAR2(20) NOT NULL,
5  CUST_ADDR  VARCHAR2(40),
6  SERVICE_START      TIMESTAMP,
7  SERVICE_END        TIMESTAMP,
8  PERIOD FOR CUST_HIST_TIME(SERVICE_START, SERVICE_END)
9  );

Table created.

In the 2nd example below, I am creating the  table  without specifying the time dimensions for the data. In such case Oracle will create 2 invisible columns CUST_HIST_TIME_START & CUST_HIST_TIME_END that can be used  almost like any other regular table column.

Not: CUST_HIST_TIME is user defined value for time dimension specified with PERIOD FOR clause.

SQL> CREATE TABLE CUSTOMER_2
2  (
3  CUST_NO    NUMBER(12) PRIMARY KEY,
4  CUST_NAME  VARCHAR2(20) NOT NULL,
5  CUST_ADDR  VARCHAR2(40),
6  PERIOD FOR CUST_HIST_TIME ==> Invisible start and end columns are created
7  );

Table created.

Inserting data into temporal tables with invisible columns

SQL> INSERT INTO CUSTOMER_2 (CUST_NO, CUST_NAME, CUST_ADDR, CUST_HIST_TIME_START , CUST_HIST_TIME_END)
2  VALUES (1,’SHAN’,’1235 RIVER RD VA 20155′,SYSDATE, SYSDATE+90);

1 row created.

SQL> INSERT INTO CUSTOMER_2 (CUST_NO, CUST_NAME, CUST_ADDR, CUST_HIST_TIME_START , CUST_HIST_TIME_END)
2   VALUES (2,’JOE’,’78628 LAKE ST IL 60148′,SYSDATE , SYSDATE+120);

1 row created.

Click here for Part-2 : Work-in-progress

Oracle12c: Session Data Unit(SDU)

Oracle August 20, 2013

Session Data Unit(SDU) is the session layer responsible for  sending/receiving data from transport layer.  See table below for OSI diagram. Generally increasing the value of SDU can result in improved performance, efficient network utilization when transmitting large amounts of data.  However until Oracle 11g  the SDU size was limited  to 64K. Starting with Oracle12c, support for  SDU  is increased to 2M thereby allowing efficient use of  high bandwidth network.

SDU can be set at  database level by configuring DEFAULT_SDU_SIZE parameter in sqlnet.ora or SDU listener.ora parameter. On the client size SDU can set be by configuring DEFAULT_SDU_SIZE parameter in client sqlnet.ora or SDU tnsnames.ora parameter.

Note: SDU size  actually used is the smaller of  the client and server value.

OSI Model

Layer# Name Description
7 Application Standard communication services and applications that everyone can use
6 Presentation Layer that takes data from application and presents it  in a format that is recognized by other layers; Functions like character set conversion is done by this layer
5 Session Establishes, maintain & end connections
4 Transport Delivers data without errors in sequence and no data loss or duplications. Large messages can be delivered but is constrained by message size limits imposed by the network layer.
3 Network Controls the way data will be sent to recipient ; Factors like the physical path the data should take based  etc are performed by this layer. This layer translates logical addresses, or names, into physical addresses, performs routing
2 Data Link Handles the transfer of data across the network ; It  establishes and terminates the logical link between two nodes.
1 Physical The lowest layer of the OSI  is generally concerned with the transfer of data medium like optical or electrical medium etc.

Temporary Undo in Oracle12c

Oracle August 16, 2013 1 Comment

Prior to Oracle12c, there was lot of confusion about Global temporary tables and redo generation.  Some of us initially misinterpreted documentation and concluded that no redo is generated for DMLs against global temporary tables.  However in Oracle12c, the documentation is very clear about undo being generated for  DMLs against global temporary tables  because undo by default is stored in undo tablespace. This off course results in generation of redo because of the undo being generated in undo tablespace. Kind of chain effect.

Depending on  the type of DML, this can be significant.  For example,  the undo for insert statement is delete (based on rowid) and so undo will be minimal and therefore the redo too.  Please note the redo is generated for undo.

I have the  illustrated an example below where the redo for global temporary table  is 601200(600k) vs 12513404 (12M) from  permanent temporary table.

Therefore  redo is generated for DMLs into Global temporary table.

SQL> create  global temporary table dba_objects_gt as select * from dba_objects where 1=0;

Table created.

SQL>
SQL> set autotrace on

SQL> insert into dba_objects_gt select * from dba_objects;

90858 rows created.

Statistics
———————————————————-
70  recursive calls
8458  db block gets
6230  consistent gets
0  physical reads
601200  redo size
857  bytes sent via SQL*Net to client
852  bytes received via SQL*Net from client
3  SQL*Net roundtrips to/from client
3  sorts (memory)
0  sorts (disk)
90858  rows processed

SQL> create  table dba_objects_pm as select * from dba_objects where 1=0
2  ;

Table created.

SQL> insert into dba_objects_pm select * from dba_objects;

90859 rows created.

Statistics
———————————————————-
61  recursive calls
8781  db block gets
7746  consistent gets
0  physical reads
  12513404  redo size
859  bytes sent via SQL*Net to client
852  bytes received via SQL*Net from client
3  SQL*Net roundtrips to/from client
1  sorts (memory)
0  sorts (disk)
90859  rows processed

As we are aware  that undo records for temporary tables are stored in the undo tablespace and therefore logged in the redo. This is same way undo is managed for permanent tables too. With Oracle12c, this default bahavoiour can be changed with  initialization parameter TEMP_UNDO_ENABLED that separated undo for temporary tables from undo for persistent tables.  Temporary undo can be enabled at session level or system level.  By default ,Temporary undo is also enabled for physical standby databases (Active Dataguard)

Other than reduced redo generation, enabling temporary undo also results in less data stored in undo which in turn can help predict more accurate undo retentions.

I enabled temporary undo and ran the insert statement once again. What a difference … From 12M to 600K  to 228 bytes.

SQL> ALTER SYSTEM SET TEMP_UNDO_ENABLED = TRUE;

System altered.

SQL>
SQL>  create  global temporary table dba_objects_gt as select * from dba_objects where 1=0;

Table created.

SQL>
SQL> insert into dba_objects_gt select * from dba_objects;

90860 rows created.

Statistics
———————————————————-
13  recursive calls
8456  db block gets
6153  consistent gets
0  physical reads
   228  redo size
860  bytes sent via SQL*Net to client
852  bytes received via SQL*Net from client
3  SQL*Net roundtrips to/from client
1  sorts (memory)
0  sorts (disk)
90860  rows processed

Oracle12c: Muliple indexes on Same columns

Oracle August 13, 2013 Comments: 2

Starting with 12c, Oracle supports creating different type of indexes on same column(s). MySQL supported this kind of indexing mechanism and glad that Oracle also supports it. Generally indexing the same column is considered  a performance bottleneck because it slows down inserts and updates to maintain multiple indexes but some times it becomes a necessity.

Few reasons why you would need different type of indexes on same of columns

  1. B*TREE for OLTP sessions v.s BITMAP for nightly reports
  2. Local vs Global Indexes
  3. Testing  performance of different type of index

SQL> SELECT * FROM V$VERSION
2  ;

BANNER
——————————————————————————
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
PL/SQL Release 11.2.0.2.0 – Production
CORE    11.2.0.2.0      Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.2.0 – Production
NLSRTL Version 11.2.0.2.0 – Production

SQL> CREATE TABLE EMP
2  (
3  EMPNO      NUMBER(9)       PRIMARY KEY ,
4  EMPNAME    VARCHAR2(15)    NOT NULL,
5  DEPTNO             NUMBER(4)       ,
6  PHONENO    NUMBER(10)      ,
7  HIREDATE   DATE            ,
8  SALARY     NUMBER(9, 2)
9  )
10  /

Table created.

SQL> CREATE INDEX EMP_DEPT_BTREE_IDX ON EMP(DEPTNO);

Index created.

SQL> CREATE BITMAP INDEX EMP_DEPTNO_BITMAP_IDX ON EMP(DEPTNO) INVISIBLE  ;
CREATE BITMAP INDEX EMP_DEPTNO_BITMAP_IDX ON EMP(DEPTNO) INVISIBLE
*
ERROR at line 1:
ORA-01408: such column list already indexed

Lets us repeat the same steps in Oracle12c

SQL> SELECT * FROM V$VERSION;

BANNER                                                                               CON_ID
——————————————————————————– ———-
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production              0
PL/SQL Release 12.1.0.1.0 – Production                                                    0
CORE    12.1.0.1.0      Production                                                                0
TNS for Linux: Version 12.1.0.1.0 – Production                                            0
NLSRTL Version 12.1.0.1.0 – Production                                                    0
SQL> CREATE TABLE EMP
2  (
3  EMPNO      NUMBER(9)       PRIMARY KEY ,
4  EMPNAME    VARCHAR2(15)    NOT NULL,
5  DEPTNO             NUMBER(4)       ,
6  PHONENO    NUMBER(10)      ,
7  HIREDATE   DATE            ,
8  SALARY     NUMBER(9, 2)
9  )
10  TABLESPACE USERS
11  ;

Table created.

SQL>
SQL>
SQL> CREATE INDEX EMP_DEPT_BTREE_IDX ON EMP(DEPTNO);

Index created.

SQL> CREATE BITMAP INDEX EMP_DEPTNO_BITMAP_IDX ON EMP(DEPTNO) INVISIBLE  ;

Index created.

.

IDENTITY Columns: Now with Oracle12c

Oracle August 13, 2013 Comments: 2

Identity columns generate  unique numeric value for each row in a table. This option was available on DB2 since version 7 and is now available with Oracle 12c too.  Hooray !!!!

You can define identify columns with ALWAYS or DEFAULT option of CREATE/ALTER TABLE command.

ALWAYS: Oracle Database always uses the sequence generator to assign a value to the column and does not allow the user  to update or insert the identity column value.

BY DEFAULT: Similar to ALWAYS but  permits the user to explicitly assign a value using insert and update.

Using “BY DEFAULT” as opposed to “ALWAYS” does not guarantee uniqueness as the values can be updated by user.  The “ALWAYS”  option pretty much does what a primary key or unique key or unique index does with out having to create them explicitly. Use this to create uniqueness at your own peril.

How is identify clause different from Sequences?

An identity column has a  1-1 relationship  with tables whereas sequences are not tied to any single table. Therefore with sequences, the same value can be used across several tables. Also most of the sequence options like start value, max values etc can be defined with identity column option.

Example to illustrate 1-1 relationship between identity columns and tables

In  this example, I am creating 2 tables with similar structure and inserting data in both the table

SQL> CREATE TABLE EMP1
2  (
3  EMPNO      NUMBER(9)       GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
4  EMPNAME    VARCHAR2(15)    NOT NULL,
5  DEPT               NUMBER(4)       ,
6  PHONENO    NUMBER(10)      ,
7  HIREDATE   DATE            ,
8  SALARY     NUMBER(9, 2)
9  )
10  TABLESPACE USERS;

Table created.

SQL> SQL> CREATE TABLE EMP2
2  (
3  EMPNO      NUMBER(9)       GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
4  EMPNAME    VARCHAR2(15)    NOT NULL,
5  DEPT               NUMBER(4)       ,
6  PHONENO    NUMBER(10)      ,
7  HIREDATE   DATE            ,
8  SALARY     NUMBER(9, 2)
9  )
10  TABLESPACE USERS;

Table created.

SQL>
SQL>
SQL> INSERT INTO EMP1 (EMPNAME,DEPT,PHONENO,HIREDATE,SALARY) VALUES (‘TIM’,10,6309351100,SYSDATE,50000);

1 row created.

SQL> INSERT INTO EMP2 (EMPNAME,DEPT,PHONENO,HIREDATE,SALARY) VALUES (‘TIM’,10,6309351100,SYSDATE,50000);

1 row created.

SQL> INSERT INTO EMP1 (EMPNAME,DEPT,PHONENO,HIREDATE,SALARY) VALUES (‘TIM’,10,6309351100,SYSDATE,50000);

1 row created.

SQL>  INSERT INTO EMP2 (EMPNAME,DEPT,PHONENO,HIREDATE,SALARY) VALUES (‘TIM’,10,6309351100,SYSDATE,50000);

1 row created.

SQL> INSERT INTO EMP1 (EMPNAME,DEPT,PHONENO,HIREDATE,SALARY) VALUES (‘TIM’,10,6309351100,SYSDATE,50000);

1 row created.

SQL>  INSERT INTO EMP2 (EMPNAME,DEPT,PHONENO,HIREDATE,SALARY) VALUES (‘TIM’,10,6309351100,SYSDATE,50000);

1 row created.

SQL> SELECT * FROM EMP1;

EMPNO EMPNAME               DEPT    PHONENO HIREDATE      SALARY
———- ————— ———- ———- ——— ———-
1 TIM                     10 6309351100 13-AUG-13      50000
2 TIM                     10 6309351100 13-AUG-13      50000
3 TIM                     10 6309351100 13-AUG-13      50000

SQL> SELECT * FROM EMP2;

EMPNO EMPNAME               DEPT    PHONENO HIREDATE      SALARY
———- ————— ———- ———- ——— ———-
1 TIM                     10 6309351100 13-AUG-13      50000
2 TIM                     10 6309351100 13-AUG-13      50000
3 TIM                     10 6309351100 13-AUG-13      50000

Example to illustrate DEFAULT option not preserving the uniqueness

In  this example, I am creating a  table with identify column (DEFAULT option) and therefore allows updates to  identity column  leading to duplicate values

SQL>
SQL> CREATE TABLE EMP4
2  (
3  EMPNO      NUMBER(9)       GENERATED BY DEFAULT AS IDENTITY ,
4  EMPNAME    VARCHAR2(15)    NOT NULL,
5  DEPT               NUMBER(4)       ,
6  PHONENO    NUMBER(10)      ,
7  HIREDATE   DATE            ,
8  SALARY     NUMBER(9, 2)
9  )
10  TABLESPACE USERS;

Table created.

SQL>
SQL> INSERT INTO EMP4 (EMPNAME,DEPT,PHONENO,HIREDATE,SALARY) VALUES (‘TIM’,10,6309351100,SYSDATE,50000);

1 row created.

SQL> INSERT INTO EMP4 (EMPNAME,DEPT,PHONENO,HIREDATE,SALARY) VALUES (‘TIM’,10,6309351100,SYSDATE,50000);

1 row created.

SQL> INSERT INTO EMP4 (EMPNAME,DEPT,PHONENO,HIREDATE,SALARY) VALUES (‘TIM’,10,6309351100,SYSDATE,50000);

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> SELECT * FROM EMP4;

EMPNO EMPNAME               DEPT    PHONENO HIREDATE      SALARY
———- ————— ———- ———- ——— ———-
1 TIM                     10 6309351100 13-AUG-13      50000
2 TIM                     10 6309351100 13-AUG-13      50000
3 TIM                     10 6309351100 13-AUG-13      50000

SQL> UPDATE EMP4 SET EMPNO=2 WHERE EMPNO=1;

1 row updated.

SQL> COMMIT;

Commit complete.

SQL>  SELECT * FROM EMP4;

EMPNO EMPNAME               DEPT    PHONENO HIREDATE      SALARY
———- ————— ———- ———- ——— ———-
2 TIM                     10 6309351100 13-AUG-13      50000
2 TIM                     10 6309351100 13-AUG-13      50000
3 TIM                     10 6309351100 13-AUG-13      50000

Example to illustrate adding new identity columns  to existing tables.

When you add a new identity column to an existing  table all existing rows are updated using
the sequence generator. The order in which a value is assigned to each existing row cannot be determined.

Also you can change an existing column to identity column

CREATE TABLE EMP3
(
EMPNO         NUMBER(9)        PRIMARY KEY,
EMPNAME      VARCHAR2(15)    NOT NULL,
DEPT         NUMBER(4)       ,
PHONENO       NUMBER(10)      ,
HIREDATE      DATE            ,
SALARY        NUMBER(9, 2)
)
TABLESPACE USERS;

SQL>
SQL> SELECT * FROM EMP3;

EMPNO EMPNAME               DEPT    PHONENO HIREDATE      SALARY
———- ————— ———- ———- ——— ———-
101 TIM                     10 6309351100 13-AUG-13      50000
102 TIM                     10 6309351100 13-AUG-13      50000
103 TIM                     10 6309351100 13-AUG-13      50000

SQL>
SQL>

SQL>  ALTER TABLE EMP3 ADD (NEW_EMPNO NUMBER(9) GENERATED ALWAYS AS IDENTITY);

Table altered.

SQL>  SELECT * FROM EMP3;

EMPNO EMPNAME               DEPT    PHONENO HIREDATE      SALARY  NEW_EMPNO
———- ————— ———- ———- ——— ———- ———-
101 TIM                     10 6309351100 13-AUG-13      50000          1
102 TIM                     10 6309351100 13-AUG-13      50000          2
103 TIM                     10 6309351100 13-AUG-13      50000          3

Modifying existing column to identify column is not supported

SQL> ALTER TABLE EMP3 MODIFY  (EMPNO GENERATED ALWAYS AS IDENTITY);
ALTER TABLE EMP3 MODIFY  (EMPNO GENERATED ALWAYS AS IDENTITY);
*
ERROR at line 1:
ORA-30673: column to be modified is not an identity column

SQL>
SQL> ALTER TABLE EMP3 MODIFY  (EMPNO GENERATED BY DEFAULT AS IDENTITY);
ALTER TABLE EMP3 MODIFY  (EMPNO GENERATED BY DEFAULT AS IDENTITY)
*
ERROR at line 1:
ORA-30673: column to be modified is not an identity column

Upgrading to Oracle 12c: How different is it from previous versions?

upgrading to Oracle 12c August 11, 2013 1 Comment

1. Preupgrade Utility

Preupgrade utility “utlu121s.sql”  is replaced with “preupgrd.sql”. The new utility provides fixup scripts “preupgrade_fixups.sql” and “postupggrade_fixups.sql” to address issues that might be present both before and after the upgrade.These fixup scripts can be executed interactively.

2. Upgrade Utility

The catupgrd.sql Upgrade utility is replaced with the catctl.pl utility. The new utility allows you to take advantage of CPUs by running the upgrade process in parallel thereby reducing the upgrade time.  This new script is used more like a wrapper on the existing one. Also by default “catuppst.sql” is run when “catctl.pl” is executed. The new script has lot of options , few of them are explained below.

Option “-n” specifies the degree of parallelism, the default is 4 and maximum is 8.
Option “-p” supports rerun of the upgrade by skipping successful steps.

Example: $ORACLE_HOME/perl/bin/perl catctl.pl -n 6 -p $ORACLE_HOME/rdbms/admin/catupgrd.sql

3. XML DB

Oracle XML DB is no longer an optional component and therefore cannot be uninstalled. It is automatically installed into SYSAUX tablespace when you create a new 12c database or if you upgrade from prior versions of Oracle.

4. Upgrade Status

A successful upgrade sets the status of database status to UPGRADED. This is different from previous versions where the status is set to VALID.  The database status is set to VALID only if ultrp.sql is executed.

Oracle12c: Moving Datafiles Online

Oracle August 11, 2013 1 Comment

Renaming or relocating Oracle datafiles prior to Oracle 11g was not straight forward process. You had to perform 4 steps.

1. Take the tablespace offline.

2. Rename or relocate using OS

3. Issue ” ALTER DATABASE RENAME FILE”  command. All that this command did was update  the control files.

4. Bring the tablespace online.

Starting with Oracle12c, All you need is one single command and the best thing is that this is done online. In fact this command will work only on online datafiles. The new command is “ALTER DATABASE MOVE DATAFILE ‘file1’ TO ‘file2’;

This command copies/renames the datafile to new location, updates the controlfile and deletes the old datafile. You also have options to retain the old datafile with “KEEP” option but as far the database goes, the retained file is not part of database.

See example below

15:52:56 SQL> create tablespace myts datafile ‘/mydb06/oradata/mydb/myts01.dbf’ size 20G;

Tablespace created.

Elapsed: 00:00:55.01

15:54:48 SQL> alter database move datafile ‘/mydb06/oradata/mydb/myts01.dbf’ to ‘/mydb05/oradata/mydb/myts01.dbf’;

Database altered.

Elapsed: 00:05:32.34 ==> 5 minutes to copy & rename the file

16:37:32 SQL> select name , status from  v$datafile where name like ‘%ts%’;

NAME                                                                            STATUS
———————————————————————————-
/mydb05/oradata/mydb/myts01.dbf                                  ONLINE

Elapsed: 00:00:00.02

Major Drawback

Renaming files using offline (pre-12c) approach is much faster if the datafiles need to be just renamed on the same filesystem and not relocated. With the new approach, Oracle makes a copy of datafile irrespective of  whether KEEP option is specified or not. This  can be achieved very fast with  OS “mv” command using the offline(pre-12c) approach. So if you can afford to take the files offline and all you want is rename and not relocate, use the offline approach as it is very fast.

16:37:51 SQL> alter database move datafile ‘/mydb05/oradata/mydb/myts01.dbf’ to ‘/mydb05/oradata/mudb/mytabs01.dbf’;

Database altered.

Elapsed: 00:05:46.57
16:54:26 SQL>
-bash-3.2$ ls -latr
-rw-r—– 1 oracle dba 21474844672 Aug 11 16:08 myts01.dbf
-rw-r—– 1 oracle dba 21474844672 Aug 11 16:51 mytabs01.dbf
-bash-3.2$

Things to know before using this command

Dataguard

Move operations on the primary or standby is independent of each other

Flashback

Flashback operations will  retain the new location of datafile irrespective of the flashback time.

OS

Old files are not deleted on Windows

Multithreaded Architecture in Oracle12c.

Oracle August 10, 2013

Until Oracle11g, Oracle databases  on Unix/Linux OS followed process based architecture.  Starting with Oracle12c,  databases on Unix/Linux systems can be configured to support multithreaded architecture. In other words one process can have multiple operating system threads in separate address spaces.   Threads are very useful whenever a process has multiple tasks to be performed independent of others. Also  multiple threads within the same processes allows sharing of resources such as memory and CPU. So far with out much testing , I feel the benefits of multithreaded architecture  is mainly for multiple instance servers because of reduced memory/CPU and other resource consumption.

This is not to be confused with shared servers or Mutlithreaded servers. This feature can be implemented by setting initialization parameter THREADED_EXECUTION to TRUE and bouncing the database.

On my test database , the number of process with thread based architecture was reduced by 87%.  Even though Oracle does not guarantee  any given process to have a process architecture  v.s threaded architecture, I was able to consistently able to get the same results over multiple shutdowns on a single  instance server.

Number of process with process based systems(48 processes)

oracle@myserver:orcl12c:/app/oracle$ ps -ef | grep orcl12c
oracle    1880     1  0 Aug09 ?        00:00:00 ora_w006_orcl12c
oracle    2018     1  0 Aug09 ?        00:00:00 ora_w007_orcl12c
oracle    3765     1  0 06:00 ?        00:00:00 ora_vkrm_orcl12c
oracle    4812     1  0 Aug09 ?        00:00:00 ora_w008_orcl12c
oracle    4947     1  0 Aug09 ?        00:00:00 ora_w009_orcl12c
oracle   27527     1  0 Aug09 ?        00:00:01 ora_pmon_orcl12c
oracle   27529     1  0 Aug09 ?        00:00:01 ora_psp0_orcl12c
oracle   27531     1  0 Aug09 ?        00:00:00 ora_vktm_orcl12c
oracle   27535     1  0 Aug09 ?        00:00:00 ora_gen0_orcl12c
oracle   27537     1  0 Aug09 ?        00:00:00 ora_mman_orcl12c
oracle   27541     1  0 Aug09 ?        00:00:00 ora_diag_orcl12c
oracle   27543     1  0 Aug09 ?        00:00:00 ora_dbrm_orcl12c
oracle   27545     1  0 Aug09 ?        00:00:36 ora_dia0_orcl12c
oracle   27547     1  0 Aug09 ?        00:00:02 ora_dbw0_orcl12c
oracle   27549     1  0 Aug09 ?        00:00:02 ora_lgwr_orcl12c
oracle   27551     1  0 Aug09 ?        00:00:02 ora_ckpt_orcl12c
oracle   27553     1  0 Aug09 ?        00:00:00 ora_lg00_orcl12c
oracle   27555     1  0 Aug09 ?        00:00:00 ora_lg01_orcl12c
oracle   27557     1  0 Aug09 ?        00:00:00 ora_smon_orcl12c
oracle   27559     1  0 Aug09 ?        00:00:00 ora_reco_orcl12c
oracle   27561     1  0 Aug09 ?        00:00:00 ora_lreg_orcl12c
oracle   27563     1  0 Aug09 ?        00:00:46 ora_mmon_orcl12c
oracle   27565     1  0 Aug09 ?        00:00:24 ora_mmnl_orcl12c
oracle   27567     1  0 Aug09 ?        00:00:00 ora_d000_orcl12c
oracle   27569     1  0 Aug09 ?        00:00:00 ora_s000_orcl12c
oracle   27581     1  0 Aug09 ?        00:00:00 ora_tmon_orcl12c
oracle   27583     1  0 Aug09 ?        00:00:00 ora_tt00_orcl12c
oracle   27585     1  0 Aug09 ?        00:00:00 ora_smco_orcl12c
oracle   27587     1  0 Aug09 ?        00:00:00 ora_fbda_orcl12c
oracle   27589     1  0 Aug09 ?        00:00:00 ora_aqpc_orcl12c
oracle   27593     1  0 Aug09 ?        00:00:00 ora_p000_orcl12c
oracle   27595     1  0 Aug09 ?        00:00:00 ora_p001_orcl12c
oracle   27597     1  0 Aug09 ?        00:00:00 ora_p002_orcl12c
oracle   27599     1  0 Aug09 ?        00:00:00 ora_p003_orcl12c
oracle   27601     1  0 Aug09 ?        00:00:00 ora_p004_orcl12c
oracle   27603     1  0 Aug09 ?        00:00:00 ora_p005_orcl12c
oracle   27605     1  0 Aug09 ?        00:00:00 ora_p006_orcl12c
oracle   27607     1  0 Aug09 ?        00:00:00 ora_p007_orcl12c
oracle   27609     1  0 Aug09 ?        00:00:13 ora_cjq0_orcl12c
oracle   27644     1  0 Aug09 ?        00:00:00 ora_w000_orcl12c
oracle   27646     1  0 Aug09 ?        00:00:00 ora_qm02_orcl12c
oracle   27650     1  0 Aug09 ?        00:00:00 ora_q002_orcl12c
oracle   27652     1  0 Aug09 ?        00:00:00 ora_q003_orcl12c
oracle   28199     1  0 Aug09 ?        00:00:00 ora_w001_orcl12c
oracle   29096     1  0 Aug09 ?        00:00:00 ora_w002_orcl12c
oracle   31615     1  0 Aug09 ?        00:00:00 ora_w003_orcl12c
oracle   31726     1  0 Aug09 ?        00:00:00 ora_w004_orcl12c
oracle   31732     1  0 Aug09 ?        00:00:00 ora_w005_orcl12c

Number of process with thread based systems(6 processes)

oracle@myserver:orcl12c:/app/oracle$ ps -ef | grep orcl12c
oracle   15016     1  0 21:17 ?        00:00:00 ora_pmon_orcl12c
oracle   15018     1  0 21:17 ?        00:00:00 ora_psp0_orcl12c
oracle   15020     1  0 21:17 ?        00:00:00 ora_vktm_orcl12c
oracle   15024     1  1 21:17 ?        00:00:01 ora_u004_orcl12c
oracle   15030     1  0 21:17 ?        00:00:00 ora_u005_orcl12c
oracle   15036     1  0 21:17 ?        00:00:00 ora_dbw0_orcl12c
oracle@myserver:orcl12c:/app/oracle$ oracle@myserver:orcl12c:/app/oracle$

So what are implications of threaded architecture?

 

1. OS Authentication

I was not able to connect as “sys as sysdba” without password after  enabling multithreaded architecture.  As of now OS authentication is not supported with threaded architecture.

oracle@myserver:orcl12c:/app/oracle$
oracle@myserver:orcl12c:/app/oracle$ sqlplus

SQL*Plus: Release 12.1.0.1.0 Production on Sat Aug 10 21:17:29 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter user-name: sys as sysdba
Enter password:
ERROR:
ORA-01017: invalid username/password; logon denied

Enter user-name: sys/mypasswd as sysdba

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

2. Killing Sessions

There is no change in behavior to kill sessions from the database  as the sessions are killed based on serial#  using “ALTER SYSTEM KILL SESSION ‘SID, SERIAL#’. However you loose the flexibility of killing specific sessions based on process id using OS  “kill” command if the process have multiple threads. All processes and threads can be viewed by querying V$PROCESS which has a new column STID that provides information about the threads. See sample output below.

SQL> SELECT SPID, STID, PROGRAM FROM V$PROCESS ORDER BY  1;

SPID                     STID                     PROGRAM
———————— ———————— ————————————————
15137                    15137                    oracle@ps-zsign-db1 (PMON)
15139                    15139                    oracle@ps-zsign-db1 (PSP0)
15141                    15141                    oracle@ps-zsign-db1 (VKTM)
15145                    15158                    oracle@ps-zsign-db1 (LGWR)
15145                    15147                    oracle@ps-zsign-db1 (GEN0)
15145                    15145                    oracle@ps-zsign-db1 (SCMN)
15145                    15162                    oracle@ps-zsign-db1 (SMON)
15145                    15164                    oracle@ps-zsign-db1 (LREG)
15145                    15154                    oracle@ps-zsign-db1 (DBRM)
15145                    15159                    oracle@ps-zsign-db1 (CKPT)
15145                    15160                    oracle@ps-zsign-db1 (LG00)
15145                    15161                    oracle@ps-zsign-db1 (LG01)
15145                    15148                    oracle@ps-zsign-db1 (MMAN)
15151                    15155                    oracle@ps-zsign-db1 (DIA0)
15151                    15151                    oracle@ps-zsign-db1 (SCMN)
15151                    15153                    oracle@ps-zsign-db1 (DIAG)
15151                    15213                    oracle@ps-zsign-db1 (QM02)
15151                    15167                    oracle@ps-zsign-db1 (D000)
15151                    15216                    oracle@ps-zsign-db1 (Q003)
15151                    15163                    oracle@ps-zsign-db1 (RECO)
15151                    15165                    oracle@ps-zsign-db1 (MMON)
15151                    15166                    oracle@ps-zsign-db1 (MMNL)
15151                    15792                    oracle@ps-zsign-db1
15151                    15168                    oracle@ps-zsign-db1 (S000)
15151                    15169                    oracle@ps-zsign-db1 (N000)
15151                    15176                    oracle@ps-zsign-db1 (TMON)
15151                    15177                    oracle@ps-zsign-db1 (TT00)
15151                    15178                    oracle@ps-zsign-db1 (SMCO)
15151                    15179                    oracle@ps-zsign-db1 (FBDA)
15151                    15180                    oracle@ps-zsign-db1 (AQPC)
15151                    15206                    oracle@ps-zsign-db1 (W000)
15151                    15182                    oracle@ps-zsign-db1 (CJQ0)
15151                    15183                    oracle@ps-zsign-db1 (P000)
15151                    15184                    oracle@ps-zsign-db1 (P001)
15151                    15185                    oracle@ps-zsign-db1 (P002)
15151                    15186                    oracle@ps-zsign-db1 (P003)
15151                    15187                    oracle@ps-zsign-db1 (P004)
15151                    15188                    oracle@ps-zsign-db1 (P005)
15151                    15189                    oracle@ps-zsign-db1 (P006)
15151                    15190                    oracle@ps-zsign-db1 (P007)
15151                    15207                    oracle@ps-zsign-db1 (VKRM)
15151                    15643                    oracle@ps-zsign-db1 (W001)
15151                    15215                    oracle@ps-zsign-db1 (Q002) 15157                    15157                    oracle@ps-zsign-db1 (DBW0)

3. Listener Changes

Finally update listener.ora file to set DEDICATED_THROUGH_BROKER_listener-name=ON to enables the server process to spawn threads when connections to the database are requested through the listener.