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/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

Oracle性能调整工具总结 (一)