what are different optimizer modes

-

 OPTIMIZER_MODE:

If you have a large table with millions of rows and you want to retrieve all the rows, you should set the OPTIMIZER_MODE parameter to "ALL_ROWS". For example:


SELECT * FROM large_table

WHERE condition = value

ORDER BY column_name;


If you want to retrieve a small number of rows quickly, you should set the OPTIMIZER_MODE parameter to "FIRST_ROWS". For example:


SELECT /*+ FIRST_ROWS */ column1, column2

FROM small_table

WHERE condition = value

ORDER BY column_name;


If you want to retrieve a specific number of rows quickly, you should set the OPTIMIZER_MODE parameter to "FIRST_ROWS_N". For example:


SELECT /*+ FIRST_ROWS(n) */ column1, column2

FROM small_table

WHERE condition = value

ORDER BY column_name;


OPTIMIZER_INDEX_COST_ADJ:

If you want to encourage the optimizer to use an index, you can set the OPTIMIZER_INDEX_COST_ADJ parameter to a higher value. For example:


ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ = 100;


If you want to discourage the optimizer from using an index, you can set the OPTIMIZER_INDEX_COST_ADJ parameter to a lower value. For example:


ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ = 10;


OPTIMIZER_INDEX_CACHING:

If you want the optimizer to cache more index data in memory, you can set the OPTIMIZER_INDEX_CACHING parameter to a higher value. For example:


ALTER SESSION SET OPTIMIZER_INDEX_CACHING = 90;


If you want the optimizer to cache less index data in memory, you can set the OPTIMIZER_INDEX_CACHING parameter to a lower value. For example:


ALTER SESSION SET OPTIMIZER_INDEX_CACHING = 50;


OPTIMIZER_DYNAMIC_SAMPLING:

If you want the optimizer to sample more data to estimate selectivity, you can set the OPTIMIZER_DYNAMIC_SAMPLING parameter to a higher value. For example:


ALTER SESSION SET OPTIMIZER_DYNAMIC_SAMPLING = 4;


If you want the optimizer to sample less data to estimate selectivity, you can set the OPTIMIZER_DYNAMIC_SAMPLING parameter to a lower value. For example:


ALTER SESSION SET OPTIMIZER_DYNAMIC_SAMPLING = 1;


eg:


If you want to encourage the optimizer to use an index, you can set the OPTIMIZER_INDEX_COST_ADJ parameter to a higher value. For example:


ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ = 100;


If you want to discourage the optimizer from using an index, you can set the OPTIMIZER_INDEX_COST_ADJ parameter to a lower value. For example:


ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ = 10;

Sure, I'd be happy to explain this parameter further and give an example.


The OPTIMIZER_INDEX_COST_ADJ parameter is used to control how much the optimizer values the use of an index. The optimizer calculates the cost of different execution plans and chooses the one with the lowest cost. If an index is used in the execution plan, the optimizer will assign a cost to that index usage.


By setting the OPTIMIZER_INDEX_COST_ADJ parameter to a higher value, you are telling the optimizer to place more importance on the use of an index when calculating the cost of different execution plans. This means that the optimizer will be more likely to choose a plan that uses an index, even if it is not the lowest-cost option.


On the other hand, if you set the OPTIMIZER_INDEX_COST_ADJ parameter to a lower value, you are telling the optimizer to place less importance on the use of an index when calculating the cost of different execution plans. This means that the optimizer will be less likely to choose a plan that uses an index, even if it is the lowest-cost option.


For example, let's say you have a table with a large number of rows, and you want to run a query that selects a small subset of those rows based on a specific column. You know that there is an index on that column and you want the optimizer to use it. You can set the OPTIMIZER_INDEX_COST_ADJ parameter to a higher value to encourage the optimizer to choose a plan that uses the index, like this:


ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ = 100;


On the other hand, if you know that the index is not selective enough, or there is some other reason why using the index would result in a slower query, you can set the OPTIMIZER_INDEX_COST_ADJ parameter to a lower value to discourage the optimizer from using it, like this:


ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ = 10;


Comments

Post a Comment

Popular posts from this blog

Understanding Terraform

-
 Terraform is a tool used  for building, changing, and versioning infrastructure safely and efficiently. A Terraform can manage multiple cloud services using a provider plugin  Terraform work flow? Terraform work flow will be comprised of validate -> plan -> apply and finally destroy. what is the important file of terraform which records the actions of Terraform apply? For every Terraform execution the action will be recorded in a file called Terraform state file .  The terraform init command is used to initialise a working directory containing Terraform configuration files. During init, the configuration is searched for module blocks, and the source code for referenced modules is retrieved from the locations given in  their source arguments. The terraform plan command is used to create an execution plan. It will not modify things in infrastructure. The terraform apply command is used to apply the changes required to reach the desired state of the configur...
Read more

How to make CRS and ASM not to restart after server reboot

-
I have noticed that server rebooting is taking lot of time on a VM machine for every reboot, so I have decided to auto start of the crs and ASM service During this phase I have noticed an attribute called auto_start when I try to execute the below command crsctl stat res -p This AUTO_START attribute is there for several resources like ora.ASM ,ora.crs,ora.DATA_DG it has got 2 options restore(0) , never(2) and always (1) always (1)  Causes the resource to restart when the node restarts regardless of the resource’s state when the node stopped -restore (0)  Does not start the resource at restart time if it was in an offline state, such as STATE=OFFLINE, TARGET=OFFLINE, when the node stopped. The resource is restored to its state when the node went down. The resource is started only if it was online before and not otherwise. -never (2)  Oracle Clusterware never restarts the resource regardless of the resource’s state when the node is stopped. So I have dec...
Read more

How to repair ASM disk header

-
Image
What is ASM disk header corruption? ASM disk header corruption is a loss or damage to the disk header which contains the metadata essential for the operation and availability of an ASM disk group in Oracle. Imagine that you have an ASM disk corruption in any of the ASM diskgroups that have critical business data and the diskgroup has been configured as "EXTERNAL" redundancy, meaning that if one disk is inaccessible the whole diskgroup will be unavailable. Now what? Is your only option to restore it from backup by recreating the diskgroup or are there any other faster ways to recover the data? Here you need to find out whether it’s a complete loss of data in the ASM disk or is it just a header corruption. Hopefully for you, this is just a header corruption. Because, yes, there is a way out!  Below find the steps to restore from the ASM header corruption. Most of the data in the ASM disk header is of interest to that disk only. However, some information in the ASM disk header ...
Read more