Parallel Server

Parallel Hints
Select                              /*+PRALLEL(<tb_name>, 8|DEFAULT)*/
                                     /*+PARALLEL_INDEX(<tb_name>, <ind_name>,3,2)*/

1- alter table <tb_name> parallel (degree 8)
2- select /*+PARALLEL(<tb_name>,<degree>) from <tb_name>
(user number is high: set up; degree level up: set down)
Parallel DML|DDL:
Alter session [enable|disable] parallel [dml|ddl];
İnsert /*+PARALLEL(emp_big,4,1)*/ into <table_name>
Parallel DDL:

Create table <tb_name> parallel (degree 8)
Create index <ind_name> on <tb_name> (<col_name>).... parallel (degree 8)
Parallel Loading:
Sqlldr direct=true parallel=true
Parallel Recovery:
Recover tablespace <tbs_name> parallel (degree [8|DEFAULT])
Parallel Replication:
INTERVAL -> ‘sysdate+1/24,
NEXT_DATE -> ‘sysdate+1/24’,
Alter session force parellel DDL parallel 5;
degree of parallelism ‐‐number of parallel execution servers
‐ A PARALLEL clause in a statement
‐ The PARALLEL clause that was used when the object was created or altered
‐ A parallel hint inserted into the statement
‐ A default determined by Oracle
Parallel execution can be tuned for you automatically by setting the initialization parameter;
Alter session force PARALLEL DDL | DML PARALLEL 5;
Alter system set parallel_max_servers = <degree of max parallism>;
***** *****
***** *****
Extended rowid needs 10 bytes of storage on disk, is displayed by 18 chars.
Data object number             //table,index                        //32 bits
Relative file number           //unique for each file             //10bits
Block number                      //position of the block              //22 bits
Row number                        //position of the row                 //16 bits
SQL>select department_id, row_id from hr.departments;

 Create table hr.employees(Employee_id number(6),
                           First_name varchar2(20),
                           Last_name varchar2(25),
                           Hire_date   date default sysdate) Storage(Initial 200k,
                                                                     Next 200k,
                                                                     Pctincrease 0,
                                                                     Minextents 1,
                                                                    Maxextents 5) Tablespace data;
Create temprary table hr.employees_temp as select * from hr.employees;
Compute pctfree and pctused values
Pct used:                    [(Average row size – initial row size) * 100] / Average row size
Pct used:                   [100 – pct free – (average row size * 100)] / Available data space

SQL> Alter table hr.employees pctfree 30 pctused 50 storage(
Next 500k
Minextents 2
Maxextents 100);
Alter table hr.employees allocate extent (size 500k datafile ‘/disk3/data1.dbf’);
Alter table hr.employees move tablesapce data1;

Truncate table hr.employees;
Drop table hr.employees cascade constrains;
Alter table hr.employees drop column comments cascade constrains checkpoint 1000;
Alter table hr.employees set unusued column comments cascade constrains;
Alter table hr.employees drop unusued columns checkpoint 1000;

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: