Showing posts with label Parallelism. Show all posts
Showing posts with label Parallelism. Show all posts

Q&As: Parallelism and Advanced Compression




(Q) How can you manually set the degree of parallelism at object level?

  • ALTER TABLE sales PARALLEL 8;
  • You can set a fixed DOP at a table or index level


(Q) Which of the operations below can make use of parallel execution?

(1) When accessing objects: table scans, index fast full scans, partitioned index range scans
(2) Joins: nested loops, sort merges, hash, start transformations
(3) DDL staements: CTAS, Create Index, Rebuild Index, Rebuild Index Partition, Move/Split/Coalesce Partition
(4) DML statements
(5) Parallel Query
(6) Other SQL operations: group by, not in, select distinct, union, union all, cube, and rollup, aggregate and tables functions
(7) SQL*Loader i.e. $sqlldr CONTROL=load1.ctl DIRECT=true PARALLEL=true


(Q) In which type of objects parallel executions CANNOT be used?

Parallel DDL cannot be used on tables with object or LOB columns


(Q) How can you gather I/O Calibration statistics? How often should it be done?

  • Use DBMS_RESOURCE_MANAGER.CALIBRATE_IO procedure
  • I/O calibration is a one-time action if the physical hardware does not change.

Advanced Compression


(Q) What is Advanced Compression?

Introduced in 11g, includes compression for
  • structured data (numbers, chars)
  • Unstructured data (documents, images, etc)
  • backups (RMAN and Data Pump) and
  • Network transport (redo log transport during Data Guard gap resolution)



(Q) What are the benefits of Advanced Compression?

(a) storage reduction – compression of all types
(b) performance improvement – compressed blocks result in higher I/O throughput
(c) Memory efficiency – oracle keeps data compressed in memory
(d) backups – enhanced compression capabilities
(e) Data Guard – allows faster synchronization of databases during gap resolution process.



(Q) What improvement Advanced Compression brings to the table compression feature introducted in Oracle9i?

With Table compression feature – data could be compressed ONLY during bulk load operations
With Advanced CompressionDuring Inserts and Updates also. Also Compression and Deduplication of SecureFiles


(Q) Does table data in compressed tables get decompressed before it is read?

No. Oracle reads directly from compressed blocks in memory.


(Q) What features are included in the Advanced Compression option?

  • OLTP table compression – improved query performance with minimal write perf overhead
  • SecureFiles – SecureFiles compression for any unstructured content. Deduplication to reduce redundancy
  • RMAN – Multiple backup compression levels (faster --- better ratio)
  • Data Pump Compression – Exports can be coompressed
  • Data Guard – Can compress redo data (reduced network traffic, faster gap resolution)


(Q) What types of data compression can be done with RMAN (using Advanced Compression Option)

  • HiGH – Good for backups over slower networks
  • MEDIUM – Recommended for most environments. (about the same as regular compression)
  • LOW – Least effect on backup throughput


(Q) How to enable Advanced Compression option?

  • Set parameter enable_option_advanced_compression = TRUE
  • With Advanced compression option enabled, you can:
    • RMAN> CONFIGURE COMPRESSION ALGORITHM [HIGH|MEDIUM|LOW
  • V$RMAN_COMPRESSION_ALGORITHM describes supported algorithms
(Q) How can the various features under Advanced Compression be turned on?
For table Compression - Methods of Table compression on 11gR2:
Basic compression – direct path load only
  • i.e. CREATE/ALTER table … COMPRESS [BASIC] – Direct-path only
OLTP compression – DML operations
  • i.e. CREATE/ALTER table … COMPRESS FOR OLTP – Direct-path only
Warehouse compression (hybrid Columnar Compression) Online archival compression (hybrid columnar compression)
For SecureFiles -
i.e CREATE TABLE t1 (a CLOB) LOB(a) STORE AS SECUREFILE( COMPRESS LOW [MEDIUM|HIGH] DEDUPLICATE [KEEP_DUPLICATES] )
For RMAN -
RMAN> BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;
or
RMAN> CONFIGURE DEVICE TYPE [DISK | TAPE] BACKUP TYPE TO COMPRESSED BACKUPSET;
For Data Pump -
COMPRESSION = [ALL|DATA_ONLY|METADATA_ONLY|NONE]
  • ALL and DATA_ONLY requires ACO enabled.
  • i.e expdp hr DIRECTORY=dpump_dir1 DUMPFILE=hr_comp.dmp COMPRESSION=DATA_ONLY

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.