Q&As: Parallelism

(Q) What types of statements and tasks can be improved through parallel execution?

• Queries requiring: large table scans, joins, or partitioned index scans
• Creation of large indexes
• Creation of large tables (and Materialized Views)
• Bulk Inserts, Updates, Meges and Deletes
• Scanning large objects (LOBs)

(Q) What are some of the characteristics of systems that can benefit from parallel execution?

• Have SMPs, clusters, or MPPs
• Sufficient IO bandwidth
• Underutilized CPUs (<30%)
• Enough memory free

(Q) When can OLTP systems mostly benefit from parallel execution?

• OLTP systems may benefit during batch processing and schema maintenance operations.

(Q) What are three key parameters controlling automatic parallel execution?

• PARALLEL_DEGREE_LIMIT
• PARALLEL_DEGREE_POLICY = { MANUAL | LIMITED | AUTO } <== MANUAL => disabled, AUTO => Auto DOP on
• PARALLEL_MIN_TIME_THRESHOLD = AUTO (default, about 10sec)

(Q) What is Intra-parallelism and Inter-parallelism?

• Intra-operation parallelism: parallelism of an individal operation
• Inter-operation parallelism: parallelism between operations in a data flow tree (in an execution plan)

(Q) What is the default degree of parallelism assumed by an Oracle Server?

• By Default, PARALLEL_DEGREE_POLICY = Manual (NO PARALLELISM)
• By default, the system only uses parallel execution when a parallel degree has been explicitly set on an object or if a parallel hint is specified in the SQL statement
• If PARALLEL_DEGREE_POLICY = AUTO, then
• Single Instance: DOP = PARALLEL_THREADS_PER_CPU X CPU_COUNT
• RAC: DOP = PARALLEL_THREADS_PER_CPU X CPU_COUNT x INSTANCE_COUNT
• important: in a multiuser environment, default parallelism is not recommended

(Q) What is the function of the PARALLEL_DEGREE_POLICY parameter? What vaules it takes?

• PARALLEL_DEGREE_POLICY = { MANUAL | LIMITED | AUTO }
• Enable/Disable (a) automatic degree of parallelism, (b) statement queueing, and (c) in-memory parallel execution
• MANUAL => Default. Revert to behavior prior to 11g. No parallelism automatically enabled
• LIMITED => Enables automatic degree of parallelism for some stmts only.
• AUTO => all three enabled.

(Q) How does the optimizer determines the degree of parallelism for a statement?

