Updates and Patches
I've said several times throughout this book that data warehousing DBAs need to ride the bleeding edge of Oracle releases and patches. But, often the temptation exists to do things the OLTP way and wait six months before installing a new release or patch. I cannot stress how wrong this is. A successful data warehouse is going to depend heavily on key Oracle features. Queries need a star transformation explain plan, which needs hash joins, bitmap indexes, and statistics; data loads need parallel, direct mode inserts; and aggregates need either parallel, direct mode inserts or parallel, enabled "upserts" (i.e., the new MERGE command).
It's exactly these new features that have the most bugs, especially for large volumes of data and when using parallel operations. Table 9-1 is a selective sampling of about 1/20 of the Oracle 8.1.7.4 release notes. I've only included the sections that apply to data warehouses and the features they use most. Notice how many places the words "bitmap indexes" and "star transformations" appear. Also note how many times the phrases "wrong results" and "data or dictionary corruption" appear. All of a sudden, riding the bleeding edge does not sound so bad, does it?
Category | Fixed | BugNo | Description |
|---|---|---|---|
Corruption | |||
8174 | 1653112 | EXCHANGE PARTITION does not check that FUNCTIONAL index definitions match | |
8174 | 2161512 | INSERT /*+ APPEND*/ into table with FUNCTIONAL INDEX loads corrupt data | |
8173 | 1616033 | Direct load to composite partitioned table can corrupt local indexes | |
8172 | 1360714 | ALTER TABLE ADD PARTITION .. STORE IN with SUBPARTITIONS can dump or corrupt dictionary | |
8172 | 1527982 | OERI:25012 / Bitmap index<->table mismatch after UPDATE of PARTITION KEY moves rows | |
Bitmap Indexes | |||
8174 | 1916487 | OERI:[QERBCROP KSIZE] possible from CREATE BITMAP INDEX on TO_DATE function | |
8174 | 2156961 | OERI:20040 possible from bitmap index | |
8173 | 1346747 | OERI:6101 / OERI:20063 possible using SERIALIZABLE transactions with DML on BITMAP indexes | |
8173 | 1358047 | Wrong Results/Dump from Bitmap AND on BTREE range scan of concatenated index | |
8173 | 1726833 | OERI:13013 / Dump in kdudcp from UPDATE using range scan converted to BITMAPS | |
8173 | 1751186 | Wrong results / dump in qerixGetKey using bitmap indexes | |
8173 | 1834495 | OERI:12337 possible with many OR predicates on bitmap index prefix column | |
8173 | 2065386 | Mem. Corruption / OERI:KGHFRE2 / OERI:17172 possible using bitmap indexes | |
8173 | 2114246 | Memory leak and long parse time for Part View with INLIST bitmap predicates | |
8172 | 1380164 | OERI:QKAGBY2 from aggregate GROUP BY with COUNT(*), Bitmap indexes and INLIST | |
Crash | |||
8173 | 1711803 | DBW & users may CRASH under heavy load on multi-CPU system with FAST_START_IO_TARGET set > 0 | |
8171 | 1482170 | SMON may dump on cleanup of PARTITIONED INDEX ONLINE BUILD | |
Hangs/Spins | |||
8174 | 2208570 | ORA-4030 / ORA-4031 / spin during query optimization with STAR TRANSFORMATION and unmergable view | |
8173 | 1685119 | OERI:KCBLIBR_USER_FOU / hang when interrupt (Ctrl-C) of PQ using STAR_TRANSFORMATION | |
8173 | 1906596 | PQ may hang when query involves ORDER BY, SUBQUERY and UNION-ALL | |
8172 | 1582923 | A query may spin / dump with Row Level Security either STAR_TRANSFORMATION_ENABLED or _PUSH_JOIN_UNION_VIEW | |
Hash Join | |||
8173 | 1839080 | Memory leak possible using HASH join (ORA-4030) | |
Memory Corruption | |||
8173 | 1711803 | DBW & users may CRASH under heavy load on multi-CPU system with FAST_START_IO_TARGET set > 0 | |
8173 | 2002799 | Wrong results / heap corruption from PQ with aggregates in inline view | |
8173 | 2048336 | OERI:150 / Memory corruption from interrupted STAR TRANSFORMATION | |
8173 | 2065386 | Mem. Corruption / OERI:KGHFRE2 / OERI:17172 possible using bitmap indexes | |
8172 | 1732885 | oeri:[KDIBR2R2R BITMAP] / memory corruption possible from BITMAP AND | |
Optimizer | |||
8172 | 1582923 | A query may spin / dump with Row Level Security either STAR_TRANSFORMATION_ENABLED or _PUSH_JOIN_UNION_VIEW | |
8172 | 1587376 | STAR_TRANSFORMATION_ENABLED=TRUE can cause INSERT as SELECT to dump | |
8172 | 1620577 | STAR_TRANSFORMATION_ENABLED=TRUE may dump in KKOSBPP or show poor performance | |
8172 | 1715860 | STAR_TRANSFORMATION_ENABLED = TRUE may give slow performance | |
8171 | 1401235 | ORA-900 from STAR_TRANSFORMATION_ENABLED with OR predicates to dimension table | |
8171 | 1482423 | OERI:4823 possible from STAR_TRANSFORMATION_ENABLED=TRUE | |
8171 | 1490373 | ORA-1008 can occur with STAR_TRANSFORMATION_ENABLED=true | |
Parallel Query (PQO) | |||
8174 | 1548982 | PQ Slaves do not use CURRENT_SCHEMA if set (ORA-12801/ORA-942 possible, or wrong table used) | |
8174 | 1621835 | Incorrect plan possible under parallel query | |
8174 | 1746797 | Wrong results possible from PQ with SET operations in correlated subquery | |
8174 | 1992414 | ORA-12801 / ORA-932 possible from PQ referencing a colunn with a DESCENDING index | |
8174 | 2091962 | PQ against composite partitioned table with INLIST on subpartition key may error (OERI:QERPXMOBJVI5) | |
8173 | 681179 | Parallel TO_LOB(LONG) may dump | |
8173 | 936107 | OERI:15814 possible from parallel query | |
8173 | 1020403 | ORA-29900 possible from PQ using extensible ANCILLARY-PRIMARY operators | |
8173 | 1183055 | ORA-12801 / ORA-942 possible with PQ against synonym on another users view | |
8173 | 1344653 | ORA-7445[KOKLIGCURENV] possible running Text query in parallel | |
Partitioned Tables | |||
8174 | 1653112 | EXCHANGE PARTITION does not check that FUNCTIONAL index definitions match | |
8174 | 1834530 | OERI:25012 / wrong results after EXCHANGE PARTITION with indexes with different FREELIST /FREELIST GROUPS | |
8174 | 2091962 | PQ against composite partitioned table with INLIST on subpartition key may error (OERI:QERPXMOBJVI5) | |
8174 | 2110573 | ORA-439 attempting to IMPORT partitioned table into nonpartitioned table without PARTIONING option | |
8174 | 2121887 | ORA-7445 [KKEHSL] possible with GLOBAL PARTITIONED INDEX and COLUMN HISTOGRAMS | |
8174 | 2141535 | ORA-604/ORA-942 possible from query against partitioned table | |
8174 | 2157502 | OERI:4819 possible when partition maintenence is running against an IOT | |
8174 | 2162632 | ORA-7445 from concurrent ANALYZE .. STATISTICS / CREATE INDEX against partitioned table | |
8174 | 2199391 | ADD/SPLIT [SUB]PARTITION can result in LOB partition in wrong tablespace | |
8174 | 2201672 | ORA-7445[MSQSEL] selecting from a view defined on other views with Partitioned tables | |
Performance | |||
8174 | 2079526 | "free buffer waits" / LRU latch contention possible on write intensive systems | |
8171 | 1318267 | INSERT AS SELECT may not share SQL when it should | |
Query Rewrite (Including Materialized Views) | |||
8174 | 1367842 | Wrong results from query rewrite of SELECT COUNT(*) against MV with SELECT DISTINCT | |
8174 | 1612352 | ORA-30457 possible refreshing a nested materialized view | |
8174 | 2097926 | Dump possible from query using Function based index with MVIEW and QUERY_REWRITE_INTEGRITY=TRUSTED | |
8174 | 2245289 | ORA-12003 creating Materialized View with >32k SQL text | |
8174 | 2263600 | Query may not rewrite when expected | |
8173 | 1314358 | OERI:KKQSGCOL-1 possible on complex MV query | |
8173 | 1618192 | OERI:voprvl1 possible for INSERT into table SELECT FROM MATERIALIZED VIEW | |
8173 | 1664189 | Query rewrite does not occur if base table has a FUNCTIONAL index on it | |
8173 | 1873265 | SELECT COUNT(*) with QUERY_REWRITE and empty MV returns NULL instead of 0 | |
8173 | 1898834 | Query rewrite may give incorrect results for outer joins | |
Resource Leaks (e.g., Memory Leaks) | |||
8173 | 1782024 | Memory leak in PQ slave during parallel propogation | |
8173 | 1839080 | Memory leak possible using HASH join (ORA-4030) | |
Space Management | |||
8174 | 1937847 | Space may be lost if migration of a tablespace to LOCALLY MANAGED is aborted | |
8174 | 2209512 | OERI:5325 possible during ALTER TABLE .. MOVE | |
8172 | 1709816 | OERI:[KTFBBSSEARCH-7] creating TABLE with FREELIST GROUPS in LOCALLY MANAGED AUTOALLOCATE tablespace | |
8171 | 1499098 | Direct loaded index blocks have fewer ITLs than possible for large INITRANS | |
Space Management?Bitmap Managed | |||
8174 | 1642738 | AUTOEXTEND of bitmap managed tablespaces does not try all files for space | |
8174 | 2157568 | OERI:KCBGTCR_4 possible from query if segment in BITMAP tablespace is TRUNCATED | |
8174 | 2194182 | ORA-604 / ORA-1000 possible querying space information for BITMAPPED tablespace | |
Star Transformation | |||
8174 | 1956846 | ORA-7445[EVAOPN2] possible from STAR TRANSFORMATION if SUBQUERY_PRUNING enabled | |
8174 | 2072348 | OERI:[KKOJOCOL:2] from STAR TRANSFORMATION with duplicate table aliases | |
8174 | 2144870 | STAR TRANSFORMATION (FACT hint) may be ignored | |
8174 | 2170565 | Wrong results possible from STAR_TRANSFORMATION_ENABLED=TRUE temp table transformation | |
8174 | 2172983 | Wrong results / Dump from STAR_TRANSFORMATION of concatenated bitmap row source | |
8174 | 2208570 | ORA-4030 / ORA-4031 / spin during query optimization with STAR TRANSFORMATION and unmergable view | |
8174 | 2241746 | "FACT" hint may be ignored when valid STAR TRANSFORMATION not used | |
8174 | 2251373 | Poor performance / CARTESIAN merge from TEMP TABLE STAR transformation | |
8173 | 1461208 | ORA-604 / ORA-918 possible from STAR TRANSFORMATION using views / subqueries | |
8173 | 1565514 | Wrong results/dump possible with STAR TRANSFORMATION and transitively generated predicate | |
Wrong Results | |||
8174 | 1367842 | Wrong results from query rewrite of SELECT COUNT(*) against MV with SELECT DISTINCT | |
8174 | 2033324 | Wrong results from BITMAP access of B*TREE index with all NULLABLE columns | |
8174 | 2170565 | Wrong results possible from STAR_TRANSFORMATION_ENABLED=TRUE temp table transformation | |
8174 | 2228217 | Join between partitioned and nonpartitioned table may loose ORDER BY clause | |
8173 | 1548495 | Wrong results from PQ of partitionwise hash join on composite partitioned table | |
8173 | 1565514 | Wrong results/dump possible with STAR TRANSFORMATION and transitively generated predicate | |
8173 | 1587619 | Wrong results possible from STAR TRANSFORMATION and SEMIJOIN | |
8173 | 1759227 | PQ may return wrong results selecting a COUNT(aggregate) column from a view | |
8173 | 1793533 | Wrong results possible from PQO with GROUP BY (affected by SORT_AREA_SIZE) | |
8173 | 1855381 | Wrong results possible from PQ partial piecewise join | |
Dumps/Abends | |||
8174 | 2110054 | Select COUNT(*) from a nested complex view with GROUP BY in inner view may dump in evaopn2 | |
8173 | 1787862 | Dump possible from queries using ORDER BY clause | |
8173 | 1805102 | Dump possible from INLINE view "UNION" and "ORDER BY" | |
8173 | 2004336 | COUNT(NOT_NULL_COLUMN) may dump (QERIXGETKEY) if column referenced in WHERE clause | |
Errors/Internal Errors | |||
8173 | 1478965 | OERI:15160 possible with EXISTS/IN and HASH or MERGE ALWAYS_SEMI_JOIN | |
8173 | 1748384 | OERI:qksopOptASJLf1 / dump in kkeajsel with ALWAYS_SEMI_JOIN=MERGE/HASH with SUBQUERY containing OR of correlated variable | |
8172 | 1397075 | OERI:KCBGCUR_9 from SMON during temp seg cleanup for segment in read only LOCALLY MANAGED TABLESPACE | |
8172 | 1656588 | ORA-1008 from STAR_TRANSFORMATION_ENABLED and TRUNC() | |
8171 | 962560 | ORA-25128 possible for INSERT .. SELECT from table with "DISABLE VALIDATE" constraint | |
8171 | 1500717 | ORA-903 with STAR_TRANSFORMATION and non alphanumeric table name | |
8171 | 1533922 | OERI:KGLCHK2_1 possible referencing a SEQUENCE with STAR_TRANSFORMATION or PARTITION_VIEW_ENABLED or _PUSH_JOIN_UNION_VIEW |
