My ASH wait chains scripts started returning the ORA-12850 error on Oracle 19c RAC installations:
SQL> @ash/dash_wait_chains program2||event2 1=1 sysdate-1 sysdate
-- Display ASH Wait Chain Signatures script v0.5 BETA by Tanel Poder ( https://tanelpoder.com )
, REPLACE(SYS_CONNECT_BY_PATH(program2||event2, '->'), '->', ' -> ')||CASE WHEN CONNECT_BY_ISLEAF = 1 AND d.blocking_session IS NOT NULL THEN ...
ERROR at line 63:
ORA-12850: Could not allocate slaves on all specified instances: 2 needed, 1 allocated
While this error message sounds like a resource shortage issue, it is actually often due to the remote instance(s) not being able to reproduce the exact same execution plan as in the query coordinator instance. Oracle PX layer peforms all kinds of plan signature checks for this. There are known bugs related to optimizer adaptive plans and query rewrites that cause this error to show up. However disabling these features didn’t work around the error in my case.
Note that my script is not actually running any queries with a parallel degree, but since I’m using GV$ views for reading cluster-wide ASH data, PX slaves are used for fetching remote data.
Today I took some time to look into this and in my case the problem (bug) occurs due to temp table transformations in the query plan. My ASH scripts use a number of WITH subqueries and by default the optimizer has the freedom to decide which ones it materializes into a physical temp table during query runtime and which ones it keeps inlined as regular “on-demand” function calls:
SQL> @pvalid with_subquery
Display valid values for multioption parameters matching "with_subquery"...
PAR# PARAMETER ORD VALUE DEFAULT
------ ----------------- ---------- -------------- -------
4136 _with_subquery 2 OPTIMIZER DEFAULT
_with_subquery 1 INLINE
_with_subquery 3 MATERIALIZE
When I set _with_subquery to “INLINE” in my test environment, the error went away:
SQL> ALTER SESSION SET "_with_subquery"=INLINE; Session altered. SQL> @ash/ash_wait_chains program2||event2 1=1 sysdate-1/24/12 sysdate -- Display ASH Wait Chain Signatures script v0.6 BETA by Tanel Poder ( https://tanelpoder.com ) %This SECONDS AAS WAIT_CHAIN ------ ---------- ---------- --------------------------------------------------------------------------------------------------------------------------------------------------- 41% 2771 9.2 -> (JDBC Thin Client) kksfbc child completion 10% 670 2.2 -> (JDBC Thin Client) cursor: pin S wait on X 9% 577 1.9 -> (JDBC Thin Client) ON CPU 7% 453 1.5 -> (JDBC Thin Client) SQL*Net break/reset to client 7% 446 1.5 -> (JDBC Thin Client) library cache lock -> (JDBC Thin Client) library cache lock 6% 406 1.4 -> (JDBC Thin Client) cursor: pin S wait on X -> (JDBC Thin Client) ON CPU 4% 293 1 -> (Jnnn) enq: TQ - DDL contention [mode=6] -> (Qnnn) enq: TX - row lock contention [mode=4] -> [idle blocker 1,159,22914 (JDBC Thin Client)] 4% 293 1 -> (Qnnn) enq: TX - row lock contention [mode=4] -> [idle blocker 1,159,22914 (JDBC Thin Client)] 2% 155 .5 -> (JDBC Thin Client) library cache: mutex X 2% 114 .4 -> (JDBC Thin Client) cursor: pin S wait on X -> (JDBC Thin Client) library cache: mutex X 2% 102 .3 -> (JDBC Thin Client) library cache lock 1% 80 .3 -> (JDBC Thin Client) library cache lock -> (JDBC Thin Client) library cache: mutex X 1% 79 .3 -> (JDBC Thin Client) library cache lock -> (JDBC Thin Client) library cache lock -> (JDBC Thin Client) library cache: mutex X 1% 44 .1 -> (JDBC Thin Client) library cache lock -> (JDBC Thin Client) ON CPU 0% 33 .1 -> (JDBC Thin Client) library cache: mutex X -> (JDBC Thin Client) library cache: mutex X 0% 31 .1 -> (JDBC Thin Client) library cache lock -> (JDBC Thin Client) library cache lock -> (JDBC Thin Client) library cache lock 0% 20 .1 -> (JDBC Thin Client) cursor: pin S wait on X -> (JDBC Thin Client) SQL*Net break/reset to client 0% 19 .1 -> (JDBC Thin Client) cursor: pin S wait on X -> (JDBC Thin Client) library cache: bucket mutex X
So if you are seeing the “ORA-12850: Could not allocate slaves on all specified instances: X needed, Y allocated” error in your environment and the other workarounds / bugfixes in My Oracle Support aren’t helping, you can try with this parameter too.
In my case I didn’t end up using the ALTER SESSION command as I don’t want my troubleshooting scripts to alter session settings when possible. I first tried to add the opt_param hint, but since I have multiple WITH subquery blocks, just adding this parameter into one of them would not work. I ended up adding the INLINE hint into all WITH subuquery blocks to work around this bug.
I have uploaded the latest v0.6 versions of ash_wait_chains.sql and dash_wait_chains.sql to my Oracle troubleshooting scripts GitHub repo.
