调优实践04–利用FORCE_MATCHING_SIGNATURE捕获非绑定变量SQL语句

Posted on 2014-06-15 by royalwzy

利用FORCE_MATCHING_SIGNATURE捕获非绑定变量SQL语句
1.10g之后的v$sql视图增加了两列:
1.EXACT_MATCHING_SIGNATURE:Signature calculated on the normalized SQL text. The normalization includes the removal of white space and the uppercasing of all non-literal strings.
2.FORCE_MATCHING_SIGNATURE:Signature used when the CURSOR_SHARING parameter is set to FORCE,也就是SQL语句在CURSOR_SHARING=FORCE模式下运行的签名值;
2.控制共享游标的参数:CURSOR_SHARING
1.FORCE:在不改变语义的情况下都使用游标共享,可能使用的并不是最优的执行计划,强制绑定变量,并在左边的列进行to_char转换;
2.EXACT:默认值,只有在SQL语句完全一致的情况下才共享游标;
3.SIMILAR:在不改变语义或者不降低性能的情况下,尽量采用游标共享,一般用于第三方的系统,无法修改源码的情况;
3.执行计划相关内容:
1.v$sql_plan:在Shared Pool中的Library Cache中保存的实际使用的执行计划;
2.查看实际的执行计划:SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(‘sql_id’));
4.在CURSOR_SHARING=EXACT模式下查看使用字面值的sql语句;
4.1创建测试表;
CREATE TABLE tb_sqltuning AS SELECT * FROM dba_objects;
ALTER TABLE TB_SQLTUNING ADD CONSTRAINT PK_sqltuning_sqlid PRIMARY KEY (OBJECT_ID);
4.2清除shared pool内容;
ALTER SYSTEM FLUSH SHARED_POOL;
4.3执行测试sql;
SELECT /* SQL_TUNING_LITERAL */ * FROM tb_sqltuning WHERE object_id = 1;
SELECT /* SQL_TUNING_LITERAL */ * FROM tb_sqltuning WHERE object_id = 2;
SELECT /* SQL_TUNING_LITERAL */ * FROM tb_sqltuning WHERE object_id = 3;
SELECT /* SQL_TUNING_LITERAL */ * FROM tb_sqltuning WHERE object_id = 4;
SELECT /* SQL_TUNING_LITERAL */ * FROM tb_sqltuning WHERE object_id = 5;
SELECT /* SQL_TUNING_LITERAL */ * FROM tb_sqltuning WHERE object_id = 6;
4.4查看sql的信息;
SELECT sql_text,
sql_id,
address,
hash_value,
plan_hash_value,
is_shareable,
executions,
parse_calls,
child_address,
child_number,
force_matching_signature,
exact_matching_signature
FROM v$sql
WHERE sql_text LIKE ‘%SQL_TUNING_LITERAL%’ AND
sql_text NOT LIKE ‘%LIKE%’;
4.5结论;
每一条sql都会进行硬结析并产生不同的执行计划,它们的exact_matching_signature不同,但是force_matching_signature相同;(plan_hash_value相同更说明了,不同的sql产生的执行计划是相同的,需要优化)
5.在CURSOR_SHARING=EXACT模式下查看使用绑定变量的sql语句;
5.1清除shared pool内容;
ALTER SYSTEM FLUSH SHARED_POOL;
5.2执行测试sql;
VARIABLE id NUMBER;
EXEC :id := 1;
SELECT /* SQL_TUNING_BIND */ * FROM tb_sqltuning WHERE object_id = :id;
EXEC :id := 2;
SELECT /* SQL_TUNING_BIND */ * FROM tb_sqltuning WHERE object_id = :id;
EXEC :id := 3;
SELECT /* SQL_TUNING_BIND */ * FROM tb_sqltuning WHERE object_id = :id;
EXEC :id := 4;
SELECT /* SQL_TUNING_BIND */ * FROM tb_sqltuning WHERE object_id = :id;
EXEC :id := 5;
SELECT /* SQL_TUNING_BIND */ * FROM tb_sqltuning WHERE object_id = :id;
EXEC :id := 6;
SELECT /* SQL_TUNING_BIND */ * FROM tb_sqltuning WHERE object_id = :id;
5.3查看sql信息;
SELECT sql_text,
sql_id,
address,
hash_value,
plan_hash_value,
is_shareable,
executions,
parse_calls,
child_address,
child_number,
force_matching_signature,
exact_matching_signature
FROM v$sql
WHERE sql_text LIKE ‘%SQL_TUNING_BIND%’ AND
sql_text NOT LIKE ‘%LIKE%’;
5.4结论;
只进行一次硬结析,之后的语句使用相同的执行计划,它们的exact_matching_signature和force_matching_signature都相同;
硬结析了三次是应为11g里面SPM(Sql Plan Management)的特性,它可以自动学习并选择最优的执行计划,最大的优点是通过避免执行计划的退化,从而保证系统性能的稳定;10g的话第一次产生执行计划就固定了,很可能不是最优的执行计划;
1.第一次执行SQL的时候跟原来一样,什么也不做,只是把执行计划保存在Library Cache中;
2.第二次执行SQL的时候会产生Plan History,而且Plan Baseline为空,把这次的执行计划放到Plan Baseline中,并且固定执行计划;
3.第三次执行SQL的时候,会把产生的执行计划放入到Plan History中,但是不会进入Plan Baseline;
6.修改参数,在CURSOR_SHARING=SIMILAR模式下查看使用字面值的sql语句;
6.1修改参数;
ALTER SYSTEM SET cursor_sharing = SIMILAR
6.2清除shared pool内容;
ALTER SYSTEM FLUSH SHARED_POOL;
6.3执行测试sql;
SELECT /* SQL_TUNING_LITERAL */ * FROM tb_sqltuning WHERE object_id = 1;
SELECT /* SQL_TUNING_LITERAL */ * FROM tb_sqltuning WHERE object_id = 2;
SELECT /* SQL_TUNING_LITERAL */ * FROM tb_sqltuning WHERE object_id = 3;
SELECT /* SQL_TUNING_LITERAL */ * FROM tb_sqltuning WHERE object_id = 4;
SELECT /* SQL_TUNING_LITERAL */ * FROM tb_sqltuning WHERE object_id = 5;
SELECT /* SQL_TUNING_LITERAL */ * FROM tb_sqltuning WHERE object_id = 6;
6.4查看sql的信息;
SELECT sql_text,
sql_id,
address,
hash_value,
plan_hash_value,
is_shareable,
executions,
parse_calls,
child_address,
child_number,
force_matching_signature,
exact_matching_signature
FROM v$sql
WHERE sql_text LIKE ‘%SQL_TUNING_LITERAL%’ AND
sql_text NOT LIKE ‘%LIKE%’;
6.5结论;
跟在EXACT模式下使用绑定变量的结果一致:每一条sql都会进行硬结析并产生不同的执行计划,它们的exact_matching_signature不同,但是force_matching_signature相同;
7.How to Find Literal SQL in Shared Pool;
DECLARE
b_myadr VARCHAR2(20);
b_myadr1 VARCHAR2(20);
qstring VARCHAR2(100);
b_anybind NUMBER;

