Chinaunix首页 | 论坛 | 博客
  • 博客访问: 10202602
  • 博文数量: 1669
  • 博客积分: 16831
  • 博客等级: 上将
  • 技术积分: 12594
  • 用 户 组: 普通用户
  • 注册时间: 2011-02-25 07:23
个人简介

柔中带刚,刚中带柔,淫荡中富含柔和,刚猛中荡漾风骚,无坚不摧,无孔不入!

文章分类

全部博文(1669)

文章存档

2023年(4)

2022年(1)

2021年(10)

2020年(24)

2019年(4)

2018年(19)

2017年(66)

2016年(60)

2015年(49)

2014年(201)

2013年(221)

2012年(638)

2011年(372)

分类: Oracle

2012-01-17 10:00:39

Oracle性能调整工具总结
[日期:2011-12-29] 来源:csdn  作者:田文
 
 
性能调整工具
 
explain plan,autotrace,tkprof,执行计划和静态统计信息的解读
 
1、 执行计划
 
执行计划的设定
 

conn ` as sysdba;
 
 
 
CREATE USER TOOL
 
  IDENTIFIED BY tool
 
  DEFAULT TABLESPACE EXAMPLE
 
  TEMPORARY TABLESPACE TEMP
 
  PROFILE DEFAULT
 
  ACCOUNT UNLOCK;
 
  -- 2 Rolesfor TOOL
 
  GRANT RESOURCE TO TOOL;
 
  GRANT CONNECT TO TOOL;
 
  ALTER USER TOOL DEFAULT ROLE NONE;
 
  -- 3 SystemPrivileges for TOOL
 
  GRANT CREATE SESSION TO TOOL;
 
  GRANT CREATE TABLE TO TOOL;
 
  GRANT UNLIMITED TABLESPACE TO TOOL;
 
 
 
CREATE GLOBAL TEMPORARY TABLE tool.PLAN_TABLE
 
(
 
  STATEMENT_ID       VARCHAR2(30 BYTE),
 
  PLAN_ID            NUMBER,
 
  TIMESTAMP          DATE,
 
  REMARKS            VARCHAR2(4000 BYTE),
 
  OPERATION          VARCHAR2(30 BYTE),
 
  OPTIONS            VARCHAR2(255 BYTE),
 
  OBJECT_NODE        VARCHAR2(128 BYTE),
 
  OBJECT_OWNER       VARCHAR2(30 BYTE),
 
  OBJECT_NAME        VARCHAR2(30 BYTE),
 
  OBJECT_ALIAS       VARCHAR2(65 BYTE),
 
  OBJECT_INSTANCE    INTEGER,
 
  OBJECT_TYPE        VARCHAR2(30 BYTE),
 
  OPTIMIZER          VARCHAR2(255 BYTE),
 
  SEARCH_COLUMNS     NUMBER,
 
  ID                 INTEGER,
 
  PARENT_ID          INTEGER,
 
  DEPTH              INTEGER,
 
  POSITION           INTEGER,
 
  COST               INTEGER,
 
  CARDINALITY        INTEGER,
 
  BYTES              INTEGER,
 
  OTHER_TAG          VARCHAR2(255 BYTE),
 
  PARTITION_START    VARCHAR2(255 BYTE),
 
  PARTITION_STOP     VARCHAR2(255 BYTE),
 
  PARTITION_ID       INTEGER,
 
  OTHER              LONG,
 
  OTHER_XML          CLOB,
 
  DISTRIBUTION       VARCHAR2(30 BYTE),
 
  CPU_COST           INTEGER,
 
  IO_COST            INTEGER,
 
  TEMP_SPACE         INTEGER,
 
  ACCESS_PREDICATES  VARCHAR2(4000 BYTE),
 
  FILTER_PREDICATES  VARCHAR2(4000 BYTE),
 
  PROJECTION         VARCHAR2(4000 BYTE),
 
  TIME               INTEGER,
 
  QBLOCK_NAME        VARCHAR2(30 BYTE)
 
)
 
ON COMMIT PRESERVE ROWS;
 
 
 
grant all on TOOL.PLAN_TABLE to public;
 
 
 
CREATE PUBLIC SYNONYM PLAN_TABLE FOR TOOL.PLAN_TABLE;
 
 
 
 
 
使用方法:
 
truncate table PLAN_TABL;
 
explain plan select * from emp;
 
select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'));
 
 
 
演示:
 
conn scott/tiger
 
SQL> explain plan for select * from dept where deptno=10;
 
Explained
 
SQL> select plan_table_output fromtable(dbms_xplan.display('plan_table',null,'serial'));
 
 
 
PLAN_TABLE_OUTPUT
 
--------------------------------------------------------------------------------
 
Plan hash value: 3383998547
 
--------------------------------------------------------------------------
 
| Id  |Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
 
--------------------------------------------------------------------------
 
|   0 | SELECTSTATEMENT  |      |    1 |    16 |     4  (0)| 00:00:01 |
 
|*  1 |  TABLE ACCESS FULL| DEPT |     1 |   16 |     4   (0)| 00:00:01|
 
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
 
---------------------------------------------------
 
   1 -filter("DEPTNO"=10)
 
 
 
13 rows selected
 
 
 
执行计划解读:--估算表
 
表v$sql_plan
 
cost概念
 
cardinality
 
 
 
 
 
 
 
查询路径—估算树
 
create table e
 
as select * from emp
 
 
 
create table d
 
as
 
select * from dept
 
 
 
 
 
Explain plan for
 
selectename,dname from d,e where e.deptno=d.deptno
 
 
 
select * from table(dbms_xplan.display());
 
 
 
 
 
Plan hash value: 1127375450
 
 
 
---------------------------------------------------------------------------
 
| Id | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
 
---------------------------------------------------------------------------
 
|   0 | SELECT STATEMENT   |      |   15 |   630 |    7  (15)| 00:00:01 |
 
|*  1 |  HASH JOIN         |     |    15 |   630 |     7  (15)| 00:00:01 |
 
|   2 |   TABLE ACCESS FULL| D    |    4 |    88 |    3   (0)| 00:00:01 |
 
|   3 |   TABLE ACCESS FULL| E    |    15 |   300 |     3   (0)| 00:00:01 |
 
---------------------------------------------------------------------------
 
 
 
Predicate Information (identified byoperation id):
 
---------------------------------------------------
 
 
 
   1 - access("E"."DEPTNO"="D"."DEPTNO")
 
 
 
Note
 
-----
 
   - dynamic sampling used for this statement
 
 
 
Explain plan for
 
select ename,dnamefrom d, (select ename,deptno from e where rownum<2) e where e.deptno=d.deptno
 
 
 
select * from table(dbms_xplan.display());
 
 
 
Plan hash value: 1791846393
 
 
 
-----------------------------------------------------------------------------
 
| Id | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
 
-----------------------------------------------------------------------------
 
|   0 | SELECT STATEMENT     |      |    1 |    42 |    5   (0)| 00:00:01 |
 
|   1 |  NESTEDLOOPS        |      |    1 |   42 |     5  (0)| 00:00:01 |
 
|   2 |   VIEW               |      |    1 |    20 |    2   (0)| 00:00:01 |
 
|*  3 |    COUNTSTOPKEY     |      |      |       |            |          |
 
|   4 |     TABLE ACCESS FULL| E    |    15 |   300 |     2   (0)| 00:00:01 |
 
|*  5 |   TABLE ACCESS FULL  | D    |     1 |    22 |     3   (0)| 00:00:01 |
 
-----------------------------------------------------------------------------
 
 
 
Predicate Information (identified byoperation id):
 
---------------------------------------------------
 
 
 
   3 - filter(ROWNUM<2)
 
   5 -filter("E"."DEPTNO"="D"."DEPTNO")
 
 
 
Note
 
-----
 
   - dynamic sampling used for this statement
 
 
 
 
 
驱动表概念
 
估算树
 
 
 
从左到右 从下到上
 
 
 
autotrace
oracle_home\sqlplus\admin\
 
 
 
conn as sysdba;
 
drop role plustrace;
 
create role plustrace;
 
 
 
grant select on v_$sesstat to plustrace;
 
grant select on v_$statname to plustrace;
 
grant select on v_$mystat to plustrace;
 
grant plustrace to dba with admin option;
 
grant plustrace  to public;
 
 
 
grant select on v_$sesstat to public;
 
grant select on v_$statname to public;
 
grant select on v_$mystat to public;
 
grant plustrace to dba with admin option;
 
grant plustrace  to public;
 
 
 
 
 
grant alter session to public;
 
 
 
使用命令
 
set autotrace on
 
set autotrace off
 
set autotrace on explain
 
set autotrace on statistics
 
set autotrace traceonly
 
 
 
 
 
autotrace输出内容解释
 
recursive calls:执行语句时、调用的oracle内部语句(如分析所用的sql)和其他语句(如触发器)。
 
测试举例:
 
举例1
 
conn
 
set autotrace on
 
alter system flush shared_pool
 
set autotrace on
 
select * from emp
 
select * from emp
 
 
 
 
 
举例2
 
create table exchage_table
 
(
 
bill_code   number(10),
 
exchage_rate  number(16,3)
 
)
 
BILL_CODE
 EXCHAGE_RATE
 
100
 4.678
 
200
 5.235
 
300
 5.430
 
400
 2.654
 
 
 
 
 
 
create or replace functiontoday_exchage(p_code in number) return number is
 
v_exange number(16,3);
 
begin
 
selectexchage_rate into v_exange from exchage_table
 
wherebill_code=p_code;
 
returnv_exange;
 
end;
 
 
 
create table affair
 
(
 
 trans_id  number(10),
 
 bill_code number(10),
 
 balance   number(16,2)
 
)
 
 
 
TRANS_ID
 BILL_CODE
 BALANCE
 
1000
 100
 1234.00
 
2000
 200
 4324.32
 
3000
 300
 65464.23
 
 
 
 
较好的写法为
 
select 
 
trans_id,
 
(select exchage_rate
 
fromexchage_table
 
wherebill_code=affair.bill_code )*balance
 
from affair
 
 
 
举例3
 
drop table tppp purge  
 
create table tppp(p integer)
 
 
 
create or replace trigger t_trigger
 
  before insert on tppp
 
  for each row
 
declare
 
  -- local variables here
 
begin
 
if:new.p>5 then
 
raise_application_error(-20001,'bbbbbbbb');
 
end if;
 
 
 
end t_trigger;
 
 
 
统计信息
 
----------------------------------------------------------
 
         29 recursive calls
 
         19  db block gets
 
         54 consistent gets
 
          0  physical reads
 
       1172  redo size
 
        676  bytes sent via SQL*Net to client
 
        627  bytes received via SQL*Net from client
 
          3  SQL*Net roundtrips to/from client
 
          1  sorts (memory)
 
          0 sorts (disk)
 
          9  rows processed
 
 
 
在一次运行
 
 
 
统计信息
 
---------------------------------------------------------
 
         29  recursive calls
 
          0  db block gets
 
        117  consistent gets
 
          1  physical reads
 
          0  redo size
 
        483  bytes sent via SQL*Net to client
 
        416 bytes received via SQL*Net from client
 
          2  SQL*Net roundtrips to/from client
 
          0  sorts (memory)
 
          0  sorts (disk)
 
          9  rows processed
 
drop  trigger t_trigger;
 
 
 
统计信息
 
----------------------------------------------------------
 
          0 recursive calls
 
          0  db block gets
 
        108  consistent gets
 
          0  physical reads
 
          0  redo size
 
        483  bytes sent via SQL*Net to client
 
        416  bytes received via SQL*Net from client
 
          2  SQL*Net roundtrips to/from client
 
          0  sorts (memory)
 
          0  sorts (disk)
 
          9  rows processed
 
解决方法为
 
1、 编写高效的trigger
 
2、 用过程代替trigger
举例4:
 
自我管理表空间与数据字典表空间
 
本地管理的表空间能够减少递归sql 
 

输出内容: 逻辑I/O  (DB BLOCKS| CONSISTENT GETS)
 
解释
 
对于一个SQL 逻辑I/O越小越好,通常通过SQL调整实现的
 
TKPROF
 
 
使用 TKPROF 工具简介
 
TKPROF 工具简介
 
TKPROF 工具的使用步骤
 
TKPROF 工具如何分析 trace 文件
 
启用TKPROF
如何设置自动跟踪
 
1、设定执行表,autotrace。方法如前所述,这里再重复一边。
用system登录
执行$ORACLE_HOME/rdbms/admin/utlxplan.sql创建计划表
执行$ORACLE_HOME/sqlplus/admin/plustrce.sql创建plustrace角色
如果想计划表让每个用户都能使用,则
SQL>create public synonym plan_table for plan_table;
SQL> grant all on plan_table to public;
 
 
 
2、设定tkprof
 
ALTER SESSION  SETSQL_TRACE = TRUE
 
ALTER SESSION  SETTIMED_STATISTICS = TRUE;
 
alter session set events ‘10046 trace name contextforever,level 12’;
 
alter session set max_dump_file_size=unlimited;
 
alter session set events '10046 trace name context off'
 
获取跟踪文件名称
 
跟踪的信息在user_dump_dest目录下可以找到或通过如下脚本获得文件名称(适用于Win环境,如果是unix需要做一定修改)
 
conn system/pwd
 

SELECT p1.value||'\'||p2.value||'_ora_'||p.spid||'.trc' filename
FROM
v$process p,
v$session s,
v$parameter p1,
v$parameter p2
WHERE p1.name = 'user_dump_dest'
AND p2.name = 'db_name'
AND p.addr = s.paddr
AND s.audsid = USERENV ('SESSIONID')
 
 
 
在unix的目录下
 
 
 
 
有了正确而详细的诊断数据之后,你需要以摘要的形式对其进行查看,这有助于你以最快的速度做出响应。
 
Cmd tkprof path\xxx.prc xxx.txt
 
 
 
报告解读:
 
parse(分析):在共享池中找到该查询(软分析)或者创建该查询的新计划(硬分析)
 
execute(执行):执行查询的所有工作
 
fetch(提取):显示select的提取工作,对于update,则没有内容
 
 
 
count(计数):执行的次数
 
cpu:此阶段cpu的耗时,以毫秒为单位
 
elapsed(占用时间):挂钟时间,如果大于cpu时间,则有等待时间
 
disk(磁盘):执行物理I/O的次数
 
QUERY(查询):检索一致性执行的I/O次数
 
CURRENT(当前):到当前多执行的逻辑I/O次数
 
ROW:此阶段被处理或者受到影响的行
 
如果一个UPDATE语句EXECUTE的QUERY,CURRENT,ROWS分别为2000 1000 500,表示这个语句访问了2000个块找到需要UPDATE的行记录,在UPDATE的时候只访问了1000个块,一共更新了500行。如果只获取很少的数据,而要访问了大量的块,表明SQL与需要优化了。
 
MISSES 缓存命中率:0 表示已经通过软分析
 
OPTIMIZER GOAL(优化程序目标)
 
执行计划:与前面的执行计划相比,增加了各个阶段涉及的行数
 
关闭
 
alter system set events '10046 trace name context off';
 
 
 
更好的方法是使用DBMS_SUPPORT包来激活扩展SQL跟踪:
dbms_support.start_trace(waits=>;true, binds=>;true)
/* code to be traced goes here */
dbms_support.stop_trace()
 
请注意DBMS_SUPPORT 没有文档说明,可能也不是数据库默认安装的一部分。要了解DBMS_SUPPORT的信息,请参考MetaLink ( metalink.oracle.com)。 
 
跟踪别人的代码。如果你想跟踪没有读/写权限的代码,则激活扩展SQL跟踪就有点麻烦了。但也不会难很多。你首先要获得你想跟踪的会话的V$SESSION.SID和V$SESSION.SERIAL#值。然后使用下面的过程调用,可以设置所选会话的TIMED_STATISTICS和MAX_DUMP_FILE_SIZE参数: 
 

dbms_system.set_bool_param_in_session(
   sid     =>; 42,
   serial# =>; 1215,
   parnam  =>; 'timed_statistics',
   bval    =>; true)
dbms_system.set_int_param_in_session(
   sid     =>; 42,
   serial# =>; 1215,
   parnam  =>; 'max_dump_file_size',
   intval  =>; 2147483647)
 

(对于Oracle8 8.1.6以前的版本,你可以用ALTER SYSTEM命令处理这些参数。) 
 
接下来要激活跟踪。有几种方法可以采用,包括下面两个:
 
方法一是使用DBMS_SUPPORT: 
 

dbms_support.start_trace_in_session(
   sid     =>; 42,
   serial# =>; 1215,
   waits   =>; true,
   binds   =>; true)
/* code to be traced executes during this time window */
dbms_support.stop_trace_in_session(
  sid      =>; 42,
  serial   =>; 1215)
 

若想激活扩展SQL跟踪,请不要使用名为SET_SQL_TRACE_IN_SESSION的DBMS_SUPPORT过程。该过程不允许在跟踪文件中指定等待和绑定的数据。 
 
第二种方法更为精致,但在Oracle数据库10g之前的版本中并不支持这种方法。 DBMS_MONITOR包的引入解决了许多复杂诊断数据收集问题,这些问题是由连接共享和多线程操作所引起的。你可以在Oracle数据库10g中指定要跟踪的服务、模块或行动,而不指定要跟踪的Oracle数据库会话: 
 

dbms_monitor.serv_mod_act_trace_enable(
  service_name  =>; 'APPS1',
  module_name   =>; 'PAYROLL',
  action_name   =>; 'PYUGEN',
  waits         =>; true,
  binds         =>; true,
  instance_name =>; null)
/* code to be traced executes during this time window */
dbms_monitor.serv_mod_act_trace_disable(
  service_name  =>; 'APPS1',
  module_name   =>; 'PAYROLL',
  action_name  =>; 'PYUGEN')
 

利用DBMS_MONITOR包,Oracle可为要跟踪的特定的业务操作提供完全支持激活或停止诊断数据收集的方法。
 

在PL/SQL中,由于不能执行alter session,可以使用
  
  dbms_session.set_sql_trace(TRUE);
  
  必须安装DBMS_SESSION包,并"直接"赋给用户alter session的权限。
 
当我们使用sql
 
For Unix: $ sqlplus "/ as sysdba" SQL*Plus: Release 9.2.0.4.0 - Production on Fri Oct 8 12:08:09 2004 Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.  Connected to:Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.4.0 - Production SQL> set echo onSQL> @gettrcnameunixSELECT       d.VALUE         || '/'         || LOWER (RTRIM (i.INSTANCE, CHR (0)))         || '_ora_'         || p.spid         || '.trc' trace_file_name    FROM (SELECT p.spid            FROM v$mystat m, v$session s, v$process p           WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,         (SELECT t.INSTANCE            FROM v$thread t, v$parameter v           WHERE v.NAME = 'thread'             AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,         (SELECT VALUE            FROM v$parameter           WHERE NAME = 'user_dump_dest') dTRACE_FILE_NAME--------------------------------------------------------------------------------/opt/oracle/admin/hsbill/udump/hsbill_ora_29630.trc
 
 
 
For Nt:
 
SELECT    d.VALUE        || '\'        || LOWER (RTRIM (i.INSTANCE, CHR (0)))        || '_ora_'        || p.spid        || '.trc' trace_file_name   FROM (SELECT p.spid           FROM v$mystat m, v$session s, v$process p          WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,        (SELECT t.INSTANCE           FROM v$thread t, v$parameter v          WHERE v.NAME = 'thread'            AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,        (SELECT VALUE           FROM v$parameter          WHERE NAME = 'user_dump_dest') dTRACE_FILE_NAME--------------------------------------------------------------------------------e:\oracle\admin\eygle\udump\eygle_ora_3084.trc
 
阅读(1284) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~