Oracle里面执行insert into插入太慢怎么解决-CSDN社区

半世琉璃的程序猿 2020-01-14

打赏
举报

赞成1楼楼主的办法

wffffc 2019-12-17

打赏
举报

弄个临时表，存储需要插入的数据

odMiracle 2019-12-17

打赏
举报

跟你说个快的 sqlload 85万条数据 26s不到

小小寅 2019-12-17

打赏
举报

用外部表，sqlload

学海无涯-回头是岸 2019-12-13

打赏
举报

引用 15 楼 minsic78 的回复:

[quote=引用 14 楼学海无涯-回头是岸的回复:] [quote=引用 13 楼 minsic78 的回复:] [quote=引用 12 楼学海无涯-回头是岸的回复:] /* + nologging paralle 4 */

nologging不是提示，顺带着后面的parallel 。。。[/quote] 可以这样用的[/quote] nologging是关键字，不是提示，但这么写当然不会报错。如果想要得到少些日志的效果，需要alter table <tablename> nologging; 只不过通常非仓库类生产环境不会允许这么干，干了也可能白干罢了…… 这算hint类非常常见的误区。回到楼主主题帖，个人觉得问题应该出在自定义函数上，而且恐怕还很难优化，楼主可以贴代码出来看看。 [/quote] 好像这样加nologging是没啥用哎，问一下：paralle 加载是direct 方式，应该不用加append了吧？

minsic78 2019-12-13

打赏
举报

引用 14 楼学海无涯-回头是岸的回复:

[quote=引用 13 楼 minsic78 的回复:] [quote=引用 12 楼学海无涯-回头是岸的回复:] /* + nologging paralle 4 */

nologging不是提示，顺带着后面的parallel 。。。[/quote] 可以这样用的[/quote] nologging是关键字，不是提示，但这么写当然不会报错。如果想要得到少些日志的效果，需要alter table <tablename> nologging; 只不过通常非仓库类生产环境不会允许这么干，干了也可能白干罢了…… 这算hint类非常常见的误区。回到楼主主题帖，个人觉得问题应该出在自定义函数上，而且恐怕还很难优化，楼主可以贴代码出来看看。

学海无涯-回头是岸 2019-12-13

打赏
举报

引用 13 楼 minsic78 的回复:

[quote=引用 12 楼学海无涯-回头是岸的回复:] /* + nologging paralle 4 */

nologging不是提示，顺带着后面的parallel 。。。[/quote] 可以这样用的

minsic78 2019-12-13

打赏
举报

引用 32 楼 riven2011 的回复:

@minsic78 老哥工作不饱和啊

你这话看的我直哆嗦

minsic78 2019-12-13

打赏
举报

引用 12 楼学海无涯-回头是岸的回复:

/* + nologging paralle 4 */

nologging不是提示，顺带着后面的parallel 。。。

riven2011 2019-12-13

打赏
举报

@minsic78 老哥工作不饱和啊

minsic78 2019-12-13

打赏
举报

改成文本模式就有了，不过只有直接物理写的请求次数和写的空间大小，转成8k数据块写入次数，与v$sql中的direct_writes保持一致：

SQL Monitoring Report

SQL Text
------------------------------
insert /*+monitor parallel(t 4)*/into t select /*+parallel(a 4)*/ * from t2 a

Global Information
------------------------------
 Status              :  DONE                           
 Instance ID         :  1                              
 Session             :  SYS (2853:16591)               
 SQL ID              :  2r7b49phnp2pb                  
 SQL Execution ID    :  16777217                       
 Execution Started   :  12/13/2019 16:48:08            
 First Refresh Time  :  12/13/2019 16:48:08            
 Last Refresh Time   :  12/13/2019 16:48:16            
 Duration            :  8s                             
 Module/Action       :  sqlplus@sjfx-db7 (TNS V1-V3)/- 
 Service             :  SYS$USERS                      
 Program             :  sqlplus@sjfx-db7 (TNS V1-V3)   

Global Stats
=================================================================================================
| Elapsed |   Cpu   |    IO    | Concurrency |  Other   | Buffer | Read | Read  | Write | Write |
| Time(s) | Time(s) | Waits(s) |  Waits(s)   | Waits(s) |  Gets  | Reqs | Bytes | Reqs  | Bytes |
=================================================================================================
|      31 |    0.76 |       29 |        0.00 |     0.80 |   175K | 1164 |   1GB |   898 | 222MB |
=================================================================================================

