[20211012]ORA-12850.txt

[20211012]ORA-12850.txt --//放假前的事情一直没时间关注,新上线生产系统,一直没时间看,执行ash_wait_chains.sql遇到ORA-12850问题. 1.环境: SYS@127.0.0.1:17101/PPPPP> @ ver SYS@127.0.0.1:17101/PPPPP> @ prxx ============================== PORT_STRING                   : x86_64/Linux 2.4.xx VERSION                       : 19.0.0.0.0 BANNER                        : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production BANNER_FULL                   : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.9.0.0.0 BANNER_LEGACY                 : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production CON_ID                        : 0 PL/SQL procedure successfully completed. SYS@127.0.0.1:17101/PPPPP> @ tpt/ash/ash_wait_chains username||':'||program2||':'||event2 1=1  trunc(sysdate) sysdate -- Display ASH Wait Chain Signatures script v0.2 BETA by Tanel Poder ( http://blog.tanelpoder.com )         AND sample_time BETWEEN trunc(sysdate) AND sysdate                                                    * ERROR at line 22: ORA-12850: Could not allocate slaves on all specified instances: 2 needed, 0 allocated --//使用百度检索,ORA-12850: Could not allocate slaves on all specified instances: 2 needed, 0 allocated --//真的应该把百度检索XXX掉,什么垃圾,浪费大量的时间. --//围绕ora-12850的bug N多,因为要打开并行,应该与这样有关,但是都不是很准确. --//我想既然是tanelpoder工具,应该有人先比我遇到这个问题.找到作者的链接: https://tanelpoder.com/posts/ora-12850-could-not-allocate-slaves-on-all-specified-instancs-error/ SYS@127.0.0.1:17101/PPPPP> ALTER SESSION SET "_with_subquery"=INLINE; Session altered. SYS@127.0.0.1:17101/PPPPP> @ tpt/ash/ash_wait_chains username||':'||program2||':'||event2 1=1  trunc(sysdate) sysdate -- Display ASH Wait Chain Signatures script v0.2 BETA by Tanel Poder ( http://blog.tanelpoder.com )         AND sample_time BETWEEN trunc(sysdate) AND sysdate                                                    * ERROR at line 22: ORA-12850: Could not allocate slaves on all specified instances: 2 needed, 0 allocated --//不行!!理论讲应该可以,不知道为什么,不过我看到作者结尾的内容: 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. 在我的情况下，我最终没有使用更改会话命令，因为我不希望我的故障排除脚本尽可能更改会话设置。我首先尝试添加opt_param提示， 但由于我有多个子查询块，只是将这个参数添加到其中一个中是不起作用的。最后，我将内联提示添加到所有的子查询块中，以解决这个 错误。 --//作者更新ash_wait_chains.sql and dash_wait_chains.sql的版本.我下载执行一切Ok. --//我仅仅修改一行 COL wait_chain FOR A300 WORD_WRAP 为 COL wait_chain FOR A190 WORD_WRAP --//大师的显示器显示宽度真大.... SYS@127.0.0.1:17101/PPPPP> @ tpt/ash/ash_wait_chains username||':'||program2||':'||event2 1=1  trunc(sysdate) sysdate -- Display ASH Wait Chain Signatures script v0.6 BETA by Tanel Poder ( http://blog.tanelpoder.com ) %This     SECONDS        AAS #Blkrs WAIT_CHAIN                                                                                    FIRST_SEEN          LAST_SEEN ------ ---------- ---------- ------ --------------------------------------------------------------------------------------------- ------------------- -------------------   20%        6296         .2      4 -> IMCJK:(JDBC Thin Client) :ON CPU                                                           2021-10-12 00:00:30 2021-10-12 10:20:36   15%        4570         .1      1 -> SYS:(PSPn) :ON CPU                                                                         2021-10-12 00:42:55 2021-10-12 09:47:46   11%        3491         .1    213 -> XXXYYY_HIS:(XXXYYY.EXE) :ON CPU                                                            2021-10-12 00:00:10 2021-10-12 10:20:34   11%        3467         .1    199 -> XXXYYY_HIS:(XXXYYY.exe) :ON CPU                                                            2021-10-12 00:00:01 2021-10-12 10:20:35    5%        1472          0      1 -> SYS:(DBRM) :ON CPU                                                                         2021-10-12 00:00:38 2021-10-12 10:20:29    4%        1355          0      1 -> SYS:(PMAN) :ON CPU                                                                         2021-10-12 00:00:23 2021-10-12 10:20:05    3%         816          0      1 -> SYS:(CKPT) :control file sequential read                                                   2021-10-12 00:00:04 2021-10-12 10:19:34    3%         770          0    150 -> XXXYYY_HIS:(wnwp.exe) :ON CPU                                                              2021-10-12 00:00:15 2021-10-12 10:20:29    2%         653          0      6 -> XXXYYY_PPI:(wnwp.exe) :ON CPU                                                              2021-10-12 00:02:04 2021-10-12 10:18:32    2%         637          0      1 -> SYS:(DIAn) :ON CPU                                                                         2021-10-12 00:00:02 2021-10-12 10:17:41    1%         453          0     38 -> XXXYYY:(bsupd.exe) :SQL*Net more data to client                                            2021-10-12 00:12:36 2021-10-12 10:20:33    1%         448          0      1 -> SYS:(RMSn) :control file sequential read                                                   2021-10-12 00:00:12 2021-10-12 10:20:19    1%         401          0      6 -> LBJH:(JDBC Thin Client) :ON CPU                                                            2021-10-12 00:00:40 2021-10-12 10:16:09    1%         367          0     10 -> SYS:(Wnnn) :latch free  -> [idle blocker 1,5741,36606 (oracle@oda1 (SMCO))]                2021-10-12 00:01:00 2021-10-12 10:20:15    1%         305          0     12 -> SYS:(Wnnn) :latch free  -> [idle blocker 2,5741,17785 (oracle@oda2 (SMCO))]                2021-10-12 00:00:58 2021-10-12 10:17:48    1%         272          0      2 -> SYS:(LGnn) :log file parallel write                                                        2021-10-12 00:05:07 2021-10-12 10:19:47    1%         242          0      1 -> SYS:(GENn) :ON CPU                                                                         2021-10-12 00:07:24 2021-10-12 09:57:44    1%         217          0     87 -> XXXYYY:(XXXYYY.exe) :ON CPU                                                                2021-10-12 00:13:03 2021-10-12 10:20:37    1%         208          0      1 -> SYS:(MMON) :ON CPU                                                                         2021-10-12 00:05:33 2021-10-12 10:17:46    1%         203          0      1 -> SYS:(CKPT) :Disk file Mirror Read                                                          2021-10-12 00:00:40 2021-10-12 10:15:46    1%         174          0     88 -> XXXYYY:(XXXYYY.EXE) :ON CPU                                                                2021-10-12 00:00:54 2021-10-12 10:18:39    1%         158          0      1 -> SYS:(CKPT) :ON CPU                                                                         2021-10-12 00:04:34 2021-10-12 10:20:28    1%         157          0      1 -> SYS:(PMON) :ON CPU                                                                         2021-10-12 00:11:44 2021-10-12 10:20:20    0%         144          0     68 -> SYS:(MZnn) :ON CPU                                                                         2021-10-12 00:00:02 2021-10-12 10:16:06    0%         138          0      1 -> SYS:(LMHB) :ON CPU                                                                         2021-10-12 00:00:55 2021-10-12 10:16:26    0%         129          0      1 -> SYS:(LMON) :ON CPU                                                                         2021-10-12 00:11:41 2021-10-12 10:16:07    0%         110          0     63 -> XXXYYY_HIS:(autoservice.exe) :ON CPU                                                       2021-10-12 00:02:00 2021-10-12 10:19:01    0%         100          0     45 -> XXXYYY_EMR:(XXXYYY.exe) :ON CPU                                                            2021-10-12 00:07:23 2021-10-12 10:19:51    0%          92          0     31 -> XXXYYY:(XXXYYY.exe) :log file sync  -> [idle blocker 1,4230,46563 (oracle@oda1 (LGWR))]    2021-10-12 00:05:30 2021-10-12 10:19:58    0%          80          0      1 -> SYS:(WnnD) :latch free  -> [idle blocker 2,5741,17785 (oracle@oda2 (SMCO))]                2021-10-12 00:07:20 2021-10-12 10:19:22 30 rows selected. --//username=IMCJK . SYS@127.0.0.1:17101/PPPPP> @ ashtop sql_id,event,machine username='IMCJK' sysdate-1 sysdate     Total   Seconds     AAS %This   SQL_ID        EVENT                        MACHINE FIRST_SEEN          LAST_SEEN --------- ------- ------- ------------- ---------------------------- ------- ------------------- -------------------      1959      .0   13% | c3jafyjuwt13b                              H3C-APM 2021-10-11 10:25:29 2021-10-12 10:20:53       679      .0    4% | f454ryjfx6syf                              H3C-APM 2021-10-11 10:25:56 2021-10-12 10:21:16       557      .0    4% | 8fm0xfacp0b0g                              H3C-APM 2021-10-11 10:25:37 2021-10-12 10:20:59       528      .0    3% | 1g11ms1r6bnuj                              H3C-APM 2021-10-11 10:25:35 2021-10-12 10:20:56       526      .0    3% | 6sbq34x7ckff7                              H3C-APM 2021-10-11 10:25:36 2021-10-12 10:20:58       524      .0    3% | f6d0fpgm1w2sw                              H3C-APM 2021-10-11 10:25:42 2021-10-12 10:21:04       521      .0    3% | czkw1ncpthxy4                              H3C-APM 2021-10-11 10:25:34 2021-10-12 10:15:35       516      .0    3% | 2v09t9vyy6zk6                              H3C-APM 2021-10-11 10:25:50 2021-10-12 10:20:52       514      .0    3% | 0tha0zcyf9maq                              H3C-APM 2021-10-11 10:25:38 2021-10-12 10:21:00       513      .0    3% | 8ss7js42xzp05                              H3C-APM 2021-10-11 10:25:41 2021-10-12 10:21:03       510      .0    3% | b282h3vx1nh1j                              H3C-APM 2021-10-11 10:25:40 2021-10-12 10:21:02       509      .0    3% | 7m6szm4t720j0                              H3C-APM 2021-10-11 10:25:43 2021-10-12 10:21:05       509      .0    3% | 9w8scutvwbjaw                              H3C-APM 2021-10-11 10:25:47 2021-10-12 10:21:09       508      .0    3% | 5ub6g7qwaf35x                              H3C-APM 2021-10-11 10:25:33 2021-10-12 10:20:54       505      .0    3% | 2sq2bmkwuz6at                              H3C-APM 2021-10-11 10:25:44 2021-10-12 10:21:06       504      .0    3% | 30a5bma58q1w7                              H3C-APM 2021-10-11 10:25:53 2021-10-12 10:21:15       501      .0    3% | az4ju0qgum193                              H3C-APM 2021-10-11 10:25:39 2021-10-12 10:21:01       500      .0    3% | fpamfm2pkznu1                              H3C-APM 2021-10-11 10:25:49 2021-10-12 10:20:51       499      .0    3% | d946h5sr5gt69                              H3C-APM 2021-10-11 10:25:46 2021-10-12 10:21:08       496      .0    3% | 5mwanf0c830mj                              H3C-APM 2021-10-11 10:25:45 2021-10-12 10:21:07       495      .0    3% | 0zrwxj39q7u3w                              H3C-APM 2021-10-11 10:25:51 2021-10-12 10:21:13       494      .0    3% | ck5qb9zs2n34g                              H3C-APM 2021-10-11 10:25:48 2021-10-12 10:21:10       493      .0    3% | 18q3m92yk5zg5                              H3C-APM 2021-10-11 10:25:52 2021-10-12 10:21:14       186      .0    1% | 5t9zzqmqdyxbg                              H3C-APM 2021-10-11 10:40:34 2021-10-12 10:20:55       177      .0    1% | 27m1sf1nknfz2                              H3C-APM 2021-10-11 10:25:54 2021-10-12 10:20:56       177      .0    1% | cyfdvynj0mtc8                              H3C-APM 2021-10-11 10:25:58 2021-10-12 10:15:38       163      .0    1% | 9yfzqfdw2yhs4                              H3C-APM 2021-10-11 10:45:48 2021-10-12 08:45:28        84      .0    1% | 1j262t18zrpxt                              H3C-APM 2021-10-11 11:06:18 2021-10-12 09:45:57        74      .0    0% | ahatnp9sk1b5s                              H3C-APM 2021-10-11 10:56:10 2021-10-12 10:21:12        49      .0    0% | 6uz4za48wf6j7                              H3C-APM 2021-10-11 10:36:04 2021-10-12 09:31:04        47      .0    0% | 68k7ckt95ttcf                              H3C-APM 2021-10-11 10:35:58 2021-10-12 10:10:57        45      .0    0% | 7ksrtc8rzpawc                              H3C-APM 2021-10-11 10:35:56 2021-10-12 09:50:55        39      .0    0% | 22356bkgsdcnh                              H3C-APM 2021-10-11 11:35:31 2021-10-12 09:15:54        30      .0    0% | 0gb620m9hwp4s                              H3C-APM 2021-10-11 10:26:13 2021-10-12 10:16:07        29      .0    0% | czd2z83tzauux                              H3C-APM 2021-10-11 10:30:36 2021-10-12 10:15:57        29      .0    0% | d78ubma8q6xj2                              H3C-APM 2021-10-11 11:06:15 2021-10-12 10:16:14        29      .0    0% | dq6bzb5xdk3h7                              H3C-APM 2021-10-11 10:30:28 2021-10-12 09:35:29        26      .0    0% | 3mubmnaquyqcj                              H3C-APM 2021-10-11 10:30:55 2021-10-12 08:56:16        25      .0    0% | gzg2phr7fjs2j ASM file metadata operation  H3C-APM 2021-10-11 11:20:31 2021-10-12 07:05:53        23      .0    0% | 99v4t515j5j56                              H3C-APM 2021-10-11 10:45:57 2021-10-12 10:16:17        23      .0    0% |                                            H3C-APM 2021-10-11 12:01:07 2021-10-12 09:56:07        22      .0    0% | 9h4w5m54fq46f                              H3C-APM 2021-10-11 11:06:14 2021-10-12 10:16:13        22      .0    0% | c2ypbq9ac2qw5                              H3C-APM 2021-10-11 11:00:44 2021-10-12 09:31:03        19      .0    0% | d0h6nu0uwfjtx                              H3C-APM 2021-10-11 11:06:02 2021-10-12 10:05:42        18      .0    0% | 63d8azd63u566                              H3C-APM 2021-10-11 12:31:06 2021-10-12 09:46:08        13      .0    0% | gwt7tu3383grt                              H3C-APM 2021-10-11 14:36:08 2021-10-12 09:25:50        12      .0    0% | 5r14h528vkacs                              H3C-APM 2021-10-11 12:30:31 2021-10-12 09:35:54        11      .0    0% | 8sxz1p1238fyh                              H3C-APM 2021-10-11 10:25:57 2021-10-12 09:55:36        10      .0    0% | 21t4z1r0k4cyd control file sequential read H3C-APM 2021-10-11 10:40:49 2021-10-12 08:15:28        10      .0    0% | 7mcwvm1xxb7wf                              H3C-APM 2021-10-11 17:00:56 2021-10-12 09:45:37 50 rows selected. --//华为监控的东西.拜托华为的研发写个监控不要乱写,不然真丢中国人的脸.看了其中一些监控sql语句,大部分都是忽悠人的东西,对于问题的分析解决毫无实际价值.