当前位置：首页 > 编程资源 > 编程问答 >内容正文

编程问答

Insert插入不同的列数量，统计信息对比

发布时间：2025/3/15 编程问答 36 豆豆

生活随笔收集整理的这篇文章主要介绍了 Insert插入不同的列数量，统计信息对比小编觉得挺不错的,现在分享给大家,帮大家做个参考.

一、实验目的：

Insert插入表中相同的行数量，不同的列数量，通过10046 和autotrace工具对比查看逻辑读、物理读、time数据，并得出相应结论

二、测试

2.1测试流程：

=>【为尽可能满足测试数据可靠性，提前准备好操作流程】

=>实验数据及环境的准备：

#目标端环境准备：授予用户yang表空间的使用权限、

转储目录的创建及读写权限授予给yang用户

及开启autotrace工具的权限

#源库使用数据泵导出一张表的测试数据，操作系统命令scp远程传输至目标端，目标端使用Impdp导入测试环境中，得到测试源表及数据后，创建一个与源表结构相同的测试表（结构相同，没有数据）

#查询验证源表，测试表是否满足上述条件，测试表是否有约束，有则禁用约束

=>会话监控10046 level 1事件开启、自动追踪 autotrace开启

会话一、使用SYS用户登录：对会话二进行10046 trace level 1事件追踪

会话二、使用yang用户登录：开启set autotrace traceonly 开启自动追踪工具

=>insert操作执行：

yang用户：执行第一个insert 操作，对测试表中所有的数据进行插入

commit提交后，truncate 截断清空测试表

yang用户：执行第二个insert操作，对测试表中的三个列数据进行插入

commit提交后

=>autotrace工具收集信息对比：

逻辑读、物理读、执行时间统计对比

=>结束会话追踪，生成trace文件，10046事件收集信息对比：

逻辑读、物理读、执行时间统计对比

=>对比结果进行分析，得出相应结论

2.2测试操作说明：

2.2.1工具说明：

=>10046事件：可以跟踪应用程序执行的SQL语句，根据开启的追踪级别不同：查询不同级别的信息

Level 1: 可以查看包含解析、执行、提取、提交、回滚等信息记录

Level 4: 可以查看包含解析、执行、提取、提交、回滚的信息 + 变量详细信息

Level 8: 可以查看包含解析、执行、提取、提交、回滚的信息 + 等待事件

Level 12:可以查看包含解析、执行、提取、提交、回滚的信息 + 绑定变量+等待事件

=>autotrace 自动追踪工具：可以查看SQL执行结果、执行计划、统计信息的查看

Set autotrace on 打开工具开关，显示SQL执行结果+执行计划+统计信息 Set autotrace traceonly 显示执行计划+统计信息 Set autotrace traceonly explain 显示执行计划 Set autotrace traceonly statistics 显示统计信息 2.2.2：操作说明 =>禁用测试表约束的考量：因为测试中，有Insert插入少量列的数据测试，如果有not null约束，则该列必须有值，为了简化操作过程，禁用约束 =>trace文件中如何唯一标识SQL，如果测试过程中，由于误操作等原因产生多个相同的Insert语句，如何获取想要的SQL统计信息，每个Insert语句，通过大小写不同（得到不同的SQL_ID），即使相同的操作，SQL_id不同，更精确查找实验结果 2.3实际操作： =>目标端准备： #授予表空间存储无限制权限 SQL> grant resource to yang; #授予yang用户可以开启autotrace权限 @?/sqlplus/admin/plustrce SQL> grant plustrace to yang;#创建转储目录 SQL> create directory yy as '/home/oracle'; #转储目录读写授予yang用户 SQL> grant read,write on directory yy to yang; #查询转储目录 SQL> select directory_name,directory_path from dba_directories;DIRECTORY_NAME DIRECTORY_PATH --------------------------------------------------------YY /home/oracle

=>源端数据导出，操作系统SCP命令远程传输文件至目标端

#测试数据从源库导出： expdp \'/ as sysdba\' DIRECTORY=DMPDIR logfile=G_BIL_C_DAT_LTE_206_T_20180414.log
DUMPFILE=G_BIL_C_DAT_LTE_206_T_20180414.dmp tables='gat.G_BIL_C_DAT_LTE_206_T_20180414'
query='"where rownum<12000"'

