一、实验目的：
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执行结果、执行计划、统计信息的查看
#授予表空间存储无限制权限
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=yy
dumpfile=G_BIL_C_DAT_LTE_206_T_20180414.dmp
remap_schema=GAT:yang remap_tablespace=TBSFSH_DATA21:users
#查询源表有多少行数据
SQL> select count(*) from G_BIL_C_DAT_LTE_206_T_20180414;
COUNT(*)
----------
#创建测试表：
CREATE TABLE "YANG"."G_BIL_C_DATA_LTE_20180414"
( ,) NOT NULL ENABLE,
,) NOT NULL ENABLE,
,) NOT NULL ENABLE,
,) NOT NULL ENABLE,
) NOT NULL ENABLE,
,),
---此处省略107个列---
) SEGMENT CREATION IMMEDIATE
PCTFREE PCTUSED INITRANS MAXTRANS COMPRESS BASIC NOLOGGING
STORAGE(INITIAL NEXT MINEXTENTS MAXEXTENTS
PCTINCREASE FREELISTS FREELIST GROUPS
BUFFER_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 userUSER 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 ----------------------------------------------------------
| Id | Operation | Name | Rows | Byte s | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------
Plan hash value: | | INSERT STATEMENT | | | 200M| ()| :: | |
| | LOAD TABLE CONVENTIONAL | G_BIL_C_DATA_LTE_20180414 | | | | | | |
| | PARTITION LIST ALL | | | 200M| ()| :: | |
| | TABLE ACCESS FULL | G_BIL_C_DAT_LTE_206_T_20180414 | | 200M| ()| :: | | |
--------------------------------------------------------------------------------
Note
-----
- )
Statistics
----------------------------------------------------------
recursive calls
db block gets
consistent gets
physical reads
redo size
bytes sent via SQL*Net to client
bytes received via SQL*Net from client
SQL*Net roundtrips to/from client
sorts (memory)
sorts (disk)
rows processed
第一次操作：结束
SQL> commit; Commit complete. SQL> truncate table G_BIL_C_DATA_LTE_20180414;
yang用户：执行第二个insert操作，对测试表中的三个列数据进行插入
commit提交后
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 t;
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 calls
25795 db block gets
1081 consistent gets
0 physical reads
10095484 redo size
841 bytes sent via SQL*Net to client
1045 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
23998 rows processed
=>autotrace工具收集信息对比：
逻辑读、物理读、执行时间统计对比
|
Autotrace |
逻辑读 consistent gets |
物理读 physical reads |
TIME 时-分-秒 |
|
Insert插入表的所有列（全表总计94个列） |
2057 |
1 |
00:00:04 |
|
Insert插入表的四个列 |
1081 |
0 |
00:00:04 |
=>结束会话追踪，生成trace文件，10046事件收集信息对比：
SQL> oradebug trace name context off; Statement processed. SQL> show parameter user_dump_dest NAME 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.trcdingding_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 t
call 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: 1
Rows (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 t
call 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: 1
Rows (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 |
1 |
15.32 |
|
Insert插入表的四个列 |
1002 |
0 |
0.31 |
|
Autotrace |
逻辑读 consistent gets |
物理读 physical reads |
TIME 时-分-秒 |
|
Insert插入表的所有列（全表总计94个列） |
2057 |
1 |
00:00:04 |
|
Insert插入表的四个列 |
1081 |
0 |
00:00:04 |
=>对比结果进行分析，得出相应结论
结论一、以上两个工具查询的统计信息对比：
Insert操作，插入的列数量越多，消耗的逻辑读就越多，代价也越大
结论二、autotrace工具显示的时间不准确，从逻辑读降低一半，但是执行时间根本没有减少
=>工具使用对比：autotrace || 10046
使用autotrace操作更加简便，但是只能当前用户执行，当前用户下查看
使用10046，操作复杂一点，但是可以查看监控其它用户下执行的sql，且执行时间维度更加精确，收集的统计信息更多。