Oracle Database Discussions

All,

For some reason I am hitting this error today consistently. I have been executing this query

3-Node Rac

3:949:11521:20586>select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE    11.1.0.7.0      Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production

3:949:11521:20586>get z
  1  select inst_id, sql_id, count(*)
  2  from gv$sql_shared_cursor
  3  where sql_id in ('c5brdpybgqss6', 'bka16g5ysh6qg', '2s3q4ra120t45')
  4  group by inst_id, sql_id
  5* order by inst_id
3:949:11521:20586>@ z
group by inst_id, sql_id
   *
ERROR at line 4:
ORA-12850: Could not allocate slaves on all specified instances: 3 needed, 2 allocated

Thanks to tanel I executed snapper on the other session to find out what it's waiting on.

Output from snapper.

3:973:13602:24348>@ snapper ash 5 1 949
Sampling SID 949 with interval 5 seconds, taking 1 snapshots...

-- Session Snapper v3.52 by Tanel Poder @ E2SN ( http://tech.e2sn.com )


-----------------------------------------------------------------------
Active% | SQL_ID          | EVENT                     | WAIT_CLASS
-----------------------------------------------------------------------
    39% | brxs9krsxcw4b   | GV$: slave acquisition re | Other

--  End of ASH snap 1, end=2012-03-06 11:15:27, seconds=5, samples_taken=41


PL/SQL procedure successfully completed.

Checked the metalink note : 371117.1 but it didn't help me .

If I execute the same query as below it works (I just included an alias).

3:949:11521:20586>get z
  1  select inst_id, sql_id, count(*)
  2  from gv$sql_shared_cursor t
  3  where sql_id in ('c5brdpybgqss6', 'bka16g5ysh6qg', '2s3q4ra120t45')
  4  group by inst_id, sql_id
  5* order by inst_id
3:949:11521:20586>@ z

   INST_ID SQL_ID          COUNT(*)
---------- ------------- ----------
         1 2s3q4ra120t45       6669
         1 bka16g5ysh6qg       3279
         1 c5brdpybgqss6      11863
         2 2s3q4ra120t45       6305
         2 bka16g5ysh6qg       6509
         2 c5brdpybgqss6      14561
         3 2s3q4ra120t45       9363
         3 bka16g5ysh6qg      10218
         3 c5brdpybgqss6       7371

9 rows selected.

Any thoughts on this.

Regards

Raj

P.S : I have been executing this query for quite some time to monitor the number of child cursors created for these sql_id.