Exadata AUTO DOP (Automatic degree Policy)

Question

i have multiple(8) node Oracle Exadata db. When i open auto dop properties on Exadata , i am getting a lor of Cell multi block physical read event which auto dop kicks “in memory parallelism” so buffer cache reads data from disk due to multi block physical read.

in addition: this parametre parallel_force_local= False. And cache fusion disable for mira. While auto dop is open, data guard apply lag occurs at data guard.

When dop is turn into manuel, data guard starts to work properly. Additionally cell multiblock physical is closed and I see cell smart table.

When I use dop auto, I want to see cell smart table scan and data guard working properly..

Do you have any comment about this case

Solution and direction

Answer 1

With Exadata, you don't want in-memory parallelism because it uses cached reads, and without using direct path you won't get offloading to the cells. Smart scan requires direct path, which in-memory PX prevents, so you simply need to disable in-memory PX.

As in-memory parallelism is enabled by default with parallel_degree_policy=auto, you have a couple options:

1. First, you may set parallel_degree_policy=manual and then go through all your large tables (greater than 100MB or so) and set a degree on them; e.g, alter table xyz parallel (degree 16). This is our team's preferred approach because it gives us more stable control over PX than auto DOP does. If you also want parallel statement queuing, you can reenable it with _parallel_statement_queuing=true

2. Second, you may try leaving parallel_degree_policy=auto but disabling in-memory PX with an underscore parameter: _parallel_cluster_cache_policy=adaptive. This is my answer to your question how to use auto DOP and still get smart scan to work.

As for parallel_force_local, that doesn't impact smart scan and can be set to either true or false. While false (the default) allows Oracle to better distribute load over the nodes, we've seen it cause problems, hit bugs, put too much traffic on the interconnect, etc.. so we have frequently found it necessary to set it to true and rely on the SCAN listener alone for load balancing. It's also saner (in my opinion) and easier to manage and monitor when PX slaves and the QC are on the same instance.

Answer 2

Thanks. If i use dop manually , can i define server max target and min threshold ?

What is your opinion about limited option for dop ?

Also, this parameter is _parallel_cluster_cache_policy=adaptive but it still getting a lot of multiblock physical read event. I evaluated this situation that this parameter _parallel_cluster_cache_perc is related to buffer cache size and table size.

Thanks.