2. it
  3. Insert 语句对 nologging 与 logging表 在不同场景下的优化

Insert 语句对 nologging 与 logging表 在不同场景下的优化

mac2022-06-30  87

前言

前段时间报表数据库上有条insert sql语句，插入的大量数据，执行非常慢，需要对其进行分析优化。

分析步骤是在：ARCHIVE与NOARCHIVE模式下进行。

测试场景: 分别对表的常规插入，表在append插入，表在append + parallel插入进行性能测试，得出结果。

环境准备

数据库版本基础表nologging表logging表Oracle 11gT1T2T3 #创建T1,T2,T3表 create table t1 as select * from dba_objects; create table t2 as select * from dba_objects where 1=2; create table t3 as select * from dba_objects where 1=2; #往T1表插入数据 SQL> insert into t1 select * from t1; 72813 rows created. SQL> / 145626 rows created. SQL> / 291252 rows created. SQL> select count(*) from t1; COUNT(*) ---------- 582504 #设置T2表为nologging属性 SQL> alter table t2 nologging; Table altered.

数据库处于ARCHIVE时

常规插入

nologging 表T2

SQL> insert into t2 select * from t1; commit; 582824 rows created. Execution Plan ---------------------------------------------------------- Plan hash value: 3617692013 --------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------- | 0 | INSERT STATEMENT | | 582K| 53M| 1455 (2)| 00:00:18 | | 1 | LOAD TABLE CONVENTIONAL | T2 | | | | | | 2 | TABLE ACCESS FULL | T1 | 582K| 53M| 1455 (2)| 00:00:18 | --------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 3345 recursive calls 46879 db block gets 27878 consistent gets 8269 physical reads 67752144 redo size 838 bytes sent via SQL*Net to client 784 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 3 sorts (memory) 0 sorts (disk) 582824 rows processed SQL> Commit complete.

耗费：67752144 redo size

logging 表T3

SQL> insert into t3 select * from t1; commit; 582824 rows created. Execution Plan ---------------------------------------------------------- Plan hash value: 3617692013 --------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------- | 0 | INSERT STATEMENT | | 582K| 53M| 1455 (2)| 00:00:18 | | 1 | LOAD TABLE CONVENTIONAL | T3 | | | | | | 2 | TABLE ACCESS FULL | T1 | 582K| 53M| 1455 (2)| 00:00:18 | --------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 2860 recursive calls 46875 db block gets 27811 consistent gets 1 physical reads 67875992 redo size 829 bytes sent via SQL*Net to client 784 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 582824 rows processed SQL> Commit complete.

耗费：67875992 redo size

append 插入

nologging 表T2

SQL> insert /*+ append */ into t2 select * from t1; commit; 582824 rows created. Execution Plan ---------------------------------------------------------- ERROR: ORA-12838: cannot read/modify an object after modifying it in parallel SP2-0612: Error generating AUTOTRACE EXPLAIN report Statistics ---------------------------------------------------------- 2627 recursive calls 9324 db block gets 8832 consistent gets 0 physical reads 143436 redo size 824 bytes sent via SQL*Net to client 798 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 582824 rows processed

耗费：143436 redo size

logging 表T3

SQL> insert /*+ append */ into t3 select * from t1; 582824 rows created. Execution Plan ---------------------------------------------------------- ERROR: ORA-12838: cannot read/modify an object after modifying it in parallel SP2-0612: Error generating AUTOTRACE EXPLAIN report Statistics ---------------------------------------------------------- 2627 recursive calls 9327 db block gets 8832 consistent gets 0 physical reads 68384900 redo size 822 bytes sent via SQL*Net to client 797 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 582824 rows processed

耗费：68384900 redo size

parallel + append 插入

nologging 表T2

SQL> alter session enable parallel dml; insert /*+ append parallel(2) */ into t2 select * from t1; commit; Session altered. SQL> 582824 rows created. Execution Plan ---------------------------------------------------------- ERROR: ORA-12838: cannot read/modify an object after modifying it in parallel SP2-0612: Error generating AUTOTRACE EXPLAIN report Statistics ---------------------------------------------------------- 52 recursive calls 32 db block gets 19 consistent gets 0 physical reads 21916 redo size 824 bytes sent via SQL*Net to client 809 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 582824 rows processed