Parallel Execution Details (DOP=4 , Servers Allocated=4)
============================================================================================================================================================
|      Name      | Type  | Server# | Elapsed |   Cpu   |    IO    | Concurrency |  Other   | Buffer | Read | Read  | Write | Write |      Wait Events      |
|                |       |         | Time(s) | Time(s) | Waits(s) |  Waits(s)   | Waits(s) |  Gets  | Reqs | Bytes | Reqs  | Bytes |      (sample #)       |
============================================================================================================================================================
| PX Coordinator | QC    |         |    0.02 |    0.00 |          |             |     0.02 |    133 |      |     . |       |     . |                       |
| p000           | Set 1 |       1 |    7.75 |    0.21 |     7.42 |        0.00 |     0.12 |  67806 |  490 | 469MB |   209 |  52MB | reliable message (1)  |
|                |       |         |         |         |          |             |          |        |      |       |       |       | direct path read (4)  |
|                |       |         |         |         |          |             |          |        |      |       |       |       | direct path write (3) |
| p001           | Set 1 |       2 |    7.72 |    0.14 |     6.92 |             |     0.66 |  25532 |  158 | 149MB |   173 |  43MB | reliable message (1)  |
|                |       |         |         |         |          |             |          |        |      |       |       |       | direct path read (1)  |
|                |       |         |         |         |          |             |          |        |      |       |       |       | direct path write (6) |
| p002           | Set 1 |       3 |    7.70 |    0.20 |     7.50 |             |     0.00 |  42309 |  269 | 256MB |   258 |  64MB | direct path read (3)  |
|                |       |         |         |         |          |             |          |        |      |       |       |       | direct path write (5) |
| p003           | Set 1 |       4 |    7.74 |    0.20 |     7.54 |             |          |  39566 |  247 | 234MB |   258 |  64MB | direct path read (3)  |
|                |       |         |         |         |          |             |          |        |      |       |       |       | direct path write (4) |
============================================================================================================================================================

SQL Plan Monitoring Details (Plan Hash Value=3136303183)
================================================================================================================================================================================
| Id |        Operation        |   Name   |  Rows   | Cost  |   Time    | Start  | Execs |   Rows   | Read | Read  | Write | Write |  Mem  | Activity |    Activity Detail     |
|    |                         |          | (Estim) |       | Active(s) | Active |       | (Actual) | Reqs | Bytes | Reqs  | Bytes | (Max) |   (%)    |      (# samples)       |
================================================================================================================================================================================
|  0 | INSERT STATEMENT        |          |         |       |         1 |     +8 |     5 |        4 |      |       |       |       |       |     6.25 | reliable message (2)   |
|  1 |   PX COORDINATOR        |          |         |       |         1 |     +8 |     5 |        4 |      |       |       |       |       |          |                        |
|  2 |    PX SEND QC (RANDOM)  | :TQ10000 |      1M | 10674 |         1 |     +8 |     4 |        4 |      |       |       |       |       |          |                        |
|  3 |     LOAD AS SELECT      |          |         |       |         7 |     +2 |     4 |        4 |      |       |   890 | 222MB |    2M |    59.37 | Cpu (1)                |
|    |                         |          |         |       |           |        |       |          |      |       |       |       |       |          | direct path write (18) |
|  4 |      PX BLOCK ITERATOR  |          |      1M | 10674 |         7 |     +2 |     4 |       2M |      |       |       |       |       |          |                        |
|  5 |       TABLE ACCESS FULL | T2       |      1M | 10674 |         8 |     +1 |    52 |       2M | 1160 |   1GB |       |       |       |    34.37 | direct path read (11)  |
================================================================================================================================================================================

minsic78 2019-12-13

打赏
举报

@riven2011 挺有意思，各种工具一起上，发现有些东西对不上号了。 1、v$sql：

2、gather_plan_statistics hint + dbms_xplan.display_cursor：

3、monitor hint + dbms_sqltune.report_sql_monitor：

minsic78 2019-12-13

打赏
举报

引用 28 楼 riven2011 的回复:

[quote=引用 27 楼 minsic78 的回复:] @riven2011 手头暂时没有更低版本的库，我在11.2做了一些测试，简单地看了下IO的情况，这个图里的数据感觉不好解释：

这个可以看到并行器的使用情况[/quote] 没有打开parallel dml？否则应该不会是传统load方式

riven2011 2019-12-13

打赏
举报

引用 27 楼 minsic78 的回复:

@riven2011

手头暂时没有更低版本的库，我在11.2做了一些测试，简单地看了下IO的情况，这个图里的数据感觉不好解释：

这个可以看到并行器的使用情况

minsic78 2019-12-13

打赏
举报

@riven2011 手头暂时没有更低版本的库，我在11.2做了一些测试，简单地看了下IO的情况，这个图里的数据感觉不好解释：

riven2011 2019-12-13

打赏
举报

引用 25 楼 minsic78 的回复:

[quote=引用 24 楼 riven2011 的回复:]
[quote=引用 23 楼 minsic78 的回复:]
[quote=引用 22 楼 riven2011 的回复:]
[quote=引用 21 楼 minsic78 的回复:]
[quote=引用 20 楼 riven2011 的回复:]
[quote=引用 19 楼 minsic78 的回复:]
[quote=引用 18 楼 riven2011 的回复:]
[quote=引用 17 楼 minsic78 的回复:]
[quote=引用 16 楼学海无涯-回头是岸的回复:]
[quote=引用 15 楼 minsic78 的回复:]
[quote=引用 14 楼学海无涯-回头是岸的回复:]
[quote=引用 13 楼 minsic78 的回复:]
[quote=引用 12 楼学海无涯-回头是岸的回复:]
/* + nologging paralle 4 */

nologging不是提示，顺带着后面的parallel 。。。[/quote]
可以这样用的[/quote]

nologging是关键字，不是提示，但这么写当然不会报错。如果想要得到少些日志的效果，需要alter table <tablename> nologging; 只不过通常非仓库类生产环境不会允许这么干，干了也可能白干罢了……
这算hint类非常常见的误区。

回到楼主主题帖，个人觉得问题应该出在自定义函数上，而且恐怕还很难优化，楼主可以贴代码出来看看。
[/quote]
好像这样加nologging是没啥用哎，问一下：paralle 加载是direct 方式，应该不用加append了吧？[/quote]

理论上来讲，parallel和append都是用了直接路径插入，不过个人感觉使用中append的效率更高，也没想明白确切的原因，若有头绪，请不吝分享。[/quote]

parallel 是并行器指示而不是方式，实际还是生效于select，append是加载方式，nologging需要配合数据模式 noarchive模式才有明细效果[/quote]

并行本身就是直接物理读或者物理写，不仅select可以并行，DML、DDL都可以并行，看你怎么用了。当然并行并不一定能带来性能收益，在一个复杂的系统上，大量肆无忌惮的并行基本上是拖后腿的。[/quote]
你可以测试一下在insert 与select 同时加parallel 它是如何运行的例如
insert /*+parallel(8)*/into a select /*+parallel(1)*/ from a[/quote]

不知如何测试它如何运行？愿闻其详[/quote]

/*+monitor*/ dbms_sqltune.report_sql_monitor
[/quote]

可否更详细点？怎么用这个包观察并行sql是怎么运行的？[/quote]

私密我一下[/quote]

发论坛吧，和大家一起共同进步 [/quote]

论坛沟通很困难我大概说一下你用/*+monitor*/指示器监控你的sql，然后SELECT dbms_sqltune.report_sql_monitor(sql_id =>'9smd2fs2mx42u',TYPE=>'HTML') FROM DUAL; 里面有实际运用的资源包括并行器 IO 时间等

minsic78 2019-12-13

打赏
举报

引用 24 楼 riven2011 的回复:

[quote=引用 23 楼 minsic78 的回复:] [quote=引用 22 楼 riven2011 的回复:] [quote=引用 21 楼 minsic78 的回复:] [quote=引用 20 楼 riven2011 的回复:] [quote=引用 19 楼 minsic78 的回复:] [quote=引用 18 楼 riven2011 的回复:] [quote=引用 17 楼 minsic78 的回复:] [quote=引用 16 楼学海无涯-回头是岸的回复:] [quote=引用 15 楼 minsic78 的回复:] [quote=引用 14 楼学海无涯-回头是岸的回复:] [quote=引用 13 楼 minsic78 的回复:] [quote=引用 12 楼学海无涯-回头是岸的回复:] /* + nologging paralle 4 */

nologging不是提示，顺带着后面的parallel 。。。[/quote] 可以这样用的[/quote] nologging是关键字，不是提示，但这么写当然不会报错。如果想要得到少些日志的效果，需要alter table <tablename> nologging; 只不过通常非仓库类生产环境不会允许这么干，干了也可能白干罢了…… 这算hint类非常常见的误区。回到楼主主题帖，个人觉得问题应该出在自定义函数上，而且恐怕还很难优化，楼主可以贴代码出来看看。 [/quote] 好像这样加nologging是没啥用哎，问一下：paralle 加载是direct 方式，应该不用加append了吧？[/quote] 理论上来讲，parallel和append都是用了直接路径插入，不过个人感觉使用中append的效率更高，也没想明白确切的原因，若有头绪，请不吝分享。[/quote] parallel 是并行器指示而不是方式，实际还是生效于select，append是加载方式，nologging需要配合数据模式 noarchive模式才有明细效果[/quote] 并行本身就是直接物理读或者物理写，不仅select可以并行，DML、DDL都可以并行，看你怎么用了。当然并行并不一定能带来性能收益，在一个复杂的系统上，大量肆无忌惮的并行基本上是拖后腿的。[/quote] 你可以测试一下在insert 与select 同时加parallel 它是如何运行的例如 insert /*+parallel(8)*/into a select /*+parallel(1)*/ from a[/quote] 不知如何测试它如何运行？愿闻其详[/quote] /*+monitor*/ dbms_sqltune.report_sql_monitor [/quote] 可否更详细点？怎么用这个包观察并行sql是怎么运行的？[/quote] 私密我一下[/quote] 发论坛吧，和大家一起共同进步

riven2011 2019-12-13

打赏
举报

引用 23 楼 minsic78 的回复:

[quote=引用 22 楼 riven2011 的回复:]
[quote=引用 21 楼 minsic78 的回复:]
[quote=引用 20 楼 riven2011 的回复:]
[quote=引用 19 楼 minsic78 的回复:]
[quote=引用 18 楼 riven2011 的回复:]
[quote=引用 17 楼 minsic78 的回复:]
[quote=引用 16 楼学海无涯-回头是岸的回复:]
[quote=引用 15 楼 minsic78 的回复:]
[quote=引用 14 楼学海无涯-回头是岸的回复:]
[quote=引用 13 楼 minsic78 的回复:]
[quote=引用 12 楼学海无涯-回头是岸的回复:]
/* + nologging paralle 4 */

nologging不是提示，顺带着后面的parallel 。。。[/quote]
可以这样用的[/quote]

nologging是关键字，不是提示，但这么写当然不会报错。如果想要得到少些日志的效果，需要alter table <tablename> nologging; 只不过通常非仓库类生产环境不会允许这么干，干了也可能白干罢了……
这算hint类非常常见的误区。

回到楼主主题帖，个人觉得问题应该出在自定义函数上，而且恐怕还很难优化，楼主可以贴代码出来看看。
[/quote]
好像这样加nologging是没啥用哎，问一下：paralle 加载是direct 方式，应该不用加append了吧？[/quote]

理论上来讲，parallel和append都是用了直接路径插入，不过个人感觉使用中append的效率更高，也没想明白确切的原因，若有头绪，请不吝分享。[/quote]

parallel 是并行器指示而不是方式，实际还是生效于select，append是加载方式，nologging需要配合数据模式 noarchive模式才有明细效果[/quote]

并行本身就是直接物理读或者物理写，不仅select可以并行，DML、DDL都可以并行，看你怎么用了。当然并行并不一定能带来性能收益，在一个复杂的系统上，大量肆无忌惮的并行基本上是拖后腿的。[/quote]
你可以测试一下在insert 与select 同时加parallel 它是如何运行的例如
insert /*+parallel(8)*/into a select /*+parallel(1)*/ from a[/quote]

不知如何测试它如何运行？愿闻其详[/quote]

/*+monitor*/ dbms_sqltune.report_sql_monitor
[/quote]

可否更详细点？怎么用这个包观察并行sql是怎么运行的？[/quote]

私密我一下

minsic78 2019-12-13

打赏
举报

引用 22 楼 riven2011 的回复:

[quote=引用 21 楼 minsic78 的回复:] [quote=引用 20 楼 riven2011 的回复:] [quote=引用 19 楼 minsic78 的回复:] [quote=引用 18 楼 riven2011 的回复:] [quote=引用 17 楼 minsic78 的回复:] [quote=引用 16 楼学海无涯-回头是岸的回复:] [quote=引用 15 楼 minsic78 的回复:] [quote=引用 14 楼学海无涯-回头是岸的回复:] [quote=引用 13 楼 minsic78 的回复:] [quote=引用 12 楼学海无涯-回头是岸的回复:] /* + nologging paralle 4 */

nologging不是提示，顺带着后面的parallel 。。。[/quote] 可以这样用的[/quote] nologging是关键字，不是提示，但这么写当然不会报错。如果想要得到少些日志的效果，需要alter table <tablename> nologging; 只不过通常非仓库类生产环境不会允许这么干，干了也可能白干罢了…… 这算hint类非常常见的误区。回到楼主主题帖，个人觉得问题应该出在自定义函数上，而且恐怕还很难优化，楼主可以贴代码出来看看。 [/quote] 好像这样加nologging是没啥用哎，问一下：paralle 加载是direct 方式，应该不用加append了吧？[/quote] 理论上来讲，parallel和append都是用了直接路径插入，不过个人感觉使用中append的效率更高，也没想明白确切的原因，若有头绪，请不吝分享。[/quote] parallel 是并行器指示而不是方式，实际还是生效于select，append是加载方式，nologging需要配合数据模式 noarchive模式才有明细效果[/quote] 并行本身就是直接物理读或者物理写，不仅select可以并行，DML、DDL都可以并行，看你怎么用了。当然并行并不一定能带来性能收益，在一个复杂的系统上，大量肆无忌惮的并行基本上是拖后腿的。[/quote] 你可以测试一下在insert 与select 同时加parallel 它是如何运行的例如 insert /*+parallel(8)*/into a select /*+parallel(1)*/ from a[/quote] 不知如何测试它如何运行？愿闻其详[/quote] /*+monitor*/ dbms_sqltune.report_sql_monitor [/quote] 可否更详细点？怎么用这个包观察并行sql是怎么运行的？

riven2011 2019-12-13

打赏
举报

引用 21 楼 minsic78 的回复:

[quote=引用 20 楼 riven2011 的回复:]
[quote=引用 19 楼 minsic78 的回复:]
[quote=引用 18 楼 riven2011 的回复:]
[quote=引用 17 楼 minsic78 的回复:]
[quote=引用 16 楼学海无涯-回头是岸的回复:]
[quote=引用 15 楼 minsic78 的回复:]
[quote=引用 14 楼学海无涯-回头是岸的回复:]
[quote=引用 13 楼 minsic78 的回复:]
[quote=引用 12 楼学海无涯-回头是岸的回复:]
/* + nologging paralle 4 */

nologging不是提示，顺带着后面的parallel 。。。[/quote]
可以这样用的[/quote]

nologging是关键字，不是提示，但这么写当然不会报错。如果想要得到少些日志的效果，需要alter table <tablename> nologging; 只不过通常非仓库类生产环境不会允许这么干，干了也可能白干罢了……
这算hint类非常常见的误区。

回到楼主主题帖，个人觉得问题应该出在自定义函数上，而且恐怕还很难优化，楼主可以贴代码出来看看。
[/quote]
好像这样加nologging是没啥用哎，问一下：paralle 加载是direct 方式，应该不用加append了吧？[/quote]

理论上来讲，parallel和append都是用了直接路径插入，不过个人感觉使用中append的效率更高，也没想明白确切的原因，若有头绪，请不吝分享。[/quote]

parallel 是并行器指示而不是方式，实际还是生效于select，append是加载方式，nologging需要配合数据模式 noarchive模式才有明细效果[/quote]

并行本身就是直接物理读或者物理写，不仅select可以并行，DML、DDL都可以并行，看你怎么用了。当然并行并不一定能带来性能收益，在一个复杂的系统上，大量肆无忌惮的并行基本上是拖后腿的。[/quote]
你可以测试一下在insert 与select 同时加parallel 它是如何运行的例如
insert /*+parallel(8)*/into a select /*+parallel(1)*/ from a[/quote]

不知如何测试它如何运行？愿闻其详[/quote]

/*+monitor*/ dbms_sqltune.report_sql_monitor