I was wake up by production call today @4:30am, but call ended in 10mins. Decided to read something from Oracle List and found message about RACATTACK event in Oracle OpenWorld OTN Lounge
http://www.blogger.com/img/blank.gifhttp://www.blogger.com/img/blank.gif
Further checking on Google found, this is Group which takes your Laptop with Windows and install Oracle RAC with VMWWARE. That allow us to play around.
They have created very nice Step-by-Step instructions with Detail Screenshots in the following link.
Oracle_RAC_At_Home from RACATTACK on Wikibook.
I haven't tried this, but Information is very nice and details.
Friday, September 23, 2011
Tuesday, April 19, 2011
Incremental Optimizer Statistics Gathering in 11g
Writing this blog entry after long time. At work we are upgrading to 11g. We have one of the biggest table with 2Billion records with Partition and Subpartitions. This accessed by reporting tool. For better performance we analyze each partition (each will have close to 130,000,000 records). Estimate with 50% will do FTS and
130,000,000 Million / 2 (for 50% Estimate) = 65,000,000 Million.
AVG_ROW_LEN = 1400 bytes
Total Temp Memory required to analyze with 50% Percent is
65,000,000 * 1400 bytes = 91,000,000,000 bytes
This is close to 85GB
We need 85GB Temp space exclusively for this process. If there is any other process run in parallel, Analyze job fail due to insufficient TEMP space.
11G has cool feature.
We need to have
This new feature in database 11g could be most useful, especially for large partitioned tables.
Gathering global statistics for a partitioned table would typically require a FTS of all table rows, even when only rows for a few partitions have changed. Using the INCREMENTAL=true setting global statistics can be gathered by scanning only the partitions that have changed.
Oracle® Database Performance Tuning Guide
11g Release 2 (11.2)
13.3.1.3.2 Incremental Statistics Gathering
http://download.oracle.com/docs/cd/E11882_01/server.112/e16638/stats.htm#i42218
The requirements for enabling incremental statistics gathering:
* The INCREMENTAL value for the partitioned table is true.
* The PUBLISH value for the partitioned table is true.
* The user specifies AUTO_SAMPLE_SIZE for ESTIMATE_PERCENT and AUTO for GRANULARITY when gathering statistics on the table.
That may in turn lead to questions as to what the PUBLISH value for a table is. Prior to 11g when gathering statistics they are immediately "published" for use, in 11g one can gather statistics and choose to not have them published immediately but rather do so at some later time. An example might be gathering statistics across a number of tables and/or table partitions and then publish them all at once when it's desirable to do so. See:
Private Statistics Using DBMS_STATS in 11g (Doc ID 567683.1)
Some examples here as well:
Statistics Collection Enhancements in Oracle Database 11g Release 1
http://www.oracle-base.com/articles/11g/StatisticsCollectionEnhancements_11gR1.php
130,000,000 Million / 2 (for 50% Estimate) = 65,000,000 Million.
AVG_ROW_LEN = 1400 bytes
Total Temp Memory required to analyze with 50% Percent is
65,000,000 * 1400 bytes = 91,000,000,000 bytes
This is close to 85GB
We need 85GB Temp space exclusively for this process. If there is any other process run in parallel, Analyze job fail due to insufficient TEMP space.
11G has cool feature.
We need to have
This new feature in database 11g could be most useful, especially for large partitioned tables.
Gathering global statistics for a partitioned table would typically require a FTS of all table rows, even when only rows for a few partitions have changed. Using the INCREMENTAL=true setting global statistics can be gathered by scanning only the partitions that have changed.
Oracle® Database Performance Tuning Guide
11g Release 2 (11.2)
13.3.1.3.2 Incremental Statistics Gathering
http://download.oracle.com/docs/cd/E11882_01/server.112/e16638/stats.htm#i42218
The requirements for enabling incremental statistics gathering:
* The INCREMENTAL value for the partitioned table is true.
* The PUBLISH value for the partitioned table is true.
* The user specifies AUTO_SAMPLE_SIZE for ESTIMATE_PERCENT and AUTO for GRANULARITY when gathering statistics on the table.
That may in turn lead to questions as to what the PUBLISH value for a table is. Prior to 11g when gathering statistics they are immediately "published" for use, in 11g one can gather statistics and choose to not have them published immediately but rather do so at some later time. An example might be gathering statistics across a number of tables and/or table partitions and then publish them all at once when it's desirable to do so. See:
Private Statistics Using DBMS_STATS in 11g (Doc ID 567683.1)
Some examples here as well:
Statistics Collection Enhancements in Oracle Database 11g Release 1
http://www.oracle-base.com/articles/11g/StatisticsCollectionEnhancements_11gR1.php
Subscribe to:
Posts (Atom)