4. TOP SQL抓取类工具根据需要抓取 TOP SQL
物理读、逻辑读、执行次数、解析次数、CPU开销、执行时间等
基本都是通过对V$SQLAREA进行分析
抓住系统中最为关键的SQL
5. TOP SQL抓取查找TOP SQL的主要工具
AWR/STATSPACK/ADDM/ASH报告
EM ADDM分析
SQLA（来自METALINK的工具）
V$SQL/V$SQLAREA
Oracle 9i SQLANALYZER
Oracle 9i EM TOP SQL
TRCA
第三方工具
7. SQL分析类工具开销分析
ADDM/awrsqrpt/sprepsql
set autotrace
执行计划分析
awrsqrpt/sprepsql
explain
set autotrace
10046事件
EM ADDM
9i sql analyzer
PL/SQL DEVELOPER等第三方工具
8. 设置AWR中的TOP SQL属性DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS ( -
retention IN NUMBER DEFAULT NULL,
interval IN NUMBER DEFAULT NULL,
topnsql IN NUMBER DEFAULT NULL);
24. EXPLAINSET STATEMENT_ID = 'text'INTO your plan tableEXPLAIN PLANFOR statement
25. EXPLAIN的例子SQL> EXPLAIN PLAN
2 set statement_id = 'demo01' for
3 select *
4 from products
5 where prod_category = 'Men'
6 and prod_subcategory = 'Jeans - Men';
Explained.
26. 显示EXPLAIN结果SQL> column "Query Plan" format a60
SQL> select id
2 , lpad(' ', 2*level)||operation
3 ||decode(id,0,' Cost = '||position)
4 ||' '||options
5 ||' '||object_name as "Query Plan"
6 from plan_table
7 where statement_id = 'demo01'
8 connect by prior id = parent_id
9 start with id = 0;@?/rdbms/admin/utlxpls.sql
@?/rdbms/admin/utlxplp.sql
27. 显示的例子SQL> select id
2 , lpad(' ',2*level)||operation||
3 decode(id, 0,' Cost = '||position)
4 ||' '||options
5 ||' '||object_name as "Query Plan"
6 from plan_table
7 where statement_id = 'demo01'
8 connect by prior id = parent_id start with id=0; ID Query Plan
----- -----------------------------------------
0 SELECT STATEMENT Cost =
1 TABLE ACCESS BY INDEX ROWID PRODUCTS
2 AND-EQUAL
3 INDEX RANGE SCAN PRODUCTS_PROD_CAT_IX
4 INDEX RANGE SCAN PRODUCTS_PROD_SUBCAT_IX
28. 阅读执行计划1234TABLE ACCESS (BY INDEX ROWID)
classesAND_EQUALINDEX
(RANGE_SCAN)
Prod category indexINDEX
(RANGE_SCAN)
Prod subcategory index
30. 使用V$SQL_PLANSELECT id
, lpad (' ', depth) || operation operation
, options , object_name , optimizer , cost
FROM V$SQL_PLAN
WHERE hash_value = 912244748
AND address = '67D419DC'
START WITH id = 0
CONNECT BY
( prior id = parent_id
AND prior hash_value = hash_value
AND prior child_number = child_number
)
ORDER SIBLINGS BY id, position;ID OPERATION OPTIONS OBJECT_NAME OPT COST
-- --------------- --------------- ---------------------- --- -----
0 SELECT STATEMENT CHO
1 TABLE ACCESS BY INDEX ROWID PRODUCTS
2 AND-EQUAL
3 INDEX RANGE SCAN PRODUCTS_PROD_CAT_IX
4 INDEX RANGE SCAN PRODUCTS_PROD_SUBCAT_I
31. DBMS_XPLAN包（10.2）DBMS_XPLAN.DISPLAY_CURSOR(
sql_id IN VARCHAR2 DEFAULT NULL,
child_number IN NUMBER DEFAULT NULL,
format IN VARCHAR2 DEFAULT 'TYPICAL');
未公开的FORMAT参数‘ADVANCED','PEEKED_BINDS'
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED'));
PLAN_TABLE_OUTPUT
SQL_ID 148pt8ma9936r, child number 0
select * from dual where dummy=:a
Plan hash value: 272002086
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
|* 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - "DUMMY"[VARCHAR2,1]
38. 使用PROFILERdeclare
err number;
begin
err:=DBMS_PROFILER.START_PROFILER (to_char(sysdate,'dd-Mon-YYYY hh:mi:ss'));
--此处执行要测试的PL/SQL存储过程或者程序
err:=DBMS_PROFILER.STOP_PROFILER ;
end;
/ column unit_name format a15 truncate;
column occured format 999999 ;
column line# format 99999 ;
column tot_time format 999.999999 ;
select p.unit_name, p.occured, p.tot_time, p.line# line,
substr(s.text, 1,75) text
from (select u.unit_name, d.TOTAL_OCCUR occured,
(d.TOTAL_TIME/1000000000) tot_time, d.line#
from plsql_profiler_units u, plsql_profiler_data d
where d.RUNID=u.runid and d.UNIT_NUMBER = u.unit_number
and d.TOTAL_OCCUR >0 and u.runid= &RUN_ID) p,
user_source s where p.unit_name = s.name(+) and p.line# = s.line (+)
order by p.unit_name, p.line#;
40. 实验（1）安装PROFILER$ sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.4.0 - Production on Sun Dec 5 19:55:36 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
SQL> @?/rdbms/admin/profload
Package created.
Grant succeeded.
Synonym created.
Library created.
Package body created.
Testing for correct installation
SYS.DBMS_PROFILER successfully loaded.
PL/SQL procedure successfully completed.
41. 实验（2）安装PROFILER[oracle@justdb ~]$ sqlplus scott/******
SQL*Plus: Release 10.2.0.4.0 - Production on Sun Dec 5 20:05:36 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
SQL> @?/rdbms/admin/proftab
Table created.
Comment created.
Table created.
Comment created.
Table created.
Comment created.
Sequence created.
43. 实验（4）创建测试环境create or replace procedure testSeq(N integer)
is
i integer; b integer;
v varchar2(20);
begin
i:=0;
v:=to_char(sysdate,'yyyy-mm-dd:hh24:mi:ss');
dbms_output.put_line(v);
loop
exit when i>N;
i:=i+1;
select sm_idseq.nextval into b from dual;
end loop;
v:=to_char(sysdate,'yyyy-mm-dd:hh24:mi:ss');
dbms_output.put_line(v);
end;
/
44. 实验（5）测试1declare
err number;
begin
err:=DBMS_PROFILER.START_PROFILER ('SEQ CACHE IS 1k ');
testseq(20000);
err:=DBMS_PROFILER.STOP_PROFILER ;
end;
/
45. 实验（6）测试结果1column RUN_COMMENT format a40 truncate;
select runid, run_date, RUN_COMMENT from plsql_profiler_runs order by runid;
column unit_name format a15 truncate;
column occured format 999999 ;
column line# format 99999 ;
column tot_time format 999999.999999 ;
select p.unit_name, p.occured, p.tot_time, p.line# line,
substr(s.text, 1,75) text
from
(select u.unit_name, d.TOTAL_OCCUR occured,
(d.TOTAL_TIME/1000000000) tot_time, d.line#
from plsql_profiler_units u, plsql_profiler_data d
where d.RUNID=u.runid and d.UNIT_NUMBER = u.unit_number
and d.TOTAL_OCCUR >0
and u.runid= &RUN_ID) p,
user_source s
where p.unit_name = s.name(+) and p.line# = s.line (+)
order by p.unit_name, p.line#;
72. set long 100000 longchunksize 100000 linesize 200 head off feedback off echo off
spool report.html
SELECT dbms_sqlpa.report_analysis_task('my_sqlpa_demo_task', 'HTML', 'ALL','ALL') FROM dual;
spool off 通过例子学习手工使用SPA(7）-生成报告
