|
|||||
|
11.2.0.2的RAC系统中原本有一张大的分区表，之前为了测试exchange分区的性能需要将这张分区表上的部分分区数据复制到测试用表上，因为数据量比较大所以记以录之: 磁盘不太给力 hdparm -tT /dev/sdd /dev/sdd: Timing cached reads: 13672 MB in 2.00 seconds = 6840.55 MB/sec Timing buffered disk reads: 605 MB in 3.02 seconds = 200.33 MB/sec cat /proc/cpuinfo |grep processor|wc -l 8 直接将源分区插入到目标分区表中 SQL> select count(*) from sales_history partition (SALES_1996) ; COUNT(*) ———- 2568089600 SQL> select (bytes) / 1024 / 1024, segment_name, . . . → Read More: 海量数据插入性能测试 Oracle 8i中引入了星型转换(star transformation)的优化器新特性以便更有效地处理星型查询。星型查询语句多用于基于星型模型设计的数据仓库应用中。星型模型的称谓源于该种模型以图形化表现时看起来形似一颗海星。这颗星的中央会由一个或多个事实表(fact tables)组成，而各个触角上则分布着多个维度表(dimension tables)，如下图：
星型转换的基本思路是尽量避免直接去扫描星型模式中的事实表，因为这些事实表总会因为存有大量数据而十分庞大，对这些表的全表扫描会引起大量物理读并且效率低下。在典型的星型查询中，事实表总是会和多个与之相比小得多的维度表发生连接(join)操作。典型的事实表针对每一个维度表会存在一个外键(foreign key)，除去这些键值(key)外还会存在一些度量字段譬如销售额度(sales amount)。与之对应的键值(key)在维度表上扮演主键的角色。而事实表与维度表间的连接操作一般都会发生在事实表上的外键和与之对应的维度表的主键间。同时这类查询总是会在维度表的其他列上存在限制十分严格的过滤谓词。充分结合这些维度表上的过滤谓词可以有效减少需要从事实表上访问的数据集合。这也就是星型转换(star transformation)的根本目的，仅访问事实表上相关的、过滤后精简的数据集合。 Oracle在Sample Schema示例模式中就存有星型模型的Schema，譬如SH: SQL> select * from v$version; BANNER ———————————————————————- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production PL/SQL Release 11.2.0.1.0 – Production CORE 11.2.0.1.0 Production TNS for 32-bit Windows: Version 11.2.0.1.0 – Production NLSRTL Version 11.2.0.1.0 – Production SQL> select * from . . . → Read More: Oracle优化器:星型转换 Applies to: Oracle Server – Enterprise Edition – Version: 11.2.0.1 to 11.2.0.2 – Release: 11.2 to 11.2 Information in this document applies to any platform.
Purpose To provide an overview of ‘parallel query parameters’ introduced in 11g Release 2 Scope and Application Any Oracle engineer interested in understanding and using the new ‘parallel query . . . → Read More: Oracle 11g Release 2, new parallel query parameters To make sure that a grid performs well all components need to be orchestrated to work together. Remember: The weakest link determines the performance. On the left side you see a high level picture of a grid. This is a system with 4 nodes, 2 CPUs and 2 HBAs per node two fibre channel switches, . . . → Read More: DW on RAC Best Practices Agenda Overview Oracle Data Warehouse Strategy IBM DB2 9.5 Infosphere Product versions Product features ETL Partitioning Compression Analytics (OLAP, Data Mining) Industry data models Pre-packaged Configurations IBM’s Balanced Warehouse Oracle’s Optimised Warehouse Initiative Summary Q&A Quote is from Ron Flannery is president and founder of One Point Solutions, an IBM Business Partner focused entirely on . . . → Read More: Competitive Analysis of Data Warehousing Capabilities:Oracle Database 11g vs IBM DB2 9.5 Today’s information architecture is much more dynamic than it was just a few years ago. Data volumes are exploding, generating larger and larger databases storing increasingly varied file types. Businesses are demanding faster response times and are delivering more analytics to an ever-widening set of users and applications. To address these requirements, it is . . . → Read More: Extreme Performance with Oracle Data Warehousing Client IO Performance Review Description of the physical hardware configuration including CPU, Memory, Disk, HBA,Storage Type, etc? Interview technical contacts to get a description of the problems. Measure IO using OS and Oracle tools available? Use Reference Configurations to compare/contrast against the client configuration! Use New 11g Grid Control screens to show IO throughput! Use . . . → Read More: IO Issues in Data Warehouse Environment Data Warehousing Strategy Best database for BI and data warehousing No other database can compare on the breadth and sophistication of Oracle’s database features Complete solution portfolio Complete database platform including ELT and Analytics Oracle BI and Performance Management solutions Broadest array of third-party technologies and solutions On the right hardware infrastructure Offer customers a . . . → Read More: Data Warehouse with Oracle Database 11g |
|
||||
|
Copyright © 2012 Oracle Clinic – Maclean Liu的个人技术博客 - All Rights Reserved |
|||||
最新评论