CURSOR my_statement IS
SELECT address FROM v$sql GROUP BY address;
CURSOR getsqlcode IS
SELECT substr(sql_text, 1, 60) FROM v$sql WHERE address = b_myadr;
CURSOR kglcur IS
SELECT kglhdadr
FROM x$kglcursor
WHERE kglhdpar = b_myadr AND
kglhdpar != kglhdadr AND
kglobt09 = 0;
CURSOR isthisliteral IS
SELECT kkscbndt FROM x$kksbv WHERE kglhdadr = b_myadr1;
BEGIN
dbms_output.enable(10000000);
OPEN my_statement;
LOOP
FETCH my_statement
INTO b_myadr;
OPEN kglcur;
FETCH kglcur
INTO b_myadr1;
IF kglcur%FOUND THEN
OPEN isthisliteral;
FETCH isthisliteral
INTO b_anybind;
IF isthisliteral%NOTFOUND THEN
OPEN getsqlcode;
FETCH getsqlcode
INTO qstring;
dbms_output.put_line(‘Literal:’ || qstring || ‘ address: ‘ || b_myadr);
CLOSE getsqlcode;
END IF;
CLOSE isthisliteral;
END IF;
CLOSE kglcur;
EXIT WHEN my_statement%NOTFOUND;
END LOOP;
CLOSE my_statement;
END;

调优实践03–使用SQL_TRACE和10046事件跟踪SQL的执行

Posted on 2014-06-15 by royalwzy

使用SQL_TRACE和10046事件跟踪SQL的执行

当在数据库中启动SQL_TRACE或者设置10046事件之后,Oracle将会启动内核跟踪程序,持续记录会话的相关信息,并写入到相应的trace文件中;跟踪记录的内容包括SQL的解析过程,执行计划,绑定变量的使用和会话中发生的等待事件等;
SQL_TRACE和10046事件介绍:
1. SQL_TRACE:
  1. 是一个静态的初始化参数,可以设置为TRUE/FALSE,用于开启/关闭SQL TRACE工具默认为FALSE;
  2. 设置SQL_TRACE=TRUE的话可以收集信息用于性能优化(DBMS_SYSTEM包可以实现相同的功能),但是对数据库会产生严重的性能问题,生产环境一定不要打开此参数,如果一定要在全局打开,要做到以下几点:
    1. 保证25%的CPU idle;
    2. 为USER_DUMP_DEST分配足够的空间:ALTER SYSTEM SET max_dump_file_size=UNLIMITED;
    3. 条带化磁盘,减轻IO负担;
    4. 设置timed_statistics打开:ALTER SYSTEM SET timed_statistics=TRUE;
  3. 打开SQL_TRACE功能:
    1. 全局打开:ALTER SYSTEM SET SQL_TRACE=TRUE SCOPE=SPFILE;重启数据库服务,会跟踪所有进程的活动,包括用户进程和后台进程;可以通过跟踪文件的实时变化来分析各个进程之间的协作关系;
    2. Session级别打开:ALTER SESSION SET SQL_TRACE=TRUE/FALSE;通过跟踪当前进程,来发现后台数据库的递归活动,用于研究SQL执行和发现后台错误等;
    3. 如果要跟踪其它用户的进程,可以通过DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION来完成,所需要的sid和serial#参数可以通过v$session视图查看得到;
    4. 如果要针对其它用户的参数进行设置,可以通过DBMS_SYSTEM.SET_INI_PARAM_IN_SESSION过程或者DBMS_SYSTEM.SET_BOOL_PARAM_IN_SESSION过程来完成;
  4. 其它方式:EXEC DBMS_SESSION.SET_SQL_TRACE(sql_trace=>TRUE/FALSE);
2. 10046事件:
  1. 是Oracle提供的内部事件,是对SQL_TRACE的增强;
  2. 10046分为四个级别:
    1. level 1:启用标准的sql_trace功能,等于sql_trace(包含了SQL语句,响应事件,服务时间,处理的行数,物理读和写的数目,执行计划以及其它一些额外信息);
    2. level 4:level 1加上绑定值;
    3. level 8:level 1加上等待事件;
    4. level 12:level 1 + level 4 + level 8;
  3. 设置10046事件:
    1. 全局开启:在spfile中添加events=”10046 trace name context forever, level 12″:ALTER SYSTEM SET EVENTS ‘10046 trace name context forever, level 12’;
    2. 全局关闭:ALTER SYSTEM SET EVENTS ‘10046 trace name context off’;
    3. 针对当前session的开启:ALTER SESSION SET EVENTS ‘10046 trace name context forever, level 12’;
    4. 针对当前session的关闭:ALTER SESSION SET EVENTS ‘10046 trace name context off’;
    5. 针对其它用户session的开启:使用DBMS_SYSTEM.SET_EV(si in integer, se in integer, ev in integer, le in integer, nm in varchar):EXEC DBMS_SYSTEM.SET_EV(38,25,10046,12,’HR’);
      1. si:sid;
      2. se:serial#;
      3. ev:event number;
      4. le:level;
      5. nm:username;
      6. 可以通过v$session视图查询:SELECT sid, serial#, username FROM v$session WHERE username IS NOT NULL;
    6. 针对其它用户session的关闭:EXEC DBMS_SYSTEM.SET_EV(38,25,10046,0,’HR’);
