setting parallel degree limit for each query
Oracle 19.24 version - 4 instance RAC on OEL7 - Datawarehouse database
Out of four Oracle instances, Instance1 is dedicated for reporting users only. Many times, we run out of parallel processes on this reporting instance because some of the users use high degree of parallel hints, i.e. 24, 32, 64, etc. Example: select /*+ parallel(s, 32) */ * from table_name;
Note: purposefully, we have set parameter parallel_force_local = TRUE, so the parallel queries from this instance does not affect the other Datawarehouse instances.
All reporting users use the tns entry, SERVICE_NAME= REPORTING_USERS, to connect to this database. your help with step by step syntax would be appreciated.