Oracle性能调整工具总结

性能调整工具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, 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/tigerSQL> explain plan for select * from dept where deptno=10;ExplainedSQL> 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_plancost概念cardinality 查询路径—估算树create table eas select * from emp create table dasselect * from dept Explain plan forselectename,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 forselect 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_homesqlplusadmin 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 onset autotrace offset autotrace on explainset autotrace on statisticsset autotrace traceonly autotrace输出内容解释recursive calls：执行语句时、调用的oracle内部语句（如分析所用的sql）和其他语句（如触发器）。测试举例：举例1conn scott/tiger@tiwenset autotrace onalter system flush shared_poolset autotrace onselect * from empselect * from emp 举例2create 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）;beginselectexchage_rate into v_exange from exchage_tablewherebill_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 举例3drop table tppp purge create table tppp（p integer） create or replace trigger t_trigger before insert on tppp for each rowdeclare -- local variables herebeginif:new.p>5 thenraise_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 processeddrop 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、编写高效的trigger2、用过程代替trigger

Oracle 关键字作字段MySQL or条件可以使用索引而避免全表相关资讯 Oracle教程

Oracle中纯数字的varchar2类型和（07/29/2015 07:20:43）
Oracle教程：Oracle中查看DBLink密（07/29/2015 07:16:55）
[Oracle] SQL*Loader 详细使用教程（08/11/2013 21:30:36）

Oracle教程：Oracle中kill死锁进程（07/29/2015 07:18:28）
Oracle教程：ORA-25153 临时表空间（07/29/2015 07:13:37）
Oracle教程之管理安全和资源（04/08/2013 11:39:32）

本文评论查看全部评论（0）