3. 也可以使用oradebug工具或者DBMS_MONITOR包;
4. 获取跟踪文件的脚本;
5. 获取当前session设置的参数的脚本;
实例分析步骤:
1. 隐式转换与索引失效:
  1. 问题描述:反应前端程序某个功能非常慢;
  2. 首先检查并跟踪数据库进程:SELECT sid, serial#, username FROM v$session WHERE username IS NOT NULL AND username <> ‘SYS’;
  3. 然后对这几个进程开启sql trace:EXEC dbms_system.set_sql_trace_in_session(sid, serial#, TRUE);等待一段时间后关闭:EXEC dbms_system.set_sql_trace_in_session(sid, serial#, FALSE);
  4. 针对产生的trace文件使用tkprof工具进行格式化,然后查看内容;
  5. 一般发生索引失效或者是隐式转换的话就会发现返回少量的数据但是却产生特别多的物理读(也有可能是汇总操作);
  6. 可以查看关键列的索引和索引列的类型:SELECT index_name, table_name, column_name FROM user_ind_columns WHERE table_name = upper(‘tb_name’);DESC tb_name;
  7. 然后可以针对发现的问题做相应的处理,比如创建索引,手动处理隐式转换等问题;
2. 对数据库进行操作时(如drop table/user)发生错误:
  1. 问题描述:对数据库操作后发生了ORA错误;
  2. 首先在当前的会话开启一个标识符:ALTER SESSION SET TRACEFILE_IDENTIFIER='<TRACEFILE_IDENTIFIER>’;
  3. 然后打开sql trace功能:ALTER SESSION SET SQL_TRACE=TRUE;
  4. 重现错误,即再执行当前操作;
  5. 关闭sql trace功能;
  6. 然后查看trace文件,找到具体的错误并解决;
10046查看等待事件的例子;
db_file_multiblock_read_count:
1. 表示全表扫描时,ORACLE一次I/O可以读取的数据库的数据块数,Oracle的一次I/O操作不能跨extent;
2. 最大值为((max OS I/O size)/db_block_size),一般操作系统一次I/O最大读取1M,db_block_size=8k,所以这个参数最大为128;
3. 这个参数也会受到SSTIOMAX的参数影响,这是一个内核参数,不能被修改,同时db_file_multiblock_read_count也不能超过db_block_buffer/4;
4. 在OLTP系统中一般设置为4-16,在DSS系统中可以根据需要设置更大的值;
5. 增大db_file_multiblock_read_count参数会使全表扫面的成本降低,但是在CBO下,Oracle会更倾向于使用全表扫面而不是索引扫描,db_file_multiblock_read_count与执行计划选择的例子;
为什么要使用10046事件:
1. 10046事件可以帮助我们解析SQL语句的运行状态(包括Parse/Fetch/Execute三个阶段中遇到的等待事件,消耗的物理和逻辑读,CPU时间,执行计划等等);
2. 即10046为我们揭示了SQL语句的运行情况,对于以点入手的SQL调优是很好的辅助工具,特别是在10g之前没有ASH的情况下;但整体系统调优不是10046所擅长的,需要用到AWR;
3. 10046还能帮助我们分析一些DDL维护命令的内部工作原理,比如RMAN,expdp/impdp等工具的缓慢问题等;
10046事件和10053事件的区别:
1. 10053事件是最常用的Oracle优化器optimizer跟踪trace,10053可以作为我们解释优化器为什么选择某个执行计划,但并不告诉我们这个执行计划到底运行地如何;
2. 10046并不解释optimizer优化器的工作,但它同样说明了在SQL解析parse阶段所遇到的等待事件和所消耗的CPU等资源,以及Execute执行和Fetch阶段的各项指标;
3. 简而言之10046告诉我们SQL(执行计划)运行地如何,10053告诉我们优化器为什么为这个SQL选择某个执行计划;
10046跟踪文件的阅读;

———————————– 获取跟踪文件的脚本 ———————————–

— 1.解析阶段;

PARSING IN CURSOR #11328540 len=56 dep=0 uid=84 oct=3 lid=84 tim=1396508012619901 hv=3963517665 ad=’4464298c’ sqlid=’25vmrurq3wyr1′
SELECT COUNT(*) FROM HR.EMPLOYEES WHERE DEPARTMENT_ID=20
END OF STMT
PARSE #11328540:c=6999,e=6974,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=2271004725,tim=1396508012619893

PARSING

1.#11328540:表示游标号,非常重要,后面的PARSE,EXEC,WAIT,FETCH,STAT,CLOSE阶段都需要使用这个游标号和前面的sql关联起来;

2.这个过程之后可能会产生很多的递归sql,一般是用来查询一些数据字典的,所消耗的资源和时间都非常的少;

3.len=56:表示sql的长度;

4.dep=0:表示Recursive Depth,即sql递归的深度;如果为0表示不是递归sql,如果大于0表示递归sql;

5.uid=84:表示解析这个游标的用户的UID,如果是0表示是sys;通过dba_users/user$查看;

6.oct=3:表示Oracle Command Type,即Oracle中的命令类型,与v$sql中的command_type列对应,可以通过查询v$sqlcommand视图查看具体的定义;

7.lid=84:表示Privilege User Id,即权限用户ID;

8.tim:表示timestamp时间戳,9i之后单位是ms,用来判断trace中两个点的时间差;来自v$timer视图,一个Oracle的内部时钟;

9.hv:表示sql的hash value,10g之前没有sqlid就使用hash value来定位一个sql;

10.ad:表示sqltext的地址,来自于v$sql的address列;

11.sqlid:表示对应的sql id;

12.err:如果有错误的话,代表错误代码,可以通过oerr ora xxx;

PARSE:是sql运行的第一个阶段,解析SQL语句;

1.c:表示cpu time,即消耗cpu的时间,9i之后单位是ms;

2.e:表示elapsed time,即消耗的自然时间,9i之后单位是ms;

3.p:表示physcial read,即物理的数目;

4.cr:表示consist read,即一致性读引起的buffer get数目;

5.cu:表示current read,即当前读取引起的buffer get数目;

6.mis:表示读取library cache的miss的数目,如果=0的话表示使用软解析或者更好的方式;如果大于0表示发生了硬结析;

7.r:表示rows,即处理的行数;

8.dep:表示Recursive Depth,即sql递归的深度;如果为0表示不是递归sql,如果大于0表示递归sql;

9.og:表示optimizer_mode,对应关系为:0-游标不可见/优化器环境未合理创建;1-ALL_ROWS;2-FIRST_ROWS;3-RULE;4-CHOOSE;

EXEC:sql运行的第二个阶段,执行sql语句;

FETCH:从游标中fetch数据行;

UNMAP:当游标使用临时表时,若游标关闭则使用UNMAP释放临时表相关的资源;

— 2.执行阶段;
EXEC #11328540:c=0,e=102,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2271004725,tim=1396508012620426

— 3.相关的等待;

WAIT #11328540: nam=’db file sequential read’ ela= 25707 file#=5 block#=243 blocks=1 obj#=76349 tim=1396508012647378

1.Nam:等待针对的事件名字,它的P1/P2/P3可以参考视图V$EVENT_NAME,也可以从V$SESSION/ASH中观察到等待事件;
2.ela:本操作的耗时,单位是ms;
3.p1,p2,p3:针对该事件的三个描述参数,见V$EVENT_NAME;

4.obj#:相关的对象id;

— 4.获取数据;

FETCH #11328540:c=4000,e=27513,p=1,cr=1,cu=0,mis=0,r=1,dep=0,og=1,plh=2271004725,tim=1396508012648208

— 5.相关的统计;

STAT #11328540 id=1 cnt=1 pid=0 pos=1 obj=0 op=’SORT AGGREGATE (cr=1 pr=1 pw=0 time=27520 us)’
STAT #11328540 id=2 cnt=2 pid=1 pos=1 obj=76349 op=’INDEX RANGE SCAN EMP_DEPARTMENT_IX (cr=1 pr=1 pw=0 time=27485 us cost=1 size=6 car
d=2)’

STAT:相关行反应解释执行计划的统计信息
1.id:执行计划的行数,从1开始;
2.cnt:该数据源的行数;
3.pid:该数据源的父ID;
4.pos:在执行计划中的位置;
5.obj:对应数据源的object id;
6.op:数据源的访问操作,例如FULL TABLE SCAN;

7.cr:代表一致性读的数量
8.pr:代表物理读的数量
9.pw:代表物理写的数量
10.time:单位为ms,本步骤的耗时间;
11.cost:本操作的优化器成本;
12.size:评估的数据源大小,单位为字节;
13.card:评估的优化器基数Cardinality;

— 6.关闭游标;

CLOSE #11328540:c=0,e=21,dep=0,type=0,tim=1396508016737280

CLOSE:关闭游标;

type:关闭游标的操作类型;

0-该游标从未被缓存且执行次数小于3次,也叫hard close;

1-该游标从未被缓存但执行次数至少3次,若在session cached cursor中有free slot,则将该游标放入session cached cursor;

2-该游标从未被缓存但执行次数至少3次,该游标置入session cached cursor的条件是讲老的缓存age out掉;

3-该游标已经在缓存里;

— 7.其它,如果有绑定变量的话;

