oracle调优之Trace方法及相关工具总结01
主要看elapsed处理时间（单位秒）。
参数说明：
call：每次SQL语句的处理都分成三个部分
- Parse：这步将SQL语句转换成执行计划，包括检查是否有正确的授权和所需要用到的表、列以及其他引用到的对象是否存在；
- Execute：这步是真正的由Oracle来执行语句，对于insert、update、delete操作，这步会修改数据，对于select操作，这步只是确定选择的记录；
- Fetch：返回查询语句中所获得的记录，这步只有select语句会被执行。
count:这个SQL语句被parse、execute、fetch的次数；
cpu:这个语句对于所有的parse、execute、fetch所消耗的cpu的时间，以秒为单位；
elapsed:这个语句所有消耗在parse、execut、fetch的总的时间；
disk：从磁盘上的数据文件中物理读取的块的数量。一般来说更想知道的是正在从缓存中读取的数据而不是从磁盘上读取的数据。
query：在一致性读模式下，所有parse、execute、fetch所获得的buffer的数量。一致性模式的buffer是用于给一个长时间运行的事务提供一个一致性读的快照，缓存实际上在头部存储了状态。
current：在current模式下所获得的buffer的数量。一般在current模式下执行insert、update、delete操作都会获取 buffer。在current模式下如果在高速缓存区发现有新的缓存足够给当前的事务使用，则这些buffer都会被读入了缓存区中。
rows: 所有SQL语句返回的记录数目，但是不包括子查询中返回的记录数目。对于select语句，返回记录是在fetch这步，对于insert、update、delete操作，返回记录则是在execute这步。
3、SQL执行效率分析
A、query+current/rows 平均每行所需的block数，太大的话（超过20）SQL语句效率太低
B、Parse_count/Execute_count parse count应尽量接近1，如果太高的话，SQL会进行不必要的reparse
C、Fetch_row/Fetch_count Fetch Array的大小，太小的话就没有充分利用批量Fetch的功能，增加了数据在客户端和服务器之间的往返次数。
D、disk/query+current 磁盘IO所占逻辑IO的比例，太大的话有可能是db_buffer_size过小(也跟SQL的具体特性有关)
E、elapsed/cpu 太大表示执行过程中花费了大量的时间等待某种资源
F、cpu Or elapsed 太大表示执行时间过长，或消耗了了大量的CPU时间，应该考虑优化
G、执行计划中的Rows表示在该处理阶段所访问的行数，要尽量减少
4、运行环境信息：
第一行表示发生在解析的硬解析数量，如果是软解析则Misses in library cache during parse将为0；
第二行表示优化模式是：ALL_ROWS (即CBO优化方式)
第三行是用户的ID，可以获取执行时的会话信息。获取用户信息可以通过如下SQL：
5、行源操作，查看COST值以及关联关系
参数：
cr=3 一致性读取，pr=0 物理读取，pw=0 物理写，time = 16 占用时间，单位：微秒
Trace文件除了关注SQL语句执行情况中的elapsed SQL总耗时间外，还需要关注的是该部分的数据。
A. 关注其中的cost值和表之间的关联情况。Cost值和执行计划中的cost值一样，值越大，说明耗时越长，这是需要注意有可能存在优化项的地方。
B. 另一点，就是看表关联之间是否存在TABLE ACCESS FULL全表扫描(下图是截取其他某trace文件)，全表扫描可视情况通过建立适当的索引来优化；在sql语句中，我们也可能使用了某些不必要的视图，这些视图对应的sql查询本身部分就是要进行全表扫描，这种情况下可考虑放弃视图，使用基表。
如下图：SQL中查询表：HR_ALL_ORGANIZATION_UNITS，在这里使用了全表扫描，如果实在很大SQL中，肯定会影响执行效率，所以我们对于全表扫描的的SQL，要进行优化。
我们看下这段代码的执行计划：
当参数为空时，会扫描全表，所用的花费为3
当参数不为空时，走的索引，所花费的时间为1
还有如果是因为SQL中使用了视图导致全表扫描，我们可以考虑使用基表。SQL中也尽量不要使用视图，可以节省很多COST。
6、执行计划
这里是SQL运行的实际执行计划，比我们用F5所得到的执行计划更加详细
7、等待事件（TOP5）
这些都是等待时间，涉及到底层的东西，但是对SQL性能优化有很大的帮助。等待时间太长，会浪费大部分资源。
我们也可以从另外的方面来了解这些东西：
SELECT EXECUTIONS,
DISK_READS,
BUFFER_GETS,
ROUND((BUFFER_GETS - DISK_READS)/BUFFER_GETS,2) HIT_RADIAO,
ROUND(DISK_READS/EXECUTIONS,2) READIO_PER_RUN,
SQL_TEXT
FROM V$SQLAREA
WHERE EXECUTIONS > 0
AND BUFFER_GETS > 0
AND SQL_TEXT LIKE ''
AND (BUFFER_GETS - DISK_READS) / BUFFER_GETS < 0.8;
这是一段查询共享池中已经解析过的SQL语句及其相关信息
其中字段意思为：
--EXECUTIONS 所有子游标的执行这条语句次数
--DISK_READS 所有子游标运行这条语句导致的读磁盘次数
--BUFFER_GETS 所有子游标运行这条语句导致的读内存次数
--Hit_radio 命中率
--Reads_per_run 每次执行读写磁盘数
笼统的说EXECUTIONS，BUFFER_GETS，Hit_radio越高表示读内存多，磁盘少是比较理想的状态，因此越高越好另外两个越高读磁盘次数越多，因此低点好。
而且一般比较好的SQL：(BUFFER_GETS-DISK_READS)/BUFFER_GETS这个比值一般是小于0.8的。
2.1.3. 视图换成基表实例
知道一个业务实体ID，要获取对应的业务实体名称。我们可以选择视图：
HR_OPERATING_UNITS,
或者使用基表：
HR_ALL_ORGANIZATION_UNITS,HR_ALL_ORGANIZATION_UNITS_TL
1、当我们使用视图时：
2、当我们使用基表时：
从上面的两个执行计划我们可以发现，我们其实只需要两个表，只会在一个表上花费时间，但是使用视图的话，我们要进行几张表表的扫描，而且在三张表上花费时间，并且应为多张表的笛卡尔（MERGE JOIN）关联，需要在值集进行排序，所以在内存排序（BUFFER SORT）上又会发给一定的时间。
所以我们在很大的SQL中要尽量避免使用视图，用基表来减少SQL执行所花费的时间。
3.
其他追踪事件
3.1.
10046（SQL_TRACE）
3.1.1. 使用方法
10046事件和SQL_TRACE作用一样，都是用来追踪SQL，通过生成的trace来了解SQL的执行过程。和普通的SQL_TRACE方法相比，10046事件提供了可选的追踪级别。
10046事件可选四个级别：level 1、4、8、12：
Level 0 停用SQL跟踪，相当于SQL_TRACE=FALSE
Level 1 标准SQL跟踪，相当于SQL_TRACE=TRUE
Level 4 在level 1的基础上增加绑定变量的信息
Level 8 在level 1的基础上增加等待事件的信息
Level 12 在level 1的基础上增加绑定变量和等待事件的信息
10046事件不但可以跟踪用户会话(trace文件位于USER_DUMP_DEST)，也可以跟踪background进程(trace文件位于BACKGROUND_DUMP_DEST)。
根据追踪的方法不同，最后生成的trace文件的大小也不同个，起决定于4个因素：
跟踪级别，跟踪时长，会话的活动级别和MAX_DUMP_FILE_SIZE参数设置。
1、数据基础参数设置：
alter session set tracefile_identifier='10046';
设置追踪标识符，标识符可随意指定，本例中生成的trace文件名中会包含‘10046’字样，方便查找；
alter session set timed_statistics=true;
设置为true，否则不会有CPU时间信息；
alter session set max_dump_file_size ='UNLIMITED';
Trace文件的最大尺寸(单位为操作系统块),UMLIMITED表示没有限制
2、开启、关闭追踪
alter session set events '10046 trace name context forever, level 12';
alter session set events '10046 trace name context off';
3、获取Trace文件
SELECT * FROM v$parameter t WHERE t.NAME = 'user_dump_dest';
3.1.2. 其他使用法
通过使用配置文件的方式，可以在接口程序中使用10046事件开启trace，也可以在请求，form个性化等方式，只要能执行代码，就都可以使用该事件去进行追踪，看个人选择。此处仅为接口中使用10046事件做代码注释
g_trace VARCHAR2(1) := nvl(fnd_profile.value('CUX_O2E_OM_WS_TRACE_FLAG'), 'N');
IF g_trace = 'Y' THEN
EXECUTE IMMEDIATE 'ALTER session SET tracefile_identifier = CRMORDER_' || p_transaction_id || ' ';
EXECUTE IMMEDIATE 'ALTER session SET timed_statistics = TRUE ';
EXECUTE IMMEDIATE 'ALTER session SET statistics_level = ALL ';
EXECUTE IMMEDIATE 'ALTER session SET max_dump_file_size = unlimited ';
EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever,level 12'' ';
END IF;
oracle调优之Trace方法及相关工具总结02
