Parallel Execution in Exadata

Parallel execution is an Oracle feature that allows one process to split the job it needs to do in several subprocesses. This job, implies I/O and CPU operations.
This feature is controlled by several parameters at instance level and the DEGREE value at segments definition level.
By default, including 12.1.0, Oracle has defined parameter PARALLEL_DEGREE_POLICY = MANUAL and by default all segments are created with Degree of Parallelism (DoP) set to 1.
It means that, Oracle will execute all SQL in serial, by default

When Parallel Execution is needed, a specific DoP can be manually set using three different ways:

1.- Set a fixed DoP at segment level:

ALTER TABLES SALES PARALLEL 6;

In this case, queries accessing the SALES table via full scan use a requested DoP of 6.
2.-Use PARALLEL hint in SQL

SQL>ALTER TABLE SALES PARALLEL 1;
SQL>SELECT /*+ PARALLEL(6) */ count(*) from SALES;

In this case, this query uses a request DoP of 6.

3.-“Force” the session to run in parallel

SQL> ALTER SESSION FORCE PARALLEL QUERY PARALLEL 6;

For above cases, the execution plan is the same

Parallel Execution Details (DOP=6 , Servers Allocated=6)
 ============================================================================================================================================
 | Name | Type | Server# | Elapsed | Cpu | IO | Application | Other | Buffer | Read | Read | Cell | Wait Events |
 | | | | Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | Offload | (sample #) |
 ============================================================================================================================================
 | PX Coordinator | QC | | 0.57 | 0.01 | | 0.00 | 0.56 | 5 | | . | NaN% | |
 | p000 | Set 1 | 1 | 0.26 | 0.07 | 0.19 | | 0.00 | 15041 | 130 | 117MB | 98.03% | |
 | p001 | Set 1 | 2 | 0.28 | 0.07 | 0.20 | | 0.01 | 16198 | 141 | 126MB | 98.03% | |
 | p002 | Set 1 | 3 | 0.27 | 0.07 | 0.19 | | 0.00 | 16198 | 140 | 126MB | 98.00% | |
 | p003 | Set 1 | 4 | 0.27 | 0.07 | 0.20 | | | 16198 | 140 | 126MB | 97.94% | |
 | p004 | Set 1 | 5 | 0.27 | 0.07 | 0.20 | | 0.00 | 15041 | 130 | 117MB | 98.02% | |
 | p005 | Set 1 | 6 | 0.27 | 0.07 | 0.20 | | | 15124 | 140 | 118MB | 98.05% | |
 ============================================================================================================================================

SQL Plan Monitoring Details (Plan Hash Value=2278008498)
 ===============================================================================================================================================================================
 | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Cell | Mem | Activity | Activity Detail |
 | | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Offload | (Max) | (%) | (# samples) |
 ===============================================================================================================================================================================
 | 0 | SELECT STATEMENT | | | | 1 | +1 | 1 | 1 | | | | | | |
 | 1 | SORT AGGREGATE | | 1 | | 1 | +1 | 1 | 1 | | | | | | |
 | 2 | PX COORDINATOR | | | | 2 | +0 | 7 | 6 | | | | | | |
 | 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | 1 | +1 | 6 | 6 | | | | | | |
 | 4 | SORT AGGREGATE | | 1 | | 1 | +1 | 6 | 6 | | | | | | |
 | 5 | PX BLOCK ITERATOR | | 490K | 4711 | 1 | +1 | 6 | 490K | | | | | | |
 | 6 | TABLE ACCESS STORAGE FULL | SALES | 490K | 4711 | 1 | +1 | 82 | 490K | 821 | 730MB | 98.01% | 18M | | |
 ===============================================================================================================================================================================

But the question is, when do I need to execute a sentence in parallel?
The answer always depends on the environment you are playing in.

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. This work can be broken down in two different types: I/O work and CPU work.

Here is when EXADATA environments are different. When Full Scan is used in EXADATA, Smart Scan goes into action, using all cell nodes and doing all I/O in parallel.
So, does it has sense to use parallel when the work for a SQL happen at I/O?
The answer for this is no, because there is a cost associated with coordinating the parallel execution servers and the cost of this coordination may outweigh the benefits of parallelism.
So, under this premise, in EXADATA, parallelism has sense when the amount of CPU used by the SQL statement is high enough to get benefit from it.
This implies than the better option for enabling parallelism is using the PARALLEL hint into your SQL and use it only when it is really needed.

One last point, Tanel Poder explains here that above three options will not FORCE the parallel execution, but rather just reduces optimizer cost estimates for full scans.

DB_BLOCK_CHECKSUM and Risk Perception

DBAs are always concerned about the performance impact of features like db_block_checksum or db_block_checking.  told me an story that several years ago, he met a lot of people who had turned off redo logging with an underscore parameter. The performance they’d get from doing this would set the expectation level in their mind, which would cause them to resist (strenuously!) any notion of switching this [now horribly expensive] logging back on. Of course, it makes you wish that it weren’t even a parameter.

Cary believes that the right analysis is to think clearly about risk. Risk is a “soft” word in most people’s minds, but in finance they teach that risk is quantifiable as a probability distribution. For example, you know every day that it’s possible for a disk drive in your system to go bad. The risk of that happening can actually be calculated. For disks, it’s not too difficult, because vendors do those calculations (MTTF) for us. The probability that you’ll wish you had set db_block_checksum enabled today is probably more difficult to compute.

From a psychology perspective, DBAs would be happier if their systems were set to full or typical values to begin with. Then in response to the question, “Would you like us to remove your safety net in exchange for going between 1% and 10% faster? Here’s the horror you might face if we do it…” I’d wager that most people would say no, thank you. They will react emotionally to the idea of their safety net being taken away.

But with the baseline of its being turned off to begin with, the question is “Would you like us to install a safety net in exchange for slowing your system down between 1% and 10%? Here’s the horror you might face if we don’t…” I’d wager that most people would answer no, thank you (the opposite verdict!), because they will react emotionally to the idea of their performance being taken away. If they had been burned by block corruptions before (which is me case because I worked for corruption team at Oracle Support), then I’m going to favor the safety net argument.

But now the question is: “if I’ve never faced a corruption and I have my disks mirrored and I have a good database backup strategy, why do I need to set these kind of parameters that will impact my performance?” The answer is “Because in other case, we can not be 100% sure  that Oracle is reliable to detect the corruption and could provide you wrong information and (the worst) you wont realise!

By default, Oracle has two internal mechanisms to validate the health of a block from physical structure point of view:

  1. At block tail, Oracle stores some information that must match some information stored at the header of the block. In case these two pieces do not match, the block is reported as FRACTURED when it is validated. ORA-1578 error is reported if this problem is found. This validation cannot be disabled. You can see a good explanation for fractured blocks in MOS.
  2. The other mechanism enabled by default is DB_BLOCK_CHECKSUM. In this case, DBWr and the direct loader calculate a checksum (a number calculated from all the bytes stored in the block) and store it in the cache header of every data block when writing it to disk. Checksums are recalculated when blocks are read and compared with the information at the header. In case they do not match, block is reported as CHECKSUM corrupted and ORA-1578 error is reported at client side.

This DB_BLOCK_CHECKSUM functionality can be disabled in order to improve performance between 1% and 5% for everything but SYSTEM tablespace but, are you able to assume the risk?

Lets try to see an example and simulate a hardware failure in the middle of a block when DB_BLOCK_CHECKSUM is disabled

  • We identify the rows we have stored in a row, in this case file 5 block 206. This is an 8k block tablespace.
SQL> select employee_id, salary from hr.employees where rowid>'AAAWZpAAKAAAADNCcP' and rowid<'AAAWZpAAKAAAADPAAA';

EMPLOYEE_ID SALARY
----------- ----------
 100 24000
 101 17000
 102 17000
 103 9000
 104 6000
 105 4800
 106 4800
 107 4200
 108 12008
 109 9000
 110 8200
...
...
98 rows selected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
  • Now, we take a copy of a piece of the block  storing these rows (512 bytes)
[oracle@oel6-121 DBTEST]$dd if=example01.dbf of=part02.dbf bs=512 skip=3299 count=1
SQL> startup
ORACLE instance started.
Total System Global Area 2499805184 bytes
Fixed Size 2927480 bytes
Variable Size 671089800 bytes
Database Buffers 1811939328 bytes
Redo Buffers 13848576 bytes
Database mounted.
Database opened.

SQL> update hr.employees set salary=salary+1 where rowid>'AAAWZpAAKAAAADNCcP' and rowid<'AAAWZpAAKAAAADPAAA';
98 rows updated.

SQL> commit;
Commit complete.

SQL> select employee_id, salary from hr.employees where rowid>'AAAWZpAAKAAAADNCcP' and rowid<'AAAWZpAAKAAAADPAAA';

EMPLOYEE_ID SALARY
----------- ----------
 100 24001
 101 17001
 102 17001
 103 9001
 104 6001
 105 4801
 106 4801
 107 4201
 108 12009
 109 9001
 110 8201
...
...
 177 8401
 178 7001
 179 6201
 180 3201
 181 3101
 182 2501
 183 2801
 184 4201
 185 4101
 186 3401
 187 3001
 188 3801
 189 3601
 190 2901
 191 2501
 192 4001
 193 3901
 194 3201
 195 2801
 196 3101
 197 3001

98 rows selected.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
  • So far so good, but now lets simulate the problem putting back the block piece taken before the UPDATE into the middle of the updated block.
[oracle@oel6-121 DBTEST]$ dd if=example01.dbf of=part01.dbf bs=512 count=3299
3299+0 records in
3299+0 records out
1689088 bytes (1.7 MB) copied, 0.01112 s, 152 MB/s
[oracle@oel6-121 DBTEST]$ dd if=example01.dbf of=part03.dbf bs=512 skip=3300
2543916+0 records in
2543916+0 records out
1302484992 bytes (1.3 GB) copied, 7.75558 s, 168 MB/s
[oracle@oel6-121 DBTEST]$ cat part01.dbf part02.dbf part03.dbf >file.dbf
[oracle@oel6-121 DBTEST]$ rm example01.dbf 
[oracle@oel6-121 DBTEST]$ mv file.dbf example01.dbf
SQL> startup
ORACLE instance started.
Total System Global Area 2499805184 bytes
Fixed Size 2927480 bytes
Variable Size 671089800 bytes
Database Buffers 1811939328 bytes
Redo Buffers 13848576 bytes
Database mounted.
Database opened.
SQL> select employee_id, salary from hr.employees where rowid>'AAAWZpAAKAAAADNCcP' and rowid<'AAAWZpAAKAAAADPAAA';

EMPLOYEE_ID SALARY
----------- ----------
 100 24001
 101 17001
 102 17001
 103 9001
 104 6001
 105 4801
 106 4801
 107 4201
 108 12009
 109 9001
 110 8201
...
...
 177 8401
 178 7001
 179 6201
 180 3201
 181 3101
 182 2501
 183 2801
 191
 192 4001
 193 3901
 194 3201
 195 2801
 196 3101
 197 3001

91 rows selected.
  • We have lost 7 rows and we have 1 row with no salary…..and there is no error anywhere!!!

In case, if DB_BLOCK_CHECKSUM had been enabled, we had seen ORA-1578 error and we could restore the block from a backup.

In summary, as my colleague Gabriel Alonso (a.k.a Gaby) says, disabling DB_BLOCK_CHECKSUM is like trying to land a plane without any instrumentation at night in the middle of a hurricane – at least risky.