BINDS:
1.kkscoacd:是绑定变量相关的描述符;
2.Bind#0:说明是第0个变量;
3.oacdty:data type,96是ANSI fixed char;
4.oacflg:代表绑定选项的特殊标志位;
5.size:为该内存chunk分配的内存大小;
6.mxl:绑定变量的最大长度;
7.pre:precision;
8.scl:Scale;
9.kxsbbbfp:buffer point;
10.bln:bind buffer length;
11.avl:实际的值的长度;
12.flg:代表绑定状态;
13.value:实际的绑定值;

———————————– 获取跟踪文件的脚本 ———————————–

SELECT a.VALUE || b.symbol || c.instance_name || ‘_ora_’ || d.spid || ‘.trc’ trace_file
FROM (SELECT VALUE FROM v$parameter WHERE NAME = ‘user_dump_dest’) a,
(SELECT substr(VALUE, -6, 1) symbol FROM v$parameter WHERE NAME = ‘user_dump_dest’) b,
(SELECT instance_name FROM v$instance) c,
(SELECT spid
FROM v$session s, v$process p, v$mystat m
WHERE s.paddr = p.addr AND
s.sid = m.sid AND
m.statistic# = 0) d;

11g later:SELECT VALUE FROM v$diag_info WHERE NAME = ‘Default Trace File’;

———————————– 获取跟踪文件的脚本 ———————————–

———————————– 获取当前session设置的参数的脚本 ———————————–

DECLARE
event_level NUMBER;
BEGIN
FOR event_number IN 10000 .. 10999 LOOP
sys.dbms_system.read_ev(event_number, event_level);
IF (event_level > 0) THEN
sys.dbms_output.put_line(‘Event ‘ || to_char(event_number) || ‘ is set at level ‘ || to_char(event_level));
END IF;
END LOOP;
END;

———————————– 获取当前session设置的参数的脚本 ———————————–

———————————– 10046查看等待事件的例子 ———————————–

— 1.查看系统参数;

show parameter db_block_size; — 8192;

show parameter db_file_multiblock_read_count; — 128;

SELECT tablespace_name, block_size, initial_extent / block_size, next_extent / block_size
FROM dba_tablespaces
WHERE tablespace_name IN (‘SYSTEM’, ‘TBS32B’);

SYSTEM 8192 8

TBS32B 8192 32 32

— 2.创建一个测试表;

sqlplus / as sysdba

CREATE TABLE TB10046 AS SELECT * FROM dba_objects;

SELECT file_id, block_id, blocks FROM dba_extents WHERE segment_name = ‘TB10046’;

   FILE_ID   BLOCK_ID     BLOCKS
———- ———- ———-
1      93784          8
1      93792          8
1      93800          8
1      93808          8
1      93816          8
1     104832          8
1     104840          8
1     104848          8
1     104856          8
1     104864          8
1     104872          8

FILE_ID   BLOCK_ID     BLOCKS
———- ———- ———-
1     104880          8
1     104888          8
1     104896          8
1     104904          8
1     104912          8
1     104960        128
1     105088        128
1     105216        128
1     105344        128
1     105472        128
1     105600        128

FILE_ID   BLOCK_ID     BLOCKS
———- ———- ———-
1     105728        128
1     105856        128

24 rows selected.

— 3.生成trace文件;

ALTER SESSION SET TRACEFILE_IDENTIFIER=’t1′;

ALTER SESSION SET EVENTS ‘10046 trace name context forever, level 12’;

SELECT event, total_waits FROM V$system_EVENT WHERE EVENT = ‘db file scattered read’; — 146913315

SELECT COUNT(*) FROM TB10046; — 发生全表扫描;

SELECT event, total_waits FROM V$system_EVENT WHERE EVENT = ‘db file scattered read’; — 146913412

— 4.查看trace文件,发现一次读取8个blocks,因为扫描不能跨extent,此时一个extent中是8个块;(trace中的file#,block#和blocks是与等待事件中参数一一对应的:SELECT NAME, parameter1, parameter2, parameter3 FROM v$event_name WHERE NAME = ‘db file scattered read’;)

WAIT #1: nam=’db file scattered read’ ela= 82 file#=1 block#=104961 blocks=8 obj#=95923 tim=1389859838564653
WAIT #1: nam=’db file scattered read’ ela= 66 file#=1 block#=104986 blocks=8 obj#=95923 tim=1389859838565112
WAIT #1: nam=’db file scattered read’ ela= 64 file#=1 block#=105009 blocks=8 obj#=95923 tim=1389859838565556
WAIT #1: nam=’db file scattered read’ ela= 65 file#=1 block#=105017 blocks=8 obj#=95923 tim=1389859838565951
WAIT #1: nam=’db file scattered read’ ela= 66 file#=1 block#=105059 blocks=8 obj#=95923 tim=1389859838566301
WAIT #1: nam=’db file scattered read’ ela= 77 file#=1 block#=105070 blocks=8 obj#=95923 tim=1389859838566646
WAIT #1: nam=’db file scattered read’ ela= 62 file#=1 block#=105106 blocks=8 obj#=95923 tim=1389859838567210
WAIT #1: nam=’db file scattered read’ ela= 65 file#=1 block#=105118 blocks=8 obj#=95923 tim=1389859838567565
WAIT #1: nam=’db file scattered read’ ela= 71 file#=1 block#=105163 blocks=8 obj#=95923 tim=1389859838567961
WAIT #1: nam=’db file scattered read’ ela= 65 file#=1 block#=105185 blocks=8 obj#=95923 tim=1389859838568283
WAIT #1: nam=’db file scattered read’ ela= 65 file#=1 block#=105200 blocks=8 obj#=95923 tim=1389859838568635
WAIT #1: nam=’db file scattered read’ ela= 68 file#=1 block#=105226 blocks=8 obj#=95923 tim=1389859838568997
WAIT #1: nam=’db file scattered read’ ela= 65 file#=1 block#=105236 blocks=8 obj#=95923 tim=1389859838569331
WAIT #1: nam=’db file scattered read’ ela= 67 file#=1 block#=105264 blocks=8 obj#=95923 tim=1389859838569659
WAIT #1: nam=’db file scattered read’ ela= 67 file#=1 block#=105291 blocks=8 obj#=95923 tim=1389859838569972
WAIT #1: nam=’db file scattered read’ ela= 65 file#=1 block#=105303 blocks=8 obj#=95923 tim=1389859838570278
WAIT #1: nam=’db file scattered read’ ela= 79 file#=1 block#=105325 blocks=8 obj#=95923 tim=1389859838570640
WAIT #1: nam=’db file scattered read’ ela= 63 file#=1 block#=105371 blocks=8 obj#=95923 tim=1389859838571135
WAIT #1: nam=’db file scattered read’ ela= 65 file#=1 block#=105425 blocks=8 obj#=95923 tim=1389859838571486
WAIT #1: nam=’db file scattered read’ ela= 69 file#=1 block#=105474 blocks=8 obj#=95923 tim=1389859838571855
WAIT #1: nam=’db file scattered read’ ela= 65 file#=1 block#=105516 blocks=8 obj#=95923 tim=1389859838572204
WAIT #1: nam=’db file scattered read’ ela= 64 file#=1 block#=105528 blocks=8 obj#=95923 tim=1389859838572530
WAIT #1: nam=’db file scattered read’ ela= 66 file#=1 block#=105601 blocks=8 obj#=95923 tim=1389859838572887
WAIT #1: nam=’db file scattered read’ ela= 65 file#=1 block#=105617 blocks=8 obj#=95923 tim=1389859838573215
WAIT #1: nam=’db file scattered read’ ela= 65 file#=1 block#=105644 blocks=8 obj#=95923 tim=1389859838573561
WAIT #1: nam=’db file scattered read’ ela= 64 file#=1 block#=105676 blocks=8 obj#=95923 tim=1389859838573917
WAIT #1: nam=’db file scattered read’ ela= 66 file#=1 block#=105700 blocks=8 obj#=95923 tim=1389859838574251
WAIT #1: nam=’db file scattered read’ ela= 66 file#=1 block#=105712 blocks=8 obj#=95923 tim=1389859838574650
WAIT #1: nam=’db file scattered read’ ela= 64 file#=1 block#=105759 blocks=8 obj#=95923 tim=1389859838575214
WAIT #1: nam=’db file scattered read’ ela= 66 file#=1 block#=105779 blocks=8 obj#=95923 tim=1389859838575545
WAIT #1: nam=’db file scattered read’ ela= 65 file#=1 block#=105795 blocks=8 obj#=95923 tim=1389859838575886
WAIT #1: nam=’db file scattered read’ ela= 65 file#=1 block#=105820 blocks=8 obj#=95923 tim=1389859838576234
WAIT #1: nam=’db file scattered read’ ela= 63 file#=1 block#=105828 blocks=8 obj#=95923 tim=1389859838576565
WAIT #1: nam=’db file scattered read’ ela= 65 file#=1 block#=105842 blocks=8 obj#=95923 tim=1389859838576902
WAIT #1: nam=’db file scattered read’ ela= 64 file#=1 block#=105871 blocks=8 obj#=95923 tim=1389859838577386
WAIT #1: nam=’db file scattered read’ ela= 66 file#=1 block#=105879 blocks=8 obj#=95923 tim=1389859838577723

— 5.创建一个256k大小extent的表空间,即一个extent可以存放32个块;

CREATE TABLESPACE TBS32B DATAFILE ‘/u01/app/oracle/oradata/ORCL/tbs32b.dbf’ SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K;

ALTER TABLE TB10046 MOVE TABLESPACE TBS32B;

SELECT file_id, block_id, blocks FROM dba_extents WHERE segment_name = ‘TB10046’;

FILE_ID   BLOCK_ID     BLOCKS
———- ———- ———-
8        128         32
8        160         32
8        192         32
8        224         32
8        256         32
8        288         32
8        320         32
8        352         32
8        384         32
8        416         32
8        448         32

FILE_ID   BLOCK_ID     BLOCKS
———- ———- ———-
8        480         32
8        512         32
8        544         32
8        576         32
8        608         32
8        640         32
8        672         32
8        704         32
8        736         32
8        768         32
8        800         32

FILE_ID   BLOCK_ID     BLOCKS
———- ———- ———-
8        832         32
8        864         32
8        896         32
8        928         32
8        960         32
8        992         32
8       1024         32
8       1056         32
8       1088         32
8       1120         32
8       1152         32

FILE_ID   BLOCK_ID     BLOCKS
———- ———- ———-
8       1184         32
8       1216         32

35 rows selected.

ALTER SYSTEM FLUSH BUFFER_CACHE;

ALTER SESSION SET TRACEFILE_IDENTIFIER=’t2′;

ALTER SESSION SET EVENTS ‘10046 trace name context forever, level 12’;

SELECT COUNT(*) FROM TB10046; — 发生全表扫描;

— 6.查看trace文件,发现一次读取32个blocks;

WAIT #1: nam=’db file scattered read’ ela= 114 file#=8 block#=132 blocks=28 obj#=95923 tim=1389860294615193
WAIT #1: nam=’db file scattered read’ ela= 118 file#=8 block#=162 blocks=30 obj#=95923 tim=1389860294616423
WAIT #1: nam=’db file scattered read’ ela= 109 file#=8 block#=194 blocks=30 obj#=95923 tim=1389860294617528
WAIT #1: nam=’db file scattered read’ ela= 111 file#=8 block#=225 blocks=31 obj#=95923 tim=1389860294618635
WAIT #1: nam=’db file scattered read’ ela= 117 file#=8 block#=256 blocks=32 obj#=95923 tim=1389860294619776
WAIT #1: nam=’db file scattered read’ ela= 133 file#=8 block#=289 blocks=31 obj#=95923 tim=1389860294620942
WAIT #1: nam=’db file scattered read’ ela= 142 file#=8 block#=320 blocks=32 obj#=95923 tim=1389860294622095
WAIT #1: nam=’db file scattered read’ ela= 115 file#=8 block#=353 blocks=31 obj#=95923 tim=1389860294623268
WAIT #1: nam=’db file scattered read’ ela= 117 file#=8 block#=384 blocks=32 obj#=95923 tim=1389860294624399
WAIT #1: nam=’db file scattered read’ ela= 113 file#=8 block#=417 blocks=31 obj#=95923 tim=1389860294625493
WAIT #1: nam=’db file scattered read’ ela= 114 file#=8 block#=448 blocks=32 obj#=95923 tim=1389860294626569
WAIT #1: nam=’db file scattered read’ ela= 112 file#=8 block#=481 blocks=31 obj#=95923 tim=1389860294627654
WAIT #1: nam=’db file scattered read’ ela= 117 file#=8 block#=512 blocks=32 obj#=95923 tim=1389860294628730
WAIT #1: nam=’db file scattered read’ ela= 113 file#=8 block#=545 blocks=31 obj#=95923 tim=1389860294629788
WAIT #1: nam=’db file scattered read’ ela= 139 file#=8 block#=576 blocks=32 obj#=95923 tim=1389860294631207
WAIT #1: nam=’db file scattered read’ ela= 123 file#=8 block#=609 blocks=31 obj#=95923 tim=1389860294632340
WAIT #1: nam=’db file scattered read’ ela= 191 file#=8 block#=640 blocks=32 obj#=95923 tim=1389860294633470
WAIT #1: nam=’db file scattered read’ ela= 193 file#=8 block#=673 blocks=31 obj#=95923 tim=1389860294634671
WAIT #1: nam=’db file scattered read’ ela= 131 file#=8 block#=704 blocks=32 obj#=95923 tim=1389860294635781
WAIT #1: nam=’db file scattered read’ ela= 119 file#=8 block#=737 blocks=31 obj#=95923 tim=1389860294636852
WAIT #1: nam=’db file scattered read’ ela= 125 file#=8 block#=768 blocks=32 obj#=95923 tim=1389860294637924
WAIT #1: nam=’db file scattered read’ ela= 126 file#=8 block#=801 blocks=31 obj#=95923 tim=1389860294638974
WAIT #1: nam=’db file scattered read’ ela= 119 file#=8 block#=832 blocks=32 obj#=95923 tim=1389860294640007
WAIT #1: nam=’db file scattered read’ ela= 123 file#=8 block#=865 blocks=31 obj#=95923 tim=1389860294641040
WAIT #1: nam=’db file scattered read’ ela= 116 file#=8 block#=896 blocks=32 obj#=95923 tim=1389860294642112
WAIT #1: nam=’db file scattered read’ ela= 114 file#=8 block#=929 blocks=31 obj#=95923 tim=1389860294643170
WAIT #1: nam=’db file scattered read’ ela= 135 file#=8 block#=960 blocks=32 obj#=95923 tim=1389860294644287
WAIT #1: nam=’db file scattered read’ ela= 183 file#=8 block#=993 blocks=31 obj#=95923 tim=1389860294645459
WAIT #1: nam=’db file scattered read’ ela= 131 file#=8 block#=1024 blocks=32 obj#=95923 tim=1389860294646588
WAIT #1: nam=’db file scattered read’ ela= 116 file#=8 block#=1057 blocks=31 obj#=95923 tim=1389860294647669
WAIT #1: nam=’db file scattered read’ ela= 116 file#=8 block#=1088 blocks=32 obj#=95923 tim=1389860294648735
WAIT #1: nam=’db file scattered read’ ela= 114 file#=8 block#=1121 blocks=31 obj#=95923 tim=1389860294649806
WAIT #1: nam=’db file scattered read’ ela= 119 file#=8 block#=1152 blocks=32 obj#=95923 tim=1389860294650864
WAIT #1: nam=’db file scattered read’ ela= 113 file#=8 block#=1185 blocks=31 obj#=95923 tim=1389860294651919

———————————– 10046查看等待事件的例子 ———————————–

———————————– db_file_multiblock_read_count与执行计划选择的例子 ———————————–

— 1.准备工作;

SELECT owner, count(*) FROM TB10046 GROUP BY owner ORDER BY 2;

OWNER                            COUNT(*)
—————————— ———-
OWBSYS                                  2
APPQOSSYS                               3
SCOTT                                   6
ORACLE_OCM                              8
SI_INFORMTN_SCHEMA                      8
BI                                      8
OUTLN                                   9
ORDPLUGINS                             10
OWBSYS_AUDIT                           12
FLOWS_FILES                            12
PM                                     27

OWNER                            COUNT(*)
—————————— ———-
HR                                     34
IX                                     55
DBSNMP                                 65
OE                                    127
ORDDATA                               248
SH                                    306
EXFSYS                                310
WMSYS                                 316
CTXSYS                                366
SYSTEM                                529
OLAPSYS                               719

OWNER                            COUNT(*)
—————————— ———-
XDB                                   844
MDSYS                                1509
PV                                   2161
APEX_030200                          2406
ORDSYS                               2532
SYSMAN                               3491
PUBLIC                              27702
SYS                                 31132

30 rows selected.

CREATE INDEX IDX_TB10046_HR ON TB10046(owner);

EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, ‘TB10046’, cascade=>TRUE);

— 2.设置db_file_multiblock_read_count参数为8,查看表的执行计划;

ALTER SYSTEM SET db_file_multiblock_read_count=8;

SET AUTOT TRACE EXP;

SQL> SELECT * FROM tb10046 WHERE OWNER=’SYSMAN’;
Execution Plan
———————————————————-
Plan hash value: 3379381082

——————————————————————————–
————–

——————————————————————————–
————–

| 0 | SELECT STATEMENT | | 3268 | 315K| 95 (
0)| 00:00:02 |

| 1 | TABLE ACCESS BY INDEX ROWID| TB10046 | 3268 | 315K| 95 (
0)| 00:00:02 |

|* 2 | INDEX RANGE SCAN | IDX_TB10046_HR | 3268 | | 8 (
0)| 00:00:01 |

——————————————————————————–
————–

Predicate Information (identified by operation id):
—————————————————

2 – access(“OWNER”=’SYSMAN’)

— 3.设置db_file_multiblock_read_count参数为128,查看表的执行计划;

ALTER SYSTEM SET db_file_multiblock_read_count=;128

SQL> SELECT * FROM tb10046 WHERE OWNER=’SYSMAN’;

Execution Plan
———————————————————-
Plan hash value: 3237706262

—————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————–
| 0 | SELECT STATEMENT | | 3268 | 315K| 99 (1)| 00:00:03 |

|* 1 | TABLE ACCESS FULL| TB10046 | 3268 | 315K| 99 (1)| 00:00:03 |

—————————————————————————–

Predicate Information (identified by operation id):
—————————————————

1 – filter(“OWNER”=’SYSMAN’)

———————————– db_file_multiblock_read_count与执行计划选择的例子 ———————————–

性能调优工具02–Sql Access Advisor工具的使用

Posted on 2014-06-01 by royalwzy

SAA(Sql Access Advisor)工具的使用

SAA可以做什么:
1. 在给定的负载上推荐一些物化视图,物化视图日志和索引;
2. 推荐的索引包括Bitmap索引,基于函数的索引和B-Tree索引;
3. 推荐如何创建物化视图,以至于可以实现快速刷新和查询重写;
SAA的使用模型:
1. SQL的来源:
  1. SQL Cache:即当前v$sql视图中记录的sql语句;
  2. User-defined:自定义一个表,记录执行的sql语句和执行sql语句的用户,然后把这个表传给SAA;
  3. STS:使用Sql Tuning Set,主要是从负载中获得;
2. 给出的建议：
  1. Simultaneously considers index solutions, materialized view solutions, or combinations of both;
  2. Considers storage for creation and maintenance costs;
  3. Does not generate drop recommendations for partial workloads;
  4. Optimizes materialized views for maximum query rewrite usage and fast refresh;
  5. Recommends materialized view logs for fast refresh;
  6. Combines similar indexes into a single index;
  7. Generates recommendations that support multiple workload queries;
3. 架构图;
4. 推荐的两种方式:Comprehensive和Limited;
  1. Add new index on table or materialized view;
  2. Drop an unused index;
  3. Modify an existing index by changing the index type;
  4. Modify an existing index by adding columns at the end;
  5. Add a new materialized view;
  6. Drop an unused materialized view;
  7. Add a new materialized view log;
  8. Modify an existing materialized view log to add new columns or clauses;
  9. Comprehensive可以分析1-8所有的推荐;
  10. Limited只能分析1,4,5,7,8推荐;
使用SAA需要的权限:
1. ADVISOR的系统权限;
2. 目标表的SELECT权限,而且这个权限不能从一个角色中获得;
使用SAA工具:
1. 执行负载;
2. 使用EM中图形化界面;
3. 使用脚本调用DMBS_ADVISOR包;
使用DMBS_ADVISOR包:
1. 步骤:
  1. 创建一个任务,并定义参数;
  2. 定义负载;
  3. 生成一些建议;
  4. 查看并应用建议;
2. SAA的工作流;
3. 创建一个任务:DBMS_ADVISOR.CREATE_TASK(DBMS_ADVISOR.SQLACCESS_ADVISOR, :task_id, :task_name);
4. 设置SAA的参数:
  1. 设置任务的参数:DBMS_ADVISOR.SET_TASK_PARAMETER(:task_name, parameter, value);
  2. 设置负载的参数:DBMS_ADVISOR.SET_SQLWKLD_PARAMETER(:workload_name, parameter, value);
5. 创建模板:DBMS_ADVISOR.CREATE_TASK(DBMS_ADVISOR.SQLACCESS_ADVISOR, :task_id, :task_name, template=>:template_name, is_template=>’TRUE’);
6. 创建一个负载:DBMS_ADVISOR.CREATE_SQLWKLD(:workload_name);
  1. SQL Tuning Sets:DBMS_ADVISOR.IMPORT_SQLWKLD_STS();
  2. 用户自定义负载:DBMS_ADVISOR.IMPORT_SQLWKLD_USER();
  3. SQL Cache:DBMS_ADVISOR.IMPORT_SQLWKLD_SQLCACHE();
  4. 单条的SQL语句:DBMS_ADVISOR.ADD_SQLWKLD_STATEMENT();
  5. 某个用户下的SQL语句:DBMS_ADVISOR.IMPORT_SQLWKLD_SCHEMA();
7. 删除负载:DBMS_ADVISOR.DELETE_SQLWKLD(:workload_name);
8. 任务与负载关联:DBMS_ADVISOR.ADD_SQLWKLD_REF(:task_name, :workload_name);
9. 生成建议并把建议保存在SAA的资源库:DBMS_ADVISOR.EXECUTE_TASK(:task_name);
  1. 相应的视图为:dba/user_advisor_recommendations;
  2. 或者使用dbms_advisor.get_task_script生成相应的脚本;
10. SAA的主要建议有:
  1. 创建/删除物化视图;
  2. 创建/删除物化视图日志;
  3. 创建/删除索引;
  4. 收集统计信息;
11. 生成SQL脚本:
  1. 创建DIRECTORY;
  2. 授权给用户;
  3. 生成脚本;
如果只是想调优一条SQL语句的话,可以执行快速调优,使用DBMS_ADVISOR.QUICK_TUNE()过程;
对于物化视图的优化:
1. DBMS_MVIEW.EXPLAIN_MVIEW:查看使用/不使用物化视图的原因;
2. DBMS_MVIEW.EXPLAIN_REWRITE:为没有么有使用查询重写,如果使用了,使用的哪个物化视图;
3. DBMS_ADVISOR.TUNE_MVIEW:生成快速刷新物化视图和生成查询重写的建议;

——————————– 使用SAA工具 ——————————–

— 1.执行负载;

CONNECT / AS SYSDBA;
ALTER USER sh IDENTIFIED BY oracle ACCOUNT UNLOCK;
GRANT DBA TO sh;
ALTER SYSTEM FLUSH shared_pool;
ALTER SYSTEM FLUSH buffer_cache;
CONNECT sh/oracle;
SET autotrace traceonly stat;

SELECT c.cust_last_name, SUM(s.amount_sold) AS dollars, SUM(s.quantity_sold) AS quantity
FROM sales s, customers c, products p
WHERE c.cust_id = s.cust_id AND
s.prod_id = p.prod_id AND
c.cust_state_province IN (‘Dublin’, ‘Galway’)
GROUP BY c.cust_last_name;

SELECT c.cust_id, SUM(amount_sold) AS dollar_sales
FROM sales s, customers c
WHERE s.cust_id = c.cust_id
GROUP BY c.cust_id;

SELECT SUM(unit_cost) FROM costs GROUP BY prod_id;
SELECT * FROM customers WHERE cust_postal_code = ‘83786’;

— 2.使用EM中的SAA工具;

1.Databaes Instance->Advisor Central->SQL Access Advisor;

2.选择SQL语句的来源:选择从内存中的SQL语句;

3.选择推荐的选项:生成索引和物化视图,并使用综合模式;

4.系统创建了一个作业,指定立即执行;

5.查看作业已完成;

6.然后查看和采用相应的建议;

— 3.使用脚本调用DBMS_ADVISOR包;

1.定义变量;

VARIABLE v_task_id NUMBER;
VARIABLE v_task_name VARCHAR2(255);
VARIABLE v_wkld_name VARCHAR2(255);

2.指定任务的名称和负载的名称;
EXECUTE :v_task_name := ‘my_task’;
EXECUTE :v_wkld_name := ‘my_sql_wkld’;

3.删除之前的任务和负载;
BEGIN
dbms_advisor.delete_sqlwkld_ref(:v_task_name, :v_wkld_name);
dbms_advisor.delete_sqlwkld(:v_wkld_name);
dbms_advisor.delete_task(:v_task_name);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/

4.创建一个任务;
EXECUTE DBMS_ADVISOR.CREATE_TASK(DBMS_ADVISOR.SQLACCESS_ADVISOR, :v_task_id, :v_task_name);

5.查看当天前的优化任务;
SELECT * FROM user_advisor_tasks;

6.设置任务的参数,生成所有建议,并使用综合模式;
EXECUTE dbms_advisor.set_task_parameter(:v_task_name, ‘EXECUTION_TYPE’, ‘FULL’);
EXECUTE dbms_advisor.set_task_parameter(:v_task_name, ‘MODE’, ‘COMPREHENSIVE’);

7.创建一个负载;

EXECUTE dbms_advisor.create_sqlwkld(:v_wkld_name);

8.把任务与负载关联;
EXECUTE dbms_advisor.add_sqlwkld_ref(:v_task_name, :v_wkld_name);

9.查看任务与负载的映射关系;

SELECT * FROM user_advisor_sqla_wk_map;

10.导入负载,使用SQL Cache的模式;
VARIABLE v_saved_stmts NUMBER;
VARIABLE v_failed_stmts NUMBER;
EXECUTE DBMS_ADVISOR.IMPORT_SQLWKLD_SQLCACHE(:v_wkld_name, ‘APPEND’, 2, :v_saved_stmts, :v_failed_stmts);

11.查看负载中的SQL语句;
SELECT * FROM user_advisor_sqlw_stmts;

12.执行任务,生成优化建议;
EXECUTE DBMS_ADVISOR.EXECUTE_TASK(:v_task_name);

13.查看优化建议;
SELECT * FROM user_advisor_recommendations;

14.生成优化建议的脚本;

直接查看:SELECT DBMS_ADVISOR.GET_TASK_SCRIPT(:v_task_name) FROM dual;

或者创建目录,生成建议的脚本;

CONNECT / AS SYSDBA;
DROP DIRECTORY ADVISOR_RESULTS;
CREATE OR REPLACE DIRECTORY ADVISOR_RESULTS AS ‘/home/oracle’;
GRANT READ, WRITE ON DIRECTORY ADVISOR_RESULTS TO sh;

CONNECT SH/ORACLE
EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT(:v_task_name), ‘ADVISOR_RESULTS’, ‘advscript.sql’);