• Based on the resource requirements of the statement.
• Limit on parallelization is set by
• (a) PARALLEL_DEGREE_LIMIT (default = PARALLEL_THREADS_PER_CPU * CPU_COUNT * num instances)
• (b) PARALLEL_MIN_TIME_THRESHOLD (default = 10sec
  

(Q) Which parameters affect the automatic degree of parallelism adopted in the system?

• PARALLEL_DEGREE_POLICY = { MANUAL | LIMITED | AUTO }
• PARALLEL_DEGREE_LIMIT
• PARALLEL_MIN_TIME_THRESHOLD

==> The default degree of parallelism is MANUAL

SQL> show parameter parallel

NAME                           TYPE        VALUE
------------------------------ ----------- -------------
..
parallel_degree_limit           string      CPU
parallel_degree_policy      string      MANUAL    
...
parallel_min_time_threshold    string      AUTO
...

SQL> alter system set parallel_degree_policy=auto;
System altered.

SQL> conn sh/sh
Connected.

SQL> select degree from user_tables where table_name ='SALES';

DEGREE
----------
  1

SQL> alter table sales parallel 4;
Table altered.

SQL> select degree from user_tables where table_name ='SALES';

DEGREE
----------
  4

SQL> explain plan for select * from sales;
Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
Plan hash value: 1550251865

---------------------------------------------------------------------------------------------
| Id  | Operation     | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |       | 918K|  25M| 526   (9)| 00:00:07 |     |     |
|   1 |  PARTITION RANGE ALL|       | 918K|  25M| 526   (9)| 00:00:07 |   1 |  28 |
|   2 |   TABLE ACCESS FULL | SALES | 918K|  25M| 526   (9)| 00:00:07 |   1 |  28 |
---------------------------------------------------------------------------------------------
Note
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
13 rows selected.

==> Note here that no parallel execution took plase. 
    The estimated serial execution time (7sec) is still below the 10sec threshold used when the parallel_min_time_threshold is set to AUTO.  


==> Now, lets change the threshold to 1sec:

SQL> conn / as sysdba
Connected.
SQL> show parameter parallel

NAME                           TYPE        VALUE
------------------------------ ----------- -------------
..
parallel_degree_limit           string      CPU
parallel_degree_policy      string      MANUAL    
...
parallel_min_time_threshold    string      AUTO
...

SQL> alter system set parallel_min_time_threshold=1;
System altered.

SQL> show parameter parallel_min
NAME                             TYPE        VALUE
-------------------------------- ----------- -------
parallel_min_time_threshold      string      1

SQL> Conn sh/sh
Connected
SQL> explain plan for select * from sales;
Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3060979429

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |   918K|    25M|   291   (9)| 00:00:04 |       |       |        |      |            |
|   1 |  PX COORDINATOR      |          |       |       |            |          |       |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)| :TQ10000 |   918K|    25M|   291   (9)| 00:00:04 |       |       |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR |          |   918K|    25M|   291   (9)| 00:00:04 |     1 |    28 |  Q1,00 | PCWC |            |
|   4 |     TABLE ACCESS FULL| SALES    |   918K|    25M|   291   (9)| 00:00:04 |     1 |    28 |  Q1,00 | PCWP |            |
------------------------------------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------
Note
-----
   - automatic DOP: Computed Degree of Parallelism is 2
15 rows selected.

(ref) On Transactions, Locks and Cursors

Oracle Locking mechanisms

• locks prevent destructive interactions that incorrectly update data or incorrectly alter underlying data structures, between transactions accessing shared data.
• Two types: exclusive locks and share locks.
• Oracle's default locking mechanisms ensures data concurrency, data integrity, and statement-level read consistency.
• A row is locked only when modified by a writer.
• A writer of a row blocks a concurrent writer of the same row.
• A reader never blocks a writer.(except: SELECT .. FOR UPDATE)
• A writer never blocks a reader.

Lock modes: level of restrictiveness x degree of data concurrency

• The less restrictive the level, the more available the data is for access by other users.
• Exclusive lock mode:
• Prevents the resource from being shared. Obtained for data modification.
• The first transaction to lock a resource exclusively is the only transaction that can alter the resource until the exclusive lock is released.
• Share lock mode:
• Allows resource to be shared. Multiple users reading data can share the data, holding share locks to prevent concurrent access by a writer who needs an exclusive lock.
• Several transactions can acquire share locks on the same resource.
• If a SELECT ... FOR UPDATE selects a single table row, the transaction acquires (a) an exclusive row lock and (b) a row share table lock.
• The row lock allows other sessions to modify any rows other than the locked row, while the table lock prevents sessions from altering the structure of the table.
• Oracle Database never escalates locks

DML_LOCKS (init.ora)

Default: Derived: 4 * TRANSACTIONS (assumes an average of 4 tables referenced for each transaction)
Modifiable: No
Range: 20 to unlimited

Automatic Locks

• DML Lock
• also called data lock. Guarantees integrity of data concurrently accessed by multiple users.
• They are either Row Locks (TX) or Table Locks (TM).
• DML_LOCKS (init.ora): determines the maximum number of DML locks (one for each table modified in a transaction). You might need to increase it if you use explicit locks.
• DDL Locks
• Protects the definition of a schema object while an ongoing DDL operation acts on or refers to the object.
• Users cannot explicitly request DDL locks.
• Exclusive DDL lock: prevents other sessions from obtaining a DDL or DML lock.
• Share DDL lock: prevents conflicting DDL operations, but allows similar DDL operations.
• Breakable Parse Locks
• Held by a SQL statement or PL/SQL program unit for each schema object that it references. Acquired so that the associated shared SQL area can be invalidated if a referenced object is altered or dropped.
• A parse lock is acquired in the shared pool during the parse phase of SQL statement execution. The lock is held as long as the shared SQL area for that statement remains in the shared pool.