耗费：21916 redo size

logging 表T3

SQL> alter session enable parallel dml; insert /*+ append parallel(2)*/ into t3 select * from t1; commit; Session altered. SQL> 582824 rows created. Execution Plan ---------------------------------------------------------- ERROR: ORA-12838: cannot read/modify an object after modifying it in parallel SP2-0612: Error generating AUTOTRACE EXPLAIN report Statistics ---------------------------------------------------------- 50 recursive calls 33 db block gets 20 consistent gets 0 physical reads 21308 redo size 824 bytes sent via SQL*Net to client 808 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 582824 rows processed

耗费：21308 redo size

数据库处于NOARCHIVE时

常规插入

nologging 表T2

Execution Plan ---------------------------------------------------------- Plan hash value: 3617692013 --------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------- | 0 | INSERT STATEMENT | | 582K| 53M| 1455 (2)| 00:00:18 | | 1 | LOAD TABLE CONVENTIONAL | T2 | | | | | | 2 | TABLE ACCESS FULL | T1 | 582K| 53M| 1455 (2)| 00:00:18 | --------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 2538 recursive calls 46869 db block gets 27796 consistent gets 8266 physical reads 67754744 redo size 824 bytes sent via SQL*Net to client 784 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 582824 rows processed

耗费：67754744 redo size

logging 表T3

Execution Plan ---------------------------------------------------------- Plan hash value: 3617692013 --------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------- | 0 | INSERT STATEMENT | | 582K| 53M| 1455 (2)| 00:00:18 | | 1 | LOAD TABLE CONVENTIONAL | T3 | | | | | | 2 | TABLE ACCESS FULL | T1 | 582K| 53M| 1455 (2)| 00:00:18 | --------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 2593 recursive calls 46873 db block gets 27800 consistent gets 1600 physical reads 67757328 redo size 824 bytes sent via SQL*Net to client 784 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 3 sorts (memory) 0 sorts (disk) 582824 rows processed

耗费：67757328 redo size

append 插入

nologging 表T2

Statistics ---------------------------------------------------------- 2627 recursive calls 9324 db block gets 8832 consistent gets 2993 physical reads 143480 redo size 822 bytes sent via SQL*Net to client 798 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 582824 rows processed

耗费：143480 redo size

logging 表T3

Statistics ---------------------------------------------------------- 2627 recursive calls 9327 db block gets 8832 consistent gets 0 physical reads 143420 redo size 821 bytes sent via SQL*Net to client 798 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 582824 rows processed

耗费：143420 redo size

parallel + append 插入

nologging 表T2

Statistics ---------------------------------------------------------- 50 recursive calls 32 db block gets 21 consistent gets 0 physical reads 21896 redo size 823 bytes sent via SQL*Net to client 810 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 582824 rows processed

耗费：21896 redo size

logging 表T3

Statistics ---------------------------------------------------------- 50 recursive calls 33 db block gets 20 consistent gets 0 physical reads 21896 redo size 821 bytes sent via SQL*Net to client 809 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 582824 rows processed

耗费：21896 redo size

综合比较

属性表名常规插入产生的redo sizeapppend插入产生的redo sizeapppend + parallel插入产生的redo size数据库模式archivenologingt26775214414343621916logingt3678759926838490021308数据库模式noarchivenologingt26775474414348021896logingt36775732814342021896

1）数据库处于ARCHIVE模式时，对logging表执行append插入，是对性能没有优化的。加并行parallel才会有影响。 2）数据库处于NOARCHIVE模式时，对logging表执行append插入，可以有效的提升性能。当然加并行parallel效果会更好

转载于:https://www.cnblogs.com/wanbin/p/9514681.html

相关资源：JAVA上百实例源码以及开源项目
最新回复(0)