What can you do to achieve this?

You have a range-partitioned table in your database. Each partition in the table contains the sales
data for a quarter. The partition related to the current quarter is modified frequently and other
partitions undergo fewer data manipulations. The preferences for the table are set to their default
values. You collect statistics for the table using the following command in regular intervals:
SQL> EXECUTE
DBMS_STATS.GATHER_TABLE_STATS(‘SH’,’SALES’,GRANULARITY=>’GLOBAL’);
You need statistics to be collected more quickly. What can you do to achieve this?

You have a range-partitioned table in your database. Each partition in the table contains the sales
data for a quarter. The partition related to the current quarter is modified frequently and other
partitions undergo fewer data manipulations. The preferences for the table are set to their default
values. You collect statistics for the table using the following command in regular intervals:
SQL> EXECUTE
DBMS_STATS.GATHER_TABLE_STATS(‘SH’,’SALES’,GRANULARITY=>’GLOBAL’);
You need statistics to be collected more quickly. What can you do to achieve this?

A.
Set DYNAMIC_SAMPLING to level 4.

B.
Set the STATISTICS_LEVEL parameter to BASIC.

C.
Set the INCREMENTAL value to TRUE for the partition table.

D.
Increase the value of STALE_PERCENT for the partition table.

Show Hint

Leave a Reply 2

Your email address will not be published. Required fields are marked *

Helcio

Helcio

How to gather statistics?
Global statistics are by far the most important statistics but they also take the longest time to collect because a full table scan is required. However, in Oracle Database 11g this issue has been addressed with the introduction of Incremental Global statistics. Typically with partitioned tables, new partitions are added and data is loaded into these new partitions. After the partition is fully loaded, partition level statistics need to be gathered and the global statistics need to be updated to reflect the new data. If the INCREMENTAL value for the partition table is set to TRUE, and the DBMS_STATS GRANULARITY parameter is set to AUTO, Oracle will gather statistics on the new partition and update the global table statistics by scanning only those partitions that have been modified and not the entire table. Below are the steps necessary to do use incremental global statistics

SQL> exec dbms_stats.set_table_prefs(‘SH’, ‘SALES’, ‘INCREMENTAL’, ‘TRUE’);

SQL> exec dbms_stats.gather_table_stats( Owname=>’SH’, Tabname=>’SALES’, Partname=>’23_MAY_2008′, Granularity=>’AUTO’);

Incremental Global Stats works by storing a synopsis for each partition in the table. A synopsis is statistical metadata for that partition and the columns in the partition. Each synopsis is stored in the SYSAUX tablespace and takes approximately 10KB. Global statistics are generated by aggregating the synopses from each partition, thus eliminating the need for the full table scan (see Figure below). When a new partition is added to the table you only need to gather statistics for the new partition. The global statistics will be automatically updated by aggregating the new partition synopsis with the existing partitions synopsis.

Reply