——————————– 使用SAA工具 ——————————–

——————————– SAA的快速调优 ——————————–

1.定义变量;

CONNECT sh/oracle;
VARIABLE v_task_name VARCHAR2(30);
EXECUTE :v_task_name := ‘quick_task’;

2.执行快速调优,只能执行一条SQL语句,默认是在当前用户下执行;
EXECUTE dbms_advisor.quick_tune(dbms_advisor.sqlaccess_advisor, :v_task_name, ‘SELECT AVG(amount_sold) FROM sh.sales WHERE promo_id=10’);

3.查看优化建议结果;

SELECT DBMS_ADVISOR.GET_TASK_SCRIPT(:v_task_name) FROM dual;

——————————– SAA的快速调优 ——————————–

性能调优工具01–Sql Tuning Advisor工具的使用

Posted on 2014-06-01 by royalwzy

STA(Sql Tuning Advisor)工具的使用

STA工具其实就是DBMS_SQLTUNE包,要使用STA的话,必须是在CBO模式下使用;
执行DBMS_SQLTUNE包,需要有advisor角色权限;
DBMS_SQLTUNE包介绍:
1. DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text, bind_list, user_name, scope, time_limit, task_name, DESCRIPTION) RETURN VARCHAR2;
  1. sql_text:需要优化的sql语句;
  2. bind_list:绑定的变量列表,默认为NULL;
  3. user_name:要优化的sql语句通过哪个用户执行,默认为NULL;
  4. scope:优化范围,有两个取值,limited-分析时不使用推荐的SQL Profile,大约每个语句1s时间;comprehensive-分析时使用推荐的SQL Profile,可能花费的时间较长,默认值;
  5. time_limit:优化过程的时间限制,默认为TIME_LIMIT_DEFAULT;
  6. task_name:优化任务的名称,默认为NULL;
  7. decription:描述信息,默认为NULL;
2. DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name);
  1. task_name:要执行的优化任务的名称;
3. DBMS_SQLTUNE.REPORT_TUNING_TASK(task_name, type, level, section, object_id, result_limit);
  1. task_name:要查看的优化任务的名称;
  2. type:优化报告的类型,TEXT,HTML或者XML,默认是TEXT;
  3. level:格式化的级别,TYPICAL, BASIC, ALL.默认是TYPICAL;
  4. section:优化报告的部分,FINDING,PLAN,INFORMATION,ERROR或者ALL,默认是ALL;
  5. 调用方法:SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(‘task_name’) from dual;
4. DBMS_SQLTUNE.DROP_TUNING_TASK(task_name);
  1. task_name:要删除的优化任务的名称;
5. DBMS_SQLTUNE.RESET_TUNING_TASK(task_name):重置优化任务的结果;
  1. task_name:优化任务的名称;
  2. 执行过之后就可以重新执行EXECUTE_TUNING_TASK过程了;
一般的步骤:
1. 创建优化任务;
2. 执行优化任务;
3. 显示优化结果;
4. 根据建议来做相应的优化;
5. 删除优化任务;
创建测试用户:SQLTUNE;
创建测试表;
执行查询语句:SELECT COUNT(*) FROM bigtable a, smalltable b WHERE a.object_name = b.object_name;
1. 需要的时间;
2. 执行计划;
3. 统计信息;
创建优化任务;
执行优化任务,并查看优化任务的执行进度;
查询优化结果;
1. 关于这次优化任务的基本信息:如任务名称,执行时间,范围,涉及到的语句,发现问题的类型及个数的信息等等;
2. 发现的问题:两个表没有收集统计信息;
3. 发现的问题:两个表没有索引;
4. 按照优化建议修改前后的执行计划的对比;
按照建议做相应的修改,然后执行语句验证优化的结果;
删除优化任务:
如果需要对多条语句进行优化时,应该使用STS(Sql Tuning Set);
1. STS可以从多种数据源(Cursor Cache, AWR, STS)获取SQL;
2. 创建一个STS:DBMS_SQLTUNE.CREATE_SQLSET(sqlset_name => ‘sts_test’);
3. 使用Cursor Cache加载STS:DBMS_SQLTUNE.LOAD_SQLSET();
4. 查看STS的内容:SELECT * FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(‘sts_test’));
5. 使用STS创建一个优化任务:DBMS_SQLTUNE.CREATE_TUNING_TASK(sqlset_name=>’sts_test’, task_name=>’sta_test’);
使用EM,在EM->Performance->Advisor Central->SQL Advisors->SQL Tuning Advisor;
单条sql的调优可以使用sqltrpt.sql(执行SQL>@?/rdbms/admin/sqltrpt.sql sqlid即可)脚本传入sqlid即可;对于多条sql语句可以使用dbmssqlt.sql脚本,这个脚本给出了dbms_sqltune包的定义和相关的例子;

