Oracle性能调整工具总结__VARCHAR2_select_from_INTEGER_BYTE_

当前位置: 数据库>oracle

Oracle性能调整工具总结

来源: 互联网发布时间：2017-04-18

本文导语: 性能调整工具 explain plan，autotrace，tkprof，执行计划和静态统计信息的解读 1、执行计划执行计划的设定 conn sys/pwd@tiwen` as sysdba; CREATE USER TOOL IDENTIFIED BY tool DEFAULT TABLESPACE EXAMPLE TEMPORARY TABLESPACE TEMP PROFILE DEFAULT ACCOUNT ...

性能调整工具

explain plan，autotrace，tkprof，执行计划和静态统计信息的解读

1、执行计划

执行计划的设定

conn sys/pwd@tiwen` 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,

Oracle认证：学好Oracle的六条总结 iis7站长之家_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

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

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

| 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

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

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

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

| 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

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

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

| 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 sys/pwd@tiwen 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 scott/tiger@tiwen

set autotrace on

alter system flush shared_pool

set autotrace on

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

您可能感兴趣的文章:

Oracle 数据库(oracle Database)性能调优技术详解

Oracle收购TimesTen 提高数据库软件性能

关于提高Oracle数据库性能的四个错误认识

用Oracle动态性能视图采集查询调优数

Oracle性能究极优化上第1/2页

用PHP连mysql比oracle数据库性能好

Oracle性能究极优化下

保持Oracle数据优良性能的技巧分享

100分寻求最优化的连接oracle的java程序，请给我讲出理由，我是初学者，在做项目时不想让连接oracle影响我的程序性能

Linux平台下如何监控Oracle数据库的性能

Oracle SQL性能优化系列学习二

性能陷阱：Oracle表连接中范围比较

基于Oracle的高性能动态SQL程序开发

浅谈Oracle性能优化可能出现的问题

如何保持Oracle数据库的优良性能

oracle 时间格式的调整

Oracle tablespace表空间调整

本站(WWW.)旨在分享和传播互联网科技相关的资讯和技术，将尽最大努力为读者提供更好的信息聚合和浏览方式。
本站(WWW.)站内文章除注明原创外，均为转载、整理或搜集自网络。欢迎任何形式的转载，转载请注明出处。