PurposeThe goal of this document is to present the best practices for the deployment of data warehousing applications on the Sun Oracle Database Machine. The goal of these best practices is to enable a small number of users to run large table scans, sorts, and aggregations in an optimal manner on the database machine.
The best practices defined here assume you are using the production hardware and software for the Sun Oracle Database Machine. It is also assumed that you are familiar with the generic data warehousing best practices.
Best practices for data warehousing on the Oracle Database MachineThe best practices defined here assume you are using the production hardware and software for the Sun Oracle Database Machine. It is also assumed that you are familiar with the generic data warehousing best practices. The goal of these best practices is to enable a small number of users to run large table scans, sorts, and aggregations in an optimal manner on the database machine.
To evenly distribute a partitioned table among multiple big file tablespaces using the STORE IN clause. For example, the DDL for a partitioned table with 128 subpartitions would look as follows:
CREATE TABLE sales_composite
PARTITION BY RANGE(sales_date)
SUBPARTITION BY HASH(salesman_id)
store in (ts1, ts2, ts3, ts4)
(PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','MM/DD/YYYY')),
PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','MM/DD/YYYY')),
PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','MM/DD/YYYY')),
PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','MM/DD/YYYY')),
PARTITION sales_may2000 VALUES LESS THAN(TO_DATE('06/01/2000','MM/DD/YYYY')));
If you are doing a temp segment merge load (when SQL*Loader is use or the Merge statement) then the final extents will be trimmed. The extent is trimmed to the nearest multiple of 64KB. For direct path loads or loads that use high watermark brokering no trimming is done.
Setting extent size using INITIAL and Next in the storage clause of non-partitioned table create statementCELL_PARTITION_LARGE_EXTENTS does not help in the case of non-partitoned tables. If you have large non-partitioned tables you still need to ensure that large extents are used. To do this set the storage clause of the create table statement to have the INITIAL clause set to 8MB. The database will make best effort to size every extent in the segment to be at least INITIAL extent size. When doing a parallel insert append on non-partitioned tables, using a parallel SQL*loader job, or using the MERGE statement, data will be loaded into new segments and then the new segments will be merged into the table being loaded. The new segments by default start with 64K extents and grow the extent size as the new segment grows in size (just like any other new segment). If you know that a lot of data will be loaded, then you can skip creating all these small extents by setting NEXT in the table's storage clause. Starting with 220.127.116.11, NEXT controls the starting extent size for the new segments created by these load operations. So if you know for example that you will be loading 500MB of data, you can set NEXT to 8M to ensure that the extent sizes used by the new segments will be large.
Create Table sales(.....) parallel compress storage (INITIAL 8M NEXT 8M) (........);
alter user oracle QUOTA UNLIMITED on tablespace ts_data;
For these reasons we strongly recommend VARCHAR2 should be used for new applications to store character data. If your customer has existing tables with CHAR columns in it, consider using VARCHAR2s instead on the database machine if possible.
Oracle Database SecureFiles and Large Objects Developer's Guide 11g Release 2 (11.2).
Insert /*+ APPEND */ into Sales partition(p2) Select * From ext_tab_for_sales_data;Direct path loads should also be run in parallel. You can set the parallel degree for a direct path load either by adding the PARALLEL hint to the CTAS or IAS statement or by setting the PARALLEL clause on both the external table and the table into which the data will be loaded. Once the parallel degree has been set a CTAS will automatically do direct path load in parallel but an IAS will not. In order to enable an IAS to do direct path load in parallel you must alter the session to enable parallel DML.
ALTER SESSION ENABLE PARALLEL DML; Insert /*+ APPEND */ into Sales partition(p2) Select * from ext_tab_for_sales_data;
exchange partition command. The exchange partition command allows you to swap the data in a non-partitioned table into a particular partition in your partitioned table. The command does not physically move data it simply updates the data dictionary to reset a pointer from the partition to the table and vice versa. Because there is no physical movement of data it is import that the non-partitioned table used for the data load should have the same storage layout as the partitioned fact table. That means the non-partitoned table should reside in the correct tablespace and should have the same extent sizes as the rest of the partitioned table. You can ensure this by setting the INITIAL and NEXT parameters in the storage clause of the table definition to be 8MB.
Create Table tmp_sales2(.....) parallel storage (INITIAL 8M NEXT 8M) tablespace main_fact_tbs ...........
Alter table Sales exchange partition p2 with table tmp_sales2 including indexes without validation;For more information on partition exchange loads see chapter 3 of the Oracle database VLBD and Partitioning guide.
CREATE TABLE sales_external (…) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY data_dir1 ACCESS PARAMETERS (RECORDS DELIMITED BY NEWLINE PREPROCESSOR exec_dir: 'gunzip' FIELDS TERMINATED BY '|' ) LOCATION (…) )Where exec_dir is an Oracle directory that points to the OS directory that contains the gunzip binary.
However you should note that the parallel degree for an external table that uses a preprocessor will be determined by the number of data files specified in the external table. Regardless of what parallel degree is specified on the external table or via hints in the DDL statement. This means if you only have 1 raw data file specified in the external table definition that has a preprocessor clause all accesses to the data in that external table will be done serially.
Populate column usage statistics before data loadPrior to loading any data it is advisable to run all queries against the empty tables to populate or seed the column usage statistics. Column usage statistics are used during optimizer statistics gathering to automatically determine which columns require histograms and the number of buckets that will be used. A column is a candidate for a histogram if it has been seen in a where clause predicate e.g. an equality, range, LIKE, etc. and if there is data skew in that colum.
Alter system set deferred_segment_creation = FALSE scope=both;Due to bug 9078678 the PLAN output for a parallel data load statement from both explain plan and v$SQL_PLAN will wrongly shown as a serial DML plan despite the fact it will run in parallel.
Due to bug 9329566 Insert As Select operations into an empty non-partitioned table will be slower in 11.2 then in previous release. You will also see two execution plans shown for the single DML statement. When Deferred Segment Creation is switched on (default) and a parallel Insert As Select statement is issued against an empty non-partitioned table we will execute the select or query part of the statement twice.
When the statement is issued initially the execution plan generated will have the select or query part of the statement happening in parallel but the insert or load part of the statement happening serial because we don't believe the table being inserted into exists. Once the first block is written to the table the segment will be created and we realize the load should have happened in parallel. This triggers the statement to be parsed again producing a second plan, this one showing both the query and the load section happening in parallel. We will rerun the query part of the statement again and then load the data in parallel. If the query part of the statement is complex or uses any sorting or grouping operation this behavior could result in a serious impact on performance. The workaround for this issue is to turn off deferred segment creation.
With basic compression Oracle compresses data by eliminating duplicate values in a database block. Basic compression only works for direct path operations (CTAS or IAS, as discussed earlier). If the data is modified using any kind of conventional DML operation (for example updates), the data within that database block will be uncompressed to make the modifications and will be written back to disk uncompressed. This can often cause row chaining that result in an increase in ‘gc buffer busy’ waits. It is strongly recommended NOT to use basic compress for data that will be actively changed via DML statements. Basic compress should only be applied to data that is loaded once and is read-only or after all DML operations have been completed.
With OLTP compression, just like standard compression, Oracle compresses data by eliminating duplicate values in a database block. But unlike standard compression OLTP compression allows data to remain compressed during all types of data manipulation operations, including conventional DML such as INSERT and UPDATE. More information on the OLTP table compression features can be found in Chapter 18 of the Oracle Database Administrator's Guide 11g.
Exadata Hybrid Columnar Compression (EHCC) achieves its compression using a different compression technique. A logical construct called the compression unit is used to store a set of Exadata Hybrid Columnar-compressed rows. When data is loaded, a set of rows is pivoted into a columnar representation and compressed. After the column data for a set of rows has been compressed, it is fit into the compression unit. If conventional DML is issued against a table with EHCC, the necessary data is uncompressed in order to do the modification and then written back to disk using a block-level compression algorithm. If your data set is frequently modified using conventional DML EHCC is not recommended, instead the use of OLTP compression is recommended.
EHCC provides different levels of compression, focusing on query performance or compression ratio respectively. With EHCC optimized for query, less compression algorithms are applied to the data to achieve good compression with little to no performance impact. Compression for archive on the other hand tries to optimize the compression on disk, irrespective of its potential impact on the query performance.
Pro's for using CHAR datatype
Using the CHAR datatype ensure there will not be any fragmentation/chaining of objects over time because the column values are fixed width. This eliminates the necessity to reorganize an object in the future. This approach is very common in companies where they use a lot of DB2. Effectively they trade space for reduced future administration.
Con's for using CHAR datatype
CHARs columns take more space on disk due to the space padding and are less efficient when it comes to processing and evaluating predicates compared to VARCHAR2s.
For new applications it is strongly recommended that you use the VARCHAR2 datatype for storing character data rather than a CHARs even if the data is known to be fixed width. If your customer has existing tables with CHAR columns in it, consider using VARCHAR2s instead on the database machine if possible. But you should bear in mind the cost of changing legacy of applications that use CHAR semantics. Changing these applications could require a lot of testing.
In case where you have to use CHARs in 18.104.22.168, please make sure you have the fix for bug 9502734 and bug 9479565. The bug fix avoids extra decompression that can happen on EHCC when CHAR columns are used. Please note even with the bugfix, CHARs are still more expensive than VARCHAR2s.
1. Allows for partition exchange loads 2. Partition pruning during queries 3. Efficient and performant partition-wise joins
Partition-wise joinsThe main performance benefits of hash partitioning is partiton-wise joins. Partition-wise joins reduce query response times by minimizing the amount of data exchanged among parallel execution servers when joins execute in parallel. For the optimizer to choose the full partition-wise join method, both tables must be equi-partitioned on their join keys. That is, they have to be partitioned on the same column with the same partitioning method and the same number of partitions. Below is the execution plan for a simple query that joins the Sales and Customers table. Both tables have the same degree of parallelism and are hash partitioned on cust_id column, which is also the join column. * Execution plan for partition-wise join:
ALTER TABLE sales PARALLEL;Due to this bug 9671271 it is not recommended that you set PARALLEL_FORCE_LOCAL to TRUE in 22.214.171.124, instead you should setup a RAC service to limit where parallel statements can execute.
Srvctl add service –d database_name -s ETL -r sid1,sid2 Srvctl add service –d database_name -s ADHOC -r sid3,sid4
Oracle Database Administrator's Guide.
EXEC DBMS_STATS.SET_GLOBAL_PREFS(AUTOSTATS_TARGET,'ORACLE');If you choose to switch off the auto job complete you will have to maintain the dictionary statistics manually using the GATHER_DICTIONARY_STATS Procedure.
incremental statistics feature that enables global or table level statistics to be automatically derived from partition level statistics. This feature will greatly reduce the amount of time necessary to gather and maintain statistics on large partitioned tables by eliminating the necessity to execute multiple full table scans. Use the following command to enable the incremental feature for the table
EXEC DBMS_STATS.SET_TABLE_PREFS('SH','SALES','INCREMENTAL','TRUE');After loading data gather table statistics using GATHER_TABLE_STATS command but there is no need to specify many parameter just the schema name and the table name.
EXEC DBMS_STATS.GATHER_TABLE_STATS('SH','SALES');The command will collect statistics for partitions and update the global statistics based on the partition level statistics and synopsis . More information about how this feature works can be found here.
Please note you should apply patch 8411968 for base bug 8318020 in 126.96.36.199 before using incremental statistics.
You should also note that due to bug 8584129 it maybe necessary to set the reqired degree of parallelism(DOP) for the statistics gathering rather than letting it inherit the DOP from the objects. A degree of 128 is recommended.
EXEC DBMS_STATS.SET_GLOBAL_PREFS('INCREMENTAL','TRUE'); EXEC DBMS_STATS.SET_GLOBAL_PREFS('DEGREE','128');
One of the most common cases where we see bloom filtering being disabled occurs when there is an index on the smaller table. If the optimizer selects an index range scan of that index then it will be done in serial and not parallel. Thus preventing a bloom filter being created. Potential workarounds in 188.8.131.52 would be to mark the index invisible or drop the index in question. Bug 7199035 was filed for this and the restriction of having a parallel left hand side of the hash join has been lifted in 11.2.
Initialization Parmeter SettingsWe strongly recommend you leave the majority of the initialization parameters at their default values. There are just a small subset of the parameters that you need to change on the database beyond the obvious ones (DB_NAME, Control_files, undo_tablespace). Below is the list of parameters you should consider changing and some guidelines on what to set them to.
- In order to take full advantage of the latest exadata features (storage index etc) you must set the compatible parameter to 11.2. If for some reason you want to lower the compatible parameter value you should remember that the exadata software was only became available in 184.108.40.206 so you must set the compatible parameter for both the ASM and Database instance to 220.127.116.11 or higher in order to communicate correctly with the exadata software on the storage.
- db_block_size= 8KB or 16KB
- 8KB is the default block_size and is the block_size used during all of Oracle's testing. Typically this is good enough for a data warehouse. By doubling the default block size you can increase the chances of getting a good compression rate as Oracle applies data compression at the block level. The more rows in the block the greater the chance Oracle will find duplicate values within a block.
- Parallel_adaptive_multi_user automatically reduces the requested degree of parallelism for a query based on the system load at query startup time. Because the DOP is depended on the system load the elapse time for a query can vary, which is not acceptable during a POC or a benchmark. To ensure consistent elapse times set this parameter to false.
- Parallel servers communicate among themselves and with the Query Coordinator by passing messages via memory buffers. If you execute a lot of large operations in parallel, it’s advisable to reduce the messaging latency by increasing the parallel_execution_message_size (the size of the buffers). By default the message size is 2K. Ideally you should increase it to 16k (16384). However, a larger parallel_execution_message_size will increase the memory requirement for the shared_pool so if you increase it from 2K to 16K your parallel server memory requirement will be 8 X more.
- This parameter determines the maximum number of parallel servers that may be started for a database instance, should there be demand for them. The default value on Oracle Database 11g is 10 * cpu_count * parallel_threads_per_cpu, and typically this is adequate. However, you may want to adjust this parameter depending the number of concurrent parallel query statements you will be executing and the DOP required for those statements. Bare in mind you do not want to flood the system with parallel server processes.
- This parameter determines the number of parallel servers that will be started during database startup. By default the value is 0. It is recommended that you set parallel_min_servers to 32. This will ensure that there are ample parallel server processes available for the majority of the queries executed on the system and queries will not suffer any additional overhead of having to spawn extra parallel servers. However, if extra parallel servers are required for additional queries above you average workload they can be spawn “on the fly” up to the value of parallel_max_servers.
- Oracle automatically sizes the working areas used by memory-intensive SQL operators (such as sort, group-by, hash-join, bitmap merge, and bitmap create) to ensure the total work area (private memory) used is below the target value specified. When you increase the value of this parameter, you indirectly increase the memory allotted to each of the work areas. Consequently, more memory-intensive operations are able to run fully in memory and less will spill into the temp tablespace on disk.
- Increase the process count to allow for all background processes such as locks, job queue processes, and parallel execution processes.
- sga_target specifies the total size of all SGA components (Buffer cache, shared pool, large pool, java pool, and streams pool). You need to set the sga_target high enough to ensure you have enough space in the buffer cache for all of the meta-data for the objects in the database and to have a large enough shared pool to accommodate all of the cursors for your application and the memory buffers used by parallel query.
- The space related metadata (segment headers, extent map blockss etc) for partitioned objects can be quite larage. The query coordinator in a parallel query needs to read the space metadata of a table before starting a table scan. If each partition of a table is smaller than a hundred megabytes, and there are many partitions, then the overhead of reading the space metadata into the buffer cache can be a significant fraction of the overall elapse time for the table scan. Therefore if you have tables with thousands of partitions, you should make sure to size your buffer cache to be large enough to hold the space metadata blocks for tables that are frequently scanned. The following formula may be used to determine the number of metadata blocks (segment header and extent map blocks) in an ASSM segment.
- In the documentation it says "the default value of this parameter is a value that corresponds to the maximum I/O size that can be performed efficiently. This value is platform-dependent and is 1MB for most platforms". However, by changing some of the other parameters listed above such as, processes, you can impact the formula used to automatically set multi_block_read_count. In order to ensure maximum I/O size is 1MB set the parameter to be the 1024/db_block_size.
- When Pre_page_sga is set to ture Oracle reads the entire SGA into memory at instance startup. Operating system page table entries are then prebuilt for each page of the SGA. However, in 18.104.22.168 and 22.214.171.124 it is strongly recommened that you do not use pre_pages_sga when you are using Automatic Memory Management (AMM). Because there is a bug with with AMM where PRE_PAGE_SGA causes every process to touch every page of the entire AMM space, not just the SGA! This has significant impact on page table consumption and session connect time.
Exadata Smart ScanOne of the most powerful features of the Database Machine is that it offloads the data search and retrieval processing to the storage cell. Exadata Cell evaluate query predicates at the storage level to optimize the performance of certain classes of bulk data processing. For example the performance of queries that require a full table or index scans to evaluate selective predicates can be improved by pushing the database expression evaluations to the storage cell. These expressions include simple SQL command predicates, such as amount > 200, and column projections, such as SELECT customer_name. For example:
SQL> SELECT customer_name FROM calls WHERE amount > 200;In the preceding example, only rows satisfying the predicate, and only the column specified, are returned to the database server, eliminating unproductive I/O operations.
Fast full scan of a B-Tree or bitmap index.
Index range scans.
Access to a compressed index.
Access to a reverse key index.
Secure Enterprise Search.