#操作系统SCP远程拷贝
$ scp G_BIL_C_DAT_LTE_206_T_20180414.dmp 192.168.20.67:/home/oracle/.

#源库查询信息：表的用户名称，表所在的表空间

SQL> select owner,tablespace_name from dba_segments where segment_name='G_BIL_C_DAT_LTE_206_T_20180414';

OWNER TABLESPACE_NAME

--------------------------------------------------------------------------------

GAT TBSFSH_DATA21

=>目标端：导入并创建测试表，查询验证

#查询目标端测试用户yang的默认永久表空间 SQL> select username,DEFAULT_TABLESPACE from dba_users where username='YANG';USERNAME DEFAULT_TABLESPACE ------------------------------ ------------------------------ YANG USERS#导入源表 [oracle@bj ~]$ impdp yang/yang directory=yydumpfile=G_BIL_C_DAT_LTE_206_T_20180414.dmpremap_schema=GAT:yang remap_tablespace=TBSFSH_DATA21:users#查询源表有多少行数据SQL> select count(*) from G_BIL_C_DAT_LTE_206_T_20180414; COUNT(*)---------- 23998 #创建测试表： CREATE TABLE "YANG"."G_BIL_C_DATA_LTE_20180414"( "TICKET_ID" NUMBER(12,0) NOT NULL ENABLE,"FILE_ID" NUMBER(18,0) NOT NULL ENABLE,"BATCH_ID" NUMBER(12,0) NOT NULL ENABLE,"SWITCH_ID" NUMBER(9,0) NOT NULL ENABLE,"SERVICE_TYPE" VARCHAR2(15) NOT NULL ENABLE,"BILLING_REGION_ID" NUMBER(10,0),---此处省略107个列---) SEGMENT CREATION IMMEDIATEPCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 COMPRESS BASIC NOLOGGINGSTORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "USERS";

#查询测试表的约束名称：

SQL> select 'alter table '||table_name||' disable constraint '||CONSTRAINT_NAME||';' "Script" from user_constraints where table_name='G_BIL_C_DATA_LTE_20180414' and status='ENABLED';

Script

--------------------------------------------------------------------------------

alter table G_BIL_C_DATA_LTE_20180414 disable constraint SYS_C004285;

--此处省略剩余6条语句

#执行上述禁用约束SQL：

alter table G_BIL_C_DATA_LTE_20180414 disable constraint SYS_C004285;

--此处省略剩余6条语句

#再次查询验证：测试表是否还有约束启用

SQL> select 'alter table '||table_name||' disable constraint '||CONSTRAINT_NAME||';' "Script" from user_constraints where table_name='G_BIL_C_DATA_LTE_20180414' and status='ENABLED';

no rows selected

=>会话监控10046 level 1事件开启、自动追踪 autotrace开启

会话一、使用SYS用户登录：对会话二进行10046 trace level 1事件追踪

#yang用户查询当前会话的操作系统进程号 SQL> select spid from v$process a,v$session b where a.addr=b.paddr and
b.sid=(select sid from v$mystat where rownum=1); SPID ------------------------ 29062

#SYS用户进行监控
SQL> show user
USER is "SYS"
SQL> oradebug setospid 29062;
Oracle pid: 34, Unix process pid: 29062, image: oracle@bj (TNS V1-V3)
SQL> oradebug event 10046 trace name context forever,level 1;
Statement processed.

会话二、使用yang用户登录：开启set autotrace traceonly 开启自动追踪工具

#yang用户开启自动追踪开关 SQL> set autotrace traceonly;

=>insert操作执行：

yang用户：执行第一个insert 操作，对测试表中所有的数据进行插入

commit提交后，truncate 截断清空测试表