— 创建测试用户SQLTUNE;

CREATE USER SQLTUNE IDENTIFIED BY “oracle”;
GRANT CONNECT, RESOURCE, ADVISOR TO SQLTUNE;
CONN SQLTUNE/ORACLE;

— 创建测试表;

CREATE TABLE bigtable AS SELECT rownum id, t.* FROM sys.all_objects t;
CREATE TABLE smalltable AS SELECT rownum id, t.* FROM sys.all_objects t;
INSERT INTO bigtable SELECT * FROM bigtable;
INSERT INTO bigtable SELECT * FROM bigtable;
INSERT INTO bigtable SELECT * FROM bigtable;
COMMIT;

— 创建优化任务;

DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := ‘SELECT COUNT(*) FROM bigtable a, smalltable b WHERE a.object_name = b.object_name’;
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text => my_sqltext, user_name => ‘SQLTUNE’, task_name => ‘sta_test’);
END;

— 执行优化任务;

EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK(‘sta_test’);

SELECT task_name,status FROM USER_ADVISOR_TASKS WHERE task_name =’sta_test’;

— 查看优化结果;

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(‘sta_test’) from dual;

— 执行优化建议;

CREATE INDEX IDX_BIGTABLE_OBJECTNAME ON BIGTABLE(‘OBJECT_NAME’);
CREATE INDEX IDX_SMALLTABLE_OBJECTNAME ON SMALLTABLE(‘OBJECT_NAME’);
EXEC dbms_stats.gather_table_stats(USER, ‘BIGTABLE’, CASCADE=>TRUE);
EXEC dbms_stats.gather_table_stats(USER, ‘SMALLTABLE’, CASCADE=>TRUE);

