SQL工具

一、EXPLAIN PLAN

1、  SYSTEM下边创建表

@/ora/prod/proddb/9.2.0/rdbms/admin/utlxplan.sql

2、  验证表创建

Desc plan_table;

3、  创建同义词，避免以表的所有者SYSTEM限定表名称的需要

Create public synonym plan_talbe for system.plan_talbe;

4、给予计划的用户在表上进行读取/写入权限

Grant select ,updata,insert ,delete on plan_table to tuner;

4、  以要生成计划的用户登陆

Conn  /

5、  生成想要为之生成执行计划的查询

explain plan

 set statement_id='test'

 into plan_table

 for

 select distinct organization_id from inv.mtl_system_items_b;

6、  查询执行计划

select decode(id,0,'',lpad(' ',2*(level-1))||level||'.'||position)||' '||operation||' '||options||' '||object_name||' '||object_type||' '||decode(id,0,'cost='||position) "EXECUTION PLAN"

from plan_table

connect by prior id=parent_id and upper(statement_id)='TEST' start  with id=0 and upper(statement_id)='TEST';

二、  DBMS_XPLAN程序包

1、

SQL> desc dbms_xplan;

FUNCTION DISPLAY RETURNS DBMS_XPLAN_TYPE_TABLE

 Argument Name                  Type                    In/Out Default?

 ------------------------------ ----------------------- ------ --------

 TABLE_NAME                     VARCHAR2                IN     DEFAULT

 STATEMENT_ID                   VARCHAR2                IN     DEFAULT

 FORMAT                         VARCHAR2                IN     DEFAULT

FORMAT：BASIC，TYPICAL，ALL，SERIAL

2、select * from table(dbms_xplan.display);

3、select * from table(dbms_xplan.display('plan_table','test','all'));

三、SQL自动跟踪

1、验证 PLUS TRACE 作用的存在

select role from dba_roles where role like 'PLUS%';

2、创建这个角色

@/ora/prod/proddb/9.2.0/sqlplus/admin/plustrce.sql

3、创建PLAN_TABLE

4、设置ANTOTRACE 为ON

SET AUTOTRACE TRACEONL EXPLAIN STATISTICS

5、设置时限为ON

SET TIMING ON

6、提交查询

select distinct organization_id from inv.mtl_system_items_b

7、关闭AUTOTRACE

SET AUTOTRACE OFF

四、SQL跟踪和TKPROF

1.通过操作系统进程的ID获取进程信息

SELECT sid,serial#,username FROM v$session b WHERE b.paddr = (SELECT addr FROM v$process c WHERE c.spid =  '15032');

415,925

2.查询v$session视图,获取进程信息

select sid,serial#,username from v$session;

3.启用相关进程sql_trace

SQL> exec dbms_system.set_sql_trace_in_session(415,925,true)

PL/SQL procedure successfully completed.

4.等候一段时间,关闭sql_trace

SQL> exec dbms_system.set_sql_trace_in_session(415,925,false)

PL/SQL procedure successfully completed.

5.检查trace文件

到/ora/prod/proddb/9.2.0/admin/PROD_erpdb/udump/ 下边找到trace文件 prod_ora_15032.trc

6.用 TKPROF 转换格式

tkprof prod_ora_15032.trc 15032

五、使用OEM