DML Locks
ROW LOCK (TX)	If a transaction obtains a lock for a row, then the transaction also acquires a lock for the table containing the row. (To prevent conflicting DDL operations). Oracle stores lock information in the data block that contains the locked row.
TABLE LOCK (TM)	acquired when a table is modified by an I,U,D, MERGE, SELECT FOR UPDATE, or LOCK TABLE statement. TM can be held as: Row Share (RS): (subshare table lock (SS)), indicates that the transaction holding the lock on the table has locked rows in the table and intends to update them. It is the least restrictive mode of table lock, offering the highest degree of concurrency for a table. Row Exclusive Table Lock (RX): (subexclusive table lock (SX)), indicates that the transaction holding the lock has updated table rows or issued SELECT ... FOR UPDATE. It An allows other transactions to query, insert, update, delete, or lock rows concurrently in the same table. Share Table Lock (S): allows other transactions to query the table (without using SELECT ... FOR UPDATE), but updates are allowed only if a single transaction holds the share table lock. Share Row Exclusive Table Lock (SRX): (share-subexclusive table lock (SSX)). More restrictive than a share table lock. Only one transaction at a time can acquire an SSX lock on a given table. Allows other transactions to query (except for SELECT ... FOR UPDATE) but not to update the table. Exclusive Table Lock (X): The most restrictive. Prohibits other transactions from performing any type of DML statement or placing any type of lock on the table.

• System Locks: Latches, Mutexes, and Internal locks
• Latches:
• Serializes access to memory structures. Protect shared memory resources from corruption when accessed by multiple processes. [ (a) Concurrent modification by multiple sessions; (b) Being read by one session while being modified by another session; (c) Deallocation (aging out) of memory while being accessed. ]
• i.e. while processing a salary update of a single employee, the database may obtain and release thousands of latches
• Increase in latching ==> decrease in concurrency. (i.e.: excessive hard parse operations create contention for the library cache latch.)
• V$LATCH: contains detailed latch usage statistics for each latch, including the number of times each latch was requested and waited for.
• Mutex (Mutual exclusion object)
• similar to a latch, but whereas a latch typically protects a group of objects, a mutex protects a single object.
• Internal Locks
• Dictionary cache locks, file and log mgmt locks, Tablespace and Undo segment locks)

Manual data Locks: overriding default

• LOCK TABLE
• SELECT FOR UPDATE clause
• SET TRANSACTION with the READ ONLY or ISOLATION LEVEL SERIALIZABLE option

On Isolation Levels

ALTER SESSION SET ISOLATION_LEVEL = {SERIALIZABLE | READ COMMITTED | READ ONLY} The ISOLATION_LEVEL parameter specifies how transactions containing database modifications are handled. It is a session parameter only, NOT an initialization parameter. SERIALIZABLE: transaction sees only changes committed at the time the transaction—not the query—began and changes made by the transaction itself. Transactions in the session use the serializable transaction isolation mode as specified in the SQL standard. If a serializable transaction attempts to execute a DML statement that updates rows currently being updated by another uncommitted transaction at the start of the serializable transaction, then the DML statement fails. A serializable transaction can see its own updates. READ COMMITTED: (default) Transactions in the session will use the default Oracle Database transaction behavior. every query executed by a transaction sees only data committed before the query—not the transaction—began. phantoms and fuzzy reads: because the database does not prevent other transactions from modifying data read by a query, other transactions may change data between query executions. Thus, a transaction that runs the same query twice may experience fuzzy reads and phantoms. If the transaction contains DML that requires row locks held by another transaction, then the DML statement will wait until the row locks are released.

