Parallel Execution of SQL Statements

Parallelism is the idea of breaking down a task so that, instead of one process doing all of the work in a query, many processes do part of the work at the same time.

SQL Operations that can be parallelized.

• Parallel Query
• Parallel DDL (Can't be used on the tables with Object or LOB type columns)
• Parallel DML
• SQL * Loader

Degree of Parallelism :

The number of parallel execution servers associated with a single operation is known as the Degree of parallelism.

Example:
With Select Statement

SELECT /*+ PARALLEL(employees 4) PARALLEL(departments 4) USE_HASH(employees)

ORDERED */

       MAX(salary), AVG(salary)

FROM employees, departments

WHERE employees.department_id = departments.department_id

GROUP BY employees.department_id;

With SQL Loader :
Available with Direct path data loads only 
$sqlldr control=emp.ctl parallel=true direct=true 

Note that hints have been used in the query to force the join order and join method, and to specify the degree of parallelism (DOP) of the tables employees and departments.

Generating Random Values in Oracle

It can be done by using the package DBMS_RANDOM.

Different functions available in DBMS_RANDOM package :

• RANDOM
• VALUE
• STRING

Examples:

1.  select SYS.DBMS_RANDOM.RANDOM FROM DUAL;
   It will return any random value
   
2. SELECT SYS.DBMS_RANDOM.VALUE FROM DUAL;
   It will return a numeric value between 0 and 1
   
3. SELECT SYS.DBMS_RANDOM.VALUE(1,1000) FROM DUAL;
   Returns any value between 1 and 1000.
   We need to use TRUNC function to remove the decimal is generated by this package
   SELECT TRUNC(SYS.DBMS_RANDOM.VALUE(1,1000)) FROM DUAL;
   
4. select SYS.DBMS_RANDOM.STRING('U',8) from DUAL;
   Returns the string with the 8 characters in uppercase
   
5. select SYS.DBMS_RANDOM.STRING('L',8) from DUAL;
   Returns the string with the 8 characters in lowercase
   A --> Alpha numeric
   X --> Alphanumeric with lower case

Table Partitioning in Oracle

Partitioning:

                A partitioned object has multiple pieces that can be managed either individually or collectively

Partitioning Key :

                Each row in a partitioned table is unambiguously assigned to  a partition. The partition key consists of one or more columns that determine the partition where each row is stored.

When to Partition a Table:

Here are some suggestions

• ·         Table greater than 2GB
• ·         Tables containing historical data, in which new data is added into new partition
• ·         When contents of table to be distributed across different storage devices

Benefits of Partitioning :

• ·         Performance
• ·         Availability
• ·         Manageability

Partitioning Strategies:

•      Range based
•       List based
•       Hash

Hash Partitioning :

Use hash partitioning if your data does not easily lend itself to range partitioning, but you would like to partition for performance and manageability reasons. Rows are mapped into partitions based on a hash value of the partitioning key

CREATE table SCOTT.STUDENT_HASH_PARTITION

(STDNO number(8),

  FIRSTNAME varchar2(10),

  MIDDLENAME varchar2(10),

  LASTNAME varchar2(10),

  MOBILENO varchar2(12),

  PHONENO varchar2(12)

)

partition by hash(STDNO)

partitions 4

STORE IN (STUDENT1,STUDENT2,STUDENT3,STUDENT4);

Benefits of Table Partitioning :

• Instead of having a 100 gig tablespace to backup, you have 100, 1 gig tablespaces. (each tablespace spends less time in backup mode, reduces the amount of potential extra redo, reduces the amount of manual recovery you need to do if the instance failes during backup).  same with restores.
• instead of having an index on a 100 gig table, you have say 100 indexes on 1 gig tables.  You can rebuild each one fast.... (and online with minimal overhead).  rebuilding a single index on a 100 gig table -- that would take maybe 100 times longer then each individual rebuild (true parallel query might reduce that).
• You can analyze each partition quickly, instead of running an analyze on a 100 gig table.
• You can reorg each partition independent of any other partition
• You can easily redistribute the load over many disks, you now have evenly distributed the data into 100 1 gig partitions, move them at will.  If you did this manually -- you would have a heck of a time moving the stuff around.

How can I delete the data in a single partition :
   SQL> ALTER TABLE <TABLENAME> DROP PARTITION <PARTITIONNAME>;

Tablespace Encryption

It introduced in 10g R2 .

It prevents the access to it from the Operating system. It allows encryption of the entire content of tablespace instead of column-by-column basis.

Wallet Creation:

Wallet must be created to hold the encryption key.

To setup the Encryption wallet Location, Configure the parameter in sqlnet.ora file

ENCRYPTION_WALLET_LOCATION=

  (SOURCE=(METHOD=FILE)(METHOD_DATA=

    (DIRECTORY=/u01/app/oracle/admin/DB11G/encryption_wallet/)))

Then the following command creates and open the wallet.

CONN sys/password@db11g AS SYSDBA

ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "myPassword";

WALLET must be reopened after an instance restart and can be closed to prevent access to encrypted data

ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "myPassword";

ALTER SYSTEM SET ENCRYPTION WALLET CLOSE;

Tablespace Creation:

CREATE TABLESPACE encrypted_ts

DATAFILE '/u01/app/oracle/oradata/DB11G/encrypted_ts01.dbf' SIZE 128K

AUTOEXTEND ON NEXT 64K

ENCRYPTION USING 'AES256'

DEFAULT STORAGE(ENCRYPT);

ALTER USER test QUOTA UNLIMITED ON encrypted_ts;

SELECT tablespace_name, encrypted FROM dba_tablespaces;

TABLESPACE_NAME                ENC

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

SYSTEM                         NO

SYSAUX                         NO

UNDOTBS1                       NO

TEMP                           NO

USERS                          NO

ENCRYPTED_TS                   YES

6 rows selected.

Test Encryption:

CONN test/test@db11g

CREATE TABLE ets_test (

  id    NUMBER(10),

  data  VARCHAR2(50)

)

TABLESPACE encrypted_ts;

CREATE INDEX ets_test_idx ON ets_test(data) TABLESPACE encrypted_ts;

INSERT INTO ets_test (id, data) VALUES (1, 'This is a secret!');

COMMIT;

Database Startup :

SQL>CONN / AS SYSDBA

Connected.

SQL> SHUTDOWN IMMEDIATE;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> STARTUP

ORACLE instance started.

Total System Global Area  626327552 bytes

Fixed Size                  2255832 bytes

Variable Size             234882088 bytes

Database Buffers          381681664 bytes

Redo Buffers                7507968 bytes

Database mounted.

Database opened.

SQL> SELECT tablespace_name, encrypted, status FROM dba_tablespaces;

TABLESPACE_NAME                ENC STATUS

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

SYSTEM                         NO  ONLINE

SYSAUX                         NO  ONLINE

UNDOTBS1                       NO  ONLINE

TEMP                           NO  ONLINE

USERS                          NO  ONLINE

EXAMPLE                        NO  ONLINE

SOE                            NO  ONLINE

ENCRYPTED_TS                   YES ONLINE

8 rows selected.

SQL> SELECT * FROM test.ets_test;

select * from test.ets_test

                   *

ERROR at line 1:

ORA-28365: wallet is not open

SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "myPassword";

System altered.

sys@db11g> select * from test.ets_test;

        ID DATA

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

         1 This is a secret!

1 row selected.

SQL>