-
加我公众号(orasos)
加我微信(17813235971)
加我QQ(107644445)
标签云
asm恢复 bbed bootstrap$ dul kcbzib_kcrsds_1 kccpb_sanity_check_2 kcratr_nab_less_than_odr MySQL恢复 ORA-00312 ORA-00704 ORA-00742 ORA-01110 ORA-01200 ORA-01555 ORA-01578 ORA-01595 ORA-600 2662 ORA-600 2663 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-600 kcratr_nab_less_than_odr ORA-600 kdsgrp1 ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 Oracle 恢复 ORACLE恢复 ORACLE数据库恢复 oracle 比特币 OSD-04016 YOUR FILES ARE ENCRYPTED 比特币加密文章分类
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (112)
- 数据库 (1,848)
- DB2 (22)
- MySQL (81)
- Oracle (1,676)
- Data Guard (53)
- EXADATA (8)
- GoldenGate (24)
- ORA-xxxxx (168)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (15)
- ORACLE 21C (3)
- Oracle 23ai (8)
- Oracle ASM (70)
- Oracle Bug (8)
- Oracle RAC (55)
- Oracle 安全 (6)
- Oracle 开发 (28)
- Oracle 监听 (29)
- Oracle备份恢复 (636)
- Oracle安装升级 (105)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (90)
- PostgreSQL (37)
- pdu工具 (7)
- PostgreSQL恢复 (13)
- SQL Server (34)
- SQL Server恢复 (14)
- TimesTen (7)
- 达梦数据库 (4)
- 达梦恢复 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (48)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (31)
-
最近发表
- 一次断电引起的Oracle故障恢复-ora-600 2662故障
- OraScan(Oracle 碎片扫描工具) 使用说明
- .[xueyuanjie@onionmail.org].AIR勒索加密数据库恢复
- oracleasm createdisk破坏的acfs文件系统恢复
- 先offline数据文件，再resetlogs导致恢复复杂的故障处理
- exp dmp导入报IMP-00098: INTERNAL ERROR: impgst2故障处理
- Oracle 19c Grid Infrastructure Release Update-202604(19.31)
- Oracle Database 19c Release Update-202604(19.31)
- aix环境rac 私网直连导致haip启动异常
- 又一例TRIM导致asm磁盘数据丢失的故障
- 一次运气好的ORA-600 kcratr_nab_less_than_odr故障处理
- OraFHR快速open被勒索加密破坏的Oracle数据库
- obet一键恢复offline数据文件
- 记录一次win删除数据文件完美恢复案例
- Oracle典型故障：The controlfile header block returned by the OS has a sequence number that is too old
- 国产信创库fio破坏主备库以及备份故障处理
- .wman扩展名勒索mysql数据库恢复
- Oracle数据库被勒索加密一键open工具–OraFHR
- 通过alert日志回顾其他dba oracle异常恢复故障处理以及后续open数据库操作
- 年前几例Oracle数据库被加密为.wman的数据库故障恢复
标签归档：imdb
在ORACLE 12C RAC中使用in memory特性请注意parallel_degree_policy和parallel_force_local参数
在12C RAC的in memory测试中由于未正确启用并行,导致测试结果flush buffer cache后,总是出现大量并行，通过ORACLE 各方努力最终确定是由于parallel_degree_policy=AUTO和parallel_force_local=false开始未设置正确导致.在rac中需要imdb的朋友请注意这两个参数.
设置表存放中inmemory
SQL> alter table CHF.XIFENFEI_888 inmemory;
Table altered.
SQL> set autot on
SQL> select /*+full(t)*/ count(*) from CHF.XIFENFEI_888 t;
COUNT(*)
----------
16883988
Execution Plan
----------------------------------------------------------
Plan hash value: 1642441725
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2566 (8)| 00:00:01 | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 16M| 2566 (8)| 00:00:01 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS INMEMORY FULL| XIFENFEI_888 | 16M| 2566 (8)| 00:00:01 | Q1,00 | PCWP | |
---------------------------------------------------------------------------------------------------------------------
Note
-----
- automatic DOP: Computed Degree of Parallelism is 2
- parallel scans affinitized for inmemory
Statistics
----------------------------------------------------------
213 recursive calls
0 db block gets
435058 consistent gets
40 physical reads
61180 redo size
545 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> set autot off
SQL> select * from gv$im_segments where owner='CHF' and segment_name='XIFENFEI_888';
no rows selected
SQL> select * from gv$im_segments where owner='CHF' and segment_name='XIFENFEI_888';
INST_ID OWNER SEGMENT_NAME PARTI SEGME TABLESPACE_NAME INMEMORY_SIZE BYTES BYTES_NOT_POPULATED POPULATE_ INMEMORY INMEMORY_DISTRI INMEMORY_DUPL INMEMORY_COMPRESS CON_ID
------- ----- --------------- ----- ----- --------------- ------------- ------------ ------------------- --------- -------- --------------- ------------- ----------------- ------
1 CHF XIFENFEI_888 TABLE CHF_DATA 469827584 3571449856 2853101568 STARTED NONE AUTO NO DUPLICATE FOR QUERY LOW 0
2 CHF XIFENFEI_888 TABLE CHF_DATA 332267520 3571449856 3040182272 STARTED NONE AUTO NO DUPLICATE FOR QUERY LOW 0
SQL> select * from gv$im_segments where owner='CHF' and segment_name='XIFENFEI_888';
INST_ID OWNER SEGMENT_NAME PARTI SEGME TABLESPACE_NAME INMEMORY_SIZE BYTES BYTES_NOT_POPULATED POPULATE_ INMEMORY INMEMORY_DISTRI INMEMORY_DUPL INMEMORY_COMPRESS CON_ID
------- ----- --------------- ----- ----- --------------- ------------- ------------ ------------------- --------- -------- --------------- ------------- ----------------- ------
1 CHF XIFENFEI_888 TABLE CHF_DATA 1510211584 3571449856 1444610048 COMPLETED NONE AUTO NO DUPLICATE FOR QUERY LOW 0
2 CHF XIFENFEI_888 TABLE CHF_DATA 1068433408 3571449856 2058321920 COMPLETED NONE AUTO NO DUPLICATE FOR QUERY LOW 0
这里可以看到表加载到inmemory需要时间,不是全表扫描一遍之后里面全表载入到in memory中.
查看执行计划确实走inmemory
SQL> set autot on
SQL> select /*+full(t)*/ count(*) from CHF.XIFENFEI_888 t;
COUNT(*)
----------
16883988
Execution Plan
----------------------------------------------------------
Plan hash value: 1642441725
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 18629 (1)| 00:00:01 | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 16M| 18629 (1)| 00:00:01 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS INMEMORY FULL| XIFENFEI_888 | 16M| 18629 (1)| 00:00:01 | Q1,00 | PCWP | |
---------------------------------------------------------------------------------------------------------------------
Note
-----
- automatic DOP: Computed Degree of Parallelism is 2
- parallel scans affinitized for inmemory
Statistics
----------------------------------------------------------
6 recursive calls
0 db block gets
177415 consistent gets
0 physical reads
23484 redo size
545 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
flush buffer cache后,inmemory执行计划中出现大量物理读
SQL> set autot off
SQL> alter system flush buffer_cache;
System altered.
SQL> /
System altered.
SQL> select * from gv$im_segments where owner='CHF' and segment_name='XIFENFEI_888';
INST_ID OWNER SEGMENT_NAME PARTI SEGME TABLESPACE_NAME INMEMORY_SIZE BYTES BYTES_NOT_POPULATED POPULATE_ INMEMORY INMEMORY_DISTRI INMEMORY_DUPL INMEMORY_COMPRESS CON_ID
------- ----- --------------- ----- ----- --------------- ------------- ------------ ------------------- --------- -------- --------------- ------------- ----------------- ------
1 CHF XIFENFEI_888 TABLE CHF_DATA 1510211584 3571449856 1444610048 COMPLETED NONE AUTO NO DUPLICATE FOR QUERY LOW 0
2 CHF XIFENFEI_888 TABLE CHF_DATA 1068433408 3571449856 2058321920 COMPLETED NONE AUTO NO DUPLICATE FOR QUERY LOW 0
SQL> set autot on
SQL> select /*+full(t)*/ count(*) from CHF.XIFENFEI_888 t;
COUNT(*)
----------
16883988
Execution Plan
----------------------------------------------------------
Plan hash value: 1642441725
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 18629 (1)| 00:00:01 | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 16M| 18629 (1)| 00:00:01 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS INMEMORY FULL| XIFENFEI_888 | 16M| 18629 (1)| 00:00:01 | Q1,00 | PCWP | |
---------------------------------------------------------------------------------------------------------------------
Note
-----
- automatic DOP: Computed Degree of Parallelism is 2
- parallel scans affinitized for inmemory
Statistics
----------------------------------------------------------
6 recursive calls
0 db block gets
177413 consistent gets
176358 physical reads
23456 redo size
545 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> set autot off
再次查询物理读消失
SQL> set autot on
SQL> select /*+full(t)*/ count(*) from CHF.XIFENFEI_888 t;
COUNT(*)
----------
16883988
Execution Plan
----------------------------------------------------------
Plan hash value: 1642441725
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 18629 (1)| 00:00:01 | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 16M| 18629 (1)| 00:00:01 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS INMEMORY FULL| XIFENFEI_888 | 16M| 18629 (1)| 00:00:01 | Q1,00 | PCWP | |
---------------------------------------------------------------------------------------------------------------------
Note
-----
- automatic DOP: Computed Degree of Parallelism is 2
- parallel scans affinitized for inmemory
Statistics
----------------------------------------------------------
6 recursive calls
0 db block gets
177414 consistent gets
0 physical reads
23448 redo size
545 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> set autot off
这里有奇怪点,启用inmemory之后,flush buffer cache后,物理读非常大(基本上和逻辑读一样),不符合常理,因为inmemory和buffer cache是两个独立的东西,就算是flush buffer cache,也不应该导致in memory内的东西失效(而且从v$im_segments中查询是正常的),对于该问题百思不得其解,最后只好寻求inmemory邮件组和GCS帮忙.最终是由于并行相关参数配置导致该问题
SQL> alter system set parallel_force_local=false sid='*'; System altered. SQL> alter system set parallel_degree_policy=AUTO sid='*'; System altered.
修改parallel_force_local和parallel_degree_policy后继续测试
SQL> select * from gv$im_segments where owner='CHF' and segment_name='XIFENFEI_888';
INST_ID OWNER SEGMENT_NAME PARTI SEGME TABLESPACE_NAME INMEMORY_SIZE BYTES BYTES_NOT_POPULATED POPULATE_ INMEMORY INMEMORY_DISTRI INMEMORY_DUPL INMEMORY_COMPRESS CON_ID
------- ----- --------------- ----- ----- --------------- ------------- ------------ ------------------- --------- -------- --------------- ------------- ----------------- ------
1 CHF XIFENFEI_888 TABLE RPT_DATA 1510211584 3571449856 1444610048 COMPLETED NONE AUTO NO DUPLICATE FOR QUERY LOW 0
2 CHF XIFENFEI_888 TABLE RPT_DATA 1069481984 3571449856 2058321920 COMPLETED NONE AUTO NO DUPLICATE FOR QUERY LOW 0
SQL> set autot on
SQL> select /*+full(t)*/ count(*) from CHF.XIFENFEI_888 t;
set autot off
COUNT(*)
----------
16883988
Execution Plan
----------------------------------------------------------
Plan hash value: 1642441725
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 18629 (1)| 00:00:01 | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 16M| 18629 (1)| 00:00:01 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS INMEMORY FULL| XIFENFEI_888 | 16M| 18629 (1)| 00:00:01 | Q1,00 | PCWP | |
---------------------------------------------------------------------------------------------------------------------
Note
-----
- automatic DOP: Computed Degree of Parallelism is 2
- parallel scans affinitized for inmemory
Statistics
----------------------------------------------------------
6 recursive calls
0 db block gets
776 consistent gets
0 physical reads
0 redo size
545 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> alter system flush buffer_cache
2 ;
System altered.
SQL> /
System altered.
SQL>
SQL> set autot on
select /*+full(t)*/ count(*) from CHF.XIFENFEI_888 t;
set autot off
SQL>
COUNT(*)
----------
16883988
Execution Plan
----------------------------------------------------------
Plan hash value: 1642441725
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 18629 (1)| 00:00:01 | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 16M| 18629 (1)| 00:00:01 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS INMEMORY FULL| XIFENFEI_888 | 16M| 18629 (1)| 00:00:01 | Q1,00 | PCWP | |
---------------------------------------------------------------------------------------------------------------------
Note
-----
- automatic DOP: Computed Degree of Parallelism is 2
- parallel scans affinitized for inmemory
Statistics
----------------------------------------------------------
6 recursive calls
0 db block gets
776 consistent gets
2 physical reads
0 redo size
545 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> spool off
通过测试证明,在RAC环境中,如果要使用IN MEMORY特性,需要设置parallel_degree_policy=AUTO和parallel_force_local=false之后,才能够真正意义上的启动IM特性,不然只是执行计划中的启用,可能是假象
