标签云
asm恢复 bbed bootstrap$ dul In Memory kcbzib_kcrsds_1 kccpb_sanity_check_2 kfed MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-01110 ORA-01555 ORA-01578 ORA-08103 ORA-600 2131 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-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)
- 操作系统 (102)
- 数据库 (1,679)
- DB2 (22)
- MySQL (73)
- Oracle (1,541)
- Data Guard (52)
- EXADATA (8)
- GoldenGate (24)
- ORA-xxxxx (159)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (14)
- ORACLE 21C (3)
- Oracle 23ai (7)
- Oracle ASM (67)
- Oracle Bug (8)
- Oracle RAC (52)
- Oracle 安全 (6)
- Oracle 开发 (28)
- Oracle 监听 (28)
- Oracle备份恢复 (563)
- Oracle安装升级 (92)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (79)
- PostgreSQL (18)
- PostgreSQL恢复 (6)
- SQL Server (27)
- SQL Server恢复 (8)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (37)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (20)
-
最近发表
- 解决oracle数据文件路径有回车故障
- .wstop扩展名勒索数据库恢复
- Oracle Recovery Tools工具一键解决ORA-00376 ORA-01110故障(文件offline)
- OGG-02771 Input trail file format RELEASE 19.1 is different from previous trail file form at RELEASE 11.2.
- OGG-02246 Source redo compatibility level 19.0.0 requires trail FORMAT 12.2 or higher
- GoldenGate 19安装和打patch
- dd破坏asm磁盘头恢复
- 删除asmlib磁盘导致磁盘组故障恢复
- Kylin Linux 安装19c
- ORA-600 krse_arc_complete.4
- Oracle 19c 202410补丁(RUs+OJVM)
- ntfs MFT损坏(ntfs文件系统故障)导致oracle异常恢复
- .mkp扩展名oracle数据文件加密恢复
- 清空redo,导致ORA-27048: skgfifi: file header information is invalid
- A_H_README_TO_RECOVER勒索恢复
- 通过alert日志分析客户自行对一个数据库恢复的来龙去脉和点评
- ORA-12514: TNS: 监听进程不能解析在连接描述符中给出的SERVICE_NAME
- ORA-01092 ORA-00604 ORA-01558故障处理
- ORA-65088: database open should be retried
- Oracle 19c异常恢复—ORA-01209/ORA-65088
标签归档: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特性,不然只是执行计划中的启用,可能是假象