How does the METHOD_OPT parameter work?

 

How does the METHOD_OPT parameter work?

The METHOD_OPT parameter is probably the most misunderstood parameter in the DBMS_STATS.GATHER_*_STATS procedures. It’s most commonly known as the parameter that controls the creation of histograms but it actually does so much more than that. The METHOD_OPT parameter actually controls the following,

• which columns will or will not have base column statistics gathered on them
• the histogram creation,
• the creation of extended statistics

The METHOD_OPT parameter syntax is made up of multiple parts. The first two parts are mandatory and are broken down in the diagram below.

The leading part of the METHOD_OPT syntax controls which columns will have base column statistics (min, max, NDV, number of nulls, etc) gathered on them. The default, FOR ALL COLUMNS, will collects base column statistics for all of the columns (including hidden columns) in the table.  The alternative values limit the collection of base column statistics as follows;

The SIZE part of the METHOD_OPT syntax controls the creation of histograms and can have the following settings;

AUTO, REPEAT, SKEWONLY,  Size Must be in the range [1,254]. 

The second part of the parameter setting needs to specify that a histogram is needed on the CUST_ID column. 

 

https://blogs.oracle.com/optimizer/post/how-does-the-method-opt-parameter-work

Dynamic sampling and its impact on the Optimizer

Dynamic sampling and its impact on the Optimizer

12c 

Dynamic sampling (DS) was introduced to improve the optimizer's ability to generate good execution plans. This feature was enhanced and renamed Dynamic Statistics in Oracle Database 12c. The most common misconception is that DS can be used as a substitute for optimizer statistics, whereas the goal of DS is to  augment optimizer statistics; it is used when regular statistics are not sufficient to get good quality cardinality estimates.

Parallel Hint

 PARALLEL HINT

Parallel Hint instructs the optimizer to make use the given degree of parallelism in processing  the sql statement parallelly, instead of processing it serially.  

Parallel  execution can improve the performance of the query by dividing the work  among multiple processers or cpus,  Thus reducing the overall cost of Execution Plan. but you have to keep in mind that you MUST have enough CPU power available on the server, or else it can cause performance issues. 

Also Parallel execution can consume a significant amount of system resources, including CPU and memory.Therefore, it is important to monitor the system’s resources when using parallel execution and to adjust the degree of parallelism as necessary.

Refer https://expertoracle.com/2022/12/04/parallel-hint-in-oracle-database-ways-to-use-and-how-to-monitor/