Sangam 16 Schedule https://sangam16.sched.org/

Hello Everyone, As you all know Sangam 16 event is about to come on 11 and 12 November 2016, Please check the schedule from the link

The List of Speakers is here

Protecting Schemas in Oracle 12C Vault

Recently i made a video tutorial for Protecting Schemas in Oracle 12C Vault, here is the Link

Recovering datafiles in Oracle 12C Multitenant Database

Recently i made a video tutorial for Recovering tablespaces in Oracle 12C Multitenant Database, here is the Link

Creating a database with Vault Option in Oracle 12C

Recently i made a video tutorial for Creating a database with Vault Option in 12C, here is the Link

Adding a database as a target to 12C Cloud Control

Recently i made a video tutorial for Adding a database as a target to 12C Cloud Control, here is the  Link

Exadata X6-2 enhancements in the hardware

While there are so many great features in Exadata Database Machine software, in this post we will see what is inside Exadata X6-2 when it comes to the hardware

Exadata X6-2 enhancements in the hardware

Database Server

# For each databaser server, Exadata X6 -2 Machine now comes with 2x 22-core Xeon E5-2699 v4 processors per Database Server
# Memory defaults to 256 GM RAM and 768 GB (max)
# disks 4x 600 GB 10,000 RPM disks (Hot-Swappable) Expandable to 8


For each Storage Server HC

CPU :- 2x 10-core Xeon E5-2630 v4 processors and the same goes for Extreme Flash Storage
Memory :- 128 GB
Disks :- 12 x 8 TB 7,200 RPM disks
Flash :- 4 x 3.2 TB NVMe PCIe 3.0 flash cards


For each Extreme Flash Storage Server

CPU :- 2x 10-core Xeon E5-2630 v4 processors
Memory :- 128 GB
Flash Capacity :- 8x 3.2 TB NVMe PCIe 3.0 flash drives


That means if you have a full rack Exadata Database Machine and if you go with Extreme flash storage you get 358.4 TB flash capacity, 8 x DB servers 352 cores , 14 x Storage servers 280 cores for SQL offload. Really Extreme performance.

Oracle Hash Scan

The Oracle Optimizer considers a hash scan when a query accesses a table in a hash cluster.

In a hash cluster, all rows with the same hash value are stored in the same data block. To perform a hash scan, Oracle Database first obtains the hash value by applying a hash function to a cluster key value specified by the statement. Oracle Database then scans the data blocks containing rows with that hash value. Now in this example, in order for Oracle to do a hash scan, Oracle Database first obtains the hash value by applying a hash function to the key value 30, and then uses this hash value to scan the data blocks and retrieve the rows.

Lets see how we can do it :-

CREATE CLUSTER employees_departments_cluster
   (deptno NUMBER(2)) SIZE 8192 HASHKEYS 100;

CREATE TABLE employees2
   CLUSTER employees_departments_cluster (deptno)
   AS SELECT * FROM emp;

CREATE TABLE departments2
   CLUSTER employees_departments_cluster (deptno)
   AS SELECT * FROM dept;

You query the employees in department 30 as follows:

SQL> SELECT * FROM   employees2 WHERE  deptno = 30;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1251        500         30
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30

6 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1423052330

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |     1 |    87 |     1   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS HASH| EMPLOYEES2 |     1 |    87 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("DEPTNO"=30)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
         15  recursive calls
          0  db block gets
         77  consistent gets
         64  physical reads
          0  redo size
       1279  bytes sent via SQL*Net to client
        551  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  rows processed