(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 Compression – During 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?
(Q) How can the various features under Advanced Compression be turned on?
- 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
For table Compression - Methods of Table compression on 11gR2:Basic compression – direct path load onlyFor SecureFiles -OLTP compression – DML operations
- i.e.
CREATE/ALTER table … COMPRESS [BASIC]
– Direct-path onlyWarehouse compression (hybrid Columnar Compression) Online archival compression (hybrid columnar compression)
- i.e.
CREATE/ALTER table … COMPRESS FOR OLTP
– Direct-path onlyi.eFor RMAN -CREATE TABLE t1 (a CLOB) LOB(a) STORE AS SECUREFILE( COMPRESS LOW [MEDIUM|HIGH] DEDUPLICATE [KEEP_DUPLICATES] )
For Data Pump -RMAN> BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;
or
RMAN> CONFIGURE DEVICE TYPE [DISK | TAPE] BACKUP TYPE TO COMPRESSED BACKUPSET;
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