Event 10046是为Oracle session收集扩展的sql_trace信息的标准方法,通常为了诊断SQL调优类问题,我们需要记录下这些语句在执行过程中产生的等待以及bind variables(绑定变量)的信。 这些信息可以通过级别为12的10046 trace获得。
在11g之前:
SQL> show parameter user_dump_dest
在11g之后:
SQL> show parameter diagnostic_dest
或者用视图 select * from v$diag_info;
注:下面的某些例子中会设定tracefile_identifier,通过这个设置可以帮助我们更容易的找到生成的trace文件
(2)在Session级打开trace
适用于SQL语句可以在新的session创建后再运行
alter session set tracefile_identifier='10046';
alter session set timed_statistics = true;
alter session set statistics_level=all;
alter session set max_dump_file_size = unlimited;
alter session set events '10046 trace name context forever,level 12';
-- 执行需要被trace的SQL --
select * from dual;
exit;
如果不退出当前session, 可以用以下命令关闭trace:
alter session set events '10046 trace name context off';
注意,如果session没有被彻底地关闭并且跟踪被停止了,某些重要的trace信息的可能会丢失。
(3)跟踪一个已经开始的进程
如果需要跟踪一个已经存在session,可以用 oradebug连接到session上,并发起10046 trace。
1、首先,用某种方法找到需要被跟踪的session
例如,在SQL*Plus里,找出目标session的OS的进程ID(spid):
select p.PID,p.SPID,s.SID
from v$process p,v$session s
where s.paddr = p.addr
and s.sid = &SESSION_ID
/
其中,
SPID 是操作系统的进程标识符(os pid)
PID 是Oracle的进程标识符(ora pid)
2、一旦找到OS PID,就可以用以下命令初始化跟踪:(此方法适用于session没有trace的权限)
使用SPID跟踪:
假设需要被跟踪的OSPID是9834. 以sysdba的身份登录到SQL*Plus并执行下面的命令:
connect / as sysdba
oradebug setospid 9834
oradebug unlimit
oradebug event 10046 trace name context forever,level 12
使用ORAPID跟踪:
使用PID(Oracle进程标识符)(而不是SPID), oradebug命令将被改为:
connect / as sysdba
oradebug setorapid 9834
oradebug unlimit
oradebug event 10046 trace name context forever,level 12
跟踪过程完成以后,关闭oradebug跟踪:
oradebug event 10046 trace name context off
(4)实例层的跟踪
注意: 在实例层设置跟踪需要非常小心,这是因为整体性能会由于所有session都被跟踪而受到影响。
这个设置将会跟踪在这个参数设置“以后”创建的每个session。已经存在的session不会被跟踪。 系统层的10046跟踪适用于当我们知道问题session会出现,但是不能预先识别它的时候。
在这种情况下,可以打开系统层跟踪一小段时间,当问题被重现以后立即将其关闭,然后从已经生成的trace中查找需要的信息。
用以下命令打开系统层的跟踪:
alter system set events '10046 trace name context forever,level 12';
用以下命令关闭在所有session中的10046跟踪:
alter system set events '10046 trace name context off';
(5)初始化参数设置
设置以下参数并重新启动实例后,实例上所有的session都会打开跟踪。
event="10046 trace name context forever,level 12"(在参数文件里面添加)
移除这个参数并且重启实例, 或者使用下面的alter system命令可以关闭跟踪。
alter system set events '10046 trace name context off';
(6)通过logon trigger设置跟踪
有的时候当需要跟踪某个特定用户的操作时,可以使用logon trigger来打开跟踪,下面是一个例子:
CREATE OR REPLACE TRIGGER SYS.set_trace
AFTER LOGON ON DATABASE
WHEN (USER like '&USERNAME')
DECLARE
lcommand varchar(200);
BEGIN
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 set_trace;
/
为了能打开跟踪session, 执行trigger的用户需要被显式地授予'alter session' 权限. 例如,
grant alter session to ;
我们在查看一条sql语句的执行计划时,只看到了CBO最终告诉我们的执行计划结果,但是却不知道CBO为什么要这么做。特别是当执行计划明显失真 时,我们一定非常想搞清楚到底是什么地方导致CBO作出了这样一个错误的执行计划。10053事件给我们提供了一种这样的方式,它允许我们深入到CBO的 内部,去看看CBO是如何工作的,它究竟是按照什么样的依据得出最终的执行计划的。10053 里面详细记录了表,列,索引等等详细的统计信息。
(1) 在session级别打开trace
alter session set events '10053 trace name context forever,level 1';
Level 2:2级是1级的一个子集,它包含以下内容:
Column statistics
Single Access Paths
Join Costs
Table Joins Considered
Join Methods Considered (NL/MS/HA)
Level 1: 1级比2级更详细,它包含2级的所有内容,在加如下内容:
Parameters used by the optimizer
Index statistics
(2) 跟踪一个已经开始的进程
Explain plan for sql_text;
oradebug event 10053 trace name context off
下面演示一个例子,该例子大概能说明在一个sql调优中,10046和10053的使用方法。
我有一张表,表名为tmp_1021,如图所示,sample_id这一列的所有值只有一个。
create index idx_1021_sample_id on tmp_1021 (sample_id)
(注意在10g后,在创建索引后oracle会自动帮你收集目标索引的统计信息)
表的统计信息如下:
该表有172行数据,索引只用到了一个块。(即一个块里面包含了所有的键值和rowid)
运行一下语句:SELECT * FROM TMP_1021 WHERE SAMPLE_ID = '39688199'
此时该语句的执行计划 为
![](https://i-blog.csdnimg.cn/blog_migrate/d772b0c1428e6b4c67b9be45ac4546fe.png)
之所以走全表扫描是因为这条语句如果走索引,则需要回表172次,开销大
不如直接走全表扫描
此时,修改下表的统计信息
- BEGIN
- dbms_stats.set_table_stats(ownname => 'MEPF_DEV',tabname => 'TMP_1021',numrows => '100000',numblks => '40000');
- END;
![](https://i-blog.csdnimg.cn/blog_migrate/2a5da602ca3c66a209f2c408020eb9e2.png)
![](https://i-blog.csdnimg.cn/blog_migrate/75043dcab6cca0c610c4addbb9702874.png)
此时索引的统计信息没有改变,但是表的统计信息已经发生了很大的变化,此时再运行如下语句:
SELECT * FROM TMP_1021 WHERE SAMPLE_ID = '39688199'
发现执行计划发生了如下的改变:
![](https://i-blog.csdnimg.cn/blog_migrate/8d0ef558056aab852ef803eaf3c5d2a9.png)
此时我们分别看下10046,它的真实的执行计划,也是如此:
SQL ID: 7hcnvq7ucv69d Plan Hash: 711258126
SELECT *
FROM
TMP_1021 WHERE SAMPLE_ID = '39688199'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 6 0 172
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 6 0 172
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
172 172 172 TABLE ACCESS BY INDEX ROWID TMP_1021 (cr=6 pr=0 pw=0 time=474 us cost=91 size=100000 card=1000)
172 172 172 INDEX RANGE SCAN IDX_1021_SAMPLE_ID (cr=2 pr=0 pw=0 time=202 us cost=1 size=0 card=172)(object id 78980)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 3 0.00 0.00
SQL*Net message from client 3 0.19 0.21
********************************************************************************
我们看下10053,看看为什么它会走index range scan,而不是table acess full
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: TMP_1021 Alias: TMP_1021
#Rows: 100000 #Blks: 40000 AvgRowLen: 100.00 ChainCnt: 0.00
Index Stats::
Index: IDX_1021_SAMPLE_ID Col#: 4
LVLS: 0 #LB: 1 #DK: 127 LB/K: 1.00 DB/K: 1.00 CLUF: 90.00
Access path analysis for TMP_1021
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for TMP_1021[TMP_1021]
Column (#4): SAMPLE_ID( NO STATISTICS (using defaults)
AvgLen: 13 NDV: 3125 Nulls: 0 Density: 0.000320
Table: TMP_1021 Alias: TMP_1021
Card: Original: 100000.000000 Rounded: 1000 Computed: 1000.00 Non Adjusted: 1000.00
Access Path: TableScan
Cost: 10843.84 Resp: 10843.84 Degree: 0
Cost_io: 10835.00 Cost_cpu: 312717600
Resp_io: 10835.00 Resp_cpu: 312717600
Access Path: index (AllEqGuess)
Index: IDX_1021_SAMPLE_ID
resc_io: 91.00 resc_cpu: 1038491
ix_sel: 1.000000 ix_sel_with_filters: 1.000000
Cost: 91.03 Resp: 91.03 Degree: 1
Best:: AccessPath: IndexRange
Index: IDX_1021_SAMPLE_ID
Cost: 91.03 Degree: 1 Resp: 91.03 Card: 1000.00 Bytes: 0
***************************************
可以看到统计信息里#Rows: 100000 #Blks: 40000,但是Index Stats::
Index: IDX_1021_SAMPLE_ID Col#: 4
LVLS: 0 #LB: 1 #DK: 127 LB/K: 1.00 DB/K: 1.00 CLUF: 90.00
索引还是原来的信息。Access path analysis for TMP_1021下面的内容,就是根据统计信息算出来的cost值,最后,优化器算出
Best:: AccessPath: IndexRange
Index: IDX_1021_SAMPLE_ID
Cost: 91.03 Degree: 1 Resp: 91.03 Card: 1000.00 Bytes: 0
最好的(best)执行方式为index range(IDX_1021_SAMPLE_ID)
以上小例子中,可以看出10046和10053以及统计信息在sql调优中的一些用法。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30109892/viewspace-1816327/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30109892/viewspace-1816327/