性能调整工具
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
--------------------------------------------------------------------------
| 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