LOCK TABLE

• explicitly locks one or more tables in a specified lock mode.
• The lock mode determines what other locks can be placed on the table.
• A table lock never prevents other users from querying a table, and a query never acquires a table lock
• When a LOCK TABLE statement is issued on a view, the underlying base tables are locked
• LOCK TABLE employees, departments IN EXCLUSIVE MODE NOWAIT;
• MODE: [ NOWAIT | WAIT n | ]
• NOWAIT: error if lock is not available immediately
• WAIT n: wait up to n secs
• <blank>: wait indefinitely to acquire the lock

what type of lock to use (check here)
ROW SHARE and ROW EXCLUSIVE SHARE MODE SHARE ROW EXCLUSIVE EXCLUSIVE MODE Let Oracle do the locking: [ SET TRANSACTION ISOLATION LEVEL level ] or [ ALTER SESSION ISOLATION LEVEL level ]

Transactions, TCL, and Isolation Levels

..later..

Autonomous transactions

Using SELECT... FOR UPDATE

• SELECT FOR UPDATE selects the rows of the result set and locks them.
• Enables you to base an update on the existing values in the rows, because it ensures that no other user can change those values before you update them.
• You can also use SELECT FOR UPDATE to lock rows that you do not want to update
• By default, SELECT FOR UPDATE waits until the requested row lock is acquired. To change this behavior, use the [ NOWAIT | WAIT | SKIP LOCKED ].
• When SELECT FOR UPDATE is associated with an explicit cursor, the cursor is called a FOR UPDATE cursor.

set serveroutput on
declare
 my_emp_ln employees.last_name%type;
 my_emp_id number;
 my_job_id varchar2(10);
 my_sal number(8,2);
 newsal number(8,2);
 
 -- declare a FOR UPDATE cursor
 cursor c1 is
   select employee_id, last_name, job_id, salary
   from employees for update;
begin
  open c1;
  loop
    fetch c1 into my_emp_id, my_emp_ln, my_job_id, my_sal;
    If my_job_id = 'SA_REP' then
      newsal := my_sal*1.12;
      -- update only the rows locked by the cursor
      -- identified through the "WHERE CURRENT OF" clause.
      update employees
      set salary = newsal
      where current of c1;
      dbms_output.put_line('Emp '|| my_emp_ln ||
               ': salary increased from '|| my_sal ||' to '|| newsal);
    end if;
    exit when c1%notfound;
  end loop;
end;

anonymous block completed
Emp Tucker: salary increased from 10000 to 11200
Emp Bernstein: salary increased from 9500 to 10640
...

Dynamic SQL: DBMS_SQL package

DBMS_SQL Package

SYS.DBMS_SQL is compiled with AUTHID CURRENT_USER (runs using the privileges of the current user)

• Defines a SQL cursor number (PL/SQL Integer): Can be passed across call boundaries and stored.
• Provides an interface to use dynamic SQL to parse ANY DML or DDL statement using PL/SQL.
• When HAS to be used?
• - you don't have the complete SELECT list
• - you don't know what placeholds in a SELECT or DML must be bound
• When you CANNOT use DBMS_SQL?
• - Dynamic SQL stmt retrieves rows into records
• - If you need to use cursor attributes (%FOUND, %ISOPEN, %NOTFOUND, or %ROWCOUNT)
• You can switch between native dynamic SQL AND DBMS_SQL package with:
• DBMS_SQL.TO_REFCURSOR function
• DBMS_SQL.TO_CURSOR_NUMBER function

Oracle Call Interface (OCI) x DBMS_SQL Package