insert /*+ parallel(t,4) */ into yang.G_BIL_C_DATA_LTE_20180414 t (TICKET_ID,FILE_ID,BATCH_ID,SWITCH_ID,SELF_CELL_ID,SELF_MSC_ID###此处省略剩余表的所有列名称###,RATABLE_RESOURCE_LIST,FLUX_CARD_LIST) select trim(TICKET_ID),trim(FILE_ID ),trim(BATCH_ID ),trim(SWITCH_ID ),trim(SELF_CELL_ID ),trim(SELF_MSC_ID )###此处省略剩余表的所有列名称###,trim(FLUX_CARD_LIST) from yang.G_BIL_C_DAT_LTE_206_T_20180414 t;

AUTOTRACE工具信息记录：

Execution Plan ----------------------------------------------------------

-----------------------------------------------------------------------------------------------------------

Plan hash value: 2515889339| 0 | INSERT STATEMENT | | 24031 | 200M| 262 (2)| 00:00:04 | |
| 1 | LOAD TABLE CONVENTIONAL | G_BIL_C_DATA_LTE_20180414 | | | | | | |
| 2 | PARTITION LIST ALL | | 24031| 200M| 262 (2)| 00:00:04 | 1 | 2| 3 | TABLE ACCESS FULL | G_BIL_C_DAT_LTE_206_T_20180414 |24031 | 200M| 262 (2)| 00:00:04 | 1 | 2 |-------------------------------------------------------------------------------- Note ------ dynamic sampling used for this statement (level=2)Statistics ----------------------------------------------------------849 recursive calls30555 db block gets2057 consistent gets1 physical reads24014124 redo size838 bytes sent via SQL*Net to client8776 bytes received via SQL*Net from client3 SQL*Net roundtrips to/from client43 sorts (memory)0 sorts (disk)23998 rows processed

第一次操作：结束

SQL> commit;Commit complete.SQL> truncate table G_BIL_C_DATA_LTE_20180414;

yang用户：执行第二个insert操作，对测试表中的三个列数据进行插入

commit提交后

AUTOTRACE工具信息记录：

Execution Plan ---------------------------------------------------------- Plan hash value: 2515889339--------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |---------------------------------------------------------------------------------------------------------------------------| 0 | INSERT STATEMENT | | 24031 | 50M| 261 (1)| 00:00:04 | | || 1 | LOAD TABLE CONVENTIONAL | G_BIL_C_DATA_LTE_20180414 | | | | | | || 2 | PARTITION LIST ALL | | 24031 | 50M| 261 (1)| 00:00:04 | 1 | 2 || 3 | TABLE ACCESS FULL | G_BIL_C_DAT_LTE_206_T_20180414 | 24031 | 50M| 261 (1)| 00:00:04 | 1 | 2 |---------------------------------------------------------------------------------------------------------------------------Note ------ dynamic sampling used for this statement (level=2)Statistics ----------------------------------------------------------24 recursive calls25795 db block gets1081 consistent gets0 physical reads10095484 redo size841 bytes sent via SQL*Net to client1045 bytes received via SQL*Net from client3 SQL*Net roundtrips to/from client1 sorts (memory)0 sorts (disk)23998 rows processed

=>autotrace工具收集信息对比：

逻辑读、物理读、执行时间统计对比

Autotrace

逻辑读

consistent gets

物理读

physical reads

TIME

时-分-秒

Insert插入表的所有列（全表总计94个列）

2057

00:00:04

Insert插入表的四个列

1081

00:00:04

=>结束会话追踪，生成trace文件，10046事件收集信息对比：

SQL> oradebug event 10046 trace name context off; Statement processed. SQL> show parameter user_dump_destNAME TYPE VALUE ------------------------------------ ----------- ------------------------------ user_dump_dest string /picclife/app/oracle/diag/rdbms/aa/dingding/trace
[oracle@bj admin]$ cd /picclife/app/oracle/diag/rdbms/aa/dingding/trace
[oracle@bj trace]$ ls *29062.trc
dingding_ora_29062.trc

[oracle@bj trace]$ tkprof dingding_ora_29062.trc /home/oracle/select.trc sys=no

#trace文件中第一次Insert,全表所有列的插入操作：

insert /*+ parallel(t,4) */ into yang.G_BIL_C_DATA_LTE_20180414 t (TICKET_ID ,FILE_ID,BATCH_ID
****此处省略90个列名称 ,FLUX_CARD_LIST) select trim(TICKET_ID)
,trim(FILE_ID ),SELF_CELL_ID****此处省略90个列名称,trim(FLUX_CARD_LIST) from yang.G_BIL_C_DAT_LTE_206_T_20180414 tcall count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.02 0.02 0 3 0 0 Execute 1 8.64 15.29 1 1062 30554 23998 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 8.66 15.32 1 1065 30554 23998
#parse解析
#execute执行
#fetch获取
#elapsed 执行时间
#query 逻辑读
#disk 物理读
#current 当前读
#rows 处理行数

#解析期间：库缓存中未命中：硬解析1次 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 54 Number of plan statistics captured: 1Rows (1st) Rows (avg) Rows (max) Row Source Operation ---------- ---------- ---------- ---------------------------------------------------0 0 0 LOAD TABLE CONVENTIONAL (cr=1179 pr=1 pw=0 time=15305076 us)23998 23998 23998 PARTITION LIST ALL PARTITION: 1 2 (cr=957 pr=0 pw=0 time= 74766 us cost=262 size=209742568 card=24031)23998 23998 23998 TABLE ACCESS FULL G_BIL_C_DAT_LTE_206_T_20180414 PARTITI ON: 1 2 (cr=957 pr=0 pw=0 time=67557 us cost=262 size=209742568 card=24031)

#trace文件中第二次Insert,四个列的插入操作：

insert /*+ parallel(t,4) */ into yang.G_BIL_C_DATA_LTE_20180414 t (TICKET_ID, BSID, IPV6_PDSN_ADDRESS, RATABLE_RESOURCE_LIST) select trim(TICKET_ID), trim(BSID ), trim(IPV6_PDSN_ADDRESS ), trim(RATABLE_RESOURCE_LIST) from yang.G_BIL_C_DAT_LTE_206_T_20180414 tcall count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 2 0 0 Execute 1 0.28 0.31 0 1000 25794 23998 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.28 0.31 0 1002 25794 23998
#解析期间：库缓存中未命中：硬解析1次 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 54 Number of plan statistics captured: 1Rows (1st) Rows (avg) Rows (max) Row Source Operation ---------- ---------- ---------- ---------------------------------------------------0 0 0 LOAD TABLE CONVENTIONAL (cr=1006 pr=0 pw=0 time=315302 us )23998 23998 23998 PARTITION LIST ALL PARTITION: 1 2 (cr=957 pr=0 pw=0 time= 52444 us cost=261 size=53060448 card=24031)23998 23998 23998 TABLE ACCESS FULL G_BIL_C_DAT_LTE_206_T_20180414 PARTITI ON: 1 2 (cr=957 pr=0 pw=0 time=47368 us cost=261 size=53060448 card=24031)

逻辑读、物理读、执行时间统计对比

10046

工具level1

逻辑读

query

物理读

disk

TIME

Elapsed（单位微秒）

1s=1，000，000微秒

Insert插入表的所有列（全表总计94个列）

1065

15.32

Insert插入表的四个列

1002

0.31

Autotrace

逻辑读

consistent gets

物理读

physical reads

TIME

时-分-秒

Insert插入表的所有列（全表总计94个列）

2057

00:00:04

Insert插入表的四个列

1081

00:00:04

=>对比结果进行分析，得出相应结论

结论一、以上两个工具查询的统计信息对比：

Insert操作，插入的列数量越多，消耗的逻辑读就越多，代价也越大

结论二、autotrace工具显示的时间不准确，从逻辑读降低一半，但是执行时间根本没有减少

=>工具使用对比：autotrace || 10046

使用autotrace操作更加简便，但是只能当前用户执行，当前用户下查看

使用10046，操作复杂一点，但是可以查看监控其它用户下执行的sql，且执行时间维度更加精确，收集的统计信息更多。

转载于:https://www.cnblogs.com/lvcha001/p/8907247.html

总结

以上是生活随笔为你收集整理的Insert插入不同的列数量，统计信息对比的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。

上一篇： WWDC 2018： Shortcuts
下一篇： offset;scroll;client