Using compression in a not exist filter is a very bad idea.
A few days ago ; one of customers had a problem about daily running jobs.
When they rebuilt some tables ; a huge performance problem occured. We had to spent some diagonastcs and analyzes to find out the root cause .
There a a not exist fileter ;
and the tablspace was default compression enabled ; when the filter table was re-created it was in a compressed format ; which caused .
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 150M(100)| | | | |
| 1 | LOAD AS SELECT | | | | | | | | |
| 2 | FILTER | | | | | | | | |
| 3 | PX COORDINATOR | | | | | | | | |
| 4 | PX SEND QC (RANDOM) | :TQ10002 | 9409K| 511M| 628 (35)| 00:00:04 | Q1,02 | P->S | QC (RAND) |
| 5 | HASH JOIN BUFFERED | | 9409K| 511M| 628 (35)| 00:00:04 | Q1,02 | PCWP | |
| 6 | PX RECEIVE | | 8984K| 171M| 142 (63)| 00:00:01 | Q1,02 | PCWP | |
| 7 | PX SEND HASH | :TQ10000 | 8984K| 171M| 142 (63)| 00:00:01 | Q1,00 | P->P | HASH |
| 8 | PX BLOCK ITERATOR | | 8984K| 171M| 142 (63)| 00:00:01 | Q1,00 | PCWC | |
| 9 | TABLE ACCESS STORAGE FULL| ACCTSTATUS | 8984K| 171M| 142 (63)| 00:00:01 | Q1,00 | PCWP | |
| 10 | PX RECEIVE | | 7383K| 260M| 471 (25)| 00:00:03 | Q1,02 | PCWP | |
| 11 | PX SEND HASH | :TQ10001 | 7383K| 260M| 471 (25)| 00:00:03 | Q1,01 | P->P | HASH |
| 12 | PX BLOCK ITERATOR | | 7383K| 260M| 471 (25)| 00:00:03 | Q1,01 | PCWC | |
| 13 | TABLE ACCESS STORAGE FULL| KEY_SUBSCRIPTION | 7383K| 260M| 471 (25)| 00:00:03 | Q1,01 | PCWP | |
| 14 | TABLE ACCESS BY INDEX ROWID | SUBSCR_STATUS | 1 | 35 | 16 (0)| 00:00:01 | | | |
| 15 | INDEX RANGE SCAN | PK_SUBSCR_STATUS | 13 | | 3 (0)| 00:00:01 | | | |
-----------------------------------------------------------------------------------------------------------------------------------
on the left one ; 1M record is searhed just in 71 seconds;
on the right one; 185k record is searhed in 125 seconds ; and counting on
roughtly 10X worse.
When they rebuilt some tables ; a huge performance problem occured. We had to spent some diagonastcs and analyzes to find out the root cause .
There a a not exist fileter ;
and the tablspace was default compression enabled ; when the filter table was re-created it was in a compressed format ; which caused .
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 150M(100)| | | | |
| 1 | LOAD AS SELECT | | | | | | | | |
| 2 | FILTER | | | | | | | | |
| 3 | PX COORDINATOR | | | | | | | | |
| 4 | PX SEND QC (RANDOM) | :TQ10002 | 9409K| 511M| 628 (35)| 00:00:04 | Q1,02 | P->S | QC (RAND) |
| 5 | HASH JOIN BUFFERED | | 9409K| 511M| 628 (35)| 00:00:04 | Q1,02 | PCWP | |
| 6 | PX RECEIVE | | 8984K| 171M| 142 (63)| 00:00:01 | Q1,02 | PCWP | |
| 7 | PX SEND HASH | :TQ10000 | 8984K| 171M| 142 (63)| 00:00:01 | Q1,00 | P->P | HASH |
| 8 | PX BLOCK ITERATOR | | 8984K| 171M| 142 (63)| 00:00:01 | Q1,00 | PCWC | |
| 9 | TABLE ACCESS STORAGE FULL| ACCTSTATUS | 8984K| 171M| 142 (63)| 00:00:01 | Q1,00 | PCWP | |
| 10 | PX RECEIVE | | 7383K| 260M| 471 (25)| 00:00:03 | Q1,02 | PCWP | |
| 11 | PX SEND HASH | :TQ10001 | 7383K| 260M| 471 (25)| 00:00:03 | Q1,01 | P->P | HASH |
| 12 | PX BLOCK ITERATOR | | 7383K| 260M| 471 (25)| 00:00:03 | Q1,01 | PCWC | |
| 13 | TABLE ACCESS STORAGE FULL| KEY_SUBSCRIPTION | 7383K| 260M| 471 (25)| 00:00:03 | Q1,01 | PCWP | |
| 14 | TABLE ACCESS BY INDEX ROWID | SUBSCR_STATUS | 1 | 35 | 16 (0)| 00:00:01 | | | |
| 15 | INDEX RANGE SCAN | PK_SUBSCR_STATUS | 13 | | 3 (0)| 00:00:01 | | | |
-----------------------------------------------------------------------------------------------------------------------------------
on the left one ; 1M record is searhed just in 71 seconds;
on the right one; 185k record is searhed in 125 seconds ; and counting on
on the right one; 185k record is searhed in 125 seconds ; and counting on
roughtly 10X worse.