• The ability to use dynamic SQL from within stored procedures generally follows the model of the Oracle Call Interface (OCI).
• Addresses (also called pointers) are NOT user-visible in PL/SQL.
• The OCI uses bind by address. DBMS_SQL package uses bind by value.
• With DBMS_SQL you must call VARIABLE_VALUE to retrieve the value of an OUT parameter for an anonymous block, and you must call COLUMN_VALUE after fetching rows to actually retrieve the values of the columns in the rows into your program.
• The current release of the DBMS_SQL package does not provide CANCEL cursor procedures.
• Indicator variables are not required, because NULLs are fully supported as values of a PL/SQL variable.

SYS.DBMS_SQL Subprograms
BIND_ARRAY (Ps) BIND_VARIABLE (Ps) CLOSE_CURSOR COLUMN_VALUE DEFINE_ARRAY DEFINE_COLUMN DESCRIBE_COLUMN (P) EXECUTE (F) EXECUTE_AND_FETCH (F) FETCH_ROWS (F) IS_OPEN (F) LAST_ERROR_POSITION (F) LAST_ROW_COUNT (F) LAST_ROW_ID LAST_SQL_FUNCTION_CODE OPEN_CURSOR (F) PARSE (Ps) TO_CURSOR_NUMBER (F) TO_REFCURSOR (F) VARIABLE_VALUE (Ps)

New security regime: Oracle 11gR1

• Stricter than in previous versions
• Checks are made when binding and executing.
• Failure lead to ORA-29470: Effective userid or roles are not the same as when cursor was parsed

Execution Flow:

1. OPEN_CURSOR
2. PARSE
3. BIND_VARIABLE or BIND_ARRAY
4. DEFINE_COLUMN, DEFINE_COLUMN_LONG, or DEFINE_ARRAY
5. EXECUTE
6. FETCH_ROWS or EXECUTE_AND_FETCH
7. VARIABLE_VALUE, COLUMN_VALUE, or COLUMN_VALUE_LONG
8. CLOSE_CURSOR

i.e. -- Procedure uses DBMS_SQL package to perform SELECT and DML statements.

Task: Delete all employees of a given deparmtent who earn more than a given value.

(A) DEL_EMP procedure is composed of the three parts below:
(1) Perform a dynamic SELECT to show the number of records that will be deleted
(2) Perform the dynamic DML.
(3) Perform a dynamic SELECT to show that the records were indeed deleted.

(B) an anonymous block calls del_emp procedure

CREATE OR REPLACE PROCEDURE del_emp (dept in number, salary in number) as 
 cursor_dml     integer;
 cursor_select  integer;
 rows_processed integer;
 v_numemp       integer;
 sel_str        varchar2(500);
 dml_str        varchar2(500);
 ignore         integer;