— 删除优化任务;

EXEC DBMS_SQLTUNE.DROP_TUNING_TASK(‘sta_test’);

— 创建SQL Tuning Set创建优化任务;

— create a STS
BEGIN
dbms_sqltune.create_sqlset(sqlset_name => ‘my_sts’);
END;
/

— load STS using cursor cache
DECLARE
l_cur dbms_sqltune.sqlset_cursor;
BEGIN
OPEN l_cur FOR
SELECT VALUE(t)
FROM TABLE(dbms_sqltune.select_cursor_cache(‘sql_id in (”588rxmp05xt7g”,”7rucbfq8vcr7d”)’)) t;

dbms_sqltune.load_sqlset(sqlset_name => ‘sts_test’, populate_cursor => l_cur);
END;
/

— display contents of STS
SELECT * FROM TABLE(dbms_sqltune.select_sqlset(‘sts_test’));

— drop a sql tuning task
BEGIN
dbms_sqltune.drop_tuning_task(task_name => ‘my_sql_tuning_task’);
END;
/

— create a sql tuning task by using STS
DECLARE
l_task_name VARCHAR2(30);
l_sqltext CLOB;
BEGIN
l_task_name := dbms_sqltune.create_tuning_task(sqlset_name => ‘sts_test’, task_name => ‘my_sql_tuning_task’);

END;
/

royalwzy的学习笔记

Monthly Archives: 6 月 2014

调优实践04–利用FORCE_MATCHING_SIGNATURE捕获非绑定变量SQL语句

调优实践03–使用SQL_TRACE和10046事件跟踪SQL的执行

性能调优工具02–Sql Access Advisor工具的使用

性能调优工具01–Sql Tuning Advisor工具的使用