BEGIN
  -- Part 1: Select number BEFORE DML statement
  -- (1) Open cursor for SELECT.
  cursor_select := dbms_sql.open_cursor; 
  sel_str := 'Select count(*) nuemp from employees ' ||
            ' where department_id = :d';           

  -- (2) Parse SQL statement
  dbms_sql.parse(cursor_select, sel_str, dbms_sql.native);

  -- (3) Bind variables
  dbms_sql.bind_variable(cursor_select, ':d', dept);
  sel_str := 'Select count(*) nuemp from employees ' ||
            ' where department_id = '|| to_char(dept);            
  dbms_output.put_line('Select is: ' || sel_str); 

  -- (4) use define_column to specify the variables that 
  -- are to receive the SELECT values, much the way an 
  -- INTO clause does for a static query. 
  dbms_sql.define_column(cursor_select, 1, v_numemp);

  -- (5) call the execute function to run STMT
  ignore := dbms_sql.execute(cursor_select);
  -- (6) Use fetch_rows to retrieve the query results. 
  -- Each successive fetch retrieves another set of rows, 
  -- until the fetch is unable to retrieve anymore rows.
  -- If SELECT returns only ONE row, you may prefer to use 
  -- EXECTUVE_AND_FETCH instead.
  If dbms_sql.fetch_rows(cursor_select) > 0 then 
    dbms_sql.column_value(cursor_select, 1, v_numemp);
  end if;
  dbms_output.put_line('Num emp in dept '|| to_char(dept) || ' before delete: ' 
                   || to_char(v_numemp)); 

  -- (7) Close the cursor.
  dbms_sql.close_cursor(cursor_select);
 
  -- Part 2: Now proceed with the DML Statement
  dml_str := 'delete from employees where department_id = :d' || 
            ' and salary > :x';
  cursor_dml := dbms_sql.open_cursor;
  dbms_sql.parse(cursor_dml, dml_str, dbms_sql.native);
  dbms_sql.bind_variable(cursor_dml, ':d', dept);
  dbms_sql.bind_variable(cursor_dml, ':x', salary);
  rows_processed := dbms_sql.execute(cursor_dml);
  dbms_output.put_line('Num rows deleted: ' || rows_processed);
  dbms_sql.close_cursor(cursor_dml);
  
  -- Part 3: Select the number AFTER the DML statement
  cursor_select := dbms_sql.open_cursor; 
  sel_str := 'Select count(*) nuemp from employees ' ||
            ' where department_id = :d';           
  dbms_sql.parse(cursor_select, sel_str, dbms_sql.native);
  dbms_sql.bind_variable(cursor_select, ':d', dept);
  dbms_sql.define_column(cursor_select, 1, v_numemp);
  sel_str := 'Select count(*) nuemp from employees ' ||
            ' where department_id = '|| to_char(dept);            
  dbms_output.put_line('Select is: ' || sel_str); 
  dbms_sql.define_column(cursor_select, 1, v_numemp);
  ignore := dbms_sql.execute(cursor_select);
  If dbms_sql.fetch_rows(cursor_select) > 0 then 
    dbms_sql.column_value(cursor_select, 1, v_numemp);
  end if;  
  dbms_output.put_line('Num emp in dept '|| to_char(dept) || ' after delete: ' 
                   || to_char(v_numemp)); 
  dbms_sql.close_cursor(cursor_select);
EXCEPTION
 when others then 
   if dbms_sql.is_open(cursor_select) then
      dbms_sql.close_cursor(cursor_select);
  end if;
  if dbms_sql.is_open(cursor_dml) then 
     dbms_sql.close_cursor(cursor_dml);
  end if;
   -- Use Function SQLCODE to return error number 
   dbms_output.put_line('Error code: ' || to_char(sqlcode));

   -- Use Function SQLERRM to return error Message 
   dbms_output.put_line('Error Message: ' || to_char(sqlerrm));
 
   -- Starting on 10gR2, Oracle recommends that you use 
   -- DBMS_UTILITY.FORMAT_ERROR_STACK() instead of SQLERRM.
   -- While SQLERRM is limited to 512bytes, the other function is not. 
   dbms_output.put_line('Error stack: ' || dbms_utility.format_error_stack());

   -- You can also use DBMS_UTILITY.FORMAT_ERROR_BACKTRACE()
   -- this function lists the complete error stack, including the line number 
   -- that generated the exception.
   dbms_output.put_line('Error backtrace: ' || dbms_utility.format_error_backtrace());
END del_emp;

(B) Anonymous block calls DEL_EMP.

set serveroutput on
declare
  nemp integer;
begin
 del_emp(110, 6000);
 commit;
end;
/

anonymous block completed
Select is: Select count(*) nuemp from employees  where department_id = 110
Num emp in dept 110 before delete: 2
Num rows deleted: 2
Select is: Select count(*) nuemp from employees  where department_id = 110
Num emp in dept 110 after delete: 0

-- If the table employees is dropped or renamed, execution of del_emp will fail:

set serveroutput on
declare
  nemp integer;
begin
 del_emp(110, 6000);
 commit;
end;
/

anonymous block completed
Error code: -942
Error Message: ORA-00942: table or view does not exist
Error stack: ORA-00942: table or view does not exist

Error backtrace: ORA-06512: at "SYS.DBMS_SQL", line 1199
ORA-06512: at "DEVEL.DEL_EMP", line 17